数据库操作(增删改查)

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.数据查询

1. 列别名

select sname,2014-sage birthday
from student

2. 增加常数列

select sname,'Year of Birth:',2014-sage
from student

3. 去重

select distinct sno
from sc

/*默认是ALL*/
select sno
from sc
/*等价于*/
select all sno
from sc

4. 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

4. 聚集函数

聚集函数	含义	举例
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(*)外,都跳过空值而只处理非空值

5. 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

6. 多表查询
      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')

7. 集合查询
   并操作UNION,交操作INTERSECT和差操作EXCEPT

select * from student where sdept='CS'
UNION
select * from student where sage<=19

8. 派生表查询

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'
)