mySQL

mySQL

数值和 null 操作结果也变为null。IFNULL(sas, 0) 表示：如果 sas 不为null，则输出 sas 本身的值，如果sas为null，那输出0

last_day(日期)，可以返回 该日期所在月份的最后一 天

多表查询

在默认情况下:当两个表查询时，规则是这样的：

1. 从第一张表中，取出一行和第二张表的每一行进行组合，返回结果含有两张表的所有列.
2. 一共返回的记录数为：$第一张表行数\times 第二张表的行数$
3. 这样多表查询默认处理返回的结果，称为笛卡尔集
4. 解决这个多表的关键就是要写出正确的过滤条件 where,需要程序员进行分析
5. 多表查询的条件不能少于 $表的个数-1$，否则会出现笛卡尔集

自连接查询

将一张表当两张表用，此时需要给表分别取别名

SELECT employee.ename AS worker, manager.ename AS boss 
FROM emp AS employee, emp AS manager
	WHERE employee.mgr = manager.empno

子查询

嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询

1. 单行子查询 只返回一行数据的子查询语句

# 查询SMITH所在部门所有员工，包括SMITH自己。所以先要查询SMITH所在部门，这个结果只有一行
SELECT ename FROM emp
	WHERE deptno = (
		SELECT deptno FROM emp     # 因为嵌套的 select 查询的结果只有一行，所以是单行子查询
			WHERE ename = "SMITH"
	)

2. 多行子查询 返回多行数据的子查询语句，使用关键字in

# 查询和部门10的工种相同的员工的姓名、岗位、工资、部门号，但是不包含10部门自己的员工。
SELECT ename, job, sal, deptno FROM emp
	WHERE job IN (
		SELECT DISTINCT job FROM emp
			WHERE deptno = 10
	) AND deptno != 10  # mysql中不等于还可以用 <> 表示

3. 可以把子查询的结果当作一份临时表，再对该表进行查询。这样的方法很有用
4. 在多行子查询中使用all 操作符

# 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp
	WHERE sal > (
		SELECT MAX(sal) FROM emp
			WHERE deptno = 30
	)
	
# 也可以这样
SELECT ename, sal, deptno FROM emp
	WHERE sal > ALL(
		SELECT sal FROM emp
			WHERE deptno = 30
	)
	

5. 在多行子查询中使用any 操作符
6. 多列子查询 多列子查序则是指查询返回多个列数据的子查询语句

# 查询所有部门、岗位和SMITH完全相同的人，包括SMITH本人
SELECT * FROM emp
	WHERE (deptno, job) = (
		SELECT deptno, job FROM emp
			WHERE ename = "SMITH"
	) 

蠕虫复制

CREATE TABLE tmp LIKE emp  # 创建一个字段和emp表完全一样的表

INSERT INTO tmp
	SELECT * FROM emp  # 快速复制：将emp表中所有记录插入到tmp表

INSERT INTO tmp
	SELECT * FROM tmp  # 自我复制

合并查询

有时在实际应用中，为了合并多个 select 语句的结果，可以使用集合操作符号 union，union all

1. union all 该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。

select ename, sal, job from emp where sal> 2500 union
select ename, sal, job from emp where job = 'MANAGER'

2. union 该操作符用于取得两个结果集的并集。当使用该操作符时，会取消重复行。

外连接

前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不 显示

stu 表

exam 表

1. 左外连接 如果左侧的表完全显示我们就说是左外连接。此时左边的表如果和右边的表没有匹配，那也会显示出来

 SELECT stu.id, `name`, grade FROM stu LEFT JOIN exam  # stu 是左连接表
		ON stu.id = exam.id     

2. 右外连接 如果右侧的表完全显示我们就说是右外连接

约束

约束用于确保数据库的数据满足特定的商业规则。在 mysq| 中，约束包括: not null、 unique,
primary key,foreign key,和check五种。

1. 主键 用法：字段名 类型 primary key

1. 用于唯一的标示表行的数据，当定义主键约束后，该列不能重复且不能为空。
2. 一张表最多只能有一个主键，但可以是复合主键 比如：primary key (id + age)
3. 主键的指定方式有两种：直接在字段名后指定:字段名 primakry key；在表定义最后写primary key(列名) ;
4. 使用desc表名，可以看到primary key的情况

2.unique 用法：字段名 类型 unique

1. 用 unique 指定的字段的值不能有重复。如果该字段没有指定not null ，则该字段仍可以添加多个null
2. 一张表中可以有多个字段为unique ，unique not null 的效果就类似 primary key

3.foreign key 用法：foreign key 本表字段名 references 主表名(主键名或unique字段名)

从表中某个字段可以设置为外键并指定依附于其他表的哪个字段，设置之后，从表中的该字段的值就不可以随意添加，一定得是主表中被依附字段中存在的值

1. 外键指向的表的字段，要求是primary key 或者是 unique
2. 表的存储引擎是innodb,这样的表才支持外键
3. 外键字段的类型要和主键字段的类型一致(长度可以不同)
4. 外键字段的值，必须在主键字段中出现过，或者为 null 前提是外键字段允许为 null
5. 一旦建立主外键的关系，数据不能随意删除了.

4.check

1. 用于強制行数据必須満足的条件,假定在sal列.上定乂了check約束,并要求sal列値在1000 ~ 2000之旬如果不再1000 ~ 2000之旬就会提示出錯。
2. mysq1 5.7目前还不支持 check , 只做语法校验，但不会生效

自增长

字段名 整型 primary key auto_ increment

1. 一般来说自增长是和primary key 配合使用的
2. 自增长也可以单独使用，但是需要配合一个unique
3. 自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
4. 自增长默认从1开始，你也可以通过如下命令修改 alter table 表名 auto increment = xxx
5. 如果你添加数据时，给自增长宇段(列)指定的有值，则以指定的值为准。那么下次自增长就从该指定值开始
6. 如果需要使用自增长，那么insert 时只需要将对应字段位置插入的值写为null

索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调 sql,查询速度就可能提高百倍干倍。

用法：create index 索引名 on 表名(字段名)

1. 创建了索引的字段访问速度才能提快，其他字段并不会提升

原理

创建索引会整个表的该字段叉棍见一个树形结构，可以是二叉排序树，平衡树等，来加快查找。所以当我们有删除或者添加或者修改操作时，会对索引进行维护，所以速度有些影响

索引类型

1. 主键索引主键自动的为主索引(类型Primary key)
2. 唯一索引 (UNIQUE)
3. 普通索引 (INDEX)

索引的增删改查

1. 添加唯一索引 CREATE UNIQUE INDEX 索引名 ON 表名 (字段名)
2. 添加普通索引
   1. CREATE INDEX 索引名 ON 表名 (字段名) 或
   2. ALTER TABLE 表名 ADD INDEX 索引名 (字段名)
3. 添加主键索引 ALTER TABLE 表名 ADD PRIMARY KEY (字段名)

如果某列的值，是不会重复的，则优先考虑使用unique索引， 否则使用普通索引

4. 删除索引 DROP INDEX 索引名 ON 表名
5. 删除主键索引 ALTER TABLE 表名 DROP PRIMARY KEY
6. 查询索引 SHOW INDEX FROM 表名 或 SHOW INDEXES FROM 表名
7. 修改索引：先删除再添加新的

哪些列.上适合使用索引？

1. 较频繁的作为查询条件字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在WHERE子包中字段不该创建索引

事务

事务用于保证数据的一致性,它由一组相关的 dml(增删改)语句组成该组的 dml 语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。

举例：向某人转账，可以分为两步：1⃣️从自己账户扣除金额；2⃣️目标账户增加金额。这两个操作就构成了一个事务，因为拆开执行，如果第一步成功，第二步失败了，就没有了数据的一致性。

当执行事务操作时(dml语句) ,mysql 会在表上加锁,防止其它用户改表的数据。这对用户来讲是非常重要的

事务操作

1. start transaction 开始一个事务
2. savepoint 保存点名 设置保存点
3. rollback to 保存点名 回退事务到特定保存点
4. rollback 直接回退到事务开始状态
5. commit 提交事务，所有的操作生效，不能回退

1. 回退事务
   在介绍回退事务前，先介绍一-下保存点(savepoint).保存点是事务中的点，用于取消部分事务，当结束事务时(commit)，会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。
2. 提交事务
   使用commit语句可以提交事务.当执行了commit语句后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效.]

SELECT * FROM salgrade;

START TRANSACTION;

INSERT INTO salgrade
	VALUES (6, 10000, 15000);

SAVEPOINT one;

INSERT INTO salgrade
	VALUES (7, 15001, 20000);

ROLLBACK TO one

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2. 如果开始一个事务，你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态.
   你也可以在这个事务中(还没有提交时)，创建多个保存点比如: savepoint aaa 执行dml , savepoint bbb 你可以在事务没有提交前，选择回退到哪个保存点.
3. mysql的事务机制需要 innodb 的存储引擎还可以使用, myisam不好使.
4. 开始一个事务：start transaction 或 set autocommit=off
5. 只有结束一个事务 commit 之后，才能开启一个新的事务

事务隔离级别

mysql 数据库支持多个连接。当某个连接登录时查询到现有数据库的数据应该只受到自己的控制，而不应该接收到其他连接对数据库进行的操作。比如说：A连接在数据库新增了一条记录，B连接说明都没做，只是在A连接新增记录前后查询了一次数据库，因此B连接两次查询的结果应该都是一样的，不应该受到A连接的影响。为了保证这种数据的一致性，于是有了事务隔离。多个连接开启各自事务操作数据库数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

1. 脏读(dirty read) 当一个事务还没 commit 就能读取另一个事务尚未提交的增删改时，产生脏读
2. 不可重复读(nonrepeatable read) 一个事务还没commit 就可以读取由于其他提交了的事务所做的修改或删除，此时发生不可重复读。
3. 幻读(phantom read)一个事务还没commit 时就可以读取到其他提交了的事务所做的插入操作 ，此时发生幻读。

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交	✅	✅	✅	不加锁
读已提交	❌	✅	✅	不加锁
可重复读	❌	❌	❌	不加锁
可串行化	❌	❌	❌	加锁

✅ 表示可能出现 ❌ 表示不会出现

4. 可重复读 只有在自己commit 事务后，才能收到其他commit了的事务 做的操作。但是当自己想操作数据库时，发现已经有事务在操作数据库，也不会阻塞，这就是不加锁。
5. 可串行化 的隔离级别最高；加锁的意思就是：当有一个连接还没提交事务时，另一个连接想操作数据库时会被阻塞，直到另一个连接提交事务之后，才能操作数据库。这非常像操作系统的访问临界区的机制。

事务的acid特性

1. 原子性(Atomicity)原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要
   么都不发生。

2. 一致性(Consistency)事务必须使数据库从一个一致性状态变换到另外-个一致性状态

3. 隔离性(Isolation)事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事
   务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4. 持久性(Durability)持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，
   接下来即使数据库发生故障也不应该对其有任何影响

5. 查看当前会话隔离级别 select @@transaction_isolation

6. 查看系统当前隔离级别 select @@global.transaction_isolation

7. 设置当前会话隔离级别 set session transaction isolation level repeatable read

8. 设置系统当前隔离级别 set global transaction isolation level repeatable read

9. mysql默认的事务隔离级别是 repeatable read，一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)

表类型和存储引擎

MySQL 的表类型由存储引擎(Storage Engines)决定，主要包括MyISAM、innoDB, Memory
MySQL数据表主要支持六种类型，分别是: CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB.
这六种又分为两类，一类是”事务安全型”(transaction-safe), 比如:InnoDB；其余都属于第二类，称为”非事务安全型”(non-transaction-safe)

特点	Myisam	InnoDB	Memory	Archive
批量插入速度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
存储限制	没有	64TB	有	没有
B树索引	支持	支持	支持
哈希索引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

1. MyISAM 不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
2. InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM 存储引擎，InnoDB写的处理效率差一些并且会 占用更多的磁盘空间以保留数据和索引。
3. MEMORY 存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，但是表的结构还在。

show engines; 查看所有引擎

alter table 表名 engine = innoDB 修改表的引擎

CREATE TABLE t28 (
	id	INT,
	name VARCHAR(32)) ENGINE MYISAM;

如何选择引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAIM是不二选择，速度快
2. 如果需要支持事务，选择InnoDB
3. Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态 )

视图

如果一个表的列信息很多，有些信息是个人重要的敏感信息，如果我们希望某个用户只能查询表中的一些非敏感信息，此时就可以使用视图。

1. 视图是一个虚拟表，其内容由查询定义。同真实的表样，视图包含列,其数据来自对应的真实表(基表)
2. 通过视图可以修改基表的数据，基表的改变，也会影响到视图的数据
3. 创建视图后，在数据库的存储路径里，只能看到视图的结构文件，并没有真正的数据
4. 视图上还可以再创建视图
5. 视图还可以使用子查询 eg：

CREATE VIEW emp_ view03
	AS	(
			SELECT empno, ename, dname, grade
				FROM emp, dept, salgrade
				WHERE emp.deptno = dept.deptno AND (sal BETWEEN losal AND hisal)
   )  -- 这种复杂的结果做成视图后，下次直接查视图就方便很多

基本使用

# 创建一个视图，该视图只能查询两列数据
create view emp_view as 
	select empno, ename from emp;
	
-- 修改成新的视图
alter view emp_view as 
	select empno, ename, sal from emp; 
	
desc emp_view  -- 查看视图
show create view emp_view  -- 查看创建视图的指令
drop view emp_view  -- 删除视图

视图的特点

1. 安全 一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2. 性能 关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接JOIN 。这样做不但麻烦，效率相对也比较低。如果建立-个视图，将相关的表和字段组合在一起，就可以避免使用``JOIN`查询数据。
3. 灵活 如果系统中有一-张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立- -张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

mySQL管理

mysq中的用户，都存储在系统数据库mysq中user表中。user表中几个重要的字段：

1. host 允许登录的 "位置”，localhost 表示该用户只允许本机登录，也可以指定ip地址，比如:192.168.1.100
2. user 用户名
3. authentication string 密码，是通过 mysql 的 password() 函数加密之后的密码。

用户管理

当我们做项目开发时，可以根据不同的开发人员，赋给他相应的 Mysql 操作权限。所以，Mysql 数据库管理人员(root)，根据需要创建不同的用户，赋给相应的权限，供人员使用。当不同用户登录进入

mySQL 数据库管理系统后，根据权限的不同可以操作的数据库可能不相同；即便相同，那么统一数据库下可操作的表、视图、触发器等也可能不同

1. 创建用户 create user 'ymy'@'localhost' identified by '123534' 三对引号分别代表用户名、允许的登录位置(可以是具体的IP)、密码(数据库存储的是该密码加密后的字段)
2. 删除用户 drop user '用户名'@'登录位置'
3. 修改自己账户的密码 set password=password('新密码') 修改其他用户的密码 需要有对应的权限 set password for 'root'@'localhost=password('新密码)'
4. 用户授权 grant 权限列表 on 库.对象名 to '用户名’@'登录位置’ [identified by '密码’]
   1. 权限列表，多个权限用逗号分开 `grant select, delete, create on …``
   2. ``grant all [privileges] on …` 表示赋予该用户在该对象上的所有权限
   3. 特别说明 *.* 代表本系统中的所有数据库的所有对象(表，视图，存储过程)；库.* 表示某个数据库中的所有数据对象(表，视图，存储过程等)
   4. identified by 可以省略，也可以写出. (1)如果用户存在，就是赋予权限的同时修改该用户的密码；(2)如果该用户不存在，就是创建该用户并赋予权限
5. 回收权限 revoke 权限列表 on 库.对象名 from '用户名'@'登录位置'
6. 如果权限没有生效，执行 FLUSH PRIVILEGES

1. 在创建用户的时候，如果不指定 Host 则为% , % 表示表示所有IP都有连接权限create user XXX 你也可以这样指定 create user 'xxx @'192.168.1.% 表示 xxx 用户在 192.168.1.* 的ip 都可以登录mysql
2. 在删除用户的时候，如果host不是%，需要明确指定'用户'@'host' ，如果 host 是 %，drop user xxx 即可。 像192.168.1.%这样的host，也必须完全指定

数据库设计范式

1. 任何一张表必须有主键，每一个字段原子性不可再分
2. 建立在第一条规则之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。当主键是由多个字段联合构成的，其他字段如果只依赖主键中的部分字段，就产生了部分依赖。
3. 建立在第二条规则之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

1. 一对多，两张表，多的表加外键
2. 多对多，三张表，关系表两个外键
3. 一对一的时候，有时候字段太多了，需要拆分表，比如：

id	 login_name  login_pwd  real_name  email  address ...

此时可以将一张表拆分成两个表。登录信息表：

id(pk)  login_name  login_pwd

用户详细信息表：

id(pk)  real_name  email  address  login_id(fk + unique)

数据库设计三范式是理论.上的。实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在 sql 当中，表和表之间连转次数越多，效率越低( 笛卡尔积)。有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的,并且对于开发人员来说，sql语句的编写难度也会降低。