自连接
- 将一张表作为两张使用
- 每张表起一个别名
查看哪些员的生日月份与入职月份相同
select e.name, e.hire_date, em.birth_date from employees as e inner join employees as em on month(e.hire_date)=month(em.birth_date) and e.employee_id=em.employee_id;
外连接的概述
- 常用于查询一个表中有，另一个表中没有的记录
- 如果从表中有和它匹配的，则显示匹配的值
- 如要从表中没有和它匹配的，则显示NULL
- 外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
- 左外连接中，left join左边的是主表left outer join
- 右外连接中，right join右边的是主表right outer join
- 左外连接和右外连接可互换，实现相同的目标
左外连接
语法
SELECT tb1.字段..., tb2.字段 FROM table1 AS tb1 LEFT OUTER JOIN table2 AS tb2 ON tb1.字段=tb2.字段
查询所有部门的人员以及没有员工的部门
select d.*, e.name from departments as d left outer join employees as e on d.dept_id=e.dept_id;
右外连接
查询所有部门的人员以及没有员工的部门
select d.*, e.name -> from employees as e -> right outer join departments as d -> on d.dept_id=e.dept_id;
left join和right join 的区别
left join（左连接）：查询结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充。
交叉连接 cross join
返回笛卡尔积
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
子查询
子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式
子查询返回的数据分类
- 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
- 单行多列：返回一行数据中多个列的内容
- 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
- 多行多列：查询返回的结果是一张临时表
子查询常出现的位置
- select之后：仅支持单行单列
- from之后：支持多行多列
- where或having之后：支持单行单列、单行多列、多行单列
单行单列
查询运维部所有员工信息
# 首先从departments表中查出运维部的编号 select dept_id from departments where dept_name='运维部'; +---------+ | dept_id | +---------+ | 3 | +---------+ 1 row in set (0.00 sec) # 再从employees表中查找该部门编号和运维部编号相同的员工 select * from employees where dept_id=( select dept_id from departments where dept_name='运维部' );
查询每个部门的人数
# 查询所有部门的信息 select d.* from departments as d; +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1 | 人事部 | | 2 | 财务部 | | 3 | 运维部 | | 4 | 开发部 | | 5 | 测试部 | | 6 | 市场部 | | 7 | 销售部 | | 8 | 法务部 | | 9 | NULL | +---------+-----------+ 9 rows in set (0.00 sec) # 查询每个部门的人数 select d.*, ( select count(*) from employees as e where d.dept_id=e.dept_id ) as amount from departments as d; +---------+-----------+--------+ | dept_id | dept_name | amount | +---------+-----------+--------+ | 1 | 人事部 | 8 | | 2 | 财务部 | 5 | | 3 | 运维部 | 6 | | 4 | 开发部 | 55 | | 5 | 测试部 | 12 | | 6 | 市场部 | 9 | | 7 | 销售部 | 35 | | 8 | 法务部 | 3 | | 9 | NULL | 0 | +---------+-----------+--------+ 9 rows in set (0.00 sec)
多行单列
单行多列
查找2018年12月基本工资和奖金都是最高的工资信息
# 查询2018年12月最高的基本工资 select max(basic) from salary where year(date)=2018 and month(date)=12; +------------+ | max(basic) | +------------+ | 25524 | +------------+ 1 row in set (0.00 sec) # 查询2018年12月最高的奖金 select max(bonus) from salary where year(date)=2018 and month(date)=12; +------------+ | max(bonus) | +------------+ | 11000 | +------------+ 1 row in set (0.00 sec) mysql> select * from salary -> where year(date)=2018 and month(date)=12 and basic=( -> select max(basic) from salary -> where year(date)=2018 and month(date)=12 -> ) and bonus=( -> select max(bonus) from salary -> where year(date)=2018 and month(date)=12 -> ); +------+------------+-------------+-------+-------+ | id | date | employee_id | basic | bonus | +------+------------+-------------+-------+-------+ | 6368 | 2018-12-10 | 117 | 25524 | 11000 | +------+------------+-------------+-------+-------+ 1 row in set (0.01 sec)
多行多列
查询3号部门及其部门内员工的编号、名字和email
# 查询3号部门和员工的所有信息 select d.dept_name, e.* from departments as d inner join employees as e on d.dept_id=e.dept_id; # 将上述结果当成一张临时表，必须为其起别名。再从该临时表中查询 mysql> select dept_id, dept_name, employee_id, name, email -> from ( -> select d.dept_name, e.* -> from departments as d -> inner join employees as e -> on d.dept_id=e.dept_id -> ) as tmp_table -> where dept_id=3; +---------+-----------+-------------+-----------+--------------------+ | dept_id | dept_name | employee_id | name | email | +---------+-----------+-------------+-----------+--------------------+ | 3 | 运维部 | 14 | 廖娜 | liaona@tarena.com | | 3 | 运维部 | 15 | 窦红梅 | douhongmei@tedu.cn | | 3 | 运维部 | 16 | 聂想 | niexiang@tedu.cn | | 3 | 运维部 | 17 | 陈阳 | chenyang@tedu.cn | | 3 | 运维部 | 18 | 戴璐 | dailu@tedu.cn | | 3 | 运维部 | 19 | 陈斌 | chenbin@tarena.com | +---------+-----------+-------------+-----------+--------------------+ 6 rows in set (0.00 sec)
分页查询
# 按employee_id排序，取出前15至20号员姓名 select employee_id, name from employees order by employee_id limit 15, 5; +-------------+--------+ | employee_id | name | +-------------+--------+ | 16 | 聂想 | | 17 | 陈阳 | | 18 | 戴璐 | | 19 | 陈斌 | | 20 | 蒋红 | +-------------+--------+ 5 rows in set (0.00 sec)
联合查询UNION
作用：将多条select语句的结果，合并到一起，称之为联合操作。
语法：( ) UNION ( )
- 要求查询时，多个select语句的检索到的字段数量必须一致
- 每一条记录的各字段类型和顺序最好是一致的
- UNION关键字默认去重，可以使用UNION ALL包含重复项
查询1972年前或2000年后出生的员工
select name, birth_date from employees where year(birth_date)<1972 or year(birth_date)>2000; +-----------+------------+ | name | birth_date | +-----------+------------+ | 梁伟 | 1971-08-19 | | 张建平 | 1971-11-02 | | 窦红梅 | 1971-09-09 | | 温兰英 | 1971-08-14 | | 朱文 | 1971-08-15 | | 和林 | 1971-12-10 | +-----------+------------+ 6 rows in set (0.01 sec) mysql> ( -> select name, birth_date from employees -> where year(birth_date)<1972 -> ) -> union -> ( -> select name, birth_date from employees -> where year(birth_date)>=2000 -> );
插入语句
不指定列名的插入
语法格式：
INSERT INTO 表名称 VALUES (值1, 值2,....)
- 需要为所有列指定值
- 值的顺序必须与表中列的顺序一致
指定列名的插入
语法格式：
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
- 列和值的顺序要一致
- 列名先后顺序不重要
主键由于是自动增长的，也可以不指定主键的值
支持子查询
mysql> insert into employees -> (name, hire_date, birth_date, email, phone_number, dept_id) -> ( -> select name, hire_date, birth_date, email, phone_number, dept_id -> from employees -> where name='张三' -> ); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
使用set语句
语法格式：
INSERT INTO 表名 SET 列名1=列值1, 列名2=列值2, ...
修改语句
修改单表记录
语法：
UPDATE 表名称 SET 列名称=新值, 列名称=新值, ... WHERE 筛选条件
修改多表记录
语法：
UPDATE 表1 AS 表1别名 INNER | LEFT | RIGHT JOIN 表2 AS 表2别名 ON 连接条件 SET 列=值, 列=值, ... WHERE 连接条件 # 修改李四所在部门为企划部 update departments as d inner join employees as e on d.dept_id=e.dept_id set d.dept_name='企划部' where e.name='李四';
删除记录
删除单表记录 语法：
DELETE FROM 表名 WHERE 筛选条件;
删除的是满足条件的整行记录，而不是某个字段
删除重复的电子邮箱
表: Person
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id是该表的主键列。 该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。
以 任意顺序 返回结果表。 （注意： 仅需要写删除语句，将自动对剩余结果进行查询）
查询结果格式如下所示。
示例 1:
输入: Person 表: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ 输出: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+ 解释: john@example.com重复两次。我们保留最小的Id = 1。 delete p1.* from Person p1,Person p2 where p1.email=p2.email and p1.id>p2.id
删除多表记录
语法：
DELETE 表1别名, 表2别名 FROM 表1 AS 表1别名 INNER | LEFT | RIGHT JOIN 表2 AS 表2别名 ON 连接条件 WHERE 筛选条件 # 删除9号部门中所有的员工 delete e from employees as e inner join departments as d on e.dept_id=d.dept_id where d.dept_id=9; Query OK, 2 rows affected (0.00 sec)
清空表
语法：
TRUNCATE TABLE 表名 TRUNCATE不支持WHERE条件
- 自增长列，TRUNCATE后从1开始；DELETE继续编号
- TRUNCATE不能回滚，DELETE可以
- 效率略高于DELETE
数据库管理
创建数据库
语法：
CREATE DATABASE [IF NOT EXISTS] <数据库名> [[DEFAULT] CHARACTER SET <字符集名>] [[DEFAULT] COLLATE <校对规则名>];
- [ ]中的内容是可选的
- <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。
- IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
- [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
- [DEFAULT] COLLATE：指定字符集的默认校对规则。
- MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。
修改数据库
语法：
ALTER DATABASE [数据库名] { [ DEFAULT ] CHARACTER SET <字符集名> | [ DEFAULT ] COLLATE <校对规则名>}
- ALTER DATABASE 用于更改数据库的全局特性。
- 使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
- 数据库名称可以忽略，此时语句对应于默认数据库。
- CHARACTER SET 子句用于更改默认的数据库字符集。
删除数据库
语法：
DROP DATABASE [ IF EXISTS ] <数据库名>
- <数据库名>：指定要删除的数据库名。
- IF EXISTS：用于防止当数据库不存在时发生错误。
- DROP DATABASE：删除数据库中的所有表格并同时删除数据库。
- 如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。
关系数据库的规范化
数据库设计的三大范式
第一范式：要求表的每个字段必须是不可分割的独立单元。
第二范式：在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
第三范式：在第二范式的基础上，要求每张表的主键之外的其它字段都只能和主键有直接决定依赖关系。
修改表
修改列名
语法：
ALTER TABLE 表 CHANGE [COLUMN] 列表 数据类型
修改列的类型或约束
语法：
ALTER TABLE 表 MODIFY [COLUMN] 列名 类型
添加新列
语法：
ALTER TABLE 表 ADD [COLUMN] 列名 类型
删除列
语法：
ALTER TABLE 表 DROP [COLUMN] 列名
修改表名
语法：
ALTER TABLE 表名 RENAME TO 新表名
删除表
语法：
DROP TABLE [IF EXISTS] 表名
表复制
仅复制表结构
语法：
CREATE TABLE 待创建的表名 LIKE 已有表名
复制表结构及数据
语法：
CREATE TABLE 待创建的表名 SELECT 字段, ... FROM 已有表名
约束
约束分类
- PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
- NOT NULL ：非空，用于保证该字段的值不能为空。
- DEFAULT：默认值，用于保证该字段有默认值。
- UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。
- FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。
约束可应用在列级或表级。列表所有约束均支持，但外键约束没有效果；表级约束可以支持主键、唯一、外键约束。
删除约束
语法：
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
事务控制语言
事务
- 也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来保证数据的一致性。
事务的特性（ACID）
- 原子性(Atomicity)：事务就像原子一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。
- 一致性(Consistency)：一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。
- 隔离性(Isolation)：一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户取钱，通过事物的隔离性，确保账户余额的正确性。
- 持久性(Durability)：也称永久性，指事务一旦提交，对数据的改变就是永久的，不可以被在回滚。
