sql 基本语言要点

表

1.创建表

• 属性在前，数据类型在后 例如：sname char(20),

• 定义计算列 列名 as 表达式

• 主码有多个属性构成是，必须作为表级完整性进行定义 例如：
  primary key（sno ，cno），
  foreign key （sno）references student (sno),

• 定义有名称的约束方便后期修改
  int cno constraint cno_1 default 0

2.修改表（包含table关键字）

• alter table 表名{
  alter column 列名
  add
  drop 删除列或者删除约束
  drop constrain 约束名 [cascade联级删除 | restrict 限制， 有关联的将不删除] }
  例如：
  alter table student
  alter column sname char(50) not null
  3.删除表
  drop table

操作表数据

1.插入

• insert into 表名（列名）values（） char的值用单引号
• insert into 表名 select * 语句
• 例如：insert into student values(1,1,‘陈伟斌’,‘you’,‘2015-09-14 23:59:59’);

2.修改update

• update 表名 set 某列的数据的修改 where 某个元组的条件

3.删除记录

• 删除符合条件的记录
  delete from 表名 where
• 删除表中所有记录
  truncate table 表名

数据查询select

1.查询经过计算的值
select 2014-sage birthday from student where …

2.消除重复行（即投影之后的重复值）
select distinct sno from student （默认为all 不消除重复行）

3.where满足条件的元组

• 确定范围 ：between and ；not between and
• 确定集合：属性指在指定集合中 sdept in （‘cs’,‘MA’）括号内也可以为select语句
• 字符匹配：like （%任意字符长度,_单个字符，escape 定义转义符）例如where cname like ‘db \ _des_nnn’ escape ’ \ ’
• 涉及空值的查询 where info is not null

4.oder by asc升序 desc 降序默认为升序

5.聚集函数（只能用在select后和group by 之中的having）

• count（对元组进行计数值为null不影响结果）,sum,avg,max,min([distinct | all ] 列名)
• where筛选from指定的数据对象；group by用于对where结果进行分组，having对分组后的数据进行筛选
  例如：选修课程成绩超过80分且至少有两门的学生学号
  select sno
  from sc
  where score>80
  group by sno
  having count(*)>2
• group by rollup（a，b）：对a，b进行groupby ，然后对a进行groupby，最后还有个总计行
• group by cube（a，b）：对a，b进行groupby，然后分别对a和b进行groupby，最后加一个总计行
• group by grouping sets（a，b）分别对a和b进行groupby

数据查询：连接查询

1.等值以及非等值连接 and

2.join （在from之后和on一起使用，on表示连接条件）

• 内连接（inner join） join默认为内连接，from 表一 join 表二 on 连接条件

• 外连接：保留悬浮元组

  • 左外连接left outer join 保留左表所有行
  • 右外连接right outer join
  • 完全外连接 full outer join 包含两个表的所有行

• 交叉连接：两表的笛卡尔集：cross join

数据查询：嵌套查询

将一个查询快嵌套在 where或者having语句之后
1.带有in的子查询

• . 不相关子查询：子查询的查询条件不依赖于父查询
• . 相关子查询:例如
  找出每个学生超过他自己选修课程平均成绩的课程号
  select sno,cno
  from sc x
  where score >(
  select avg(score)
  from sc y
  where x.sno=y.sno
  )
  执行步骤分三步（由外向内）：（1）从外层取出一个x.sno=2020
  （2）完成内层查询，假设为80 （3）用80代替内层查询，完成外层查询
  注：有些in子查询可以被and连接运算代替，如果可以使用and的最好使用and因为sql对in相关子查询优化还不是太好

2. 比较子查询any all （也可用聚集函数来实现）

   ：子查询返回单值时可用比较运算符，返回多值时使用any，all

例如查询非计算机系比计算机所有学生年龄都小的学生姓名及年龄
select sname sage
from student
where sage<all(select sage
from student
where sdept=‘cs’
) and sdept<>‘cs’

用聚集函数实现：
select sname sage
from student
where sage<(select min(sage)
from student
where sdept=‘cs’
) and sdept<>‘cs’

3.exists子查询
：返回逻辑真假值，所有in，all，any，比较运算符，都可用exists等价替换

• 两个not exists：
  例如查询选修了全部课程的学生姓名

  • 关系代数分析：除运算，学生姓名对应的选课表(做学号和课程号的投影)投影学号的象集包含全部课程，这样的学生姓名即为所求。
    
    除法用两个not exist实现：除数作为第二层，被除数作为第三层且要将一二两层联系起来，第一层则是题目所需的列。
    select sname
    from student
    where not exists
    (
    select*
    from course
    where not exists
    ( select*
    from sc
    where cno=course.cno and sno=student.sno
    )
    )

  • 也可用谓词逻辑进行分析：再用谓词逻辑进行分析时因为sql里没有全称量词以及蕴含，因此必须要进行等价转换。
    
    

数据查询：集合查询

1.into：产生新表，将select查询所得的结果保存到一个新表中
select sno,sex
into sextable1
from student
where sex=1
2.union将多个select查询结果，合并展示
3.intersect两个查询结果的交集
4.except差集
except，union，intersect一般from的查询的表一般为同一个表。
select *
from student
where sex=1
union
select*
from student
where sno<4
5.CTE（common table expression）cte相当于临时表，生命周期为批处理语句执行完结束
with cte表名（属性名）as （select 语句）
之后可使用cte表名进行查询。
例如：with cte1（number，point）
as (select sno score from sc where cno=‘101’)

数据查询：派生表

1.子查询不仅可以写在where之后也可写在from之后构成临时派生表
from （select语句）as 派生表名[（属性名）]

视图

1. 不存储视图数据，只存储视图定义在数据字典当中
2. 创建视图：create view 视图名[(指定的属性列)] as select子查询with check option
3. 更新视图：视图是虚表，因此对视图的增删改，是对视图对应的基本表的修改，视图并不是全都可以更新的，一般行列子集视图（从单个基本表中导出，去掉了基本表的某些行或者某些列，但保留主码）可更新