SQL简明教程

《SQL必知必会》的笔记，基于MySQL 5.7.35

检索数据

检索单个列

select prod_name from Products;

检索多个列

select prod_id, prod_name, prod_price
from Products;

检索所有列

select * from Products;

去除重复值

distinct关键字必须直接放在列名前面。distinct关键字作用于所有的列，而不仅仅是跟在其后的那一列。

select distinct vend_id from Products;

限制结果数量

返回5条数据

select prod_name from Products limit 5;

跳过前4条，返回5条数据

select prod_name from Products limit 5 offset 4;

注释

# 注释
-- 注释
/* 注释 */

排序检索数据

如果不明确规定排序顺序，则数据是无需的，可能是插入的顺序，也可能不是。

排序

按名称升序

select prod_name from Products order by prod_name;

按多个列排序

先按价格排序，后按名称排序

select prod_id, prod_price, prod_name
from Products
order by prod_price, prod_name;

指定排序方向

默认为升序，用desc关键字指定为降序

select prod_id, prod_price, prod_name
from Products
order by prod_price desc;

过滤数据

使用where子句

检索价格为3.49的产品

select prod_name, prod_price
from Products
where prod_price = 3.49;

检索价格小于10的产品

select prod_name, prod_price
from Products
where prod_price < 10;

检索价格小于等于10的产品

select prod_name, prod_price
from Products
where prod_price <= 10;

检索供应商不是DLL01的产品

select vend_id, prod_name
from Products
where vend_id != 'DLL01';

检索价格在5和10之间的产品，包括5和10

select prod_name, prod_price
from Products
where prod_price between 5 and 10;

检查空值

空值为null，与空字符串、空格和0不同

select prod_name from Products where prod_price is null;

组合where子句

检索供应商为DLL01，并且价格小于等于4的产品

select prod_id, prod_price, prod_name
from Products
where vend_id = 'DLL01'
  and prod_price <= 4;

检索供应商为DLL01，或者BRS01的产品

select prod_id, prod_price, prod_name
from Products
where vend_id = 'DLL01'
   or vend_id = 'BRS01';

检索价格为10以上，供应商为DLL01或BRS01的产品

select prod_id, prod_price, prod_name
from Products
where (vend_id = 'DLL01' or vend_id = 'BRS01')
  and prod_price >= 10;

IN操作符

检索供应商为DLL01或BRS01的产品，按名称排序

select prod_id, prod_price, prod_name
from Products
where vend_id in ('DLL01', 'BRS01')
order by prod_name;

NOT操作符

检索除DLL01之外所有供应商制造的产品

select prod_name
from Products
where not vend_id = 'DLL01'
order by prod_name;

Like操作符

检索以Fish开头的产品，%表示任何字符出现任意次数

select prod_id, prod_name
from Products
where prod_name like 'Fish%';

检索名称包含bean的产品

select prod_id, prod_name
from Products
where prod_name like '%bean%'

下划线_匹配单个字符，如

select prod_id, prod_name
from Products
where prod_name like '__ inch teddy bear';

创建计算字段

拼接字段

计算字段指通过SQL转换和格式化数据

拼接供应商名称和供应商所在国家

select concat(vend_name, '__', vend_country)
from Vendors
order by vend_name;

为计算字段设置别名

select concat(vend_name, '__', vend_country) as title
from Vendors
order by vend_name;

删除右边的空格

select rtrim(vend_country) from Vendors;

删除左边的空格

select ltrim(vend_country) from Vendors;

删除两端空格

select trim(vend_country) from Vendors;

执行算数计算

乘法

select prod_id, quantity, item_price, quantity * item_price as total
from OrderItems;

使用函数处理数据

字符串相关函数

转换大写

select vend_name, upper(vend_name) as up from Vendors;

转换小写

select vend_name, lower(vend_name) as up from Vendors;

返回左边5个字符

select vend_name, left(vend_name, 5) as up from Vendors;

返回右边5个字符

select vend_name, right(vend_name, 5) as up from Vendors;

计算字符串长度

select vend_name, length(vend_name) as up from Vendors;

返回子字符串

select vend_name, substr(vend_name, 5,5) as up from Vendors;

日期时间相关函数

用extract函数提取年份

select order_num from Orders where extract(year from order_date) = 2020;

也可以使用year函数

select order_num
from Orders
where year(order_date)=2020;

字符串转日期

select str_to_date('2020-01-01', '%Y-%m-%d');

汇总数据

计算产品平均价格，avg函数会忽略值为NULL的行

select avg(prod_price) as avg_price from Products;

统计行数，如果指定列名则忽略值为NULL的行，count(*)包括NULL

select count(*) as total from Customers;

计算产品最高价格

select max(prod_price) as max_price from Products;

计算产品最低价格

select min(prod_price) as max_price from Products;

求和，计算订单号为20005的订单中物品总数

select sum(quantity) as item_ordered from OrderItems where order_num=20005;

分组数据

计算每个供应商的产品数量

select vend_id, count(*) as num_prods
from Products
group by vend_id;

统计顾客订单数量（2个及以上）

select cust_id, count(*) as orders
from Orders
group by cust_id
having count(*) >= 2

查询具有两个以上产品且价格大于等于4的供应商

select vend_id, count(*) as num_prods
from Products
where prod_price >= 4
group by vend_id
having count(*) >= 2;

子查询

把一条select语句返回的结果用于另一条select语句的where子句。

查询订购了产品RGAN01的顾客信息

select cust_name, cust_contact
from Customers
where cust_id in
      (select cust_id
       from Orders
       where order_num in
             (select order_num
              from OrderItems
              where prod_id = 'RGAN01'));

计算每个顾客的订单总数

select cust_name, cust_state, 
       (select count(*) from Orders where Orders.cust_id = Customers.cust_id) as orders
from Customers order by orders;

联结表

在一条select语句中，联结多个表返回一组输出

查询产品及供应商信息

select vend_name, prod_name, prod_price
from Vendors,
     Products
where Vendors.vend_id = Products.vend_id;

上面的SQL等同于

select vend_name, prod_name, prod_price
from Vendors
         inner join Products P on Vendors.vend_id = P.vend_id;