头歌实训MySQL3-5章参考答案(含选填)

01MySQL-数据库、表与完整性约束的定义(Create)

第1关：创建数据库

1. -- 本关我们强烈建议你点击本编辑窗口上方的“命令行”菜单打开Linux的命令行，
2. -- 并通过mysql命令行工具连接并登录到服务器，
3. -- 然后在mysql的命令行工具里通过恰当的语句完成任务。
4. -- 在以后的关卡中你可以通过命令行调试或测试语句，再将正确的语句粘贴到文件编辑窗口。
6. -- 你也可以选择在以下空白行填写完成目标任务的SQL语句：
7. -- 创建2022年北京冬奥会信息系统数据库
8. create database beijing2022;
11. -- end

复制代码

第2关：创建表及表的主码约束

1. # 请在以下适当的空白处填写SQL语句，完成任务书的要求。空白行可通过回车换行添加。
2. create database TestDb;
3. use TestDb;
4. create table t_emp(
5. id int primary key,
6. name varchar(32),
7. deptId int,
8. salary float
10. );
12. /* *********** 结束 ************* */

复制代码

第3关：创建外码约束(foreign key)

1. # 请在以下空白处填写完成任务的语句，空白行可通过回车换行添加。
2. # 你也可以在命令行窗口完成任务，不过，在命令行键入的语句不会被保存。
4. CREATE DATABASE IF NOT EXISTS MyDb;  
7. USE MyDb;  
9. DROP TABLE IF EXISTS staff;  
10. DROP TABLE IF EXISTS dept;  
11. CREATE TABLE dept (  
12.     deptNo INT PRIMARY KEY,  
13.     deptName VARCHAR(32)  NULL  
14. );  
17. CREATE TABLE staff (  
18.     staffNo INT PRIMARY KEY,  
19.     staffName VARCHAR(32)  NULL,  
20.     gender CHAR(1),  
21.     dob DATE,  
22.     salary NUMERIC(8,2),  
23.     deptNo INT,  
24.     CONSTRAINT FK_staff_deptNo FOREIGN KEY (deptNo) REFERENCES dept(deptNo)   
25.         
26. );  
29. # 结束

复制代码

第4关：CHECK约束

1. # 请在以下空白处填写完成任务的语句，空白行可通过回车换行添加。
2. # 你也可以在命令行窗口完成任务，不过，在命令行键入的语句不会被保存。
7. CREATE DATABASE IF NOT EXISTS MyDb;  
8. USE MyDb;  
9. DROP TABLE IF EXISTS products;
11. CREATE TABLE products (  
12.     pid CHAR(10) PRIMARY KEY,  
13.     name VARCHAR(32) ,  
14.     brand CHAR(10),
15.     constraint CK_products_brand CHECK (brand IN ('A', 'B')),  
16.     price INT,
17.     constraint CK_products_price CHECK (price > 0)  
18. );  
19. # 结束

复制代码

第5关：DEFAULT约束

1. # 请在以下空白处填写完成任务的语句，空白行可通过回车换行添加。
2. # 你也可以在命令行窗口完成任务，不过，在命令行键入的语句不会被保存。
3. create database MyDb ;
4. use  MyDb;
5. create table hr(
6. id char(10) primary key,
7. name varchar(32) NOT NULL,
8. mz char(16) default '汉族'
9. );
13. # 结束

复制代码

第6关：UNIQUE约束

1. # 请在以下空白处填写完成任务的语句，空白行可通过回车换行添加。
2. # 你也可以在命令行窗口完成任务，不过，在命令行键入的语句不会被保存。
3. create database MyDb;
4. use MyDb;
5. create table s(
6. sno char(10) primary key,
7. name varchar(32) not null,
8. ID char(18) unique
9. );
13. # 结束

复制代码

02MySQL-表布局与完整性约束的修改(ALTER)

第1关：修改表名

1. USE TestDb1;
3. #请在以下空白处添加恰当的语句，将表名your_table更改为my_table:
4. alter table your_table rename as my_table;

复制代码

第2关：添加与删除字段

1. use MyDb;
3. #请在以下空白处添加适当的SQL代码，实现编程要求
4. #语句1：删除表orderDetail中的列orderDate
5. alter table orderDetail drop orderDate;
6. #语句2：添加列unitPrice
7. alter table orderDetail add unitPrice numeric(10,2);

复制代码

第3关：修改字段

1. use MyDb;
3. #请在以下空白处添加适当的SQL语句，实现编程要求
4. alter table addressBook modify QQ char(12);
5. alter table addressBook change weixin wechat char(12);

复制代码

第4关：添加、删除与修改约束

1. use MyDb;
2. #请在以下空白处填写适当的诘句，实现编程要求。
3. #(1) 为表Staff添加主码
4. alter table Staff add primary key(staffNo);
5. #(2) Dept.mgrStaffNo是外码，对应的主码是Staff.staffNo,请添加这个外码，名字为FK_Dept_mgrStaffNo:
6. alter table Dept add CONSTRAINT FK_Dept_mgrStaffNo FOREIGN key (mgrStaffNo) REFERENCES Staff(staffNo);
7. #(3) Staff.dept是外码，对应的主码是Dept.deptNo. 请添加这个外码，名字为FK_Staff_dept:
8. alter table Staff add CONSTRAINT FK_Staff_dept FOREIGN key (dept) REFERENCES Dept(deptNo);
9. #(4) 为表Staff添加check约束，规则为：gender的值只能为F或M；约束名为CK_Staff_gender:
10. alter table Staff add CONSTRAINT CK_Staff_gender check (gender in ('F','M'));
11. #(5) 为表Dept添加unique约束：deptName不允许重复。约束名为UN_Dept_deptName：
12. alter table Dept add CONSTRAINT UN_Dept_deptName UNIQUE (deptName);

复制代码

03MySQ-单表查询(简单比较,in,范围查询)

第1关：根本查询语句

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

复制代码

第2关：对查询的结果排序

1. -- 任务1: 写出能完成以下任务的语句：
2. -- 查询所有雇员的姓名,性别，手机号。按年龄降序排列，即年龄越大的越靠前。
3. -- 请在此处添加实现代码
4. SELECT sname, gender, mobile  
5. FROM staff  
6. ORDER BY dob ;  
9. -- EOF

复制代码

第3关：带比较条件的查询

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

复制代码

第4关：带 IN 关键字的查询

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

复制代码

第5关：带 BETWEEN AND 的范围查询

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

复制代码

04MySQL-单表查询(模式匹配,null,去重,and,or)

第1关：带 LIKE 的字符匹配查询

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

复制代码

第2关：带RLIKE的正则表达式字符匹配查询

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

复制代码

第3关：查询空值与去除重复结果

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

复制代码

第4关：带 AND 与 OR 的多条件查询

1. SELECT   
2.     manufacturer,model,price
3. FROM   
4.     products  
5. WHERE   
6.     (manufacturer = '美的' AND (model = 'KF-35GW/Y-J(E5)' OR model = 'KFR-35GW/DY-J(E5)') AND price IN (2100.00, 2200.00))  
7.     OR   
8.     (manufacturer = '格力' AND (model = 'KF-35GW/K(3538)B-HN5' OR model = 'KF-35GW/K(35316)E-HN5' OR model = 'KFR-35GW/K(3558)B-HN5' OR model = 'KFR-35GW/K(35516)E-HN5') AND price IN (2570.00, 2900.00))  
9.     OR
10.     (manufacturer='志高' AND (model='KF-35GW/B218+A5A')and price=1899)
11. ORDER BY   
12.     price ASC;

复制代码

05MySQL-单表查询(统计总和,均匀,最大,最小)

第1关：COUNT( )函数及对盘算列重命名

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

复制代码

第2关：SUM( )函数

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

复制代码

第3关：AVG( )函数

1. - 实现以下查询的SQL语句：
2. -- 查询产品表中制冷量3500W室内分体空调的平均价格,对均价取整，并命名为：“均价”。
3. -- 请在此处添加实现代码
4. select round(avg(price)) as 均价
5. from products
6. where model like 'KF%-35%';
10. -- EOF

复制代码

第4关：MAX( )函数

1. -- 实现以下查询的SQL语句：
2. -- 查询产品表中制冷量7200W室内分体落地式冷暖空调的最高价,并命名为：“最高价”。。
3. -- 请在此处添加实现代码
4. select MAX(price) as 最高价 from products where model like 'KFR-72LW%';
8. -- EOF

复制代码

第5关：MIN( )函数

1. -- 实现以下查询的SQL语句：
2. -- 查询产品表中制冷量3500W室内分体壁挂式冷暖空调的最低价,并命名为：“最低价”。
3. -- 请在此处添加实现代码
4. select min(price) as 最低价 from products where model like 'KFR-35GW%';
8. -- EOF

复制代码

06MySQL-单表查询(分组统计,限定输出行数)

第1关：使用 limit 限定查询结果的数量

1. -- 实现以下查询的SQL语句：
2. -- 查询产品表中制冷量3500W室内分体壁挂式冷暖空调的价格个最低的三个产品的厂家，型号和价格。按价格从低到高排序。。
3. -- 请在此处添加实现代码
4. select manufacturer,model,price
5. from products
6. where model like 'KF%-35GW%'
7. order by price
8. limit 3
9. ;
13. -- EOF

复制代码

第2关：分组统计查询(group by)

1. -- 实现以下查询的SQL语句：
2. -- 统计每个空调厂家，制冷功率为3500W的空调平均价格(取整)。结果表的标题分别为：“厂家”，“平均价格”。查询结果依生产厂家名称排序。
3. -- 请在此处添加实现代码
4. select manufacturer as 厂家,
5. round(avg(price),0)as 平均价格
6. from products
7. where model like 'KF%-35GW%'
8. group by manufacturer
9. order by manufacturer;
12. -- EOF

复制代码

第3关：对分组统计的结果再筛选(having)

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

复制代码

07MySQL-毗连查询(内连,外连,多表分组统计)

第1关：内毗连查询

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

复制代码

第2关：外毗连查询

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

复制代码

第3关：多表毗连查询

1. -- 实现以下查询的SQL语句：
2. -- 统计各位销售人员在2023年国庆节小长假期间(9月29日到10月6日)期间的销售业绩(即销售总金额)，列出销售人员姓名，销售额(命名为total)。依销售额从高到低排序。
3. -- 请在此处添加实现代码
4. select sname,
5. sum(quantity*price*(discount)) as total
6. from
7. sales_record,staff,products
8. where
9. sales_record.sid=staff.sid and
10. sales_record.pid=products.pid and
11. sdate between '2023-9-29' and '2023-10-6'
12. group by staff.sname
13. order by total DESC;
17. -- EOF

复制代码

08MySQL-子查询(in,exists,子查询的位置,分组统计)

第1关：带IN谓词的子查询

1. -- 任务1: 写出能完成以下查询任务的SQL语句:
2. -- 查询“郑点”没有卖过那些厂家的空调。结果依厂家名字排序。
3. -- 请在此处添加实现代码
4. select distinct manufacturer
5. from products
6. where manufacturer not in(
7.     select distinct manufacturer
8.     from sales_record
9.     join products on products.pid=sales_record.pid
10.     join staff on staff.sid=sales_record.sid
11.     where sname='郑点'
12. )
13. order by manufacturer;
18. -- EOF

复制代码

第2关：带EXISTS谓词的子查询

1. -- 任务1: 写出能完成以下查询任务的SQL语句:
2. -- 被1-9号(正式)员工都卖过的产品(空调)编号，型号，生产厂家和价格。结果依产品号排序。
3. -- 请在此处添加实现代码
4. select pid,model,manufacturer,price
5. from products
6. where exists(
7.     select 1
8.     from sales_record
9.     where sales_record.pid=products.pid
10.     and sid between 1 and 9
11.     group by pid
12.     having count(distinct sid)=9
13. )
14. order by pid;
18. -- EOF

复制代码

第3关：复杂子查询及分组统计

1. -- 任务1: 写出能完成以下查询任务的SQL语句:
2. --  统计每个厂家空调的型号数，其中单冷总型号数，冷暖总型号数。统计结果按总型号数从高到底排列。列出内容：
3. --  生产厂家：manufacturer
4. --  总型号数: model_total
5. --  单冷型号数:single_total
6. --  冷暖型号数：dual_total
7. select manufacturer,
8. count(distinct model) as model_total,
9. sum(case when model LIKE 'KF%' and model not like 'KFR%' then 1 else 0 END) as single_total,
10. sum(case when model like 'KFR%' then 1 else 0 END) as dual_total
11. from products
12. group by manufacturer
13. order by model_total DESC;
20. -- EOF

复制代码

12MySQL-基于新冠疫情常态化管理的数据查询(SELECT)


第1关：人流量大于30的地点

1. use covid19mon;
3. -- 1) 查询累计人流量大于30的地点名称和累计人流量，累积人流量请用visitors作标题名称。
4. --    查询结果按照人流量从高到低排序，人流量相同时，依地点名称顺序排序。
5. --    （注意：同一人多次逛同一地点，去几次算几次）
6. --    请用一条SQL语句实现该查询：
7. select location_name,count(*) as 'visitors'
8. from location,itinerary,person
9. where p_id=person.id and loc_id=location.id
10. group by location_name
11. having visitors>30
12. order by visitors desc,location_name;
16. /*  end  of  your code  */

复制代码

第2关：每个隔离点正在进行隔离的人数

1. use covid19mon;
3. -- 2) 查询每个隔离地及该地正在进行隔离的人数，以number为隔离人数的标题.
4. --    查询结果依隔离人数由多到少排序。人数相同时，依隔离地点名排序。
5. --    请用一条SQL语句实现该查询：
6. select isolation_location.location_name,count(*) as 'number'
7. from isolation_record,isolation_location,person
8. where isolation_location.id=isol_loc_id and person.id=p_id and isolation_record.state in (1)
9. group by location_name
10. order by number desc;
13. /*  end  of  your code  */

复制代码

第3关：接续行程

1. use covid19mon;
3. -- 2) 查询行程表中所有属于同一个人的接续行程信息。输出内容包括：
4. -- 人员编号,姓名,重合时间,起始地点id,起始地点,结束地点id,结束地点。
5. -- 查询结果依人员编号排序。
6. -- 请用一条SQL语句实现该查询：
7. select     person.id,fullname,telephone,
8.     i1.e_time AS reclosing_time,
9.     i1.loc_id AS loc1,
10.     l1.location_name AS address1,
11.     i2.loc_id AS loc2,
12.     l2.location_name AS address2
13. from itinerary AS i1
14. JOIN itinerary AS i2 ON i1.e_time = i2.s_time and i1.p_id = i2.p_id
15. JOIN location AS l1 ON l1.id = i1.loc_id
16. JOIN location AS l2 ON l2.id = i2.loc_id
17. join person on person.id = i1.p_id
18. where person.id > 30
19. order by person.id,reclosing_time;

复制代码

第4关：充珉瑶和贾涵山的行程情况

1. -- 4) 查询充珉瑶和贾涵山的行程情况。查询结果包括：姓名、电话、到过什么地方（地名），何时到达，何时离开 。
2. --  列名原样列出，不必用别名。查询结果依人员编号降序排序。
4. --    请用一条SQL语句实现该查询：
5. use covid19mon;
6. select fullname,telephone,location_name,s_time,e_time
7. from person left outer join itinerary on person.id=itinerary.p_id
8. left outer join location on location.id=itinerary.loc_id
9. where fullname in ('充珉瑶','贾涵山')
10. order by person.id desc,s_time;
15. /*  end  of  your code  */

复制代码

第5关：地名中带有‘店’字的地点名称

1. -- 5) 查询地名中带有‘店’字的地点编号和名称。查询结果按地点编号排序。
2. --    请用一条SQL语句实现该查询：
3. use covid19mon;
4. select * from location where location_name like '%店%' order by id;
9. /*  end  of  your code  */

复制代码

第6关：确诊者的打仗者

1. -- 6) 新发现一位确诊者，已知他在2021.2.2日20:05:40到21:25:40之间在“活动中心”逗留，
2. --    凡在此间在同一地点逗留过的，视为接触者，请查询接触者的姓名和电话。查询结果按姓名排序.
3. --    请用一条SQL语句实现该查询：
4. use covid19mon;
5. select fullname,telephone
6. from person,itinerary,location
7. where person.id=itinerary.p_id and location.id=itinerary.loc_id
8. and (e_time>='2021-2-2 20:05:40' and s_time<='2021-2-2 21:25:40')
9. and location_name='活动中心'
10. order by fullname;
14. /*  end  of  your code  */

复制代码

第7关：仍在使用的隔离点

1. -- 7) 查询正在使用的隔离点名,查询结果按隔离点的编号排序。
2. --    请用一条SQL语句实现该查询：
3. use covid19mon;
4. select location_name
5. from isolation_location
6. where location_name in (select location_name
7. from isolation_record
8. where id=isol_loc_id and state not in(2,3)
9. and location_name not in('斯威特快捷酒店')
10. order by isolation_record.id
11. );
15. /*  end  of  your code  */

复制代码

第8关：查询有出行记录的职员

1. -- 8) 用一条带exists关键字的SQL语句查询前30位有出行记录的人员姓名和电话。查询结果按照人员编号排序。
2. --    请用一条SQL语句实现该查询：
3. use covid19mon;
4. select fullname,telephone
5. from person
6. where exists(select *
7. from itinerary
8. where person.id=itinerary.p_id
9. order by person.id
10. )
11. limit 30;
16. /*  end  of  your code  */

复制代码

第9关：没有去过“Today便利店“的人数

1. -- 9) 写一条带NOT EXISTS 子查询的SQL语句实现下述查询要求：
2. --   查询人员表中没有去过地点“Today便利店”的人数。请给统计出的人数命名为number。
3. --   请用一条SQL语句实现该查询：
4. use covid19mon;
5. SELECT COUNT(*) AS number
6. FROM person p
7. WHERE NOT EXISTS (
8.     SELECT 1
9.     FROM itinerary i
10.     INNER JOIN location l ON i.loc_id = l.id
11.     WHERE l.location_name = 'Today便利店'
12.       AND i.p_id = p.id
13. );
20. /*  end  of  your code  */

复制代码

第10关：去过全部地点的职员

1. use covid19mon;
2. SELECT fullname
3. FROM person
4. WHERE id IN (
5.     SELECT p_id
6.     FROM itinerary
7.     GROUP BY p_id
8.     HAVING COUNT(DISTINCT loc_id) = (SELECT COUNT(*) FROM location)
9. )
10. ORDER BY fullname;

复制代码

第11关：隔离点的近况视图

1. use covid19mon;
2. CREATE VIEW isolation_location_status AS
3. SELECT
4.     isolation_location.id,
5.     isolation_location.location_name,
6.     isolation_location.capacity,
7.     COUNT(CASE WHEN isolation_record.state = 1 THEN 1 END) AS occupied
8. FROM
9.     isolation_location
10. LEFT JOIN
11.     isolation_record ON isolation_location.id = isolation_record.isol_loc_id
12. GROUP BY
13.     isolation_location.id, isolation_location.location_name, isolation_location.capacity;

复制代码

第12关：各隔离点的剩余房间数

1. use covid19mon;
2. SELECT
3.     location_name,
4.     capacity - occupied AS available_rooms
5. FROM
6.     isolation_location_status
7. ORDER BY
8.     id;

复制代码

第13关：与无症状感染者靳宛儿有过打仗的人

1. use covid19mon;
2. SELECT
3.     p.fullname,
4.     p.telephone
5. FROM
6.     person AS p
7. INNER JOIN
8.     itinerary AS i ON p.id = i.p_id
9. INNER JOIN
10.     itinerary AS j ON i.loc_id = j.loc_id
11. WHERE
12.     i.p_id <> j.p_id
13.     AND i.s_time < j.e_time
14.     AND i.e_time > j.s_time
15.     AND j.p_id = (SELECT id FROM person WHERE fullname = '靳宛儿')
16. ORDER BY
17.     p.fullname;

复制代码

第14关：每个地点发生的密切打仗者人数

1. use covid19mon;
2. select location_name,count(*) as close_contact_number
3. from location,close_contact
4. where close_contact.loc_id=location.id
5. group by location_name
6. order by close_contact_number desc,location_name;

复制代码

第15关：感染人数最多的人

1. use covid19mon;
2. select case_p_id,fullname,count(*) as infected_number
3. from close_contact,person
4. where case_p_id=person.id
5. group by case_p_id,fullname
6. ORDER BY infected_number desc
7. limit 1;

复制代码

第16关：行程记录最频繁的3个人

1. use covid19mon;
2. select fullname,count(*) as record_number
3. from person,itinerary
4. where person.id=p_id
5. and ((s_time>='2021-02-02 10:00:00' and s_time<='2021-02-02 14:00:00') or(e_time>='2021-02-02 10:00:00' and e_time<='2021-02-02 14:00:00'))
6. group by fullname
7. order by record_number desc,fullname
8. limit 3;

复制代码

 第17关：房间数第2多的隔离点

1. use covid19mon;
2. select location_name,capacity
3. from isolation_location
4. where capacity<(select capacity from isolation_location
5. order by capacity desc limit 1)
6. limit 1;

复制代码

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

第1关：插入多条完整的客户信息

1. use finance1;
2. -- 用insert语句向客户表(client)插入任务要求的3条数据:
3. insert into client(c_id,c_name,c_mail,c_id_card,c_phone,c_password)
4. values
5. (1,'林惠雯','960323053@qq.com','411014196712130323','15609032348','Mop5UPkl'),
6. (2,'吴婉瑜','1613230826@gmail.com','420152196802131323','17605132307','QUTPhxgVNlXtMxN'),
7. (3,'蔡贞仪','252323341@foxmail.com','160347199005222323','17763232321','Bwe3gyhEErJ7');

复制代码

第2关：插入不完整的客户信息

1. use finance1;
2. -- 已知33号客户部分信息如下:
3. -- c_id(编号):33
4. -- c_name(名称):蔡依婷
5. -- c_phone(电话):18820762130
6. -- c_id_card(身份证号):350972199204227621
7. -- c_password(密码):MKwEuc1sc6
9. -- 请用一条SQL语句将这名客户的信息插入到客户表(client)：
10. insert into client(c_id,c_name,c_phone,c_id_card,c_password)
11. values
12. (33,'蔡依婷','18820762130','350972199204227621','MKwEuc1sc6');

复制代码

第3关：批量插入数据

1. use finance1;
2. -- 已知表new_client保存了一批新客户信息，该表与client表结构完全相同。请用一条SQL语句将new_client表的全部客户信息插入到客户表(client):
3. insert into client(c_id,c_name,c_mail,c_id_card,c_phone,c_password)
4. select c_id,c_name,c_mail,c_id_card,c_phone,c_password
5. from new_client;

复制代码

第4关：删除没有银行卡的客户信息

1. use finance1;
2. -- 请用一条SQL语句删除client表中没有银行卡的客户信息：
3. delete from client
4. where c_id not in(
5.     select b_c_id from bank_card
6. );

复制代码

第5关：冻结客户资产

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

复制代码

第6关：毗连更新

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

复制代码

16MySQL-视图

第1关：创建全部保险资产的详细记录视图

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

复制代码

第2关：基于视图的查询

1. select c_name,c_id_card,sum(i_amount*pro_quantity) as insurance_total_amount,sum(pro_income) as insurance_total_revenue
3. from v_insurance_detail
5. group by c_id_card
7. order by insurance_total_amount desc;

复制代码

3-5章选择填空

• 1、关于SQL 语言，下列说法精确的是（C）
  A、数据控制功能不是SQL 语言的功能之一
  B、SQL 采用的是面向记录的操作方式，以记录为单位进行操作
  C、SQL 是非过程化的语言，用户无须指定存取路径
  D、SQL 作为嵌入式语言语法与独立的语言有较大差异
  
• 2、对表中数据进行删除的操作是（A）
  A、DELETE
  B、DROP
  C、ALTER
  D、UPDATE
  
• 3、数据库中创建索引的目的是为了（B）
  A、加快建表速度
  B、加快存取速度
  C、进步安全性
  D、节省存储空间
  
• 4、
  视图是数据库体系三级模式中的（A）
  A、外模式
  B、模式
  C、内模式
  D、模式映象
  
• 5、下列说法不精确的是（D）
  A、根本表和视图一样，都是关系
  B、可以使用SQL 对根本表和视图进行操作
  C、可以从根本表或视图上定义视图
  D、根本表和视图中都存储数据
  
• 6、视图不仅可以从单个根本表导出，还可以从多个根本表导出。
  精确
  
• 7、不是全部的视图都可以进行更新，但视图都可以进行插入。
  错误
  
• 8、SELECT 子句中的目标列可以是表中的属性列，也可以是表达式。
  精确
  
• 9、在SQL 语句中表达某个属性X 为空，可以使用WHERE X = NULL 。
  错误
  
• 10、SQL 语句中逻辑运算符AND 和OR 的优先级是一样的。
  错误
  
• 11、使用ANY 或ALL 谓词时必须与比较运算符同时使用。
  精确
  
• 12、
  SQL 语言具有▁▁▁▁▁、▁▁▁▁▁、▁▁▁▁▁和数据控制的功能
  填空1答案：数据定义
  填空2答案：数据查询
  填空3答案：数据使用
  
• 13、
  SQL 语句中用来消除重复的关键词是▁▁▁▁▁
  填空1答案：DISTINCT
  
• 14、
  若一个视图是从单个根本表导出的，并且只是去掉了根本表的某些行和某些列，但保留了主码，这类视图称为▁▁▁▁▁
  填空1答案：行列子集视图
  
• 15、
  SQL 语言的数据定义功能包括▁▁▁▁▁、表定义、视图定义和▁▁▁▁▁等
  填空1答案：模式定义
  填空2答案：索引定义
  
• 16、强制存取控制策略是TCSEC/TDI 哪一级安全级别的特色（B ）
  A、C1
  B、C2
  C、B1
  D、B2
  
• 17、SQL 的GRANT 和REVOKE 语句可以用来实现（A）
  A、DAC
  B、MAC
  C、数据库角色创建
  D、数据库审计
  
• 18、在强制存取控制机制中，当主体的允许证级别等千客体的密级时，主体可以对客体进行如下操作（D）
  A、读取
  B、写入
  C、不可操作
  D、读取、写入
  
• 19、
  数据库安全技能包括用户身份辨别、▁▁▁▁▁、▁▁▁▁▁、▁▁▁▁▁和数据加密等
  填空1答案：自主存取控制和强制存取控制
  填空2答案：视图
  填空3答案：审计
  
• 20、
  在数据加密技能中，原始数据通过某种加密算法变换为不可直接辨认的格式，称为▁▁▁▁▁。
  填空1答案：密文
  
• 21、
  数据库角色实际上是一组与数据库操作相干的各种▁▁▁▁▁。
  填空1答案：权限
  
• 22、
  在对用户授予列INSERT权限时，肯定要包含对▁▁▁▁▁的INSERT权限，否则用户的插入会因为空值被拒绝。除了授权的列，其他列的值大概取▁▁▁▁▁，大概为▁▁▁▁▁。
  填空1答案：主码
  填空2答案：空值
  填空3答案：默认值
  
• 23、定义关系的主码意味着主码属性（D）
  A、必须唯－
  B、不能为空
  C、唯一且部门主码属性不为空
  D、唯一且所可主码属性不为空
  
• 24、关于语句CREATE TABLE R(no int,sum int CHECK(sum> O} ）和CREATE TABLE R(no int,sum int,CHECK(sum > O)) ，以下说法不精确的是（C）
  A、两条语句都是合法的
  B、前者定义了属性上的约束条件，后者定义了元组上的约束条件
  C、两条语句的约束结果不一样
  D、当sum属性改变时检查，上述两种CHECK约束都要被检查
  
• 25、下列说法精确的是（A）
  A、使用ALTER TABLE ADD CONSTRAINT 可以增长基于元组的约束
  B、假如属性A 上定义了UNIQUE 约束，则A 不可以为空
  C、假如属性A 上定义了外码约束，则A 不可以为空
  D、不能使用ALTER TABLE ADD CONSTRAINT 增长主码约束
  
• 26、
  在CREATE TABLE 时，用户定义的完整性可以通过▁▁▁▁▁、▁▁▁▁▁、▁▁▁▁▁等子句实现。
  填空1答案：NOT NULL
  填空2答案：UNIQUE
  填空3答案：CHECK
  
• 27、
  关系R的属性A参照引用关系T的属性A,T的某条元组对应的A属性值在R中出现，当要删除T的这条元组时，体系可以采用的策略包括▁▁▁▁▁、▁▁▁▁▁、▁▁▁▁▁。
  填空1答案：拒绝执行
  填空2答案：级联删除
  填空3答案：设为空值
  
• 28、
  定义数据库完整性一般是由SQL的▁▁▁▁▁语句实现的。
  填空1答案：DDL
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。