SQL基础

测试环境：https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc

1. 基本概念

表中的一行为一条数据，一列即为一个字段（可以认为相当于java中的属性）

1.1. SQL的种类

DDL(data definition language)：数据定义语言
- create：创建表(不局限于表)
- join：关联多个表
- drop：删除表(不局限于表)(不可回滚)
- alter：修改表的定义(不局限于表)
- truncate：删除数据，但不删除表定义(不可以指定行)(不可回滚)
DML(data manipulation language)：数据操作语言
- insert：添加数据
- update：修改数据
- delete：删除数据，但不删除表定义(可以指定行)
DQL(data query language)：数据查询语言
- select：查询
DCL(data control language)：数据控制语言
- grant：添加权限
- revoke：移除权限

2. DML

2.1. select

SQL的执行顺序

from, join(inner join, left join ...),on

生成一个临时表
where

筛选from指定的数据元
group by

分组，分组列意外的数据只能在聚合函数中使用
having

对于分组后的数据筛选
select

抽出需要的列
distinct

去重
order by

对于结果排序
limit

截取部分数据

2.1.1. 基础版

将Customers表中的所有列无条件地取出

-- from用来指定数据元（意为取什么表的数据）
select * from Customers;

将Customers表中的指定列无条件地取出

-- 可以指定多个列
select customerid, customername, contactname, address from Customers

2.1.2. where

将Customers表中的指定列的有条件地取出

-- 单一条件
-- 客户id大于等于40
select customerid, customername, contactname, address from Customers
where customerid >= 40;
-- 客户id等于40
select customerid, customername, contactname, address from Customers
where customerid = 40;
-- 客户id不等于40
select customerid, customername, contactname, address from Customers
where customerid != 40;
-- 多条件结合1
select customerid, customername, contactname, address from Customers
where customerid >= 40 and customerid <= 60;
-- 多条件结合2
select customerid, customername, contactname, address from Customers
where customerid between 40 and 60;
-- 多条件结合3
select customerid, customername, contactname, address from Customers
where customerid >= 40 or customerid <= 60;
-- 多条件结合4
select customerid, customername, contactname, address from Customers
where customerid >= 40 or customerid >= 30 and customerid = 20;
-- 多条件结合5(条件可以指定多个不同的列)(实际上也可以不使用select中指定的列)
select customerid, customername, contactname, address, country from Customers
where customerid >= 40 and country = 'USA';
-- 特殊条件
略（见统合）
-- 以上条件都是精准的，还有一种模糊的条件
select customerid, customername, contactname, address from Customers
where customername like 'M%'; -- %：占位符，代表0~N个任意字符，M%：M后有0~N个任意字符的都符合条件

2.1.3. order by

将select的结果排序

-- 单一条件升序
select customerid, customername, contactname, address from Customers
where customerid >= 40 or customerid <= 60
order by customerid; -- 不指定ASC（升序）或者DESC（降序）时，默认ASC
-- 单一条件降序
select customerid, customername, contactname, address from Customers
where customerid >= 40 or customerid <= 60
order by customerid desc;
-- 多条件排序
select customerid, customername, contactname, address, country from Customers
where customerid >= 40
order by country desc, customername; -- 字典序 ascii

2.1.4. group by

将数据分组

-- 不使用group by，不分组？实际上是把所有数据分在一组
select count(0) as "record数" from Customers
where customerid >= 40;
-- 根据单一列分组
select count(0) as "record数", country from Customers
where customerid >= 40
group by country; -- 分组的意义：统计每个组的数据
-- 根据多列分组
select count(0) as "record数", country, city from Customers
where customerid >= 40
group by country, city;

2.1.5. 聚合函数

统计分组后各组的数据

-- 统计每组数据量
select country, city, count(0) as "人数" from Customers
group by country, city;
-- 统计每组指定列最大值和最小值
select categoryid, max(price) as "最大价格", min(price) as "最小价格" from Products
group by categoryid;
-- 统计每组指定列的平均值
select categoryid, avg(price) as "最大价格" from Products
group by categoryid;
-- 统计每组指定列的合计
select categoryid, sum(price) as "合计价格" from Products
group by categoryid;

2.1.6. having

对于分组后的数据进行筛选

-- 取出合计价格大于50的组（商品表）NG
select categoryid, sum(price) as "sum_price" from Products
group by categoryid
where sum_price >= 50; -- 对于已经分组后的数据，不能使用where作为筛选条件
-- 取出合计价格大于50的组（商品表）OK
select categoryid, sum(price) as "sum_price" from Products
group by categoryid
having sum_price >= 50;
-- 以上补充，无别名写法
select categoryid, sum(price) as "sum_price" from Products
group by categoryid
having sum(price) >= 50; -- 并不推荐，只了解就好

2.1.7. 子查询

在原有查询基础上再查询（将内层查询的结果作为外层查询的数据元）

-- 取出最大价格和最小价格差值最大的组（商品表）
select categoryid, max(max_price - min_price) as "最大差价" from
(select categoryid, max(price) as "max_price", min(price) as "min_price" from Products
group by categoryid);

2.1.8. 表关联

cross join

-- 将两个及以上表做笛卡尔积（A表的每一条数据都与B表的所有数据匹配一次）
select * from orders cross join shippers; -- 虽说确实得到了结果，但是稍微观察后我们可以发现，结果中有很多无效的数据。

inner join

-- 数据准备
insert into shippers values (4, 'Test Shipper1', null);
-- 为解决cross join的无效解问题
select * from orders a inner join shippers b on a.shipperid = b.shipperid; -- 如何知道结果有多少条数据(用sql)

outer join

-- left outer join
select * from orders a left outer join shippers b on a.shipperid = b.shipperid; -- 左表为主表保留未匹配的数据
-- right outer join
select * from orders a right outer join shippers b on a.shipperid = b.shipperid; -- 右表为主表保留未匹配的数据
-- full outer join
select * from orders a full outer join shippers b on a.shipperid = b.shipperid; -- 以上两种的交集
-- ※outer可以省略 -> left join, right join, full join

2.1.9. 补充

其他语法

-- 查询指定列为null的数据
select * from shippers where phone = null; -- NG
select * from shippers where phone is null; -- OK
/*
  不可以使用 = null 的原因如下：
  在SQL的逻辑表达式中有三种可能的值，而非我们常识中的两种，分别是：
  true，false，unknown
  当我们是用任何值(包括null本身，根本原因)与null作比较时，都将返回unknown，且
  在条件语句(如where，having等)中都将被视作false。
  而 is null 不会出现上述问题，这就是我们必须使用 is null 原因。
 */
-- 查询指定列不为null的数据
select * from shippers where phone is not null;

-- 查询结果去重
select distinct country from customers; -- distinct必须放在所有指定列的最前面

2.2. insert

不指定列名

-- 不指定列名，在指定表中插入一条数据，必须传入所有列的值
insert into shippers values (5, 'Test Shipper2', '');

指定列名

-- 不指定列名，在指定表中插入一条数据，只需要传入指定列的值
insert into shippers (shipperid, shippername) values (6, 'Test Shipper2');

2.3. update

不使用条件语句

-- 不使用条件语句的update
update shippers set shippername = 'Test Shipper1';

使用条件语句(推荐)

-- 使用条件语句的update
update shippers set shippername = 'Test Shipper1' where shipperid = 4;

2.4. delete