数据库(MySQL)-DQL数据查询语言

        DQL(Data Query Language 数据查询语言)的用途是查询数据库数据，如select语句。其中，可以根据表的结构和关系分为单表查询和多表联查。
单表查询

        单表查询：针对数据库中的一张数据表举行查询
全字段查询

        语法：select 字段名 from 表名

1. #推荐
2. select sid,sname,birthday,ssex,classid from student;
3. #不推荐，不利于SQL优化
4. SELECT * from student;

复制代码

结果：

 部分字段查询

1. SELECT sname from student;

复制代码

结果：

字段起名

        语法：select 旧字段名 as 新字段名，旧字段名 ‘新字段名’，...， 旧字段名 ‘新字段名’  from 表名

1. select sname as '姓名', birthday '生日' from student;

复制代码

结果：

添加字段

        语法：select 旧字段名，...，旧字段名，‘新字段值’  新字段名 from 表名

1. SELECT sname,'猿究院' 学校 from student;

复制代码

结果：
 

1. select sid,sname,birthday,ssex,classid,'猿究院' 学校 from student;

复制代码

去重

使用distinct去重

        语法： select distinct 须要去重的值 from 表名

1. select distinct ssex from student;

复制代码

结果：

        要注意的是，所有字段都相同时,distinct才去重。若有一个陈同学 男和陈同学 女，我们不应该将其视为去重对象：

1. select distinct sname,ssex from student;

复制代码

结果：

使用group by去重

条件查询

        使用【where】条件子句举行查询。
单条件查询

        语法：select * from 表名 where 条件；

1. # 在student表中查询sid等于5的数据
2. select * from student where sid=5;
3. select * from student where sid<>5;

复制代码

结果：

多条件查询

        使用"and" 和"or"来连接多个条件

1. #查找1班的女同学
2. select * from student where classid=1 and ssex='女';

复制代码

结果：

         那我们想查找sid为3，6，9的门生信息呢？

1. SELECT * FROM student
2. WHERE sid=3 OR sid=200 OR sid=6;

复制代码

        也可以使用in关键字：

1. SELECT * FROM student
2. WHERE sid in(3,6,9);

复制代码

         结果：

 含糊查询

        含糊符号：① "%"：匹配任意多个字符
                         ② ”_“：匹配任意一个字符
        首先我们先插入几条数据：

1. insert into student (sname)
2. values('卫小佳'),('小佳佳'),('卫公主'),('宇宙无敌美女佳佳'),('卫美女');

复制代码

我们想查找关于‘卫小佳’的信息，就可以通过含糊查询举行查找：

1. SELECT *FROM student WHERE sname LIKE '%佳%'

复制代码

 结果：

1. SELECT *FROM student WHERE sname LIKE '卫__';
2. SELECT *FROM student WHERE sname LIKE '卫%';

复制代码

结果：

 查找null和非null值

关键字：is null

1. SELECT * FROM student WHERE birthday IS NULL;

复制代码

结果：

关键字：is not null 

1. select * from student where birthday is not null;

复制代码

结果：

常用聚合函数

1.count():统计个数
语法：select count(字段\常量\*) from 表名  
注意：count()方法不统计null
统计行数：

1. SELECT COUNT(sid) from student;
2. SELECT COUNT('a') from student; -- 不推荐
3. SELECT COUNT(1) from student;
4. SELECT COUNT(*) from student;

复制代码

 2.max():求最大值
3.min():求最小值
4.sum():求和
5.avg():求平均

1. SELECT COUNT(score) from sc;
2. SELECT sum(score) from sc;
3. SELECT avg(score) from sc;
4. SELECT max(score) from sc;
5. SELECT min(score) from sc;

复制代码

6.综合
案例1. 统计出一共有多少场测验，平均分，总成绩、最高分、最低分

1. select count(*),sum(score),avg(score),max(score),min(score) from sc;

复制代码

结果：

 分组-group by

关键字:group by
案例1.

1. select ssex,count(*) from student group by ssex;

复制代码

结果：

案例2.查询各班有多少人

1. select classid,count(*) from student group by classid;

复制代码

结果：

案例3.查看成绩表中每个同学的总分和平均分

1. select sid,sum(score),avg(score) from sc group by sid;

复制代码

运行结果：

案例4.统计平均分不合格的同学

1. SELECT sid,avg(score) FROM sc
2. WHERE avg(score)<60 GROUP BY sid;

复制代码

         运行后发现报错：

        缘故原由是：where语句是用来判断每一条子句的，反面不能跟聚合函数，我们可以使用having关键字来判断。

1. select sid,avg(score) from sc group by sid having avg(score)<60;

复制代码

结果：

排序-order by

升序-asc

使用ASC关键字，也可以不写，默认为升序排序:

1. select * from sc order by score;
2. select * from sc order by score asc;

复制代码

结果：

降序-desc

        使用dsc关键字举行降序排序，不可省略：

1. select * from sc order by score desc;

复制代码

结果：

综合排序

        案例：先按score降序，若相同，再按cid升序排序

1. select * from sc score desc,cid asc;

复制代码

结果：

分页-limit

        语法：select * from student limit 开始位置 步长，位置=页码-1*步长

1. SELECT * from student LIMIT 0,3

复制代码

结果：

2.

1. SELECT * from student LIMIT 1,3

复制代码

 结果：

3. 找到成绩合格的总分数排名第二的 sid总成绩

1. select sid,sum(score) from sc
2. group by sid
3. having sum(score)>60
4. order by sum(score) desc
5. limit 1,1;

复制代码

结果：

多表联查

        多表联查：针对数据库中两张或两张以上的表同时举行查询
        多表联查可以通过连接运算，即将多张通过主外键关系关联在一起举行查询，以下五张表是我们的数据：

内联查询

 非等值查询

        非等值查询就是将两个表做了笛卡尔积运算，运行结果的逻辑时杂乱的

1. SELECT * FROM student,class;

复制代码

 

等值查询

        只有完全满足条件(主外键关系)的数据才气显示结果
案例1：查询出门生和班级的信息

1. select * from student,class
2. where student.classid=class.classid;

复制代码

结果：

案例2： 5张表联查

1. select * from student,class,teacher,sc,course
2. where student.classid=class.classid and
3. course.Tid=teacher.Tid and
4. course.Cid=sc.Cid and
5. sc.Sid=student.sid;

复制代码

结果：

案例3：查询学过张三老师课程的门生的学号姓名

1. select sid,sname from student,teacher,sc,course
2. where course.Tid=teacher.Tid and
3. course.Cid=sc.Cid and
4. sc.Sid=student.sid and
5. tname='张三';

复制代码

结果：

案例4：查询每个门生的平均成绩

1. select sname,classname,avg(score) 'avg'
2. from student,class,sc
3. where student.classid=class.classid and student.Sid=sc.Sid
4. group by student.sid;

复制代码

结果：

inner join on内联 

        关键字：inner join on 两表的关系
        内联查询得当于：表少但数据量大,内存占用小，io高
案例1：查询出门生和班级的信息

1. select * from student
2. inner join class on student.classid=class.classid;

复制代码

结果：

 案例2：男同学的班级信息

1. SELECT * FROM student
2. INNER JOIN class on student.classid=class.classid
3. WHERE ssex='男';

复制代码

结果：

案例3：五表联查

1. SELECT * FROM studen
2. INNER JOIN class on student.classid=class.classid
3. INNER JOIN sc on sc.Sid=student.sid
4. INNER JOIN course ON course.Cid=sc.Cid
5. INNER JOIN teacher on course.Tid=teacher.Tid;

复制代码

结果：

案例4：查询每门课程的平均成绩 显示:课程名称 老师名称 平均成绩

1. SELECT Cname,Tname,avg(sc.score) from sc
2. inner JOIN course ON course.Cid=sc.Cid
3. INNER JOIN teacher on teacher.Tid=course.Tid
4. GROUP BY course.Cid;

复制代码

结果：

外联查询

重点要找到主查表：student
LEFT JOIN ON 左外联

主表(student)在 jion的左边：

1. SELECT *FROM student
2. LEFT JOIN class on student.classid=class.classid;

复制代码

结果：

 RIGHT JOIN ON 右外联

主表(student)在 jion的右边

1. SELECT *FROM class
2. RIGHT JOIN student on student.classid=class.classid;

复制代码

结果：

综合

案例1：查询所有的门生都学过多少门课程

1. select sname,count(cname) '课程数' from student
2. left join sc on student.sid=sc.sid
3. LEFT JOIN course ON sc.cid=course.Cid
4. group by student.sid

复制代码

结果：

案例2：查询没有班级的同学

1. SELECT * FROM student
2. LEFT JOIN class ON class.classid=student.classid
3. WHERE class.classid is null;

复制代码

结果：

案例3：查询没有同学的班级

1. SELECT * FROM class
2. LEFT JOIN student on class.classid=student.classid
3. WHERE student.sid is not NULL;

复制代码

结果：

并集-UNION

注意：UNION有去重机制(所有参数必须完全相同才会被去重)
案例1：查询所有人的名字

1. SELECT sname FROM student
2. UNION
3. SELECT tname FROM teacher

复制代码

结果：

案例2：查询所有人的姓名和性别(在student表中，性别的范例是char,而在teacher表中，性别的范例是int,但是差别范例的字段可以合并)

1. SELECT sname,ssex FROM student
2. UNION
3. SELECT tname ,tsex FROM teacher

复制代码

结果：

//差别列数量的结果集不可以合并：

1. SELECT sname,ssex,sid FROM student
2. UNION
3. SELECT tname ,tsex FROM teacher

复制代码

结果：

//起别名给第一个结果集才有效：

1. SELECT sname 姓名,ssex 性别 FROM student
2. UNION
3. SELECT tname ,tsex FROM teacher

复制代码

结果：

1. SELECT sname ,ssex  FROM student
2. UNION
3. SELECT tname 姓名,tsex 性别 FROM teacher

复制代码

结果：

案例3： 查询没有班级的同学和查询没有同学的班级

1. SELECT * FROM student
2. LEFT JOIN class ON class.classid=student.classid
3. WHERE class.classid is null
4. UNION
5. SELECT * FROM student
6. RIGHT JOIN  class on class.classid=student.classid
7. WHERE student.sid is not NULL;

复制代码

结果：

全连接

去重

案例：要获取没有班级的同学、查询没有同学的班级还要既有班级又有同学的

1. SELECT * FROM student
2. LEFT JOIN class ON class.classid=student.classid
3. UNION
4. SELECT * FROM student
5. RIGHT JOIN  class on class.classid=student.classid

复制代码

不去重

1. SELECT * FROM student
2. LEFT JOIN class ON class.classid=student.classid
3. UNION ALL
4. SELECT * FROM student
5. RIGHT JOIN  class on class.classid=student.classid

复制代码

 

子连接

        子查询又叫内部查询，子查询必须放在小括号中，子查询的效率极低
1.WHERE子查询

案例1：找出最大id的门生信息

1. select * from student
2. where sid=(select max(sid) from student)

复制代码

结果：(先实行子查询，查找出最大的id，根据最大i查出门生信息)

案例2：查询每个班id最大的门生

1. #子句：查询出每个最大的id
2. #select max(sid) from student group by classid
4. select * from student
5. where sid in(
6. select max(sid) from student group by classid
7. )

复制代码

结果：

案例3：查询学过张三老师课程的门生

1. select * drom student where sid in(
2.     select sid from sc where cid=(
3.         select cid from course where tid=(
4.             select tid from teacher where tname='张三')))

复制代码

结果：

案例4：查询没学过张三老师课程的门生

1. select * drom student where sid not in(
2.     select sid from sc where cid=(
3.         select cid from course where tid=(
4.             select tid from teacher where tname='张三')))

复制代码

结果：

2.FROM子查询

案例1：查询人数大于5的班级 显示：班级 人数
方法1：不使用子查询

1. select classname count(*) from class
2. left join student on class.classid=student.classid
3. group by class.classid
4. having count(*)>5

复制代码

结果：

方法2：使用子查询

1. select classname,人数 from class
2. left join
3. (select classid,count(*) 人数 from student
4. group by classid) t1
5. on class.classid=t1.classid
6. where 人数>5

复制代码

结果：

3.EXISTS子查询

用法：子句有结果，父查询实行；子句没有结果，父查询不实行：

1. select * from teacher
2. where exists(SELECT * FROM student where classid=1)

复制代码

子句有结果：

父查询实行：

4.ANY\SOME\ALL子查询

案例1：查询1班比2班最低成绩高的门生
方法1：使用子查询

1. //子查询先查询出2班的最低成绩
2. /*
3. SELECT min(score) from sc
4. LEFT JOIN student on sc.sid=student.Sid
5. where sc.cid=2;
6. */
7. select student.* from sc
8. left join student on sc.sid=student.Sid
9. where student.classid=1 and score>(
10.     SELECT min(score) from sc
11.     LEFT JOIN student on sc.sid=student.Sid
12.     where sc.cid=2;
13. )

复制代码

方法2：使用any

1. SELECT DISTINCT student.* FROM sc
2. LEFT JOIN student on sc.sid=student.Sid
3. WHERE student.classid=1 and score >any(
4. SELECT score from sc
5.         LEFT JOIN student on sc.sid=student.Sid
6.         where student.classid=2)

复制代码

结果：

 案例2：查询1班比2班最高成绩高的门生
使用all关键字：

1. SELECT DISTINCT student.* FROM sc
2. LEFT JOIN student on sc.sid=student.Sid
3. WHERE student.classid=1 and score >all(
4. SELECT score from sc
5.         LEFT JOIN student on sc.sid=student.Sid
6.         where student.classid=2)

复制代码

结果：

结果集的控制语句

1.IF(expr1,expr2,expr3)

参数：
-- expr1 条件
-- expr2 条件建立，显示数据
-- expr3 条件不建立，显示数据
//例如在teacher表中，性别是通过int范例表示的，当我们想让结果集按我们规定的:1代表女，0代表男来显示，就可以使用if控制语句：

1. select tid,tname,if(tsex=1,'女','男') 性别,tbirthday,taddress from teacher;

复制代码

结果：

2.IFNULL(expr1,expr2)

参数：
-- expr1 字段
-- expr2 字段为null时显示的默认值
//查询student表中的数据，当birthday为null时显示"石头里蹦出来的"：

1. select sidsname,ifnull(birthday,'石头里蹦出来的') 生日,ssex from student

复制代码

结果：

3.CASE WHEN THEN END

这个语句类似于java中的switch语句，详细用法如下：

1. select tid,tname
2. case tsex
3.     when 0 then '男'
4.     when 1 then '女'
5.     else '保密'
6. END '性别' ,tbirthday FROM teacher

复制代码

结果：

注意：这个语句自带break语句：

1. SELECT score,
2. CASE
3.         when score>=90 THEN 'A'
4.         when score>=80 THEN 'B'
5.         when score>=70 THEN 'C'
6.         when score>=60 THEN 'D'
7.         when score <60 THEN '不及格'
8. end '等级' from sc;

复制代码

结果：

案例1：统计各个分数段的人数
思绪：通过CASE WHEN THEN END语句可以只显示出满足该成绩段的成绩，其余不显示：

1. SELECT
2. CASE WHEN score<=100 and score>=90 then score end '100-90',
3. CASE WHEN score<=90 and score>=70 then score end '90-70',
4. CASE WHEN score<=70 and score>=60 then score end '70-60',
5. CASE WHEN score<60 then score end '不及格'
6. from sc

复制代码

结果：

然后可以直接通过count()统计每个分数段的行数(count不统计null值），得出的行数就是该分数段的人数：

1. SELECT  '人数' 分数段,
2. count(CASE WHEN score<=100 and score>=90 then score end)  '100-90',
3. count(CASE WHEN score<=90 and score>=70 then score end)  '90-70',
4. count(CASE WHEN score<=70 and score>=60 then score end) '70-60',
5. count(CASE WHEN score<60 then score end)  '不及格'
6. FROM sc

复制代码

结果：

方法2：

1. SELECT '100-90' 分数段 ,count(score) 分数 FROM sc
2. WHERE score<=100 and score>=90
3. UNION
4. SELECT '90-70' 分数段 ,count(score) 分数 FROM sc
5. WHERE score<=90 and score>=70
6. UNION
7. SELECT '70-60' 分数段 ,count(score) 分数 FROM sc
8. WHERE score<=70 and score>=60
9. UNION
10. SELECT '不及格' 分数段 ,count(score) 分数 FROM sc
11. WHERE score<=100 and score<60

复制代码

 结果：

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。