《SQL 必知必会》读书笔记

目录

 

检索数据

排序检索数据

 过滤数据

高级过滤数据

用通配符进行过滤

创建计算字段

使用函数处理数据

汇总数据

分组数据

使用子查询

联结表(join)

创建高级联结

组合查询(union)

插入数据

更新和删除数据

创建和操纵表

使用视图

 

---

检索数据

SELECT prod_name FROM products; //查询单列
SELECT prod_id,prod_namae,prod_price FROM products;//查询多列
SELECT * FROM products;//查询所有列
SELECT DISTINCT vend_id FROM products;//去重
SELECT TOP 5 prod_name FROM products;//查询前5行
SELECT prod_name FROM products LIMIT 5;//返回不超过5行结果

排序检索数据

SELECT prod_name
FROM products
ORDER BY prod_name; //结果按prod_name进行排序

SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name;//结果按prod_price排序，若prod_price相同，再按name排序

SELECT prod_price
FROM products
ORDER BY pro_pricr DESC;//按降序进行排列

SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name;//结果按prod_price降序排序，若prod_price相同，再按name升序排序

 过滤数据

 

SELECT prod_name,prod_price
FROM products
WHERE prod_price=3.49;//查询价格为3.49的产品名称和产品价格

SELECT prod_name,prod_price
FROM products
WHERE prod_price<10;//查询价格小于10的产品名称和产品价格

SELECT prod_name,prod_price
FROM products
WHERE prod_price BWTWEEN 5 AND 10;//查询价格在5到10的产品名称和价格

SELECT prod_name
FROM products
WHERE prod_price IS NULL;//价格为空的产品名称

高级过滤数据

SELECT vend_id,prod_price,prod_name
FROM products
WHERE vend_id ='DLL' AND prod_price <= 4;//查询供应商id为DLL，并且价格小于等于4的

SELECT vend_id,prod_price,prod_name
FROM products
WHERE vend_id ='DLL' OR prod_price <= 4;//查询供应商id为DLL，或者价格小于等于4的

SELECT vend_name,prod_price
FROM products
WHERE vend_id='DLL' OR vend_id='BRS' AND prod_price>=10;
//结果为供应商id为DLL的所有产品 和 供应商id为BRS并且价格大于等于10的产品

//如果想查询DLL或者BRS价格大于等于10的产品，应该将OR两边括起来
SELECT vend_name,prod_price
FROM products
WHERE (vend_id='DLL' OR vend_id='BRS') AND prod_price>=10;

SELECT vend_name,prod_price
FROM products
WHERE vend_id IN ('DLL','BRS') AND prod_price>=10;//与上条效果一样

用通配符进行过滤

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'FISH%';//查询名称以FISH开头的产品，%可以为0个，1个，多个字符

SELECT prod_id，prod_name
FROM products
WHERE prod_name LIKE '%tag%';//查询名称包含tag的产品

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'FISH_'；//查询名称以FISH开头，且后面只有一个字符的产品

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '[JM]%';//查询名称以J或者以M开头的产品，不以J或M使用[^JM]

创建计算字段

SELECT CONCAT(prod_name,' (',prod_price,')')
FROM products
ORDER BY prod_name;//将产品名称和产品价格拼接起来，并将价格用()括起来

SELECT CONCAT(prod_name,' (',prod_price,')')  AS total
FROM products
ORDER BY total;//使用别名

SELECT prod_price*nums AS total
FROM products
ORDER BY total;//查询产品价格*采购数，并按积排序

使用函数处理数据

SELECT UPPER(prod_name)
FROM products;//将产品名称转为大写

SELECT prod_name
FROM products
WHERE YEAR(date)=2012;//查询2012年的产品名称

汇总数据

SELECT AVG(prod_price)
FROM products;//查询产品价格平均值,AVG忽略为NULL的行

SELECT COUNT(*)
FROM products；//查询有多少产品

SELECT SUM(nums)
FROM products;//查询所有产品的采购数

SELECT AVG(DISTICNT prod_price)
FROM products;//查询不同价格产品的平均值

分组数据

SELECT vend_id,count(*)
FROM products
GROUP BY vend_id;//查询各个供应商供应的产品数量

SELECT vend_id,count(*)
FOMR products
GROUP BY vend_id
HAVING count(*)>10;//查询出供应产品数量大于10个的供应商

HAVING和WHERE区别，HAVING在分组后过滤，WHERE在分组前过滤

SELECT vend_id,count(*)
FOMR products
WHERE prod_price>5
GROUP BY vend_id
HAVING count(*)>10;//查询出供应产品价格大于5并且数量大于10的供应商

SELECT vend_id,count(*) AS items
FOMR products
WHERE prod_price>5
GROUP BY vend_id
HAVING count(*)>10
ORDER BY items；//最后按供应的数量排序

使用子查询

//三张表，Order表存订单日期，编号，顾客id， OrderItems存储各订单相关物品，Customers存储顾客信息
//需求1：查询订购过物品RGA的顾客信息
//步骤：先查询包含物品RGA的所有订单编号，根据订单编号查询顾客id,再根据顾客id查顾客信息
select order_id
from OrderItems
where prod_id='RGA';

//然后
select cust_id
from Order
where order_id in (...);//把上条查询结果放入括号内

//合并上面两条
select cust_id
from Order
where order_id in (select order_id
                    from OrderItems
                    where prod_id='RGA');
//查询顾客信息
select * 
from Customers
where cust_id in(...);//把上条结果放入括号

//合并上面两条
select * 
from Customers
where cust_id in(select cust_id
                from Order
                where order_id in (select order_id
                                    from OrderItems
                                    where prod_id='RGA'));

//子查询作为计算字段
//需求：查询每个顾客的订单数
select cust_name,
        (select count(*) 
        from order
        where Orders.cust_id=Customers.cust_id) as nums
from Customers;

联结表(join)

//查询供应商的产品名称和价格
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id=products.vend_id;//没有where，返回笛卡尔积，每一行与另一张表所有行


//之前特别复杂的子查询，改用联结
select cust_name
from customers,orders,orderItems
where customers.cust_id=orders.cust_id
    and orderItems.order_id=orders.order_id
    and prod_id='RGA'

创建高级联结

//公司，员工表  找出跟“tom”所在同一公司的所有员工
select name,comp
from cn as cn1 ,cn as cn2
where cn1.comp=cn2.comp
    and cn1.name='tom'

组合查询(union)

select cust_name,cust_email
from customers
where cust_state in ('IL','IN');

select cust_name,cust_email
from customers
where cust_name='RGA';

//会将两条查询的结果合并
select cust_name,cust_email
from customers
where cust_state in ('IL','IN')
union  //union all不去重
select cust_name,cust_email
from customers
where cust_name='RGA';
//末尾还可加order by cust_name;

//不使用union
select cust_name,cust_email
from customers
where cust_state in ('IL','IN') or cust_name='RGA';

插入数据

//插入完整的行
insert into customers
    values("tom",
            "fbkasfb@163.com",
             21    
            );
//插入部分行
insert into customs(cust_name,
                    cust_email
                    )
        values("jerry",
                "dad@163.com"
                );
//插入select结果
insert into customers(fieldname1,
                      fieldname2,
                      fieldname3)
select fieldname1,fieldname2,fieldname3 from tmp;

更新和删除数据

//更新数据，更新特定行，更新所有行
update customers
set cust_name='zlq'
where cust_id='0001';

update customers
set cust_name='zlq'
    cust_emial='fgwhiagg@163.com'
where cust_id='0001';

//删除数据
delete from customers
where cust_id='0001';

创建和操纵表

create table products(
prod_id char(10) NOT NULL,
vend_id char(10) NOT NULL,
prod_name char(254)
)
//只有不允许为NULL字段可作为主键

create table orderItems(
order_num integer NOT NULL,
order_item integer NOT NULL,
quantity Integer NOT NULL DEFAULT 1
)
//字段默认值

alter table vendors
add vend_phone char(20)
//添加字段

alter table vendors
drop column vend_phone
//删除字段

drop table products
//删除表

使用视图

//视图是虚拟的表，与保存数据的表不一样

//查询购买过物品RGA的用户信息
select cust_name,cust_contact
from customers,order,orderItems
where customers.cust_id=orders.cust_id
and orders.order_num=orderItems.order_num
and prod_id='RGA';

//我们可以把刚查询的结果封装成product_cust的虚拟表
select cust_name,cust_contact
from product_cust
where prod_id='RGA';

//视图作用：方便重用，不必知道细节，简化sql
//创建视图
create view product_cust as
select cust_name,cust_contact,prod_id
where customers.cust_id=orders.cust_id
and orders.order_num=orderItems.order_num;