SQL基础（五）SQL语句大全

练sql有本书灰常的好，《SQL基础教程》，简单易懂，全是图片

不过有些人不想看书，就想当个伸手党，《SQL基础教程》的所有重点整理到这儿了

按我的经验来讲，这篇绝对值得点赞喜欢收藏三连。面试、工作用都给力

不会的时候control + F，搜索关键词查询，比百度管事

---

第一章 表的建立

先建立一个数据库 再在数据库中建立不同的表

Create database shop;Create table product not null,Product_name varchar not nll,Primary key);

1. 数据类型

• Integer-整数型数据
• Char（）-字符型数据 指定长度 长度不够制定值时通过半角符号补全
• Varchar（）-字符型数据 长度未达最大值时不补全
• Date-时间格式数据

2.表的删除

Drop table product;

2.1添加列

Alter table product add column product_name varchar;

2.2删除列

Alter table product drop column product_name;

3.向表中插入数据

Insert into product values；Insert into product values；

练习题答案

Create table address_book not null,Address varchar not null,Tel_no char,Mail_address char,Primary key);Alter table address book add postal_code char not null;

第二章 查询基础

1.使用select语句来筛选出某个表中想要的列

Select <列名>，<列名> from <表名>

2.查询出所有的列

Select * from<表名>

3.为列设置别名

Select <列名> as <别名>，<列名>as<别名> from <表名>

4.删除重复行

SELECT DISTINCT product_type, sale_pricefrom product; --把product_type里相同的sale_price都删除了

5.用where来指定查询数据的条件

SELECT product_name, product_type FROM ProductWHERE product_type = '衣服';

6.注释的书写方式

--只有一行可跨越多行

7.将所有的售价都提升一倍

Select product_name,sale_price*2 as sale_pricex2from product;

8.选出售价为500的产品

SELECT product_name, product_type FROM ProductWHERE sale_price = 500;

9.逻辑运算符not and or

And-取交集or-取并集 通过（）来嵌套逻辑规则

SELECT product_name, product_type, regist_date FROM ProductWHERE product_type = '办公用品'AND ;

练习题答案

Select product_name,regist_date from productWHERE regist_date>'2009-09-20';Select product_name,sale_price,purchase_price from productWhere sale_price-purchase_price>500;Select product_name,product_type,sale_price*0.9 as profile from productWhere sale_price*0.9-purchase_price>100;

第二章 聚合与排序

聚合函数：对数据进行某种操作或计算时使用的函数

Count（计算表中的特定记录的行数）-sum-avg-max-min

1.计算null之外的数据的行数

Select countFrom product;Select sum as total_sale_price,sum as total_purchase_priceFrom product;

2.使用聚合函数删除重复值后计算

计算去除重复数据行后的数据行数

Select countFrom product;Select sum From product;

3.对表进行分组——将表的行按不同类型归类

按照商品种类统计数据行数

Select product_type,countFrom productGroup by product_type;

4.同时使用where和group by子句

Select purchase_price,countFrom productWhere product_type=’衣服’Group by purchase_price;--执行顺序-from-where-group by-select

5.为聚合指定条件

注意⚠️where只能指定记录（行）的条件，不能用来指定组的条件（对某些行求和 ）

从按照商品种类进行分组后的结果中，取出“包含的数据行数为2 行”的组

Select product_type,countFrom productGroup by product_typeHaving count=2;--先把数据分组 再选出包含两条数据的呢个组SELECT product_type,AVG FROM ProductGROUP BY product_typeHAVING AVG >= 2500;--先把数据group以后 从中选出均价大于2500的group

注意⚠️having函数只能包含-常数 聚合函数 聚合键

6.对查询结果进行排序

子句书写顺序-select-from-where-group by-having-order by

• Desc降序排列asc升序排列

指定多个排序键-先按第一个要求排 若其中有重复项再按第二个规则排

SELECT product_id,product_name,sale_price,purchase_price FROM ProductORDER BY sale_price ,product_id;

• Order by子句中可以使用聚合函数

SELECT product_type,COUNT FROM ProductGROUP BY product_type ORDER BY COUNT;

--显示product_type 和所有行数两列，根据product_type分组 并按组内数量排序

练习题答案

Select product_type,sum,sum from productGroup by product_typeHaving sum>1.5*sumORDER BY regist_date desc;

第四章 数据更新

1.向表中插入一行数据

INSERT INTO ProductIns VALUES ;

插入默认值 没有默认值且无数值时为null

CREATE TABLE ProductIns NOT NULL,sale_price INTEGER DEFAULT 0, -- 销售单价的默认值设定为0;PRIMARY KEY );

2.表的删除

DROP TABLE 语句可以将表完全删除

DELETE 语句会留下表，而删除表中的全部数据- DELETE FROM Product

指定删除 删除部分数据

DELETE FROM ProductWHERE sale_price >= 4000;

3.改变表中的数据

将登记日期全部更新为“2009-10-10”

UPDATE Product

SET regist_date = '2009-10-10'

将商品种类为厨房用具的记录的销售单价更新为原来的10 倍

UPDATE ProductSET sale_price = sale_price * 10WHERE product_type = '厨房用具';

第五章 复杂查询 视图 子查询 关联子查询

--视图和表的区别：表将所有数据保存在计算机存储系统中-视图仅保存本次操作时使用的SQL语句 并不保存数据 而下次再调取的时候只需消耗一点cpu执行语句就能生成一张新的临时表

--视图的优点：极大的节省计算机容量 表中的数据更新以后 视图也会自动更新

1.创建视图

Create view productsumAsSelect product_type,countFrom productGroup by product_type;

使用视图

SELECT * from productsum;

2.视图的使用禁忌

视图不能使用order by语句

视图不能插入或更新数据-只能在表中修改数据

3.删除视图

DROP VIEW ProductSum;

4.子查询-一张一次性视图

将用来定义视图的select语句直接作用于from子句中

--从哪里查询？-从一个临时创建的视图里

Select product_type,cnt_productFrom as cnt_productFrom productGroup by product_type) as productsum;

增加子查询的嵌套层数

Select product_type,cnt_productFrom as cnt_product From product Group by product_type ) as productsum Where cnt_product=4)as productsum2;

--创建两个临时视图进行操作

5.标量子查询

--必须且只能返回1行1列的结果 返回表中某一行的某一列的值

在where中使用标量子查询-该子查询结果必须是单一的

查出销售单价高于平均销售单价的商品

--计算出每组的平均值 找出每组内售价高于平均值的商品

Select product_id,product_name,sale_priceFrom productWhere sale_price> From product);

在having子句中使用标量子查询

选出按照商品种类计算出的平均销售单价高于全部商品平均销售单价的商品种类

Select product_type,avgFrom productGroup by product_typeHaving avg>from product);

--执行顺序：将产品归类 归类好了后将产品类型和对应的平均售价输出成行-寻找：归类后的平均售价大于归类前平均售价的商品所有信息 并根据上层约束条件输出其种类信息及平均售价信息

6.关联子查询

选出每类商品中高于该类商品平均售价的商品

Select product_type,product_name,sale_priceFrom product as p1Where sale_price> from product as p2 Where p1.product_type=p2.product_type Group by product_type);

--where表示限定‘商品种类’对平均单价进行比较

注意⚠️结合条件一定写在子查询内

练习题答案

Create view viewpractice5_1As Select product_name,sale_price,regist_dateFrom productSelect*from viewpractice5_1Where sale_price>1000 and regist_date=’2009-09-20’;Select product_id,product_name,product_type,sale_price,avgas sale_price_allFrom product;

第六章 函数、谓语、case表达式

函数类型

• 算术函数-（用来进行数值计算的函数）
• 字符串函数-（用来进行字符串操作的函数）
• 日期函数-（用来进行日期操作的函数）
• 转换函数-（用来转换数据类型和值的函数）
• 聚合函数-（用来进行数据聚合的函数）

1.算术函数

1.1计算数值的绝对值

SELECT m,ABS AS abs_colFROM SampleMath;

1.2计算除法的余数

SELECT n,p,MOD AS mod_colFROM SampleMath;

1.3对m 列的数值进行n 列位数的四舍五入处理 对m数据四舍五入n位

SELECT m,n,ROUND AS round_colFROM SampleMath;

2.字符串函数

2.1拼接三个字符串

SELECT str1,str2,str3,str1 | | str2 | | str3FROM SampleStr WHERE str1 = '山田'

2.2计算字符串长度

SELECT str1,LENGTH AS len_strFROM SampleStr;

2.3大写转换为小写

SELECT str1,LOWER AS low_strFROM SampleStr;

2.4替换字符串的一部分

SELECT str1,str2,str3,REPLACE AS rep_strFROM SampleStrl;

--用str3替换str1中的str2

2.5截取出字符串中第3 位和第4 位的字符

SELECT str1,SUBSTRING AS sub_str FROM SampleStr;

2.6将小写转换为大写

SELECT str1,UPPER AS up_strFROM SampleStrWHERE str1 IN ;

2.7日期函数

SELECT CURRENT_TIME;

2.8转换函数

将NULL 转换为其他值

SELECT CAST AS date_col;SELECT COALESCE,COALESCE,COALESCEFrom Samledata;

col_1|col_2|col_3

-------+-------+-----------

1 | test | 2009-11-01

依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值

3.常用谓语

• Like - 字符串的部分一致查询
• Between - 范围查询
• Is null, is not null - 判断是否为null
• In - 使用子查询作为IN 的参数

-- 取得“在大阪店销售的商品的销售单价”

SELECT product_name,sale_priceFROM ProductWHERE product_id IN ;

4.Case表达式

4.1 通过CASE 表达式将A ~ C 的字符串加入到商品种类当中

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

4.2 使用CASE 表达式进行行列转换

-- 对按照商品种类计算出的销售单价合计值进行行列转换

SELECT SUM AS sum_price_clothes, SUM AS sum_price_kitchen, SUM AS sum_price_officeFROM Product

第七章 集合运算

--使用2张以上的表的SQL语句，通过以行的方式为单位的集合运算符和以列为单位的联结，可讲分散在多张表中的数据

1.集合运算-增加行

使用UNION 对表进行加法

SELECT product_id,product_name FROM ProductUNIONSELECT product_id,product_nameFROM Product2;

集合运算相当于并集，同时还能够去除重复的记录

注意⚠️：作为运算对象的记录列数必须相同-列的类型必须一致-可以使用任意select语句，但order by只能使用一次

2.1使用INTERSECT 选取出表中公共部分

SELECT product_id,product_name FROM ProductINTERSECTSELECT product_id,product_nameFROM Product2 ORDER BY product_id;

2.2使用EXCEPT 对记录进行减法运算

SELECT product_id,product_name FROM ProductEXCEPTSELECT product_id,product_nameFROM Product2 ORDER BY product_id;

从第一个表里减去两个表重复的内容

3. 联结运算-增加列

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_priceFROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id;

--将表ShopProduct命名为sp并取出SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price列，将表Product命名为p，并将两表的列按shop_id为规则合并

3.1内联结和WHERE 子句结合使用

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_priceFROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_idWHERE SP.shop_id = '000A';

3.2外联结-两个表中所有的行都进行联结（内联结只显示两表共有的行）同时需要确定主表

ELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_priceFROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P ON SP.product_id = P.product_id

3.3对3 张表进行内联结

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price,IP.inventory_quantityFROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_idINNER JOIN InventoryProduct AS IP ON SP.product_id = IP.product_idWHERE IP.inventory_id = 'P001';

这么香，该点赞的点赞 该收藏的收藏