代码拉取完成，页面将自动刷新
train_dev_1.3.4;train_dev_1.3.4;SELECT 100;
SELECT 'join';
SELECT 100*90;
SELECT 100 值;
SELECT 200 AS 值;
SELECT 300 "hello world"; #别名有空格一般使用双引号
SELECT DISTINCT dept_id from t_emp;
SELECT 100+90; #数值相加
SELECT '100' + 90; #转成数值类型，再相加
SELECT 'john' + 90; #john转成0, 然后再相加
SELECT null + 90; #只要有一个为null, 则计算结果为null
SELECT last_name + first_name as 姓名 from t_emp; #所以结果为0
SELECT CONCAT(last_name, first_name) as 姓名 from t_emp;
SELECT CONCAT('A', 'B', 'C');
SELECT IFNULL(last_name, 'a') AS 名 from t_emp;
SELECT* FROM t_emp WHERE last_name LIKE '%a%'; #名字包含a的员工
SELECT last_name FROM t_emp WHERE last_name LIKE '_%';#查询员工名中第二个字符为_的员工名，需要使用\转义字符
SELECT last_name FROM t_emp WHERE last_name LIKE '$_%' ESCAPE '$';#查询员工名中第二个字符为_的员工名，定义$为转义字符
SELECT * FROM t_emp WHERE id BETWEEN 100 AND 120;
SELECT last_name, job_id FROM t_emp WHERE job_id IN('ceo', 'cfo');
SELECT * FROM t_emp WHERE salay <=> null;
SELECT * FROM t_emp WHERE salay <=> 1200;
SELECT * FROM t_emp;
SELECT * FROM t_emp WHERE last_name like '%%';
SELECT * FROM t_emp ORDER BY salary DESC;
SELECT salary 薪资 FROM t_emp ORDER BY 薪资 DESC;
SELECT LENGTH(last_name) 字节长度 FROM t_emp ORDER BY LENGTH(last_name) DESC;
SELECT * FROM t_emp ORDER BY salary ASC, ept_id DESC;
SELECT VERSION();#版本号
SELECT database(); #显示当前数据库
SELECT user();#显示当前用户
SELECT password('字符'); 返回字符的密码形式
SELECT md5('字符'); 返回字符的md5形式
1 if函数
SELECT IF(10 < 5, 'yes', 'no');
SELECT IF(last_name is null, '没有名字', '有名字');
2 case 函数
/*
使用方法一、
casse 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end;
/
SELECT salary 原始工资, department_id,
CASE department_id
WHEN 30 THEN salary1.1
WHEN 40 THEN salary1.2
WHEN 50 THEN salary1.3
ELSE salary
END AS 新工资
FROM t_emp;
/*
使用方法二：
case
when 条件1 then 要显示的值1或语句1,
when 条件2 then 要显示的值2或语句2,
...
else 要显示的值n或语句n
end
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'b'
ELSE 'C'
END AS 工资级别
FROM t_emp;
SELECT SUM(salary) FROM t_emp;
SELECT SUM(DISTINCT salary) from t_emp;
SELECT AVG(salary), emp_id FROM t_emp; #错误，不能这样写
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) 天数 FROM t_emp;
SELECT AVG(salary), department_id, job_id FROM t_emp
GROUP BY department_id, job_id ORDER BY AVG(salary) DESC;
按年代分类
** sql92标准-仅支持内连接
** sql99标准-支持内连接+左外连接+右外连接+交叉连接
按功能分类
** 内连接：
*** 等值连接
*** 非等值连接
** 外连接：
*** 左外连接
*** 右外连接
*** 全外连接
** 交叉连接：
SELECT name, boyname FROM boys,beauty WHERE beauty.boyfriend_id=boys.id;
SELECT e.last_name, e.job_id, j.job_title FROM employees e, jobs j WHERE e.`job_id`=j.`job_id`;
案例 查询员工的工资和工资级别
SELECT salary, grade_level FROM employees e, job_grades j
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;
案例 查询员工名和上级的名称
SELECT e.employee_id, e.last_name,m.employee_id, m.last_name
from employees e, employees m WHERE e.manager_id = m.employee_id;
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类 连接类型
内连接： 【inner】
外连接：
左外： left 【outer】
右外： right【outer】
全外： full 【outer】
交叉连接： cross
等值连接
非等值连接
自连接
查询员工名、工种号、工种名, 两个表的顺序可以换
inner可以去掉
SELECT e.last_name, e.job_id, j.job_title FROM employees e
INNER JOIN jobs j ON e.`job_id`=j.`job_id`;
案例 查询员工名， 部门名，工种名，并按部门名降序
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN department d on e.department_id = d.departmentPid
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;
筛选条件放在where后面，连接条件放在on后面， 提高分离性，便于阅读
inner join 连接和sql92语法中的等值连接效果一样的，都是查询多表的交集。
案例 查询员工的工资和工资级别
SELECT salary, grade_level FROM employees e JOIN job_grades j
ON salary BETWEEN g.lowest_sal AND g.highest_sal;
案例 查询员工名和上级的名称
SELECT e.employee_id, e.last_name,m.employee_id, m.last_name
from employees e JOIN employees m ON e.manager_id = m.employee_id;
应用场景： 用于查询一个表中有，另外一个表中没有的记录
外连接的查询结果 = 内连接的结果 + 主表中有而从表中没有的记录
如果从表中有和它匹配的， 则显示匹配的值
如果从表中没有和它匹配的，则显示null
右外连接： right join 右边的是主表
案例 查询男朋友不在男神表的女神名
outer可以去掉
SELECT b.name, bo.* FROM beauty b
LEFT OUTER JOIN boys bo ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;
SELECT b.name, bo.* FROM boys bo
RIGHT OUTER JOIN beauty b ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;
SELECT b., bo. FROM beauty b
FULL OUTER JOIN boys bo ON b.boyfriend_id=bo.id;
SELECT b., bo. FROM beauty b CROSS JOIN boys bo;
相当于 ： SELECT b., bo. FROM beauty b, boys bo;
含义： 出现在其他语句中的select语句，称为子查询或内查询
分类：
按查询出现的位置：
select 后面-仅仅支持标量子查询
from 后面-支持表只查询
where 或 having 后面-支持标量子查询，列子查询，行只查询
exists 后面-支持表子查询
按结果集行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
表子查询（结果集一般为多行多列）
特点：
1，子查询放在小括号内
2，子查询一般放在条件右侧
3， 标量子查询，一般搭配着单行操作符使用：> < >= <= = <>
4, 列子查询，一般搭配着多行操作符使用： in、any/some、 all
in/not in 等于列表中的任意一个, a in (10, 20, 30) 替换 a = any(10, 20, 30), a not in （10， 20， 30） 替换 a <> all(10, 20,30)
any/some 和列表中的某一个值比较(较少用，替换方案)，a > any(10, 20, 30), 可以替换成 a > min(10, 20, 30)
all 和列表中的所有值比较（较少用，替换方案）， a > all(10,20,30), 替换成 a > max(10,20,30)
5, 子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果
案例 谁的工作比lisi高
SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name='lisi');
案例 返回job_id与141号员工相同，salary比143号员工多的员工 姓名、job_id和工资
SELECT name, job_id, salary FROM employees
WHERE job_id=(SELECT job_id FROM employees WHERE employee_id = 141)
AND salary>(SELECT salary FROM employees WHERE employee_id = 153);
案例 返回location_id是1400或1700的部门编号
SELECT last_name FROM employees WHERE department_id in(
SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700)
);
案例 查询员工编号最小并且工资最高的员工信息。
SELECT * FROM employees WHERE employee_id=(
SELECT MIN(employee_id) FROM employees
) AND salary = (
SELECT MAX(salary) FROM employees
);
或使用行子查询：
SELECT * FROM employees WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary) FROM employees
);
案例 查询每个部门的员工个数
SELECT d., (
SELECT COUNT() FROM employees e WHERE e.department_id = d.department_id
)个数
FROM departments d;
案例 查询员工号=102的部门名
SELECT (
SELECT department_name FROM departments d
INNER JOIN employees e ON d.department_id=e.department_id
WHERE e.employee_id=102
)部门名；
案例： 查询每个部门的平均工资的工资等级
SELECT ag_dep.*, g.grade_level FROM (
SELECT AVG(salary) ag, department_id FROM employees GROUP BY department_id
) ag_dep
INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
exists() 表示是否存在, 最终结果是1或0
SELECT EXISTS(SELECT employee_id FROM employees);
#案例 查询有员工的部门名, 可以用in代替
SELECT department_name FROM departments d
WHERE EXISTS(SELECT * FROM employee e WHERE d.department_id = e.department_id);
案例： 查询各部门中工资比本部门平均工资高的员工的员工号、姓名、工资。
SELECT employee_id, last_name, salary,e.department_id FROM employees e
INNER JOIN (
SELECT AVG(salary) ag, department_id FROM employees GROUP BY department_id
) ag_dep
ON e.department_id=ag_dep.department_id
WHERE salary>ag_dep.ag;
语法：
select 查询列表 -7
from 表 -1
【连接类型 join 表2 -2
on 连接条件 -3
where 筛选条件 -4
group by 分组字段 -5
having 分组后的筛选 -6
order by 排序的字段】-8
limit offset, size; -9
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：1，limit语句放在查询语句的最后，运行也在最后。
2，公式： 要显示的页数page, 每页显示的条数size
select 查询列表 from 表 limit (page - 1) * size, size;
案例 查询前5条员工信息
SELECT * from employees LIMIT 0, 5;
相等于：SELECT * from employees LIMIT 5;
案例 查询第11条到第25条
SELECT * FROM employees LIMIT 10, 15;
案例 有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;
union 联合 合并： 将多条查询语句的结果合并成一个结果
应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时。
特点： 1、多个查询的列数必须一致
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认是去重的，如果使用union all 会保留重复项。
案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
案例： 查询中国用户中男性的信息以及外国用户中男性的用户信息
SELECT id, cname, csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id, tName, tGender FROM t_ua WHERE tGender='male';
语法：
#方式一，insert into 表名(列名,...) values(值1,...);
1,插入的值的类型要与列的类型一致或兼容
2,列的个数和值的个数必须一致
3，可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty(id, name, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '18823185102', NULL, 2);
INSERT INTO beauty
VALUES(13, '唐艺昕', '女', '1990-4-23', '18823185102', NULL, 2),(14, '刘昕', '女', '1990-4-23', '18823185102', NULL, 2);
INSERT INTO beauty(id, name,sex) SELECT 25, '宋茜','女';
#方式二，insert into 表名 set 列名=值,...;
INSERT INTO beauty SET id=19,name='刘涛',phone='18823185102';
#两种方式大pk
1，方式一支持插入多行，方式二不支持。
2，方式一支持子查询，方式二不支持
语法：
1修改单表的记录
update 表名 set 列=值，列=值,...where 筛选条件；
UPDATE beauty SET phone=18899999999, name='唐一' WHERE name LIKE '唐%';
2修改多表的记录
sql92语法
update 表1 别名， 表2 别名 set 列=值，... where 连接条件 and 筛选条件；
sql99语法
update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值，... where 筛选条件;
案例 修改张无忌的女朋友的手机号为114
UPDATE boys bo INNER JOIN beauty b ON bo.id=b.boyfriend_id set b.phone=114 WHERE bo.boyName='张无忌';
语法：
1单表的删除
delete from 表名 where 筛选条件；
案例： 删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
2多表的删除
语法：
sql92语法:
delete 表1的别名,表2的别名 from 表1 别名, 表2 别名, where 连接条件 and 筛选条件；
sql99语法:
delete 表1的别名,表2的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件；
案例：删除张无忌的女朋友的信息 (只删女神表)
DELETE b FROM beauty b INNER JOIN boys bo ON b.boyfriend_id=bo.id WHERE bo.boyName='张无忌';
3 truncate 方式删除，清空表
TRUNCATE TABLE boys;
#delete pk truncate
1,delete 可以加where条件，truncate不能加；
2,truncate删除效率高一点点
3,假如要删除的表中有自增长的列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
语法： create database 库名;
CREATE DATABASE books;
CREATE DATABASE IF NOT EXISTS books;
更改库的字符集
ALTER DATABASE books CHARACTER SET utf-8;
DROP DATABASE books;
DROP DATABASE IF EXISTS books;
语法：
create table 表名(
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
...
);
案例：创建book表
CREATE TABLE IF NOT EXISTS book(
id INT,
bName VARCHAR(20) COMMENT '书名',
price DOUBLE COMMENT '价格',
authorId Int COMMENT '作者id',
publishDate DATETIME COMMENT '发布时间EMAIL_BEANEMAIL_BEANEMAIL_BEAN'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='教师端升级管理表';
修改列名
alter table 表名 change column 旧列名 新列名 列的类型;
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
修改列的类型或约束
alter table 表名 modify column 列名 新类型;
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
添加列
alter table 表名 add column 新列名 新列类型;
ALTER TABLE book ADD COLUMN pages DOUBLE;
删除列
alter table 表名 drop column 列名;
ALTER TABLE book DROP COLUMN pages;
修改表名
alter table 旧表名 rename to 新表名;
ALTER TABLE book RENAME TO mybook;
DROP TABLE IF EXISTS mybook;
1,仅仅复制表的结构
CREATE TABLE author2 LIKE author;
2,复制表的结构 + 数据
CREATE TABLE author2 SELECT * FROM author;
3,只复制部分数据
CREATE TABLE author2 SELECT id, au_name FROM author WHERE nation='中国';
4，仅仅复制某些字段
CREATE TABLE author2 SELECT id, au_name FROM author WHERE 1=2;
类型 字节
Tinyint 1
Smallint 2
Mediumint 3
Int、integer 4
Bigint 8
#如何设置无符号和有符号, 无符号如果插入负数，则插入的是0， 默认有符号。
#如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值。
#如果不设置长度，会有默认的长度，这个长度不是值的大小， 而是表中显示的宽度，
#如类型后面加ZEROFILL，超过宽度0填充。
create table tab_int(
t1 INT,
t2 INT UNSIGNED,
t3 INT(10) ZEROFILL
);
定点数: 最大取值范围与double相同，给定decimal简写成dec的有效取值范围与m和d决定，默认m是10，d是0。
类型 字节
DEC(M, D) M+2
DECIMAL(M, D) M+2
浮点数: m、d可以省略。m所有数字的位数=整数位+小数位，d指小数点后面有几位，m和d的默认值由类型决定。
类型 字节
float(M, D) 4
double(M, D) 8
较短的文本：char、 varchar、 enum-枚举字符只能选一个、set-可以选多个字符
字符串类型 最多字符数 描述及存储的需求 特点 空间耗费 效率
char(M) M默认1 M为0255之间的整数 固定长度的字符 比较耗费 高65535之间的整数 可变长度的字符 比较节省 低
varchar(M) M M为0
create table tab_char(
c1 ENUM('a','b','c')
);
insert into tab_char values('a');
create table tab_char2(
c1 SET('a', 'b', 'c')
);
insert into tab_char2 values('a,b');
较长的文本： text、 blob(较长的二进制数据)
日期和时间类型 字节 最小值 最大值
date 4 1000-01-01 9999-12-31
datetime 8 1000-01-01 00:00:00 9999-12-31 23:59:59
timestamp 4 19700101080001 2038年的某个时刻
time 3 -838:59:59 839:59:59
year 1 1901 2155
#timestamp会受时区影响，也受mysql版本影响， 随时区改变而改变
create table tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(), NOW());
#SHOW VARIABLES LIKE 'time_zone'; 显示当前时区
#SET time_zone='+9:00'; 修改时区
分类：
NOT NULL: 非空
DEFAULT: 默认值约束
PRIMARY KEY: 主键，唯一非空。
UNIQUE: 唯一，可以为NULL
CHECK: 检查约束，mysql不支持
FOREIGN KEY：从表加外键，用于表示两个表的关系，用于保证该字段的值必须来自于主表额关联列的值（最好不要用，使表变复杂）
#唯一约束只能有一行为null,如果出现两个null,就不是唯一了。
#只能有一个字段设置主键
#主键和唯一约束可以用两个字段合并为一个约束， 两个字段都相同时才不能插入（不推荐）
外键：
1，要求在从表设置外键关系
2，从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求。
3，主表的关联列必须是一个key(一般是主键或唯一)
4，插入数据时，先插入主表，再插入从表
5，删除数据时，先删除从表，再插入主表
6，实际开发中不用外键，因为4、5点实行起来太麻烦了。
添加约束的时机：
1，创建表时
2，修改表时
约束的添加分类：
1，列级约束， 外键约束不支持
2，表级约束， 非空和默认约束不支持
create table 表名(
字段名 类型名 列级约束,
字段名 类型名，
表级约束
);
#列级约束
create table stuinfo(
id INT PRIMARY KEY,
sutName VARCHAR(20) NOT NULL,
gender CHAR(1),
seat INT UNIQUE,
age INT DEFAULT 18,
majorId INT
);
SHOW INDEX FROM stuinfo; 查看表的索引
主键，外键，唯一键会自动添加索引。
表级约束, CONSTRAINT uq 这样可以不写，会默认一个名字和字段名相同
create table stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),
UNIQUE(seat, stuname),
CONSTRAINT ck CHECK(gender='男' OR gender='女'),
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
通用写法是，外键用表级约束，其他用列级约束
保证唯一性 是否允许为空
主键 是 否
唯一 是 是
添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
添加主键约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
添加唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo ADD UNIQUE(seat);
添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
删除主键约束
ALTER TABLE stuinfo DROP PRIMARY KEY;
删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
1标识列必须和主键或者唯一键搭配
2一个表至多有一个标识列
3标志里的类型只能是数值型
创建表时设置标识列
create table tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
SHOW VARIABLES LIKE '%auto_increment%';
SET suto_increment_increment=3; #设置自增长步长
修改表时修改标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
事务的ACID属性
1、原子性
2、一致性
3、隔离性
4、持久性
查看语句是否自动提交，默认on
SHOW VARIABLES LIKE 'autocommit';
关闭自动提交, 只对当前事务有效，而不是对所有事务有效
set autocommit=0;
步骤：
1，set autocommit=0;
start transaction;可选的
2，编写事务中的sql语句（select insert update delete）
语句1；
语句2；
....
3， 结束事务
commit;提交事务
rollback;回滚事务
案例
SET autocommit=0;
start transaction;
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='赵敏';
COMMIT;
savepoint 节点名，设置保存点
SET autocommit=0;
start transaction;
UPDATE account SET balance=500 WHERE username='张无忌';
SAVEPOINT a;
UPDATE account SET balance=1500 WHERE username='赵敏';
ROLLBACK TO a; #回滚到保存点。
1,脏读：对于两个事务t1、t2， t1读取了已经被t2更新但还没有被提交的字段之后，
若t2回滚，t1读取的内容就是临时且无效的。
2,不可重复读：对于两个事务t1、t2, t1读取了一个字段，然后t2更新了改字段之后没有提交，
t1再次读取同一个字段，值就不同了。
3,幻读：对于两个事务t1、t2， t1从一个表中读取了一个字段，然后t2在该表中插入了一些新的行没有提交，
之后，如果t1再次读取同一个表，就会多出几行。
查看隔离级别
select @@tx_isolation;
默认级别是：REPEATABLE-READ
修改当前连接的隔离级别
set session transaction isolation level read uncommitted;
修改数据库系统的全局的隔离级别：
set global transaction isolation level read committed;
隔离级别对比
隔离级别 描述
READ UNCOMMITTED(读未提交数据) 脏读、不可重复读、幻读都会出现
READ COMMITTED(读已提交数据) 解决了脏读问题
REPEATABLE READ(可重复读) 解决了脏读和不可重复读问题（默认）
SERIALIZABLE(串行化) 问题都解决了，但效率低
delete支持事务， truncate不支持事务。
含义： 虚拟表，和普通表一样的使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
行与列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存sql逻辑，不保存查询结果
使用场景：
1，多个地方用到同样的查询结果
2，该查询结果使用的sql语句较复杂
3, 保护数据，提高安全性
语法：
create view 视图名 as 查询语句;
创建
CREATE VIEW myv1 AS SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN jobs j ON j.job_id=e.job_id;
使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
方式一、
create or replace view 视图名 as 查询语句;
方式二：
alter view 视图名 as 查询语句;
drop view 视图名1，视图名2,...;
desc 视图名;
或 show create view 视图名;
插入，视图插入数据，原始表也会插入数据
insert into myv1 values('张飞', 'zf@qq.com');
修改， 视图修改数据，原始表也会修改数据
update myv1 set last_name='张无忌' where last_name='张飞';
删除，删除一条数据，原始表也会上传对应数据
delete from myv1 where last_name='张无忌';
具备下面特点的识别不能更新视图：
1，包含关键字：分组函数、distinct、group by、having、union、union all;
2, 常量视图
3，select中包含子查询
4，join
5, from一个不能更新的视图
6，where子句的子查询引用了from子句中的表。
分类：
系统变量：有系统提供的
全局变量：跨连接依然有效，不能跨重启，服务器每次启动将为所有的全局变量赋初始值。
会话变量：只有当前连接有效，
自定义变量：
用户变量：
局部变量：
SHOW GLOBAL VARIALBLES;
SHOW 【SESSION】 VARIALBLES;
show global|【session】 variables like '%char%';
select @@global|【session】【.】系统变量；
select @@global.autocommit;
select @@global.isolation;
select @@tx_isolation;
select @@session.tx_isolation;
set global|【session】系统变量名 = 值;
或 set @@global|【session】【.】系统变量名=值;
set @@global.autocommit=0;
set @@session.tx_isolation='read-uncommitted';
set @@tx_isolation='read-uncommitted';
set session tx_isolation='read-uncommitted';
set tx_isolation='read-uncommitted';
注意：
如果是全局级别，则需要加global,如果是会话级别，则需要加session，如果不写，默认session;
作用域：针对当前会话（连接）有效，等同于会话变量的作用域,可以放在任意地方，begin end里面和外面都可以；
赋值的操作符：=或:=
1、声明并初始化
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
2、赋值（更新用户变量的值）
方式一、通过set或select
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
案例：SET @name='john';
SET @name=100; #跟类型没有关系
SET @count=1;
方式二、通过select into
select 字段 into 变量名 from 表;
select count(*) into @count from employees;
3、使用（查看用户变量的值）
select @用户变量名;
作用域：仅仅在定义它的begin end 中有效, 应用在begin end的第一句话；
声明：
declare 变量名 类型;
或 declare 变量 类型 default 值;
赋值：
方式一、通过set或select
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
方式二、通过select into
select 字段 into 局部变量名 from 表;
使用：
select 局部变量名;
自定义用户变量和局部变量对比
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何位置 必须加@符号，不用限定类型
局部变量 BEGIN END中 只能在BEGIN END中，且为第一句话 一般不用加@符号（有一种需要加），需要限定类型
含义：一组预先编译好的sql语句的集合，理解成批处理语句
好处： 1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高效率
创建存储过程
create procedure 存储过程名（参数列表）
begin
存储过程体（一组合法的sql语句）
end
注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
举例： IN stuname VARCHAR(20)
参数模式：
in: 该参数可以作为输入，也就是该参数需要调用方传值
out: 该参数可以作为输出，也就是该参数可以作为返回值
inout: 该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，有可以返回值
2、如果存储过程体仅仅只有一句话，BEGIN END可以省略
3、存储过程体的每题sql语句的结尾要求必须加分号
4、 存储过程的结尾可以使用DELIMITER 重新设置
语法：
DELIMITER 结束标记
例如：DELIMITER $
调用存储过程
CALL 存储过程名（实参列表）;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username, password) VALUES('john1', '123'),('lisi', '456');
END $
DELIMITER ;
调用：
CALL myp1();
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.name=beautyName;
END $
DELIMITER ;
调用：
call myp2('liuyan');
DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN password VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result FROM admin WHERE admin.username=username AND admin.password=password;
SELECT result;
END $
DELIMITER ;
调用：
call myp3('liuyan', '123456');
DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName FROM boys bo INNER JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.name=beautyName;
END $
DELIMITER ;
调用：
SET @bName;
call myp4('liuyan', @bName);
SELECT @bName;
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
SELECT bo.boyName, bo.userCP INTO boyName, userCP
FROM boys bo INNER JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.name=beautyName;
END $
DELIMITER ;
调用：
SET @bName;
SET @userCP;
call myp5('liuyan', @bName, @userCP);
SELECT @bName, @userCP;
DELIMITER $
CREATE PROCEDURE myp6(INOUT a INT, INOUT b)
BEGIN
SET a=a2;
SET b=b2;
END $
DELIMITER ;
调用：
SET @m=10;
SET @n=20;
call myp6(@m, @n);
SELECT @m, @n;
DROP procedure 存储过程名;
show create procedure myp2;
含义：一组预先编译好的sql语句的集合，理解成批处理语句
好处： 1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高效率
函数和存储过程的区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数： 有且仅有1个返回， 适合做处理数据后返回一个结果。
创建语法
create function 函数名（参数列表） returens 返回类型
begin
函数体
end
注意：
1、参数列表包含两部分：
参数名 参数类型
2、函数体：肯定会有return语句，如果没有会报错；
如果return语句没有放在函数体的最后也不会报错，但不建议
3，函数体中就有一句话，return 值; 则可以省略begin end
4, 使用delimiter语句设置结束标记
调用语法
SELECT 函数名（参数列表）;
案例1，没有参数有返回
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c FROM employees;
RETURN c;
END $
DELIMITER ;
调用：
SELECT myf1();
案例2，有参数有返回
DELIMITER $
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS INT
BEGIN
set @sal=0; #用户变量
SELECT salary INTO @sal FROM employees WHERE last_name=empName;
RETURN @sal;
END $
DELIMITER ;
调用：
SELECT myf2('k_ing');
SHOW CREATE FUNCTION myf3;
DROP FUNCTION myf3;
顺序结构
分支结构
循环结构
1，if函数
功能：实现简单的分支
语法：if(表达式1， 表达式2， 表达式3）；表达式1成立，则执行表达式2，否则执行表达式3；
2，case 结构
3，if结构: 只能引用在BEGIN END 中；
语法：
if 条件1 then 语句1；
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;
案例：
DELIMITER $
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END$
DELIMITER;
分类：while、loop、repeat
循环控制：
iterate类似于java的conginue, 继续，结束本次循环，继续下一次循环；
leave 类似于 beak, 跳出， 结束当前所在的循环
语法：
【标签:】 while 循环条件 do
循环体;
end while 【标签】;
【标签:】 loop
循环体;
end loop 【标签】;
【标签:】 repeat
循环体;
until 结束循环的条件;
end repeat 【标签】;
案例：批量插入，根据次数插入到admin表中多条记录
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<= insertCount Do
INSERT INTO admin(username, 'password') VALUES (CONCAT('Rose' + i), '666');
SET i=i+1;
END WHILE;
END $
DELIMITER;
CALL pro_while1(100);
案例：批量插入，根据次数插入到admin表中多条记录, 如果次数>20则停止
DELIMITER $
CREATE PROCEDURE pro_while2(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<= insertCount Do
INSERT INTO admin(username, 'password') VALUES (CONCAT('Rose' + i), '666');
IF I>=20 THEN LEAVE a;
SET i=i+1;
END IF;
END WHILE a;
END $
DELIMITER;
CALL pro_while2(100);
案例：批量插入，根据次数插入到admin表中多条记录, 只插入偶数个
DELIMITER $
CREATE PROCEDURE pro_while3(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<= insertCount Do
SET i=i+1;
IF MOD(i, 2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username, 'password') VALUES (CONCAT('Rose' + i), '666');
END WHILE a;
END $
DELIMITER;
CALL pro_while3(100);
MyISAM和InnoDB数据库引擎对比
对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁 行锁，适合高并发的操作
缓存 只缓存索引，不缓存真实数据 索引和真实数据都缓存，内存对性能影响很大
表空间 小 大
关注点 性能 事务
默认安装 是 是
1、a、b的交叉 select * from a inner join b on a.key=b.key; 2、a的独有+a和b的交叉部分 select * from a left join b on a.key=b.key; 3、b的独有+a和b的交叉部分 select * from a right join b on a.key=b.key; 4、a的独有部分 select * from a left join b on a.key=b.key where b.key is NULL; 5、b的独有部分 select * from a right join b on a.key=b.key where a.key is NULL; 6、a的独有+b的独有+ab的交叉部分 select * from a left join b on a.key=b.key union select * from a right join b on a.key = b.key; 7、a的独有部分+b的独有部分 select * from a left join b on a.key=b.key where b.key is NULL union select * from a right join b on a.key=b.key where a.key is NULL;
案例：给user表创建name的单值索引
create index idx_user_name on user(name);
案例：给user表创建name、email的复合索引
create index idx_user_nameEmail on user(name,email);
drop index idx_user on user;
show index from user;
索引会影响where后面的查找和order by后面的排序；
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上；
数据量大且不经常改变的表才加索引。
一个表最好不要超过5个索引
使用的是B树结构
1、主键自动建立唯一索引；
2、频繁作为查询条件的字段应该创建索引
3、查询中与其它表关联的字段，外键关系建立索引
4、单键\组合索引应选择组合索引
5、排序字段应建索引
6、查询中统计或分组字段需建索引，分组必须先会排序
1、频繁更新的字段不适合创建索引
2、where条件里用不到的字段不创建索引
3、表记录太少不建索引
4、频繁增删改成的表不建索引
5、重复率太高的字段不适合建索引
mysql自带的性能分析器是：MySql Query Optimizer
explain 来分析查询性能；
explain select * from user;
有三种情况：
分类：
一般来说，得保证查询至少达到range级别，最好能达到ref级别；
查询涉及到的字段上若存在索引，则改索引被列出，但不一定被查询实际使用。
查询中若使用了覆盖索引，则该索引仅出现在key列表中。
案例：explain select col1, col2 from t1;#possible_keys为null，key为idx_col1_col2, 覆盖索引；
覆盖索引指select查询的字段刚好跟索引的字段吻合，顺序和个数都要一样，所以不要用select *;
key_len显示的值为索引字段最大可能的长度，并非实际使用长度，即key_len是根据表定义计算而得，
不是通过表内检索出的。
包括：
查询category_id为1且comments大于1的情况下，views最多的article_id.
explain select id,author_id from article where category_id=1 and comments>1 order by views limit 1;
create index idx_article_ccv on article(category_id, comments, views); 范围后面的索引会失效，所以这个不是最优方案。
create index idx_article_cv on article(category_id, views); 索引不会失效，两个索引都命中了，所以范围值不要配索引（最优方案）
案例1：
explain select * from book inner join class on book.card=class.card;
案例2：
explain select * from class left join book on class.card=book.card;
alter table book add index idx_b(card);
案例3：
explain select * from book right join class on book.card=class.card;
alter table book add index idx_b(card);
左右连接，索引都是相反加，如：左连接索引加在右表上，右连接索引加在左表上，
并且并且小表驱动大表，如：左连接小表放在左表上，右连接小表放在右表上。
案例：
explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
alter table phone add index idx_p(card);
alter table book add index idx_b(card);
案例：
alter table staffs add index idx_staffs_nameAgePos(name, age, pos);
1、全值匹配我最爱
带头大哥一定要有，
中间兄弟不能断，
explain select * from staffs where name='july';索引有效，定位到一个索引
explain select * from staffs where name='july' and age=18;索引有效，定位到两个索引
explain select * from staffs where name='july' and age=18 and pos='dev';索引有效，定位到三个索引
explain select * from staffs where pos='dev' and age=18 and name='july';索引有效，定位到三个索引，因为数据库会自动将顺序优化，ref都是const时才会调顺序
2、最佳左前缀法则
explain select * from staffs where age=18 and pos='dev';索引失效，全表扫描，索引的第一个字段name不能丢失。
explain select * from staffs where name='july' and pos='dev'; 只用到了name一个索引，pos没有定位到。
3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
explain select * from staffs where left(name, 4)='july';索引失效，不能用函数包裹索引字段
4、存储引擎不能使用索引中范围条件右边的列；
explain select * from staffs where name='july' and age>18 and pos='dev';前两个字段索引有效,pos索引失效，范围效率低
5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *;
explain select name,age,pos from staffs where name='july' and age=18 and pos='dev';索引有效，并且extra用到了Using index,所有效率更高。
explain select name,age,pos from staffs where name='july' and age>18 and pos='dev';虽然pos索引没用上，但是extra用上了Using index, type是ref而不是4中的
range,级别和效率会更高。
explain select name from staffs where name='july' and age=18 and pos='dev';索引有效，并且extra也会用到了Using index,所有效率更高。
6、mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
explain select * from staffs where name!='july';索引失效
explain select * from staffs where name<>'july';索引失效
7、is null, is not null也无法使用索引
explain select * from staffs where name is null;索引失效
explain select * from staffs where name is not null;索引失效
8、like以通配符开头（'%abc...'）mysql索引会失效变成全表扫描操作
explain select * from staffs where name like '%july%';索引失效
explain select * from staffs where name like '%july';索引失效
explain select * from staffs where name like 'july%';索引有效。 type是range
explain select * from staffs where name like 'july%' and age= 18;name和age的索引都有效
explain select * from staffs where name like 'july%kk%' and age= 18;name和age的索引都有效
案例：解决like '%字符串%'时索引失效的方法？（使用覆盖索引解决）
create index idx_user_nameAge on tbl_user(name, age);
explain select name, age from staffs where name like '%july%'; 索引有效
explain select id from staffs where name like '%july%'; 索引有效, 因为id是主键，也是索引
explain select name from staffs where name like '%july%'; 索引有效
explain select age from staffs where name like '%july%'; 索引有效
explain select id, name from staffs where name like '%july%'; 索引有效
explain select id, name, age from staffs where name like '%july%'; 索引有效
explain select name, age from staffs where name like '%july%'; 索引有效
explain select * from staffs where name like '%july%'; 索引失效
explain select id, name, age,email from staffs where name like '%july%'; 索引失效
9、字符串不加单引号索引失效，会隐式类型转换
explain select * from staffs where name='2000'; 索引有效
explain select * from staffs where name=2000; 索引失效
10、少用or,用她来连接时会索引失效。
explain select * from staffs where name='zhangsan' or name='lisi'; 索引失效
案例：create index idx_test_c1234 on test(c1,c2,d3,c4);
explain select * from test where c1='a1' and c2='a2' and c3='a3' and c4='a4';用到4个索引
explain select * from test where c4='a4' and c2='a2' and c3='a3' and c1='a1';用到4个索引,顺序自动优化
explain select * from test where c1='a1' and c2='a2' and c3>'a3' and c4='a4';用到3个索引
explain select * from test where c1='a1' and c2='a2' and c4>'a4' and c3='a3';用到4个索引,顺序自动优化
explain select * from test where c1='a1' and c2='a2' and c4='a4' orer by c3;查找用到2个索引，排序用了一个索引c3
explain select * from test where c1='a1' and c2='a2' orer by c3;查找也是用到2个索引，排序用了一个索引c3
explain select * from test where c1='a1' and c2='a2' orer by c4;查找也是用到2个索引，但是extra出现了Using filesort，效率会降低。
explain select * from test where c1='a1' and c5='a5' orer by c2,c3;查找用到一个索引，排序用到两个索引，无filesort
explain select * from test where c1='a1' and c5='a5' orer by c3,c2;查找用到一个索引，出现了filesort,无排序索引。
explain select * from test where c1='a1' and c2='c2' and c5='a5' orer by c3,c2;查找用到两个个索引，排序用到一个索引，无filesort,原因是c2已经在查找中用到了常量，所以不会再去排序，所以排序索引只用到c3
explain select * from test where c1='a1' and c2='a2' order by c2,c3;查找用到两个索引，排序用到两个索引
explain select * from test where c1='a1' and c2='a2' and c5='a5' order by c2,c3;查找用到两个索引，排序用到两个索引
explain select * from test where c1='a1' and c4='a4' group by c2,c3;查找索引用到两个，分组索引用到两个，无filesort
explain select * from test where c1='a1' and c4='a4' group by c3,c2;查找索引用到两个，分组没有索引，出现filesort和Using temporar;
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
like百分写最右，覆盖索引不写星；
不等空值还有or, 索引失效要少用；
var引号不能丢， sql高级也不难；
永远小表驱动大表。
select * from t_emp e where e.deptId in (select id from t_dept d);
select * from t_emp e where exists (select 1 from t_dept d where d.id = e.dept_id);
order by 子句，尽量使用index方式排序，避免使用filesort方式排序。
create index idx_A_ageBirth on tablA(age, birth);
explain select * from tablA where age>20 order by age; 有排序索引。
explain select * from tablA where age>20 order by age,birth; 有两个排序索引。
explain select * from tablA where age>20 order by birth;无排序索引，出现filesort;(范围查询)
explain select * from tablA where age>20 order by birth,age;无排序索引,出现filesort
explain select * from tablA order by birth;无排序索引,出现filesort
explain select * from tablA where birth>'2021-01-01' order by birth;无排序索引,出现filesort
explain select * from tablA where birth>'2021-01-01' order by age;有排序索引，查询也有索引
explain select * from tablA order by age asc,birth desc; 无排序索引,出现filesort，需同升或同降
-查看是否开启：show variables like '%slow_query_log%';
-开启慢查询日志：set global slow_query_log=1;只对当前数据库生效，如果mysql重启后会失效。
如果要永久有效，改配置文件my.cnf。
默认存储文件名：主机名-slow.log;
-哪些查询会记录到慢查询日志中呢？
先查询时长：show variables like 'long_query_time%';超过这个时长会存储
设置慢的阙值时间：set global long_query_time=3; //超过3秒会存储
修改了阙值时间，再查询，数值没有变? 原因： 需要重新连接或新开一个会话才能看到修改值。
然后用命令：show global variables like 'long_query_time%'; 来查询。
-select sleep(4);
-cd /var/lib/mysql
-打开文件 主机名-slow.log日志文件，这个文件名可以在配置文件中设置。
-show global status like '%Slow_queries%'; 查询有几条命令比较慢。
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/cyh-slow.log
long_query_time=3;
log_output=FILE;
-mysqldumpslow --help
-s:排序方式
c:访问次数
I:锁定时间
r:返回记录
t:查询时间
aI:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:表示返回前面多少条的数据
g:后面搭配一个正则表达式匹配模式，大小写不敏感；
mysqldumpslow -s r -t 10 /var/lib/mysql/cyh-slow.log
mysqldumpslow -s c -t 10 /var/lib/mysql/cyh-slow.log
mysqldumpslow -s t -t 10 /var/lib/mysql/cyh-slow.log
创建函数如果报错：This function has none of DETERMINISTIC...
则：show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
-存储过程
delimiter $$
create function rand_sting(n INT) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare retrun_sr varchar(255) default '';
declare i INT default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_st;
end
dellimiter ;
是分析语句执行的资源消耗情况。
默认情况下关闭，并保存最近15次的运行结果
-show variables like 'profiling';
-set profiling=on;
-select * from emp group by id%10 limit 150000;
-select * from emp group by id%10 limit 5;
-show profiles; 会找出最新的15条sql，每条sql执行花费的时间
-show profile cpu,block io for query 1; 1表示上一步前面的问题sql数字号码， 会找出每一步花费的时间。
-与cpu,block相似的可以写：all:所有开销
block io: io开销
context switches：上下文切换开销
cpu: cpu开销
ipc:显示发送和接收开销
memory:内存相关开销
page faults:显示页面错误相关开销
source: 显示source相关开销
swaps: 显示交换次数相关开销
-出现下面情况说明有问题：converting HEAP to MyISAM 查询结果太大，内存不够用
Create tmp table 创建了临时表
Copying to tmp table on disk 把内存中的临时表复制到磁盘,危险！！！
loced 上了锁
#全局查询日志：只在测试环境用。
-set global geneal_log=1;
-set global log_output='TABLE';
-select * from mysql.general_log;
分类：
从对数据操作的类型分：读锁和写锁
从对数据操作的粒度分：表锁（常用于myisam引擎）和行锁（常用于InnoDB引擎）
手动增加表锁：
lock table 表名字 read(write),表名字2 read(write),其他;
show open tables;查看锁的情况。
lock table emp read, book write;
unlock tables;释放锁
-会话1加了表1的读锁，则会话1可以读表1，不能读表2，不能写表1；
会话2可以读表1，也可以读表2，可以写表2， 但写表1会等待，会话1解锁后，写表1才会执行完。
-会话1加了表1的写锁，则会话1可以读表1，可以写表1，不能读表2，
会话2可以读表2，读表1会等待，会话1解锁后，读表1才会执行完。
----如果读不行，写就更不行。
show status like 'table%';
table_locks_imediate:产生表级锁的次数
table_locks_waited:出现表级锁争用而发生等待的次数，此值高则说明存在较严重的表级锁竞争情况。
InnoDB和MyISAM的最大不同：一是支持事务，二是采用了行级锁；
-会话1和会话2：set autocommit=0;
会话1更新表1；
会话1查询表1，结果是改变了的新数据。
会话2查询表1，结果还是旧的数据。
会话1提交
会话2查询表1，结果还是旧数据。
会话2提交
会话2查询表1，结果是新数据。
-会话1更新表1值4002；
会话1查询表1，新数据4002；
会话2更新表相同字段4003，阻塞；
会话1，commit提交
会话2自动更新
会话2，commit提交
会话1查询表1，结果4003
会话2查询表2，结果4003
-会话1更新表1值4005；
会话2更新表1的另外一行数据9001
会话1和会话2都commit.
相互之间不影响。
begin;
select * from test where a=8 for update;
commit;
在commit之前，其他会话就无法操作8这一行了，会阻塞，commit后才会自动执行。
show status like 'innodb_row_lock%';
Innodb_row_lock_waits 阻塞的次数。
Innodb_row_lock_curent_waits:当前正在等待的锁定的数量
Innodb_row_lock_time等待的总时长
Innodb_row_lock_time_avg:等待平均时长
案例：求所有人物对应的掌门人名称:
方法一：NO4
select c.name, ab.name ceoname from t_emp c left join
(select b.id, a.name from t_emp a inner join t_dept d on b.ceo=a.id)ab
on c.deptId=ab.id;
方法二：NO3
select ab.name, c.name ceoname from
(select a.name, b.ceo from t_emp a
left join t_empt b on a.deptId=b.id)ab
left join t_emp c on ab.ceo=c.id;
方法三：NO1
select a.name, c.name ceoname from t_emp a
left join t_dept b on a.deptId=b.id
left join t_emp c on b.ceo=c.id;
方法四：NO2
select a.name, (select c.name from t_emp c where c.id=b.ceo)ceoname
from t_emp a
left join t_dept b on a.deptId=b.id;
CREATE TABLE dept (
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
ceo INT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE emp (
id INT(11) NOT NULL AUTO_INCREMENT,
empno INT NOT NULL,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1;
DELIMITER $$
CREATE FUNCTION rand_sting(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE retrun_sr VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
return return_st;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num + RAND() * (to_num-from_num+1));
RETURN i;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE insert_emp(START INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit=0;
REPEAT
SET i=i+1;
INSERT INTO emp(empno, NAME, age, deptId) VALUES((START+i), rand_string(6), rand_num(30,50),rand_num(1,10000));
UNTIL i=max_num
END REPEAT;
COMMIT;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE insert_dept(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit=0;
REPEAT
SET i=i+1;
INSERT INTO dept(deptname, address, ceo) VALUES(rand_string(8), rand_string(10),rand_num(1,500000));
UNTIL i=max_num
END REPEAT;
COMMIT;
END $$
DELIMITER ;
CALL insert_dept(10000);
CALL insert_emp(100000, 500000);
SELECT index_name FROM infromation_schema.STATISTICS WHERE table_name='t_emp' AND table_schema='mydb'
AND index_name <> 'PRIMARY' AND seq_in_index=1;
DELIMITER $$
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200), tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname
AND table_name=tablename AND seq_in_index=1 AND index_name <> 'PRIMARY';
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2;
OPEN _cur;
FETCH _cur INTO _index;
WHILE index<> '' DO
SET @str=CONCAT('drop index ', _index, ' on ', tablename);
PRIPARE sql_str FROM @str;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END $$
DELIMITER ;
执行删除
CALL proc_drop_index('dbname', 'tablename');
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30; //每次查询都不会走缓存
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。