show databases;

use 库名;

show tables;

show tables from 库名;

desc 表名;

show columns from 表名;

show index from 表名;

# 查看指定表的状态信息
show table status from 表名/数据库;

# 查看主从复制中的主机状态
show master status;

# 查看主从复制中从机状态
show slave status;

# 方式一，在mysql服务端查看：
select version();
# 方式二，在cmd窗口查看：
mysql --version
或
mysql --V

# 比如想添加用户：user，密码：123456，并授予select权限
# 只需要在user表的指定列添加内容即可。
insert into mysql.user(host,user,password，select_priv)
	values('localhost','user',password('123456'),'Y');
flush privileges;
/* 
其中host列表示限制登陆的主机，localhost表示只能当前主机登陆，
可以指定ip，使用%表示任意主机。
MySQL5.7以后，password改名为authentication_string。
密码需要使用password()函数进行加密，MySQL8.0.11移除了这个函数，可以使用MD5()代替。
可以根据需求授予其他权限，只需要相应列赋值为'Y'即可。
添加完后，使用flush privileges语句刷新授权表，使之立即生效，
否则需要重启MySQL服务才生效。
*/

# 比如给指定数据库比如student库，添加用户user，密码123,授予select权限
grant select   # 指定授予的权限
on student.*   # 指定数据库和表，*表示所有，这里表示student库所有表
to 'user'@'localhost'  # 指定用户名和登陆主机，localhost根据需求填写
indentified by '123';   # 指定登录密码。

select 字段,...				# 7
from 表1 [别名]				# 1
[连接类型 join 表2]...			# 2
[on 连接条件]				# 3
[where 筛选条件]			# 4
[group by 分组字段]			# 5
[having 分组后的筛选条件]		# 6
[order by 排序的字段或表达式]		# 8
[limit 偏移量(起始条目索引),条数]; 	# 9

SELECT 查询内容 
[FROM 表名];

#1.查询单个字段
SELECT last_name FROM employees;

#2.查询多个字段
SELECT last_name,salary,email FROM employees;

#3.查询所有字段
SELECT 
    `employee_id`,
    `first_name`,
    `last_name`,
    `phone_number`,
    `last_name`,
    `job_id`,
    `phone_number`,
    `job_id`,
    `salary`,
    `commission_pct`,
    `manager_id`,
    `department_id`,
    `hiredate` 
FROM
    employees ;
#方式二，*号表示所有字段：  
SELECT * FROM employees;

#4.查询常量
SELECT 100;

#5.查询函数
SELECT VERSION();

#6.查询表达式
SELECT 100%98;

#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

select 100+90;  # 结果为190
select '123'+90; # 结果为213
select 'john'+90; # 结果为90
select null+10;  # 结果为null

select 要查询的字段|表达式|常量值|函数
from 表
where 筛选条件;  # where后面加筛选条件

#案例1：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;

#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT 
	last_name,
	department_id
FROM employees
WHERE department_id<>90;

#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
	last_name,
	salary,
	commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;

#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * FROM employees
WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

#查询员工名中包含字符a的员工信息
SELECT * FROM employees
WHERE last_name LIKE '%a%';

#查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees
WHERE last_name LIKE '_#_%' ESCAPE '#'; # 自定义#作为转义字符

#查询员工编号在100到120之间的员工信息
SELECT * FROM employees
WHERE employee_id BETWEEN 100 AND 120;
#等价于：
SELECT * FROM employees
WHERE employee_id >= 100 AND employee_id<=120;

#查询工种编号是IT_PROG、AD_VP、AD_PRES中其中一个的所有员工的姓名和工种编号
SELECT last_name, job_id FROM employees
WHERE job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
#等价于
SELECT last_name, job_id FROM employees
WHERE job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';

#查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct 
FROM employees
WHERE commission_pct IS NULL;

#案例1：查询没有奖金的员工名和奖金率
SELECT last_name, commission_pct
FROM employees
WHERE commission_pct <=>NULL;

#案例2：查询工资为12000的员工名和工资
SELECT last_name, salary
FROM employees
WHERE salary <=> 12000;

select 查询列表
from 表名
[where 筛选条件]
order by 排序的字段|表达式|函数|别名 [asc|desc];  # 使用order by排序

#1、按单个字段排序
SELECT * FROM employees ORDER BY salary DESC;

#2、添加筛选条件再排序
#查询部门编号>=90的员工信息，并按员工编号降序
SELECT * FROM employees WHERE department_id>=90
ORDER BY employee_id DESC;

#3、按表达式排序
#查询员工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#4、按别名排序
#查询员工信息 按年薪升序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;

#5、按函数排序
#查询员工名，并且按名字的长度降序
SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6、按多个字段排序
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT * FROM employees
ORDER BY salary DESC,employee_id ASC;

select 函数名(实参列表) [from 表名...]

#用法1：
case 要判断的字段或表达式
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
endl;
    
#用法2：
case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end;

# if
# 例一
SELECT IF(10<5,'大','小');

# 例2
SELECT 
	last_name,
	commission_pct,
	IF(commission_pct IS NULL,'没奖金','有奖金') 备注
FROM employees;

# case
# 例1
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

# 例2
SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
# 与distinct搭配使用
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;  # 57,107

# 和分组函数一同查询的字段有限制
# 下面这种，employee_id是无意义的，只有group by后的字段才能和分组函数一起查询
SELECT AVG(salary),employee_id  FROM employees;

select 查询列表
from 表
[where 筛选条件]
group by 分组的字段  # 使用group by进行分组
[having 条件语句];

/*
MySQL 5.7版本，出现以下结果
*/
SELECT salary FROM employees 
WHERE salary>15000; # 输出正确结果

SELECT last_name FROM employees
WHERE salary>15000; # 输出正确结果
#－－－－－－－－－－－－－－－－－－
SELECT salary FROM employees 
HAVING salary>15000; # 输出正确结果

#如果having后的字段，没在select中，会报错
SELECT last_name FROM employees 
HAVING salary>15000; # 显示语法错误，原因是‘having clause’中没有salary列

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id  #和分组函数同时查询的字段，是用于分组的字段
FROM employees
GROUP BY job_id;

#案例2：查询邮箱中包含a字符的每个部门的最高工资
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#案例3：查询哪个部门的员工个数>5
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id  # 先查询每个部门员工个数
HAVING COUNT(*)>5;  # 然后选出个数>5的

#案例4：查询每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m;

#按多个字段分组
#案例5：查询每个查询每个工种每个部门的平均工资
SELECT department_id, job_id,AVG(salary) FROM employees
GROUP BY department_id,job_id; # 顺序可以颠倒
# 这两种写法等价，每个A，每个B和每个B，每个A结果相同。
SELECT department_id, job_id,AVG(salary) FROM employees
GROUP BY job_id,department_id;

# SQL 92语法
select 查询列表
from 表1 别名，表2 别名
where 表1.字段= 表2.字段   # 连接条件 
[and 筛选条件]
其他结构...

# SQL 99语法
select 查询列表
from 表1 别名
[inner] join 表2 别名
on 连接条件
其他结构...
# SQL 99语法将筛选条件和连接条件分离，便于阅读

# SQL 92
select 查询列表
from 表1 别名，表2, 别名...
where 非等值连接条件
[and 筛选条件]
其他结构...

# SQL 99
select 查询列表
from 表1 别名
[inner] join 表2 别名
on 非等值连接条件
其他结构...

# 自连接是指同一张表自己和自己连接。
#将同一张表看作两个表，起两个别名。
# SQL 92
select 查询列表
from 表 别名1,表 别名2
where 连接条件
[and 筛选条件]
其他结构...

# SQL 99语法
select 查询列表
from 表 别名1
[inner] join 表 别名2
on 连接条件
其他结构...

#案例1：查询有奖金的每个部门的部门名、部门的领导编号，以及该部门的最低工资
# SQL 92，等值连接
SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;

# SQL 99，等值连接
SELECT d.department_name,d.manager_id,MIN(salary)
FROM employees e 
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`commission_pct` IS NOT NULL
GROUP BY e.`department_id`;

#案例2：查询员工的工资和工资级别
# SQL 92，非等值连接
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

# SQL 99，非等值连接
SELECT salary,grade_level
FROM employees e
JOIN job_grades g 
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#案例3：查询员工名和上级的名称
# SQL 92，自连接
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

# SQL 99，自连接
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;

# 语法：
select 查询内容
from 表1
left [outer]|right [outer]|full [outer]|cross join 表2 
on 连接条件
其他结构...;

#outer可以省略

#案例1：查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
 
#右外,调换两个表的顺序后使用右外连接，和上面写法等价 
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

# 嵌套子查询
# 查询工资大于平均工资的员工信息
/*
平均工资是子查询，其不依赖于外查询
*/
SELECT * FROM employees
WHERE salary>( 
	SELECT AVG(salary) FROM employees
);

# 相关子查询
# 查询每个部门的员工数量和部门信息
/*
可以使用子查询的方式，也可以使用外连接+分组查询的方式。
查询员工数量时，需要将子查询和父查询根据部门id关联起来，
先统计一个部门的数量，然后继续统计下一个部门数量,
子查询和父查询二者交替执行
*/
SELECT d.*,(
	SELECT COUNT(*) FROM employees e
	WHERE e.`department_id`=d.`department_id`
	) AS 'NUMBER'
FROM departments d;

# exist也是相关子查询。能用exist的，也能使用in代替
# 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *   
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

# -------------用在where和having后使用子查询------------
#案例1：查询最低工资大于50号部门最低工资的 部门id 和 其最低工资
/*思路：
1.先查出50号部门的最低工资
2.然后查出每个部门的最低工资，
3.根据前两步的结果筛选出最低工资大于50号部门最低工资的部门信息
*/
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
);

#案例2：查询其它工种中，比‘IT_PROG’工种任一工资低的员工的 员工号、工种和薪资
/*思路：
1.查询‘IT_PROG’部门的任一工资
2.查询员工信息，salary<1的结果
*/
SELECT employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

# 小于any，只要比最大值小就可以，因此可以不用any
SELECT employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary) 
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

# -------------用在select后使用子查询------------
#案例：查询每个部门的员工个数和部门信息
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;
 
# -------------用在from后使用子查询------------
# from后面的子查询结果作为一张表，必须起别名
# 案例：查询每个部门的平均工资的工资等级
/*思路：
1.使用子查询，查询每个部门的平均工资
2.根据平均工资查找工资等级
*/
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后使用子查询------------
# 查询有员工的部门名
# 使用 in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
);
# 使用外连接
SELECT DISTINCT department_name
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NOT NULL;

# 使用exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

select 查询内容
from 表
[where 条件]
[group by 分组字段]
[having 条件]
[order by 排序字段]
limit [偏移量,] 条目数;  # 使用limit语句进行分页

select * from 表
limit (page-1)*sizePage,sizePage;

#案例1：查询第11条到第25条信息
SELECT * FROM  employees LIMIT 10,15;

#案例2：查询有奖金的员工中，工资较高的前10名员工信息
SELECT * FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10;

# 1.查询工资最低的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary = ( # 先使用子查询查找最低工资
	SELECT MIN(salary) FROM employees
);

# 2.查询平均工资最低的部门信息
/*思路：
1.先查询每个部门的平均工资
2.筛选出平均工资中最低的部门
3.根据上一步的结果，查询出部门信息
*/
# 方式一：
SELECT * FROM departments d # d.根据id，查询部门信息
WHERE d.`department_id` = 
(
	SELECT department_id # c.找出平均工资为最低平均工资的部门id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=
    ( 
		SELECT MIN(ag.a_s) # b.找出最低的平均工资
		FROM( # a.查询出每个部门的平均工资和id
			SELECT AVG(salary) a_s,department_id 
            FROM employees
			GROUP BY department_id
			) AS ag
	)
);
# 方式二，使用limit，求出平均工资以后进行排序，就可以直接选出部门id
SELECT * FROM departments d
WHERE d.`department_id`= 
(
	SELECT department_id 
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1   # 递增排序中的第一条就是要查询的部门
);

# 3.各个部门中 最高工资最低的部门的最低工资是多少
SELECT MIN(salary) FROM employees
WHERE department_id = ( # 子查询将排序和分页组合找出最值
	SELECT department_id 
    FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary) 
	LIMIT 1
);

# 4.查询平均工资最高的部门的manager信息
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id=
( # b,根据部门id找出管理者id
	SELECT manager_id FROM departments
	WHERE department_id = 
    (  # a,找出平均工资最高的部门id
		SELECT department_id FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) DESC
		LIMIT 1
	)
);

# 使用union将多个查询语句合并
select xxx
union [all]
select xxx
union [all]
.....
select xxx

insert into 表名(字段1,字段2,...) values(值1,值2,...);

insert into 表名
set 列名1=值,列名2=值,...;

update 表名 set 字段=新值,字段=新值...
[where 条件];

# 案例
# 将90号部门的员工工资修改为15000
update employees
set salary=15000
where department_id90;

# SQL 92语法
update 表1 别名1,表2 别名2
set 字段=新值，字段=新值...
where 连接条件
[and 筛选条件];

# SQL 99语法，唯一不同的就是连接表的方式不同。其余相同
update 表1 别名
[inner]|left [outer]|right [outer] join 表2 别名
on 连接条件
set 列=值,...
[where 筛选条件];

delete from 表名 
[where 筛选条件]
[limit 条目数]

# 同样地，也分为两种标准的语句，仅仅是连接表的时候不同，其余语句完全相同。
# 以SQL 92为例
delete 别名1，别名2
from 表1 别名1，表2 别名2
where 连接条件
[and 筛选条件];

truncate table 表名;

create database [if not exists] 库名;

# 语法：
create table [if not exists] 表名(
	列名 列的类型[(长度) 约束],
	列名 列的类型[(长度) 约束],
	列名 列的类型[(长度) 约束],
	...
	列名 列的类型[(长度) 约束]
);

# 例：创建名为stuinfo的表，包含的列和类型如下：
CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT,
	stuName VARCHAR(20),  # varchar类型必须有长度
	gender CHAR,
	bornDate DATETIME
);

# 仅复制表的结构
create table 新表名 like 旧表

# 复制表的字段和数据
create table 新表名
select 字段列表 from 旧表 [where 筛选条件];

#仅仅复制某些字段
create table 新表名
select 字段列表 from 旧表 where false;

#已废弃.现在需要去文件路径中重命名
RENAME DATABASE books TO 新库名; 
# 更数据库字符集
ALTER DATABASE books CHARACTER SET gbk;

# 添加新的列，必须指定类型
# 新列默认在表中的位置为最后，可以使用first或after参数指定位置
alter table 表名 add column 列名 类型 [约束] [first|after 列名];

# 删除某列（无法使用if exist进行判断）
alter table 表名 drop column 列名;

# 修改列名
alter table 表名 change column 旧列名 新列名 类型;

# 修改列的类型或约束
alter table 表名 modify column 列名 类型 [约束];

# 格式：
alter table 表名 rename [to] 新表名;
# 案例：
ALTER TABLE author RENAME TO book_author;

drop database [if exists] 库名;

drop table [if exists] 表名;

#方式一：级联删除，删除主表数据时，将从表中相关的记录(整行)也删除
#添加外键时，在最后添加 on delete cascade。例如：
ALTER TABLE stuinfo 
ADD CONSTRAINT fk_stuinfo_major 
FOREIGN KEY(majorid) 
REFERENCES major(id) ON DELETE CASCADE; 

# 方式二：级联置空，删除主表数据时，将从表中相关的记录中对应的字段置为null
#添加外键时，在最后添加 on delete set null。例如：
ALTER TABLE stuinfo 
ADD CONSTRAINT fk_stuinfo_major 
FOREIGN KEY(majorid) 
REFERENCES major(id) ON DELETE SET NULL; 

# 例如。
create table 表名(
    字段名 字段类型 not null,  # 非空
    字段名 字段类型 primary key,  # 主键
    字段名 字段类型 unique,  # 唯一键
    字段名 字段类型 default,  # 默认
    ...
);

# 语法：
[constraint 约束别名] 约束(字段名);
# 例如：
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
    
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查（MySQL不支持）
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

SHOW INDEX FROM 表名;

#1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

#2、添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键相关];

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一键，使用删除索引的语法
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键，需要先将对应的索引删除
ALTER TABLE stuinfo DROP INDEX fk_stuinfo_major;
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

# 创建表时设置标识列
CREATE TABLE tab_identity(
	id INT,
	NAME FLOAT UNIQUE AUTO_INCREMENT,
	seat INT 
);

# 修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

#三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;

set autocommit=0; # 将自动提交模式关闭
执行语句

# 方式一：
start transaction;

#方式二：
begin;

#开启事务，三种均可
#SET autocommit=0;
#START TRANSACTION;
begin;

#事务执行语句
UPDATE employees SET salary = 17200 WHERE username='K_ing';
UPDATE employees SET salary = 10000 WHERE username='Marry';

#结束事务，要么提交，要么回滚
ROLLBACK;
#commit;

# 设置全局隔离级别
set global transaction isolation level 隔离级别名;

# 设置当前会话连接的隔离级别
set [session] transaction isolation level 隔离级别名;

#查看当前会话隔离级别
select @@tx_isolation;
#查看全局隔离级别
select @@global.tx_isolation;

create [or replace] view 视图名 as 查询语句;
# 使用or replace还可以用于视图的修改

# 使用视图，查询各部门的平均工资级别

# 1.创建视图查看每个部门的平均工资
CREATE VIEW myview AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

# 2.使用创建的视图查询
SELECT myview.`ag`,g.grade_level
FROM myview
JOIN job_grades g
ON myview.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

drop view 视图名,视图名,...;  # 可以一次删除多个视图

#案例：删除myv1，myv2，myv3三个视图
DROP VIEW myv1,myv2,myv3;

#方式一：
DESC 视图名;

#方式二：
SHOW CREATE VIEW 视图名;

#方式一：可用于修改和创建视图
create or replace view  视图名
as 查询语句;

# 方式二：
alter view 视图名
as 查询语句;

# 查看所有全局变量
SHOW GLOBAL VARIABLES;

# 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE 'xxx';

# 查看指定的系统变量的值，
SELECT @@global.变量名

# 以autocommit为例
# 方式一：
SET @@global.变量名=值;
# 方式二：
SET GLOBAL 变量名=值;

# 查看所有会话变量
SHOW [SESSION] VARIABLES;

# 查看满足条件的部分会话变量
SHOW [SESSION] VARIABLES LIKE 'xxx';

# 查看指定的会话变量的值
#SELECT @@[session.]变量名;
# 例：
SELECT @@autocommit;
# 或写成
SELECT @@session.autocommit;

SET @@session.变量名=值;
SET [SESSION] 变量名=值;

#方式一
SET @变量名=值;

#方式二
SET @变量名:=值;

#方式三
SELECT @变量名:=值;

# 方式一，和初始化时相同：
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

# 方式二：
SELECT 字段 INTO @变量名 FROM 表;

SELECT @变量名;

DECLARE 变量名 类型 [DEFAULT 值];

#方式一，同样是三种：
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;

#方式二：
SELECT 字段 INTO 局部变量名 FROM 表;

SELECT 局部变量名;

create procedure 存储过程名(参数模式 参数名 参数类型,...)
begin
	存储过程体(一条或多条合法的SQL语句);
end 结束符

call 存储结构名(实参);
# 如果是in模式的参数，可以直接传入值
# 如果是out或inout模式的参数，必须预先定义变量，然后作为参数传入。

# 每次只能删除一个，不能删除多个
DROP PROCEDURE [IF EXISTS] 存储过程名;

SHOW CREATE PROCEDURE 存储过程名;

/* 声明存储结构
*/
DELIMITER @@  # 设置结束符为@@
# 确保存储过程能正确创建，如果已经存在，删除原有的
DROP PROCEDURE IF EXISTS `myp`@@
CREATE PROCEDURE myp(IN id INT,
                     OUT managerId INT,
                     OUT managerName VARCHAR(20))
BEGIN
	# 将查询的结果值赋给输出变量，这里参数为局部变量
	SELECT m.`employee_id`,m.`last_name` INTO managerId,managerName
	FROM employees m 
	INNER JOIN employees e
	ON m.`employee_id`=e.`manager_id`
	WHERE e.`employee_id`=id;
END @@  # end后面要加设置的结束符
DELIMITER ;  # 将结束符改回分号

/*调用存储结构
in模式参数直接传入值，
out模式的参数要先声明，或者传入的时候指定名字
*/
CALL myp (110,@mid,@mname);  # 调用存储结构，传入参数
SELECT @mid,@mname;  # 查看结果值

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体;
	RETURN xx;  #必须有返回值
END 结束符

SELECT 函数名(参数列表);  # 存储过程用的是CALL

# 同样每次只能删除一个
DROP FUNCTION [IF EXISTS] 函数名; 

SHOW CREATE FUNCTION 函数名;

/*创建函数
*/
DELIMITER @@   # 先将分隔符设置为@@
DROP FUNCTION IF EXISTS `myf`@@
CREATE FUNCTION myf(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	#声明一个局部变量，用于接收查询结果，并返回
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;  # 返回结果值
END @@
DELIMITER ;  # 将分隔符重新设置为分号

/*调用函数
*/
SELECT myf('IT');

if 条件1 then 语句1;
elseif 条件1 then 语句2;
...
else 语句n;
end if;

# 方式一：类似于switch
case 表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end case;

# 方式二：类似于多重if
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end case;

# 方式一：使用if结构
DELIMITER @@   # 先将分隔符设置为@@
DROP FUNCTION IF EXISTS `myf_if`@@
CREATE FUNCTION myf_if(score FLOAT) RETURNS CHAR
BEGIN
	# 设置局部变量保存结果
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE ch='D';
	END IF;
	RETURN ch;
END @@
DELIMITER ;
#调用函数
SELECT myf_if(87);

# 方式二：使用case结构
DELIMITER @@   # 先将分隔符设置为@@
DROP FUNCTION IF EXISTS `myf_case`@@
CREATE FUNCTION myf_case(score FLOAT) RETURNS CHAR
BEGIN 
	#设置局部变量保存结果
	DECLARE ch CHAR DEFAULT 'A';
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	RETURN ch;
END @@
DELIMITER ;
#调用函数
SELECT myf_case(56);

[标签:] WHILE 循环条件 DO
	循环体
END WHILE [标签];

[标签:] loop
	循环体;
end loop [标签];

[标签:] repeat
	循环体;
until 结束循环条件
end repeat [标签];

# 使用存储结构和循环结构，实现批量插入，要求只插入偶数次的记录
DELIMITER @@   # 先将分隔符设置为@@
DROP PROCEDURE IF EXISTS test_while@@
CREATE PROCEDURE test_while(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('xiaohua',i),'0000');
	END WHILE a;
END @@
DELIMITER ;

# 传入100，只会插入偶数时的记录
CALL test_while1(100);