基本查询
一 SQL查询基本结构
SQL查询是由三个基本语句构成，select，from和where，更多的语句也是在这个基础上进行拓展。from后面添加模型关系，在这些关系上进行where和select语句中指定的运算，然后产生一个关系作为结果，查询的结果是一个元组。
二 单关系模型查询
我们使用如下结构构建我们的表结构和数据。
# 使用use + 库名切换数据库
use db1;
# 部门表
create table department
(
department_name varchar(20),
building varchar(15),
budget numeric(12, 0),
primary key (department_name)
);
# 产品表
create table product
(
# 用int类型 改成自动增长的主键
product_id int primary key auto_increment,
title varchar(20) not null,
credits numeric(8, 0),
department_name varchar(20),
foreign key (department_name) references department (department_name)
on update cascade
on delete cascade
);
# 人员表
create table staff
(
staff_id varchar(10),
name varchar(15) not null,
age int,
# 关联的外键与被关联外键数据类型应该一致
product_id int,
department_name varchar(20),
primary key (staff_id),
foreign key (product_id) references product (product_id),
foreign key (department_name) references department (department_name)
on update cascade
on delete cascade
);
insert into department(department_name, building, budget)
values ('市场部', '市场部大楼', 35000000),
('研发部', '研发部大楼', 20000000),
('人事部', '人事部大楼', 1000000);
insert into product(title, credits, department_name)
values ('1号产品', 888888, '研发部'),
('2号产品', 7777, '研发部'),
('3号产品', 999999, '研发部'),
('4号产品', 100000, '市场部'),
('5号产品', 5000000, '人事部');
insert into staff(staff_id, name, age, department_name,product_id)
values ('1', 'Albert', 18, '市场部',1),
('2', 'James', 35, '市场部',2),
('3', '刘德华', 50, '研发部',3),
('4', '张学友', 35, '人事部',4),
('5', '孙悟空', 500, '人事部',5);
单模型关系基本的查询语句使用起来非常简单，就像讲话一样方便。
# 1 找出所有的产品
select title
from product;
# 2 找出负责所有的产品的部分
select department_name
from product;
# 这样看到的结果会有重复，保留重复元组在大部分的数据库软件中是默认的，如异，可以使用all指明不重复
select all department_name
from product;
# 我们希望看到的结果肯定是没有重复的，在必要的地方使用distinct来完成基本去重
select distinct department_name
from product;
# 3 select中可以使用 + - * / 算数运算，这只是查询结果的的改变，不会影响模型关系的任何变化
select budget + 10000000
from department;
select budget - 800000
from department;
select budget * 1.1
from department;
select budget * 2
from department;
# 4 where允许只选出满足特定条件的元组
# (1) 查出年龄超过100的员工
select name
from staff
where age > 100;
# (2) 查出年龄超过30的市场部人员
select name
from staff
where age > 30
and department_name = '市场部';
三 多关系模型查询
1. 多表查询基本形式
依然是上面的表结构，这里面有三个模型关系，我们可以是使用非常简单的查询语句select * from 表名查询出每个表的数据如下：
| department_name | building | budget |
|---|---|---|
| 人事部 | 人事部大楼 | 1000000 |
| 市场部 | 市场部大楼 | 35000000 |
| 研发部 | 研发部大楼 | 20000000 |
| product_id | title | credits | department_name |
|---|---|---|---|
| 1 | 1号产品 | 888888 | 研发部 |
| 2 | 2号产品 | 7777 | 研发部 |
| 3 | 3号产品 | 999999 | 研发部 |
| 4 | 4号产品 | 100000 | 市场部 |
| 5 | 5号产品 | 5000000 | 人事部 |
| staff_id | name | age | product_id | department_name |
|---|---|---|---|---|
| 1 | Albert | 18 | 1 | 市场部 |
| 2 | James | 35 | 2 | 市场部 |
| 3 | 刘德华 | 50 | 3 | 研发部 |
| 4 | 张学友 | 35 | 4 | 人事部 |
| 5 | 孙悟空 | 500 | 5 | 人事部 |
如果我们需要查询出所有的员工名字以及他们负责的产品和所在的部门，就需要找一个关联关系进行跨表查询。
select distinct name, title, staff.department_name
from department,
product,
staff
where staff.department_name = product.department_name;
2. 查询语句的理解
再来看一下select，from和where三个语句的作用：
- select语句用于列出查询结果所需要的属性
- from语句是查询求值中需要访问的关系模型
- where语句是作用在from语句关系之上的限制要求
where限制要求是可选项，如果没有where语句，则限制要求默认为True。
尽管查询语句必须以select，from，where这样的次序书写，但理解查询所代表的运算最容易的方式是按照运算的顺序来看：首先是from，然后是where，最后是select。
3. 查询过程说明
查询过程是迭代循环，可能在这个过程中不同关系模型中会出现相同的属性名，所以我们在属性名前面加上关系名作为说明，表示该属性来自于哪个关系。如果是单个关系模型，我们通常会去掉关系名，因为这样不会造成任何混淆。查询结果是求笛卡尔积，如果我们我们不指定条件，使用select * from department,product,staff语句，那么即使是我们测试的数据量这么小，得出的结果也会使你眼花缭乱，而且得出的这些组合也是没有意义的，where语句就是限制笛卡尔积所建立的组合，只留下那些对所需答案有意义的组合。
四 自然连接
1. 自然连接说明
在多关系模型查询中我们往往需要多个表的组合信息，匹配条件是属性名相同，为了简化SQL开发者的工作，我们可以使用自然连接作用在模型关系之上，自然连接会自动的将多关系模型中的所有元组进行连接，重要的是：自然连接只考虑那些在多关系模型中都出现的属性相同的元组对。
# 查询每个部门所负责的产品
# 不实用自然连接
select product.department_name, title
from department,
product
where department.department_name = product.department_name;
# 使用自然连接
select product.department_name, title
from department
natural join product;
2. 多关系自然连接
自然连接可以两个关系连接，也可以多个关系连接。
# 查出一个人负责一个产品的人，产品和所在部门，然后给他涨工资
select name, title, department_name
from staff
natural join department
natural join product;
自然连接的结果依然是关系，所以在自然连接之后的结果依然可以像模型关系那样使用在from语句中。
select name, title
from staff
natural join department,
product;
3. 自然连接扬长避短
自然连接会只考虑那些在多关系模型中都出现的属性相同的元组对，也就是说会考虑多关系模型中所有属性相同的元组对，而在有些时候，我们并不需要所有的元组对。为了发扬自然连接的有点，同时避免不必要的相等的属性带来的限制，SQL提供了一种自然连接的构造形式，允许用书来指定需要哪些属性相等，我们使用using语句来完成，这时是限制性连接而不是自然连接，所以natural关键字就不再需要了。
select name, title
from staff
natural join product
join department using (department_name);
五 SQL附加运算
1. 更名运算
数据库设计者为了查询方便，有些字段名会使用一样的，对于调用者在复杂查询中就可能会混淆。而有些时候这个字段名又会很长，调用者使用不方便，这时我们可以使用as对属性名做更名。
select department_name as Dname
from department;
as可以使用在select中也可以使用在from中
select D.department_name as Dname, title as T
from department as D,
product as P
where D.department_name = P.department_name;
需要注意的是：在一些SQL版本，尤其是Oracle中，不允许在from中使用as，但是也有同样的功能，所以这并不影响大局，Oracle中会把select department_name as Dname语句写成select department_name Dname，幸运的是，在MySQL中as也可以省略，所以影响不大(把以上语句所有as都去掉依然可以正常执行)。
2. 字符串运算
(1) SQL标准
在SQL标准中，字符串的相等运算是大小写敏感的，所以在表达式staff.name = staff.Name的结果是假，然而在一些特定的数据库，如MySQL和SQL Server中，默认并不区分大小写，所以返回结果可能为真，但是这种默认方式是可以在数据库级或者特定属性级做修改的。SQL中还允许在字符串上有变种函数，如upper(s)，lower(s)和trim(s)去除空格。
(2) like匹配
在字符串上可以使用like操作符来实现模式匹配。
- 百分号
%：匹配任意字符串 - 下划线
_：匹配任意一个字符，模式匹配中大小写是敏感的，例如：Pro%匹配任何以‘Pro’开头的字符串，% Pro %匹配任何包含‘Pro’的字符串。 - 三个下划线
___：匹配只包含三个字符的字符串 ___%：匹配至少包含三个字符的字符串
为了使模式中能够包含特殊模式的字符(百分号或者下划线)，SQL中允许定义转义字符，使用的时候转义字符直接放在特殊字符前面，在like比较运算中使用escape关键字来定义转义字符。
# \ 为定义的转义字符，
# 转义第一个%为字符串，匹配所有以abc%d开头的字符串
like 'abc\%d %' escape '\'
# 第一个\为转义字符，匹配所有以abc\d开头的字符串
like 'abc\\d %' escape '\'
SQL中还允许使用not like比较运算搜索不匹配项。
# 匹配所有不是以abc%d开头的字符串
not like 'abc\%d %' escape '\'
(3) 正则匹配
SQL可以支持使用REGEXP来进行正则匹配，用法和Python，PHP等语言非常类似，这里我们给出几个简单的示例。
select *
from staff
where name REGEXP 'A.';
select *
from staff
where name REGEXP '^A';
select *
from staff
where name REGEXP 't$';
select *
from staff
where name REGEXP '[abc]';
select *
from staff
where name REGEXP '[^abc]';
select *
from staff
where name REGEXP '[a|b|c]';
3. 序列运算
(1) 基本排序
SQL为用户提供了一种对关系中元组显示次序的控制，使用order by语句可以时候查询结果中的元组按照排列顺序显示。
# 按照年龄大小排序，默认是升序
select name
from staff
order by age;
# 添加desc改为降序
select name
from staff
order by age desc;
# 按照字符编码表排序，默认是从前往后，asc可以省略
select name
from staff
order by name asc;
# 从后往前排序
select name
from staff
order by name desc;
(2) 时间排序
除此之外，我们常用的还有按照时间排序，这些我们先介绍以下用于存储时间的数据类型。
- date：日期，包括年月日
- time：一天当中的时间，包括时分秒
- timestamp：data与time的组合，包括年月日和时分秒
除此之外，SQL中定义了一些函数获取当前的时间和日期。
- current_date：当前日期
- current_time：当前时间(带有时区，时区可以在配置文件中设置，也可手动加减)
- localtime：当前本地时间，与
select now()结果一样 - current_timestamp：带有时区的时间戳(时间戳指的是年月日和时分秒的组合，从Unix元年1970年开始计时)
- localtimestamp：本地时间戳
create table time1
(
name char(10),
data_time date
);
create table time2
(
name char(10),
time_time time
);
create table time3
(
name char(10),
timestamp_time timestamp
);
insert into time1 (name, data_time)
values ('1', '2001-04-25'),
('2', '2021-04-25'),
('3', '2011-01-25'),
('4', '2001-12-02'),
('5', '2001-04-05'),
('6', '2001-04-11');
# 插入数据的时候可以随意一些，但是存储的时候还是按照规范2001-04-11存储
insert into time1 (name, data_time)
values ('1', '2001-04-5'),
('2', '2001-4-11');
insert into time2 (name, time_time)
values ('1', '09:30:00'),
('2', '21:4:11'),
('3', '00:4:11'),
('4', '21:14:11'),
('5', '21:42:11'),
('6', '21:47:11');
insert into time3 (name, timestamp_time)
values ('1', '2001-04-5 09:30:00'),
('2', '2011-04-5 09:30:00'),
('4', '2011-04-5 01:30:00'),
('5', '2011-04-5 10:30:00'),
('6', '2011-04-5 01:30:01');
select *
from time1
order by data_time;
select *
from time1
order by data_time desc;
select *
from time2
order by time_time;
select *
from time2
order by time_time desc;
select *
from time3
order by timestamp_time;
select *
from time3
order by timestamp_time desc;
select current_date;
select current_time;
select localtime;
select now();
select current_timestamp;
select localtimestamp;
4. 比较运算
(1) between比较
为了简化where语句，SQL提供了between比较运算符来说明一个值小于等于某个值，同时打不等于另外一个值。
use db1;
# 不支持 30 <= age <= 100
select name
from staff
where age <= 100
and age >= 30;
select name
from staff
where age between 30 and 100;
同理，我们还是可以使用not between来做排除筛选
select name
from staff
where age not between 30 and 100;
(2) 分量比较
在SQL中使用(v1,v2,v3,...,vn)来表示一个分量的值分别为v1，v2，v3，vn的n维元组，在元组上可以使用比较运算，按照字典的顺序进行一对一比较，比如(a1,a2) <= (b1,b2)，则代表a1<=b1并且a2<=b2，同理，比较相当的时候也是一样的。
# 查出研发部负责3号产品的人
select name
from staff,
product
where (product.title, staff.department_name) = ('3号产品', '研发部');
5. 集合运算
(1) 集合运算说明
SQL中有语句union，intersect和except分别对应数学集合中并，交，差运算。
(2) 并运算
与select语句不同的是，union语句会自动去除重复。
select name
from staff
where staff.product_id = 1;
(select name
from staff,
product
where staff.product_id = 1)
union
(select name
from staff,
product
where staff.product_id = 2);
如果我们希望保留重复，需使用union all代替union。
(select name
from staff,
product
where staff.product_id = 1)
union all
(select name
from staff,
product
where staff.product_id = 2);
(3) 交运算
非常遗憾的是MySQL中没有交运算的instersect语句，但是这样的语句在Oracle，PostgreSQL和SQL Server中都是有的。
# 适用于Oracle，PostgreSQL和SQL Server
(select name
from staff
where staff.department_name = '市场部');
instersect
(select name
from staff
where staff.product_id = 2);
# instersect 语句会自动去重，如果希望显示全部结果，在后面添加一个all即可
# MySQL中等价用法可以用分量比较
select name
from staff
where (staff.product_id, staff.department_name) = (2, '市场部');
(4) 差运算
MySQL中也没有差运算的except,但是在PostgreSQL和SQL Server中有，Oracle中使用MINUS语句来实现同样的功能，并运算和交运算都很好理解，差运算这里说明一下：指的是前一个结果关系中有而后一个结果关系中没有，也就是前一个比后一个多的部分，即前一个减去后一个，这也就是差的来源。
# PostgreSQL和SQL Server
(select name
from staff
where staff.department_name = '市场部');
except
(select name
from staff
where staff.product_id = 2);
# 同理，自动去重，可以加all显示全部
MySQL中类似于差运算的语句将会在我们下一章节的内容中讲解。
6. 空值运算
在生产环境中，数据库出现空值是很常见的，空值运算也给我们带来了特殊的问题。如果在比较运算中出现1 > null这样的语句，由于我们不知道空值代表的是什么，所以也发评定结果是为真还是为假，但是根据我们的逻辑语句来推理，如果1 > null为真，那么not (1 > null)就应该为假，反之，亦然。但是这却并没有什么意义，数据库设计者为了程序的健壮性，将涉及空值的任何比较运算的结果视为unknown，这是除了true和false之外的第三个逻辑。由于where语句可以使用and，or和not做逻辑运算，所以unknown也被扩展到这三个关键字上面。
- and：
ture and unknown的结果是unknown，false and unknown的结果是false，unknown and unknown的结果是unknown。 - or：
ture or unknown的结果是true，false or unknown的结果是unknown，unknown or unknown的结果是unknown。 - not：
not unknown的结果是unknown。
如果是在where语句中对一个元组计算出false或者unknown，那么该元组自然不能被加入到结果中。在where语句中可以使用特殊关键字null来测试空值，也允许使用is unknown来测试一个表达式的结果是否为unknown，它们两种的用法前面添加not自然也是可以使用的。
# 插入空值
insert into staff(staff_id, name, product_id)
values ('6', '小龙女', 1);
select *
from staff;
# 找空值
select *
from staff
where age is null;
select *
from staff
where age is not null;
# 条件为unknown
select *
from staff
where name = '小龙女'
and age > 15;
# 认为条件正确，取其结果
select *
from staff
where (name = '小龙女'
and age > 15) is unknown;
# unknown is True 自然是false，无结果
select *
from staff
where (name = '小龙女'
and age > 15) is true;
# 认为条件不正确，取反
select *
from staff
where (name = '小龙女'
and age > 15) is FALSE;
