MySQL视图触发器存储过程内外链接数据锁

MySQL视图触发器存储过程内外链接数据锁

视图

什么是视图

是一个虚拟表，其内容由查询定义。同真实的一样，视图包含一系列带有名称的列和行数据

视图有什么用

视图的作用就是缓存数据，可以这么理解，我把一条sql的查询结果装在了一个视图里，我下次再查的时候，就不用走SQL了，直接取这个视图里就可以了，神奇的是我可以修改这个视图里的数据就等于修改了原表的数据

视图的特点

1.视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系

2.视图是由基本表(实表)产生的表(虚表)

3.视图的建立和删除不影响基本表

4.对视图内容的更新(添加、删除、修改)直接影响基本表

5.当视图来自多个基本表时,不允许添加和删除数据

视图的创建

create view 视图名称 as sql查询语句;

视图的使用

select 字段名+条件 from 视图名称;

视图的更新

update 视图名称;

视图的删除

drop view 视图名称;

触发器

什么是触发器

简单地说，就是一张表发生了某件事(插入删除更新)，然后自动触发了预先编写好的若干条SQL语句的执行

触发器的作用

监视某种情况，并触发某种操作(保证数据的完整性，起到约束的作用)

触发器创建语法四要素

1.监视地点(table)

2.监视事件(insert/update/delete)

3.触发时间(after/before)

4.触发事件(insert/update/delete)

创建触发器语法

create trigger triggerName after/before insert/update/delete

on 表名 for each row     #这句话是固定的

begin

#需要执行的SQL语句

End

mysql>create trigger agf after delete on s1 for each row

->insert into it values(‘架构师’，’小明’,’150505188201241312’,’海归’,’10000’);

#查看触发器

mysql>show triggers\G

注意1：after/before:只能选一个，after表示后置触发,before表示前置触发

注意2：insert/update/delete:只能选择一个

存储过程

咱在mysql中敲SQL语句的时候，是一行一行的敲，那么存储过程就好比，把多条SQL语句放到存储过程之中，然后再去执行这个存储过程

存储过程有什么优点缺点

优点:

1.存储过程增强了SQL语言灵活性

2.减少网络流量，降低了网络负载

3.存储过程只在创造时进行编译，以后每次执行存储过程都不需要再重新编译

缺点:

1.扩展功能不方便

2.不便于系统后期维护

创建不带参数的存储过程

例:创建名称为ShowStuScore的存储过程，存储过程的作用是从it表中查询学生的成绩信息，输入的SQL语句和执行过程如下所示

mysql>DELIMITER //

mysql>CREATE PROCEDURE ShowStuScore()

  ->BEGIN

      ->select * from it;

      ->END //

创建存储过程后通过call语句条用该存储过程的SQL语句和执行结果如下

mysql>CALL ShowStuScore

创建带参数的存储过程

例:创建名称为GetScoreByStu的存储过程，输入参数是姓名。存储过程的作用是通过输入的姓名从it表中查询指定学生的信息，输入的SQL语句和执行过程如下所示。

mysql>DELIMITER

mysql>create procedure GetScoreByStu (IN name varchar(30))

->BEGIN

->select 工资 from it where 姓名=name

->END //

创建存储过程GetScoreBystu后,通过call语句调用该存储过程的SQL语句和执行结果如下所示.

mysql>DELIMITER

mysql>CALL GetScoreByStu

删除存储过程

删除存储过程GetScoreByStu,查看存储过程的运行结果如下所示。

mysql>DROP PROCEDURE GetScoreByStu

MySQL外键约束

MySQL外键约束用来在两个表的数据之间建立连接，他可以是一列或者多列，一个表可以有一个或者多个外键，在设置外键的时候，要有两张表，一个是主表(父表)，另一个表是从表(子表)

主表:对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表

从表:对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表

外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。外键的主要作用是保持数据的一致性、完整性。例如，部门表tb_dept的主键是id，在员工表tb_emp5中有一个键deptId与id相连

选取设置MySQL外键约束的字段

定义一个外键时,需要遵守下列规则

1.主表必须已经存在于数据库中，或者是当前正在创建的表如果是后一种情况，则主表与从表是同一个表，这样的表成为自参照表，这种结构成为自参照完整性

2.必须为主表定义主键

3.主键不能包含空值,但允许在外键中出现空值。

在创建表时设置外键约束

在数据表中创建外键使用FOREIGN KEY关键字，具体的语法规则如下；

[CONSTRAINT <外键名>] F0REIGN KEY字段名[, 字段名2,… ]

REFERENCES <主表名>主键列1 [, 主键列2,… ]

其中:外键名为定义的外键约束的名称，-个表中不能有相同名称的外键;字段名表示子表需要添加外健约束的字段列;主表名即被子表外键所依赖的表的名称;主键列表示主表中定义的主键列或者列组合。

1、创建表时设置外键约束

----#########----在gs 数据库中创建一个 jsb 的表 字段有 id  sex

mysql> create table jsb(id int(10) not null,sex enum('a','b') default null,primary key (id)) engine=innodb;             

Query OK, 0 rows affected (0.00 sec)

格式：create table 表名称(新建)(id int(10) primary key,

           name varchar(25),

           deptld int(11),

           constraint 外键名 foreign key(关联字段) references 父表名称(主键字段) on delete cascade on update cascade);

外键名：定义外键约束的名称，一个表中不能有相同名称的外键

              字段名表示子表需要添加外健约束的字段列

              主表名即被子表外键所依赖的表的名称

              主键列表示主表中定义的主键列或者列组合

例：创建表 jsb_1  并在表上创建外键约束 fk_jsb ，让它的键 deptId 作为外键关联到表 jsb 的主键 id            

mysql> create table jsb_1(id int(10) primary key,name varchar(25),deptld int(11),constraint fk_jsb foreign key(deptld) references jsb(id) on delete cascade on update cascade);                               

Query OK, 0 rows affected (0.01 sec)

~~~~~~~~~~~~写入数据省略.....

mysql> select * from jsb;                                     

+----+------+

| id | sex  |

+----+------+

|  1 | a    |

|  2 | b    |

|  3 | a    |

+----+------+

3 rows in set (0.00 sec)

mysql> select * from jsb_1;       

+----+--------------+--------+

| id | name         | deptld |

+----+--------------+--------+

|  1 | aaa          |      1 |

|  2 | sss          |      1 |

|  3 | ccc          |      2 |

|  4 | 啊啊地方      |      2 |

|  5 | 5adf         |      3 |

|  6 | aad          |      3 |

+----+--------------+--------+

6 rows in set (0.00 sec)

mysql> delete from jsb where id=2;

Query OK, 1 row affected (0.00 sec)

mysql> select * from jsb;

+----+------+

| id | sex  |

+----+------+

|  1 | a    |

|  3 | a    |

+----+------+

2 rows in set (0.00 sec)

mysql> select * from jsb_1;

+----+------+--------+

| id | name | deptld |

+----+------+--------+

|  1 | aaa  |      1 |

|  2 | sss  |      1 |

|  5 | 5adf |      3 |

|  6 | aad  |      3 |

+----+------+--------+

4 rows in set (0.00 sec)

 发现我们删除jsb上的  2 ，jsb_1 表的 deptld 中的 2 也已经删除了

2、已经创建表时添加外键约束

 格式：alter table 表名称 add constraint 外键约束 foreign key(字段) references 主表名称(主键字段);

例：  创建表 jsb_2

mysql> create table  jsb_2(id int(11) primary key,name varchar(22) not null,deptld int(15));

Query OK, 0 rows affected (0.00 sec)

               进行添加外键约束

mysql> alter tab2 add constraint aaa foreign key(deptld) references jsb(id);sb(id);

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

             查看

mysql> SHOW CREATE TAB2\G

*************************** 1. row ***************************

       Table: jsb_2

Create Table: CREATE TABLE `jsb_2` (

  `id` int(11) NOT NULL,

  `name` varchar(22) NOT NULL,

  `deptld` int(15) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `aaa` (`deptld`),

  CONSTRAINT `aaa` FOREIGN KEY (`deptld`) REFERENCES `jsb` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 注意：如果未显示内容说明存储引擎不是innodb修改后在进行实验

删除外键约束

在数据库中定义的外键，如果不需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系

格式：alter table 从表名称 drop foreign 外键名称;

mysql> show create table jsb_2\G                              #没有删除前

*************************** 1. row ***************************

       Table: jsb_2

Create Table: CREATE TABLE `jsb_2` (

  `id` int(11) NOT NULL,

  `name` varchar(22) NOT NULL,

  `deptld` int(15) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `aaa` (`deptld`),

  CONSTRAINT `aaa` FOREIGN KEY (`deptld`) REFERENCES `jsb` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

mysql> alter table jsb_2 drop foreign key aaa;                 #删除外键

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table jsb_2\G                                #删除后

*************************** 1. row ***************************

       Table: jsb_2

Create Table: CREATE TABLE `jsb_2` (

  `id` int(11) NOT NULL,

  `name` varchar(22) NOT NULL,

  `deptld` int(15) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `aaa` (`deptld`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

五、MySQL 内外连接查询

1、MySQL INNER JOIN :内连接查询

 关键字：inner join on

---------------############------  先做环境

使用之前创建的yg 表 创建一个 yy表

mysql> create table yy(姓名 varchar(20),身份证 varchar(20),UID int(20));

Query OK, 0 rows affected (0.00 sec)

写入数据

mysql> insert into yy values('小i',1111111111,110),('小乔',222277,210),('迪斯科',33333,10),('蒙牛',454545454,25);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from yy ;

+-----------+------------+------+

| 姓名      | 身份证     | UID  |

+-----------+------------+------+

| 小i       | 1111111111 |  110 |

| 小乔      | 222277     |  210 |

| 迪斯科    | 33333      |   10 |

| 蒙牛      | 454545454  |   25 |

+-----------+------------+------+

4 rows in set (0.00 sec)

内连接：在查询中设置连接条件的方式，来移动查询结果集中某些数据行后的交叉连接

 简单说就是：查询的结果为两表的公共部分

格式：select 表名称2.字段1,表名称1.字段2,表名称1.字段3 from 表名称1 inner join 表名称2 on 表名称2.字段1 = 表名称1.字段1;

 注意：如果这样写：select 姓名,职业 .....   会报错显示 in field list is ambiguous（字段不明确）需要完全限定表名 表名.字段1

例：在 yg 表和 yy表中查询出两个表的  姓名 字段一致的 姓名

mysql> select yy.UID,yg.姓名,yg.职业,yg.工资 from yg inner join yy on yy.姓名 = yg.姓名;

+------+--------+-----------------+--------+

| UID  | 姓名   | 职业            | 工资   |

+------+--------+-----------------+--------+

|  110 | 小i    | 云计算          |   1000 |

|  210 | 小乔   | IT工程师        |   1000 |

|   25 | 蒙牛   | apache工程师    |   1000 |

+------+--------+-----------------+--------+

3 rows in set (0.00 sec)

2、MySQL LEFT/RIGHT JOIN：外连接查询

外连接：先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录

外连接更加注重两张表之间的关系；按照连接表的顺序，可以分为左连接和右连接

2.1、左连接查询 left join

关键字：left join on / left outer join on

左外连接：左表数据不动，右边表的数据往左表上去添加，不管是否找到，都将显示左边表中全部记录

格式：select * from 表1 left join 表2 on 表名称1.字段1=表名称2.字段1;

mysql> select * from yg left join yy on yg.姓名=yy.姓名;

+-----------------+-----------+--------+--------+--------+--------+--------+------------+------+

| 职业            |  姓名      | 生日   |  编号   |  学历  |  工资   |  姓名  |   身份证   | UID  |

+-----------------+-----------+--------+--------+--------+--------+--------+------------+------+

| 云计算          | 小i       |   NULL |    180 | 本科   |   1000 | 小i    | 1111111111 |  110 |

| IT工程师        | 小乔      |   NULL |    170 | 大专   |   1000 | 小乔   | 222277     |  210 |

| apache工程师    | 蒙牛      |   NULL |    666 | 高中   |   1000 | 蒙牛   | 454545454  |   25 |

| 云计算          | 小V       |   NULL |    220 | 初中   |   1000 | NULL   | NULL       | NULL |

| java工程师      | 王小红    |   NULL |    200 | 高中   |   1000 | NULL   | NULL       | NULL |

| 云计算          | 小光      |   NULL |    100 | 本科   |   1000 | NULL   | NULL       | NULL |

+-----------------+-----------+--------+--------+--------+--------+--------+------------+------+

6 rows in set (0.00 sec)

2.2、右连接 right join

关键字：right join on / right outer join on

右外连接：用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示

格式： select * from 表1 right outer join 表2 on 表名称1.字段1=表名称2.字段1;

mysql> select * from yg right outer join yy on yy.姓名=yg.姓名;

+-----------------+--------+--------+--------+--------+--------+-----------+------------+------+

| 职业            | 姓名   | 生日   | 编号   | 学历   | 工资   | 姓名      | 身份证     | UID  |

+-----------------+--------+--------+--------+--------+--------+-----------+------------+------+

| 云计算          | 小i    |   NULL |    180 | 本科   |   1000 | 小i       | 1111111111 |  110 |

| IT工程师        | 小乔   |   NULL |    170 | 大专   |   1000 | 小乔      | 222277     |  210 |

| apache工程师    | 蒙牛   |   NULL |    666 | 高中   |   1000 | 蒙牛      | 454545454  |   25 |

| NULL            | NULL   |   NULL |   NULL | NULL   |   NULL | 迪斯科    | 33333      |   10 |

+-----------------+--------+--------+--------+--------+--------+-----------+------------+------+

4 rows in set (0.00 sec)

MySQL锁

锁概述

MySQL的锁的机制，就是数据库为了保证数据的一致性而设计的面对并发场景的一种规则。

最显著的特点是不同的存储引擎支持不同的锁机制，InnoDB支持行锁和表锁，MyISAM支持表锁。

表锁就是把整张表锁起来，特点是加锁快，开销小，不会出现死锁，锁力度大，发生锁冲突的概率高，并发相对较低。

行锁就是以行为单位把数据锁起来，特点加锁慢，开销大，会出现死锁，锁力度小，发生锁冲突的概率低，并发度也相对表锁较高

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环

行锁增加了系统的开销，要比表锁系统开销大

当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能产生死锁