最全的基础SQL语法/学习要点

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

在SQL中建立关系模式结构：

定义SQL关系create table

可以使用"create table"命令定义SQL关系
可用以下几条命令创建department关系(院系)：

create table department
(   
      dept_name varchar(20), --“系名”，最大长度为20的字符串
      building varchar(15),  --最大长度为15的字符串
      budget numeric(12,2),  --12位数，其中2位数字在小数点后面
      primary key(dept_name)  --关键字primary key指明属性dept_name是department关系的主码
      
      //primary key声明的属性构成关系的主码，作为主码的属性可能只有一个属性，
      //也有可能有多个属性组合，主码属性非空且唯一
)

外码约束foreign key

主码约束primary key

外码声明表示关系中任意元组在属性上的取值必须对应于另一关系中某元组在主码属性上的取值。

create table instructor
( 
      ID   char(5),
      name  varchar(20) not null,  --定义name非空
      dept_name varchar(20),
      salary  numeric(8,2),
      primary key(ID),
      foreign key(dept_name)references department  --foreign key即外码声明了该关系的属性dept_name是依赖于另一关系department

      //对每个教师元组来说，该元组所表示的系名必然存在于关系department的主码属性（系名）中
      //通过这个约束避免某门课程指定了一个不存在的系名
);

完整性约束

完整性约束保证授权用户对数据库所做的修改不会破坏数据的一致性，可以防止对数据的意外破坏。
增加描述：
not null
primary key()
foreign key() references r
check --对属性域加以限制

插入与删除

插入数据 insert

insert into

insert into instructor values('10211','smith','Biology',66000);
//values中给出值的顺序应该对应属性在关系模式中列出的顺序

清除数据 delete

delete from

delete from instructor
where dept_name = 'Finance'
//delete命令只是清除了数据，但是关系任然存在数据库系统

数据更新update

update / set

update instructor
set salary=salary*1.05
where salary<=10000

update instructor
set salary=case
     when salary<=100000 then salary*1.05
     else salary*1.03
     end;

删除表结构 drop table

drop table r
//使用这条命令从数据库中删除关于关系r的所有信息，
//执行后关系r不再存在，不能使用insert来添加数据

属性的修改alter table

增加属性：

alter table r add A D
//需要修改的关系 r,add往关系里添加属性
//A表示待添加属性的名字，D表示待添加属性的域

删除属性：

alter table r drop A
//r为关系名，A表示关系的一个属性的名字

查询语句select

SQL查询的基本结构由3个子句构成：
select / from / where
查询的输入是在from子句中列出的关系，在这些关系上进行where和select子句中指定的运算，然后产生一个关系作为结果。

select name
from instuctor
where depat_name='computer' and salaty>7000;
//最终的输出结果是输出满足约束条件：系名为计算机，工资大于7000的所有老师的名字
//其中where子句的作用是从关系中选出符合要求的元组，即记录筛选

select sname,salary*1.2
from teacher
//基于teacher表，希望查看“若将每位教师的工资提高20%后”
//各位教师的姓名和工资是多少；

去除重复元组distinct

distinct

select distinct dept_name from instructor
//可去掉重复元素（若系名出现多次），每个系名只出现一次

SQL实现多个关系上的数据查询

select name,instructor_name,building
from instructor,department
where instructor_name=department_name;
//where子句的作用是限制两个关系的笛卡尔积所建立的组合，留下对所需答案有意义的组合

聚集函数avg/min/max

如果要计算表记录的统计数据就要用到聚集函数：
avg(平均值) min(最小值) max(最大值) sum(总和) count(计数)

select count(distinct ID)
from teaches
where semester='Spring' and year=2010;

分组聚集

group by子句

给出的一个或多个属性是用来构造分组。在group by子句中的所有属性上，取值相同的元组将被分在一个组中

select dept_name,avg(salary)
from instructor
group by dept_name;

查询course表中各个课程号与相应的选课人数：

select CID,count(*) as number from course     
group by CID
order by number desc

//count(*)就是选择所有属性，也就是计算有多少行
//如果没有group by则计算结果为总共的行数
//有了group by则是按照CID进行分组，计算每个分组中的行数
//并按照计算值的大小进行降序排序desc

Having子句

不是针对单个元组，而是针对group by子句构成的分组，having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数。

select dept_name,avg(salary)
from instructor
group by dept_name
having avg(salary)>42000;

更名运算as

更名运算as用来改变结果中的属性名字，as子句既可以出现在select子句中，也可以出现在from子句中。
old-name as new-name

select ID,name,salary/12 as monthly_salary
from instructor

select instructor as T,instrutor as S
where T.salary>S.salary and S.dept_name='comp.sci'

字符串运算（字符匹配like）

在字符串上可以使用like操作符来实现模式匹配
%匹配任意字串
_匹配任意一个字符
（模式是大小写敏感的，就是说大写字符与小写字符不匹配）

‘Intro%’可以匹配任何以Intro打头的字串
‘%Comp%’可以匹配任何包含Comp字串的字符串
‘_ _ ’匹配只包含3个字符的字符串
‘ _ _ %’匹配至少含3个字符的字符串

select *  --*用在select子句中表示所有的属性，表示教师表中的所有属性都被选中
from instructor
where name like '%dar%'
//找出名字里包含dar的教师

排列元组显示次序order by

order by子句可以让查询结果中元组按照排列顺序显示
asc 升序
desc 降序

select *
from instructor
order by salary desc,name asc
//先按照工资的降序列出整个关系，如果由几位教师的工资相同，就将它们按姓名升序排列

比较运算符between

也可以使用not between

select name
from instructor
where salary between 9000 and 10000
//等效where salary <=10000 and salary <=9000

集合运算Union/intersect

union(并集) intersect(交集) except(差运算)

（select course_id  
  from section
  where semester='Fall' and 'year'=2009）
  union
  (select course_id
   from section
   where semester='Spring' and 'year'=2010);

  //找出在2009年秋季开课或2010年春季开课，或两个学期都开课的所有课程
  //该运算自动去除重复，可使用union all来保留重复
  //若使用intersect则表示2009年秋季和2010年春季同时开课的所有课程
  //若使用except表示2009年秋季开课但不在2010年春季开课的所有课程集合

空值null

select name
from instructor
where salary is null
//找出关系表中工资为空值的所有老师

嵌套查询in/all

嵌套子查询可用在where子句，from子句，having子句中
运算符in / not in

select distinct course_id
from section
where semester = 'Fall' and year=2009 and course_id in(select course_id from semester='Spring' and year=2010);
//找出在2009年和2010年同时开课的课程

运算符all

select name
from instructor
group by depat_name
having avg(salary)salary >= all(select avg(salary) 
from instuctor 
group by depat_name)

关系连接

关系连接的类型
inner join / left outer join / right outer join / full outer join
关系连接的条件
natural / on / using(A1,A2…)

select name,course_id
       from instructor,teaches
       where instructor.ID=teaches.ID;

内连接/自然连接：只保留两张表中相同属性下取值相同的元组，其他不保留；
左连接：保留左边表所有的元组以及两张表中相同属性下取值相同的元组，没有的属性设置为null；
右连接：保留右边表所有的元组以及两张表中相同属性下取值相同的元组,没有的属性设置为null；

自然连接
自然连接只考虑那些在两个关系中都出现的属性上取值相同的元组对。

select name,course_id
from instructor natural join teaches
//来自instructor的元组和来自teaches的元组在共同属性ID上的取值相同
//这样查询不会重复列出那些在两个关系模式中都出现的属性，这样的属性只出现一次

左连接
基于表student、SC和course，查看到计算机系CS的每位学生所选课程的所有信息，包括学生姓名、所在系、课程名称、课程学分的情况。

SELECT *           --选择所有属性
FROM(SELECT *
FROM student
     WHERE DEPT='CS') as n
NATURAL LEFT JOIN SC LEFT JOIN course USING (CID);

//第一步学生表中选出所有的CS学生，再与SC表进行左连接，
//第二步将第一步得到的表与course表依据CID进行连接

视图creat view

视图是指计算机数据库中的一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。（我的理解是视图就是通过选择所需要的数据组成自己想要的表，然后就可以对这个表各种操作）

视图的字段是由我们自定义的，只供查询，数据不可更改，查询数据来源于我们建立的实体表。视图可以从多个复杂关联表中，提取出我们需要的信息，优化查询速度。

create view physics_fall_2009 as
select course.course_id,sec_id,building,room_number
from course,section
where course.course_id=section.course_id
      and course.dept_name='physics'
      and section.semester='Fall'
      and section.year='2009';
      select course_id
      from physics_fall_2009
      where buiding='Watson'

例如：
根据表student,SC,course创建视图 S_C_SC,使其具有如下内容：学号，姓名，课程名，成绩，并使用视图查询姓Wu的各门课程成绩。
student(SID,SNAME,DEPT,AGE,GENDER)
中文语义：学生（学号，姓名，系，年龄，性别）
course (CID,CNAME,DEPT,CREDITS,TEACHER)
中文语义：课程（课程号，课程名，系，学分，任课教师）
SC(SID,CID,GRADE)
中文语义：学习（学号，课程号，成绩）

create view S_C_SC_2 as
select student.SID,SNAME,student.DEPT,GRADE
from student,sc,course
where student.SID=sc.SID
and course.CID=sc.CID;
select *
from S_C_SC
where SNAME like 'Wu%'
//这里select后的SID前面要使用student的原因是确定是哪一个表中的SID
//因为student和sc中都有SID,DEPT同理，否则报错，SNAME由于只有
//student中才有，所以SNAME/student.SNAME均可

索引create index

索引是为了加速对表中的数据行的检索而创建的一种存储结构。（我的理解就是索引就是选一个关键字排序。）
注意：
建立索引要花费时间，并且占用存储空间。
建索引能加快检索速度，但是会减慢数据修改速度。

索引的类型
按索引的列数是单列还是多列
（1）单列索引：只有一列进行索引
（2）组合索引：由多列组成的索引
按索引关键字值是否有重复值
（1）唯一（unique）索引
（2）非唯一索引：索引的列值可以有重复值，如年龄索引
按照索引的顺序和数据库的物理存储顺序是否相同，可分为
（1）聚集索引（CLUSTERED）：也叫簇索引，会改变表中的记录的物理存储顺序，表的记录不再按输入记录的先后排列，而是按索引的顺序存放。
（2）非聚集索引（NONCLUSTERED）：表中记录的物理存储顺序并不改变，系统将使用索引页来创建一个索引结构，用以表示行的逻辑顺序。

创建索引
Create [unique] [clustered] [nonclustered] index index_name
on {table| view} (column [asc|desc][,…n])
[with index_option [,…n]]
[on filegroup]
删除索引
drop index ‘table.index| view.index’[ ,…n]
例：以学生年龄，姓名为索引，为student表创建索引INDEX_AGE_NAME

create unique index INDEX_AGE_NAME
on course(SNAME,AGE);

（以navicat为例）在这里可以通过新建查询，添加以上代码建立索引，也可通过表的设计表中的索引来进行物理设置。
注意：建立索引带来的影响不是对表的顺序排序，而是对所选出来的数据进行排序（点击新建查询，然后点击查询创建工具，选择某一个表中某一些的属性，点击确定，建立索引前和建立索引后所选数据组成的表的排序会有变化）

关系代数

关系代数是一种抽象查询语言，是关系数据操纵语言的一种传统表达方式。
关系代数与SQL语句之间一一对应，常用的关系代数符号有：并（U）,交（⌒）,投影（π）,选择（σ）,笛卡儿积（×）和链接(⋈)　。
例：

select name
from instructor;

对应的关系代数表达式为：
Πname(instructor)

(select course_id
from section
where 'year'=2009)
union
(select course_id
 from section
 where 'year'=2010);

对应的关系代数表达式为：
Πcourse_id
(σ ‘year’=2009∨‘year’=2010(section))

授权grant

授权是指给个别用户在数据库的某些部分授予几种形式的权限。
grant<权限列表>
grant语句用来授予权限，权限列表使得一个命令可以授予多个权限

grant select on department to Amit,Satoshi
//该授权语句是授予数据库用户Amit,Satoshi在department关系上的select权限
//该授权使得这些用户可以在department关系上执行查询

on<关系名或视图名>
on语句指出操作的对象，这里可以是关系，也可以是视图。

grant update(dudget)on department to Amit,Satoshi
//这条grant语句授予用户Amit,Satoshi在department关系的budget属性上的更新权限。

to<用户列表>
to语句列出权限授予的对象
revoke语句
revoke语句用来收回授予的权限

revoke select on department to Amit,Satoshi

SQL存储过程

SQL除了提供一些常用的内建函数，如聚集，日期等函数外，还可以编写存储过程，即自己定义的函数和过程，把它们存储在数据库里并在SQL语句中调用。

create function dept_count(dept_name varchar(20))
returns integer
begin
declare d_count integer;
    delect count(*)into d_count
    from instructor
    where instructor.dept_name = dept_name
 return d_count;
 end

函数的调用

select dept_name,budget
       from department
       where dept_count(dept_name)>12

触发器create trigger

触发器是一条语句，当对数据库作修改时，它自动被系统执行。

create trigger trigger_name trigger_time trigger_event on table_name for each row
begin
//触发后执行的操作
end

create trigger:创建一个触发器
trigger_name:触发器名字
trigger_time:触发器的时刻，包括after,before,代表在之前触发还是在之后触发
trigger_event:触发器事件，指明了激活触发程序的语句类型：insert,update,delete
table_name:需要建立触发器的表名

在定义行的时候，有new和old的区分，因为定义触发器的sql语句可以直接关联表中的任意列，但是不能直接使用列的名称去定义，因此在删除，更新，插入数据时候，新的列和以前旧的列都是同时存在的。

new.column_name 用来引用新行的一列
old.column_name 用来引用更新或删除之前已有行的一列
show triggers; 显示当前数据库下的触发器
drop trigger trigger_name; 删除触发器

例如：
一卡通卡基本信息表，消费表，充值表的模式：
卡基本信息表card(卡号cid, 学号sid, 余额balance)
消费表payment(消费单号pid, 卡号cid, 消费金额out_money)
充值表inpour(充值单号iid, 卡号cid, 充值金额in_money)
要求：在一卡通消费表(payment)上面建立触发器,使得在插入消费记录，删除消费记录，更新消费记录的同时，能同时更新卡基本信息表（card）中的余额。

create trigger payment_insert after insert on payment
for each row
begin
update card set balance=balance-new.out_money
where card.cid=new.cid;
end

create trigger payment_delete after delete on payment
for each row
begin
update card set balance=balance+old.out_money
where card.cid=old.cid;
end

例子：

//该触发器在任何一次对关系section的插入操作之后被启动
create trigger timeslot_check1 after insert on section
referencing new row as nrow     --定义了一个过度变量nrow用来在插入完成后存储所插入行的值
for each row    --显式地在每一个被插入地行上进行迭代
when(nrow.time_slot_id not in(
select time_slot_id 
from time_slot))
begin
rollback    --对引起触发器被执行的事务进行回滚，这样所有违背参照完整约束的事务都将被回滚
//从而确保数据库中的数据满足约束条件
end

触发器和存储过程的不同
存储过程可以被人为调用，触发器是在某种情况下的自动操作,不能被调用,旨在减轻数据库维护人员的工作。