1.定义表
建立“学生”表student, 学号是主码, 姓名取值唯一。
create table student
(sno varchar(9) primary key, /* 列级完整性约束条件*/
sname varchar(20) unique, /* sname取唯一值*/
ssex char(2) ) check (ssex in ('男','女') )
sage smallint,
sdept char(20) /* 系代码*/
);
2.删除数据
删除学号为200215128的学生记录。
delete from student where sno= 200215128';
删除计算机科学系所有学生的选课记录。
delete from sc where sno in (select sno from student where sdept =‘cs');
删除没有考试的选课记录
delete from sc where grade is NULL;
3.条件查询
查询计算机系年龄在20岁以下的学生姓名。
select sname from student where sdept='cs'and sage<20;
排序
查询选修了3号课程的学生的学号及其成绩, 查询结果按分数降序排列。
select sno, grade from sc where cno='3'order by grade desc;
4.插入数据
将学生元组(学号:200215128;姓名:陈冬;性别:男;所在系:is;年龄:18岁)插入到student表中。
insert into student values ('200215128','陈冬','男','is', 18);
插入一条选课记录('200215128','1')。
insert into sc(sno, cno) values ('200215128','1', 80);
5.按条件修改数据
将学生200215121的年龄改为22岁
update student set sage=22 where sno='200215121';
将所有学生的年龄增加1岁
update student set sage= sage+1;
6.建立信息系的学生成绩视图。
create view Vstudent(sno, sname, ssex, cno, cname, grade)
as
select student.sno, student.sname, student.ssex, course.cno,
course.cname, grade
from student, sc, course
where student.sno=sc.sno and sc.cno=course.cno and sdept='is'
7.定义行级触发器,向成绩表中加入一条完整记录,如果加入的成绩大于100分自动将成绩设定为100分。
create trigger tsc
before insert on sc /*触发事件是插入操作*/
for each row /*行级触发器*/
begin
if (new.grade>100) then
set new.grade=100;
end if;
end;
8过程 将某学生的某个选课成绩(提供学号和课程号)降低30分,如果下降后成绩小于0分则将其成绩设定为0分。
create procedure psc(sno varchar(20),cno int)
as declare
newgrade int;
begin
6406
第一题
创建名为"testdb"的数据库
create database testdb;
查看当前用户可使用的数据库
show databases;
3)删除testdb数据库
drop database testdb;
第二题
创建名为stud的数据库,并指定其字符集为gbk
create database stud character set gbk;
创建名为stu的数据库,同时检查且保证在stu数据库不存在时才创建
create database stu if not exists;
查看当前可使用的所有数据库
show databases;
4)查看所有以s开头的数据库
show database like ‘s%’;
第三题
修改stud数据库的字符集为utf8
alter database stud character set utf8;
修改stud数据库的校对规则为utf8_general_ci
alter database stud collate utf8_general_ci;
3)删除stu数据库
drop database stu;
第四题
创建名为"st"的数据库
create database st;
在数据库st中,使用CREATE语句创建一个数据表stu,包括sid,sname,ssex三个字段,相应的字段类型分别为整型,字符串型(char(10))和字符串型(char(2)),引擎为MyISAM
Create table stu(sid int, sname char(10), ssex char(2)) engine=MyISAM;
3)查看stu表的结构
Show create table stu;
第五题
用SQL命令使product数据库为当前数据库
Use product;
2)使用create语句创建一个订单数据表ordering,包括订单号no,客户号co,订单总价amount,订单日期orderday四个字段,相应的字段类型分别为整型,字符串型(char(8)),float和date
Create table ordering (no int, co char(8), amount float, orderday date);
3)查看ordering表的结构
Show create table ordering;
第六题
创建名为book的数据库
Create database book;
用SQL命令使book数据库为当前数据库
Use book;
3)使用create语句创建一个图书数据表book,包括图书编号bno,图书名称bname,出版日期bdate三个字段,相应的字段类型分别为整型,字符串型(char(20)),和日期型date
Create table book(bno int, bname char(20), bdate date);
第七题
用SQL命令使student数据库为当前数据库
Use student;
使用create语句创建一个学生数据表stu,包括学号stuid,姓名stuname,性别stusex,生日stubirth,入校成绩stuscore五个字段,相应的字段类型分别为整型,空字符串(varchar(20)),字符串型(char(2)),日期型和单精度浮点型,并设置学号不能为空且数据引擎为InnoDB
Create stu( stuid int not null, stuname varchar(20), stusex char(2), stubirth date, stuscore float) engine=InnoDB;
3)查看ztu表结构
Show create table ztu;
64086412
第一题
从stu表中统计出全校男同学的人数(count(*)),结果列名为“男生人数”
Select count(*) as ‘男生人数’ from stu where stusex=’男’;
第二题
从score表中查询学生'20160511011'所考课程的平均分信息,结果列名为“学号”,“平均分”
Select stuid as ‘学号’, avg(score) as ‘平均分’ from score where stuid=’20160511011;’
第三题
从course表中查询最高课时和最低课时信息,结果列名分别为“最高课时” , “最低课时”
Select max(coursetime) as ‘最高课时’, min(coursetime) as ‘最低课时’ from course;
第四题
从stu表中按学号降序查询所有女生基本情况
Select*from stu where stusex=’女’ order by stuid desc;
第五题
从score表中按学号升序查询所有成绩,若学号相同则按成绩降序排列
Select*from score order by stuid, score desc;
第六题
从course表中查询从第2门课程开始的连续3门课程的信息
Select*from course limit 1,3;
第七题
从stu表中分别统计男,女生的人数,结果为“性别”,“人数”2列
Select stusex as ‘性别’, count(*) as ‘人数’ from stu group by stusex;
第八题
从stu表中查询各学院学生的人数,查询结果包含“学院”,“学生人数”2列
Select stuschool as ‘学院’, count(stuID) as ‘学生人数’ from stu group by courseid
having 平均分>=80;
第九题
从score中计算每门课程的平均分,将平均分大于等于80分的科目信息输出(用having筛选),结果包含“课程编号”,“平均分”两列
Select courseid as ‘课程编号’, avg(score) as ‘平均分’ from score group by courseid
having 平均分>=80;
6413
第一题
查询学生的stuid,stuname以及所修课程的courseid,score信息,查询结果按学号升序排列
select stu.stuid,stuname,courseid,score
from stu inner joinfrom on stu.stuid=score.stuid
order by stu.stuid;
第二题
查询每门课程的coursename,以及选修人数信息,结果包含课程名称,选修人数2列,并按选修人数降序排列
select coursename as ‘课程名称’,count(*) as 选修人数
from course,score
where course.courseid=score.courseid
group by coursename
order by 选修人数 desc;
第三题
用SQL语句查询“销售部”的员工总人数,要求查询结果显示为“总人数”
SELECT
COUNT(*) as ‘总人数’
FROM tb_employee, tb_dept
WHERE tb_employee.deptno=tb_dept.deptno
AND
tb_dept.dname=’销售部’;
第四题
从stu表中查询“飞行技术学院”和“计算机学院”学生的所有信息,要求使用联合查询实现
Select*from stu where stuschool=’飞行技术学院’ union select*from stu where stuschool=
’计算机学院’;
第五题
从course表中查询课时(coursetime)跟“数据库原理”课程一样的所有课程的信息
Select*from course
where coursetime=
(select coursetime from course
where coursename=’数据库原理’);
第六题
从stu表中查询比“张雪”同学年龄大的同学的姓名和年龄,结果含“姓名”,“年龄”两列
Select stuname as ‘姓名’,
(year(curdaye())-year(stubirth)) as ‘年龄’
from stu
where stubirth<
(select stubirth from stu
where stuname=’张雪’);
第七题
从stu表和score表中查询分数在85分以上(包含85分)的学生的stuid,stuname,stuschool信息
Select(stuid,stuname,stuschool)
from stu
where stuid in
(select stuid from score where score>=85);
第八题
从score表中查询成绩比所有“E2201140”号课程成绩都低的同学及课程
Select*
from score
where score<all
(select score from score where courseid=’E2201140’);
第九题
用SQL语句查询一个员工都没有的部门的deptno,dname信息
Select deptno,dname
from tb_dept
where deptno not in
(select deptno from tb_employee);
6414
第一题
利用course表,创建视图course_view,包含课时数在64以上(包含64)的各门课程的coursename和coursetime信息,结果列名为"课程名称"和“课时”
Creat view course_view as select coursename as 课程名称, coursetime as 课时 from course where coursetime>=64;
第二题
利用score表,创建视图course_sc,包含各门课程的“课程编号”和“平均成绩”两列
Create view course_view as select coursename as 课程编号,avg(score)as 平均成绩 from score group by courseid;
第三题
使用show create命令查看视图stu_view的结构
Show create view stu_view;
第四题
删除视图stu_view
Drop view stu_view;
第五题
用SQL语句为“采购部”建立一个员工视图v_emp,包括职工号(eno),姓名(ename),年龄(age)和工资(salary)
Create view v_emp as
select eno,ename,age,salary from tb_employee
where tb_employee.deptno=(select deptno from tb_dept where dname=’采购部’);
第六题
使用SQL语句,在数据库db_mall中创建一个视图v_bjcommodity,要求该视图包含商品表中产地为上海的全部商品信息
Create view v_bjcommodity as select*from tb_commodity where origin=’上海’;
6415
第一题
创建一个名为tr_stu_del的触发器,要求在删除stu表中某学生信息时,可自动删除该学生在score表中的信息
USE STUDENT;
CREATE trigger tr_stu_del after delete
ON stu for each row
Delete FROM score WHERE stuid=OLD.stuid;
第二题
创建一个名为tr_sc_ins的触发器,要求在向score表插入数据前,判断其score值,若小于0或大于100,则将score置为0
USE STUDENT;
DELIMITER //
Create trigger tr_sc_ins tr_sc_ins BEFORE insert
ON score for each row
IF new.score<0 or new.score>100
THEN set new.score=0;
END IF//
第三题
创建一个名为tr_id_update的触发器,要求在修改course表的课程号后,同时修改score表中对应的课程号
USE STUDENT;
CREATE TRIGGER tr_id_update after update
ON course FOR EACH ROW
UPDATE score SET courseid=new.courseid
WHERE courseid=old.courseid;
第四题
1)查看当前数据库中定义的所有触发器的详细信息
Show triggers;
2)仅查看触发器tr_sc_ins的详细信息
Show create trigger tr_sc_ins;
3)删除触发器tr_id_update
Drop trigger tr_id_update;
第五题
设计一个名为ev_cre_tb的事件,在3秒后创建表test(a int primary key auto_increment,b datetime)
USE STUDENT;
SET GLOBAL event_scheduler=on;
CREATE event ev_cre_tb
ON SCHEDULE at now()+interval 3 second
Do CREATE TABLE test(a int primary key auto_increment,b datetime);
第六题
设计一个名为ev_ins的事件,每2秒向test表插入一条当前时间的记录,且10秒后事件结束
USE STUDENT;
SET GLOBAL event_scheduler=on;
Create ev_ins
On schedule EVERY 2 second
STARTS now() ENDS now()+interval 10 second
DO insert into test SET b=now();
第七题
设计一个名为ev_bak的事件,从2019年9月1日00:00:00起每天00:00:00将stu表的数据备份到文件bakfile.txt
USE STUDENT;
SET GLOBAL event_scheduler=on;
DELIMITER //
CREATE EVENT ev_bak
ON SCHEDULE every 1day
Starts '2019-09-01 00:00:00'
Do
Begin
SELECT * FROM stu INTO OUTFILE 'bakfile.txt' LINES TERMINATED BY ';';
End //
DELIMITER ;
第八题
1) 利用关键字on,开启事件调度器,并将该语句存储考生文件夹下的到event1.txt文件中
set global event_scheduler=on;
2)关闭事件ev_ins
USE STUDENT;
SET GLOBAL event_scheduler=on;
CREATE EVENT ev_ins
ON SCHEDULE EVERY 1 hour
STARTS now() ENDS now()+INTERVAL 1 day
DO INSERT INTO test SET b=now();
alter EVENT ev_ins disable;
3)修改事件ev_bak的名字为event_bake
USE STUDENT;
SET GLOBAL event_scheduler=on;
CREATE EVENT ev_bak
ON SCHEDULE EVERY 1 DAY
STARTS '2019-09-01 00:00:00'
DO
SELECT * FROM stu INTO OUTFILE 'bakfile.txt' LINES TERMINATED BY ';';
alter event ev_bak rename to event_bake;
4)删除事件ev_ins
drop event ev_ins;
6416
第一题
1)创建无参数存储过程get_score_proc,要求该存储过程能查询student.score表中score列大于等于90分的纪录。
delimter // /*修改命令结束符为//
create procedure get_score_proc()
Begin
select * from student.score where score>=90;
end//
delimiter ;
2)用call命令调用1中建立的过程get_score_proc,观察结果。
call get_score_proc();
第二题
1)创建带有IN类型参数的存储过程getin_score_proc,要求该存储过程能根据输入的分数值,查询student.score表中score列大于等于该值的记录
delimiter //
create procedure get_score_proc(in num float)
begin
select * from student.score where score>=num;
end//
delimiter ;
2)用call命令调用1中建立的过程getin_score_proc查询score中85分以上的记录信息,观察结果。
call getin_score_proc(85);
第三题
1)创建带有out类型参数的存储过程getout_score_proc,要求该存储过程能统计student.score表中score列大于等于85分的记录有多少个
delimiter //
create procedure getout_score_proc (out num int)
begin
select count(*) into num from student.score where score>=85;
end//
delimiter ;
2)用call命令调用1中建立的过程getout_score_proc,观察结果。
call getout_score_proc(@x);
select @x;
第四题
1)创建带有INOUT类型参数的存储过程score_proc,要求该存储过程完成如下功能:
1.当输入参数为1时,统计student.score表中score列的最大值。
2.当输入参数为0时,统计student.score表中score列的最小值。
delimiter //
create procedure score_proc(inout num int)
begin
if num=1 then
select max(score) into num from student.score;
elseif num=0 then
select min(score) into num from student.score;
end if;
end//
delimiter ;
用call命令调用1中建立的过程score_proc,求出score列的最大最小值,观察结果。
/*求成绩最高分*/
set @x=1;
call score _proc(@x);
select @x;
/*求成绩最低分*/
set @x=0;
call score_proc(@x);
select @x;
第五题
删除数据库中已有的存储过程getin_score_proc,将删除命令保存到考生文件夹下的proc.txt文件中
drop procedure getin_score_proc;
第六题
在给定的学生选课数据库xsxk中有一个学生表,包含的属性有学号,姓名,出生日期,学院名称.设计一个存储过程PR_学分,根据学号返回学生的总学分(注意:成绩>=60分才能获得相应的学分)
DELIMITER $$
CREATE procedure PR_学分(IN xh CHAR(10),OUT zxf INT)
BEGIN
SELECT sum(课程,课程学分) INTO zxf
FROM 选课, 课程
WHERE 选课.课程名称=课程.课程名称 AND 学号=xh AND 成绩>=60;
end $$
DELIMITER ;
第七题
创建一个函数circle_area_fun,该函数功能为:输入圆的半径,输出圆的面积。
set global log_bin_trust_function_creators = 1;
delimiter //
create function circle_area_fun(x float)
returns float
begin
declare y float;
set @y=pi()*x*x;
return(@y);
end//
delimiter ;
第八题
创建一个函数avgscore_fun,该函数功能为:根据输入学生的学号返回student.score表中该学生所选课程的平均分,如果没有该学生则返回“查无此人!”
delimiter //
create function sele_fun(id varchar(11))
returns varchar(30)
begin
declare avgscore varchar(30);
select avg(score) into avgscore from student.score where stuid=id group by stuid;
if avgscore is null then
return('查无此人!');
else
return(avgscore);
end if;
end//
delimiter ;
第九题
创建一个函数sele_fun,该函数功能为:输入学生的学号,若该生存在,则返回该生姓名,否则输出“查无此人!”
delimiter //
create function sele_fun(id varchar(11))
returns varchar(30)
begin
declare name varchar(30);
select stuname into name from student.stu where stuid=id;
if name is null then
return('查无此人!');
else
return(name);
end if;
end//
delimiter ;
第十题
设计一个名称为fn_emp的存储函数,要求能根据给定的部门名称(dname)返回该部门的工资总和
DELIMITER $$
CREATE FUNCTION fn_emp (dept CHAR(20))
RETURNS FLOAT
NO SQL
BEGIN
Declare sum_salary float;
SELECT sum(salary) INTO sum_salary
FROM tb_employee INNER JOIN tb_dept
ON tb_employee.deptno=tb_dept.deptno
WHERE dname=dept
GROUP BY dname ;
RETURN(sum_salary);
END $$
DELIMITER ;
第十一题
删除已有函数circle_area_fun
drop function circle_area_fun;
6417
第一题
创建用户test,主机为localhost,密码为123456
create user test@localhost identified by '123456';
2)通过mysql数据库中的user表中的host,user字段,查看用户账户
identified by select host,user from mysql.user;
第二题
用一条命令创建用户client,登录机器IP为127.0.0.1,密码为'mima',并为其授予对stu表的select权限
grant select on student.stu to client@127.0.0.1
identified by 'mima';
2)修改client用户名为'userc'
rename user to userc@127.0.0.1;
第三题
1)用一条命令创建用户jack,密码为'abc',并为其授予对student数据库的所有权限
grant all on student.* identified by 'abc';
2)删除用户账户'userc@127.0.0.1'
drop user userc@127.0.0.1;
第四题
1)用一条命令创建用户Alice,主机名为localhost,密码为'Alice',并为其授予对score表score列的select,update权限,且允许其可以将次权限授予其他人,
grant select(score), update(score) on score
to Alice@localhost identified by 'Alice'
with grant option;
撤销Alice修改score列数据的
revoke update (score) on score
from Alice@localhost;