SQL基础教程(适合收藏、新手随时查阅)
（统计了所有知识点，由于篇幅原因，只写了PostgreSql写法，基本都是标准SQL，学会标准 SQL就可以在各种RDBMS中书写 SQL语句了，五星畅销书 SQL基础教程 ）
1.数据库和SQL
1.1表的创建
create table Shohin(shohin_id CHAR(4) NOT NULL, shohin_mei VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32) NOT NULL, hanbai_tanka INTEGER, shiire_tanka INTEGER, torokubi DATE, PRIMARY KEY (shohin_id));
1.2往已创建好的表格添加列和删除列
ALTER TABLE Shohin ADD COLUMN shohin_mei_kana VARCHAR(100);
ALTER TABLE Shohin DROP COLUMN shohin_mei_kana;
1.3向表中插入数据
BEGIN TRANSACTION;
INSERT INTO Shohin VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-9-20');
INSERT INTO Shohin VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-9-11');
INSERT INTO Shohin VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Shohin VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-9-20');
INSERT INTO Shohin VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-1-15');
INSERT INTO Shohin VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Shohin VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Shohin VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;
1.4变更表名
ALTER TABLE Shohin RENAME TO Sohin;
2.查询基础
2.1列的查询
SELECT shohin_id, shohin_mei, shiire_tanka FROM Shohin;
SELECT * FROM Shohin;
2.2使用别名
SELECT shohin_id AS id, shohin_mei AS namae, shiire_tanka AS tanka FROM Shohin;
SELECT shohin_id AS "商品编号", shohin_mei AS "商品名称", shiire_tanka AS "进货单价" FROM Shohin;
2.3书写列名及常数（就是select可以直接显示 string、num、time类型）
SELECT '商品' AS mojiretsu, 38 AS kazu, '2009-02-24' AS hizuke, shohin_id, shohin_mei FROM Shohin;
2.4结果中删除重复行
SELECT DISTINCT shohin_bunrui FROM Shohin;
2.5按条件查询
SELECT shohin_mei, shohin_bunrui FROM Shohin WHERE shohin_bunrui = '衣服';
2.6注释
-- 注释一行
/* 注释多行 */
算数运算符和比较运算符
2.7 取2倍 （运算已行为单位执行 包含NULL的计算都是NULL）
SELECT shohin_mei, hanbai_tanka, hanbai_tanka * 2 AS "hanbai_tanka_×2" FROM Shohin;
2.8比较运算符
SELECT shohin_mei, shohin_bunrui FROM Shohin WHERE hanbai_tanka = 500;
SELECT shohin_mei, shohin_bunrui FROM Shohin WHERE hanbai_tanka <> 500;
SELECT shohin_mei, shohin_bunrui, torokubi FROM Shohin WHERE torokubi < '2009-09-27';
SELECT shohin_mei, hanbai_tanka, shiire_tanka FROM Shohin WHERE hanbai_tanka - shiire_tanka >= 500;
2.9对字符串使用不等号
先创建带字符的表
CREATE TABLE Chars (chr CHAR(3) NOT NULL, PRIMARY KEY (chr));
插入数据
BEGIN TRANSACTION;
INSERT INTO Chars VALUES('1');
INSERT INTO Chars VALUES('2');
INSERT INTO Chars VALUES('3');
INSERT INTO Chars VALUES('10');
INSERT INTO Chars VALUES('11');
INSERT INTO Chars VALUES('222');
COMMIT;
字符串比较只比较第一个字符 如下查询结果因该是 ‘3’ ‘222’
所有的数据按照从小到大排列应该是’1’<‘10’<‘11’<‘2’<‘222’<‘3’
SELECT chr FROM Chars WHERE chr > '2';
NULL不能使用比较运算符 如下显示不出为NULL的行
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shiire_tanka <> 2800;
显示NULL记录和不为NULL记录的语法
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shiire_tanka IS NULL;
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shiire_tanka IS NOT NULL;
逻辑运算符
2.10使用NOT运算符否定该条件
SELECT shohin_mei, shohin_bunrui, hanbai_tanka FROM Shohin WHERE NOT hanbai_tanka >= 1000;
使用AND使两侧的查询条件都成立时才成立
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shohin_bunrui = '厨房用具' AND hanbai_tanka >= 3000;
使用OR使查询条件有一个成立时整个查询条件都成立
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shohin_bunrui = '厨房用具' OR hanbai_tanka >= 3000;
AND运算优先于OR运算符 所以已下两句SQL会执行出不同结果(增加括号)
SELECT shohin_mei, shohin_bunrui, torokubi FROM Shohin WHERE shohin_bunrui = '办公用品' AND torokubi = '2009-09-11' OR torokubi = '2009-09-20';
SELECT shohin_mei, shohin_bunrui, torokubi FROM Shohin WHERE shohin_bunrui = '办公用品' AND (torokubi = '2009-09-11' OR torokubi = '2009-09-20');
3.聚合查询
COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
3.1.计算表中数据的行数COUNT;
SELECT COUNT(*) FROM Shohin;
3.2计算NULL之外的数据的行数
SELECT COUNT (shiire_tanka) FROM Shohin;
3.3计算合计值
聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。
SELECT SUM(hanbai_tanka), SUM(shiire_tanka) FROM Shohin;
3.4计算平均值
除掉NULL之外的所有个数计算平均值
SELECT AVG(hanbai_tanka), AVG(shiire_tanka) FROM Shohin;
3.5计算最大值和最小值
SUM/AVG 函数只能对数值类型的列使用，MAX/MIN 函数原则上可以适用于任何数据类型的列
SELECT MAX(hanbai_tanka), MIN(shiire_tanka) FROM Shohin;
3.6在聚合函数的参数中使用DISTINCT，可以删除重复数据。
SELECT COUNT(DISTINCT shohin_bunrui) FROM Shohin;
SELECT SUM(hanbai_tanka), SUM(DISTINCT hanbai_tanka) FROM Shohin;
3.7使用GROUP BY子句
GROUP BY 子句中指定的列称为聚合键或者分组列
如以下举例将商品类型分成三组 在对每一组计算COUNT
子句的书写顺序（暂定）
- SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui;
3.8聚合键中包含NULL的情况
聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来
SELECT shiire_tanka, COUNT(*) FROM Shohin GROUP BY shiire_tanka;
3.9使用WHERE子句时GROUP BY的执行结果
执行顺序：FROM -> WHERE -> GROUP BY -> SELECT
SELECT shiire_tanka, COUNT(*) FROM Shohin WHERE shohin_bunrui='衣服' GROUP BY shiire_tanka;
GROUP BY 常见在错误
1.在SELECT子句中书写了多余的列
2.在GROUP BY子句中写了列的别名
4.GROUP BY子句的结果是随机的
5.只有SELECT子句和HAVING子句(以及ORDER BY子句)中能够使用聚合函数
3.10使用HAVING子句(HAVING子句要写在GROUP BY子句之后)
1.聚合分组后的结果中，取出行数为2行的组
2.取出销售单价的平均值大于等于2500日元的组
SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui HAVING COUNT(*) = 2;
SELECT shohin_bunrui, AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) >= 2500;
HAVING子句用来指定"组"的条件 WHERE子句用来指定"行"的条件
3.11 对查询结果进行排序
ORDER BY子句通常写在SELECT语句的末尾
SELECT * FROM Shohin ORDER BY hanbai_tanka;
3.12 指定升序或降序
SELECT * FROM Shohin ORDER BY hanbai_tanka ASC;
SELECT * FROM Shohin ORDER BY hanbai_tanka DESC;
3.13指定多个排序键
优先使用左侧键接着在参考右侧键
SELECT * FROM Shohin ORDER BY hanbai_tanka, shohin_id;
3.14 NULL的排序顺序
不同数据库会在开头或者末尾进行汇总(shiire_tanka 存在空)
SELECT * FROM Shohin ORDER BY shiire_tanka;
3.15在排序键中使用别名
SELECT子句执行在GROUP BY之后 ORDER BY子句之前
SELECT shohin_id AS id, shohin_mei, hanbai_tanka FROM Shohin ORDER BY id;
3.16 ORDER BY子句可以使用SELECT子句中未使用的列和聚合函数
SELECT shohin_mei, hanbai_tanka, shiire_tanka FROM Shohin ORDER BY shohin_id;
SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui ORDER BY COUNT(*);
4数据更新(INSERT、DELETE、UPDATE)
先创建一个表使用了默认值DEFAULT 0
CREATE TABLE ShohinIns(shohin_id CHAR(4) NOT NULL, shohin_mei VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32) NOT NULL, hanbai_tanka INTEGER DEFAULT 0, shiire_tanka INTEGER, torokubi DATE, PRIMARY KEY (shohin_id));
4.1插入一行数据
前面括号为列清单 后面为值清单 数量必须一致
可以省略列清单
插入NULL的时候该列不能设置为NOT NULL
INSERT INTO ShohinIns(shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO ShohinIns VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
4.2插入显示默认值与插入隐式默认值
INSERT INTO ShohinIns VALUES('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
INSERT INTO ShohinIns(shohin_id, shohin_mei, shohin_bunrui, shiire_tanka, torokubi) VALUES ('0007', '擦菜板', '厨房用具', 790, '2009-04-28');
4.3从其他表中复制数据
接下来实现将当前表中的值复制到新表
INSERT SELECT 语句 可以进行数据备份时使用
CREATE TABLE ShohinCopy(shohin_id CHAR(4) NOT NULL, shohin_mei VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32) NOT NULL, hanbai_tanka INTEGER, shiire_tanka INTEGER, torokubi DATE, PRIMARY KEY (shohin_id));--创建新表
INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi FROM Shohin;--复制数据
使用了其他表中数据合计值的INSERT SELECT 语句
CREATE TABLE ShohinBunrui(shohin_bunrui VARCHAR(32) NOT NULL, sum_hanbai_tanka INTEGER, sum_shiire_tanka INTEGER, PRIMARY KEY (shohin_bunrui));--创建表
INSERT INTO ShohinBunrui(shohin_bunrui, sum_hanbai_tanka, sum_shiire_tanka) SELECT shohin_bunrui, SUM(hanbai_tanka), SUM(shiire_tanka) FROM Shohin GROUP BY shohin_bunrui;--复制数据
4.4数据的删除DELETE
通过WHERE子句指定条件进行删除
DELETE FROM Shohin WHERE hanbai_tanka >= 4000;
清空表 只清空内容
DELETE FROM Shohin;
DELETE语句中不能使用GROUP BY、HAVING和ORDER BY 应为它们都在删除数据时起不到什么作用
4.5数据的更新UPDATE
将登记日期全部更新为’2009-10-10’
UPDATE Shohin SET torokubi = '2009-10-10';
将厨房用具销售单价更新为原来10倍
将圆珠笔的登记日期更新为NULL
UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 10 WHERE shohin_bunrui = '厨房用具';
UPDATE Shohin SET torokubi = NULL WHERE shohin_id = '0008';
多列更新的两种方法 第一种比较通用
UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 10, shiire_tanka = shiire_tanka/2 WHERE shohin_bunrui = '厨房用具';
UPDATE Shohin SET(hanbai_tanka, shiire_tanka) = (hanbai_tanka*10, shiire_tanka/2) WHERE shohin_bunrui = '厨房用具';
4.6事务
事务是需要在同一单元中执行一系列更新处理的集合
开始语句
BEGIN TRANSACTION;--开始
COMMIT;--提交
ROLLBACK;--回滚
使用事务进行回滚
使用UPDATE更新然后ROLLBACK回滚后我们发现最后T恤衫价格并没有更新为1
BEGIN TRANSACTION;
UPDATE Shohin SET hanbai_tanka = 1 WHERE shohin_mei = 'T恤衫';--设置价格为1元
ROLLBACK;
SQL Server、PostgreSQL、MySQL默认使用自动提交模式
ACID特性
所有DBMS的事务都遵循四种标准规格的约定
1.原子性 在事务中有两条UPDATE语句 DBMS不会只执行其中一条
2.一致性 设置了NOT NULL约束列违反的SQL处理会被取消
3.隔离性 某个事务向表中添加了记录 在没有提交之前 其他事务看不到新纪录
4.持久性 事务一旦结束 DBMS会保证该时点的数据状态得以保存的特性
5复杂查询(视图、子查询、关联子查询)
5.1创建视图
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>
表中存储的是实际数据，视图只是保存取出数据的SELECT语句，所以视图归根到底就是SELECT语句
使用视图的时候不用频繁编写带GROUP BY、COUNT的SELECT语句
CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui;--创建视图
SELECT shohin_bunrui, cnt_shohin FROM ShohinSum;--使用视图
5.2使用视图的查询(多重视图)
如下ShohinSum 已经是视图，在这个基础上在建立视图ShohinSumJim，多重视图会降低SQL的性能，尽量能够使用单一视图
CREATE VIEW ShohinSumJim(shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, cnt_shohin FROM ShohinSum WHERE shohin_bunrui = '办公用品';
SELECT * FROM ShohinSumJim;
视图的限制
①定义视图时不能使用ORDER BY子句
②通过聚合得到的视图无法进行更新
INSERT INTO ShohinSum VALUES('电器制品', 5);--该视图无法插入值
5.3删除视图
DROP VIEW ShohinSum;--删除视图(出现关联视图 则使用下面递归删除)
DROP VIEW ShohinSum CASCADE;--递归删除视图
5.4子查询
子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中
SELECT shohin_bunrui, cnt_shohin FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM Shohin GROUP BY shohin_bunrui) AS ShohinSum;
5.5.标量子查询
只能返回单一值的子查询
SELECT AVG(hanbai_tanka) FROM Shohin;--单一值
SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin);--标量子查询
子查询书写位置：能够使用常数或者列名的地方，SELECT、GROUP BY、HAVING、ORDER BY等子句都可以使用
SELECT shohin_bunrui, AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) > (SELECT AVG(hanbai_tanka) FROM Shohin);--HAVING子句使用标量子查询
5.6关联子查询(在细分的组内进行比较时，需要使用关联子查询)
比如查询出各个组高于该组平均值的商品,查询出来的打孔器是大于所有办公用品平均值，菜刀是大于所有厨房永平平均值
SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin AS S1 WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin AS S2 WHERE S1.shohin_bunrui = S2.shohin_bunrui GROUP BY shohin_bunrui);--查询出的产品在各自的组内都是大于当前组平均值的产品
6函数、谓词、CASE表达式
创建表格用来展示函数
CREATE TABLE SampleMath(m NUMERIC (10,3), n INTEGER, p INTEGER);
BEGIN TRANSACTION;
INSERT INTO SampleMath(m,n,p) VALUES(500, 0, NULL);
INSERT INTO SampleMath(m,n,p) VALUES(-180, 0, NULL);
INSERT INTO SampleMath(m,n,p) VALUES(NULL, NULL, NULL);
INSERT INTO SampleMath(m,n,p) VALUES(NULL, 7, 3);
INSERT INTO SampleMath(m,n,p) VALUES(NULL, 5, 2);
INSERT INTO SampleMath(m,n,p) VALUES(NULL, 4, NULL);
INSERT INTO SampleMath(m,n,p) VALUES(8, NULL, 3);
INSERT INTO SampleMath(m,n,p) VALUES(2.27, 1, NULL);
INSERT INTO SampleMath(m,n,p) VALUES(5.555, 2, NULL);
INSERT INTO SampleMath(m,n,p) VALUES(NULL, 1, NULL);
INSERT INTO SampleMath(m,n,p) VALUES(8.76, NULL, NULL);
COMMIT;
6.1数值不同的函数
实例截图只采用最后一条SQL
SELECT m, ABS(m) AS abs_col FROM SampleMath;--绝对值函数ABS
SELECT n, p, MOD(n,p) AS mod_col FROM SampleMath;--求余函数MOD
SELECT m, n, ROUND(m, n) AS round_col FROM SampleMath;--四舍五入函数ROUND
创建表格用来展示字符串函数
CREATE TABLE SampleStr(str1 VARCHAR(40), str2 VARCHAR(40), str3 VARCHAR(40));
BEGIN TRANSACTION;
INSERT INTO SampleStr(str1,str2,str3) VALUES('opx', 'rt', NULL);
INSERT INTO SampleStr(str1,str2,str3) VALUES('abc', 'def', NULL);
INSERT INTO SampleStr(str1,str2,str3) VALUES('山田', '太郎', '是我');
INSERT INTO SampleStr(str1,str2,str3) VALUES('aaa', NULL, NULL);
INSERT INTO SampleStr(str1,str2,str3) VALUES(NULL, 'xyz', NULL);
INSERT INTO SampleStr(str1,str2,str3) VALUES('@!#$%', NULL, NULL);
INSERT INTO SampleStr(str1,str2,str3) VALUES('ABC', NULL, NULL);
INSERT INTO SampleStr(str1,str2,str3) VALUES('aBC', NULL, NULL);
INSERT INTO SampleStr(str1,str2,str3) VALUES('abc太郎', 'abc', 'ABC');
INSERT INTO SampleStr(str1,str2,str3) VALUES('abcdefabc', 'abc', 'ABC');
INSERT INTO SampleStr(str1,str2,str3) VALUES('micmic', 'i', 'I');
COMMIT;
6.2字符串的不同函数
SQL Server 和MySQL使用 + 拼接字符串
SELECT str1, str2, str1 || str2 AS str_concat FROM SampleStr;--字符串拼接 ||
SELECT str1, str2, str3, str1 || str2 || str3 AS str_concat FROM SampleStr WHERE str1 = '山田';--拼接三个字符串
//长度函数，大小写转换，字符串替换，字符串截取函数
SELECT str1, LENGTH(str1) AS len_srt FROM SampleStr;--字符串长度函数LENGTH
SELECT str1, LOWER(str1) AS low_str FROM SampleStr WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');--小写转换函数LOWER
SELECT str1, UPPER(str1) AS low_str FROM SampleStr WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');--大写转换函数UPPER
SELECT str1, str2, str3, REPLACE(str1,str2,str3) AS rep_str FROM SampleStr;--字符串替换函数REPLACE str2->str3
SELECT str1,SUBSTR(str1, 3, 2) AS sub_str FROM SampleStr;--字符串截取函数SUBSTRING(对象字符串，截取的起始位置，截取的字符数)
//日期函数
SELECT CURRENT_DATE;--日期函数CURRENT_DATE
SELECT CURRENT_TIME;--当前时间函数CRUUENT_TIME
SELECT CURRENT_TIMESTAMP;--当前日期和时间函数CURRENT_TIMESTAMP
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;--截取日期元素函数EXTRACT(日期元素 FROM 日期)
//转换函数
SELECT CAST('0001' AS INTEGER) AS int_col;--类型转换函数CAST(转换前的值，AS，想要转换的数据类型);
SELECT CAST('2009-12-14' AS DATE) AS date_col;--字符串转日期类型
SELECT COALESCE(str2, 'NULL') FROM SampleStr;--将NULL转换为其它值
谓词
谓词就是函数中的一种，是需要满足返回值是真值条件的函数
● LIKE
● BETWEEN
● IS NULL、IS NOT NULL
● IN
● EXISTS
6.3LIKE谓词——字符串的部分一致查询
创建SampleLike表
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';
6.4BETWEEN谓词——范围查询
SELECT shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka BETWEEN 100 AND 1000;--包含100和1000
SELECT shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > 100 AND hanbai_tanka < 1000;--不包含只能使用 > 和 <
6.5 IS NULL、IS NOT NULL——判断是否为NULL
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shiire_tanka IS NULL;
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shiire_tanka IS NOT NULL;
6.6 IN谓词——OR的简便用法
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shiire_tanka = 320 OR shiire_tanka = 500 OR shiire_tanka = 5000;
SELECT shohin_mei, shiire_tanka FROM Shohin WHERE shiire_tanka IN(320,500,5000);
6.7使用子查询作为IN谓词的参数
创建表格ShohinShop
CREATE TABLE ShohinShop(shop_id CHAR(4) NOT NULL, shop_name VARCHAR(200) NOT NULL, shohin_id CHAR(4) NOT NULL, quantity INTEGER NOT NULL, PRIMARY KEY(shop_id, shohin_id));
BEGIN TRANSACTION;
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShohinShop(shop_id, shop_name, shohin_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;
读取出“大阪店（000C）在售商品（shohin_id）的销售单（hanbai_tanka）”
SELECT shohin_mei, hanbai_tanka FROM Shohin WHERE shohin_id IN (SELECT shohin_id FROM ShohinShop WHERE shop_id = '000C');
NOT IN和子查询
SELECT shohin_mei, hanbai_tanka FROM Shohin WHERE shohin_id NOT IN (SELECT shohin_id FROM ShohinShop WHERE shop_id = '000A');
6.8EXIST谓词 //等待完成
6.9 CASE表达式
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
. . .
ELSE <表达式>
END
扫一扫
2094
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
