二级MySQL，未来教育MySQL题库，15套试卷，sql题答案。

未来教育MySQL题库，15套试卷，sql题答案。

一、

1.insert into tb_dept values('D4','公关部','Liming','010-82953306');

2.alter table tb_employee alter column salary set default 3500;

3.select count(*) as '总人数' from tb_employee where 
depton=(select depton from tb_dept where dname='销售部');

4.create view v_emp as select eno,ename,age,salary from tb_employee
where depton=(select depton from tb_dept where dname='采购部');
5.create user 'Yaoming'@'localhost' identified by 'abc123';

二、

1.select sum(price) as total from tb_commodity where origin='北京'
and cname='电视机';

2.alter table tb_commodity drop column desc1;

3.insert into tb_commodity(cname,ctype,origin,birth,price)

values('钢笔','文具','上海','2012-12-25',25)；

4.create view v_bjcommodity as select * from tb_commodity 
where origin='北京';
5.create use 'client'@'localhost';
grant select(cno,cname) on tb_commodity to 'client'@'localhost' with grant option;

三、

1.alter table tb_student add column ssex char(1) default 'M';

2.update tb_student set smajor='计算机' where sno=100;

3.create view v_avg(cname,caverage) as select cname,avg(price)
from tb_score group by cname;

4.alter table tb_student add unique index idx_stu(sno);
5.create user 'newuser'@'localhost';
grant select on tb_student to 'newuser'@'localhost' with grant option;

四、

constraint charger_id foreign KEY (charger_id)
 references ClassCharger(id)
-- constraint 外键名称 foreign key (主键列)
 references 主表名称(主表列名称)
--切记:作为外键一定要和关联主键的数据类型保持一致


1.create tbale Dept1(
depton int primary key,
dname varchar(20),
higherdepton int default null,
constraint fk_higher foreign key(depton) references Dept2(depton)
);

2.insert into Dept2(depton,dname,higherdep`ton)
values(9,'newdept','higherdepton');

3.create view SJAVA as select S.sno,S.sname,SC.grade from 
S,SC,C where S.sno=SC.sno and C.cname='JAVA';

4.update SC set SC.grade=90 where 
SC.sno=(select S.sno from S where S.sname='李红') 
and 
SC.cno=(select C.cno from C where C.cname='DB');
5.create user 'backupdb'@'localhost' identified by 'backup';

五、

1.alter table tb_dept add unique index idx_dept(deptname desc);

2.insert into tb_dept(depton,deptname)values('D4','营销部');

3.select name as '姓名',2014-birth as 年龄 from tb_emp;

4.create view v_dept(部门负责人姓名,姓名，学历，工资) as
select manager,name,education,salary from
tb_dept,tb_emp where tb_dept.depton=tb_emp.depton 
and deptname='市场部';
5.create user 'stud'@'localhost';
grant select on tb_emp to 'stud'@'localhost' with grant option;

六、

1.update tb_book set price=42 where bname='MySQL程序设计';

2.create view v_book as select *from tb_book 
where publisher='高等教育出版社';

3.select bname,publisher,price from tb_book where btype='计算机';

4.alter table tb_book drop column desc1;
5.create user 'client'@'locathost';
grant select(bno,bname) on tb_book to 'client'@'locathost' with grant option;

七、

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

八、

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

九、

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

十、

1.create table Emp(empid int primary key auto_increment,
empname char(20) character set GB12312 not null,
streetname varchar(50),
cityname varchar(30) not null default 'N/A');
2.select cname as '课程名',max(grade) as '最高分数' from
Score group by cname having Avg(grade)>85;
3.select name,stuid,sum(grade) as total from Student,Score
where Student.stuid=Score.stuid group by stuid order by total asc;

4. create view StuView as select name,cname,grade from Student,Score
   where Student.stuid=Score.stuid;
   5.grant select,update on newtest.* to 'abc'@'192.168.1.100';

十一、

1.update student set age='20' where stu_id=2;
2.select name from student where sex='M' order by age Asc;
3.select name from student,ordering where student.id=ordering.id 
and score<60; 
4.create user 'stu_user'@'localhost';
grant select,insert on student to 'stu_user'@'localhost';
5.insert into ordering(id,cid)values(1,5);

十二、

1.create table publisher(pub_id int primary key auto_increment,
pub_name char(30) not null,
pub_address char(30) not null);
2.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);
3.insert into student(stu_id,stu_name,stu_sex,stu_age)values
(8,'试试','男',10);
insert into book(book_id,book_name,price,author)values
(6,'发',65,'a啊大大');
insert into reading values(8,6,'wen');
4.delete from reading where s_id=3;
5.select b_name from book where b_id=3;

十三、

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

十四、

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

十五、

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