一、数据库基础
1.定义:
Database:A database is an organized collection of data,stored and accessed electronically. [wikipedia].
数据库:数据库是按照数据结构来组织、存储和管理数据的仓库。【百度百科】
2.易混淆术语:
数据库系统(DBS):是指在计算机系统中引入数据库后的系统,一般由数据库、数据库管理系统、应用系统、数据库管理员(DBA)构成。
数据库管理系统(DBMS):是一种操纵和管理数据库的大型软件,用于建立、使用和维护数据库(如:MySQL)。
数据库(DB):数据库是按照数据结构来组织、存储和管理数据的仓库。
注意:我们通常用数据库这个术语来代表 DBMS,严格来说,这是不正确的,容易产生混淆。
3.分类:
- 关系型数据库
不仅存储数据本身,还存储数据之间的关系,比如说用户信息和订单信息。关系型数据库模型把复杂的数据结构归结为简单的二维表(关系表) 。
数据和信息,信息是有意义的数据。 - 非关系型数据库
非关系型数据库也被称为NoSQL数据库。NoSQL的产生并不是要否定关系型数据库,而是作为关系型数据库的一个有效补充。
4.DB-Engines排行榜(2019/5)
1970 E.F.Codd 提出
1974 SEQUEL 论文发表
1979 第一个商用关系型数据库 Oracle 2 诞生
1995 开源数据库 MySQL 诞生
2008 MySQL被SUN公司收购
2010 SUN 被 Oracle 收购
非关系型数据库:
文档型
键值型
搜索引擎
列存储
图形数据库
1970:NoSQL = We have no SQL
1980:NoSQL = Know SQL
2000:NoSQL = No SQL!
2005:NoSQL = Not only SQL
2013:NoSQL = No, SQL!
为什么NoSQL最后证实了关系型数据库的重要性呢?
标准问题。任何关系型数据库都可以用SQL语言对数据库进行操作,关系型数据库之间的迁移是比较容易的。而NoSQL没有固定的标准。
5.数据库的架构(C/S 架构)
数据库管理系统、数据库服务、数据库和表的关系
客户端和服务器如何通信?
通过网络通信,即两个程序之间的的通信。
数据在表中的形式
对象与行对应,属性与列对应。
ORM (Object Relational Mapping)框架:期望通过操作对象的方式去操作数据库,比如Hibernate & MyBatis。
二、MySQL安装和使用
三、SQL概述
SQL是结构化查询语言(Structured Query Language)的缩写。它是一种专门用来与关系型数据库沟通的语言。
它主要有如下的优点:
- SQL 是一种通用语言,几乎所有的关系型数据库都支持 SQL。
- SQL 简单易学。它的语句是由一些有很强描述性的关键词组织而成, 而且这些关键词并不多。
- SQL 虽然简单,但它是一种强有力的语言,灵活地使用 SQL, 可以进行非常复杂的数据库操作。
- 半衰期很长 ,SQL标准:SQL92, SQL99
SQL 的扩展:
标准 SQL 是由 ANSI 标准委员会管理的,从而称为 ANSI SQL。许多 DBMS 厂商通过增加语句或指令,对 SQL 进行了扩展,目的是提供一些特定的操作,或者是简化某些操作。 虽然这种扩展很有必要,但同时也给 SQL 代码的移植带来了麻烦。 ——SQL方言
即使 DBMS 有自己的扩展,但它们都支持 ANSI SQL。
注意:请正确认识 “SQL 不区分大小写“
虽然 SQL 不区分大小写,但是表名、列名和值可能区分!(这依赖具体的 DBMS 及其配置)。
SQL 不区分大小写(关键字不区分大小写)!!!
关键字大写, 表名,列名,值最好是以它定义时值。
组成:
DDL: 数据定义语言
DML:数据操作语言 (增,删,改)
DQL: 数据查询语言 (查)
DCL: 数据控制语言
TPL: 事务处理语言
…
四、SQL语言
0.字符集和校对规则
校对集:定义了字符集中字符的比较规则。校对集依赖于字符集而存在,一个字符集可以有多个校对集。所以字符集和校对集是一对多的关系。
校对集以ci结尾不区分大小写,以bin结尾区分大小写
################################## 字符集和校对规则 ###############################
show character set;
show variables like '%char%';# 查看系统默认字符集
select @@character_set_database;
show collation; # 查看所有的校对集
1.数据定义语言(DDL)
DDL:Data Definition Language
作用:创建 & 管理数据库和表的结构。
常用关键字:
CREATE , ALTER , DROP
# 2. 创建数据库
# 语法:create database [if not exists] db_name [specifications]
# 练习:
# 创建一个名称为mydb1的数据库。
create database mydb1;
create database mydb1; #create database mydb1 Error Code: 1007. Can't create database 'mydb1'; database exists
create database if not exists mydb1; #create database if not exists mydb1 1 row(s) affected, 1 warning(s): 1007 Can't create database 'mydb1'; database exists
# 创建一个使用gbk字符集的mydb2数据库。
create database if not exists mydb2 character set gbk;
# 创建一个使用gbk字符集,并带校对规则(gbk_bin)的mydb3数据库。
create database if not exists mydb3 character set gbk collate gbk_bin;
# 1. 查看数据库
show databases; # 查看所有的数据库
show create database mydb1; # 查看数据库的创建语句
show create database mydb2;
show create database mydb3;
# 3. 删除数据库
# 语法:drop database [if exist] db_name;
# 练习:删除前面创建的mydb3数据库
drop database mydb3;
drop database mydb3;
drop database if exists mydb3;
# 4. 修改数据库
# 语法:alter database db_name [specifications]
# 练习:把mydb2的字符集修改为utf8
alter database mydb2 character set utf8;
# 5. 创建表
# 语法:
-- create table tb_name (
-- field_name1 dtype1,
-- field_name2 dtype2,
-- field_name3 dtype3
-- )[specifications];
# 注意事项:创建表之前要使用数据库
use mydb1;
# 练习:创建User表,包含(id, name, password, birthday)字段
create table t_user (
id int,
name varchar(255),
passsword varchar(255),
birthday date
);
# 6. 查看表
show tables; # 查看当前数据所有表
show tables in world; # 查看指定数据库中所有表
describe t_user; # 查看表的结构
desc t_user; # describe t_user的缩写
show create table t_user; # 查看表的创建语法
RENAME TABLE 语句的另一个用法是移动该表到另一个数据库
语法为:
RENAME TABLE 旧数据库名.旧表名 TO 新数据库名.新表名
提示:我们可以把 RENAME TABLE 的这两种用法很好地统一起来,如果我们把 “重命名” 理解为 “在同一数据库里的移动”。甚至我们可以省略数据库名,如果你恰好正在使用该数据库。
# 7. 修改表
# a. 添加列
# 语法1:alter table tb_name add [column] 列的定义 [, add column 列的定义]
# 练习:添加gender列, 类型为varchar(255).
alter table t_user add column gender varchar(255);
# 语法2:alter table tb_name add column 列的定义 after field_name;
# 练习:在name后面添加balance列, 类型为int
alter table t_user add column balance int after name;
# 语法3:alter table tb_name add column 列的定义 first;
# 练习:在前面添加a列,类型为int
alter table t_user add column a int first;
# 练习:一次性添加b和c列, 类型都为int类型。
alter table t_user add column b int, add column c int;
# b. 修改列
# 修改列的名称
# 语法1:alter table tb_name change column col_name new_col_name dtpye;
# 练习:把balance修改成salary
alter table t_user change column balance salary int;
# 修改列的定义
# 语法2:alter table tb_name modify column col_name dtpye;
# 练习:把gender的类型修改为 bit(1)
alter table t_user modify column gender bit(1);
# c. 删除列
# 语法:alter table tb_name drop column col_name;
# 练习:删除a列
alter table t_user drop column a;
# 练习:删除b,c列,同时将salary的名字改成balance
alter table t_user drop column b ,drop column c, change column salary balance int;
# d. 修改表的名称
# 语法:rename table tb_name to new_tb_name;
# 练习:将t_user修改成t_student;
rename table t_user to t_student;
# 迁移表
# 练习:将t_student迁移到mydb2;
rename table t_student to mydb2.t_student;
show tables;
show tables in mydb2;
# 练习:将mydb2中的t_student表迁移到mydb1, 并将表的名字修改为t_user;
rename table mydb2.t_student to t_user;
# e. 修改表的字符集和校对规则
# 语法:alter table tb_name character set charset_naem collate collation_name;
# 练习:把t_user的字符集修改成utf8
alter table t_user character set utf8;
show create table t_user;
# 8. 删除表
# 语法:drop table tb_name;
# 练习:删除t_user表
drop table t_user;
show tables;
2.数据类型
注意:这里以MySQL为例,不同的DBMS的都支持数值类型,字符串类型以及日期类型,但他们的实现可能不一样。
-
数值类型
整数类型
浮点数类型和定点数类型
其中 M 称为精度,表示总共的位数; D 称为标度,表示小数的位数。
DECIMAL 类型不同于 FLOAT & DOUBLE,DECIMAL 实际是以字符串存放的,它的存储空间并不固定,而是由精度 M 决定的。 -
日期与时间类型
DATETIME 和 TIMESTAMP (时间戳)虽然显示的格式是一样的,但是它们有很大的区别:
a.DATETIME 的系统默认值是 NULL, 而 TIMESTAMP 的系统默认值是当前时间 NOW();
b.DATETIME 存储的时间与时区无关,而 TIMESTAMP 与时区有关。 -
字符串类型
ENUM 类型总有一个默认值,当ENUM 列声明为NULL,则默认值为NULL。如果 ENUM 列被声明为 NOT NULL,则其默认值为列表的第一个元素。 -
二进制类型
字符串类型存储的字符串(字符),
二进制类型存储的是二进制数据(字节)。
########################### 数据类################################
# 1. 数值类型
# 1.1 整数类型
-- 类型 占用字节数 表示范围
-- tinyint 1 256
-- smallint 2 65536
-- mediumint 3 ...
-- int 4 约43亿
-- bigint 8 ...
# 1.2 小数类型
# float(M,D) double(M,D) decimal(M,D)
# 2. 日期类型
# year, date, time, datetime, timestmap
# timestamp 存储自基准时间(格林威治时间1970年1月1日0点0分0秒)以来的 秒数
# datetime和timestamp的区别
# a. DATETIME的系统默认值是NULL, 而TIMESTAMP的系统默认值是当前时间NOW();
create table t_time (
a datetime,
b timestamp
);
insert into t_time (a) values (now());
insert into t_time (b) values (now());
select * from t_time;
# b. DATETIME存储的时间与时区无关,而TIMESTAMP与时区有关。
set time_zone = '+10:00';
select * from t_time;
set time_zone = '+8:00';
select * from t_time;
# 3. 字符类型
# char(M), varchar(M), tinytext(等价于varchar(255)), text, mediumtext, longtext, enum, set
# char(M) 和 varchar(M)的区别
# eg: char(10): "hello" --> 占10个字节, varchar(10): "hello" --> 占6个字节,用一个字节表示hello的长度
# enum:枚举
create table t_enum(
gender enum('male', 'female')
);
insert into t_enum values('male'); # Y
insert into t_enum values('female'); # Y
insert into t_enum values('male,female'); # N
insert into t_enum values('transgender'); # N
select * from t_enum;
create table t_enum1 (
a int,
gender enum('male', 'female')
);
insert into t_enum1 (a) values (1);
insert into t_enum1 (gender) values ('female');
select * from t_enum1;
create table t_enum2 (
a int,
gender enum('male', 'female') not null
);
insert into t_enum2 (a) values (1);
insert into t_enum2 (gender) values ('female');
select * from t_enum2;
# set: 集合
create table t_set(
hobbies set('a','b','c','d')
);
insert into t_set values ('a'); # Y
insert into t_set values ('a,b,c'); # Y
insert into t_set values ('d,b,a'); # Y
insert into t_set values ('d,b,a,d,a'); # Y
insert into t_set values ('x'); # N
insert into t_set values ('a,b,x'); # N
select * from t_set;
# 4. 二进制类型
# bit(M), binary(M), varbinary(M), tinyblob, blob, mediumblob, longblob
# 注意事项:在MySQL中字符串类型和日期类型都应该用单引号括起来
3.DML数据操纵语言
DML:Data Manipulation Language
作用:用于向数据库表中插入、删除、修改数据。
常用关键字:
INSERT ,UPDATE , DELETE
############################# DML ############################
# 1. 插入数据
# 语法:insert into tb_name [(column[, column...])] valuse (value[,value...]);
use mydb1;
create table t_user(
id int,
name varchar(255),
age int,
gender enum('female','male')
);
insert into t_user values (1, '刘亦菲', 16, 'female');
insert into t_user values (2, '王语嫣', null, 'female');
insert into t_user (id,name) values (3, '王宇');
insert into t_user (name, id) values ('杨平',4);
insert into t_user (id, name) values (5, '王五'),(6,'赵六'),(7,'孙七');
select * from t_user;
create table user(
id int,
name varchar(255),
age int,
gender enum('female','male')
);
insert into user(select * from t_user);
select * from user;
# 2. 更新数据
# 语法:update tb_name set col_name=val [, col_name=value..] [where条件]
select * from t_user;
# 练习:将王语嫣的age设置成17
update t_user set age = 17 where name='王语嫣';
update t_user set age = 17;
# 主要事项:不要忘记写where条件,除非你想更新所有记录。
# 3. 删除数据
# 语法:delete from tb_name [where条件]
# 练习:删除名字为'王五'的记录
delete from t_user where name='王五';
delete from t_user;
select * from t_user;
show tables;
desc t_user;
注意事项:
a. 删除的基本单位是记录,如果你想把某条记录的某个字段"删除",应该使用update。
b. 不要忘记写where条件,除非你想删除所有记录。
c. 仅仅是删除数据,不会删除表。删除表应该使用drop table。
4.数据的导入和导出
- 数据导入
方式一(cmd):mysql -u $user -p db_name < file.sql
方式二(mysql): source file.sql - 数据导出
方式(cmd):mysqldump -u $user -p db_name > file.sql
5.常见运算符介绍
- 算术运算符 + , - , * , / , %
- 比较运算符
# 查询没有辅助角色定义的英雄有哪些?
select name, role_assist from heros where role_assist = null;
select name, role_assist from heros where role_assist <=> null;
select name, role_assist from heros where role_assist is null;
select null = null;
select null <=> null;
select * from heros;
# 查询有辅助角色定位的英雄有哪些?
select name, role_assist from heros where role_assist != null;
select name, role_assist from heros where role_assist is not null;
# between and: 是否在某个闭区间内
# in: 是否在列表内
# not in: 是否不在列表内
# 查询最大生命大于等于5399,小于等于7350的英雄有哪些?
select name,hp_max from heros where hp_max >= 5399 and hp_max <= 7350;
select name,hp_max from heros where hp_max between 5399 and 7350;
# 查询主要角色定位为法师和射手的英雄有哪些?
select name,role_main from heros where role_main='法师' or role_main='射手';
select name,role_main from heros where role_main in ('法师', '射手');
# 查询主要角色定位不是辅助和坦克的英雄有哪些?
select name,role_main from heros where role_main!='辅助' and role_main!='坦克';
select name,role_main from heros where role_main not in ('辅助', '坦克');
# like: 与通配符搭配使用,表示模糊查询。
# %: 匹配任何数目的字符,包括零字符
# _: 只能匹配一个字符
# 查询姓张的英雄有哪些?
select name from heros where name like '张%';
select name from heros where name like '%太%';
# 查询除第一个字符外,名字中包含'太'的英雄有哪些?
select name from heros where name like '_%太%';
# 注意:LIKE 关键字一般是与通配符一起使用, 在 MySQL 中 '_' 表示匹配单个字符,
# '%' 表示匹配任意个字符。在不同的 DBMS 中,通配符的符号可能不一样。
# 比如,在 ACCESS 中使用 '?' 匹配单个字符,使用 '*' 匹配任意个字符。
- 逻辑运算符
NOT(!) , AND(&&) , OR(||) - 位操作运算符
& , | , ~ , ^ , << , >>
6.DQL数据查询语言(简单查询)
DQL:Data Query Language
作用:查询表中的数据。
关键字:
SELECT
简单查询的关键字是 SELECT ,它的用法非常丰富。
- 计算表达式和函数的值
虽然 SELECT 语句通常用于从表中检索数据,但我们也可以用它计算表达式和函数的值。
# 1. 计算表达式和函数的值
select 2 * 3;
select now();
select concat('a','b','c','d'); #拼接字符
select sqrt(9); #开根号
- 查询表中的字段
查询单个字段的值,比如:查询 heros 表中所有英雄的名字。
查询多个字段的值,多个字段之间用 , 分隔。比如:查询 heros 表中所有英雄的名字,最大生命值,最大法力值以及主要角色定位。
还可以用 * 代指所有字段。比如:查询 heros 表中所有数据。
# 2. 查询表中的字段
select name from heros;
select name,hp_max,mp_max,role_main from heros;
select * from heros;
注意:在生产环境中,尽量不要使用 * 通配符。因为查询不必要的数据会降低查询和应用程序的效率!
- 使用 WHERE 子句过滤记录
WHERE 子句后面接逻辑表达式。如果逻辑表达式的结果为真,这条记录就会添加到结果集中,否则就不会添加到结果集。
注意:LIKE 关键字一般是与通配符一起使用, 在 MySQL 中 ‘_’ 表示匹配单个字符, ‘%’ 表示匹配任意个字符。在不同的 DBMS 中,通配符的符号可能不一样。比如,在 ACCESS 中使用 ‘?’ 匹配单个字符,使用 ‘*’ 匹配任意个字符。
# 3. 使用 WHERE 子句过滤记录
# 查询主要角色定位为'法师'的英雄有哪些?
select name,role_main from heros where role_main='法师';
- 给字段起别名
AS 可以给字段起别名。比如:
# 4. 给字段起别名
select name, hp_max as hp, mp_max as mp from heros;
select name, hp_max hp, mp_max mp from heros;
注意:① AS 关键字可以省略,但是不推荐这样做。② AS 关键字不仅仅可以给字段起别名,还可以给表起别名。
- 去除重复行
DISTINCT 可以对查询结果去重。
# 5. 去除重复行
# 查询主要角色定义有哪些?
# select role_main from heros;
select distinct role_main from heros;
# 查询不同的主要角色定位和辅助角色定位。
select distinct role_main, role_assist from heros;
注意:① DISTINCT 是对所有查询字段的组合进行去重,也就是说每个字段都相同,才认为两条记录是相同的。② DISTINCT 关键字必须放在所有查询字段的前面。
- 排序
ORDER BY 可以对结果集进行排序。 ASC 表示升序, DESC 表示降序,默认情况为升序。
还可以对多个字段进行排序。即先按照第一个字段排序,当第一个字段相同时,再按照第二个字段排序,依此类推。
ORDER BY 可以对非选择字段进行排序,也就是说排序的字段不一定要在结果集中。
甚至,我们还可以对计算字段进行排序。
# 6.排序
select name,hp_max from heros order by hp_max; # 升序
select name,hp_max from heros order by hp_max asc; # 升序
select name,hp_max from heros order by hp_max desc; # 降序
# 按照多个字段排序
select name,hp_max,mp_max from heros order by hp_max, mp_max;
select name,hp_max,mp_max from heros order by hp_max, mp_max desc;
select name,hp_max,mp_max from heros order by hp_max asc, mp_max desc;
# 对非选择字段进行排序
select name,hp_max from heros order by hp_max asc, mp_max desc;
# 对计算字段排序
select name,hp_max,mp_max from heros order by (hp_max + mp_max);
- 限制结果集的数量
LIMIT 可以限制结果集的数量。它有两种使用方式: LIMIT offset, nums 和 LIMIT nums OFFSET offset 。比如:我们想查询最大生命值最高的5名英雄。
当 OFFSET 为 0 的时候,我们可以将其省略。
使用 LIMIT 可以很方便地实现分页查询。
注意:不同的 DBMS 用来限制结果集的关键字是不一样的。比如,Microsoft SQL Server 和
Access 使用的是 TOP 关键字。
# 7. 限制结果集的数量
# 语法:LIMIT offset, nums; LIMIT nums OFFSET offset;
# 练习:我们想查询最大生命值最高的5名英雄。
select name, hp_max from heros order by hp_max desc limit 0, 5;
select name, hp_max from heros order by hp_max desc limit 5 offset 0;
# 当偏移量为0时,我们可以省略offset
select name, hp_max from heros order by hp_max desc limit 5;
# 分页查询(page, rows)
# limit rows offset (page-1)*rows
# 注意:不同的 DBMS 用来限制结果集的关键字是不一样的。
# 比如,Microsoft SQL Server 和 Access 使用的是 TOP 关键字。
- 计算字段
计算字段并不实际存在于数据库表中,它是由表中的其它字段计算而来的。一般我们会给计算字段起一个的别名。
# 8. 计算字段
select name, hp_max + mp_max from heros;
select name, hp_max + mp_max as total from heros;
- 聚合函数
聚合函数是对某个字段(列)的值进行统计的,而不是对某条记录进行统计。如果想计算某个学生各科成绩的总分,那么你应该使用计算字段。
聚合函数往往是搭配分组使用的。如果没有分组,那么聚合函数统计的是整个结果集的数据;如果分组了,那么聚合函数统计的是结果集中每个组的数据。
SQL 中一共有 5 个聚合函数。分别为 COUNT() , SUM() , AVG() , MAX() , MIN() 。
- a. COUNT()
COUNT(*) 可以统计记录数。
COUNT() 作用于某个具体的字段,可以统计这个字段的非 NULL 值的个数。 - b. SUM()
SUM() 用于统计某个字段非 NULL 值的和。 - c. AVG()
AVG() 用于统计某个字段非 NULL 值的平均值。 - d. MAX()
MAX() 用于统计某个字段非 NULL 值的最大值。 - e. MIN()
MIN() 用于统计某个字段非 NULL 值的最小值。 - f. DISTINCT
我们还可以对字段中不同的值进行统计。先用 DSITINCT 去重,再用聚合函数统计。
# 9. 聚合函数
# count(), sum(), avg(), max(), min()
# a. count()
# `COUNT(*)` 可以统计记录数, 可以统计null记录。
create table t_count(
a int,
b int
);
insert into t_count values (null, null), (1, null),(null, 2),(3, 3);
select * from t_count;
select count(*) as nums from t_count;
# `COUNT()` 作用于某个具体的字段,可以统计这个字段的非 `NULL` 值的个数。
select count(a) from t_count;
# b. sum()
SELECT SUM(hp_max) FROM heros;
# c. avg()
select avg(hp_max) from heros;
select round(avg(hp_max), 2) as avg from heros;
# d. max()
select max(hp_max) from heros;
# e. min()
select min(hp_max) from heros;
# f. distinct
select sum(hp_max) from heros;
select sum(distinct hp_max) from heros;
- 分组
GROUP BY 可以对记录进行分组。 - a. 搭配聚合函数使用
按照主要角色定位进行分组,并统计每一组的英雄数目。
按照次要角色定位进行分组,并统计每一组的英雄数目。
你会发现 NULL 值也会被列为一个分组。在 heros 表中有 40 个英雄没有次要角色定位。 - b. GROUP_CONCAT
如果我们想知道每种角色的英雄都有哪些,可以使用 GROUP_CONCAT() 函数。 - c. 多字段分组
我们可以对多个字段进行分组。也就是说,每个字段的值都相同的记录为一组。 - d. HAVING 过滤分组
HAVING 可以过滤分组。比如:我们想要按照英雄的主要角色定位,次要角色定位进行分组,并且筛选分组中英雄数目大于 5 的组,最后根据每组的英雄数目从高到低进行排序。
WHERE 和 HAVING 的区别:WHERE 和 HAVING 都可以用来过滤数据,但是两者有着很明显的区别。WHERE 是分组前用来过滤记录的,HAVING 是分组后用来过滤分组的。
我们可以通过一个具体的例子来看一下它们之间的区别。筛选最大生命值大于 6000 的英雄,按照主要角色定位,次要角色定位分组,并且筛选英雄数目大于 5 的分组,最后按照英雄数目从高到低进行排序。
# 10. 分组
# a.搭配聚合函数使用
# 练习:按照主要角色定位进行分组,并统计每一组的英雄数目。
select role_main, count(*) from heros group by role_main;
# 练习:按照次要角色定位进行分组,并统计每一组的英雄数目。
select role_assist, count(*) from heros group by role_assist;
# b.GROUP_CONCAT
# 练习:我们想查询每种角色的英雄都有哪些?
# select role_main, name from heros group by role_main;
select role_main, group_concat(name) from heros group by role_main;
# c. 多字段分组
SELECT COUNT(*) AS num, role_main, role_assist
FROM heros
GROUP BY role_main, role_assist
ORDER BY num DESC;
# d. HAVING过滤分组
# 练习:我们想要按照英雄的主要角色定位,次要角色定位进行分组,并且筛选分组中英雄数目大于 5 的组,
# 最后根据每组的英雄数目从高到低进行排序。
SELECT COUNT(*) AS num, role_main, role_assist
FROM heros
GROUP BY role_main, role_assist
HAVING num > 5
ORDER BY num DESC;
# 练习:筛选最大生命值大于 6000 的英雄,按照主要角色定位,次要角色定位分组,
# 并且筛选英雄数目大于 5 的分组,最后按照英雄数目从高到低进行排序。
SELECT COUNT(*) AS num, role_main, role_assist
FROM heros
WHERE hp_max > 6000
GROUP BY role_main, role_assist
HAVING num > 5
ORDER BY num DESC;
注意:虽然 DBMS 实现的时候,往往会对分组进行排序。但是如果没有明确的 ORDER BY 子句,我们就不应该假定结果集是有序的。
- SELECT 的顺序
SELECT 是 RDBMS 中执行最多的操作。我们不仅仅要理解 SELECT 的语法,还要理解它底层执行的原理。
有两个关于 SELECT 的顺序,我们需要记住。
a. 语法中关键字的顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
b. 语句的执行顺序
FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> DISTINCT --> ORDER BY -->LIMIT
不同的 RDBMS,它们 SELECT 语句的执行顺序基本是相同的。
举个例子:
SELECT DISTINCT team_id, team_name, count(*) AS num # 顺序5
FROM player JOIN team ON player.team_id = team.team_id # 顺序1
WHERE height > 1.80 # 顺序2
GROUP BY player.team_id # 顺序3
HAVING num > 2 # 顺序4
ORDER BY num DESC #顺序6
LIMIT 2; #顺序7
详细解释一下 SQL 的执行顺序:
- 首先是从 FROM 开始执行的。在这个阶段,如果是多表连接查询,还会经历以下几个步骤:
1.1 通过 CROSS JOIN 求得笛卡尔乘积,得到虚拟表 vt1-1;
1.2 通过 ON 进行连接,在 vt1-1 的基础上进行筛选,得到虚拟表 vt1-2;
1.3 如果是外连接,还会在 vt1-2 的基础上添加外部行,得到虚拟表 vt1-3;
1.4 如果连接的表不止两张,还会重复上面步骤,直到所有表都处理完成。这个过程完成之后,我们就得到了虚拟表 vt-1,也就是我们的原始数据。 - WHERE 会在 vt-1 的基础上进行筛选,得到虚拟表 vt-2。
- GROUP BY 会在 vt-2 的基础上进行分组,得到虚拟表 vt-3。
- HAVING 会在 vt-3 的基础上对分组进行筛选,得到虚拟表 vt-4。
- SELECT 会在 vt-4 的基础上提取想要的字段,得到虚拟表 vt-5。
- DISTINCT 会在 vt-5 的基础上,去掉重复行,得到虚拟表 vt-6。
- ORDER BY 会按照指定的字段对 vt-6 进行排序,得到虚拟表 vt-7。
- LIMIT 会在 vt-7 的基础上提取指定的记录,得到虚拟表 vt-8。
#个人学习记录,如发现有错误之处,欢迎与我交流