自学MySQL第二天

　　MySQL进阶

一、约束

什么是约束?

为了确保表中数据的完整性, 为表添加了一些限制, 是数据库汇总表设计最基本的一种规则; 减少了冗余数据(脏数据)

约束有五种约束:

主键约束
唯一约束
非空约束
默认约束
外键约束

（一）主键约束

PRIMARY KEY 约束，简称PK，用于标识数据库表中的每条记录是唯一不重复的并且不为空。主键一般设置在id上;

添加主键约束

l 方式一：创建表时，在字段描述处，声明指定字段为主键：

CREATE TABLE p1(

id int PRIMARY KEY,

name varchar(200),

idCard varchar(50)

)

错误信息: 多个主键被定义

l 方式二：创建表时，在constraint约束区域，声明指定字段为主键：

创建格式：[constraint 名称] primary key (字段列表)

关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。

CREATE TABLE p2(

id int,

name varchar(200),

idCard varchar(50),

PRIMARY KEY(id)

)

以上称之为联合主键: 多个字段需要同时使用, 结合一起组成了一个主键(单个值重复是可以正常添加的, 但是多个字段的值不可以同时重复)

l 方式三：创建表之后，通过修改表结构，声明指定字段为主键：

CREATE TABLE p4(

id int,

name varchar(200),

idCard varchar(50)

)

写法格式:

Alter table 表名 add primary key(字段名);

#因为一般主键都是提前确定好的，都是跟随表一起创建

删除主键约束:（扩展-了解）

如需删除 PRIMARY KEY 约束，请使用下面的 SQL：

ALTER TABLE 表名 DROP PRIMARY KEY;

注意:

主键一旦确定, 实际开发中不会轻易去删除;

（二）唯一约束

唯一约束用来限定表中某列数据不允许重复，与主键约束类似，但级别没有主键高。一个表中唯一约束可以创建多个，并且唯一约束的列允许为空。

唯一约束一般用于约束手机号、账号、邮箱等信息。

唯一约束关键字: unique

添加唯一约束

l 方式1：创建表时，在字段描述处，声明唯一：

CREATE TABLE p5(

id int,

name varchar(200),

idCard varchar(50)

)

l 方式2：创建表时，在约束区域，声明唯一：

CREATE TABLE p7(

id int,

name varchar(200),

idCard varchar(50)

)

删除唯一约束（扩展-了解）

如需撤销 UNIQUE 约束，请使用下面的 SQL：

ALTER TABLE p6 DROP INDEX 名称;

唯一约束与主键约束的区别:

一个表中可以由多个唯一约束, 但是一个表中只能有一个主键约束;
唯一约束唯一不重复但是可以为空值, 主键约束唯一不重复, 并且非空;

注意:

唯一约束一旦确定, 实际开发不会轻易删除;

（三）非空约束

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

CREATE TABLE p8(

id int,

name varchar(200),

idCard varchar(50)

)

（四）默认约束

可能通过default设置默认值约束，设置了默认约束的列，如果不给值就会使用默认值来填充。

注意:

当性别列不给值时使用默认值填充;
当性别列指定default关键字时, 也会使用默认值;
当给性别一个新值时, 会替代默认值;

（五）外键约束

外键约束用来在两个表的数据之间建立关联，MySQL数据库是关系型数据库，即表与表之间是存在关系的，而这种关系我们就称之为外键关系。

外键的作用:

将多表之间建立联系

何为主表何为从表?

主表: 数据提供方(一方)

从表: 数据使用方(多方)

代码准备:

#创建分类表：

CREATE TABLE category(

cid varchar(32) PRIMARY KEY,#主键ID

cname VARCHAR(20)#分类名称

);

INSERT INTO category VALUES('c001','电脑办公');

INSERT INTO category VALUES('c002','服装');

#创建商品表：

CREATE TABLE product(

pid INT PRIMARY KEY,#主键ID

pname VARCHAR(20),#商品名称

price DOUBLE,#商品价格

category_cid VARCHAR(32)#外键

);

INSERT INTO product(pid,pname,price,category_cid) VALUES(1,'联想电脑',5000,'c001');

INSERT INTO product(pid,pname,price,category_cid) VALUES(2,'海尔电脑',3000,'c001');

INSERT INTO product(pid,pname,price,category_cid) VALUES(3,'雷神电脑',5000,'c001');

INSERT INTO product(pid,pname,price,category_cid) VALUES(4,'JACK JONES',800,'c002');

INSERT INTO product(pid,pname,price,category_cid) VALUES(5,'真维斯',200,'c002');

INSERT INTO product(pid,pname,price,category_cid) VALUES(6,'花花公子',440,'c002');

INSERT INTO product(pid,pname,price,category_cid) VALUES(7,'劲霸',2000,'c002');

为了保证数据的完整性, 需要添加外键约束, 确保数据达到完整性;

声明外键约束：

格式：

alter table 从表 add foreign key (从表外键字段名) references 主表 (主表的主键);

添加数据:

先添加主表中的数据, 在添加从表中的数据;

删除数据:

先删除从表中的数据, 确保主表中的数据没有被引用, 再删除主表中的数据;

如果为从表添加外键约束，必须保证：

u 从表外键的数据类型和长度，必须和主表主键数据类型和长度一致

u 从表和主表为空，或者数据必须完整

（六）自动增长-策略

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为主键

CREATE TABLE p9(

id int PRIMARY KEY,

name varchar(200),

idCard varchar(50)

)

总结:(自增长特点)

只有整数型列才可以设置自增列
只有主键才可以设置自增列
自增列可以不赋值
初始化为1, 增量为1
自增列一旦使用过就不会重复出现

二、表关系

在一个关系型数据库中，利用关系可以避免表中数据的冗余，保证数据紧密性和方便查阅。

表关系的分类:

一对一关系: one_to_one 人与身份号丈夫与妻子

一对多关系: one_to_many 商品与分类用户与订单班级与学生

多对多关系(双向一对多): many_to_many 老师与学生商品与订单演员与角色

（一）一对一关联

一对一关联:

在实际的开发中应用不多.因为一对一可以合成一张表。

建表方式：

外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。

（二）一对多关联

一对多关系中，一方的表称为主表，多方的表称为从表。在从表中添加一个外键用来关联主表的主键，这样就可以实现一对多关系。

应用场景:

商品与分类用户与订单班级与学生

建表方式:

（三）多对多关联

两张表A、B想要实现多对多关系，需要借助一张中间表C，在C中设置两个外键，分别关联A、B表的主键。

应用场景:

老师与学生商品与订单演员与角色

建表方式:

（四）实战演练

实战1：省和市

代码准备:

CREATE TABLE province(

id INT PRIMARY KEY,

NAME VARCHAR(20),

description VARCHAR(20)

);

CREATE TABLE city(

id INT PRIMARY KEY,

NAME VARCHAR(20),

description VARCHAR(20)

);

实战2:用户和角色(多对多关系)

代码准备:

create table actor(

aid int primary key,

name varchar(30)

);

create table role(

rid int primary key,

name varchar(30)

);

三、连接查询

连接查询就是将多张表中的数据按照指定连接条件一起查询出来;

常见的连接查询:

内连接查询
外连接查询

以下准备两张表演练连接查询:

部门表:

员工表:

内连接查询:

内连接查询:

两张表或者多张表按照特定的关联关系关联在一起, 实现查询,返回的是查询两张表的交集;

图解:

内连接分为:

l 隐式内连接

l 显示内连接

隐式内连接:

两张表或者多张表按照特定的关联关系关联在一起实现查询, 对于隐式内连接查询, 表与表之间使用逗号隔开, 使用where设置两张表的关联条件;

写法格式:

Select * from 表1, 表2 where [关联条件];

#查询每个部门下有哪些员工?

查询结果:

#查询开发部门下有哪些员工?

查询结果:

注意事项:

表1与表2之间的顺序是可以相互调换的;
如果不添加where条件, 会出现笛卡尔积现象; 查询的是两张表的乘积, 没有任何意义;如何避免笛卡尔积现象, 添加where关联条件;

如果在连接查询中起了别名, 在使用的时候一定要使用别名;

错误写法:

正确写法:(使用别名.字段名)

如果两张表中出现了同名的字段, 一定要在前边加上表名; 例如: 表名(别名).字段名

显示内连接

两张表或者多张表按照特定的关联关系关联在一起实现查询, 对于显示内连接, 使用inner join将两张表关联在一起, 使用on设置关联条件;

写法格式:

Select * from 表1 inner join 表2 on [关联条件];

查询结果:

外连接查询

外连接查询:

两张表或者多张表联合查询, 以一张表为基准(需要显示该表的全部数据), 另一张表显示与该表中对应的数据, 如果没有对应的数据, 则以null补全;

外连接查询:

左外连接查询和右外连接查询

左外连接查询:

关键字: left join ... on ...

写法格式:

Select * from 表1 left join 表2 on [关联条件];

特点:

以左表为基准, 去匹配右表中的数据, 如果匹配到了, 将显示全部显示;

如果匹配不到, 左表中的数据全部显示, 而右表中的数据以null补全;

图解:

右外连接查询:

关键字: right join ... on ...

写法格式:

Select * from 表1 right join 表2 on [关联条件];

特点:

以右表为基准, 去匹配左表中的数据, 如果匹配到了, 将显示全部显示;

如果匹配不到, 右表中的数据全部显示, 而左表中的数据以null补全;

图解:

# 查询所有员工信息以及部门名称(分别使用左外连接和右外连接)

查询结果:

总结:

内连接和外连接的区别:

内连接查询的是两张表的交集, 去掉没有连接条件的数据
外连接查询是以其中一张表为基准, 去匹配另外一张表, 如果匹配到了, 显示对应的数据, 如果匹配不到, 则以null补全;

四、聚合函数

SQL中存在一些聚合函数，对一组值进行数据统计，并返回单个值，也被称为组函数。这些特殊函数是不能直接在where后面当做条件使用的，经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。(where后不能跟聚合函数, having后可以跟聚合函数)

聚合函数包括：

l count(字段)：统计数量；

l sum(字段)：计算指定列的和；

l max(字段)：计算指定列的最大值；

l min(字段)：计算指定列的最小值；

l avg(字段)：计算指定列的平均值；

代码准备：

#1 查询商品的总条数

#查看商品总价格、最大价格、最小价格、价格的平均值

#2 查询价格大于200商品的总条数

#3 查询分类为'电脑办公'的所有商品的总记录

#4 查询分类为'服装'所有商品的平均价格

五、分组查询

分组查询关键字：group by

分组查询的原理：先对数据进行分组，再对分组后的数据进行汇总。分组查询通常用于配合聚合函数，达到分类汇总统计信息的目的。而其分类汇总的本质实际上就是先将信息分组，分组后相同类别的信息会聚在一起，然后通过需求进行统计计算。

写法格式：

Select * from 表名 group by 分组字段;

代码准备:

#1 统计各个分类下的商品的个数

#2 统计各个分类商品的个数,且只显示分类名不为空值的数据

Where 和 having的区别:(面试题)

Where不能放在group by分组后;
Having是跟在group by分组后, 需要连在一起使用;

注意事项:

二者可以同时去使用, where子句优先于having执行, where 必选放在分组前, 而having必须放在分组后使用;where后不能跟聚合函数, having后可以跟聚合函数; where属于第一次筛选过滤,

分组的二次筛选过滤使用having;

Select查询书写顺序(重点)：

SELECT 字段 FROM 表名 WHERE 条件 GROUP BY（分组）字段 HAVING 条件 ORDER BY（排序）字段;

其中，SELECT字段，FROM表名为必写，其余均可根据情况而定

执行顺序：（了解）

1. From 表名

2. Where 条件 (第一次条件筛选)

3. Group by 字段字段值相同的数据会划分成一组

4. Having 条件对每一组分别进行一次运算(二次条件筛选)

5. Select 字段把每组中第一条数据取出来。合并成一张新伪表

6. 展示这个新伪表上的部分字段

7. order by 字段对新伪表进行最后排序

六、子查询

子查询:

当一个查询时另一个查询的条件时, 称之为自查询, 也叫嵌套查询;

在多表操作中，子查询是一种常见的查询方式。

子查询的三种情况：

I、子查询的结果是一个值的时候

需求：

1、查询工资最高的员工是谁？

2、查询工资小于平均工资的员工有哪些

II、子查询结果是单列多行的时候

需求：

1、查询工资大于5000的员工，来自于哪些部门的名字

2、查询开发部与财务部所有员工的信息

III、子查询的结果是多行多列

需求：查询出2011年以后入职的员工信息，包括部门名称

通过以上子查询得出结论：

子查询结果只要是单列，肯定在 WHERE 后面作为条件

SELECT 查询字段 FROM 表 WHERE 字段 =（子查询）

子查询结果只要是多列，肯定在 FROM 后面作为表

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件

七、数据库的备份和恢复

可视化工具:

备份:

证明备份成功;

恢复:

八、 SQL多表查询综合练习

代码准备:

#部门表

CREATE TABLE dept (

id INT PRIMARY KEY,

dname VARCHAR(50),

loc VARCHAR(50)

);

#添加4个部门

INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'),(20,'学工部','上海'),(30,'销售部','广州'),(40,'财务部','深圳');

#职务表，职务名称，职务描述

CREATE TABLE job (

id INT PRIMARY KEY,

jname VARCHAR(20),

description VARCHAR(50)

);

#添加4个职务

INSERT INTO job (id, jname, description) VALUES(1, '董事长', '管理整个公司，接单'),(2, '经理', '管理部门员工'),(3, '销售员', '向客人推销产品'),(4, '文员', '使用办公软件');

#员工表

CREATE TABLE emp (

id INT PRIMARY KEY, -- 员工id

ename VARCHAR(50), -- 员工姓名

job_id INT, -- 职务id

mgr INT , -- 上级领导

joindate DATE, -- 入职日期

salary DECIMAL(7,2), -- 工资

bonus DECIMAL(7,2), -- 奖金

dept_id INT, -- 所在部门编号

CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),

CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)

);

#添加员工

INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES

(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),

(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),

(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),

(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),

(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),

(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),

(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),

(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),

(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),

(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),

(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),

(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),

(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),

(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

#工资等级表

CREATE TABLE salarygrade (

grade INT PRIMARY KEY, -- 级别

losalary INT, -- 最低工资

hisalary INT -- 最高工资

);

#添加5个工资等级

INSERT INTO salarygrade(grade,losalary,hisalary) VALUES

(1,7000,12000),

(2,12010,14000),

(3,14010,20000),

(4,20010,30000),

(5,30010,99990);

代码准备:

#练习1查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述

#练习2查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

#练习3查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

#练习4查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

#练习5查询出部门编号、部门名称、部门位置、部门人数

#练习6查询所有员工信息。显示员工信息和部门名称，没有员工的部门也要显示

#练习7查询所有员工信息。显示员工姓名，员工工资，职务名称，工资等级，并按工资升序排序

#练习8列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示

#练习9查询入职期早于直接上级的所有员工编号、姓名、部门名称

#练习10查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级

#练习1查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述

SELECT e.id '员工编号', e.ename '员工姓名', e.salary '工资', j.jname '职务名称', j.description '职务描述'

FROM emp e, job j

WHERE e.job_id = j.id;

#练习2查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

SELECT e.id '员工编号', e.ename '员工姓名', e.salary '工资', j.jname '职务名称',

j.description '职务描述', d.dname '部门名称', d.loc '部门位置'

FROM emp e, job j, dept d

WHERE e.job_id = j.id

AND e.dept_id = d.id;

#练习3查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

SELECT e.ename '员工姓名', e.salary '工资', j.jname '职务名称',

j.description '职务描述', d.dname '部门名称', d.loc '部门位置', s.grade '工资等级'

FROM emp e, job j, dept d, salarygrade s

WHERE e.job_id = j.id

AND e.dept_id = d.id

AND e.salary

BETWEEN s.losalary AND s.hisalary;

#练习4查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

SELECT e.ename '员工姓名', e.salary '工资', j.jname '职务名称',

j.description '职务描述', d.dname '部门名称', d.loc '部门位置', s.grade '工资等级'

FROM emp e, job j, dept d, salarygrade s

WHERE e.job_id = j.id

AND e.dept_id = d.id

AND e.salary

BETWEEN s.losalary AND s.hisalary

AND j.jname='经理';

#练习5查询出部门编号、部门名称、部门位置、部门人数

SELECT d.id '部门编号', d.dname '部门名称', d.loc '部门位置', COUNT(*) '部门人数'

FROM dept d, emp e

WHERE d.id = e.dept_id

GROUP BY d.id;

#练习6查询所有员工信息。显示员工信息和部门名称，没有员工的部门也要显示

SELECT e.*, d.dname

FROM dept d

LEFT JOIN emp e

ON e.dept_id = d.id;

#练习7查询所有员工信息。显示员工姓名，员工工资，职务名称，工资等级，并按工资升序排序

SELECT e.ename '员工姓名', e.salary '员工工资', j.jname '职务名称', s.grade '工资等级'

FROM emp e, job j, salarygrade s

WHERE e.job_id = j.id

AND e.salary

BETWEEN s.losalary AND s.hisalary

ORDER BY e.salary ASC;

#练习8列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示(自查询)

SELECT e.ename '员工姓名', m.ename '直接上级领导'

FROM emp e

LEFT JOIN emp m

ON e.mgr = m.id;

#练习9查询入职期早于直接上级的所有员工编号、姓名、部门名称

SELECT e.id '员工编号', e.ename '姓名', d.dname '部门名称'

FROM emp e, emp m, dept d

WHERE e.mgr = m.id

AND e.dept_id = d.id

AND e.joindate < m.joindate;

#练习10查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级

#平均工资

SELECT AVG(salary) FROM emp;

SELECT e.*, d.dname '部门名称', m.ename '上级领导', s.grade '工资等级'

FROM emp e

LEFT JOIN emp m ON e.mgr = m.id -- 直接上级领导

INNER JOIN dept d ON e.dept_id = d.id -- 部门信息

INNER JOIN salarygrade s ON e.salary

BETWEEN s.losalary AND s.hisalary -- 工资等级

AND e.salary > (SELECT AVG(salary) FROM emp);

posted @ 2020-09-15 19:16 master_hxh 阅读(183) 评论(0) 编辑收藏举报

刷新页面返回顶部

master_hxh

自学MySQL第二天

MySQL进阶

一、 约束

（一）主键约束

（二）唯一约束

（三）非空约束

（四）默认约束

（五）外键约束

（六）自动增长-策略

二、 表关系

（一）一对一关联

（二）一对多关联

（三）多对多关联

（四）实战演练

三、 连接查询

四、 聚合函数

五、 分组查询

六、 子查询

七、 数据库的备份和恢复

八、 SQL多表查询综合练习

公告

　　MySQL进阶

一、约束

二、表关系

三、连接查询

四、聚合函数

五、分组查询

六、子查询

七、数据库的备份和恢复