MySQL 入门到精通

一、数据库

1、概述

数据库database用来存储数据和管理数据的仓库
分类：关系型和非关系型

二、SQL语言

1、概述

是结构化查询语言，专门用来操作数据库的语言，是一种标准化语言，可以操作各种数据库产品

分类

1、DML：数据操纵语言
2、DDL：数据定义语言
3、DCL：数据控制语言
4、DQL：数据查询语言
常用操作：
对数据库的操作、对表的操作、对数据记录的操作

2、对数据库的操作

show databases；#查看所有库
create database 库名 default character set utf8 ; #创建库
drop database 库名 ; #删库

use 库名; #使用指定的数据库
create table 表名(字段名 字段类型(字段长度),字段名 字段类型(字段长度)); #新建表
show tables ; #查看表
drop table 表名; #删表
alter table 表名 add column 字段名 字段类型(字段长度) ; #修改表
desc 表名; #描述表的结构

select * from 表名; #查数据
insert into 表名 values(字段1的值,字段2的值,字段3的值,字段4的值) ;#添加数据
update 表名 set 字段名=新值 #改数据
delete from 表名 ; #删数据

三、可视化工具

1、概述

用sqlyog,连接服务器,操作服务器里的数据.库, 表, 记录.
1, 新连接: 文件-新连接-输入连接名和密码-测试连接-连接-ok
2, 创建库: 右键-新建数据库-输入库名选好utf8-ok
3, 创建表: 右键-创建表-输入表名和选utf8-设计字段名字段类型和长度-保存
4, 表里的记录: 右键-打开表-新增/删除/保存/刷新

四、字段约束&函数

1,字段约束

当表里的字段值想要添加约束条件时,就可以使用各种字段约束
常见的有: 主键约束 非空约束 唯一约束
通常,在创建表时已经确定了,哪些字段要添加哪种约束

```sql
#字段约束
#1.主键约束:给字段添加主键约束时,字段的值必须唯一且非空
  #自增策略:AUTO_INCREMENT把主键的值交给数据库维护,值是自增
CREATE TABLE a(id INT)
CREATE TABLE b(id INT PRIMARY KEY)#主键(唯一+非空)
CREATE TABLE c(id INT PRIMARY KEY AUTO_INCREMENT)#主键且自增
#2.非空约束:not null,给字段加了非空约束,字段值不能为空
CREATE TABLE d(NAME VARCHAR(10))
CREATE TABLE e(NAME VARCHAR(10) NOT NULL)
INSERT INTO e VALUES()#报错,必须有值
INSERT INTO e VALUES(NULL)#报错,必须有值
#3.唯一约束:unique,给字段添加唯一约束,字段的值不能相同
CREATE TABLE f(tel VARCHAR(11))
CREATE TABLE g(tel VARCHAR(11) UNIQUE )
#4.默认约束default: 给字段设置默认值
CREATE TABLE h(
 id INT PRIMARY KEY AUTO_INCREMENT,#主键自增
 sex VARCHAR(10) DEFAULT '男' #默认约束
)
#5.检查约束CHECK:检查字段的值是否合法
CREATE TABLE i(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age>0 AND age<18)#检查约束,不合法时会报错
)

2、函数

为了方便的操作数据库里的数据 , MySQL提供了很多函数.
常见的有: lower upper length substr concat replace 日期函数 小数的函数

SELECT * FROM emp#查所有列,低效
SELECT ename FROM emp#查指定的列(把*换成字段名),高效
SELECT ename,empno FROM emp#查询结果和查询顺序一样的
#基础函数:
#lower:全转小写 upper:全转大写 
SELECT ename,empno,LOWER(ename),UPPER(ename) FROM emp
#length:获取长度,一个字母或数字长度为1,一个汉字长度是3
SELECT * FROM dept
SELECT dname,LENGTH(dname),loc,LENGTH(loc) FROM dept#查部门名称
#concat:拼接字符串,第一个参数是字段名,后面的参数都是要拼接的数据
SELECT dname,CONCAT(dname,'hello') FROM dept
SELECT dname,CONCAT(dname,'hello',123,789) FROM dept
#substr(1,2):截取字符串,1是字段名,2是从哪个字符开始
SELECT dname,SUBSTR(dname,3) FROM dept
#substr(1,2,3):1是字段名,2是从哪个字符开始,3是截取长度
SELECT dname,SUBSTR(dname,5,3) FROM dept
#replace(1,2,3):1是字段名,是要把2换成3
SELECT dname,REPLACE(dname,'a','666') FROM dept
#ifnull(1,2):1是字段名,判断1如果是null就换成2
SELECT comm,IFNULL(comm,200) FROM emp
SELECT * FROM emp
#计算月薪
SELECT sal,comm,sal+IFNULL(comm,0) FROM emp
SELECT sal,comm,sal+IFNULL(comm,0),sal*12+IFNULL(comm,0)*12 FROM emp
#round四舍五入, ceil向上取整 , floor向下取整
SELECT comm,ROUND(comm),CEIL(comm),FLOOR(comm) FROM emp
#日期函数 now当前时间 
#year年 month月 day日 hour时 minute分 second秒
SELECT NOW()#2021-08-26 15:39:18
SELECT YEAR( NOW() )
SELECT YEAR('2020-08-26'),MONTH(NOW()),DAY(NOW())
SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())
#转义符号\
SELECT 'xi\'an' 
SELECT "xi'an" 

3、条件查询

#条件查询
#1. distinct 去重
SELECT DISTINCT loc  FROM dept
SELECT * FROM dept
#2. where条件
#查询deptno=1的部门信息
SELECT * FROM dept WHERE deptno=1
#查询deptno=2的部门名称
SELECT dname FROM dept WHERE deptno=2
#查询地址在二区的部门名称
SELECT dname FROM dept WHERE loc='二区'
#多个条件可以用and 或者 or连接
#查询地址在二区，编号是2的部门信息
SELECT * FROM dept WHERE loc='二区' OR deptno=2
#查询编号是2的或者编号是3的 部门信息
SELECT * FROM dept WHERE deptno=2 OR deptno=3
SELECT * FROM dept WHERE deptno IN(2,3)#同上等效
#3. like模糊查询
#%通配符,通配了0~n个字符,_通配符,通配了1个字符
#查名称里包含o的部门信息
SELECT * FROM dept WHERE dname LIKE '%o%'#低效
SELECT * FROM dept WHERE dname LIKE 'o%'#以o开始,高效
SELECT * FROM dept WHERE dname LIKE '%o'#以o结束
SELECT * FROM emp WHERE ename LIKE 'l__'#了解
#4. null操作空数据
SELECT * FROM emp WHERE comm IS NULL#查是null的
SELECT * FROM emp WHERE comm IS NOT NULL#查不是null的
#5. between and 区间范围,是包含的关系
SELECT * FROM emp WHERE sal>3000 AND sal<10000
SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000

#limit:分页
SELECT * FROM emp LIMIT 2 #展示前两条数据
SELECT * FROM emp LIMIT 0,2 #展示前两条数据
SELECT * FROM emp LIMIT 1,4 #从第n+1行开始展示,要展示的行数
SELECT * FROM emp LIMIT 3,2
#order by: 排序,默认是升序ASC(可以省略)
SELECT * FROM emp ORDER BY sal ASC#按照empno排序
SELECT * FROM emp ORDER BY sal DESC#按照empno降序
SELECT * FROM emp ORDER BY ename#按照ename排序,字典顺序
SELECT * FROM emp ORDER BY hiredate#按照数值排序
SELECT * FROM emp ORDER BY job#按照汉字对应的数字排
#统计案例:
#查询2017年以前入职的员工信息
SELECT * FROM emp WHERE hiredate<'2017-1-1'
SELECT * FROM emp WHERE YEAR(hiredate) < 2017
#可以给列设置别名 ,使用as关键字(也可以省略)
#计算入职年份
SELECT *,YEAR(NOW())-YEAR(hiredate) AS 年份 FROM emp 
#统计年薪
SELECT *,sal*13+IFNULL(comm,0)*13 年薪  FROM emp 

4、聚合函数

指把一列的值聚合在一起,在做分析
聚合函数: max() min() avg() sum() count()

#聚合函数: max min sum avg count
SELECT MAX(sal) FROM emp#查最高薪
SELECT MIN(sal) FROM emp #查最低薪
SELECT AVG(sal) FROM emp #查平均工资
SELECT SUM(sal) FROM emp #工资总和

SELECT COUNT(*) FROM emp #统计个数
SELECT COUNT(1) FROM emp #统计个数,高效
SELECT COUNT(empno) FROM emp #了解
SELECT COUNT(comm) FROM emp #了解,不统计null

#查询时,出现了混合(聚合列和非聚合列)列的现象
SELECT empno,SUM(sal) FROM emp #报错
SELECT SUM(sal) FROM emp 
SELECT empno FROM emp 

5、分组

把查询结果进行分组 , 统计一组数据中的最大值,最小值…使用group by

#分组:GROUP BY
 #口诀:当查询时出现了聚合列和非聚合列时,必须按非聚合列分组
SELECT MAX(sal) FROM emp #查最高薪
#查每个部门的最高薪
SELECT MAX(sal),deptno FROM emp GROUP BY deptno
#查每个岗位的平均工资		
SELECT job,AVG(sal) FROM emp GROUP BY job
#查每年入职的人数
SELECT YEAR(hiredate) YEAR,COUNT(1) FROM emp
			GROUP BY YEAR(hiredate)
#查每个部门的人数
SELECT deptno,COUNT(1) FROM emp
		        GROUP BY deptno	
#having:完成分组后的过滤		        
#查每个部门的人数,只要人数>1的部门
SELECT deptno,COUNT(1) FROM emp
GROUP BY deptno
HAVING COUNT(1) > 1
#查每个岗位的平均工资,只要>8000的
SELECT job,AVG(sal) FROM emp
GROUP BY job
HAVING AVG(sal)>8000#低效,分完组才过滤

SELECT job,AVG(sal) FROM emp
WHERE AVG(sal)>8000#比having高效,因为在分组前就过滤了
#where里不能出现聚合函数
GROUP BY job

#查每年入职的人数,只要人数>1的
SELECT COUNT(1),YEAR(hiredate) FROM emp
GROUP BY YEAR(hiredate) #按照非聚合列分组
HAVING COUNT(1) > 1

五、事务

1、概述

用来 保证多个操作 要么全成功,要么全失败.
四个特性:ACID
1,原子性: 多个操作, 是绑定到一起的,要么全成功,要么全失败
2,一致性: 在多个系统中,保证数据是一致的
3,隔离性: 在保证了性能的同时隔离用户的操作
4,持久性: 对数据的操作都是有持久影响的
隔离级别:
1,读未提交: 效率高,安全性差
2,读已提交: 牺牲了效率,提高了安全性–Oracle数据的默认隔离级别
3,可重复读: 牺牲了效率,提高了安全性–Mysql数据的默认隔离级别
4,串行化: 安全性最高,但是效率太低

2、操作

1, 开启事务: start transaction;
2, 结束事务: commit 提交事务(最终持久性影响数据库)
rollback 回滚事务(回滚到事务操作前)
3, MySQL数据库默认就已经管理了事务 , 会为每条SQL提供事务 .

3、测试

START TRANSACTION ; #开启事务
INSERT INTO dept VALUES(NULL,'test','大钟寺');
INSERT INTO dept VALUES(NULL,'test2','大钟寺2');
COMMIT;#关闭事务

六、外键约束

REATE TABLE tb_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20)
)
#外键约束的效果：
#1,子表的id必须取自主表的id 
#2,想删除主表的数据必须先删掉子表相关的
CREATE TABLE tb_user_addr(
 user_id INT PRIMARY KEY,
 addr VARCHAR(20),
 #外键：通过特殊字段(外键),描述了两张表间的关系 
 #foreign key(当前表的主键) REFERENCES 对方表(对方表的主键)
 FOREIGN KEY(user_id) REFERENCES tb_user(id)
)

七、索引

1、概述

好处是最大的作用就是提高查询效率，坏处是索引本身也是一张表不适合大量的添加
实现过程：1，设置索引 2，使用索引
分类：单值索引 ， 唯一索引 ， 复合索引

2、测试

#0.查看索引
SHOW INDEX FROM dept
#1.创建单值索引(给常用来作为查询条件的字段加)
  #语法：CREATE INDEX 索引名  on 表名（字段名）
CREATE INDEX dname_index ON dept(dname)
#2.使用索引(背后的手段)
EXPLAIN#观察sql的性能/执行计划(找possible_keys的值)
SELECT * FROM dept WHERE dname='java'
#3.创建唯一索引(索引列的值不能重复)
CREATE UNIQUE INDEX uni_index ON dept(dname)
#4. 创建复合索引 并 使用复合索引
  # CREATE INDEX 索引名 ON 表名 (字段1,字段2)
CREATE INDEX fuhe ON emp(ename,job)
SHOW INDEX FROM emp
#5. 使用复合索引(最左特性,否则复合索引失效)
EXPLAIN SELECT * FROM emp WHERE ename='jack' #按ename查，生效
	#按ename和job查，生效
EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='副总'
	#按job和ename查，生效
EXPLAIN SELECT * FROM emp WHERE job='副总' AND 	ename='jack'
	#按job查，失效
EXPLAIN SELECT * FROM emp WHERE job='副总'	

SHOW INDEX FROM emp
#删除索引
#   修改表  表名  删除索引  索引名
ALTER TABLE emp DROP INDEX fuhe

八、多表联查

1、概述

用来完成联合多张表的查询（3张以下），

2、测试

#多表联查：产生了大量的冗余数据
#1.笛卡尔积：把多张表用逗号隔开
SELECT * FROM dept,emp 
	#描述两张表的关系：表名.字段名
	WHERE dept.deptno=emp.deptno 
#2.连接查询,join..on
SELECT * FROM dept JOIN emp
	#描述两张表的关系：表名.字段名
	ON dept.deptno=emp.deptno
#3.子查询：把上次的查询结果，用来作为下次查询的条件
	

#练习语法：teachers / courses
SELECT * FROM teachers , courses WHERE teachers.tno=courses.tno
SELECT * FROM teachers JOIN courses ON teachers.tno=courses.tno
#练习1：查询部门名称叫accounting的员工姓名
#子查询：
#1，查部门表，根据部门名称accounting查deptno
SELECT deptno FROM dept WHERE dname='accounting'
#2，查询员工表，根据deptno查姓名
SELECT ename FROM emp WHERE deptno=1
SELECT ename FROM emp WHERE deptno=(
	SELECT deptno FROM dept WHERE dname='accounting')
	
#笛卡尔积：逗号隔开表名，where里写查询条件，可以多个
SELECT emp.ename FROM dept,emp 
	WHERE dept.deptno=emp.deptno #表关系
	AND dept.dname='accounting' #业务条件
#连接查询：JOIN连接表名，ON写表关系，where里写查询条件，可以多个 
SELECT emp.ename FROM dept JOIN emp 
	ON dept.deptno=emp.deptno #表关系
	WHERE dept.dname='accounting' #业务条件
#练习2：列出research部门下的所有员工的信息
#子查询：
#根据部门名称查部门编号
SELECT deptno FROM dept WHERE dname='research'
#根据部门编号查员工信息
SELECT * FROM emp WHERE deptno=2
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='research')
#笛卡尔积：
SELECT emp.* FROM dept,emp 
	WHERE dept.deptno=emp.deptno #表关系
	AND dept.dname='research' #业务条件

#三种连接查询：
#内连接inner join:取两个表的交集
#左外连接left join:左表的所有和右表满足条件的，不满足是null
#右外连接right join:右表的所有和左表满足条件的，不满足是null
SELECT * FROM dept LEFT JOIN emp 
	ON dept.deptno=emp.deptno #表关系
	WHERE dept.dname='research' #业务条件
#练习3：查询工作地址在二区的所有员工信息
#子查询:可以用in或者=连接子查询
#根据部门地址查部门编号
SELECT deptno FROM dept WHERE loc="二区" #会查到多个值
#根据编号查员工信息
SELECT * FROM emp WHERE deptno=2 OR deptno=3
SELECT * FROM emp WHERE deptno IN(2,3) #in子查询
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE loc="二区")
#笛卡尔积
SELECT emp.* FROM dept,emp 
WHERE dept.deptno=emp.deptno #表示了两张表的关系
AND dept.loc="二区" #业务条件
#连接查询--相对高效，小表驱动大表（左表写一个小表）
SELECT emp.* FROM dept INNER JOIN emp #内连接取交集
ON dept.deptno=emp.deptno #表示了两张表的关系
WHERE dept.loc="二区" #业务条件

九、扩展视图

1、概述

可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。
下次还要发起相同的sql，直接查视图。
使用： 1，创建视图 2，使用视图

2、测试

create view 视图名 as  SQL语句;
select * from 视图名;
#视图：就是一个特殊的表，缓存上次的查询结果
#好处是提高了SQL的复用率，坏处是占内存无法被优化

#1.创建视图
CREATE VIEW emp_view AS
SELECT * FROM emp WHERE ename LIKE '%a%' #模糊查询，名字里包含a的
#2.使用视图
SELECT * FROM emp_view

十、SQL优化

1、SQL的执行顺序

1、FROM [left_table] 选择表
2、ON <join_condition> 链接条件
3、<join_type> JOIN <right_table> 链接
4、WHERE <where_condition> 条件过滤
5、GROUP BY <group_by_list> 分组
6、AGG_FUNC(column or expression)... 聚合
7、HAVING <having_condition> 分组过滤
8、SELECT (9) DISTINCT column... 选择字段、去重
9、ORDER BY <rder_by_list> 排序

2、优化

1，用字段名代替*
2，where里能用and就不用or
3，设置字段类型时，能用varchar就不用char
4，字段值尽量用数字代替字符串
5，给常用来作为查询条件的字段，设计索引，但是，单表的索引控制再5个以内
6，观察SQL的性能，使用explain关键字，本质上就是看有没有用到索引
7，模糊查询，where name like ‘张%’ ，是高效的写法，甚至可以让索引生效
8，where name=123，会导致索引失效。
where name=‘123’，会让name字段的索引生效

面试题

1. 数据库里索引的作用

主要作用是为了提高查询效率，它的执行也是有一定条件的，不是加了索引就一定能够加快查询的效率，由于索引的创建是需要占据内存空间的.以下不适合加索引：
1、如果每次都需要取到所有表记录，无论如何都必须进行全表扫描了，那么是否加索引也没有意义了
2、对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义
3、对于记录比较少的表，增加索引不会带来速度的优化反而浪费了存储空间，因为索引是需要存储空间的，而且有个致命缺点是对于update/insert/delete的每次执行，字段的索引都必须重新计算更新
4、mysql中通过Explain来查看索引的扫描次数

2. 什么是数据库的事务

事物就是为了保证原有事物的完整性和一致性，通过一定的条件规定该事物的可靠性和安全性（如数据库中的事物）,同时具有4大特性:
A：原子性：一个事物是不可再分的工作单位，该事物要么成功要么失败
B：一致性：事物必须是使数据库从另外一个一致性状态到另一个一致性状态，与原子性密切相关
C：持久性：指一个事物一旦提交后对数据库中数据的改变应该是永久不变的
D：隔离性：一个事物的执行不能够被其他事物所干扰

3. 数据库里的字段约束有哪些，各有什么作用

主键约束：实现实体的完整性（PK：主键），值唯一，不能为空，且使用主键的自增策略
外键约束：用于多表之间的查询该表组建作为另外张表的外键建立标语标志就的关系（FK）
非空约束：（NOT NULL）保证该字段的内容不能为空null
唯一约束：（UNIQUE）字段的值必须唯一的约束
检查约束：（CHECK）检查列的值是否满足一个boolean表达式
默认约束：（default）给指定的字段可以设置默认值，不设置值时使用默认值而不是null

4. 数据库优化的方案有哪些

(1)查询时，能不用* 就不用，尽量写全字段名
(2)索引不是越多越好，每个表控制在5个索引以内
(3)大部分情况连接效率远大于子查询，但是有例外
(4)多用explain 和 profile分析查询语句
(5)连接查询的性能上尽量使用小表驱动大表的原则
(6)查看慢查询日志，找出执行时间长的SQL进行优化
(7)尽量避免使用order by
(8)因为where句子后面条件是执行顺序是从右到左，所以尽量把能过滤掉大部分数据的条件放在最后