SQL基础篇(MICK)

SQL基础教程(Mick)

数据库和SQL

C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d shop

数据库的基本概念

数据库(DB):将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合
数据库管理系统(DBMS):用来管理数据库的计算机系统
DBMS的重要性:(文本文件和电子表格无法做到)

无法多人共享数据
无法提供操作大量数据所需的格式
实现读写自动化需要编程能力
无法应对突发事故
DBMS的种类
层次数据库：Hierarchical DB
关系数据库：Relational DB 使用SQL(Structured Query Language)查询
- 关系数据库管理系统：Relational Database Management System
  - Oracle
  - SQL Server
  - DB2
  - Postgresql
  - MySQL
面向对象数据库:Object Oriented DB
XML数据库：XMLDB
键值存储系统：Key_Value Store

数据库的结构

RDBMS的常见系统结构
客户端、服务器，客户端通过SQL调用数据
表的结构

表：由行列组成的二维表
根据SQL语句的内容返回的数据必须是二维表的形式
字段、记录关系数据库以行为单位进行数据读写
列(垂直方向)和行(水平方向)交叉的方格称为单元格,一个单元格中只能输入一个数据

SQL概要

SQL是用来操作关系数据库的语言
SQL语句的种类
- DDL(Data Definition Language) 用来创建或删除存储数据用的数据库以及数据库中的表等对象
  - CREATE:创建数据库和数据表
  - DROP:删除数据库和数据表
  - ALTER:修改数据库和表的结构
- DML(Data Manipulation Language) 用来查询或者变更表中的记录
  - SELECT:查询表中的数据
  - INSERT:向表中插入新数据
  - UPDATE:更新表中的数据
  - DELETE:删除表中的数据
- DCL(Data Control Language) 用来确认或者取消对数据库中的数据进行的变更
  - COMMIT:确认对数据库中的数据进行的变更
  - ROLLBACK:取消对数据库中的数据进行的变更
  - GRANT:赋予用户操作权限
  - REVOKE:取消用户的操作权限
SQL的基本书写规则
- SQL语句要以分号(;)结尾一个SQL语句可以描述一个数据库操作
- SQL语句不区分大小写为便于理解统一规则
  - 关键字大写
  - 表名的首字母大写
  - 列名小写
  - 插入到数据表中的数据是区分大小写的，在单元格里Computer、COMPUTER、computer是不一样的
- 常数的书写方式是固定的
  - 日期需要单引号括起来建议统一'2010-01-26'格式
  - 数字常数无需加注单引号
  - 单词需要用半角空格或者换行来分隔

表的创建

数据库的创建

CREATE DATABASE <数据库名>;
CREATE DATABASE shop;

表的创建

CREATE TABLE <表名>
    				(<列名1><数据类型><该列的约束>
    				 <列名2><数据类型><该列的约束>
    				 <列名3><数据类型><该列的约束>
    				 ...
    				 <该表的约束1><该表的约束2>);
CREATE TABLE Product
    				 (product_id CHAR(4) NOT NULL,
    				  product_name VARCHAR(10) NOT NULL,
    				  product_type VARCHAR(32) NOT NULL,
    				  sale_price INTEGER ,
    				  purchase_price INTEGER ,
    				  regist_date DATE ,
    				  PRIMARY KEY (product_id)
    				 );  --Postgresql通过 \dt来查看数据库里的表 \dt <表名>来查看表的定义

命名规则
- 只能使用半角英文字母、数字、下划线作为数据库、表和列的名称
- 名称必须以半角英文字母开头
- 同一个数据库中不能创建两个相同名称的表
数据类型
- 所有的列都必须指定数据类型
  数据类型表示数据的种类，包括数字型、字符型和日期型
  - INTEGER 用来存储整数，不能存储小数
  - CHAR 用来指定存储字符串定长字符串区分大小写
  - VARCHAR 用来指定存储字符串可变字符串区分大小写 -- Oracle中使用VARCHAR2
  - DATE 用来指定存储日期
约束的设置：除了数据类型外，对列中存储的数据进行限制或追加条件的功能
- NOT NULL 非空
- PRIMARY KEY 主键(非空不重复)

表的删除和更新

表的删除

     DROP TABLE <表名>;
     DROP TABLE <Product>; --无法恢复

表的更新

-- 增加列
ALTER TABLE <表名> ADD COLUMN <列名>;
-- Oracle和MS SQL 可以不写COLUMN
-- Oracle添加多列：ALTER TABLE <表名> ADD (<列名1>,<列名2>,...);

-- 删除列
ALTER TABLE <表名> DROP COLUMN <列名>;
-- Oracle不用写COLUMN
-- Oracle删除多列：ALTER TABLE <表名> DROP (<列名1>,<列名2>,...); --无法恢复

-- 向表中插入数据
-- DML：插入数据
BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20');
INSERT INTO Product VALUES ('0002','打孔器','办公用品',500,320,'2009-09-11');
INSERT INTO Product VALUES ('0003','运动T恤','衣服',4000,2800,NULL);
INSERT INTO Product VALUES ('0004','菜刀','厨房用具',3000,2800,'2009-09-20');
INSERT INTO Product VALUES ('0005','高压锅','厨房用具',6800,5000,'2009-01-15');
INSERT INTO Product VALUES ('0006','叉子','厨房用具',500,NULL,'2009-09-20');
INSERT INTO Product VALUES ('0007','擦菜板','厨房用具',880,790,'2008-04-28');
INSERT INTO Product VALUES ('0008','圆珠笔','办公用品',100,NULL,'2009-11-11');
COMMIT;  
-- MySQL改为：START TRANSACTION -- Oracle和DB2中不用BEGIN TRANSACTION

-- 表名的修改
ALTER TABLE Poduct RENAME TO Product; -- Oracle/PostgreSQL
RENAME TABLE Poduct TO Product; -- DB2:
sp_rename 'Poduct','Product'; -- SQL SEVER:
RENAME TABLE Poduct TO Product; --MySQL:

查询基础

SELECT语句基础

列的查询

SELECT <列名1>,<列名2>,... FROM <表名>;
-- 子句 SELECT子句列举了需要查询出的列的名称 FROM指定了选取出数据表的名称
-- 列的顺序可以指定,多列逗号分隔,查询列的顺序与SELECT子句顺序相同
SELECT product_id,product_name,purchase_price FROM Product;

查询所有列

SELECT * FROM Product;
-- *号代表所有列 此时会按照CREATE TABLE语句的定义对列进行排序
-- 以子句为单位使用换行符 不要随意空行

为列设定别名

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;

从结果中删除重复行

SELECT DISTINCT product_type FROM Product; -- NULL也被视为一类数据，保留一个，多列之前使用DISTINCT
SELECT DISTINCT product_type,regist_date FROM Product; -- 指定多列一样才删除，DISTINCT必须使用在第一个列名之前

根据WHERE子句来查询

SELECT <列名>,... FROM <表名> WHERE <条件表达式>;
SELECT product_name,product_type FROM Product WHERE product_type = '衣服';
-- SELECT中子句的书写顺序是固定的，不能随便更改

注释的书写方法

-- 单行注释使用'--' 只能写在同一行
-- 多行注释使用/* 和 */之间 可以跨多行

算数运算符和比较运算符

算数运算符(+ - * /)

SELECT product_name,sale_price,sale_price * 2 AS "sale_price_x2" FROM Product;
-- 运算以行为单位来执行 SELECT子句中可以使用常数和表达式
-- 需要注意NULL所有包含NULL的计算，结果肯定是NULL(不想要这样做的解决办法在第6章)
-- FROM字句并非不可少 Oracle必不可少
SELECT (100+200) * 3 AS calculation;

比较运算符(= <> <= >= < > )

SELECT product_name,product_type FROM Product WHERE sale_price <> 500;
SELECT product_name,product_type,sale_price FROM Product WHERE sale_price >= 500;
SELECT product_name,product_type,regist_date FROM Product WHERE regist_date < '2009-09-27';
SELECT product_name,sale_price,purchase_price FROM Product WHERE sale_price - purchase_price >= 500;
-- 要注意区分数字和字符，例如1和'1',如果对字符串进行比较运算，会按照字典先后顺序进行排序
-- 不能对NULL使用比较运算符
SELECT product_name,purchase_price FROM Product WHERE purchase_price = 2800;
SELECT product_name,purchase_price FROM Product WHERE purchase_price <> 2800;
SELECT product_name,purchase_price FROM Product WHERE purchase_price = NULL; -- 无效语法
SELECT product_name,purchase_price FROM Product WHERE purchase_price IS NULL;
SELECT product_name,purchase_price FROM Product WHERE purchase_price IS NOT NULL;

逻辑运算符

-- NOT运算符
SELECT product_name,product_type,sale_price FROM Product WHERE sale_price >= 1000;
SELECT product_name,product_type,sale_price FROM Product WHERE NOT sale_price >= 1000;	
SELECT product_name,product_type,sale_price FROM Product WHERE sale_price < 1000;	
-- AND和OR运算符 AND优先级高于OR
-- AND:运算符在两侧的查询条件都成立时整个查询条件才成立，相当于"并且"
SELECT product_name,purchase_price FROM Product WHERE product_type = '厨房用具' AND sale_price >= 3000;
-- OR：运算符在两侧的查询条件有一个成立时整个查询条件都成立，相当于"或"
SELECT product_name,purchase_price FROM Product WHERE product_type = '厨房用具' OR sale_price >= 3000;
-- 通过()提高运算符的优先级
SELECT product_name,product_type,regist_date FROM Product WHERE product_type = '办公用品' AND regist_date = '2009-09-11' OR regist_date ='2009-09-20'; -- 不合查询逻辑
SELECT product_name,product_type,regist_date FROM Product WHERE product_type = '办公用品' AND (regist_date = '2009-09-11' OR regist_date ='2009-09-20');

逻辑运算符和真值 NOT AND OR称为逻辑运算符真值

AND 一假必假;
OR 一真必真;
NOT 真假互换

三值逻辑：

AND 不确定和真为不确定，不确定和假为假;

OR 不确定和真为真，不确定和假为不确定仍然满足AND一假必假,OR一真必真的逻辑

聚合和排序

对表进行聚合查询

聚合函数：COUNT SUM MIN MAX AVG 聚合：将多行汇总成一行

计算表中数据的行数

SELECT COUNT(*) FROM Product; --括号里面的输入值称为参数，输出称为返回值 *作为COUNT()的参数是COUNT函数特有的,会包含NULL，其他聚合函数会将NULL排除在外

计算NULL之外的数据的行数需要指定字段

SELECT COUNT(purchase_price) FROM Product;

计算合计值

SELECT SUM(sale_price) FROM Product;
SELECT SUM(sale_price),SUM(purchase_price) FROM Product; -- 会排除NULL

计算平均值

SELECT AVG(sale_price),AVG(purchase_price),SUM(purchase_price)/COUNT(*) AS "Logic_mean" FROM Product; --avg=2035,sum/count=1526

计算最大、最小值

SELECT MAX(sale_price),MIN(purchase_price) FROM Product;
SELECT MIN(regist_date),MAX(regist_date) FROM Product; -- 对于日期来说，求和求平均没什么意义 max和min几乎适用于所有类型的列，avg和sum值适用于数值类型的列

使用聚合函数删除重复值

SELECT COUNT(DISTINCT product_type) FROM Product; --结果为3 DISTINCT必须写在括号中，该用法一般用于计算种类
SELECT SUM(sale_price),sum(DISTINCT sale_price) FROM Product;

对表进行分组

GROUP BY子句

SELECT <列名1>,<列名2>,<列名3>,... FROM <表名> GROUP BY <列名1>,<列名2>,<列名3>,...;
SELECT product_type,COUNT(*) FROM Product GROUP BY product_type; --GROUP BY 称为聚合键和分组列

聚合键中包含NULL的情况被分为一个组对待

SELECT purchase_price,COUNT(*) FROM Product GROUP BY purchase_price;
-- 使用WHERE子句时GROUP BY的执行结果 先过滤掉不满足WHERE的，剩下的聚合计算
SELECT <列名1>,<列名2>,<列名3>,... FROM <表名> WHERE <条件表达式> GROUP BY <列名1>,<列名2>,<列名3>;
SELECT purchase_price,COUNT(*) FROM Product WHERE product_type = '衣服' GROUP BY purchase_price;

与聚合函数和GROUP BY子句有关的常见错误

使用聚合函数时，SELECT子句只能存在以下3种元素：常数、聚合函数和聚合键

-- 错误1：在SELECT子句中书写了多余的列
SELECT product_name,purchase_price,COUNT(*) FROM Product GROUP BY purchase_price; --报错，因为product_name为3者以外的字段

SELECT子句中的项目可以通过AS指定别名，但GROUP BY子句中是不能使用列名的

-- 错误2：在GROUP BY子句中写了列的别名
SELECT product_type AS pt,COUNT(*) FROM Product GROUP BY pt;--postgresql不会报错，但其他dbms会报错

GROUP BY子句的结果是无序的

-- 错误4：WHERE子句中使用聚合函数	
SELECT product_type,COUNT(*) FROM Product GROUP BY product_type;
SELECT product_type,COUNT(*) FROM Product WHERE COUNT(*) = 2 GROUP BY product_type; 
--WHERE子句不能使用聚合函数

为聚合结果指定条件

HAVING子句：HAVING子句必须写在GROUP BY子句之后,执行顺序也在GROUP BY之后

SELECT <列名1>,<列名2>,<列名3>,... FROM <表名> GROUP BY <列名1>,<列名2>,<列名3>,... HAVING <分组结果对应的条件>
SELECT product_type,COUNT(*) FROM Product GROUP BY product_type HAVING COUNT(*) = 2;

不使用HAVING子句：SELECT product_type,AVG(sale_price) FROM Product GROUP BY product_type;

SELECT product_type,AVG(sale_price) FROM Product GROUP BY product_type HAVING AVG(sale_price) >= 2500;

HAVING子句的构成要素：常数、聚合函数、聚合键
相对于HAVING子句，更适合写在WHERE子句中的条件 DBMS中WHERE比HAVING更快因为WHERE是过滤,HAVING是先分组后合并
- 非聚合条件 --> WHERE 指定行对应的条件
- 聚合条件 --> 指定组所对应的条件

对查询结果进行排序

ORDER BY子句

SELECT product_id,product_name,sale_price,purchase_price FROM Product; --排序是随机的
SELECT <列名1>,<列名2>,<列名3>,... FROM <表名> ORDER  BY <排序基准列1>,<排序基准列2>,...; --排序键
-- 书写顺序： SELECT AS FROM WHERE GROUP BY HAVING ORDER BY

指定升序和降序:默认升序 DESC降序

SELECT product_id,product_name,sale_price,purchase_price FROM Product ORDER BY sale_price DESC;

指定多个排序键

SELECT product_id,product_name,sale_price,purchase_price FROM Product ORDER BY sale_price,product_id;

NULL的顺序；排序键中包含NULL时，会在开头或末尾进行汇总
排序键中使用显示的别名

SELECT product_id AS id,product_name,sale_price AS sp,purchase_price FROM Product ORDER BY sp,id;

**SELECT语句的执行顺序：FROM WHERE GROUP BY HAVING SELECT ORDER BY **

ORDER BY子句可以使用的列

-- ORDER BY子句中也可以使用存在于表中，但并不包含在SELECT子句中的列
SELECT product_name,sale_price,purchase_price FROM Product ORDER BY product_id;

-- ORDER BY子句中也可以使用聚合函数
SELECT product_type,COUNT(*) FROM Product GROUP BY product_type ORDER BY COUNT(*);

-- ORDER BY子句还可以使用在SELECT子句中出现的列所对应的编号，但不建议使用 1.不便于阅读 2.标准SQL将来会删除
SELECT product_id,product_name,sale_price,purchase_price FROM Product ORDER BY sale_price DESC,product_id;
SELECT product_id,product_name,sale_price,purchase_price FROM Product ORDER BY 3 DESC,1;

数据更新

数据的插入INSERT

-- 创建表
CREATE TABLE ProductIns
  		(product_id CHAR(4) NOT NULL,
  		 product_name VARCHAR(100) NOT NULL,
  		 product_type VARCHAR(32) NOT NULL,
  		 sale_price INTEGER DEFAULT 0,
  		 purchase_price INTEGER ,
  		 regist_date DATE,
  		 PRIMARY KEY (product_id)
  		);

单行插入

INSERT INTO <表名> (列1,列2,...) VALUES (列1值,列2值,...); -- 列清单和值清单要保持维度一致
INSERT INTO ProductIns (product_id,product_name,product_type,sale_price,purchase_price,regist_date) VALUES 
('0001','T恤衫','衣服',1000,500,'2009-09-20');
-- 原则上，执行一次INSERT语句会插入一行数据

多行插入

INSERT INTO ProductIns VALUES ('0002','打孔器','办公用品',500,320,'2009-09-11'),
						    ('0003','运动T恤','衣服',4000,2800,NULL),
						    ('0004','菜刀','厨房用具',3000,2800,'2009-09-20');
-- Oracle多行插入：INSERT ALL INTO ProductIns VALUES ('0002','打孔器','办公用品',500,320,'2009-09-11') INTO ProductIns VALUES ('0003','运动T恤','衣服',4000,2800,NULL)
INTO ProductIns VALUES ('0004','菜刀','厨房用具',3000,2800,'2009-09-20') SELECT * FROM DUAL;

列清单的省略

-- 列清单的省略：对表进行全列INSERT时，可以省略表名后的列清单
INSERT INTO ProductIns VALUES ('0005','高压锅','厨房用具',6800,5000,'2009-09-15');

插入NULL:INSERT语句中想给某一列插入NULL时,可以直接在值清单中写入NULL,但写入NULL的列一定不能设置NOT NULL约束

INSERT INTO ProductIns VALUES ('0006','叉子','厨房用具',500,NULL,'2009-09-20');

插入默认值：如果没有设置默认值的列，则插入默认值会输入NULL，如果设置了非空的列，隐式的方式插入默认值会报错；显式方式：使用列赋值DEFAULT

-- 插入默认值：如果没有设置默认值的列，则插入默认值会输入NULL，如果设置了非空的列，隐式的方式插入默认值会报错；显式方式：使用列 赋值DEFAULT
INSERT INTO ProductIns (product_id,product_name,product_type,sale_price,purchase_price,regist_date) VALUES 
('0007','擦菜板','厨房用具',DEFAULT,790,'2009-04-28');

-- 隐式方式：列名清单省略列 值省略
INSERT INTO ProductIns (product_id,product_name,product_type,purchase_price,regist_date) VALUES ('0007','擦菜板','厨房用具',DEFAULT,790,'2009-04-28')

从其他表复制数据

-- 从其他表复制数据
-- 创建ProductCopy表
CREATE TABLE ProductCopy
		(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 ProductCopy 
(product_id,product_name,product_type,purchase_price,regist_date) 
SELECT product_id,product_name,product_type,purchase_price,regist_date FROM Product;
-- 创建ProductType表	
CREATE TABLE ProductType 
		(product_type VARCHAR(32) NOT NULL,
		 sum_sale_price INTEGER ,
		 sum_purchase_price INTEGER,
		 PRIMARY KEY (product_type)
		);
INSERT INTO ProductType (product_type,sum_sale_price,sum_purchase_price) 
SELECT product_type,sum(sale_price),sum(purchase_price) FROM Product GROUP BY product_type;
-- INSERT的语句中的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何SQL语法，但使用ORDER BY并不会产生任何效果

数据的删除DELETE

DROP TABLE语句和DELETE语句
- DROP TABLE语句可以将表完全删除 -- 误删表恢复起来将变得十分困难
- DELETE语句会留下表容器，而删除表中的全部数据 -- 可以重新对表进行赋值
DELETE语句的基本用法

DELETE FROM <表名>; -- DELETE删除的对象是行，而不是表或者记录

搜索型DELETE 指定部分对象的DELETE语句

DELETE FROM <表名> WHERE <条件>;
DELETE FROM Product WHERE sale_price >= 4000; --DELETE语句不能使用GROUP BY HAVING ORDER BY
TRUNCATE <表名>; 用法处理时间比DELETE短，但没有ROLLBACK功能，默认直接COMMIT

数据的更新UPDATE

UPDATE的基本用法

UPDATE <表名> SET <列名> = <表达式>;
UPDATE Product SET regist_date = '2009-10-10';

搜索型UPDATE 指定条件的UPDATE语句

UPDATE <表名> SET <列名> = <表达式> WHERE <条件表达式>;
UPDATE Product SET sale_price = sale_price * 10 WHERE product_type = '厨房用具';

使用NULL进行更新该更新俗称NULL清空

UPDATE Product SET regist_date = NULL WHERE product_id = '0008';

多列更新

UPDATE Product SET sale_price = sale_price * 10,purchase_price = purchase_price / 2 WHERE product_type = '厨房用具'; -- 可以在所有的DBMS都可以使用
UPDATE Product SET (sale_price,purchase_price) = (sale_price * 10 ,purchase_price /2) WHERE product_type = '厨房用具'; -- 只能在Postgresql和DB2中使用

事务 TRANSACTION

事务：需要在同一个处理单元中执行的一系列更新处理的结合，在INSERT UPDATE DELETE中有很大价值

创建事务：

事务开始语句;
			DML语句1;
			DML语句2;
			DML语句3;
事务结束语句;(COMMIT或ROLLBACK)

不同的事务开始语句：标准SQL中没有定义事务开始语句
- PostgreSQL、SQL SEVER: BEGIN TRANSACTION;
- MySQL: START TRANSACTION;
- Oracle、DB2:无
事务结束语句：只有COMMIT 和 ROLLBACK两种，所有rdbms通用
- COMMIT:是提交事务包含的全部更新处理的结束指令，一旦提交，无法恢复，尤其是DELETE语句后的COMMIT需要尤其小心
- ROLLBACK:取消处理事务包含的全部更新处理的结束指令，一旦回滚，数据库恢复到事务开始前的状态
事务何时开始处理？通常有如下两种情况：

每条SQL语句是一个事务(自动提交模式);2.直到用户执行COMMIT或者ROLLBACK为止算作一个事务
SQL SEVER/PostgreSQL/MySQL均为自动提交模式，而Oracle为模式2.

ACID特性：
- 原子性(Atomicity):指事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行
- 一致性(Consistency)：指事务中包含的处理要满足数据库提前设置的约束，只有那些没有违反约束的会被执行
- 隔离性(Isolation):指不同的事务之间互不干扰的特性，该特性保证了事务之间不会相互嵌套
- 持久性(Durability)：持久性也可以称为耐久性，指的是在事务(不论是提交还是回滚)结束后，DBMS能够保证该时间点的数据状态都会被保存的特性；保证了数据可以恢复，最常见的恢复办法是通过保存事务的日志

复杂查询

视图

视图和表：
- 区别：
  - 从SQL的角度来看视图就是一张表，更新时视图和表会有一些不同，区别在于是否保存了实际的数据
  - 通常使用INSERT语句将数据保存到数据库之中，数据库中的数据实际被保存到计算机的存储设备上，视图不会讲数据保存到存储设备中，也不会保存到其他任何地方，视图只是保存了SELECT语句的结果，相当于临时表
- 优点：
  - 视图无需保存数据，可以节省存储设备的容量；
  - 可以将频繁使用的的SELECT语句保存成视图，这样就不用每次都重复书写了；
  - 视图中的数据会跟着原表的变化来自动更新。
创建视图的方法：

CREATE VIEW <视图名称> (<视图名称1>,<视图名称2>,...) AS <SELECT语句>
CREATE VIEW ProductSum (product_type,cnt_product) AS SELECT product_type,COUNT(*) FROM Product GROUP BY product_type;

使用视图的查询的步骤(使用2条及以上的视图) -- 虽然语法上没有错误，但不建议使用多重视图，这是因为大多数DBMS上使用多重视图会降低SQL的性能
- 首先执行定义视图的SELECT语句;
- 根据得到的结果,再执行在FROM子句中使用视图的SELECT语句

CREATE VIEW ProductSumJim (product_type,cnt_product) AS SELECT
product_type,cnt_product FROM ProductSum WHERE product_type = '办公用品'; 
SELECT * FROM ProductSumJim;

视图的两个限制：
- 定义视图是不能使用ORDER BY子句，这是因为视图和表一样，数据行是没有顺序的，在某些特定的如PostgreSQL上可以使用ORDER BY子句，但这毕竟不是通用的语法
- 对视图进行更新，标准SQL中规定可以更新的条件：
  - SELECT子句中未使用DISTINCT;
  - FROM子句中只使用一张表;
  - 未使用GROUP BY子句
  - 未使用HAVING子句
删除视图：

DROP VIEW <视图名称> (<视图列名1>,<视图列名2>,...)

子查询

没有被保存的，只使用一次的视图

SELECT product_type,cnt_product FROM (SELECT product_type,COUNT(*) AS cnt_product FROM Product GROUP BY product_type) AS ProductSum; --oracle中不能使用AS

增加子查询的层数 --随着子查询嵌套层数越来越多，SQL语句会变得可读性差，性能也会越来越差，应该尽量避免

SELECT product_type,cnt_product 
FROM (SELECT * FROM (SELECT product_type,COUNT(*) AS cnt_product FROM Product GROUP BY product_type) AS ProductSum 
WHERE cnt_product = 4) AS ProductSum2;

子查询的名称：
原则上子查询必须设定名称，因此尽量根据实际查询出来的结果命名，需要使用AS关键字，该关键字有时可以省略，但在Oracle中必须使用
标量(单一、一行一列)子查询 Scalar subquery：返回单一结果的查询

SELECT AVG(sale_price) FROM Product;
SELECT product_id,product_name,sale_price FROM Product WHERE sale_price > (SELECT AVG(sale_price) FROM Product); --sql会最先执行子查询

标量子查询的书写位置：

能够使用常数和列名的地方，都可以使用标量子查询，GROUP BY SELECT HAVING ORDER BY等都可以使用

SELECT product_id,product_name,sale_price,(SELECT AVG(sale_price) FROM Product) FROM Product; 
--注意此处不能单独使用AVG(sale_price)
SELECT product_type,AVG(sale_price) FROM Product GROUP BY product_type HAVING AVG(sale_price) > (SELECT AVG(sale_price) FROM Product);

使用标量子查询的注意事项：该子查询绝对不能返回多行结果

关联子查询

适用于分组后的各个样本，和分组后的某指标的比较

普通子查询和关联子查询的区别：普通子查询一般在分组前使用，关联子查询一般对有GROUP BY条件，且要查询的对象为分组后的条件来使用

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);

关联子查询一般放在WHERE子句中，并在子查询的WHERE里面添加关联条件对于子查询来说，子查询每执行一次，外部查询按行执行一次
在细分的组内进行比较时，就可以用到关联子查询
关联子查询的结合条件一定要写在子查询之中，这是因为关联名称的作用域问题，"内部可以看到外部，外部看不到内部"

函数、谓词和CASE表达式

各种各样的函数：

函数：输入某一个值得到相应输入结果的功能，输入值称为参数(parameter),输出值称为返回值

函数分类：
- 算数函数：用来进行数值计算的函数 + - * /
- 字符串函数：用来进行字符串操作的函数
- 日期函数：用来进行日期操作的函数
- 转换函数：用来转换数据类型和值的函数
- 聚合函数：用来进行数据聚合的函数 SUM AVG MIN MAX COUNT
算数函数：

CREATE TABLE SampleMath
  			(m NUMERIC(10,3),
  			 n INTEGER,
  			 p INTEGER
  			);
BEGIN TRANSACTION;
INSERT INTO SampleMath VALUES (500,0,NULL),
							  (-180,0,NULL),
							  (NULL,NULL,NULL),
							  (NULL,7,3),
							  (NULL,5,2),
							  (NULL,4,NULL),
							  (8,NULL,3),
							  (2.27,NULL,3),
							  (5.555,2,NULL),
							  (NULL,1,NULL),
							  (8.76,NULL,NULL);
COMMIT;

ABS函数：

SELECT m,ABS(M) AS abs_col FROM SampleMath; 
--绝大多数函数作用于null都会返回null,转换函数中的COALESCE函数

MOD函数(求余)：

SELECT n,p,MOD(n,p) AS mod_col FROM SampleMath; 
-- SQL SEVER不支持该函数 而是直接使用 %

ROUND函数(四舍五入)：

SELECT m,n,ROUND(m,n) AS round_col FROM SampleMath;

字符串函数：

CREATE TABLE SampleStr
		(str1 CHAR(40),
		 str2 CHAR(40),
		 str3 CHAR(40)
		);
BEGIN TRANSACTION;
INSERT INTO SampleStr VALUES ('opx','rt',NULL),
							 ('abc','def',NULL),
							 ('山田','太郎','是我'),
							 ('aaa',NULL,NULL),
							 (NULL,'xyz',NULL),
							 ('@!#$%',NULL,NULL),
							 ('ABC',NULL,NULL),
							 ('aBC',NULL,NULL),
							 ('abc太郎','abc','ABC'),
							 ('abcdefabc','abc','ABC'),
							 ('micmic','i','I');
COMMIT;

||和concat函数

SELECT str1,str2,str1 || str2 AS str_concat FROM SampleStr;
SELECT str1,str2,str3,str1 || str2 || str3 AS str_concat FROM SampleStr WHERE str1 = '山田';  -- SQL SEVER 和 MySQL使用concat函数

length函数

SELECT str1,LENGTH(str1) AS len_str FROM SampleStr; 
--SQL SEVER 使用len函数

lower和upper函数

SELECT str1,LOWER(str1) AS low_str FROM SampleStr WHERE str1 IN ('ABC','aBC','abc','山田');
SELECT str1,UPPER(str1) AS up_str FROM SampleStr WHERE str1 IN ('ABC','aBC','abc','山田');

replace(替换对象,替换前字符,替换后字符)

SELECT str1,str2,str3,REPLACE(str1,str2,str3) AS rep_str FROM SampleStr;

提取字符串的函数：

--PostgreSQL/MySQL：substring(对象字符串,FROM 开始位置 FOR 截取的字符数) 
SELECT str1,SUBSTRING(str1 FROM 3 FOR 2) AS sub_str FROM SampleStr; --从第3个开始取2个
--SQL SEVER:substring(对象字符串,截取的开始位置,截取的字符数)
SELECT str1,SUBSTRING(str1,3,2) AS sub_str FROM SampleStr;
-- Oracle/DB2:substr(对象字符串,截取的开始位置,截取的字符数)
SELECT str1,SUBSTR(str1,3,2) AS sub_str FROM SampleStr;

日期函数：

当前日期：CURRENT_DATE函数

-- PostgreSQL/MySQL：
SELECT CURRENT_DATE;
-- SQL SEVER:
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
-- Oracle:
SELECT CURRENT_DATE FROM dual;
-- DB2:
SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;

当前时间：CURRENT_TIME函数

-- PostgreSQL/MySQL：
SELECT CURRENT_TIME;
-- SQL SEVER:
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
-- Oracle:
SELECT CURRENT_TIME FROM dual;
-- DB2:
SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1;

当前日期和时间：CURRENT_TIMESTAMP函数

-- PostgreSQL/MySQL/SQL SEVER：
SELECT CURRENT_TIMESTAMP;
-- Oracle:
SELECT CURRENT_TIMESTAMP FROM dual;
-- DB2:
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

截取日期元素：EXTRACT函数 EXTRACT (日期元素 FROM 日期)

-- PostgreSQL/MySQL:
SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
-- SQL SEVER:
SELECT CURRENT_TIMESTAMP,
DATEPART(YEAR , CURRENT_TIMESTAMP) AS year,
DATEPART(MONTH , CURRENT_TIMESTAMP) AS month,
DATEPART(DAY , CURRENT_TIMESTAMP) AS day,
DATEPART(HOUR , CURRENT_TIMESTAMP) AS hour,
DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;
-- Oracle:指定临时表dual;
-- DB2:指定临时表SYSIBM.SYSDUMMY1;

转换函数：用来转换数据类型和值的函数

CAST(转换前的值 AS 想要转换成的数据类型)

-- 字符串--数值 
-- PostgreSQL/SQL SEVER：
SELECT CAST('0001' AS INTEGER) AS int_col;
-- MySQL：
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- Oracle:
SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL;
-- DB2:
SELECT CAST('0001' AS INTEGER) AS int_col FROM SYSIBM.SYSDUMMY1;

-- 字符串--日期/时间
-- PostgreSQL/SQL SEVER/MySQL：
SELECT CAST('2009-12-14' AS DATE) AS int_col;
-- Oracle:
SELECT CAST('2009-12-14' AS INTEGER) AS int_col FROM DUAL;
-- DB2:
SELECT CAST('2009-12-14' AS DATE) AS int_col FROM SYSIBM.SYSDUMMY1;

COALESCE(数据1,数据2,数据3) 返回左边第一个不为NULL的值

-- PostgreSQL/MySQL/SQL SEVER:
SELECT COALESCE(NULL,1) AS col_1,
COALESCE(NULL,'test',NULL) AS col_2,
COALESCE(NULL,NULL,'2009-11-01') AS col_3;
-- Oracle:
SELECT COALESCE(NULL,1) AS col_1,
COALESCE(NULL,'test',NULL) AS col_2,
COALESCE(NULL,NULL,'2009-11-01') FROM DUAL;
-- DB2:SELECT COALESCE(NULL,1) AS col_1,
COALESCE(NULL,'test',NULL) AS col_2,
COALESCE(NULL,NULL,'2009-11-01') FROM SYSIBM.SYSDUMMY1;

谓词

谓词是返回值为真的特殊函数，谓词的返回值全部是TRUE/FALSE/UNKNOWN
常用谓词LIKE/BETWEEN/IS NULL/IS NOT NULL/IN/EXISTS
LIKE谓词，字符串部分一致查询

CREATE TABLE SampleLike
					(strcol VARCHAR(6) NOT NULL,
					PRIMARY KEY (strcol));
BEGIN TRANSACTION;
INSERT INTO SampleLike (strcol) VALUES ('abcddd');
INSERT INTO SampleLike (strcol) VALUES ('dddabc');
INSERT INTO SampleLike (strcol) VALUES ('abdddc');
INSERT INTO SampleLike (strcol) VALUES ('abcdd');
INSERT INTO SampleLike (strcol) VALUES ('ddabc');
INSERT INTO SampleLike (strcol) VALUES ('abddc');
COMMIT;

模式匹配："%"表示多个字符 " _"表示一个字符

-- 前方一致：
SELECT * FROM SampleLike WHERE strcol LIKE 'ddd%';
-- 中间一致：
SELECT * FROM SampleLike WHERE strcol LIKE '%ddd%';
-- 后方一致：
SELECT * FROM SampleLike WHERE strcol LIKE '%ddd';
SELECT * FROM SampleLike WHERE strcol LIKE 'ddd__';
SELECT * FROM SampleLike WHERE strcol LIKE 'ddd___';

BETWEEN谓词左闭右闭

SELECT product_name,sale_price FROM Product WHERE sale_price BETWEEN 100 AND 1000;

IS NULL、IS NOT NULL 判断是否为NULL

SELECT product_name,purchase_price FROM Product WHERE purchase_price IS NULL;
SELECT product_name,purchase_price FROM Product WHERE purchase_price IS NOT NULL;

IN谓词 OR的简便用法：

SELECT product_name,purchase_price FROM Product WHERE purchase_price = 320 OR purchase_price = 500 OR purchase_price = 5000;
SELECT product_name,purchase_price FROM Product WHERE purchase_price IN (320,500,5000);
SELECT product_name,purchase_price FROM Product WHERE purchase_price NOT IN (320,500,5000);

使用子查询作为IN谓词的参数

CREATE TABLE ShopProduct
		(shop_id    CHAR(4)       NOT NULL,
		 shop_name  VARCHAR(200)  NOT NULL,
		 product_id CHAR(4)       NOT NULL,
		 quantity   INTEGER       NOT NULL,
		 PRIMARY KEY (shop_id, product_id));
BEGIN TRANSACTION;
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0001',	30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0002',	50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0003',	15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0002',	30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0003',	120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0004',	20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0006',	10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0007',	40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0003',	20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0004',	50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0006',	90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0007',	70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D',	'福冈',		'0001',	100);
COMMIT;
SELECT product_name,sale_price FROM Product WHERE product_id IN (SELECT product_id FROM ShopProduct WHERE shop_id = '000C');
SELECT product_name,sale_price FROM Product WHERE product_id NOT IN (SELECT product_id FROM ShopProduct WHERE shop_id = '000A');

EXISTS谓词：
- exists的用法与之前的都不相同
- 语法理解起来比较困难
- 实际上即使不使用exists，基本上也可以使用IN或者NOT IN来替代

SELECT product_name,sale_price FROM Product AS P WHERE EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id = '000C' AND SP.product_id= P.product_id);	-- 不关心SELECT的结果
SELECT product_name,sale_price FROM Product AS P WHERE NOT EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id = '000C' AND SP.product_id= P.product_id);	-- 不关心SELECT的结果

CASE表达式

区分情况，通常用于条件分枝
CASE的语法结构：

	CASE WHEN <求值表达式> THEN <表达式>
	WHEN <求值表达式> THEN <表达式
	WHEN <求值表达式> THEN <表达式>
	...
	ELSE <表达式>
	END  --求值表达式  类似于 列 = 值

CASE表达式的使用方法：书写位置(任何一个表达式的位置都可以)

SELECT product_name,
	   CASE WHEN product_type = '衣服' THEN 'A: ' || product_type
			WHEN product_type = '办公用品' THEN 'B: ' || product_type
			WHEN product_type = '厨房用具' THEN 'C: ' || product_type
			Else NULL
	   END AS abc_product_type --end 不可以省略
	   FROM Product;  -- 搜索case表达式


SELECT product_type,
SUM(sale_price) AS sum_price FROM Product GROUP BY product_type;
SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office,
SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen 
FROM Product;

SELECT product_name,
	   CASE product_type
			WHEN '衣服' THEN 'A: ' || product_type
			WHEN '办公用品' THEN 'B: ' || product_type
			WHEN  '厨房用具' THEN 'C: ' || product_type
			Else NULL
			END AS abc_product_type --end 不可以省略
			FROM Product;  -- 简单case表达式	   
-- Oracle还使用DECODEN函数替代case表达式 --MySQL还可以使用IF函数来替代

集合运算

并集union 交集intersect 差集except/minus

表的加减法

集合在数学领域表示各种各样事物的总和，在数据库领域表示记录的集合**
表的加法：

CREATE TABLE Product2
			 (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));

BEGIN TRANSACTION;
INSERT INTO Product2 VALUES ('0001', 'T恤', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
COMMIT;
		
SELECT product_id,product_name FROM Product 
UNION SELECT product_id,product_name FROM Product2; 
-- union会进行去重

集合运算的注意事项：
- 注意事项1：作为运算对象的记录的列数必须相同;
- 注意事项2：作为运算对象的记录中的列的类型必须一致;一定要使用不同类型的列时，可以先使用cast转换成类型一样
- 注意事项3：可以使用任何SELEC语句，但order by子句只能在最后使用一次
包含重复行的集合运算--ALL选项

SELECT product_id,product_name FROM Product UNION ALL SELECT product_id,product_name FROM Product2;

选取公共部分：intersect PostgreSQL/DB2/Oracle/SQL SEVER 和intersect all

SELECT product_id,product_name FROM Product 
INTERSECT
SELECT product_id,product_name FROM Product2 ORDER BY product_id; 
-- MySQL不支持

记录的减法 EXCEPT PostgreSQL/DB2/SQL SEVER MINUS Oracle

-- MySQL不支持
SELECT product_id,product_name FROM Product
EXCEPT 
SELECT product_id,product_name FROM Product2;
SELECT product_id,product_name FROM Product
MINUS 
SELECT product_id,product_name FROM Product2;

联结

以列为单位对表进行联结
什么是联结？：union intersect 和except/minus 都是以行方向为单位进行拼接，union一般会增加行，intersect和except/minus一般会减少行

联结运算是基于列的添加列的运算
内联结：INNER JOIN

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id; -- oracle中不能使用AS

- 内联结要点1：from 子句，别名不是必须的，但起别名有助于可读性，多张表
- 内联结要点2：on子句，联结键，书写在from子句后
- 内联结要点3：select子句：<别名>.<列>  
- 内联结可以和where子句结合使用

外联结：LEFT OUTER JOIN / RIGHT OUTER JOIN 就涉及到主表的概念，left以左表为主表，right以右表为主表

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price FROM ShopProduct AS SP LEFT OUTER JOIN Product AS P ON SP.product_id = P.product_id; -- oracle中不能使用AS

3张以上的表的联结：方法完全一样
交叉联结：笛卡尔积的结果 = 内联结 + 外连接

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price FROM ShopProduct AS SP CROSS JOIN Product AS P ON SP.product_id = P.product_id; -- oracle中不能使用AS

过时的知识：from后面跟多张表，where后面跟联结条件，默认内联结

posted @ 2019-08-21 23:24 Evian_Jeff 阅读(798) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Evian_Jeff

The best time to plant a tree is 10 years ago, the second is now