SQL系统学习笔记之数据库、表创建与基础查询

前言

以postgres为说明对象

数据库的创建

CREATE DATABASE <数据库名称>;

表的创建

CREATE TABLE <表名>
（<列名1> <数据类型> <该列所需约束>，
 <列名2> <数据类型> <该列所需约束>，
 <列名3> <数据类型> <该列所需约束>，
 <列名4> <数据类型> <该列所需约束>，
...
 <该表的约束1>， <该表的约束2>，……）；
 
 #e.g.
 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));

数据类型

• INTEGER型

  用来指定存储整数的列的数据类型（数字型），不能存储小数。

• CHAR型

  • CHAR 是 CHARACTER（字符）的缩写，是用来指定存储字符串的列的数据类型（字符型）。像 CHAR(10) 或者 CHAR(200) 这样，在括号中指定该列可以存储的字符串的长度（最大长度）。字符串超出最大长度的部分是无法输入到该列中的。RDBMS 不同，长度单位也不一样，既存在使用字符个数的情况，也存在使用字节长度 A 的情况;
  • 字符串以定长字符串  的形式存储在被指定为 CHAR 型的列中;所谓定长字符串，就是当列中存储的字符串长度达不到最长度的时候，使用半角空格进行补足

• VARCHAR型

  • CHAR 类型一样，VARCHAR 型也是用来指定存储字符串的列的数据类型（字符串类型），也可以通过括号内的数字来指定字符串的长度（最大长度）。
  • 以可变长字符串 的形式来保存字符串的 ,(即使字符数未达到最大长度，也不会用半角空格补足);

• DATE型

  用来指定存储日期（年月日）的列的数据类型（日期型）。

表的删除（DROP TABLE语句）

DROP TABLE <表名>

表定义的更新（ALTER TABLE语句）

#添加列
ALTER TABLE <表名> ADD COLUMN <列的定义>
e.g.
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);

Oracle和SQL Server中不用写COLUMN。

• ALTER TABLE <表名> ADD <列定义> ；
• ALTER TABLE <表名> ADD （<列定义>，<列定义>，……）；#添加多列

---

# 删除列
ALTER TABLE <表名> DROP COLUMN <列名>；

Oracle中不用写COLUMN。

• ALTER TABLE <表名> DROP<列名> ；
• ALTER TABLE <表名> DROP（<列名>，<列名>，……）；#删除多列

---

# 变更表名:指令（RENAME）
ALTER TABLE Poduct RENAME TO Product;#Oracle、PostgreSQL
RENAME TABLE Poduct to Product; #mysql

基础查询

#查询全部列；
SELECT * FROM <表名>;
 #查询指定列；
SELECT <列名1>， <列名2>...  FROM <表名>;

#列设置别名： AS关键字
SELECT product_id AS id,
 product_name AS name,
 purchase_price AS price
 FROM Product;
#别名可以使用中文，使用中文时需要用双引号（"）括起来
SELECT product_id AS "商品编号",
 product_name AS "商品名称",
 purchase_price AS "进货单价"
 FROM Product;

#常数的查询
SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date,
 product_id, product_name
 FROM Product;
#注意：使用字符串或者日期常数时，必须使用单引号 (')将其括起来。

结果：

string	number	date	product_id	product_name
商品	38	2009-02-24	0001	T恤衫
商品	38	2009-02-24	0002	打孔器
商品	38	2009-02-24	0003	运动T恤
商品	38	2009-02-24	0004	菜刀
商品	38	2009-02-24	0005	高压锅
商品	38	2009-02-24	0006	叉子
商品	38	2009-02-24	0007	擦菜板
商品	38	2009-02-24	0008	圆珠笔

---

#删除重复行：关键字：DISTINCT
SELECT DISTINCT product_type FROM Product;#对product_type去重查询（包含null值）

#组合删除重复行
SELECT DISTINCT product_type, regist_date FROM Product;#product_type+regist_date组合唯一
#注意:DISTINCT 关键字只能用在第一个列名之前

---

# WHERE 子句:指定查询数据的条件
SELECT <列名>, …… FROM <表名> WHERE <条件表达式>;#WHERE 子句查询出符合指定条件的行，然后再选取出 SELECT 语句指定的列
#注意：WHERE 子句必须紧跟在 FROM 子句之后，书写顺序发生改变的话会造成执行错误

---

#关于null的运算
所有包含 NULL 的计算，结果肯定是 NULL
 5 + NULL //NULL
 10 - NULL//NULL
 1 * NULL//NULL
 4 / NULL//NULL
 NULL / 9 //NULL
 NULL / 0//NULL

---

#逻辑运算符：
##NOT运算符：不能单独使用，必须和其他查询条件组合起来使用。
SELECT product_name, product_type, sale_price 
FROM Product
WHERE NOT sale_price >= 1000;
##AND运算符：在其两侧的查询条件都成立时整个查询条件才成立
##OR运算符：在其两侧的查询条件有一个成立时整个查询条件都成立
#注意:AND 运算符优先于 OR 运算符

聚合

常用函数：

• COUNT：计算表中的记录数（行数）

  COUNT函数的结果根据参数的不同而不同
  count(*):计算所有行数，包含null
  count(<列名>)：计算NULL之外的数据的行数

• SUM： 计算表中数值列中数据的合计值

  会将NULL排除在外;只能对数值类型的列使用

• AVG： 计算表中数值列中数据的平均值

  会将NULL排除在外;只能对数值类型的列使用

• MAX： 求出表中任意列中数据的最大值

  数原则上可以适用于任何数据类型的列

• MIN： 求出表中任意列中数据的最小值

  数原则上可以适用于任何数据类型的列

#计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
 FROM Product;#对类型先去重，再计算种类数量
#想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
#不仅限于 COUNT 函数，所有的聚合函数都可以使用 DISTINCT。

分组

#关键字：GROUP BY
##在 GROUP BY 子句中指定的列称为聚合键或者分组列
##一定要写在FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）。
SELECT <列名1>, <列名2>, <列名3>, ……
 FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

常见错误① ——在SELECT子句中书写了多余的列
SELECT 子句中只能存在以下三种元素:

• 常数
• 聚合函数
• GROUP BY子句中指定的列名（也就是聚合键）
  MySQL 中支持select中支持多写列，多列候补中只要有一列满足要求就可以了；并不会报错；

常见错误② ——在GROUP BY子句中写了列的别名
e.g.SELECT product_type AS pt, COUNT(*) FROM Product GROUP BY pt;
执行顺序造成的——SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时，SELECT 子句中定义的别名，DBMS 还并不知道。
虽然这样的写法在PostgreSQL和MySQL都不会发生执行错误，但是这并不是通常的使用方法

常见错误③——GROUP BY子句的结果能排序吗
随机的；当你再次执行同样的 SELECT 语句时，得到的结果可能会按照完全不同
的顺序进行排列。

常见错误④——在WHERE子句中使用聚合函数
只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。

聚合结果指定条件

关键字：HAVING

SELECT <列名1>, <列名2>, <列名3>, ……
 FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>

SELECT product_type, COUNT(*)
 FROM Product
 GROUP BY product_type
HAVING COUNT(*) = 2;#从按照商品种类进行分组后的结果中，取出“包含的数据行数为2
行”的组

HAVING 子句必须写在 GROUP BY 子句之后，其在 DBMS 内部的执行顺序也排在 GROUP BY 子句之后。
HAVING 子句中能够使用的 3 种要素如下:

• 常数
• 聚合函数
• GROUP BY子句中指定的列名（即聚合键）
  例如：HAVING COUNT （*）= 2；有常数有聚合函数

排序

关键字：ORDER BY

• 不进行排序的话，每次的查询结果顺序可能不同；
• ORDER BY子句通常写在SELECT语句的末尾。
• 未指定ORDER BY子句中排列顺序时会默认使用升序进行排列。
• 排序键中包含NULL时，会在开头或末尾进行汇总
• 在 GROUP BY 子句中不能使用SELECT 子句中定义的别名(执行顺序原因)，但是在 ORDER BY 子句中却是允许使用别名。
• ORDER BY 子句中也可以使用存在于表中、但并不包含在 SELECT子句之中的列；除此之外，还可以使用聚合函数

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, ……

SELECT product_type, COUNT(*)
 FROM Product
 GROUP BY product_type
ORDER BY COUNT(*);

子查询

标量子查询

• 标量即是单-的意思;限制就是必须而且只能返回 1 行 or 1 列的结果，也就是返回表中某一行的某一列的值
• 可以使用的位置：能够使用常数或者列名的地方，无论是 where 子句、SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

SELECT product_id, product_name, sale_price
 FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)
 FROM Product

关联子查询

• 在细分的组内进行比较时，需要使用关联子查询
• 关键是在子查询中使用 where 子句条件

-- 对不同类型的商品的平均价格进行分组,查询各自类型中单价大于平均价的商品
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
);-- group by子句可以删除

SELECT product_type, product_name, sale_price
 FROM Product AS P1
 WHERE P1.product_type = P2.product_type -- 将查询关键where子句移到外面报错，因为P2的作用域在子查询语句中
 WHERE sale_price > (SELECT AVG(sale_price)
    FROM Product AS P2
    GROUP BY product_type
);-- group by子句可以删除

---

SELECT 语句的执行顺序（粗略）

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

子句的书写顺序

1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →
2. HAVING 子句 → 6. ORDER BY 子句