《SQL 必知必会》学习笔记

前言

  1. 到官网下载实验数据脚本:Sams Teach Yourself SQL in 10 Minutes。本文以 MySQL 为例,对应的脚本文件下载地址:MySQL

  2. 终端中运行 mysql -uroot -p 登录 MySQL,执行以下命令创建数据库:

    1
    CREATE DATABASE sql_test;
  3. 运行 create.txt SQL 脚本文件创建表:

    1
    $ mysql -uroot -p sql_test < create.txt
  4. 运行 populate.txt SQL 脚本文件插入数据:

    1
    $ mysql -uroot -p sql_test < populate.txt

了解 SQL

  1. 数据库:保存有组织的数据容器(通常是一个文件或一组文件)。
  2. 表:某种特定类型数据的结构化清单。
  3. 列:表中的一个字段,所有表都是由一个或多个列组成的。
  4. 行:表中的一个记录。
  5. 主键:一列(或一组列),其值能够唯一标识表中每一行。

检索数据

  1. 检索单个列

    1
    2
    SELECT prod_name
    FROM Products;
  2. 检索多个列

    1
    2
    SELECT prod_id, prod_name, prod_price
    FROM Products;
  3. 检索所有列

    1
    2
    SELECT *
    FROM Products;
  4. 检索不同的值

    1
    2
    SELECT DISTINCT vend_id
    FROM Products;
  5. 限制结果

    1
    2
    3
    4
    5
    6
    7
    SELECT prod_name
    FROM Products
    LIMIT 2 OFFSET 3;
    /* OR */
    SELECT prod_name
    FROM Products
    LIMIT 3, 2;
  6. 使用注释

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT prod_name    -- 行内注释
    FROM Products;

    # 单行注释
    SELECT prod_name
    FROM Products;

    /* 多行注释
    SELECT prod_name, vend_id
    FROM Products; */
    SELECT prod_name
    FROM Produncts;

排序检索数据

  1. 排序数据

    1
    2
    3
    SELECT prod_name
    FROM Products
    ORDER BY prod_name;
  2. 按多个列排序

    1
    2
    3
    SELECT prod_id, prod_price, prod_name
    FROM Products
    ORDER BY prod_price, prod_name;
  3. 按列位置排序

    1
    2
    3
    SELECT prod_id, prod_price, prod_name
    FROM Products
    ORDER BY 2, 3;
  4. 指定排序方向

    1
    2
    3
    SELECT prod_id, prod_price, prod_name
    FROM Products
    ORDER BY prod_price DESC, prod_name DESC;

过滤数据

  1. 检查单个值

    1
    2
    3
    SELECT prod_name, prod_price
    FROM Products
    WHERE prod_price < 10;
  2. 不匹配检查

    1
    2
    3
    SELECT vend_id, prod_name
    FROM Products
    WHERE vend_id <> 'DLL01';
  3. 范围值检查

    1
    2
    3
    SELECT prod_name, prod_price
    FROM Products
    WHERE prod_price BETWEEN 5 AND 10;
  4. 空值检查

    1
    2
    3
    SELECT cust_name
    FROM Customers
    WHERE cust_email IS NULL;

高级数据过滤

  1. AND 操作符

    1
    2
    3
    SELECT prod_id, prod_price, prod_name
    FROM Products
    WHERE vend_id = 'DLL01' AND prod_price <= 4;
  2. OR 操作符

    1
    2
    3
    SELECT prod_name, prod_price
    FROM Products
    WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
  3. 求值顺序

    1
    2
    3
    SELECT prod_name, prod_price
    FROM Products
    WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
  4. IN 操作符

    1
    2
    3
    4
    SELECT prod_name, prod_price
    FROM Products
    WHERE vend_id IN ( 'DLL01', 'BRS01' )
    ORDER BY prod_name;
  5. NOT 操作符

    1
    2
    3
    4
    SELECT prod_name
    FROM Products
    WHERE NOT vend_id = 'DLL01'
    ORDER BY prod_name;

用通配符进行过滤

  1. 百分号(%)通配符

    1
    2
    3
    SELECT prod_id, prod_name
    FROM Products
    WHERE prod_name LIKE '%bean bag%';
  2. 下划线(_)通配符

    只匹配单个字符:

    1
    2
    3
    SELECT prod_id, prod_name
    FROM Products
    WHERE prod_name LIKE '__ inch teddy bear';
  3. 方括号([])通配符

    匹配方括号中的每个字符:

    1
    2
    3
    4
    SELCET cust_contact
    FROM Customers
    WHERE cust_contact LIKE '[JM]%'
    ORDER BY cust_contact;

    否定方括号中的每个字符:

    1
    2
    3
    4
    SELCET cust_contact
    FROM Customers
    WHERE cust_contact LIKE '[^JM]%'
    ORDER BY cust_contact;