从一个前端视角初识sql本文从一个前端视角初识sql，主要介绍了sql的一些基础命令，及一些常见的join，view，i

本文将从一个前端开发者的视角快速入门sql相关命令，主要参考资料如下

learning-sql 3rd version

head first sql

sqlbolt

w3c

本文有如下约定

sql包含的关键词一律大写
注释： -- 是单行注释，/* */是多行注释
[]表示这些可有可不有，比如column_name1 VARCHAR(25) [constraints]表示 constraints可以不写
;是语句结束的分割符，,一般是用来分开column的

需要注意，这里写的是较为通用的sql命令或者是基础概念类的命令，在各sql实现比如mysql postgresql里可能会有细微出入，但总体概念是没问题的

常用命令

数据表创建等操作

这部分主要包含创建或删除数据表、新增或删除column、修改column名称或数据类型

-- 创建table
CREATE TABLE [IF NOT EXISTS] table_name
  (
  column_name1 dataType [constraints],
  column_name2 VARCHAR(4),
  );


-- 删除table
DROP TABLE table_name [IF EXISTS];


-- 修改table name, 有两种写法
RENAME table_name1 TO table_name2;
ALTER table_name1 RENAME TO table_name2;


/* 修改column的相关操作
- ADD 新增column，约束、index
- DROP 删除column，约束、index
- MODIFY 修改数据类型
- CHANGE 修改名称和数据类型
*/
ALTER TABLE table_name
ADD column_name dataType [constraints];

ALTER TABLE table_name
DROP column_name;

ALTER TABLE table_name
MODIFY column_name dataType [constraints];

ALTER TABLE table_name
CHANGE column_name1 column_name2 dataType [constraints];

数据增删改查相关操作

本部分主要介绍crud相关操作

基础的crud命令

SELECT column_name1, column_name2 FROM table_name;

INSERT INTO table_name
  (column_name1, column_name2)
  VALUES (column_value1, column_value2);

UPDATE table_name
  SET column_name1='name', column_name2='name2'
  WHERE id='id-1';

DELETE FROM table_name WHERE id='id-1';

复杂一点的查询命令

常用过滤数据关键词

名称	作用
GROUP BY	按照column，将同value的行统计在一起进行操作，一般是需要使用`COUNT`等函数对数据进行计算是使用
HAVING	跟在`GROUP BY`后面使用，用来对`GROUP BY`的结果进行过滤操作
ORDER BY	顾名思义，进行排序
WHERE	过滤不需要的数据
LIMIT	展示的数据量
OFFSET	忽视前n个数据，从n+1开始
DISTINCT	去除重复值，只留下唯一的数据
LIKE	用来过滤字符串类型数据，常用'%NAME%'对应'xxxNAMExxx','NAME'对应'xNAMEx'，"%"对应一个或多个字符，"_"对应一个字符

下面的例子就是获取拥有大于1家商店的店主名称，取拥有商店数量最多的5位展示

SELECT owner, COUNT(name) AS owned_store_number
FROM stores
  GROUP BY owner
  HAVING COUNT(name) > 1
  ORDER BY owned_store_number DESC LIMIT 5;

多表联查

有时候查询需要多表联查，这时候就分为了inner join和outer join，分别对应的常用关键字是INNER JOIN、CROSS JOIN 和LEFT JOIN 、RIGHT JOIN、FULL JOIN；

通过集合运算的图来理解一下区别

名称	含义
INNER JOIN	取交集
CROSS JOIN	将A中的每一项都和B中的每一项链接一遍产生的结果
LEFT JOIN	包含全部A和A与B的交集，即便某些数据只在A有，不在B内
RIGHT JOIN	包含全部B和A与B的交集，即便某些数据只在B有，不在A内
FULL JOIN	并集

join

下面是一个简单的INNER JOIN的语句

mysql> SELECT f.film_id, f.title, i.inventory_id
    -> FROM film f
    ->   INNER JOIN inventory i
    ->   ON f.film_id = i.film_id
    -> WHERE f.film_id BETWEEN 13 AND 15;
+---------+--------------+--------------+
| film_id | title        | inventory_id |
+---------+--------------+--------------+
|      13 | ALI FOREVER  |           67 |
|      13 | ALI FOREVER  |           68 |
|      15 | ALIEN CENTER |           75 |
|      15 | ALIEN CENTER |           76 |
+---------+--------------+--------------+

subquery 子查询

一般来说，join是可以替代subquery的，且join的性能可能比subquery要好，所以在业务开发中不是很推荐使用

subquery是指在一个query语句里再嵌套一个query语句的场景，有时候在实现多表联查的时候比join写起来更简单

subquery有两种类型

noncorrelated subqueries: subquery和父query无关
correlated subqueries: subquery需要用到父query里的数据作为判定条件

下面是两种subquery的例子

# noncorrelated subqueries
mysql> SELECT city_id, city
    -> FROM city
    -> WHERE country_id <>
    ->  (SELECT country_id FROM country WHERE country = 'India');
    +---------+----------------------------+
    | city_id | city                       |
    +---------+----------------------------+
    |       1 | A Corua (La Corua)         |
    |       2 | Abha                       |
    |       3 | Abu Dhabi                  |
    |       4 | Acua                       |
    |       5 | Adana                      |
    +---------+----------------------------+


# correlated subqueries
mysql> SELECT c.first_name, c.last_name
    -> FROM customer c
    -> WHERE 20 =
    ->  (SELECT count(*) FROM rental r
    ->   WHERE r.customer_id = c.customer_id);
+------------+-------------+
| first_name | last_name   |
+------------+-------------+
| LAUREN     | HUDSON      |
| JEANETTE   | GREENE      |
| TARA       | RYAN        |
+------------+-------------+

其他特性介绍

这部分主要包含比如index，transaction等偏向于概念类内容的介绍

index索引

index的作用是生成一个索引，可以加快查询速度；数据的存储一般是无序的，在查询的时候如果遍历会很慢，设置index之后数据库会把设置了index对应的column进行排序存储，在之后搜索对应column的速度就会快很多，index的存储方式有b tree等等方式，这里就不做介绍了,详细内容可以看 how-indexing-works

index可以在创建table的时候就设置好，或者是在后续再新增

-- 创建table时创建index
CREATE TABLE customer (
  customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id TINYINT UNSIGNED NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  -- 此处是index的创建
  PRIMARY KEY  (customer_id),
  KEY idx_fk_store_id (store_id),
);


-- 后续创建index的两种方法
CREATE INDEX idx_name ON table_name (column_name);

ALTER TABLE table_name
ADD INDEX idx_name (column_name);

constraints约束

约束主要是用来控制数据是否正确的，比如数据是否唯一、是否满足CHECK、是否满足外键依赖等等; CHECK可以用来校验某个column是否满足对应的条件，对输入内容进行约束；

创建方式和index比较像

CREATE TABLE customer (
  customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (customer_id),
  CONSTRAINT fk_customer_address FOREIGN KEY (address_id)
    REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
)

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18);

transaction事务

在实际生产中一般要开启事务再进行数据的更新，这样可以万一在更新数据的时候出现问题，可以回滚到开始事务时或者指定的SAVEPOINT

START TRANSACTION;

UPDATE product
SET date_retired = CURRENT_TIMESTAMP()
WHERE product_cd = 'XYZ';

SAVEPOINT before_close_accounts;

UPDATE account
SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(),
  last_activity_date = CURRENT_TIMESTAMP()
WHERE product_cd = 'XYZ';

ROLLBACK TO SAVEPOINT before_close_accounts;
COMMIT;

lock锁

在数据库的更新过程中可能会出现concurrency并发问题，需要加锁来避免这种情况导致不可预料的问题

分为lock table和lock row两个级别，但是在sql标准里没有lock的定义，是各数据库自行实现，所以此处不给出具体的使用语句，请自行搜索

view 视图

view可以理解为一个query语句的别名，在某些时候可以把view当table使用;不但可以从view读取数据，有时也可以直接使用view更新数据，但是需要注意：

不可以更新派生出来的数据
如果view包含多个表格的数据，那一次只可以更新一个表格的数据

CREATE VIEW low_budget_locs_view AS
(
  SELECT city, country FROM locations
  WHERE budget <= 1500
);

SELECT * FROM low_budget_locs_view;

temporary table 临时表

临时表的主要作用是保存获取到的数据，可能读取一次的数据量大或者性能不好，需要临时存放，以供后续使用；一般临时表会在一次session里生效，关闭链接后会自动清除，但具体要看各数据库的实现

CREATE TEMPORARY TABLE hot_location AS (
  SELECT * FROM locations
  WHERE weather_id  = ( SELECT weather_id
        FROM weather
        WHERE weather = "hot"
        )
);

-- DROP TABLE也可以用来删除临时表
DROP [TEMPORARY] TABLE hot_location'

CTE-Common Table Expression，with语句

CTE其实就是with语句，就我个人的理解，CTE的主要作用是将数据的前置，简化查询，可读性提升，在非递归cte时可以提升性能

WITH avg_salary AS (
    SELECT department, AVG(salary) avg_salary
    FROM employees
    GROUP BY department
)
SELECT *
FROM avg_salary;

需要注意的一些事项

NULL

NULL不等于任何值，也即 NULL != NULL,可以使用IS NULL来判断是否是NULL

VARCHAR

VARCHAR 需要多占用一个byte用来存prefix； VARCHAR(4) needs anywhere from 1 to 5 bytes

条件语句

case语句可以用来对选中的数据进行二次计算，格式如下

CASE
  WHEN C1 THEN E1
  WHEN C2 THEN E2
  ...
  WHEN CN THEN EN
  [ELSE ED]
END

mysql的例子如下

mysql> SELECT c.first_name, c.last_name,
    ->   CASE
    ->     WHEN active = 0 THEN 0
    ->     ELSE
    ->      (SELECT count(*) FROM rental r
    ->       WHERE r.customer_id = c.customer_id)
    ->   END num_rentals
    -> FROM customer c;
+-------------+--------------+-------------+
| first_name  | last_name    | num_rentals |
+-------------+--------------+-------------+
| MARY        | SMITH        |          32 |
| PATRICIA    | JOHNSON      |          27 |
| AUSTIN      | CINTRON      |          19 |
+-------------+--------------+-------------+

一对一，一对多，多对多； one-to-many, many-to-many

one-to-one一般可以存在一个表里，但是有时候会拆开来，有如下情况：

拆开可能可以加速数据的query
更细粒度的控制数据的访问权限
某些column的数据量很大，拆分到另一个表可能更好
把在新增时可能不知道的数据放入另一个表，有助于避免null值

many-to-many 的某些使用场景：学生参加课程，课程表有学生的信息，学生需要有其参加的课程的信息；一般需要一个关系表来专门存储学生id和课程id，作为两个表的联系

如何拆分表格

每一行数据都时原子化的数据，比如旅行社的路线活动表，某个城市可以进行多个活动，则这些活动需要放在其他表中
每一行数据都要有primary key
如果primary key是由多个column组成的，那不能有某个非key的column依赖于部分组成primary key的column，比如名字+性别组成primary key，名字的拼音首字母就不可以放在此表格里，因为和名字相关联，但是和性别无关——no partial functional deps
非key的column依赖于另一个非key的column——no transitive deps

拆分原则