开始实验前查看 /etc/f 的指定存储引擎是否为： innodb 以防后面没有效果
default-storage-engine=innodb
一、视图
视图：就是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据
作用：缓存数据；把一条sql的查询结果放在一个视图里，下次查询时，就不用走sql了，直接取视图里的就可以，还可以修改这个视图里面的数据，等于修改了原表数据
视图的特点：
1、视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系
2、视图是由基本表（实表）产生的表（虚表）
3、视图的建立和删除不影响基本表
4、对视图内容的更新（添加、删除和修改）直接影响基本表
5、当视图来自多个基本表时，不允许添加和删除数据
1、视图的创建
格式：CREATE VIEW 视图名称 AS sql语句(如：SELECT * FROM 表名称);
例：mysql> use gs;...........mysql> CREATE VIEW ss AS SELECT * FROM yg;Query OK, 0 rows affected (0.00 sec)
2、使用视图
格式：select 字段名称+条件from 视图名称;
例：mysql> select 姓名,工资 from ss;+-----------+--------+| 姓名| 工资 |+-----------+--------+| 小V | 6000 || 王小红 | 9080 || 小i | 16000 || 小乔| 7600 || 小光| 15000 || 蒙牛| 5555 |+-----------+--------+6 rows in set (0.00 sec)
3、更新视图
格式：update 视图名称 set 字段1=数据1;
mysql> update ss set 工资=1000;Query OK, 6 rows affected (0.00 sec)Rows matched: 6 Changed: 6 Warnings: 0mysql> select 姓名,工资 from ss ;+-----------+--------+| 姓名| 工资 |+-----------+--------+| 小V | 1000 || 王小红 | 1000 || 小i | 1000 || 小乔| 1000 || 小光| 1000 || 蒙牛| 1000 |+-----------+--------+6 rows in set (0.00 sec)
4、删除视图
格式：drop view 视图名称;
例：mysql> drop view ss;Query OK, 0 rows affected (0.00 sec)
二、触发器
触发器：就是一张表发生了某件事（插入、删除、更新操作），然后自动触发了预先编写好的若干条SQL语句的执行
作用：监视某种情况，并触发某种操作(保证数据的完整性，起到约束的作用)
注意：after/before：只能选一个 ,after 表示 后置触发, before 表示前置触发
注意：insert/update/delete：只能选一个
创建触发器
准备两个表一个 yg 一个 aaa 表；aaa为空表
mysql> select * from yg;+-----------+-----------+--------+--------+--------+| 职业| 姓名| 编号 | 学历 | 工资 |+-----------+-----------+--------+--------+--------+| 工程师 | 小V |5 | 小学 | 5000 || 云计算 | 哈哈哈 |6 | 大专 | 1000 || 工程师 | 小林|4 | 本科 | 20000 || 云计算 | 奥美|3 | 本科 | 20000 || NULL| NULL| NULL | NULL | NULL |+-----------+-----------+--------+--------+--------+5 rows in set (0.00 sec)mysql> create table aaa(id int(20),name varchar(10));Query OK, 0 rows affected (0.00 sec)
格式：create trigger 触发器名称after/before insert/update/deleteon 表名 for each row sql语句
例：创建触发器名称为 name 在执行操作 yg表 删除后 执行sql 语句 给 aaa 表 写入内容 的操作 mysql> create trigger name after delete on yg for each row insert into aaa values('1','哈哈哈'); Query OK, 0 rows affected (0.01 sec)
mysql> delete from yg where 姓名='奥美'; Query OK, 1 row affected (0.00 sec)mysql> select * from yg;+-----------+-----------+--------+--------+--------+| 职业| 姓名| 编号 | 学历 | 工资 |+-----------+-----------+--------+--------+--------+| 工程师 | 小V |5 | 小学 | 5000 || 云计算 | 哈哈哈 |6 | 大专 | 1000 || 工程师 | 小林|4 | 本科 | 20000 || NULL| NULL| NULL | NULL | NULL |+-----------+-----------+--------+--------+--------+4 rows in set (0.00 sec)mysql> select * from aaa;+------+-----------+| id | name|+------+-----------+| 1 | 哈哈哈 |+------+-----------+1 row in set (0.00 sec)
查看触发器
mysql> show triggers\G*************************** 1. row ***************************Trigger: nameEvent: DELETETable: ygStatement: insert into aaa values('1','哈哈哈')Timing: AFTERCreated: -12-17 00:08:13.95sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDefiner: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)
删除触发器
格式：drop trigger 触发器名称;
mysql> drop trigger name;Query OK, 0 rows affected (0.00 sec)mysql> show triggers\GEmpty set (0.00 sec)
三、存储过程
存储过程：类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等
1、创建不带参数的存储过程
格式：delimiter //
create procedure 表名称()
begin
SQL语句;
end //
注意：delimiter 定义结束符为"//", 然后最后又定义为";", MYSQL的默认结束符为" ; "
delimiter的详细说明：就是告诉mysql解释器，该段命令是否已经结束了mysql是否可以执行了
在默认情况下，delimiter是分号;在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令
在默认情况下，是不可能等到用户把这些语句全部输入完之后，再执行整段语句，因为mysql一遇到分号，它就要自动执行；即，在语句 select '';"时，mysql解释器就要执行了,这种情况下，就需要事先把delimiter换成其它符号，如：//或$$
例：创建名称为 aaa 的存储过程，存储的作用是从 yg 表中查询的信息，输入到sql语句和执行过程-----------------------------可直接复制粘贴 注意：修改表名称delimiter //create procedure aaa()beginselect * from yg;end //-----------------------------------------------以下是操作过程mysql> delimiter //mysql> create procedure aaa()-> begin-> select * from yg;-> end //Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> call aaa();+-----------------+-----------+--------+--------+--------+| 职业 | 姓名| 编号 | 学历 | 工资 |+-----------------+-----------+--------+--------+--------+| 云计算| 小V | 220 | 初中 | 1000 || java工程师| 王小红 | 200 | 高中 | 1000 || 云计算| 小i | 180 | 本科 | 1000 || IT工程师 | 小乔| 170 | 大专 | 1000 || 云计算| 小光| 100 | 本科 | 1000 || apache工程师 | 蒙牛| 666 | 高中 | 1000 |+-----------------+-----------+--------+--------+--------+6 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
2、创建带参数的存储过程
格式：delimiter //
create procedure 表名称()
begin
SQL语句;
end //
例：-------------------------------------- 直接复制 注意：修改表名称delimiter //create procedure ID(IN name varchar(20))beginselect 工资 from yg it where 姓名=name;end //-------------------------------------------- 以下是操作过程mysql> create procedure ID(IN name varchar(20))-> begin-> select 工资 from yg it where 姓名=name;-> end //Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> call ID('小V');+--------+| 工资 |+--------+| 1000 |+--------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
3、删除存储过程
格式：drop procedure 存储过程名称;
例：删除存储过程 aaa mysql> drop procedure aaa;//Query OK, 0 rows affected (0.00 sec)
四、MySQL 外键约束（FOREIGN KEY）
MySQL 外键约束（FOREIGN KEY）：是在两个表的数据之间建立链接，它可以是一列或者多列；一个表可以有一个或多个外键
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表
外键：是表的一个字段，不是本表的主键，但对应另一个表的主键；定义外键后，不允许删除另一个表中具有关联关系的行
外键的作用：保持数据的一致性、完整性
定义外键的规则：
1、主表必须已经存在于数据库中，或是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性
2、必须为主表定义主键
3、主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的
4、在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键
5、外键中列的数目必须和主表的主键中列的数目相同
6、外键中列的数据类型必须和主表主键中对应列的数据类型相同
7、主从表创建时，存储引擎必须是InnoDB
1、创建表时设置外键约束
----#########----在gs 数据库中创建一个 jsb 的表 字段有 id sexmysql> 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_2mysql> 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_2Create 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=utf81 row in set (0.00 sec)
注意：如果未显示内容说明存储引擎不是innodb修改后在进行实验
3、删除外键约束
在数据库中定义的外键，如果不需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系
格式：alter table 从表名称drop foreign 外键名称;
mysql> show create table jsb_2\G#没有删除前*************************** 1. row ***************************Table: jsb_2Create 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=utf81 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: 0mysql> show create table jsb_2\G #删除后*************************** 1. row ***************************Table: jsb_2Create 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=utf81 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: 0mysql> 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.字段3from 表名称1inner join 表名称2on 表名称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 表2on 表名称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在服务器层和存储引擎层的的并发控制
加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等
1、粒度锁
MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：
• MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
•BDB 存储引擎采用的是页面锁(page-level locking)，但也支持表级锁
•InnoDB 存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁
默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令
但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成
2、不同粒度锁的比较：
表级锁：
1、开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
2、这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁
3、表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
例： 给aaa 表进行加锁mysql> lock tables yg read;Query OK, 0 rows affected (0.00 sec)
重新打开一个通道进行查看是可以查看的
mysql> select * from yg;+-----------+-----------+--------+--------+--------+| 职业| 姓名| 编号 | 学历 | 工资 |+-----------+-----------+--------+--------+--------+| 工程师 | 小V |5 | 小学 | 5000 || 云计算 | 哈哈哈 |6 | 大专 | 1000 || 工程师 | 小林|4 | 本科 | 20000 || NULL| NULL| NULL | NULL | NULL |+-----------+-----------+--------+--------+--------+4 rows in set (0.00 sec)
但是不能写入，会和下面一样一直等待
在释放锁
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)########### 写入成功mysql> insert into yg values('打杂','小二',250,'幼儿园',50);Query OK, 1 row affected (1 min 6.19 sec)
mysql> lock tables yg write; #加写锁,其他会话不可读,不可写Query OK, 0 rows affected (0.00 sec)会和上面的gif一样等待
行级锁：
1、开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
2、最大程度的支持并发，同时也带来了最大的锁开销
3、在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的
4、行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
记录锁：就是锁住记录，锁住索引记录，而不是真正的数据记录
1、锁是非主键索引，会在索引记录上加锁后，在去主键索引上加锁
2、表上没有索引，会在隐藏的主键索引上加锁
3、如果要锁的列没有索引，进行全表记录加锁
锁等待与死锁
锁等待也可称为事务等待，后执行的事务等待前面处理的事务释放锁，但是等待时间超过了 MySQL 的锁等待时间，就会引发这个异常。 等待超时后就会报错
查看锁等待允许时间mysql> show variables like "innodb_lock_wait_timeout";+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50 |+--------------------------+-------+1 row in set (0.00 sec)
查看锁等待mysql> SHOW STATUS LIKE 'innodb_row_lock%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| Innodb_row_lock_current_waits | 0| # 当前正在等待锁的数量| Innodb_row_lock_time| 0| # 从系统启动到现在锁定总时间长度| Innodb_row_lock_time_avg| 0| # 每次等待所花平均时间| Innodb_row_lock_time_max| 0| # 从系统启动到现在等待最长的一次所花的时间长度| Innodb_row_lock_waits | 0| # 系统启动到现在总共等待的次数+-------------------------------+-------+5 rows in set (0.00 sec)
死锁发生的原因是两个事务互相等待对方释放相同资源的锁，从而造成的死循环
在实际应用中，尽量避免死锁现象的发生，可以从以下几点预防：
1、事务尽可能小，不要讲复杂逻辑放进一个事务里
2、涉及多行记录时，约定不同事务以相同顺序访问
3、业务中要及时提交或者回滚事务，可减少死锁产生的概率
4、表要有合适的索引
5、可尝试将隔离级别改为 RC
