二、DML—数据操纵语言
1、数据查询
数据查询是数据库中最常见的操作。在本文档里将作重点介绍。SQL语言提供SELECT语句,通过查询操作可得到所需的信息。
SELECT语句的一般格式为:
SELECT〈列名〉[{,〈列名〉}]
FROM〈表名或视图名〉[{,〈表名或视图名〉}]
[WHERE〈检索条件〉]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];
SELECT语句的执行过程是:
根据WHERE子句的检索条件,从FROM子句指定的基本表或视图中选取满足条件的元组,再按照SELECT子句中指定的列,投影得到结果表。
如果有GROUP子句,则将查询结果按照<列名1>相同的值进行分组。
选择列表中的列'change_log_200811.log_id' 无效,因为该列没有包含在聚合函数或GROUP BY 子句中。
如果GROUP子句后有HAVING短语,则只输出满足HAVING条件的元组。
如果有ORDER子句,查询结果还要按照<列名2>的值进行排序。
1.1、查询指定列
SELECT <列名> FROM <表名或视图名>
1.2、查询全部列
SELECT * FROM <表名或视图名>
或SELECT <全部列名> FROM <表名或视图名>
1.3、取消相同取值的行
在查询结果中有可能出现取值完全相同的行了。
SELECT DISTINCT <列名> FROM <表名或视图名>
1.4、比较大小
比较运算符有 =,>,>=,<=,<,<>,!>,!<
NOT+上述比较运算符
SELECT <列名> FROM <表名或视图名> WHERE <列名> [比较运算符] <比较的值>
1.5、多重条件查询
当WHERE子句需要指定一个以上的查询条件时,则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。
其优先级由高到低为:NOT、AND、OR,用户可以使用括号改变优先级。
SELECT <列名> FROM <表名或视图名> WHERE <条件1> AND <条件1> OR <条件1>…
1.6、确认范围查询
用于确定范围运算符有:BETWEEN…AND…和NOT BETWEEN…AND…
SELECT <列名> FROM <表名或视图名> WHERE <列名> [NOT] BETWEEN 值1 AND 值2
这与下等价
SELECT <列名> FROM <表名或视图名> WHERE <列名>>=值1 AND <列名><=值2
SELECT <列名> FROM <表名或视图名> WHERE <列名><值1 OR <列名>>值2
1.7、确认集合
确定集合符号:IN,NOT IN
SELECT <列名> FROM <表名或视图名> WHERE <列名>[NOT] IN (常量1,常量2,…,常量n)
1.8字符匹配查询
字符匹配查询符号:LIKE,NOT LIKE
SQL Server 2000支持如下四种通配符:
_(下划线):匹配任意一个字符;
%(百分号): 匹配O个或多个字符;
[ ]:匹配[ ]中的任意一个字符。如[acdg]表示匹配a或c或d或g,如果[ ]中的字符是有序的,则可以使用连字符一来简化[ ]中的内容,例如[abcde]可简写为:[a-e];
[^]:不匹配[ ]中的任意一个字符。如[^acdg]表示不匹配a、c、d、g,如果[^]中的字符是有序的,也可以使用简化形式例如[^abcde]可简写为:[^a-e]。
SELECT <列名> FROM <表名或视图名> WHERE <列名> [NOT] LIKE <匹配字符串>
1.9空值查询
空值不同于零和空格,它不占任何存储空间。
判断某个值是否为NULL值,不能使用普通的比较运算符(一、!一等),而只能使用专门的判断NULL值的子句来完成。
SELECT <列名> FROM <表名或视图名> WHERE <列名> IS [NOT] NULL
1.10常用库函数及统计汇总查询
常用的库函数
AVG: 按列计算平均值
SUM:按列计算值的总和
MAX:求一列中的最大值
MIN:求一列中的最小值
COUNT:按列值计算个数
总数:select count(field1) as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
select max(change_type) as maxvalue from change_log_200811
最小:select min(field1) as minvalue from table1
注1:SQL规定,当使用计算函数时,列名不能与计算函数一起使用(除非他们出现在其他集合中)。
例如查询年龄最大的学生的姓名和年龄,如下写法是错误的:
SELECT 姓名,MAX(年龄)FROM Student
注2:计算函数不能出现在WHERE子句中。 .
例如查询年龄最大的学生的姓名如下写法是错误的:
SELECT 姓名 FROM Student WHERE 年龄=MAX(年龄)
正确的命令应为:
SELECT 姓名,年龄 FROM Student
Where 年龄=(select max(年龄) from student)
1.11分组查询
SELECT <列名> FROM <表名或视图名>
GROUP BY<分组依据列>[,…n]
[HAVING<组提取条件>]
注1:分组依据列不能是text、ntext、image和bit类型的列。
注2:有分组时,查询列表中的列只能取自分组依据列(计算函数中的列除外)
1.12对查询结果进行排序
SELECT <列名> FROM <表名或视图名>
ORDER BY<列名>[ASC l DESC][,…n]
1.13数据表连接查询
A INNER JOIN:
这是最普通的联接类型。只要在这两个表的公共字段之中有相符值,内部联接将组合两个表中的记录。
SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];
B、left outer join:
左外连接(左连接):结果集包括连接表的匹配行,也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
select * from member_info_1 left join member_info_mapping on member_info_1.uuid=member_info_mapping.uuid
select * from table1 left join table2 on table1.id=table2.id
C:right outer join:
右外连接(右连接):结果集包括连接表的匹配连接行,也包括右连接表的所有行。
D:full outer join:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
1.14使用TOP限制结果集
使用TOP谓词时注意最好与ORDER BY子句一起使用,因为这样的前几名才有意义。但当使用WITH TIES时,要求必须使用ORDER BY子句。
TOP谓词写在SELECT单词的后边,查询列表的前边。
使用TOP谓词的格式为:
TOP n[percent]with ties]
其中:n为非负整数。
TOP n:表示取查询结果的前n行;
TOP n percent:表示取查询结果的前n% 行;
With ties:表示包括并列的结果。
1.15将查询结果存入表中
INTO子句的语法格式为:
INTO 新表名
INTO子句跟在SELECT子句之后、FROM子句之前。SELECT <列名> INTO 新表名 FROM。
新表名是要存放查询结果的表名,SELECT INTO语句包含两个操作:首先按查询列表创建新表,然后执行查询语句,并将结果保存到新表中。
用INTO子句创建的新表可以是永久表,也可以是临时表。临时表又分为两种:局部临时表和全局临时表。局部临时表要在表名前加#,它只能用在当前的连接中;全局临时表要在表名前加##,它的生存期为创建全局临时表的连接的生存期
1.16合并查询
使用UNION的格式为:
SELECT 语句1
UNION
SELECT 语句2
UNION [ALL]
SELECT 语句n
使用UNION的两个基本规则是:
A、所有查询语句中的列个数和列的顺序必须相同。
B、所有查语句中的对应列的数据类型必须兼容。
1.17子查询
A、使用子查询进行比较测试
使用子查询进行比较测试时,通过比较运算符(=、!=、<、>、<=、>=),将一个表达式的值与子查询返回的单值进行比较。如果比较运算的结果为True,则比较测试也返回True。
使用子查询进行的比较测试要求子查询语句必须是返回单值的查询语句。
例1:查询修了"c02"课程的且成绩高于此课程的平均成绩的学生的学号和成绩。
SELECT 学号,成绩 FROM SC
WHERE 课程号=‘c02’
and 成绩>( SELECT AVG(成绩) from SC
WHERE 课程号=‘c02’)
B、使用子查询基于集合的测试
使用子查询进行基于集合的测试时,通过运算符IN和NOT IN,将一个表达式的值与子查询返回的结果集进行比较。这同前边在WHERE子句中使用的IN作用完全相同。使用IN运算符时,如果该表达式的值与集合中的某个值相等,则此测试为True;如果该表达式与集合中的所有值均不相等,则返回False。
注意:使用子查询进行基于集合的测试时,由该子查询返回的结果集是仅包含单个列的一个列表,该列必须与测试表达式的数据类型相同。当子查询返回结果之后,外层查询将使用这些结果。
C 使用子查询进行存在性测试
使用子查询进行存在性测试时,往往使用EXISTS谓词。带EXISTS谓词的子查询不返回查询的数据,只产生逻辑真值和逻辑假值。
例6:查询选修了‘‘c01”号课程的学生姓名。
SELECT 姓名 FROM Student
WHERE EXISTS
(SELECT * FROM SC
WHERE 学号=Student.学号
AND 课程号=‘c01’)
注1:带EXISTS谓词的查询是先执行外层查询,然后再执行内层查询。由外层查询 的值决定内层查询的结果;内层查询的执行次数由外层查询的结果数决定。
上述查询语句的处理过程为:
(1)找外层表Student表的第一行,根据其学号的值处理内层查询;
(2)用外层的值与内层的结果比较,由此决定外层条件的真、假值;如果为真,则此记录为符合条件的结果;
(3)顺序处理外层表Student表中的第2、3、…行。
注2:由于EXISTS的子查询只能返回真或假值,因此在这里给出列名无意义。所以在有EXISTS的子查询中,其目标列表达式通常都用“*”。
2.数据更新
SQL语言的数据更新语句DML主要包括插入数据、修改数据和删除数据三种语句。
2.1插入一行新记录
INSERT INTO <表名>[(<列名1>[,<列名2>…])] VALUES(<值>)
2.2插入一行的部分数据值
只写上部分列名,没有写上的列名值自动为空,如果列是NOT NULL则必需赋值。
2.3插入多行记录
INSERT INTO <表名> [(<列名1>[,<列名2>…])] 子查询
2.4修改数据
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>]…
[WHERE <条件>]
2.5删除记录
DELETE
FROM<表名>
[WHERE <条件>]