-- 创建数据库语法

CREATE DATABASE [IF NOT EXISTS] 数据库名 CHARACTER SET 字符集 COLLATE 校对规则;

-- 查看、删除数据库

-- 显示数据库语句：
SHOW DATABASHES

-- 显示数据库创建语句：
SHOW CREATE DATABASE db_name

-- 删除数据库语句：
DROP DATABASE [IF EXISTS] db_name

-- 案例

-- 创建数据库
CREATE DATABASE db_01 CHARACTER SET utf8 COLLATE utf8_bin;

-- 查看所有的数据库
SHOW DATABASES;

-- 查看数据库；
show database db_01;

-- 删除数据库
drop database db_01;

-- 恢复数据库 - sql 中执行

Source 文件名.sql

-- 表创建语法

create table 表名(
  字段1  类型,字段2  类型。。。
)character set 字符集 collate 校对规则 engine 引擎

-- unsinged  的使用

-- 创建字段 使用 unsigend 表示不带符号,大小从 0 开始
create table usr (id tinyint UNSIGNED);

-- 添加数据
insert into usr values(2);

-- 查询数据
select * from usr where id = 1;

-- 小数 使用 案例

create table b1 (num1 float, num2 double, num3 decimal(30,20) );

insert into b1 values(88.12313123, 99.23423424234234,12312.12312313123);

select * from b1;

-- 语法

-- 查看表
desc 表名;

-- 删除表
drop table 表名;

-- 修改表名
rename table 表名 to 新表名;

-- 修改字符集
alter table 表名 character set 字符集;

-- 添加字段
alter table 表名 add 字段名 字段类型 ;

-- 修改字段
alter table 表名 modify 字段名 字段类型;

-- 删除字段
alter table 表名 drop 字段名;

-- 表 及 字段的增删改

-- 创建表
create table emp (
  `id` int,
  `name` VARCHAR(32),
  `sex` CHAR,
  `brithday` date,
  `entry_date` datetime,
  `job` varchar(32),
  `salary` double,
  `resume` text
) CHARSET utf8 collate utf8_bin engine innodb;

-- 添加数据
insert into emp values(1,'小旋风','男','2022-1-1','2022-3-3 5:20:32','巡山',1000,'大王叫我来巡山');

-- 添加字段
-- not null default '' : 不能为空, 默认值为 '' 空字符串
-- after resume : 添加在 resume 字段后面
alter table emp add image varchar(3000) not null default '' after resume;

-- 修改字段, 并且不能为空
alter table emp modify job varchar(60) not null default '';

-- 删除字段
alter table emp drop sex;

-- 修改表名
rename table emp to employee;

-- 修改表的字符集
alter table employee character set utf8;

-- 修改字段名
alter table employee change `name` user_name varchar(32) not null default '';

select * from employee;

-- 语法

inset into 表名 values (字段值,字段值...)

inset into 表名(字段,字段...) values (字段值,字段值...)

-- 添加数据

create table `phone`(`id` int, `name` varchar(10), `price` double);

insert into phone values(1,'华为',3000);

insert into phone(`id`,`name`,`price`) values(2,'苹果',5000),(3,'小米',3000);

-- 字段可以为空时,实际值可以不写
insert into phone values();

-- 语法

update 表名 set 字段=值,字段=值;

update 表名 set 字段=值 where 查询条件;

-- 修改数据

update emp set salary = 5000;

update emp set salary = 2000 where user_name = '张三';

-- 语法

delete from 表名 where 查询条件;

-- 删除数据

delete from emp where user_name = '李四';

-- 案例

-- 将 emp 查询的数据 逐条添加到 test01 表中
insert into test01 (id, `name`, sal, job, deptno) select empno, ename, sal, job, deptno from emp;

-- 表数据的自我复制 (每次运行都会 将自己的每一条数据都复制一次)
insert into test01 select * from test01;

-- 去除表中重复的数据

-- 复制 表 结构 创建临时表
create table table_tmp like test01;

-- 将原表中的数据去重 写入 临时表
insert into table_tmp select distinct * from test01;

-- 清除 原表中的数据
delete from test01;

-- 将 临时表中的数据 写入 原表中
insert into test01 select * from table_tmp;

-- 删除 临时表
drop table table_tmp;

select * from test01;

-- 基本语法

select [distinct]*| 列名 from 表名 where 查询条件;

-- 字段取别名
select 字段名 as 别名 from 表名;

-- 去重 求和 - 案例

create table student (`id` int, `name` varchar(20), `chinese` float, `english` float, `math` float);

insert into student values(1,'韩信',89,78,90),(2,'张飞',67,98,56),(3,'宋江',87,78,77),(4,'关羽',88,98,90),(5,'赵云',82,84,67),(6,'欧阳锋',55,85,45),(7,'黄蓉',75,65,30);

-- 对结果去重
select distinct english from student;

-- 对所有成绩求和
select `name`,(chinese + english + math) as sum from student;

-- 案例

-- 查询赵云的成绩
select * from student where `name`='赵云';

-- 查询 英语成绩大于 90 分的
select * from student where `english` > 90;

-- 查询 数学大于 60 分并且 ID 大于 4
select * from student where `math` > 60 and `id` > 4;

-- 查询 总分大于 200 分, 并且数学成绩小于语文成绩,
-- 赵% : 表示以赵开头的人
select * from student where (`chinese` + `english` + `math`) > 200 and `math` < `chinese` and `name` like '赵%';

-- 查询 查询英语成绩大于语文成绩的
select * from student where `english` > `chinese`;

-- 查询 英语分数在 89 ~ 90 之间的
select * from student where `english` between 80 and 90;

-- 查询 数学分数在 89, 90, 91 的
select * from student where `math` in (89, 90, 91);

-- 查询 所有姓李的
select * from student where `name` like '关%';

-- 查询 数学比语文少 30 分的
select * from student where (`Chinese` - `math`) > 30;

select * from student;

-- 语法

select 列名 from 表名 where 查询条件 order by 排序条件;

-- 案例

insert into student values(8, '韩非子', 45,65,99);

-- 对数学成绩排序 升序
select * from student order by `math`;

-- 按总分 从高到底输出
select `name`, (`chinese` + `english` + `math`) as total_score from student
    order by total_score desc;

-- 语法

select count(*) from 表名 where 查询条件;

-- 案例

-- 统计 一共有多少个学生
select count(id) from student;

-- 统计 数学成绩大于 90 的
select count(`id`) as num from student where `math` > 90;

-- 统计 总分大于 250 的
select count(`id`) as num from student where (`chinese` + `english` + `math`) > 250;

-- 语法

select sum(字段) from 表名 where 查询条件;

-- 案例

-- 统计 数学总成绩
select sum(`math`) from student;

-- 统计 各科成绩
select sum(`chinese`), sum(`english`),sum(`math`) from student;

-- 统计 所有成绩的总和
select sum(`chinese` + `english` + `math`) from student;

-- 统计 语文的平均分
select sum(`chinese`) / count(`id`) from student;

-- 语法

select avg(列名) from 表名 where 查询条件;

-- 案例

-- 求数学成绩的平均值
select avg(`math`) from student;

-- 求班级总分的平均值
select avg(`chinese` + `english` + `math`) from student;

-- 语法

select max(列名) from 表名 where 查询条件;

-- 案例

-- 求数学成绩的最高分
select max(`math`) from student;

-- 求班级的最低分
select min(`chinese` + `english` + `math`) from student;

-- 语法

select 字段 from 表名 where 查询条件 group by 分组条件;

select 字段 from 表名 where 查询条件 group by 分组条件 having 过滤条件;

-- 案例

-- dept 部门表 (部门编号 deptno, 名称 dname, 所在城市 loc)
-- emp 员工表 (编号 empon, 名字 ename, 工作 job, 上级编号 mgr, 入职时间 hiredate, 薪水 sal, 红利 comn, 部门编号 deptno)
-- 工资级别表 (级别 grade, 最低工资 losal, 最高工资 hisal)

-- 查询 每个部门的平均工资 和 最高工资
select avg(sal), max(sal), deptno from emp group by deptno;

-- 查询 每个部门的 每个岗位 的平均工资 和 最低工资
select deptno, job, avg(sal), min(sal) from emp group by deptno, job;

-- 查询 平均工资 低于 2000 的部门号和平均工资
select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;

-- 案例

-- dept 部门表 (部门编号 deptno, 名称 dname, 所在城市 loc)
-- emp 员工表 (编号 empon, 名字 ename, 工作 job, 上级编号 mgr, 入职时间 hiredate, 薪水 sal, 红利 comn, 部门编号 deptno)
-- 工资级别表 (级别 grade, 最低工资 losal, 最高工资 hisal)

-- charset(str)	返回字符串 的字符集
select charset(ename) from emp;		-- utf8

-- concat(str1, str2...)	拼接 字符串
select concat(ename,'_',job) from emp; -- SMITH_CLERK

-- instr(str, substr)	返回 substr 在 str 中出现的位置,没有返回 0
-- dual: 亚元表, 系统表, 可以作为测试表来使用
select instr(`name`, '阳') from student where `name`='欧阳锋';

-- ucase(str)	转换成大写
select ucase(`ename`) from emp;

-- lcase(str)	转换成小写
select lcase(`ename`) from emp;

-- left(str, length)	从 str 中的左边起, 取 length 个字符
select ename, left(`ename`, 4) from emp;
-- 从右边取
select ename, right(`ename`, 4) from emp;

-- length(str)	str 长度 [按照字节]
select job,length(`job`) from emp;

-- replace(str, search_str, replacce_str)	在 str 中用 replace_str 替换 search_str
select job,replace(job, 'MANAGER', '经理') from emp;

-- strcmp(str, str)	逐字比较两个字符串大小
select strcmp('pupper','oupper') from dual;

-- substring(str, position)	从 str 的 position 开始 [从 1 开始计算 ], 取 length 个字符
select substring('pupper',2) from dual;		-- upper

-- ltrim(str),  rtrim(str)	去除前端空格 或 后端空格
-- trim	去除 前后空格
select ltrim('      pupper      ') from dual;
select rtrim('      pupper      ') from dual;
select trim('      pupper      ') from dual;

-- 以首字母小写的方式显示所有员工的姓名
select concat(lcase(left(`ename`,1)),substring(`ename`,2)) from emp;

-- 案例

-- abs(num)	绝对值
select abs(-10) from dual;

-- bin(decimal_number)	十进制 转 二进制
select bin(10) from dual;

-- ceiling(num)	向上取整 , 得到比 num 大的最小整数
select ceiling(10.01) from dual;

-- conv(num, from_base, to_base)	进制转换
-- 8 为 10 进制, 转为 2 进制
select conv(8, 10 ,2) from dual;

-- floor(num)	向下取整, 得到比 num 小的最大整数
select floor(1.1) from dual;

-- format(num, decimal_places)	保留小数位数, 四舍五入
select format(3.1415926634, 3) from dual;

-- hex(decimalNumber)	转 16 进制
select hex('a') from dual;

-- least(number1,number2)	求最小值
select least(1341123,213443242) from dual;

-- mod(numerator, denominator)	求余数
select mod(3,2) from dual;

-- rand([seed])	随机数, 其范围为 0 ≤ v ≤1.0
select rand() from dual;
-- 如果 seed 填写了, 在 seed 不变时,产生的 随机数不变
select rand(2) from dual;

-- 案例

create table mes(id int, content varchar(30), send_time datetime);
insert into mes values(1,'北京新闻', current_timestamp()),(2,'上海新闻',now()),(3,'广州新闻',now());

-- current_date()	当前日期
select current_date() from dual;

-- current_time()	当前时间
select current_time() from dual;

-- current_timestamp()	当前时间戳
select current_timestamp() from dual;

-- date(datetime)	返回 datetime 的日期部分
select date(`hiredate`) from emp;

-- 显示发布日期,不显示时间
select id, content, date(`send_time`) from mes;

-- 查询在 10 分钟内发布的新闻
select * from mes where date_add(`send_time`, interval 10 minute) >= now();
select * from mes where send_time >= date_sub(now(), interval 10 minute);

-- 计算 2011-11-11 和 1990-1-1 相差的天数
select datediff('2011-11-11','1990-01-01') from dual;

-- 计算活了多少点
select datediff(now(), '1989-11-22') from dual;

-- 计算 活到 80 岁还剩多少天
select datediff(date_add('1989-11-22', interval 80 year), now()) from dual;


-- timediff(date1, date2)	两个时间差(时分秒)
select timediff(now(), '2022-06-15 09:17:18') from dual;

-- year|month|date(datetime)
select year(now()) from dual;

-- unix_timestamp() 返回 1970-1-1 到现在的 秒数
select unix_timestamp() from dual;

-- from_unixtime() 将 时间戳 转换为 指定格式的 日期
select from_unixtime(1655285874) from dual;
select from_unixtime(1655285874, '%Y-%m-%d') from dual;
select from_unixtime(1655285874, '%Y-%m-%d %H:%i:%s') from dual;

-- user()	查询用户
select user();

-- dtabase()	数据库名称
select database();

-- md5(str)	为字符串加密 MD5 32 位
select md5('pupper');
-- b24fb9e5effed88af6c2590b9a7ab9a4

-- password(str)	用于对 MySQL 数据库的用户密码加密
select password('pupper');
-- *6EF8B45E1D589DB302041535A61774C1294D54D7

-- if(expr1,expr2,expr3)	如果 expr1 为 true, 则返回 expr2, 否则返回 expr3
select if(true, '北京', '杭州');		-- 北京

-- ifnull(expr1,expr2)	 如果 expr1 不为 null, 则返回 expr1 , 否则返回 expr2
select ifnull(null, '杭州');			-- 杭州

-- select case when expr1 then expr2 when expr3 then expr4 else expr5 end;
-- 如果 expr1 为 true, 则返回 expr2, 如果 expr3 为 true, 则返回 expr4, 否则返回 expr5
select case when 1 = 3 then '条件 1 成立' when 2=3 then '条件二成立' else '都不成立' end;		-- 都不成立

-- 如果 comm 为null, 则显示 0.0
-- 判断是否为 null, 要使用 is null, 不为空 is not null
select ename,if(comm is null, 0.0, comm) from emp;
select ename,ifnull(comm, 0.0) from emp;

-- 如果 emp 表的 job 是 clerk 则显示为 职员, 如果是 manager 则显示为 经理, 如果是 salesman 则显示 销售, 其他正常显示
select ename,
  case when job = 'CLERK' then '职员'
        when job = 'MANAGER' then '经理'
        when job = 'SALESMAN' then '销售' else job end as job
from emp;

-- 按照 员工 ID 升序取出, 每页显示 3 条数据
-- limit 每页显示的记录数 * (第几页-1), 每页显示的记录数
select * from emp order by empno limit 0,3 ;
select * from emp order by empno limit 3,3 ;
select * from emp order by empno limit 6,3 ;

-- 显示每种岗位的员工总数和平均工资
select job,count(empno), avg(sal) from emp group by job;

-- 显示员工总数 和 获得补助的员工数
select count(*), count(comm) from emp;

-- 显示管理者的人数
select count(distinct mgr) from emp;

-- 显示员工工资的最大差额
select (max(sal)- min(sal)) from emp;

select column1, column2, column3 ... from table
        group by column
        having condition
        order by column
        limit start, rows;

-- 统计 各个部门平均工资且大于 1000 且从高到低排序 且显示 2 行
select deptno,avg(sal) as avg_sal from emp
	group by deptno
	having avg_sal > 1000
	order by avg_sal desc
  limit 0, 2;

-- dept 部门表 (部门编号 deptno, 名称 dname, 所在城市 loc)
-- emp 员工表 (编号 empon, 名字 ename, 工作 job, 上级编号 mgr, 入职时间 hiredate, 薪水 sal, 红利 comm, 部门编号 deptno)
-- 工资级别表 (级别 grade, 最低工资 losal, 最高工资 hisal)

-- 查询 1992-1-1 之后入职的员工
select * from emp where hiredate > '1992-1-1';

-- 查询 姓名首字母为 S 的员工
select ename, sal from emp where ename like 'S%';

-- 查询 姓名 第三个字母为 O 的员工
select ename, sal from emp where ename like '__O%';

-- 查询 表结构
desc emp;

-- 按照工资从低到高显示员工信息
select * from emp order by sal;

-- 按照 部门编号的升序 工资的降序 显示员工工资
select * from emp order by deptno asc, sal desc;

-- dept 部门表 (部门编号 deptno, 名称 dname, 所在城市 loc)
-- emp 员工表 (编号 empon, 名字 ename, 工作 job, 上级编号 mgr, 入职时间 hiredate, 薪水 sal, 红利 comm, 部门编号 deptno)
-- salgrade 工资级别表 (级别 grade, 最低工资 losal, 最高工资 hisal)

-- 查询 员工的名字,工资,部门名称,并按部门编号降序排列
select  ename, sal, dname, dept.deptno from dept, emp where dept.deptno = emp.deptno order by dept.deptno desc;

-- 查询 部门号为 10 的 员工名字,工资,部门名称
select  dept.deptno, dname, ename, sal from dept, emp where dept.deptno = emp.deptno and dept.deptno = 10;

-- 查询各个员工的名字, 工资, 及工资级别
select ename, sal, grade from emp, salgrade where losal <= sal and hisal >= sal;
select ename, sal, grade from emp, salgrade where sal between losal and hisal;

-- 显示员工名字和 上级的名字
select worker.ename, boss.ename from emp worker, emp boss where worker.mgr = boss.empno;

-- 案例 - 雇员数据库

-- dept 部门表 (部门编号 deptno, 名称 dname, 所在城市 loc)
-- emp 员工表 (编号 empon, 名字 ename, 工作 job, 上级编号 mgr, 入职时间 hiredate, 薪水 sal, 红利 comm, 部门编号 deptno)
-- salgrade 工资级别表 (级别 grade, 最低工资 losal, 最高工资 hisal)

-- 返回单行 子查询
-- 查询 SMITH 同一部门的其他员工
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');

-- 返回多行 子查询
--  查询 和部门10 工作相同的员工信息,但是不包含 10 号部门的员工
select * from emp where job in (select distinct job from emp where deptno = 10) and deptno != 10;

-- dept 部门表 (部门编号 deptno, 名称 dname, 所在城市 loc)
-- emp 员工表 (编号 empon, 名字 ename, 工作 job, 上级编号 mgr, 入职时间 hiredate, 薪水 sal, 红利 comm, 部门编号 deptno)
-- salgrade 工资级别表 (级别 grade, 最低工资 losal, 最高工资 hisal)

-- 查询 工资比 30 号部门所有员工工资高的员工信息
select * from emp where sal > all(select distinct sal from emp where deptno = 30);
select * from emp where sal > all(select max(sal) from emp where deptno = 30);

-- 查询 工资比 30 号部门其中一个员工工资高的员工信息
select * from emp where sal > any(select distinct sal from emp where deptno = 30);
select * from emp where sal > any(select min(sal) from emp where deptno = 30);

-- 查询 与 ALLEN 部门和岗位完全相同的所有员工,不含其本人
select * from emp where (deptno, job) = (select deptno, job from emp where ename = 'ALLEN') and ename != 'ALLEN';

-- 查询 每个部门工资高于部门平均工资的个人资料
select empno, ename, job, mgr
	from emp, (select deptno, avg(sal) as avg_sal from emp group by deptno) temps
	where emp.deptno = temps.deptno and sal > avg_sal;

-- 查询 每个部门工资最高的员工资料
select empno, ename, job, mgr
	from emp, (select deptno, max(sal) as max_sal from emp group by deptno) temps
	where emp.deptno = temps.deptno and sal = max_sal;

 -- 查询每个部门的 名称, 编号, 地址 和人员数量
select dept.deptno, dname, loc, com
		from dept,(select deptno,count(empno) as com from emp group by deptno) num
		where dept.deptno = num.deptno;

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

create table stu (id int, `name` varchar(32));
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
select * from stu;

create table exam (id int, grade int);
insert into exam values(1, 56),(2,76),(11,8);
select * from exam;

-- 左外连接
select `name`, stu.id, grade from stu left join exam on stu.id = exam.id;

-- 右外连接
select `name`, stu.id, grade from stu right join exam on stu.id = exam.id;

-- 列出所有部门及其员工,包括没有员工的部门
select dname,ename, job from dept left join emp on dept.deptno = emp.deptno order by dname ;

-- 创建表时指定 主键
create table test02 (id int primary key, `name` varchar(32));

-- 创建表之后 指定主键
create table test03 (id int, `name` varchar(32), primary key (id));

-- 复合主键
create table test04 (id int, `name` varchar(32), primary key (id, `name`));

-- 非空
字段名 字段类型 not null

-- 唯一
字段名 字段类型 unique

-- 创建表时指定 字段数据唯一并且非空
create table test06 (id int unique not null, `name` varchar(32), `add` varchar(32));

foreign key (从表字段名) references 主表名(主键名 或 unique 字段名)

-- 创建主表
create table my_class(
	id int primary key,
	`name` varchar(32) not null default '');

-- 创建从表
create table my_stu(
	id int primary key,
	`name` varchar(32) not null default '',
	class_id int,
	-- 创建外键
	foreign key (class_id) references my_class (id));


insert into my_class values(100, 'java'),(200,'web');
insert into my_stu values (1, 'jack', 100),(2, 'tom', 200),(3, 'money', 200),(4, 'tank', 100);

select * from my_class;
select * from my_stu;

字段名 整型 primary key auto_increment

-- 自增长 字段赋值 null 时, 默认从 1 开始自增长
insert into xxx (字段1, 字段2...) values (null, 值2...);
insert into xxx (字段2...) values (值1, 值2...);
insert into xxx values (null, 值2...);

-- 自增长 - 案例

-- 创建 表
create table test05 (
	id int primary key auto_increment,
	email varchar(32) not null default '',
	`name` varchar(32) not null default '');

insert into test05(id, email, `name`) values (null, '123@gmail.com', 'jack');
insert into test05(email, `name`) values ('asdf@qq.com', 'tom');
insert into test05 values (null, '43241233@qq.com', 'join');

create index empno_index on emp (empno)
-- empno_index : 表示索引名称
-- on emp(empno) : 表示在 emp 表的 empno 字段 创建索引

-- 添加索引前 2.246s 添加索引后 0.007s
create index ename_index on emp(ename);
select * from emp where ename = 'sToObQ';

-- 索引的 增, 删, 改, 查

-- 创建测试表
create table test06 (id int, tname varchar(32));

-- 查看 表索引
show indexes from test06;
show index from test06;
show keys from test06;
________________________________________________________________________________

-- 添加主键索引 (创建表时没有 添加主键 时可以使用)
-- 如果创建表时指定了主键, 则主键自动成为主键索引
alter table test06 add primary key (tname);

-- 添加 唯一索引
create unique index id_index on test06(id);

-- 添加普通索引
create index tname_index on test06(tname);

-- 添加普通索引
alter table test06 add index tname_index(tname);
________________________________________________________________________________

-- 删除索引
drop index id_index on test06;
drop index tname_index on test06;

-- 删除 主键索引
alter table test06 drop primary key;

-- 索引的修改 : 先删除, 再重新添加;

-- 数据库创建

-- 要求 客户名不为空, 邮箱不重复, 单价 在 1.0 ~ 9999.99 之间

-- 创建数据库
create database shop_db character set utf8 collate utf8_bin;

-- 创建 商品表 goods, 商品号 goods_id, 商品名 goods_name, 单价 unitprice, 商品类别 category, 供应商 provider
create table goods(
	goods_id int primary key,
	goods_name varchar(64) not null default '',
	unitprice decimal(10,2) not null default 0
		check(unitprice >= 1.0 and unitprice <10000),
	category int not null default 0,
	provider varchar(64) not null default '');

-- 创建 客户表 customer, 客户id crstomer_id, 姓名 name, 地址 address, 邮箱 email, 性别 sex, 身份证 card_id
create table customer(
	customer_id int primary key,
	`name` varchar(64) not null default '',
	address varchar(64)not null default '',
	email varchar(64) unique not null default '',
	sex enum('男','女') not null, 	-- 枚举类型
	card_id varchar(32) unique);

-- 创建 订单表 purchase, 订单号 order_id, 客户号 customer_id, 商品号 goods_id, 购买数量 nums
create table purchase(
	order_id int unsigned primary key,
	customer_id int not null default 0,
	goods_id int not null default 0,
	nums int not null default 0,
	foreign key (customer_id) references customer(customer_id),
	foreign key (goods_id) references goods(goods_id));

create table test06 (id int, `name` varchar(32));

-- 开启事务
start transaction;
insert into test06 values(100, 'tom');

-- 添加保存点
savepoint a;
insert into test06 values(200, 'jack');

-- 添加保存点
savepoint b;

select * from test06;

-- 回退到 a 保存点
rollback to a;

-- 回退全部
rollback;

-- 提交事务
commit;

-- 查询当前会话隔离级别
select @@tx_isolation;

-- 查看系统当前隔离级别
select @@global.tx_isolation;

-- 设置当前会话隔离级别
set session transaction isolation level xxx;

-- 设置系统当前隔离级别
set session transaction isolation level xxx;

-- 创建视图
create view 视图名 as select 列名 from 表名 ...

-- 修改(更新)视图
alter view 视图名 as select 语句

-- 查看创建视图的指令
show create view 视图名;

-- 删除视图
drop view 视图名 1, 视图名 2;

-- dept 部门表 (部门编号 deptno, 名称 dname, 所在城市 loc)
-- emp 员工表 (编号 empon, 名字 ename, 工作 job, 上级编号 mgr, 入职时间 hiredate, 薪水 sal, 红利 comm, 部门编号 deptno)
-- salgrade 工资级别表 (级别 grade, 最低工资 losal, 最高工资 hisal)

-- 创建视图表, 可以显示 员工编号,名称,部门名称,薪水等级等 (三表联合查询)
create view emp_view02 as
	select empno, ename, dname, grade from dept, emp,salgrade
	where emp.deptno = dept.deptno and (sal between losal and  hisal);

-- 创建用户
create user '用户名'@'允许登录的位置'	identified by '密码'

-- 删除用户
drop user '用户名'@'允许登录的位置';

-- 修改自己的密码
set password = password('新密码');

-- 修改别人的密码
set password for '用户名'@'允许登录的位置' = password('密码');

-- 授予权限
grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码']

-- 回收权限
revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

-- 权限生效指令 (如果权限没有生效,可使用下面的指令)
flush privileges;

-- 添加用户
create user 'myroot'@'127.0.0.1' identified by '123456';

-- 添加权限
-- grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码']
grant select, insert on tmp.* to 'myroot'@'127.0.0.1';

-- 回收权限
-- revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';
revoke insert on tmp.* to 'myroot'@'127.0.0.1';

-- dept 部门表 (部门编号 deptno, 名称 dname, 所在城市 loc)
-- emp 员工表 (编号 empno, 名字 ename, 工作 job, 上级编号 mgr, 入职时间 hiredate, 薪水 sal, 红利 comm, 部门编号 deptno)
-- salgrade 工资级别表 (级别 grade, 最低工资 losal, 最高工资 hisal)

-- 3 显示所有部门名称
select dname from dept;
-- 显示所有员工年的收入 13 月
select ename, (sal+ifnull(comm,0))*13 as '年收入' from emp;

-- 4 工资超过 2850 的员工
select ename, sal from emp where sal > 2850;
-- 工资在 2850 ~ 1500 之间的员工
select ename, sal from emp where sal > 2850 or sal < 1500;
-- 编号为 7566 的员工
select ename, deptno from emp where empno = 7566;
-- 10 和 30 号部门, 工资超 1500 的员工
select ename, sal from emp where sal > 1500 and deptno in (10, 30);
-- 无上级的员工
select ename, job from emp where mgr is null;

-- 5 在 1991-2-1 和 1991-5-1 之间入职的员工
select ename, job, hiredate from emp where hiredate between '1991-2-1' and '1991-5-1' order by hiredate;
-- 有补助的员工
select ename, sal, comm from emp where comm is not null order by sal;

-- 6 部门 30 的员工
select * from emp where deptno = 30;
-- 所有 工作为 CLERK 的员工
select ename, empno, deptno from emp where job = 'CLERK';
-- 佣金高于薪金的员工
select * from emp where comm > sal;
-- 佣金高于薪金 60%
select * from emp where comm > (sal * 0.6);
-- 部门 10 的 MANAGER 和 部门 20 的 CLERK
select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');
-- 部门 10 的 MANAGER 和 部门 20 的 CLERK 和 既不是 MANAGER 也不是 CLERK 且 工资大于 2000
select * from emp
	where (deptno = 10 and job = 'MANAGER')
	or (deptno = 20 and job = 'CLERK')
	or (job not in('MANAGER', 'CLERK') AND sal >= 2000);
-- 收取佣金的不同工作
select distinct job from emp where comm is not null;
-- 不收取佣金或佣金少于 100
select * from emp where comm is null or comm < 100;
-- 每月倒数第三天入职的员工
select * from emp where hiredate = (date_sub(last_day(hiredate),interval 2 day));
select * from emp where hiredate = last_day(hiredate) - 2;
-- 早去 12 年前入职的员工
select * from emp where date_add(hiredate, interval 12 year) < now();
-- 首字母小写
select concat(lcase(left(`ename`,1)),substring(`ename`,2)) from emp;
-- 名字前 5 个字母
select left(ename, 5) from emp;
-- 名字不包含 R 的员工名字
select ename from emp where ename not like '%R%';
-- 员工名字前三个字符
select substring(ename,1,3) from emp;
-- 替换名字 a 替换 A
select replace(ename, 'A', 'a') from emp;
-- 入职满 10 年的员工
select ename, hiredate from emp where date_add(hiredate, interval 10 year) <= now();
-- 按名字排序
select * from emp order by ename;
-- 按入职年限排序
select * from emp order by hiredate;
-- 先按工作排序,再按工资排序
select * from emp order by job desc,sal;
-- 员工姓名,入职年份,月份, 先按月排序,再按年排序
select ename, year(hiredate), month(hiredate) from emp order by month(hiredate),year(hiredate);
-- 日薪
select floor(sal/30) as '日薪' from emp;
-- 2 月入职
select * from emp where month(hiredate) = 2;
-- 入职天数
select datediff(now(), hiredate) as '入职天数' from emp;
-- 员工名字包含 A
select ename from emp where ename like '%A%';
-- 日期方式显示入职时间
select from_days(datediff(now(), hiredate)) as '服务天数' from emp;

-- 7 至少有一个员工的部门
select dname from dept where deptno in (select deptno from emp group by deptno);
-- 薪资比 SMITH 多的员工
select * from emp where sal > (select sal from emp where ename = 'SMITH');
-- 雇佣时间比上级晚的员工
select emp.ename, emp.hiredate from emp, emp tmps where emp.mgr = tmps.empno and emp.hiredate > tmps.hiredate;
-- 部门名称和员工信息,包括没有员工的部门
select dname, emp.deptno, ename, hiredate from dept left join emp on dept.deptno = emp.deptno order by dname;
-- 所有 工作为 CLERK 的员工姓名和 部门名称
select ename, dname, job from dept, emp where dept.deptno = emp.deptno and job = 'CLERK' order by dname;
-- 最低薪资大于 1500 的工作
select  job, min(sal) min_sal from emp group by job having min_sal > 1500;
-- 在部门 sales 工作的员工
select * from emp where deptno = (select deptno from dept where dname = 'sales');
-- 薪水高于平均工资的员工
select * from emp where sal > (select avg(sal) from emp);
-- 与 SCOTT 工作相同的员工
select * from emp where job = (select job from emp where ename = 'SCOTT') and ename <> 'SCOTT';
-- 薪水高于 30 号部门所有员工
select * from emp where sal > (select max(sal) from emp where deptno = 30) and deptno != 30;
-- 每个部门的员工人数, 平均工资, 平均服务年限
select count(empno) as '人数', avg(sal), format(avg(datediff(now(), hiredate)/365),2) from emp group by deptno;
-- 所有员工的姓名,部门,工资
select ename, dname, sal from dept, emp where dept.deptno = emp.deptno;
-- 所有部门的详细信息及部门人数
select dname, count(dname) from dept, emp where dept.deptno = emp.deptno group by dname;
-- 各个工种的最低工资
select job, min(sal) from emp group by job;
--  MANAGER 的最低薪水
select min(sal) from emp where job = 'MANAGER';
-- 计算年薪,升序排列
select ename, (sal + ifnull(comm, 0))*12 as year_sal from emp order by year_sal;