MySQL

数据库的相关概念

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 ASFROM
  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 
		要查询的字段|表达式|常量值|函数
	fromwhere 
		条件 ;

分类:

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

二、逻辑表达式
	逻辑运算符:
	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 计数
  1. sum和avg一般用于处理数值型,max、min、count可以处理任何类型
  2. 都忽略null值
  3. 都可以和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 查询列表
    
    from1 别名 【连接类型】
    
    join2 别名
    
    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 ;

总结:

image-20210303114050281 image-20210303114150561
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
    
    unionALL】
    
    查询语句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语法

        update1 别名,表2 别名
        
        set=值…
        
        where 筛选条件
        
        and 筛选条件;
        
      • sql99语法:

        update1 别名
        
        inner | left | right join2 别名
        
        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 别名(要删哪个表就写哪个表的别名,都删就都写)
          
          from1 别名,表2 别名
          
          where 连接条件
          
          and 筛选条件
          
          limit 条目数
          
        • sql99语法

          delete 别名(要删哪个表就写哪个表的别名,都删就都写)
          
          from1 别名
          
          inner | left | right join2 别名 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 表名 renameto】 新表名;
      
    • 修改列名

      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 ;
      

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值