student数据库

create  table student(

 sid     int(11) primary key not null,-- 唯一标识，不能为空

 sname   char(25) not null,-- 不能为空

 age     int(11) not null,-- 不能为空

 sex     char(2) not null,-- 不能为空

 department char(40) ,

 address  char(200) ,

 birthplace  varchar(256)

);

create  table sc(

  sid   int(11) not null,

  cid   int(11) not null,

  grade int(11)

);

create  table course(

  cid    int(11) not null primary key default 4,

  cname  char(40),

  teacher  char(40)

);

#以下是插入课程表的数据

delete from course ;

insert into course values('8108001','math','sandy');

insert into course values('8108002','english','sherry');

insert into course values('8108003','computer','sandy');

insert into course values('8108004','web','sandy');

insert into course values('8108005','java','sandy');

insert into course values('8108006','C languge','sherry');

insert into course values('8108007','python','xiaozhu');

insert into course values('8108008','testing','xiaozhu');

insert into course values('8108009','linux','sherry');

insert into course values('8108010','shell','sherry');

#以下是插入成绩级表的数据

delete from sc;

insert into sc values('3108001','8108010','90');

insert into sc values('3108001','8108003','67');

insert into sc values('3108002','8108003','54');

insert into sc values('3108002','8108010','84');

insert into sc values('3108003','8108003','78');

insert into sc values('3108004','8108004','89');

insert into sc values('3108005','8108006','56');

insert into sc values('3108006','8108005','60');

insert into sc values('3108007','8108004','79');

insert into sc values('3108008','8108008','89');

insert into sc values('3108009','8108002','46');

insert into sc values('3108010','8108003','87');

insert into sc values('3108011','8108001','85');

insert into sc values('3108011','8108002','81');

insert into sc values('3108012','8108001','97');

insert into sc values('3108012','8108002','55');

insert into sc values('3108013','8108002','86');

insert into sc values('3108013','8108001','71');

insert into sc values('3108014','8108002','69');

insert into sc values('3108014','8108001','78');

insert into sc values('3108015','8108002','67');

insert into sc values('3108016','8108001','85');

insert into sc values('3108016','8108003','85');

insert into sc values('3108016','8108002','85');

insert into sc values('3108016','8108004','85');

insert into sc values('3108016','8108005','85');

insert into sc values('3108016','8108006','80');

insert into sc values('3108016','8108007','79');

insert into sc values('3108016','8108009','36');

insert into sc values('3108016','8108010','78');

insert into sc values('3108016','8108008','88');

insert into sc values('3108016','8108021','83');

insert into sc values('3108015','8108001','85');

insert into sc values('3108015','8108003','85');

insert into sc values('3108015','8108004','85');

insert into sc values('3108015','8108005','85');

insert into sc values('3108015','8108006','80');

insert into sc values('3108015','8108007','79');

insert into sc values('3108015','8108009','36');

insert into sc values('3108015','8108010','78');

insert into sc values('3108015','8108008','88');

insert into sc values('3108015','8108021','83');

#以下是插入学生信息数据

delete from student;

insert into student values('3108001','wang min',21,'f','computer-tec','zhongshan

road','jiangsu');

insert into student values('3108002','jidu',20,'m','english','zhongshan road','fujian');

insert into student values('3108003','wangqing',19,'f','computer-tec','zhongshan

road','jiangsu');

insert into student values('3108004','liuxin',23,'f','chinese','zhongshan road','shanghai');

insert into student values('3108005','ligu',22,'f','computer-tec','zhongshan road','jiangsu');

insert into student values('3108006','songjia',19,'m','english','zhongshan road','jiangsu');

insert into student values('3108007','huamao',20,'f','chinese','zhongshan road','shanghai');

insert into student values('3108008','zhujiao',21,'f','english','zhongshan road','jiangsu');

insert into student values('3108009','wuyi',23,'m','computer-tec','zhongshan road','jiangsu');

insert into student values('3108010','jilian',18,'f','chinese','zhongshan road','hunan');

insert into student values('3108011','linbiao',22,'m','computer-tec','zhongshan

road','jiangsu');

insert into student values('3108012','maoguai',21,'m','english','zhongshan road','fujian');

insert into student values('3108013','rongqi',23,'m','computer-tec','zhongshan

road','jiangsu');

insert into student values('3108014','sangzi',20,'f','chinese','zhongshan road','hunan');

insert into student values('3108015','surui',16,'f','computer-tec','zhongshan road','fujian');

insert into student values('3108016','liushaoqi',24,'m','english','zhongshan road','hunan');

commit;

　　

-- 查询地址
select DISTINCT address FROM student;
-- 查询名字，数学成绩，英语查询
SELECT *from sc
select *from course,sc,student;
-- 查询名字，数学成绩查询
select c.cid,stu.sname,cname,sc.grade  from student stu,sc,course c where stu.sid=sc.sid and sc.cid=c.cid and cname='math'

-- 查询年龄大于20岁的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age > 20;

-- 查询年龄大于等于20岁的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age >= 20;
-- 查询年龄大于等于20岁的学员并且年龄小30岁的的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age >= 20 and age <=30;
select sid,sname,age,sex,department,address,birthplace from student where age BETWEEN 20 and 30;
-- 查询年龄等于18岁的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age = 18;
-- 查询年龄不等于18岁的学员信息
select sid,sname,age,sex,department,address,birthplace from student where age <> 18
-- 查询年龄等于18岁或者 年龄小于20岁或者年龄等于22岁的学员信息
Select sid,sname,age,sex,department,address,birthplace from student where age = 18 or age =20 or age =22;
select sid,sname,age,sex,department,address,birthplace from student where age in (18,20,22);
-- 查询学习英语的学生
select stu.sname,co.cname,sc.grade from student stu, sc, course co where stu.sid=sc.sid and co.cid=sc.cid and co.cname='english'

select c.cid,cname,sc.grade from sc,course c where  sc.cid=c.cid and cname='math';
select c.cid,cname from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and teacher='sandy';



-- sandy老师所教的课程号、课程名称；
select stu.sname,co.cid,cname,teacher from student stu ,sc,course co where stu.sid=sc.sid and sc.cid=co.cid and teacher='sandy';
-- 在学生表中按性别排序，且男在前女在后显示记录。
select sid,sname,age,sex,department,address,birthplace from student order by sex desc;
-- “wuyi”所选修的全部课程名称；
select stu.sname,course.cname from student stu,sc,course where stu.sid=sc.sid and sc.cid=sc.cid and stu.sname='武邑' ;
-- -所有成绩都在80分以上的学生姓名及所在系；
select DISTINCT stu.sname,stu.department,sc.grade from student stu,sc,course co where stu.sid = sc.sid and sc.cid = co.cid and sc.sid not in (select sid from sc where grade<80);
-- 没有选修“english”课的学生的姓名；
select sname from student where   not sid in(select sid from sc where cid in (select cid from course where cname='english'));
-- 与“jilian”同乡的男生姓名及所在系；
select sname,birthplace,department from student where birthplace in (select birthplace from student where sname='jilian')

-- 英语成绩比数学成绩好的学生;


SELECT
	*
FROM
	student s,
	(
		SELECT
			t.sid,
			grade
		FROM
			sc t,
			course c,
			student s
		WHERE
			c.cid = t.cid
		AND s.sid = t.sid
		AND cname = 'english'
	) a,
	(
		SELECT
			t1.sid,
			grade
		FROM
			sc t1,
			course c1,
			student s1
		WHERE
			c1.cid = t1.cid
		AND s1.sid = t1.sid
		AND cname = 'math'
	) b
WHERE
	s.sid = a.sid
AND a.sid = b.sid
AND a.grade > b.grade;


-- 选修同一门课程时，女生比所有男生成绩都好的学生名单；
select * from student s,sc t,student s1,sc t1 where s.sid=t.sid and s1.sid=t1.sid and s.sid=s1.sid and s.sex='f'and s1.sex='m' and

t.cid=t1.cid and t.grade>t1.grade;
-- 至少选修两门及以上课程的学生姓名、性别；

select sname,sex from student s,sc t where s.sid=t.sid having count(t.cid)>=2
-- 选修了sandy老师所讲课程的学生人数；
select count(sid) from student s where sid in (select distinct sid from sc where cid in (select cid from course where

teacher='sandy'));

-- 本校学生中有学生姓名/性别重复的同学，请编写脚本查出本校所有学生的信息，显示学号，姓名，性别，总成绩，对于姓名/性别重复的学生信息只取总成绩最高的那一条记录。
select s.sid,sname,sex,sum(grade) from student s,sc t where s.sid=t.sid

group by  s.sid,sname;
-- “english”课程得最高分的学生姓名、性别、所在系；
select sname,sex,department from student where sid = (select sid from sc where grade =(select max(grade) from sc where cid= (select cid

from course where cname='english')));

-- 统计班级有多少学生
select count(*) from student;

-- 统计数学的做高分学生名称
select sid,sname,age,department from student where sid=( select sid from sc where grade=(
select max(grade) from sc where cid=(
select cid from  course where cname='math')));
-- 查询数学最低分的学生
select sid,sname,age,department from student where sid=( select sid from sc where grade=(
select min(grade) from sc where cid=(
select cid from  course where cname='math')));
-- 查询数学的总成绩
select sum(grade) from sc where cid=(
select cid from  course where cname='math')
-- 查询数学的平均份
select avg(grade) from sc where cid=(
select cid from  course where cname='math')
-- 查询男同学女同学的平均分
select sex  from student where sid=(
select sid from sc where grade=(
select avg(grade) from sc where cid=(
select cid from  course where cname='math'))) GROUP BY sex;
select*from student;
-- 查询 从0开始查询下，查询3条数据
select * from student limit 0,3
-- 每页显示3条数据，查询第一页的数据
select * from student limit 0,3
-- 每页显示3条数据，查询第二页的数据
select * from student limit 3,3
-- 每页显示3条数据，查询第三页的数据
select * from student limit 6,3
-- 起始索引=（当前页码-1）*每页显示的条数