MySQL（二）--数据表的基本操作

在数据库中，数据表是数据库中最重要的、最基本的操作对象，是数据存储的基本单位。

1 创建数据表

首先USE database_name;
USE test_db;
创建tb_emp1表：

CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
查看表：
SHOW TABLES;

1.1 使用主键约束

主键：Primary Key Constraint）
又称主码，是表中一列或多列的组合；
主键约束要求主键列的数据唯一，并且不允许为空，能够唯一标识表中的一条记录；
主键分两种：单字段主键和多字段联合主键。

1.1.1 单字段主键

主键由一个字段组成。
(1) 在定义列的同时指定主键
CREATE TABLE tb_emp2
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
(2) 在定义完所有列之后指定主键
[CONSTRAINT <约束名>] PRIMARY KEY[字段名]
CREATE TABLE tb_emp3
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);

1.1.2 多字段联合主键

主键由多个字段联合组成。
CREATE TABLE tb_emp4
(
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(name,deptId)
);

1.2 使用外键约束

外键：FOREIGN KEY
外键用来在两个表之间建立连接，它可以是一列或者多列；
一个表可以有一个或多个外键；
一个表的外键可以为空值，若不为空，则每一个外键值必须等于另一个表中主键的某个值；
它可以不是本表的主键，但对应另外一个表的主键；
外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行；
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表为主表；
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表为主表。

创建相关联的主表
CREATE TABLE tb_dept1
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) NOT NULL,
location VARCHAR(50)
);
创建相关联的从表
CREATE TABLE tb_emp5
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

1.3 使用非空约束

非空：Not Null Constraint
CREATE TABLE tb_emp6
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) NOT NULL,
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp-dept2 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

1.4 使用唯一约束

唯一性约束：Unique Constraint
要求该列唯一，且允许为空，但只能有一个空值；
(1) 在定义完列之后直接指定唯一约束
CREATE TABLE tb_dept2
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) UNIQUE,
location VARCHAR(50)
);
(2) 在定义完所有列之后指定唯一约束
CREATE TABLE tb_dept3
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
location VARCHAR(50),
CONSTRAINT STH UNIQUE(name)
);

1.5 使用默认约束

默认约束：Default Constraint
CREATE TABLE tb_emp7
(
id INT(11) PRIMARY KEY,
name VARCHAR(2) NOT NULL,
deptId INT(11) DEFAULT 1111,
salary FLOAT,
CONSTRAINT fk_emp_dept3 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

1.6 设置表的属性值自动增加

在MySQL中AUTO_INCREMENT的初始值为1，字段值自动加1；
一个表只能有一个字段使用AUTO_INCREMENT约束，且必须为主键的一部分；
CREATE TABLE tb_emp8
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept4 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

2 查看数据表的结构

2.1 查看表基本结构语句

DESCRIBE table_name;
或者简写为：
DESC table_name;
可以查看表的字段信息，其中包括:字段名、字段数据类型、是否为主键、是否有默认值等。

2.2 查看表详细结构语句

SHOW CREATE TABLE table_name\G;#如果不加参数“\G”，显示的结果可能比较混乱。

3 修改数据表

3.1 修改表名

ALTER TABLE <旧表名> RENAME [T0] <新表名>;
ALTER TABLE tb_dept3 RENAME tb_deptment3;

3.2 修改字段的数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

3.3 修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);

3.4 添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST |AFTER 已存在的字段名];
(1) 添加无完整性约束条件的字段
ALTER TABLE tb_dept1 ADD column1 INT(10);
(2) 添加有完整性约束条件的字段
ALTER TABLE tb_dept1 ADD column2 VARCHAR(12) not null;
(3) 在表的第一列添加一个字段
ALTER TABLE tb_dept1 ADD column3 VARCHAR(12) not null FIRST;
(4) 在表的指定列添加一个字段
ALTER TABLE tb_dept1 ADD column4 VARCHAR(12) not null AFTER name;

3.5 删除字段

ALTER TABLE <表名> DROP <字段名>;
ALTER TABLE tb_dept1 DROP column1;

3.6 修改字段的排列位置

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
(1) 修改字段为表的第一个字段
ALTER TABLE tb_dept1 MODIFY column2 VARCHAR(22) FIRST;
(2) 修改字段到表的指定列之后
ALTER TABLE tb_dept1 MODIFY column2 VARCHAR(22) AFTER loc;

3.7 更改表的存储引擎

ALTER TABLE <表名> ENGINE=<更改后的存储引擎>;

ALTER TABLE tb_deptment3 ENGINE=MyISAM;

3.8 删除表的外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp_dept1;

4 删除数据表

4.1 删除没有被关联的表

DROP TABLE [IF EXISTS] 表1, 表2, …表n;

DROP TABLE IF EXISTS tb_dept2;

4.2 删除被其他表关联的主表

数据表之间存在外键关联时，直接删除父表，结果会显示失败，因为这样会破坏表的完整性。
这时应该先取消表之间的关联关系，再删除父表。