很久都没有整理SQL语句了,遇到让写SQL语句的题也很迷茫,所以就重拾一下知识,本文章根据SQL必知必会进行梳理
文章目录
一、检索所有列
1.select
1.1检索单个列
查找所有的产品名称
select prod_name
from products;
注意:sql语句不区分大小写
返回结果:
1.2 检索多个列
查询商品id,查询商品名称,查询商品价格
select prod_id,prod_name,prod_price
from products;
返回结果:
1.3 检索所有列
查询products表中的所有信息
select*
from products;
1.4 检索不同值
查询产品供货商
select vend_id
from products;
展示结果:
这里我们发现,展示了所有的供货商,但是有重复,实际只有三个供应商,此时如果只是想展示3个供应商,那么就需要使用distinct关键字
查询产品供应商
select distinct vend_id
from products;
注意:distinct关键字作用域后面的所有列
1.5 限制结果
mysql中取出前两行的数据
select prod_id
from products
limit 2;
如果是从第二行开始,后面的两行
select prod_id
from products
limit 2 offset 2;
这里单纯的限制多少行,使用limit就足够了,限制从第几行开始之后的几行,就需要使用limit和offset
1.6 使用注释
在sql语句中,注释使用的是:
--
/* */
二、排序检索数据
2.1 按照多个列排序
使用关键字 order by,想对哪一列进行排序,就把他放在order by后面
查询商品号,商品价格,商品名称,首先按照价格排序,然后按照名称排序
select prod_id,prod_price,prod_price
from products
order by prod_price,prod_name;
我们发现商品的价格进行了从小到大的排序
2.2 按照列位置排序
查询商品号,商品价格,商品名称,首先按照价格排序,然后按照名称排序
select prod_id,prod_price,prod_price
from products
order by 2,3;
可以看到和上一个列明列出来的效果是相同的
2.3 指定排序方向
查询商品号,商品价格,商品名称,首先按照价格排序,然后按照名称排序
select prod_id,prod_price,prod_price
from products
order by 2,3 desc;
==默认的都是升序asc,如果我们想要按照降序排列就需要关键字desc ==
desc只应用在他之前的那一列中,如果想对每一列都进行降序,那么只能在每一列中都增加desc
三,过滤数据
3.1 使用where子句
查找产品价格等于32.4的产品名称
select prod_name,prod_price
from products
where prod_price = 32.4;
这里有一个疑问,假如没有我们想要查询的信息,数据库将会给我们返回什么?
答案是:返回空
4.2检查单个值
输出价格低于30的所有产品名
select prod_name,prod_price
from products
where prod_price < 30;
4.3不匹配查询
查询制造商不是DLL001的商品名
select prod_name,vend_id
from products
where vend_id != 'DLL001';
在这里本作者出了一个错误,就是’DLL001’没有带单引号,希望读者可以注意这个问题,共勉
4.4范围值查询
查询产品价格在10元到20元之间的产品
select prod_name,prod_price
from products
where prod_price between 10 and 20;
4.5空值查询
查询价格为空的商品
select prod_name,prod_price
from products
where prod_price is null;
五,高级数据过滤
5.1使用and
找出商品价格大于20并且制造商为DLL001的所有商品
select prod_name,prod_price,vend_id
from products
where prod_price > 20 and vend_id = 'DLL001';
5.2使用or
找出制造商为DLL001或BRS01的商品
select prod_name,vend_id
from products
where vend_id='DLL001' or vend_id = 'BRS01';
5.3使用in
查找制造商在DLL001和BRS01里的商品
select prod_name,vend_id
from products
where vend_id in('DLL001','BRS01');
六,用通配符进行过滤
6.1 %通配符
找商品名称为pro起头的商品名称
select prod_name
from products
where prod_name like 'pro%';
他不仅可以匹配以什么开头,还可以匹配中间有什么字符的
找到以roducts为中间的商品名
select prod_name
from products
where prod_name like '%roducts%';
6.2 _通配符
找商品名称为pro起头的商品名称
select prod_name
from products
where prod_name like 'pro_';
可以看出区别了吗?%通配符适用于匹配多个字符,而_只能匹配单个字符
七,使用函数
7.1 upper函数
将商品名称全部转换为大写
select prod_name,upper(prod_name) as prod_name2
from products
order by prod_name;
7.2 日期处理函数
省略
八,聚集函数
8.1avg()函数求平均值
算出所有商品的价格平均值
select avg(prod_price) as avg_price
from products;
获得厂商为DLL001的商品价格平均值
select vend_id,avg(prod_price) as avg_price
from products
where vend_id = 'DLL001';
8.2count()函数求总数
求所有商品的总数
select count(prod_id) as count_prod
from products;
或者
select count(*) as count_prod
from products;
8.3max()函数求最大值
求商品价格的最大值
select max(prod_price) as max_price
from products;
8.4sum()函数求和
求所有商品的价值总和
select sum(prod_price) as sum_price
from products;
九,分组数据
9.1创建分组
创建分组我们使用的是group by
查询不同的制造商制造几种商品
select vend_id,count(*) as varible_vend_sum
from products
group by vend_id;
需要注意的一点是:group by 子句必须出现在where之后,order by之前
9.2 过滤分组
错误示范
查询制造商制造种类大于2的制造商
select vend_id,count() as varible_vend_sum
from products
where count() >= 2
group by vend_id;
他会报错,为什么呢?因为where语句后面应该跟的是原本就存在的列,而不是基于过滤分组后的列,这时候我们就应该使用having关键字了
查询制造商制造种类大于2的制造商
select vend_id,count(*) as varible_vend_sum
from products
group by vend_id
having count(*) >= 2;
但是有没有既拥有where的sql语句又拥有having的sql语句的查询呢?有,下面就是个例子
查询开发商制造大于两个种类的产品,并且产品价格高于18.3的开发商
select vend_id,count(*) as varible_vend_sum
from products
where prod_price > 18.3
group by vend_id
having count(*) >= 2;
9.3分组和排序
我们前面讨论过,order by,那order by 和group by有什么区别呢?
order by对产生的输出结果进行排序,通常有两种排序方式,一种是升序asc,一种是降序desc,任意列都能使用。
group by对行进行分组,但是输出的不一定是分组的顺序,只可能使用选择列或表达式列,如果与聚集函数一起使用,那么就必须使用group by
我们延续上面的例子
查询制造商制造种类大于1的制造商
select vend_id,count(*) as varible_vend_sum
from products
group by vend_id
having count(*) >= 1;
如果我们还要求输出的数目应该按照升序排列,那么
查询制造商制造种类大于1的制造商,并且按照数量升序排列
select vend_id,count(*) as varible_vend_sum
from products
group by vend_id
having count(*) >= 1
order by varible_vend_sum,vend_id;
9.4select子句顺序
子句 | 说明 |
---|---|
select | 选择要返回的列或者是表达式,必须使用 |
from | 从中检索数据的表,只在表选择数据时候使用 |
where | 行级过滤,不是必须使用的 |
group by | 分组说明,尽在按照组进行聚合函数计算时使用 |
having | 组级过滤,不是必须使用 |
order by | 输出排序,不是必须使用 |
十,使用子查询
检索包含物品RGAN01的所有订单的编号
select oder_num
from oderitems
where prod_id = 'RGAN01';
检索具有RGAN01订单编号的所有顾客的ID
select cust_id
from orders
where order_num in(select order_num
from oderitems
where prod_id = 'RGAN01');
显示customers表中每个顾客的订单总数
select cust_name,(select count(*)
from orders
where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;
十一 联结表
11.1创建连接
求开放商名称,商品名称,商品价格
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id;
其实上述的联结是等值连接,也称为内连接
换一种写法
select vend_name,prod_name,prod_price
from vendors inner join products
on venders.vend_id = products.vend_id;
查询商品名称,查询开发商名称,商品价格,物品数量,并且订单号为20007
select prod_name,vend_name,prod_price,quantity
from oderitems,vendors,productors
where odertimes.prod_id = products.id
and vendors.vend_id = vendors.vend_id
11.2创建外连接
外连接包括左外连接和右外连接,两个在本质上是没有区别的,左连接关键字是left outer join... on...,右连接的关键字是right outer join...on。外连接主要就是为了显示没有的东西,比如我想要查询没有订单的用户,我需要分析他们为什么么有买东西,这时候就需要进行外连接。
查询包括没有订单顾客在内的所有顾客
select customers.cust_id,order.order_num
from customers left outer join oders
on customers.cust_id = orders.cust_id;
十二 数据插入
使用关键字insert
插入完整的行
insert into customers
values('xx',
'dsj',
...);
插入部分行
insert into customers(插入的行)
values(值);
十三 数据更新和删除
更新使用update关键字,删除使用delete关键字
更新用户193690的电子邮箱地址
update customers
set cust_email ='shakhdn@1297.com'
where cust_id = '193690';
delete有两种用法,删除特定的行,从表中删除所有的行
删除customers中的一行,顾客id为23728979
detete from customers
where cust_id = '23728979';
十四 创建和操纵表
创建表
create table 表名(
列名字 类型 是否为null,
列名字2 ...
);
默认值设置为default
更新表
alter table ...
add ...;
十五 游标
创建表
declare 游标名cursor
for
select ...;
使用游标
open cursor ...
关闭游标
close 游标名