基本的增删改
增加
语法:
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);
- 查询教师表中全体教师的记录(全部字段)
select * from teacher;
- 查询全体教师的编号和姓名(部分字段)
select tno,tname from teacher;
- 查询教师表中全体教师的姓名及年龄(指定别名)
select tname as 姓名,
'的年龄是:',
year(getdate())-year(tbirthday) as 年龄
from teacher;
4-5 涉及到的表:选修(学号,课程号,成绩);
- 查询选修了课程的学生号(去重)
select distinct 学号 from 选修;
- 查询选修表中的前三条记录的信息(限制返回行数)
select top 3 * from 选修;
查询条件
常用查询条件:
涉及到的表:选修(学号,课程号,成绩);
- 查询选修表成绩大于或等于50分的记录
select * from 选修 where 成绩>=50;
- 查询选修表中课程号为‘001’的成绩在60分到70分之间的同学的学号和成绩。
select 学号,成绩from 选修 where 课程号='001' and 成绩>=60 and 成绩<70;
-- where 课程号='001' and ( 成绩 between 60 and 70);
- 查询选修表中学号为‘0001’的学生没有成绩的课程号。
select 课程号 from 选修 where 学号=‘0001'and 成绩 is null;
注意这里不能使用
=null
连接查询
- 查询的列出自多个表
- 当多个关系具有相同的属性名时,为不致引起混淆,采用: <关系名>.<属性名或*>
- 找出工资多于1230元的职工号和他们所在的城市
select 职工号,城市
from 仓库,职工
where 仓库.仓库号 = 职工.仓库号 and 工资 > 1230;
嵌套查询
查询的结果出自一个关系,但条件涉及多个关系。
- 哪些城市至少有一个仓库的职工的工资为1250元?
select 城市 from 仓库
where 仓库号 in
(select 仓库号 from 职工 where 工资 = 1250);
- 找出那些所有职工的工资都多于1210元的仓库的信息
相当于:没有一个职工的工资少于或等于1210元的仓库的信息
select * from 仓库 where
仓库号 not in(select 仓库号 from 职工 where 工资<1210)
and 仓库号 in(select 仓库号 from 职工);
- 找出和职工“E4”挣同样工资的所有职工
select 职工号 from 职工 where
工资 = (select 工资 from 职工 where 职工号 = 'E4');
统计
使用集合函数
- 求平均值函数AVG:返回数值型列值的算术平均值.
- 统计个数函数COUNT:返回列中不为NULL值的数据个数。
- 求最大值函数MAX:返回数值、字符和日期型列的最大值。
- 求最小值函数MIN:返回数值、字符和日期型列的最小值。
- 求和函数SUM:返回数值型列的总和。
示例代码
- 查询所有学生的成绩平均分。(涉及到的表:涉及的表:选修(学号,课程号,成绩))
select avg(成绩) as 平均分 from 选修;
- 查询学号为‘0001’ 学生的成绩的最高分
select max(成绩) as 最高分 from 选修 where 学号 = '0001';
- 查询大于学生平均年龄的学生信息。(涉及到的表:涉及的表:S(sno,sanme,age))
select * from s where age>(select avg(age) from s);
切记:这些集合函数可以用在SELECT子句中对查询结果进行计算,但不能用作where子句的一部分,如果需要的话可以在WHERE子句中通过子查询来使用集合函数获取相应的结果。
- 关于count函数的使用:
对于表供应商(供应商号,供应商名,地址)
select count(distinct 地址) from 供应商;
-- 消除重复,去掉空值,查询的是供应商所在地的数目
select count(*) from 供应商;
-- 查询的是供应商的个数,实际上统计的是记录的个数
分组
group by子句
- 查询每门课程的平均成绩
涉及的表:选修(学号,课程号,成绩)
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子句
- 查询每个学生所选课程的数量及平均成绩
涉及的表:选修(学号,课程号,成绩)
select 学号,count(*),avg(成绩) from 选修 group by 学号;
- 查询至少选了两门课的学生所选课程的门数及平均成绩。
select 学号,count(*),avg(成绩) from 选修
group by 学号 having count(*)>=2;
- 查询每个成绩大于等于80分的学生的课程数量
select 学号,count(*) from 选修
where 成绩 >= 80 group by 学号;
子查询
带有any或all运算符的子查询
- any、some:只要子查询中有一行能使结果为真,则结果就为真。
- all:要求子查询中的所有行都使结果为真,结果才为真。
- 查询有职工的工资大于或等于WH1仓库中任意一名职工的工资的仓库号。
职工(仓库号,职工号,工资)
select distinct 仓库号 from 职工
where 工资 >=
(select min(工资) from 职工 where 仓库号='WH1');
等价于:
select distinct 仓库号 from 职工
where 工资 >= any
(select 工资 from 职工 where 仓库号='WH1');
- 查询有职工的工资大于或等于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的子查询中,其目标列名序列通常都用“*”。
- 检索那些仓库中还没有职工的仓库信息。
职工(仓库号,职工号,工资)仓库(仓库号,城市,面积)
select * from 仓库 where
not exists
(select * from 职工 where 仓库号=仓库.仓库号);
等价于:
select * from 仓库
where 仓库号
not in (select 仓库号 from 职工);
- 检索那些仓库中至少已经有一个职工的仓库信息。
select * from 仓库
where exists
(select * from 职工 where 仓库号=仓库.仓库号);
等价于:
select * from 仓库
where 仓库号 in
(select 仓库号 from 职工);
like表达式
- 用于字符串的比较,在SQL中选择部分值符号“_”表示单个未说明的字符,符号“%”表示0个或多个字符。
- 例: 以s开头并且大于两个字符:“s_%”
- 例: 转义字符[]:“%[%]%”
- 查找所有张姓同学的信息。
select * from s where
sname like '张%';