Mysql学习总结第二篇

基础查询

语法：select 查询列表 from 表名；
查询列表可以是：表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格

1. 查询表中的单个字段

例：select last_name from employees；

2. 查询表中的多个字段

例：select last_name，salary from employees；

3. 查询表中的所有字段

例： select * from 表名；

4. 查询常量值

例：select 100；/ select ‘john’;

5. 查询表达式

例：select 100*98;

6. 查询函数

例：select version();

7. 起别名

好处：便于理解、如果要查询的字段有重名情况，使用别名区分
用AS：select 100*98 as 结果；
省略AS：select last_name 姓；

别名中有关键字，加引号，例：select salary as ‘out put’ from employees；
8. 去重
例：查询员工表中所有部门编号select DISTINCT department_id from employees;
9. +号的作用
仅有一个功能，运算符

如要拼接，使用concat()。
select CONCAT( last_name,first_name) as 姓名 from employees;//正确方式
如concat中有某列存在null值，结果为null。
可使用IFNULL（列名，为null时默认值），不为null时返回原本值。
ISNULL 判断结果，为true返回1，false返回0。

条件查询

语法： select 查询列表 from 表名 where 筛选条件
分类：
1.按条件表达式筛选
条件运算符：大于> 小于< 等于= 不等于<> !=大于等于>= 小于等于<=
2.按逻辑运算符筛选
逻辑运算符：
与：&& and 如果两个条件都为true则为true，否则为false
或：|| or 如果有一个条件为true则为true，否则为false
非：！not 取反
3.模糊查询
like
一般和通配符搭配使用 %：0或多个字符 _任意单个字符
可判断字符型或数值型
例：查询员工名包含a的信息

select * from enployees where last_name **like** '%a';

例：查询员工名第三个字符为a第五个为b的信息

 select * from enployees where last_name **like** '__a_b%';   

例：查询员工名第二个字符为_的信息

 select * from enployees where last_name like  '_\_%';
  select * from enployees where last_name like '\_$_%' escape '\$' ;

between and可提高语句的简洁度，包含边界值，不能颠倒。

例：查询工资在8000到9000之间的员工信息

 select * from employees where salary BETWEEN 8000 AND 9000；

in用于判断某字段的值是否属于in列表中的某一项
提高语句简洁度，in列表的值类型必须一致或兼容，不能包含通配符
例：查询员工编号1,2,3中的一个的员工信息

 select * from employees where employee_id in (1,2,3);

is null
例：查询没有奖金的员工名

 select last_name from employees where commission_pct IS NULL;

为空用IS NULL 不为空用IS NOT NULL不能用=或！=< =>
安全等于，除普通数值，还可用于判断null值。可读性差。

排序查询

语法：

select 查询列表 from 表 where 筛选条件 order by 排序列表 [asc/desc]

默认为asc升序。
一般放在查询语句的最后面，limit语句除外。
别名排序：

 SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪;

函数排序：

 SELECT LENGTH(last_name) 姓名长度,last_name FROM employees ORDER BY 姓名长度;

多字段排序：

 SELECT * FROM employees ORDER BY salary,employee_id DESC;

常见函数

好处：提高重用性、隐藏实现细节
调用：select 函数名(实参列表) from 表名
分类：
单行函数，如concat、length、ifnull等
1.字符函数
①length(str) 获取str的字节个数 一个汉字占3字节(utf8)
②concat(str1,str2…strn) 拼接字符串
③upper(str)、lower（str） 转换大小写
④substr、substring返回子字符串，SQL中索引从1开始
substr(str,n) 截取从n开始的所有字符
substr(str,n,m) 截取从n开始的m个字符
⑤instr(str,substr) ，返回substr在str中第一次出现的索引，没有返回0。
⑥trim(str)，去掉字符串中空格。
trim(x from str),去掉str前后的x字符。
⑦lpad(str,n,c) 左填充，用指定字符c填充str左侧至n长度。
rpad(str,n,c) 右填充，用指定字符c填充str右侧至n长度。
⑧replace(str,a,b) 替换
2.数学函数
①round(x) 四舍五入
②ceil(x) 向上取整，返回大于等于参数的最小整数
③floor(x) 向下取整，返回小于等于参数的最大整数
④truncate(n,m) 截断n，保留m位
⑤mod(a,b) 取余
⑥rand() 获取0-1之间的随机数
3.日期函数
①now() 返回当前系统日期+时间
②curdate() 返回当前日期
③curtime() 返回当前时间
④year(),month(),day(),hour(),minute(),second() 获取年月日时分秒
⑤str_to_date() 将日期格式的字符转换成指定格式的字符串
⑥date_format() 将日期转换成字符
⑦datediff()返回两日期相差的天数
4.其他函数
①version() 版本号
②databases() 查看所有数据库
③user() 当前用户
④password(str) 返回加密形式（MySQL8已弃用）
⑤md5(str) 返回字符串的md5模式
5.流程控制函数
①if (exp1,exp2,exp3) 若exp1为true，返回exp2，否则返回exp3
②case

用法一：
case 要判断的字段或表达式
when 常量1 then 要显示的值 或 语句1；
…
when 常量n then 要显示的值 或 语句n；
else 要显示的值或语句m
end

用法二：
case
when 条件1 then 要显示的值或语句1；
…
when 条件n then 要显示的值 或 语句n；
else 要显示的值或语句m
end

分组函数：做统计使用，又称统计函数或聚合函数
①sum() 求和
②avg() 求平均值
③min() 求最小值
④max() 求最大值
⑤count() 求非空个数
count(*)/count(常量值) 统计总行数

sum和avg对数值型处理，min和max可对字符型和日期型排序
所有分组函数都忽略null值，可和distinct搭配使用
和分组函数一同查询的字段要求是group by后的字段

分组查询

语法：

select 分组函数，列
from 表名 【where 筛选条件】
group by 分组列表 【order by 字句】

注意：查询列表必须使分组函数和group by后出现的字段
特点：

分组前筛选 数据源为原始表 用**where**
分组后筛选 数据源为分组后结果集 用**having**
分组函数做条件肯定放在**having**子句中
能用分组前筛选的优先考虑分组前筛选

group by支持单个字段，多个字段(用，隔开)，表达式或函数分组，也可以添加排序（放在最后）。

连接查询

又称多表查询，当查询的字段来自多个表时，会用到连接查询
笛卡尔乘积：查询多个表时没有添加有效的连接条件，导致多个表出现完全连接。如表1有a行，表2有b行，将产生a*b行结果。
避免：添加连接条件

**内连接：**inner 可以省略
1.等值连接
2.非等值连接
3.自然连接
例：查询员工及对应上级名

SELECT e1.`last_name`,e2.`last_name` FROM  employees e1
INNER JOIN employees e2 ON e1.`manager_id`=e2.`employee_id`

外连接：
用于查询一个表中有，另一个表中没有的记录
特点：
外连接查询的结果为主表中的所有记录，如果从表有和他匹配的则显示匹配的值，若没有则显示null。外连接查询结果=内连接结果+主表有而从表没有的记录。

左外连接中left左边的是主表，右外连接right右边的是主表。
左外和右外交换顺序，可实现同样的效果
左外连接：left 【outer】
例：查询哪个部门没有员工

SELECT d.*,e.`id` FROM departments d 
LEFT JOIN employees e ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL

右外连接：right【outer】

交叉连接：cross【outer】
两个表进行笛卡尔乘积

全外连接：full【outer】
等于内连接的结果+表1中有表2中没有的+表2中有但表1中没有的

子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
1.select后面 仅支持标量子查询

案例：查询每个部门的员工个数
SELECT d.*,(SELECT COUNT(*) FROM employees WHERE employees.`department_id`=d.`department_id`)
FROM departments d

2.from后面 支持表子查询

案例：查询每个部门的平均工资的工资等级

SELECT t1.*,t2.`grade_level`
FROM(
	SELECT department_id,AVG(salary) avg_salary FROM employees
	GROUP BY department_id
) t1 INNER JOIN job_grades t2
ON t1.avg_salary BETWEEN t2.`lowest_sal` AND t2.`highest_sal`
将子查询结果充当一张表，要求必须起别名

3.where或having后面 ⭐
特点：
子查询都放在小括号内、子查询放在条件右侧、标量子查询搭配单行操作符（>, <, >=, <=, =, <>）、列子查询搭配多行操作符（in/not in,any/some,all）、子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
①支持标量子查询（单行）

案例：谁的工资比abel高?
SELECT e.`last_name` FROM employees e WHERE e.`salary`>(SELECT salary FROM employees WHERE last_name = 'Abel')；

②列子查询（多行）

案例：查询其他部门比it_prog部门任意工资低的员工名，工作类别和工资

SELECT last_name,job_id,salary FROM employees
WHERE job_id <> 'IT_PROG' AND salary<(
SELECT MAX(salary) FROM employees 
WHERE job_id='IT_PROG'
)

③行子查询（少）

案例：查询员工编号最小并且工资最高的员工信息

SELECT * FROM employees
WHERE (employee_id,salary) = (SELECT MIN(employee_id),MAX(salary) FROM employees)

4.exists后面（相关子查询） 支持表子查询
结果为1或0，1表示存在结果，0表示不存在。

案例：查询没有女朋友的男人
SELECT * FROM boys bo
WHERE NOT EXISTS(SELECT * FROM beauty b WHERE b.boyfriend_id=bo.id)

按功能不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集，一般多行多列）

分页查询

**应用场景：**当要显示的数据一页显示不全，需要分页提交sql请求
**特点：**limit语句放在查询语句的最后
**公式：**要显示的页数page，每页的条目数size

select 查询列表 from 表 limit （page-1)*size,size

语法：

select 查询列表 from 表 .... limit offset，size

**offset：**要显示条目的索引 从0开始
**size：**要显示的条目个数

案例：查询前5条员工信息
SELECT * FROM employees LIMIT 0,5

案例：查询有奖金的员工信息，并显示工资较高的前10名
SELECT * FROM employees 
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC  LIMIT 0,10

查询涉及的关键字 ----------执行顺序

from 表 -----------------------------①
连接类型 join 表2-----------------②
on 连接条件-------------------------③
where 筛选条件--------------------④
group by 分组列表----------------⑤
having 分组后筛选----------------⑥
select 查询列表 ------------------⑦
order by 排序列表-----------------⑧
limit 偏移，条目数 ----------------⑨

联合查询

将多条查询语句的结果合并成一个结果
语法：
查询语句1 union 查询语句2 …
应用场景：当要查询的结果来自多表且多表间无直接连接关系
特点：
①要求多条查询语句的查询列数一致
②要求多条查询语句查询的每一列的类型和顺序最好一致
③默认去重，使用union all可以包含重复项
④将一条比较复杂的查询语句拆分成多条

索引

原理： 当对表字段创建一个索引，数据库就会创建一个索引页，索引页不仅存储的有索引的数据，还保存了索引数据在数据库的物理位置;

作用： 加快查询速度，类似一本书的目录，索引可以避免对表全面扫描;

1.如果列很少，不建议建索引;

2.主键和唯一键会自动创建索引;

3.适合在常用的字段创建索引;

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

缺点：

创建索引和维护索引要耗费时间

索引需要占物理空间

创建索引

CREATE INDEX index_tb_emp ON tb_emp(ename);

使用索引

ename='Corbie’就使用了索引

SELECT *FROM tb_emp WHERE ename='Corbie';
SELECT *FROM tb_emp;

多次insert，update,DELETE之后会有索引碎片，垃圾文件会影响查询速度

删除索引2种方法

alter

ALTER TABLE tb_emp DROP INDEX index_tb_emp;

drop

DROP INDEX index_tb_emp ON tb_emp;

如果有一个CHAR(255)的列，如果在前10个或30个字符内，多数值是唯一的，则不需要对整个列进行索引。

短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作。

DESC tb_emp;

创建短索引

CREATE INDEX index_tb_emp ON tb_emp(ename(3));