mysql表结构管理_Mysql数据库（三）Mysql表结构管理

最新推荐文章于 2022-12-09 03:01:21 发布

夜看满天繁星

最新推荐文章于 2022-12-09 03:01:21 发布

阅读量265

点赞数

文章标签： mysql表结构管理

本文链接：https://blog.csdn.net/weixin_36057373/article/details/113216917

版权

一、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;

+----------+------------------+------+-----+---------+-------+

+----------+------------------+------+-----+---------+-------+

| del | tinyint(1) | YES | | 0 | |

| id | int(11) | NO | | NULL | |

+----------+------------------+------+-----+---------+-------+

11 rows in set (0.00 sec)

mysql> DESC tb_bookinfobak;

+----------+------------------+------+-----+---------+-------+

+----------+------------------+------+-----+---------+-------+

| 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;

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

| 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;

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

| 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;

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

| 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;

+----------+------------------+------+-----+---------+-------+

+----------+------------------+------+-----+---------+-------+

| 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;

+------------+------------------+------+-----+---------+-------+

+------------+------------------+------+-----+---------+-------+

| del | tinyint(1) | YES | | 0 | |

| id | int(11) | 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;

+----------+------------------+------+-----+---------+-------+

+----------+------------------+------+-----+---------+-------+

| 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)

夜看满天繁星

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql表结构管理_Mysql数据库（三）Mysql表结构管理

一、MySQL数据类型1.数字类型(1)整数数据类型包括TINYINT/BIT/BOOL/SMALLINT/MEDIUMINT/INT/BIGINT(2)浮点数据类型包括FLOAT/DOUBLE/DECIMAL原则： 1.选择最小的可用类型，如果值永远不超过127，则使用TINYINT比INT强。2.对于完全都是数字的，可以选择整数类型。3.浮点类型用于可能具有小数部分的数。如货物单价、网上购物交...
复制链接

扫一扫

mysql表结构管理_Mysql数据库（三）Mysql表结构管理

“相关推荐”对你有帮助么？