MySQL系列04：数据表

数据表

数据表是数据库最重要的组成部分之一，是其他对象的基础。

打开数据表 USE

USE 打开数据表

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type, 
...
);

column_name 列名称
data_type 数据类型

例如：

CREATE TABLE tb1(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
);

创建一个数据表，里面有3个数据列，分别是 用户名、年龄和薪水；
其中username 选择的数据类型为VARCHAR(20)，满足中国复姓的需要；
age 使用的数据类型为 无符号（UNSIGNED）的TINYINT型（小于255的正整数）；
salary 使用的是带有2位小数的浮点型，也是无符号的正数。

查看数据表

SHOW TABLES [FROM db_name]
[LIKE 'pattern' |WHERE expr]

SHOW TABLES; 就是查看当前数据库下的数据表；
SHOW TABLES FROM mysql; 就是查看mysql数据库下的所有数据表，这个命令并不会改变数据库的位置；

查看数据表的列

SHOW COLUMNS FORM tbl_name

查看tbl_name表下的数据列

插入记录

INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...)

如果省略“[ ]”的内容，即需要为表中所有的数据列赋值；
如：

INSERT tb1 VALUES('tom',25,4000.00);
INSERT tb1(username,salary) VALUES('john',5000.69);

查找记录

SELECT expr,... FROM tbl_name

例如：

SELECT * FROM tb1;

其中的“*” 可以查找全部的记录，也是字段的过滤。

空值与非空

NULL 和 NOT NULL
例如：

CREATE TABLE tb2(
uername VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);

INSERT tb2 VALUES('TOM',NULL);
INSERT tb2 VALUES('NULL',NULL);

自动编号

AUTO_INCREMENT
必须与主键组合使用；
默认情况下，起始值为1，每次的增量为1.
使用自动编号作为“id”，必须在设计表的时候，把自动编号设置为主键。但是“主键”不一定必须使用AUTO_INCREMENT。

主键 PRIMARY KEY

• 每张数据表只能存在一个主键；
• 主键保证记录的唯一性；
• 主键自动为 NOT NULL。

例如：

CREATE TABLE tb4(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL
);

查看tb4 可以看到id设置为了 PRIMARY KEY主键，默认也必须为NOT NULL，并且使用 AUTO_INCREMENT 自动从“1”开始编号

其中‘smallint(5)’ 后面跟了一个（5），它的意思是这个数字型只有5位，但是它依然不能超过‘65535’ ，超过这个值，赋值是会报错的。

设置了id为主键，在为一条数据赋值的时候，id字段可以不赋值，自动从“1”开始递增，但是如果你为其赋值，则id不可重复，并且，在你不为其赋值后，id默认从最大位数再次递增。例如：

INSERT INTO tb2 VALUES(1,'user1',NULL,20000.00,'男');
INSERT INTO tb2(username,age,salary,sex) VALUES('user2',NULL,20000.00,'男');
INSERT INTO tb2 VALUES(8,'user3',NULL,20000.00,'男');
INSERT INTO tb2(username,age,salary,sex) VALUES('user4',NULL,20000.00,'男');

执行上述命令后，user1、2、3、4的“id”分别为“1、2、8、9”。

唯一约束 UNIQUE KEY

• 唯一约束可以保证记录的唯一性
• 唯一约束的字段可以为空值（NULL）
• 每张数据表可以存在多个唯一约束

可以看出UNIQUE KEY 和 RPIMARY KEY存在很多不同，它们在创建索引的时候也存在着不同，同时，UNIQUE KEY 可以为空值也可以在一张表中存在多个唯一约束。

举例：

CREATE TABLE tb4(
id SMALLINT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NULL UNIQUE KEY,
email VARCHAR(255) NOT NULL UNIQUE KEY,
age TINYINT UNSIGNED );

默认约束 DEFAULT

• 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
  例如：

CREATE TABLE tb6(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('男','女','保密') DEFAULT'保密'
);

此时，“sex”字段 赋值需在“男”“女”“保密”3个其中之一，但是默认的为“保密”，也就是如果不为sex 赋值的话，则为“保密”；当然，如果咱们这样定义sex字段的话，sex字段是可以赋空值的（NULL）。
当然我们也可以使用“DEFAULT”为其赋值，例如：

INSERT tb6(username) VALUES('lihua');
INSERT INTO tb6 VALUES(DEFAULT,'zhang',DEFAULT);
INSERT tb6(username,sex) VALUES('wang',NULL);

如此赋值出来，“lihua”和“zhang”的sex 都为“保密”，“wang”的sex值为NULL。

删除数据表

DROP TABLE [IF EXISTS] tb_name;
当然这个命令可以批量的删除数据表，例如：
DROP TABLE IF EXISTS tb1,tb2,tb3,...tbN;
如果你需要删除一个数据库下的多个甚至全部数据表，你可以可以这样操作，输入：

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'db_name';
/*db_name 是需要删除的表所在的数据库名称*/

在运行的结果集中可以将结果再“shift”选中，复制粘贴再运行一次，即可删除所有数据表。