SQL学习之基础查找

一、基础查找

1.简单查询

1
2
SELECT brand_name,count_price/current_price AS 折扣率
FROM brand;

查找brand（品牌）表格中brand_name品牌名列中的所有内容:

  • 第1行，使用了关键字SELECT选择要查找的字段名,指定了2个字段brand_name和折扣率，中间用逗号隔开
  • count_price/current_price 是对现有字段的除法操作，在sql中有加（+）减（-）乘（*）除(/)操作，乘除的优先级高于加减
  • AS：在输出时使用别名
  • 第2行，使用关键字FROM指定要查找的表格，指定了要查找的订单表brand
  • sql语句关键字大小写不敏感，但为了代码清晰，建议关键字一律大写
  • sql语句对换行缩进没有要求，但为了代码可读性，建议每个功能子句进行换行
  • 注意：sql语句必须以;结束

例题：

1.一次性将表中的新加坡币价格转换为人民币价格，1 SGD = 5 RMB

1
2
SELECT hotel_name,price_SGD * 5 AS price_RMB
FROM hotel;

2.排序与去重

2.1 排序使用 ORDER BY 语句
  • 默认的排序方式是升序。当我们想要进行升序排列时，可以指定关键字 ASC，也可以不指定。
  • 如果要按照降序排序， 也就是由高到低排列，必须指定关键字 DESC 。
  • 关键字 DESC 应该放在某一字段名之后，表示仅按照该列进行降序排列。
2.2 控制显示行数用 LIMIT 语句
  • 例如，当我们只想显示订单表按照价格降序排列后，排在第11到第13的数据时，我们就可以使用 LIMIT 10, 3 。表示跳过前 10 个记录从第11个开始向后取 3 条数据。而 LIMIT 10 就表示前10条记录
2.3 去除列中的重复记录用 DISTINCT语句

  • 例如，查找客户表中的地区，并去除重复的地区信息：

    1
    2
    SELECT  DISTINCT district
    FROM  account_2c;

例题：

1.从商品（commodity）表中找到折扣价最便宜的一个商品：

1
2
3
4
SELECT *
FROM commodity
ORDER BY discount_price 
LIMIT 1;

2.想要查看公司商品(commodity)表的中价格最高的三个商品对应的部分商品属性信息。输出的列名为：商品序号，商品名称，商品价格,请把英文字段名重命名为中文字段名:

1
2
3
4
SELECT id AS 商品序号,name AS 商品名称,price AS 商品价格
FROM commodity
ORDER BY price DESC
LIMIT 3;

3.从course表中筛选出参与了选课的同学(student)，需要去除重复的同学名字。

1
2
SELECT DISTINCT student 
FROM course;

3.按条件筛选数据

3.1 where子句

从商品表中列出所有单价高于500的商品名称与商品价格：

1
2
3
SELECT sku_name, sku_price
FROM commodity
WHERE sku_price > 500;

需要注意比较运算符和python的不同之处：

  • 等于：SQL 是 = , 而python 是 ==
  • 不等于：SQL 是 <> 或是 != ，而Python就只是 !=
  • SQL中可以对时间进行比较，例如：可以筛选出在1994年8月30日之后出生的人：birth > ‘1994-08-30’ ,若只限定到“x年-x月-x日”，则SQL会默认该数据为“x年-x月-x日 00:00:00”，即这一天的零点整。
  • SQL中的字符比较，大小比较是对字符在字母表中位置的前后进行判断。比如我们筛选出名称首字母不在‘A~H’的人，即H之后的人。name > ‘H’
3.2 缺失值Null

在 Python 语言中，条件表达式的结果非真即假。即条件表达式的结果为布尔值 True 或者 False。而在SQL中，一个条件的结果除了真与假之外，还会出现缺失值NULL。 无法用比较运算去检查某行数据是否缺失。

  • 在SQL中，使用IS NULL判断某行数据是否为空
  • 使用IS NOT NULL判断某行数据是否非空

例如：

从 contact_info 中选择满足 phone_number IS NULL 的 * （所有信息 ）。

1
2
3
SELECT *
FROM contact_info
WHERE phone_number IS NULL;
3.3 总结子句的执行顺序

SQL 在执行代码时并非按照我们书写的顺序：

  • 第一步，对FROM后指定的表格进行加载
  • 第二步，进入 WHERE 筛选阶段
  • 第三步，通过SELECT后置定的字段准备数据
  • 最后是对要输出的数据进行排序与限行

而书写的顺序：SELECT → FROM → WHERE → ORDER BY → LIMIT

例题：

1.从商品(commodity)表格中提取出有折扣的（即折扣不为1的）商品，并列出折扣最高的(即计算的折扣数值最小的)3款商品名 (name) 以及其对应的折扣 (discount)

id name price state discount_price
1 长胖眼影 77 上架中 69.3
2 神仙口红 59 已下架 59
3 榴莲牛肉粒 108 已下架 108
4 五彩缤纷遮瑕 193 已下架 193
5 神仙开心粉 199 待补货 139.3
6 变态辣悲伤果 105 上架中 84
7 麻辣粉底 196 上架中 98
8 长胖开心粉 125 已下架 125
9 变态辣悲伤果 153 上架中 45.9
10 长胖口红 64 已下架 64 
1
2
3
4
5
SELECT name,discount_price/price AS discount
FROM commodity
WHERE discount_price/price <> 1
ORDER BY discount_price/price 
LIMIT 3;

2.在给出的employees_and_managers表中，包含所有员工，主管也在其中，每个员工对应一个主管。找出员工收入大于主管收入的员工姓名

id employee_name employee_salary manager_name manager_salary
1 花花 10000 草草 15000
2 风儿 6000 沙儿 16000
3 凡凡 8000 平平 15000
4 心心 20000 爱爱 15000
5 快快 7500 乐乐 17000 
1
2
3
SELECT employee_name
FROM employees_and_managers
WHERE employee_salary > manager_salary;

4.模糊匹配与多条件筛选

设想场景：需要从商品评论数据中筛选出所有包含买家秀（即包含照片）的评论，然后进行筛选。

可以将问题简化为，从商品评论表中的文本内容列中筛选出所有包含 <img 的数据。可以用以下方法：

1
2
3
SELECT *
FROM comment
WHERE text LIKE '%<img%';
  • LIKE关键字，表示对text列进行模糊匹配
  • %为通配符，表明零到任意数量个常规字符（与之相对的是下划线_，表示的是任意一个常规字符）
  • %<img% 为模式字符串
4.1 对时间的匹配

在数据库中，时间类型是以字符的形式存储的，所以我们也可以对时间数据进行类似的匹配。比如我们想要找到11月的订单，可以对订单时间以‘%-11-%’的方式匹配。

1
2
3
SELECT *
FROM orders
WHERE order_time LIKE '%-11-%';
4.2 逻辑运算符

与python相似，SQL也有与（and）、或（or）、非（not）三类逻辑运算符

4.3 多个条件的连接

可以在一次查询中使用多个逻辑关系，但为了避免出错，请使用小括号来指定优先级。

例如：筛选所有金额大于500的支付宝订单

id account_id total_price order_time payment_method status addr_id
1000000001 1004000161 1026.7 2019-11-09 22:04:11 Wechat true 1002000161
1000000002 1004000206 288.6 2020-11-16 22:04:11 Wechat true 1002000206
1000000003 1004000054 1578.95 2020-11-11 21:25:36 Wechat true 1002000054
1000000004 1004000237 605.7 2020-11-13 21:25:36 other true 1002000237
1000000005 1004000194 NULL 2019-06-07 23:52:52 Alipay false 1002000194
1000000006 1004000051 635.95 2019-06-05 23:52:52 Wechat true 1002000051
1000000007 1004000036 NULL 2020-09-06 09:04:57 Wechat false 1002000036
1000000008 1004000096 465.55 2019-11-08 22:30:58 Alipay false 1002000096
1000000009 1004000157 222.4 2020-11-06 22:30:58 Alipay true 1002000157
1000000010 1004000128 1446 2019-12-12 11:20:34 Wechat true 1002000128 
1
2
3
SELECT *
FROM orders
WHERE (total_price > 500) AND (payment_method = 'alipay');

例题：统计一下2019年双十一（2019-11-01～2019-11-12 00:00:00）的所有订单。

1
2
3
SELECT *
FROM orders
WHERE (order_time >= '2019-11-01') AND (order_time <= '2019-11-12');
4.4 范围筛选

  • BETEEN AND

    要设定 “从X到Y之间的” 类似于这样的设定范围的条件。除了使用逻辑运算符外，可以利用BETWEEN A AND B 这样的格式。比如，要筛选2000年到2021年出生的所有人，就可以设定：

    1
    WHERE birthday BETWEEN '2000-01-01' AND '2021-12-31'

  • IN

    要设定 “满足X或Y或Z的” 类似于这样指定成员进行筛选的条件，除了使用逻辑运算符外，可以利用IN ( X，Y，Z ) 这样的格式。比如，当要筛选广东省或深圳市的所有用户，就可以设定：

    1
    WHERE province IN ('广东省'，'深圳市')

    例子：从商品表中筛选出品牌编号为200010、品牌编号为200011、品牌编号为200012的所有商品。

    1
    2
    3
    SELECT sku_name,brand_id
    FROM commodity
    WHERE brand_id IN (200010,200011,200012);

    例子2：筛选出2019年每一笔在每月16日购买的订单。

    1
    2
    3
    SELECT id,total_price,order_time
    FROM orders
    WHERE order_time LIKE '2019-__-16%';

例题1：在用户(customer)表中，已经给推荐人做了编号，字段名为referee_id。从用户表中提取出没有推荐人的（推荐人编号是NULL的）用户。输出的列为：name(用户名), referee_id(推荐人编号)

name referee_id
玛丽莲梦娜 NULL
梦琪 NULL
公主 NULL 
1
2
3
SELECT name,referee_id
FROM customer
WHERE referee_id IS NULL;

例题2：找出所有影片描述 (description) 与”boring”无关的，即不无聊的并且序号 (id) 为偶数的影片，结果要按照等级 (rating) 降序排列。

id movie description rating
1 悬崖之下 touched 9.1
2 她的婚礼 too boring and fake plot 7.2
3 指环女王 war 9.4
4 猪猪侠大战变形金刚 great 3D 8.4
5 感动他250次 boring 7.1
6 秘密造访 fiction 8 
1
2
3
4
SELECT *
FROM cinema
WHERE (description NOT LIKE '%boring%') AND (id % 2 = 0)
ORDER BY rating DESC;