关于学习数据库一些基础操作

数据查询
一、 单表无条件查询

1. 查询指定列
   select sno,sname,ssex
   from student;

2. 查询所有列
   select *
   from student;

3. 使用计算
   select sno,cno,grade*0.6
   from sc;

4. 使用列别名
   select sno,cno,grade*0.6 as 期末成绩
   from sc;

5. 补充
   （1） 查询系统当前日期
   select current_date from dual;
   （2） 查询系统当前年份
   （3） 查询系统当前月份
   （4） 查询系统当前日子
   （5） 根据年龄查询学生出生年份
   select sno,sname,year(current_date)-sage as 出生年份
   from student;

6. 去掉重复
   select distinct sno
   from sc;

7. 排序
   select *
   from student
   order by sage ;

select *
from student
order by sage desc;
二、 条件查询

1. 比较运算符 > < = >= <= <>
   select *
   from student
   where ssex <> ‘男’;

select *
from student
where sage >= 20;
2. 逻辑运算符 and or not
select *
from student
where sage >= 20 and ssex = ‘男’;

select *
from student
where sage >= 20 or ssex = ‘男’;
3. 区间运算符
select sno,cno,grade
from sc
where grade between 60 and 90;

select sno,cno,grade
from sc
where grade not between 60 and 90;
4. 模糊查询 like 通配符： % _
select *
from student
where sname like ‘张%’;

select *
from student
where sname like ‘张_’;

select *
from student
where sname like ‘张__’;

select *
from course
where cname like ‘%数据%’;

5. 空值
   select *
   from student
   where sage is null;

select *
from student
where sage is not null;
6. 列表运算符 in
年龄：18 19 21 22
Sage In(18, 19, 21, 22)
Dno in(‘CS’,’IS’)
select *
from student
where sage in(18,19,21,22);
三、 统计

1. 聚合函数
   select sum(grade),avg(grade),max(grade),min(grade),count(grade)
   from sc;
   查询有多少同学选课了
   select count(distinct sno)
   from sc;
2. 分组
   查询每位同学平均成绩
   select sno,avg(grade)
   from sc
   group by sno;
   查询每门课平均成绩
   select cno,avg(grade)
   from sc
   group by cno;
   查询男女生各有多少人
   select ssex, count(sno)
   from student
   group by ssex;
   查询每个系男女生各有多少人
   Select dno, ssex, count(sno)
   from student
   group by dno,ssex;

Select
From
Where
Group by
Having
Order by
Where:对原表源数据的条件
Having：条件中含有聚合函数；
没有group by就没有 Having
查询选修了3门以上课程的学生
select sno,count(cno)
from sc
group by sno
having count(cno) >= 3;
查询平均成绩在80分以上的学生
select sno,avg(grade)
from sc
group by sno
having avg(grade)>=80;

四、 连接查询
Select
From
Where
Group by
Having
Order by
查询刘晨的所有成绩
select grade
from sc join student on student.sno = sc.sno
where sname = ‘刘晨’;
注意事项：（1）不是任意2个表都可以连接，只有 有相关列的2个表才可以连接；
（2）n个表连接，就有n-1个连接条件
数据库课程的成绩
select sno,sc.cno,cname,grade
from sc join course on sc.cno = course.cno
where cname = ‘数据库’;
数据库课程的平均成绩
Select 平均成绩
From sc join course on sc.cno = course.cno
Where 数据库课程

五、 子查询
谁和刘晨同岁？
select sno,sname
from student
where sage = (select sage
from student
where sname = ‘刘晨’)
and sname <> ‘刘晨’;
谁和刘晨在同一个系？