SQL关键字
不等号(!=,<>)
查询出来的结果集不包含有当前字段为null的数据
<> 是标准的sql语法, 开发中尽量使用<>, 会将字段为null的数据也当做满足不等于的条件而将数据筛选掉
!=不能比较null值,最好用ISNULL()转一下在比较
Like
% |
包含零个或多个字符的任意字符串。 |
WHERE title LIKE '%computer%' 将查找在书名中任意位置包含单词 "computer" 的所有书名。 |
(下划线) |
任何单个字符 |
WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字(Dean、Sean 等)。 |
[ ] |
指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。 |
WHERE au_lname LIKE '[C-P]arsen' 将查找以 arsen 结尾并且以介于 C 与 P 之间的任何单个字符开始的作者姓氏,例如 Carsen、Larsen、Karsen 等。 在范围搜索中,范围包含的字符可能因排序规则的排序规则而异。 |
[^] |
不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。 |
WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始并且其后的字母不为 l 的所有作者的姓氏。 |
LIKE 支持 ASCII 模式匹配和 Unicode 模式匹配。
当所有自变量 (match_expression,模式,和escape_character,如果存在) 是 ASCII 字符数据类型,将执行 ASCII 模式匹配。 如果任何一个参数为 Unicode 数据类型,则所有参数都将转换为 Unicode,并执行 Unicode 模式匹配。 当你使用 Unicode 数据 (nchar或nvarchar数据类型) 具有类似的内容,尾随空格有意义; 但是,非 Unicode 数据,尾随空格则没有意义。
备注:like比较受排序规则影响
like多值匹配
SELECT * FROM kp_study_type WHERE outSystemSign = 'guangdongjlcyry' AND valid = 1 AND studyName LIKE '%继[教续]%' ; //包含'继续'或'继教' 注意:'%[0123456789]%'筛选出所有带数字的记录
Between…and 包括边界,not between…and 不包括边界s
Union
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同
默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
Select into
从一个表复制数据,然后把数据插入到另一个新表中
注意:MySQL 不支持 SELECT ... INTO 语句,但支持 INSERT INTO ... SELECT
可以使用以下语句来拷贝表结构及数据:
CREATE TABLE 新表 SELECT * FROM 旧表
SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可:
SELECT * INTO newtable FROM table1 WHERE 1 = 0;
Insert into select
从一个表复制数据,然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
INSERT INTO table2 SELECT * FROM table1;
Contains
只能在where子句中使用,可以指定逻辑操作符(包括 AND ,AND NOT,OR )
查询以 '湖' 开头的地址
SELECT
student_id,
student_name
FROM students
WHERE CONTAINS(address, '"hu*"')
如果要检查的是两个关键字,如是或地关系:
SELECT * FROM company
WHERE CONTAINS(*, '"北京" or "tttt"')
如果是与的关系:
SELECT * FROM company WHERE CONTAINS(*, '"北京" and "tttt"')
可以搜索:
1、词或短语。
2、词或短语的前缀。
3、与另一个词相邻的词。
由另一个词的词形变化而生成的词(例如,drive 一词是 drives、drove、driving 和 driven 词形变化的词干)。
4、使用同义词库确定的另一个词的同义词(例如,metal 一词可能有 aluminum 和 steel 等同义词)
Exists
EXISTS检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,内部有一个子查询语句(SELECT ... FROM...),返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值。
exists (sql 返回结果集为真) ,not exists (sql 不返回结果集为真)
一种通俗的可以理解为:将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE,这一行行可作为外查询的结果行,否则不能作为结果。
可以通过使用 EXISTS 条件句防止插入重复记录。
INSERT INTO TableIn (ANAME, ASEX) SELECT TOP 1 '张三', '男' FROM TableIn WHERE NOT EXISTS (SELECT * FROM TableIn WHERE TableIn.AID = 7)
EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN不走索引,但要看实际情况具体使用:
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况(需要遍历外表)
比较使用 EXISTS 和 IN 的查询。注意两个查询返回相同的结果。
SELECT * FROM TableIn WHERE EXISTS (SELECT BID FROM TableEx WHERE BNAME = TableIn.ANAME) SELECT * FROM TableIn WHERE ANAME IN (SELECT BNAME FROM TableEx)
比较使用 EXISTS 和 = ANY 的查询。注意两个查询返回相同的结果。
SELECT * FROM TableIn WHERE EXISTS (SELECT BID FROM TableEx WHERE BNAME = TableIn.ANAME) SELECT * FROM TableIn WHERE ANAME = ANY (SELECT BNAME FROM TableEx)
在子查询中使用 NULL 仍然返回结果集
select * from TableIn where exists(select
null)
等同于: select * from TableIn
例子:
Id Name Class Count Date
1 苹果 水果 10 2011-7-1
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 白菜 蔬菜 12 2011-7-1
2 青菜 蔬菜 19 2011-7-2
得到下面结果:(Id唯一,Date选最近的一次)
1 香蕉 水果 15 2011-7-3
2 青菜 蔬菜 19 2011-7-2
法一:
SELECT Id, Name, Class, Count, Date
FROM table t
WHERE (NOT EXISTS
( SELECT Id, Name, Class, Count, Date FROM table
WHERE Id = t.Id AND Date > t.Date))
法二:
SELECT Id, Name, Class, COUNT, Date FROM table1 t WHERE (Date = ( SELECT MAX(Date) FROM table1 WHERE Id = t.Id))
注意:distinct得不到这个结果, 因为distinct是作用与所有列的
CREATE INDEX
在表上创建一个简单的索引。允许使用重复的值:
CREATE UNIQUE INDEX
在表上创建一个唯一的索引。不允许使用重复的值:唯一的索引意味着两个行不能拥有相同的索引值
排序:TOP、LIMIT、ROWNUM
//Sql Server
SELECT TOP number|percent column_name(s) FROM table_name;
//MySQL
SELECT * FROM Persons LIMIT 5;
//Oracle
SELECT * FROM Persons WHERE ROWNUM <= 5;
<![CDATA[ ]]>(用于xml文档)
- CDATA 部分中的所有内容都会被解析器忽略
- 不能包含字符串 "]]>"
- 结尾的 "]]>" 不能包含空格或折行
- 不能嵌套
Xml文档中非法的 XML 字符必须被替换为实体引用
转义序列字符之间不能有空格;
转义序列必须以”;”结束;
单独出现的”&”不会被认为是转义的开始
< |
< |
非法 |
& |
& |
|
> |
> |
合法,习惯替换为实体引用 |
' |
' |
|
" |
" |
<![CDATA[]]>
、xml转义字符比较:
- <![CDATA[]]>不能适用所有情况,转义字符可以;
- 对于短字符串<![CDATA[]]>写起来啰嗦,对于长字符串转义字符写起来可读性差;
- <![CDATA[]]>表示xml解析器忽略解析,所以更快
sql执行顺序
- FROM [left_table]
- <join_type> JOIN <right_table>
- ON <join_condition>
- WHERE <where_condition>
- GROUP BY <group_by_list>
- WITH <CUBE | RollUP>
- HAVING <having_condition>
- SELECT
- DISTINCT
- ORDER BY <order_by_list>
- <Top Num> <select list>