mysql 简单使用进阶（外键,多表，索引）
调侃
什么是外键？
我也说不清楚！
但还是用自己的语言描述一下。
有些关系数据无用一个表来完整表达，所以需要建立另外一个相关的表，于是他们存在一个引用关系，这个引用就需要使用外键。
真实例子
一家公司，公司会有一张表记录员工的基本信息，然后又需要另外一张表记录员工的工资发放，但有时候需要从工资表查看员工基本信息，这个时候就需要外键。
设计表
一个员工的基本信息无非是：
1. 姓名
2. 性别
3. 出生日期
4. 年龄
5. 籍贯
6. 学历
7. 家庭住址
8. 联系电话
9. email
10. 身份证
。。。。
为了简单，我们表的字段就简单一点
姓名，性别，年龄，联系电话 四个就够了，但是这四个没有一个适合做主键，所以添加一个id作为主键。
MariaDB [houziyu]> create table imployee_information_1 (
> id int not null auto_increment primary key,
> name char(32) not null,
> sex enum('男','女') not null default '男',
> age int(3) not null,phone int(15) not null,
> remarks varchar(200));
接着我们添加几条信息
MariaDB [houziyu]> insert into imployee_information (name, sex, age, phone) values
-> ('小明','男',18,10098),
-> ('小刚','男',19,10086),
-> ('小红','女',17,10095);
MariaDB [houziyu]> select * from imployee_information;
+----+--------+-----+-----+-------+---------+
| id | name | sex | age | phone | remarks |
+----+--------+-----+-----+-------+---------+
| 1 | 小明 | 男 | 18 | 10098 | NULL |
| 2 | 小刚 | 男 | 19 | 10086 | NULL |
| 3 | 小红 | 女 | 17 | 10095 | NULL |
+----+--------+-----+-----+-------+---------+
关于外键
建好了主键表现在就建一个外键表，外键表里也会有主键。
这张表我们要记录的是员工的工资发放情况，里面可能会有的信息是：
1. id编号作为主键
2. 发放的时间
3. 发放的人是谁
4. 工资的金额
5. 详细的奖罚过程。。。。
实际中肯能会有更多信息但是这里只列出主要信息。
首先我们先了解下外键的基本创建语法：
CREATE TABLE 外键表 (
字段1 类型 能否为空 默认值 (可以写主键),
字段2 类型 能否为空 默认值,
PRIMARY KEY (字段1),
KEY 外键 (外键别名), //这里可以不写
CONSTRAINT 外键 FOREIGN KEY (外键别名) REFERENCES 主表 (主表那个字段)
);
建外键表
MariaDB [houziyu]> create table wages (
-> id int auto_increment primary key,
-> time date not null,
-> money int not null,
-> detailed varchar(200),
-> claim int not null,
-> key name_id (claim),
-> constraint name_id foreign key (claim) references student (id));
不懂可以对照上面的语法
MariaDB [houziyu]> DESC wages;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | NO | | NULL | |
| money | int(11) | NO | | NULL | |
| detailed | varchar(200) | YES | | NULL | |
| claim | int(11) | NO | MUL | NULL | |
+----------+--------------+------+-----+---------+----------------+
从上表 claim 的key值是mul，表示外键
现在先添加几条数据
MariaDB [houziyu]> insert into wages (time, money, claim) values
('2018-03-04', 5000, 1),
('2018-03-04', 5000, 1),
('2018-03-04', 5000, 2),
('2018-03-04', 5000, 3),
('2018-04-04', 8000, 3),
('2018-04-04', 8000, 1),
('2018-04-04', 3000, 2);
MariaDB [houziyu]> select * from wages;
+----+------------+-------+----------+-------+
| id | time | money | detailed | claim |
+----+------------+-------+----------+-------+
| 1 | 2018-03-04 | 5000 | NULL | 1 |
| 2 | 2018-03-04 | 5000 | NULL | 1 |
| 3 | 2018-03-04 | 5000 | NULL | 2 |
| 4 | 2018-03-04 | 5000 | NULL | 3 |
| 5 | 2018-04-04 | 8000 | NULL | 3 |
| 6 | 2018-04-04 | 8000 | NULL | 1 |
| 7 | 2018-04-04 | 3000 | NULL | 2 |
+----+------------+-------+----------+-------+
查看表的创建过程
MariaDB [houziyu]> show create table wages;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wages | CREATE TABLE `wages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` date NOT NULL,
`money` int(11) NOT NULL,
`detailed` varchar(200) DEFAULT NULL,
`claim` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `name_id` (`claim`),
CONSTRAINT `name_id` FOREIGN KEY (`claim`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
多表查询
NUll
当mysql查询数据为null时，mysql无法正常工作，为此有了三大运算符：
1. IS NULL ：当查询值为null，运算符返回true；
2. IS NOT NULL ： 当查询值不为null， 运算符返回true；
3. < = > ：当两边的值为null时， 运算符返回true 。
MySQL 连接
(left join, right join, innet join, full join)
MySQL 用join来进行多表查询。
只要有三种方法：
一、INNET JOIN (内连接或等值连接)：获取连个表中字段匹配关系的记录。
二、LEFT JOIN （获取左边所有记录，即使右面没有匹配记录）
三、 RIGHT JOIN (与left join相反)
语法SELECT * FROM 表1 (INNET/LEFT/RIGHT) JOIN 表2 ON 表1.字段 = 表2.字段
MySQL 并不支持full jion，但是可以自这样SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段 UNION SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段
事务
开始一个事务用 begin
如果确认提交数据 commit
如果不要像回滚 rollback
begin; // statr
insert into xxxx (xxx,xxx,xxx) values (xxx,xxx,xxx);
// 确定要提交上面的数据
commit;
// 如果上面的数据报错或者写入有误
bollback;
如果中途宕机或者断开连接，事务将会回滚。
索引
索引能大大提高数据的查询速度。
当面对庞大的数据量时，比如上亿的数据量，索引的至关重要就能体现的淋漓尽致。
没有索引你可能有上百秒，但是有索引你可能只有几秒到十几秒。
索引分为单列索引和组合索引。
单列索引，即索引只包含一个列，一个表可以有多个单列索引，但它不是组合索引。
组合索引，是一个索引包含多个列。
基本操作
创建索引
语法CREATE INDEX 索引名 ON 表名(字段(length))
如果是char，varchar length可以小于实际长度，BLOB和TEXT 必须指定length。
修改表结构
语法ALTER 表名 ADD INDEX [索引名] ON (字段(length))
创建时指定索引
语法CREATE TABLE mytable(id int not null,username varchar(16) not null,index [indexname] (username(length)));
MariaDB [houziyu]> create index name_index on student(name(32));
查看索引
MariaDB [houziyu]> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 9 | NULL | NULL | | BTREE | | |
| student | 1 | name_index | 1 | name | A | 9 | NULL | NULL | | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
主键自动就是索引
删除索引
语法DROP INDEX [索引名] ON 表名;