SQL 学习笔记

Archived University Note

This content is from my university archives and may not be reliable or up-to-date.

SQL 学习笔记

基础查询

-- 查询指定列
select sno,sn,age from s
select * from sc

-- 列别名
select sn name, sno,age from s

条件查询

-- 简单条件
select SNo,score from sc where cno='c1'
select sno,cno,score from sc where score > 85

-- 复合条件
select SNo,score from sc where (cno='c1' or cno='c2') and score > 85

范围查询与模式匹配

-- BETWEEN 范围查询
select tno,tn,prof from t where sal between 1000 and 1500
select tno,tn,prof from t where sal not between 1000 and 1500

-- LIKE 模式匹配
select tno,tn from t where tn like '张%'  -- 以'张'开头
select tno,tn from t where tn like '%'    -- 包含任意字符
select tno,tn from t where tn like '_雪'  -- 第二个字是'雪'

聚合函数

-- 常用聚合函数：avg() sum() max() min() count()
select sum(score) as totalscore, avg(score) as avgscore
from sc where sno='s1'

-- 计算最高分、最低分和分数差
select max(score) as MaxScore, min(score) as MinScore, Max(Score)-Min(Score) as diff
from sc where (cno='c1')

-- 统计记录数
select count(*) from s where dept='计算机'

-- distinct 消除重复行（count对null不计算，对0计算）
select count(distinct dept) as deptnum from s

分组查询

-- GROUP BY 分组
select tno, count(*) as C_Num from tc Group by tno

-- HAVING 过滤分组
select Sno, count(*) as sc_num from sc group by sno having (count(*) >= 2)

排序查询

-- ORDER BY 排序
select sno,cno,score from sc
where cno in ('c2','c3','c4','c5')
order by sno, score desc

连接查询

-- 自连接
select X.TN, X.sal as Sal_a, Y.Sal as Sal_b
from T as x, T as Y
where X.Sal > Y.Sal and Y.TN = '刘伟'

子查询

/* 返回单值的子查询 */
select tno,tn from t
where prof = (Select prof From T where Tn='刘伟')

/* ANY 比较符 */
select tn from t
where (tno = any (select tno from tc where cno='c5'))
-- In可以代替 '=any'

/* ALL 比较符 */
select tn,sal from t
where (sal > all(select sal from t where dept='计算机'))

-- 等价写法
select Tn,Sal from T
where (Sal > (Select Max(sal) from t where dept='计算机'))
and (Dept <> '计算机')

相关子查询

/* 查看选修所有课程的学生姓名（搜索不存在没有选课信息学生的姓名）*/
select sn from s
where (not exists (
    select * from c
    where not exists(
        select * from sc
        where sno=s.sno and cno=c.cno
    )
))

数据操纵

-- 存储查询结果到表中
select sno as 学号, sum(score) as 总分
into #cal_table
from sc
group by sno

-- 插入数据
insert into s (sno,sn,age,sex,dept)
values ('s7','插入哥',21,'男','失业群众')

-- 添加一行记录的部分数据值
insert into sc (sno,cno) values ('s7','c1')

-- 添加多行记录
create table avgval (
    department varchar(20),
    average smallint
)
insert into avgval
select dept, avg(sal) from t group by dept

更新数据

-- 把刘伟老师转移到信息系
update t set dept='信息' where tn='刘伟'

-- 将所有学生的年龄增加一岁
update s set age=age-1

-- 把工资低于1000的讲师的工资提高20%
update t set sal=1.2 * sal
where (Prof='讲师') and (sal <= 1000)

-- 把讲授c5课程的教师的岗位津贴增加100元（未测试）
update t set Comm = Comm + 100
where (tno in (select tno from t, tc where t.tno = tc.tno and tc.cno = 'c5'))

-- 把所有教师的工资提高到平均工资的1.2倍（未测试）
update t set sal = (select 1.2 * avg(sal) from t)

删除数据

-- 删除数据
delete from t where tn = '刘伟'

-- 删除所有老师的授课记录
delete from tc

-- 删除刘伟老师授课
delete from tc
where (tno = (select tno from t where tn='刘伟'))

视图

-- 创建一个计算机系教师情况的视图 Sub_T
create view Sub_T AS
select Tno, Tn, Prof from t where dept = '计算机'

-- 创建一个学生平均成绩视图 S_Avg
create view S_avg(Sno,Avg) as
select sno, avg(Score) from sc group by sno