（MySQL）头歌数据库作业答案

1.数据库和表的基本操作

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

第1关：查看表结构与修改表名

本关任务：修改表名，并能顺利查询到修改后表的结构。

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 ##########

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

本关任务：修改表中的字段名，并修改字段的数据类型。

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;

第3关：添加与删除字段

本关任务：分别在表的最后一列、第一列和指定列后添加新的字段，并删除表中的指定字段。

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;

第4关：修改字段的排列位置

本关任务：修改表中某字段的顺序，分别将其排至表中的第一列与指定列之后。

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;

第5关：删除表的外键约束

本关任务：删除表中的外键约束。

我们曾在第一章中讲过外键的作用，以及如何创建一个表的外键。建立了外键我们就建立起了两张表的关联关系，那如果我想删除主表呢？为了确保数据库的正确性，我们必须先解除两个表之间的关联关系，那就是删除外键约束啦！让我们先来看看删除外键约束的语法规则。

语法规则为： ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名; 。

USE Company;#请在此处添加实现代码########## Begin #################### delete the foreign key ##########ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;########## End ##########SHOW CREATE TABLE tb_emp \G;

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

第1关：插入数据

本关任务：为表同时插入多条我们规定的记录。

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;

第2关：更新数据

有道是：人生在世，孰能无过。过而能改，善莫大焉!

本关就为各位着重介绍数据的更新（UPDATE）。

本关任务：将数据表中原有的数据修改为本关指定的数据。

USE Company;#请在此处添加实现代码########## Begin #################### update the value ##########update tb_emp SET Name='Tracy' WHERE Name='Carly';UPDATE tb_emp SET DeptId=302 WHERE Name='Tracy';UPDATE tb_emp SET Salary=4300.00 WHERE Name='Tracy';  ########## End ##########SELECT * FROM tb_emp;

第3关：删除数据

本关任务：删除表中的指定行。

USE Company;#请在此处添加实现代码########## Begin #################### delete the value ##########DELETE FROM tb_emp WHERE Salary>3000; ########## End ##########SELECT * FROM tb_emp;

---

2.简单查询

2.1单表查询（一）

第1关：基本查询语句

本关任务：

• 用SELECT语句检索数据表中指定字段的数据；

• 用SELECT语句检索数据表中所有字段的数据。

  USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary ##########SELECT Name,SalaryFROM tb_emp;########## retrieving all the table ##########SELECT * FROM tb_emp;########## End ##########

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

  本关任务：使用IN关键字检索数据表中指定的数据内容。

  USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary with IN statement ##########SELECT Name,SalaryFROM tb_empWHERE Id NOT IN (1);########## End ##########

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

  本关任务：使用BETWEEN AND关键字检索数据表中指定的数据内容。

  USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary with BETWEEN AND statement ##########SELECT Name,SalaryFROM tb_empWHERE Salary BETWEEN 3000 AND 5000;########## End ##########

   2.2单表查询（二） 

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

  本关任务：使用通配符%检索数据表中指定字段的数据。

  USE Company;######### Begin #########SELECT Name,SalaryFROM tb_empWHERE Name LIKE 'c%';######### End #########

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

  本关任务：使用关键字IS NULL检索数据表中指定的字段的空值；使用关键字DISTINCT检索数据表中指定的不重复的内容。

  USE Company;######### Begin #########SELECT *FROM tb_empWHERE DeptId IS NULL;######### End ################## Begin #########SELECT Distinct NameFROM tb_emp;######### End #########

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

  本关任务：使用关键字AND检索数据表中指定的字段的内容；使用关键字IN检索数据表中指定的字段的内容。

  USE Company;######### Begin #########SELECT *FROM tb_empWHERE DeptId = 301 AND Salary > 3000;######### End ################## Begin #########SELECT *FROM tb_empWHERE DeptId in(301,303);######### End #########

  2.3单表查询（三）

  第1关：对查询结果进行排序

  本关任务：以成绩的降序显示学生成绩表中所有信息。

  USE School;#请在此处添加实现代码########## Begin #################### 查询1班同学的所有信息以成绩降序的方式显示结果 ##########SELECT * FROM tb_score  where class_id=1 ORDER BY score desc;########## End ##########

  第2关：分组查询

  本关任务：对班级表中的班级名称进行分组查询。

  USE School;#请在此处添加实现代码########## Begin #################### 对班级名称进行分组查询 ##########SELECT stu_id,class_id,name FROM tb_class GROUP BY class_id;########## End ##########

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

  本关任务：使用LIMIT关键字查询班级中第2名到第5名的学生信息，并根据学生成绩进行降序排序。

  USE School;#请在此处添加实现代码########## Begin #################### 查询班级中第2名到第5名的学生信息 ##########SELECT * FROM tb_score order by score desc LIMIT 1,4;########## End ##########

  ---

  3.高级查询

  3.1连接查询

  第1关：内连接查询

  本关任务：使用内连接查询数据表中学生姓名和对应的班级。

  内连接查询

  • 仅将两个表中满足连接条件的行组合起来作为结果集，称为内连接；

  • 关键字：[inner] join ... on。

    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 ##########

    第2关：外连接查询

    本关任务：使用外连接查询数据表中所有班级和对应班级里学生的姓名。

    外连接查询

    • 以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配，正确保留；不能匹配，其它表的字段都置空（null），称为外连接。

    • 外连接查询分为左外连接查询和右外连接查询；

    • 关键字：left/right [outer] join ... on。

      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 #################### 使用右外连接查询所有学生姓名和对应的班级 ###########请在此处添加实现代码########## Begin ##########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;########## End ##########

      第3关：复合条件连接查询

      本关任务：使用连接查询，查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级。

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

      3.2子查询

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

      本关任务：查询大于所有平均年龄的员工姓名与年龄。

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

      第2关：关键字子查询

      本关任务：根据要求使用关键字进行查询。

      USE Company;#请在此处添加实现代码########## Begin ###########1.使用 ALL 关键字进行查询SELECT position,salary FROM tb_salary WHERE salary > All(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 ##########

      3.3分组选择数据

      第1关：GROUP BY 与 聚合函数

      本关任务：使用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 ##########

      第2关：使用 HAVING 与 ORDER BY

      使用having子句进行分组筛选

      简单来说，having子句用来对分组后的数据进行筛选，即having针对查询结果中的列发挥筛选数据作用。因此having通常与Group by连用。

      USE School;#请在此处添加实现代码########## Begin ###########1.查询表中至少有两门课程在90分以上的学生信息select sno,count(*)from tb_gradewhere score >= 90group by snohaving count(pno) >= 2;#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息select sno,avg(score) from tb_gradewhere sno in (select sno from tb_grade where score >=95 and pno = '语文' )group by sno having avg(score) >=90;########## End ##########

      3.4使用聚合函数查询

       第1关：COUNT( )函数

      本关任务： 1.使用COUNT()函数查询数据表中总数据量， 2.使用COUNT()函数统计班级总人数。

      USE School;#请在此处添加实现代码########## Begin #################### 查询该表中一共有多少条数据 ##########select count(*) from tb_class;########## 查询此表中367班有多少位学生 ##########select classid,count(*) from tb_class where classid=367;########## End ##########

      第2关：SUM( )函数

      本关任务： 1.使用SUM()函数查询数据表中学生的总成绩； 2.使用SUM()函数查询语文课程中学生的总成绩。

      USE School;#请在此处添加实现代码########## Begin #################### 查询所有学生总分数 ##########select sum(score) from tb_class;########## 查询学生语文科目的总分数 ##########select course,sum(score) from tb_class where course='语文';########## End ##########

      第3关：AVG( )函数 

      本关任务：使用AVG()函数查询学生各科目的平均分数。 

      USE School;#请在此处添加实现代码########## Begin #################### 查询学生语文科目的平均分数 ##########select course,avg(score) from tb_class where course='语文';########## 查询学生英语科目的平均分数 ##########select course,avg(score) from tb_class where course='英语';########## End ##########

      第4关：MAX( )函数 

      本关任务：使用MAX()函数查询各科中的最高分数。 

      USE School;#请在此处添加实现代码########## Begin #################### 查询语文课程中的最高分数 ##########select course,max(score) from tb_class where course='语文';########## 查询英语课程中的最高分数 ##########select course,max(score) from tb_class where course='英语';########## End ##########

      第5关：MIN( )函数 

       本关任务：使用MIN()函数查询学生在各科中的最低分数。

      #请在此处添加实现代码########## Begin #################### 查询语文课程中的最低分数 ##########select course,min(score) from tb_class where course='语文';########## 查询英语课程中的最低分数 ##########select course,min(score) from tb_class where course='英语';########## End ##########

      3.4视图

      第1关：视图

      本关任务：通过学习视图，创建一个单表视图和一个多表视图。

      use School;#请在此处添加实现代码########## Begin ###########1.创建单表视图CREATE VIEW stu_viewASselect math,chinese,math+chineseFROM student;#2.创建多表视图CREATE VIEW stu_classesASselect student.stu_id,student.name,stu_info.classesFROM student,stu_infoWHERE student.stu_id=stu_info.stu_id;########## End ##########

      4.存储器和触发器

      4.1存储过程（新）

      第1关：建立和调用存储过程（不带输出参数的存储过程）

       本关任务： 该实验是针对数据表jdxx，该数据表有四个字段，分别是省份(sf)、城市(cs)、区县(qxmc)、街道(name)。 例如，查询天心区(qxmc)的所有字段的值

       use province; #代码开始 #定义过程delimiter $$CREATE PROCEDURE dqxx(in city varchar(10),in district varchar(10))begindeclare x int;declare jd int;declare z int;declare qt int;select count(name) from jdxx where cs = city and qxmc = district and name like "%乡" into x;select count(name) from jdxx where cs = city  and qxmc= district and name like "%街道" into jd;select count(name) from jdxx where cs = city and qxmc = districtand name like "%镇" into z;select count(name) from jdxx where cs = city and qxmc = district and name not like "%镇" and name not like"%街道" and name not like "%乡" into qt;select x 乡,jd 街道,z 镇,qt 其他;end $$delimiter ; #调用过程call dqxx("长沙市","开福区");call dqxx("厦门市","同安区"); #代码结束

       第2关：建立和调用存储过程（带输出参数）

       本关任务： 销售数据库有工作人员、销售单数据表 工作人员gzry数据表有雇员号gyh、姓名gyxm、出生日期csrq、学历xl、工资gz、部门bm、电话dh字段

       use sale; #代码开始 #定义过程 #调用过程delimiter $create procedure ygyj(in nf int,in yf int,in xm varchar(10) ,out pj  varchar(10))begindeclare yj real;select sum(sjfk) from xsd,gzry where xsd.gyh=gzry.gyh and year(xsrq)=nf and month(xsrq)=yf and gyxm=xm into yj;casewhen yj is null then set pj='无业绩';when yj