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之后
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;
-
or
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()对表中行的数目进行计算
- count(*),无论行中包含的是什么,都计数
- 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 ;
例如表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