1、删除除了id号不同,其他都相同的学生冗余信息
学生表 如下:
id号 姓名 分数
1 张三 69
2 李四 89
3 张三 69
创建表
create table tablename(id int auto_increment primary key,name varchar(20),code varchar(20));
insert into tablename values(null,"菜菜","100");
insert into tablename values(null,"嘿嘿","200");
insert into tablename values(null,"菜菜","100");
delete from tablename where id not in(select min(id) myid from
tablename group by name,code );
在mysql中，不能先select出同一表中的某些值，再update这个表。
oracle就不会出现这样的情况，解决方法：
delete from tablename where id not in(select myid from (select min(id)
myid from tablename group by name,code ) as t);
2、用一条SQL语句 查询出每门课都大于80分的学生姓名
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
创建表
create table student (id int auto_increment primary key,
name varchar(20),
kemu varchar(20),
code varchar(20));
插入数据
insert into student values(null,"张三","语文","98");
insert into student values(null,"张三","数学","75");
insert into student values(null,"小李","语文","98");
insert into student values(null,"小李","数学","97");
查询：
select distinct name from student where name not in(
select distinct name from student where code <80);
3、.每个月份的发生额都比101科目多的科目
请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。
数据库名：JcyAudit，数据集：Select * from TestDB
准备数据的sql代码：
创建表：
drop table if exists TestDB;
create table TestDB(id int auto_increment primary key,
AccID varchar(20),
Occmonth varchar(20),
DebitOccur bigint
);
准备数据：
insert into TestDB values(null,"101","1995-1-1",110);
insert into TestDB values(null,"101","1995-2-1",120);
insert into TestDB values(null,"101","1995-3-1",130);
insert into TestDB values(null,"101","1995-4-1",140);
insert into TestDB values(null,"101","1995-5-1",150);
insert into TestDB values(null,"101","1995-6-1",160);
insert into TestDB values(null,"101","1995-7-1",170);
insert into TestDB values(null,"101","1995-8-1",180);
insert into TestDB values(null,"101","1995-9-1",190);
insert into TestDB values(null,"101","1995-10-1",200);
insert into TestDB values(null,"101","1995-11-1",210);
insert into TestDB values(null,"101","1995-12-1",220);
insert into TestDB values(null,"102","1995-1-1",120);
insert into TestDB values(null,"102","1995-2-1",130);
insert into TestDB values(null,"102","1995-3-1",140);
insert into TestDB values(null,"102","1995-4-1",150);
insert into TestDB values(null,"102","1995-5-1",160);
insert into TestDB values(null,"102","1995-6-1",170);
insert into TestDB values(null,"102","1995-7-1",180);
insert into TestDB values(null,"102","1995-8-1",190);
insert into TestDB values(null,"102","1995-9-1",220);
insert into TestDB values(null,"102","1995-10-1",210);
insert into TestDB values(null,"102","1995-11-1",220);
insert into TestDB values(null,"102","1995-12-1",230);
insert into TestDB values(null,"103","1995-1-1",120);
insert into TestDB values(null,"103","1995-2-1",130);
insert into TestDB values(null,"103","1995-3-1",140);
insert into TestDB values(null,"103","1995-4-1",150);
insert into TestDB values(null,"103","1995-5-1",160);
insert into TestDB values(null,"103","1995-6-1",170);
insert into TestDB values(null,"103","1995-7-1",100);
insert into TestDB values(null,"103","1995-8-1",190);
insert into TestDB values(null,"103","1995-9-1",220);
insert into TestDB values(null,"103","1995-10-1",210);
insert into TestDB values(null,"103","1995-11-1",220);
insert into TestDB values(null,"103","1995-12-1",230);
查询数据：
(select * from TestDB where AccID="101") as db101
select distinct AccID from TestDB where AccID not in(
select t1.AccID from TestDB as t1 inner join
(select * from TestDB where AccID="101") as db101
on t1.Occmonth=db101.Occmonth and t1.DebitOccur<=db101.DebitOccur
);
sql语言包括：
数据定义DDL:(Create Alter Drop table) Create /Drop Index等
数据操作：select delete update insert
数据控制:grank,revoke
什么是存储过程，优点？
存储过程是一些预编译的SQL语句。
存储过程是一组记录集，由一些T-SQL语句组成的代码块，这些代码块实现了一些特定的功能（比如单表或多表的增删查改），然后给代码块取个名字，在需要用的时候调用它。
存储过程是一个预编译的代码块，执行效率比较高
一个存储过程代替了大量的SQL语句，可以降低了网络的通信量
一定程度上确保数据安全。
索引是什么，有什么作用和优缺点？
索引是用于提高数据表数据访问速度的数据库对象，
事务：
事务是并发控制的基本单位，事务操作中要么都执行，要么都不执行，它是不可分割的工作单位。比如银行转账工作，一个账号扣款另一个账号增款，这两个操作都要执行，要么都不执行，所以把他们看作一个事务来处理。
事务有4个特性（ACID）：
原子性：Atomic ,事务中的操作被看成一个最小的逻辑单位，这些操作要么全部都成功，要么都失败。
一致性(Consistemcy)：事务操作失败的话，应该会滚到最初状态
隔离性(Isolation)：多个事务之间是互不影响，多个用户对数据并发访问，不影响数据的正确性和完整性
持久性(Durability)：事务处理后，事务的处理结果存储在数据库，影响是永久的
网友评论