sql必知必会知识点总结(长

程序员文章站 2022-11-22 17:07:22

sql学习1.SELECT检索单个列SELECT prod_name FROM Products;返回未排序数据返回所有行,没有过滤检索多个列,列名之间加上,就行,SELECT prod_id,prod_name FROM Products;检索所有列,SELECT * FROM Products;,可以看到自己不知道名字的列2. 排序检索数据排序数据: SELECT prod_name,prod_price FROM Products ORDER BY prod_price;...

sql学习

1.SELECT

检索单个列SELECT prod_name FROM Products;
- 返回未排序数据
- 返回所有行,没有过滤
检索多个列,列名之间加上,就行,SELECT prod_id,prod_name FROM Products;
检索所有列,SELECT * FROM Products;,可以看到自己不知道名字的列

2. 排序检索数据

排序数据: SELECT prod_name,prod_price FROM Products ORDER BY prod_price;
按多个列(列名)排序:SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name;
按照select清单中列的相对位置排序

SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3;
指定排序方向 (ASC 和 DESC),多个列写在各自列后面
- 单个列逆序(按照prod_price逆序):SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC;
- 多个列(prod_price降序,prod_name排序):SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC,prod_name;
  
  多个列降序排序,必须每个列都指定

3. where子句

> 指定搜索条件(过滤条件)

相等测试 SELECT prod_name,prod_price FROM Products WHERE prod_price=3.49;

where和order by字句的位置:order by 位于 where之后
select prod_id,prod_name,prod_price from Products
where prod_price=3.49
order by prod_name DESC;

where子句的操作符(不列举了)

操作符between and

select prod_name,prod_price from Products
where prod_price
between 5 and 10;

空值检查

select prod_name 
from Products 
where prod_price is null;

select vend_address,vend_city,vend_state 
from Vendors 
where vend_state is null 
order by vend_city desc;

4. 高级数据过滤

and

select prod_id,vend_id,prod_price,prod_name
from Products
where vend_id='DLL01' and prod_price <= 4;

select prod_name,vend_id,prod_price
from Products
where vend_id = 'DLL01' or vend_id = 'BRS01'
order by prod_price desc;

and 和 or同时使用的优先级,and > or

select prod_name,vend_id,prod_price
from Products
where vend_id = 'DLL01' or vend_id = 'BRS01' 
and prod_price >= 10;

条件当vend_id='BRS01'和prod_price>=10先结合,然后vend_id = 'DLL01'

select prod_name,vend_id,prod_price
from Products
where (vend_id = 'DLL01' or vend_id = 'BRS01')
and prod_price >= 10;

IN , 注意是where的子句,并且合法值在圆括号组成的清单中,逗号隔开

select prod_name,vend_id,prod_price 
from Products
where vend_id 
IN ('DLL01','BRS01')
order by prod_price;

select prod_id,vend_id,prod_price
from Products 
where prod_id 
in ('BR01','BR02','BR03');

使用IN的优点:

长的合法选项清单，
一般比or快
包含其他select子句

NOT

select *
from Products 
where not vend_id = 'DLL01'
and (prod_price <= 5 or prod_price >= 10);

select prod_name
from Products 
where not vend_id = 'DLL01'
order by prod_name;

等价

select prod_name 
from Products
where vend_id != 'DLL01'
order by prod_name;

5. 使用通配符过滤（用于文本段,where子句使用like操作符

%通配符: 任何字符出现任意次数 (正则中的*)

找到Fish开头的产品

select prod_id,prod_name 
from Products 
where prod_name like 'fish%'
;

任意位置包含bean bag

select prod_id,prod_name
from Products 
where prod_name like '%bean bag%'
;

_:只匹配单个字符 (正则中的?)

匹配2个字符

select prod_id,prod_name
from Products 
where prod_name like '__ inch teddy bear'
;

也可使用%

select prod_id,prod_name 
from Products 
where prod_name like '$ inch teddy bear'
;

[],在mariadb测试好像并不支持sql的[]通配符

改用正则表达式,

找到J M开头的联系人

select cust_contact 
from Customers 
wher cust_contact 
regexp '^[JM]'
order by cust_contact
;

6. 计算字段

拼接字段

sql中:在select语句中使用+或者||

mysql不支持,使用concat()函数拼接项表

mysql:
select concat(vend_name,'(',vend_country,')')
from Vendors
;

原生sql:
select vend_name + '(' + vend_country + ')'
from Vendors 
;

拼接字段后列的名字是一个值 (没有列名),给列赋予别名(alias)

select concat(vend_name,'(',vend_country,')')
as vend_title 
from Vendors 
;

注意as是在from之前的

执行算术计算

在订单物品表中算出每一个物品的总价,

查看单价和数量

select prod_id,quantity,item_price
from OrderItems 
where order_num = 20008
;

添加计算结果列

select 
prod_id,quantity,item_price,
quantity*item_price as all_price
from OrderItems 
where order_num = 20008
;

7.数据处理函数

文本处理函数

upper()转大写

select vend_name,upper(vend_name) as vend_name_upcase 
from Vendors 
order by vend_name;

soundex()发音类似

select cust_name,cust_contact 
from Customers 
where soundex(cust_contact) = soundex('Michael Green')
;

数学函数(举个例子

mysql中的round()取整

select order_num,order_item,
round(item_price) as item_round
from OrderItems 
;

8.汇总数据(针对数据集合而不是每条数据本身)

avg() 返回某列的平均值

count() 返回某列的行数

max() 返回某列的最大值

min() 返回某列的最小值

sum() 返回某列之和

avg()

返回所有产品的平均值

select avg(prod_price) as avg_price 
from Products
;

返回特定条件(某产品)的平均值

select avg(prod_price) as avg_price 
from Products 
where vend_id = 'DLL01'
;

count()对表中行的数目进行计算
1. count(*),无论行中包含的是什么,都计数
2. count(colnum),对特定列计算,忽略NULL
```
select count(*) as num_cust 
from Customers 
;
```
```
select count(cust_email) as num_cust 
from Customers 
;
```

max()

select max(prod_price) as max_price 
from Products 
;

min()

select min(prod_price) as min_price
from Products 
;

sum()

select sum(prod_price) as sum_price 
from Products 
;

select sum(quantity * item_price) as items_all
from OrderItems 
;

DISTINCT的使用(避免相同的值出现)

在avg中

select avg(distinct prod_price) as avg_price 
from Products 
where vend_id = 'DLL01'
;

count中(避免重复值计数)

select count(distinct prod_price) as count_price
from Products 
;

组合聚集函数

select count(*) as num_items,
min(prod_price) as price_min,
max(prod_price) as price_max,
avg(prod_price) as price_avg
from Products 
;

9.分组数据

创建分组 , 理解为每一列中的某些行的值是相同的,分组是把这些相同的值放在一起

产品表: 统计每个供应商提供了多少产品(产品分组)
```
select vend_id,count(vend_id) as num
from Products 
group by vend_id 
;
```
供应商表: 统计的每个供应商来自那些国家(国家分组)
```
select vend_country,count(vend_country) as num
from Vendors 
group by vend_country 
;
```
订单项目表: 统计每个订单分别由那些产品组成(由产品分组)
```
select prod_id,count(prod_id) as num
from OrderItems 
group by prod_id 
;
```

上面的数据,可以通过where过滤掉行,但是根据分组出来的数据来过滤呢?也就是以组为单位过滤

过滤分组

Having子句

select prod_id,count(prod_id) as num
from OrderItems 
group by prod_id 
having (count(prod_id) >= 2 and count(prod_id) <= 3)
;

在订单项目表中,统计产品相同的订单并且这些产品相同的订单数目>=2和<=3

select cust_id,count(*) as orders 
from Orders 
group by cust_id 
;

在订单表中,统计客户相同的订单(按客户分组)

使用Where在分组前过滤某些行,然后在分组过滤

select vend_id,count(*) as num 
from Products 
where prod_price >= 4 
group by vend_id 
having count(*) >= 2
;

在产品表中,选出卖出产品数量大于>=2,产品价格>=4的供应商

分组和排序,

保证数据正确排序的方法,不要仅依赖Group by排序数据

默认group by排序:

select order_num,count() as num
from OrderItems
group by order_num
having count() > 2
;


添加order by子句,指定排序字段

```sql
select order_num,count(*) as num
from OrderItems 
group by order_num 
having count(*) > 2
order by count(*),order_num 
;

10.使用子查询

列出物品RGAN01的所有客户

从订单项目表中根据物品名字找到订单号

根据订单号从订单表中找打客户id

分2部分做到
select order_num
from OrderItems 
where prod_id = 'RGAN01'
;

select cust_id 
from Orders 
where order_num in (20007,20008)
;

使用子查询组合起来

select cust_id 
from Orders 
where order_num in (
	select order_num 
	from OrderItems 
	where prod_id = 'RGAN01'
);

更进一步,列出所有客户信息

套了2个子查询

select cust_name,cust_address,cust_contact 
from Customers 
where cust_id in (
	select cust_id
	from Orders 
	where order_num in (
		select order_num
		from OrderItems 
		where prod_id = 'RGAN01'
	)
);

计算字段使用子查询,计算字段中给出外面表的字段作为条件

select cust_name,cust_state,(
	select count(*)
	from Orders 
	where Orders.cust_id = Customers.cust_id 
	)
as num
from Customers 
order by cust_name 
;

11. 联结表

等值联结(也称内部联结):

2个表有相同字段,查询的字段同时来自不同的表

查货物信息以及对应的供货商信息(通过相同vend_id字段)
```
select vend_name,prod_name,prod_price
from Vendors,Products 
where Vendors.vend_id = Products.vend_id
;
```
- from子句列出2个表,where子句来联结,并且要给出完全限定的列名Vendors.vend_id,如果仅是vend_id有二义性
  
  笛卡尔积:
- 没有联结条件的表关系返回结果为笛卡尔积
例如表A有3项,表B有4项,则笛卡尔积有12项,每一项是2个不同项连接一起
```
select vend_name,prod_name,prod_price
from Vendors,Products 
;
```
等值联结也称内部联结,也可用下面语法 inner A join B on 条件
```
select vend_name,prod_name,prod_price 
from Products inner join Vendors 
on Products.vend_id = Vendors.vend_id 
;
```

联结多个表

显示订单号为20007的订单物品

select prod_name,vend_name,prod_price,quantity 
from OrderItems,Products,Vendors 
where order_num = 20007
	and OrderItems.prod_id = Products.prod_id 
	and Products.vend_id = Vendors.vend_id 
;

将之前子查询转换成联结查询

子查询:

select cust_name,cust_contact 
from Customers 
where cust_id in (
	select cust_id 
	from Orders 
	where order_num in(
		select order_num
		from OrderItems 
		where prod_id = 'RGAN01'
	)
);

首先列处3个表以及2个字段,然后把子查询的结果,等价转化为等值条件

select cust_name,cust_contact 
from Customers,Orders,OrderItems
where prod_id = 'RGAN01'
	and OrderItems.order_num = Orders.order_num 
	and Customers.cust_id = Orders.cust_id 
;

12.高级联结

使用表别名缩短语句

select cust_name,cust_contact 
from Customers as C,Orders as O,OrderItems as OI
where prod_id = 'RGAN01'
	and OI.order_num = O.order_num
	and C.cust_id = O.cust_id 
;

2种联结

自联结

因为单条select 不能2次引用相同表

在Customers表根据员工名所在公司,在本表中查处此公司的所有员工

使用子查询

select cust_id,cust_name,cust_contact
from Customers 
where cust_name = (
	select cust_name 
	from Customers 
	where cust_contact = 'Jim Jones'
);

因为2次查询都是在相同的表,可以通过as重命名看作不同表,然后查询

select c1.cust_id,c1.cust_name,c1.cust_contact 
from Customers as c1,Customers as c2
where c2.cust_contact = 'Jim Jones'
	and c1.cust_name = c2.cust_name
;

在Products表中也试试

select c1.prod_id,c1.vend_id,c1.prod_name
from Products as c1,Products as c2
where c2.prod_id = 'BNBG01'
	and c2.vend_id = c1.vend_id 
;

外部联结

与内部联结的差距是,内部联结需要列的值相等,(即2个表相同字段的行的值不相同会被过滤)

而外部联结会选择其中一个表让所有行都出现,

left outer join 或者 right outer join,(随意改变2个表出现的顺序)
```
select Customers.cust_id,Orders.order_num
from Customers left outer join Orders
on Customers.cust_id = Orders.cust_id 
;
```
让Customers中的所有cust_id行都出现

带聚集函数的联结

统计有订单的客户的所有订单数

select Customers.cust_id,count(Orders.order_num) as num_ord 
from Customers inner join Orders 
on Customers.cust_id = Orders.cust_id 
group by Customers.cust_id 
;

因为是等值联结所以就并不是所有客户都出现了

统计所有客户的所有订单数

select Customers.cust_id,count(Orders.order_num) as num_ord 
from Customers left outer join Orders 
on Customers.cust_id = Orders.cust_id 
group by Customers.cust_id 
;

13.组合查询

Union 用来组合数条SQL查询,对于多条select语句,可以将结果组合成单个集合

使用union

每条select语句之间放上union就行

查询位于IN,IL,MI州或者是Fun4All单位的所有客户 1.使用where来做

select cust_name,cust_contact,cust_email,cust_state 
from Customers 
where cust_state in ('IN','IL','MI')
or cust_name = 'Fun4All'
;

使用union合并2个select查询出来的集合

select cust_name,cust_contact,cust_email,cust_state 
from Customers 
where cust_state in ('IN','IL','MI')
union 
select cust_name,cust_contact,cust_email,cust_state 
from Customers 
where cust_name = 'Fun4All'
;

union的规则

必须由2条或者以上的select语句组成,语句间用union分隔

union中的每个查询必须包含相同的列,表达式,或聚集函数

union从查询结果集中自动去除了重复的行,如果想要全部结果,使用union all

select cust_name,cust_contact,cust_email 
from Customers 
where cust_state in ('IN','IL','MI')
union all
select cust_name,cust_contact,cust_email 
from Customers 
where cust_name = 'Fun4All'
;

对组合查询结果排序

只能使用一条order by 子句,并且出现在最后一条select语句之后

select cust_name,cust_contact,cust_email 
from Customers 
where cust_state in ('IN','IL','MI')
union 
select cust_name,cust_contact,cust_email 
from Customers 
where cust_name = 'Fun4All'
order by cust_name,cust_contact 
;

14.插入数据

语法: insert into 表A values ()

插入检索出来的数据

如果有一个CustNew表和Customers字段相同,可以使用Insert into select语句

将CustNew表数据合并到Customers表中
```
insert into Customers 
select * from CustNew;
```
或者将CustNew中的某条记录插入表Customers中:
```
insert into Customers 
select * from CustNew 
where cust_id = '1000000008';
```

15.更新和删除数据

更新数据

基本格式: update 表 set 列=值 where 条件

例:更新客户1000000005的email

update Customers 
set cust_email = 'kim@thetoystore.com'
where cust_id = '1000000005'
;

删除数据

格式: delete from 表 where 条件, （如果没有where子句则所有的行都会被删除

删除cust_id = '1000000006’的客户
```
delete from Customers 
where cust_id = '1000000006';
```

16.创建表

书中的Products表

create table Products (
	prod_id char(10) not null,
	vend_id char(10) not null,
	prod_name char(254) not null,
	prod_price decimal(8,2) not null,
	prod_desc varchar(1000) null
);

decimal:除掉任何符号8位,小数点后最多2位

**默认null 和 not null **

create table Vendors (
	vend_id char(10) not null,
	vend_name char(50) not null,
	vend_address char(50),
	vend_city char(50),
	vend_state char(5),
	vend_zip char(10),
	vend_country char(50)
);

指定默认值

create table OrderItems (
	order_num integer not null,
	order_item integer not null,
	prod_id char(10) not null,
	quantity integer not null default 1,
	item_price decimal(8,2) not null
);

插入一行时,没有给出具体值则使用默认值

insert into OrderItems (order_num,order_item,prod_id,item_price)
values (150,200,'prod_id_1',1.99);

就能看到default的值

在表中添加新的列

添加列就要给出数据类型
```
alter table Vendors 
add vend_phone char(20)
;
```

删除列

alter table Vendors 
drop column vend_phone;

删除表

删除Vendors表
```
drop table Vendors;
```

17.使用视图

使用视图的好处:

简化复杂的sql操作,重用

使用表的组成部分而不是整个表

保护数据

可更改数据格式和表示

视图创建后,利用方式和表基本相同

视图的规则和限制:

创建视图

语法: create view 视图名 as

将之前的联结查询(产品客户表)包装成一个视图,简化sql查询

select cust_name,cust_contact
from Customers,Orders,OrderItems 
where Customers.cust_id = Orders.cust_id 
and OrderItems.order_num = Orders.order_num 
and prod_id = 'RGAN01'
;

将联结查询的cust_name,cust_contact,prod_id结果信息制作为视图,然后从视图中查询数据

create view ProductCustomers as 
select cust_name,cust_contact,prod_id
from Customers,Orders,OrderItems 
where (Customers.cust_id = Orders.cust_id)
and (OrderItems.order_num = Orders.order_num)
;

从view中查询订购RGAN01产品的客户信息

select * 
from ProductCustomers 
where prod_id = 'RGAN01'
;

删除视图
```
drop view ProductCustomers
;
```

用视图重新格式化检索出的数据

使用视图前每次select都需要在计算字段中格式化

select concat(vend_name,'(',vend_country,')')
as vend_title 
from Vendors 
order by vend_name 
;

将上面语句结果转为视图,则每次需要查询时不必格式化

create view VendorsLocations as 
select concat(vend_name,'(',vend_country,')')
as vend_title 
from Vendors 
;

select *
from VendorLocations
;

用视图过滤不想要的数据

过滤掉email为null的客户

create view CustomerEmailList as 
select cust_id,cust_name,cust_email
from Customers 
where cust_email is not null
;

-- 查询
select * 
from CustomerEmailList 
;

视图与计算字段

之前的检索某个订单中的物品和计算此物品的总价格:

select prod_id,quantity,item_price,quantity*item_price as expanded_price
from OrderItems 
where order_num = 20008
;

转为视图

查询某个订单的总价格,可以create一个所有订单的总价格view,然后select某个订单

create view OrderItemsExpanded as 
select order_num,
		prod_id,
		quantity,
		item_price,
		quantity*item_price as expanded_price
from OrderItems
;

-- 查询
select * 
from OrderItemsExpanded 
where order_num = 20008
;

创建view的主要目的是减少重复的sql语句,(方便),view=虚拟的表,包含的不是数据而是根据需要检索数的查询

本文地址：https://blog.csdn.net/qq_43580151/article/details/108985962

sql必知必会知识点总结(长

sql学习

1.SELECT

2. 排序检索数据

3. where子句

4. 高级数据过滤

5. 使用通配符过滤（用于文本段,where子句使用like操作符

6. 计算字段

7.数据处理函数

8.汇总数据(针对数据集合而不是每条数据本身)

DISTINCT的使用(避免相同的值出现)

组合聚集函数

9.分组数据

Having子句

10.使用子查询

11. 联结表

笛卡尔积:

12.高级联结

2种联结

13.组合查询

14.插入数据

15.更新和删除数据

16.创建表

17.使用视图

SQL必知必会笔记检索和排序数据

SQL必知必会笔记过滤数据

SQL必知必会笔记-分组数据

SQL必知必会笔记创建计算字段

SQL必知必会笔记使用通配符过滤数据

MySQl数据库必知必会sql语句(加强版)

《sql必知必会》学习笔记（四）视图

Java线程池必知必会知识点总结

《SQL必知必会》知识点汇总

SQL必知必会讲解

sql必知必会知识点总结(长

sql学习

1.SELECT

2. 排序检索数据

3. where子句

4. 高级数据过滤

5. 使用通配符过滤 （用于文本段,where子句使用like操作符

6. 计算字段

7.数据处理函数

8.汇总数据(针对数据集合而不是每条数据本身)

DISTINCT的使用(避免相同的值出现)

组合聚集函数

9.分组数据

Having子句

10.使用子查询

11. 联结表

笛卡尔积:

12.高级联结

2种联结

13.组合查询

14.插入数据

15.更新和删除数据

16.创建表

17.使用视图

SQL必知必会笔记检索和排序数据

SQL必知必会笔记过滤数据

SQL必知必会笔记-分组数据

SQL必知必会笔记创建计算字段

SQL必知必会笔记使用通配符过滤数据

MySQl数据库必知必会sql语句(加强版)

《sql必知必会》学习笔记（四）视图

Java线程池必知必会知识点总结

《SQL必知必会》知识点汇总

SQL必知必会讲解

5. 使用通配符过滤（用于文本段,where子句使用like操作符