mysql外键，锁

mysql外键：

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

注意事项：

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

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

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

外键：foreign key 表A字段名

references 表B 字段名

如何新增外键：

例子：创建test5员工姓名表，和test6员工工资表，在test6表查询员工工资
mysql> create table test5 (name char(30),primary key(name),age tinyint(90));
Query OK, 0 rows affected (0.01 sec)

mysql> desc test5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | char(30)    | NO   | PRI | NULL    |       |
| age   | tinyint(90) | YES  |     | NULL    |       |

插入两个员工信息
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 |
+----------+

多表查询：

格式：select * from 表1名，表2名   where  表1名.字段名=表2名.字段名；
例子：mysql> select * from test3,test4 where test3.name=test4.name;
+------+--------+--------+---------+-------+------+------+
| name | gender | hobby3 | address | money | name | age  |
+------+--------+--------+---------+-------+------+------+
| lisi | NULL   |   NULL | NULL    |  NULL | lisi |   30 |
+------+--------+--------+---------+-------+------+------+

左连接查询：

格式：select 字段名 from  表a  left  join  表2  on  表1名.字段名=表1名.字段名；
例子：查看下test3和4
mysql> select * from test4;
+----------+------+
| name     | age  |
+----------+------+
| lisi     |   30 |
| wanger   |   10 |
| zhangsan |   10 |
+----------+------+
3 rows in set (0.00 sec)

mysql> select * from test3;
+------+--------+--------+---------+-------+
| name | gender | hobby3 | address | money |
+------+--------+--------+---------+-------+
| lisi | NULL   |   NULL | NULL    |  NULL |
| 10   | NULL   |   NULL | NULL    |  NULL |
+------+--------+--------+---------+-------

以3为准左连查询：
mysql> select test3.name from test3 left join test4 on test3.name=test4.name;
+------+
| name |
+------+
| lisi |
| 10   |
+------+


以test4为准右连查询：
mysql> select test3.name from test3 right join test4 on test3.name=test4.name;
+------+
| name |
+------+
| lisi |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)
#以test4为准，test3里面没有这个值所有显示Null

mysql存储引擎

1.MyISAM

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

不支持事务

表级锁

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

读取速度快，占用资源少

不支持mvcc高并发

崩溃恢复性较差

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

MyISAM物理文件：

.frm表格式定义

.myd数据文件

.myi索引文件

2，InnoDB

特点：支持行级锁

支持MVCC多并发控制

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

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

崩溃恢复性好

5.5后作为默认存储引擎

InnoDB数据文件：

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

数据文件：ibdatal datadir

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

查看所有的存储引擎：

show engines;

查看当前默认存储引擎：

show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

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;

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                 |
+----+------+-----------+------+---------+------+---------------------------------+----------------------+
|  3 | root | localhost | db1  | Sleep   | 6800 |                                 | NULL                 |
|  4 | root | localhost | NULL | Sleep   | 3850 |                                 | NULL                 |
|  5 | root | localhost | db1  | Sleep   |   56 |                                 | NULL                 |
|  6 | root | localhost | db1  | Query   |   19 | Waiting for table metadata lock | select * from  test4 |
|  9 | root | localhost | NULL | Query   |    0 | starting                        | show processlist     |
+----+------+-----------+------+---------+------+---------------------------------+----------------------+
5 rows in set (0.01 sec)