目录
▐ 前言:
• DQL全称 Data Query Language 数据查询语言
• 可以从一个表中查询数据,也可以从多个表中查询数据。
• 基本语法:select 查询的列 from 表名 where 条件 排序 数量限制 分组......
📖 首先让我们先创建一个表,本篇文章的查询都以此表为例
-- 创建一个学生表 CREATE TABLE student( num INT PRIMARY KEY AUTO_INCREMENT,-- 学号 -主键 NAME VARCHAR(10) NOT NULL, -- 名字 gender CHAR(1) NOT NULL, -- 性别 score INT NOT NULL, -- 分数 birthday DATE, -- 出生日期 height DECIMAL(3,2) -- 身高 ) -- 插入数据 INSERT INTO student(NAME,gender,score,birthday,height) VALUE ('一鸣','男',84,'2005-4-5','1.75'), ('张三','男',76,'2005-7-4','1.81'), ('小李','女',68,'2005-1-12','1.71'), ('小张','女',54,'2004-8-23','1.64'), ('李四','男',90,'2004-9-6','1.68'), ('张三','男',80,'2006-12-9','1.77')
就此我们的表就创建完成了,接下来会讲解对表的各种查询操作,上船~启航!
▐ 特定列查询
• select 查询列表 from 表名
-- 查找name,score列
SELECT NAME,score FROM student
-- 查找所有列 *
SELECT * FROM student
▐ 逻辑处理
• case when 条件 then 结果1 else 结果2 end 可以有多个when
-- case when 条件 then 结果1 else 结果2 end 可以有多个when
SELECT NAME ,(CASE WHEN score>=90 THEN '优秀'
WHEN score>80 AND score<90 THEN'良好'
ELSE '一般' END)AS '等级'FROM student
-- 使用as为列重命名
▐ 字符函数操作
• 单行字符函数--会对查询的每条记录进行操作
-- length():获取参数值的字节个数
SELECT NAME,LENGTH(NAME) FROM student
-- char_length()获取参数值的字符个数
SELECT NAME,CHAR_LENGTH(NAME)AS NAME,gender FROM student
-- concat(str1,str2,.....):拼接字符串 as 别名
SELECT num,CONCAT(NAME,':',gender)AS NAME FROM student
-- upper()/lower():将字符串变成大写/小写
SELECT UPPER(NAME),LOWER(NAME) FROM student
-- substring(str,pos,length):截取字符串 位置从1开始
SELECT SUBSTRING(NAME,2,3) FROM student
-- instr(str,指定字符):返回子串第一次出现的索引(indexof()),如果找不到返回0
SELECT INSTR(NAME,'三') FROM student
-- trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT CHAR_LENGTH(TRIM(NAME)) FROM student
SELECT TRIM('张' FROM NAME) FROM student
-- lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(NAME,5,'a') , RPAD(NAME,5,'b')FROM student
-- rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
-- replace(str,old,new):替换,替换所有的子串
SELECT REPLACE(NAME,'一','I') FROM student
▐ 分组函数统计
• sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
-- sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
-- sum(),avg()只能用于数值类型
-- max(),min()可以适用于所有类型
-- count统计行数
-- count(*) count(1) count(列名) 列的值为空不统计
SELECT SUM(height),AVG(height),MAX(height),MIN(height),COUNT(*) FROM student
-- 查询身高最高的
SELECT * FROM student WHERE height =(SELECT MAX(height)FROM student)
▐ 数学函数
• round( , 保留位数 )四舍五入
• ceil向上取整
• floor向下取整
• truncate 截断,不会进行四舍五入
• mod(被除数,除数)
• rand()获取随机数 0~1之间的小数
-- round(数值):四舍五入
SELECT NAME,ROUND(height) FROM student
-- 指定保留小数位数
SELECT NAME,ROUND(height,1) FROM student
-- ceil(数值):向上取整,返回>=该参数的最小整数
SELECT NAME,CEIL(height),FLOOR(height) FROM student
-- floor(数值):向下取整,返回<=该参数的最大整数
-- truncate(数值,保留小数的位数):截断,小数点后截断到几位, 不会四舍五入
SELECT NAME,TRUNCATE(height,1) FROM student
-- mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
SELECT NAME, MOD(num,3) FROM student
-- rand():获取随机数,返回0-1之间的小数
SELECT NAME, RAND() FROM student
▐ 日期函数
• str_to_date(字符串格式日期,格式):将日期格式的字符转换成指定格式的日期
• date_format(日期列,格式):将日期转换成字符串
• datediff(big,small):返回两个日期相差的天数
-- 日期格式化
SELECT NAME, YEAR(birthday),MONTH(birthday),DAY(birthday) FROM student
-- 将字符串格式化为日期类型
SELECT STR_TO_DATE('2001-2-2','%Y-%m-%d') FROM student
-- 将日期格式化为指定的字符串
SELECT DATE_FORMAT(birthday,'%Y-%m') FROM student
-- 计算两个日期之间相差的天数
SELECT DATEDIFF(CURDATE(),birthday) FROM student
日期格式:
▐ 条件查询
• 使用WHERE 子句,将不满足条件的行过滤掉,WHERE 子句紧随 FROM 子句
• 语法:select <结果列> from <表名> where <条件>
• 比较符号: = , != 或<> , > , < , >= , <=
-- select 结果列 from 表名 where 条件
SELECT * FROM student WHERE num =2
• 逻辑运算: and (与) or (或) not (非)
-- and 所有条件都满足 or满足其一即可
SELECT * FROM student WHERE gender ='男'AND height>=1.75
• between ... and ... 两者之间 ( 包含临界值 )
-- 包含临界值
SELECT * FROM student WHERE height BETWEEN 1.70 AND 1.85
• in 判断某字段的值是否属于in列表中的某一项
-- in (等价or) 否定在in前加not
SELECT * FROM student WHERE height IN(1.71,1.81)
• IS NULL(为空的)或 IS NOT NULL(不为空的)
-- is (not) null
SELECT * FROM student WHERE address IS NULL
• 模糊查询:like (效率低很少用)
-- 模糊查询(效率低很少用) %通配符
-- like
SELECT * FROM student WHERE NAME LIKE '张%'
▐ 排序
• 查询结果排序使用 ORDER BY order by 排序列 ASC/DESC
• ASC 升序 (默认) DESC 降序
• 可以给多个排序条件 (什么排序都没有,默认按主键升序排序)
-- ASC 升序(默认) DESC降序
SELECT * FROM student ORDER BY height DESC
SELECT * FROM student ORDER BY height ASC
-- 可以给多个排序条件
▐ 数量限制
• 对查询的显示结果限制数目。limit 开始位置 , 查询的数量 (sql语句最末尾位置)
• 实际应用场景:数据分页显示
-- limit 开始位置,查询的数量
SELECT * FROM student LIMIT 0,2
SELECT * FROM student LIMIT 2,2
📖 若设n为分页的页数,m为查询的数量,则总数为 (n-1)*2
▐ 合并
• union 合并多个查询结果,可以去除重复数据.
• union all 只合并不去重 (效率更高)
📖 [SQL 语句 1] [SQL 语句 1]
UNION UNION ALL
[SQL 语句 2] [SQL 语句 2]
注意:合并时要求结果列相同.
▐ 分组查询
• 语法:select 列 from 表 where 筛选条件 group by 分组的列表 having 分组后的筛选
-- 只筛选出同名的人
SELECT COUNT(*),NAME FROM student GROUP BY NAME HAVING COUNT(*)>1