四、数据表的基本操作_数据准备阶段用于两表之间的操作有哪些

4.1 创建数据表

在创建完数据库之后，接下来的工作就是创建数据表。所谓创建数据 表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数 据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整 性和域完整性等）约束的过程。 数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库 名>”指定操作是在哪个数据库中进行，如果没有选择数据库，就会抛出“No database selected”的错误。

创建数据表的语句为CREATE TABLE，语法规则如下：

CREATE TABLE TAB_NAME(
	字段1 类型 约束 默认值,
	字段2 类型 约束 默认值,
	字段n 类型 约束 默认值
)

使用CREATE TABLE创建表时，必须指定以下信息： （1） 要创建的表的名称，不区分大小写，不能使用SQL语言中的关键 字，如DROP、ALTER、INSERT 等。 （2） 数据表中每一列（字段）的名称和数据类型，如果创建多列，就要用逗号隔开。 案例：创建员工表：employee

创建表的sql语句如下：

CREATE TABLE employee(
	emp_id INT,
	emp_name VARCHAR(50),
	emp_sex CHAR(2),
	emp_salary FLOAT,
	emp_phone VARCHAR(50),
	emp_birth DATE,
	dept_id INT
);

 

此时可以通过以下命令查看该库下的表

SHOW TABLES;

 

通过上图可以看到表中还有一些默认值，主键，非空等。这些都是属于表的约束。

4.2 表约束

1.数据完整性

数据的完整性是指数据的可靠性和准确性 1.实体完整性：实体的完整性强制表的标识符列或主键的完整性(通过索引,唯一约束,主键约束或标识列属性). 2.域完整性：限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值定义,非空约束和规则). 3.引用完整性：在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完整性确保键值在所有表中一致.这样的一致辞性要求不能引用不存在的值.如果一个键值更改了,那么在整个数据库中,对该键值的引用要进行一致的更改. 4.自定义完整性：用户自己定义的业务规则. 四种完整性约束：

实体完整性：唯一约束、主键约束、标识列

域完完整性：限制数据类型、外键约束、默认值、非空约束

引用完整性：外键 自定义完整性：过程，触发器等

2.约束 约束是在表上强制执行的一些数据校验规则，被插入、修改或删除的数据必须符合在相关字段上设置的这些约束条件。 五类完整性约束： NOT NULL 非空 UNIQUE 唯一 PRIMARY KEY 主键 FOREIGN KEY 外键 CHECKED 检查(mysql不支持的)

4.2.1 主键约束

主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一地标 识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以 加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关 系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。

1.单子段主键

主键由一个字段组成，SQL语句格式分为以下两种情况。 1)定义列的同时指定主键，语法如下

字段名 字段类型 primary key

如：创建部门表 dept，表结构如下

sql语句如下：

CREATE TABLE dept(
	deptid INT PRIMARY KEY,
	dept_name VARCHAR(50),
	dept_desc VARCHAR(200),
	dept_num INT
);

通过上图，我们还看到有个自增，自增是什么？ 在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的 主键值。可以通过为表主键添加 AUTO_INCREMENT关键字来实现。默认 的，在MySQL中AUTO_INCREMENT的初始值是1，每新增一条 记录，字段 值自动加1。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字 段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数 类型（TINYINT、SMALLIN、INT、BIGINT 等）。

CREATE TABLE dept(
	deptid INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(50),
	dept_desc VARCHAR(200),
	dept_num INT
);

 

2)定义表之后指定主键

ALTER TABLE 表名 ADD CONSTRAINT主键名 PRIMARY KEY(字段);

ALTER TABLE employee ADD CONSTRAINT pk_empid PRIMARY KEY(emp_id);

2.联合主键

主键由多个字段联合组成，语法规则如下：

primary key(字段1,字段2,...字段n)

表关系详细讲解。 主键选取原则：

1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。

2. 主键应该是单列的，以便提高连接和筛选操作的效率。

3. 永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。

4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。

5. 主键应当有计算机自动生成 我们在建立数据库的时候，需要为每张表指定一个主键，所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然，其它字段可以辅助我们在执行这些操作时消除共享冲突，不过就不在这里讨论了。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。

4.2.2 非空约束

非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空 约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

 字段名 数据类型 not null

4.2.3 唯一约束

唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出 现一个空值。唯一约束可以确保一列或者几列不出现重复值。 唯一性约束的语法规则如下

字段名 数据类型 UNIQUE

如果定义完表后也可以给某个字段添加唯一约束，语法如下：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (约束字段);

ALTER TABLE employee ADD CONSTRAINT uk_phone UNIQUE (emp_phone);

 

删除唯一索引的语法：

ALTER TABLE 表名 DROP INDEX 约束名;

ALTER TABLE employee DROP INDEX uk_phone;

4.2.4 默认值约束

默认约束（Default Constraint）指定某列的默认值。如男性同学较多， 性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。 默认约束的语法规则如下：

字段名 字段类型 default 默认值

CREATE TABLE employee(
	emp_id INT,
	emp_name VARCHAR(50) NOT NULL ,
	emp_sex CHAR(2) DEFAULT '男',
	emp_salary FLOAT,
	emp_phone VARCHAR(50) UNIQUE ,
	emp_birth DATE,
	dept_id INT
);

4.2.5 外键约束

外键用来在两个表的数据之间建立连接，可以是一列或者多列。一个表 可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空 值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。 外键：首先它是表中的一个字段，虽可以不是本表的主键，但要对应另 外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后， 不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致 性、完整性。例如，部门表tb_dept的主键是id，在员工表tb_emp5中有一个键deptId与这个id关联。 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所 在的那个表即是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所 在的那个表即是从表。 语法：

ALTER TABLE 子表 ADD CONSTRAINT 约束名 FOREIGN KEY (外键) REFERENCES 主表(主键);

ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
REFERENCES dept(deptid);

以上案例就是一个一对多的表关系。一个部门下有多个员工，一个员工从属一个部门。 常用的表关系【重点】有三种：一对一，一对多【自关联】，多对多

一对一：人与身份证

/*
	一对一
	人 	身份证
	1 	   1
*/
CREATE TABLE person(
	pid INT PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(50),
	pphone VARCHAR(50)
);
CREATE TABLE idcard(
	cid INT PRIMARY KEY,
	cnum VARCHAR(50),
	cstart DATE,
	cend DATE,
	cpublish VARCHAR(200)
);
ALTER TABLE idcard ADD CONSTRAINT fk_card_person FOREIGN KEY(cid) REFERENCES person(pid);

一对多：部门与员工

/*
	1.一对多
	部门 员工
	1 	N
	1 	1
*/
CREATE TABLE department(
	departid INT PRIMARY KEY AUTO_INCREMENT,
	departname VARCHAR(50),
	departnum INT DEFAULT 0,
	departdesc VARCHAR(200)
);
CREATE TABLE employee(
	empid INT PRIMARY KEY AUTO_INCREMENT,
	empname VARCHAR(50),
	empsex CHAR(2) DEFAULT '男',
	empbirth DATE,
	empphone VARCHAR(50),
	deptid INT -- FK
);
/*
alter table tabname add constratin 约束名 foreign key(字段) references 主表
(主键);
*/
ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptid) REFERENCES department(departid);

多对多：用户与角色【中间表多列主键】

/* 多对多的关系
用户 角色
1	 N
N 	1
N 	N
*/
CREATE TABLE users(
	uid INT PRIMARY KEY AUTO_INCREMENT,
	uname VARCHAR(50) NOT NULL UNIQUE,
	upwd VARCHAR(50) NOT NULL,
	ustatus INT
);
CREATE TABLE roles(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(50) NOT NULL
);
CREATE TABLE userroles(
	uid INT ,
	rid INT,
	PRIMARY KEY(uid,rid)
);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_user FOREIGN KEY(uid) REFERENCES users(uid);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_role FOREIGN KEY(rid) REFERENCES roles(rid);

自关联：省市

CREATE TABLE cities(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(50),
	pid INT ,
	CONSTRAINT fk_pro_ci FOREIGN KEY(pid) REFERENCES cities(cid)
)

4.2.6 查看表结构

DESCRIBE/DESC语句可以查看表的字段信息，其中包括字段名、字段 数据类型、是否为主键、是否有默认值等。语法规则如下：

DESCRIBE 表名;

或者简写为

DESC 表名;

 

其中，各个字段的含义分别解释如下 NULL：表示该列是否可以存储NULL值。 Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分； UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定 值允许出现多次。 Default：表示该列是否有默认值，有的话指定值是多少。 Extra：表示可以获取的与给定列有关的附加信息，例如 AUTO_INCREMENT等。 查看表详细结构语句： SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE 语句，语法格式如下：

SHOW CREATE TABLE employee;

 

CREATE TABLE `employee` (
	`emp_id` int(11) NOT NULL AUTO_INCREMENT,
	`emp_name` varchar(50) NOT NULL,
	`emp_sex` char(2) DEFAULT '男',
	`emp_salary` float DEFAULT NULL,
	`emp_phone` varchar(50) DEFAULT NULL,
	`emp_birth` date DEFAULT NULL,
	`dept_id` int(11) DEFAULT NULL,
	PRIMARY KEY (`emp_id`),
	UNIQUE KEY `emp_phone` (`emp_phone`),
	KEY `fk_emp_dept` (`dept_id`),
CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept`
    (`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

4.2.7 修改表结构

修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用 ALTER TABLE语句修改表。常用的修改表的操作有修改表名、修改字段数 据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对和修改表有关的操作进行讲解。

1. 修改表名

   ALTER TABLE <旧表名> RENAME [TO] <新表名>;
   

   ALTER TABLE employee RENAME TO emp;
   

2. 修改字段的数据类型

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

   ALTER TABLE emp MODIFY emp_name VARCHAR(30);
   

3. 修改字段名

   ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
   

   ALTER TABLE emp CHANGE dept_id deptid INT;
   

4. 添加字段

   ALTER TABLE <表名> ADD <新字段名> <新字段类型> <约束条件> [FIRST|AFTER 已存在字段名];
   

   ALTER TABLE emp ADD empaddr VARCHAR(20);
   ALTER TABLE emp ADD empaddr VARCHAR(20) AFTER empsex;
   ALTER TABLE emp ADD empnum VARCHAR(20) FIRST;
   

   比如条件null

   ALTER TABLE tabname ADD column1 VARCHAR(12) not null;
   

5. 删除字段

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

   ALTER TABLE emp DROP empno;
   

6. 修改字段的排序位置

   ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
   

   ALTER TABLE emp MODIFY deptid INT AFTER empphone;
   

7. 删除表的外键约束

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

   ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;
   

8. 更改表的存储引擎

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

   ALTER TABLE tb_deptment3 ENGINE=MyISAM;
   

9. 删除数据表

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

建表的时候，如果选择数据类型

1. 整数和浮点数 如果不需要小数部分，就使用整数来保存数据；如果需要表示小数部分，就使用浮点数类型。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如，假设列的值的范围为1~99999，若使用整数，则 MEDIUMINT UNSIGNED是 好的类型；若需要存储小数，则使用FLOAT 类型 浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此要求存储精度较高时应选择DOUBLE类型

2. 浮点数和定点数浮点数

   FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。由于浮点数容易产生误差，因 此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。 在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题， 好不要这样使用。另外，两个浮点数进行减法和比较运算时也容易出 问题，因此在进行计算的时候，一定要小心。进行数值比较时， 好使用DECIMAL类型

3. 日期与时间类型 MySQL对于不同种类的日期和时间有很多数据类型，比如YEAR和TIME。如果只需要记录年份，则使用YEAR类型即可；如果只记录时间，则使用TIME类型。 如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。由于 TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期 好使用DATETIME。 TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录的同时插入当前时间时，使用TIMESTAMP是方便的。另外，TIMESTAMP在空间上比 DATETIME更有效。

4. CHAR与VARCHAR之间的特点与选择 CHAR和VARCHAR的区别如下 CHAR是固定长度字符，VARCHAR是可变长度字符。 CHAR会自动补空格，VARCHAR不自动补。 CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点是浪费存储空间，所以对存储不大但在速度上有要求的可以使用CHAR类型，反之可以使用 VARCHAR类型来实现。