一、DQL数据查询语言
-
单表查询
6.条件查询
- 准备表和数据
CREATE TABLE student3 (
id int,
name varchar(20),
age int,
sex varchar(5),
address varchar(100),
math int,
english int
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
2.比较运算符
在查询条件中，可以使用多种比较运算符来表示查询条件。
=等于、 >大于、 <小于、 <=小于等于、 >=大于等于、 <>或!=不等于
示例1：查询math分数大于80分的学生
SELECT * FROM student3 WHERE math>80
示例2：查询english分数小于或等于80分的学生
SELECT * FROM student3 WHERE english<=80;
示例3：查询age等于20岁的学生
SELECT * FROM student3 WHERE age=20;
示例4：查询age不等于20岁的学生
SELECT * FROM student3 WHERE age!=20;
SELECT * FROM student3 WHERE age<>20;
3.逻辑运算符
and(&&) 多个条件同时满足
or(||) 多个条件其中一个满足
not(!) 不满足
示例1：查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student3 WHERE age>35 AND sex='男';
示例2：查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student WHERE age>35 OR sex='男';
示例3：查询id是1或3或5的学生
SELECT * FROM student3 WHERE id=1 OR id=3 OR id=5;
关于not，在后面的案例中讲解！
4.指定范围内查询 in
语法
SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2...);
in里面的每个数据都会作为一次条件，只要满足条件的就会显示。
示例
查询id是1或3或5的学生
SELECT * FROM student3 WHERE id IN (1,3,5)
说明
相当于使用or连接多个条件。
示例2
查询id不是1或3或5的学生
SELECT * FROM student3 WHERE id NOT IN (1,3,5);
5.范围
BETWEEN 值1 AND 值2，表示从值1到值2范围，包括开头值又包括结尾值。
比如：age BETWEEN 80 AND 100 相当于： age>=80 && age<=100
示例：查询english成绩大于等于75，且小于等于90的学生
SELECT * FROM student3 WHERE english>=75 AND english<=90;
SELECT * FROM student3 WHERE english BETWEEN 75 AND 90;
6.模糊查询like
语法
SELECT * FROM 表名 WHERE 字段名 like '通配符字符串';
满足通配符字符串规则的数据就会显示出来。
MySQL通配符有两个：
%: 表示0个或多个字符(任意个字符)
_: 表示一个字符
示例1：查询姓马的学生
SELECT * FROM student3 WHERE NAME LIKE '马%';
示例2：查询姓名中包含'德'字的学生
SELECT * FROM student3 WHERE NAME LIKE '%德%';
示例3：查询姓马，且姓名有三个字的学生
SELECT * FROM student3 WHERE NAME LIKE '马__';
7.为空查询 is null
查询某个字段为空（null）的数据，不是使用=null（null和任何值都不相等），而是使用is null来进行判断。
示例
查询english成绩时null的学生信息
select * from student3 where english is null
· 排序
通过ORDER BY子句，可以将查询出的结果进行排序，排序只影响显示结果，不会影响数据库中数据的顺序。
语法
SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC];
ASC: 升序，默认是升序
DESC: 降序
1.单列排序
单列排序就是使用一个字段排序。
示例
查询所有数据，使用年龄降序排序：
SELECT * FROM student3 ORDER BY age DESC;
2.组合排序
组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。 上面的例子中，年龄是有相同的。当年龄相同再使用math进行排序。
语法
SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC];
示例
查询所有数据，在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序：
SELECT * FROM student3 ORDER BY age DESC, math DESC;
-
单行函数
单行函数是指对于每一行数据进行计算后得到一行输出结果。SQL单行函数根据数据类型分为字符函数、数字函数、日期函数、转换函数等。
1.数值函数
1.abs(x)
返回 x 的绝对值
示例
SELECT ABS(-1) -- 返回1
2.ceil(x)
返回大于或等于 x 的最小整数（向上取整）
示例
SELECT CEIL(1.5) -- 返回2
3.floor(x)
返回小于或等于 x 的最大整数（向下取整）
示例
SELECT FLOOR(1.5) -- 返回1
4.rand()
返回 0 到 1 的随机数
示例
SELECT RAND() --0.93099315644334
5.round(x)
返回离 x 最近的整数(四舍五入取整)
示例
SELECT ROUND(1.23456) --返回1
2.字符串函数
1.concat(s1,s2...sn)
字符串 s1,s2 等多个字符串合并为一个字符串
示例
SELECT CONCAT("SQL ", "Runoob ", "Google ", "Facebook") AS ConcatenatedString;
返回 SQLRunoobGooogleFacebook
2.locate(s1,s)
从字符串 s 中获取 s1 的开始位置
示例
SELECT LOCATE('st','myteststring'); -- 5
3.lower (s)
将字符串 s 的所有字母变成小写字母
示例
SELECT LOWER('RUNOOB') -- runoob
4.upper(s)
将字符串转换为大写
示例
SELECT UPPER("runoob"); -- RUNOOB
5.replace(s,s1,s2)
将字符串 s2 替代字符串 s 中的字符串 s1
示例
SELECT REPLACE('abc','a','x') --xbc
6.substr(s, start, length)
从字符串 s 的 start 位置截取长度为 length 的子字符串
示例
SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
7.trim(s)
去掉字符串 s 开始和结尾处的空格
示例
SELECT TRIM(' RUNOOB ') AS TrimmedString;
8.reverse(s)
反转字符串
示例
SELECT reverse(' dog'); -- 返回god
3.时间日期函数
1.now()
返回系统当前时间(包括年月日时分秒)
示例
SELECT now()
2.sysdate()
返回系统当前时间(包括年月日时分秒)
示例
SELECT sysdate()
3.curdate()
返回当前日期(年月日)
示例
select curdate()
4.curtime()
返回当前时间(时分秒)
示例
select curtime()
5.month(date)
返回参数日期的月份值
示例
select MONTH('2020-11-12')
6.week(date)
返回参数日期是一年中的第几个星期
示例
select week('2020-11-12')
7.day(date)
返回参数日期的日值
示例
select day('2020-11-12')
8.date_add(date,INTERVAL expr type)
在参数日期上增加相应的时间
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值：
| MICROSECOND |
| SECOND |
| MINUTE |
| HOUR |
| DAY |
| WEEK |
| MONTH |
| QUARTER |
| YEAR |
| SECOND_MICROSECOND |
| MINUTE_MICROSECOND |
| MINUTE_SECOND |
| HOUR_MICROSECOND |
| HOUR_SECOND |
| HOUR_MINUTE |
| DAY_MICROSECOND |
| DAY_SECOND |
| DAY_MINUTE |
| DAY_HOUR |
| YEAR_MONTH |
示例
select date_add('2020-11-12 12:11:22',interval 3 MONTH)
4.流程控制函数
1.if(condition,expr1,expr2)
conditon条件为true时返回expr1。condition条件为false时，返回expr2。
示例
select if(5>3,'conditonistrue','conditionisfalse')
2.ifnull(expr1,expr2)
expr1不为null时返回expr1。expr1为null时返回expr2。
示例
用此函数解决前面null运算结果为null的问题
解决方式
因为english有null值，所以用ifnull函数处理english
select name,math+ifnull(english,0) as total from student3
5.其他函数
- version()
查看数据库版本
示例
select version()
2.user()
查看数据库当前用户
示例
select user()
3.md5(expr)
返回参数md5加密后的值
示例
select md5('aaa')
· 聚合函数
聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。
聚合函数会忽略空值。
五个聚合函数：
count： 统计指定列记录数，记录为NULL的不统计
sum： 计算指定列的数值和，如果不是数值类型，那么计算结果为0
max： 计算指定列的最大值
min： 计算指定列的最小值
avg： 计算指定列的平均值，如果不是数值类型，那么计算结果为0
1.count函数
统计指定列记录数
示例
查询总条数
SELECT COUNT(*) FROM student3;
示例2
查询年龄大于40的总人数
SELECT COUNT(*) FROM student3 WHERE age>40;
说明：先查询后进行聚合函数统计
示例3
SELECT COUNT(english) FROM student3
说明：忽略了null值
2.sum函数
计算指定列的数值和，如果不是数值类型，那么计算结果为0
示例
计算math的总和
SELECT SUM(math) FROM student3;
3.avg函数
计算指定列的平均值，如果不是数值类型，那么计算结果为0
示例
查询数学成绩平均分
SELECT AVG(math) FROM student3;
4.max函数
计算指定列的最大值
示例
查询数学成绩最高分
SELECT MAX(math) FROM student3;
5.min函数
计算指定列的最小值
示例
查询数学成绩最低分
SELECT MIN(math) FROM student3;
-
分组
分组查询是指使用 GROUP BY语句对查询信息进行分组，相同数据作为一组。
语法
SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
1.分组查询
GROUP BY怎么分组的？将分组字段结果中相同内容作为一组
示例1
SELECT * FROM student3 GROUP BY sex;
这句话会将sex相同的数据作为一组。
第一步：将sex相同的数据作为一组，分为男，女两组。
第二步：返回每组的第一条数据，作为查询结果。
说明
以上的操作是将分组字段结果中相同内容作为一组，并且返回每组的第一条数据。这样的操作显然没有什么意义。而且在有些数据库中（例如oracle）中，这样的语句是错误的！
分组的目的就是为了统计，一般分组会跟聚合函数一起使用。
示例2
SELECT SUM(math), sex FROM student3 GROUP BY sex;
是将每组的math进行求和，返回每组统计的结果。
聚合函数是在分组后运行，统计每组的数据
注意事项
当我们使用某个字段分组，在查询的时候最好将这个字段查询出来，否则看不到数据属于哪组的。
示例3
查询男女各多少人
SELECT sex, COUNT(*) FROM student3 GROUP BY sex;
2、分组时的条件过滤
示例
查询年龄大于25岁的人，按性别分组,统计每组的人数
先过滤掉年龄小于25岁的人；再分组；最后统计每组的人数
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex;
示例2
查询年龄大于25岁的人，按性别分组，统计每组的人数，并只显示性别人数大于2的数据。
错误示范
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;
一个SQL语句中不应该出现两个where子句！
使用having子句解决
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2;
只有分组后人数大于2的`男`这组数据显示了出来。
3、where和having的对比
1.having是在分组后对数据进行过滤，where是在分组前对数据进行过滤
2.having语句中可以使用聚合函数，where后面不可以使用聚合函数
· limit
LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数。
准备数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
语法
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];
注意：limit子句要放在SQL语句的最后
LIMIT语法格式
LIMIT offset,length;或者limit length;
offset是指偏移量，可以认为是跳过的记录数量，默认为0，表示从哪开始
length是指需要显示的总记录数，表示显示几条
示例
查询学生表中数据，从第三条开始显示，显示6条。
SELECT * FROM student3 LIMIT 2,6;
LIMIT的使用场景：分页。比如我们登录京东、淘宝，返回的商品信息可能有几万条，不是一次全部显示出来，是一页显示固定的条数。 假设我们以每页显示5条记录的方式来分页。
SQL语句如下
-- 每页显示5条
-- 第一页： LIMIT 0,5; 跳过0条，显示5条
-- 第二页： LIMIT 5,5; 跳过5条，显示5条
-- 第三页： LIMIT 10,5; 跳过10条，显示5条
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5,5;
SELECT * FROM student3 LIMIT 10,5;
注意
1.如果第一个参数是0可以简写：
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5;
2.LIMIT 10,5; -- 如果不够5条，则有多少显示多少。
· select语句总结
1、书写顺序
SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段 HAVING 条件 ORDER BY 字段 LIMIT offset,length;
其中，SELECT字段，FROM表名为 必写，其余均可根据情况而定。
2、执行顺序
1、from表名
2、where条件
3、group by 字段
4、having 条件
5、select 字段
6、order by 字段
7、limit