SQL语法基础练习与掌握

1、一条 SQL 查询语句的执行顺序？

查询语句执行顺序

  1. FROM：对 FROM 子句中的左表和右表执行笛卡儿积（Cartesianproduct），产生虚拟表 VT1。
  2. ON：对虚拟表 VT1 应用 ON 筛选，只有那些符合的行才被插入虚拟表 VT2 中。
  3. JOIN：如果指定了 OUTER JOIN（如 LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1）～步骤 3），直到处理完所有的表为止。
  4. WHERE：对虚拟表 VT3 应用 WHERE 过滤条件，只有符合的记录才被插入虚拟表 VT4 中
  5. GROUP BY：根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5。
  6. CUBE|ROLLUP：对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6。
  7. HAVING：对虚拟表 VT6 应用 HAVING 过滤器，只有符合的记录才被插入虚拟表 VT7 中。
  8. SELECT：第二次执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中。
  9. DISTINCT：去除重复数据，产生虚拟表 VT9。
  10. ORDER BY：将虚拟表 VT9 中的记录按照进行排序操作，产生虚拟表 VT10。
  11. LIMIT：取出指定行的记录，产生虚拟表 VT11，并返回给查询用户。

2、检索数据

（1）检索并列出所有已订购商品（prod_id）的去重后的清单。

1
2
SELECT DISTINCT prod_id
FROM OrderItems

（2）从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。

1
2
3
4
5
# 根据列名排序
SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id,order_date DESC
# order by 对多列排序的时候，先排序的列放前面，后排序的列放后面。

（3）显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。

1
2
3
4
SELECT quantity, item_price
FROM OrderItems
ORDER BY quantity DESC,item_price DESC

3、过滤数据

WHERE 可以过滤返回的数据。

下面的运算符可以在 WHERE 子句中使用：

运算符 描述
= 等于
<> 不等于。 注释： 在 SQL 的一些版本中，该操作符可被写成 !=
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
IN 指定针对某个列的多个可能值

4、汇总数据

汇总数据相关的函数：

函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

5、分组数据

HAVING vs WHERE：

  • WHERE：过滤指定的行，后面不能加聚合函数（分组函数）。
  • HAVING：过滤分组，必须要与 GROUP BY 连用，不能单独使用

（1）返回订单数量总和不小于 100 的所有订单号，最后结果按照订单号升序排序。

1
2
3
4
5
SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING Sum(quantity) >= 100
ORDER BY order_num

6、使用子查询

（1）使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。

1
2
3
4
5
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT DISTINCT order_num
    FROM OrderItems
    where item_price >= 10)

7、连接表

连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。

使用 JOIN 连接两个表的基本语法如下：

1
2
3
4
SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.common_column1 = table2.common_column2;

SQL 允许在 JOIN 左边加上一些修饰性的关键词，从而形成不同类型的连接，如下表所示：

连接类型 说明
INNER JOIN 内连接 （默认连接方式）只有当两个表都存在满足条件的记录时才会返回行。
LEFT JOIN / LEFT OUTER JOIN 左(外)连接 返回左表中的所有行，即使右表中没有满足条件的行也是如此。
RIGHT JOIN / RIGHT OUTER JOIN 右(外)连接 返回右表中的所有行，即使左表中没有满足条件的行也是如此。
FULL JOIN / FULL OUTER JOIN 全(外)连接 只要其中有一个表存在满足条件的记录，就返回行。
SELF JOIN 将一个表连接到自身，就像该表是两个表一样。为了区分两个表，在 SQL 语句中需要至少重命名一个表。
CROSS JOIN 交叉连接，从两个或者多个连接表中返回记录集的笛卡尔积。

如果不加任何修饰词，只写 JOIN，那么默认为 INNER JOIN。

8、组合查询

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

UNION 基本规则：

  • 所有查询的列数和列顺序必须相同。
  • 每个查询中涉及表的列的数据类型必须相同或兼容。
  • 通常返回的列名取自第一个查询。

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

1
2
3
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

JOIN vs UNION：

  • JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。
  • UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

9、增删改操作

SQL 插入记录的方式汇总：

  • 普通插入（全字段） ：INSERT INTO table_name VALUES (value1, value2, ...)
  • 普通插入（限定字段） ：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
  • 多条一次性插入 ：INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
  • 从另一个表导入 ：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
  • 带更新的插入 ：REPLACE INTO table_name VALUES (value1, value2, ...)（注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）

更新记录：

1
UPDATE examination_info SET tag = 'Python' WHERE tag='PYTHON'

或

1
2
3
4
UPDATE examination_info
SET tag = REPLACE(tag,'PYTHON','Python')

# REPLACE (目标字段，"查找内容","替换内容")

删除记录：

1
DELETE FROM exam_record WHERE MINUTE (TIMEDIFF(submit_time , start_time)) < 5 AND score < 60

10、表与索引操作

创建表

1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS user_info_vip(
    id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT'自增ID',
    uid INT(11) UNIQUE NOT NULL COMMENT '用户ID',
    nick_name VARCHAR(64) COMMENT'昵称',
    achievement INT(11) DEFAULT 0 COMMENT '成就值',
    `level` INT(11) COMMENT '用户等级',
    job VARCHAR(32) COMMENT '职业方向',
    register_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)CHARACTER SET UTF8

修改表

1
2
3
4
ALTER TABLE user_info
    ADD school VARCHAR(15) AFTER level,
    CHANGE job profession VARCHAR(10),
    MODIFY achievement INT(11) DEFAULT 0;

删除表

1
DROP TABLE IF EXISTS exam_record_2011;

创建索引

1
2
3
4
5
6
7
8
9
10
11
12
-- 示例：
-- 添加B-Tree索引：
	CREATE INDEX idx_name(索引名) ON 表名 (字段名);   -- idx_name为索引名，以下都是
-- 创建唯一索引：
	CREATE UNIQUE INDEX idx_name ON 表名 (字段名);
-- 创建一个主键索引：
	ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
-- 创建一个全文索引
	ALTER TABLE 表名 ADD FULLTEXT INDEX idx_name (字段名);

-- 通过以上示例，可以看出create 和 alter 都可以添加索引

示例：

1
2
3
4
ALTER TABLE examination_info
    ADD INDEX idx_duration(duration),
    ADD UNIQUE INDEX uniq_idx_exam_id(exam_id),
    ADD FULLTEXT INDEX full_idx_tag(tag);

删除索引

1
2
3
4
5
-- 使用 DROP INDEX 删除索引
DROP INDEX idx_name ON 表名;

-- 使用 ALTER TABLE 删除索引
ALTER TABLE employees DROP INDEX idx_email;

注：在 MySQL 中，一次删除多个索引的操作是不支持的。每次删除索引时，只能指定一个索引名称进行删除。

示例：

1
2
DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;

快来参与讨论吧~


本站总访问量次