SQL语句总结

基本的增删改

增加

语法：

insert [into] table_name[(column_list)] values([val,...]);

语法注释：

• [into]：一个可选的关键字，可以将它用在 INSERT 和目标表之间。
• table_name：将要接收数据的表或 table 变量的名称。
• (column_list)：要在其中插入数据的一列或多列的列表。必须用圆括号将column_list括起来，并且用逗号进行分隔。
• values：引入要插入的数据值的列表。对于column_list（如果已指定）中或者表中的每个列，都必须有一个数据值。必须用圆括号将值列表括起来。如果 values列表中的值与表中列的顺序不相同，或者未包含表中所有列的值，那么必须使用column_list明确地指定存储每个传入值的列。

示例代码：

insert into student(sno,sanme,age) values('0007','小明',19);

省略values清单的insert语句：
在T-SQL中，有一种简单的插入多行的方法。这种方法是使用SELECT语句查询出的结果代替values子句。这种方法的语法结构如下:

insert into table_name(column_name1,...) select子句;

示例代码：

insert into 学生(学号,姓名,年龄) 
	select sno,sname,age from student;

insert into 学生 
	select * from student;--(表结构相同时)

更新

语法：

update table_name
	set column_name1=value1,column_name2=value2,...
	where some_column=some_value;

语法注释：

• table_name：需要更新的表的名称。
• set：指定要更新的列或变量名称的列表。
• column_name：含有要更改数据的列的名称。

示例代码：

update student set age = 21 where sno = '0007';

删除

语法：

delete from table_name
	[from {<table_source>}[,...n]]
	[where {<search_condition>}];

语法注释：

• table_name：是要从其中删除行的表的名称。
• from< table_source >：指定附加的 from子句。
• where：指定用于限制删除行数的条件。如果没有提供 where子句，则 DELETE 删除表中的所有行。
• <search_condition> ：指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。
  示例代码：

delete from student where age<20;

清空表格：
语法：

truncate table table_name;

示例代码：

truncate table student;

查询

基本语法

select select_list
	[into new_table_name] 
	from table_list
	[where search_conditions]
	[group by group_by_list]
		[having search_conditions]
	[order by order_list [asc|desc]]; 

语法注释：

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

简单查询

1-3 涉及到的表：teacher(tno,tname,tbirthday);

1. 查询教师表中全体教师的记录（全部字段）

select * from teacher;

2. 查询全体教师的编号和姓名（部分字段）

select tno,tname from teacher;

3. 查询教师表中全体教师的姓名及年龄（指定别名）

select tname as 姓名,
	'的年龄是：',
	year(getdate())-year(tbirthday) as 年龄
	from teacher;

4-5 涉及到的表：选修（学号，课程号，成绩）;

4. 查询选修了课程的学生号（去重）

select distinct 学号 from 选修;

5. 查询选修表中的前三条记录的信息（限制返回行数）

select top 3 * from 选修;

查询条件

常用查询条件：

涉及到的表：选修(学号,课程号,成绩);

1. 查询选修表成绩大于或等于50分的记录

select * from 选修 where 成绩>=50；

2. 查询选修表中课程号为‘001’的成绩在60分到70分之间的同学的学号和成绩。

select 学号,成绩from 选修 where 课程号='001' and 成绩>=60 and 成绩<70;
-- where 课程号='001' and ( 成绩 between 60 and 70);  

3. 查询选修表中学号为‘0001’的学生没有成绩的课程号。

select 课程号 from 选修 where 学号=‘0001'and 成绩 is null;

注意这里不能使用=null

连接查询

• 查询的列出自多个表
• 当多个关系具有相同的属性名时，为不致引起混淆，采用： <关系名>.<属性名或*>

1. 找出工资多于1230元的职工号和他们所在的城市

select 职工号，城市 
	from 仓库，职工 
	where 仓库.仓库号 = 职工.仓库号 and 工资 > 1230;

嵌套查询

查询的结果出自一个关系，但条件涉及多个关系。

1. 哪些城市至少有一个仓库的职工的工资为1250元？

select 城市 from 仓库
	where 仓库号 in
		（select 仓库号 from 职工 where 工资 = 1250）;

2. 找出那些所有职工的工资都多于1210元的仓库的信息

相当于：没有一个职工的工资少于或等于1210元的仓库的信息

select * from 仓库 where
	仓库号 not in（select 仓库号 from 职工 where 工资<1210）
	and 仓库号 in（select 仓库号 from 职工）;

3. 找出和职工“E4”挣同样工资的所有职工

select 职工号 from 职工 where
	工资 = （select 工资 from 职工 where 职工号 = 'E4'）;

统计

使用集合函数

• 求平均值函数AVG：返回数值型列值的算术平均值.
• 统计个数函数COUNT：返回列中不为NULL值的数据个数。
• 求最大值函数MAX：返回数值、字符和日期型列的最大值。
• 求最小值函数MIN：返回数值、字符和日期型列的最小值。
• 求和函数SUM：返回数值型列的总和。

示例代码

1. 查询所有学生的成绩平均分。（涉及到的表：涉及的表：选修(学号,课程号,成绩)）

select avg（成绩） as 平均分 from 选修;

2. 查询学号为‘0001’ 学生的成绩的最高分

select max(成绩) as 最高分 from 选修 where 学号 = '0001';

3. 查询大于学生平均年龄的学生信息。（涉及到的表：涉及的表：S(sno,sanme,age)）

select * from s where age>(select avg(age) from s);

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

4. 关于count函数的使用：
   对于表供应商（供应商号，供应商名，地址）

select count(distinct 地址) from 供应商;
-- 消除重复，去掉空值，查询的是供应商所在地的数目
select count(*) from 供应商;
-- 查询的是供应商的个数，实际上统计的是记录的个数

分组

group by子句

1. 查询每门课程的平均成绩

涉及的表：选修(学号,课程号,成绩)

select 课程号,avg(成绩) form 选修 group by 课程号;

having子句

having子句指定分组搜索条件来挑选结果集中出现的组。

having与where区别:

• where子句作用于表或视图，在分组前进行，对行进行选择，且不能包含集合函数;
• having子句作用于组，在分组后进行，挑选满足条件的组，可包含集合函数。having子句可有比较运算符（可带all或any）、between、in、like、is null以及逻辑算符and、or、not。

where、group by和having子句的执行顺序:

• where子句、group by子句、having子句

1. 查询每个学生所选课程的数量及平均成绩

涉及的表：选修(学号,课程号,成绩)

select 学号,count(*),avg(成绩) from 选修 group by 学号;

2. 查询至少选了两门课的学生所选课程的门数及平均成绩。

select 学号,count(*),avg(成绩) from 选修 
	group by 学号 having count(*)>=2;

3. 查询每个成绩大于等于80分的学生的课程数量

select 学号,count(*) from 选修 
	where 成绩 >= 80 group by 学号;

子查询

带有any或all运算符的子查询

• any、some：只要子查询中有一行能使结果为真，则结果就为真。
• all：要求子查询中的所有行都使结果为真，结果才为真。

1. 查询有职工的工资大于或等于WH1仓库中任意一名职工的工资的仓库号。

职工（仓库号，职工号，工资）

select distinct 仓库号 from 职工 
	where 工资 >= 
	(select min(工资) from 职工 where 仓库号='WH1');

等价于：

select distinct 仓库号 from 职工 
	where 工资 >= any
	(select 工资 from 职工 where 仓库号='WH1');

2. 查询有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号。

select distinct 仓库号 from 职工 
	where 工资>=all 
	(select 工资 from 职工 where 仓库号='WH1');

等价于：

select distinct 仓库号 from 职工 
	where 工资>= 
	(select max(工资) from 职工 where 仓库号='WH1');

带有exists运算符的子查询

• 用来检查在子查询中是否有结果返回(即是否存在元组)。
• exists：当子查询中有满足条件的数据时， exists返回真值；否则返回假值。
• not exists：当子查询中有满足条件的数据时， exists返回假值；当子查询中不存在满足条件的数据时，则返回真值。
• 带exists谓词的查询是先执行外层查询，然后在执行内层查询。由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果决定。
• 由于exists的子查询只能返回真值和假值，因此在子查询中指定列名是没有意义的。所以在有exists的子查询中，其目标列名序列通常都用“*”。

1. 检索那些仓库中还没有职工的仓库信息。

职工（仓库号，职工号，工资）仓库（仓库号，城市，面积）

select * from 仓库 where
	not exists
	(select * from 职工 where 仓库号=仓库.仓库号);

等价于：

select * from 仓库 
	where 仓库号 
	not in (select 仓库号 from 职工);

2. 检索那些仓库中至少已经有一个职工的仓库信息。

select * from 仓库 
	where exists 
	(select * from 职工 where 仓库号=仓库.仓库号);

等价于：

select * from 仓库 
	where 仓库号 in    
	(select 仓库号 from 职工);

like表达式

• 用于字符串的比较，在SQL中选择部分值符号“_”表示单个未说明的字符，符号“％”表示0个或多个字符。
• 例： 以s开头并且大于两个字符：“s_%”
• 例： 转义字符[]：“%[%]%”

1. 查找所有张姓同学的信息。

select * from s where
	sname like '张%';