计算机二级mysql操作题答案（15套全）

第1套

insert into tb_dept(deptno,dname,manager,telephone) values('D4','公关部','Liming','010-82953306');alter table tb_employee alter column salary set default '3500';select count(*) as '总人数' from tb_employee where deptno=(select deptno from tb_dept where dname='销售部');create view v_emp as select eno,ename,age,salary from tb_employee where deptno=(select deptno from tb_dept where dname='采购部');create user 'Yaoming'@'localhost' identified by'abc123';

第2套 

select sum(price) as total from tb_commodity where cname='电视机' and origin='北京';alter table tb_commodity drop column desc1;insert into tb_commodity(cname,ctype,origin,birth,price) values('钢笔','文具','上海','2012-12-25',25);create view v_bjcommodity as select * from tb_commodity where origin='北京';create user 'client'@'localhost';grant select(cno,cname) on tb_commodity to 'client'@'localhost' with grant option;

 第3套

alter table tb_student add column ssex char(1) default 'M';update tb_student set smajor='计算机' where sno='100';create view v_avg(cname,caverage) as select cname,avg(grade) from tb_score group by cname;alter table tb_student add unique index idx_stu(sno);create user 'newuser'@'localhost';grant select on tb_student to 'newuser'@'localhost' with grant option;

第4套

create table Dept1(deptno int primary key,dname varchar(20),higherdeptno int default null,constraint fk_higher foreign key(deptno) references Dept2(depton));insert into Dept2(deptno,dname,higherdeptno) values(9,'newdept',higherdeptno);create view SJAVA(sno,sname,grade) as select S.sno,S.name,SC.grade from S,C,SC where S.sno=SC.sno and C.cno=SC.cno and C.cname='JAVA';update SC set grade=90 where sc.cno=(select cno from C where cname='DB') and sno=(select sno from S where sname='李红');create user 'backupdb'@'localhost' identified by 'backup';

第5套

alter table tb_dept add unique index idx_dept(deptname desc); # asc升序索引insert into tb_dept(deptno,deptname) values('D4','营销部');select name as '姓名',2014-birth as '年龄' from tb_emp;create view v_dept1(部门负责人姓名,姓名,学历,工资) as select manager,name,education,salary from tb_dept,tb_emp where deptname='市场部' and tb_emp.deptno=tb_dept.deptno;create user 'stud'@'localhost';grant select on tb_emp to 'stud'@'localhost' with grant option;

第6套

update tb_book set price=42 where bname='MySQL程序设计';create view v_book as select * from tb_book where publisher='高等教育出版社';select bname,publisher,price from tb_book where btype='计算机';alter table tb_book drop column desc1;create user 'client'@'localhost';grant select(bno,bname) on tb_book to 'client'@'localhost' with grant option;

第7套

alter table tb_student add colunm ssex char(2) null defalut '女';delete from tb_gs where tb_gs.sno in (select sno from tb_student where smajor='计算机');create view v_number(smajor,snumber) as select smajor,count(distinct tb_gs.sno) from tb_gs,tb_student wheretb_student.sno=tb_gs.sno group by smajor;alter table tb_gs add constraint fk_dept foreign key(sno) references tb_student(sno);create user 'tmpuser'@'localhost' identified by 'a12345';grant select,insert on tb_group to 'tmpuser'@'localhost' with grant option;

第8套

alter table tb_book drop column shopDate;update tb_reader set sex='女' where readerName='王依然';select readerName,bookNo from tb_reader,tb_borrow where tb_borrow.borrowDate>'2014-01-01' and tb_borrow.readerNo=tb_reader.readerNo;create view v_borrow(读者编号,借书总册数) as select readerNo,count(readerNo) from tb_borrow group by readerNo;load data infile 'backuo.txt' into table tb_reader1;select * from tb_name into outfile 'backup.txt';

第9套

create table dept(deptno int primary key auto_increment,dname char(20) not null,addr varchar(50));select cname as '课程名',max(grade) as '最高分' from score group by cname;insert into student values('907','张敏','女');update score set grade=90 where stuid='906' and cname='英语';create user 'abc'@'localhost' identified by '123';grant select on db_exam.score to 'abc'@'localhost' with grant option;

第10套

create table Emp(empid int primary key auto_increment,empname char(20) character set GB2312 not null.streetname varchar(50),cityname varchar(30) not null default 'N/A');select cname as '课程名',max(grade) as '最高分数' from Score group by cname having Avg(grade)>85;select name,student.stuid,sum(grade) as total from student,Score where student.stuid=Score.stuid group by student.stuid order by total asc;create view StuView(name,cname,grade) as select name,cname,grade from Student,Score where Student.stuid=Score.stuid;grant select,update on newtest.* to 'abc'@'192.168.1.100';

第11套

update student set stu_age=20 where stu_id=2;select stu_name as 'M' from student where stu_sex='M' order by stu_age asc;select stu_name from student,ordering where student.stu_id=ordering.stu_no and ordering.score<60;create user 'stu_user'@'localhost';grant select,insert on XK.student to 'stu_user'@'localhost' with grant option;insert ordering(stu_no,course_no) values(1,5);

第12套

create table publisher(pub_id int primary key auto_increment,pub_name char(30) not null,pub_address char(30) not null);alter table book add column pub_post int;alter table book add constraint FK_book_pub_post foreign key(pub_post) references publisher(pub_id);insert reading values(8,6,'wen');delete from reading where stu_no=3;select book_name from book where book_id=3;

第13套

alter table 学生 add column QQ varchar(20);alter table 选课 add constraint FK_XH foreign key(学号) references 学生(学号);select 学院名称,count(学号) as 总人数 from 学生 group by 学院名称; select 学生.学号,姓名,学院名称 from 学生,选课 where 学生.学号=选课.学号 and 选课.课程名称='操作系统';create view v_信息学院学生(姓名，学号，出生日期) as select 姓名,学号,出生日期 from 学生 where 学院名称='信息学院';

第14套

create index index_选课学号 on 选课(学号);select 课程学分 from 课程 where 课程名称='操作系统';select 学号 from 选课 where 课程名称='操作系统' and 学号 in (select 学号 from 选课 where 课程名称='C语言程序设计');create view v_成绩(课程名称,平均成绩) as select 课程名称,ROUND(avg(成绩),2) from 选课 group by 学号 order by AVG(成绩) desc;create user 'test'@'localhost';grant all on xsxk.* to 'test'@'localhost';

第15套

create table S(SNO int primary key auto_increment,SName char(10),Sex char(1),Age int);create index in_stu on student(depton);select deptno as '系别', avg(score) as '平均成绩' from student where group by deptno;select sid,sname,score from student,dept where student.deptno = dept.deptno and dept.deptname='数学系';select sid,sname,score from student where student.deptno=(select deptno from dept where deptname='数学系');create user 'wang'@'192.168.2.12' indentified by 'test1234';

来源地址：https://blog.csdn.net/weixin_62087061/article/details/128231569