数据库第三次作业

 

create database mydb11_stu;
use mydb11_stu;
create table student(id int(10) not NuLL unique primary key, name varchar(20) not NULL, sex varchar(4),birth year, department varchar(20), address varchar(50));
create table score(id int(10) not null unique primary key auto_increment, stu_idint int(10) not null,c_name varchar(20),grade int(10));

insert student values(901,'张三丰','男',2002,'计算机系','北京市海淀区');
insert student values(902,'周全有','男',2000,'中文系','北京市昌平区');
insert student values(903,'张思维','女',2003,'中文系','湖南省永州市');
insert student values(904,'李广昌','男',1999,'英语系','辽宁省皋新市');
insert student values(905,'王翰','男',2004,'英语系','福建省厦门市');
insert student values(906,'王心凌','女',1998,'计算机系','湖南省衡阳市');
select *from student;

insert into score values(null,901,'计算机',98);
insert into score values(null,901,'英语',80);
insert into score values(null,902,'计算机',65);
insert into score values(null,902,'中文',88);
insert into score values(null,903,'中文',95);
insert into score values(null,904,'计算机',70);
insert into score values(null,904,'英语',92);
insert into score values(null,905,'英语',94);
insert into score values(null,906,'计算机',49);
insert into score values(null,906,'英语',83);
select *from score;

 #1

select *from student;

select *from score;

 

 

#2
select *from student limit 1,4;

 

 #3

select *from student where department ='计算机系' or department = '英语系';

 

 #4

select *from student where (year(now())-birth)<22;

 

 #5

select department, count(id) from student group by department;

 

#6
select score.c_name,max(grade) from score group by c_name;

 

#7
select name,c_name,grade from student left join score on student.id = score.stu_idint where name='李广昌';

 

#8
select student.*,c_name,grade from student left join score on student.id= score.stu_idint;

 

#9
select name,sum(grade) from student inner join score on student.id= score.stu_idint group by name;

 

 #10

select score.c_name,avg(score.grade)from student inner join score on student.id= score.stu_idint group by score.c_name;

 

 #11

select * from student left join score on student.id= score.stu_idint where c_name ='计算机'and grade<95;

 

#12

select name, score.c_name,grade from student left join score on student.id= score.stu_idint where c_name='计算机' order by grade desc ;

 

 #13

select id from student union select score.stu_idint from score;

 

#14
select name,student.department,score.c_name,score.grade from student left join score on student.id= score.stu_idint where name regexp  '^[张李]';

 

 #15

select name,year(now())-birth,department, score.c_name,grade from student left join score on student.id= score.stu_idint where address regexp '[湖南]';