MySQL语法练习题

MySql语法练习题

一、根据提示完成题目

student表，则包含字段：Sno int（5），Sname varchar（10），Ssex varchar（2），Sage int（3），Sdept varchar（5） Sno 主键 自增；遇SC表，则包含Sno，Cno，Grade， level字段（level 表明该课程是否为选修课）。

1、 创建bookshop数据库

create database if not exists bookshop;

2、创建名为admin_user(管理员用户)的数据表，包含以下字段：uid,uname,password,ulevel,e-mail（数据类型、长度，主键等自己考虑，设计的越合理越好）

create table admin_user(
    uid int primary key auto_increment,
    uname char(10) not null,
    passward varchar(32) default '00000000',
    ulevel int default 0,
    e-mail char(50))engine=innodb default charset utf8;

3、创建名为members（会员用户）的数据表，包含字段、主键、数据类型等均自由设计，越合理越好。

create table memmbers(
	mid int(5) primary key,
    mname varchar(10))engine=innodb default charset utf8;

4、创建名为goods的数据表，包含字段：gid，gname，class, category

create table goods(
	gid bigint primary key,
    gname char(20),
    class char(10),
    category varchar(10))engine=innodb default charset utf8;

5、删除数据库。

drop database if exists bookshop;

6、 删除数据表student。

drop table if exists student;

7、将student表重命名为user表

alter table student rename user;

8、为admin_user增加一个 TEL字段，数据类型为int，长度为5，非空，唯一

alter table admin_user add tel int(5) not null unique;

9、删除student表中的Sdept字段的约束条件

alter table admin_user modify sdept char(20);

10、将student表中的Ssex字段修改为varchar类型

alter table student modify Ssex varchar(2);

11、将course表中的Cno字段修改为主键，并且自增

alter table course modify Cno int primary key auto_increment;

12、将admin_user表的TEL字段改名为Telephone

alter table admin_user change TEL Telephone numeric(11);

13、查询admin_user表中的所有用户

select * from admin_user;

14、查询admin_user表中，所有ulevel在8级以上的用户

select * from admin_user where ulevel=8;

15、查询姓名为李四的用户的用户等级

select ulevel from admin_user where uname='李四';

16、查询admin_user表除用户密码外的所有用户信息，表头改为用户ID， 用户名， 用户等级， 电子邮箱， 联系电话

select uid 用户ID,uname 用户名,ulevle 用户等级,e-mail 电子邮箱,tel 联系电话 from admin_user;

17、查询SC的Sno，消除重复行

select distinct Sno from SC;

18、询所有年龄在20岁以下的学生姓名及其年龄

select sname,sage from student where age<20;

19、查询年龄在18至20岁之间的学生的学号及姓名（请用3中方法）

select sno,sname from student where between 18 and 20;

20、查询计算机系所有姓刘的学生的全部信息

select * from student where sdept='计算机系' and sname like '刘%';

21、查询电子信息工程专业，所有名字中包含井的学生的全部信息

select * from student where sdept='电子信息工程系' and sanme like '%井%'；

22、查询计算机系年龄在20岁以下的学生姓名

select sname from student where sdept='计算机系' and sage<20;

23、查询计算机系、电子信息工程系、信管系，大四学生的详细信息，并按照学号降序排列

select * from student where sdept int ('计算机系','电子信息工程系','信管系') and slevel='大四' order by sno;

24、计算1号课程的学生平均成绩

select avg(score) from SC where kid=1; 

25、查询选修1号课程的学生最高分数

select max(score) from SC where kid=1;

26、查询每门课程有多少学生学过

select count(*) from SC group by kid;

27、查询所有学生学过课程的总数

select count(*) from SC;

28、查询所有选修课，有多少学生修过

select count(*) from Sc

29、查询平均成绩大于80分的学生的学号和姓名

select sid,sname from student s inner join score c on s.sid=c.sid group by s.sid s.name having avg(score)>80;

30、查询有3门以上课程（不包含选修课）是90分以上的学生的学号及（90分以上的）课程数

select sno,count(*) from SC where score>90 group by sno having count(*)>3;

二、信息如下,回答下列问题

• student学生信息表

sno	sname	sex	birthday	class
108	曾华	男	1977-01-09	95033
105	匡明	男	1975-02-01	95031
107	王丽	女	1976-01-23	95033
101	李军	男	1976-02-20	95033
109	王芳	女	1975-02-10	95031
103	陆军	男	1974-06-03	95031

• teache老师信息表

tno	tname	sex	birthday	prof	depart
804	李诚	男	1958-02-12	副教授	计算机系
825	王萍	女	1972-05-05	助教	计算机系
831	刘冰	女	1977-08-14	助教	电子工程系
856	李旭	男	1969-12-03	讲师	电子工程系

• course课程表

cno	cname	tno
3-105	计算机导论	825
3-245	操作系统	804
6-166	数字电路	856
9-888	高等数学	825

• score成绩表

sno	cno	degree
103	3-245	86.00
109	3-245	68.00
105	3-245	75.00
103	3-105	92.00
105	3-105	88.00
109	3-105	76.00
101	3-105	64.00
107	3-105	91.00
108	3-105	78.00
101	6-166	85.00
107	6-166	79.00
108	6-166	81.00

创建表信息，并添加约束。

create table student(
	sno int primary key,
	sname char(10) not null,
	sex enum('男','女'),
	birthday date,
	class int not null)engine=innodb default charset utf8;

create table teacher(
	tno int primary key,
    name char(10) not null,
    sex enum('男','女')，
    birthday date,
    prof enum('教授','副教授'