前言
最近一直在做一款和数据库相关的软件,其中涉及到了不少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()修整字符