数据库MySQL（基础四）

7. 多表查询

7.1 表的映射关系

1. 一对一
2. 一对多
3. 多对多

7.2 多表的查询操作

• union 链接多条sql语句 去重重复数据
  • 三条数据 两条数据重复去除重复的一条数据

mysql> select * from student union select * from student where stuid = 2;
+-------+---------+-------+
| stuid | stuname | teaid |
+-------+---------+-------+
|     1 | 小明    |     1 |
|     2 | 小红    |  NULL |
+-------+---------+-------+
2 rows in set (0.00 sec)

• inner join 内连接 获取表的相同的部分的数据

mysql> select * from student inner join teacher on student.teaid = teacher.teaid;
+-------+---------+-------+-------+---------+
| stuid | stuname | teaid | teaid | teaname |
+-------+---------+-------+-------+---------+
|     1 | 小明    |     1 |     1 | 张三    |
+-------+---------+-------+-------+---------+
1 row in set (0.00 sec)

• outter join 外连接
  • 左连接
  • 右连接
• left join 左连接
  • 以左边的表为主表获取左边表所有的数据 获取右边表相同部分的数据

mysql> select * from student as stu left join teacher as tea on  stu.teaid = tea.teaid;
+-------+---------+-------+-------+---------+
| stuid | stuname | teaid | teaid | teaname |
+-------+---------+-------+-------+---------+
|     1 | 小明    |     1 |     1 | 张三    |
|     2 | 小红    |  NULL |  NULL | NULL    |
+-------+---------+-------+-------+---------+
2 rows in set (0.00 sec)

• right join 右连接

mysql> select * from student as stu right join teacher as tea on  stu.teaid = tea.teaid;
+-------+---------+-------+-------+---------+
| stuid | stuname | teaid | teaid | teaname |
+-------+---------+-------+-------+---------+
|     1 | 小明    |     1 |     1 | 张三    |
|  NULL | NULL    |  NULL |     2 | 李四    |
+-------+---------+-------+-------+---------+
2 rows in set (0.00 sec)

• 子查询 (自己查自己)
  • 先以括号中的查处的数据作为一张表在进行二次查询
  • as 起别名的意思 一般情况下as可以省略

mysql> select stubook.bookid,stubook.bookname,stubook.bookauthor,stubook.bookprice from (select * from book where bookprice < 150) as stubook where bookname like '%记%';
+--------+-----------+------------+-----------+
| bookid | bookname  | bookauthor | bookprice |
+--------+-----------+------------+-----------+
|      3 | 西游记    | 吴承恩     |       120 |
+--------+-----------+------------+-----------+
1 row in set (0.00 sec)

• 复合查询

7.3 一对多

• department
• employee

mysql> create table department(
    -> deptid int(11) primary key auto_increment,
    -> deptname varchar(25)
    -> )default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> create table employee(
    -> emid int(11) primary key auto_increment,
    -> emname varchar(25),
    -> deptid int(11),
    -> foreign key (deptid) references department(deptid)
    -> )default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into department values(null,"后勤部"),(null,"技术部"),(null,"教学部");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into employee values(null,"小明",1),(null,"小红",1),(null,"小于",2),(null,"小李",3),(null,"小王",3);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> seelct * from department;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'seelct * from department' at line 1
mysql> select * from department;
+--------+-----------+
| deptid | deptname  |
+--------+-----------+
|      1 | 后勤部    |
|      2 | 技术部    |
|      3 | 教学部    |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> select * from employee;
+------+--------+--------+
| emid | emname | deptid |
+------+--------+--------+
|    1 | 小明   |      1 |
|    2 | 小红   |      1 |
|    3 | 小于   |      2 |
|    4 | 小李   |      3 |
|    5 | 小王   |      3 |
+------+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from department join employee on department.deptid = employee.deptid where emname = "小王";
+--------+-----------+------+--------+--------+
| deptid | deptname  | emid | emname | deptid |
+--------+-----------+------+--------+--------+
|      3 | 教学部    |    5 | 小王   |      3 |
+--------+-----------+------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from department join employee on department.deptid = employee.deptid where deptname ="后勤部";
+--------+-----------+------+--------+--------+
| deptid | deptname  | emid | emname | deptid |
+--------+-----------+------+--------+--------+
|      1 | 后勤部    |    1 | 小明   |      1 |
|      1 | 后勤部    |    2 | 小红   |      1 |
+--------+-----------+------+--------+--------+
2 rows in set (0.00 sec)

7.4 多对多

多对多要通过第三张表建立两张表之间的主外键关系。

mysql> show create table teacher;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table
                                                  |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `teaid` int(11) NOT NULL AUTO_INCREMENT,
  `teaname` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`teaid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table stu_tea(
    -> teaid int ,
    -> stuid int,
    -> foreign key (teaid) references teacher(teaid),
    -> foreign key (stuid) references student(id)
    -> )default charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into stu_tea values(1,901),(1,902),(1,903),(2,901),(2,902),(2,903),(3,901),(3,902),(3,903),(3,904);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> select * from student left join stu_tea on student.id = stu_tea.stuid left join teacher on teacher.teaid = stu_tea.teaid where teaname = " 张三丰";
+-----+-----------+------+-------+--------------+--------------------+-------+-------+-------+-----------+
| id  | name      | sex  | birth | department   | address            | teaid | stuid | teaid | teaname   |
+-----+-----------+------+-------+--------------+--------------------+-------+-------+-------+-----------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |     1 |   901 |     1 | 张三丰    |
| 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |     1 |   902 |     1 | 张三丰    |
| 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |     1 |   903 |     1 | 张三丰    |
+-----+-----------+------+-------+--------------+--------------------+-------+-------+-------+-----------+
3 rows in set (0.00 sec)

8. 事务(transaction)

8.1 事务的简介

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 脏读
• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。persistence（持久化）

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

8.2 MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• start transaction 开启事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

8.3 事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

mysql> select @@tx_isolation;# 查看默认的事务隔离级别
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

修改事务隔离级别:

set session transaction isolation level read uncommitted;