四、数据表的基本操作

陈天在睡觉

已于 2022-04-14 16:16:45 修改

阅读量2.4k

点赞数 2

分类专栏： MySQL 文章标签：数据库

于 2022-04-14 15:50:56 首次发布

本文链接：https://blog.csdn.net/qq_52059326/article/details/124173533

版权

MySQL 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

4.1 创建数据表

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

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

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

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

创建表的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，表结构如下

字段名	数据类型	备注
deptid	int	部门编号主键
dept_name	varchar(50)	部门名称
dept_desc	varchar(200)	部门描述
dept_num	int	部门人数

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)

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

主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。
主键应该是单列的，以便提高连接和筛选操作的效率。
永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。
主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
主键应当有计算机自动生成我们在建立数据库的时候，需要为每张表指定一个主键，所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然，其它字段可以辅助我们在执行这些操作时消除共享冲突，不过就不在这里讨论了。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。

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语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对和修改表有关的操作进行讲解。

修改表名

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

ALTER TABLE employee RENAME TO emp;

修改字段的数据类型

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

ALTER TABLE emp MODIFY emp_name VARCHAR(30);

修改字段名

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

ALTER TABLE emp CHANGE dept_id deptid INT;

添加字段

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;

删除字段

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

ALTER TABLE emp DROP empno;

修改字段的排序位置

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

ALTER TABLE emp MODIFY deptid INT AFTER empphone;

删除表的外键约束

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

ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;

更改表的存储引擎

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

ALTER TABLE tb_deptment3 ENGINE=MyISAM;

删除数据表

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

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

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

FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，好不要这样使用。另外，两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候，一定要小心。进行数值比较时，好使用DECIMAL类型
日期与时间类型 MySQL对于不同种类的日期和时间有很多数据类型，比如YEAR和TIME。如果只需要记录年份，则使用YEAR类型即可；如果只记录时间，则使用TIME类型。如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。由于 TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期好使用DATETIME。 TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录的同时插入当前时间时，使用TIMESTAMP是方便的。另外，TIMESTAMP在空间上比 DATETIME更有效。
CHAR与VARCHAR之间的特点与选择 CHAR和VARCHAR的区别如下 CHAR是固定长度字符，VARCHAR是可变长度字符。 CHAR会自动补空格，VARCHAR不自动补。 CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点是浪费存储空间，所以对存储不大但在速度上有要求的可以使用CHAR类型，反之可以使用 VARCHAR类型来实现。