【mysql的DQL简单介绍】

环境准备

[ root@rocky2 ~]#mysql < hellodb_innodb.sql
[ root@rocky2 ~]#mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
6 rows in set (0.00 sec)

mysql> use hellodb

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

查询DQL

select

直接跟字符串跟echo用法差不多

单表操作

students表结构

常用选项

select * from student;                                     #查看表里的所有列，也可指定列
select stuid 学员号,name 姓名, gender 性别 from student;      #列后面跟值可更改显示的列名，别名alias
select * from student where age <=20;                      #where指定符合age <=20的行，where后面跟指定符合条件的子句
select * from student where age <=20 and gender = 'F';     #指定符合age <=20并且gender = 'F'的行，或者用or
select * from student where classid in (1,2,3);            #取符合范围的值
select * from student where age between 20 and 30 ;        #大于20，小于30
select * from student limit3;                              #limit3只显示前三行
select * from student limit 2,3;                           #跳过前两行显示后面的三行
select * from student where classid is null;               #取空值用is，非空 not null
select * from student where name like 'ma%';               #模糊查询，%=*  ，like类似于
select distinct age from student;                          #将查询出的值去重distinct
select count(*) from student;                              #统计表里的记录数量count(*)
select avg(age) from student;                              #取平均值avg(age)
select max(age) from student;                              #取最大值max(age)
select sum(age) from student;                              #总和数sum(age)
select avg(age)/count(*) from student;                     #除法运用
select gender,avg(age) from student group by gender;       #对性别分组group by取各自平均值，注意是取所分组的列或者聚合函数，先分组后进行筛选条件用having，先进行筛选条件后分组可用where
select classid,gender,avg(age) from student group by classid,gender;   #多项分组，空值也会显示
#建议效率法，先分组再过滤
select * from student order by age;                        #根据年龄排序order by，倒序加上desc，    order by desc
select * from student order by age desc,classid;           #优先根据年龄倒数排列，相同值再根据classid的值正排序
select gender,group_concat(name) from student group by gender;  #把gender分组并将对应的值放在一行中

多表查询

子查询

#调用其他子句查询结果
update teachers set age = (select max(age) from student) where id=2;
select * from teachers where age = (select max(age) from student);

联合查询union

竖向合并，需两个表的数据类型相同，且表头类型一样，以防数据不一

#表头用的是第一张表的表头，可改
select stuid,name,age,gender from student union select * from teachers;

#自己跟自己合并会默认去重，可加all取消去重
select * from teachers union all select * from teachers;

交叉连接cross

横向合并，完全组合，不常用

#将student表的每一行都与teacher表的每一行相合并，简单化：11,12,13,21,22,23,31,32,33
select * from student cross join teacher;

内连接inner

挑出两张表中都符合同一条件的值

select * from student inner join teacher on student.teacherid=teacher.tid;

#将重复出现的表名用别名代替，from来自哪个表，students s中间省略不写as，定义别名
select s.stuid,s.name,s.teacherid,t.tid,t.name from students s inner join teacher t on s.teacherid=t.tid;

左右外连接

左外连接left outer

#两张表取左边的全部值和右边所包含的相同左边的值
select * from student s left outer join teacher t on s.teacherid=t.id;

#完全外连接，不取相同部分
select * from student s left outer join teacher t on s.teacherid=t.id where t.tid is null; 

右外连接right outer

#两张表取右边的全部值和左边所包含的相同右边的值
select * from student s right outer join teacher t on s.teacherid=t.id;

#完全外连接，不取相同部分
select * from student s right outer join teacher t on s.teacherid=t.id where s.teacherid is null; 

三张表查询

#取student表的name和scores表的score加courses表的course
select st.name,sc.score,co.course 
from student st inner join scores sc on st.stuid=sc.stuid 
inner join courses co on sc.courseid=co.courseid;

自连接

自己调用自己

#将一张表定义两个别名，然后将两个别名当成两张表来用，函数IFNULL(l.name,'无上级')判断是否为空值，为空则显示'无上级'的内容
select e.name emp_name,IFNULL(l.name,'无上级') leader_name from emp e left join emp l on e.leaderid=l.id;

语句优先级

FROM Clause起始指令 --> WHERE Clause子句 --> GROUP BY分组 --> HAVING Clause筛选条件 -->SELECT查看 --> ORDER BY排序 --> LIMIT限制