-- 创建数据库
create database 库名
-- 删除数据库
drop database 库名
-- 查看所有表
show tables
-- 创建表(student(整数id,字符串name,整数age))
create table student(
id int,
name varchar(20),
age int
)
-- 删除学生表
drop table student;
-- 插入数据
insert into 表名 values(值)
-- 修改数据
update 表名 set 字段名=值 where 条件
-- 删除数据
delete from 表名 where 条件
说明 | 关键字 |
---|---|
定义别名 | as |
去除重复 | distinct |
在一个指定的范围之类,包头又包尾 | between … and |
查询集合中任何一个元素 | in |
模糊查询 | like % _ |
为空,判断是否为空 | IS NULL |
聚合函数 |
---|
sum() |
count() |
max() |
min() |
avg() |
功能 | 关键字 |
---|---|
排序(升序/降序) | order by … asc/desc |
分组查询 | group by … having |
分页查询 | limit 起始行,返回行数 |
约束名 | 约束关键字 |
---|---|
主键 | primary key |
唯一 | unique |
非空 | not null |
默认 | default |
外键 | foreign key … references |
指定某一列不能出现相同的值
在建表的时候创建
create table 表名 (
字段名 字段类型 unique
)
-- 创建学生表st3, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
create table st3 (
id int,
name varchar(20) unique
);
desc st3;
-- 添加一个同名的学生
select * from st3;
insert into st3 values(1,'张三');
-- 添加重复的: Duplicate entry '张三' for key 'name'
insert into st3 values(2,'张三');
设置某列数据不能为空,必须要输入
create table 表名 (
字段名 字段类型 not null
)
-- 创建表学生表st4, 包含字段(id,name,gender)其中name不能为NULL
create table st4(
id int,
name varchar(20) not null,
age int
);
desc st4;
-- 添加一条记录其中姓名不赋值
-- Column 'name' cannot be null
insert into st4 values(1,null,20);
select * from st4;
-- Field 'name' doesn't have a default value
insert into st4 (id,age) values(1,30);
如果一个字段没有设置它的值,将使用默认值
create table 表名 (
字段名 字段类型 default 默认值
)
-- 创建一个学生表 st5,包含字段(id,name,address), 地址默认值是广州
create table st5(
id int,
name varchar(20),
address varchar(30) default '广州'
);
desc st5;
-- 添加一条记录,使用默认地址
select * from st5;
insert into st5 (id,name) values (1,'张三');
insert into st5 values (1,'张三',default);
-- 添加一条记录,不使用默认地址
insert into st5 values(2,'李四','深圳');
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT , -- 主键自增
NAME VARCHAR(20) NOT NULL , -- 非空约束
addr VARCHAR(20) DEFAULT '广州', -- 默认约束
email VARCHAR(20) UNIQUE -- 唯一约束
)
主键自增约束
id INT PRIMARY KEY AUTO_INCREMENT , – 主键自增 包含了非空和唯一约束!!
唯一约束的关键字是?
unique:不包含非空!!可以为NULL或者空字符。
非空约束的关键字是?
not null 只是不能是NULL 其他都可以
默认值的关键字是?
default
为什么要外键约束
外键的概念:在一张表建立一个字段关联另一张表的主键,这个字段称为外键。
创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加5条数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3V5sCDil-1594611788136)(assets/1562061151559.png)]
-- 创建一个员工表
create table emp (
id int primary key auto_increment, -- 主键自增长
`name` varchar(20),
age int,
dep_name varchar(10), -- 部门名
dep_location varchar(20) -- 部门所在城市
);
select * from emp;
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
结论:单张表存储信息可能出现大量数据的冗余,例如此表中部门信息大量冗余。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yRVQriUX-1594611788140)(assets/1562061151560.png)]
/*
这种表设计上有问题:
1. 会出现大量重复的数据
2. 会出现修改或删除的问题,如:更新2号员工:销售部 广州。如果一个部门只有一个员工
*/
delete from emp where id = 7;
-- 解决方法:将部门设计成一张表
create table department (
id int primary key auto_increment,
dep_name varchar(10), -- 部门名
dep_location varchar(20) -- 部门所在城市
);
-- 员工表
create table employee (
id int primary key auto_increment, -- 主键自增长
`name` varchar(20),
age int,
dept_id int -- 外键,引用部门表中主键
);
-- 添加部门表的记录
insert into department values(null,'研发部', '广州'),(null, '销售部', '深圳');
select * from department;
-- 添加员工信息
INSERT INTO employee (NAME, age, dept_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dept_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dept_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dept_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dept_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dept_id) VALUES ('小王', 18, 2);
select * from employee;
-- 违反外键约束
INSERT INTO employee (NAME, age, dept_id) VALUES ('小乔', 18, 6);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r9y5qeLt-1594611788144)(assets/1562061433448.png)]
解决方案: 必须对外键添加约束
一个部门包含多个员工,反之一个员工只属于一个部门。部门是一方,员工是多方。
主表: 1方是主表,包含主键。
从表: 多方是从表,包含外键
什么是外键:一定是出现在从表中。被主表中主键约束的。
创建表的时候,先创建主键,再创建从表。删除表的时候先删除从表,再删除主表。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GTi2wuom-1594611788149)(assets/1562061433449.png)]
新建表时增加外键:
create table 表名 (
外键字段名 字段类型,
foreign key(外键字段名) references 主表(主键)
)
已有表增加外键:
alter table 表名 add constraint 约束名 foreign key(外键字段名) references 主表(主键)
-- 约束名用于后期删除外键约束
-- 删除从表 employee
drop table employee;
-- 创建从表 employee 并添加外键约束emp_depid_fk
create table employee (
id int primary key auto_increment, -- 主键自增长
`name` varchar(20),
age int,
dept_id int, -- 外键,引用部门表中主键
foreign key (dept_id) references department(id)
);
-- 部门错误的数据添加失败
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dept_id) VALUES ('小乔', 18, 6);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fSuQnH6H-1594611788152)(assets/1563329669857.png)]
-- 删除employee表的employee_ibfk_1外键
alter table employee drop foreign key employee_ibfk_1;
-- 在employee表情存在的情况下添加外键
alter table employee add foreign key (dept_id) references department(id);
外键的作用:外键约束降低数据冗余的同时,还对数据的一致性和准确性有强力的约束。
创建外键约束的语法是什么?
constraint 外键别名 foreign key(外键) references 主表(主键)
主表:部门表。
从表:员工表。
部门和员工:一对多的关系。会把1的一方的主键拿来作为多的一方的外键。
建立外键约束有几种方式?2种
建表后:
-- 约束名用于后期删除外键约束
alter table 表名 add constraint 约束名 foreign key(外键字段名) references 主表(主键)
alter table 表名 add foreign key(外键字段名) references 主表(主键)
建表时:
-- 创建从表 employee 并添加外键约束emp_depid_fk
create table employee (
id int primary key auto_increment, -- 主键自增长
`name` varchar(20),
age int,
dept_id int, -- 外键,引用部门表中主键
-- constraint 约束名 foreign key (dept_id) references department(id)
-- 如果申明外键没有给外键约束名称,默认有默认名称:employee_ibfk_1
foreign key (dept_id) references department(id)
);
删除employee表的employee_ibfk_1外键
alter table employee drop foreign key employee_ibfk_1;
select * from department;
select * from employee;
-- Cannot delete or update a parent row: a foreign key constraint fails (不能直接修改主表中主键)
update department set id=5 where id=2;
-- 要删除部门id等于1的部门, 能不能直接删除呢?
-- Cannot delete or update a parent row: a foreign key constraint fails(如果有从表引用了主表中主键,主表中这条记录不能删除)
delete from department where id=1;
修改主表中主键,或者删除主表中记录的时候,从表中外键也被同时修改,或者删除。
写在外键约束的后面,在创建外键约束的时候创建级联操作
级联操作 | 语法 |
---|---|
级联更新 | on update cascade |
级联删除 | on delete cascade |
-- 删除外键约束
alter table employee drop foreign key employee_ibfk_1;
-- 添加外键约束,级联更新和级联删除
alter table employee add constraint fk_emp_dept
foreign key (dept_id) references department(id) on update cascade on delete cascade;
-- delete from employee where id > 6;
select * from employee;
-- 把部门表中id等于1的部门改成id等于9
select * from department;
update department set id=9 where id=1;
-- 删除部门号是2的部门
delete from department where id=2;
级联操作 | 语法 |
---|---|
级联更新 | on update cascade |
级联删除 | on delete cascade |
我们现在默认使用的都是root用户,超级管理员,拥有全部的权限。但是,一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以,我们应该可以根据不同的项目建立不同的用户,分配不同的权限来管理和维护数据库。
create user '用户名'@'主机名' identified by '密码'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IbW7xjsh-1594611788155)(assets/1562158759012.png)]
创建user1用户,只能在localhost这个服务器登录mysql服务器,密码为123
创建user2用户可以在任何电脑上登录mysql服务器,密码为123
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wj0UG1Al-1594611788157)(assets/1563332035884.png)]
注:创建的用户名都在mysql数据库中的user表中可以查看到,密码经过了加密。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cAksOsCu-1594611788160)(assets/1563332059612.png)]
用户创建之后,没什么权限!需要给用户授权
grant 权限 on 数据库名.表名 to '用户名'@'主机名'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FKwnY5uX-1594611788162)(assets/1562158784637.png)]
给user1用户分配对test这个数据库操作的权限:创建表,修改表,插入记录,更新记录,查询
给user2用户分配所有权限,对所有数据库的所有表 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DhhwcKPb-1594611788164)(assets/1563332503308.png)]
revoke 权限 on 数据库名.表名 from '用户名'@'主机名'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FuJ45qEJ-1594611788166)(assets/1562158894949.png)]
撤销user1用户对test数据库所有表的操作的权限
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yqaA8okE-1594611788169)(assets/1563332595494.png)]
注:用户名和主机名要与创建时相同,各自要加上单引号
drop user '用户名'@'主机名'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eeFN8Xv4-1594611788172)(assets/1563333895288.png)]
在DOS命令行下执行:
mysqladmin.exe 在bin文件夹下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K8ZH1Xud-1594611788174)(assets/1562159677067.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KrsvEGm6-1594611788176)(assets/1563334038685.png)]
登录mysql使用
set password=password('密码')
注:用户登录后操作
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CDNb7qjB-1594611788178)(assets/1563334141621.png)]
在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补与估量的。
注:这个操作不用登录,也是bin目录下可执行文件
mysqldump -u用户名 -p密码 数据库名 > 文件名
注:这个操作需要登录,这是mysql中命令
use 数据库;
source 还原文件名;
-- 数据还原到当前的数据库中
备份day15数据库中的数据到d:\day15.sql文件中
mysqldump -uroot -proot day15 > d:/day15.sql
还原day15数据库中的数据,注意:还原的时候需要先登录MySQL,并选中对应的数据库。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3hMKuO05-1594611788181)(assets/1563334699726.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7x039h4E-1594611788183)(assets/1562030233396.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QwX5jy4q-1594611788185)(assets/1562030253423.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zyzG6nOc-1594611788187)(/assets/1562030289605.png)]
备份数据库的目的是什么?
防止数据丢失
备份有两种方式:
一种规则,指导程序员创建表的规则。数据库的范式有6种范式,程序员在设计表的时候,只需要符合三大范式就可以了。
分成几个等级,一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的。
满足最低要求的范式是第一范式(1NF),在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。
原子性:表中每一列都不可以再分割成更小的列。如果一列中包含的数据可以再拆分。
第一范式:满足列不可再分。
学号 | 姓名 | 班级信息 |
---|---|---|
1000 | 张三 | 一年级3班 |
1001 | 李四 | 一年级2班 |
2001 | 王五 | 二年级1班 |
学号 | 姓名 | 年级 | 班号 |
---|---|---|---|
1000 | 张三 | 一年级 | 3班 |
什么是第一范式?
原子性:每一列不可再拆分,每一列都是唯一字段值。
什么是第二范式
在满足第一范式的基础上,有更多的要求,才是第二范式。表中每一列都必须完全依赖于主键,
主键 | 学生证号 | 学生证名称 | 学生证办理时间 | 借书证号 | 借书证名称 | 借书证办理时间 |
---|---|---|---|---|---|---|
1 | A2349U | 张三学生证 | 2018-01-23 | 29374 | 张三借书证 | 2019-03-10 |
什么是第二范式?
表中每列都是完全依赖于主键的。
什么是第三范式
在满足第二范式的基础上,有更多的要求。 表中每列都直接依赖于主键,而不是通过其它列间接依赖于主键。
所谓传递依赖,指的是如果存在"A → B → C"的决定关系,则C传递依赖于A。
满足第三范式的数据库表应该不存在如下依赖关系:主键列 → 非主键列x → 非主键列y
学号 | 姓名 | 年龄 | 所在学院 | 学院地点 |
---|---|---|---|---|
1000 | 张三 | 20 | 传智专修学院 | 江苏 |
2000 | 李四 | 19 | 广州美术学院 | 广州 |
存在传递的决定关系:学号 -> 学院 -> 地点
从表:有外键 (多方)
主键 | 姓名 | 年龄 | 所在学院ID |
---|---|---|---|
从表 | 外键 |
学院ID | 学院名字 | 学院地点 |
---|---|---|
主表(1方) |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4SSNnjQ9-1594611788190)(assets/1563336139925.png)]
注:表使用的范式越高级,被拆分成表就越多。表过多,数据操作的性能会下降!
反三范式的概念:在性能和数据库规范下做折中!有时候为了追求性能可能会反三范式设计!!
第一范式:列不可拆分。原子性。
第二范式:满足第一范式,同时每列都依赖于主键:建立主键。
第三范式:不存在传递依赖,拆表(一张表只描述一件事)
降低数据的冗余,维护好数据的准确性。
表存储数据,数据就是实现生活中各种对象的抽象。
现实生活中,实体与实体之间肯定是有关系的。
比如:部门和员工,老师和学生等。
那么我们在设计表的时候,就应该体现出表与表之间的这种关系!
总结:表与表的关系可以实现数据的联系,准确性,某种程度上降低冗余。
记录之间的对应关系 | 表与表之间的三种关系 |
---|---|
A表和B表:A表中一条记录对应B表中一条记录。如:身份证 - 护照 | 一对一 |
A表1方,B表多方: A表中一条记录对应了B表中多条记录。如:部门 - 员工 B表中一条记录对应了A表中一条记录。 |
一对多 |
A表中一条记录对应了B表中多条记录 B表中一条记录对应了A表中多条记录 如: 学生与老师是多对多的关系。 |
多对多 |
一对一(1:1) 例如:
A表中一条记录对应B表中一条记录: 学生表 简历表。
直接建立外键即可。随便找一张表的主建做为另一张表的外键参考即可。
或者直接合并两张表。
一对多(1:n) 例如:部门与员工关系。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Aaw5cK5-1594611788192)(assets/1562159677068.png)]
1对N建表的原则: 拿1的一方的主键作为多的 一方的外键
多对多(m:n) 例如:学生和课程 (50 10)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aVUGTFSZ-1594611788194)(assets/1562159677069.png)]
多对多关系建表原则:建立中间表,把两方的主键拿来作为中间表的联合主键。
表与表之间有哪三种关系?
创建1对1的表关系
一对一(1:1) 在实际的开发中应用不多,因为一对一可以创建成一张表。
两种建表原则:
一对一的建表原则 | 说明 |
---|---|
外键唯一 | 将从表的外键添加唯一约束,变成了一对一的关系。其实是一个特殊的一对多的关系。 |
外键是主键 |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ESID33zw-1594611788196)(assets/1562221725724.png)]
-- 主表
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
NAME VARCHAR(20)
);
-- 从表
CREATE TABLE info(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
address VARCHAR(20),
use_name VARCHAR(10),
weight DOUBLE,
-- 直接把主键约束成外键。 主键也可以是外键。
FOREIGN KEY(id) REFERENCES stu(id)
);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZVQ8KS7a-1594611788198)(assets/1563346173059.png)]
一对一有哪两种创建方式?
实现一个"线路分类"中有多个"旅游线路"的一对多的关系 。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9RTeHMjF-1594611788202)(assets/1562222089416.png)]
route 线路 , category 分类。
一个分类对应多条线路:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tT15IKwV-1594611788205)(assets/1562222126818.png)]
/*
一对多,一个分类对应多条线路
因为sql中不区分大小写:如果有多个单词,中间使用_分隔
*/
/*
创建旅游线路分类表tab_category
cid旅游线路分类主键,自动增长
cname旅游线路分类名称非空,唯一,字符串100
*/
create table tab_category(
cid int primary key auto_increment,
cname varchar(100) not null unique
);
/*
创建旅游线路表tab_route
rid旅游线路主键,自动增长
rname旅游线路名称非空,唯一,字符串100
price价格
rdate 上架时间,日期类型
cid 外键,所属分类
*/
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int, -- 外键
foreign key(cid) references tab_category(cid)
);
1对多的关系在表中通过什么来维护?
把1的一方的主键作为多的一方的外键。
一个用户收藏多个线路,一个线路被多个用户收藏,建表体现线路与用户之间的关系
用户与线路之间是多对多的关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KotekBZm-1594611788208)(assets/1562222326359.png)]
对于多对多的关系我们需要增加一张中间表来维护他们之间个关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lDarEKXw-1594611788211)(assets/1562222362257.png)]
-- 多对多的关系
/*
创建用户表tab_user
uid用户主键,自增长
username用户名长度100,唯一,非空
password密码长度30,非空
name真实姓名长度100
birthday生日
sex性别,定长字符串1
telephone手机号,字符串11
email邮箱,字符串长度100
*/
create table tab_user(
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1),
telephone varchar(11),
email varchar(100)
);
/*
创建收藏表tab_favorite
rid 旅游线路id,外键
date 收藏时间
uid用户id,外键
rid和uid不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int, -- 线路id的外键
`date` datetime,
uid int , -- 用户id的外键
-- 创建复合主键
primary key(rid, uid),
foreign key(rid) references tab_route(rid), -- 关联了线路的主键
foreign key(uid) references tab_user(uid) -- 关联了用户的主键
);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gqt9FCCk-1594611788214)(assets/1563348042355.png)]
表与表的关系 | 关系的维护 |
---|---|
一对多 | 通过从表外键来维护 |
多对多 | 通过中间表,把两表的主键拿来作为中间表的联合主键。 |
一对一 | 1. 从表的外键唯一 2.从表的主键又是外键 3.也可以直接合并成一张表即可 |
三范式并不是绝对的,有时候需要反三范式:涉及到查询性能的时候,为了提高查询性能。
准备数据:
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept (name) values ('开发部'),('市场部'),('财务部');
# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键)
);
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);
查询某员工在哪个部门?
只查询一张表不能查询出员工名字和部门名字,需要使用多表操作。
完成多表操作的两种方式:
隐式内连接:不出现join关键字
显示内连接:出现join…on关键字
左外连接,通常又叫左连接。右外连接通常又叫右连接。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bjs9FxqG-1594611788217)(assets/1562224531368.png)]
-- 需求:查询所有的员工和所有的部门
部门是左表,员工是右表。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aBLFFrba-1594611788221)(assets/1562224609615.png)]
我们发现不是所有的数据组合都是有用的,只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。
-- 查询孙悟空在哪个部门名字
select * from emp;
select * from dept;
-- 需求:查询所有的员工和所有的部门
-- 查询2张表结果是,是2张表记录的乘积,称为笛卡尔积
select * from emp,dept;
-- 如何消除笛卡尔积:条件是从表.外键=主表.主键
select * from emp,dept where emp.dept_id = dept.id;
-- 这就是隐式内连接,使用where,没有用到join...on
-- 给表起别名
select * from emp e ,dept d where e.dept_id = d.id;
-- 查询孙悟空在哪个部门名字
select * from emp e ,dept d where e.dept_id = d.id and e.id=1;
-- 只显示2列
select e.name 员工名,d.name 部门名 from emp e ,dept d where e.dept_id = d.id and e.id=1;
select 列名 from 左表,右表 where 从表.外键=主表.主键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i0khohRP-1594611788223)(assets/1563349640578.png)]
-- 显示内连接, on后面就是表连接的条件
select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键
查询唐僧的信息,显示员工id,姓名,性别,工资和所在的部门名称
-- 查询唐僧的信息,显示员工id,姓名,性别,工资和所在的部门名称
-- 1. 确定查询哪些表
select * from emp e inner join dept d;
-- 2. 确定表连接的条件
select * from emp e inner join dept d on e.dept_id = d.id;
-- 3. 如果有其它的查询条件,添加条件
select * from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';
-- 4. 确定查询哪些列
select e.id 编号, e.name 姓名, e.gender 性别, e.salary 工资, d.name 部门名
from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';
笛卡尔积现象:左表中所有的记录乘以右边所有记录:交叉组合,形成大量错误的组合数据。
select * from employee , dept;
内连接的两种方式:可以解决笛卡尔积现象
显示:inner join … on
SELECT 列名 FROM 左表 INNER JOIN 右表 ON 从表.外键=主表.主键
隐式:条件跟在where的后面
SELECT 列名 FROM 左表,右表 WHERE 从表.外键=主表.主键
select 列名 from 左表 left join 右表 on 从表.外键=主表.主键
-- 需求:在部门表中增加一个销售部,需要查询所有的部门和员工,将部门表设置成左表,员工表设置成右表
select * from dept;
insert into dept values(null,'销售部');
-- 使用内连接查询
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用左外连接查询
select * from dept d left join emp e on d.id = e.dept_id;
-- 左连接:左表中所有的记录都出现在结果,如果右表没有匹配的记录,使用NULL填充
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SPqImMbm-1594611788227)(assets/1562225317114.png)]
select 列名 from 左表 right join 右表 on 从表.外键=主表.主键
-- 右连接
select * from emp;
-- 需求:在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null
insert into emp values(null, '沙僧','男',6666,'2013-02-24',null);
-- 希望员工的信息全部显示出来
-- 使用内连接查询
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用右外连接查询
select * from dept d right join emp e on d.id = e.dept_id;
右连接:保证右表中所有的数据都出现,如果左表没有对应的记录,使用NULL填充
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zw0kZ0hy-1594611788230)(assets/1562225378984.png)]
什么是左连接,关键字是什么?
在内连接的基础之上保证左表的数据都出现,left join
什么是右连接,关键字是什么?
在内连接的基础之上保证右表的数据都出现,right join
-- 需求:查询开发部中有哪些员工
select * from emp;
select id from dept where name='开发部';
select * from emp where dept_id=1;
-- 写成一句:使用子查询
select * from emp where dept_id=(select id from dept where name='开发部');
select * from emp where dept_id=(select id from dept where name='市场部');
单行单列:一个值
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XGkiA5vZ-1594611788233)(assets/1562225585954.png)]
多行单列:可以认为是一个数组
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uHyWasrB-1594611788235)(assets/1562225605676.png)]
多行多列:可以认为是一张虚拟表,我们可以二次查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pfpq5pAm-1594611788238)(assets/1562225632007.png)]
如果子查询是单行单列,父查询使用比较运算符:> < =
-- 案例:查询工资最高的员工是谁?
-- 1. 查询最高工资是多少
select max(salary) from emp;
-- 2. 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary=(select max(salary) from emp);
-- 查询工资大于"蜘蛛精"的员工
-- 1. 查询蜘蛛精的工资是多少
select salary from emp where name='蜘蛛精';
-- 2. 查询大于这个工资的员工
select * from emp where salary > (select salary from emp where name='蜘蛛精');
单行单列的结果,父查询使用什么运算符?
使用比较运算符: > < = !=
多行单列的子查询如何操作
多行单列认为是一个数组,父查询使用in /any /all
-- 查询工资大于5000的员工,来自于哪些部门,得到部门的名字
-- 1. 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;
-- 2. 再查询在这些部门id中部门的名字
-- Subquery returns more than 1 row
select * from dept where id=(select dept_id from emp where salary > 5000);
select * from dept where id in(select dept_id from emp where salary > 5000);
-- 列出工资高于在1号部门工作的所有员工,显示员工姓名和工资、部门名称。
-- 1. 查询1号部门所有员工的工资,得到多行单列
select salary from emp where dept_id=1;
-- 2. 使用大于号不能计算,怎么办
select * from emp where salary > all (select salary from emp where dept_id=1);
-- any表示任何一个,all所有
select * from emp where salary > any (select salary from emp where dept_id=1);
多行单列的子查询,父查询使用什么关键字?
in, any, all
多行多列的子查询如何操作
认为它是一张虚拟表,可以使用表连接再次进行多表查询
子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段
-- 查询出2011年以后入职的员工信息,包括部门名称
-- 1. 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date > '2011-1-1';
-- 2. 查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门id等于的dept_id
select * from dept d inner join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;
select * from dept d right join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;
多行多列的子查询如何操作的?
可以做成一张虚拟表再次进行查询,或者继续进行表连接
通过5个案例学习多表连接查询
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY, -- 部门id
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);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h2G3WXff-1594611788244)(assets/1562309009547.png)]
查询所有员工姓名,工资,工资等级
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UAa6uvZD-1594611788247)(assets/1562309171440.png)]
-- 查询所有员工姓名,工资,工资等级
-- 1. 确定查询哪些表
select * from emp e inner join salarygrade s;
-- 2. 确定表连接的条件
select * from emp e inner join salarygrade s on e.salary between s.losalary and s.hisalary;
-- 3. 确定查询哪些列
select e.ename 姓名, e.salary 工资, s.grade 工资等级
from emp e inner join salarygrade s on e.salary between s.losalary and s.hialary;
查询经理的信息。显示经理姓名,工资,职务名称,部门名称,工资等级
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QtbAMOkM-1594611788252)(assets/1562309276885.png)]
select e.ename 姓名, e.salary 工资, j.jname 职务, d.dname 部门名, s.grade 工资等级
from emp e inner join dept d on e.dept_id = d.id
inner join job j on j.id = e.job_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary where j.jname='经理';
查询部门编号、部门名称、部门位置、部门人数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iRIYnJJp-1594611788256)(assets/1562309368651.png)]
select d.*, count(e.dept_id) 部门人数 from dept d left join emp e on d.id = e.dept_id group by d.id;
列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示
不是一种新的连接,左表与右表是同一张表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f8xjU18Y-1594611788259)(assets/1562309544494.png)]
select e.ename 员工名字, IFNULL(m.ename,'BOSS') 上级名字 from emp e left join emp m on e.mgr = m.id;
查询工资高于公司平均工资的所有员工列:员工所有信息,部门名称,上级领导,工资等级。
注:所有员工都要显示出来,没有上级的员工显示为"自己"
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Hm7LihL-1594611788262)(assets/1562309634144.png)]
-- 查询工资高于公司平均工资 员工所有信息,部门名称,上级领导,工资等级。没有上级的员工显示为"自己"
-- 查询公司平均工资
select avg(salary) from emp;
-- 1. 确定查询哪些表:员工表,员工表,部门表,工资等级
select * from emp e left join emp m on e.mgr = m.id inner join dept d on d.id = e.dept_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary;
-- 2. 确定表连接的条件
-- 3. 确定查询哪些列
select e.*,d.dname 部门名, ifnull(m.ename,'自己') 上级名字, s.grade 工资等级 from emp e left join emp m on
e.mgr = m.id inner join dept d on d.id = e.dept_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary where e.salary > (select avg(salary) from emp);
通常关联字段:主表.主键=从表.外键,也不一定。
关联的条件数 = 表数量 - 1
多表查询的步骤:
能够使用SQL语句添加主键、外键、唯一、非空约束
约束名 | 约束关键字 |
---|---|
主键 | primary key |
非空 | not null |
唯一 | unique |
外键 | foreign key(外键列) references 主表(主键) |
默认 | default |
能够使用DCL处理MySQL中的用户(了解即可)
能够理解三大范式
范式 | 特点 |
---|---|
第一范式 | 原子性:表中每列不可再拆分 |
第二范式 | 表中每列完全依赖于主键。 |
第三范式 | 不产生传递依赖,开始拆表,表中和每列都是直接依赖于主键,建立外键关联数据 |
能够说出多表之间的关系及其建表原则
表与表的关系 | 关系的维护 |
---|---|
一对多 | 拿1的一方的主键作为多的一方的外键。 |
多对多 | 创建一张中间表,拿两方的主键作为中间表的联合主键。 |
一对一 | 1. 外键约束设置唯一 2. 从表的主键又是外键 |
能够使用内连接进行多表查询
-- 1.隐式内连接语法: select 列名 from 左表,右表 where 从表.外键=主表.主键
SELECT * FROM employee , dept WHERE employee.`dept_id` = dept.id;
-- 2.显式内连接语法: select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键
SELECT * FROM employee INNER JOIN dept ON employee.`dept_id` = dept.`id`;
-- inner可以省略不写
SELECT * FROM employee JOIN dept ON employee.`dept_id` = dept.`id`;
能够使用左外连接和右外连接进行多表查询
select 列名 from 左表 left join 右表 on 条件
select 列名 from 左表 right join 右表 on 条件
能够使用子查询进行多表查询
能够理解多表查询的规律
-- a.确定表:员工表,部门,等级表
-- b.简单粗暴连接表。
– c.加条件 注意:边连表边加条件。
ue |
| **外键** | foreign key(外键列) references 主表(主键) |
| **默认** | default |
2. 能够使用DCL处理MySQL中的用户(了解即可)
1. 创建用户:create user '用户名'@'主机名' identified by '密码'
2. 授权:grant 权限 on 库名.表名 to '用户名'@'主机名'
3. 撤销:revoke 权限 on 库名.表名 from '用户名'@'主机名'
4. 删除用户:drop user '用户名'@'主机名'
3. 能够理解三大范式
| **范式** | **特点** |
| ------------ | ------------------------------------------------------------ |
| **第一范式** | 原子性:表中每列不可再拆分 |
| **第二范式** | 表中每列完全依赖于主键。 |
| **第三范式** | 不产生传递依赖,开始拆表,表中和每列都是直接依赖于主键,建立外键关联数据 |
4. 能够说出多表之间的关系及其建表原则
| **表与表的关系** | **关系的维护** |
| ---------------- | -------------------------------------------------- |
| **一对多** | 拿1的一方的主键作为多的一方的外键。 |
| **多对多** | 创建一张中间表,拿两方的主键作为中间表的联合主键。 |
| **一对一** | 1. 外键约束设置唯一<br />2. 从表的主键又是外键 |
5. 能够使用内连接进行多表查询
```mysql
-- 1.隐式内连接语法: select 列名 from 左表,右表 where 从表.外键=主表.主键
SELECT * FROM employee , dept WHERE employee.`dept_id` = dept.id;
-- 2.显式内连接语法: select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键
SELECT * FROM employee INNER JOIN dept ON employee.`dept_id` = dept.`id`;
-- inner可以省略不写
SELECT * FROM employee JOIN dept ON employee.`dept_id` = dept.`id`;
能够使用左外连接和右外连接进行多表查询
select 列名 from 左表 left join 右表 on 条件
select 列名 from 左表 right join 右表 on 条件
能够使用子查询进行多表查询
能够理解多表查询的规律
-- a.确定表:员工表,部门,等级表
-- b.简单粗暴连接表。
– c.加条件 注意:边连表边加条件。
本文地址:https://blog.csdn.net/iceking7/article/details/107314053
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论