【MySQL】数据库的基本操作

数据表的基本操作

创建数据表

CREATE TABLE<表名>
	字段名1，数据类型 [列级别约束条件] [默认值]，
	字段名2, 数据类型 [列级别约束条件] [默认值]，
	...... 
	[表级别约束条件]
);

使用 CREATE TABLE创建表时,必须指定以下信息:

• 要创建的表的名称,不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
• 数据表中每一个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。

主键约束

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

单字段主键

（1）在定义列的同时指定主键,语法规则如下:

字段名 数据类型 PRIMARY KEY [默认值]

【例】定义数据表tb_emp2，其主键为id，SQL 语句如下:

CREATE TABLE tb_emp2
(
	id INT(11) PRIMARY KEY,
	name VARCHAR(25),
	deptId INT(11),
	salary FLOAT
);

（2）在定义完所有列之后指定主键。

[CONSTRAINT<约束名>] PRIMARY KEY [字段名]

【例】定义数据表tb_emp3，其主键为id，SQL语句如下:

CREATE TABLE tb_emp3
(
	id INT(11),
	name VARCHAR (25),
	depttd INT(11),
	salary FLOAT,
	PRIMARY KEY (id)
);

多字段联合主键

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

PRTMARY KEY [字段1， 字段2， ...  字段n]

【例4.4】定义数据表tb_emp4，假设表中间没有主键d，为了唯一确定一个员工，可以把name、deptld联合起来做为主键，SQL语句如下:

CREATE TABLE tb_emp4
(
	name VARCHAR (25),
	deptId INT (11),
	salaoy FLOAT,
	PRIMARY KEY (name ,deptId)
);

外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

• 外键:首先它是表中的一个字段,它可以不是本表的主键，但对应另外一个表的主键。外
  键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。
  例如，部门表tb_dept 的主键是id，在员工表tb_emp5中有一个键 deptId与这个id关联。
• 主表(父表):对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
• 从表(子表):对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

[CONSTEAINT <外键名>] FOREIGN KEY 字段名1[,字段名2] REFERENCES <主表名> 主键列1 [, 主键列2, ...]

• “外键名”为定义的外键约束的名称,一个表中不能有相同名称的外键;
• “字段名”表示子表需要添加外键约束的字段列;
• “主表名”即被子表外键所依赖的表的名称;
• “主键列”表示主表中定义的主键列,或者列组合。

【例】定义数据表tb_emp5，并在tb_emp5表上创建外键约束。创建一个表tb_dept2，SQL语句如下:

CREATE TABLE tb_dept2
(
	id INT (11) PRIMARY KEY,
	name VARCHAR(22) NOT NULL,
	location VARCHAR(50)
);

定义数据表tb_emp5，让它的键 deptld作为外键关联到tb_dept2的主键id，SQL语句为:

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

以上语句执行成功之后，在表tb_emp5上添加了名称为fk_emp_deptl 的外键约束，外键名称为deptld，其依赖于表tb_dept2的主键id。

非空约束

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

字段名 数据类型 not null

设置表的属性值自动增加

字段名 数据类型 AUTO_INCREMENT

【例】定义数据表tb_emp8，指定员工的编号自动递增，SQL语句如下:

CREATE TABLE tb_emp8
(
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR (25) NOT NULL,
	deptId INT(11),
	salary FLOAT
);

输入

INSERT INTO tb_emp8 (name, salary) VALUES ('LUCKT', 1000), ('Kevin', 1500);

id自动增加

查看数据表的结构

查看表基本结构语句 DESCRIBE

语法规则

DESCRIBE 表名;

或者简写为：

DESC 表名;

查看表详细结构语句 SHOW CREATE　TABLE

SHOW CREATE　TABLE　表名；

修改数据表

修改表名

MySQL是通过ALTER TABLE语句来实现表名的修改的，具体的语法规则如下:

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

【例】使用ALTER TABLE将表tb_dept3改名为tb_deptment3,
SQL语句如下:

ALTER TABLE tb_dept3 RENAME tb_deptment3;

修改字段的数据类型

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

【例】将数据表tb_emp4中 name字段的数据类型由 VARCHAR(25)修改成VARCHAR(30)。

ALTER TABLE tb_emp4 MODIFY name VARCHAR(30);

name字段的数据类型由 VARCHAR(25)修改成VARCHAR(30)，修改成功

修改字段名

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

添加字段

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

• 新字段名为需要添加的字段的名称;
• “FIRST”为可选参数，其作用是将新添加的字段设置为表的第一个字段;
• “AFTER”为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面。

添加无完整约束性条件的字段

ALTER TABLE tb_dept1 ADD managerId INT (10);

添加有完整性约束条件的字段

ALTER TABLE tb_dept1 ADD columnl VARCHAR (10) NOT NULL;

在表的第一列添加字段

ALTER TABLE tb_dept1 ADD columnl2 VARCHAR (10) FIRST;

在表的指定列添加字段

ALTER TABLE tb_dept1 ADD columnl3 VARCHAR (10) AFTER name;

删除字段

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

修改字段的排列顺序

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

修改字段为表的第一个字段

【例】将数据表tb_deptl中的columnl字段修改为表的第一个字段，SQL语句如下:

ALTER TABLE tb_dept1 MODIFY columnl VARCHAR (12) FIRST;

修改字段到表的指定列之后

【例】将数据表tb_dept1中的columnl2字段插入到name字段后面，SQL语句如下:

ALTER TABLE tb_dept1 MODIFY columnl2 VARCHAR(12) AFTER name;

删除表的外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系，MySQL 中删除外键的语法格式如下:

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

删除数据表

删除没有被关联的表

在MySQL中使用DROP TABLE可以一次删除一个或多个没有被其他表关联的数据表
语法格式如下:

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

参数“IF EXISTS”用于在删除前判断删除的表是否存在，加上该参数后，再删除表的时候，如果表不存在，SQL语句可以顺利执行，但是会发出警告（warning）。

删除被其他表关联的主表

数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败。原因是直接删除，将破坏表的参照完整性。如果必须要删除，可以先删除与它关联的子表，再删除父表，只是这样同时删除了两个表中的数据。但有的情况下可能要保留子表，这时如要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表。

创建关联表

使用SHOW CREATE TABLE命令查看表tb_emp5的外键约束

解除关联子表tb_emp5的外键约束

ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp_dept1;

删除父表

DROP TABLE tb_dept2;