SQL个人学习笔记01

1.DISTINCT：寻找表格内不同的值（多个空值将返回一个null）

SELECT DISTINCT column_name FROM table_name

2.TOP：将表中前面一定的行数返回到结果集

SELECT TOP n column_name FROM table_name;将table_name中前n行column_name列的值返回到结果集

SELECT TOP n PERCENT column_name FROM table_name;将将表table_name中前百分之n行column_name列的值返回到结果集

3.WHERE：WHERE子句中可使用比较运算符（=、> 、>=、<、<=、!=等）、逻辑运算符（OR AND NOT）、IN、BETWEEN、LIKE等

4.IN：在WHERE子句中已知至少一个我们需要的值

SELECT column_name FROM table_name WHERE column_name IN ('value_1','value_2')

按照一个或数个不连续的值查找表中数据

5.BETWEEN：在WHERE子句中已知我们需要的值的范围

SELECT * FROM table_name WHERE column_name BETWEEN value_1 AND value_2

注意日期类型：SELECT * FROM Orders WHERE OrderDate BETWEEN '1996/7/4' AND '1996/7/8'

BETWEEN 等价于 包含>=和<=的表达式

NOT BETWEEN 等价于 包含>和<的表达式

6.LIKE：实现字符串的模糊比较

SELECT column_name FROM table_name WHERE column_name LIKE '匹配的字符串描述'

通配符：

%：包含任意多位的字符串，LIKE '王%'表示以王开头的字符串

_：包含一位的字符串，LIKE '李_'表示以李开头的两个字

[ ]：包含指定范围的字符串，LIKE [1-9][2][09]表示以1~9的某个数字开头，第二个数字是2，第三个数字是0或9

[^]：除指定范围外的字符串，LIKE [^po]yle表示不以p或o开头且以yle结尾的长度为4的字符串

注意：对于查找datetime类型数据时，推荐使用LIKE+CONVERT()

1)查找1992，LIKE %92%,因为SQL Server将其转换为1992年11月4日00：00的形式

2)查找1996/8，SELECT * FROM Orders WHERE CONVERT(VARCHAR(50),OrderDate,120 )LIKE '1996-08%'

CONVERT()：格式转换函数，此处是将datetime类型的OrderDate转换为varchar(50)，第三个参数代表日期数据转换的格式，如下：

------------------------------------------------------------------------------------------------------------
Style(2位表示年份)   | Style(4位表示年份)   |   输入输出格式                                   
------------------------------------------------------------------------------------------------------------
0                               | 100                           |   mon dd yyyy hh:miAM(或PM)             
------------------------------------------------------------------------------------------------------------
1                               | 101   美国                |   mm/dd/yy                                      
------------------------------------------------------------------------------------------------------------
2                               | 102    ANSI               |   yy-mm-dd                                       
------------------------------------------------------------------------------------------------------------
3                               | 103    英法                |   dd/mm/yy                                      
------------------------------------------------------------------------------------------------------------
4                               | 104    德国                |   dd.mm.yy                                       
------------------------------------------------------------------------------------------------------------
5                               | 105    意大利             |   dd-mm-yy                                       
------------------------------------------------------------------------------------------------------------
6                               | 106                            |   dd mon yy                                       
------------------------------------------------------------------------------------------------------------
7                               | 107                            |   mon dd,yy                                       
------------------------------------------------------------------------------------------------------------
8                               | 108                            |   hh:mm:ss                                        
------------------------------------------------------------------------------------------------------------
9                               | 109                            |   mon dd yyyy hh:mi:ss:mmmmAM(或PM)
------------------------------------------------------------------------------------------------------------
10                             | 110    美国                 |   mm-dd-yy                                        
------------------------------------------------------------------------------------------------------------
11                             | 111    日本                 |   yy/mm/dd                                       
------------------------------------------------------------------------------------------------------------
12                             | 112    ISO                  |   yymmdd                                          
------------------------------------------------------------------------------------------------------------
13                             | 113     欧洲默认值     |   dd mon yyyy hh:mi:ss:mmm(24小时制)
------------------------------------------------------------------------------------------------------------
14                             | 114                            |   hh:mi:ss:mmm(24小时制)                   
------------------------------------------------------------------------------------------------------------
20                             | 120     ODBC 规范     |    yyyy-mm-dd hh:mi:ss(24小时制)        
------------------------------------------------------------------------------------------------------------
21                             |   121                           |    yyyy-mm-dd hh:mi:ss:mmm(24小时制)

------------------------------------------------------------------------------------------------------------

7.OEDER BY：让结果集按序显示

SELECT * FROM table_name [WHERE 条件] ORDER BY column_name [ASC,DESC]；默认为ASC

或多关键字排序：SELECT * FROM table_name [WHERE 条件] ORDER BY column_name1 [ASC,DESC], column_name2 [ASC,DESC]