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,Salary
FROM 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,Salary
FROM tb_emp
WHERE Id NOT IN (1);
########## End ##########
第3关:带 BETWEEN AND 的范围查询
本关任务:使用
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 ##########
2.2单表查询(二)
第1关:带 LIKE 的字符匹配查询
本关任务:使用通配符
%
检索数据表中指定字段的数据。
USE Company;
######### Begin #########
SELECT Name,Salary
FROM tb_emp
WHERE Name LIKE 'c%';
######### End #########
第2关:查询空值与去除重复结果
本关任务:使用关键字
IS NULL
检索数据表中指定的字段的空值;使用关键字
DISTINCT
检索数据表中指定的不重复的内容。
USE Company;
######### Begin #########
SELECT *
FROM tb_emp
WHERE DeptId IS NULL;
######### End #########
######### Begin #########
SELECT Distinct Name
FROM tb_emp
;
######### End #########
第3关:带 AND 与 OR 的多条件查询
本关任务:使用关键字
AND
检索数据表中指定的字段的内容;使用关键字
IN
检索数据表中指定的字段的内容。
USE Company;
######### Begin #########
SELECT *
FROM tb_emp
WHERE DeptId = 301 AND Salary > 3000;
######### End #########
######### Begin #########
SELECT *
FROM tb_emp
WHERE 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 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 ##########
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_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 ##########
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_view
AS
select math,chinese,math+chinese
FROM student;
#2.创建多表视图
CREATE VIEW stu_classes
AS
select student.stu_id,student.name,stu_info.classes
FROM student,stu_info
WHERE 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))
begin
declare 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 = district
and 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))
begin
declare 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;
case
when yj is null then set pj='无业绩';
when yj <5000 then set pj='不达标';
when yj <10000 then set pj='达标';
else set pj='优秀';
end case;
end
$
delimiter ;
#调用过程
call ygyj(2015,7,'王雅静',@yj1);
call ygyj(2015,6,'廖秉娴',@yj2);
call ygyj(2015,7,'赵敏',@yj3);
call ygyj(2015,7,'章伟',@yj4);
#代码结束
select @yj1,@yj2,@yj3,@yj4;
第3关:建立和调用存储函数
本关任务: 销售数据库有顾客、销售单数据表 顾客gk数据表有会员号hyh、姓名name、性别sex、电话tel、部门dept字段
use sale;
#代码开始
delimiter $$
create function gkjb(nf int, xm varchar(10))
returns varchar(10)
DETERMINISTIC
begin
declare jg int;
declare pj varchar(10);
select sum(sjfk)
from xsd join gk on gk.hyh = xsd.hyh
where name = xm and year(xsrq) = nf into jg;
case
when isnull(jg) then
set pj = "非会员";
when jg < 5000 then
set pj = "一般会员";
when jg < 10000 then
set pj = "vip";
else set pj = "超级vip";
end case;
return pj;
end $$
delimiter ;
select name 姓名, gkjb(2015, name) 等级 from gk;
#代码结束
第4关:修改多个数据表的存储过程
本关任务: 图书管理数据库有读者reader图书book借阅数据表 读者表reader有读者证号dzzh、姓名xm、性别xb、身份sf、电话号码dhhm字段
use library;
#代码开始
#定义过程
delimiter $$
create procedure hs(in sh varchar(8), in dzbh varchar(3), in rq date, out zt varchar(12))
begin
declare jywh int;
select count(*) from borrow where txm=sh and dzzh=dzbh and isnull(hsrq) into jywh;
if jywh=0 then
set zt = "没有该借阅";
else
update borrow set hsrq=rq where dzzh=dzbh and txm=sh and isnull(hsrq);
update book set zk=1 where txm=sh;
set zt = "还书成功";
end if;
end $$
delimiter ;
call hs("P0000001", "001", "2022-5-1", @zt1);
call hs("P0000001", "002", "2022-5-1", @zt2);
#代码结束
select @zt1,@zt2;
select txm, sm, zk from book;
select * from borrow;
第5关:使用游标的存储过程
本关任务: jdxx数据表有四个字段,分别是省份(sf)、城市(cs)、区县(qxmc)、街道(name)。 例如,查询天心区(qxmc)的所有字段的值结果如图所示
use province;
#代码开始
delimiter $$
create procedure tjdq(in sm varchar(10))
begin
declare flag int default 1;
declare city varchar(10);
declare qx varchar(10);
declare jd int;
declare x int;
declare z int;
declare qt int;
declare dq cursor for select distinct cs, qxmc from jdxx where sf = sm;
declare continue handler for not found set flag = 0;
delete from dqtj;
open dq;
fetch dq into city, qx;
while flag = 1 do
select count(*) from jdxx where cs = city and qxmc = qx and name like "%街道" into jd;
select count(*) from jdxx where cs = city and qxmc = qx and name like "%乡" into x;
select count(*) from jdxx where cs = city and qxmc = qx and name like "%镇" into z;
select count(*) from jdxx where cs = city and qxmc = qx and name not like "%镇" and name not like "%街道" and name not like "%乡" into qt;
insert into dqtj values(city, qx, x, jd, z, qt);
fetch dq into city, qx;
end while;
close dq;
end $$
delimiter ;
call tjdq("安徽省");
#代码结束
select * from dqtj;
版权归原作者 陈阿炳 所有, 如有侵权,请联系我们删除。