四十六、MySQL数据库3

"""
# 补充知识点，插入数据的时候，可以指定字段顺序
create table t1(
id int,
name char(16)
);
insert into t1(name,id） values(‘jason‘,1);
create table t2(
id int,
name char(16) not null,
gender enum(‘male‘,‘female‘,‘others‘) default ‘male‘
);
mysql> desc t2;
+--------+-------------------------------+------+-----+---------+-------+
| Field  | Type                          | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+-------+
| id     | int(11)                       | YES  |     | NULL    |       |
| name   | char(16)                      | NO   |     | NULL    |       |
| gender | enum(‘male‘,‘female‘,‘other‘) | YES  |     | male    |       |
+--------+-------------------------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
insert into t2(id,name) values(1,‘jason‘);
insert into t2 values(2,‘egon‘,‘female‘);
mysql> select * from t2;
+------+------------------+--------+
| id   | name             | gender |
+------+------------------+--------+
|    1 | jason            | male   |
|    2 | egon             | female |
+------+------------------+--------+
2 rows in set (0.00 sec)
"""

"""
# 单列唯一
create table t3(
id int unique,
name char(16)
);
insert into t3 values(1,‘jason‘),(1,‘egon‘);
insert into t3 values(1,‘jason‘),(2,‘egon‘);
mysql> desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.05 sec)
mysql> insert into t3 values(1,‘jason‘),(1,‘egon‘);
ERROR 1062 (23000): Duplicate entry ‘1‘ for key ‘id‘
# 联合唯一
ip 和 port
单个都可以重复，但是加在一起必须唯一
id     ip    port
1       1     1
2       1     2
3       2     1
4       3     1
create table t4(
id int,
ip char(16),
port int,
unique(ip,port)
);
mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| ip    | char(16) | YES  | MUL | NULL    |       |
| port  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)
insert into t4 values(1,‘127.0.0.1‘,8080);
insert into t4 values(2,‘127.0.0.1‘,8081);
insert into t4 values(3,‘127.0.0.2‘,8080);
mysql> insert into t4 values(3,‘127.0.0.2‘,8080);
Query OK, 1 row affected (0.05 sec)
前面三条执行成功
insert into t4 values(4,‘127.0.0.1‘,8080);
mysql> insert into t4 values(4,‘127.0.0.1‘,8080);
ERROR 1062 (23000): Duplicate entry ‘127.0.0.1       -8080‘ for key ‘ip‘
"""

"""
# 1.单单从约束效果上来看 primary key  等价于  not null + unique
非空且唯一！！！！
create table t5(id int primary key);
mysql> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.04 sec)
insert into t5 values(null);
mysql> insert into t5 values(null);
ERROR 1048 (23000): Column ‘id‘ cannot be null
insert into t5 values(1),(2);
insert into t5 values(3),(3);
mysql> insert into t5 values(3),(3);
ERROR 1062 (23000): Duplicate entry ‘3‘ for key ‘PRIMARY‘
#2.它除了有约束效果之外，还是存储引擎Innodb存储引擎组织数据的依据
Innodb 存储引擎规定在创建表的时候，必须要有primary key
因为它类似于数的目录，能够帮助提升查询效率，并且也是建表的依据
2.1一张表中有且只能有一个主键，如果你没有设置主键，那么会从上往下搜索知道遇到一个非空且唯一的字段将它主动升为主键
create table t6(
id int,
name char(16),
age int not null unique,
addr char(32) not null unique
);
mysql> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
| age   | int(11)  | NO   | PRI | NULL    |       |
| addr  | char(32) | NO   | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.06 sec)
2.2 如果表中既没有设置主键也没有其他任何非空且唯一的字段，那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用它，就无法提升查询速度
2.3 一张表中通常都应该有一个主键字段，并且通常将id/uid/sid字段作为主键
#单个字段主键
create table t5(
id int primary key,
name char(16)
);
#联合主键（多个字段联合起来作为表的主键，本质还是一个主键）
create table t7(
id int,
ip char(16),
port int,
primary key(ip,port)
);
mysql> desc t7;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| ip    | char(16) | NO   | PRI | NULL    |       |
| port  | int(11)  | NO   | PRI | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
也就意味着，以后我们在创建表的时候，id字段一定要加primary key
"""