MySQL多表操作

外键约束

-- 给product中的cno添加一个外键约束
-- 修改表product给cno添加外键并且关联到category表的cid上面
-- references关联的列必须是主键
alter table product add foreign key(cno) references category(cid);
-- 如果添加了外键约束，比如说向product表中添加了一个cno在category中cid不存在的值，则会失败，同理添加外键时候如果不匹配也会失败。

-- 建表的时候创建外键
create table student(
    oid int ,
    pid int,
    foreign key(oid) references user(kid)
    foreign key(pid) references order(id)
)

唯一约束和主键约束

唯一约束：列表内容必须唯一，但是可以为空 unique
- 不能作为其它表的外键
- 可以有多个
主键约束: 默认必须不能为空且唯一
- 外键一般都是指向另外一张表的主键
- 一张表只能有一个

多表查询

-- 交叉连接查询(笛卡儿积)
select * from t1 cross join t2;   # 返回笛卡尔积
-- 如果有条件则等效于内连接
-- 查询了两张表(一般结果无意义,不过可以通过条件过滤,例如where)
select * from product p,student s where p.cno=s.sid;

-- 内连接查询(没有主次之分，只要条件不匹配就不显示)
-- 隐式内链接
select * from product p,student s where p.cno=s.sid;
-- 显式内链接(效果同上,on后面跟条件)
select * from product p inner join student s on p.cno=s.sid;

-- 外连接查询(有主次表之分,即使不匹配也会显示主表信息)
-- 左外连接(左表中数据全部查询出来，如果右边没遇对应的数据则显示NULL)
select * from product p left outer join student s on p.cno=s.sid;

-- 右外连接(右表中数据全部查询出来，如果左边没遇对应的数据则显示NULL)
select * from product p right outer join student s on p.cno=s.sid;

> 内外连接在查询结果上面的区别就是内链接必须匹配条件而且无null值一般

-- 分页查询
select * from student limit 1 ,10;
-- 子查询
select * from student where age>(select cage from  people where name='sdas');
select pname,(select cname from catergory c where p.cno=c.cid) from product p;
补充：大部分情况下，连接查询和子查询可以互用

多表查询分类

将多个表的数据横向的联合起来。

内连接
外连接
- 左外连接
- 右外连接
交叉连接
自然连接

内连接【inner join】

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

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

例题

方法一：
mysql> select stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno;
+----------+--------+-------------+---------+
| stuname  | stusex | writtenexam | labexam |
+----------+--------+-------------+---------+
| 李斯文        | 女      |          80 |      58 |
| 李文才        | 男       |          50 |      90 |
| 欧阳俊雄        | 男       |          65 |      50 |
| 张秋丽         | 男       |          77 |      82 |
| 争青小子        | 男       |          56 |      48 |
+----------+--------+-------------+---------+

方法二：
mysql> select stuinfo.stuno,stuname,stusex,writtenexam,labexam from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;
+--------+----------+--------+-------------+---------+
| stuno  | stuname  | stusex | writtenexam | labexam |
+--------+----------+--------+-------------+---------+
| s25303 | 李斯文        | 女      |          80 |      58 |
| s25302 | 李文才        | 男       |          50 |      90 |
| s25304 | 欧阳俊雄        | 男       |          65 |      50 |
| s25301 | 张秋丽         | 男       |          77 |      82 |
| s25318 | 争青小子        | 男       |          56 |      48 |
+--------+----------+--------+-------------+---------+

可以给表取别名
mysql> select i.stuno,stuname,stusex,writtenexam,labexam from stuinfo i,stumarks s where i.stuno=s.stuno;
+--------+----------+--------+-------------+---------+
| stuno  | stuname  | stusex | writtenexam | labexam |
+--------+----------+--------+-------------+---------+
| s25303 | 李斯文        | 女      |          80 |      58 |
| s25302 | 李文才        | 男       |          50 |      90 |
| s25304 | 欧阳俊雄        | 男       |          65 |      50 |
| s25301 | 张秋丽         | 男       |          77 |      82 |
| 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 stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;
+----------+-------------+---------+
| stuname  | writtenexam | labexam |
+----------+-------------+---------+
| 张秋丽         |          77 |      82 |
| 李文才        |          50 |      90 |
| 李斯文        |          80 |      58 |
| 欧阳俊雄        |          65 |      50 |
| 诸葛丽丽         |        NULL |    NULL |
| 争青小子        |          56 |      48 |
| 梅超风        |        NULL |    NULL |
+----------+-------------+---------+

思考：
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 left join 表1 on 表1.公共字段=表2.公共字段   是否一样？
答：不一样，左连接一左边的表为准。

右外连接【right join】

以右边的表为标准，如果左边的表没有对应的记录，用NULL填充。

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

例题

mysql> select stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;
+----------+-------------+---------+
| stuname  | writtenexam | labexam |
+----------+-------------+---------+
| 李斯文        |          80 |      58 |
| 李文才        |          50 |      90 |
| 欧阳俊雄        |          65 |      50 |
| 张秋丽         |          77 |      82 |
| 争青小子        |          56 |      48 |
| NULL     |          66 |      77 |
+----------+-------------+---------+
6 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】

插入测试数据

mysql> create table t1(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (1,'tom'),(2,'berry');
Query OK, 2 rows affected (0.00 sec)

mysql> create table t2(
    -> id int,
    -> score int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values (1,88),(2,99);

1、如果没有连接表达式返回的是笛卡尔积

mysql> select * from t1 cross join t2;   # 返回笛卡尔积
+------+-------+------+-------+
| id   | name  | id   | score |
+------+-------+------+-------+
|    1 | tom   |    1 |    88 |
|    2 | berry |    1 |    88 |
|    1 | tom   |    2 |    99 |
|    2 | berry |    2 |    99 |
+------+-------+------+-------+

2、如果有连接表达式等价于内连接

mysql> select * from t1 cross join t2 where t1.id=t2.id;
+------+-------+------+-------+
| id   | name  | id   | score |
+------+-------+------+-------+
|    1 | tom   |    1 |    88 |
|    2 | berry |    2 |    99 |
+------+-------+------+-------+

自然连接【natural】

自动的判断连接条件，它是过同名字段来判断的

自然连接又分为：

自然内连接 natural join
自然左外连接 natural left join
自然右外连接 natural right join

例题：

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

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

自然连接结论：

表连接通过同名的字段来连接的
如果没有同名的字段返回笛卡尔积
会对结果进行整理，整理的规则如下

a) 连接字段保留一个

b) 连接字段放在最前面

c) 左外连接左边在前，右外连接右表在前

using()

用来指定连接字段。
using()也会对连接字段进行整理，整理方式和自然连接是一样的。
和自然连接区别在于，如果存在多个同名字段，可以using指定。

mysql> select * from stuinfo inner join stumarks using(stuno);   # using指定字段
+--------+----------+--------+--------+---------+------------+---------+-------------+---------+
| 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)

子查询

语法

语法：select 语句 where 条件 (select … from 表)

外面的查询称为父查询，括号中的查询称为子查询
子查询为父查询提供查询条件

例题

1、查找笔试80分的学生

mysql> select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);
+--------+---------+--------+--------+---------+------------+
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+---------+--------+--------+---------+------------+
| s25303 | 李斯文       | 女      |     22 |       2 | 北京           |
+--------+---------+--------+--------+---------+------------+

2、查找笔试最高分的学生

# 方法一：
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

in|not in子查询

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

1、查找笔试成绩及格的同学

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

2、查询不及格的同学

mysql> select * from stuinfo where stuno  in (select stuno from stumarks where writtenexam<=60);
+--------+----------+--------+--------+---------+------------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25302 | 李文才        | 男       |     31 |       3 | 上海          |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           |
+--------+----------+--------+--------+---------+------------+

3、查询没有通过的同学（不及格，缺考）

mysql> select * from stuinfo where stuno  not in (select stuno from stumarks where writtenexam>=60);
+--------+----------+--------+--------+---------+------------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25302 | 李文才        | 男       |     31 |       3 | 上海          |
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           |
| s25319 | 梅超风        | 女      |     23 |       5 | 河北          |
+--------+----------+--------+--------+---------+------------+
4 rows in set (0.00 sec)

exists和not exists

1、如果有人笔试超过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 | 河北          |
+--------+----------+--------+--------+---------+------------+

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

mysql> select * from stuinfo where not exists (select * from stumarks where writtenexam>=80);
Empty set (0.02 sec)

子查询分类

1、标量子查询：子查询返回的结果就一个

2、列子查询：子查询返回的结果是一个列表

3、行子查询：子查询返回的结果是一行

例题：查询成绩最高的男生和女生

mysql> select stuname,stusex,ch from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);
+----------+--------+------+
| stuname  | stusex | ch   |
+----------+--------+------+
| 争青小子        | 男       |   86 |
| Tabm     | 女      |   88 |
+----------+--------+------+

4、表子查询：子查询返回的结果当成一个表

例题：查询成绩最高的男生和女生

mysql> select stuname,stusex,ch from (select * from stu order by ch desc) as t group by stusex;
+----------+--------+------+
| stuname  | stusex | ch   |
+----------+--------+------+
| Tabm     | 女      |   88 |
| 争青小子        | 男       |   86 |
+----------+--------+------+

==脚下留心：from后面是一个表，如果子查询的结果当成表来看，必须将子查询的结果取别名。as t==

union（联合）

插入测试数据

mysql> create table GO1(
    -> id int primary key,
    -> name varchar(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into Go1 values (1,'李白'),(2,'张秋丽');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

union的使用

作用：将多个select语句结果集纵向联合起来

语法：select 语句 union [选项] select 语句 union [选项] select 语句

mysql> select stuno,stuname from stu union select id,name from Go1;
+--------+----------+
| stuno  | stuname  |
+--------+----------+
| s25301 | 张秋丽         |
| s25302 | 李文才        |
| s25303 | 李斯文        |
| s25304 | 欧阳俊雄        |
| s25305 | 诸葛丽丽         |
| s25318 | 争青小子        |
| s25319 | 梅超风        |
| s25320 | Tom      |
| s25321 | Tabm     |
| 1      | 李白         |
| 2      | 张秋丽         |
+--------+----------+

例题：查询上海的男生和北京的女生

mysql> select stuname,stuaddress,stusex from stu where (stuaddress='上海' and stusex='男') or (stuaddress='北京' and stusex='女');
+---------+------------+--------+
| stuname | stuaddress | stusex |
+---------+------------+--------+
| 张秋丽        | 上海          | 男       |
| 梅超风       | 北京           | 女      |
+---------+------------+--------+
2 rows in set (0.00 sec)

mysql> select stuname,stuaddress,stusex from stu where stuaddress='上海' and stusex='男' union select stuname,stuaddress,stusex from stu where stuaddress='北京' and stusex='女';
+---------+------------+--------+
| stuname | stuaddress | stusex |
+---------+------------+--------+
| 张秋丽        | 上海          | 男       |
| 梅超风       | 北京           | 女      |
+---------+------------+--------+
2 rows in set (0.02 sec)

union的选项

union的选项有两个

1、 all：显示所有数据

2、 distinct：去除重复的数据【默认】

mysql> select name from go1 union select stuname from stu;
+----------+
| name     |
+----------+
| 李白         |
| 张秋丽         |
| 李文才        |
| 李斯文        |
| 欧阳俊雄        |
| 诸葛丽丽         |
| 争青小子        |
| 梅超风        |
| Tom      |
| Tabm     |
+----------+

默认是去重复的

mysql> select name from go1 union all select stuname from stu;  # all不去重复记录
+----------+
| name     |
+----------+
| 李白         |
| 张秋丽         |
| 张秋丽         |
| 李文才        |
| 李斯文        |
| 欧阳俊雄        |
| 诸葛丽丽         |
| 争青小子        |
| 梅超风        |
| Tom      |
| Tabm     |
+----------+

union的注意事项

1、 union两边的select语句的字段个数必须一致

2、 union两边的select语句的字段名可以不一致，最终按第一个select语句的字段名。

3、 union两边的select语句中的数据类型可以不一致。

视图【view】

1、视图是一张虚拟表，它表示一张表的部分或多张表的综合的结构。

2、视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。

创建视图

语法

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

例题：

mysql> create view vw_stu
    -> as
    -> select stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks using(stuno);
Query OK, 0 rows affected (0.00 sec)

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

使用视图

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

mysql> select * from vw_stu;
+----------+--------+-------------+---------+
| stuname  | stusex | writtenexam | labexam |
+----------+--------+-------------+---------+
| 李斯文        | 女      |          80 |      58 |
| 李文才        | 男       |          50 |      90 |
| 欧阳俊雄        | 男       |          65 |      50 |
| 张秋丽         | 男       |          77 |      82 |
| 争青小子        | 男       |          56 |      48 |
+----------+--------+-------------+---------+

mysql> update vw_stu set writtenexam=88 where stuname='李斯文';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看视图的结构

语法：

desc 视图名

例题

mysql> desc vw_stu;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| stuname     | varchar(10) | NO   |     | NULL    |       |
| stusex      | char(2)     | NO   |     | NULL    |       |
| writtenexam | int(11)     | YES  |     | NULL    |       |
| labexam     | int(11)     | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

查看创建视图的语法

语法：

show create view 视图名

显示所有视图

 #方法一：
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| stu              |
| stuinfo          |
| stumarks         |
| t1               |
| t2               |
| vw_stu           |

# 方法二
mysql> select table_name from information_schema.views;
+------------+
| table_name |
+------------+
| vw_stu     |
+------------+
1 row in set (0.05 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 * from stuinfo;
Query OK, 0 rows affected (0.00 sec)

删除视图

语法：

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

例题

mysql> drop view vw_stu;
Query OK, 0 rows affected (0.00 sec)

视图的作用

筛选数据，防止未经许可访问敏感数据
隐藏表结构
降低SQL语句的复杂度

视图的算法

场景：找出语文成绩最高的男生和女生

mysql> select * from (select * from stu order by ch desc) as t group by stusex;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25321 | Tabm     | 女      |     23 |       9 | 河北          |   88 |   77 |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           |   86 |   92 |
+--------+----------+--------+--------+---------+------------+------+------+

我们可以将子查询封装到视图中

mysql> create view vw_stu
    -> as
    -> select * from stu order by ch desc;
Query OK, 0 rows affected (0.00 sec)

可以将上面的子查询更改成视图，但是，结果和上面不一样

mysql> select * from vw_stu group by stusex;
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京           |   80 | NULL |
| s25303 | 李斯文       | 女      |     22 |       2 | 北京           |   55 |   82 |
+--------+---------+--------+--------+---------+------------+------+------+

原因：这是因为视图的算法造成的

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

解决：在创建视图的时候指定视图的算法

create algorithm=temptable view 视图名
as
    select 语句

指定算法创建视图

mysql> create algorithm=temptable view vw_stu
    -> as
    ->  select * from stu order by ch desc;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from vw_stu group by stusex;   # 结果是一致的
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25321 | Tabm     | 女      |     23 |       9 | 河北          |   88 |   77 |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           |   86 |   92 |
+--------+----------+--------+--------+---------+------------+------+------+

事务【transaction】

事务是一个不可分割的执行单元
事务作为一个整体要么一起执行，要么一起回滚
为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

插入测试数据

mysql> create table bank(
    -> cardid char(4) primary key,
    -> money int
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bank values ('1001',1000),('1002',100);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

事务操作

开启事务：start transaction或begin [work]
提交事务：commit
回滚事务：rollback

例题：

mysql> delimiter //            # 更改定界符

mysql> start transaction;           # 开启事务
    -> update bank set money=money-100 where cardid='1001';
    -> update bank set money=money+100 where cardid='1002'  //
Query OK, 0 rows affected (0.00 sec)

mysql> commit //   # 提交事务

mysql> rollback //  # 回滚事务

思考：事务什么时候产生？什么时候结束？
答：开启的时候产生，提交事务或回滚事务都结束

脚下留心：只有innodb和BDB才支持事务，myisam不支持事务。

设置事务的回滚点

语法：

设置回滚点： savepoint 回滚点名
回滚到回滚点： rollback to 回滚点

例题：

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

mysql> insert into bank values ('1003',1000);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint aa;    # 设置回滚点 aa
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bank values ('1004',500);
Query OK, 1 row affected (0.00 sec)
 
mysql> savepoint bb;   # 设置回滚点bb
Query OK, 0 rows affected (0.00 sec)
 
mysql> rollback to aa;    # 回滚到aa点
Query OK, 0 rows affected (0.00 sec)

mysql> commit;    # 提交事务

mysql> select * from bank ;
+--------+-------+
| cardid | money |
+--------+-------+
| 1001   |   800 |
| 1002   |   200 |
| 1003   |  1000 |
+--------+-------+

事务的特性（ACID）

原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行。
一致性（Consistency）：事务完成时，数据必须处于一致的状态。
隔离性（Isolation）：每个事务都是相互隔离的,事务可以开启多个，但是一个某个事务执行操作，另外事务执行操作会阻塞，等待第一个事务执行完毕。
永久性/持久性（Durability）：事务完成后，对数据的修改是永久性的。

设置事务隔离级别

set global transaction isolation level read uncommitted

read uncommitted:读未提交
- 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读
read committed:读已提交
- 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
repeatable read：可重复度
- 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题
serializable：串行化，效率低，极少使用
- 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

InnoDB MVCC多版本并发控制

解决mysql的可重复读的幻读问题

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

mvcc.png

索引【index】

索引的优点：查询速度快

索引的缺点：

增、删、改（数据操作语句）效率低了
索引占用空间

索引的类型

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

创建普通索引【create index】

语法：

create index [索引名] on 表名 （字段名）
alter table 表名 add index [索引的名称] （列名）

例题：

# 创建索引方法一
mysql> create index ix_stuname on stuinfo(stuname);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 创建索引方法二
mysql> alter table stuinfo add index ix_address (stuaddress);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 创建表的时候就添加索引
mysql> create table emp(
    -> id int,
    -> name varchar(10),
    -> index ix_name (name)   # 创建索引
    -> );
Query OK, 0 rows affected (0.00 sec)

创建唯一索引

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

例题

# 方法一：
mysql> create unique index UQ_stuname on stu(stuname);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 方法二：
mysql> alter table stu add unique UQ_address (stuaddress);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 方法三
mysql> create table stu2(
    -> id int,
    -> name varchar(20),
    -> unique UQ_name(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

删除索引

语法

drop index 索引名 on 表名

例题

mysql> drop index ix_stuname on stuinfo;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引的指导原则

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

函数

数字类

mysql> select rand();           # 生成随机数
+---------------------+
| rand()              |
+---------------------+
| 0.18474003969201822 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from stuinfo order by rand();   # 随机排序

mysql> select * from stuinfo order by rand() limit 2;    # 随机抽两个学生
+--------+----------+--------+--------+---------+------------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           |
+--------+----------+--------+--------+---------+------------+
2 rows in set (0.00 sec)

mysql> select round(3.5);     #四舍五入
+------------+
| round(3.5) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(3.1);    # 向上取整
+-----------+
| ceil(3.1) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql> select floor(3.9);   # 向下取整
+------------+
| floor(3.9) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

mysql> select truncate(3.1415926,3);    # 截取数字
+-----------------------+
| truncate(3.1415926,3) |
+-----------------------+
|                 3.141 |
+-----------------------+
1 row in set (0.00 sec)

字符串类

mysql> select ucase('i am a boy!');     # 转成大写
+----------------------+
| ucase('i am a boy!') |
+----------------------+
| I AM A BOY!          |
+----------------------+
1 row in set (0.00 sec)

mysql> select lcase('I Am A Boy!');     #转成小写
+----------------------+
| lcase('I Am A Boy!') |
+----------------------+
| i am a boy!          |
+----------------------+
1 row in set (0.00 sec)

mysql> select left('abcde',3);      # 从左边开始截取，截取3个
+-----------------+
| left('abcde',3) |
+-----------------+
| abc             |
+-----------------+
1 row in set (0.00 sec)

mysql> select right('abcde',3);     # 从右边开始截取，截取3个
+------------------+
| right('abcde',3) |
+------------------+
| cde              |
+------------------+
1 row in set (0.00 sec)

mysql> select substring('abcde',2,3);   #从第2个位置开始截取，截取3个【位置从1开始】
+------------------------+
| substring('abcde',2,3) |
+------------------------+
| bcd                    |
+------------------------+
1 row in set (0.00 sec)

mysql> select concat('中国','上海');    # 字符串相连
+-----------------------+
| concat('中国','上海')       |
+-----------------------+
| 中国上海                    |
+-----------------------+
1 row in set (0.00 sec)

mysql> select concat(stuname,'-',stusex) from stuinfo;  # 将表中的姓名和性别连接起来
+----------------------------+
| concat(stuname,'-',stusex) |
+----------------------------+
| 张秋丽-男                          |
| 李文才-男                         |
| 李斯文-女                        |
| 欧阳俊雄-男                         |
| 诸葛丽丽-女                         |
| 争青小子-男                         |
| 梅超风-女                        |
+----------------------------+
7 rows in set (0.00 sec)

# coalesce(字段1，字段2)  如果字段1不为空就显示字段1，否则，显示字段2
mysql> select stuname,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks;   # 将考试成绩为空的显示为缺考
+----------+------------------------------+--------------------------+
| stuname  | coalesce(writtenexam,'缺考')    | coalesce(labexam,'缺考')    |
+----------+------------------------------+--------------------------+
| 张秋丽         | 77                           | 82                       |
| 李文才        | 50                           | 90                       |
| 李斯文        | 88                           | 58                       |
| 欧阳俊雄        | 65                           | 50                       |
| 诸葛丽丽         | 缺考                            | 缺考                        |
| 争青小子        | 56                           | 48                       |
| 梅超风        | 缺考                            | 缺考                        |
+----------+------------------------------+--------------------------+

mysql> select length('锄禾日当午');      # 字节长度
+----------------------+
| length('锄禾日当午')          |
+----------------------+
|                   10 |
+----------------------+
1 row in set (0.00 sec)

mysql> select char_length('锄禾日当午');     # 字符个数
+---------------------------+
| char_length('锄禾日当午')          |
+---------------------------+
|                         5 |
+---------------------------+
1 row in set (0.00 sec)

时间类

mysql> select unix_timestamp(); #获取时间戳
+------------------+
| unix_timestamp() |
+------------------+
|       1537084508 |
+------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(unix_timestamp());  # 将时间戳转成年-月-日 小时:分钟:秒的格式
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2018-09-16 15:55:56             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select now();        # 获取当前日期时间
+---------------------+
| now()               |
+---------------------+
| 2018-09-16 15:57:04 |
+---------------------+
1 row in set (0.00 sec)

mysql> select year(now()) 年,month(now()) 月, day(now()) 日,hour(now()) 小,minute(now()) 分钟,second(now()) 秒;
+------+------+------+------+------+------+
| 年     | 月     | 日     | 小时   | 分钟     | 秒     |
+------+------+------+------+------+------+
| 2018 |    9 |   16 |   15 |   59 |   14 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> select dayname(now()) 星期,monthname(now()),dayofyear(now()) 本年的第几天;
+--------+------------------+--------------+
| 星期       | monthname(now()) | 本年的第几天           |
+--------+------------------+--------------+
| Sunday | September        |          259 |
+--------+------------------+--------------+
1 row in set (0.00 sec)

mysql> select datediff(now(),'2008-8-8');   # 日期相减
+----------------------------+
| datediff(now(),'2008-8-8') |
+----------------------------+
|                       3691 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select convert(now(),date),convert(now(),time);  # 将now()转成日期和时间
+---------------------+---------------------+
| convert(now(),date) | convert(now(),time) |
+---------------------+---------------------+
| 2018-09-16          | 16:07:24            |
+---------------------+---------------------+

mysql> select cast(now() as date),cast(now() as time);   # 将now()转成日期和时间
+---------------------+---------------------+
| cast(now() as date) | cast(now() as time) |
+---------------------+---------------------+
| 2018-09-16          | 16:08:03            |
+---------------------+---------------------+
1 row in set (0.00 sec)

加密函数

+----------------------------------+------------------------------------------+
| md5('root')                      | sha('root')                              |
+----------------------------------+------------------------------------------+
| 63a9f0ea7bb98050796b649e85481845 | dc76e9f0c0006e8f919e0c515c66dbba3982f785 |
+----------------------------------+------------------------------------------+
1 row in set (0.00 sec)

判断函数

语法

if(表达式,值1,值2)

例题：

mysql> select if(10%2=0,'偶数','奇数');
+--------------------------+
| if(10%2=0,'偶数','奇数')        |
+--------------------------+
| 偶数                        |
+--------------------------+
1 row in set (0.00 sec)

# 语文和数学都超过60分才通过
mysql> select stuname,ch,math,if(ch>=60 && math>=60,'通过','不通过') '是否通过' from stu;
+----------+------+------+----------+
| stuname  | ch   | math | 是否通过       |
+----------+------+------+----------+
| 张秋丽         |   80 | NULL | 不通过        |
| 李文才        |   77 |   76 | 通过        |
| 李斯文        |   55 |   82 | 不通过        |
| 欧阳俊雄        | NULL |   74 | 不通过        |
| 诸葛丽丽         |   72 |   56 | 不通过        |
| 争青小子        |   86 |   92 | 通过        |
| 梅超风        |   74 |   67 | 通过        |
| Tom      |   65 |   67 | 通过        |
| Tabm     |   88 |   77 | 通过        |
+----------+------+------+----------+
9 rows in set (0.00 sec)

预处理

预编译一次，可以多次执行。用来解决一条SQL语句频繁执行的问题。

因为sql语句执行之前还要进行，词法分析，语法分析，然后执行，而预处理就是前两部提前执行，可以提高性能。

预处理语句：prepare 预处理名字 from ‘sql语句’
执行预处理：execute 预处理名字 [using 变量]

例题一：

mysql> prepare stmt from 'select * from stuinfo';   # 创建预处理
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;    # 执行预处理
+--------+----------+--------+--------+---------+------------+
| 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)

例题二：传递参数

mysql> delimiter // 
mysql> prepare stmt from 'select * from stuinfo where stuno=?' // -- ?是位置占位符
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @id='s25301';       -- 变量以@开头，通过set给变量赋值
    -> execute stmt using @id //  -- 执行预处理，传递参数
Query OK, 0 rows affected (0.00 sec)

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

脚下留心：
1、?是位置占位符
2、变量以@开头
3、通过set给变量赋值

例题三：传递多个参数

mysql> prepare stmt from 'select * from stuinfo where stusex=? and stuaddress=?'  //
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @sex='男';
    -> set @addr='北京';
    -> execute stmt using @sex,@addr //
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

存储过程【procedure】

存储过程的优点

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

创建存储过程

语法：

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

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

例题

mysql> delimiter //
mysql> create procedure proc()     -- 创建存储过程
    -> begin
    -> select * from stuinfo;
    -> end //
Query OK, 0 rows affected (0.00 sec)

调用存储过程

语法：

call 存储过程名()

例题：

mysql> call proc() //     -- 调用存储过程
+--------+----------+--------+--------+---------+------------+
| 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)

删除存储过程

语法

drop procedure [if exists] 存储过程名

例题：

mysql> drop procedure proc //    -- 删除存储过程
Query OK, 0 rows affected (0.00 sec)

查看存储过程的信息

show create procedure 存储过程名\G

例题

mysql> show create procedure proc \G
*************************** 1. row ***************************
           Procedure: proc
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`()
begin
select * from stuinfo;
end
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

显示所有的存储过程

mysql> show procedure status \G

存储过程的参数

存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout）

存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。

例题一：传递学号，获取对应的信息

mysql> create procedure proc(in param varchar(10))   -- 输入参数
    -> select * from stuinfo where stuno=param //
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)

例题二：查找同桌

mysql> create procedure proc(name varchar(10))
    -> begin
    -> declare seat tinyint;   -- 声明局部变量
    -> select stuseat into seat from stuinfo where stuname=name;  -- 将座位号保存到变量中
    -> select * from stuinfo where stuseat=seat+1 or stuseat=seat-1;  -- 查找同桌
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call proc('李文才') //
+--------+----------+--------+--------+---------+------------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           |
+--------+----------+--------+--------+---------+------------+
2 rows in set (0.00 sec)

强调

1、通过declare关键字声明局部变量；全局变量@开头就可以了
2、给变量赋值有两种方法
    方法一：set 变量名=值
    方法二：select 字段 into 变量 from 表 where 条件
3、声明的变量不能与列名同名

例题三：输出参数

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

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

mysql> select @result //
+---------+
| @result |
+---------+
|     100 |
+---------+
1 row in set (0.00 sec)

例题四：输入输出参数

mysql> create procedure proc(inout num int)  #  inout 表示是输入输出参数
    -> begin
    -> set num=num*num;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> set @num=10;
    -> call proc(@num);
    -> select @num //
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------+
| @num |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql触发器

待完善

存储过程与触发器的区别

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

最后编辑于：2020.01.13 10:06:17

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 161,326评论 4赞 369
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 68,228评论 1赞 304
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 110,979评论 0赞 252
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,489评论 0赞 217
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,894评论 3赞 294
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,900评论 1赞 224
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 32,075评论 2赞 317
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,803评论 0赞 205
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,565评论 1赞 249
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,778评论 2赞 253
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,255评论 1赞 265
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,582评论 3赞 261
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,254评论 3赞 241
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,151评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,952评论 0赞 201
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 36,035评论 2赞 285
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,839评论 2赞 277

MySQL多表操作

外键约束

唯一约束和主键约束

多表查询

多表查询分类

内连接【inner join】

左外连接【left join】

右外连接【right join】

交叉连接【cross join】

自然连接【natural】

using()

子查询

例题

in|not in子查询

exists和not exists

子查询分类

union（联合）

union的使用

union的选项

union的注意事项

视图【view】

创建视图

使用视图

查看视图的结构

查看创建视图的语法

显示所有视图

更改视图

删除视图

视图的作用

视图的算法

事务【transaction】

事务操作

设置事务的回滚点

事务的特性（ACID）

设置事务隔离级别

InnoDB MVCC多版本并发控制

索引【index】

索引的类型

创建普通索引【create index】

创建唯一索引

删除索引

创建索引的指导原则

函数

数字类

字符串类

时间类

加密函数

判断函数

预处理

存储过程【procedure】

存储过程的优点

创建存储过程

调用存储过程

删除存储过程

查看存储过程的信息

显示所有的存储过程

存储过程的参数

mysql触发器

存储过程与触发器的区别

推荐阅读更多精彩内容