【无标题】数据库代码

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;