mysql基础知识

注：这篇文章是我在学习mysql基础知识时做的笔记，如果内容有错误或者看此篇文章时有什么建议，欢迎在下方评论。

（一）思维导图

二次整理思维导图（如上）

（二）关系型数据库

	概念：建立在关系模型基础之上的，由多张表连接而成的二维表组成的数据库
	特点：
	1、使用表存储数据，格式统一，便于维护
	2、使用SQL语言操作。标准统一，使用方便

一、SQL分类：

DDL：数据库定义语言，用来定义数据库对象，比如定义数据库、表
DML：数据库操纵语言，主要用来对数据库进行增删改
DCL：数据库控制语言，用来创建创建数据库对象，设置访问权限
DQL：数据库查询语言，用来查询表中数据

一、DDL

1、数据库：
	查询：
		查看所有数据库：SHOW DATABASES;
		查看当前所在数据库：SELECT  DATABASE();
	创建：
		create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]
	删除：
		drop database [if exists] 数据库名
	使用：
		use 数据库名
2、表：
	查询：
		查询所有表：show tables;
		查询表结构：desc 表名
		查询指定表的建表语句：show create table 表名
	创建：
		create table 表名(
			字段1 字段的类型[comment 注释],
			...
			字段1 字段的类型[comment 注释]
		)[comment 表注释]
	修改表名;
		alter table 表名 rename to 新表名
	删除表：
		删除表名：drop table [if exists] 表名
		删除指定表，并重新创建表（可用于清空数据）：
			truncate table 表名		
3、字段：
	添加字段（用add）：alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
	修改字段（用change）：alter table 表名 change 旧名 新名 类型(长度)  [comment 注释] [约束];
	删除字段（drop）：alter table 表名 drop 字段名;
	

二、DML

1、添加：
	注：字符串和日期类型应该加引号
	指定字段：insert into 表名(字段名1,字段名2,...) values(值1,值2,...);
	所有字段：insert into 表名 values(值1,值2,...);
	批量添加：
		insert into 表名(字段名1,字段名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...);
		insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...);
2、修改
	update 表名 set 字段名1=值1,字段名2=值2,... [where 条件];
	（如果不加where条件，将更新该表的所有数据）
3、删除
	delete from 表名 [where 条件]
	（如果不加where条件，将删除整个表的数据--->联想到了-->truncate table 表名）
	

三、DQL

1、基本查询
	select 字段1，字段2，字段3... from 表名;
	select * from 表名;	//以表格形式展示
	select * from 表名 \G;	//以行形式展，在命令行中可以生效，其他管理工具不知，可自测。
	
	设置别名：as可省略
	select 字段1 [as] 别名1,字段2 [as] 别名2,... from 表名;
	
	去重：distinct
	select distinct 字段列表 from 表名
	
2、条件查询
	select 字段1，字段2，字段3... from 表名 [where 条件]
	where后的条件：比较运算符、逻辑运算符：
	
3、聚合函数：将一列作为一个整体，进行纵向计算
	count：计算个数
	max：最大值
	min：最小值
	avg：平均值
	sum：求和
	
	用法：select 聚合函数(字段名) from 表名
	例：计算所有name为张三的年龄之和
		select sum(age) from user where name='张三'
	
	注：所有的null值，不进行聚合函数计算
	
4、分组查询
	select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
	
	where与having的区别：
	①执行时机不同：where是在分组之前过滤。不满足where条件，不参与分组，而having是分组之后的结果进行过滤。
	②判断条件不同：where不能对聚合函数进行判断，而having可以
	
	1）查询出男性员工和女性员工的数量：
			select gender,count(*) from emp group by gender
		结果：	|	gender		|	count(*)	|
				1  |		女			|		8			|
				2	|		男			|		9			|
				
	2）查询出男性员工和女性员工的平均年龄：
			select gender,avg(*) from emp group by gender
		结果：	|	gender		|	avg(*)		|
				1  |		女			|		8			|
				2	|		男			|		9			|	
				
	3）查询年龄小于45的员工，并根据工作地址分组，员工数量大于3的工作地址
		select workaddress,count(*) from emp where age <45 group by workaddress having count(*) >=3
		
	注：
		查询结果只能是group by后的字段或者是聚合函数，查询其他字段会出错，
		如：1）中查询的gender为group by 后的值，2）、3）也是如此

5、排序查询
	语法：select 字段列表 from 表名 order by 字段1 排序方式，字段2 排序方式
	排序方式：ASC、DESC
	注：
		如果是多字段排序，当前一个字段相同时，下一个字段的排序方式才有效

6、分页查询
	语法：select 字段列表 from 表名 limit 起始索引,查询记录数
	注：
		起始索引从0开始，起始索引=（查询页码-1）*每页记录数。如：每页五个记录，第二页的起始索引=（2-1）*5=5
	
	1）查询第一页，每页显示10条
		select * from emp limit 0,10;
		或者
		select * from emp limit 10;
		
	2）查询第二页，每页显示10条			//起始索引=（2-1）*10=10
		select * from emp limit 10,10;		//从第10条开始查，查询10
		
7、DQL的执行顺序
		select
			字段列表 		---》④
		from
			表名				---》①
		where
			条件列表		---》②
		group by
			分组字段列表	---》③
		having
			分组条件		
		order by
			排序条件		---》⑤
		limit
			分页参数		---》⑥
			

四、DCL

1、管理用户
	（1）查询
		use mysql
		select * from user
	（2）创建用户
		create user '用户名'@'主机名' identified by '密码'
		将 主机名 设置为%时，可在任意主机访问：create user 'hello'@'%' identified by '147852369'
	（3）修改用户密码
		alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'
	（4）删除用户
		drop user '用户名'@'主机名'

2、设置权限
	（1）查询权限
		show grants for '用户名'@'主机名'
	（2）授予权限
		grant [all] 权限列表 on 数据库名.表名 to '用户名'@'主机名'
	（3）撤销权限
		revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'
注：
	①多个权限之间，使用逗号
	②授权时，数据库名和表名都分别可以使用*进行通配，代表所有
	

二、函数

一、字符串函数

CONCAT(S1,S2,...Sn)：字符串拼接，将S1,S2,...Sn拼接成一个字符串
LOWER(str)：将字符串str全部转为小写
UPPER(str)：将字符串str全部转为大写
LPAD(str,n,pad)：左填充，用字符串pad 对 str的 左边 进行填充，达到n个字符串长度
RPAD(str,n,pad)：右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)：去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)：返回从字符串str从start位置起的len个长度的字符串

例：update name set name=lpad(name,5,'0')：对name的长度不足5的在左边用0填充
	

二、数值函数

ceil(x)：向上取整
floor(x)：向下取整
mod(x,y)：返回x/y的模
rand()：返回0~1内的随机数
round(x,y)：求参数四舍五入的值，保留y位小数
abs(x)：返回x的绝对值

三、日期函数

curdate()：返回当前日期
curtime()：返回当前的时间
now()：返回当前的日期和时间
year(date) ：返回当前date年份
month(date)：返回当前date月份
day(date)：获取当前date的日期
datediff(date1,date2)：返回date1到date2之间的天数

四、流程函数

if(value,t,f)：value为true，则返回t，否则返回f
ifnull(value1,value2)：如果value1不为空，则返回value1，否则返回value2
case when [val1] then [res1] ... else[default] end：如果val1为true，返回res1，...否则返回default默认值
case [expr] when [val1] then [res1] ...else [default] end：如果expr的值等与val1，返回res1，...否则返回default默认值
	

三、约束

（主键、唯一、非空、默认、检查、外键）

定义：作用在表中"字段"上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确性、有效性和完整性。
分类：
	1、非空约束（not null）：限制该字段的数据不能为null
	2、唯一约束（unique）：保证该字段的所有数据都是唯一、不重复的
	3、主键约束（primary key）：主键是一行数据的唯一标志，要求非空且唯一
	4、默认约束（default）：设置默认值
	5、检查约束（check）：保证字段值满足某一条件
	6、外键约束（foreign key）：用来让两张表的数据建立完整性，保证一致性和完整性
		（1）外键操作
			例：CREATE TABLE info(
				id INT PRIMARY KEY AUTO_INCREMENT,
				username VARCHAR(5) NOT NULL UNIQUE,
				gender CHAR(1) CHECK(gender IN ('男','女')),
				status CHAR(1) DEFAULT '1',
				dept_id INT CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id)		//方式1：创建表时就添加外键
			)
			
			方式2：创建表后额外给字段添加外键：
				ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表中要关联的字段名)
				例：ALTER TABLE user ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id)
			
			给字段删除外键：（删除的是外键，而字段仍然存在）
				ALTER TABLE user DROP NOT NULL fk_emp_dept_id
			
		（2）外键删除/更新行为
			no action：删除/更新父表对应的记录时，有关联就不允许删除/更新（与restrict相同）
			restrict：删除/更新父表对应的记录时，有关联就不允许删除/更新（与no action相同）
			cascade（串行）：删除/更新父表对应的记录时，子表也对应删除或更新
			set null：删除父表对应的记录时，子表外键设置为空
			set default：父表有变更时，字表设置为默认值（innodb不支持）
			
			在创建外键时设置：
																																	//设置更新时为cascade   删除时为cascade
				alter table 表名 add constraint 外键名 foreign(外键字段) references 主表名(主表字段名) on update cascade on delete cascade
				

四、多表查询

（联合查询、连接查询、子查询）

1、关系：

一对一：一个人对应一个身份证号，一个身份证号对应一个人
一对多：一个部门对应多个员工，一个员工对应一个部门	---》在员工表中设置外键对应部门表
多对多：一个学生可选多个课，一个课程可以有多个人 	---》再创建一个关系表，关系表和学生和课程分别构成一对多的关系

2、笛卡尔积

多表查询时不经过滤会展现所有种组合情况（笛卡尔积）：
	例：第一个emp2条数据，dept表有3条数据，
		select * from emp,dept：结果有2*3=6条数据。
所以我们需要消除笛卡尔积：
	例：select * from emp e,dept d where d.id=e.did

3、分类

1）连接查询：
	①内连接：查询交集的数据
		隐式内连接：select 字段列表 from 表1,表2 where 条件...;
			例：查询每一个员工的姓名，及其部门的名称
				--->连接表：emp，dept
				--->连接条件：emp.dept_id = dept.id
				select emp.name,dept.name from emp,dept where emp.dept_id=dept.id
				
			注：如果某个员工没有部门，则不会显示该员工
			
		显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
			例：查询每一个员工的姓名，及其部门的名称
				--->连接表：emp，dept
				--->连接条件：emp.dept_id = dept.id
				select e.name,d.name from emp e inner join dept d on e.dept_id = d.id
			注：如果某个员工没有部门，则不会显示该员工
			
	②外连接：
		左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件...;
						将会包括 表1 的所有数据，以及表1和表2交集的数据：
			例：查询emp的所有数据，对应dept的信息
				select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id
					
		右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件...;
						将会包括 表2 的所有数据，以及交集的数据
			例：查询dept的所有数据，对应emp的信息
				select e.*,d.name from emp e right outer join dept d on e.dept_id = d.id
			
	③自连接：select 字段列表 from 表A 别名A join 表B 别名B on 条件...;
			当前表与自身的连接查询，自连接必须使用表别名。自连接查询可以是内连接查询，也可以是外连接查询。
		
		例1：查询员工及其所属领导的名字
			--->表结构：emp a，emp b
			select a.name,b.name from emp a,emp b where managerid = b.id
			
		例2：查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来
			--->表结构：emp a，emp b
			select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id
2）联合查询：
	union：将结果集合并，并且去重
	union all：将结果集合并
	联合查询多张表的列数必需保持一致，字段类型也要保持一致
	
		select * from emp where salary<5000
		union all
		select * from emp where age>50
		
		select * from emp where salary<5000
		union
		select * from emp where age>50
3）子查询（嵌套查询）
	概念：将sql语句中嵌套select的语句，称为子查询或嵌套查询
	举例：select * from t1 where column1=(select column1 from t2);
	分类：
		根据“子查询”的结果划分（是根据子查询的结果，不是根据总体查询结果划分）：
			①标量子查询（子查询结果为为单个值）：select  * from emp where dept_id = (select id from dept where name = '销售部')
			②列子查询（子查询的结果为一列）
				常用的操作符：
					in：在集合内多选一
					not in：不在集合内
					any：子查询返回列表中，中任意一个满足即可
					some：与any相等，是用some的地方都可以使用any
					all：子查询返回列表的返回值都必须满足

				例1：查询“销售部”和“市场部”的所有员工信息
					select * from emp where dept_id in (select id from dept where name = '销售部' or name)
				例2：查询比财务部所有人工资高的员工信息
					select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'))
				例3：查询比研发部任意一人工资高的员工信息（比研发部最低工资高的员工信息）
					select * from emp where salary >any(select salary from emp where dept_id = (select id from dept where name = '研发部'))
				
			③行子查询（子查询的结果为一行）
				例1：查询与张三的薪资及其领导相同的员工信息
					select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张三')
				
			④表子查询（子查询的结果为多列多行）
				例1：查询与“张三”，“李四”的职位和薪资相同的员工信息
					select * from emp where(job,salary) in (select job,salary from emp where name ='张三' or name = '李四');
				例2：查询入职日期是"2006-01-01"之后的员工信息，及其部门信息
					--->理解：分成查询员工的部门信息--》其中的员工：查询为入职日期为2006-01-01的员工信息
							先：查询为入职日期为2006-01-01的员工信息
							然后：查询员工的部门信息
					select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id

练习：1、查询拥有员工的部门ID，部门名称
			select distinct d.id,d.name from emp e,dept d where e.dept_id=d.id
		
		2、查询所有年龄大于40的员工，及其部门名称，若没有部门也显示。
			select e.*,d.name from emp e left join dept d on e.dept_id d = d.id where e.age>40
			
		3、查询所有员工的工资等级
			select e.*,s.grade,s.low_salary,s.high_salary from emp e,salgrade s where e.salary >=s.low_salary and e.salary <= s.high_salary
				

五、事务

1、概念：事务是一组操作的集合，是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即要么同时成功，要么同时失败
2、操作：
	①查看/设置事务提交方式：
		select @@autocommit;
		set @@autocommit = 0;		//1为自动提交；2为手动提交
	②开始事务：
		start transaction 或 begin
	③提交事务：
		commit
	④回滚事务：
		rollback

3、四大特性：ACID
	原子性（Atomicity）：事务不可再分，要么全成功，要么全失败）：事务不可再分，要么全成功，要么全失败
	隔离性（Isolation）：事务不受外部并发的影响，独立的进行
	一致性（Consistency）：事务完成时，必须是所有的数据保持一致
	持久性（Durability）：事务一旦提交或回滚，对数据库的数据的改变是永久的

4、“并发”事务问题
	脏读：一个事务读到另一个事务还没有提交的数据
	幻读：查询时没有查出这条数据，插入时，又发现这条数据已存在
	不可重复读：先后读取同一条纪录，出现的结果不同

5、隔离级别
	读未提交（read uncommitted）：可能脏读、幻读、不可重复读
	读已提交（read committed）：可能幻读、不可重复读
	可重复度（repeatable read，是mysql的默认隔离级别）：可能幻读
	可序列化（serializable）：最安全
	
	查看事务隔离级别：select @@transaction_isolation
	设置事务隔离级别：set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}