MySQL 多表查询、视图、事物、索引

多表查询分类

需求：在设计数据表时，那么将现实中的一个实体设计为一个表，但在查询数据时，很多时候会将具有关系的不同实体表中的数据一同取出来。
语法：
select * from 表A,表B 【where 子句】
说明：
从多个表中获取where子句的匹配条件进行获取数据
如果省略where子句，那么得到的结果是一个笛卡尔积
笛卡尔积：
得到的结果集的记录数是：两个表的记录数的乘积
字段数：两个表的字段数的和

union联合查询

注意：

对同一个表的不同的部分进行不同的操作
一般用于对比较大的表进行分表存储，联合查询

mysql> (select * from stu where sex=1 order by age limit 999)union(select * from stu where sex=0 order by age desc limit 999);
+----+--------+----------+------+------+------+
| id | s_num  | s_name   | sex  | age  | c_id |
+----+--------+----------+------+------+------+
|  7 | it007  | songjiu  | 1    |   18 |    2 |
|  1 | it001  | zhangsan | 1    |   20 |    1 |
|  6 | it006  | wangwang | 1    |   20 |    1 |
|  9 | it009  | zhener   | 1    |   23 |    1 |
| 11 | it0011 | wu       | 1    |   23 |    3 |
|  2 | it002  | lisi     | 1    |   26 |    2 |
| 10 | it0010 | qianqian | 1    |   26 |    1 |
|  4 | it004  | zhaoliu  | 1    |   30 |    3 |
| 13 | it0013 | deng     | 0    |   34 |    5 |
|  5 | it005  | tianqi   | 0    |   28 |    1 |
| 12 | it0012 | chen     | 0    |   26 |    3 |
|  3 | it003  | wangwu   | 0    |   22 |    2 |
|  8 | it008  | zhousan  | 0    |   19 |    2 |
+----+--------+----------+------+------+------+
13 rows in set (0.00 sec)

内连接【inner join】

语法一：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段

inner是可以省略的

mysql> select * from stuinfo inner join stuMarks on stuinfo.stuNo=stuMarks.stuNo;
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)

语法二：select 列名 from 表1,表2 where 表1.公共字段=表2.公共字段

mysql> select * from stuinfo,stuMarks where stuinfo.stuNo=stuMarks.stuNo;
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)

select from 表1 inner join 表2 on 表1.公共字段=表2.公共字段和select from 表2 inner join 表1 on 表1.公共字段=表2.公共字段是否一样？
答：一样的，因为内连接显示的是两个表的公共记录。

三个表的内连接如何实现？
答：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段

左外连接【left join】

以左边的表为标准，如果右边的表没有对应的记录，用NULL填充。
语法：select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段

mysql> select * from stuinfo left join stuMarks on stuinfo.stuNo=stuMarks.stuNo;
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |
| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       | NULL    | NULL   |        NULL |    NULL |
| s25319 | 梅超风       | 女     |     23 |       5 | 河北       | NULL    | NULL   |        NULL |    NULL |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
7 rows in set (0.00 sec)

select from 表1 left join 表2 on 表1.公共字段=表2.公共字段和select from 表2 left join 表1 on 表1.公共字段=表2.公共字段是否一样？
答：不一样，第一个SQL语句以表1为准，第二个SQL语句以表2为准

右外连接【right join】

以右边的表为标准，如果右边的表没有对应的记录，用NULL填充。
语法：select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段

mysql> select * from stuinfo right join stuMarks on stuinfo.stuNo=stuMarks.stuNo;
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
5 rows in set (0.00 sec)

思考：select from 表1 left join 表2 on 表1.公共字段=表2.公共字段和select from 表2 right join 表1 on 表1.公共字段=表2.公共字段是否一样？
答：一样。

交叉连接【cross join】

1、如果没有连接表达式返回的是笛卡尔积
mysql> select * from stuinfo cross join stuMarks;
等价于
mysql> select * from stuinfo,stuMarks;
2、如果有连接表达式等价于内连接

mysql> select * from stuinfo cross join stuMarks on stuinfo.stuNo=stuMarks.stuNo;
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |
+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+

自然连接【natural】

自动的判断连接条件，它是通过同名字段来判断的
自然连接又分为：
1、自然内连接 natural join
select * from stuinfo natural join stuMarks;
2、自然左外连接 natural left join
select * from stuinfo natural left join stuMarks;
3、自然右外连接 natural right join
select * from stuinfo natural rigth join stuMarks;

自然连接结论：
1、表连接通过同名的字段来连接的
2、如果没有同名的字段返回笛卡尔积
3、会对结果进行整理，整理的规则如下
a) 连接字段保留一个
b) 连接字段放在最前面

using()

1、用来指定连接字段。
2、using()也会对连接字段进行整理，整理方式和自然连接是一样的。
select * from stuinfo inner join stuMarks using(id);

子查询

1、语法：select 语句 where 条件 (select … from 表)
2、外面的查询称为父查询，括号中的查询称为子查询
3、子查询为父查询提供查询条件

=子查询

使用表连接的方法

mysql> select stuName,writtenExam from stuinfo natural join stuMarks having writtenExam>60;
+--------------+-------------+
| stuName      | writtenExam |
+--------------+-------------+
| 李斯文       |          80 |
| 欧阳俊雄     |          65 |
| 张秋丽       |          77 |
+--------------+-------------+
3 rows in set (0.00 sec)

使用子查询方法

mysql> select * from stuinfo where stuNo in(select stuNo from stuMarks where writtenExam>60);
+--------+--------------+--------+--------+---------+------------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |
+--------+--------------+--------+--------+---------+------------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |
+--------+--------------+--------+--------+---------+------------+
3 rows in set (0.00 sec)

找出最高分

mysql> select * from stuinfo where stuNo=(select stuNo from stuMarks order by writtenExam desc limit 1);
+--------+-----------+--------+--------+---------+------------+
| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |
+--------+-----------+--------+--------+---------+------------+
| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |
+--------+-----------+--------+--------+---------+------------+
1 row in set (0.00 sec)

mysql> select * from stuinfo where stuNo=(select stuNo from stuMarks where writtenExam=(select max(writtenExam) from stuMarks));
+--------+-----------+--------+--------+---------+------------+
| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |
+--------+-----------+--------+--------+---------+------------+
| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |
+--------+-----------+--------+--------+---------+------------+
1 row in set (0.00 sec)

in|not in子查询

用于子查询的返回结果多个值

some()、any()、all()

some:一些
any:一些和some()是一样的类似于in
all:全部

=some（=any）等于其中任何一个
!=some（!=any）不等于其中任何一个就可以了
=all 等于其中的所有
!=all 不等于其中的所有

思考：!=some()和not in一样吗？
答：不一样；!=some()表示父查询的记录只要不等于some中的任意一条即可。
!=all()才和not in一样

mysql> select * from stuinfo where stuNo=some(select stuNo from stuMarks where writtenExam>60);
+--------+--------------+--------+--------+---------+------------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |
+--------+--------------+--------+--------+---------+------------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |
+--------+--------------+--------+--------+---------+------------+
3 rows in set (0.00 sec)

mysql> select * from stuinfo where stuNo=any(select stuNo from stuMarks where writtenExam>60);
+--------+--------------+--------+--------+---------+------------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |
+--------+--------------+--------+--------+---------+------------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |
+--------+--------------+--------+--------+---------+------------+
3 rows in set (0.00 sec)

mysql> select * from stuinfo where stuNo=all(select stuNo from stuMarks where writtenExam>60);
Empty set (0.00 sec)

exists和not exists

如果有人大于等于80分就显示所有的学生

mysql> select * from stuinfo where exists (select * from stuMarks where writtenExam>=80);
+--------+--------------+--------+--------+---------+------------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |
+--------+--------------+--------+--------+---------+------------+
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       |
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |
| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       |
| s25319 | 梅超风       | 女     |     23 |       5 | 河北       |
+--------+--------------+--------+--------+---------+------------+
7 rows in set (0.00 sec)

如果没有人超过80分就显示所有的学生

1 2	mysql> select * from stuinfo where not exists (select * from stuMarks where writtenExam>=80); Empty set (0.00 sec)

子查询分类

1、标量子查询：子查询返回的结果就一个
2、列子查询：子查询返回的结果是一个列表
3、行子查询：子查询返回的结果返回一行
4、表子查询：子查询返回的结果当成一个表(一定要取别名)

mysql> select *,count(*) from (select * from stuinfo order by stuAge desc) as new1 group by stuSex;
+--------+--------------+--------+--------+---------+------------+----------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | count(*) |
+--------+--------------+--------+--------+---------+------------+----------+
| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       |        3 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       |        4 |
+--------+--------------+--------+--------+---------+------------+----------+
2 rows in set (0.00 sec)

子查询一定要取别名

视图【view】

1、视图是一张虚拟表，它表示一张表的部分或多张表的综合的结构。
2、视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。

创建视图

语法

create [or replace] view 视图的名称
as
select语句

因为视图是一个表结构，所以创建视图后，会在数据库文件夹中多一个与视图名同名的.frm文件

mysql> create view vw_stu
    -> as
    ->   select * from stuinfo nutural join stumarks using(stuno);
Query OK, 0 rows affected (0.02 sec)

查询视图

视图是一张虚拟表，视图的用法和表的用法一样

mysql> select * from vw_stu;
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 |          80 |      58 |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 |          50 |      90 |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 |          65 |      50 |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 |          77 |      82 |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 |          56 |      48 |
+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+
5 rows in set (0.00 sec)

查看视图的结构

mysql> desc vw_stu;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| stuNo       | char(6)     | NO   |     | NULL    |       |
| stuName     | varchar(10) | NO   |     | NULL    |       |
| stuSex      | char(2)     | NO   |     | NULL    |       |
| stuAge      | tinyint(4)  | NO   |     | NULL    |       |
| stuSeat     | tinyint(4)  | NO   |     | NULL    |       |
| stuAddress  | varchar(10) | NO   |     | NULL    |       |
| examNo      | char(7)     | NO   |     | NULL    |       |
| writtenExam | int(11)     | YES  |     | NULL    |       |
| labExam     | int(11)     | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
9 rows in set (0.02 sec)

查看创建视图的语法

mysql> show create view vw_stu \g
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | character_set_client | collation_connection |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| vw_stu | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_stu` AS select `nutural`.`stuNo` AS `stuNo`,`nutural`.`stuName` AS `stuName`,`nutural`.`stuSex` AS `stuSex`,`nutural`.`stuAge` AS `stuAge`,`nutural`.`stuSeat` AS `stuSeat`,`nutural`.`stuAddress` AS `stuAddress`,`stumarks`.`examNo` AS `examNo`,`stumarks`.`writtenExam` AS `writtenExam`,`stumarks`.`labExam` AS `labExam` from (`stuinfo` `nutural` join `stumarks` on((`nutural`.`stuNo` = `stumarks`.`stuNo`))) | utf8                 | utf8_general_ci      |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

显示所有视图

mysql> show tables;
+-----------------+
| Tables_in_uiste |
+-----------------+
| stuMarks        |
| stuinfo         |
| vw_stu          |
+-----------------+
3 rows in set (0.01 sec)

精确查找视图

方法一：information_schema中的views表保存的是视图信息

mysql> select table_name from information_schema.views;
+------------+
| table_name |
+------------+
| vw_stu     |
+------------+
1 row in set (0.12 sec)

方法二：show table status查看表的属性

mysql> show table status\G
*************************** 1. row ***************************
           Name: stuMarks
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-08-31 17:18:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: stuinfo
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-08-31 17:18:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: vw_stu
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
3 rows in set (0.00 sec)

显示所有视图的语法是

mysql> show table status where comment='view'\G
*************************** 1. row ***************************
           Name: vw_stu
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

更改视图

语法：
alter view 视图名
as
select 语句

mysql> alter view vw_stu
    -> as
    ->  select stuaddress,count(stuaddress) from stuinfo left join stumarks using(stuno) group by stusex;         Query OK, 0 rows affected (0.02 sec)
mysql> select * from vw_stu;
+------------+-------------------+
| stuaddress | count(stuaddress) |
+------------+-------------------+
| 北京       |                 3 |
| 上海       |                 4 |
+------------+-------------------+
2 rows in set (0.02 sec)

删除视图

语法：drop view [if exists] 视图1,视图2,…

mysql> drop view if exists vw_stu;
Query OK, 0 rows affected (0.01 sec)

mysql> select table_name from information_schema.views;
Empty set (0.03 sec)

视图的作用

1、筛选数据，防止未经许可访问敏感数据
2、增加表的兼容性
3、隐藏表结构
4、降低SQL语句的复杂度

视图的算法

找出语文成绩最高的男生和女生
既然子查询当成一个表，我们可以用视图来代替
将子查询写法改为如下

结果不正确！原因：这是因为视图的算法造成的

视图的算法

1、 merge：合并算法，将视图的语句和外层的语句合并后在执行。
2、 temptable：临时表算法，将视图生成一个临时表，再执行外层语句
3、 undefined：未定义，MySQL到底用merge还是用temptable由MySQL决
定，这是一个默认的算法，一般视图都会选择merge算法，因为merge效率高。

解决

在创建视图的时候指定视图的算法
create view algorithm=temptable 视图名
as
select 语句

重写视图

重新执行查询

事务安全【transaction】

1、事务是一个不可分割的执行单元
2、事务作为一个整体要么一起执行，要么一起回滚

开启事务1

语法：start transaction或begin [work]

执行sql更新语句2

update 表名 set 字段名=字段内容条件

设置还原点3

savepoint sp1

执行sql更新语句4

成功提交事务5

语法：commit

失败回滚事务5

语法：rollback

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (4,4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | num1 |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
+------+------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | num1 |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (4,4);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | num1 |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
+------+------+
4 rows in set (0.00 sec)

思考：事务什么时候产生？什么时候结束？
答：开启的时候产生，提交事务或回滚事务都结束
脚下留心：只有innodb和BDB才支持事务，myisam不支持事务。

事务的特性（ACID）

1、原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行。
2、一致性（Consistency）：事务完成时，数据必须处于一致的状态。
3、隔离性（Isolation）：每个事务都是相互隔离的
4、永久性（Durability）：事务完成后，对数据的修改是永久性的。

自动提交事务

默认情况下，每个独立的SQL语句都在自动提交事务的。
通过如下语句查看是否自动提交事务

可以更改自动提交事务
set autocommit=0 | 1；

MySQL的锁的机制

当在对数据表进行并发(同一时刻会有多个客户端操作同一条记录)操作时，就会触发MySQL的锁的机制
①、表锁当某个客户端对一个表进行更改操作时会锁住整个表(其他客户端只能等待)非键值
②、行锁–键值

索引【index】

索引的优点：查询速度快
索引的缺点：
1、增、删、改（数据操作语句）效率低了
2、索引占用空间

索引的类型

1、普通索引
2、唯一索引（唯一键）
3、主键索引：只要主键就自动创建主键索引，不需要手动创建。
4、全文索引，搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。

创建普通索引【create index】

语法：create index 索引名 on 表名（字段名）

创建索引
查看索引

通过修改表的方式创建索引

语法：alter table 表名 add index [索引的名称] （列名）
创建索引

创建表的时候指定索引

mysql> create table temp(
    -> id int,
    -> name varchar(20),
    -> age tinyint,
    -> index ix_temp_name(name),		--创建索引
    -> key(age)				--创建索引
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> show create table temp\G
*************************** 1. row ***************************
       Table: temp
Create Table: CREATE TABLE `temp` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  KEY `ix_temp_name` (`name`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建唯一索引

语法一：create unique index 索引名 on 表名（字段名）
语法二：alter table 表名 add unqiue index [索引的名称] （列名）
语法三：创建表的时候添加唯一索引，和创建唯一键是一样的。

创建多列组合索引

删除索引

语法：drop index 索引名 on 表名

创建索引的指导原则

1、该列用于频繁搜索
2、改列用于排序
3、公共字段要创建索引
4、如果表中的数据很少，不需要创建索引。MySQL搜索索引的时间比逐条搜索数据的时间要长。
5、如果一个字段上的数据只有几个不同的值，改字段不适合做索引，比如性别。

存储过程【procedure】

存储过程的优点

1、存储过程可以减少网络流量
2、允许模块化设计
3、支持事务

创建存储过程

语法：
create procedure 存储过程名(参数)
begin
//sql语句
end;

脚下留心：由于过程中有很多SQL语句，每个语句的结束都要用（；）表示。默认情况下，分号既表示语句结束，又表示向服务器发送SQL语句。我们希望分号仅表示语句的结束，不要将SQL语句发送到服务器执行，通过delimiter来更改结束符。

mysql>delimiter //

创建简单的存储过程

调用存储过程

语法：call 存储过程名()

删除存储过程

语法：drop procedure [if exists] 存储过程名

创建复杂的存储过程

mysql> create procedure proc(param char(6))
    -> begin
    ->      select * from stuinfo where stuno=param;
    ->      select * from stumarks where stuno=param;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call proc('s25301') //
+--------+---------+--------+--------+---------+------------+
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京
+--------+---------+--------+--------+---------+------------+
1 row in set (0.00 sec)

+---------+--------+-------------+---------+
| examNo  | stuNo  | writtenExam | labExam |
+---------+--------+-------------+---------+
| s271816 | s25301 |          77 |      82 |
+---------+--------+-------------+---------+
1 row in set (0.01 sec)

存储过程的参数

存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。
存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout）

变量

局部变量

1、通过declare关键字来声明变量
2、语法：declare 变量名数据类型 [default 默认值]
3、使用set或select …into…给变量赋值

使用select…into…给变量赋值

mysql> create procedure proc(in id char(6))
    -> begin
    ->      declare name varchar(20);
    ->      declare sex char(1);
    ->      select stuname,stusex into name,sex from stuinfo where stuno=id;
    ->      select name,sex from dual;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call proc('s25301')//
+--------+------+
| name   | sex  |
+--------+------+
| 张秋丽       | 男     |
+--------+------+
1 row in set (0.00 sec)

例题：查找同桌
例题：使用set赋值

全局变量

在变量前面加一个@，就是全局变量
全局变量是一个弱类型的变量，它的类型取决于付给变量的值

系统变量

只要变量名前面有两个@@的肯定是系统变量

带输出参数的存储过程【out】

mysql> create procedure proc(num int,out result int)
    -> begin
    ->      set result=num*num;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call proc(5,@result) //
Query OK, 0 rows affected (0.00 sec)

mysql> select @result from dual //
+---------+
| @result |
+---------+
|      25 |
+---------+

带输入输出参数的存储过程【inout】

查看存储过程的信息

—————内连接—————-
–语法一：
select i.stuno,stuname,writtenexam from stuinfo i inner join stumarks s on i.stuno=s.stuno;
–语法二：
select stuinfo.stuno,stuname,writtenexam from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;
–inner 可以省略
select i.stuno,stuname,writtenexam from stuinfo i join stumarks s on i.stuno=s.stuno;
————–左外连接,右外连接———-
select stuinfo.stuno,stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;

select stuinfo.stuno,stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;

————–自然连接—————
–自然内连接
select * from t1 natural join t2;
–自然左外连接
select * from t1 natural left join t2;
–自然右外连接
select * from t1 natural right join t2;

—————-using()——————-
select * from t1 inner join t2 using(id);
select * from t1 left join t2 using(id);

——————-子查询————————–
–查找80分的学生
–方法一
select stuname,writtenexam from stuinfo natural join stumarks where writtenexam=80;
–方法二
select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);

–找出最高分
select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=(select max(writtenexam) from stumarks));

–查找及格的学生
select * from stuinfo where stuno in (select stuno from stumarks where writtenexam>=60);

–查找不及格的学生
select * from stuinfo where stuno not in (select stuno from stumarks where writtenexam>=60);

–查找缺考的学生
select * from stuinfo where stuno in (select stuno)

select * from stuinfo where stuno in (select stuno from stuinfo natural left join stumarks where writtenexam is null);

–some

select * from stuinfo where stuno=some(select stuno from stumarks where writtenexam>=60);

–exits
select * from stuinfo where exists (select * from stumarks where writtenexam>=80)

–行子查询
select * from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);

–表子查询
select * from (select * from stu order by ch desc) as t group by stusex;

–示地区和每个地区参见考试的人数，并按人数降序排列
select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc;

–显示有学生参见考试的地区
select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress having c>0;

select distinct stuaddress from stuinfo natural right join stumarks where stuaddress is not null;

–显示男生和女生的人数
select stusex,count(*) from stuinfo where stusex='男' union select stusex,count(*) from stuinfo where stusex='女';

select sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo;

–显示每个地区的男生和女生，以及总人数
select stuaddress,count(*) 总人数,sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo group by stuaddress;

———————–视图———————–

1
2
3

create view vw_stu
as
  select stuname,stusex,writtenexam from stuinfo inner join stumarks using (stuno);

select * from (select * from stu order by ch desc) t group by stusex;

1
2
3

create or replace algorithm=temptable view vw1
as
select * from stu order by ch desc;

--
create table temp(
id int,
name varchar(20),
age tinyint,
index ix_temp_name(name),
key(age)
)

多表查询分类

将多个表的数据横向的联合起来。
1、内连接
2、外连接
a) 左外连接
b) 右外连接
3、交叉连接
4、自然连接