一、数据库基本概念
1.1 什么是数据库
数据库是计算机应用系统中的一种专门管理数据资源的系统。数据有多种形式,如文字、数码、符号、图形、图像以及声音等。数据是所有计算机系统所要处理的对象。人们所熟知的一种处理办法是制作文件,即将处理过程编成程序文件,将所涉及的数据按程序要求组织成数据文件,用程序文件来调用。数据文件与程序文件保持着一定的对应关系。在计算机应用迅速发展的情况下,这种文件式方法便显出不足。比如,它使得数据通用性差,不便于移植,在不同文件中存储大量重复信息、浪费存储空间、更新不便等。数据库系统便能解决上述问题。数据库系统不从具体的应用程序出发,而是立足于数据本身的管理,它将所有数据保存在数据库中,进行科学的组织,并借助于数据库管理系统,以它为中介,与各种应用程序或应用系统接口,使之能方便地使用数据库中的数据。
数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件,用于建立、使用和维护数据库,简称DBMS。用户通过DBMS访问数据库中的数据,数据库管理员也通过dbms进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立,修改和询问数据库。
1.2 为什么要使用数据库
存储方式比较
- 内存
优点:速度快
缺点:容量有限,不能够永久保存,数据是临时状态的 - Excel
优点:数据永久保存
缺点:少量数据直接打开获取数据,大量数据使用程序IO流操作文件不方便,同一时间多人操作同一文件也可能出现问题。 - 数据库
优点:
1、海量存储,查询效率高。
2、数据永久保存。
3、方便存储和管理数据。
4、操作方式统一。
缺点:
1、占资源。
2、部分数据库需要付费。
1.3 数据库分类
1、关系型数据库
关系型数据库模型是把复杂的数据结构归结为简单的二元关系(即二维表格形式)。在关系型数据库中,对数据的操作几乎全部建立在一个或多个关系表格上,通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。
例如:Oracle和MySQL
2、非关系型数据库(NoSQL)
NoSQL,指的是非关系型的数据库。NoSQL有时也称作Not Only SQL的缩写,是对不同于传统的关系型数据库的数据库管理系统的统称。
NoSQL用于超大规模数据的存储。这些类型的数据存储不需要固定的模式,无需多余操作就可以横向扩展。
例如:MongoDB、Redis
3、
比较标准 | 关系数据库 | 非关系数据库 | 区别 |
数据库原理 | 完全支持 | 部分支持 | 关系数据库有关系代数理论作为基础;NoSQL没有统一的理论基础 |
数据规模 | 大 | 超大 | 关系数据库很难实现横向扩展,纵向扩展的空间也比较有限,性能随着数据规模的增大而降低;NoSQL很容易通过添加更多设备来支持更大规模的数据 |
数据库模式 | 固定 | 灵活 | 关系数据库需要定义数据库模式,严格遵守数据定义和相关约束条件;NoSQL不存在数据库模式,可自由、灵活地定义、存储各种不同类型的数据 |
查询效率 | 快 | 简单查询高效,但复杂查询性能不尽人意 | 关系数据库借助索引机制实现快速查询;很多NoSQL数据库没有面向复杂查询的索引,虽然可以使用MapReduce来加速查询,但复杂查询方面性能不如关系数据库 |
一致性 | 强一致性 | 弱一致性 | 关系数据库严格遵守事务ACID模型,保证事务强一致性;NoSQL大多只遵守BASE模型,保证最终一致性 |
数据完整性 | 容易实现 | 很难实现 | 关系数据库容易实现数据完整性,如通过主键或非空约束实现实体完整性,通过主键、外键实现参照完整性,通过约束或触发器实现用户自定义完整性;NoSQL却难以实现 |
扩展性 | 一般 | 好 | 关系数据库很难实现横向扩展,纵向扩展的空间也比较有限;NoSQL通过添加廉价设备实现扩展 |
可用性 | 好 | 很好 | 关系数据库在任何时候都以保证数据一致性为优先目标,其次是优化系统性能,随着数据规模增大,可用性较弱;大多NoSQL都能提供较高的可用性 |
标准化 | 是 | 否 | 关系数据库已经标准化(SQL);NoSQL没有行业标准,不同NoSQL数据库都有自己的查询语言,很难规范应用程序接口 |
技术支持 | 高 | 低 | |
可维护性 | 复杂 | 复杂 |
1.4关系型数据库主要对象
对象名称 | 简介 |
表 | 数据库,由行(Row)和列(Column)构成。列又称为字段,列的标题被称为字段名。数据库表中的行,一行数据称为一条记录,多数是同类信息组成。一般来说,一个数据库表是由一条或多条记录组成,如果是没有记录的表,则称为空表。为了惟一地确定一条记录,每个数据库表中一般都是有一个主关键字。 |
字段 | MySQL提供了一组可以赋给表中各个列的数据类型,每个类型都强制数据满足为该数据类型预先确定的一组规则,例如大小、类型及格式。 |
索引 | 索引是为了给用户提供快速访问数据的途径,时刻监督数据库表的数据,从而参照特定数据库表列建立起来的一种顺序,主要是为了便于用户访问指定数据,避免数据的重复。 |
视图 | 视图也有一组数据项和命名字段,只是在用户执行查询操作的时候才会出现,其实在数据库中并不存在,通过控制用户对数据的访问权限,简化数据,只显示用户需要的数据项。 |
二、MySQL数据库
2.1 MySQL存储引擎
1、InnoDB存储引擎
InnoDB 是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键。MySQL5.5.5之后,InnoDB 作为默认的存储引擎,InnoDB 主要特性有:
- 支持事务
- 灾难恢复性好
- 为处理巨大数据量的最大性能设计
- 实现了缓冲管理,不仅能缓冲索引也能缓冲数据,并且会自动创建散列索引以加快数据的获取
- 支持外键完整性约束。存储表中的数据时,每张表的存储都按逐渐顺序存放,如果没有显示在表定义时指定主键,InnoDB会为- 每一行生成一个6B的ROWID,并以此作为主键。
- 被用在众多需要高性能的大型数据库站点上
2、MyISAM存储引擎
MyISAM 基于 ISAM 的存储引擎,并对其进行扩展。它是在Web、数据存储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度,但不支持事务。在 MySQL5.5.5 之前的版本中,MyISAM 是默认的存储引擎。MyISAM 主要特性有:
- 不支持事务
- 使用表级锁,并发性差
- 主机宕机后,MyISAM表易损坏,灾难恢复性不佳
- 可以配合锁,实现操作系统下的复制备份、迁移
- 只缓存索引,数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
- 数据紧凑存储,因此可获得更小的索引和更快的全表扫描性能
- 可以把数据文件和索引文件放在不同目录
- 使用 MyISAM 引擎创建数据库,将产生3个文件。文件的名字以表的名字开始,扩展名指出文件类型:frm 文件存储表定义,数据文件的扩展名为 .MYD(MYData),索引文件的扩展名是 .MYI(MYIndex)。
3、MEMORY存储引擎
MEMORY 存储引擎将表中的数据存储在内存中,为查询和引用其他表数据提供快速访问。MEMORY 主要特性有:
- 使用表级锁,虽然内存访问快,但如果频繁的读写,表级锁会成为瓶颈
- 只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型,浪费空间
0 不支持TEXT、BLOB字段。当有些查询需要使用到临时表(使用的也是MEMORY存储引擎)时,如果表中有TEXT、BLOB字段,那么会转换为基于磁盘的MyISAM表,严重降低性能 - 由于内存资源成本昂贵,一般不建议设置过大的内存表,如果内存表满了,可通过清除数据或调整内存表参数来避免报错
- 服务器重启后数据会丢失,复制维护时需要小心
2.2 数据类型
2.2.1.整型:
整数类型 | 字节 | 最小值 | 最大值 |
TINYINT | 1 | 有符号-128,无符号0 | 有符号127,无符号255 |
SMALLINT | 2 | 有符号-32768,无符号0 | 有符号32767,无符号65535 |
MEDIUMINT | 3 | 有符号-8388608,无符号0 | 有符号8388607,无符号1677215 |
INT和INTEGER | 4 | 有符号-2147483648,无符号0 | 有符号2147483647,无符号4294967295 |
BIGINT | 8 | 有符号-9223372036854775808,无符号0 | 有符号9223372036854775807,无符号18446744073709551615 |
2.2.2.浮点数类型:
浮点数类型 | 字节 | 最小值 | 最大值 |
FLOAT | 4 | + -1.75494351E-38 | + -3.402823466E + 38 |
DOUBLE | 8 | + -2.2250738585072014E-308 | + -1.7976931348623517E + 308 |
2.2.3.日期和时间类型:
日期和时间类型 | 字节 | 最小值 | 最大值 |
DATE | 4 | 1000-01-01 | 9999-12-31 |
DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 4 | 19700101080001 | 2038年某个时刻 |
TIME | 3 | -835:59:59 | 838:59:59 |
YEAR | 1 | 1901 | 2155 |
DATE:表示年月日
DATETIME:表示年月日时分秒
TIMESTAMP :需要经常插入或者更新日期为当前系统时间(与实际时区对应)
TIME:表示时分秒
YEAR:表示年份
2.2.4.字符串类型:
字符串类型 | 字节 | 描述 |
CHAR(X) | X | X为0〜255之间的整数 |
VARCHAR(X) | X | X为0~65535之间的整数 |
TINYTEXT | 0~255的长度 | 为+2个字节 |
TEXT | 0〜65535值的长度 | 为+2个字节 |
MEDIUMTEXT | 0~167772150值的长度 | 为+3个字节 |
LONGTEXT | 0~4294967295值的长度 | 为+4个字节 |
BINARY(X) | X | 允许长度为0 - X |
VARBINARY(X) | X | 允许长度为0 - X |
TINYBLOB | 0~255 | |
BLOB | 0〜2 ^ 16 | |
MEDIUMBLOB | 0~2 ^ 24 | |
LONGBLOB | 0〜2 ^ 32 |
- CHAR和VARCHAR用于储存少量字符串(只能存字符数据)
- BINARY和 VARBINARY与CHAR和VARCHAR非常相似,但是可以储存例如图片,音乐或者视频文件等二进制数据,如果存储时经常发生变化则选择VARBINARY类型,否则选择后者
- BLOB系列与TEXT系列非常相似,前者可以存储二进制数据(例如图片,音乐或者视频文件),后者只能存储字符数据
2.3Mysql 8.0安装教程(Windows版)
官⽹地址:https://dev.mysql.com/downloads/windows/installer/8.0.html
选择下面版本下载
2.3.1安装流程
- 双击下载好的mysql软件安装包,选择Custom个性化安装,点击Next,进⾏下⼀步;
- 点开MySQL Servers,点击MySQL Server 8.0 ,选择适合⾃⼰电脑的版本;然后点开MySQL Connector下的Connector/ODBC 8.0,选择适合⾃⼰电脑的版本,点击Next,进⾏下⼀步。
- 点击Execute,进⼊下⼀步;
- 点击Next,下⼀步;
- 点击Execute,进⼊下⼀步;
- 等待安装完成,点击Next,下⼀步;
- 点击Next,下⼀步;
- 默认即可,点击Next,下⼀步;
- 默认,点击Next,下⼀步;
- 点击Next,下⼀步;
设置Mysql登录密码,启动Mysql时要⽤到这个密码,⼀定要记住; - 启动Mysql服务要⽤到的Windows Server Name: MySQL80,默认即可,点击Next,下⼀步
- 点击Execute,等待安装完成;
- 点击Next,下⼀步;
- 点击Finish,完成安装。
2.3.2 环境变量
- 右键点击此电脑,选择属性;
- 选择⾼级系统设置;
- 点击环境变量;
- 选择系统变量(S),找到Path,点击编辑;
- 找到Mysql的安装⽬录,进⼊到bin⽬录下,复制该地址;
- 点击新建,将复制的Mysql地址粘贴进去,确定;
- 在搜索框中输⼊:cmd,回⻋,以管理员身份运⾏命令提示符;
- 输⼊:net start MySQL80,启动Mysql服务;同理,net stop mysql80,就是停⽌服务;
- 在Mysql服务开启后,输⼊:mysql -uroot -p,回⻋,然后输⼊⾃⼰设置的密码,出现如下界
⾯,即安装成功
三、SQL语句
3.1. DDL: Data Definition Language(数据定义语言)
语句 | 简介 |
create database 数据库名 | 创建数据库 |
alter database 数据库名 | 修改数据库 |
drop database 数据库名 | 删除数据库 |
create table 表名(字段名称1 字段类型 长度,字段名称2 字段类型 长度) | 创建表 |
desc 表名 | 查看表结构 |
show tables | 查看当前数据库中有哪些数据表 |
show create table 表名 | 查看数据表的建表语句 |
drop table 表名 | 删除表 |
drop table if exists 表名 | 先判断是否存在再删除 |
rename table 旧表名 to 新表明 | 重命名表 |
alter table 表名 add 字段名 字段类型 | 为表添加一个新的字段 |
alter table 表名 modify字段名 字段类型 | 对表的描述字段进行修改 |
alter table 表名 change 旧列名 新列名 类型(长度) | 修改列名 |
alter table 表名 drop 列名 | 删除列 |
3.2.DML: Data Manipulation Language(数据操作语言)
语句 | 简介 |
nsert into 表名(字段名1,字段名2,字段名3) values(值1,值2,值3); | 插入全部字段(将字段名全部写出来) |
insert into 表名 values(值1,值2,值3); | 插入全部字段 |
insert into 表名(字段1,字段3) values(值1,值3) | 插入指定字段 |
update 表名 set 字段名=值; | 不带条件修改数据,修改所有行 |
update 表名 set 字段名1=值1 where 字段名2 =值2 ; | 带条件修改数据(将字段名2=值2的数据的字段名1改为值1) |
update 表名 set 字段名1=值1,字段名2=值2 where 字段名3=值3 ; | 带条件修改多行数据 |
delete from 表名 | 删除所有数据 |
delete from 表名 where 字段名=值; | 带条件的删除数据 |
truncate table 表名 | 删除表中所有记录,相当于删除表的结构,再创建一张表。可以消除自增长。 |
3.3. DQL: Data Query Language(数据查询语言 )
语句 | 简介 |
select * from 表名; | 查询表中所有数据 |
select 字段名1 字段名2 from 表名 | 查询部分字段 |
select 字段名1 as ‘别名’ from 表名 | 别名查询 |
select distinct 字段名 from 表名; | 去重查询 |
select 字段名+5 from 表名; | 运算查询 |
3.4 排序
3.4.1单列排序
- SELECT 字段名 FROM [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
ASC | 升序排序(默认) |
DESC | 降序排序 |
3.4.2 组合排序
- SELECT 字段名 FROM [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC],字段名 [ASC / DESC]
3.4.3 聚合函数
SELECT 聚合函数(字段名) FROM 表名;
聚合函数 | 用途 |
count(字段) | 统计指定字段不为NULL的记录行数 |
sum(字段) | 计算指定字段的数值和 |
max(字段) | 计算指定字段的最大值 |
min(字段) | 计算指定字段的最小值 |
avg(字段) | 计算指定字段的平均值 |
3.5 分组
- SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
3.6 limit关键字
SELECT 字段名 FROM 表名 LIMIT offset ,length;
offset | 起始行数,从0开始计数,如果省略则默认为0 |
length | 返回的行数 |
offset和length关键字可以接受一个或者两个为0或者正整数的参数。
3.7 约束
- 对数据库表添加约束,能够对表中的数据进行限制。保证数据的正确性、有效性和完整性。不正确的数据将无法插入表中。
约束名 | 约束关键字 |
主键约束 | primary key |
唯一约束 | unique |
非空约束 | not null |
外键约束 | foreign key |
3.7.1 主键约束
- 一般将与业务无关的字段作为主键,主键一般没有任何含义。将表中的某个字段添加主键约束,该字段的值不能重复且非空。可以在创建表时给字段添加主键约束,也可以对已有表的字段添加主键约束,一个表中只能有一个主键。
特点 | 不可重复 唯一 非空 |
作用 | 用来表示数据库中的每一条记录(用来唯一标识数据表中的一条记录) |
- 创建表时添加主键约束
CREATE TABLE user (id INT PRIMARY KEY, name VARCHAR(20), age INT);
- 对已有表的字段添加主键约束
ALTER TABLE user ADD PRIMARY KEY (id);
- 删除表字段的主键约束
ALTER TABLE user DROP PRIMARY KEY;
- 创建表时添加主键自增约束,并指定起始值
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(5),
age INT
) KEY AUTO_INCREMENT = 1;
- 对已有表修改主键自增起始值
ALTER TABLE user AUTO_INCREMENT = 2 - delete 和 truncate 对主键自增长的影响
清空数据表的方式 | 特点 |
delete | 只删除表中所有数据,对自增没有影响 |
truncate | truncate是将整个表删除掉,然后创建一个新的表。 自增的主键重新从1开始 |
3.7.2 非空约束
- 给表中的字段添加非空约束,使该字段的值不能为 null
CREATE TABLE user(
id INT,
name VARCHAR(5) NOT NULL
);
3.7.3 唯一约束
- 给表中的字段添加唯一约束,使该字段的值不能重复
CREATE TABLE user(
id INT;
name VARCHAR(5) UNIQUE
);
主键约束和唯一约束的区别 |
1、主键约束唯一,且不能为空(非空约束+唯一约束) |
2、唯一约束唯一,但可以为空 |
3、一个表中可以用多个唯一约束,但只能有一个主键 |
3.7.4 外键约束
- 创建表时给表添加外键约束
语法格式:[CONSTRAINT] [ 外键约束名称] FOREIGN KEY( 外键字段名) REFERENCES 主表名( 主键字段名);
CREATE TABLE department (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(5),
lacation VARCHAR(10)
);
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(5),
emp_id INT,
CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
employee 表和 department 表是一对多的关系,一个部门对应用多个员工。
部门表称之为主表,员工表称之为从表。
插入员工数据表,根据 dep_id 字段,在部门表中没有对用的部门,则插入数据失败。
- 给已有表添加外键约束
语法格式:ALTER TABLE 从表 ADD [CONSTRAINT] [ 外键约束名称] FOREIGN KEY ( 外键字段名) REFERENCES 主表 ( 主键字段名);
alter table employee add foreign key (dept_id) references department(id);
- 删除从表外键约束
alter table employee drop foreign key emp_dep_fk;
有主外键关系的两个表插入数据的时候:首先插入主表,然后再插入从表。
删除:首先应该删除从表的关联数据,然后再删除主表的关联数据(因为主表对从表有引用)
3.8多表联查
3.8.1、多表联查最早的方式(笛卡尔积)
select * from category,produects;
3.8.2、笛卡尔积+where条件
select * from category c,produects p where c.cid = p.category_id;
3.8.3、内连接查询
- 内连接的特点:
通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键 方式去匹配
3.8.3.1、隐式内链接
- 语法格式:SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
SELECT * FROM products,category WHERE category_id = cid;
查询商品表的商品名称 和 价格,以及商品的分类信息
可以通过给表起别名的方式, 方便我们的查询(有提示)
SELECT
p.`pname`,
p.`price`,
c.`cname`
FROM products p , category c WHERE p.`category_id` = c.`cid`;
3.8.3.2、显式内连接
- 使用 inner join …on 这种方式, 就是显式内连接
语法格式:SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件 (inner 可以省略)
- 查询所有商品信息和对应的分类信息
# 显式内连接查询
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
- 查询鞋服分类下,价格大于500的商品名称和价格.
# 查询鞋服分类下,价格大于500的商品名称和价格
-- 我们需要确定的几件事
-- 1.查询几张表 products & category
-- 2.表的连接条件 从表.外键 = 主表的主键
-- 3.查询的条件 cname = '鞋服' and price > 500
-- 4.要查询的字段 pname price
SELECT
p.pname,
p.price
FROM products p INNER JOIN category c ON p.category_id = c.cid
WHERE p.price > 500 AND cname = '鞋服';
3.8.4、外连接查询
3.8.4.1、左外连接
- 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
- 左外连接的特点
以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
如果匹配不到, 左表中的数据正常展示, 右边的展示为null - 语法格式:SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
-- 左外连接查询
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;
- 左外连接, 查询每个分类下的商品个数
# 查询每个分类下的商品个数
/*
1.连接条件: 主表.主键 = 从表.外键
2.查询条件: 每个分类 需要分组
3.要查询的字段: 分类名称, 分类下商品个数
*/
SELECT
c.`cname` AS '分类名称',
COUNT(p.`pid`) AS '商品个数'
FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`
GROUP BY c.`cname`;
3.8.4.2、 右外连接
- 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
- 右外连接的特点
以右表为基准,匹配左边表中的数据,如果能匹配到,展示匹配到的数据
如果匹配不到,右表中的数据正常展示, 左边展示为null - 语法格式:SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
-- 右外连接查询
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;
- 内连接: inner join , 只获取两张表中 交集部分的数据.
- 左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
- 右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分
- 内连接和左外连接使用居多
3.9 合并查询
3.9.1、UNION
- UNION 操作符用于合并两个或多个 SELECT 语句的结果集,并消除重复行。
- 注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同
时,每条 SELECT 语句中的列的顺序必须相同。 - 基本语法:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
- 总结:
- 选择的列数必须相同;
- 所选列的数据类型必须在相同的数据类型组中(如数字或字符)
- 列的名称不必相同
- 在重复检查期间,NULL值不会被忽略
3.9.1、UNION ALL
- UNION ALL 运算符用于将两个 SELECT 语句的结果组合在一起,重复行也包含在内。
- UNION ALL 运算符所遵从的规则与 UNION 一致。
语法:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
- 总结:
- UNION和UNION ALL关键字都是将两个结果集合并为一个,也有区别。
- 重复值:UNION在进行表连接后会筛选掉重复的记录,而Union All不会去除重复记录。
- UNION ALL只是简单的将两个结果合并后就返回。
- 在执行效率上,UNION ALL 要比UNION快很多,因此,若可以确认合并的两个结果集中不
包含重复数据,那么就使用UNION ALL。
四、 子查询
- 子查询概念
一条select 查询语句的结果, 作为另一条 select 语句的一部分 - 子查询的特点
子查询必须放在小括号中
子查询的场景中还会有另外一个特点,整个sql至少会有两个select关键字 - 子查询常见分类
where型 子查询: 将子查询的结果, 作为父查询的比较条件 =
from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
4.1、子查询的结果作为查询条件
语法格式
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
- 通过子查询的方式, 查询价格最高的商品信息
# 通过子查询的方式, 查询价格最高的商品信息
-- 1.先查询出最高价格
SELECT MAX(price) FROM products;
-- 2.将最高价格作为条件,获取商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
- 查询化妆品分类下的 商品名称 商品价格
#查询化妆品分类下的 商品名称 商品价格
-- 先查出化妆品分类的 id
SELECT cid FROM category WHERE cname = '化妆品';
-- 根据分类id ,去商品表中查询对应的商品信息
SELECT
p.`pname`,
p.`price`
FROM products p
WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妆品');
- 查询小于平均价格的商品信息.
-- 1.查询平均价格
SELECT AVG(price) FROM products;
-- 2.查询小于平均价格的商品
SELECT * FROM products WHERE price < (SELECT AVG(price) FROM products);
4.2 子查询的结果作为一张表
- 语法格式
SELECT 查询字段 FROM (子查询)表别名 WHERE 条件; - 查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称
-- 1. 先查询分类表的数据
SELECT * FROM category;
-- 2.将上面的查询语句 作为一张表使用
SELECT
p.`pname`,
p.`price`,
c.cname
FROM products p
-- 子查询作为一张表使用时 要起别名 才能访问表中字段
INNER JOIN (SELECT * FROM category) c
ON p.`category_id` = c.cid WHERE p.`price` > 500;
注意: 当子查询作为一张表的时候,需要起别名,否则无法访问表中的字段。
4.3子查询结果是单列多行
- 子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
语法格式
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
- 查询价格小于两千的商品,来自于哪些分类(名称)
# 查询价格小于两千的商品,来自于哪些分类(名称)
-- 先查询价格小于2000 的商品的,分类ID
SELECT DISTINCT category_id FROM products WHERE price < 2000;
-- 在根据分类的id信息,查询分类名称
-- 报错: Subquery returns more than 1 row
-- 子查询的结果 大于一行
SELECT * FROM category
WHERE cid = (SELECT DISTINCT category_id FROM products WHERE price < 2000);
- 使用in函数, in( c002, c003 )
-- 子查询获取的是单列多行数据
SELECT * FROM category
WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);
- 查询家电类 与 鞋服类下面的全部商品信息
# 查询家电类 与 鞋服类下面的全部商品信息
-- 先查询出家电与鞋服类的 分类ID
SELECT cid FROM category WHERE cname IN ('家电','鞋服');
-- 根据cid 查询分类下的商品信息
SELECT * FROM products
WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家电','鞋 服'));
- 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用
单列单行 =
单列多行 in - 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).