sailan
- 一、数据库软件的由来
- 二、数据库概述
- 2.1 什么是数据库(DataBase,简称DB)
- 2.2 数据库管理系统(DataBase Management System,简称DBMS)
- 2.3 数据库服务器、数据管理系统、数据库、表与记录的关系
- 2.4 数据库管理技术发展史
- 三、MySQL介绍
- 3.1 MySQL是什么
- 3.2 安装及预备操作知识
- 3.2.1 下载与安装
- 3.2.2 做成系统服务
- 3.2.3 基础操作命令
- 3.2.4 忘记密码怎么办
- 3.2.5 统一编码修改配置文件
- 四、基本sql语句
- 4.1 针对库的增删改查
- 4.2 针对表的增删改查
- 4.3 针对数据的增删改查
- 五、数据类型
- 六、条件约束
- 七、查询数据
- 7.1 单表查询
- 7.1.1 where
- 7.1.2 分组查询:GROUP BY
- 7.1.3 聚合函数
- 7.1.4 HAVING过滤
- 7.1.5 查询排序:ORDER BY
- 7.1.6 限制查询记录数:LIMIT
- 7.2 多表查询
- 7.2.1 多表查询
- 7.2.2 子查询
- 7.2.3 把多张表链接起来
- 查询练习
一、数据库软件的由来
数据想要永久保存都是存于硬盘,文件则是操作系统提供给我们操作数据的接口,一个文件只能存在于一台机器上,如果暂且忽略直接基于文件来存取数据的效率问题,并且假设程序所有的组件都运行在一台机器上,那么用文件存取数据,并没有问题,但并不现实,以下列出几个问题;
- 程序所有的组件都运行在一台机器上可能会出现的问题;
这台机器一旦挂掉则意味着整个软件的崩溃,并且程序的执行效率依赖于承载它的硬件,而一台机器机器的性能总归是有限的,受限于目前的硬件水平,就一台机器的性能垂直进行扩展是有极限的。
于是我们只能通过水平扩展来增强我们系统的整体性能,这就需要我们将程序的各个组件分布于多台机器去执行。
- 数据安全问题
根据上例1的描述,我们将程序的各个组件分布到各台机器,但需知各组件仍然是一个整体,言外之意,所有组件的数据还是要共享的。但每台机器上的组件都只能操作本机的文件,这就导致了数据必然不一致。
于是我们想到了将数据与应用程序分离:把文件存放于一台机器,然后将多台机器通过网络去访问这台机器上的文件(用socket实现),即共享这台机器上的文件,共享则意味着竞争,会发生数据不安全,需要加锁处理。
- 需支持并发
根据2的描述,我们必须写一个socket服务端来管理这台机器(数据库服务器)上的文件,然后写一个socket客户端,完成如下功能:
1.远程连接(支持并发)
2.打开文件
3.读写(加锁)
4.关闭文件
小结:
我们在编写任何程序之前,都需要事先写好基于网络操作一台主机上文件的程序(socket服务端与客户端程序),于是有人将此类程序写成一个专门的处理软件,这就是mysql等数据库管理软件的由来,但mysql解决的不仅仅是数据共享的问题,还有查询效率,安全性等一系列问题,把程序员从数据管理中解脱出来。
二、数据库概述
2.1 什么是数据库(DataBase,简称DB)
数据库数据库如其名就是存放数据的仓库,仓库也就指的是计算机的存储设备,描述事物的符号记录称为数据(data),描述事物的符号既可以是数字,也可以是文字、图片,图像、声音、语言等,数据由多种表现形式,它们都可以经过数字化后按一定格式存入计算机,
过去人们将数据存放在文件柜里,现在数据量庞大,已经不再适用数据库是长期存放在计算机内、有组织、可共享的数据即可。数据库中的数据按一定的数据模型组织、描述和储存,具有较小的冗余度、较高的数据独立性和易扩展性,并可为各种用户共享。
2.2 数据库管理系统(DataBase Management System,简称DBMS)
数据库管理系统是一类软件的统称,可以科学的组织和存储数据,以及高效地获取和维护数据,如MySQL、Oracle、SQLite、Access、MS SQL Server;
mysql主要用于大型门户,例如搜狗、新浪等,它主要的优势就是开放源代码,因为开放源代码这个数据库是免费的,他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大,软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品,主要应用于大中型企业,如联想、方正等。
2.3 数据库服务器、数据管理系统、数据库、表与记录的关系
记录:
1号小帅皮 sailan 20
多个字段的信息组成一条记录,即文件中的一行内容;
表:
student,scholl,class_list
即文件
数据库:
oldboy_stu
即文件夹
数据库管理系统:
如MySQL、Oracle、SQLite、Access、MS SQL Server
是一个软件
数据库服务器:
一台计算机
对内存有要求
总结:
数据库服务器-:运行数据库管理软件
数据库管理软件:管理-数据库
数据库:即文件夹,用来组织文件/表
表:即文件,用来存放多行内容/多条记录
2.4 数据库管理技术发展史
数据管理技术的发展可以大体归为三个阶段:人工管理、文件系统和数据库管理系统,数据库管理系统是目前最高级的阶段,这里就不过多叙述了详细可百度。
三、MySQL介绍
3.1 MySQL是什么
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统,在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一;
MySQL是开放源代码的,因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下,MySQL是管理内容最好的选择;
mysql是一个基于socket编写的C/S架构的软件,它集成了客户端与服务端于一体;
数据库管理软件分类:
- 关系型: 如sqllite,db2,oracle,access,sql server,MySQL;
- 非关系型: mongodb,redis,memcache;
可简单理解为,关系型数据库需要有表结构,非关系型数据库是key-value存储的,没有表结构。
注意:sql语句通用
3.2 安装及预备操作知识
3.2.1 下载与安装
Windows下载与安装《====点此查看
3.2.2 做成系统服务
在执行【mysqd】启动MySQL服务器时,当前终端会被hang住,需要设置一下即可解决此问题;
注意:--install前,必须用mysql启动命令的绝对路径
# 制作MySQL的Windows服务,在终端执行此命令:
"c:\mysql-5.7.16-winx64\bin\mysqld" --install
# 移除MySQL的Windows服务,在终端执行此命令:
"c:\mysql-5.7.16-winx64\bin\mysqld" --remove
注册成服务之后,以后再启动和关闭MySQL服务时,仅需执行如下命令:
# 启动MySQL服务
net start mysql
# 关闭MySQL服务
net stop mysql
3.2.3 基础操作命令
sql语句标识:
MySQL中的sql语句必须以分号作为结束的标志,也就是语句结尾必须加分号;
基本命令:
show databases;
查看所有库名
链接服务端命令:
MySQL第一次以管理员身份进入是无需密码的
完整命令:mysql -h 127.0.0.1 -p 3306 -uroot -p
简写:mysql -uroot -p
撤销命令: 当输入命令不对时,并且不想让服务端执行返回报错信息,可结尾\c取消 命令
客户端退出命令: quit\exit
设置密码:
mysqladmin -uroot -p 原密码 password 新密码
这个命令直接在终端输入,初始时原密码可留空
刷新:
flush privileges;
将修改后的数据立刻刷入硬盘
其他补充:
链接服务端时只输入mysql也能链接,但此时登入状态为游客;
查看进程
tasklist
tasklist |findstr mysql
杀进程(只在管理员cmd窗口下可执行)
taskkill \F \PID pid号
3.2.4 忘记密码怎么办
我们可以把mysql的用户认证功能看成是个装饰器,装饰在了客户端请求访问的功能上面,我们把这个装饰器移除那么mysql服务端就不会校验用户名与密码;
先关闭mysql服务端,命令行输入下命令启动服务端;
mysql --skip-grand-tables
(让mysql跳过用户认证)
然后就可以无密码登入
修改密码:
update mysql.user set password=password(新密码) where user='root' and host = 'localhost';
刷新
关闭服务端再正常启动
3.2.5 统一编码修改配置文件
程序启动会先加载配置文件里的配置然后启动
[mysql] # 客户端启动加载
[mysqld] # 服务端启动加载
[client] # 其他的客户端
mysql里配置文件需要我们自己新建一个my.ini的文件,修改后需重启服务器才生效;
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
# cv进my.ini即可
四、基本sql语句
4.1 针对库的增删改查
数据库的命名规范:
1、由字母、数字、下划线、@、#、$组成
2、区分大小写
3、唯一性
4、不能使用关键字如 create select
5、不能单独使用数字
6、最长128位
增加:
下例库名统一例为db1;
create database db1;
create database db1 charset='gbk'; # 指定库字符编号
查看:
show databases; # 查看所有库
show create database db1; # 查单个
修改:
alter database db1 charset='utf-8'; # 修改库的编码
删除:
drop database db1 # 删除单个库
4.2 针对表的增删改查
操作表时需切换到指定库下再进行操作;
切换库:
use db1;
查看当前所在库;
select database();
增加:
create table t1(id int,name char(4)); # 这段意思为创建t1表有两个字段,其中id 字段必须为int类型,name字段必须为字符串且不能超过四位;
create table db2.t1(id int); # 可指定路径操作其他库新建
alter table t1 add gender char(4); # 添加字段默认在尾部
alter table t1 add gender char(4) first; # 在开头添加
alter table t1 add level int after ID(); # 指定某个字段后添加
create table t2 select user,host,password from mysql.user.user; # 复制表
create table t2 select user,host,password from mysql.user.user where 1i = 1; # 只复制表结构
查看:
show tables; # 查看当前库下所有表名;
show creare table t1; # 查看单个表;
describe t1; # 查看单个表,以表格形式展出 可简写为desc t1;
修改:
alter table t1 modify name char(10); # 修改表的某个字段信息
alter table t1 rename t2; # 修改表名称
alter table t1 change id ID tinyint,change name NAME char(4); # 修改表的字段信息
删除:
drop table t1; # 删除
4.3 针对数据的增删改查
需先有库再有表才能操作数据;
1. 插入完整数据(顺序插入)
语法一:
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二:
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法:
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法:
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法:
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
增加:
insert into t1 values(1,'sailan');
insert into t1 values(1,'sailan1'),(2,'sailan2'); # 可叠加多个数据
查看:
select * from t1; # 查看t1表下的所有内容 这个命令数据量大时不建议使用
select name from t1; # 把t1表下name字段的内容取出来
修改:
updata t1 set name = 'handsome' where id >1; # 这条意思为把t1表下name字段id大于一的改为handsome,条件可修改;
删除:
delete from t1 where id >1; # 删除表下id大于1的
delete from t1; # 清空表内数据
truncate t1; # 清空表内容同上 推荐
五、数据类型
数据类型
六、条件约束
条件约束
七、查询数据
7.1 单表查询
单表查询的语法:
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
重点中的重点:关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件,去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序:order by
8.限制结果的显示条数
# 简单查询
select name,sex from emp;
select name as 名字,sex 性别 from emp;
select * from emp;
# 避免重复(针对的是记录)
select distinct post from emp;
# 进行四则运算
select name as 名字,salary*12 as 年薪 from emp;
# concat()拼接记录的内容
select name ,concat(salary*12,"$") from emp;
select name ,concat("annual_salary",':',salary*12) as 年薪 from emp;
select name ,concat("annual_salary",':',salary*12,':','$') as 年薪 from emp;
select name ,concat_ws(":","annual_salary",salary*12,'$') as 年薪 from emp; # 设置拼接符
# 其他方法
select (
case
when name="egon" then
name
when name="alex" then
concat(name,"_dsb")
else
concat(name,"_sb")
end
) as 名字 from emp;
7.1.1 where
where字句中可以使用:
- 比较运算符:> < >= <= <> !=
- between 80 and 100 值在10到20之间
- in(80,90,100) 值是10或20或30
- like ‘egon%’
pattern可以是%或_,
%表示任意多字符
_表示一个字符 - 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
使用案例
select * from emp where id >= 3 and id <= 5; # 大于三小于五
select * from emp where id between 3 and 5; # 三到五之间
select * from emp where id not between 3 and 5; # 小于三大于五
select * from emp where id=3 or id=5 or id=7; # 3或5或7
select * from emp where id in (3,5,7); # 3或5或7
select * from emp where id not in (3,5,7); # not3或5或7
# 关键字LIKE模糊匹配
select * from emp where name like 'jin%'; # 通配符’%’匹全部
select * from emp where name like 'jin___'; # 通配符’_’匹单个
select * from emp where name regexp 'n$'; # 正则
7.1.2 分组查询:GROUP BY
首先明确一点,分组发生在where之后,即分组是基于where之后得到的记录而进行的;
分组指的是,将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等;
分完组之后只能看到分组的字段以及聚合的结果;
select depart_id,count(id),avg(salary),max(age),min(salary),sum(salary) from emp group by depart_id;
聚合函数:
max() # 最大
min() # 最小
avg() # 平均
sum() # 和
count() # 个数
group_concat() # 指定字段查询
select depart_id,count(id),avg(salary),max(age),min(salary),sum(salary) from emp group by depart_id;
# 每个部门都有多少个人
select depart_id,count(id) from emp group by depart_id;
# 每个职位男生的平均薪资
select post,avg(salary) from emp where sex="male" group by post;
select post, group_concat(name) from emp group by post;
select post, group_concat(name) from emp where sex="male" group by post;
7.1.3 聚合函数
强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
max() # 最大
min() # 最小
avg() # 平均
sum() # 和
count() # 个数
group_concat() # 指定字段查询
示例:
SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id=1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;
7.1.4 HAVING过滤
HAVING与WHERE不一样的地方在于;
执行优先级从高到低:where > group by > having
-
Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
-
Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数;
# 找出来男生平均薪资大于3000的职位
select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 8000;
7.1.5 查询排序:ORDER BY
select * from emp order by salary;
select * from emp order by salary desc; # 默认升序,指定desc则为降序
select * from emp order by age,id desc; # 先按年龄排,然后在其基础上,再按id排
7.1.6 限制查询记录数:LIMIT
控制显示几条信息;
默认初始位置为0
select * from emp limit 0,5; # 从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
select * from emp limit 5,5; # 从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
select * from emp limit 10,5;
select * from emp limit 15,5;
select * from emp limit 20,5;
7.2 多表查询
建表进行解析
表1:
create table dep(
id int,
name varchar(20)
);
表2:
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into emp(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
部门表内容
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
员工表内容
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
7.2.1 多表查询
外链接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
- 交叉连接,生成笛卡尔积;
mysql> select * from emp,dep;
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
24 rows in set (0.00 sec)
- 内连接: 保留两张表有对应关系的记录;
相当于利用条件从笛卡尔积结果中筛选出了正确的结果
方式一:
mysql> select * from emp,dep where emp.dep_id=dep.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
方式二:(专业)
mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
- 外连接-左连接: 优先显示左表全部记录;
以左表为准,即找出所有员工信息,在内连接的基础上增加左边有右边没有的结果;
mysql> select *from emp left join dep on emp.dep_id = dep.id;
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
+----+------------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
- 外连接-右链接: 在内连接的基础上保留右表的记录;
mysql> select *from emp right join dep on emp.dep_id = dep.id;
+------+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-----------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
- 全外连接: 在内连接的基础上保留左右表的记录;
mysql> select *from emp right join dep on emp.dep_id = dep.id union select *from emp left join dep on emp.dep_id = dep.id;
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
+------+------------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)
注意 union与union all的区别:union会去掉相同的纪录
案例: 即找出年龄大于25岁的员工以及员工所在的部门
mysql> select emp.name,dep.name from emp inner join dep on emp.dep_id = dep.id where age >25;
+---------+--------------+
| name | name |
+---------+--------------+
| alex | 人力资源 |
| wupeiqi | 人力资源 |
| yuanhao | 销售 |
+---------+--------------+
3 rows in set (0.00 sec)
7.2.2 子查询
- 子查询是将一个查询语句嵌套在另一个查询语句中。
- 内层查询语句的查询结果,可以为外层查询语句提供查询条件。
- 子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
- 还可以包含比较运算符:= 、 !=、> 、<等
IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
not in 无法处理null的值,即子查询中如果存在null的值,则查不到结果,需提前过滤掉null值再查询
ANY关键字的子查询
#在 SQL 中 ANY 和 SOME 是同义词,SOME 的用法和功能和 ANY 一模一样。
# ANY 和 IN 运算符不同之处1
ANY 必须和其他的比较运算符共同使用,而且ANY必须将比较运算符放在 ANY 关键字之前,所比较的值需要匹配子查询中的任意一个值,这也就是 ANY 在英文中所表示的意义
例如:使用 IN 和使用 ANY运算符得到的结果是一致的
select * from employee where salary = any (
select max(salary) from employee group by depart_id);
select * from employee where salary in (
select max(salary) from employee group by depart_id);
结论:也就是说“=ANY”等价于 IN 运算符,而“<>ANY”则等价于 NOT IN 运算符
# ANY和 IN 运算符不同之处2
ANY 运算符不能与固定的集合相匹配,比如下面的 SQL 语句是错误的
SELECT * FROM T_Book WHERE
FYearPublished < ANY (2001, 2003, 2005)
ALL关键字的子查询
# all同any类似,只不过all表示的是所有,any表示任一
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all (
select avg(salary) from employee group by depart_id);
查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工
select * from employee where salary < any ( select avg(salary) from employee group by depart_id);
查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any (
select avg(salary) from employee group by depart_id);
比较运算符的子查询
#比较运算符:=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询
#department表中存在dept_id=203,Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205,False
mysql> select * from employee
-> where exists
-> (select id from department where id=204);
Empty set (0.00 sec)
in与exists区别及其执行原理
当in和exists在查询效率上比较时,in查询的效率快于exists的查询效率;
exists:
exists后面一般都是子查询,后面的子查询被称做相关子查询(即与主语句相关),当子查询返回行数时,exists条件返回true,
否则返回false,exists是不返回列表的值的,exists只在乎括号里的数据能不能查找出来,是否存在这样的记录。
# 例
查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id)
# exists的执行原理为:
1、依次执行外部查询:即select * from class
2、然后为外部查询返回的每一行分别执行一次子查询:即(select * from stu where stu.cid=class.cid)
3、子查询如果返回行,则exists条件成立,条件成立则输出外部查询取出的那条记录
in :
in后跟的都是子查询,in()后面的子查询 是返回结果集的;
# 例
查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')
# in的执行原理为:
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.
not in与 not exists区别
not exists查询的效率远远高与not in查询的效率。
not in:
为了证明not in成立,即找不到,需要一条一条地查询表,符合要求才返回子查询的结果集,不符合的就继续查询下一条记录,直到把表中的记录查询完,只能查询全部记录才能证明,并没有用到索引。
not exists:
如果主查询表中记录少,子查询表中记录多,并有索引。
例如:查询那些班级中没有学生的班级
select * from class
where not exists
(select * from student where student.cid = class.cid)
not exists的执行顺序是:
在表中查询,是根据索引查询的,如果存在就返回true,如果不存在就返回false,不会每条记录都去查询。
7.2.3 把多张表链接起来
方式一;
select * from
(select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id) as t1
inner join
dep
on t1.dep_id = dep.id
;
方式二;
select * from emp
inner join dep
on emp.dep_id = dep.id
inner join dep as t1
on t1.id = dep.id;
查询练习
练习
更多推荐
MySQL数据库初步入门
发布评论