MySQL头歌实践教学平台实训作业（有目录）

已于 2025-01-19 18:15:16 修改
阅读量546 收藏 16
点赞数 6
分类专栏： 数据库系统原理 文章标签： mysql 数据库
于 2025-01-19 17:29:58 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Parry_Guopeiyuan/article/details/145244895
版权

目录

MySQL数据定义与操作实战

初始MySQL

第一关 创建数据库

mysql -uroot -p123123 -h127.0.0.1

create database MyDb;

第二关 创建表

mysql -uroot -p123123 -h127.0.0.1

create database TestDb;

use TestDb;

create table t_emp (id int,
name varchar(32),
deptId int,
salary float);

第三关 使用主键约束

mysql -uroot -p123123 -h127.0.0.1

create database MyDb;

use MyDb;

create table t_user1(
userId INT PRIMARY KEY,
name VARCHAR(32),
password VARCHAR(11),
phone VARCHAR(11),
email VARCHAR(32));

create table t_user2(
name VARCHAR(32),
phone VARCHAR(11),
email VARCHAR(32),
PRIMARY KEY(name,phone));

第四关 外键约束

mysql -uroot -p123123 -h127.0.0.1

create database TestDb;

use TestDb;

 CREATE TABLE t_class
(
    id INT  PRIMARY KEY,
    name VARCHAR(22) 
);

CREATE TABLE t_student
(
    id INT  PRIMARY KEY,
    name VARCHAR(22) ,
    classId int,
    CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id)
);

第五关 添加常用约束

mysql -uroot -p123123 -h127.0.0.1

CREATE DATABASE MyDb;

USE MyDb;

CREATE TABLE t_user
(
id INT  PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32) NOT NULL UNIQUE,
sex VARCHAR(4) DEFAULT '男'
)DEFAULT CHARSET=utf8;

单表查询（一）

第一关 基本查询语句

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary ##########
select Name,Salary from tb_emp;

########## retrieving all the table ##########
select * from tb_emp;

########## End ##########

第二关 带 IN 关键字的查询

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary with IN statement ##########
SELECT Name,Salary FROM tb_emp WHERE Id NOT IN (1);

########## End ##########

第三关 带 BETWEEN AND 的范围查询

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary with BETWEEN AND statement ##########
SELECT Name,Salary FROM tb_emp 
WHERE Salary BETWEEN 3000 AND 5000;

########## End ##########

单表查询（二）

第一关 带 LIKE 的字符匹配查询

USE Company;

######### Begin #########
SELECT Name,Salary FROM tb_emp WHERE Name LIKE "C%";

######### End #########

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

USE Company;

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId IS NULL;

######### End #########

######### Begin #########
SELECT DISTINCT Name FROM tb_emp;

######### End #########

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

USE Company;

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId=301 AND Salary > 3000;

######### End #########

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId=301 OR DeptId=303;

######### End #########

单表查询（三）

第一关 对查询结果进行排序

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
select * from tb_score where class_id = 1 order by score desc;

########## End ##########

第二关 分组查询

USE School;
#请在此处添加实现代码
########## Begin ##########

########## 对班级名称进行分组查询 ##########
SELECT * FROM tb_class GROUP BY class_id;

########## End ##########

第三关 使用 LIMIT 限制查询结果的数量

USE School;
#请在此处添加实现代码
########## Begin ##########

########## 查询班级中第2名到第5名的学生信息 ##########
SELECT * FROM tb_score order by score desc LIMIT 1,4;

########## End ##########

数据库和表的基本操作（一）

第一关 查看表结构与修改表名

USE Company;
########## Begin ##########

########## modify the table name ##########
ALTER TABLE tb_emp RENAME jd_emp;

########## show tables in this database ##########
show tables;

########## describe the table ##########
describe jd_emp;

########## End ##########

第二关 修改字段名与字段数据类型

USE Company;
#请在此处添加实现代码
########## Begin ##########

########## change the column name ##########
ALTER TABLE tb_emp change Id prod_id int(11);

########## change the data type of column ##########
ALTER TABLE tb_emp MODIFY Name varchar(30);

########## End ##########

DESCRIBE tb_emp;

第三关 添加与删除字段

USE Company;
#请在此处添加实现代码
########## Begin ##########

########## add the column ##########
ALTER TABLE tb_emp ADD Country varchar(20) AFTER Name;
########## delete the column ##########
ALTER TABLE tb_emp DROP Salary;

########## End ##########

DESCRIBE tb_emp;

第四关 修改字段的排列位置

USE Company;
#请在此处添加实现代码
########## Begin ##########

########## modify the column to top ##########
ALTER TABLE tb_emp MODIFY Name varchar(25) FIRST;

########## modify the column to the rear of another column ##########
ALTER TABLE tb_emp MODIFY DeptId int(11) AFTER Salary;

########## End ##########

DESCRIBE tb_emp;

第五关 删除表的外键约束

USE Company;
#请在此处添加实现代码
########## Begin ##########

########## delete the foreign key ##########
ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;

########## End ##########
SHOW CREATE TABLE tb_emp \G;

数据库和表的基本操作（二）

第一关 插入数据

USE Company;
#请在此处添加实现代码
########## Begin ##########

########## bundle insert the value #########
INSERT INTO tb_emp(Id,Name,DeptId,Salary)
VALUES (1,"Nancy",301,2300.00),
(2,"Tod",303,5600.00),(3,"Carly",301,3200.00);

########## End ##########
SELECT * FROM tb_emp;
########## End ##########

第二关 更新数据

USE Company;
#请在此处添加实现代码
########## Begin ##########
########## update the value ##########
update tb_emp
set Name="Tracy",DeptId=302,Salary=4300.00
where Id = 3;
########## End ##########
SELECT * FROM tb_emp;

第三关 删除数据

USE Company;
#请在此处添加实现代码
########## Begin ##########

########## delete the value ##########
DELETE FROM tb_emp
WHERE Salary>3000;

########## End ##########

SELECT * FROM tb_emp;

连接查询

第一关 内连接查询

USE School;

########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className from tb_student join tb_class on tb_class.id = tb_student.class_id; 

########## End ##########

第二关 外连接查询

USE School;
########## 使用左外连接查询所有学生姓名和对应的班级 ##########

#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className
from tb_class right join tb_student on
tb_class.id=tb_student.class_id;

########## End ##########

########## 使用右外连接查询所有学生姓名和对应的班级 ##########
select tb_student.name as studentName,tb_class.name as className
from tb_class left join tb_student
on tb_class.id=tb_student.class_id;
#请在此处添加实现代码
########## Begin ##########

########## End ##########

########## End ##########

第三关 复合条件连接查询

USE School;

########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select s1.name as studentName,score,
s2.name as className from tb_student as s1,
tb_class as s2 where s1.class_id=s2.id and
s1.score>90 order by score desc;
########## End ##########

子查询

第一关 带比较运算符的子查询

USE Company;
#请在此处添加实现代码
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄
select name,age from tb_emp where age>(select avg(age) from tb_emp);

########## End ##########

第二关 关键字子查询

USE Company;
#请在此处添加实现代码
########## Begin ##########
#1.使用 ALL 关键字进行查询
SELECT position,salary FROM tb_salary WHERE salary >
ANY(SELECT max(salary) FROM tb_salary where position="java");
#2.使用 ANY 关键字进行查询
SELECT position,salary FROM tb_salary WHERE salary >
ANY(SELECT min(salary) from tb_salary where position="java");
#3.使用 IN 关键字进行查询
select position,salary from tb_salary where position in("java");
########## End ##########

复杂查询（一）

第一关 交换工资

#请在此添加实现代码
########## Begin ##########
UPDATE tb_Salary
SET
sex = CASE sex WHEN "m"  THEN "f"
ELSE "m"
END;

########## End ##########

第二关 换座位

#请在此添加实现代码
########## Begin ##########
SELECT if(Id%2=0,Id-1,if(Id=5,Id,Id+1)) AS id,name
FROM tb_Seat ORDER BY Id;
########## End ##########

第三关 分数排名

#请在此添加实现代码
########## Begin ##########
select Score,(select count(distinct score) from score where score >=s.score) as Rank
from score as s order by Score desc;
select Score,(select count(*) from score as s2 where s2.score >s1.score)+1 as Rank
from score as s1 order by Rank;

########## End ##########

第四关 体育馆的人流量

#请在此添加实现代码
########## Begin ##########
select distinct a.* from gymnasium a,gymnasium b,gymnasium c
where a.visitors_flow>=100 and b.visitors_flow>=100
and c.visitors_flow>=100
and(
    (a.id = b.id-1 and b.id = c.id - 1)or
    (a.id = b.id-1 and a.id = c.id + 1)or
    (a.id = b.id+1 and b.id = c.id + 1)
)
order by a.id;

########## End ##########

第五关 统计总成绩

#请在此添加实现代码
########## Begin ##########
select t1.classname,t1.chinese,t2.maths
from(select c.classname classname,sum(s.chinese)
chinese from tb_class c,tb_score s where c.stuname=
s.name and s.chinese>=60 group by c.classname)t1,
(select c.classname classname,sum(s.maths)maths from tb_class c,tb_score s
where c.stuname=s.name and s.maths>=60 group by c.classname)t2
where t1.classname=t2.classname;

########## End ##########

复杂查询（二）

第一关 查询学生平均分

#请在此添加实现代码
########## Begin ##########
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2)as avg_score from student b 
inner join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score <60
union 
select a.s_id,a.s_name,0 as avg_score from student a 
where a.s_id not in (select distinct s_id from score);

########## End ##########

第二关 查询修课相同学生信息

#请在此添加实现代码
########## Begin ##########
create view temp as(select s_id,group_concat(c_id)as c from score group by s_id);
select * from student where s_id in(select s_id from temp where c=(select c from temp where s_id="01")and s_id<>"01");
########## End ##########

第三关 查询各科成绩并排序

#请在此添加实现代码
########## Begin ##########
select a.*,count(b.s_score)+1 rank from score a left join score b 
on a.c_id = b.c_id and a.s_score <b.s_score
group by a.c_id,a.s_id
order by a.c_id,count(b.s_score);

########## End ##########

第四关 查询张老师课程成绩最高的学生信息

#请在此添加实现代码
########## Begin ##########
select a.*,b.s_score,b.c_id,c.c_name from student a 
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN course c ON b.c_id = c.c_id
where b.c_id = (select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name="张三")
and b.s_score in (select MAX(s_score)from score where c_id="02");

########## End ##########

第五关 查询两门课程不及格同学信息

#请在此添加实现代码
########## Begin ##########
select a.s_id,a.s_name,ROUND(AVG(b.s_score))
avg_score from student a 
inner join score b on a.s_id = b.s_id
where a.s_id in(
    select s_id from score where s_score<60 GROUP BY s_id having count(*)>=2
)
GROUP BY a.s_id,a.s_name;

########## End ##########

使用聚合函数查询

第一关 COUNT( )函数

USE School;
#请在此处添加实现代码
########## Begin ##########

########## 查询该表中一共有多少条数据 ##########
select count(*) from tb_class;

########## 查询此表中367班有多少位学生 ##########
select classid,count(*) from tb_class where classid=367;

########## End ##########

第二关 SUM( )函数

USE School;
#请在此处添加实现代码
########## Begin ##########

########## 查询所有学生总分数 ##########
select sum(score) from tb_class;

########## 查询学生语文科目的总分数 ##########
select course,sum(score) from tb_class where course="语文";

########## End ##########

第三关 AVG( )函数

USE School;
#请在此处添加实现代码
########## Begin ##########

########## 查询学生语文科目的平均分数 ##########
select course,avg(score)from tb_class where course="语文";

########## 查询学生英语科目的平均分数 ##########
select course,avg(score) from tb_class where course="英语";

########## End ##########

第四关 MAX( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询语文课程中的最高分数 ##########
select course,max(score) from tb_class where course="语文";

########## 查询英语课程中的最高分数 ##########
select course,max(score) from tb_class where course="英语";

########## End ##########

第五关 MIN( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询语文课程中的最低分数 ##########
select course,min(score) from tb_class where course="语文";

########## 查询英语课程中的最低分数 ##########
select course,min(score) from tb_class where course="英语";

########## End ##########

其他函数的使用

第一关 字符函数

#请在此添加实现代码
########## Begin ##########
select CONCAT(UPPER(SUBSTR(Name,1,1)),LOWER(SUBSTR(Name,2,LENGTH(Name)))) as Name from employee;
########## End ##########

第二关 数学函数

#请在此添加实现代码
########## Begin ##########
update Score set s_score=TRUNCATE(s_score-(round(sqrt((power(4,4)-power(3,3))/power(2,2)),2)),2);
########## End ##########

第三关 日期时间函数和流程控制类函数

#请在此添加实现代码
########## Begin ##########

##########  查询学生出生年份及年龄 ##########
select year(s_birth) year,'2019-01-01'-s_birth '年龄'
from Student;
##########  查询课程的最高分、最低分、平均分和及格率 #########
select c.c_id '课程id',
c_name '课程名',
max(s_score) '最高分',
min(s_score) '最低分',
round(avg(s_score),2) '平均分',
round((count(s_score >= 60 or null)/count(s_score)) * 100,2)  '及格率'
from Score s,Course c
where s.c_id=c.c_id
group by s.c_id;
########## End ##########

第四关 自定义函数

#请在此添加实现代码
########## Begin ##########
delimiter //
create function fn_three_max(param_1 int,param_2 int,param_3 int) RETURNS int
BEGIN
        DECLARE max_val int DEFAULT 0;
        if param_1 > param_2 then
            set max_val=param_1;
        else 
            set max_val=param_2;
        end if;
        if param_3 > max_val then
            set max_val=param_3;
        end if;
        return max_val;
END
//
########## End ##########

分组选择数据

第一关 GROUP BY 与 聚合函数

USE School;

#请在此处添加实现代码
########## Begin ##########

#1.查询表中2,3,4年级中分别男女的总人数
select gradeId,sex,count(*)
from student where gradeId in (2,3,4)
group by gradeId,sex;

########## End ##########

第二关 使用 HAVING 与 ORDER BY

USE School;

#请在此处添加实现代码
########## Begin ##########

#1.查询表中至少有两门课程在90分以上的学生信息
select sno,count(*)from tb_grade
where score >=90
group by sno having count(pno) >= 2;

#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息
select sno,avg(score) from tb_grade where sno 
in(select sno from tb_grade where score >=95 and pno = "语文")
group by sno having avg(score) >=90;

########## End ##########

视图

use School;

#请在此处添加实现代码
########## Begin ##########

#1.创建单表视图
CREATE VIEW stu_view
AS
select math,chinese,math+chinese
FROM student;
#2.创建多表视图
CREATE VIEW stu_classes
AS
select student.stu_id,[student.name](http://student.name/),stu_info.classes
FROM student,stu_info
WHERE student.stu_id=stu_info.stu_id;
########## End ##########

索引

use School;
#请在此处添加实现代码
########## Begin ##########

#1.创建名为pk_student的主键索引
create table student(
stu_id int not null,
name varchar(25) not null,
age int not null,
sex char(2) not null,
classes int not null,
grade int not null,
primary key(stu_id)
);

#2.创建名为idx_age的普通索引
create index idx_age on student(age);
#3.创建名为uniq_classes的唯一索引
create unique index uniq_classes on student(classes);
#4.创建名为idx_group的组合索引
alter table student add index idx_group(name,sex,grade);
########## End ##########

分页和索引

第一关 MySQL 分页查询

USE Products;
#请在此处添加实现代码
########## Begin ##########

#1.分页查询
select prod_id from products where prod_id >(select prod_id from products limit 4,1) limit 5;
#2.用子查询优化分页查询语句
select prod_id from products where prod_id >(select prod_id from products limit 9,1) limit 5;
########## End ##########

第二关 索引（单列索引）

USE Students;
#请在此处添加实现代码
########## Begin ##########

#1.创建student表结构并且设置id为主键索引

CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
score int(10),
PRIMARY KEY (id)
);
#2.对name建立唯一索引
CREATE UNIQUE  INDEX name_index ON `student`(`name`);
#3.对score建立普通索引
CREATE INDEX score_index ON `student`(`score`);

SHOW INDEX FROM student;
########## End ##########

第三关 索引（组合索引）

USE Person;
#请在此处添加实现代码
########## Begin ##########

#1.增加组合索引
ALTER TABLE person ADD INDEX name_city_score (name,age,address);

########## End ##########
SHOW INDEX FROM person;

删除重复数据

第一关 利用主键删除

#请在此添加实现代码
########## Begin ##########
delete from users where id in (
    select * from (
        select id from users where user_name 
        in (
            select user_name from users group by user_name having count(1) > 1
            ) 
        and id not in (
            select min(id) from users group by user_name having count(1) > 1
            )
    ) as stu_repeat_copy
);
########## End ##########

第二关 复杂重复数据删除

#请在此添加实现代码
########## Begin ##########
update users b join (
  select user_name,group_concat(
    distinct SUBSTRING_INDEX(SUBSTRING_INDEX(mobile,',',t.id),',',-1)
  ) mobile from (
    select user_name,mobile,length(concat(mobile,','))-length(replace(mobile,',','')) size from users
  ) a inner join tb_sequence t on a.size>=t.id group by a.user_name
) c on b.user_name = c.user_name set b.mobile = c.mobile;
########## End ##########

查询、索引和完整性

第一关 基本查询的学习

//请在下面补齐查询一的MySQL语句
/***begin***/
select ename,eid,sex from emp
where did in
(select did from dept
where dname='cwb'
)
/***end***/
and
birth<=all
(select birth from emp
where did in
(select did from dept
where dname='yfb'
)
);

//请在下面输入查询二的MySQL语句
/***begin***/
select ename,income,outcome
from emp,sal,dept
where emp.eid=sal.eid and
emp.did=dept.did and
dname='cwb' and income>5200;

/***end***/

第二关 深入学习查询语句

//请在下面输入查询一的MySQL语句
/***begin***/

select count(eid)
from emp
where did=
(select did
from dept
where dname='cwb');

/***end***/

//请在下面输入查询二的MySQL语句
/***begin***/

select count(eid)
from emp
group by did;

/***end***/

//请在下面输入查询三的MySQL语句
/***begin***/

select emp.ename
from emp,sal
where emp.eid=sal.eid
order by income;

/***end***/

/***end***/

第三关 视图的创建和使用

//请在下面输入创建cx_sal的视图的MySQL语句
/***begin***/

create or replace view cx_sal
as
select ename,income,outcome
from emp,sal,dept
where emp.eid=sal.eid and
emp.did=dept.did and
dname='cwb';
/***end***/

//请在下面输入查询财务部雇员薪水情况视图的MySQL语句
/***begin***/
select * from cx_sal;
/***end***/

第四关 索引与完整性

//请在下面输入创建索引的MySQL语句
/***begin***/
create index pk_xs_bak
on emp(eid);

/***end***/

//请在下面输入实现域完整性的MySQL语句
/***begin***/

alter table emp
add(constraint ch_tel check(tel between 0 and 9));
/***end***/

//请在下面输入实现实体完整性的MySQL语句
/***begin***/
alter table dept
add constraint un_dept unique(dname);

/***end***/

//请在下面输入实现参照完整性的MySQL语句
/***begin***/

alter table emp
add constraint sal_id foreign key(eid)
references sal(eid);

/***end***/

MySQL数据管理技术实战

存储过程

USE mydb;
#请在此处添加实现代码
########## Begin ##########
drop procedure if exists mydb.GetCustomerLevel;
delimiter $$
create PROCEDURE GetCustomerLevel(in p_customNumber int(11),out p_customerLevel varchar(10))
Begin
declare levels int;
select creditlimit into levels from customers where customerNumber=p_customNumber;
if levels <5000 then
set p_customerLevel = 'SILVER';
elseif levels <10000 then
set p_customerLevel = 'GOLD';
else
set p_customerLevel = 'PLATINUM';
end if;
select p_customNumber as customerNumber,p_customerLevel;
End $$
delimiter ;
########## End ##########

安全性控制

第一关 用户和权限

请填写语句，完成以下功能：
#(1) 创建用户tom和jerry，初始密码均为'123456'；
create user tom identified by '123456';
create user jerry identified by '123456';
#(2) 授予用户tom查询客户的姓名，邮箱和电话的权限,且tom可转授权限；
grant select(c_name,c_mail,c_phone) on client to tom with grant option;
#(3) 授予用户jerry修改银行卡余额的权限；
grant update(b_balance) on bank_card to jerry;
#(4) 收回用户Cindy查询银行卡信息的权限。
revoke select on bank_card from Cindy;

第二关 用户、角色与权限

请填写语句，完成以下功能：
(1) 创建角色client_manager和fund_manager；
create role client_manager,fund_manager;
(2) 授予client_manager对client表拥有select,insert,update的权限；
grant select,insert,update on client to client_manager;
(3) 授予client_manager对bank_card表拥有查询除银行卡余额外的select权限；
grant select(b_number,b_type,b_c_id) on bank_card to client_manager;
(4) 授予fund_manager对fund表的select,insert,update权限；
grant select,insert,update on fund to fund_manager;
(5) 将client_manager的权限授予用户tom和jerry；
grant client_manager to tom,jerry;
(6) 将fund_manager权限授予用户Cindy.
grant fund_manager to Cindy;

授权与撤销授权

第一关 数据库授权

##########  开始编写 SQL  ##########
set password for casual_user@'localhost' = password('123456');
-- grant SELECT,INSERT,UPDATE on . to casual_user@'localhost' identified by "123456";
grant SELECT ,INSERT, UPDATE on *.* to casual_user@'localhost';
FLUSH PRIVILEGES;

第二关 数据库撤销权限

##########  开始编写 SQL  ##########

revoke all on mydb1.table1 from 'user1'@'localhost';

revoke all on mydb1.table2 from 'user1'@'localhost';

revoke insert,select,update,create,delete,alter on mydb2.* from 'user2'@'localhost';
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值