sql代码:
建数据库和表:
Create database sc_db;
use sc_db;
create table student
(sno char(8),
sname char(8),
ssex char(2),
sage int,
sdept char(10));
create table course
(cno char(3) ,
cname char(12),
cpno char(3),
ccredit int);
create table sc
(sno char(8),
cno char(3),
grade int);
插入数据:
use sc_db;
insert student
values('95001','李勇','男',20,'CS');
insert student
values('95002','刘晨','女',19,'IS');
insert student
values('95003','王敏','女',18,'MA');
insert student
values('95004','张立','男',19,'IS');
insert student
values('95005','欧阳小峰','男',19,'RE');
insert course
values('1','数据库','5',4);
insert course
values('2','数学','5',4);
insert course
values('3','信息系统','1',4);
insert course
values('4','操作系统','6',3);
insert course
values('5','数据结构','7',4);
insert course
values('6','数据处理',NULL,2);
insert course
values('7','PASCAL语言','6',3);
insert sc
values('95001','1',92);
insert sc
values('95001','2',85);
insert sc
values('95001','3',88);
insert sc
values('95002','2',90);
insert sc
values('95002','3',82);
insert sc(sno,cno)
values('95003','3',70);
insert sc(sno,cno)
values('95004','4',89);
1.数据查询
- 列别名
select sname,2014-sage birthday
from student
- 增加常数列
select sname,'Year of Birth:',2014-sage
from student
- 去重
select distinct sno
from sc
/*默认是ALL*/
select sno
from sc
/*等价于*/
select all sno
from sc
- where子句条件查询
查询条件 | 谓词 | 举例 |
---|---|---|
比较 | =, >, <, >=, <=, !=, <>, !>, !< | |
范围 | BETWEEN AND, NOT BETWEEN AND | select sname,sdept,sage from student where sage between 20 and 23 |
集合 | IN, NOT IN | select sname,sdept,sage from student where sdept IN (‘CS’,‘MA’,‘IS’) |
字符匹配 | LIKE, NOT LIKE; _(下划线)代表单个字符,%(百分号)代表任意长度,长度可以为0;当查询结果中含有通配符时,需要使用ESCAPE进行转义 | 1.select sname,sdept,sage from student where sname LIKE ‘刘%’ 2.select cno,ccredit from course where cname LIKE ‘DB\_Design’ ESCAPE '\' |
空值 | IS NULL, IS NOT NULL | |
逻辑运算 | AND, OR, NOT |
- 聚集函数
聚集函数 | 含义 | 举例 |
---|---|---|
COUNT(*) | 统计元组个数 | select count(*) form student |
COUNT([DISTINCT|ALL] <列名>) | 某列中取值的个数 | select count(sno) from student |
SUM([DISTINCT|ALL] <列名>) | 计算一列值的总和,必须是数值型 | |
AVG([DISTINCT|ALL] <列名>) | 计算一列值的平均值,必须是数值型 | |
MAX([DISTINCT|ALL] <列名>) | 计算一列值的平均值,必须是数值型 | |
MIN([DISTINCT|ALL] <列名>) | 计算一列值的平均值,必须是数值型 |
注意:当聚集函数遇到空值时,除COUNT(*)外,都跳过空值而只处理非空值
- group by和having
/*
*按照sno分组,select后面就不能包含sno之外的属性
*但是可以让聚集函数的值成一列
*聚集函数所计算的范围是分组后各组的值
*/
/***1**/
select sno
from sc
group by sno
having count(*)>3
/***2**/
select cno,count(sno)
from sc
group by cno
- 多表查询
1.连接查询
2.嵌套查询
相关子查询的理解:找出每个学生超过他自己选修课程平均成绩的课程号
select sno,cno from sc x where grade >(select avg(grade) from sc y where y.sno=x.sno)
从外层查询中取出sc的一个元组a,将a的sno值(例如95002)传给内层查询:select avg(grade) from sc y where y.sno=‘95002’
执行内层查询得到86((82+90)/2),用该值代替内层查询,得到外层查询:select sno,cno from sc x where grade >86
3.带谓词ANY,ALL的子查询
select sname,sage
from student
where sage<any(select sage
from student
where sdept='cs')
and sdept <>'cs'
4.exists
select sname from student
where exists(select * from sc
where sno=student.sno and cno='1')
- 集合查询
并操作UNION,交操作INTERSECT和差操作EXCEPT
select * from student where sdept='CS'
UNION
select * from student where sage<=19
- 派生表查询
select sno,cno
from sc,(select sno,avg(grade) avg_grade from sc group by sno) as sno_avg_grade
where sc.sno=sno_avg_grade.sno and
sc.grade>=sno_avg_grade.avg_grade
2.添加数据
1.插入元组
insert
into sc(sno,cno)
values('95008','3')
2.插入子查询结果
create table dept_avgAge(
sdept char(15),
avg_age smallint)
insert
into dept_avgAge(sdept,avg_age)
select sdept,avg(sage)
from student
group by sdept
3.数据修改
1.修改一个元组的值
update student
set sage=22
where sno='95002'
2.修改多个元祖的值
update student
set sage=sage+1
update sc
set grade =0
where sno in(
select sno
from student
where sdept='CS'
)
4.数据删除
1.删除一个元组的值
delete
from student
where sno='95002'
2.删除多个元组
delete
from sc
delete
from sc
where sno in (
selete sno
from student
where sdept='CS'
)