2 进阶
直接调用函数方法：
select 函数(参数);可以和sql语句配合使用
字符串函数
| 函数 | 功能 |
|---|---|
| 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个长度的字符串 |
数值函数
| 函数 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
日期函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间获取指定date的年份 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
| 函数 | 功能 |
|---|---|
| lF(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默认值（when和then可以有很多个） |
| CASE [ expr ] WHEN [val1 ] THEN [res1] ... ELSE [ default] END | 如果expr的值等于val1，返回res1，..否则返回default默认值（when和then可以有很多个） |
约束
作用于表中字段上的规则，用于限制存储在表中的数据 保证数据库中数据的正确性、有效性、完整性
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | DEFAULT |
| 检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 | FOREIGN KEY |
| 约束是作用在表中字段上的，可以在创建表/修改表的时候添加约束 | ||
| 一个字段可以添加多个约束 |
示例：
| 字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
|---|---|---|---|---|
| id | ID唯一标识 | int | 主键，并且自动增长 | PRIMARY KEY, AUTO_INCREMENT |
| name | 姓名 | varchar(10) | 不为空，并且唯一 | NOT NULL, UNIQUE |
| age | 年龄 | int | 大于0，并且小于等于120 | CHECK |
| status | 状态 | char(1) | 如果没有指定该值，默认为1 | DEFAULT |
| gender | 性别 | char(1) | 无 |
create table user(
id int primary key auto_increment comment'主键',
name varchar(10) not null unique comment'姓名',
age int check(age>0 && age <=120) comment'年龄',
status char(1) default'1' comment'状态',
gender char(1) comment'性别'
)comment'用户表';
insert into user(name, age, status, gender) values('tom',19,1,'男'),('jerry',25,1,'男');不需要插入id数据，数据库也能自动添加
外键约束
让两张表的数据之间建立连接从而保证数据的一致性和完整性
[!warning] 注意 目前上述的两张表，在数据库层面并未建立外键关联，所以是无法保证数据的一致性和完整性的。
添加外键：
创建表的时候直接添加
CREATE TABLE表名(
字段名 数报类型
……
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);表结构创建好后再添加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);示例： 在创建表后添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);在删除外键连接的列时会判断与父表使用外键连接的子表中是否有与该列相关的键如果有就阻止删除
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称；删除/更新行为
| 行为 | 说明 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致） |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NOACTION一致） |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 |
| SET NULL | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。（设为null） |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值（Innodb不支持） |
| 语法：在添加外键的语法后面加上关键字 |
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;on update 和on delete分别指在更新时和删除时做什么
多表查询
笛卡尔积
使用select可以查询多个表
select * from emp,dept;但查询出来数据并不符合直觉，其中一个表中的主键都要和另一个表中的主键进行匹配，查询出来的数据量就是两个表中数据的乘积，这种现象就被称为笛卡尔积
笛卡尔乘积指在数学中两个集合A和集合B的所有组合情况 在多表查询时，需要消除无效的笛卡尔积 要消除无效的笛卡尔积就需要添加条件限制，根据两个表某一字段的对应关系进行筛选
select * from emp,dept where emp.dept_id=dept.id;分类
连接查询
内连接
相当于查询a、b交集部分数据
隐式内连接
select 字段列表 from 表名（可多选） where 条件;之前的例子就是隐式内连接，如果筛选的字段的某个是null则不属于两者交集部分就不会显示出来
显式内连接 显式内连接和隐式内连接作用相同 但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显
select 字段列表 from 表1 [inner] join 表2 on 连接条件;示例：
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 条件;示例：
select e.*,d.name from emp e left outer join dept d on d.id = e.dept_id;右外连接 查询右表所有数据以及两帮表交集部分数据
select 字段列表 from 表1 right [outer] join 表2 on 条件;自连接 当前表与自身的连接查询，自连接必须使用表别名 即将一张表的两个别名看作两张表来操作
select 字段列表 from 表1 别名a join 表1 别名b on 条件;如：一个表中员工有自己的id以及自己直属领导id（领导也在这个表中）就可以通过自连接查询员工的直属领导
select a.name,b.name from emp a, emp b where a.managerid=b.id;但是查询到的只是交集，如果某人没有直属领导（领导id为null）就不会显示出来 此时需要使用外连接查询出所有数据
select a.name,b.name from emp a left outer join emp b on a.managerid=b.id;union联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...；all：是否去重，如果存在两个条件都满足的元素不使用all会去重只显示一次，使用all每次都会显示 省略号表示没有写完后面可以跟sql语句进行选择性查询 多张表的列数必须一致，字段类型也必须一致
子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT *FROM t1 WHERE column1 =(SELECT column1 FROM t2 );子查询外部的语句可以是INSERT/UPDATE/DELETE /SELECT的任何一个。 根据子查询结果不同，分为:
标量子查询
子查询结果为单个值：数字，字符串，日期等 常用操作符：= <> > >= < <=
查询“销售部”的所有员工信息
a.查询“销售部”部门ID
select id from dept where name =
'销售部';
根据销售部门id查询员工信息
select * from emp where dept_id=4;
使用标量子查询
select * from emp where dent id = (select id from dent where name ='销售部');列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。 常用操作符：
| 操作符 | 描述描述 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
查询比 财务部 所有工资都高的员工信息
a.查询所有 财务部 人员工资
select salary from emp where dept_id=(select id from emp where name='财务部');
b.比财务部 所有人工资都高的员工信
select * from emp where salary > all (select salary from emp where dept_id =(select id from dept where name='财务部'));行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。 常用的操作符：=、<>、IN、NOT IN
-- 1.查询与“张无忌”的薪资及直属领导相同的员工信息
--a.查询“张无忌”的薪资及直属领导
select salary, managerid from emp where name ='张无忌”;
b.查询与“张无忌”的薪资及直属领导相同的员工信息；
select * from emp where (salary,managerid) = (select salary, managerid from emp where name ='张无忌');表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。 常用的操作符：IN
1.查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
a、查询“鹿杖客”，“宋远桥”的职位和薪资
select job, salary from emp where name ='鹿杖客'or name=‘宋远桥';
b、食询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name ='鹿杖客'or name ='宋远桥');根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。
事务
简介
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。 如：张三给李四转一千块钱操作分为以下步骤 查询张三账户余额 张三账户余额-1000 李四账户余额+1000 如果在给李四账户余额+1000操作时出了问题张三的钱减了但李四的钱没加显然是不行的 这种情况就要用到数据库的事务操作 MySQL的事务默认是自动提交的，也就是说每次执行sql语句都会隐式提交事务
操作
查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit=0;如果是1就是自动提交，0就是手动提交
提交事务
COMMIT;回滚事务
ROLLBACK;开启事务
START TRANSACTION 或 BEGIN;事务的四大特性
原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
并发事务问题
脏读 一个事务读到另外一个事务还没有提交的数据。 当前面的事务执行回滚操作，另外的事务查询到的信息就没有价值了
不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。 当一条事务执行时先查询了数据库，然后另一个并发事务修改了数据库，这样这条事务再执行查询时就会和前面查询的结果不同
幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。 一条事务查询时发现一个主键不存在（主键独立且唯一）此时另一条事务插入了该主键，这条事务再插入相同主键时就会发生冲突
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted读未提交 | t | t | t |
| Read committed读已提交 | f | t | t |
| Repeatable Read(默认)可重复读 | f | f | t |
| Serializable串行化 | f | f | f |
| 这个默认隔离级别是MySQL的，其他的数据库可能不同 | |||
| 从上至下安全性依次提高，但性能依次降低，要选择合适的隔离级别 |
查看事务隔离级别
SELECT@@TRANSACTION_ISOLATION;设置事务隔离级别
SET [SESSION| GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}session会话级别，表示只针对当前客户端窗口有效 global全部，针对所有客户端有效
在串行化级别时当一个事务开启时其他事务不能执行，只能等该事务执行完才能执行
引擎
InnoDB
在查询建表语句时会显示使用的引擎，如果不指定默认使用lnnoDB 使用show engines展示所有引|擎及其信息 InnoDB逻辑存储结构
其中page和extent都是固定的大小
MyISAM
MyISAM是MySQL早期的默认存储引擎. 特点 不支持事务，不支持外键 支持表锁，不支持行锁 访问速度快 文件 XXX.sdi：存储表结构信息 XXX.MYD:存储数据 XXX.MYI:存储索引
Memory
Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。 特点 内存存放 hash索引（默认） 文件 xxx.sdi：存储表结构信息
InnoDB：是Mysq|的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。 MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。 MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
| 特点 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | ||
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 支持 | ||
| 全文索引 | 支持（5.6版本后） | 支持 | |
| 空间使用 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 |
贡献者
pinkdopeybug版权所有
版权归属：
