数据库学习-表结构、键值
回顾数据类型
mysql> create table test9 ( name char(10), age tinyint(3) unsigned, address varchar(50), pay float(7,2), start year, birthday date,likes set('eat','game','film','music'), sex enum('boy','girl'));
Query OK, 0 rows affected, 2 warnings (0.46 sec)
mysql> desc test9;
+----------+----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| pay | float(7,2) | YES | | NULL | |
| start | year(4) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| likes | set('eat','game','film','music') | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
+----------+----------------------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
mysql> insert into test9 values('lisi',16,'shenzhen-baoan-xixiang',782.53,1997,date(now()),'game,film','boy');
Query OK, 1 row affected (0.06 sec)
mysql> select * from test9;
+------+------+------------------------+--------+-------+------------+-----------+------+
| name | age | address | pay | start | birthday | likes | sex |
+------+------+------------------------+--------+-------+------------+-----------+------+
| lisi | 16 | shenzhen-baoan-xixiang | 782.53 | 1997 | 2023-05-06 | game,film | boy |
+------+------+------------------------+--------+-------+------------+-----------+------+
1 row in set (0.03 sec)
表结构
字段约束条件（限制字段赋值的）
NULL： 是否允许赋空值，默认允许，可设置
列名 类型 not null;
.
key: 键值
.
default : 默认值，向表里添加新行时，不给列赋值，会使用默认值赋值
列名 类型 default 值;
.
mysql> desc test11;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| money | float(8,3) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
例，建表并设置约束条件
mysql> create table test10 (name char(10) not null, age tinyint(3) unsigned default "27", class char(10) not null default "class2021", up_time time );
Query OK, 0 rows affected, 1 warning (0.21 sec)
mysql> desc test10;
+---------+---------------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+-----------+-------+
| name | char(10) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 27 | |
| class | char(10) | NO | | class2021 | |
| up_time | time | YES | | NULL | |
+---------+---------------------+------+-----+-----------+-------+
4 rows in set (0.01 sec)
加表记录
mysql> insert into test10(name,up_time) values("bob",curtime());
Query OK, 1 row affected (0.04 sec)
mysql> insert into test10(name,up_time) values("bob",time(152325));
Query OK, 1 row affected (0.10 sec)
mysql> select * from test10;
+------+------+-----------+----------+
| name | age | class | up_time |
+------+------+-----------+----------+
| bob | 27 | class2021 | 20:52:27 |
| bob | 27 | class2021 | 15:23:25 |
+------+------+-----------+----------+
2 rows in set (0.00 sec)
mysql> insert into test10 values("tom",null,"class2105",122345);
mysql> insert into test10 values("tom",22,"null",122345);
注：以下这两种情况，都不为空：
mysql> insert into test10(name) values("");
#这里直接双引号，为0个字符，不是空
mysql> insert into test10(name) values("null");
mysql> insert into test10(name) values(null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql> select * from test10;
+------+------+-----------+----------+
| name | age | class | up_time |
+------+------+-----------+----------+
| bob | 27 | class2021 | 20:52:27 |
| bob | 27 | class2021 | 15:23:25 |
| tom | NULL | class2105 | 12:23:45 |
| tom | 22 | null | 12:23:45 |
| | 27 | class2021 | NULL |
| null | 27 | class2021 | NULL |
+------+------+-----------+----------+
6 rows in set (0.00 sec)
设置default列为0
mysql> create table test11 (
-> name char(5) not null default "",
-> age tinyint(3) default 25
-> );
Query OK, 0 rows affected, 1 warning (0.13 sec)
mysql> desc test11;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name | char(5) | NO | | | |
| age | tinyint(3) | YES | | 25 | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into test11 values ('bob',null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test11(name) values ('bob');
mysql> select * from test11;
+------+------+
| name | age |
+------+------+
| bob | NULL |
| bob | 25 |
+------+------+
2 rows in set (0.00 sec)
修改表结构
例添加新字段，并设置位置。
不定义位置，默认在行尾。
定义到开头，加 first
定义到xx行后，为 after xx
mysql> alter table test11 add address char(20);
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test11;
+--------+------+---------+
| name | age | address |
+--------+------+---------+
| bob | NULL | NULL |
| bob | 25 | NULL |
| 小明 | 25 | NULL |
+--------+------+---------+
3 rows in set (0.00 sec)
mysql> alter table test11 add hobby char(20) after age, add sex enum('boy','girl') first;
# 不定义位置，默认在行尾。
# 定义到开头，加 first
# 定义到xx行后，为 after xx
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test11;
+---------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------+------+-----+---------+-------+
| sex | enum('boy','girl') | YES | | NULL | |
| name | char(5) | NO | | | |
| age | tinyint(3) | YES | | 25 | |
| hobby | char(20) | YES | | NULL | |
| address | char(20) | YES | | NULL | |
+---------+--------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
修改字段类型
mysql> alter table test11 modify hobby set('music','film','sleep','write');
Query OK, 3 rows affected (0.48 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc test11;
+---------+-------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------------------------------+------+-----+---------+-------+
| sex | enum('boy','girl') | YES | | NULL | |
| name | char(5) | NO | | | |
| age | tinyint(3) | YES | | 25 | |
| hobby | set('music','film','sleep','write') | YES | | NULL | |
| address | char(20) | YES | | NULL | |
+---------+-------------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
change 修改字段名
mysql> alter table test11 change hobby likes set('music','film','sleep','write');
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test11;
+---------+-------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------------------------------+------+-----+---------+-------+
| sex | enum('boy','girl') | YES | | NULL | |
| name | char(5) | NO | | | |
| age | tinyint(3) | YES | | 25 | |
| likes | set('music','film','sleep','write') | YES | | NULL | |
| address | char(20) | YES | | NULL | |
+---------+-------------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
注意：在修改字段名或字段类型时，如果此时相应列已有数据，会报错。！！！要修改的内容，不能与已有的值冲突！！！
mysql> insert into test11 values ('girl','lili',36,'music','shenzhen');
Query OK, 1 row affected (0.09 sec)
mysql> select * from test11;
+------+--------+------+-------+----------+
| sex | name | age | likes | address |
+------+--------+------+-------+----------+
| NULL | bob | NULL | NULL | NULL |
| NULL | bob | 25 | NULL | NULL |
| NULL | 小明 | 25 | NULL | NULL |
| girl | lili | 36 | music | shenzhen |
+------+--------+------+-------+----------+
4 rows in set (0.00 sec)
mysql> desc test11;
+---------+----------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------------------+------+-----+---------+-------+
| sex | enum('boy','girl') | YES | | NULL | |
| name | char(5) | NO | | | |
| age | tinyint(3) | YES | | 25 | |
| likes | set('music','film','sleep','write') | YES | | NULL | |
| address | enum('shenzhen','guangzhou','beijing') | YES | | NULL | |
+---------+----------------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 这里要改喜好字段，因为喜欢列已经有了music的值，所以更改的内容里，不能与其冲突。再改不能把music去掉。
mysql> alter table test11 modify likes set('write','read','game');
ERROR 1265 (01000): Data truncated for column 'likes' at row 4
mysql> alter table test11 modify likes set('write','read','game','music');
Query OK, 4 rows affected (0.27 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc test11;
+---------+----------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------------------+------+-----+---------+-------+
| sex | enum('boy','girl') | YES | | NULL | |
| name | char(5) | NO | | | |
| age | tinyint(3) | YES | | 25 | |
| likes | set('write','read','game','music') | YES | | NULL | |
| address | enum('shenzhen','guangzhou','beijing') | YES | | NULL | |
+---------+----------------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
drop 删字段（列）
mysql> desc test11;
+---------+----------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------------------+------+-----+---------+-------+
| sex | enum('boy','girl') | YES | | NULL | |
| name | char(5) | NO | | | |
| age | tinyint(3) | YES | | 25 | |
| likes | set('write','read','game','music') | YES | | NULL | |
| address | enum('shenzhen','guangzhou','beijing') | YES | | NULL | |
+---------+----------------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table test11 drop sex;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test11;
+---------+----------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------------------+------+-----+---------+-------+
| name | char(5) | NO | | | |
| age | tinyint(3) | YES | | 25 | |
| likes | set('write','read','game','music') | YES | | NULL | |
| address | enum('shenzhen','guangzhou','beijing') | YES | | NULL | |
+---------+----------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
修改表名
mysql> alter table test11 rename about_key;
Query OK, 0 rows affected (0.14 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| about_key |
| st1 |
| st2 |
MySQL的键值,key（标签）
普通索引（index）
主键（primary key）
外键(foreign key)
唯一索引（unique）
全文索引（full text）
.
查看标签：desc 库.表
查看标签详细信息： show index from 库.表
索引类型（排列方式）
二叉树：btree
B+tree
hash
普通索引 ( 索引：类似书的目录，让列的值排队)
索引：
优：加快查找速度
缺：占用硬盘空间，写数据的速度会慢
mysql> create index haha on test11(name);
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test11;
+---------+----------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------------------+------+-----+---------+-------+
| name | char(5) | NO | MUL | | |
| age | tinyint(3) | YES | | 25 | |
| likes | set('write','read','game','music') | YES | | NULL | |
| address | enum('shenzhen','guangzhou','beijing') | YES | | NULL | |
+---------+----------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
在创建表的同时，添加索引
mysql> create table test12 (name char(5),id char(20), index(name));
Query OK, 0 rows affected (0.21 sec)
mysql> desc test12;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(5) | YES | MUL | NULL | |
| id | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
查索引情况
mysql> create index hehe on test11(age);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test11 \G;
*************************** 1. row ***************************
Table: test11
Non_unique: 1
Key_name: haha
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: test11
Non_unique: 1
Key_name: hehe
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.01 sec)
删索引
mysql> drop index hehe on test11;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test11 \G;
*************************** 1. row ***************************
Table: test11
Non_unique: 1
Key_name: haha
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> desc test11;
+---------+----------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------------------+------+-----+---------+-------+
| name | char(5) | NO | MUL | | |
| age | tinyint(3) | YES | | 25 | |
| likes | set('write','read','game','music') | YES | | NULL | |
| address | enum('shenzhen','guangzhou','beijing') | YES | | NULL | |
+---------+----------------------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
主键
建表时，定义主键的方式
1、选项创建
mysql> create table test013(
-> name char(10) primary key,
-> age int(3)
-> );
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> desc test013;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | NO | PRI | NULL | |
| age | int(3) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2、命令创建
mysql> create table test014 (
-> name char(5),
-> age int(3),
-> sex enum('girl','boy'),
-> primary key(name)
-> );
Query OK, 0 rows affected, 1 warning (0.33 sec)
mysql> desc test014;
+-------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| name | char(5) | NO | PRI | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('girl','boy') | YES | | NULL | |
+-------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
修改、删除主键。！！注：取消主键后的列，‘null’列为NO，YES 要手动改！！
mysql> alter table test013 drop primary key;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test013;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | NO | | NULL | |
| age | int(3) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table test013 modify name char(10);
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test013;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
2、修改主键：
mysql> alter table test013 add primary key(age);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test013;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | int(3) | NO | PRI | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
如果字段下面的值，有相同的，则无法创建主键！！！
mysql> select * from test010;
+------+------+-----------+----------+
| name | age | class | up_time |
+------+------+-----------+----------+
| bob | 27 | class2021 | 20:52:27 |
| bob | 27 | class2021 | 15:23:25 |
| tom | NULL | class2105 | 12:23:45 |
| tom | 22 | null | 12:23:45 |
| | 27 | class2021 | NULL |
| null | 27 | class2021 | NULL |
+------+------+-----------+----------+
6 rows in set (0.03 sec)
mysql> desc test010;
+---------+---------------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+-----------+-------+
| name | char(10) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 27 | |
| class | char(10) | NO | | class2021 | |
| up_time | time | YES | | NULL | |
+---------+---------------------+------+-----+-----------+-------+
4 rows in set (0.00 sec)
mysql> alter table test010 add primary key(name);
ERROR 1062 (23000): Duplicate entry 'bob' for key 'PRIMARY'
mysql> alter table test010 add primary key(age);
ERROR 1138 (22004): Invalid use of NULL value
复合主键（表里的多列一起定义为主键，复合主键的值不允许同时一样！！）
mysql> create table test015 (ip varchar(15), port smallint, status enum('open','no'), primary key(ip,port) );
Query OK, 0 rows affected (0.15 sec)
mysql> desc test015;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | NULL | |
| port | smallint(6) | NO | PRI | NULL | |
| status | enum('open','no') | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into test015 values ('192.168.12.12',22,'no');
ERROR 1062 (23000): Duplicate entry '192.168.12.12-22' for key 'PRIMARY'
mysql> insert into test015 values ('192.168.12.12',43,'no');
Query OK, 1 row affected (0.06 sec)
mysql> insert into test015 values ('192.168.12.12',23,'open');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test015 values ('192.168.12.12',80,'no');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test015;
+---------------+------+--------+
| ip | port | status |
+---------------+------+--------+
| 192.168.12.12 | 22 | open |
| 192.168.12.12 | 23 | open |
| 192.168.12.12 | 43 | no |
| 192.168.12.12 | 80 | no |
+---------------+------+--------+
4 rows in set (0.00 sec)
修改、删除主键
mysql> alter table test015 drop primary key;
Query OK, 4 rows affected (0.21 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc test015;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| ip | varchar(15) | NO | | NULL | |
| port | smallint(6) | NO | | NULL | |
| status | enum('open','no') | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table test015 add primary key(ip,port);
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test015;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | NULL | |
| port | smallint(6) | NO | PRI | NULL | |
| status | enum('open','no') | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
主键与auto_increment（自动增长+1）连用。需要字段为主键，列的类型为数值类型！！！
mysql> create table test016 (
-> id int primary key auto_increment,
-> name char(5),age tinyint(3),class char(10));
Query OK, 0 rows affected, 1 warning (0.16 sec)
mysql> desc test016;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(5) | YES | | NULL | |
| age | tinyint(3) | YES | | NULL | |
| class | char(10) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> insert into test016(name,age,class) values
mysql> insert into test016(name,age,class) values
mysql> insert into test016(name,age,class) values )
mysql> select * from test016;
+----+------+------+-----------+
| id | name | age | class |
+----+------+------+-----------+
| 1 | bob | 26 | class2305 |
| 2 | tom | 26 | class2305 |
| 3 | lisi | 35 | class2305 |
+----+------+------+-----------+
3 rows in set (0.00 sec)
2、自定义编号值
mysql> insert into test016 values (5,'haha',35,'class2305');
Query OK, 1 row affected (0.06 sec)
mysql> insert into test016 values (null,'haha',35,'class2305');
Query OK, 1 row affected (0.02 sec)
# 赋值空，会继续按照自+1进行
mysql> select * from test016;
+----+------+------+-----------+
| id | name | age | class |
+----+------+------+-----------+
| 1 | bob | 26 | class2305 |
| 2 | tom | 26 | class2305 |
| 3 | lisi | 35 | class2305 |
| 5 | haha | 35 | class2305 |
| 6 | haha | 35 | class2305 |
+----+------+------+-----------+
5 rows in set (0.01 sec)
按唯一编号，去查找信息
mysql> select * from test016 where id=3;
+----+------+------+-----------+
| id | name | age | class |
+----+------+------+-----------+
| 3 | lisi | 35 | class2305 |
+----+------+------+-----------+
1 row in set (0.01 sec)
mysql> select * from test016 where id>=3;
+----+------+------+-----------+
| id | name | age | class |
+----+------+------+-----------+
| 3 | lisi | 35 | class2305 |
| 5 | haha | 35 | class2305 |
| 6 | haha | 35 | class2305 |
+----+------+------+-----------+
3 rows in set (0.06 sec)
有自增长的主键，要先删掉自增长才能删主键。2、按上述表状态就算把表情空，再添加记录，编号也会按清空前的最后一位+1。3、要想重新排序，删字段再重建。
mysql> delete from test016;
Query OK, 5 rows affected (0.07 sec)
mysql> insert into test016 values (null,'haha',35,'class2305');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test016 ;
+----+------+------+-----------+
| id | name | age | class |
+----+------+------+-----------+
| 7 | haha | 35 | class2305 |
+----+------+------+-----------+
1 row in set (0.00 sec)
mysql> alter table test016 drop id;
Query OK, 1 row affected (0.32 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from test016 ;
+------+------+-----------+
| name | age | class |
+------+------+-----------+
| haha | 35 | class2305 |
+------+------+-----------+
1 row in set (0.00 sec)
mysql> alter table test016 add
-> id int primary key auto_increment first;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into test016 values (null,'haha',35,'class2305');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test016 ;
+----+------+------+-----------+
| id | name | age | class |
+----+------+------+-----------+
| 1 | haha | 35 | class2305 |
| 2 | haha | 35 | class2305 |
+----+------+------+-----------+
2 rows in set (0.00 sec)
外键
建员工和工资表，指定外键工资列的id与员工表的对应，同步。
mysql> create table test017 (
-> name char(10),
-> id int(10) primary key auto_increment)
-> engine=innodb;
Query OK, 0 rows affected, 1 warning (0.22 sec)
mysql> desc test017;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| name | char(10) | YES | | NULL | |
| id | int(10) | NO | PRI | NULL | auto_increment |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into test017(name) values ('bob'),('tom'),('lisi');
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test017;
+------+----+
| name | id |
+------+----+
| bob | 1 |
| tom | 2 |
| lisi | 3 |
+------+----+
3 rows in set (0.00 sec)
1、指定外键：
mysql> alter table test017 rename test017_yg;
Query OK, 0 rows affected (0.05 sec)
mysql> create table test017_gz (
-> gz_id int,
-> pay float(8,2),
-> foreign key(gz_id) references test017_yg(id) on update cascade on delete cascade
-> )engine=innodb;
Query OK, 0 rows affected, 1 warning (0.26 sec)
2、查看结构：
mysql> desc test017_gz;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11) | YES | MUL | NULL | |
| pay | float(8,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc test017_yg;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| name | char(10) | YES | | NULL | |
| id | int(10) | NO | PRI | NULL | auto_increment |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
验证是否有外键：
mysql> show create table test017_gz\G;
*************************** 1. row ***************************
Table: test017_gz
Create Table: CREATE TABLE `test017_gz` (
`gz_id` int(11) DEFAULT NULL,
`pay` float(8,2) DEFAULT NULL,
KEY `gz_id` (`gz_id`),
CONSTRAINT `test017_gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `test017_yg` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.09 sec)
ERROR:
No query specified
添加不在条件范围内的，会报错！！
mysql> select * from test017_yg;
+------+----+
| name | id |
+------+----+
| bob | 1 |
| tom | 2 |
| lisi | 3 |
+------+----+
3 rows in set (0.00 sec)
mysql> insert into test017_gz values (4,15566);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`test017_gz`, CONSTRAINT `test017_gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `test017_yg` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>
验证，同步删除的效果
mysql> delete from test017_yg where id=2;
Query OK, 1 row affected (0.03 sec)
mysql> select * from test017_yg;
+------+----+
| name | id |
+------+----+
| bob | 1 |
| lisi | 3 |
+------+----+
2 rows in set (0.00 sec)
mysql> select * from test017_gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| NULL | 11863.55 |
| NULL | 17580.85 |
| NULL | 16455.66 |
| 1 | 15600.00 |
| 3 | 15566.00 |
+-------+----------+
5 rows in set (0.00 sec)
修改，同步效果
mysql> update test017_yg set id=7 where name='bob';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test017_yg;
+------+----+
| name | id |
+------+----+
| lisi | 3 |
| bob | 7 |
+------+----+
2 rows in set (0.00 sec)
mysql> select * from test017_gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| NULL | 11863.55 |
| NULL | 17580.85 |
| NULL | 16455.66 |
| 7 | 15600.00 |
| 3 | 15566.00 |
+-------+----------+
5 rows in set (0.00 sec)
删外键：
mysql> alter table test017_gz drop foreign key test017_gz_ibfk_1;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test017_gz;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test017_gz | CREATE TABLE `test017_gz` (
`gz_id` int(11) NOT NULL,
`pay` float(8,2) DEFAULT NULL,
PRIMARY KEY (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into test017_gz values(9,45222);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test017_gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 3 | 45222.00 |
| 7 | 45222.00 |
| 9 | 45222.00 |
+-------+----------+
3 rows in set (0.00 sec)
mysql> select * from test017_yg;
+------+----+
| name | id |
+------+----+
| lisi | 3 |
| bob | 7 |
+------+----+
2 rows in set (0.00 sec)
扩展，外键可存空值，这于表的设计思路不符，需要进行控制。把目标外键设置为主键，可避免为空和重复。
mysql> delete from test017_gz;
Query OK, 5 rows affected (0.05 sec)
mysql> desc test017_gz;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11) | YES | MUL | NULL | |
| pay | float(8,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table test017_gz add primary key(gz_id);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test017_gz;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test017_gz | CREATE TABLE `test017_gz` (
`gz_id` int(11) NOT NULL,
`pay` float(8,2) DEFAULT NULL,
PRIMARY KEY (`gz_id`),
CONSTRAINT `test017_gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `test017_yg` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
验证：
mysql> insert into test017_gz values(7,45222);
Query OK, 1 row affected (0.08 sec)
mysql> insert into test017_gz values(null,45222);
ERROR 1048 (23000): Column 'gz_id' cannot be null
mysql> insert into test017_gz values(3,45222);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test017_gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 3 | 45222.00 |
| 7 | 45222.00 |
+-------+----------+
2 rows in set (0.00 sec)
742
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
