数据库操作

插入

insert into table_name(column_list) values(expression)

示例： 向学生表中插入一条yuki，18岁的记录

insert into student(name,age) values('yuki',18)

当某项不定时以NULL代替,不能空缺

insert into student(id,name,age) values(1,'yuki',NULL)

多行插入

insert into table_name(column_list) select ...

使用时表结构应当相同。示例：将student表中部分信息插入到备份表student1中

insert into student1(id,name) 
select id,name from student

更新

在指定表中更新满足条件的项

update table_name
set
	column_name={expression|NULL|default}
where search_condition

示例：修改id为1的学生年龄为18

update student
set
	age=18
where id=1

删除

在指定表中删除满足要求的记录

delete from table_name where search_condition

清空指定表

truncate table table_name

查询

基本语法格式如下

select select_list
[into new_table_name]
from table_list
[where search_condtion]
[group by group_by_list]
	[having search_condition]
[ORDER BY order_list [ASC | DESC] ] 

• SELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、列表、变量等。
• INTO子句用于指定所要生成的新表的名称。
• FROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。
• WHERE子句用来限定查询的范围和条件。
• GROUP BY子句是分组查询子句。
• HAVING子句用于指定分组子句的条件。
• ORDER BY字句用于对查询的结果进行排序。

简易查询如下

select select_list
from table_source
where search_condition

select 修饰词

• ALL
• DISTINCT
  取消重复的行
• TOP
  限制返回行数

where 条件

区间查询

在指定区间内查询

 BETWEEN begin_expression AND end_expression

如果要求反则

 NOT BETWEEN begin_expression AND end_expression

字符串匹配

match_expression [NOT] LIKE pattern

空值查询

expression IS [NOT] NULL

注意一下的写法是错的

column_name = NULL

case

CASE
         ｛WHEN condition1  THEN  result1｝
         ｛WHEN condition2  THEN  result2｝
         ［ELSE resultn］
END

示例：把学生百分制的成绩换成5分制的成绩输出

SELECT  SNO, CNO
CASE
 WHEN SCORE>=80 AND SCORE<=100  THEN  5
WHEN SCORE>=70  AND SCORE<=80   THEN  4
WHEN SCORE>=60 AND SCORE<=70    THEN  3
ELSE 2
END  GRADE
FROM S_C

查询结果分组

select group_id,<function>(expression),...
from table_name
[where search_condition]
group by group_id
[having search_condition]

注意SELECT子句里只能出现GROUP BY子句里的列和分组统计列

SELECT 课程号, AVG(成绩)
FROM      选修
GROUP BY 课程号

HAVING子句指定分组搜索条件来挑选结果集中出现的组。
示例：查询至少选了两门课的学生所选课程的门数及平均成绩。

SELECT 学号,COUNT(*),AVG(成绩)
FROM    选修
GROUP BY  学号
HAVING  COUNT(*)>=2    

注意:having从分组中选择符合条件的记录,如果出现了在分组查询中没有出现的属性,那是不对的
示例:求每门课大于90分的人数

select claId,count(distinct id) as num
from score
group by claId
having grade>90

上面这个会报错，未知列grade在having条件中。
下面这个才是正解，先找出大于90分的记录再分组

select claId,count(distinct id) as num
from score
where grade>90
group by claId

HAVING与WHERE区别:
WHERE子句作用于表或视图，在分组前进行，对行进行选择，且不能包含集合函数;
HAVING子句作用于组，在分组后进行，挑选满足条件的组，可包含集合函数。HAVING子句可有比较运算符（可带ALL或ANY）、BETWEEN、IN、LIKE、IS NULL以及逻辑算符AND、OR、NOT。

WHERE、GROUP BY 和 HAVING 子句的执行顺序:
WHERE子句、GROUP BY子句、HAVING子句

查询结果排序

select ...
from table_name
where search_condition
order by order_expression[ASC|DESC]

示例:查询全体学生信息，查询结果按年龄的降序排列，相同年龄的按照学号升序排列。

SELECT *
FROM  S
ORDER BY  AGE DESC ,SNO ASC

汇总查询

• 求平均值函数AVG
  返回数值型列值的算术平均值.
• 统计个数函数COUNT
  返回列中不为NULL值的数据个数。
  统计不重复元素与非空值： count(dinstinct column_name)
  统计记录的个数，包括重复与空：count(column_name)
• 求最大值函数MAX
  返回数值、字符和日期型列的最大值。
• 求最小值函数MIN
  返回数值、字符和日期型列的最小值。
• 求和函数SUM
  返回数值型列的总和。

这些集合函数可以用在SELECT子句 中对查询结果进行计算，但不能用WHERE子句的一部分，如果需要的话可以在WHERE子句中通过子查询来使用集合函数获取相应的结果。

示例：查询大于学生平均年龄的学生信息

select *
from student
where 
	age> (select avg(age) from student)

带明细的汇总查询

在汇总的同时也现实详细信息

select column_list
from table_name
where search_condition
compute <function>(expression)

带明细的分组汇总查询

select column_list
from table_name
where search_condition
order by order_expression[ASC|DESC]
compute <function>(expression) by expression

示例：求平均成绩最高的学生学号及平均分。

select TOP(1)  id,avg(grade) as ans 
from score 
group by id 
order by ans DESC

SELECT  SNO, AVG(SCORE)
FROM      S_C
GROUP BY  SNO
HAVING  AVG(SCORE)>=
    (SELECT  MAX(AVG(SCORE))
     FROM  S_C
    GROUP BY  SNO)

嵌套查询

子查询

select ...
from table_source
where search_condition in
						   (select ...
							from table_source
							where search_condition)

注意外层判断条件被包含于子查询的选择
示例：查询选修了课程“Math”，但没有选修“sql”的学生的学号和姓名

select distinct id,name
from student
where
	claId='Math'
and
	id not in (select id from score where claId='sql')

示例：查询选修了课程“Matn”和“sql”的学生的学号和姓名

select distinct id,name
from student
where
	id in (select id from score where claId='Math')
and
	id in (select id from score where claId='sql')

量词

<expression> <compare>[ANY|SOME|ALL] (in select)

1. any,some
   只要子查询中有一行能使结果为真，则结果就为真。
2. all
   要求子查询中的所有行都使结果为真，结果才为真。

EXISTS

[not] exists (in select)

用来检查在子查询中是否有结果返回，即是否存在元组
由于EXISTS的子查询只能返回真值和假值，因此在子查询中指定列名是没有意义的。
带EXISTS谓词的查询是先执行外层查询，然后在执行内层查询。由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果决定。
示例：求年龄最大的学生信息

select *
from student ma
where not exists
	(select * from student mi where ma.age<mi.age)

选修了课程的学生信息

select *
from course s
where exists
	(select * from course where id=s.id)

选修了所有课程的学生信息

select * 
from course s
where not exists
	(select * from course where id=s.id and class is NULL)

连接查询

select ...
from table_source 
[inner|{left|right|full}[outer]] join table_source on joined_condition
where search_condition

内连接 inner

该关键字可缺省。使用时先生成一张笛卡尔积表，之后在笛卡尔积表中进行查询。用on限定可以过滤笛卡尔积表中不符合要求的记录。
示例：成绩为90分或以上的学生姓名、课程号和成绩

SELECT  name,class,grade
FROM    student  INNER JOIN  course
ON (student.class = course.class)
WHERE grade>= 90

外连接 outer

1. 左连接 left
   结果集中包括“左”表中的所有行及“右”表中所 有匹配的行，不包括“右”表中不匹配的
   //韦恩图
2. 右连接 right
   包括“右”表中的所有行及“左”表中所有匹配的行，不包括“左”表中不匹配的行。
   //
3. 全连接 full
   包括所有连接表中的所有行，不论它们是否匹配。

自连接

在一个表与其自身之间进行连接为自连接。在自连接中，给表取别名是必不可少的

别名：<关系名> <别名>

示例：列出每门课的先行课情况

select a.class,'after',b.class
from course a join course b
where a.before = b.class

查询学生表中同名的学生信息

select id,name
from student s1 join student s2
where s1.name=s2.name and s1.id <> s2.id

选修了两门或两门以上课程的学生学号及课程号

select distinct id,class
from course C1 join course C2
where C1.id<>C2.id and C1.class=C2.class

示例：查询选修了课程“C2”或“C4”的学生的学号和姓名

select id,name
from student s join course c on s.id=c.id
where c.class='C2' or c.class='C4'

查询选修了课程“C2”和“C4”的学生的学号和姓名

select s.name,c.id
from student s join score c on s.id==c.id
where 
	s.id in (select id from score where claId='C2')
and
	c.claId=="C4"

示例：查询选修了课程“C2”，但没有选修“C4”的学生的学号和姓名

select s.name,c.id
from student s join score c on s.id==c.id
where 
	s.id in (select id from score where claId='C2')
and
	c.claId!="C4"