MySQL 从入门到入狱 rm - rf /* 咳咳~ 到精通_sql数据库rm-rf

做好笔记，勤加练习。 刷多少不重要，重要的是你学了多少。每学完一个视频要问自己是否学会了，囫囵吞枣的刷视频虽然也有一点点效果， 但还是要认认真真学。 我就拿我自己来说，我学前端js时候是看视频都是全程跟老师写代码(相当于没自己练)，笔记也不做，跟快餐式的刷视频，也不复习，这样做结果造成了什么，一个月学的东西全部忘了。

咳咳~上面一句正能量  下面我们就开始学习MySQL 请大家三连哦~

为什么要学MySQL ??? 看下面调查

DB 数据库(database): 存储数据的“仓库”。它保存了一系列有组织的数据。

DBMS （Database Management System）。数据库是通过DBMS 创建和操作的容器

 SQL 结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。

 概念：MySQL数据库属于MySQL AB公司，总部位于瑞典，后被oracle收购

* 优点：~成本低：开放源代码，一般可以免费使用

show databases;        查看所有数据库

 use db01         #db01是数据库名称  use 锁定数据库名称

 show tables;        #查看锁定库里面的表名

show tables from db01;        #db01是数据库名  此命令一键查看

 

mysql> create table stuinfo(         #创建表        自己起的表名stuinfo
    -> id int,
    -> name varchar(20));
Query OK, 0 rows affected (0.02 sec)

 

 desc stuinfo;        #查看 stuinfo 表结构

 

 查看mysql版本的三种方式

1：select version();        #登录到mysql服务端

2:  mysql --version         #未登录到mysql服务端

3: mysql -V                       #未登录到mysql服务端   

一，基础查询

        1：进阶1

#语法 select 查询列表 from 表名；
 
#1. 查询表中的单个字段
select last_name from employees;
#2. 查询表中的多个字段
select last_name,salary,email from employees;
#3. 查询表中的所有字段
select * from employees;
#4. 查询常量值
select 100;
select 'john';
#5. 查询表达式
select 100*98;
select 100%98;
#6. 查询函数 mysql 版本号
select VERSION();
#7. 起别名  也可以把as省略
select 100%98 as 结果;
#8. 去重 关键字 DISTINCT
#案例：查询员工表中涉及到的所有的部门编号
select DISTINCT department_id from employees;
#9. +号的作用
#案例：查询员工名和姓链接成一个字段,并显示为 姓名
select last_name+first_name AS 姓名 from employees;
select CONCAT('a','b','c') AS '结果';
select CONCAT(last_name,first_name) AS '姓名' from employees;
/*
   java中的+号
	 1. 运算符, 两个操作数都为数值型
	 2. 连接符, 两个有一个操作数为字符串
	 
	 mysql 中的＋号：
	 仅仅只有一个功能：运算符
	 select 100+90; 两个操作数都为数值型,则做加法运算
	 select '123'+90; 其中一方为字符型,试图将宇字符型数值转换成数值型
										如果转换成功,则继续做加法运算
										如果转换失败,则将字符型数值转换成0
										select 'john'+90;
		select null+10; 只要其中一方为null, 则结果肯定为null
	 
*/
select '100'+50;
select 'join'+50；
select null+50;
#10. 显示表 departments 的结构，并查询其中的全部数据
desc departments;
# 题目 1. 显示出表 employees 中的全部 job_id （不能重复）
select distinct job_id from employees;
# 题目 2. 显示出表 employees 中的全部列，各个列之间用逗号链接，列头显示成OUT_PUT
# 如果字段中为null 会显示 null 我们用 ifnull 把null替换成0
select concat(first_name,',',last_name,',',IFNULL(commission_pct,0)) as 'OUT_PUT' from employees;
 

        进阶2

#进阶2：条件查询
/*
 语法：
	select
		查询列表
	from
		表名
	where
		筛选条件
	分类：
		一，按条件表达式筛选
				条件运算符：> < = != <> >= <=
		二，按逻辑表达式筛选
				逻辑运算符：&& || ！
		三，模糊查询： like, between and, in, is null
		
*/
#案例1：查询工资>12000的员工信息
select * from employees where salary > 12000;
#案例2: 查询部门编号不等于90号的员工名和部门编号
select last_name,department_id from employees where department_id != 90;
select last_name,department_id from employees where department_id <> 90;
# && 和 and 两个条件都为true,结果为true,反之为false
# || 或 or  只要有一个条件为true,结果为true,反之为false
# ！ 或 not 如果链接的条件本身为false，结果为 true，反之为 false
 
#案例1：查询工资在10000到20000之间的员工名，工资以及奖金
select last_name,salary,commission_pct from employees where salary >=10000 and salary <= 20000
#案例2：查询部门编号不是在90到110之间,
select * from employees where department_id<90 or department_id > 110 or salary>15000;
select * from employees where not(department_id>=90 and department_id <=110) or salary>15000;
select department_id from employees
#模糊查询
/*
  like
	特点：
	1. 一般和通配符搭配使用
		通配符
		% 任意多个字符,包含0个字符
		—— 任意单个字符
*/
#案例1：查询员工名中包含字符a的员工信息
select * from employees where last_name like '%a%';
#案例2：查询第三个是s第五个是i的员工名和工资
select last_name,salary from employees where last_name like '__s_i%';
#案例3：查询员工名中第二个字符为_的员工名  ESCAPE表示$是个转义字符
select last_name from employees where last_name like '_\_%';
select last_name from employees where last_name like '_$_%' ESCAPE '$';
#2. between and
#案例1：查询员工编号在100到200之间的员工信息
select * from employees where employee_id >=100 and employee_id<=200
select * from employees where employee_id between 100 and 200
#3. in
#案例：查询员工的工种编号是 
select last_name,job_id from employees where job_id in('AD_PRES','AD_VP','IT_PROG'); 
#4. is null
#案例1：查询没有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct is null 
select last_name,commission_pct from employees where commission_pct is not null 
#安全等于 <=>
select last_name,commission_pct from employees where commission_pct <=> null;
#查询员工号为176的员工的姓名和部门号和年薪
select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) as '年薪' from employees;

#查询没用奖金,且工资小于18000的salary,last_name
select salary,last_name,commission_pct from employees where commission_pct is null and salary<18000;
#查询employees表中,job_id不为 ‘IT’ 或者 工资为12000的员工信息
select job_id from employees where job_id<>'IT'  or salary <=> 12000;
#查询部门 departments 表的结构,查询效果如下
desc departments;
#查询部门departments表中涉及到了哪些位置编号
select distinct location_id from departments;
#  isnull 是否为null 1是true 0是false 
select isnull(commission_pct),commission_pct from employees;
# 模糊查询 数值型 用 _ 任意单个字符
select * from employees where department_id like '1__';

进阶3

#进阶3：排序查询
/*
 语法：
			select 查询列表
			from 表
			where 筛选条件
			order by 排序列表 【asc/desc】
	特点：
			1. asc代表的是升序, desc 代表的是降序
			如果不写,默认是升序
			2. order by 子句一般是放在查询语句的最后面, limit 子句除外
*/
select * from employees order by salary desc;#升序
select * from employees order by salary asc;#降序
 
#案列2：查询部门编号 >= 90 的员工信息, 按入职的先后进行排序
select * from employees where department_id >= 90 order by hiredate asc;
#案例3：按年薪的高低显示员工的信息和 年薪 [按表达式排序]
select *,salary*12*(1+IFNULL(commission_pct,0)) '年薪'
from employees order by salary*12*(1+IFNULL(commission_pct,0)) desc;
#案例3：按年薪的高低显示员工的信息和 年薪 [按别名排序]
select *,salary*12*(1+IFNULL(commission_pct,0)) '年薪'
from employees order by '年薪' desc;
#案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
select length(last_name) as '字节长度',last_name,salary from employees order by length(last_name) desc;
#案例6：查询员工信息，要求先按工资排序,再按员工编号排序【多个字段排序】
select * from employees order by salary asc,employee_id desc;
 
#测试 查询员工的姓名和部门号和年薪, 按年薪降序 按姓名升序
select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees order by 年薪 desc,last_name asc;
# 选择工资不在 8000到17000的员工的姓名和工资,按工资降序
select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc;
# 查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
select * from employees where email like '%e%' order by length(email) desc,department_id asc;

进阶四

#进阶4：常见函数
/*
 功能：类似于Java方法, 将一组逻辑语句封装在方法体中,对外暴露方法名
 好处：1, 隐藏了实现细节  2, 提高代码的重用性
 调用：select 函数名(实参列表) [from表]
	特点：1叫什么（函数名）
				2干什么（函数功能）
	分类： 1，单行函数
		如 concat , length, ifnull 等
		2，  分组函数
		
*/
# 1. substr,substring
select substr('李莫愁爱上了陆展元',7) str;
# 截取从指定索引处指定字符长度的字符
select substr('李莫愁爱上了陆展元',1,3) str;
# 2. instr 返回子串第一次出现的索引,如果找不到返回0
select instr('杨过pk小龙女','小龙女') as 结果;
#6. trim
select length(trim('   刘小子   ')) as 结果;
# 去掉 a 前后
select trim('a' from 'aaaaaaaaa长aaaaaaaaaaaaa老大aaaaaaaaaaa') as 结果;
#7. lpad 用指定的字符实现左填充指定长度 rpad 右填充
select lpad('六大大',10,'*') as 结果;
#9. replace 替换
select replace('把今天替换成明天','今天','明天') as 结果;
 
# 二, 数学函数
#round 四舍五入
select round(1.65);
select round(1.65,2);#小数点保留2位
# ceil 向上取整, 返回 >= 该参数的最小整数
select ceil(1.35);
#floor 向下取整,返回 <= 该参数的最大整数
select floor(-9.99);
# truncate 截断 后面的几位
select truncate(1.65,1);
#mod 取余
select mod(10,3);
 
# 三, 日期函数
#now 返回当前系统日期+时间
select now();
#curdate 返回当前系统日期,不包含时间
select curdate();
#curtime 返回当前时间,不包含日期
select curtime();
#可以获取指定的部分,年,月,日,小时,分钟,秒
select year(now());
#月
select month(NOW());
# str_to_adte 将字符通过指定的格式转换成日期
# 将日期格式的字符转换成指定格式的日期
select str_to_date('1998-3-2','%Y-%c-%d') as 结果;
#查询入职日期为1992-4-3的员工信息
select * from employees where hiredate =  str_to_date('4-3 1992','%c-%d %Y') ;
# date_format: 将日期转换成字符
select DATE_FORMAT(NOW(),'%y-%m-%d');
# 查询有奖金的员工名和入期日期(xx/xx/xx)
select last_name,DATE_FORMAT(hiredate,'%y-%m-%d') 入职日期 from employees where commission_pct is not null;
 
# 四, 其他函数
select version();
select database();
select user();

流程控制函数

# 五, 流程控制函数
# 1. if 函数: if else 的效果
select if(10>5,'大','小');
select last_name,commission_pct,IF(commission_pct is null,'没奖金,呵呵','有奖金,嘻嘻') from employees;
 
#2. case函数的使用一： switch case 的效果
/* 案例：查询员工的工资,要求
		部门号=30, 显示的工资为1.1倍
		部门号=40, 显示的工资为1.2倍
		部门号=50, 显示的工资为1.3倍
		其他部门,显示的工资为原工资
*/
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;
 
#案例：查询员工的工资的情况
/*
	如果工资>20000,显示A级别
	如果工资>15000,显示B级别
	如果工资>10000,显示C级别
	否则, 显示D级别
*/
 select salary,
 case 
 when salary>20000 then 'A'
 when salary>15000 then 'B'
 when salary>10000 then 'C'
 ELSE 'D'
 END as 工资级别
 from employees;
 
#案列：1 查询员工号, 姓名 ,工资 以及工资提高百分之20%后的结果
select employee_id,last_name,salary*1.2 "new" from employees;
#2. 将员工的姓名按首字母排序,并写出姓名的长度(length)
select length(last_name) 长度,substr(last_name,1,1) 首字母,last_name from employees order by 首字母 ;
 
 
 
 
 

分组 进阶五

# 二, 分组函数
/*
功能：用作统计使用,又称为聚合函数或统计函数或组函数
分类：
	sum 求和
	avg  平均数
	max 最大值
	min 最小值
	count 计算个数
*/
select sum(salary)from employees;
select avg(salary)from employees;
select max(salary)from employees;
select count(salary)from employees;
select min(salary) 最小, round(avg(salary),2) 平均 from employees;
#2,参数支持哪些类型
select sum(last_name) from employees;#不支持
select max(last_name),min(last_name) from employees;#支持
select count(commission_pct) from employees;
select commission_pct from employees where commission_pct is not null;
select count(last_name)from employees;
#3. 和distinct搭配 去重
select sum(distinct salary),sum(salary) from employees;
#4. count函数的详细介绍
select count(salary) from employees;
select count(*) from employees;
select count(1) from employees;
# 查询员工表中的最大入职时间和最小入职时间的相差天数
select datediff(max(hiredate),min(hiredate)) datediff from employees;
select datediff('2017-10-1','2017-9-29');
 
#进阶5：分组
/*
   语法：
			select 分组函数,列（要求出现在group by 的后面）
			from 表
			【where 筛选条件】
			group by 分组的列表
			【order by 子句】
	注意：
			查询列表必须特殊,要求是分组函数和group by 后出现的字段
	特点：1，分组查询中的筛选条件分为两种
				分组前筛选 数据源  
				分组后筛选 原始表
				分组函数做条件肯定放在having子句中
				2,group by 子句支持单个多个字段分组,用逗号隔开（没用顺序）
*/
 
# 查询每个工种的最高工资
select max(salary),job_id from employees group by job_id;
# 查询每个位置上的部门个数
select count(*),location_id from departments group by location_id
# 查询邮箱中包含a字符的,每个部门的平均工资
select email,department_id,avg(salary) from employees where email like '%a%' group by department_id;
# 案例2：查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id from employees where commission_pct is not null group by manager_id;
# 案例：查询哪个部门的员工个数>2
select count(department_id) 个数,department_id from employees group by department_id having 个数>2;
# 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary) max ,job_id from employees where commission_pct is not null group by job_id having max>12000;
# 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
select manager_id,min(salary) 最低 from employees where manager_id>120 group by manager_id having 最低>5000;
# 按表达式或函数分组
# 案例：按员工姓名的长度分组, 查询每一组的员工个数,筛选员工个数>5的有哪些
select count(*) as 个数,LENGTH(last_name) len_name from employees group by len_name having 个数>5;
# 案例：查询每个部门工种的员工的平均工资
select avg(salary),department_id,job_id from employees group by job_id,department_id order by avg(salary) desc;
# 1. 查询各job_id 的员工 工资的最大值，最小值，平均数，总和 , 并按job_id升序
select max(salary),min(salary),avg(salary),sum(salary),job_id from employees group by job_id order by job_id;
# 2. 查询员工最高工资和最低工资的差距（DIFFERENCE） 差距
select max(salary) - min(salary) from employees;
# 3.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
select min(salary) 最低,manager_id from employees where manager_id is not null group by manager_id having 最低>=6000;
# 4. 查询所有部门的编号, 员工数量和工资平均值,并按平均工资降序
select count(*),department_id,avg(salary) from employees group by department_id order by avg(salary);

进阶06 多表联查

#阶级6：链接查询
/*
 含义：又称多表查询,当查询的字段来自于多个表时,就会用到链接查询
 
	笛卡尔乘积现象：表1 有m行, 表2有n行,结果 =m*n行
	发生原因
	分类：
	  按年代分类：
			sql192标准
			sql199标准【推荐】
			
			按功能分类：
					内连接：
						等值连接
						非等值连接
						自连接
					外连接：
						左外连接
						右外链接
						全外链接
					交叉链接
*/
 
select * from beauty;
 
select * from boys;
#两表查询
select name,boyName from beauty,boys where beauty.boyfriend_id = boys.id;
 
#一，sql 192标准
#1，等值链接
# 案例1：查询女神名和对应的男神名
select name,boyName from beauty,boys where beauty.boyfriend_id = boys.id;
 
 
 
 
#注意：如果为表起了别名,则查询的字段就不能使用原来的表名去限定
/*
	1. 等值连接
		1. 多表等值连接的结果为多表的交集部分
		2. n表连接, 至少需要n-1个连接条件
		3. 多表的顺序没有要求
		4. 一般需要为表起别名
		5. 可以搭配前面介绍的所有子句使用,比如排序,分组,筛选
*/
#案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#查询员工名,工种号,工种名 为表起别名
SELECT last_name,jobs.job_id,job_title
FROM employees,jobs
WHERE employees.job_id = jobs.job_id;
#案例：查询有奖金的员工名,部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct is not null;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND l.city LIKE '_o%';
#查询每个城市的部门个数
SELECT count(*) 个数,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city;
#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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
#查询每个工种的工种名和员工的个数,并且按员工个数降序
SELECT job_title,COUNT(*) 个数
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY 个数 DESC
# 三表 查询员工名,部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city LIKE 's%'
ORDER BY department_name DESC;
 
# 二, 非等值连接
 
CREATE TABLE job_grades
(grade_levenl VARCHAR(3),
lowest_sal INT,
highest_sal INT);
 
INSERT INTO job_grades
VALUES ('A',1000,2999);
INSERT INTO job_grades
VALUES ('B',3000,5999);
INSERT INTO job_grades
VALUES ('C',6000,9999);
INSERT INTO job_grades
VALUES ('D',10000,14999);
INSERT INTO job_grades
VALUES ('E',15000,24999);
INSERT INTO job_grades
VALUES ('F',25000,40000);
 
select * from job_grades;
 
# 查询员工的工资和工资级别
SELECT salary,grade_levenl
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_levenl ='A';
 
# 三, 自连接
# 查询 员工名和上级的名称
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 MAX(salary) 最大,AVG(salary) 平均 FROM employees;
# 查询员工表的employee_id, job_id,last_name , 按 department_id 降序, salary升序
SELECT employee_id,job_id,last_name FROM employees ORDER BY department_id DESC,salary ASC;
#查询员工表的job_id中包含 a 和 e的 , 并且a在e的前面
SELECT job_id FROM employees WHERE job_id LIKE '%a%e%';
# 显示当前日期,以及去前后空格,截取字符串的函数
SELECT now();
SELECT trim(字符 from '')
select substr(str,startIndex)
select substr(str,startIndex,length)
 
 
#下面是练习
#1. 显示所有员工的姓名,部门号和部门名称
SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
#2. 查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees a,departments b
WHERE a.department_id = b.department_id
AND b.department_id = 90
#3. 选择 city在Toronto工作的员工的
SELECT last_name,job_id,d.department_id,department_name
FROM employees e, departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city ='Toronto';
#4. 查询每个工种,每个部门的部门号,工种名和最低工资
SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY department_name,job_title;
#6. 查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY country_id
HAVING 部门个数 > 2
#7. 选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
SELECT e.last_name as employees, e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name="kochhar";
 
 
 

/*
应用场景：用于查询一个表中有,另一个表没有的记录
特点：
1. 外连接的查询结果为主表中所有记录
			如果从表中有和它匹配的,则显示匹配的值
			如果从表中没有和它匹配的,则显示null
			外连接查询结果 = 内连接结果+主表中有而从表没有的记录
2. 左外连接, left join 左边的是主表
	 右外连接, right join 右边的是主表
3. 左外和右外交换两个表的顺序,可以实现同样的效果
*/
#引入：查询男朋友 不在男神表的女神名
 
SELECT * FROM boys;
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id is null;
#修改
UPDATE boys SET userCP = NULL WHERE id =3;
 
# 查询编号>3 的女神的男朋友信息,如果有则列出详细,如果没有,用null填充
SELECT b.id,b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE b.id >3;
 
 
#二,sql99 语法
/*
语法：
			select 查询列表
			from 表1 别名 【连接类型】
			join 表2 别名
			on 连接条件
			【where 筛选条件】
			【group by 分组】
			【having 筛选条件】
			【order by 排序列表】
分类：
内连接（）：inner
外连接	
		左外：left 【outer】
		右外：right 【outer】
		全外: full 【outer】
交叉连接：cross join
*/
 
# 一, 内连接
/*
语法：
	select 查询列表
	from 表1 别名
	inner join 表2 别名
	on 连接条件;
	
	分类：
		等值
		非等值
		自连接
	特点：
		inner 可以省略
		筛选条件放在 where 后面, 连接条件放在on后面,提高分离
*/
 
#1. 等值连接
# 案例1.查询员工名,部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
#案例2：查询名字中包含e的员工名和工种名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE e.last_name LIKE '%e%';
#3. 查询部门个数>3的城市名和部门个数
SELECT city,count(*) 个数
FROM departments e
INNER JOIN locations d
ON e.location_id = d.location_id
GROUP BY city
HAVING 个数>3;
# 查询那个部门的员工个数>3的部门名和员工个数,并按个数降序
SELECT count(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name
HAVING 个数>3
ORDER BY 个数 DESC;
# 5.查询员工名,部门名,工种名,并按部门降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;
 
#二, 非等值连接
# 查询员工的工资级别
SELECT salary,grade_levenl
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
# 查询工资级别的个数>20的个数,并且按工资级别降序
SELECT count(*) 个数,grade_levenl
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_levenl
HAVING 个数>20
ORDER BY grade_levenl DESC;
 
#三 子链接
# 查询姓名中包含字符k的员工的名字,上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.manager_id
WHERE e.last_name LIKE '%k%';
 
#二, 外连接
#案例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 city,d.*
FROM departments d
RIGHT OUTER JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_id IS NULL;
# 查询部门名为 SAL 或IT的员工信息
SELECT e.*,d.department_name
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE d.department_name IN ('SAL','IT');
 
 

进阶07 子查询

/*
进阶7：子查询
含义：
	出现在其他语句中的select语句,称为子查询或内查询
	外部的查询语句,称为主查询或外查询
	
	分类：
	按子查询出现的位置：
				select后面：
							仅仅支持标量子查询
						
				from后面：
						支持表子查询 
				where或having后面
						标量子查询 ***重点（单行）
						列子查询  ***重点 （多行）
						行字查询
						
				exists后面（相关子查询）
						表子查询
	按结果集的行列数不同：
				标量子查询（结果集只有一行一列）
				列子查询（结果集只有一列多行）
				行子查询（结果集有一行多列）
				表子查询（结果集一般为多行多列）
*/
 
# 一, where或having后面
# 1.标量子查询（单行子查询）
# 2.列子查询（多行子查询）
# 3.行子查询（多列多行）
/*
特点
1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 标量子查询, 一般搭配着行操作符使用
 > < >= <= = <>
 列子查询,一般搭配着多行操作符使用
 in,any/some,all
 
 4. 子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
*/
#1. 标量子查询
#案例：谁的工资比Abel 高？
SELECT salary
FROM employees
WHERE last_name ='Abel';
# 查询员工的信息, 满足 salary>结果
SELECT * 
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name ='Abel'
);
#案例2：返回job_id与141号员工相同, salary比143号员工多的员工 姓名, job_id 和工资
#1.查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
#2. 查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
#3. 查询员工的姓名,job_id 和工资,要求job_id=1并且salary>2
SELECT last_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 = 143
);
#案例3：返回公司工资最少的员工的last_name,job_id 和 salary
#1. 查询公司的 最低工资
SELECT MIN(salary)
FROM employees 
#2. 查询 last_name,job_id 和 salary, 要求salary = 1
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees 
)
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#1. 查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
#2. 查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
#3. 筛选2 , 满足 min（salary）>1
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
 
#2. 列子查询
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#1. 查询location_id 是1400或1700的部门编号
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
#2. 查询员工姓名,要求部门号是1列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例2：返回某它工种中比job_id为'IT_PROG'部门任一工资低的员工的员工号,姓名,job_id 以及salary
#查询job_id为'IT_PROG'部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id ='IT_PROG'
#2. 查询员工号,姓名,job_id 以及salary, salary<1 的任意一个
SELECT last_name,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';
#用MAX也行
WHERE salary<(
SELECT DISTINCT MAX(salary)
FROM employees
WHERE job_id ='IT_PROG'
) AND job_id <> 'IT_PROG';
 
#案例3：返回其它部门中比job_id为 'IT_PROG'部门所有工资都低于的员工 的员工号,姓名,job_id以及salary
#也可以用min
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id ='IT_PROG'
) AND job_id <> 'IT_PROG';
 
#三, 行子查询 （结果集一行多列或多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
#1. 查询最小的员工编号
SELECT MIN(employee_id)
FROM employees
#2. 查询最高工资
SELECT MAX(salary)
FROM employees
#查询员工信息
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
#案例：查询每个部门的员工个数
SELECT d.*,(
		SELECT COUNT(*)
		FROM employees e
		WHERE e.department_id = d.department_id
) 个数
FROM departments d;
 
#案例2：查询员工号=102的部门号
SELECT (
		SELECT department_name
		FROM departments d
		INNER JOIN employees e
		ON d.department_id = e.department_id
		WHERE e.employee_id = 102
) 部门名;
 
#三, from 后面
#案例：查询每个部门的平均工资的工资等级
#1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2连接1的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.grade_levenl
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后面(相关子查询)
/*
语法：
exists（完整的查询语句）
结果 
1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=30000);
#案例1：查询员工的和部门名 满足条件就能查询出来
SELECT department_name
FROM departments d
WHERE EXISTS(
		SELECT * 
		FROM employees e
		WHERE d.department_id = e.department_id
);
# 用 in 实现
SELECT department_name
FROM departments d
WHERE d.department_id IN(
		SELECT department_id
		FROM employees
);
 
 

练习dom

#1. 查询和Zlotkey相同部门的员工姓名和工资
#1.查询Zlotkey的部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';
#2. 查询部门号=1的姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
)
#2. 查询工资比公司平均工资高的员工的员工号,姓名和工资
#1. 查询平均工资
SELECT AVG(salary)
FROM employees
#2.查询工资>1
SELECT salary,last_name,employee_id
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
)
#3. 查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
#1. 查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2.连接1 结果集和employees表,进行筛选
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;
#4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#1. 查询姓名中包含字母u的员工的部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
#2. 查询部门号=1中任意一个的员工号和姓名
SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);
#5. 查询在部门的location_id为1700的部门工作的员工的员工号
SELECT department_id
FROM departments
WHERE location_id = 1700
#2. 
SELECT employee_id
FROM employees
WHERE department_id = ANY(
SELECT department_id
FROM departments
WHERE location_id = 1700
);
#6. 查询管理者是K_ing的员工姓名和工资
#1. 查询姓名为K_ing的员工编号
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing'
#2. 查询那个员工的manager_id = 1
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing'
);
#7. 查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为 姓名
#查询最高工资
SELECT MAX(salary),last_name
FROM employees
#查询工资=1的姓名
SELECT CONCAT(first_name,last_name) 姓名
FROM employees 
WHERE salary =(
SELECT MAX(salary)
FROM employees
)

进阶08  分页查询

/* 进阶8：分页查询
应用场景：当要显示的数据, 一页显示不全,需要分页提交sql请求
语法：
SELECT 查询列表
from 表
offset 要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
		1.公式 当你不知道起始页数时
		要显示的页数 page, 每页的条目数 size
		select 查询列表
		from 表
		limit (page-1)*size,size;
		size=10
		page   
		1     0
		2     10
		3     20
*/
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5; 
SELECT * FROM employees LIMIT 5; 
#案例2：查询第11条---第25条
SELECT * FROM employees LIMIT 10,15; 
#案例3：有奖金的员工信息,并且工资较高的前10名显示出来
SELECT * from employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;
 
#做题
#1. 查询工资最低的员工信息：last_name,salary
SELECT MIN(salary)
FROM employees;
#2
SELECT last_name,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
)
#2. 查询平均工资最低的部门信息和该部门的平均工资
#1. 各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2. 求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#3. 查询部门信息
SELECT d.*,ag
FROM departments d
JOIN (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary)
		LIMIT 1
) ag_dep
ON d.department_id = ag_dep.department_id;
#3. 查询平均工资最高的 job 信息
#1.查询每个job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
#2. 查询job_id 信息
SELECT * 
FROM jobs
WHERE job_id =(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
#5. 查询平均工资高于公司平均工资的部门有哪些
#1. 查询平均工资
SELECT AVG(salary)
FROM employees
#2. 查询每个部门的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id
#3. 筛选2结果集,满足平均工资>1
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) >(
SELECT AVG(salary)
FROM employees
);
# 6. 查询出公司中所有 manager 的详细信息.
# 查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees
# 查询详细信息,满足 employee_id = 1
SELECT *
FROM employees
WHERE employee_id = ANY(
SELECT DISTINCT manager_id
FROM employees
);
# 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
#1. 查询各部门的最高工资 中最低的那个
SELECT MAX(salary) FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
#2. 查询那个部门的最高工资 =1
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) =(
SELECT MAX(salary) FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
)
#8. 查询平均工资最高的部门的 manager 的详细信息：last_name,department_id,email,salary
#1. 查询平均工资最高的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
#2. 将employees和departments连接查询,筛选条件是1
SELECT 
	last_name,d.department_id,email,salary
FROM employees e
INNER JOIN 
		departments d ON d.manager_id = e.employee_id
WHERE 
		d.department_id=(
		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) DESC
		LIMIT 1
		);
 

下面仅限了解                因为我们是初级 啊哈哈哈

变量

#变量
/*
系统变量：
		全局变量
		会话变量
自定义变量：
		用户变量
		局部变量
*/
#一,系统变量
-- 注意：
-- 	如果是全局级别,则需要加global,如果是会话级别,则需要加session,如果不写,则默认session
-- 
-- 
/*
说明：变量由系统提供,不是用户定义,属于服务器层面
使用语法：
*/
#1.查看所有的系统变量
-- SHOW GLOBAL [SESSION] VARIABLES;
#2. 查看满足条件的部分系统变量
-- show global [session] variables like '%char%'
#3. 查看指定的某个系统变量的值
-- select @@global [SESSION]. 系统变量名
 
#4. 为某个系统变量赋值
-- 方式一：
-- set global | [session] 系统变量 = 值;
-- 
-- 方式二：
-- set @@global | [session] 系统变量名=值;
 
#1. 全局变量
#1.查看所有的全局变量
SHOW GLOBAL VARIABLES; 
#2.查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#3.查看指定的全局变量的值
SELECT @@global.autocommit;
 
 
 

常见的数据类型 

#常见的数据类型
/**
数值型：
		整型
		小数：
					定点数
					浮点数
字符型：
		较短的文本：char,varchar
		较短的文本: text,blob (较长的二进制数据)
日期型：
		
*/
 
#一,整型
/*
分类：
tinyint 1字节,smallint 2字节,mediumint 3字节,int/integer 4字节,bigint 8字节
特点：
1. 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字
2. 如果插入的数值超出了整型的范围,会报 out of range 异常, 并且插入临界值
3. 如果不设置长度,会默认有长度
长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用！
*/
 
#1.如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
				t1 int,
				t2 int UNSIGNED
)
desc tab_int;
 
INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
 
select * from tab_int;
 
 
#二,小数
/*
1. 浮点数
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)
特点：
1. M和D
M: 整数部位+小数部位
D：小数部位
如果超过范围,则插入临界值
2.
M和D都可以省略
如果是decimal, 则M默认值为10, D默认值为0
如果是float和double, 则会根据插入的数值的精度来决定精度
3.定点型
*/
 
CREATE TABLE tab_flocat(
				f1 FLOAT(5,2),
				f2 DOUBLE(5,2),
				f3 DECIMAL(5,2)
);
SELECT * FROM tab_flocat;
# (5,2) 长度不能超出5位 
INSERT INTO tab_flocat VALUES(123.45,123.45,123.45)
INSERT INTO tab_flocat VALUES(123.456,123.456,123.456)
INSERT INTO tab_flocat VALUES(1523.4,1523.4,1523.4)
 
#三, 字符型
/**
较短的文本
char
varchar
较长的文本
text
blob(较大的二进制)
*/
 
#SET
CREATE TABLE tab_set1(
		s1 SET('a','b','c','d')
)
select * from tab_set1;
INSERT INTO tab_set1 VALUES('a');
INSERT INTO tab_set1 VALUES('a,b');
INSERT INTO tab_set1 VALUES('a,b,c,d');
 
#四 日期型
/**
分类：
date只保存日期
time 只保存时间
year 只保存年
datetime 保存日期+时间
timestamp 保存日期+时间
*/
 
CREATE TABLE tab_date(
		t1 DATETIME,
		t2 TIMESTAMP
);
 
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
 
 
 
 

常见约束

#常见约束
/*
含义：一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类：六大约束
			NOT NULL：非空, 用于保证字段的值不能为空
			比如姓名,学号等
			DEFAULT: 默认, 用户保证该字段有默认值
			比如性别
			PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
			比如学号,员工编号等
			UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
			比如座位号
			FOREIGN KEY:外键,用于限制两表的关系,用于保证该字段的值必须来自于主表的关联列的值
									在从表添加外键约束,用于引用主表中某列的值
			比如学生表的专业编号,员工表的部门编号,员工表的工种编号
			
	添加约束的时机：
				1. 创建表时
				2. 修改表时
	约束的添加分类		
				列级约束
						六大约束语法上都支持,但外键约束没用效果
				表级约束
						除了非空,默认,其他的都支持
	
*/
CREATE TABLE 表明(
			字段名 字段类型 列级约束,
			字段名 字段类型,
			表级约束
)
 
# 一, 创建表时添加约束
#1. 添加列级约束
CREATE TABLE stuinfo(
			id INT PRIMARY KEY,#主键
			stuName VARCHAR(20) NOT NULL, #非空
			gender CHAR(1),
			seat INT UNIQUE,#唯一
			age INT DEFAULT 18,#默认约束
			majorId INT REFERENCES major(id)#外键
)
desc stuinfo;
CREATE TABLE major(
			id INT PRIMARY KEY,
			majorName VARCHAR(20)
		
)
 
#2. 添加表级约束
DROP TABLE IF EXISTS stuinfo;
/**
语法：在各个字段的最下面
CONSISTENT 约束名 约束类型(字段名)
*/
CREATE TABLE stuinfo(
			id INT,
			stuName VARCHAR(20), 
			gender CHAR(1),
			seat INT,
			age INT,
			majorId INT,
			CONSISTENT pk PRIMARY KEY(id),
			CONSISTENT up UNIQUE(seat),
			CONSISTENT ck CHECK(gender='男' OR gender='女'),
			CONSISTENT fk FOREIGN KEY(majorId) REFERENCES major(id)
)
 
 
 
# 二,修改表的约束
/**
1. 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2. 添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) 【外键的引用】
*/
 
 
#1. 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stunam VARCHAR(20) NOT NULL;
#2. 添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3. 添加主键
#1. 列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#2. 表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#1. 列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#2. 表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5. 添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES majorid(id);
 
#三，删除表约束
#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 FOREIGN KEY fk_stuinfo_major;
 
SHOW INDEX FROM stuinfo;
 
#标识列
/*
又称为自增长列
含义：可以不用手动的插入值,系统提供默认的序列值
特点：
	1. 标识列必须和主键搭配吗？ 不一定， 但要求是一个key
	2. 一个表可以有几个标识列？至少一个？
	3. 标识列的类型只能是数值型
	show variables like '%auto_increment%'; //自增长列
	SET auto_increment_increment =3; 可以自己设置
	
*/
#一，创建表时设置标识列
 
CREATE TABLE tb_student(
    id INT(4) PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(25) NOT NULL
    );
SELECT * FROM tb_student;
INSERT INTO tb_student VALUES(NULL,"john") #id 自增长
 
show variables like '%auto_increment%'; //自增长列
 
SET auto_increment_increment =3;

存储过程

#存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1. 提高代码的重用性
2. 简化操作
*/
#存储过程
/*
含义：一组预先编译号的sql语句的集合,理解成批处理语句
1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库服务器的连接次数,提高效率
*/
#一, 创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
		存储过程体(一组合法的sql语句)
 
END
 
注意：
	1. 参数列表包含三部分
	参数模式 参数名 参数类型
举例：
IN stuname VARCHAR(20)
 
参数模式
IN : 该参数可以作为输入,也就是该参数需要调用方法入值
OUT : 该参数可以作为输出, 也就是该参数可以作为返回值
INOUT :
 
2. 如果存储过程体仅仅只有一句话, BEGIN END 可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号。
存储过程的结尾可以使用 DELIMITER 重写设置
语法
DELIMITER 结束标记
DELIMITER $
 
#二, 调用语法
CALL 存储过程名(实参列表);
 
#1. 空参列表
#案列：插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $
 
# 设置存储过程
CREATE PROCEDURE myp1()
BEGIN  
  INSERT INTO admin(username,password)
	VALUES('join1','0000'),('lily','0000'),('jack','0000'),('roos','0000'),('tom','0000');
END
 
#调用
CALL myp1()
 
SELECT * FROM admin;
 
-- 创建存储过程
delimiter $$
create procedure proc01()
begin
	select username,password from admin;
end $$
delimiter ;
-- 调用存储过程
call proc01;
 
 

事务

#TCL
/*
 Transaction Control Language 事务控制语言
	
	事务：
	一个或者一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行
	案列：转账
	
	张三丰 	1000
	郭襄		1000
	
	显式事务：事务具有明显的开启和结束的标记
	前提：必须先设置自动提交功能为禁用
	
	set autocommit =0;
	步骤1：开启事务
	set autocommit =0;
	start transaction;可选的
	步骤2：编写事务中的sql语句(select insert update delete)
	语句1;
	语句2;
	.....
	步骤3：结束事务
	commit; 提交事务
	rollback; 回滚事务
	
	开启事务的语句
	update 表 set 张三丰的余额=500 where name='张三丰'
	意外
	update 表 set 郭襄的余额 =1500 where name='郭襄'
	结束事务的语句
	
	事务的特征：
	ACID
	原子性：一个事务不可再分割,要么都执行要么都不执行
	一致性：一个事务执行会使数据从一个一致状态切换到另一个一致状态
	隔离性：一个事务的执行不受其他事务的干扰
	持久性：一个事务一旦提交,则会永久的改变数据库的
	事务的创建
	隐式事务：事务没有明显的开启和结束的标记
	比如 insert,update,delete语句
	
	delete from 表 where id="";
*/
 
show engines; 
 
show variables like 'autocommit'; #查看变量
 
#演示事务的使用步骤
 
CREATE TABLE account(
		id INT PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(20),
		balance DOUBLE
)
 
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);
 
select * from account;
 
#开启事务
SET autocommit =0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=1000 WHERE username='张无忌';
UPDATE account SET balance=1000 WHERE username='赵敏';
 
 
ROLLBACK;#回滚事务
COMMIT;#结束事务
 
SELECT * FROM account;
 
#3. 演示savepoint 的使用
SET autocommit =0;
START TRANSACTION;
DELETE FROM account WHERE id=2;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=4;
ROLLBACK TO a;#回滚到保存点
 
 
#演示delete
SET autocommit =0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
 
SELECT * FROM account;
 
#演示truncate 删除 不能回滚
SET autocommit =0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

 视图

#视图
/*
含义：虚拟表,和普通表一样使用
mysql 5.1 
	
*/
 
#案列：查询性张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m NO s.majorid = m.id
WHERE s.stuname LIKE '张%';
 
#视图封装
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m NO s.majorid = m.id
 
SELECT * FROM v1 WHERE stuname LIKE '张%';
 
#一,创建视图
/*
语法：
	create view 视图名
	as
	查询语句;
*/
 
#二, 视图的修改
# 方式一
/*
create or replace view 视图名
as
查询语句：
跟 insert into 表明
查询语句一样的
*/
 
CREATE TABLE employees(
			id int,
			last_name varchar(50),
			email varchar(50),
			
)
 
#视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
 
select * from myv1;
 
#1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com')
 
#2.修改
UPDATE myv1 SET last_name = '张无忌' where Last_name ='张飞';
 
#3.删除
DELETE FROM myv1 WHERE last_name ='张无忌';
 
#具备以下特点的视图不允许更新
#1.包含以下关键字的sql语句：分组函数 distinct,group by,having,union 或者 union all
 
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id;
 
#更新
select * from myv1;