头歌实践教学平台--【数据库概论】--SQL

一、表结构与完整性约束的修改(ALTER)

1.修改表名

USE TestDb1;
alter table your_table rename TO my_table;

2.添加与删除字段

#语句1：删除表orderDetail中的列orderDate
alter table orderDetail drop orderDate;
#语句2：添加列unitPrice
alter table orderDetail add unitPrice numeric(10,2);

3.修改字段

#请在以下空白处添加适当的SQL语句，实现编程要求
alter table addressBook modify column QQ char(12);
alter table addressBook rename column weixin to wechat;

4.添加、删除与修改约束

二、 单表查询(简单比较,in,范围查询)

1.基本查询语句

-- 任务1: 查询雇员(staff)的全部信息
-- 请在此处添加实现代码
select * from staff;
-- 任务2: 查询全部雇员(staff)的姓名(sname)、出生日期(dob)和手机号码(mobile)
-- 请在此处添加实现代码
select sname,dob,mobile from staff;
-- EOF

2.对查询的结果排序

-- 任务1: 写出能完成以下任务的语句：
-- 查询所有雇员的姓名,性别，手机号。按年龄降序排列，即年龄越大的越靠前。
-- 请在此处添加实现代码
select sname,gender,mobile
from staff
order by dob asc;

3.带比较条件的查询

-- 任务1: 写出能完成以下人物的sql语句：
-- 查询格力空调的型号和价格，依价格升序排列。
-- 请在此处添加实现代码
select model,price 
from products
where manufacturer='格力'
order by price asc;

4.带IN关键字的查询

-- 任务1: 写出能完成以下查询任务的SQL语句:
-- 查询10，40，70号产品(空调)的型号、生产厂家和价格，依价格从低到高排序。
-- 请在此处添加实现代码
select model,manufacturer,price
from products
where pid in(10,40,70)
order by price asc;

5.带BETWEEN AND关键字的查询

-- 任务1: 写出可以完成以下查询任务的sql语句：
-- 查询价格在2000到3000之间的所有空调的型号，生产厂家和价格。依价格从低到高排序。
-- 请在此处添加实现代码
select model,manufacturer,price
from products
where price  between 2000 and 3000
order by price;

三、单表查询(模式匹配,null,去重,and,or)

1.带LIKE 的字符匹配查询

-- 实现以下查询的SQL语句：
-- 查询1.5匹，即功率3500W的冷暖空调，列出型号，生产厂家和价格，依价格从低到高排序。
-- 请在此处添加实现代码
select model,manufacturer,price
from products
where model like 'KFR%_35%'
order by price;

2.带RLIKE 的字符匹配查询

-- 实现以下查询的SQL语句：
-- 查询分体式室热泵制热冷暖双制，制冷量7200W或6000W的落地式空调的型号、生产厂家和价格，查询结果依价格从低到高排序输出。
-- 请在此处添加实现代码：
select model,manufacturer,price
from products
where model rlike '^KFR-(72|60)LW.*' 
order by price;

3.. 查询空值与去除重复结果

-- 任务1：查查询产品表(products)表中，有多少厂家(manufacturer)的产品，一个厂家只列一次，即便该厂家有多个型号的产品。查询结果依厂家名称排序输出。
-- 请在此处添加实现代码
select distinct manufacturer
from products
order by manufacturer;
-- 任务2：查询出生日期(出生日期)未填写(值为NULL)的员工编号，姓名。查询结果依姓名排序输出。
-- 请在此处添加实现代码
select sid,sname
from staff
where dob is null
order by sname;

4.带 AND 与 OR 的多条件查询

-- 查询“美的”、“格力”或 “志高”价格在3000(含)以下，功率为3500W的空调信息，列出厂家，型号，价格。查询结果依价格从低到高排序。
-- 请在此处添加实现代码
select manufacturer,model,price
from products
where manufacturer in('美的', '格力','志高' )and price<=3000 and model like 'KF%_35%'
order by price;

四、单表查询(统计总和,平均,最大,最小)

1.COUNT( )函数及对计算列重命名

-- 查询2023年国庆长假期间(9-29至10-6)共有多少员工共完成了多少笔销售交易，涉及多少个型号的空调。统计结果分别命名为：人数,笔数，型号数。
-- 请在此处添加实现代码
select count(distinct sid) as '人数',
count(rid) as '笔数',
count(distinct pid) as '型号数'
from sales_record
where sdate between '2023-9-29' and '2023-10-6';

2.SUM( )函数

-- 查询7号员工2023年上半年共销售了几个型号的总共多少台空调？统计结果分别命名为：“型号数”、“总台数”。
-- 请在此处添加实现代码
select 
count(distinct pid) as '型号数',
sum(quantity) as '总台数'
from sales_record
where sid='7' and sdate between '2023-1-1' and '2023-6-30' ;

3. AVG( )函数

-- 查询产品表中制冷量3500W室内分体空调的平均价格,对均价取整，并命名为：“均价”。
-- 请在此处添加实现代码
select round(avg(price)) as '均价'
from products
where model rlike '^KF[RD]?-35[DGLTQ]W.*'

4.MAX( )函数

-- 查询产品表中制冷量7200W室内分体落地式冷暖空调的最高价,并命名为：“最高价”。。
-- 请在此处添加实现代码
select max(price) '最高价'
from products
where model rlike '^KF[RD]?-72LW.*'

5.MIN( )函数

-- 查询产品表中制冷量3500W室内分体壁挂式冷暖空调的最低价,并命名为：“最低价”。
-- 请在此处添加实现代码
select min(price) '最低价'
from products
where model rlike '^KFR-35GW.*'

五、单表查询(分组统计,限定输出行数)

1. 使用 limit 限制查询结果的数量

-- 查询产品表中制冷量3500W室内分体壁挂式冷暖空调的价格个最低的三个产品的厂家，型号和价格。按价格从低到高排序。。
-- 请在此处添加实现代码
select manufacturer,model,price
from products
where model rlike '^KF[RD]?-35GW.*'
order by price asc
limit 0,3;

2. 分组统计查询(group by)

-- 统计每个空调厂家，制冷功率为3500W的空调平均价格(取整)。结果表的标题分别为：“厂家”，“平均价格”。查询结果依生产厂家名称排序。
-- 请在此处添加实现代码
select manufacturer as '厂家', round(avg(price)) as'平均价格'
from products
where model rlike '^KF[R]?-35[DGLTQ]W.*'
group by manufacturer 
order by 厂家;

3. 对分组统计的结果再筛选(having)

-- 查询2023国庆长假期间(9月29日到10月6日)所有员工销售空调的总台数，并按销售量从高到底排序。只输出总台数超过20(含)台数的结果。总台数命名为total。
-- 请在此处添加实现代码
select sid,sum(quantity) as total
from sales_record where sdate between '2023-9-29' and '2023-10-6'
group by sid 
having total>=20
order by total desc;

六、连接查询(内连,外连,多表分组统计)

1.内连接查询

-- 查询2023年国庆小长假期间(9月29日到10月6日)的销售明细，包括：型号(model)，厂家(manufacturer)，数量(qunantity), 单价(price)和折扣(discount)。查询结果依销售记录号(rid)排序。
-- 请在此处添加实现代码
select model,manufacturer,quantity,price,discount
from products inner join sales_record on products.pid=sales_record.pid
where sdate between '2023-9-29' and '2023-10-6'
order by rid;

2.外连接查询

-- 查询2023年国庆节小长假期间(9月29日到10月6日)“格力”空调的销售明细，包括：型号(model)，数量(qunantity), 单价(price)和折扣(discount)。没有销售记录的型号也要包括在查询结果中。查询结果按产品号(pid)排序,产品号相同时，再依销售记录号(rid)排序。
-- 请在此处添加实现代码
select model,quantity,price,discount
from products left join sales_record on products.pid=sales_record.pid 
and sdate between '2023-9-29' and '2023-10-6'
where manufacturer='格力'
order by products.pid,rid;

3.多表连接查询

-- 统计各位销售人员在2023年国庆节小长假期间(9月29日到10月6日)期间的销售业绩(即销售总金额)，列出销售人员姓名，销售额(命名为total)。依销售额从高到低排序。
-- 请在此处添加实现代码
select sname,sum(quantity*price *discount) as total 
from staff,products,sales_record
where sales_record.sid=staff.sid and sales_record.pid=products.pid and sdate between '2023-9-29' and '2023-10-6'
group by staff.sid
order by total desc;

七、子查询(in,exists,子查询的位置,分组统计)

1. 带IN谓词的子查询

-- 查询“郑点”没有卖过那些厂家的空调。结果依厂家名字排序。
-- 请在此处添加实现代码
select distinct manufacturer 
from products
where manufacturer not in(select distinct manufacturerfrom productsinner join sales_record on products.pid=sales_record.pidinner join staff on sales_record.sid=staff.sidwhere sname='郑点'
)
order by manufacturer;

2. 带EXISTS谓词的子查询

-- 被1-9号(正式)员工都卖过的产品(空调)编号，型号，生产厂家和价格。结果依产品号排序。
-- 请在此处添加实现代码
select p.pid,p.model,p.manufacturer,p.price
from products p
inner join sales_record sr on p.pid=sr.pid
inner join staff s on sr.sid=s.sid
where s.sid between 1 and 9
group by p.pid,p.model,p.manufacturer
having count(distinct s.sid)=9
order by p.pid;

3. 复杂子查询及分组统计

--  统计每个厂家空调的型号数，其中单冷总型号数，冷暖总型号数。统计结果按总型号数从高到底排列。列出内容：
--  生产厂家：manufacturer
--  总型号数: model_total
--  单冷型号数:single_total
--  冷暖型号数：dual_total
select manufacturer,
count(distinct model) as model_total,
count(distinct case when model regexp '^KF-[0-9]{2}[DGLTQ]W.*' then model end) as single_total,
count(distinct case when model regexp '^KF[RD]-[0-9]{2}[DGLTQ]W.*' then model end) as dual_total
from products
group by manufacturer
order by model_total desc;

八、数据的插入、修改与删除(Insert,Update,Delete)

1. 插入多条完整的客户信息

-- 用insert语句向客户表(client)插入任务要求的3条数据:
insert into client(c_id,c_name,c_mail,c_id_card,c_phone,c_password)
values(1,'林惠雯','[e-mail]','411014196712130323','15609032348','Mop5UPkl'),
(2,'吴婉瑜','[e-mail]','420152196802131323','17605132307','QUTPhxgVNlXtMxN'),
(3,'蔡贞仪','[e-mail]','160347199005222323','17763232321','Bwe3gyhEErJ7');

2. 插入不完整的客户信息

-- 已知33号客户部分信息如下:
-- c_id(编号):33
-- c_name(名称):蔡依婷
-- c_phone(电话):18820762130
-- c_id_card(身份证号):350972199204227621
-- c_password(密码):MKwEuc1sc6-- 请用一条SQL语句将这名客户的信息插入到客户表(client)：
insert into client(c_id,c_name,c_phone,c_id_card,c_password)
value(33,'蔡依婷','18820762130','350972199204227621','MKwEuc1sc6');

3. 批量插入数据

-- 已知表new_client保存了一批新客户信息，该表与client表结构完全相同。请用一条SQL语句将new_client表的全部客户信息插入到客户表(client):
insert into client
select *
from new_client;

4. 删除没有银行卡的客户信息

-- 请用一条SQL语句删除client表中没有银行卡的客户信息：
delete from client
where not exists(select 1from bank_cardwhere bank_card.b_c_id=client.c_id
);

5. 冻结客户资产

-- 请用一条update语句将手机号码为“13686431238”的这位客户的投资资产(理财、保险与基金)的状态置为“冻结”。：
update client
inner join property on client.c_id=property.pro_c_id
set property.pro_status='冻结'
where client.c_phone='13686431238'
and property.pro_type in(1,2,3);

6.连接更新

-- 在金融应用场景数据库中，已在表property(资产表)中添加了客户身份证列，列名为pro_id_card，类型为char(18)，该列目前全部留空(null)。-- 请用一条update语句，根据client表中提供的身份证号(c_id_card)，填写property表中对应的身份证号信息(pro_id_card)。
update property join client as c on property.pro_c_id=c.c_id
set property.pro_id_card=c.c_id_card;

九、视图

1. 创建所有保险资产的详细记录视图

-- 创建包含所有保险资产记录的详细信息的视图v_insurance_detail，包括购买客户的名称、客户的身份证号、保险名称、保障项目、商品状态、商品数量、保险金额、保险年限、商品收益和购买时间。
-- 请用1条SQL语句完成上述任务：
create view v_insurance_detail as select c_name,c_id_card,i_name,i_project,pro_status,pro_quantity,i_amount,i_year,pro_income,pro_purchase_time from (select * from (select * from (select * from client union select * from client new_client) a join property b on a.c_id = b.pro_c_id) c join insurance d on c.pro_pif_id = d.i_id) e where e.pro_type = '2';

2. 基于视图的查询