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

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之后
1
2
3
select prod_id,prod_name,prod_price from Products
where prod_price=3.49
order by prod_name DESC;

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

操作符between and

1
2
3

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

1
2
3

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()转大写

1
2
3

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

返回所有产品的平均值

1
2
3

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
1
2
3
select count(*) as num_cust from Customers ;
1
2
3
select count(cust_email) as num_cust from Customers ;

max()

1
2
3

select max(prod_price) as max_price 
from Products 
;

min()

1
2
3

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中(避免重复值计数)

1
2
3

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.分组数据

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

产品表: 统计每个供应商提供了多少产品(产品分组)
1
2
3
4
select vend_id,count(vend_id) as num from Products group by vend_id ;
供应商表: 统计的每个供应商来自那些国家(国家分组)
1
2
3
4
select vend_country,count(vend_country) as num from Vendors group by vend_country ;
订单项目表: 统计每个订单分别由那些产品组成(由产品分组)
1
2
3
4
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子句,指定排序字段

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

10.使用子查询

列出物品RGAN01的所有客户

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

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

分2部分做到
1
2
3
4
5
6
7
8
9
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字段)
1
2
3
4
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个不同项连接一起
1
2
3
select vend_name,prod_name,prod_price from Vendors,Products ;
等值联结也称内部联结,也可用下面语法 inner A join B on 条件
1
2
3
4
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个表出现的顺序)
1
2
3
4
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表中
1
2
insert into Customers select * from CustNew;
或者将CustNew中的某条记录插入表Customers中:
1
2
3
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’的客户
1
2
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
);

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

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

就能看到default的值

在表中添加新的列

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

删除列

1 2	`alter table Vendors drop column vend_phone;`

删除表

删除Vendors表
1
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'
;

删除视图
1
2
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 
;

1
2
3

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=虚拟的表,包含的不是数据而是根据需要检索数的查询

数据库(入门

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

有限状态机实例:服务端实现简单的HTTP请求的读取和分析上一篇

IPv4协议详解下一篇