mqsql练习

image.png
  1. 下面的语句是否可以执行成功
    select last_name , job_id , salary as sal
    from employees;
  2. 下面的语句是否可以执行成功
    select * from employees;
  3. 找出下面语句中的错误
    select employee_id , last_name，
    salary * 12 “ANNUAL SALARY”
    from employees;
  4. 显示表 departments 的结构，并查询其中的全部数据
  5. 显示出表 employees 中的全部 job_id（不能重复）
  6. 显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT
SELECT CONCAT(`employee_id`,",",`first_name`,",",`email`,",",`phone_number`,",",`job_id`,",",`salary`,",",
IFNULL(`commission_pct`,""),",",IFNULL(`manager_id`,""),",",`department_id`,",",`hiredate`) AS OUT_PUT FROM `employees`

#ifnull(表达式1，表达式2)
/*
表达式1：可能为null的字段或表达式
表达式2：如果表达式1为null，则最终结果显示的值

功能：如果表达式1为null，则显示表达式2，否则显示表达式1

*/

条件查询

演示

#一、按关系表达式筛选
#案例1：查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id <> 100;


#案例2：查询工资<15000的姓名、工资
SELECT last_name,salary
FROM employees
WHERE salary<15000;


#二、按逻辑表达式筛选

#案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
#方式1：
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;

#方式2：


SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);



#案例2：查询奖金率>0.03 或者 员工编号在60-110之间的员工信息
SELECT *
FROM employees
WHERE commission_pct>0.03 OR (employee_id >=60 AND employee_id<=110);


#三、模糊查询

#1、like

/*
功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询
常见的通配符：
_ 任意单个字符
% 任意多个字符,支持0-多个
like/not like 
*/

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

#案例2：查询姓名中包含最后一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%e';

#案例3：查询姓名中包含第一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE 'e%';

#案例4：查询姓名中包含第三个字符为x的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '__x%';

#案例5：查询姓名中包含第二个字符为_的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '_\_%';

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';


#2、in
/*
功能：查询某字段的值是否属于指定的列表之内

a  in(常量值1,常量值2,常量值3,...)
a not in(常量值1,常量值2,常量值3,...)

in/not in
*/

#案例1：查询部门编号是30/50/90的员工名、部门编号


#方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

#方式2：

SELECT last_name,department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;


#案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息
#方式1：
SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');

#方式2：
SELECT *
FROM employees
WHERE NOT(job_id ='SH_CLERK'
OR job_id = 'IT_PROG');


#3、between and

/*
功能：判断某个字段的值是否介于xx之间

between and/not between and

*/


#案例1：查询部门编号是30-90之间的部门编号、员工姓名

#方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

#方式2：

SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;


#案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪

SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))<100000 OR salary*12*(1+IFNULL(commission_pct,0))>200000;



SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;



#4、is null/is not null

#案例1：查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;



#案例2：查询有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;


SELECT *
FROM employees
WHERE salary IS 10000;

#----------------对比------------------------------------

=       只能判断普通的内容

IS              只能判断NULL值

<=>             安全等于，既能判断普通内容，又能判断NULL值




SELECT *
FROM employees
WHERE salary <=> 10000;

SELECT *
FROM employees
WHERE commission_pct <=> NULL;

作业

  1. 查询工资大于 12000 的员工姓名和工资
  2. 查询员工号为 176 的员工的姓名和部门号和年薪
  3. 选择工资不在 5000 到 12000 的员工的姓名和工资
  4. 选择在 20 或 50 号部门工作的员工姓名和部门号
  5. 选择公司中没有管理者的员工姓名及 job_id
  6. 选择公司中有奖金的员工姓名，工资和奖金级别
  7. 选择员工姓名的第三个字母是 a 的员工姓名
  8. 选择姓名中有字母 a 和 e 的员工姓名
  9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
  10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
  11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
1. 查询工资大于 12000 的员工姓名和工资
SELECT `last_name`,`salary` FROM employees WHERE salary > 12000; 
2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT `last_name`,`department_id`,`salary`*12*(1+ IFNULL(`commission_pct`, 0)) AS 年薪 
FROM employees WHERE employees_id = 176;
3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT `last_name`,`salary` FROM employees salary NOT BETWEEN 5000 AND 12000;
4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT `last_name`,`department_id` FROM employees `department_id` IN(20,50);
5. 选择公司中没有管理者的员工姓名及 job_id
SELECT `last_name`,`job_id` FROM employees WHERE `manager_id` IS NULL;
6. 选择公司中有奖金的员工姓名，工资和奖金级别
SELECT `last_name`,`salary`,`commission_pct` FROM employees`commission_pct` IS NOT NULL;
7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT * FROM employees WHERE last_name LIKE "__a%"
8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT last_name FROM employees WHERE last_name LIKE "%a%" AND last_name LIKE "%e%";
9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT *  FROM employees WHERE first_name LIKE "%e";
10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT `last_name`,`job_id` FROM employees WHERE `department_id` BETWEEN 80 AND 100;
11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT `last_name`,`job_id` FROM
`employees` 
 WHERE `manager_id` IN (100,101,110);

排序查询

特点：

1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合**
2、升序 ，通过 asc ，默认行为
降序 ，通过 desc

演示

#一、按单个字段排序
#案例1：将员工编号>120的员工信息进行工资的升序
SELECT * 
FROM employees 

ORDER BY salary ;

#案例1：将员工编号>120的员工信息进行工资的降序
SELECT * 
FROM employees 
WHERE employee_id>120 
ORDER BY salary DESC;

#二、按表达式排序
#案例1：对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;


#三、按别名排序
#案例1：对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees

ORDER BY 年薪 DESC;

#四、按函数的结果排序

#案例1：按姓名的字数长度进行升序


SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);


#五、按多个字段排序

#案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序

SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;


#六、补充选学：按列数排序


SELECT * FROM employees 
ORDER BY 2 DESC;  # 按第2列排


SELECT * FROM employees 
ORDER BY first_name;

作业

  1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序

  2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

  3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

# 1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name, `department_id`, salary*12*(1+ IFNULL(commission_pct,0)) 年薪 
FROM employees 
ORDER BY 年薪 DESC,last_name ASC;
#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY 
salary DESC;
#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees WHERE email LIKE "%e%" ORDER BY LENGTH(email) DESC, `department_id`;

函数

image.png
image.png
#进阶4：常见函数
/*
函数：类似于java中学过的“方法”，
为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露方法名，供外部调用

1、自定义方法(函数)
2、调用方法(函数)★
    叫什么  ：函数名
    干什么  ：函数功能
    

1》字符函数

concat(str1,str2,..):拼接字符

substr(str,pos):截取从pos开始的所有字符，   起始索引从1开始

substr(str,pos,len):截取len个从pos开始的字符，起始索引从1开始

length(str):获取字节个数

char_length(str):获取字符个数

upper(str):变大写

lower(str):变小写

trim(【substr from】str):去前后指定字符，默认是去空格

left(str,len):从左边截取指定len个数的 字符

right(str,len):从右边截取指定len个数的 字符

lpad(str,substr,len):左填充

rpad(str,substr,len)：右填充

strcmp(str1,str2):比较两个字符的大小

instr(str,substr):获取substr在str中第一次出现的索引

2》数学函数

ceil(x):向上取整

floor(x):向下取整

round(x,d):四舍五入

mod(x,y):取模/取余

truncate(x,d):截断，保留小数点后d位

abs(x):求绝对值



3》日期函数

now():获取当前日期——时间

curtime():只有时间

curdate():只有日期

date_format(date,格式):格式日期为字符

str_to_date(str,格式):将字符转换成日期

datediff(date1,date2):获取两个日期之间的天数差

year(date)

month(date)

...



**4》流程控制函数**

①if(条件，表达式1，表达式2)：如果条件成立，返回表达式1，否则返回表达式2

②case 表达式

when 值1 then 结果1

when 值2 then 结果2

...

else 结果n

end

**③**

**case** 

when 条件1 then 结果1

when 条件2 then 结果2

...

else 结果n

end


*/

#一、字符函数
1、CONCAT 拼接字符

SELECT CONCAT('hello,',first_name,last_name)  备注 FROM employees;

2、LENGTH 获取字节长度

SELECT LENGTH('hello,郭襄');

3、CHAR_LENGTH 获取字符个数
SELECT CHAR_LENGTH('hello,郭襄');

4、SUBSTRING 截取子串
/*
注意：起始索引从1开始！！！
substr(str,起始索引，截取的字符长度)
substr(str,起始索引)
*/
`employees`
SELECT SUBSTR('张三丰爱上了郭襄',7);


5、INSTR获取字符第一次出现的索引

SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');

6、TRIM去前后指定的字符，默认是去空格


SELECT TRIM(' 虚  竹    ')  AS a;
SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx')  AS a;

7、LPAD/RPAD  左填充/右填充
SELECT LPAD('木婉清',10,'a');
SELECT RPAD('木婉清',10,'a');

8、UPPER/LOWER  变大写/变小写

#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”


SELECT UPPER(SUBSTR(first_name,1,1)),first_name FROM employees;
SELECT LOWER(SUBSTR(first_name,2)),first_name FROM employees;
SELECT UPPER(last_name) FROM employees;

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) "OUTPUT"
FROM employees;

9、STRCMP 比较两个字符大小
一样大返回为0 前者大为1 前者小为-1
SELECT STRCMP('bec','ceb');


10、LEFT/RIGHT  截取子串
SELECT LEFT('鸠摩智',1);
SELECT RIGHT('鸠摩智',1);


#二、数学函数

1、ABS 绝对值
SELECT ABS(-2.4);
2、CEIL 向上取整  返回>=该参数的最小整数
SELECT CEIL(-1.09);
SELECT CEIL(0.09);
SELECT CEIL(1.00);

3、FLOOR 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);
SELECT FLOOR(1.00);

4、ROUND 四舍五入
SELECT ROUND(1.8712345);
SELECT ROUND(1.8712345,2);

5、TRUNCATE 截断

SELECT TRUNCATE(1.8712345,1);

6、MOD 取余

SELECT MOD(-10,3);
a%b = a-(INT)a/b*b
-10%3 = -10 - (-10)/3*3   = -1

SELECT -10%3;
SELECT 10%3;
SELECT -10%-3;
SELECT 10%-3;


#三、日期函数


1、NOW  当前日期和时间
SELECT NOW();

2、CURDATE 当前日期

SELECT CURDATE();

3、CURTIME 当前时间
SELECT CURTIME();


4、DATEDIFF 获取两个日期只差是天数
SELECT DATEDIFF('1998-7-16','2019-7-13');

5、DATE_FORMAT(DATETIME,FORMAT)

SELECT DATE_FORMAT('1998-7-16','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期;



SELECT DATE_FORMAT(hiredate,'%Y年%m月%d日 %H小时%i分钟%s秒')入职日期 
FROM employees;



6、STR_TO_DATE 按指定格式解析字符串为日期类型
SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');



#四、流程控制函数


1、IF函数
IF(表达式,真返回，假返回)
SELECT IF(100>9,'好','坏'); # 如果100>9 结果为好 不成立为 坏


#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct)  奖金,commission_pct
FROM employees;



2、CASE函数

①情况1 ：类似于switch语句，可以实现等值判断
CASE 表达式         
WHEN 值1 THEN 结果1 #如果表达式满足值1，then 结果为 结果1
WHEN 值2 THEN 结果2 #如果表达式满足值2，then 结果为 结果2
...
ELSE 结果n
END


案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变

显示 部门编号，新工资，旧工资

SELECT department_id,salary,
CASE department_id 
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END '新工资'
FROM employees;


②情况2：类似于多重IF语句，实现区间判断
CASE 
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...

ELSE 结果n

END



案例：如果工资>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  a
 FROM employees;

作业

  1. 显示系统时间(注：日期+时间)
  2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
  3. 将员工的姓名按 首字母排序，并写出姓名的长度（length）
  4. 做一个查询，产生下面的结果
    <last_name> earns <salary> monthly but wants <salary*3>
    Dream Salary
    King earns 24000 monthly but wants 72000

  5. 使用 case-when，按照下面的条件：
    job grade
    AD_PRES A
    ST_MAN B
    IT_PROG C
    SA_REP D
    ST_CLERK E
    产生下面的结果


    image.png

#1. 显示系统时间(注：日期+时间)
SELECT NOW();

#2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）

SELECT employee_id,last_name,salary,salary*1.2 "new salary"
FROM employees;

#3. 将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT LENGTH(last_name) 长度
FROM employees
ORDER BY SUBSTR(last_name,1,1) ASC;


#4. 做一个查询，产生下面的结果
/*
<last_name> earns <salary> monthly but wants <salary*3>
          Dream Salary
King earns 24000 monthly but wants 72000
*/


SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3)  "Dream Salary"
FROM employees;




#5. 使用 case-when，按照下面的条件：
/*
job     grade
AD_PRES  A
ST_MAN   B
IT_PROG  C
SA_REP   D
ST_CLERK E



产生下面的结果
Last_name Job_id    Grade
king      AD_PRES   A

*/


SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES'  THEN 'A'
WHEN 'ST_MAN'  THEN 'B'
WHEN 'IT_PROG'  THEN 'C'
WHEN 'SA_REP'  THEN 'D'
WHEN 'ST_CLERK'  THEN 'E'
END Grade

FROM employees;

分组函数

演示

#进阶5：分组函数
/*

说明：分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数

分组函数清单：

sum(字段名)：求和
avg(字段名)：求平均数
max(字段名)：求最大值
min(字段名)：求最小值
count(字段名)：计算非空字段值的个数

*/

#案例1 ：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数

SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;

#案例2：添加筛选条件
   #①查询emp表中记录数：
    SELECT COUNT(employee_id) FROM employees;

   #②查询emp表中有佣金的人数：
    
    SELECT COUNT(salary) FROM employees;
    
    
   #③查询emp表中月薪大于2500的人数：
    SELECT COUNT(salary) FROM employees WHERE salary>2500;

   
   #④查询有领导的人数：
    SELECT COUNT(manager_id) FROM employees;
    
    
#count的补充介绍★


#1、统计结果集的行数，推荐使用count(*)
    
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 30;


SELECT COUNT(1) FROM employees;
SELECT COUNT(1) FROM employees WHERE department_id = 30;


#2、搭配distinct实现去重的统计

#需求：查询有员工的部门个数

SELECT COUNT(DISTINCT department_id) FROM employees;


#思考：每个部门的总工资、平均工资？

SELECT SUM(salary)  FROM employees WHERE department_id = 30;
SELECT SUM(salary)  FROM employees WHERE department_id = 50;


SELECT SUM(salary) ,department_id
FROM employees
GROUP BY department_id;

分组查询

语法：

select 查询列表
from 表名
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;

特点：
①查询列表往往是 分组函数和被分组的字段 ★
②分组查询中的筛选分为两类

            筛选的基表   使用的关键词      位置

分组前筛选 原始表 where group by 的前面

分组后筛选 分组后的结果集 having group by的后面

where——group by ——having

问题：分组函数做条件只可能放在having后面！！

#进阶6：分组查询
/*
语法：

select 查询列表
from 表名
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;

执行顺序：
①from子句
②where子句
③group by 子句
④having子句
⑤select子句
⑥order by子句






特点：
①查询列表往往是  分组函数和被分组的字段 ★
②分组查询中的筛选分为两类
            筛选的基表   使用的关键词      位置
分组前筛选       原始表     where           group by 的前面

分组后筛选       分组后的结果集  having         group by的后面

where——group by ——having

问题：分组函数做条件只可能放在having后面！！！

*/


#1）简单的分组
#案例1：查询每个工种的员工平均工资

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个领导的手下人数

SELECT COUNT(*),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;





#2）可以实现分组前的筛选
#案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary) 最高工资,department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;


#案例2：查询每个领导手下有奖金的员工的平均工资
SELECT AVG(salary) 平均工资,manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;


#3）可以实现分组后的筛选
#案例1：查询哪个部门的员工个数>5
#分析1：查询每个部门的员工个数
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id

#分析2：在刚才的结果基础上，筛选哪个部门的员工个数>5

SELECT COUNT(*) 员工个数,department_id
FROM employees

GROUP BY department_id
HAVING  COUNT(*)>5;


#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct  IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;


#案例3：领导编号>102的    每个领导手下的最低工资大于5000的最低工资
#分析1：查询每个领导手下员工的最低工资
SELECT MIN(salary) 最低工资,manager_id
FROM employees
GROUP BY manager_id;

#分析2：筛选刚才1的结果
SELECT MIN(salary) 最低工资,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000 ;




#4）可以实现排序
#案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
#分析1：按工种分组，查询每个工种有奖金的员工的最高工资
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS  NULL
GROUP BY job_id


#分析2：筛选刚才的结果，看哪个最高工资>6000
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS  NULL
GROUP BY job_id
HAVING MAX(salary)>6000


#分析3：按最高工资升序
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS  NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;


#5）按多个字段分组
#案例：查询每个工种每个部门的最低工资,并按最低工资降序
#提示：工种和部门都一样，才是一组

工种  部门  工资
1   10  10000
1       20      2000
2   20
3       20
1       10
2       30
2       20


SELECT MIN(salary) 最低工资,job_id,department_id
FROM employees
GROUP BY job_id,department_id;

作业

SELECT MIN(salary),MAX(salary),AVG(salary),SUM(salary)
FROM employees

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) 天数 
FROM employees


SELECT COUNT(employee_id) FROM employees
WHERE department_id =90;

连接查询

笛卡尔乘积

语法:
select 查询列表
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表

一）等值连接

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

#案例1：查询女神名和对应的男神名
SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

#案例2：查询员工名和对应的部门名

SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;



#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

*/
#查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;


#3、两个表的顺序是否可以调换

#查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;


#4、可以加筛选


#案例：查询有奖金的员工名、部门名

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 city LIKE '_o%';

#5、可以加分组


#案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;


#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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`;
#6、可以加排序


#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;




#7、可以实现三表连接？

#案例：查询员工名、部门名和所在的城市

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;

二）非等值连接

执行下面的创建sql语句

CREATE TABLE job_grades
(grade_level 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);

案例1：查询员工的工资和工资级别


SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

内连接

语法：

SELECT 查询列表
FROM 表名1 别名
【INNER】 JOIN 表名2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表;

一）等值连接

#一）等值连接
#①简单连接
#案例：查询员工名和部门名

SELECT last_name,department_name
FROM departments d 
 JOIN  employees e 
ON e.department_id =d.department_id;



#②添加筛选条件
#案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;


#③添加分组+筛选
#案例1：查询每个城市的部门个数

SELECT COUNT(*) 部门个数,l.`city`
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`;




#④添加分组+筛选+排序
#案例1：查询部门中员工个数>10的部门名，并按员工个数降序

SELECT COUNT(*) 员工个数,d.department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

二）非等值连接

#二）非等值连接

#案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT * FROM sal_grade;


SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.grade;

作业

#1.显示所有员工的姓名，部门号和部门名称。
USE myemployees;

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 e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;



#3. 选择所有有奖金的员工的
last_name , department_name , location_id , city


SELECT last_name , department_name , l.location_id , city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;


#4.选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 

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';



#5.查询每个工种、每个部门的部门名、工种名和最低工资


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、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式。自连接
employees   Emp#    manager Mgr#
kochhar     101 king    100


SELECT e.last_name 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';

一、查询员工姓名、入职日期并按入职日期升序

select last_name,hiredate
from employees
order by hiredate asc;



date_formate(hiredate,'%Y')

year(hiredate)
month(hiredate)
day(hiredate)
hour(hiredate)
minute(hiredate)
second(hiredate)



二、将当前日期显示成 xxxx年xx月xx日


select date_format(now(),'%Y年%m月%d日');


三、
已知学员信息表stuinfo
    stuId
    stuName 
    gender 
    majorId

已知专业表major
    id  
    majorName
已知成绩表result
    id成绩编号
    majorid
    stuid
    score


1、查询所有男生的姓名、专业名和成绩，使用SQL92和SQL99两种语法方式实现


SQL92

select stuname,majorname,score
from stuinfo s,major m,result r
where s.majorid = m.id and r.stuid = s.stuid
and s.gender = '男';



SQL99

select stuname,majorname,score
from stuinfo s

join major m on s.majorid = m.id 
join result r on r.stuid = s.stuid
where s.gender = '男';



2、查询每个性别的每个专业的平均成绩，并按平均成绩降序



select avg(score) 平均成绩,gendeer,s.majorid
from stuinfo s
join result r on s.stuid = r.stuid
group by gender,s.majorid
order by 平均成绩 desc;

外连接

说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null

应用场景：一般用于查询主表中有但从表没有的记录

特点：

1、外连接分主从表，两表的顺序不能任意调换
2、左连接的话，left join左边为主表
右连接的话，right join右边为主表

语法：

select 查询列表
from 表1 别名
left|right|full 【outer】 join 表2 别名
on 连接条件
where 筛选条件;

image.png

image.png

演示

USE girls;
#案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null

#左连接
SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

#右连接
SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`;



#案例2：查哪个女神没有男朋友

#左连接
SELECT b.`name`
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

#右连接
SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;


#案例3：查询哪个部门没有员工，并显示其部门编号和部门名,没有员工的部门个数

SELECT COUNT(*) 部门个数
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

作业

#一、查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充
4   小红     大飞
5   小白     大黄
6   小绿     NULL



SELECT b.id,b.name,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id
WHERE b.id>3;



#二、查询哪个城市没有部门

SELECT l.city
FROM departments d
RIGHT JOIN locations l ON l.location_id = d.location_id
WHERE d.`department_id` IS NULL;


#三、查询部门名为 SAL 或 IT 的员工信息

SELECT d.*,e.*
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`
WHERE d.`department_name` = 'SAL' OR d.`department_name`='IT';

子查询

#子查询
/*
说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询
外面的select语句称为主查询或外查询。


分类：

按子查询出现的位置进行分类：

1、select后面
    要求：子查询的结果为单行单列（标量子查询）
2、from后面
    要求：子查询的结果可以为多行多列
3、where或having后面 ★
    要求：子查询的结果必须为单列
        单行子查询
        多行子查询
4、exists后面
    要求：子查询结果必须为单列（相关子查询）
    
特点：
    1、子查询放在条件中，要求必须放在条件的右侧
    2、子查询一般放在小括号中
    3、子查询的执行优先于主查询
    4、单行子查询对应了 单行操作符：> < >= <= = <>
       多行子查询对应了 多行操作符：any/some  all in   



*/
#一、放在where或having后面
#一）单行子查询

#案例1：谁的工资比 Abel 高?


#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name  = 'Abel'
#②查询salary>①的员工信息
SELECT last_name,salary
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name  = 'Abel'

);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

#②查询143号员工的salary

SELECT salary
FROM employees
WHERE employee_id = 143

#③查询job_id=① and salary>②的信息
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

#①查询最低工资
SELECT MIN(salary)
FROM employees

#②查询salary=①的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees

);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50


#②查询各部门的最低工资，筛选看哪个部门的最低工资>①

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(

    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);


#二）多行子查询
/*

in:判断某字段是否在指定列表内  
x in(10,30,50)


any/some:判断某字段的值是否满足其中任意一个

x>any(10,30,50)
x>min()

x=any(10,30,50)
x in(10,30,50)


all:判断某字段的值是否满足里面所有的

x >all(10,30,50)
x >max()

*/


#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#①查询location_id是1400或1700的部门
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)


#②查询department_id = ①的姓名
SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)

);



# 连接查询


#题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

#①查询job_id为‘IT_PROG’部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'


#②查询其他部门的工资<任意一个①的结果

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(

    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'


);



等价于

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(

    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'


);




#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary

#①查询job_id为‘IT_PROG’部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'


#②查询其他部门的工资<所有①的结果

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(

    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'


);



等价于

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(

    SELECT MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'


);


#二、放在select后面

#案例；查询部门编号是50的员工个数

SELECT 
(
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 50
)  个数;


#三、放在from后面

#案例：查询每个部门的平均工资的工资级别
#①查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id



#②将①和sal_grade两表连接查询

SELECT dep_ag.department_id,dep_ag.ag,g.grade
FROM sal_grade g
JOIN (

    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id

) dep_ag ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;


#四、放在exists后面



#案例1 ：查询有无名字叫“张三丰”的员工信息
SELECT EXISTS(
    SELECT * 
    FROM employees
    WHERE last_name = 'Abel'

) 有无Abel;


#案例2：查询没有女朋友的男神信息

USE girls;

SELECT bo.*
FROM boys bo
WHERE bo.`id` NOT IN(
    SELECT boyfriend_id
    FROM beauty b
)



SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty b
    WHERE bo.id = b.boyfriend_id
);


#1. 查询和 Zlotkey 相同部门的员工姓名和工资


#①查询Zlotkey的部门编号
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'

#②查询department_id = ①的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey

);


#2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。

#①查询平均工资
    
SELECT AVG(salary)
FROM employees

#②查询salary>①的信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
    SELECT AVG(salary)
    FROM employees
);

#3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

#①查询各部门的平均工资
    
SELECT department_id,AVG(salary) ag
FROM employees
GROUP BY department_id

#②将①结果和employees表连接查询

SELECT employee_id,last_name,salary
FROM employees e
JOIN  (
    SELECT department_id,AVG(salary) ag
    FROM employees
    GROUP BY department_id

) dep_ag ON e.department_id = dep_ag.department_id
WHERE e.salary>dep_ag.ag;


#4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名

#①查询姓名中包含字母 u 的员工的部门编号
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'

#②查询部门号是①的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'

);



#6. 查询管理者是 King 的员工姓名和工资


#①查询管理者是king的编号
SELECT employee_id
FROM employees
WHERE last_name  = 'k_ing'

#②查询哪个员工的领导编号是①
SELECT last_name,salary
FROM employees
WHERE manager_id  IN(
    SELECT employee_id
    FROM employees
    WHERE last_name  = 'k_ing'
);
#3. 查询平均工资最低的部门信息和该部门的平均工资

#①查询各部门的平均工资
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id



#②查询哪个部门的平均工资最低
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY ag 
LIMIT 1

#③连接②和departments表

SELECT d.*,dep_ag.ag
FROM departments d
JOIN (

    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
    ORDER BY ag 
    LIMIT 1

) dep_ag ON d.department_id = dep_ag.department_id

#---------内连接去做-------------

SELECT AVG(salary),d.`department_name`,d.`department_id`
FROM employees e
JOIN `departments` d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_id`
ORDER BY AVG(salary)
LIMIT 1


#7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
#①查询各部门的最高工资
SELECT MAX(salary) mx,department_id
FROM employees
GROUP BY department_id

#②查询各部门的最高工资最低的那个部门
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) 
LIMIT 1


#③查询部门编号是②的部门的最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) 
    LIMIT 1

);

作业

1. 查询工资最低的员工信息: last_name, salary
2. 查询平均工资最低的部门信息
3. 查询平均工资最低的部门信息和该部门的平均工资
4. 查询平均工资最高的 job 信息
5. 查询平均工资高于公司平均工资的部门有哪些?
6. 查询出公司中所有 manager 的详细信息.
7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email,salary

分页查询

语法：

select 查询列表
from 表1 别名
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 分组后筛选
order by 排序列表
limit 起始条目索引,显示的条目数

执行顺序：

1》from子句
2》join子句
3》on子句
4》where子句
5》group by子句
6》having子句
7》select子句
8》order by子句
9》limit子句

特点：
①起始条目索引如果不写，默认是0
②limit后面支持两个参数
参数1：显示的起始条目索引
参数2：条目数

公式

limit (page-1)*size,size

演示

#案例1：查询员工信息表的前5条
SELECT * FROM employees LIMIT 0,5;
#完全等价于
SELECT * FROM employees LIMIT 5;

#案例2：查询有奖金的，且工资较高的第11名到第20名
SELECT 
    * 
FROM
    employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10,10 ;

作业

1.使用分页查询实现，查询员工信息表中部门为50号的工资最低的5名员工信息
select *
from employees
where deparrment_id = 50
order by salary asc
limit 0,5;



2.使用子查询实现城市为Toroto的，且工资>10000的员工姓名

#①查询城市为Toroto的部门编号

select department_id
from departments d
join locations l on d.location_id = l.location_id
where city = 'Toroto'

#②查询部门号在①里面的员工姓名
select last_name
from employees
where salary>10000 and department_id in(

    select department_id
    from departments d
    join locations l on d.location_id = l.location_id
    where city = 'Toroto'

);






3.创建表qqinfo，里面包含qqid，添加主键约束、昵称nickname，添加唯一约束、邮箱email（添加非空约束）、性别gender


create table if not exists qqinfo(
    qqid int primary key,
    nickname varchar(20) unique,
    email varchar(20) not null,
    gender char
);

4.删除表qqinfo

drop table if exists qqinfo;

5.试写出sql查询语句的定义顺序和执行顺序

1》定义顺序（书写顺序）
select distinct  查询列表
from 表名 别名
join 表名 别名 
on 连接条件
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表
limit 条目数;


2》执行顺序

from子句
join子句
on子句
where子句
group by子句
having子句
select 子句
order by子句
limit子句

联合查询 union

#进阶9：联合查询
/*
说明：当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询

语法：
select 查询列表 from 表1  where 筛选条件  
    union
select 查询列表 from 表2  where 筛选条件  


特点：

1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2、union实现去重查询
   union all 实现全部查询，包含重复项
*/

#案例：查询所有国家的年龄>20岁的用户信息

SELECT * FROM usa WHERE uage >20 UNION
SELECT * FROM chinese WHERE age >20 ;


#案例2：查询所有国家的用户姓名和年龄

SELECT uname,uage FROM usa
UNION
SELECT age,`name` FROM chinese;


#案例3：union自动去重/union all 可以支持重复项


SELECT 1,'范冰冰' 
UNION ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' ;

事务

由一条或多条sql语句组成，要么都成功，要么都失败

image.png

特性：ACID

  • 原子性
  • 一离性
  • 持久致性
  • 隔性

分类：

隐式事务：没有明显的开启和结束标记
比如dml语句的insert、update、delete语句本身就是一条事务.因为它自动提交了

insert into stuinfo values(1,'john','男','ert@dd.com',12);

显式事务：具有明显的开启和结束标记

一般由多条sql语句组成，必须具有明显的开启和结束标记

步骤：
取消隐式事务自动开启的功能


1、开启事务
2、编写事务需要的sql语句（1条或多条）
    insert into stuinfo values(1,'john','男','ert@dd.com',12);
    insert into stuinfo values(1,'john','男','ert@dd.com',12);
3、结束事务

SHOW VARIABLES LIKE '%auto%'

演示事务的使用步骤

1、取消事务自动开启

SET autocommit = 0;

image.png

2、开启事务

START TRANSACTION;

3、编写事务的sql语句

将张三丰的钱-5000

UPDATE stuinfo SET balance=balance-5000 WHERE stuid = 1;

将灭绝的钱+5000

UPDATE stuinfo SET balance=balance+5000 WHERE stuid = 2;

4、结束事务

提交

commit;

回滚. 当有什么异常发生就回滚，try except 去捕获异常

ROLLBACK;

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 160,108评论 4赞 364
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,699评论 1赞 296
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 109,812评论 0赞 244
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,236评论 0赞 213
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,583评论 3赞 288
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,739评论 1赞 222
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,957评论 2赞 315
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,704评论 0赞 204
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,447评论 1赞 246
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,643评论 2赞 249
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,133评论 1赞 261
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,486评论 3赞 256
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,151评论 3赞 238
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,108评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,889评论 0赞 197
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,782评论 2赞 277
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,681评论 2赞 272

推荐阅读更多精彩内容

  • sqlP39-P48
    #进阶3：排序查询 /* #引入 SELECT * FROM employees 语法：执行顺序 select 查...
    kevinXiao阅读 263评论 0赞 0
  • Mysql数据库
    基础查询 select 查询列表 from 表名; USE myemployees; //最好写上 1.查询表中的...
    暧莓悠阅读 517评论 0赞 1
  • 数据库(5) | MySQL SQL 语言查询
    SQL语言基础 本章，我们将会重点探讨SQL语言基础，学习用SQL进行数据库的基本数据查询操作。另外请注意本章的S...
    厲铆兄阅读 5,232评论 2赞 46
  • MySQL笔记
    数据库的相关概念 数据库的好处 1、持久化数据到本地2、可以实现结构化查询，方便管理 数据库的常见概念☆ 1、DB...
    lzh_1阅读 565评论 0赞 3
  • MySQL基础总结
    资料参考：https://www.bilibili.com/video/BV12b411K7Zu?p=1[http...
    攻城老狮阅读 253评论 0赞 0