一、表結構與完整性約束的修改(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,'林惠雯','960323053@qq.com','411014196712130323','15609032348','Mop5UPkl'),
(2,'吳婉瑜','1613230826@gmail.com','420152196802131323','17605132307','QUTPhxgVNlXtMxN'),
(3,'蔡貞儀','252323341@foxmail.com','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. 基于視圖的查詢