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)查询效率更高

  • 10
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值