数据库基本操作

一 单表：

student

id	name	grade	gender
1	张三	50	男
2	李四	40	女
3	王五	100	女
4	赵六	89	女
5	孙丽	66	女
6	王田	77	男
7	李可	97	女
8	杨庄	null	null

 

1. 查询性别（展示不同的性别）

   select distinct(gender) from student

2.查询大于60分，小于90分学生的信息

  select * from student where grade<=90 and grade>=60

  select * from student where grade between 60 and 90

3.查看性别为空的学生信息

  select * from student where gender is null

4.查看姓李的学生信息

  select * from student where name like '张%'

5.查看分数总和

 s elect  sum(grade) from student

6.查看平均分数

  select avg(grade) from student

7.查看最高分

  select max(grade) from student

8.查看那最低分

  select min(grade) from student

9.查看

10.分数升序排列

   select * from student order by grade asc

11.分数降序排列

  select * from student order by grade desc

12.筛选出分数排名为3-10条内容

   select * from student order by grade desc limit 2,8

   select a.* from (select * from student order by grade desc limit 4)  a order by grade asc  limit 2

 

13. 插入一条数据，学号9，名字王菲菲，分数73，性别女

    insert into students values (9,'王菲菲',73,'女')

 

14.修改 王菲菲的 分数为 94

update student set grade=94 where name='王菲菲'

二 多表

student

Sid	Sname	Sage	Sgender
1	张三	1991/10/9	男
2	李四	1992/3/10	女
3	王五	1990/5/1	女
4	赵六	1991/4/12	女
5	孙丽	1991/8/19	女
6	王田	1991/1/14	男
7	李可	1991/7/15	女
8	杨庄	1991/10/16	女

 

 

teacher

Tid	Tname
1	崔荷花
2	张梅花
3	王杏花

 

 

grade

Sid	Cid	score
1	1	80
2	1	70
3	1	90
4	1	100
5	1	88
6	1	99
7	1	66
8	1	55
1	2	56
2	2	77
3	2	95
4	2	44
5	2	90
6	2	99
1	3	43
2	3	66
3	3	88
4	3	87
5	3	85
6	3	98
7	3	96

 

 

Course

Cid	Cname	Tid
1	语文	1
2	数学	2
3	英语	3

 

1. 查询学生赵六id以及语数外成绩

  select a.Sid, b.score as '语文', c.score as '数学', d.score as '英语' from (select * from student where Sname='赵雷') a, sc b, sc c, sc d where a.Sid=b.Sid and b.Sid=c.Sid and c.Sid=d.Sid and b.Cid='01' and c.Cid='02' and d.Cid='03'

 

2. 查询所有同学的学生编号，学生姓名，选课总数，所有课程总成绩

  select a.Sid, a.Sname, co  unt(c.Cid) as '选课总数', sum(b.score) as '分数总和' from student a, grade b, Course c where a.Sid=b.Sid group by a.sid, b.Cid=c.Cid order by a.sid

 

3. 语文成绩比数学成绩高的学生信息

  select a.*,  b.score as '语文', c.score as '数学' from student a, grade b, grade c  where a.Sid=b.Sid and b.Sid = C.Sid and b.Cid='01' and c.Cid='02' and b.score > c.score

 

4.查询平均成绩大于等于85的所有学生的学号，姓名，平均成绩

  select a.Sid, a.Sname, avg(b.score) from student a, grade b where a.Sid = b.Sid group by a.Sid, a.Sname having avg(b.score) >= 85 order by a.Sid

 

5.查询出语数外三科的平均成绩

  select avg(a.score) as '语文', avg(b.score) as '数学', avg(c.score) as '英语' from grade a, grade b, grade c where a.Sid=b.Sid and b.Sid=c.Sid  and a.Cid='01' and b.Cid='02' and c.Cid='03'