大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

SQL: 海阔天空篇

幸运的周五,见到可爱的学弟学妹。时至今日,当时你们面试的场景,还是历历在目的,可以说变化很大,也许是比较熟悉总觉得大家都还是一样。啊,可能是年龄大了,会莫名想起以前的日子。
美妙周末开始前夜,小采风默默记录下SQL中其他常见的概念,之所以取名为海阔天空,是因为对于SQL的学习永无止境,不断的理解、应用和优化,技能慢慢是会被点亮的。
本文的思路是,创建几张相关的表,千万别嫌弃表太多,非常好的方便理解,然后结合创建的表温习一下概念,严格遵循习大大的精神,空谈误国,实干兴邦。

//客户表
create table customers(
   cust_id int primary key auto_increment,
   cust_name varchar(20) not null,
   cust_tel int not null,
   cust_email varchar(20) 
)
//订单表
create table orders(
   order_id int primary key auto_increment,
   order_date date not null,
   cust_id int,
   constraint cust_id_FK 
   foreign key(cust_id) references customers(cust_id)
)
//订单条目表
create table orderitems(
   order_id int,
   order_item varchar(20),
   prod_id int,
   quantity int,
   item_price float,
   primary key(order_id,order_item),
   constraint order_id_FK 
   foreign key(order_id) references orders(order_id),
   constraint prod_id_FK
   foreign key(prod_id) references production(prod_id) 
)
//供应商表
create table vendors(
   vend_id int primary key auto_increment,
   vend_name varchar(20) not null,
   vend_addr varchar(20) not null
)
//商品表
create table productions(
   prod_id int primary key auto_increment,
   vend_id int,
   prod_name varchar(20),
   prod_price float,
   prod_desc text,
   constraint vend_id_FK 
   foreign key(vend_id) conferences vendors(vend_id) 
)

//注:关于表中联合主键和外键的概念,请参照浅尝辄止篇

一、子查询

子查询,是嵌套在其他查询中的查询,常用于:
1 利用子查询过滤
2 使用子查询作为计算字段
注意:子查询往往不是最优的查询方式,但是方便理解,使用中尽量选取最优化的查询语句

//列出订购物品小米的用户:作为过滤条件
select cust_name,cust_tel from customers
where cust_id in (select cust_id from orders where order_id in  
(select order_id from orderitems where orderitem='xiaomi'));

//客户表中每个客户的订单数:作为计算字段
select cust_name,cust_tel,
(select count(*) from orders 
where orders.cust_id = customers.cust_id) as order_num 
from customers order by cust_name;

二、联结

通常,数据是分布在多个表中的,通过联结语法,获得一组表的联合输出,联结是强大的SQL语法,主要用于:
1 内联结(与where过滤条件相同)
2 自联结(同一张表相互联结)
3 外联结(左外联结与右外联结)
4 带聚集函数的联结
注意:左外联结为左表全部与右表共有的,内联结为左右表共有

//1 内联结
//列出每个供应商提供的商品价格:
select vend_name,prod_name,prod_price 
from vendors,productions 
where vendors.vend_id = productions.vend_id
order by vend_name,prod_name
//过滤与内联结完全相同
select vend_name,prod_name,prod_price
from vendors v INNER JOIN productions p
ON v.vend_id=p.vend_id
order by vend_name,prod_name 

// 2 自联结
//列出某不合法商品供应商的其他商品
select p1.prod_id,p1.prod_name 
from productions p1,productions p2
where p1.vend_id = p2.vend_id and p2.prod_name='xiaomi'

// 3 外联结:左外联结
// 列出所有客户的订单,包括没有订单的
select c.cust_name,o.order_id
from customers c LEFT OUTER JOIN orders o
ON  c.cust_id = o.cust_id;

// 4 带聚集函数的联结
// 列出所有客户的订单数,包括没有订单的
select c.cust_id,c.cust_name,count(o.order_id) as order_num
from customers c LEFT JOIN orders o
ON c.cust_id = o.cust_id group by c.cust_id;

三、组合查询

组合查询和多where条件查询,可以实现相同的效果,但是在不同的查询中,其性能有所差异,比较选择使用:
注意:
UNION自动删除重复的行记录,UNION ALL不自动删除;
UNION只能使用一条order by语句,两个表不能有两种排序;

// 查询出价格大于10的商品,且供应商为111和531的商品
select prod_id,prod_name,prod_price
from productions where price > 10
union 
selecct prod_id,prod_name,prod_price
from productions where prod_id in (111,531);

四、存储过程

存储过程,为以后使用而保存的一条或多条的SQL语句的组合,具有简单、安全和高性能的特点,和MySQL中的自定义函数相比,功能更加全面,使用简单,注意事项如下:
IN,使用时传递进去参数类型,OUT,存储过程使用后传递出去类型;
使用中需要使用delimiter暂时更新新的语句结束分割符;

// 传递订单编号,计算订单价格
create procedure order_total(
   IN order_num int,
   OUT order_sum decimal(8,2)
)
BEGIN
   select sum(item_price*quantity) 
   from orderitems where order_id = order_num
   into order_sum;
END;
// 调用这个存储过程
call order_total(111,@total);
// 查看订单结果:切记此处需要@
select @total;
// 删除过程:切记此处没有括号
dorp prodedure order_total;

五、视图

视图,一张虚拟的表,其本身不包含数据,使用时是从其他表中获取数据,其主要是用来检索数据的,多数视图的不能更新的,主要用于:
1 简化复杂联结
2 格式化检索数据
3 过滤不想要的数据

// 1 简化复杂联结
//创建出所有客户订购商品视图
create view customer_productions as
select cust_name,cust_tel,os.prod_id 
from customers c,order o,orderitems os
where c.cust_id=o.cust_id
and os.order_id = o.order_id;
//列出订购xiaomi的客户
select cust_name,cust_tel 
from customer_productions 
where prod_name='xiaomi';

// 2 格式化检索出的数据
//将每个供应商的名称和地址化格式化为:名称(地址)
create view vendor_style as
select concat(vend_name,'(',vend_addr,')')
from vendors order by vend_name;
//将所有供应商进行格式化
select * from vendor_style;

// 3 过滤不想要的数据
//过滤邮箱地址为空的客户
create view customer_email as 
select cust_name,cust_tel,cust_email
from customers where email is null;
//列出所有邮箱非空的用户信息
select * from customer_email;

SQL的学习,可能要暂时更新到这里。意识到一个严重的问题,学习的方法论,温故而知新。SQL最初的学习,已经过了七八个月,重新会看当时的书,真的会有新的认识,方法不当,学习效率低下。多读书,多读书。
明天,要再晨跑中见证新的一天,愿明早的日光,带给我更大的好运哦。