mysql外键，锁

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

mysql外键：

场景：用于建立两个表之间的联系，让A表中一个字段，可以在另一个表中字段值的范围去查找

注意事项：

（1）被参照表和参照表字段属性必须一致

（2）参照表必须设置主键

（3）必须选择支持外键的

外键：foreign key 表A字段名

references 表B 字段名

如何新增外键：

插入两个员工信息
mysql> insert test5 values ('zhangsan',30),('lisi',40);

查看下test5表内容
mysql> select * from test5;
+----------+------+
| name | age |
+----------+------+
| lisi | 40 |
| zhangsan | 30 |
+----------+------+

创建test6表，并设置外键
mysql> create table test6 (name char(40),money int(100),foreign key(name) references test4(name));
#foreign key(当前表外键字段)
#references test4(name)被参照表的表名和字段，中间不能有逗号

如果在test6表中插入数值，范围必须从test5中的主键字段里面选值

先查看下test5的数据内容
mysql> select * from test5;
+----------+------+
| name | age |
+----------+------+
| lisi | 40 |
| zhangsan | 30 |
+----------+------+去test6表插入其他名字的字段会有外键约束
mysql> insert test6 values ('www',99),('zhongguo',57);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`test6`, CONSTRAINT `test6_ibfk_1` FOREIGN KEY (`name`) REFERENCES `test4` (`name`))插入lisi 看看
mysql> insert test6 values ('zhangsan',99);
Query OK, 1 row affected (0.01 sec)mysql> select * from test6;
+----------+-------+
| name | money |
+----------+-------+
| lisi | 22 |
| zhangsan | 99 |
+----------+-------+

如何修改外键，同步修改同步删除：on update cascade on delete cascade

mysql> create table test6 (name char(100),money int(99),foreign key(name) references test5(name) on update cascade on delete cascade);

mysql> update test5 set name='hhh' where age=55;
Query OK, 1 row affected (0.01 sec)
查看test5和test6的值就是一样的

如何删除外键里面的数据：

mysql> delete from test5 where name='zhangsan';
Query OK, 1 row affected (0.01 sec)mysql> select * from test5;
+------+------+
| name | age |
+------+------+
| hhh | 55 |
+------+------+
1 row in set (0.00 sec)mysql> select * from test6;
+------+-------+
| name | money |
+------+-------+
| hhh | 888 |
+------+-------+
1 row in set (0.00 sec)

如何查看外键：

show create table test5;

如何删除外键约束：

导入数据(需禁用SElinux)：

格式：load date infile '文件的绝对路径' into table table_name fields terminated by '文件分隔符' lines terminated by '\n';

数值比较：
select name,UID from table_name where UID > 0; #查找UID大于0的名字
select name,UID from table_name where UID > 0 and UID < 100 #查找UID在0-100间的名字
select name from table_name where describe = 'root'; 查看

limit排序，限制显示行数
select * from table_name limit 1,2;
#第一行不显示，显示第二三行

如何设置属性的默认值

mysql> create table t7 (name char(30),primary key(name),age tinyint(90) default 23);
#创建t7表，创建名字字段并且设置为主键，创建age字段，默认是23插入数据：
mysql> insert t7 (name) values ('zhangsan');
Query OK, 1 row affected (0.00 sec)mysql> select * from t7;
+----------+------+
| name | age |
+----------+------+
| zhangsan | 23 |
+----------+------+
1 row in set (0.00 sec)

不进入数据库查询某个表的一列字段

select 表名.字段名 from 数据库名.表名；
例子：mysql> select t7.name from db1.t7 ;
+----------+
| name |
+----------+
| zhangsan |
+----------+

多表查询：

左连接查询：

mysql存储引擎

1.MyISAM

特点：mysql5.5之前默认是MyISAM版的存储引擎

不支持事务

表级锁

读写相互阻塞，写的时候不能读，读的时候不能写

读取速度快，占用资源少

不支持mvcc高并发

崩溃恢复性较差

使用场景：只读，写入情况比较少，表比较小，能接受长时间进行修复操作

MyISAM物理文件：

.frm表格式定义

.myd数据文件

.myi索引文件

2，InnoDB

特点：支持行级锁

支持MVCC多并发控制

支持事务，适合处理大量短期事务

读写阻塞与事务隔离级别有关

崩溃恢复性好

5.5后作为默认存储引擎

InnoDB数据文件：

表数据和索引在同一个表空间

数据文件：ibdatal datadir

表格式文件：frm datadir 下的数据库对应目录下

查看所有的存储引擎：

show engines;

查看当前默认存储引擎：

mysql锁机制

读锁：共享锁 S 只读不可写，包含当前事务，多个读不阻塞

写锁，X写锁会影响其他事务的读写操作，不包含当前事务

读锁例子：
格式：locak table 表名 read;
例子： lock table test3 read;
查看这个表：select * from test3;
插入数据：insert test3 (name) values ('wanger');
ERROR 1099 (HY000): Table 'test3' was locked with a READ lock and can't be updated写锁：
格式：lock table 表名 write;
例子：

案例：找到未完成的导致阻塞的事务

#在第一个会话中开启一个事务
mysql> use db1;
mysql> begin;
mysql> update date set name="centos";
mysql> select * from date;
#在第二个会话中执行（不需要开启一个事务）
mysql> select * from db1.date;
mysql> update date set name="rhel";
#在运维管理会话
mysql> show engine innodb status\G;
#查看当前正在进行的事务
mysql> select * from information_schema.innodb_trx\G;
#查看当前锁定的事务
mysql> select * from information_schema.innodb_locks\G;
#查看当前等锁的事务
mysql> select * from information_schema.innodb_lock_waits\G;
如何解决？
#查看事务列表
mysql> show processlist;