SQL开发指南

2021-09-15 15:14:22

SQL开发指南

标准SQL和特定SQL

PostgreSQL,MySQL

DDL data definition language

create drop alter

DML data maniputation language

select insert update delete

DCL data control language

commit rollback grant revoke

在DDL语句中的alter

Oracle 和 SQL server中不用写 COLUMN，属于特定的SQL

alter table table_name add < 列名 >;

//DB2 PostgreSQL MySQL
ALTER TABLE Product ADD COLUMN product_name varchar(100);
//Oracle
ALTER TABLE Product ADD (product_name varchar(100));
//SQL Server
ALTER TABLE Product ADD product_name varchar(100);

DROP删除表的特定的列，DB2 PostgreSQL MySQL，SQL Server是需要COLUMN关键词的。

而Oracle则是不需要COLUMN关键词的，还有需要一个括号。

DCL语句的不同

MySQL 开启事务 start transaction;

PostgreSQL 开启事务 begin transaction;

Oracle则是不需要开启事务

修改表的名字

由于没有标准SQL语句修改表的名字，所以各个数据库修改表的名字不相同。

DML 语句

查询

查询常数 字符串常数，数字常数，日期常数

select ‘商品‘ as string ,38 as number,‘2009-10-23‘ as date,product_id ,product_name from product;

string | number | date | product_id | product_name
--------+--------+------------+------------+--------------
商品 | 38 | 2009-10-23 | 0001 | T衬衫
商品 | 38 | 2009-10-23 | 0002 | 打孔机
商品 | 38 | 2009-10-23 | 0003 | 运动T血
商品 | 38 | 2009-10-23 | 0004 | 菜刀
商品 | 38 | 2009-10-23 | 0005 | 高压锅
商品 | 38 | 2009-10-23 | 0006 | 叉子
商品 | 38 | 2009-10-23 | 0007 | 擦菜板
商品 | 38 | 2009-10-23 | 0008 | 圆珠笔

distinct 关键字会保留null的数据，因为null也是一条不同与其他的数据。

注释

一行注释 --

多行注释 /* */

尤其注意MySQL中的一行注释，--后要增加一个半角空格。

select 查询中的四则运算 + - * /

select purchase_price,purchase_price*2 as price from product;

注意：对于 NULL的数据，运算结果是NULL.

AND运算符优先级大于 OR运算符

NULL值不可以使用比较运算符，也不能使用逻辑运算符

对于含有null的逻辑运算，会返回除 false true真值外的第三种结果，UNKNOWN不确定。

这是SQL语句的特有情况

聚合函数

count() max() min() avg() sum()

count(*) 和 count(purchase_price) 区别：前者会包含null，后者不会去掉null值的行。

所以的聚合函数，如果以列名为参数，都会去掉null值

sum 和 avg 只能应用数值类型的列；而min和max能适用不同的数据类型。

聚合函数和distinct关键词

select count(product_type) from product; 结果8

select count( distinct product_type) from product; 结果3

distinct能让聚合函数去掉相同的行

group by

当分组的列中存在null值时，null值会当作特殊的一列，统计所以的null值，通知结果。

select purchase_price,count(*) from product group by purchase_price;
purchase_price | count
----------------+-------
| 2
320 | 1
500 | 1
2800 | 2
5000 | 1
790 | 1
(6 行记录)

group容易发生的错误

group by与where使用时，解析的顺序是 FROM → WHERE → GROUP BY → SELECT 这个非常重要，记住这个执行顺序，就能避免很多的错误。

having子句对group by的结果进行过滤

having在group by 后面执行

having 可以使用聚合函数

select product_type,avg(sale_price) from product group by product_type having avg(sale_price)>=2500;

having 子句中能存在的三要素：常数，聚合函数，group by中指定的列

对查询结果进行排序 order by

SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →5 . HAVING 子句 → 6. ORDER BY 子句, 这是个表现，实际上select子句的执行在group 之后，order之前。

注意不能在 group by子句中使用别名，但是能在order 子句中使用别名。

这是因为 SQL 语句在 DBMS 内部的执行顺序被掩盖起来
了。SELECT 语句按照子句为单位的执行顺序如下所示。

使用 HAVING 子句时 SELECT 语句的顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

order by 子句除了使用select存在的列外，也能使用聚合函数。

select product_type from product group by product_type order by count(*);

delete and truncate

truncate是舍弃意思，truncate <table_name>; 但是它只能删除表中的全部数据

delete的处理速度相比truncate 要长的多。

update 多值更新

update product set purchase_price=0,regist_data=‘2020-01-19‘ where purchase_price is null or regist_data is null;

事务处理何时开始

两种情况

每天SQL语句就是一个事务，自动提交模式
直到用户执行commit 或rollback 为止算一个事务

使用oracle时，数据库连接建立后，第一条SQL语句执行时，事务已经悄悄开始了。

复杂查询

视图和表

视图实际保存的是select语句，从视图表读取数据时，视图会在内部执行该select语句并创建出一张临时表。

视图的优点：一是节省存储数据的空间；二是将频繁使用的select语句保存成视图，三是复杂查询时，利用视图提高效率。

create view view_name (列名1，...) AS select 子句

视图的限制

定义视图时不能使用order 子句，即定义视图的select不能有order by子句。

这是因为视图和表一样，数据行都是没有顺序的，但有些DBMS是允许的，这个不是统一的语法。

对视图更新的限制，使用insert,update,delete语句时的，标准SQL定义了如果定义视图时的select子句满足，某些条件，视图就可以更新。

代表性情况：

select子句没有使用distinct

from子句中只有一张表

select子句没有使用group by子句

没有使用having 子句

多重视图，即在视图的基础上再建立视图，这种情况会降低数据库的性能。

子查询

Oracle的from子句中不能使用as，这个属于特定的SQL。为临时查询起别名不需要添加AS关键词。

标量子查询

标量子查询就是返回单一值的子查询。返回一行一列的结果

例如错误写法

select product_id,product_name ,purchase_price from product where purchase_price > avg(purchase_price); 聚合函数不能出现在where中出现。

可以使用标量子查询代替聚合函数

select product_id,product_name ,purchase_price from product where purchase_price > (select avg(purchase_price) from product); 在where中使用标量子查询。

在where子句中使用标量子查询，由于返回的是一行一列数值，可以使用>,<,=等判断运算符，如果不是标量的子查询，返回的是多列，那么不能使用>,<号，可以使用in, not in或者>any,>all等。

使用子查询的SQL语句，会从子查询开始执行

能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是
ORDER BY 子句，几乎所有的地方都可以使用。

例：

select product_id,product_name,purchase_price,(select avg(purchase_price) from product ) as avg_pirce from product;

select product_type,avg(purchase_price) from product group by product_type having avg(purchase_price)>(select avg(purchase_price) from product);

关联子查询

对分组后的数据进行组合平均值和单值的比较。

select product_id,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 p1.product_type=p2.product_type

这个查询是选取高于小组平均单价的商品。

关联子查询也能进行集合的切分

组合条件一定要写在子查询中，因为这涉及到了关联名称的作用域，在子查询中其他作用域才有效。

比如上述的p2作用域在子查询中，不能在外查询的where中出现p2，否则会报错。

函数

算术函数

ABS(#) MOD(#,#) ROUND(#,#)

字符串函数

拼接函数 str1||str2 , 但是在SQL Server 和 MySQL 不能使用，前者使用+代替，后者使用CONCA(#,#,...)代替

字符串长度函数 LENGTH(#) , SQL Server 使用LEN(#)

LOWER(#) UPPER(#)

REPLACE(#,#,#)

SUBSTRING(# FROM 1 FOR 3) 是标准的SQL,但是只要PostSQL 和 MYSQL支持

日期函数

CURRENT_DATE 只要PostgreSQL和MySQL支持

CURRENT_TIME

CURRENT_TIMESTAMP

EXTRACT(日期元素 from 日期) 截取日期元素

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;

转换函数

CAST( 转换前的值 AS 转换后的数据类型 )

select cast(‘2020-01-21‘ as date) as tommorow;

COALESCE——将NULL转换为其他值

谓词

BETWEEN

IS NULL,IS NOT NULL

EXISTS

EXISTS谓词的作用就是“判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST（存在）谓词的主语是“记录”。

EXISTS谓词常常和关联子查询一起搭配使用，通常指定关联子查询作为EXISTS的参数

select product_name,product_price from product as p where exists (select select * from shopproduct as sp where sp.shod_id=‘000c‘ and sp.product_id=p.product_id);

选出在大阪店在售商品的销售单价。

CASE表达式

简单CASE表达式

搜索CASE表达式

CASE表达式是在区分情况时使用的，这种情况的区分在编程中称为条件分支。

CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END

WHEN 子句中的“< 求值表达式 >”就是类似“列 = 值”这样，返回值为真值（TRUE/FALSE/UNKNOWN）的表达式。我们也可以将其看作使用 =、!= 或者 LIKE、BETWEEN 等谓词编写出来的表达式。

CASE 表达式会从对最初的 WHEN 子句中的“< 求值表达式 >”进行求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果为真（TRUE），那么就返回 THEN 子句中的表达式，CASE 表达式的执行到此为止。如果结果不为真，那么就跳转到下一条 WHEN 子句的求值之中。如果直到最后的 WHEN 子句为止返回结果都不为真，那么就会返回 ELSE中的表达式，执行终止。

无论case表达式多么复杂，then返回得都是简单值，如0，1，‘奥迪’ 等。

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_type from product;

case表达式，可以出现在任何地方，比如聚合函数sum()里面

case表达式实现行列转换

select 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_kitchen, sum(case when product_type=‘办公用品‘ then sale_price else 0 end) as sum_price_office from product;

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 FROM Product;

-- 使用简单CASE表达式的情况
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 FROM Product;

虽然看上去简化了书写，但是想要在 WHEN 子句中指定不同列时，简单 CASE 表达式就无能为力了。

集合运算

表的加法 UNION | UNION ALL

选取表的公共部分，交集 INTERSECT

表的减法 EXCEPT

联结 JOIN

联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。
UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。

联结大体上分为内联结和外联结两种。

INNER JOIN内联结

联结的要点在于 ON子句

from 会联结两张表合并成为一张表

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;

FROM ShopProduct AS SP INNER JOIN Product AS P

联结的条件 ON SP.product_id = P.product_id

INNER JOIN

[LEFT|RIGHT] OUTER JOIN

内联结和外联结的区别：

内联结只能选取出同时存在于两张表中的数据；相反，对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。

外联结还有一点非常重要，那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是 LEFT 和 RIGHT。

三张表以上的联结

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity FROM ShopProduct AS SP INNER JOIN Product AS P

ON SP.product_id = P.product_id

INNER JOIN InventoryProduct AS IP

ON SP.product_id = IP.product_id

WHERE IP.inventory_id = ‘P001‘;

交叉联结CROSS JOIN

交叉联结在业务中基本不会使用，是所以联结运算的基础。

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name FROM ShopProduct AS SP CROSS JOIN Product AS P

相当于行之间的笛卡尔积

窗口函数

窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作

OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。

语法：

<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

? 窗口函数大体可以分为以下两种。

① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

PARTITION BY 能够设定排序的对象范围

ORDER BY 能够指定按照哪一列、何种顺序进行排序

根据不同的商品种类，按照销售单价从低到高的顺序创建排序表

select product_name,product_type,sale_price,rank() over (partition by product_type order by sale_price) as ranking from product;

product_name | product_type | sale_price | ranking
--------------+--------------+------------+---------
圆珠笔 | 办公用品 | 100 | 1
打孔机 | 办公用品 | 160 | 2
叉子 | 厨房用具 | 100 | 1
擦菜板 | 厨房用具 | 395 | 2
高压锅 | 厨房用具 | 1400 | 3
菜刀 | 厨房用具 | 2500 | 4
T衬衫 | 衣服 | 250 | 1
运动T血 | 衣服 | 1000 | 2

窗口函数兼具之前我们学过的 GROUP BY 子句的分组功能以及
ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具备
GROUP BY 子句的汇总功能。因此，使用 RANK 函数并不会减少原表中
记录的行数，结果中仍然包含 8 行数据。

专用窗口函数

RANK 函数计算排序时，如果存在相同位次的记录，则会跳过之后的位次，有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位

DENSE_RANK 同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位

ROW_NUMBER 赋予唯一的连续位次。有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位

目前为止我们学过的函数大部分都没有使用位置的限制，最多也就是在 WHERE 子句中使用聚合函数时会有些注意事项。但是，使用窗口函数的位置却有非常大的限制。更确切地说，窗口函数只能书写在一个特定的位置。这个位置就是 SELECT 子句之中。反过来说，就是这类函数不能在WHERE 子句或者 GROUP BY 子句中使用。

GROUPING 运算符

只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的。如果想要同时得到，可以使用GROUPING运算符。

select ‘合计‘ as product_type , sum(sale_price) from product union all select product_type,sum(sale_price) from product group by product_type;

GROUPING 运算符包含以下 3 种

ROLLUP 同时得到合计和小计
GROUPING 函数，与ROLLUP搭配，让超级分组更容易识别
CUBE
GROUPING SETS

select product_type,sum(sale_price) from product group by ROLLUP(product_type);

从语法上来说，就是将 GROUP BY 子句中的聚合键清单像 ROLLUP（< 列 1>,< 列 2>,...）这样使用,在本例中就是一次计算出了如下两种组合的汇总结果。① GROUP BY ()② GROUP BY (product_type)

数据类型

约定

主键无需指定自增

外键也不在大型开发中很少用到，谨慎使用外键

对于Date 日期，统一使用 ‘2009-09-28‘ 年月日的方式，省去时间。

SQL进阶教程

用case表达式进行条件分支统计情况

关键是select子句和group by子句一起搭配使用，而且case分支情况要相同。

case统计相同的情况

select case pref_name when ‘德岛‘ then ‘四国‘ when ‘香川‘ then ‘四国‘ when ‘爱媛‘ then ‘四国‘ when ‘丽水‘ then ‘四国‘ when ‘福冈‘ then ‘四洲‘ when ‘东京‘ then ‘四洲‘ else ‘其他‘ end as distin,sum(population) from poptbl group by case pref_name when ‘德岛‘ then ‘四国‘ when ‘香川‘ then ‘四国‘ when ‘爱媛‘ then ‘四国‘ when ‘丽水‘ then ‘四国‘ when ‘福冈‘ then ‘四洲‘ when ‘东京‘ then ‘四洲‘ else ‘其他‘ end;

case注意的要点不要忘记写else 和 end

sum(case ) 搭配使用统计不同的情况，比如统计不同地区的男女人数

用case表达式将行结构，转换为列结构。

select pref_name, sum(case when sex=1 then population else 0 end) as man,

sum(case when sex=2 then population else 0 end) as women from poptbl

group by pref_name;

高手使用select 子句作为条件分支，where子句过滤行

case表达式then也能嵌套case表达式，比如一个检查约束，某公司规定了女性员工的工资不能超过20000元。

constraint check_salary check (case when sex=2 then case when salary<=20000 then 1 else 0 end else 1 end =1)

这个check先是判断员工性别，如果是男性直接返回1，如果是女性，那么用一个嵌套case 判断工资，如果不超过20000返回1，否则返回0，返回的最终结果与=1 进行判断。

在update里面使用case表达式，需要是根据员工的薪资水平决定降薪。

update salaries set salary= case when salary>20000 then salary*1.2 when salary<20000 AND salary>10000 then salary * 0.9 else salary end ;

这个SQL里面一定要注意else salary 这种情况，代表不满足条件，员工工资不变，如果不这样写，直接返回0，那么员工的工资直接归零。

在case 表达式里面用谓词 IN BETWEEN LIKE EXISTS

表的匹配

/* 表的匹配：使用IN谓词 */
SELECT CM.course_name,
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses 
                      WHERE month = 200706) THEN ‘○‘
            ELSE ‘×‘ END AS "6月",
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200707) THEN ‘○‘
            ELSE ‘×‘ END AS "7月",
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200708) THEN ‘○‘
            ELSE ‘×‘ END  AS "8月"
  FROM CourseMaster CM;
  
  /* 表的匹配：使用EXISTS谓词 */
SELECT CM.course_name,
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200706
                        AND CM.course_id = OC.course_id) THEN ‘○‘
            ELSE ‘×‘ END AS "6月",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200707
                        AND CM.course_id = OC.course_id) THEN ‘○‘
            ELSE ‘×‘ END AS "7月",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200708
                        AND CM.course_id = OC.course_id) THEN ‘○‘
            ELSE ‘×‘ END  AS "8月"
  FROM CourseMaster CM;

无论使用 IN 还是 EXISTS，得到的结果是一样的，但从性能方面来说，EXISTS 更好。

更高级的用法，在case里面用聚合函数

注意不是在聚合函数里面使用case

/* 在CASE表达式中使用聚合函数 */
SELECT std_id,
       CASE WHEN COUNT(*) = 1 /* 只加入了一个社团的学生 */
            THEN MAX(club_id)
            ELSE MAX(CASE WHEN main_club_flg = ‘Y‘
                          THEN club_id
                          ELSE NULL END)
        END AS main_club
  FROM StudentClub
 GROUP BY std_id;

SQL开发指南

码农公寓

标准SQL和特定SQL

复杂查询

数据类型

约定

SQL进阶教程

相关文章