MySQL

最新推荐文章于 2022-06-22 14:38:02 发布

Boer99

最新推荐文章于 2022-06-22 14:38:02 发布

阅读量145

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46041401/article/details/114381679

版权

数据库的相关概念

DBMS、DB、SQL

1、DB：数据库，保存一组有组织的数据的容器

2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据

3、SQL：结构化查询语言，用于和DBMS通信的语言

与java类似点

表具有一些特性，这些特性定义了数据在表中如何存储，类似java中类的设计。
表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的属性
表中的数据是按行存储的，每一行类似于java中的对象。

MySQL的常用命令

查看当前所有的数据库：
```
show databases;
```
打开指定的库：
```
use 库名
```
查看当前的所有表：
```
show tables;
```
查看其他库的所有表：
```
show tables from 库名;
```

创建表：

create table 表名(
 列名 列类型,
 列名 列类型,
 …
);

查看表结构：
```
desc 表名;
```

DQL

数据查询语言（data query language）

基础查询

语法：

SELECT （要查询的东西）
FROM （表名）

select相当于java中的sout

要查询的东西可以是常量值、可以是表达式、可以是字段、可以是函数

# 单个字段：
SELECT `first_name` FROM employees # 字段上加着重号``标识这是字段，和关键字区分

# 多个字段：
SELECT `first_name` , `last_name` , `email` FROM employees

# 所有字段：
SELECT * FROM employees 

# 查询常量值
SELECT 100;
SELECT 'john';

# 查询表达式
SELECT 100%98;

# 查询函数
SELECT VERSION(); # 版本

# 起别名 方式一：AS  方式二：空格
SELECT 
  100 % 98 AS 结果 ;

SELECT 
  first_name AS 姓,
  last_name AS 名 
FROM
  employees ;

SELECT 
  first_name 姓,
  last_name 名 
FROM
  employees ;

SELECT 
  salary AS 'out put' # 特殊情况 防止歧义
FROM
  employees 

# 去重
SELECT DISTINCT 
  department_id 
FROM
  employees ;

# 加号 只有一个功能：运算符
# 错误案例
SELECT 
  last_name + first_name AS 姓名 
FROM
  employees ;

SELECT 
  '123' + 90 ; # 213
SELECT 
  'john' + 90 ; # 90
  
# CONCAT 拼接
SELECT 
  CONCAT('a', 'b', 'c') AS 结果 ; # abc 

SELECT 
  CONCAT(last_name, first_name) AS 姓名 
FROM
  employees ;
  
# IFNULL
# 如果为空 显示0
SELECT 
  IFNULL(commission_pct, 0) AS 奖金率,
  commission_pct 
FROM
  employees ;

条件查询

语法：

select 
		要查询的字段|表达式|常量值|函数
	from 
		表
	where 
		条件 ;

分类：

一、条件表达式
	条件运算符：	
	>	<	>	=	<=	>=	 !=		<>

二、逻辑表达式
	逻辑运算符：
	and	
	or	
	not

三、模糊查询
	like	
	between and		
	in		
	is null

# 条件表达式
SELECT 
  department_id 
FROM
  employees 
WHERE department_id != 90 ;

# 逻辑表达式
SELECT 
  last_name,
  salary,
  department_id 
FROM
  employees 
WHERE NOT (
    department_id >= 90 
    AND department_id <= 110
  ) 
  OR salary > 15000 ;

# 模糊查询

/*
like
一般和通配符搭配使用
	通配符:
	% 任意多个（可以为0）个字符
	_ 任意单个字符
*/

# 案例一
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '%al%' ;

# 案例二：第三个字符为n，第五个为l
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '__n_l%' ;

# 案例三：第二个字符为_ 
  # 方式一：用转义字符/
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '_\_%' ;

  # 方式二：自定义转义字符
SELECT 
  last_name 
FROM
  employees 
WHERE last_name LIKE '_a_%' ESCAPE 'a' ;

/*
between and
	提高语句简洁度
	包含两个临界值
	两个临界值不能颠倒顺序
*/
# 案例一：员工编号在100-120之间
SELECT 
  employee_id 
FROM
  employees 
WHERE employee_id BETWEEN 100 
  AND 120 ;

/*
IN
含义：判断谋字段的值是否属于in列表中的某一项
特点：
	提高简洁度
	in列表类型值必须一直或兼容
*/
SELECT 
  last_name,
  job_id 
FROM
  employees 
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES') ;

/*
is null
	=和<>不能判断null值
*/
SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NOT NULL ;

排序查询

语法：

select 要查询的东西
from 表
# where 条件
order by 排序的字段|表达式|函数|别名 【asc|desc】

/*
order by
	如果不写默认是升序
	支持单个字段、多个字段、表达式、函数、别名
	一般放在查询语句的最后面，limit子句除外
*/

# 按表达式排序
SELECT 
  *,
  salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪 
FROM
  employees 
ORDER BY salary * 12 * (1+ IFNULL(commission_pct, 0)) DESC ;

# 按函数排序
SELECT 
  LENGTH(last_name) 字节长度,
  last_name 
FROM
  employees 
ORDER BY LENGTH(last_name) DESC ;

# 多个字段排序
SELECT 
  salary,employee_id
FROM
  employees 
ORDER BY salary ASC,
  employee_id DESC

常见函数

一、单行函数

1、字符函数

concat 拼接

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

substr 截取子串 (注意索引从1开始)

SELECT 
  SUBSTR('1234567', 5) out_put ; #567

SELECT 
  SUBSTR('1234567', 2, 4) out_put ; #2345

upper 转换成大写

lower 转换成小写

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

trim 去前后指定的空格和字符

ltrim 去左边空格

rtrim 去右边空格

SELECT 
  TRIM('a' FROM 'aa12aa3aa') AS out_put ; # 12aa3

replace 替换

SELECT REPLACE('aaabbb','a','b') # bbbbbb

lpad 左填充

rpad 右填充

SELECT 
  LPAD('abc', 10, 'b') AS out_put ; # bbbbbbbabc

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

SELECT 
  INSTR('123456', '456') AS out_put ; # 4

length 获取字节个数

案例：查询邮箱的用户名

SELECT 
  SUBSTR(`email`, 1, INSTR(`email`, '@') - 1) 用户名 
FROM
  `employees`

2、数学函数

round 四舍五入

SELECT 
  ROUND(1.567, 2) ; # 1.57

rand 随机数

SELECT 
  RAND();

mod 取余

SELECT 
  MOD(10, 3) ;

truncate 截断

SELECT 
  TRUNCATE(1.69999, 1) ; # 1.6

floor 向下取整，返回<=该参数的最小整数

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

3、日期函数

now 当前系统日期+时间

curdate 当前系统日期，没有时间

curtime 当前系统时间

str_to_date 将字符转换成日期

date_format 将日期转换成字符

# 可获取指定的部分
SELECT 
  YEAR(NOW()) 年 ;

SELECT 
  YEAR('2021-1-1') 年 ;

SELECT 
  YEAR(hiredate) 年 
FROM
  employees ;
  
# STR_TO_DATE 将字符转换成日期
SELECT 
  STR_TO_DATE('1999:1:17', '%Y:%c:%d') AS out_put ;
  
SELECT 
  * 
FROM
  employees 
WHERE hiredate = STR_TO_DATE('4-3 1992', '%c-%d %Y') ; # 适用于具体业务中

# 将日期转换成字符
SELECT 
  DATE_FORMAT(NOW(), '%Y年%m月%d日') ;

4、其他函数

SELECT VERSION(); # 版本
SELECT DATABASE(); # 当前库
SELECT USER(); # 当前连接用户

5、流程控制函数

if 处理双分支

SELECT 
  IF(10 < 5, '大', '小') ;

SELECT 
  last_name,
  commission_pct,
  IF(
    commission_pct IS NULL,
    '没奖金',
    '有奖金'
  ) 奖金 
FROM
  employees ;

case语句处理多分支
情况1：处理等值判断

# case到end相当于一个函数，再用as起了别名
SELECT 
  salary 原始工资,
  `department_id`,
  CASE
    `department_id` 
    WHEN 30 
    THEN salary * 2 
    WHEN 40 
    THEN salary * 3 
    ELSE salary 
  END AS 新工资 
FROM
  employees ;

情况2：处理条件判断，相当于else if

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

二、分组函数

sum 求和
max 最大值
min 最小值
avg 平均值
count 计数

sum和avg一般用于处理数值型，max、min、count可以处理任何类型
都忽略null值
都可以和distinct搭配

# 都可以和distinct搭配
SELECT 
  SUM(DISTINCT `salary`),
  salary 
FROM
  employees ; # 107
  
SELECT 
  COUNT(DISTINCT salary) 
FROM
  employees ; # 57

# count函数
SELECT COUNT(salary) FROM employees;	
SELECT COUNT(*) FROM employees;

# 和分组函数查询的字段有限制
SELECT 
  AVG(`salary`),
  employee_id 
FROM
  employees ; # 结果只有一行

案例1：查询员工表中最大入职时间和最小入职时间的相差天数，用 DATEDIFF

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

案例2：查询部门编号为90的员工

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

分组查询

group by

语法：

select 查询的字段，分组函数
from 表
# where 筛选条件
group by 分组的字段
# order by 子句

查询列表必须特殊，要求是分组函数和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 
  AVG(`salary`),
  department_id 
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY department_id ;

按多个字段分组

# 查询每个部门每个工种的员工的平均工资
SELECT 
  AVG(`salary`),
  `department_id`,
  `job_id` 
FROM
  employees 
GROUP BY `department_id`,
  `job_id` ;

having

where和having对比：

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

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

性能上优先考虑where

# 查询每个部门员工数，且员工数>2
SELECT 
  COUNT(*),
  department_id
FROM
  employees 
GROUP BY department_id 
HAVING COUNT(*) > 2 ; # having针对分组进行筛选，不能用where

# 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT 
  `job_id`,
  MAX(`salary`) 
FROM
  employees 
WHERE `commission_pct` IS NOT NULL 
GROUP BY `job_id` 
HAVING MAX(`salary`) > 12000 ;

按表达式或函数分组

# 按员工的姓名长度分组，查询每一组的员工个数，筛选员工个数>5有哪些
SELECT 
  COUNT(*) c,
  LENGTH(`last_name`) len_name 
FROM
  employees 
GROUP BY len_name 
HAVING c > 5 ;

添加排序

# 查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
SELECT 
  `department_id`,
  `job_id`,
  AVG(`salary`) 
FROM
  employees 
WHERE `department_id` IS NOT NULL 
GROUP BY `department_id`,
  `job_id` 
ORDER BY AVG(`salary`) ;

多表连接查询

技巧：先不加连接条件，用笛卡尔积分析会直观很多

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n
- 发生原因：没有有效的连接条件
- 如何避免：添加有效的连接条件
分类：
- 按年代分类：
  - sql92标准：仅仅支持内连接
  - sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
- 按功能分类：
  - 内连接
    - 等值连接
    - 非等值连接
    - 自连接
  - 外连接
    - 左外连接
    - 右外连接
    - 全外连接（mysql不支持）
  - 交叉连接

sql92标准

等值连接

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名

# 查询女神名和对应的男神名
SELECT 
  `name`,
  `boyName` 
FROM
  boys,
  beauty 
WHERE `beauty`.`boyfriend_id` = `boys`.`id` ;

为表起别名：

提高语句简洁度
区分重名字段

**注意：**如果起了别名，就不能用原来的表名

# 查询员工名、工种号、工种名
SELECT 
  e.`last_name`,
  e.`job_id`,
  j.`job_title` 
FROM
  `employees` e,
  `jobs` j 
WHERE e.`job_id` = j.`job_id` ;

三表连接：

# 查询员工的工资和工资级别
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` ;

非等值连接

# 查询员工的工资和工资级别
SELECT 
  salary,
  grade_level 
FROM
  employees e,
  job_grades g 
WHERE salary BETWEEN g.lowest_sal 
  AND g.highest_sal ;

自连接

相当于将一张表复制为两份

# 员工名和上级的名称
SELECT 
  e1.`last_name`,
  e1.`employee_id`,
  e1.`manager_id`,
  e2.`last_name`,
  e2.`employee_id` 
FROM
  `employees` e1,
  `employees` e2 
WHERE e1.`manager_id` = e2.`employee_id`

sql99语法

语法：

select 查询列表

from 表1 别名 【连接类型】

join 表2 别名

on 连接条件

where 筛选条件

group by 分组

having 筛选条件

order by 排序列表

内连接（同上）：连接类型是inner
- 特点：
  - 添加排序、分组、筛选
  - inner可以省略
  - 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
  - inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
外连接
- 左外：left 【outer】
- 右外：right【outer】
- 全外：full 【outer】
交叉连接：cross

等值连接

# 查询员工名、部门名
SELECT 
  `last_name`,
  `department_name` 
FROM
  `employees` e 
  INNER JOIN `departments` d 
    ON e.`department_id` = d.`department_id` ;

# 查询名字中包含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的城市名和部门个数
SELECT 
  `city`,
  COUNT(*) 部门个数
FROM
  `departments` d 
  INNER JOIN `locations` l 
    ON d.`location_id` = l.`location_id` 
GROUP BY `city`
HAVING 部门个数 > 3 ;

# 查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序排序
SELECT 
  `department_name`,
  COUNT(*) 员工个数 
FROM
  `departments` d 
  INNER JOIN employees e 
    ON d.`department_id` = e.`department_id` 
GROUP BY d.`department_id` 
HAVING 员工个数 > 3 
ORDER BY 员工个数 DESC ;

# 查询员工名、部门名、工种名，并按部门名降序
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 j.`job_id` = e.`job_id` 
ORDER BY `department_name` DESC ;

非等值连接

# 查询员工的工资级别
SELECT 
  salary,
  grade_level 
FROM
  employees e 
  INNER JOIN job_grades g 
    ON e.`salary` BETWEEN g.`lowest_sal` 
    AND g.`highest_sal` ;

自连接

# 查询姓名中包含字符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%" ;

外连接

应用场景：用于查询一个表中有，另一个表没有的记录

外连接的查询结果为主表中的所有记录
- 如果从表中有和它匹配的，则显示匹配的值
- 如果从表中没有和它匹配的，则显示null
- 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
左外连接：left join 左边的是主表
右外连接：right join 右边的是主表
- 左外和右外交换两个表的顺序，可以实现同样的效果
圈外链接 = 内连接的结果 + 表1中有但表2中没有的 + 表2中有但表1中没有的

引入：

# 查询没有男朋友的女神名
SELECT 
  `name`,
  bo.* 
FROM
  `beauty` b 
  LEFT JOIN `boys` bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE bo.`id` IS NULL ;

查询哪个部门没有员工

左外：

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 
  CROSS JOIN boys bo ;

总结：

sql92 和 sql99 pk

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

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询
嵌套在其他语句内部的select语句成为子查询或内查询
外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
外面如果为select语句，则此语句称为外查询或主查询
分类：
- 按子查询出现的位置：
  - select后面：仅仅支持标量子查询
  - from后面：支持表子查询
  - where或having后面：支持标量子查询，列子查询，行子查询（较少）
  - exists后面（相关子查询）：支持表子查询
- 按功能、结果集的行列数不同：
  - 标量子查询（结果集只有一行一列）
  - 列子查询（结果集只有一列多行）
  - 行子查询（结果集有一行多列）
  - 表子查询（结果集一般为多行多列）

where或having后面

标量子查询（单行子查询）（较多）
列子查询（多行子查询）（较多）
行子查询（多列多行）（较少）
特点：
- 子查询放在小括号内
- 子查询一般放在条件的右侧，where，having
- 标量子查询，一般搭配着单行操作符使用（> < >= <= = <>）
- 列子查询，一般搭配着多行操作符使用（IN、ANY/SOME、ALL）
- 子查询的执行优选与主查询执行，主查询的条件用到了子查询的结果

标量子查询

案例：返回job_id与141号员工相同，salary比143员工多的员工，姓名，job_id，工资

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

案例：返回公司工资最少的员工的last_name, job_id和salary

SELECT 
  `last_name`,
  `job_id`,
  `salary` 
FROM
  employees 
WHERE `salary` = 
  (SELECT 
    MIN(`salary`) 
  FROM
    employees)

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

SELECT 
  `department_id`,
  MIN(`salary`) 
FROM
  `employees` 
GROUP BY (`department_id`) 
HAVING MIN(`salary`) > 
  (SELECT 
    MIN(`salary`) 
  FROM
    `employees` 
  WHERE `department_id` = 50) ;

列子查询

多行子查询
多行比较操作符：
- IN/NOT IN：等于列表中的任意一个
- ANY|SOME：和子查询返回的某一个值比较，用的较少
- ALL：和子查询返回的所有值比较

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

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

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

或者用max代替any

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

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

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

或者用min代替all

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

行子查询

结果集一行多列或多行多列

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

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

select后面（可替代）

仅仅支持标量子查询

案例：查询每个部门的员工个数

# 分组+左外
SELECT 
  d.`department_id`,
  COUNT(e.`employee_id`) 
FROM
  `departments` d 
  LEFT JOIN `employees` e 
    ON d.`department_id` = e.`department_id` 
GROUP BY d.`department_id` ;

# select后标量子查询
SELECT 
  d.`department_id`,
  (SELECT 
    COUNT(*) 
  FROM
    employees e 
  WHERE d.`department_id` = e.`department_id`) 个数 
FROM
  departments d ;

from后面

将子查询结果充当一张表，要求必须起别名

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

先用笛卡尔积分析一下

SELECT 
  ag_dep.*,
  g.* 
FROM
  (SELECT 
    `department_id`,
    AVG(`salary`) ag 
  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 
  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 e) ;

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

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

可用连接替代

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

子查询经典题目

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

SELECT 
  `employee_id`,
  `last_name`,
  `salary`,
  avg_sal.* 
FROM
  `employees` e 
  INNER JOIN 
    (SELECT 
      AVG(`salary`) ag,
      `department_id` 
    FROM
      employees 
    GROUP BY `department_id`) avg_sal 
    ON avg_sal.ag < e.`salary` 
    AND e.`department_id` = avg_sal.`department_id`

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

select 查询列表

from 表

#【join type】 join 表2

# on 连接条件

# where 筛选条件

# group by 分组字段

# having 分组后的筛选

# order by 排序的字段】

limit offset ，size；

	offset：要显示条目的起始索引（从0开始）
	size：要显示的条目个数

特点：

limit语句放在查询语句的最后
公式：

要显示的页数page，每页的条目数size

select 查询列表

from 表

limit （page - 1）* size， size；

案例：查询前5条员工信息

SELECT * FROM employees LIMIT 0, 5;
或者
SELECT * FROM employees LIMIT 5;

案例：查询第11条-第25条

SELECT * FROM employees LIMIT 10, 15;

案例：有奖金的员工信息，并且工资较高的前10名显示出来

SELECT 
  * 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

子查询经典例题

查询平均工资最低的部门信息

SELECT 
  * 
FROM
  `departments` 
WHERE `department_id` = 
  (SELECT 
    `department_id` 
  FROM
    `employees` 
  GROUP BY `department_id` 
  ORDER BY AVG(`salary`) ASC 
  LIMIT 1) ;

查询平均工资最低的部门信息和该部门的平均工资

SELECT 
  d.*,
  dd.ag 
FROM
  `departments` d 
  INNER JOIN 
    (SELECT 
      `department_id`,
      AVG(`salary`) ag 
    FROM
      `employees` 
    GROUP BY `department_id` 
    ORDER BY AVG(`salary`) ASC 
    LIMIT 1) dd 
    ON d.`department_id` = dd.`department_id`

查询平均工资最高的job信息

SELECT 
  * 
FROM
  `jobs` 
WHERE `job_id` = 
  (SELECT 
    `job_id` 
  FROM
    `employees` 
  GROUP BY `job_id` 
  ORDER BY AVG(`salary`) DESC 
  LIMIT 1)

查询平均工资高于公司平均工资的部门有哪些

SELECT 
  `department_id` 
FROM
  `employees` 
WHERE `department_id` IS NOT NULL 
GROUP BY `department_id` 
HAVING AVG(`salary`) > 
  (SELECT 
    AVG(`salary`) 
  FROM
    employees)

查询出公司中所有manager的详细信息

SELECT 
  * 
FROM
  employees 
WHERE `employee_id` IN 
  (SELECT DISTINCT 
    `manager_id` 
  FROM
    employees)

各个部门中，最高工资中，最低的那个部门的最低工资是多少

SELECT 
  MAX(`salary`) max_sal,
  `department_id` 
FROM
  employees 
GROUP BY department_id 
ORDER BY max_sal ASC 
LIMIT 1

查询平均工资最高的部门的manager的详细信息

SELECT 
  *
FROM
  employees e 
  INNER JOIN `departments` d 
    ON e.`employee_id` = d.`manager_id` 
WHERE e.`department_id` = 
  (SELECT 
    `department_id` 
  FROM
    `employees` 
  GROUP BY `department_id` 
  ORDER BY AVG(`salary`) DESC 
  LIMIT 1);
  
SELECT 
  * 
FROM
  employees 
WHERE `employee_id` = 
  (SELECT DISTINCT
    `manager_id` 
  FROM
    employees 
  WHERE `department_id` = 
    (SELECT 
      `department_id` 
    FROM
      `employees` 
    GROUP BY `department_id` 
    ORDER BY AVG(`salary`) DESC 
    LIMIT 1));

联合查询

union：联合，合并，将多条查询语句的结果合并成一个结果

语法：

查询语句1

union 【ALL】

查询语句2

**应用场景：**要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致
特点：
- 要求多条查询语句的查询列数是一致的
- 要求多条查询语句的查询的每一列的类型和顺序最好是一致的
- union关键字默认去重，如果使用union all可以包含重复项

引入案例：查询部门编号>90或邮箱包含a的员工信息

SELECT 
  * 
FROM
  employees 
WHERE email LIKE "%a%" 
  OR department_id > 90 ;

用联合查询为：

SELECT 
  * 
FROM
  employees 
WHERE email LIKE "%a%" 
UNION
SELECT 
  * 
FROM
  employees 
WHERE department_id > 90;

DML

（Data Manipulation Language）数据操作语言

涉及到数据的
- 插入：insert
- 修改：update
- 删除：delete

1. 插入语句

方式1：

语法：

insert into 表名（列名，…） 
values（值1，…）

示例1：插入的值的类型要与列的类型一致或兼容

INSERT INTO beauty (
  `id`,
  `name`,
  `sex`,
  `borndate`,
  `phone`,
  `photo`,
  `boyfriend_id`
) 
VALUES
  (
    13,
    '唐艺昕',
    '女',
    '1990-4-23',
    '18988888888',
    NULL,
    2
  ) ;

示例2：不可以为null的列必须插入值。可以为null的列如何插入值？

# 方式1：字段的值写null 同上
# 方式2：不写该字段
INSERT INTO beauty (`id`, `name`, `sex`, `phone`) 
VALUES
  (14, '娜扎', '女', '12348888888') ;

示例3：列的顺序是否可以调换：可以

INSERT INTO beauty(NAME, sex, id, phone)
VALUES('蒋欣', '女', 16, '110');

示例4：列数和值的个数必须一致
示例5：可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
```
INSERT INTO beauty
VALUES(18, '李易峰', '男', NULL, '19', NULL, NULL);
```

方式2：

语法：

insert into 表名 
set 列名=值，列名=值，…

INSERT INTO beauty SET id = 19,
NAME = '刘涛',
phone = '999'

两种方式对比

方式1支持插入多行，但是方式2不支持

INSERT INTO beauty
VALUES
(20, '李易峰', '男', NULL, '19', NULL, NULL),
(21, '李易峰', '男', NULL, '19', NULL, NULL),
(22, '李易峰', '男', NULL, '19', NULL, NULL);

方式1支持子查询，方式2不支持

INSERT INTO beauty(id, NAME, phone)
SELECT 26, '送钱', '12341234';

INSERT INTO beauty (`id`, `name`, `phone`) 
SELECT 
  `id`,
  `boyName`,
  '123456' 
FROM
  boys 
WHERE id < 3 ；

2. 修改语句

语法：

修改单表的记录

update 表名

set 列=新值，列=新值…

where 筛选条件；

修改多表的记录

sql92语法

update 表1 别名，表2 别名

set 列=值…

where 筛选条件

and 筛选条件；

sql99语法：

update 表1 别名

inner | left | right join 表2 别名

on 连接条件

set 列=值，…

where 筛选条件；

修改单表的记录

案例：修改beauty表中姓唐的女神电话为109090909

UPDATE 
  `beauty` 
SET
  `phone` = '123456' 
WHERE `name` LIKE '唐%'

案例：修改boys表中id号位2的名称为张飞，魅力值为10

UPDATE 
  boys 
SET
  boyname = '张飞',
  usercp = 10 
WHERE id = 2 ;

修改多表的记录

案例：修改张无忌的女朋友的手机号为114

UPDATE 
  `boys` bo 
  INNER JOIN `beauty` b 
    ON bo.`id` = b.`boyfriend_id` SET b.`phone` = '114' 
WHERE bo.`boyName` = '张无忌'

案例：修改没有男朋友的女神的男朋友编号都为 2号

UPDATE 
  `beauty` b 
  LEFT JOIN `boys` bo 
    ON b.`boyfriend_id` = bo.`id` SET b.`boyfriend_id` = 2 
WHERE bo.id IS NULL ;

3. 删除语句

方式1：delete

语法

单表的删除

delete from 表名 where 筛选条件

多表的删除

sql92语法

delete 别名（要删哪个表就写哪个表的别名，都删就都写）

from 表1 别名，表2 别名

where 连接条件

and 筛选条件

limit 条目数

sql99语法

delete 别名（要删哪个表就写哪个表的别名，都删就都写）

from 表1 别名

inner | left | right join 表2 别名 on 连接条件

where 筛选条件

limit 条目数；

案例：删除手机号以0结尾的女神信息
```
DELETE FROM beauty WHERE phone LIKE '%0'
```

案例：删除黄晓明的信息以及他女朋友的信息

DELETE 
  b,
  bo 
FROM
  beauty b 
  INNER JOIN boys bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE bo.`boyName` = '黄晓明'

方式2：truncate
- 语法
```
truncate table 表名
```
- truncate语句中不许加where
- 一删全删
```
TRUNCATE TABLE boyes ;
```
delete对比truncate（常见面试题）
- delete可以加where条件，truncate不可以
- truncate删除效率高一些
- 假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。
- truncate删除没有返回值，delete删除有返回值
- truncate删除不能回滚，delete删除可以回滚

DDL

（Data Definition Language）数据定义语言

库和表的管理
- 库的管理：创建、修改、删除
- 标的管理：创建、修改、删除
- 创建：create
- 修改：alter
- 删除：drop

1. 库的管理

库的创建
- 语法：create database [if not exists] 库名 [character set 字符集名]；
- 案例：创建库book
```
CREATE DATABASE IF NOT EXISTS books;
```

库的修改

修改库名的语句【已停用】
```
RENAME DATABASE books TO new_books;
```

更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

库的删除
```
DROP DATABASE IF EXISTS books;
```

2. 表的管理

表的创建

语法：

create table 【if not exists】 表名(
 列名 列的类型【（长度） 约束】，
 列名 列的类型【（长度） 约束】，
 …
)

案例：创建表 book

CREATE TABLE book (
  id INT,
  bname VARCHAR (20),
  price DOUBLE,
  authorid INT,
  publishdate DATETIME
) ;

案例2：创建表author

CREATE TABLE author (
  id INT,
  au_name VARCHAR (20),
  nation VARCHAR (10)
) ;

案例3：查看创建的表
```
DESC author;
```

表的修改

语法：

alter table 表名 add | drop | modify | change column 列名 【列类型 约束】；

添加列：

alter table 表名 add column 列名 类型 【first | after 字段名】；

修改列的类型或约束：

alter table 表名 modify column 列名 新类型 【新约束】；

修改列名：

alter table 表名 change column 旧列名 新列名 类型；

删除列：

alter table 表名 drop column 列名；

修改表名：

alter table 表名 rename 【to】 新表名；

修改列名

ALTER TABLE book 
  CHANGE COLUMN publishdate pubdate DATETIME ;

修改列的类型或约束

ALTER TABLE book 
  MODIFY COLUMN pubdate TIMESTAMP ;

添加新列

ALTER TABLE author 
  ADD COLUMN annual DOUBLE ;

删除列

ALTER TABLE author 
  DROP COLUMN annual ;

修改表名

ALTER TABLE author 
  RENAME TO book_author ;

表的删除

语法：
```
drop table if exists 表名；
```
查看有哪些表：
```
show tables；
```
if exists 只能在库，表的创建和删除的时候使用，列的操作不能使用。

通用的写法：

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
 
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
 
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();

表的复制

仅仅复制表的结构
```
CREATE TABLE copy LIKE book_author ;
```

复制表的结构+数据

CREATE TABLE copy2 
SELECT 
  * 
FROM
  book_author ;

只复制部分数据

CREATE TABLE copy3 



SELECT 



  id,



  au_name 



FROM



  book_author 



WHERE nation = '中国' ;

仅仅复制某些字段（部分结构）：设置where不满足，那么就没有数据

CREATE TABLE copy4 



SELECT 



  id,



  au_name 



FROM



  book_author 



WHERE 0 ;

Boer99

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫