第三章 DQL数据查询语言
重点,DQL是我们每天接触编写最多也是最难的sql,该语言用来查询记录,不会修改数据库和表结构:
一、构建数据库
学习之前我们需要创建数据库并填充部分数据:
drop TABLE if EXISTS student;
CREATE TABLE student (
id INT(10) PRIMARY key,
name VARCHAR (10),
age INT (10) NOT NULL,
gander varchar(2)
);
drop TABLE if EXISTS course;
CREATE TABLE course (
id INT (10) PRIMARY key,
name VARCHAR (10) ,
t_id INT (10)
) ;
drop TABLE if EXISTS teacher;
CREATE TABLE teacher(
id INT (10) PRIMARY key,
name VARCHAR (10)
);
drop TABLE if EXISTS scores;
CREATE TABLE scores(
s_id INT ,
score INT (10),
c_id INT (10) ,
PRIMARY key(s_id,c_id)
) ;
表单填充数据:
insert into student (id,name,age,gander)VALUES(1,'白杰',19,'男'),(2,'连宇栋',19,'男'),(3,'邸志伟',24,'男'),(4,'李兴',11,'男'),(5,'张琪',18,'男'),(6,'武三水',18,'女'),(7,'张志伟',16,'男'),(8,'康永亮',23,'男'),(9,'杨涛瑞',22,'女'),(10,'王杰',21,'男');
insert into course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);
insert into teacher (id,name)VALUES(1,'张楠'),(2,'李子豪'),(3,'薇薇姐'),(4,'猴哥'),(5,'八戒');
insert into scores (s_id,score,c_id)VALUES(1,80,1);
insert into scores (s_id,score,c_id)VALUES(1,56,2);
insert into scores (s_id,score,c_id)VALUES(1,95,3);
insert into scores (s_id,score,c_id)VALUES(1,30,4);
insert into scores (s_id,score,c_id)VALUES(1,76,5);
insert into scores (s_id,score,c_id)VALUES(2,35,1);
insert into scores (s_id,score,c_id)VALUES(2,86,2);
insert into scores (s_id,score,c_id)VALUES(2,45,3);
insert into scores (s_id,score,c_id)VALUES(2,94,4);
insert into scores (s_id,score,c_id)VALUES(2,79,5);
insert into scores (s_id,score,c_id)VALUES(3,65,2);
insert into scores (s_id,score,c_id)VALUES(3,85,3);
insert into scores (s_id,score,c_id)VALUES(3,37,4);
insert into scores (s_id,score,c_id)VALUES(3,79,5);
insert into scores (s_id,score,c_id)VALUES(4,66,1);
insert into scores (s_id,score,c_id)VALUES(4,39,2);
insert into scores (s_id,score,c_id)VALUES(4,85,3);
insert into scores (s_id,score,c_id)VALUES(5,66,2);
insert into scores (s_id,score,c_id)VALUES(5,89,3);
insert into scores (s_id,score,c_id)VALUES(5,74,4);
insert into scores (s_id,score,c_id)VALUES(6,80,1);
insert into scores (s_id,score,c_id)VALUES(6,56,2);
insert into scores (s_id,score,c_id)VALUES(6,95,3);
insert into scores (s_id,score,c_id)VALUES(6,30,4);
insert into scores (s_id,score,c_id)VALUES(6,76,5);
insert into scores (s_id,score,c_id)VALUES(7,35,1);
insert into scores (s_id,score,c_id)VALUES(7,86,2);
insert into scores (s_id,score,c_id)VALUES(7,45,3);
insert into scores (s_id,score,c_id)VALUES(7,94,4);
insert into scores (s_id,score,c_id)VALUES(7,79,5);
insert into scores (s_id,score,c_id)VALUES(8,65,2);
insert into scores (s_id,score,c_id)VALUES(8,85,3);
insert into scores (s_id,score,c_id)VALUES(8,37,4);
insert into scores (s_id,score,c_id)VALUES(8,79,5);
insert into scores (s_id,score,c_id)VALUES(9,66,1);
insert into scores (s_id,score,c_id)VALUES(9,39,2);
insert into scores (s_id,score,c_id)VALUES(9,85,3);
insert into scores (s_id,score,c_id)VALUES(9,79,5);
insert into scores (s_id,score,c_id)VALUES(10,66,2);
insert into scores (s_id,score,c_id)VALUES(10,89,3);
insert into scores (s_id,score,c_id)VALUES(10,74,4);
insert into scores (s_id,score,c_id)VALUES(10,79,5);
二、单表查询
1、基本查询
(1)基本语法
查询所有列,其中*表示查询所有列,而不是所有行的意思:
select * from 表名;
查询指定列:
select 列1,列2,列n from 表名;
select `id`,`name`,`age`,`gander` from `student`;
select `id`,`name`,`age` from `student`;
完全重复的记录只显示一次,在查询的列之前添加distinct
(2)列运算
数量类型的列可以做加、减、乘、除等运算:
-- 查询给所有员工工资加1000的结果
select id,name,sal+1000 from employee;
select `id`,`name`,`age`*10 from student;
说明:
1、null加任何值都等于null,,需要用到ifnull()函数。SELECT IFNULL(sal,0) from 表名; 如果薪资列为空,则输出0;
2、将字符串做加减乘除运算,会把字符串当作0。
(3)别名
我们可以给列名起【别名】,因为在查询的结果中列名可能重复,可能名字不够简洁,或者列的名字不满足我们的要求:
select 列名1 (as) 别名1,列名2 (as) 别名2 from 表名;
select `id` `编号`,`name` `名字`,ifnull(`age`,0) as `age` from `student` as s;
只需要在列名后加 as 新列名 ,或是直接加上 新列名即可。
(4)条件控制
条件查询:在后面添加where指定条件,我们在学习update语句时,接触过这里大致是一样的:
-- 条件控制 select * from 表名 where 列名=指定值;
select * from student where id = 3;
select * from student where id in (1,3,7);
select * from student where id >5 ;
select * from student where id between 3 and 7 ;
select * from student where id between 6 and 7 or age > 20;
模糊查询:当你想查询所有姓张的记录。用到关键字like。
select * from student where name like '张_';
select * from student where name like '张%';
(_代表匹配任意一个字符,%代表匹配0~n个任意字符)
2、排序(所谓升序和降序都是从上往下排列)
- 升序: ascend
select * form 表名 order by 列名 asc; asc为默认值可以不写
-降序:descend
select * from 表名 order by 列名 desc;
- 使用多列作为排序条件: 当第一列排序条件相同时,根据第二列排序条件排序(当第二列依旧相同时可视情况根据第三例条件排序):
select * from 表名 order by 列名1 asc, 列名2 desc;
意思是当列名1的值相同时按照列名2的值降序排。
3、聚合函数
1.count:查询满足条件的记录行数,后边可以跟where条件:
如果使用的列值为空,不会进行统计,
我们如果统计真实的表记录条数,最好不要用可以为空的列:
count(*) count(id) count(1)
select count(列名) from 表名;
select max(age) from student where id > 5;
2.max:查询满足条件的记录中的最大值,后边可以跟where条件:
select max(列名) from 表名;
3.min:查询满足条件的记录中的最大值,后边可以跟where条件:
select min(列名) from 表名;
4.sum:查询满足条件的记录中的值的和,后边可以跟where条件:
select sum(列名) from 表名;
5.avg:查询满足条件的记录中的值的平均数,后边可以跟where条件:
select avg(列名) from 表名;
#4、分组查询
顾名思义:分组查询就是将原有数据进行分组统计:
我们举一个例子:将班级的同学按照性别分组,统计男生和女生的平均年龄和成绩。这就是一个典型的分组查询。
基本语法:
select 分组列名,聚合函数1,聚合函数2 from 表名 group by 该分组列名;
分组要使用关键词group by,后边可以是一列,也可以是多个列,分组后查询的列只能是分组的列,或是使用了聚合函数的其他列,其他列不能单独使用。
我们可以这样理解:一旦发生了分组,我们查询的结果只能是所有男生的成绩总和、平均值,而不能查询某一个男生的成绩。
有时我们需要对数据进行帅选,作为分组条件的列和聚合函数:
分组查询前,还可以通过关键字【where】先把满足条件的人分出来,再分组,语法为:
select 分组列,聚合函数 from 表名 where 条件 group by 分组列;
分组查询后,也可以通过关键字【having】把组信息中满足条件的组再细分出来,语法为:
select 分组列,聚合函数 from 表名 where 条件 group by 分组列 having 聚合函数或列名(条件);
例子:
select gander,avg(age) avg_age,sum(age) sum_age from student GROUP BY gander HAVING gander = '男'
5、LIMIT子句
LIMIT用来限定查询结果的起始行,以及总行数,通常用来做分页查询,他是mysql中独有的语法。
例如:
select * from 表名 limit 4,3;
如果一个参数:说明从开始查找三条记录
SELECT id,name,age,gander FROM student limit 3;
如果两个参数:说明从第三行起(不算),向后查三条记录
SELECT id,name,age,gander FROM student limit 3,3;