用sql取a与b的交集_【庖丁解牛SQL（二）】SQL核心语法速查

最新推荐文章于 2023-11-21 17:25:48 发布

weixin_39676021

最新推荐文章于 2023-11-21 17:25:48 发布

阅读量171

点赞数

文章标签：用sql取a与b的交集

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

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

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

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

第一章表的建立

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

Create database shop;


Create table product(
product_id char(4) not null,
Product_name varchar(100) not nll,
Primary key(product_id)
);

1. 数据类型

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

2.表的删除

Drop table product;

2.1添加列

Alter table product add column product_name varchar(100);

2.2删除列

Alter table product drop column product_name;

3.向表中插入数据

Insert into product values(‘001’,’牛仔裤’,’衣服’，‘2000’)；
Insert into product values(‘002’,’帽子’,’衣服’，‘200’)；

练习题答案

Create table address_book
(
regist_no integer not null,
Name varchar(128) not null,
Address varchar(256) not null,
Tel_no char(10),
Mail_address char(20),
Primary key(regist_no)
);


Alter table address book add postal_code char(8) not null;

第二章查询基础

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

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

2.查询出所有的列

Select * from<表名>

3.为列设置别名

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

4.删除重复行

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

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

SELECT product_name, product_type FROM Product
WHERE product_type = '衣服';

6.注释的书写方式

--只有一行
/* */可跨越多行

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

Select product_name,sale_price*2 as sale_pricex2
from product;

8.选出售价为500的产品

SELECT product_name, product_type FROM Product
WHERE sale_price = 500;

9.逻辑运算符not and or

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

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
       ,regist_date from product
WHERE regist_date>'2009-09-20';

Select product_name
       ,sale_price
       ,purchase_price from product
Where sale_price-purchase_price>500;


Select product_name
       ,product_type
       ,sale_price*0.9 as profile from product
Where sale_price*0.9-purchase_price>100;

第二章聚合与排序

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

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

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

Select count(purchase_price)
From product;


Select sum(sale_price) as total_sale_price
       ,sum(purchase_price) as total_purchase_price
From product;

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

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

Select count(distinct product_type)
From product;


Select sum(distinct sale_price) 
From product;

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

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

Select product_type
       ,count(*)
From product
Group by product_type;

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

Select purchase_price,count(*)
From product
Where product_type=’衣服’
Group by purchase_price;
--执行顺序-from-where-group by-select

5.为聚合指定条件

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

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

Select product_type
       ,count(*)
From product
Group by product_type
Having count(*)=2;
--先把数据分组 再选出包含两条数据的呢个组


SELECT product_type
       ,AVG(sale_price) FROM Product
GROUP BY product_type
HAVING AVG(sale_price) >= 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 Product
ORDER BY sale_price
         ,product_id;

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

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

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

练习题答案

Select product_type
       ,sum(sale_price)
       ,sum(purchase_price) 
from product
Group by product_type
Having sum(sale_price)>1.5*sum(purchase_price)
ORDER BY regist_date desc;

第四章数据更新

1.向表中插入一行数据

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');

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

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

2.表的删除

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

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

指定删除删除部分数据

DELETE FROM Product
WHERE sale_price >= 4000;

3.改变表中的数据

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

UPDATE Product

SET regist_date = '2009-10-10'

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

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

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

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

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

1.创建视图

Create view productsum(product_type,cnt_product)
As
Select product_type,count(*)
From product
Group by product_type;

使用视图

SELECT * from productsum;

2.视图的使用禁忌

视图不能使用order by语句

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

3.删除视图

DROP VIEW ProductSum;

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

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

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

Select product_type
       ,cnt_product
From(
select product_type,count(*) as cnt_product
From product
Group by product_type
) as productsum;

增加子查询的嵌套层数

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;

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

5.标量子查询

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

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

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

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

Select product_id
       ,product_name
       ,sale_price
From product
Where sale_price>(select avg(sale_price) From product);

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

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

Select product_type
       ,avg(sale_price)
From product
Group by product_type
Having avg(sale_price)>(select avg(sale_price)from product);

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

6.关联子查询

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

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表示限定‘商品种类’对平均单价进行比较

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

练习题答案

Create view viewpractice5_1
As 
Select product_name,sale_price,regist_date
From product


Select*from viewpractice5_1
Where sale_price>1000 
      and regist_date=’2009-09-20’;


Select product_id
       ,product_name
       ,product_type
       ,sale_price,avg(sale_price)as sale_price_all
From product;

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

函数类型

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

1.算术函数

1.1计算数值的绝对值

SELECT m
       ,ABS(m) AS abs_col
FROM SampleMath;

1.2计算除法(n ÷ p)的余数

SELECT n
       ,p
       ,MOD(n, p) AS mod_col
FROM SampleMath;

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

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

2.字符串函数

2.1拼接三个字符串(str1+str2+str3)

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

2.2计算字符串长度

SELECT str1
       ,LENGTH(str1) AS len_str
FROM SampleStr;

2.3大写转换为小写

SELECT str1
       ,LOWER(str1) AS low_str
FROM SampleStr;

2.4替换字符串的一部分

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

--用str3替换str1中的str2

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

SELECT str1
       ,SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
 FROM SampleStr;

2.6将小写转换为大写

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

2.7日期函数

SELECT CURRENT_TIME;

2.8转换函数

将NULL 转换为其他值

SELECT CAST('2009-12-14' AS DATE) AS date_col;


SELECT COALESCE(NULL, 1)
       ,COALESCE(NULL, 'test', NULL)
       ,COALESCE(NULL, NULL, '2009-11-01')
From Samledata;

col_1|col_2|col_3

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

1 | test | 2009-11-01

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

3.常用谓语

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

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

SELECT product_name
       ,sale_price
FROM Product
WHERE product_id IN (SELECT product_id
                     FROM ShopProduct
                     WHERE shop_id = '000C');

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(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

第七章集合运算

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

1.集合运算-增加行

使用UNION 对表进行加法

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

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

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

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

SELECT product_id
       ,product_name 
FROM Product
INTERSECT
SELECT product_id
       ,product_name
FROM Product2 
ORDER BY product_id;

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

SELECT product_id
       ,product_name 
FROM Product
EXCEPT
SELECT product_id
       ,product_name
FROM Product2 
ORDER BY product_id;

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

3. 联结运算-增加列

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;

--将表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_price
FROM ShopProduct AS SP 
INNER JOIN Product AS P ON SP.product_id = P.product_id
WHERE SP.shop_id = '000A';

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

ELECT SP.shop_id
      ,SP.shop_name
      ,SP.product_id
      ,P.product_name
      ,P.sale_price
FROM 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_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';

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

weixin_39676021

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
用sql取a与b的交集_【庖丁解牛SQL（二）】SQL核心语法速查

练sql有本书灰常的好，《SQL基础教程》，简单易懂，全是图片不过有些人不想看书，就想当个伸手党，《SQL基础教程》的所有重点整理到这儿了按我的经验来讲，这篇绝对值得点赞喜欢收藏三连。面试、工作用都给力不会的时候control + F，搜索关键词查询，比百度管事第一章表的建立先建立一个数据库再在数据库中建立不同的表Create database shop;Create table pro...
复制链接

扫一扫