SQL 语法笔记

前言

最近一直在做一款和数据库相关的软件，其中涉及到了不少SQL语句。现在软件也快做完了，就对前期学习进行总结并将学习到的SQL语句用法进行整理。

SQL编程习惯

编程一定要养成一个自己风格的习惯，优秀的编程习惯将会使得代码看起来清爽整洁。SQL不区分大小写，但对于SQL编程我的习惯是将SQL关键字全部进行大写，标识符全部小写,即大写关键字小写标识符。若标识符需要用两个以上单词解释时，单词间可以用下划线连接。例如：

SELECT name FROM authors WHERE state = ' NY' ORDER BY name

还有一种方式是标识符首字母大写，两个单词以上的标识符单词首字母大写，我正是这么做的。

SQL数据类型

以SQL Server为例，其支持的数据类型有char，nvarchar，text,nchar,nvarchar,ntext，int,float,real等。不同的DBMS支持的数据类型种类不太相同。
提示：SQL Server不支持double数据类型，若是使用SQL Server建立数据库又需要保留数据较多精度时，建议存储为nvarchar等字符型，取数据时进行转换以避免数据精度丢失。

SQL语法实操

一、建立数据库

CREATE DATABASE books_db
ON PRIMARY(
    /*数据文件具体描述*/
    NAME = 'books_data',            --主数据文件的逻辑名称
    FILENAME = 'D:\books_data.mdf', --主数据文件的物理名称
    SIZE = 5mb,                     --主数据文件的初始大小
    MAXSIZE = 100mb,                --主数据文件增长的最大值
    FILEGROWTH = 15%                --主数据文件的增长率
)
LOG ON(
    NAME ='books_log',
    FILENAME = 'D:\books_log.ldf',
    SIZE = 2mb,
    FILEGROWTH =1mb
)

二、删除数据库

SQL Server将数据库的清单存放在master系统数据库的sysdatabases表中，只需要查看该表是否存在于该数据库中就可以。

USE master
GO
IF  EXISTS(SELECT *FROM sysdatabases WHERE NAME = 'books_bd')
    DROP DATABASE books_db
GO

三、创建表

USE books_db
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='authors')
DROP TABLE authors
CREATE TABLE authors
(
    au_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,   --设置au_id字段为主键，非空，自增
    au_fname VARCHAR(15) NOT NULL,
    au_lname VARCHAR(15) NOT NULL,
    phone VARCHAR(12),
    address VARCHAR(20),
    city VARCHAR(15),
    state CHAR(2),
    zip CHAR(5)
)
GO

四、删除表

第三节中已经可以看到

USE books_db
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='authors')
DROP TABLE authors

五、插入数据到表

因为设置了主键为自增，所以主键处的值要省略。

USE books_db
INSERT INTO authors VALUES('Sarah','bUCHMAN','718-496-7223','75 Wwst 205 St','Bronx','ny','10468');

六、从表中检索数据

后面例子都将基于自己数据库中真实使用的数据进行操作演示。
（1）使用SELECT和FROM检索列
SELECT子句列出需要显示的列，FROM子句指定从中提取的列的表。

USE DatabaseName
SELECT Columns FROM TableName;

提示：列可以是一列也可以是多列
实操：

USE MeasureQtDB
SELECT PointName,CurrentValue,CurrentTime FROM SettlementMonitors;

若要检索全部的列，可以用

USE MeasureQtDB
SELECT * FROM SettlementMonitors;

可以看到从表中检索出的3列数据。

（2）创建列的别名

SELECT column1 AS alias,
column2,
column3 AS alias3
FROM table;

其中，列2没有起别名。

实操：

USE MeasureQtDB
SELECT PointName AS 'column1',CurrentValue AS 'column2', CurrentTime AS 'column3' FROM SettlementMonitors;

为了对比，将列名修改为column1，column2，column3，结果如下：

（3）使用DISTINCT消除重复行

SELECT DISTINCT columns FROM table;

提示：columns可以是一列也可以是多列
使用DISTINCT消除重复的行，是需要排序进行。排序需要大量开销，除非不得不做，否则不要使用DISTINCT。
（4）使用ORDER BY排序行

SELECT * FROM table ORDER BY column1 [ASC|DESC],column2 [ASC|DESC],column3 [ASC|DESC];

实操：

USE MeasureQtDB
SELECT * FROM SettlementMonitors ORDER BY PointName ASC,CurrentTime DESC;

首先按照点名，然后按照时间排序效果：

按列的相对位置排序

SELECT columns FROM SettlementMonitors ORDER BY column_num1 DESC,column_num2 DESC;

提示：其中column_num不能超出columns的个数。此方法比如column是第一个，column_num=1,排序column。
实操：

USE MeasureQtDB
SELECT PointName, CurrentValue,CurrentTime FROM SettlementMonitors ORDER BY 3 DESC,1 DESC;

效果（可以看到第三列按照降序排列，又按照第一列降序排列）：

（5）使用WHERE筛选行
条件的类型：比较=，<>,<,<=,>,>=;模式匹配LIKE;范围筛选BETWEEN;列表筛选IN;空值测试IN NULL.
1.比较操作符
实操：

USE MeasureQtDB
SELECT * FROM SettlementMonitors WHERE CurrentTime > '2016-08-20'

（6）使用AND,OR,NOT组合求反条件

AND实操：

USE MeasureQtDB
SELECT * FROM SettlementMonitors WHERE PointName='3GD-2' AND CurrentTime > '2016-08-20'

OR实操：

USE MeasureQtDB
SELECT * FROM SettlementMonitors WHERE PointName='3GD-2' OR CurrentTime > '2016-08-20'

NOT实操：

USE MeasureQtDB
SELECT * FROM SettlementMonitors WHERE (NOT PointName='3GD-2')

等价于

USE MeasureQtDB
SELECT * FROM SettlementMonitors WHERE PointName<>'3GD-2'

3.AND，OR和NOT一起使用
实操：

USE MeasureQtDB
SELECT * FROM SettlementMonitors WHERE (NOT PointName='3GD-2')AND CurrentTime>'2016-08-20' AND CurrentValue>136.0

（7）使用LIKE匹配模式
%匹配0个或多个字符串
_匹配任意一个字符串

'A%'    --匹配以A开头的长度大于或等于1的串，包括单个字母A.
'%s'    --匹配以s结尾的字符串
'%in%'  --匹配任意位置包含in的串
'____'  --匹配任意四个字符的串
'Qua__' --匹配以Qua开头的任何一个5字符串

使用LIKE模糊匹配，LIKE的位置处于比较操作。
实操：

USE MeasureQtDB
SELECT * FROM SettlementMonitors WHERE PointName LIKE '3GD-_'

USE MeasureQtDB
SELECT * FROM SettlementMonitors WHERE PointName LIKE '3GD-%'

转义字符！

'100%'  --未转义
'100!%' --转义，匹配'100%'
'_op'   --未转义
'!_op'  --转义，匹配'_op'

'[a-c]at'   --匹配'bat','cat'
'[bcf]at'   --匹配'bat','cat','fat'
'[^c]at'    --匹配'bat','fat',不匹配'cat'
'se[^n]&'   --匹配以se开头并且第三个字符不是n的、长度大于或等于2的串

（8）BETWEEN进行范围筛选
提示：低端值要小于等于高端值

WHERE Column BETWEEN low_value AND high_value

（9）使用IN进行列表筛选

WHERE column IN （value1,value2,value3）

等价于

WHERE column=value1 OR column=value2 OR column=value2

（10）IS NULL 测试空值

SELECT columns FROM table WHERE column IS[NOT]NULL

七、操作符和函数

（1）创建派生列
因为不需要检索，所以不需要FROM

USE MeasureQtDB
SELECT 2+3

创建派生列，并用别名标识

USE MeasureQtDB
SELECT CurrentValue,0.1 AS "Discount" , ProjectId*0.4 AS "NewValue" FROM SettlementMonitors

（2）执行算术运算（+，-，*，/）

USE MeasureQtDB
SELECT ProjectId,-ProjectId AS "NewValue" FROM SettlementMonitors

效果：

（3）确定计算的顺序
算术运算符的优先级高于比较操作符，比较操作符的优先级又高于逻辑操作符，其实和C/C++的一致。
（4）使用||连接串

'string1'||'string2'--等于string1string2

（5）使用SUBSTRING（）提取子串
子串是源串的连续字符序列，包括空串或源串本身。

SUBSTRING（string FROM start[FOR length]）

（6）使用UPPER（）和LOWER（）更改串的大小写

WHERE PointName = "JOHN" OR PointName = "John"

转换串的大小写

UPPER（String）
LOWER（String）

（7）使用TRIM（）修整字符