MySQL操作记录的方法集合，供以后查看

增

insert into 表名(字段1, 字段2) values (1, 1);
insert into 表名(字段1, 字段2) values (1, 1), (2, 2);    #多条记录

insert into 表名 values (1, 1);　　#不设置字段名，对于自增的字段不能处理，会显示字段数量不匹配
insert into 表名 values (1, 1), (2, 2);    #多条记录　　返回受影响的行数

删

delete from 表 where id = 1;
delete 表1 from 表1 a, 表2 b where a.id = b.id;    #删表1　　返回受影响的行数

改

update 表 set id = 1, name = '张三' where id % 3 = 1;
update 表1 a, 表2 b set a.name = b.name where a.id = b.di;    #多个表　　返回受影响的行数

查

select ...
from 表1 a, 表2 as b　　#from中包括外连接语句，在form之后执行
where a.id = b.id     #where中不能出现分组函数和select中的别名
group by a.id　　#group by之后就可以只用select中的别名了，但是聚合函数在分组之后执行所以不能将聚合函数作为分组依据。
having count(*)>1    #having与group by必须同时存在，mysql中不写group by直接用having会优化为group by null
order by a.id asc, b.id desc
limit 1,4 #从第2条开始取4条记录，第1个数的缺省值为0

查询语句的执行顺序为

from-->left join on-->where-->group by-->count()(select中的)-->having-->select-->distinct-->order by-->limit

表关系图，作为一下示例的依附

行转列

要求为按平均score从高到低，按语文、数学、英语的格式显示score

select b.sid, 
max(case cname when '语文' then score else null end) '语文',    #case when中的'语文'两边的引号必须加
max(case cname when '数学' then score else null end) '数学',
max(case cname when '英文' then score else null end) 英文, 
count(*) 有效课程数, avg(score) '有效平均分'    #起别名时加不加引号都一样，加上也没用
from course a, SC b
where a.cid = b.cid and a.cname in ('语文','数学','英文')
group by b.sid
order by 有效平均分 desc;    #使用别名时不能加引号，在这里的有效平均分两边如果加上引号，这句话就会无效

其结果

其中max的使用要注意，这里max()的作用是取分组之中某一列的最大值。取消分组和聚合函数就可以看出。以下同过两步比较来说明。

1、在未进行行转列时表的情况用如下语句查询

select *
from course a, SC b
where a.cid=b.cid and a.cname in ('语文','数学','英文') and b.sid = 1; #为了减少数据量，这里限制学号为1的同学

其结果

该同学一共上了两门课。然后比较未进行max()函数修饰的语句，并且去掉分组语句，同样限制学号为1。

两图结合后，可以看出对于第一张图的第一条记录，遇到三句case when，不是语文所以null，是数学所以为score，不是英语所以为null。这样的结果就是，原本的一条记录，经过转换后还是一条记录，无法将三科成绩统一成一条记录，所以需要分组。于是在上图的语句后加上分组语句group by b.sid。但是这时会发现，分组后的只会取得第一条记录。如下图

于是需要加上max()来取其最大的值，将三科成绩合一。在这里突然发现，对于avg()来说，似乎操作的是原本未经行行转列的表，因为转换后的表没有score这个选项。它也无法进行一行记录的某几个字段的求值，在这里就是无法进行语文、数学、英语三个字段的横向求平均值。

与此类似的还有：统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select b.cid, b.cname,
sum(case when score>85 then 1 else 0 end) '[100-85]',
sum(case when score>70 and score<85 then 1 else 0 end) '[85-70]',
sum(case when score>60 and score<70 then 1 else 0 end) '[70-60]', 
sum(case when score<60 then 1 else 0 end) '[<60]'
from SC a, course b
where a.cid = b.cid
group by a.cid;

结果图

查询男生、女生人数
select 
sum(case ssex when '男' then 1 else 0 end) 男,
sum(case ssex when '女' then 1 else 0 end) 女
from student;

exists的使用

exists和not exists的使用情况为存在量词或全称量词的出现。理解的总概为：对于from中的表的一条记录，对于该条记录如果满足exists中的条件则返回。not exists则是不满足则返回。一个例子

查询没学过“张老师”课的同学的学号、姓名；
select a.sid, a.sname
from student a
where not exists (　　#2、任意课没学过
    select *
    from teacher b, course c, SC d
    where b.Tname='张老师' and b.tid = c.tid and c.cid= d.cid and a.sid = d.sid); #1、存在学过张老师的课，只要学过一个张老师的课，就会有结果集，这里不需要结果集

查询的过程为

①、在外层student中取一条记录，放入子查询中。

②、执行子查询

③、子查询中如果有结果则丢弃该条student的记录。如果没有结果则返回该条记录。(是丢弃还是返回取决于是exists还是not exists)

④、取下一条student中的记录，继续上述步骤直至完成。

这一查询和in的效果相同。

再看一个两层嵌套的情况。

查询没有学全所有课的同学的学号、姓名；
select a.sid, a.sname
from student a
where exists(　　#3、不否定，不变
    select *
    from course b
    where not exists(　　#2、一层否定，存在没学过
        select * from SC c
        where c.sid = a.sid and c.cid = b.cid)　　#1、任意课都学过，只要有一个没学过就会有结果集，不需要结果集
    );

对于上面的sql语句，我认为的读法是：（返回的意思是作为结果集的一部分）对于外层是，查找某一学生满足条件（则返回）。。。对于子查询是，查找某一课程不满足条件（则返回）。。。综合在一起，查找某名学生满足条件（子查询有返回这个学生就返回），条件是在确定为这名学生的情况下，查找某门课程不满足条件（子查询无返回这个课程就返回），这个条件是在确定是名学生和这门课的情况下c.sid = a.sid and c.cid = b.cid（任意一门课他都学过）。或者说，查找这样的学生，存在某一门课他没有学过。这两层的嵌套我认为更像是两层的for循环，外层的循环是student，内层循环为course。一个student会用来测试所有的course。不满足条件的course会被返回，如果有返回的course外层的循环就能检测到，说明该student满足条件。该student就会返回。

双重否定的情况。全称量词转存在量词，所有都满足-->不存在不满足

查询学过“张老师”所教的所有课的同学的学号、姓名；
select a.sid, a.sname
from student a
where not exists(　　#3、不存在课没学过
    select *
    from course c, teacher d
    where c.tid = d.tid and d.Tname = '张老师' and not exists(　　#2、存在课没学过
        select * from SC b
        where a.sid = b.sid and b.cid = c.cid)　　#1、这句话想表达，任意课都学过
    );

查找某名学生不满足条件（子查询无返回这个学生就返回），条件是在确定为这名学生的情况下，查找某门课程不满足条件（子查询无返回这个课程就返回），这个条件是在确定是名学生和这门课的情况下c.sid = a.sid and c.cid = b.cid（任意一门课他都学过）。有点绕，但该是对的。不满足（不满足（任意课都学过））找这样的学生，不存在某一门张老师的课他没学过。

与上面的双重否定对比，这里贴出一开始我出错的地方，还是这个题，我是这样写的

查询学过“张老师”所教的所有课的同学的学号、姓名；
select a.sid, a.sname
from student a
where not exists(
    select *
    from course c, teacher d     #c和d依次带入后，例如不满足是张老师的条件，不满足条件所以c和d返回，只要有某一个返回就会导致a不能返回
    where not exists(
        select * from SC b
        where a.sid = b.sid and b.cid = c.cid and c.tid = d.tid and d.Tname = '张老师')
    );

这里没有找到任何的结果集，我仔细想了下，是这样认为的。在我注释的那一行中，course和teacher会组合带入n*m个值，n是course的取值个数，m是teacher的取值个数。这样，对于这一查询来说，一个student如果最内层在n*m个测试中有一次不满足，这一student就不会返回了。对于任一的学生，会测试所有老师，不是张老师，这就会导致条件不满足。所以结果为空。（n*m是我个人认为，也可能是n或m等）这个需要小心。

转载于:https://www.cnblogs.com/ant-xu/p/11329276.html