Python全栈开发-Mysql知识点总结(上)

最新推荐文章于 2023-03-28 18:29:01 发布

落空空。

最新推荐文章于 2023-03-28 18:29:01 发布

阅读量1.6k

点赞数 1

分类专栏： mysql 文章标签： mysql python 数据库

本文链接：https://blog.csdn.net/kc44601/article/details/121728737

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Myqsl 知识点总结

一. 数据库相关概念

DB：数据库，保存一组有组织的数据容器
DBMS：数据库管理系统又称数据库软件（产品）用于管理DB中数据
sql 结构化查询语言用于和DBS通信的语言

二. mysql 常见命令

1. 查看当前所有数据库

show databases;

2. 打开指定库

use 库名

3.查看当前库所有表

show tables;

4.查看其他库所有表

show tables from 库名;

5.创建表

create table 表名(
     列名  列类型，
     列名  列类型
     ...
);

6.查看表结构

desc 表名;

7.查看服务器版本

方式一：登录到mysql服务端

select version();

方式二：没有登陆到mysql服务端

mysql –version
mysql -V

mysql 的语法规范

1.不区分大小写，但建议关键字大写，表名，列名小写
2.每条命令用分号结尾 (有时\g)
3.每条命令根据需要，可以进行缩进或换行
4.注释
单行注释：#注释文字
单行注释：-- 注释（–后面必须有个空格）
多行注释： /注释文字/

进阶1: 基础查询

语法:

select 查询列表 from 表名;

特点：

查询列表可以是表中字段,常量值，表达式，函数
查询结果是一个虚拟表格

1.查询表中单个字段

SELECT last_name FROM employees;

2.查询表中多个字段

SELECT last_name,salary,email FROM employees;

3.查询表中所有字段

SELECT * FROM 表;  （顺序为表中顺序）

注意：
1.操作前先use该库
2.着重号``可以方便标识字段

4.查询常量值

SELECT 100;
SELECT 'john'

5.查询表达式

SELECT 100*98;

注：字符型和日期型必须用单引号

6.查询函数

SELECT VERSION();

7.起别名

#方式一（使用as）

SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二（使用空格）

SELECT first_name 姓,last_name 名 FROM employees;好处：

1.便于理解
2.如果查询的字段有重名情况，使用别名可以区分开来

案例：查询salary，显示结果为out put

SELECT salary AS "out put" FROM employees;

注意：此时out put中间有空格，建议加上双引号

8.去重（字段前加DISTINCT）

案例查询员工表中涉及到的所有部门编号

SELECT DISTINCT department_id FROM employees;

9.+号的作用

/*运算符
例select 100+90 （两个操作数为数值型，做加法）
select '123'+90  (其中一方为字符型，试图将字符型转化为数值型
                      如果转化成功，继续做加法
select 'john'+90      如果转化失败，则将字符型转化为0
select null+0         只要一方为null，结果肯定为null
*/

concat 拼接

案例：查询员工名和姓连接成一个字段，并显示为姓名

SELECT CONCAT('a','b','c') AS 结果;
SELECT 
  CONCAT(last_name,first_name) AS 姓名 
FROM
  employees ;

补充

ifnull：判断字段或表达式是否为null，如果为null，返回指定值
select ifnull (commission_pct,0 ) from employees;
isnull : 判断某字段或者表达式是否为null，是返回1，否返回0

案例

显示departments的结构，并查询其中全部数据

DESC departments;
SELECT * FROM departments;

显示employees中全部job_id(不能重复)

SELECT DISTINCT job_id FROM employees;

显示出表employee的全部列，各个列之间逗号链接，列头显示成out_put

SELECT 
  IFNULL(commission_pct, 0) AS 奖金率,
  commission_pct 
FROM
  employees ;
  
  
SELECT   CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct, 0)) AS out_put
FROM  employees;

进阶2：条件查询

语法：

select
     查询列表
from
     表名
where
     筛选条件

分类

按条件表达式筛选条件运算符： < > = <> >= <=
按逻辑表达式筛选逻辑运算符： && || ！ and or not
模糊查询 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;

二.按逻辑表达式筛选

案例1：查询工资在10000到20000之间的员工名，工资以及奖金

SELECT last_name,salary,commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;

案例2：查询部门编号不是在90–110之间，或者工资高于15000的员工信息

SELECT *
FROM employees
WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

三.模糊查询

like特点：

1.一般和通配符搭配使用
通配符
% 任意多个字符
_ 任意单个字符

1.like

案例1：查询员工名中包含a的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '%a%';

案例2.查询员工名中第三个字符为n，第五个字符为l的员工名和工资

SELECT last_name,salary
FROM employees
WHERE last_name LIKE '__n_l%';

案例3.查询员工名中第二个字符为 _ 的员工名

SELECT last_name
FROM employees
WHERE last_name LIKE '_\_%'

#(’_ $KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '$ ’ 即任意字符都可以用来表示转义)

2.between and

/*
1.使用between and 可以提高语句简洁度
2.包含临界值
3.两个临界值不要调换顺序
*/

案例1：查询员工编号在100-120之间的员工信息

SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120;

3.in

含义：判断某字段的值是否属于in列表中的某一项
特点
1. 使用in提高语句简洁度
2. In列表的值类型必须一致或兼容

案例1：查询员工工种编号是IT_PROG,AD_VP,AD_PRES中的一个的员工名和工种编号

SELECT last_name,job_id
FROM employees
WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');

4.is null

= 或<> 不能用于判断null值
is null 或is not null 可以判断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;

#is null PK <=>
IS NULL : 仅仅可以判断null值，可读性较高，建议用此
<=> : 既可以判断null值，又可以判断普通的数值，可读性较低

案例：

1.查询员工号为176的员工姓名和部门编号和年薪

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

2.查询没有奖金，且工资小于18000的salary，last_name

SELECT salary,last_name
FROM employees
WHERE commission_pct IS NULL AND salary<18000;

3.查询employees表中，job_id不为’IT’或者工资为12000的员工信息

SELECT *
FROM employees
WHERE job_id<>'IT' OR salary=12000;

4.查看department表结构，查询效果如下

DESC departments;

在这里插入图片描述

5.查询部门departments表中涉及到了哪些位置编号

SELECT DISTINCT location_id
FROM departments;

在这里插入图片描述

进阶3：排序查询

语法：

select 查询列表
from 表
[where 筛选条件]
order by 排序列表 [asc|desc]

特点：
1. asc 升序 desc降序默认为升序
2. order by子句中可以支持单个字段,多个字段,表达式,函数,别名
3. order by子句一般放在查询语句最后面,但limit子句除外(最后)

案例1：查询员工信息，要求工资从高到低排序

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;

案例4：按年薪高低显示员工信息，年薪【按别名排序】

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

案例5：按姓名长度显示员工姓名，工资【按函数排序】

SELECT LENGTH('last_name') 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH('last_name') DESC;

案例6：查询员工信息，要求先按工资升序，再按员工编号降序

SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

案例综合：

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 *,LENGTH(email)
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.length (获得参数值的字节个数)

SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');

2.concat （拼接字符串）

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

3.upper lower

SELECT UPPER('john');
SELECT LOWER('JoHn');

示例：将姓变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 
FROM employees;

4.substr substring (截取字符)

#注意：索引从1开始
#截取从指定索引后所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) put_put;
#截取从指定索引处指定字符长度
SELECT SUBSTR(‘李莫愁爱上了陆展元’,1,3) put_put;

#案例：姓名中首字母大写，其他字符小写，然后用_拼接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),’_’,LOWER(SUBSTR(last_name,2))) out_put
FROM employees;

5.instr 返回子串第一次出现的索引，找不到返回0

SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷六侠') AS out_put;

6.trim 去前后空格

SELECT LENGTH(TRIM('     张翠山    ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaa张aa翠aa山aaa') AS out_put;

7.lpad 用指定字符实现左填充

SELECT LPAD('殷素素','10','*') AS out_put;

8.rpad 用指定的字符实现右填充

SELECT RPAD('殷素素','10','*') AS out_put;

9.replace 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

二.数学函数

round 四舍五入

SELECT ROUND(-1.45);
SELECT ROUND(1.567,2);

ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.002);

floor 向下取整,返回<=该参数的最大整数

SELECT FLOOR(-9.99);

truncate 截断

SELECT TRUNCATE(1.6999,1);

mod 取余 mod(a,b) a-a/b*b

SELECT MOD(10,-3);
SELECT 10%3;

三.日期函数

now 返回当前系统日期+时间

SELECT NOW();

curdate 返回当前系统日期，不包含时间

SELECT CURDATE();

curtime 返回当前时间，，不包含日期

SELECT CURTIME();

可以获取指定的部分，年月日时分秒

SELECT YEAR(NOW()) AS 年;
SELECT YEAR('1998-1-1') AS 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

str_to_date 将日期格式的字符转化成指定格式的日期

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');

date_format 将日期转化成字符

SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');

查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3-1992','%c-%d-%Y');

查询有奖金的员工名和入职日期（xx月/xx日 xx年）

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年')
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的效果

/*
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end
*/
/*案例：查询员工工资，要求
部门号=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;

3.case函数的使用2：类似于多重if

/*
case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
…
else 要显示的值n或语句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'
END AS 工资级别
FROM employees;

常见函数总结一

字符函数

length concat substr instr trim upper lower lpad rpad
replace

数学函数

round ceil floor truncate mod

日期函数

now curdate curtime year month monthname day hour
minute second str_to_date date_formate

其他函数

version datebase user

控制函数

if case

案例

1.显示系统时间(注：日期+时间)

SELECT NOW();

2.查询员工号,姓名,工资,以及工资提高20%后的结果

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

3.将员工姓名按首字母排序,并写出姓名的长度(length)

SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字母,last_name
FROM employees
ORDER BY 首字母;

4.做一个查询产生下面结果

#<last_name> earns monthly but wants <salary*3>
#Dream Salary
#Kings earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,'earns',salary,'monthly but wants',salary*3) AS 'Dream Salary'
FROM employees
WHERE salary=24000;

5.使用case-when 实现下面条件

job	grade
AD_PRES	A
ST_MAN	B
IT_PROG	C
SA_PRE	D
ST_CLERK	E

SELECT last_name,job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_PRE' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees
WHERE job_id='AD_PRES';

案例

1.查询员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary) mx_sal,MIN(salary) min_sal,ROUND(AVG(salary),2) ag_sal,SUM(salary) sm_sal
FROM employees;

2.查询员工表中的最大入职时间和最小入职时间相差天数

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) difference
FROM employees;

3.查询部门编号为90的员工个数

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

进阶5：分组查询

语法：

select分组函数，列(要求出现在group by后面)
from 表
【where 筛选条件】
group by 分组列表
【order by子句】

注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
1.分组查询中的筛选条件分为两类

	数据源	位置	关键字
分组前筛选	原始表	group by 前	where
分组后筛选	分组后的结果集	group by 后	having

①分组函数做条件肯定放在having子句中
②能用分组前筛选的优先考虑分组前
2.group by子句支持单个字段分组，多个字段分组(逗号隔开无序)，表达式，函数分组
3.也可以添加排序，放在最后

引入：查询每个部门的平均工资

SELECT AVG(salary) FROM employees;

案例1：查询每个工种的最高工资

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

案例2：查询每个位置上的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

添加分组前筛选条件

案例1：查询邮箱中包含a字符的每个部门的平均工资

SELECT AVG(salary),department_id
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;

添加分组后的筛选条件

案例1：查询那个部门的员工个数大于2

①查询每个部门的员工个数

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;

②根据①的结果进行筛选，查询哪个部门的员工个数大于2

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

案例2：查询每个工种有奖金的员工的最高工资>12000

①查询每个工种有奖金的员工的最高工资

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

②根据①结果继续筛选，最高工资>12000

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

案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

①查询领导的每个手下的员工最低工资

SELECT MIN(salary),manager_id
FROM employees
GROUP BY manager_id;

②添加筛选条件：编号＞102

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id;

③添加筛选条件：最低工资＞5000

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

按表达式或函数筛选

案例：按员工姓名长度分组，查询每一组员工个数，筛选个数＞5的信息

①查询每个长度的员工个数

SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);

②添加筛选条件

SELECT COUNT(*) c,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING c>5;

按多个字段分组

案例：查询每个部门每个工种的平均工资

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

添加排序

案例：查询每个部门每个工种的平均工资,并且按平均工资高低显示

SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;

案例

1.查询各job_id员工工资最大值，最小值，平均值，总和，并按job_id升序

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id;

2.查询最高工资和最低工资的差距

SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;

3.查询各个管理者手下员工最低工资，其中最低工资不能低于6000，没有管理者不计

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

4.查询所有部门编号，员工数量和工资平均值，并按平均工资降序

SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;

5.选择具有各个job_id的员工人数

SELECT COUNT(*) 个数,job_id
FROM employees
GROUP BY job_id;

进阶6：连接查询

/*
含义：又称多表查询，当查询字段来自于多个表
笛卡尔乘积：表1 有m行表2有n行结果m*n行
如何发生：没有有效链接条件
如何避免：添加有效连接条件

分类：
按年代分类：
sql92标准：仅仅支持内连接
slq99标准【推荐】支持内外（左右）链接+交叉
按功能分类
内连接 : 等值连接非等值连接自链接
外连接: 左外连接右外连接全外连接
交叉连接
*/

SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;

一.sql92标准

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

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 last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.job_id=j.job_id;

3.两个表的顺序可否调换

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

4.可以加筛选

案例1：查询有奖金的员工名，部门名

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;

2.非等值连接

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

SELECT * FROM job_grades;

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 salary,employee_id FROM employees;

3自然连接.

案例：查询员工名以及上级名称

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

案例

1.显示员工表最大工资，平均工资

SELECT MAX(salary),AVG(salary)
FROM employees;

2.查询员工表employee_id,last_name,job_id 按department_id降序，salary升序

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

3.查询员工表job_id包含a和e的，并且a在e前

SELECT job_id 
FROM employees
WHERE job_id LIKE '%a%e%';

4.显示当前日期，以及去前后空格，截取字符串函数

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

3.选择有奖金员工的部分字段

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工作的员工信息

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 d.department_name,j.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 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';

二.sql99语法

/*
语法
select 查询列表
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

内连接 inner
外连接
左外 left 【outer】
右外 right【outer】
全外 full 【outer】
交叉连接 cross

特点：
①添加排序，分组，筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面
④inner join连接和sql92语法的等值连接效果一样，都是查询多表交集

一）内连接

/*
语法：
select 查询列表
from 表别名
inner join 表二别名
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,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';

案例3.查询部门个数>3的城市名和部门个数

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

案例4:查询那个部门的部门员工个数>3的部门名和员工个数，并按个数降序排序

SELECT COUNT(*),department_name
FROM employees e 
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) 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;

2. 非等值链接

查询员工工资级别

SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

查询每个工资级别个数大于2的个数，并且按工资级别降序排序

SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

3. 自链接

查询姓名中包含k的员工名字，上级名字

SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

二.外连接

/*
应用场景：用于查询一个表有，另一个表没有的记录
特点：
1.外连接的查询结果为主表中所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和他匹配的，则显示null
外连接查询结果=内连接结果+主表中有从表没有的记录
2.左外连接 left 左边是主表
右外连接 right 右边是主表
3.左外和右外交换两个表的顺序可以实现同样的效果
4.全外连接=内连接结果+表1中有但表2中没有+表2有表1没有
*/

引入：查询男朋友不在男神表的女神名

SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

案例1：查询那个部门没有员工

左外

SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

右外

SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

全外链接

SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.id;

交叉连接（笛卡尔乘积）

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

sql92和sql99

功能：sql99支持的更多
可读性：sql99实现链接条件和筛选条件的分离，可读性高

案例

一.查询编号>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;

二.查询哪个城市没有部门

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 OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE d.`department_name` IN('SAL','IT');

进阶7：子查询

/*
含义：出现在其他语句中的select语句，称为子查询或内查询
外部查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
select后面
仅仅支持标量子查询
from后面
支持表子查询
where或having后面♥♥♥
标量子查询（单行）
列子查询（多行）
行子查询
exists后面（相关子查询）
表子查询
按结果集行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有多行多列）
表子查询（结果集一般为多行多列）

/
#一.where或者having后面
/
1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多列多行）
特点
①子查询放在小括号内
②子查询一般放在条件右侧
③标量子查询，一般搭配单行操作符使用

< >= <= = <>
列子查询，一般搭配多行操作符使用
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
in any/some all
*/

1.标量子查询

案例1：谁的工资比abel高

①查询able的工资

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与14号员工相同，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和工资，要求job_id=①并且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;

②查询last_name,job_id,salary,并且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;

③筛选②，满足min(salary)>①

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部门中所有员工姓名

①查询location_id是1400或1700的部门编号

SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);

②查询员工姓名，要求部门号是①列表的某一个

SELECT last_name 
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);

案例2：返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号，姓名，job_id以及salary

查询job_id为‘IT_PROG’部门任一工资

SELECT salary
FROM employees
WHERE job_id='IT_PROG';

②查询员工号，姓名，jobn_id以及salary,salary<①中任意一个

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG';

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

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

3.行子查询（结果一行多列）

案例：查询员工编号最小且工资最高的员工信息

SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

①查询最小的员工编号

SELECT MIN(salary)
FROM employees;

②查询最高工资

SELECT MAX(salary)
FROM employees;

③查询员工信息

SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(salary)
	FROM employees
) AND salary=(
	SELECT 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后面

案例：查询每个部门的平均工资的工资等级

①查询每个部门的平均工资

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

SELECT * FROM job_grades;

②链接①的结果集和job_grade表，筛选条件平均工资between lowest_sal and highest_sal

SELECT ag_dep.*,g.`grade_level`
FROM(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

四.exists后面（相关子查询）

/*
exists(完整的查询语句)
结果：1或者0

案例1：查询有员工的部门名

in

SELECT department_name
FROM departments d
WHERE d.department_id IN(
	SELECT department_id
	FROM employees
);

exists

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

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

in

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

exists

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

案例综合

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

①查询zlotkey的部门

SELECT department_id
FROM employees
WHERE last_name ='Zlotkey';

②查询部门号=①的姓名和工资

SELECT last_name,salary
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	WHERE last_name ='Zlotkey'
);

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

①查询平均工资

SELECT AVG(salary)
FROM employees;

②查询工资＞①的员工号，姓名和工资

SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(
	SELECT AVG(salary)
	FROM employees
);

3.查询各部门中工资比平均工资高的员工号，姓名，工资

①查询各部门的平均工资

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

②链接①结果集和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的员工在相同部门工作的员工的员工号

①查询姓名中包含字母u的员工的部门

SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';

②查询部门号=①中任意一个的员工号和姓名

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的部门工作的员工的员工号

①查询location_id为1700的部

SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;

②查询部门号=①中的任意一个的员工号

SELECT employee_id
FROM employees
WHERE department_id=ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);

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

①查询姓名为king的员工编号

SELECT employee_id
FROM employees
WHERE last_name = 'K_ing';

②查询哪个员工的manager_id=①

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)
FROM employees;

②查询工资=①的姓名

SELECT CONCAT(first_name,last_name) '姓,名'
FROM employees
WHERE salary=(
	SELECT MAX(salary)
	FROM employees
);

落空空。

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Python全栈开发-Mysql知识点总结(上)

mysql知识点总结(上)1.基础查询 2.条件查询 3.排序查询 4.常见函数 5.分组查询 6.链接查询 7.子查询
复制链接

扫一扫

专栏目录

Python全栈开发-Mysql知识点总结(上)

Myqsl 知识点总结

一. 数据库相关概念

二. mysql 常见命令

1. 查看当前所有数据库

2. 打开指定库

3.查看当前库所有表

4.查看其他库所有表

5.创建表

6.查看表结构

7.查看服务器版本

mysql 的语法规范

进阶1: 基础查询

1.查询表中单个字段

2.查询表中多个字段

3.查询表中所有字段

4.查询常量值

5.查询表达式

6.查询函数

7.起别名

案例：查询salary，显示结果为out put

8.去重（字段前加DISTINCT）

案例 查询员工表中涉及到的所有部门编号

9.+号的作用

concat 拼接

案例：查询员工名和姓连接成一个字段，并显示为 姓名

案例

显示departments的结构，并查询其中全部数据

显示employees中全部job_id(不能重复)

显示出表employee的全部列，各个列之间逗号链接，列头显示成out_put

进阶2：条件查询

一.按条件表达式筛选

案例1：查询工资大于12000的员工信息

案例2：查询部门编号不等于90号的员工名和部门编号

二.按逻辑表达式筛选

案例1：查询工资在10000到20000之间的员工名，工资以及奖金

案例2：查询部门编号不是在90–110之间，或者工资高于15000的员工信息

三.模糊查询

1.like

案例1：查询员工名中包含a的员工信息

案例2.查询员工名中第三个字符为n，第五个字符为l的员工名和工资

案例3.查询员工名中第二个字符为 _ 的员工名

2.between and

案例1：查询员工编号在100-120之间的员工信息

3.in

案例1：查询员工工种编号是IT_PROG,AD_VP,AD_PRES中的一个的员工名和工种编号

4.is null

案例1：查询没有奖金的员工名和奖金率

案例：

1.查询员工号为176的员工姓名和部门编号和年薪

2.查询没有奖金，且工资小于18000的salary，last_name

3.查询employees表中，job_id不为’IT’或者工资为12000的员工信息

4.查看department表结构，查询效果如下

5.查询部门departments表中涉及到了哪些位置编号

进阶3：排序查询

案例1：查询员工信息，要求工资从高到低排序

案例2：查询部门编号>=90的员工信息，按入职时间的先后排序【添加筛选条件】

案例3：按年薪高低显示员工信息，年薪【按表达式排序】

案例4：按年薪高低显示员工信息，年薪【按别名排序】

案例5：按姓名长度显示员工姓名，工资【按函数排序】

案例6：查询员工信息，要求先按工资升序，再按员工编号降序

案例综合：

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

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

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

进阶4：常见函数

一.字符函数

1.length (获得参数值的字节个数)

2.concat （拼接字符串）

3.upper lower

示例：将姓变大写，名变小写，然后拼接

4.substr substring (截取字符)

5.instr 返回子串第一次出现的索引，找不到返回0

6.trim 去前后空格

7.lpad 用指定字符实现左填充

8.rpad 用指定的字符实现右填充

9.replace 替换

二.数学函数

round 四舍五入

ceil 向上取整,返回>=该参数的最小整数

案例查询员工表中涉及到的所有部门编号

案例：查询员工名和姓连接成一个字段，并显示为姓名