ST-6-测试-Mysql学习-2

8. 表的约束

8.1 表的约束（constraint）【6 种】

说明：mysql 常用的约束有如下 6 种：

作用：约束是一种表级别的限制，它通过对表的行或列的数据做出限制，保证数据的完整性和一致性

主键(Primary Key, 简称PK)约束

主键(PRIMARY KEY)是用于约束表中的每一行，作为这一行的标识符，主键字段，对于表中的数据来说，就类似于一个人的身份证号，不允许重复。

定义主键的作用：避免表中的数据重复。

特殊说明：一个表只能有一个主键，这个主键可以由一个字段构成，也可以由多个字段构成。

主键特点：唯一(unique)，非空(not null)，可以由单个字段或者多个字段构成，一个表只能有一个主键

PRIMARY：主要的，基本的

KEY：键，关键字

建表时，违反主建约束，系统会做如下提示：

Duplicate entry '1' for key 'PRIMARY'：由于主键原因导致数据重复。

-- 举例：创建表 customers1，建表时，将 c_no 字段定义为主键

CREATE TABLE test.customers1(c_no INT(4), c_name CHAR(20),address TEXT(50),PRIMARY

KEY(c_no));

-- 说明：PRIMARY KEY(c_no)用来将 c_no

-- 举例：在 test 库中，创建班级表 class，包括字段班级编号 cno，班主任名称 cmaster，系的编号 dno

-- 为班级编号添加主键约束，为班主任字段添加唯一性约束

CREATE TABLE test.class

(

cno INT(3)PRIMARY KEY, -- 将 cno 字段定义为主键

cmaster CHAR(30) UNIQUE, -- 将 cmaster 字段定义为唯一键

dno INT(3)

);-- 当主键由多个字段构成时，称为复合主键；-- 面试题

举例：school2 数据库中，创建表 stu7，表中包括字段学号 sid,名称 sname,分数 score

班号 cno，将字段 sid 和 sname 设置为复合主键。

CREATE TABLE school2.stu7

(

sid INT(4), sname CHAR(30), score FLOAT(6,2), cno INT(6), PRIMARY KEY(sid,sname) -- 将字段 sid 和 sname 设置为复合主键

); -- 面试题

非空(NOT NULL) 约束

非空约束，指定某列字段不能为 null 值；mysql 中的 NULL 值，意思是不确定，没有具体值；所有数据类型的值都可以是 null，包括 int、float、char 等；空字符串不等于 null，0 也不等于 null；null 不区分大小写。

-- 举例：创建表 classa,将 cno 字段设置为主键，将 cname 设置为非空

CREATE TABLE test.classa(cno INT(4),cname CHAR(20) NOT NULL);

INSERT INTO test.`classa`(cno,cname) VALUES(1,NULL); -- 应该插入失败

唯一性(Unique) 约束

唯一性约束用来限制不受主键约束的列上的数据的唯一性；同一个表中可以有多个唯一性约束。

Unique：唯一

唯一性约束特点：可以为空，数据不允许重复，一个表中可以有多个唯一键

举例：在 test 库中创建表 stu(sno，sname，cowner（班主任），cno),将字段 sno 定义为主键，将字段cowner 定义为唯一键

Create table test.stu(sno int(5) primary key,sname char(30),cowner char(30) unique,cno int(5))；

外键(Foreign Key, 简称FK) 约束

外键(FORGEIGN KEY) 既能确保数据完整性，也可以用来建立一个和另一个表的直接关系；一个表可以有一个或者多个外键，每个外键的数据必须references引用另一个表的主键或者唯一键。添加了外键的约束的列，取值必须在它参考的列（另一个表的主键或者唯一键）中有对应值。

-- 举例：在 test 库，创建客户表 customers1，建表时，将 c_no 字段定义为主键（用来配合另一个表创建时设置外键）

CREATE TABLE test.customers1(c_no INT(4), c_name CHAR(20),address TEXT(50),PRIMARY

KEY(c_no));

-- 说明：PRIMARY KEY(c_no)用来将 c_no 字段定义为主键。

举例：在 test 库，创建订单表，表中字段有：订单编号，OrderNo；商品名称，P_name，客户编号，c_no，要求为 OrderNo 添加主键约束，为字段 c_no 字段添加外键约束，c_no 字段的数据引用自 customers1表的主键字段 c_no 的数据

CREATE TABLE test.Orders -- 创建订单表

(

OrderNo INT , -- 订单编号字段

P_name CHAR(30), -- 商品名称

c_no INT(4), -- 客户编号

PRIMARY KEY (OrderNo), -- 将 OrderNo 字段定义为主键

FOREIGN KEY (c_no) REFERENCES customers1(c_no) -- 将 c_no 字段定义为外键，

-- 该字段的数据来源于表 customers1 的字段 c_no 的值

);

-- REFERENCES 参考，引用，来源于的意思

-- FOREIGN KEY 外键的意思

-- PRIMARY KEY 主键的意思

--说明：外键约束相关问题（面试题）

-- 1、存在有引用关系的多个表，被引用数据的表称为主表，引用其他数据的表称为从表；

-- 2、往表中插入数据时，需要先往主表中插入数据，再往从表中插入数据，否则，报错；

-- 3、删除表中数据时，需要先删除从表中的数据，再删除主表中的数据，否则报错；

-- 4、删除表时，删除表的顺序，需要先删除从表，再删除主表，否则报错。

默认值(default)约束

DEFAULT 约束用于向列中插入默认值。

如果往表中插入数据时，插入语句中没有指定插入的值，那么会将默认值插入到表中；

如果指定了插入的值，那么将插入指定的值。

举例：创建表 wclass,将 cno 字段设置为主键，为字段 cowner 设置默认值为“zhangxueyou”

CREATE TABLE test.wclass(cno INT(4),cname CHAR(20),cowner CHAR(20) DEFAULT "zha

ngxueyou",dno INT(5)，

PRIMARY KEY(cno));

主键字段自增长auto_increment

--auto 自动

--increment 增长

CREATE TABLE test.foods

(

fid INT(5) PRIMARY KEY AUTO_INCREMENT, -- 定义主键字段可以自增长

fname CHAR(30), city CHAR(40)

);

-- AUTO_INCREMENT 自动递增的意思

INSERT INTO test.`foods`(fname,city)

VALUES("noodle","jingzhou"),("cake","guanzhou")；

面试题：主键约束和唯一性约束的区别主键不可以重复，不可以为空，一个表中只能有一个主键，但主键可以由一个字段构成，也可以由多个字段构成；

唯一键可以为空，数据不可以重复，一个表中可以由多个唯一键。

8.2 表的修改

-- 说明：修改表，就是对表的表名、字段【添加、修改、删除】、字段类型、字段长度、

约束等进行修改。

8.2.1 表的名称修改

-- 修改表名语法：

ALTER TABLE 库名.表名

RENAME TO 新表名;

举例：修改 test 库中 stu 表名称为 sstuu

ALTER TABLE hrdb.`stu` RENAME TO stuu; -- RENAME 为重命名的意思

8.2.2 修改表的字段或者约束（修改表结构）

8.2.2.1 为表添加主键约束

-- 语法：

ALTER TABLE 库名.表名

ADD PRIMARY KEY(字段名);

建销售表 sales 表以备练习：

在 test 数据库中创建销售表 sales：

字段定义信息为:

customer_no int(10),customer_name char(30),sales_no int(10),product_name VARCHAR(30));

Create table test.sales

(customer_no int(10), customer_name char(30), sales_no int(10), product_name VARCHAR(30));

举例 1：

修改 sales 表，为字段 customer_no 添加主键约束

ALTER TABLE hrdb.`sales` ADD PRIMARY KEY(customer_no);

SHOW KEYS FROM hrdb.`sales`; -- 查看指定表的约束

-- 解释：

-- ALTER TABLE：修改表的意思，ALTER 为修改或者改变的意思

-- ADD PRIMARY KEY(customer_no) 将字段 customer_no 设置为主键

举例 2：删除 sales 表的主键 customer_no

ALTER TABLE hrdb.`sales` DROP PRIMARY KEY; -- 删除主键

-- 语法：ALTER TABLE 库名.表名

DROP PRIMARY KEY;

8.2.2.2 为表添加非空约束

举例 3：修改 sales 表约束，使客户名称 customer_name 不允许为空

-- 语法：

ALTER TABLE 库名.表名

MODIFY 字段名 数据类型(数据长度) NOT NULL; -- modify 修改

ALTER TABLE hrdb.sales

MODIFY customer_name CHAR(25) NOT NULL; -- 解释： MODIFY 为修改的意思

8.2.2.3 修改表中某个字段的名称

举例 4：修改表 sales，将字段`product_name`名称修改为 pct_name

-- 语法：

ALTER TABLE 库名.表名

CHANGE 原字段名 新字段名 数据类型(数据长度); -- 解释：change 为更换的意思

ALTER TABLE hrdb.`sales` CHANGE product_name pro_name CHAR(40);

8.2.2.4 为表添加一个字段（面试题）

举例 5：为表 sales 添加字段 product_name,字段类型为 varchar，长度为 30

-- 解释：

ADD [COLUMN]：增加列的意思，column 为列，字段的意思

ALTER TABLE hrdb.`sales` ADD COLUMN product_name VARCHAR(30);

8.2.2.5 修改表中指定字段的数据类型和数据长度

举例 6：修改 sales 表中字段`pct_name`的字段类型为 char(20) -- 语法：

ALTER TABLE 库名.表名

MODIFY [COLUMN] `字段名` 新数据类型(新数据长度);

ALTER TABLE hrdb.`sales` MODIFY pro_name CHAR(20); -- MODIFY 为修改的意思

-- 小结：

Modify 既可以修改数据类型，还可以修改字段长度、约束

Change：修改字段

Add:添加字段drop：删除字段

8.2.2.6 删除表中指定的字段 （面试题）

举例 7：删除表 sales 中的字段 product_name （面试题）

-- 语法：

ALTER TABLE 库名.表名

DROP 【COLUMN】 字段名; -- column 可以省略

ALTER TABLE hrdb.`sales` DROP COLUMN product_name; -- 解释：DROP COLUMN product_name，意思是删除字段 product_name

-- DROP：删除的意思

-- COLUMN：字段，列的意思

8.2.2.7 创建表时将表中指定字段设置为主键

举例 8： 在 hrdb 库中，创建学生表 student(学生编号 sno int(5),将该字段定义为主键, -- 学生名称 sname char(30),班级编号 classno int（6）)

CREATE TABLE hrdb.student(

sno INT(5) PRIMARY KEY, sname CHAR(30), classno INT(6) -- 班级编号);-- 举例 9：创建表班级表 class（班号 cno int(6),班级名称 cname char(25),系名 depno int（4） ）

CREATE TABLE hrdb.class(cno INT(6), -- 班级编号

cname CHAR(25), depno INT(4)

);

8.2.2.8 修改表将表中指定字段设置为外键

举例 10：为 test 库中表 student 的字段 classno 添加外键约束，该外键字段的数据引用

自表 sclass 的主键字段 cno

-- 添加外键语法：

ALTER TABLE 库名.表名

ADD CONSTRAINT 外键约束名称 FOREIGN KEY(本表设置为外键字段的名称)

REFERENCES 库名.`被引用字段的表名称`(被引用字段的名称);

------创建两个辅助练习表

CREATE TABLE test.student -- 创建学生表（从表）

(sno INT(5), sname CHAR(29),classno INT(5), PRIMARY KEY(sno));

CREATE TABLE test.sclass -- 创建班级表（主表）

(cno INT(5), cname CHAR(20), dno INT(5), PRIMARY KEY(cno));

修改外键语句如下：

ALTER TABLE hrdb.`student` ADD CONSTRAINT student_cno_fk FOREIGN KEY(cno) REFERENCES hrdb.`class`(cno);

-- 解释：

-- ADD CONSTRAINT：添加约束的意思

-- student_cno_fk：外键约束的名称

-- FOREIGN KEY(classno)：将字段 classno 设置为外键

-- REFERENCES hrdb.`sclass`(cno)：(数据）引用自 hr 库的 class 表的主键 cno 字段

-- REFERENCES：引用，参考的意思

8.2.2.9 删除表中指定字段的外键约束

步骤一：删除外键约束

-- 语法

ALTER TABLE 库名.表名

DROP FOREIGN KEY 外键约束名称;

步骤二：删除外键索引

DROP INDEX 索引名称 ON 库名.表名；

-- index 索引

举例 11：删除 xuexi 库 stu5 表的外键约束 stu5_cno_fk

说明：删除外键，分为两步：

步骤一：删除外键约束

ALTER TABLE xuexiao.`stu5` DROP FOREIGN KEY student_cno_fk; -- 解释：DROP FOREIGN KEY 删除外键，drop 是删除的意思，FOREIGN KEY 外键的意思

-- student_cno_fk :外键约束的名称

-- 删除外键语法：

步骤一：

ALTER TABLE 库名.表名

DROP FOREIGN KEY 外键约束名称;

步骤二：删除外键索引

DROP INDEX 表名_字段名_约束类型 ON 库名.`表名`;-- 解释：

-- DROP INDEX：删除索引

-- INDEX：索引

-- stu5_cno_fk：索引名称（外键名称），和外键约束名称相同

-- ON xuexiao.`stu5`：通过关键字 ON 指明是删除 hr 库中学生表的索引

-- 删除索引语法

Drop index 库名.索引名 On 库名.表名；

补充举例：举例：删除 hrdb 库中 student 表的字段 classno 的外键约束名词

student_classno_fk

第一步：删除外键约束名称

ALTER TABLE hrdb.student

DROP FOREIGN KEY student_classno_fk;

第二步：删除外键索引

DROP INDEX student_classno_fk ON hrdb.student;

8.2.2.10 创建表，将其中两个字段设置为复合主键（重点）

举例 12：创建系表 dep(系编号 dep_id int（4）,系名 dep_name varchar(20),系主任

dep_master varchar(30)),将字段系编号、系名两个字段设置为联合主键

Create table hrdb.dep(  Dep_id int(4), Dep_pname varchar(20),Dep_master varchar(30), Primary key(Dep_id ,Dep_pname)

);

--创建表设置复合主键语法：

Create table 库名.表名

(

字段 1 数据类型 1(数据长度 1), 字段 2 数据类型 2(数据长度 2), 字段 3 数据类型 3(数据长度 3), Primary key(字段名 1 ,字段名 2)

);

8.2.2.11 修改指定表的表名

举例 13：修改 hrdb 库中 dep 表的表名为 dep4

rename table hrdb.dep to dep4;

-- 修改表名语法：

rename table 库名.旧表名 to 新表名;

-- 该命令等效于：alter table 旧表名 rename 新表名:

如：

ALTER TABLE hrdb.`dep2` RENAME dep6;

重要总结：（面试题）当一个表（从表）引用另一个表（主表）的数据时，如果需要将两个表都删除，需要先删除从表，才可以再删除主表；如果顺序相反，将导致删除失败。

-- 重要总结：DROP 可以删除那些数据库对象？（面试题）

可以删除库、表、字段、索引、约束

8.3 复制表（考试题）

3.1 只复制表结构,不复制数据

-- 语法：

create table 库名.表名 1 like 库名.表名 2；

意思：创建一个类似于表名 2 的表名 1。

like：像，类似

举例：创建一个和 sales 表相同的表 sales2(只复制表结构)

CREATE TABLE sales2 LIKE sales; -- like 类似于

3.2 同时复制表结构和数据

-- 语法：

create table 库名.表名 1 [as] select * from 库名.表名 2;

举例：创建一个和 sales 表相同的表 sales3(同时复制表结构和数据)

CREATE TABLE sales3 AS SELECT * FROM hrdb.`sales`; -- as 可以省略

9. 表中数据的操作(重点)

#查询表中的数据(重中之重) --测试时常用

##查询单个表中的数据

-- 结构化查询语言-SQL 语言

说明：

结构化查询语言（Structured Query Language 或简称为 SQL）,即 SQL 语言，是一种应用最为广泛的关系数据库语言，该语言定义了操作关系数据库的标准语法，几乎所有的关系

数据库管理系统都支持 SQL 语言。【关系型数据库：mysql、oracle、SQL server、DB2、

sybase】

SQL 语言分类：

下面的五大分类要能讲出来（面试题）

n DDL 语言(数据定义语言) - Create、Alter、Drop、truncate

n DQL 语言(数据查询语言) - Select 查询语句

n DML语言(数据操纵语言) - Insert、Update、Delete

n DTL 语言(事务控制语言) - Commit(提交)、Rollback(回滚) 事务提交与回滚语句

n DCL 语言(数据控制语言) - Grant(授权)、Revoke(回收权限) 授予权限与回收权限语句

Select 语句语法如下：

(1) select 字段列表

(2) from 数据表(3) [ where 条件 ]

(4) [ group by 分组字段]

(5) [ having 条件]

(6) [ order by 排序字段 [ asc | desc ] ]

(7) [ limit [start],length];

【划重点】：select 语句中，各子句出现的顺序不能乱，否则报错

-- 解释：

-- select ：查询的意思

-- 字段列表包括：

-- * 代表查询表中所有字段的值

-- 单个字段：查询所有行对应该字段的值

-- 逗号隔开的多个字段：查询给出行对应的相应字段的值

-- 关系运算符：等于号=，大于号>,小于号<,不等于号!=、<>,大于等于>= 、小于等于

<=

-- 数学运算符 加+、减-、乘*、除/

-- 逻辑运算符：not 非，and 和，or 或

-- not 表达式：取表达式计算结果相反的数据

条件 1 AND 条件 2：查询结果返回的值同时符合条件 1 和条件 2

-- 条件 1 or 条件 2：查询结果中的数据，如果只符合条件 1，那么结果只显示符合条件 1

相关数据；如果查询结果数据只符合条件 2，那么结果中只显示符合条件 2 的相关数据，如果两个条件都符合，那么结果显示符合两个条件的所有数据。

举例：查询员工表（employees）中所有人员的所有信息SELECT * FROM hrdb.`employees`; * 号：代表表中的所有字段，以及所有记录

举例：查询 employees 表中所有人员的姓、名

SELECT first_name,last_name FROM hrdb.`employees`;

举例：查询员工表中员工编号为 101 的员工的员工编号、工资

SELECT employee_id,salary

FROM hrdb.`employees` WHERE employee_id=101;

9.1 关系运算符 大于号>、小于号<、不等于号!=\<>、大于等于号>=、小于等于号<= 的用法

举例：查询工资大于 9000 的人员的姓名、工资

SELECT first_name,last_name,salary

FROM HRDB.`employees` WHERE salary>9000;

举例：查询工资大于等于 9000 的人员的工资、姓、部门编号

SELECT salary,last_name,department_id

FROM hrdb.`employees` WHERE salary>=9000;

举例：查询工资不等于 9000 的人员的工资、姓、部门编号

方法一：

SELECT salary,last_name,department_id

FROM hrdb.`employees`WHERE salary!=9000;

方法二：

SELECT salary,last_name,department_id

FROM hrdb.`employees` WHERE salary<>9000;

9.2 逻辑运算符 not（非）、and（与）、or（或）的用法

方法三：

SELECT salary,last_name,department_id

FROM hrdb.`employees` WHERE NOT salary=9000; -- not 是取反的意思

举例：查询工资大于 9000，部门编号小于 102 的人员的员工编号、入职日期、工资、部门

编号

SELECT employee_id,hire_date,salary,department_id

FROM hrdb.`employees` WHERE salary>9000 AND department_id<102; -- and 连接两个查询条件

举例：查询员工表中部门编号分别为 60 和 90 的人员的工资、部门编号、员工编号

SELECT salary,department_id,employee_id

FROM hrdb.`employees` WHERE department_id=60 OR department_id=90; -- or 连接两个查询条件，符合任何一个条件的数据都将被显示在查询结果中

9.3 关键字 in 在查询语句中的用法

说明：当查询条件给出的为多个类似的值时，可以使用关键字 IN，不同值之间使用逗号隔开，in 后跟数据列表，匹配其中任何一条数据的记录都将显示在查询结果中。

举例：查询工资分别为 9000、10000、11000、12000 的员工编号、工资

SELECT employee_id,salary

FROM hrdb.`employees` WHERE salary IN(9000,10000,11000,12000);

9.4 关键字 between...and...的用法

说明：between...and...意思是在...和...之间

-- between 值 1 and 值 2，意思是在值 1 和值 2 之间

举例：查询工资在 3000-6000 之间的人员的工资、员工编号、职位编号

SELECT salary,employee_id,job_id

FROM hrdb.`employees` WHERE salary BETWEEN 3000 AND 6000;

9.5 null 关键字在查询语句中的用法

说明：null 为空、不确定的意思

-- 说明：is null 代表为空的意思， is not null 为非空的意思，不能使用=null

举例：查询部门表中经理编号为空的部门的编号、部门名称、经理编号

SELECT department_id,department_name,manager_id

FROM hrdb.`departments` WHERE manager_id IS NULL;

错误写法：

SELECT department_id,department_name,manager_id

FROM hrdb.`departments`WHERE manager_id = NULL;

举例：查询部门表中经理编号不为空的部门的编号、部门名称、经理编号

SELECT department_id,department_name,manager_id

FROM hrdb.`departments` WHERE manager_id IS NOT NULL;

9.6 聚合函数的用法

-- 说明：聚合函数即分组函数，主要包括：求最大值 max()函数、最小值 min()函数、

-- 平均值 avg()函数、求和 sum()函数

-- 统计条数 count()函数。

举例：查询员工表中的最高工资、最低工资、平均工资、工资总和

SELECT MAX(salary) 最高工资,MIN(salary) 最低工资,AVG(salary) 平均工资,SUM(salary) 和

FROM hrdb.`employees`; -- 说明：最高工资、最低工资、平均工资、和为对应函数的别名

举例：查询员工表中的最高工资、最低工资、平均工资、工资总和

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)

FROM hrdb.`employees`;

SELECT MAX(salary) 最高工资,MIN(salary) 最低工资,AVG(salary) 平均工资,SUM(salary) 工资总和

FROM hrdb.`employees`; -- 说明：工资总和等为字段的别名

举例：查询员工表中的所有人员的工资之和以及人员的数量

方法一：SELECT SUM(salary),COUNT(employee_id) FROM hrdb.`employees`; -- 说明：COUNT(employee_id) ，通过使用 count()函数，统计主键字段 employee_id 中对应员工的编号数量，进而统计数员工数量

-- COUNT(employee_id)不统计 employee_id 为 null 的数据

方法二：SELECT SUM(salary),COUNT(*) FROM hrdb.`employees`; -- *号代表所有记录（一行

为一条记录）

-- COUNT(*)：统计结果，包含值为 null 的记录

-- 说明：count(*)和 count(字段名)区别：(考题) -- count(*)统计结果包括所有记录；count(字段名)统计结果不包括字段值为 null 的记录

-- 方法三： SELECT SUM(salary),COUNT(1) FROM hrdb.`employees`; -- 说明：count(*)和 count(1)区别：

-- 二者统计结果相同，但 count(1)查询效率更高