- 本博客是《MySQL基础》系列博客的第七部分,主要介绍MySQL语句中的DQL语句,即我们常说的查询语句的基本用法,包括简单的单表查询、去重、排序、分组查询、统计查询、多表查询、连接查询、子查询和联合查询
- 本博客既为方便自己查看复习而作,亦为你而作,望能有所裨益
- 学习交流请联系 April_0911@163.com
DQL简介
DQL,即Data Definition Language,数据查询语言,用于数据库、数据表等数据的查询,也就是我们常说的增删改查中的查。
DQL用法
示例数据库
为了后续介绍方便,我们使用MySQL自带的数据库 Sakila :
此外,在使用数据库前,我们需要对数据库有一个基本的了解,如果一个一个表打开看显然不太合适,这时候就需要有一个全局视图来帮助我们。而MySQL确实也提供了这样的功能:
- 首次打开时需要自行找到示例数据库所在地,默认存放在MySQL文件夹下的Samples and Examples文件夹内,以.mwb为后缀名
- 打开后是这样的画面:
- 进而了解到该数据库处理的是租赁事务,其中有顾客数据,有商家数据以及租赁商品的数据。
下面我们就开始学习SQL语句的重头戏——查询语句SELECT
的用法吧。
简单的单表查询
/* 简单的表查询*/
select * from <数据库>.<数据表>; -- 查询某一数据库中某表的全部信息
select <字段名> from <数据库>.<数据表>; -- 查询某表的某些字段
select actor_id,first_name from actor;
-- 查询actor表中actor_id和first_name两个字段
select <字段名> from <数据库>.<数据表> where <条件>; -- 条件查询
select actor_id,first_name from actor where actor_id > 10;
-- 查询actor表中actor_id和first_name两个字段中actor_id大于10的字段
select actor_id,first_name from actor where actor_id != 10;
-- 查询actor表中actor_id和first_name两个字段中actor_id不等于10的字段
select actor_id,first_name from actor where actor_id between 10 and 20;
-- -- 查询actor表中actor_id和first_name两个字段中actor_id位于[10,20]区间内的字段
select actor_id,first_name from actor where actor_id in (10,13,29);
-- 查询actor表中actor_id和first_name两个字段中actor_id为10,13和29的字段
select actor_id,first_name from actor where actor_id not in (10,13,29);
-- 查询actor表中actor_id和first_name两个字段中actor_id不为10,13和29的字段
-- 通配符的使用
select actor_id,first_name from actor where first_name like 'j%';
/* 查询actor表中actor_id和first_name两个字段中first_name
为以j开头,后跟任意个字符的字段 */
select actor_id,first_name from actor where first_name like 'j__';
/* 查询actor表中actor_id和first_name两个字段中first_name
为以j开头长度为3个字符的字段,一个_代表一个字符 */
select actor_id,first_name from actor where first_name not like 'j%';
/* 查询actor表中actor_id和first_name两个字段中first_name
不以j开头,后跟任意个字符的字段 */
select actor_id,first_name from actor where first_name like 'j%' and last_name like '%s';
/* 查询actor表中actor_id和first_name两个字段中first_name
为以j开头且last_name为以s结尾的字段 */
select actor_id,first_name from actor where first_name like 'j%' or last_name like '%s';
/* 查询actor表中actor_id和first_name两个字段中first_name
为以j开头或last_name为以s结尾的字段 */
去重
-- 去重(distinct)
-- 原始数据中不可避免地存在重复数据,因此在分析数据前要对数据进行去重
select distinct <字段名> from <数据库>.<数据表>;
select first_name from actor; -- 返回200个名字
select distinct first_name from actor; -- 去重后返回128个名字
select first_name,last_name from actor; -- 返回200个姓名
select distinct first_name,last_name from actor; -- 去重后返回199个姓名
排序
-- 排序
-- 数据库中记录的默认排序为升序
select <字段名> from <数据库>.<数据表> order by <字段名> [desc];
-- desc为降序,添加则将该关键词前面的字段降序排列后返回查询结果
select actor_id,first_name from actor order by actor_id desc;
-- 按actor_id降序排列后返回查询结果
select actor_id,first_name from actor order by actor_id desc, first_name;
-- 先按演员号降序排列,再按演员名升序排列(字段为字符串时排序效果不明显)
select actor_id,first_name from actor order by actor_id desc, first_name desc;
-- 先按演员号降序排列,再按演员名降序排列
分组查询
-- 分组查询
-- 数据量较大时,通常对数据进行分组,以了解数据的大致情况
select <字段名> from <数据库>.<数据表> group by <分组字段名>;
select * from film group by rating;
-- 将rating分组后再查看所有数据
select rating,group_concat(film_id) from film group by rating;
-- 查看每个等级对应的记录编号,concat表示合并多个数组或字符串
select rating,count(film_id) from film group by rating;
-- 查看每个等级对应的记录数
select rating,count(film_id) from film group by rating with rollup;
-- 查看每个等级对应的记录数并合计
-- 在分组查询的基础上进行条件查询
select <字段名> from <数据库>.<数据表> group by <分组字段名> having <条件>;
select rating,count(film_id) from film group by rating having count(film_id) > 200; -- 查询电影数目大于200的某一等级及该等级的电影数
-- 在分组查询的基础上进行排序查询
select rating,count(film_id),rental_duration from film group by rating,rental_duration order by rating;
-- 在rating分组的基础上用rental_duration分组,再以rating字段升序排列
统计查询
-- 统计查询
-- 为了对表中数据有个初步了解,通常情况下我们要进行下简单的统计查询
-- 常用的统计函数有count、sum、avg、max、min等
select <统计函数>(字段名) from <数据库>.<数据表>;
select * from sakila.payment; -- 以payment表为例
select count(payment_id) from actor;
-- 有多少条顾客的购买记录
select customer_id,sum(amount) from payment where customer_id = 1;
-- 编号为1的顾客的付款总额
select customer_id,sum(amount) from payment group by customer_id;
-- 每个顾客的付款总额
select customer_id,avg(amount) from payment group by customer_id;
-- 每个顾客的平均付款额
select customer_id,max(amount),min(amount) from payment group by customer_id;
-- 每个顾客单次付款的最大值和最小值
在 SQL 中 SELECT 语句的执行顺序为
FROM
→
\rightarrow
→WHERE
→
\rightarrow
→GROUP BY
→
\rightarrow
→HAVING
→
\rightarrow
→SELECT
→
\rightarrow
→ORDER BY
其中 SELECT 的执行顺序在 GROUP BY 和 HAVING 之后,而在 ORDER BY 之前,也就是说在 ORDER BY 语句中可以使用别名,在 GROUP BY 语句中不可以使用别名
连接查询
有时候我们需要查询的数据分散在不同的表中,此时我们就需要将这些存放在不同的表中的数据连接起来,也称多表查询。
示例数据表
由于Sakila数据库中的表的数据过多,因此,为了演示方便,我们创建两张新的数据表用来演示学习:
(顺便复习一下DDL的相关语句的用法,岂不美哉?)
create table city1(
city_id smallint(5) unsigned not null auto_increment,
city varchar(50) not null,
country_id smallint(5) unsigned not null,
primary key(city_id)
)
select city_id,city,country_id from city limit 3;
-- 从city表中抽出前三条记录放入新建的city1表
create table country1(
country_id smallint(5) unsigned not null auto_increment,
country varchar(50) not null,
primary key(country_id)
)
select country_id,country from country limit 2;
-- 从country表中抽出前两条记录放入新建的country1表
)
创建完成后的表是这样的:
- City1表
- country1表
那么下面就开始学习下如何进行连接查询吧
交叉连接
交叉连接(Corss Join)也称笛卡尔积,是将所连接的表进行笛卡尔积的运算。
假设交叉连接的表有A,B两表,A表有
x
x
x行记录,B表有
y
y
y行记录,此时将返回
x
∗
y
x*y
x∗y行记录。
select * from city1,country1;
-- 等价于 select * from city1 join country1;
内连接
内连接(Inner Join)是通过定义连接的表内数据之间的关系进行查询
select * from <连接的表> inner join <被连接的表> on <条件表达式>;
注:观察到我们创建的两张表其共同字段 country_id 并没有相同值,因此我们必须修改一张表的数据以使其满足连接需要
update city1 set country_id = '1' where (city_id='1');
更改后的 City1 表的数据是这样的:
此时我们进行内连接可以得到:
select * from city1 inner join country1
on city1.country_id = country1.country_id;
外连接
外连接(Outer Join)是相对于内连接而言的,它不仅显示符合条件的记录,也显示不符合条件的某张表的数据:
- 左连接(Left Join):不仅显示符合条件的记录,也显示不符合条件的左表的数据
select * from city1 left join country1 on city1.country_id = country1.country_id;
- 右连接(Right Join):不仅显示符合条件的记录,也显示不符合条件的右表的数据
select * from city1 right join country1 on city1.country_id = country1.country_id;
子查询
有时候我们通过一次查询并不能得到想要的结果,需要在一次查询的基础上再进行查询,这就是所谓的子查询。
我们以 sakila数据库的customer表和payment表 为例,其数据如下:
- customer表
- payment表
下面进入正题:
select * from customer where
customer_id in (select customer_id from payment where amount>10);
-- 支付额大于10的顾客信息(顾客支付表和顾客信息表的联合查询)
-- 试试下面这个康康有啥子区别?
/* select * from customer inner join payment on
customer.customer_id=payment.customer_id and payment.amount>10; */
select * from customer where
exists (select customer_id from payment
where payment.customer_id=customer.customer_id and amount>10);
-- 效果同上,不同的是EXISTS先执行括号外语句
-- 这个不如上面那句简单清晰,建议使用上面那个
下面再介绍两个常用的函数any()
和all()
select * from customer where
customer_id < any(select distinct customer_id from payment where amount > 10);
-- 小于任意一个编号即可,即<max()
select * from customer where
customer_id < all(select distinct customer_id from payment where amount > 10);
-- 小于所有的编号,即<min()
联合查询
有时候我们需要将查询到结果合并起来,这时候就需要用到联合查询:Union
-- 联合查询
select count(customer_id) from sakila.rental; -- 返回16045条结果
select count(customer_id) from payment; -- 返回16050条结果
select customer_id from rental union select customer_id from payment;
-- 自动去重,返回599条结果
select customer_id from rental union all select customer_id from payment;
-- 不去重,返回32093条结果