# lanzhoutbc
**Repository Path**: azureroots/lanzhoutbc
## Basic Information
- **Project Name**: lanzhoutbc
- **Description**: 兰州工商学院数据库实践课程集中实训
- **Primary Language**: SQL
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No
## Statistics
- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-11-07
- **Last Updated**: 2025-11-12
## Categories & Tags
**Categories**: Uncategorized
**Tags**: None
## README
## 1、准备环境
## 2、权限操作
### 2.0、 创建数据库
在 MySQL 5.x 中使用以下语句来创建数据库:
```sql
create database 数据库名 default character set utf8mb4 ;
```
在 MySQL 8.x 中可以将 `default character set utf8mb4` 省略
```sql
create database 数据库名;
```
### 2.1、创建用户
在 MySQL 5.x 中可以通过以下方式创建用户:
```sql
create user 用户名 identified by '密码' ;
```
( 这里注意，密码必须使用引号包起来 )
### 2.2、删除用户
```sql
drop user 用户名 ;
```
### 2.3、修改用户名
```sql
rename user 原用户名 to 新用户名 ;
```
### 2.4、授予权限
```sql
grant 权限名 (columns) on 权限对象 to 用户名@域 [ identified by '密码' ] ;
```
(权限名称 和 权限对象 比较多，参见 《MySQL 技术内幕 第四版》 12.4 )
我们采用以下形式为用户授权:
```sql
grant all on 数据库名称.* to 用户名@域 identified by '密码';
```
例如，假设存在数据库 taobao ，存在用户 malajava ，把 taobao 数据库的所有权限授予 malajava 用户 :
```sql
grant all on taobao.* to malajava@localhost identified by 'malajava' ;
```
### 2.5、查看当前用户
```sql
select user() ;
```
### 2.6、查看用户的权限
```sql
show grants for '用户名'@'域'
```
查看当前用户的权限:
```sql
show grants ;
```
### 2.7、修改 root 用户的密码
第一种方法:
```sql
mysql> set password for 'root'@'localhost' = password('新密码');
```
第二种方法:
```sql
mysql> use mysql ;
mysql> update user set password = password('newpass') where user = 'root';
mysql> flush privileges ;
mysql> PRIVILEGES;
```
## 3、数据库操作
### 3.1、创建数据库
在 MySQL 8 之前，创建数据库时建议使用以下语句:
```sql
create database 数据库名 default character set 字符集编码名称;
```
主要是通过 `default characte set ` 来设置数据库所采用的字符编码方案。
例如：建立一个名为 `lanzhoutbc`的数据库
```sql
create database `lanzhoutbc` ;
```
创建 `lanzhoutbc` 数据库并指定数据库的字符集编码:
```sql
create database `lanzhoutbc` default character set utf8mb4;
```
这里需要注意的是，在 MySQL 使用的 utf8 并不是真正意义上的 `UTF-8` 。
在 MySQL 中使用真正的 `UTF-8` 应该指定为 `utf8mb4` 。
从 MySQL 8 开始，创建数据库时默认就采用了 `utf8mb4` (即 `UTF-8`编码):
```sql
create database lanzhoutbc ;
```
可以通过 `show create database lanzhoutbc` 来查看:
```sql
mysql> show create database lanzhoutbc ;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------------------------------------+
| lanzhoutbc | CREATE DATABASE `lanzhoutbc` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */|
+----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
### 3.2、显示所有的数据库
```sql
show databases
```
对于root用户来说，可以看到所有数据库；
对于非root用户来说，只能看到该用户拥有操作权限的数据库；
### 3.3、删除数据库
```sql
drop database 数据库名 ;
```
例如：删除名为 malajava 的数据库
```sql
drop database malajava ;
```
### 3.4、连接数据库
连接数据库也称作**选择数据库**或打开数据库，采用 use 关键字实现:
```sql
use 数据库名 ;
```
例如：如果 `malajava` 数据库存在，尝试打开它：
```sql
use malajava
```
### 3.5、查看当前数据库
查看当前正在操作的数据库(当前已经选择的数据库)
```sql
select database();
```
以上操作仅在 命令提示符 可以执行。
### 3.6、当前数据库包含的表
```sql
show tables ;
```
注意，必须先选中一个数据库，即先使用 use 打开一个数据库，比如: use malajava ，之后才可以查看该数据库中所包含的表。
### 3.7、查看创建数据库时的相关信息
```sql
show create database 数据库名称 ;
```
## 4、表操作
操作表之前，必须先选中相应的数据库( 使用 `use` 命令，比如 `use malajava` )
### 4.1、创建表
```sql
create table 表名 ( 列名 数据类型(列宽) , ... ) ;
```
比如创建 student 表:
```sql
create table student ( id int primary key , name varchar(20) ) ;
```
### 4.2、查看表结构
语法:
```sql
describe 表名 ;
```
其中，describe 可以缩写为 desc 。
举例(查看 student 表的表结构):
```sql
desc student ;
```
### 4.3、增加列
语法:
```sql
alter table 表名 add column ( 列名 类型 约束 ) ;
```
比如，为 `t_students` 表添加 `address` 列 :
```sql
alter table `t_students` add column ( `address` varchar(50) );
```
### 4.4、修改列
#### 4.4.1 修改列类型
语法:
```sql
alter table 表名 modify 列名 类型(列宽) ;
```
举例(修改 student 表的 address 列的 类型 和 宽度 ):
```sql
alter table `t_students` modify `address` varchar(100) ;
```
#### 4.4.2 修改列名
语法:
```sql
alert table 表名 change column 原列名 新列名 类型 ;
```
举例(将 t_students 表的 address 列名 修改为 hometown ):
```sql
alert table `t_students` change column `address` `hometown` varchar(100) ;
```
### 4.5、删除列
语法:
```sql
alter table 表名 drop column 列名 ;
```
举例 ( 删除 t_students 表中的 hometown 列 ) :
```sql
alter table `t_students` drop column `hometown` ;
```
### 4.6、删除表
语法:
```sql
drop table 表名 ;
```
举例( 删除 t_students 表 ):
```sql
drop table `t_students` ;
```
### 4.7、截断表
语法:
```sql
truncate table 表名 ;
```
举例( 截断 `t_students` 表 ):
```sql
truncate table `t_students` ;
```
### 4.8、重命名表
语法:
```sql
rename table 原表名 to 新表名 ;
```
举例( 将 t_students 重命名为 students ):
```sql
rename table t_students to students ;
```
### 4.9、查看建表时的相关信息
语法:
```sql
show create table 表名 ;
```
举例:
```sql
show create table t_students ;
```
## 5、数据操作
### 5.1、插入数据
语法:
```sql
insert into 表名 ( 列名 , ... ) values ( 数值 , ... ) ;
```
举例:
```sql
insert into t_students ( id , name , gender ) values ( 1 , '张无忌' , '男' ) ;
```
批量插入数据语法:
```sql
insert into 表名 ( 列名 , ... ) values ( 数值 , ... ) , ( 数值 , ... ),( 数值 , ... ) , ... ;
```
举例:
```sql
insert into t_students ( id , name ) values ( 2 , '周芷若' ) , ( 3 , '赵敏' ) ;
```
### 5.2、更新数据
语法:
```sql
update 表名 set 列名 = 取值 , ... [ where 条件 ] ;
```
其中的 where 子句并不是必须的，可以根据实际需要来确定是否需要跟 where 子句。
举例:
```sql
update t_students set name='敏敏特穆尔' , gender = '女' where id = 3 ;
```
注意，如果在 update 语句中没有使用 where 子句筛选，则会更新整张表中的每一行数据。
### 5.3、删除数据
语法:
```sql
delete from 表名 where 条件 ;
```
其中的 where 子句不是必须的，根据实际需要来确定是否需要跟 where 子句。
举例( 删除 t_students 表中 id 为 3 的记录 ):
```sql
delete from t_students where id = 3 ;
```
注意，如果在 delete 语句中没有使用 where 子句筛选，则会删除整张表中的所有数据。
如果要删除一张表中的所有数据，使用 delete from 效率是较低的，建议使用 truncate table 。
## 6、数据查询
### 6.1、数据查询
#### 6.1.1、简单查询
##### (1)、查询部分列
如果需要在查询结果中仅包含表中部分列，可以在 `SELECT` 关键字之后显式书写这些列的列名:
```sql
SELECT deptno, dname FROM dept;
SELECT empno, ename, sal FROM emp;
```
##### (2)、查询全部列
如果需要查询表中所有列，可以在 `SELECT` 之后使用 `*` 来表示所有列:
```sql
SELECT * FROM emp;
SELECT emp.* FROM emp;
```
此处 `*` 就表示所有列。
##### (3)、算术运算
在 SQL 语句中可以通过算术运算符实现运算:
- 求 `emp` 表中所有员工的 年薪
```sql
SELECT empno, ename, sal * 12 FROM emp;
```
- 求 `emp` 表中所有员工的 周薪
```sql
SELECT empno, ename, sal / 4 FROM emp;
```
需要注意的是，MySQL 8.4.x 中允许通过 `%` 和 `mod` 来求模:
```sql
SELECT 5%2, mod(5,2) FROM dual;
```
##### (4)、虚表
从 MySQL 8 开始，允许使用一个名称为 `dual` 的 续表 来辅助查询。
- 执行算术运算
```sql
SELECT mod( 5, 2 ) FROM dual;
```
- 查询时间
```sql
SELECT sysdate(), cureent_date(), current_time(), current_timestamp() FROM dual;
```
- 查询当前数据库
```sql
SELECT database() FROM dual;
```
##### (5)、列的别名
在 `SELECT` 中可以为列指定别名:
```sql
SELECT empno 工号, ename AS 姓名, sal * 12 AS 年薪 FROM emp;
```
为列指定别名时，可以使用 `AS` 关键字，也可以省略。
##### (6)、拼接字符串
在 MySQL 数据库中可以通过 `concat` 函数来拼接字符串。
```sql
select concat(first_name, concat( '·', last_name) ) from s_emp;
```
##### (7)、处理空值
数据库中的空值既不是空字符串，也不是数字0，而是没有值，是未知值。
因此，尽管在插入数据时可以使用 null 来表示该列的值是个空值，但两个都为空的值是不能直接比较的( 判断某个值是否为空需要使用 is null 而不是 x = null )。
因为不能直接比较空值，所以在主流的数据库中都会提供相应的函数来处理空值。
MySQL 数据库中提供了 `ifnull` 函数来处理空值。
- 查询 `emp` 表中所有雇员的工号、姓名、领导编号(若某个员工没有领导则显示`这是老板`)
```sql
SELECT empno, ename , ifnull( manager, '这是老板' ) FROM emp ;
```
##### (8)、去除重复行
去除查询结果中重复的数据行，可以通过 `DISTINCT` 关键字来完成。
- 查询 `emp` 表中所有的部门号 (含重复行)
```sql
SELECT ALL deptno FROM emp;
```
此处的 `ALL` 是默认值，通常是省略的。
- 查询 `emp` 表中所有的部门号 (去除重复行)
```sql
SELECT DISTINCT deptno FROM emp;
```
- 查询 `emp` 表中的 部门 和 岗位 (含重复行)
```sql
SELECT deptno, job FROM emp;
```
此处 `SELECT` 之后并没有显式书写 `ALL` ，因为 `ALL`是默认值，所以效果是一样的。
- 查询 `emp` 表中的 部门 和 岗位 (去除重复行)
```sql
SELECT DISTINCT deptno, job FROM emp;
```
#### 6.1.2、数据过滤
在本节之前所做的所有查询都是没有带任何过滤条件的查询，因此查询结果中包含被查询表中的所有数据行。
如果需要对表中的数据进行筛选，可以使用 SELECT 语句中的 WHERE 子句实现数据过滤，从而只返回那些满足条件的数据行。
其语法格式为:
```sql
SELECT column_name [ , … ]
FROM table_name
WHERE condition_expression ;
```
其中:
- `column_name` 为需要查询的列名
- `table_name` 为被查询表的表名称
- `condition_expression` 是筛选数据时所使用的条件
在WHERE子句指定的条件中，通常需要使用比较运算符、SQL运算符、逻辑运算符。
##### (1)、⽐较运算符
可以在 where 字句后面实用的比较运算符，包括：
| 运算符 | 含义 | 举例 |
| -------- | ---------- | -------------- |
| = | 等于 | deptno = 20 |
| <> 或 != | 不等于 | deptno != 10 |
| < | 小于 | salary > 1500 |
| > | 大于 | salary < 1500 |
| <= | 小于或等于 | salary <= 1500 |
| >= | 大于或等于 | salary >= 1500 |
应用举例：
- 查询 `emp` 表中部门号为 `20` 的雇员信息，列出其工号、姓名、月薪、岗位
```sql
SELECT empno, ename, sal, job FROM emp WHERE deptno = 20 ;
```
- 查询 `emp` 表中月薪高于 1500，低于 3000 的雇员信息，列出其工号、姓名、月薪、岗位
```sql
SELECT empno, ename, sal, job
FROM emp
WHERE sal > 1500 AND sal < 3000 ;
```
- 查询 `emp` 表中月薪不低于 1500，不高于 3000 的雇员信息，列出其工号、姓名、月薪、岗位
```sql
SELECT empno, ename, sal, job
FROM emp
WHERE sal >= 1500 AND sal <= 3000 ;
```
##### (2)、SQL 运算符
使用SQL 运算符可以基于字符串的模式匹配、值的列表、值的范围和是否空值等情况来过滤数据。
MySQL 中常用的 SQL 运算符有:
| 运算符 | 描述 |
| -------------------- | -------------------------------------- |
| like | 按照指定的模式匹配(比如模糊查询) |
| in | 匹配的值的清单 in( list ) |
| between .... and ... | 匹配的值的范围 ( between ... and ... ) |
| is null | 与空值匹配 |
| is nan | 与非数字匹配 ( nan: not a number ) |
应用举例:
- 查询 `emp` 表中月薪不低于 1500，不高于 3000 的雇员信息，列出其工号、姓名、月薪、岗位
```sql
SELECT empno, ename, sal, job
FROM emp
WHERE sal BETWEEN 1500 AND 3000 ;
```
- 查询 `emp` 表中 **提成为空** 的雇员信息，列出其工号、姓名、月薪、岗位、提成
```sql
SELECT empno, ename, sal, job, comm
FROM emp
WHERE comm IS NULL;
```
- 查询 `emp` 表中 **提成不为空** 的雇员信息，列出其工号、姓名、月薪、岗位、提成
```sql
SELECT empno, ename, sal, job, comm
FROM emp
WHERE comm IS NOT NULL;
```
- 查询 `emp` 表中 **没有提成** 的雇员信息，列出其工号、姓名、月薪、岗位、提成
```sql
SELECT empno, ename, sal, job, comm
FROM emp
WHERE comm IS NULL OR comm = 0 ;
```
此处需要注意，**没有提成** 是指 **提成为空** 以及 **提成等于 0** 的两类数据。
- 查询 `emp` 表中 10、20 两个部门的 工号、姓名、岗位、部门号
```sql
SELECT empno, ename, job, deptno
FROM emp
WHERE deptno IN (10, 20);
```
- 查询 `s_emp` 表中 `last_name` 第三个字母是 `a` 的雇员的 `id` 和`last_name`
```sql
SELECT id, last_name
FROM s_emp
WHERE lower(last_name) LIKE '__a%';
```
- 查询 `s_emp` 表中 `last_name` 包含 `_` 的雇员信息
```sql
SELECT id, last_name
FROM s_emp
WHERE lower(last_name) LIKE '%\_%';
```
此处使用 `\` 对 `_` 进行转义。
- 自定义转义字符
```sql
SELECT id, last_name
FROM s_emp
WHERE lower(last_name) LIKE '%\_%' ESCAPE '\' ;
```
##### (3)、逻辑运算符
逻辑运算符有 3 个:
| 运算符 | 描述 |
| ------ | -------------------------------------------------------- |
| AND | 与，当两个条件都为 真 时，结果为 真 ( Java : && ) |
| OR | 或，当两个条件至少有一个为真时，结果为真 ( Java : \|\| ) |
| NOT | 取反 ( Java: ! ) |
通常，使用逻辑运算符连接多个条件时，一定要考虑条件的优先级和执行顺序。
在复合条件中，优先级高的条件先执行；
如果优先级相同，按照条件出现的先后顺序执行(从左至右)；
如果复合条件使用了括号，那么括号内的优先级别高，因此先予执行；
对于逻辑运算符来说，从高到底依次是：`not > and > or`
请务必注意 `or` 的优先级是最低的:
```sql
SELECT id, last_name, salary, dept_id
FROM s_emp
where dept_id = 42 and salary > 1000 or 'abc' = 'abc';
```
这里的 `'abc' = 'abc'` 单纯是为了说明 `or` 的优先级比较低而指定的。
#### 6.1.3、数据分组
##### (1)、组函数
此处仅关注几个常用的组函数:
- `max` 求同一分组中的最大值
- `min` 求同一分组中的最小值
- `avg` 求同一分组中的平均值
- `sum` 对同一组数据求和
- `count` 统计同一分组中的数据个数
- 如果统计的是表中的指定列，当列为空时则会忽略该行数据
应用举例:
- 统计 `emp` 表中所有员工的最低月薪、最高月薪、平均月薪
```sql
SELECT min(sal), max(sal), avg(sal) FROM emp;
```
白话: 如果查询语句中没有 `group by` 子句，则可以认为是将整个表作为一个分组来对待。
- 统计 `emp` 表中有部门号的那些员工的最低月薪、最高月薪、平均月薪
```sql
SELECT min(sal), max(sal), avg(sal)
FROM emp
WHERE deptno IS NOT NULL;
```
白话: 这里要把 `deptno` 不为空的所有员工当作一个分组来对待。
- 统计 `emp` 表中有多少人**提成不为空**
```sql
SELECT count(comm) FROM emp;
```
- 统计 `emp` 表中有多少人的提成大于零
```sql
SELECT count(comm) FROM emp WHERE comm > 0 ;
```
使用组函数时，需注意：
- 组函数**只能**出现在`SELECT`子句、`HAVING`子句、`ORDER BY`子句中
- `SELECT`子句是对查询结果进行“挑选”，此时已经分组，因此可以使用组函数
- `HAVING`子句必须跟在`GROUP BY`之后，此时已经分组，可以用组函数对分组后的数据进行处理
-
- 组函数**不能**出现在 `WHERE` 子句和 `GROUP BY` 子句中
- 不能出现在`WHERE`子句中是因为`WHERE`是对表中数据首次筛选，此时尚未分组
- 不能出现在 `GROUP BY` 子句中是因为分组正在进行中
##### (2)、group by ⼦句
如果需要按照指定条件进行分组，则可以在 查询语句中使用 `group by` 来实现：
```sql
SELECT 列 [, ...]
FROM 表名
[WHERE 过滤条件]
GROUP BY 分组条件 [ , ....]
```
比如，根据 `deptno` 进行对 `emp` 表中的员工进行分组，统计各部门的员工数:
```sql
SELECT deptno, job, count(ename)
FROM emp
GROUP BY deptno, job ;
```
在`SELECT`子句中的列，要么是曾经作为分组条件在`GROUP BY` 子句中出现过的，要么必须经过组函数处理的。
- 前面举例中 `deptno` 就是在 `group by` 子句中出现过的
- 前面举例中的 `ename` 虽然没有在 `group by` 中出现，但是它被 `count` 函数所处理
分组条件中可以使用多个列，只需要在 `group by` 之后依次书写这些列并用逗号隔开即可。
比如，查询 `emp` 表中 每个部门各个岗位 的平均工资:
```sql
SELECT deptno, job, avg(sal)
FROM emp
GROUP BY deptno, job;
```
##### (3)、having ⼦句
在 `GROUP BY` 子句中可以使用 `HAVING` 子句对分组后的数据再次过滤:
```sql
SELECT 列 [, ...]
FROM 表名
[WHERE 过滤条件]
GROUP BY 分组条件 [ , ....]
HAVING 过滤条件 ;
```
比如，查询 `emp` 表中平均工资高于 2000 的部门的部门号和平均工资:
```sql
SELECT deptno, avg(sal)
FROM emp
GROUP BY deptno
HAVING avg(sal) > 2000 ;
```
#### 6.1.4、数据排序
数据排序通过 `ORDER BY` 子句实现，其中 `ASC` 表示 升序，`DESC` 表示降序。
```sql
SELECT 列 [, ...]
FROM 表名
[ WHERE 过滤条件 ]
[ GROUP BY 分组条件 [ HAVING 过滤条件 ] ]
[ ORDER BY 排序条件 ];
```
比如查询 `emp` 表中所有雇员的工号、姓名、入职日期，并根据入职日期升序排列:
```sql
SELECT empno, ename , hiredate FROM emp ORDER BY hiredate ASC;
```
此处的 `ASC` 是可以省略的，因为默认的就是 升序排列。
应用举例:
- 查询`emp`表中所有员工的 工号、姓名、年薪，并根据年薪降序排列
```sql
SELECT empno 工号, ename 姓名, sal * 12 AS 年薪
FROM emp
ORDER BY sal DESC;
```
- 在 `ORDER BY` 之后可以使用 表达式
```sql
SELECT empno 工号, ename 姓名, sal * 12 AS 年薪
FROM emp
ORDER BY sal * 12 DESC;
```
- 在 `ORDER BY` 之后可以使用 列的别名
```sql
SELECT empno 工号, ename 姓名, sal * 12 AS 年薪
FROM emp
ORDER BY 年薪 DESC;
```
- 在 `ORDER BY` 之后可以跟查询结果中各个列的索引
```sql
SELECT empno 工号, ename 姓名, sal * 12 AS 年薪
FROM emp
ORDER BY 3 DESC;
```
- 查询`emp`表中所有员工的工号、姓名、年薪，根据年薪降序、姓名升序排列
```sql
SELECT empno 工号, ename 姓名, sal * 12 AS 年薪, deptno 部门号
FROM emp
ORDER BY 3 DESC, 2 ASC;
```
#### 6.1.5、分页查询
在 `MySQL`中可以通过 `LIMIT` 语句实现分页查询:
```sql
SELECT 列名
FROM 表名
[ WHERE 查询条件 ]
[ GROUP BY 分组条件 [HAVING 筛选条件] ]
[ ORDER BY 排序条件 ]
LIMIT 起始位置, 该页最多行数;
```
应用举例:
- 查询`emp`表所有数据，显示第 `1` 页，每页最多显示 `5` 条数据
```sql
SELECT empno, ename, deptno, job
FROM emp
LIMIT 0, 5 ;
```
- 查询`emp`表所有数据，显示第 `2` 页，每页最多显示 `5` 条数据
```sql
SELECT empno, ename, deptno, job
FROM emp
LIMIT 5, 5 ;
```
- 查询`emp`表所有数据，显示第 `3` 页，每页最多显示 `5` 条数据
```sql
SELECT empno, ename, deptno, job
FROM emp
LIMIT 10, 5 ;
```
- 查询`emp`表所有数据，显示第 `4` 页，每页最多显示 `5` 条数据
```sql
SELECT empno, ename, deptno, job
FROM emp
LIMIT 15, 5 ;
```
### 6.2、高级查询
#### 6.2.1、多表查询
实际应用中，经常会碰到需要查询的数据存在于两个或两个以上的表中的情况，这时就需要使用 select 查询多张表。多表查询比单表查询要复杂许多，本节将详解多表查询技术。
> 国内资料上可能将 多表查询 称作 多表联查。
为了尽可能简单地说明问题，我们创建表结构非常简单的两张表:
- 创建专业表
```sql
CREATE TABLE `t_majors` (
`id` INT auto_increment,
`name` VARCHAR(50),
PRIMARY KEY(`id`)
);
```
- 创建班级表
```sql
CREATE TABLE `t_classes` (
`id` INT auto_increment ,
`name` VARCHAR(50) ,
`major_id` INT(5) ,
FOREIGN KEY( `major_id` ) REFERENCES `t_majors`(`id`),
PRIMARY KEY(`id`)
);
```
创建表之后，分别向专业表(t_majors)和班级表(t_classes)中插入数据:
- 插入专业
```sql
INSERT INTO t_majors ( id , name ) VALUES ( 1 , '软件' );
INSERT INTO t_majors ( id , name ) VALUES ( 2 , '金融' );
```
- 插入班级
```sql
INSERT INTO t_classes ( id , name , major_id ) VALUES ( 1 , '开发' , 1 );
INSERT INTO t_classes ( id , name , major_id ) VALUES ( 2 , '测试' , 1 );
INSERT INTO t_classes ( id , name , major_id ) VALUES ( 3 , '会计' , NULL );
```
专业表中数据结构如下:
| ID | NAME |
| ---- | ---- |
| 1 | 软件 |
| 2 | 金融 |
班级表中数据结构如下:
| ID | NAME | MAJOR_ID |
| ---- | ---- | -------- |
| 1 | 开发 | 1 |
| 2 | 测试 | 1 |
| 3 | 会计 | |
以上准备完毕，即可开始后续学习。
##### (1)、表的别名
表的别名是指在 查询语句 中为表定义的**临时名称**，目的是为了简化对表的引用，方便在其他地方使用。
为表指定别名，只需要在表名称之后使用空格隔开，直接指定别名即可:
```sql
SELECT m.id , m.name FROM t_majors m ;
```
以上查询语句中，为被查询的表 `t_majors` 表取了别名`m`并在`select`子句中予以使用。
表的别名，在使用时以简短为第一原则，兼顾"见名知义"，力求简单而又能明显地把不同的表的别名区分开；
```sql
SELECT m.id , m.name , c.id , c.name FROM t_majors m , t_classes c ;
```
这里可以看到，我们可以使用 m.name 、c.name 的方式引用表中的字段，而不需要使用 t_majors.name 和 t_classes.name ，这样的写法在后面即将看到的连接查询非常有用！
表的别名使用规则:
- `where` 子句之后可以跟 表的别名，但不可以跟 列的别名 ；
- `group by` 子句之后可以跟 表的别名 ，但不可以跟 列的别名 ；
- `order by` 子句之后可以跟 表的别名，也可以跟 列的别名，甚至是列的位置 。
##### (2)、笛卡尔乘积
在集合运算中，集合与集合之间的乘积被称为**笛卡尔乘积**。
对于下面查询来说:
```sql
SELECT * FROM t_classes, t_majors ;
```
如果把 `t_classes` 中的记录依次排列到 `x` 轴，把 `t_majors` 表中的记录依次排列到 `y` 轴，在两张表的每条记录点上画出与 `x` 轴、`y` 轴平行的线条，则它们相交的点组成的集合就是**笛卡尔乘积**。
在上面的询语句中，`FROM`关键字之后使用了两张表，类似这种查询两张表或查询两张以上的表的操作，即为**连接查询**（国内资料可能称作**多表查询**或**多表连查**）。
以上查询中，因为没有使用任何条件对数据进行筛选，因此所产生的结果即为笛卡尔乘积，这种查询结果是我们将来需要极力避免的。为了避免这种结果，我们可以在查询语句中使用 *连接条件* 来筛选数据。
所谓**连接条件**，就是用来**避免**产生笛卡尔乘积的条件，它通常是采用两张表中有关联的两个或多个列进行比较。
当查询结果中的所有数据全部都满足连接条件时，这种查询被称作**内连接**(`inner join`)。
当查询结果中除了满足连接条件的数据外，还包含部分不满足条件的数据时，这种查询被称作**外连接**(`outter join`)。
在标准SQL中采用以下形式实现连接查询:
```sql
SELECT 列名
FROM 左表
INNER | [ LEFT OUTER | RIGHT OUTER| FULL OUTER] JOIN 右表
ON 连接条件;
```
注意:
- `JOIN` 可以视为 `FROM` 的子句
- 在 `JOIN` 之前的表就是所谓的 “左表”
- 在 `JOIN` 之后的表就是所谓的 “右表”
- `ON` 可以视为 `JOIN` 的子句
- 在 `ON` 关键字之后就是所谓的 “连接条件”
##### (3)、内连接
查询结果中全部都是满足连接条件的数据行。
###### 等值连接
查询每个员工及其部门，列出工号、姓名、部门号、部门名称。(查询 `emp` 表和`dept`表)
```sql
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno ;
```
其中 `INNER JOIN` 是默认操作，因此可以省略 `INNER` 关键字:
```sql
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e
JOIN dept d
ON e.deptno = d.deptno ;
```
此处的连接条件为 `e.deptno = d.deptno` 。
###### 非等值连接
查询`emp`表中各员工的月薪及薪资等级。
```sql
SELECT e.empno, e.ename, e.sal, g.grade
FROM emp e
INNER JOIN salgrade g
ON e.sal >= g.losal AND e.sal < g.hisal ;
```
注意，这里的连接条件是 `e.sal >= g.losal AND e.sal < g.hisal` ，它表示 `e.sal` 在一个区间内，而不是等于具体某个值。
##### (4)、外连接
查询结果中除了完全满足连接条件的数据外，还包含了不满足连接条件的数据行。
###### 左外连接
在查询结果中除了完全满足连接条件的数据外，还包含了**左表**中不满足条件的数据行。
查询各员工及其部门信息，列出工号、姓名、部门号、部门名称，若某个员工没有部门号，也将其包含在查询结果中。
```sql
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno = d.deptno ;
```
其中 `OUTER` 关键字可以省略。
###### 右外连接
在查询结果中除了完全满足连接条件的数据外，还包含了**右表**中不满足条件的数据行。
查询各员工及其部门信息，列出工号、姓名、部门号、部门名称，若某个部门没有员工，也将其包含在查询结果中。
```sql
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno ;
```
###### 全外连接
在 标准SQL 中的 全外连接写法为:
```sql
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e
FULL OUTER JOIN dept d
ON e.deptno = d.deptno ;
```
MySQL 确实**不直接支持 `FULL OUTER JOIN` 语法**（这是 MySQL 的历史设计限制，直到最新版本仍未原生支持），但可以通过「`LEFT JOIN + RIGHT JOIN + UNION`」的组合方式**模拟全外连接**，效果完全等价于原生全外连接。
```sql
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno
UNION
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno ;
```
#### 6.2.2、子查询
## 7、事务控制
### 7.1、数据库引擎
MySQL 常用引擎: MyISAM 和 InnoDB 。
其中，InnoDB 是支持事务操作的 引擎，因此建表时建议使用该引擎:
```sql
create table 表名 ( 列名 类型(列宽) 约束 , ... ) engine = InnoDB ;
```
如果建表时没有指定引擎，早期的MySQL默认采用 MyISAM 引擎，该引擎默认不支持事务操作。
故建完表后需要修改引擎:
```sql
alter table 表名 type = InnoDB ;
```
或者使用:
```sql
alter table 表名 engine = InnoDB ;
```
查看修改结果:
```sql
show table status from 数据库名 ;
```
或者使用:
```sql
show create table 表名 ;
```
注意:
我们使用的 MySQL 5.6 及 5.7 、8.x 默认的引擎都是 InnoDB , 因此，建表时无须指定，建表后也无须修改。
### 7.2、事务隔离级别
#### 7.2.1、ANSI/ISO SQL标准定义的 4 种事务隔离级别
| read uncommited | 读未提交 |
| read commited | 读已提交 |
| repeatable read | 可重复读 |
| serializable | 序列化 |
#### 7.2.2、查看当前事务的隔离级别:
```sql
SELECT @@transaction_isolation;
```
#### 7.2.3、设置 InnoDB 的事务级别
第一种方法:
```sql
set 作用域 transaction isolation level 事务隔离级别 ;
```
第一种方法举例:
```sql
set global transaction isolation level read committed; -- global 作用域表示全局的
set session transaction isolation level read uncommitted ; -- session 作用域表示当前会话
set session transaction isolation level read committed;
set session transaction isolation level repeatable read ;
set session transaction isolation level serializable ;
```
第二种方法:
```sql
set 作用域 transaction_isolation = '事务隔离级别' ;
```
第二种方法举例:
```sql
set global transaction_isolation = 'repeatable-read'; -- 全局 ( global )
set session transaction_isolation = 'read-uncommitted'; -- 当前会话 ( session )
set session transaction_isolation = 'read-committed';
set session transaction_isolation = 'serializable';
```
其中的作用域:
- global 表示全局，修改后所有的会话都将生效
- session 表示当前会话，修改后仅针对当前会话有效
修改事务隔离级别后，使用 select @@tx_isolation 来查看修改后的隔离级别。
### 7.3、事务控制语句(TCL)
#### 7.3.1、显式开启一个事务
```sql
begin ;
```
或:
```sql
start transaction ;
```
注意，以上语句在 Oracle 中不适用。
#### 7.3.2、提交事务
```sql
commit ;
```
#### 7.3.3、回滚事务
```sql
rollback ;
```
#### 7.3.4、创建保存点
```sql
savepoint 保存点名称 ;
```
#### 7.3.5、删除某个事务保存点
```sql
release savepoint 保存点名称 ;
```
#### 7.3.6、闪回(回滚)到某个保存点
```sql
rollback to 保存点名称 ;
```
#### 7.3.7、设置事务隔离级别:
参看 7.2.3
#### 7.3.8、禁用 mysql 的自动提交
默认情况下，MySQL默认是自动提交事务的，可以通过以下语句来修改它:
```sql
set autocommit = false ; -- 不要再自动提交
set autocommit = true ; -- 自动提交 (默认值)
```
#### 7.3.9、查看自动提交状态
```sql
mysql> select @@autocommit ;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
```
其中，1 表示 true (自动提交) ，0 表示false (不自动提交)。
### 7.4、隐式提交事务的SQL
当执行 DDL 语句 或 DCL 时，会导致当前未结束的事务被提交。
(DDL ，数据定义语言，比如 create table 、alter table 、drop table 、truncate table )
(DCL ，数据控制语言，比如 drop user 、grant 等)
## 8、备份与恢复
备份数据通常在命令提示符或终端下，使用 mysqldump 命令完成。
### 8.1、导出整个数据库
```sql
C:\> mysqldump -u 用户名 -p --default-character-set=latin1 数据库名 > 导出的文件名
```
数据库默认的字符编码为 latin1 ，如果创建数据库时指定了编码，请保持一致。
举例:
```sql
C:\> mysqldump -u root -p malajava > C:/malajava.sql
```
### 8.2、导出一张表
```sql
C:\> mysqldump -u 用户名 -p 数据库名 表名 > 导出的文件名
```
举例:
```sql
C:\> mysqldump -u root -p malajava t_students > C:/students.sql
```
### 8.3、导出一个数据库结构
```sql
C:\> mysqldump -u root -p -d –add-drop-table malajava > C:/malajava.sql
```
其中:
- -d 表示仅仅导出表结构，不导出表中的数据
- –add-drop-table 表示在每个 create 语句之前增加一个 drop table 语句
### 8.4、导入数据库
在 mysql 提示符下，使用 source 命令即可导入相应的 sql 脚本:
```sql
mysql> source 数据库脚本 ;
```
举例:
```sql
mysql> source C:/malajava.sql
```
## 9、常用数据类型
《待整理》