04.DQL：查询表中的记录 - 《MySQL》

DQL：查询表中的记录
select from 表名;

1. 语法：
select
字段列表|表达式|常量值|函数
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定

2. 基础查询
1. 多个字段的查询
select 字段名1，字段名2… from 表名；
注意：
** 如果查询所有字段，则可以使用来替代字段列表。
2. 去除重复
distinct
SELECT DISTINCT address FROM student3;
3. 计算列
一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
表达式1：哪个字段需要判断是否为null
表达式2：如果该字段为null后的替换值。
null值不能使用 = （!=）判断
4. 起别名：
as*：as也可以省略

**3. 条件查询**<br />        **1. where子句后跟条件**<br />    **    2. 运算符**<br />            **> **、**<** 、**<=** **、>=** **、**= 、**<> ** (<> :是不等于 跟 != 效果一样)<br />            ** BETWEEN...AND**  （在...之间）<br />                -- 查询年龄大于等于20 小于等于30     （3种格式）<br />SELECT *FROM student3 WHERE age>=20 && age<=30;<br />SELECT *FROM student3 WHERE age>=20 AND age<=30;<br />SELECT *FROM student3 WHERE age BETWEEN 20 AND 30;    （包括20 30）        <br />        <br />             **IN( 集合)** <br />**LIKE**：模糊查询<br />**占位符：**<br />**_  **:单个任意字符<br />**%** :多个任意字符<br />IS NULL  <br />and（&&）:两个条件如果同时成立，结果为true，否则为false<br />or(||)：两个条件只要有一个成立，结果为true，否则为false<br />not(!)：如果条件成立，则not后为false，否则为true<br />                -- 查询年龄大于20岁
            SELECT * FROM student WHERE age > 20;<br />                <br />                SELECT * FROM student WHERE age >= 20;<br />                <br />                -- 查询年龄等于20岁<br />                SELECT * FROM student WHERE age = 20;<br />                <br />                -- 查询年龄不等于20岁<br />                SELECT * FROM student WHERE age != 20;<br />                SELECT * FROM student WHERE age <> 20;<br />                <br />                -- 查询年龄大于等于20 小于等于30<br />                <br />                SELECT * FROM student WHERE age >= 20 &&  age <=30;<br />                SELECT * FROM student WHERE age >= 20 AND  age <=30;<br />                SELECT * FROM student WHERE age BETWEEN 20 AND 30;<br />                <br />                -- 查询年龄22岁，18岁，25岁的信息<br />                SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25<br />                SELECT * FROM student WHERE age IN (22,18,25);<br />                <br />                -- 查询英语成绩为null<br />                SELECT * FROM student WHERE english = NULL; -- 不对的。null值不能使用 = （!=） 判断<br />                <br />                SELECT * FROM student WHERE english IS NULL;<br />                <br />                -- 查询英语成绩不为null<br />                SELECT * FROM student WHERE english  IS NOT NULL;<br />    
            -- 查询姓马的有哪些？ like<br />                SELECT * FROM student WHERE NAME LIKE '马%';<br />                -- 查询姓名第二个字是化的人<br />                <br />                SELECT * FROM student WHERE NAME LIKE "_化%";<br />                <br />                -- 查询姓名是3个字的人<br />                SELECT * FROM student WHERE NAME LIKE '___';<br />                <br />                <br />                -- 查询姓名中包含德的人<br />                SELECT * FROM student WHERE NAME LIKE '%德%';
**4.DQL复杂查询**<br />    **1. 排序查询**<br />         语法：**order by** 子句<br />               *order by 排序字段1 排序方式1 ，  排序字段2 排序方式2...<br />         排序方式：<br />            * ASC：升序，默认的。<br />            * DESC：降序。<br />        ** 注意**：<br />            * 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
**2. 聚合函数**：将一列数据作为一个整体，进行纵向的计算。<br />         1. count：计算个数<br />            1. 一般选择非空的列：主键<br />            2. count(*)：计算有多少条结果<br />2.max：计算最大值<br />3.min：计算最小值<br />4.sum：计算和<br />5.avg：计算平均值<br />          SELECT COUNT(NAME) FROM student3; -- 计算student3表中 name 的个数<br />              SELECT MAX(math) FROM student3;-- 计算student3表中 math成绩的最大值<br />              SELECT MIN(math) FROM student3;<br />              SELECT SUM(math) FROM student3;<br />              SELECT AVG(math) FROM student3;
    *** 注意**：聚合函数的计算，排除null值。<br />            解决方案：<br />                1. 选择不包含非空的列进行计算<br />                2. IF NULL函数<br />                select MAX(IFNULL(english,0)) FROM student3;
**3. 分组查询**:<br />        **1. 语法**：group by 分组字段；<br />        **2. 注意**：<br />            1. 分组之后查询的字段：分组字段、聚合函数<br />            2. where 和 having 的区别？<br /> where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来<br />    where 后不可以跟聚合函数，having可以进行聚合函数的判断。
        -- 按照性别分组。分别查询男、女同学的平均分
        SELECT sex , AVG(math) FROM student GROUP BY sex;<br />            <br />            -- 按照性别分组。分别查询男、女同学的平均分,人数<br />            <br />            SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;<br />            <br />            --  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组<br />            SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;<br />            <br />            --  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人<br />            SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;<br />            <br />            SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
**4. 分页查询：**<br />        **1. 语法**：limit 开始的索引,每页查询的条数;<br />        **2. 公式**：开始的索引 = （当前的页码 - 1） * 每页显示的条数<br />            -- 每页显示3条记录 
        SELECT * FROM student LIMIT 0,3; -- 第1页<br />            <br />            SELECT * FROM student LIMIT 3,3; -- 第2页<br />            <br />            SELECT * FROM student LIMIT 6,3; -- 第3页
    3. limit 是一个MySQL"方言"<br />

5. UNION：
1.用法：UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句的结果集必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
2.语法：**默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
#默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
3. UNION ALL 语法： UNION ALL允许显示重复值
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
4.mysql中 + 号的作用：仅仅只有一个功能，做运算符**
两个操作数都为数值型，则做加法运算
select 100+99;—199
只要操作数中有一个是字符型，那么mysql会尝试把它转换为数值型，如果转换成功则继续做加法运算，如果转换失败，那么将字符型数值转换为0
select ‘123’+100;— 223
select ‘nike’+100; —100

6.MySQL中的常用函数：
好处：1.隐藏了实现细节 2.提高代码重用性
SELECT 函数名(实参列表) 【from 表名】;

1.单行函数1、字符函数
concat 拼接
CONCAT(‘a’,’b’,’c’,’…’);
SELECT CONCAT(e.last_name,’ ‘,e.first_name) AS 姓名 FROM employees e;
substr 截取子串 —有4个重载方法
SELECT SUBSTR(‘我是一个JAVA工程师’,5); —JAVA工程师,mysql中所有索引都是从1开始
upper 转换成大写
SELECT UPPER(‘abc’); —ABC
lower 转换成小写
SELECT LOWER(‘ABC’); —abc
trim 去前后指定的空格和字符
SELECT TRIM(‘ 我的两边有空格 ‘); —我的两边有空格
ltrim 去左边空格
rtrim 去右边空格
replace 替换
SELECT REPLACE(‘哈哈替换我替换我哈哈’,’哈哈’,’嘿嘿’); —嘿嘿替换我替换我嘿嘿
lpad 左填充
SELECT LPAD(‘ABC’,10,’‘); —**ABC 用指定字符填充到指定长度
rpad 右填充
SELECT RPAD(‘ABC’,10,’‘);—ABC** 用指定字符填充到指定长度
instr 返回子串第一次出现的索引
SELECT INSTR(‘ABCDEFG’,’F’);—6
length 获取字节个数
SELECT LENGTH(‘我的长度’);—12
2、数学函数
round 四舍五入
SELECT ROUND(-1.55); — -2
SELECT ROUND(-1.567,2); — -1.57
rand 随机数
SELECT RAND();
SELECT RAND(5); —传入一个种子数，每次随机结果相等
floor 向下取整
返回小于等于该参数的最小整数
SELECT FLOOR(-1.567); — -2
SELECT FLOOR(1.567); — 1
ceil 向上取整
返回大于等于该参数的最小整数
SELECT CEIL(5.3); —6
SELECT CEIL(1.00); —1
mod 取余
SELECT MOD(10,3); —1 跟java中%一样 10%3 = 1 % = a-a/bb
truncate 截断
SELECT TRUNCATE(1.633,1); —1.6 保留1为小数
*3、日期函数
now 当前系统日期+时间
SELECT NOW(); — 2020-07-19 18:34:50
curdate 当前系统日期
SELECT CURDATE(); — 2020-07-19
curtime 当前系统时间
SELECT CURTIME(); — 18:34:50
str_to_date 将字符串转换成日期
04.DQL：查询表中的记录 - 图1

SELECT STR_TO_DATE(‘2020-7-19’,’%Y-%m-%d’);
date_format 将日期转换成字符串
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i:%s’); —2020-07-19 18:50:50
获取指定的年、月、日、小时、分钟、秒
SELECT YEAR(NOW()); —2020 年
SELECT MONTH(NOW()); —7 月
SELECT DAY(NOW()); —19 日
SELECT HOUR(NOW()); —18 小时
SELECT MINUTE(NOW()); —40 分
SELECT SECOND(NOW()); —25 秒
可以传入字符串：
SELECT YEAR (‘1998-4-5’); —1998
获取月份的单词：
SELECT MONTHNAME(NOW()); — July
4、流程控制函数
if 处理双分支
SELECT IF(10>6,’大’,’小’);— 大
case语句处理多分支
情况1：处理等值判断
情况2：处理条件判断
CASE 要判断字段或表达式
WHEN 常量1 then 要显示的值1或语句1; （如果是值则不写;号）
WHEN 常量2 then 要显示的值2或语句2;
WHEN 常量3 then 要显示的值3或语句3;
…
ELSE 要显示的值n或语句n;
END
case语句第二种用法：与第一种相比 CASE后不跟任何表达式或语句（当做Java中的if…else）
CASE
WHEN 常量1 then 要显示的值1或语句1; （如果是值则不写;号）
WHEN 常量2 then 要显示的值2或语句2;
WHEN 常量3 then 要显示的值3或语句3;
…
ELSE 要显示的值n或语句n;
END

5、其他函数
version版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql —version
或
mysql —V
database当前库
SELECT DATABASE();
user当前连接用户
SELECT USER();

p54