Mysql数据库（三）Mysql表结构管理
一、MySQL数据类型
1.数字类型
（1）整数数据类型包括TINYINT/BIT/BOOL/SMALLINT/MEDIUMINT/INT/BIGINT
（2）浮点数据类型包括FLOAT/DOUBLE/DECIMAL
原则： 1.选择最小的可用类型，如果值永远不超过127，则使用TINYINT比INT强。
2.对于完全都是数字的，可以选择整数类型。
3.浮点类型用于可能具有小数部分的数。如货物单价、网上购物交付金额等。
2.字符串类型
（1）普通的文本字符串类型包括CHAR（取值为1~255）和VARCHAR（变长的字符串）
（2）TEXT和BLOB类型。它们的大小可以改变，TEXT类型适合存储长文本，而BLOB类型适合存储二进制数据，支持任何数据，例如文本、声音和图像等。
（3）特殊类型SET和ENUM。
原则： 1.从速度方面考虑，要选择固定的列，可以使用CHAR类型。
2.要节省空间，使用动态地列，可以使用VARCHAR类型。
3.要将列中的内容仙之子一种选择，可以使用ENUM类型。
4.允许在一个列中有多于一个的条目，可以使用SET类型。
5.如果要搜索的内容不区分大小写，可以使用TEXT类型。
6.如果要搜索的内容区分大小写，可以使用BLOB类型。
3.日期和时间数据类型
（1）DATE：YYYY-MM-DD
（2）TIME：HH:MM:SS
（3）DATETIME：YYYY-MM-DD HH:MM:SS
（4）TIMESTAMP：时间标签，在处理报告时使用的显示格式取决于M的值。
（5）YEAR：年份可指定两位数字和四位数字的格式。
二、创建表
mysql> use db_library;
Database changed
mysql> CREATE TABLE tb_bookinfo(
-> barcode varchar(30),
-> bookname varchar(70),
-> typeid int(10) unsigned,
-> author varchar(30),
-> ISBN varchar(20),
-> price float(8,2),
-> page int(10) unsigned,
-> bookcase int(10) unsigned,
-> inTime date,
-> del tinyint(1) DEFAULT'0',
-> id int(11) NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
1.设置默认的存储引擎
mysql> CREATE TABLE tb_booktype(
-> id int(10) unsigned NOT NULL,
-> typename varchar(30),
-> days int(10) unsigned
-> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
2.设置自增类型字段的同时，最好将其设置为主键（注意反引号和单引号的区别，反引号是Esc下面那个）
mysql> CREATE TABLE tb_booktype1(
-> id int(10) unsigned NOT NULL AUTO_INCREMENT,
-> typename varchar(30),
-> days int(10) unsigned,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.03 sec)
3.设置字符集
mysql> CREATE TABLE tb_booktype2(
-> id int(10) unsigned NOT NULL AUTO_INCREMENT,
-> typename varchar(30),
-> days int(10) unsigned,
-> PRIMARY KEY (`id`)
-> )DEFAULT CHARSET=GBK;
Query OK, 0 rows affected (0.02 sec)
4.复制表结构，实现在数据库db_library中创建一份数据表tb_bookinfo的副本tb_bookinfobak。
（1）向数据表tb_bookinfo中插入一条数据
mysql> INSERT INTO tb_bookinfo VALUES ('17120107','Java King','3','LianJiang','115',49.80,350,1,'2018-04-17',0,1);
Query OK, 1 row affected (0.00 sec)
（2）创建一份数据表tb_bookinfo的副本tb_bookinfobak
mysql> CREATE TABLE tb_bookinfobak LIKE tb_bookinfo; Query OK, 0 rows affected (0.02 sec)
（3）查看两个表的结构
mysql> DESC tb_bookinfo; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | barcode | varchar(30) | YES | | NULL | | | bookname | varchar(70) | YES | | NULL | | | typeid | int(10) unsigned | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(20) | YES | | NULL | | | price | float(8,2) | YES | | NULL | | | page | int(10) unsigned | YES | | NULL | | | bookcase | int(10) unsigned | YES | | NULL | | | inTime | date | YES | | NULL | | | del | tinyint(1) | YES | | 0 | | | id | int(11) | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 11 rows in set (0.00 sec) mysql> DESC tb_bookinfobak; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | barcode | varchar(30) | YES | | NULL | | | bookname | varchar(70) | YES | | NULL | | | typeid | int(10) unsigned | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(20) | YES | | NULL | | | price | float(8,2) | YES | | NULL | | | page | int(10) unsigned | YES | | NULL | | | bookcase | int(10) unsigned | YES | | NULL | | | inTime | date | YES | | NULL | | | del | tinyint(1) | YES | | 0 | | | id | int(11) | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 11 rows in set (0.00 sec)
（4）分别查看两个表的内容，发现复制的表中没有复制表中的数据
mysql> SELECT * FROM tb_bookinfobak; Empty set (0.00 sec) mysql> SELECT * FROM tb_bookinfo; +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | barcode | bookname | typeid | author | ISBN | price | page | bookcase | inTime | del | id | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | 17120107 | Java King | 3 | LianJiang | 115 | 49.80 | 350 | 1 | 2018-04-17 | 0 | 1 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ 1 row in set (0.00 sec)
（5）如果在复制数据表时，想要同时复制其中的数据的话，需要这样实现。但是，新复制出来的数据表并不包括原表中设置的主键、自动编号等内容。
mysql> CREATE TABLE tb_bookinfobak1 AS SELECT * FROM tb_bookinfo; Query OK, 1 row affected (0.14 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb_bookinfobak1; +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | barcode | bookname | typeid | author | ISBN | price | page | bookcase | inTime | del | id | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | 17120107 | Java King | 3 | LianJiang | 115 | 49.80 | 350 | 1 | 2018-04-17 | 0 | 1 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ 1 row in set (0.00 sec)
（6）如果要复制一下表结构和数据都完全一样的数据表，那么需要应用下面两句语句实现。
mysql> CREATE TABLE tb_bookinfobak2 LIKE tb_bookinfo; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO tb_bookinfobak2 SELECT * FROM tb_bookinfo; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb_bookinfobak2; +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | barcode | bookname | typeid | author | ISBN | price | page | bookcase | inTime | del | id | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ | 17120107 | Java King | 3 | LianJiang | 115 | 49.80 | 350 | 1 | 2018-04-17 | 0 | 1 | +----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+ 1 row in set (0.00 sec)
三、修改表结构
1.在数据表tb_bookinfobak中添加一个translator字段，并且将inTime的类型由date改为DATETIME(6)
mysql> desc tb_bookinfobak;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| barcode | varchar(30) | YES | | NULL | |
| bookname | varchar(70) | YES | | NULL | |
| typeid | int(10) unsigned | YES | | NULL | |
| author | varchar(30) | YES | | NULL | |
| ISBN | varchar(20) | YES | | NULL | |
| price | float(8,2) | YES | | NULL | |
| page | int(10) unsigned | YES | | NULL | |
| bookcase | int(10) unsigned | YES | | NULL | |
| inTime | date | YES | | NULL | |
| del | tinyint(1) | YES | | 0 | |
| id | int(11) | NO | | NULL | |
+----------+------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
mysql> alter table tb_bookinfobak add translator varchar(30) not null,
-> modify inTime DATETIME(6);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tb_bookinfobak;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| barcode | varchar(30) | YES | | NULL | |
| bookname | varchar(70) | YES | | NULL | |
| typeid | int(10) unsigned | YES | | NULL | |
| author | varchar(30) | YES | | NULL | |
| ISBN | varchar(20) | YES | | NULL | |
| price | float(8,2) | YES | | NULL | |
| page | int(10) unsigned | YES | | NULL | |
| bookcase | int(10) unsigned | YES | | NULL | |
| inTime | datetime(6) | YES | | NULL | |
| del | tinyint(1) | YES | | 0 | |
| id | int(11) | NO | | NULL | |
| translator | varchar(30) | NO | | NULL | |
+------------+------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
2.修改约束条件
（1）添加主键约束格式为Alter TABLE 数据表名 ADD CONSTRAINT 约束名 约束类型 （字段名）
其中约束类型包括：PRIMARY KEY（主键约束）、DEFAULT（默认值约束）、UNIQUE KEY（唯一约束）、NOT NULL（非空约束）、FOREIGN KEY（外键约束）。
mysql> Alter TABLE tb_bookinfo ADD CONSTRAINT mrprimary PRIMARY KEY(id); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_bookinfo; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | barcode | varchar(30) | YES | | NULL | | | bookname | varchar(70) | YES | | NULL | | | typeid | int(10) unsigned | YES | | NULL | | | author | varchar(30) | YES | | NULL | | | ISBN | varchar(20) | YES | | NULL | | | price | float(8,2) | YES | | NULL | | | page | int(10) unsigned | YES | | NULL | | | bookcase | int(10) unsigned | YES | | NULL | | | inTime | date | YES | | NULL | | | del | tinyint(1) | YES | | 0 | | | id | int(11) | NO | PRI | NULL | | +----------+------------------+------+-----+---------+-------+ 11 rows in set (0.00 sec)
（2）删除约束条件
删除主键约束：ALTER TABLE 表名 DROP PRIMARY KEY;
删除外键约束：ALTER TABLE 表名 DROP FOREIGN 约束名；
删除唯一性约束：ALTER TABLE 表名 DROP INDEX 唯一索引名；
（3）修改表的其他选项
修改表的存储引擎：ALTER TABLE 表名 ENGINE=新的存储引擎类型
修改表的字符集：ALTER TABLE 表名 DEFAULT CHARSET=新的字符集
修改表的自增类型字段的初始值：ALTER TABLE 表名 AUTO_INCREMENT=新的初始值
（4）修改表名
重命名：RENAME TABLE 数据表名1 To 数据表名2
四、删除表
DROP TABLE 数据表名；
DROP TABLE IF EXISTS 数据表名；
五、设置索引
1.索引是一种将数据库中单列或者多列的值进行排序的结构。在MySQL中，索引由数据表中的一列或多列组合而成，创建索引的目的是为了优化数据库的查询速度。
通过索引查询数据，不但可以提高查询速度，也可以降低服务器的负载。创建索引后，用户查询数据时，系统可以不必遍历数据表中的所有记录，而是查询索引列。这样就可以有效地提高数据库系统的整体性能。这和我们通过图书的目录查找想要阅读的章节内容一样，十分方便。
但是，创建索引和维护需要耗费时间同时还需要占用物理空间，给数据的维护造成很多麻烦。向有索引的表中插入记录时，数据库系统会按照索引进行排序。所以，用户可以将索引删除后再插入数据，当数据插入操作完成后，用户可以重新创建索引。
常见MySQL索引包括：普通、唯一性、全文、单列、多列、空间。
2.创建索引
（1）在建立数据表时创建索引，并且查看表结构。
mysql> CREATE TABLE tb_score(
-> id int(11) auto_increment primary key not null,
-> name varchar(50) not null,
-> math int(5) not null,
-> english int(5) not null,
-> chinese int(5) not null,
-> index(id)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE tb_score;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_score | CREATE TABLE `tb_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`math` int(5) NOT NULL,
`english` int(5) NOT NULL,
`chinese` int(5) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
（2）在已建立的数据表中创建索引
mysql> CREATE INDEX idx_name ON tb_bookinfo(bookname); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE tb_bookinfo; +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_bookinfo | CREATE TABLE `tb_bookinfo` ( `barcode` varchar(30) DEFAULT NULL, `bookname` varchar(70) DEFAULT NULL, `typeid` int(10) unsigned DEFAULT NULL, `author` varchar(30) DEFAULT NULL, `ISBN` varchar(20) DEFAULT NULL, `price` float(8,2) DEFAULT NULL, `page` int(10) unsigned DEFAULT NULL, `bookcase` int(10) unsigned DEFAULT NULL, `inTime` date DEFAULT NULL, `del` tinyint(1) DEFAULT '0', `id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`bookname`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3.删除索引
mysql> DROP INDEX idx_name ON tb_bookinfo; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE tb_bookinfo; +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_bookinfo | CREATE TABLE `tb_bookinfo` ( `barcode` varchar(30) DEFAULT NULL, `bookname` varchar(70) DEFAULT NULL, `typeid` int(10) unsigned DEFAULT NULL, `author` varchar(30) DEFAULT NULL, `ISBN` varchar(20) DEFAULT NULL, `price` float(8,2) DEFAULT NULL, `page` int(10) unsigned DEFAULT NULL, `bookcase` int(10) unsigned DEFAULT NULL, `inTime` date DEFAULT NULL, `del` tinyint(1) DEFAULT '0', `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
六、定义约束
1.定义主键约束
主键可以使表中的某一列，也可以是表中多个列所构成的一个组合。其中，由多个列组合而成的主键也成为复合主键。
原则：每个表只能定义一个主键。
唯一性原则。主键的值必须能够唯一标识表中每一行记录，切不能为NULL。
最小化规则。复合主键不能包含不必要的多余列。
一个列名在复合主键的列表中只能出现一次。
（1）作为列的完成性约束：在表的某个列的属性定义时，加上关键字PRIMARY KEY
mysql> CREATE TABLE tb_manager(
-> id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name varchar(30),
-> PWD varchar(30)
-> );
Query OK, 0 rows affected (0.02 sec)
（2）作为表的完整性约束：在表的所有列的属性定以后，加上PRIMARY KRY（insex_col_name,...）
mysql> create table tb_student(
-> id int(10) auto_increment,
-> name varchar(30) not null,
-> sex varchar(2),
-> classid int not null,
-> birthday date,
-> PRIMARY KEY(id,classid)
-> );
Query OK, 0 rows affected (0.02 sec)
2.定义候选键约束
如果一个属性集能唯一标识元组，且又不含有多余的属性，那么这个属性集称为关系的候选键。候选键的值必须是唯一的，且不能为空。其实现方法与主键约束类似，也是可作为列的完整性约束或者表的完整性约束。候选键和主键的区别有：一个表只能创建一个主键，但可以定义若干个候选键；定义主键约束时，系统会自动创建PRIMARY KEY索引，而定义候选键约束时，系统会自动创建UNIQUE索引。
mysql> CREATE TABLE tb_managerbak(
-> id int(10) unsigned NOT NULL,
-> bookname varchar(70) UNIQUE
-> );
Query OK, 0 rows affected (0.02 sec)
3.定义非空约束
通过CREATE TABLE或ALTER TABLE语句中，某个列定义后面加上关键字NOT NULL来定义。
4.定义CHECK约束
（1）对列实施CHECK约束，限制其age字段的值只能是7~18（不包括18）
mysql> CREATE TABLE tb_managerbak1(
-> id int(10) unsigned NOT NULL,
-> age int not null CHECK(age>6 and age<18)
-> );
Query OK, 0 rows affected (0.03 sec)
（2）对表实施CHECK约束，限制其typeid字段的值只能是tb_booktypr表中id字段的某一个id值
mysql> CREATE TABLE tb_managerbak2(
-> id int(10) unsigned NOT NULL,
-> typeid int(10) unsigned,
-> CHECK(typeid IN (SELECT id FROM tb_booktype))
-> );
Query OK, 0 rows affected (0.03 sec)
儿女情长什么的，最影响我们闯荡江湖了。
