SQL基础教程

CREATE与SELECT子句

CREATE DATABASE shop;

CREATE TABLE product(
  product_id CHAR(4) NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  product_type VARCHAR(32) NOT NULL,
  sale_price INTEGER,
  purchase_price INTEGER,
  regist_date DATE,
  PRIMARY KEY (product_id)
);

INSERT INTO product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20'),
                           ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
                           ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
                           ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'),
                           ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'),
                           ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'),
                           ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28'),
                           ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

SELECT DISTINCT product_type
  FROM product;

DISTINCT关键字只能写在第一个列名之前，用以删除重复记录。

• 算术运算符：+、-、*、/；

• 比较运算符：=、<>、>=、>、<=、<;

• 逻辑运算符：NOT、AND、OR。

GROUP BY子句

聚合函数：COUNT、SUM、AVG、MIN、MAX，会排除字段值为NULL的记录。

SELECT product_type,
       COUNT(product_type),
       current_date AS query_date
  FROM product
  WHERE product_type <> '衣服'
  GROUP BY product_type;

• 使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名（只允许存在：常数、聚合函数、聚合键）；

• 在GROUP BY子句中不能使用SELECT子句中定义的别名；

• GROUP BY子句结果的显示是无序的；

• 只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。

HAVING子句

SELECT product_type,
       COUNT(product_type)
  FROM product
  GROUP BY product_type
  HAVING count(product_type) = 2;

HAVING子句中只允许存在：常数、聚合函数、聚合键，但是聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。

ORDER BY子句

子句的书写顺序：1、SELECT子句-->2、FROM子句-->3、WHERE子句-->4、GROUP BY子句-->5、HAVING子句-->6、ORDER BY子句。

• 未指定ORDER BY子句中排列顺序时会默认使用升序进行排列，ASC和DESC是ascendent（上升的）和descendent（下降的）这两个单词的缩写；

• 排序键中包含NULL时，会在开头或末尾进行汇总；

• 在ORDER BY子句中可以使用SELECT子句中定义的别名；

• 在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

INSERT子句

INSERT INTO productcopy
  SELECT *
    FROM product
    WHERE product_type = '厨房用具';

• 通过在建表语句中设置DEFAULT约束来设定默认值，省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）

• INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何SQL语法（ 但使用ORDER BY子句并不会产生任何效果）。

DELETE子句

DELETE FROM productcopy
  WHERE product_type = '叉子';

DELETE语句的删除对象并不是表或者列，而是记录（行）,可以通过WHERE子句指定对象条件来删除部分数据。

TRUNCATE productcopy;

TRUNCATE语句只能用于删除表中全部数据。

UPDATE子句

UPDATE product
  SET sale_price = sale_price * 10,
      purchase_price = purchase_price / 2
  WHERE product_type = '厨房用具';

事务

• 事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交(COMMIT)和取消(ROLLBACK)进行管理；

• DBMS的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）四种特性。

子查询

表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句，应该将经常使用的SELECT语句做成视图，并避免在视图的基础上创建视图。

子查询作为内层查询会首先执行，标量子查询就是返回单一值的子查询。

SELECT product_type,
       product_name,
       sale_price
  FROM product AS p1
  WHERE sale_price > (SELECT AVG(sale_price)
                      FROM product AS p2
                      WHERE p1.product_type = p2.product_type
                      GROUP BY product_type);

关联子查询会在细分的组内进行比较时使用，结合条件一定要写在子查询中。

函数、谓词、CASE表达式

谓词：LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS(通常指定关联子查询作为EXIST的参数)。

SELECT SUM(CASE WHEN product_type = '衣服'
                     THEN sale_price ELSE 0 END ) AS "衣服合计",
       SUM(CASE WHEN product_type = '厨房用具'
                     THEN sale_price ELSE 0 END ) AS "厨房用具合计",
       SUM(CASE WHEN product_type = '办公用品'
                     THEN sale_price ELSE 0 END ) AS "办公用品"
  FROM product;

表的加减法

集合运算符：UNION（并集）、INTERSECT（交集）、EXCEPT（差集），以行为单位进行操作。集合运算符会除去重复的记录，使用ALL选项，可以保留重复行。

联结：INNER JOIN（内联结）、OUTER JOIN（外联结），以列为单位对表进行操作。

• 进行联结时需要在FROM子句中使用多张表；

• 进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间；

• 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。