SQL经典50题

SQL练习50题

测试表格：

1) Student

+------+--------+---------------------+------+

| SId  | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 01   |赵雷   |1990-01-01 00:00:00 |男   |

| 02   |钱电   |1990-12-21 00:00:00 |男   |

| 03   |孙风   |1990-05-20 00:00:00 |男   |

| 04   |李云   |1990-08-06 00:00:00 |男   |

| 05   |周梅   |1991-12-01 00:00:00 |女   |

| 06   |吴兰   |1992-03-01 00:00:00 |女   |

| 07   |郑竹   |1989-07-01 00:00:00 |女   |

| 09   |张三   |2017-12-20 00:00:00 |女   |

| 10   |李四   |2017-12-25 00:00:00 |女   |

| 11   |李四   |2017-12-30 00:00:00 |女   |

| 12   |赵六   |2017-01-01 00:00:00 |女   |

| 13   |孙七   |2018-01-01 00:00:00 |女   |

+------+--------+---------------------+------+

2）Teacher

+------+--------+

| TId  | Tname |

+------+--------+

| 01   |张三   |

| 03   |王五   |

| 02   |李四   |

+------+--------+

3)Course

+------+--------+------+

| CId  | Cname | TId  |

+------+--------+------+

| 01   |语文   |02   |

| 02   |数学   |01   |

| 03   |英语   |03   |

+------+--------+------+

4)SC

+------+------+-------+

| SId  | CId | score |

+------+------+-------+

| 01   | 01  |  80.0 |

| 01   | 02  |  90.0 |

| 01   | 03  |  99.0 |

| 02   | 01  |  70.0 |

| 02   | 02  |  60.0|

| 02   | 03  |  80.0 |

| 03   | 01  |  80.0 |

| 03   | 02  |  80.0 |

| 03   | 03  |  80.0 |

| 04   | 01  |  50.0 |

| 04   | 02  |  30.0 |

| 04   | 03  |  20.0 |

| 05   | 01  |  76.0 |

| 05   | 02  |  87.0 |

| 06   | 01  |  31.0 |

| 06   | 03  |  34.0 |

| 07   | 02  |  89.0 |

| 07   | 03  |  98.0 |

+------+------+-------+



SQL基本函数，聚合函数对一组值执行计算，并返回单个值，也被称为组函数。 聚合函数经常与SELECT 语句的GROUP BY 子句的HAVING一同使用。

Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的，Where中不能使用聚合函数。 Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

1.   查询"01 "课程比" 02"课程成绩高的学生的信息及课程分数

SELECT * FROM (SELECT SId,CId,score FROMSC WHERE CId='01') as t1, (SELECT SId,CId,score FROM SC WHERE CId='02') ast2  WHERE t1.SId=t2.SId andt1.score>t2.score;

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

select * from (select SId,score from scwhere sc.CId='01')as t1,(select SId,score from sc where sc.CId='02') as t2where t1.SId=t2.SId;

1.2  查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为null )

select * from (select SId,score from scwhere sc.CId='01') as t1 LEFT JOIN (select SId,score from sc where sc.CId='02')as t2 on t1.SId=t2.SId;

1.3  查询不存在" 01 "课程但存在" 02 "课程的情况

select * from sc where sc.SId not in(select SId from sc where sc.CId='01') and sc.CId='02';

2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

先算sc表中每个学生的平均值，构成一个新表；再查询student和这个新表。

1) selectstudent.*,t1.avgscore from student,

    -> (select sc.SId,AVG(sc.score)

    -> as avgscore from sc group by sc.Sidhaving avgscore>=60) as t1

-> wherestudent.SId=t1.SId;

2) selectstudent.*,t1.avgscore

from student inner JOIN(

select sc.SId,AVG(sc.score)as avgscore

from sc

GROUP BY sc.SId

HAVINGAVG(sc.score)>=60)as t1 on student.SId=t1.SId

3. 查询在 SC 表存在成绩的学生信息

select distinctstudent.* from student, SC where student.SId=SC.SId;

DISTINCT:用于返回唯一不同值，去重！

4.  查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )(类似第2题)

select student.SId,student.Sname,t1.number,t1.sumscore

from student,(select sc.SId,COUNT(CID) as number, SUM(score) as sumscore from

SC group by sc.SId) as t1 where student.SId=t1.SId;

4.1 查有成绩的学生信息

select * from studentwhere EXISTS (select * from sc where sc.SId=student.SId);

EXISTS:

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS指定一个子查询，检测行的存在。

5.  查询「李」姓老师的数量

1）select count(*) from teacher where left(teacher.Tname,1)='李';

Left(b,1):取列字符的第一个字。

2）select * from teacher where teacher.Tname like ‘李%’;

Like模糊查询

 6.  查询学过「张三」老师授课的同学的信息

select student.*

    -> from teacher  ,course ,student,sc

    -> where teacher.Tname='张三'

    -> and  teacher.TId=course.TId

    -> and  course.CId=sc.CId

    -> and  sc.SId=student.SId;

7.  查询没有学全所有课程的同学的信息

1) select student.* from (select count(CId) as num from course) ast1,student where student.SId not in (select sc.SId from sc group by sc.SIdhaving count(sc.CId)>=t1.num);

2) select student.* from student where student.SId not in (select

sc.SId from sc group by sc.SId having count(sc.CId)>=(select count(CId) from

course));

8.  查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

select DISTINCT student.* from student,sc where sc.SId=student.SId and sc.CId in (select CId from sc where SId='01');

9.  查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

1) select

sc.CId from sc where sc.SId='01':查找‘01’号同学学过的课 ；

2）select student.SId,t.Cid from student ,(select sc.CId from sc where

sc.SId='01') as t：映射所有同学和‘01’学过的所有课；                  

3）(selectstudent.SId,t.Cid from student ,(select sc.CId from sc where sc.SId='01') as t)as t1 left join sc ont1.SId=sc.SId and t1.CId=sc.CId where sc.CId is null )：没有学过对应CId的同学

4）not in：学过对应CId的同学

select * from student wherestudent.SId not in (

select t1.Sid from (select student.SId,t.Cid fromstudent ,(select sc.CId from sc where sc.SId='01') as t )as t1 left join sc on t1.SId=sc.SIdand t1.CId=sc.CId where sc.CId is null ) and student.SId !='01'

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

先查找张三老师教授的课；再用left join连接主表副标表；再用not in查询没有学过的学生姓名

select *

    -> from student

    -> where student.SId not in (

    -> select t1.SId

    -> from

    -> (select student.SId,t.CId

    -> from student ,(select sc.CId from scwhere sc.SId='01') as t )as t1

    -> left join sc on t1.SId=sc.SId andt1.CId=sc.CId

    -> where sc.CId is null )

    -> and student.SId !='01';

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select

student.SId,student.Sname,avg(sc.score) from student,sc WHERE

student.SId=sc.SId and sc.score<60 GROUPBY student.SId,student.Sname HAVING count(*)>=2;

12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select student.* fromstudent,(select sc.score,sc.SId from sc where CId ='01') as t1 wheret1.SId=student.SId and t1.score<60 ORDER BY t1.score DESC;

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

selectsc.SId,sc.CId,sc.score,t1.avg from sc,(select sc.SId,avg(sc.score) as

avg from sc Group by sc.SId)

as t1 where sc.SId=t1.SIdORDER BY t1.avg DESC;

14. 查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

重点：sum(case when…then 1 else 0 end)语句，sum 中case when的else 需要为0，而count case when的else 只能为null，不能为0

select sc.CId,max(sc.score)as 最高分,min(sc.score)as 最低分,AVG(sc.score)as 平均分,count(*)as 选修人数,sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,sum(case when sc.score>=70 and sc.score<80 then 1 else 0

end )/count(*)as 中等率,sum(case when

sc.score>=80 and sc.score<90 and sc.score<80 then 1 else 0 end

)/count(*)as 优良率,sum(case when

sc.score>=90 then 1 else 0 end )/count(*)as 优秀率

    -> from sc

    -> GROUP BY sc.CId

    -> ORDER BY count(*)DESC,sc.CId asc;

15. 按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺

函数：Rank()

over(PARTITION BY cid order by score desc) as grade_rank

select cid, score, Rank()over(PARTITION BY cid order by score desc) as grade_rank from sc order by cid;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

函数：dense_rank() over(partition by CId order by score DESC) as grand_rank

select CId,score,dense_rank()over(partition by CId order by score DESC) as grand_rank from sc ORDER BY CId;

16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

注意：这里是对总成绩排名，每个SId只对应一个总成绩，所以不需要PARTITION BY Sid去进行分类

select student.sid,student.sname, sum(sc.score),rank() over (order by sum(sc.score) DESC) asgrade_rank from sc,student where sc.SId=student.SId GROUP BYstudent.SId,student.Sname ORDER BY sum(sc.score) DESC;

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select student.sid,student.sname, sum(sc.score),dense_rank() over (order b

y sum(sc.score) DESC) asgrade_rank from sc,student where sc.SId=student.SId GROUP

 BY student.SId,student.Sname ORDER BYsum(sc.score) DESC;

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比

18. 查询各科成绩前三名的记录

思路：前三名转化为若大于此成绩的数量少于3即为前三名。

select * from sc where(select count(*) from sc as a where sc.CId=a.CId and sc.score

19. 查询每门课程被选修的学生数

select cid,count(*) from scGROUP BY cid;

20. 查询出只选修两门课程的学生学号和姓名

select student.sid,student.sname  from student, sc  where student.sid =

sc.sid group bystudent.SId,student.Sname having count(sc.cid) = 2;

21. 查询男生、女生人数

select count(*) from studentGROUP BY Ssex;

+----------+

| count(*) |

+----------+

|        4 |

|        8 |

+----------+

22. 查询名字中含有「风」字的学生信息

select * from student where

student.Sname like '%风%';

+------+--------+---------------------+------+

| SId  | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

+------+--------+---------------------+------+

23. 查询同名同性学生名单，并统计同名人数

select Sname,Ssex, count(*)as num from student GROUP BY Sname,Ssex having count(*)>1;

+--------+------+-----+

| Sname  | Ssex | num |

+--------+------+-----+

| 李四   |女   |   2 |

+--------+------+-----+

24. 查询 1990 年出生的学生名单

关键字：YEAR

select * from student whereYEAR(Sage)=1990;

+------+--------+---------------------+------+

| SId  | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |

| 02   |钱电   | 1990-12-21 00:00:00 |男   |

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

| 04   |李云   | 1990-08-06 00:00:00 |男   |

+------+--------+---------------------+------+

25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select avg(sc.score) from sc

GROUP BY sc.CId ORDER BY avg(sc.score) DESC,sc.CId

asc;

+---------------+

| avg(sc.score) |

+---------------+

|      72.66667 |

|      68.50000 |

|      64.50000 |

+---------------+

26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

1)selectstudent.SId,student.Sname,t1.avg from (select avg(sc.score) as avg,sc.SId fromsc GROUP BY sc.SId) as t1,student where student.SId=t1.SId and avg>=85;

+------+--------+----------+

| SId  | Sname | avg      |

+------+--------+----------+

| 01   |赵雷   | 89.66667 |

| 07   |郑竹   | 93.50000 |

+------+--------+----------+

2)selectstudent.SId,student.Sname,avg(sc.score) from student,sc where student.SId=sc.SIdGROUP BY student.SId,student.Sname having avg(sc.score)>=85;

+------+--------+---------------+

| SId  | Sname | avg(sc.score) |

+------+--------+---------------+

| 01   |赵雷   |     89.66667 |

| 07   |郑竹   |     93.50000 |

+------+--------+---------------+

27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

selectstudent.Sname,sc.score,course.Cname from student, sc,course where

student.SId=sc.SId and

sc.CId=course.CId and course.Cname='数学' having sc.score

<60;

+--------+-------+--------+

| Sname  | score | Cname  |

+--------+-------+--------+

| 李云   |  30.0 |数学   |

+--------+-------+--------+

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select

student.Sname,student.SId,sc.score,sc.CId from student left joinsc on student.sid=sc.sid;

+--------+------+-------+------+

| Sname  | SId | score | CId  |

+--------+------+-------+------+

| 赵雷   | 01   | 80.0 | 01   |

| 赵雷   | 01   | 90.0 | 02   |

| 赵雷   | 01   | 99.0 | 03   |

| 钱电   | 02   | 70.0 | 01   |

| 钱电   | 02   | 60.0 | 02   |

| 钱电   | 02   | 80.0 | 03   |

| 孙风   | 03   | 80.0 | 01   |

| 孙风   | 03   | 80.0 | 02   |

| 孙风   | 03   | 80.0 | 03   |

| 李云   | 04   | 50.0 | 01   |

| 李云   | 04   | 30.0 | 02   |

| 李云   | 04   | 20.0 | 03   |

| 周梅   | 05   | 76.0 | 01   |

| 周梅   | 05   | 87.0 | 02   |

| 吴兰   | 06   | 31.0 | 01   |

| 吴兰   | 06   | 34.0 | 03   |

| 郑竹   | 07   | 89.0 | 02   |

| 郑竹   | 07   | 98.0 | 03   |

| 张三   | 09   | NULL | NULL |

| 李四   | 10   | NULL | NULL |

| 李四   | 11   | NULL | NULL |

| 赵六   | 12  |  NULL| NULL |

| 孙七   | 13   | NULL | NULL |

+--------+------+-------+------+

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

selectstudent.Sname,course.Cname,sc.score from student,sc,course wherestudent.sid=sc.sid and course.cid=sc.cid and sc.score>70;

+--------+--------+-------+

| Sname  | Cname | score |

+--------+--------+-------+

| 赵雷   |语文   |  80.0 |

| 赵雷   |数学   |  90.0 |

| 赵雷   |英语   |  99.0 |

| 钱电   |英语   |  80.0 |

| 孙风   |语文   |  80.0 |

| 孙风   |数学   |  80.0 |

| 孙风   |英语   |  80.0 |

| 周梅   |语文   |  76.0|

| 周梅   |数学   |  87.0 |

| 郑竹   |数学   |  89.0 |

| 郑竹   |英语   |  98.0 |

+--------+--------+-------+

30. 查询不及格的课程

selectsc.SId,course.CId,course.Cname from sc,course where sc.CId=course.

CId and sc.score<60;

+------+------+--------+

| SId  | CId | Cname  |

+------+------+--------+

| 04   | 01  |语文   |

| 04   | 02  |数学   |

| 04   | 03  |英语   |

| 06   | 01  |语文   |

| 06   | 03  |英语   |

+------+------+--------+

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

selectstudent.Sname,student.SId from student,sc where student.SId=sc.SId and CId='01'and sc.score>80;

Empty set (0.02 sec)

32. 求每门课程的学生人数

select count(*) from sc GROUPBY CId;

+----------+

| count(*) |

+----------+

|        6 |

|        6 |

|        6 |

+----------+

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

1) select student.*,sc.score

from student,sc,course,teacher where student.SId=sc.SId and course.CId=sc.CId

and course.TId=teacher.TId and teacher.Tname='张三' ORDER BY sc.score DESCLIMIT 1;

+------+--------+---------------------+------+-------+

| SId  | Sname | Sage                | Ssex |score |

+------+--------+---------------------+------+-------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |  90.0 |

+------+--------+---------------------+------+-------+


select student.*,sc.score

from student,sc,course,teacher where student.SId=sc.SId and course.CId=sc.CId

and course.TId=teacher.TId and teacher.Tname='张三' ORDER BY sc.score DESCLIMIT 2;

+------+--------+---------------------+------+-------+

| SId  | Sname | Sage                | Ssex |score |

+------+--------+---------------------+------+-------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |  90.0 |

| 07   |郑竹   | 1989-07-01 00:00:00 |女   |  89.0 |

+------+--------+---------------------+------+-------+


34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

selectstudent.*,sc.score,sc.CId from student,teacher,course,sc

    -> where student.SId=sc.SId

    -> and sc.CId=course.CId

    -> and teacher.TId=course.TId

    -> andsc.score=(select

max(sc.score) from sc,course,teacher where teacher.TId=course.TId and

sc.CId=course.CId and teacher.Tname='张三');

+------+--------+---------------------+------+-------+------+

| SId  | Sname | Sage                | Ssex | score | CId  |

+------+--------+---------------------+------+-------+------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |  90.0 | 02  |

| 07   |郑竹   | 1989-07-01 00:00:00 |女   |  90.0 | 02  |

+------+--------+---------------------+------+-------+------+

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

1) select distinct t1.* from(select * from sc) as t1,(select * from sc) as

t2 where t1.score=t2.scoreand t1.CId!=t2.CId and t1.SId=t2.SId;

+------+------+-------+

| SId  | CId | score |

+------+------+-------+

| 03   | 02  |  80.0 |

| 03   | 03  |  80.0 |

| 03   | 01  |  80.0 |

+------+------+-------+

[if !supportLists]2) [endif]select DISTINCT t1.* from (select * from sc) as t1 INNER JOIN(select * f

rom sc) as t2 ont1.score=t2.score and t1.CId!=t2.CId and t1.SId=t2.SId;

+------+------+-------+

| SId  | CId | score |

+------+------+-------+

| 03   | 02  |  80.0 |

| 03   | 03  |  80.0 |

| 03   | 01  |  80.0 |

+------+------+-------+

36. 查询每门功成绩最好的前两名

select * from sc as t1 where (select count(*) from sc as t2 where t1.CId=t2.CId and

t1.score<t2.score)<2ORDER BY t1.CId;

+------+------+-------+

| SId  | CId | score |

+------+------+-------+

| 01   | 01  |  80.0 |

| 03   | 01  |  80.0 |

| 01   | 02  |  90.0 |

| 07   |02   | 90.0 |

| 01   | 03  |  99.0 |

| 07   | 03  |  98.0 |

+------+------+-------+

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。

1) select sc.CId,count(*) from sc GROUP BY sc.CId HAVINGcount(*)>5;

+------+----------+

| CId  | count(*) |

+------+----------+

| 01   |       6 |

| 02   |       6 |

| 03   |       6 |

+------+----------+

2) select t1.CId,t1.num from (select sc.CId,count(*) as num from scGROUP BY

 sc.CId) as t1;

+------+-----+

| CId  | num |

+------+-----+

| 01   |   6|

| 02   |   6|

| 03   |   6|

+------+-----+

38. 检索至少选修两门课程的学生学号

select count(*) from sc GROUPBY sc.SId HAVING count(*)>=2;

+----------+

| count(*) |

+----------+

|        3 |

|        3 |

|        3 |

|        3 |

|        2 |

|        2 |

|        2 |

+----------+

39. 查询选修了全部课程的学生信息

select student.* from studentwhere not exists (select * from course where not exists(select * from sc whereSId=student.SId and sc.CId=course.CId));

+------+--------+---------------------+------+

| SId  | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |

| 02   |钱电   | 1990-12-21 00:00:00 |男   |

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

| 04   |李云   | 1990-08-06 00:00:00 |男   |

+------+--------+---------------------+------+

select * from sc

where SId=student.SId and sc.CId=course.Cid:所有已经选择过课程的学生及相应课程；

select * from course where

not exists(select * from sc where SId=student.SId and sc.CId=course.CId): 所有没有被选择的课

select student.*

from student where not exists: 所有没有未选择课程的学生，即选择了所有课程的学生信息。

40. 查询各学生的年龄，只按年份来算

时间函数的使用

select

student.SId,student.Sname,(year(now())-year(Sage))as age fromstudent;

+------+--------+------+

| SId  | Sname | age  |

+------+--------+------+

| 01   |赵雷   |   30|

| 02   |钱电   |   30|

| 03   |孙风   |   30|

| 04   |李云   |   30|

| 05   |周梅   |   29|

| 06   |吴兰   |   28|

| 07   |郑竹   |   31|

| 09   |张三   |    3|

| 10   |李四   |    3|

| 11   |李四   |    3|

| 12   |赵六   |    3|

| 13   |孙七   |    2|

+------+--------+------+

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

时间函数的使用

select

student.SId,student.Sname,TIMESTAMPDIFF(year,student.Sage,CURDATE())as age fromstudent;

+------+--------+------+

| SId  | Sname | age  |

+------+--------+------+

| 01   |赵雷   |   30|

| 02   |钱电   |   29|

| 03   |孙风   |   30|

| 04   |李云   |   29|

| 05   |周梅   |   28|

| 06   |吴兰   |   28|

| 07   |郑竹   |   30|

| 09   |张三   |    2|

| 10   |李四   |    2|

| 11   |李四   |    2|

| 12   |赵六   |    3|

| 13   |孙七   |    2|

+------+--------+------+

42. 查询本周过生日的学生

select student.*

from student where WEEK(sage)=WEEK(now());

43. 查询下周过生日的学生

select student.*

from student where WEEK(sage)=WEEK(now())+1;

44. 查询本月过生日的学生

select student.* from student

where MONTH(sage)=MONTH(now());

45. 查询下月过生日的学生

select student.* from student

where MONTH(sage)=MONTH(now())+1;




补充1：DELETE,DROP,TRUNCATE区别

（1）   DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE

TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2）   表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

（3）   一般而言，drop > truncate > delete

（4）   应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

（5）   TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

（6）   truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7）   delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的tigger,执行的时候将被触发。

（8）   truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（9）   在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名 速度快,而且效率高,因为:

truncate table

在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE

TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用DELETE。如果要删除表定义及其数据，请使用DROP TABLE 语句。

（12） 对于由 FOREIGN

KEY 约束引用的表，不能使用TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。


补充2：count(1),count(*),count(列名)

执行效果上：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。


执行效率上：

列名为主键，count(列名)会比count(1)快

列名不为主键，count(1)会比count(列名)快

如果表多个列并且没有主键，则 count（1） 的执行效率优于count（*）

如果有主键，则select count（主键）的执行效率是最优的

如果表只有一个字段，则select count（*）最优。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,590评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 86,808评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,151评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,779评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,773评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,656评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,022评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,678评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 41,038评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,659评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,756评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,411评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,005评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,973评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,203评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,053评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,495评论 2赞 343

推荐阅读更多精彩内容