纯属笔记记录------大部分内容来自公众号SQL数据库开发
数据库:SQL Servers
本博客主要介绍的还是SQL Servers背景下关于sql的一些基本用法。笔者自身学习的是MySQL,若有疑问,欢迎留言。
目录
1、索引
索引是一种特殊的查询表,可以被数据库搜索引擎用来加速数据的检索。简单来说,索引就是指向表中数据的指针。数据库的索引同书籍后面的索引非常相像。例如,如果想要查阅一本书中与某个特定主题相关的所有页面,你会先去查询索引(索引按照字母表顺序列出了所有主题),然后从索引中找到一页或者多页与该主题相关的页面。简单而言,就是根据具体地址,查找对应位置。
(1)索引作用
索引能够提高SELECT查询和WHERE子句的速度,但却降低了包含UPDATE语句或INSERT语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。
创建索引需要使用CREATE INDEX语句,该语句允许对索引命名,指定要创建索引的表以及对哪些列进行索引,还可以指定索引按照升序或降序排列。
同UNIQUE约束一样,索引可以是唯一的。在这种情况下,索引会组织列中(或者列的组合,其中某些列有索引)出现重复的条目。
(2)CREATE INDEX命令
语法如下:
CREATE INDEX index_name ON table_name;
单列索引:
CREATE INDEX index_name ON table_name (column_name);
(3)唯一索引
唯一索引不仅用于提升查询性能,还能保证数据完整性。唯一索引不允许向表中插入任何重复值。
CREATE UNIQUE index_客户ID ON Customers (客户ID);
(4)聚簇索引
聚簇索引在表中两个或更多的列的基础上建立。其基本语法如下:
CREATE INDEX index_name ON table_name (column1,column2);
PS:创建单列索引还是聚簇索引,要看每次查询中,哪些列在作为过滤条件的WHERE子句中最常出现。如果只需要一列,创建单列;如果作为过滤条件的WHERE子句用到了两个或更多的列,那么聚簇索引就是最好的选择。
(5)隐式索引
隐式索引由数据库服务器在创建某些对象的时候自动生成。例如,对于主键约束和唯一约束,数据库服务器就会自动创建索引。
(6)删除索引
索引可以用DROP命令删除。删除索引时应当将是否会降低或者提高数据库性能考虑在内。基本语法:
DROP INDEX index_name ON table_name;
(7)避免使用索引
下面几条指导原则给出了何时应当考虑是否使用索引:
1)小的数据表不应当使用索引。因数据表较小,查询很快,创建索引会增加内存损耗。
2)需要频繁进行大批量的更新或插入操作的表。这样的表,插入和更新时,有索引效率会降低。
3)如果列中包含大数或NULL值,不宜创建索引。因为NULL值可能被处理为0或者“ ”,一旦建立索引,遇到真正的0或者“ ”发生冲突。(字段中有null值查询不走索引的问题解决)
4)频繁操作的列不宜创建索引。道理同2)
2、子查询
子查询(Sub Query)或者说内查询(Inner Query),也可以称作嵌套查询(Nested Query),是一种嵌套在其他SQL查询的WHERE子句种的查询。
子查询用于为主查询返回其所需数据,或者对检查数据进行进一步的限制。
子查询可以在SELECT、INSERT、UPDATE和DELECT语句中,同=、<、>、>=、<=、IN、BETWEEN等运算一起使用。
使用子查询必须遵循以下几个规则:
1)子查询必须括在圆括号中。
2)子查询的SELECT中一般只有一个列,除非主查询中有多个列,用于与主查询选中的列比较。
3)子查询不能使用ORDER BY。在子查询中GROUP BY可以起到与ORDER BY相同的作用。
4)返回多行数据的子查询只能同多指操作符一起使用,比如IN操作符。
5)SELECT列表中不能包含任何对BLOB、ARRAY、CLOB或者NCLOB类型值的引用。
6)子查询不能直接用在集合函数中。
7)BETWEEN操作符不能同子查询一起使用,但是BETWEEN操作符可以用在子查询中。
(1)SELECT子查询
基本语法如下:
SELECT column_name [,column_name]
FROM table1 [,table2]
WHERE column_name OPERATOR
(SELECT column_name [,column_name]
FROM table1 [,table2]
[WHERE])
(2) INSERT子查询语句
子查询可以用在INSERT语句中。INSERT语句可以将子查询返回的数据插入到其他数据表中。子查询选取的数据可以被任何字符、日期或数值函数所修饰。其基本语法如下:
INSERT INTO table_name [(column1 [,column2])]
SELECT [*|column1 [,column2]]
FROM table1 [,table2]
[WHERE VALUE OPERATOR]
实例:
INSERT INTO Customers_bak
SELECT * FROM Custormers
WHERE 客户ID IN
(SELECT 客户ID FROM Orders
WHERE 员工ID=9);
% 满足子查询中信息的表中插入Customers_bak
(3)UPDATE子查询语句
子查询可用于UPDATE中,当子查询同UPDATE一起使用的时候,即可以更新单个列,也可以更新多个列。其基本语法如下:
UPDATE table
SET column_name=new_value
[WHERE OPERATOR [VALUE]
(SELECT column_name
FROM table_name)
[WHERE]]
实例:将Customers表中在Orders表里有发货的客户,他们所在的城市要加一个“市”字。
UPDATE Customers
SET 城市=城市+'市'
WHERE 客户ID IN
(SELECT 客户ID FROM Orders)
% 满足子查询的城市更新为“城市市”
(4)DELECT子查询语句
其基本语法如下所示:
DELETE FROM table_name
[WHERE OPERATOR [VALUE]
(SELECT column_name
FROM table_name)
[WHERE]]
PS:子查询在平时的查询中用的比较多,可以很方便的将有关联的内容写在子查询中,然后将子查询的内容返回给主查询。原则上子查询嵌套不超过三层,如果超过,可以考虑使用临时表将子查询结果先保存,再和其他查询进行关联。
3、事务
事务是在数据库上按照一定的逻辑顺序执行的任务序列,既可以由用户手动执行,也可以由某种数据库程序自动执行。
事务实际上就是数据库的一个或多个更改。当你在某张表上创建更新或者删除记录的时,你就已经在使用事务了。控制事务以保证数据完整性,并对数据库错误做出处理,对数据库来说非常重要。
实践中,通常会将很多SQL查询组合一起,并将其作为某个事物一部分来执行。
(1)事务的属性
事务具有以下四个标准属性,通常用缩略词ACID来表示:
原子性:保证事务中的所有操作都执行完毕;否则,事务会在出现错误时终止,并回滚之前所有操作到原始状态。
一致性:如果事务成功执行,则数据库的状态得到了进行了正确的转变。
隔离性:保证不同的事务相互独立、透明地执行。
持久性:即使出现系统故障,之前成功执行的事务的结果也会持久存在。
(2)事务控制
有四个命令用于控制事务:
COMMIT:提交更改;
ROLLBACK:回滚更改;
SAVE:在事务内部创建一系列可以ROLLBACK的还原点;
SET TRANSACTION:命名事务。
COMMIT命令
COMMIT命令用于保存事务多数据库所做的更改。
COMMIT命令会将自上次COMMIT命令或者ROLLBACK命令执行以来所有的事务都保存到数据库中。其语法如下:
COMMIT;
实例:
BEGIN TRANSCATION
DELETE FROM customers_bak WHERE 姓名='张三'
COMMIT;
# 删除表中姓名为'张三'的记录,然后将更改提交(COMMIT)到数据库中
(3)ROLLBACK命令
ROLLBACK命令用于撤销尚未保存到数据库中的事务。ROLLBACK命令只能撤销自上次COMMIT命令或者ROLLBACK命令执行以来的事务。下面的示例将会从表中删除城市为“广州”的记录,然后回滚(ROLLBACK)对数据库所做的更改。
BEGIN TRANSCATION
DELETE FROM Customers WHERE 城市='广州'
ROLLBACK;
结果是删除操作并不会对数据库产生影响。
(4)SAVE命令
SAVE命令是事务中的状态点,使得我们可以将事务回滚至特定的点,而不是整个事务都撤销。
SAVE命令记录如下:
SAVE TRANSCATION save_name;
# MySQL下的设置还原点
SAVEPOINT p1;
该事务只能在事务语句之间创建保存点(SAVE)。ROLLBACK命令可以用于撤销一些列的事务。
回滚至某一保存点的语法如下:
ROLLBACK TRANSACTION save_name;
示例:
BEGIN TRANSCATION
SAVE TRANSCATION SP1
DELETE FROM Customers WHERE 姓名='张三'
SAVE TRANSCATION SP2
DELETE FROM Customers WHERE 姓名='李四'
SAVE TRANSCATION SP3
DELETE FROM Customers WHERE 姓名='王无'
# 以上操作三次删除,均可生效;因有三个保存点,可以ROLLBACK到任何一点
ROLLBACK TRANSCATION SP2
COMMIT;
# 上述代码回滚到SP2处,并COMMIT
(5)SET TRANSCATION命令
SET TRANSCATION命令可以用来初始化数据库事务,指定随后的事务的各种特征。其语法如下:
SET TRANSCATION [READ WRITE | READ ONLY]
% 上述代码可以将某事物指定为只写或只读
PS:事务在SQL应用中很广泛,特别时针对一些读写问题时,使用事务可以很好的规避意外(脏读、幻读、不可重复读)。而且事务在出来误操作时也有很好的预防作用,特别是像SQL Server这种自动提交的数据库平台,使用事务能防止误删和误更新。
4、常用数学函数
常用的数学函数:MAX(),MIN(),COUNT(),AVG(),SUM()等。
(1)MAX()函数
MAX()返回所选列的最大值。其语法如下:
SELECT MAX(column_name)
FROM table_name
WHERE condition;
示例:查找最昂贵的产品价格
SELECT MAX(价格) AS 最高价格
FROM Products;
(2)MIN()函数
SELECT MIN(column_name)
FROM table_name
WHERE condition;
示例:查找最便宜的产品价格
SELECT MIN(价格) AS 最低价格
FROM Products;
(3) COUNT()
返回指定条件的函数。其中,NULL不计算在内。
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
COUNT(DISTINCT column_name)返回指定列的不同值的数目:
SELECT COUNT(DISTICT column_name)
FROM table_name;
(5) AVG()函数
返回数字列的平均值。其语法如下:
SELECT AVG(column_name)
FROM table_name
WHERE condition
示例:
SELECT 名称,价格 FROM Products
WHERE 价格>
(SELECT AVG(价格) FROM Products)
# 显示出价格大于平均价格的产品名称及价格
(6)SUM()函数
返回数字列的综合。其语法如下:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
(7)STD()函数
MySQL下的标准差,分为总体标准差和样本标准差。
总体标准差函数:
STD(expression)
- 返回表达式的总体标准偏差。如果没有匹配的行,则STD
函数返回NULL
。STDDEV(expression)
– 相当于STD
函数,仅提供与Oracle数据库兼容。STDEV_POP(expression)
- 相当于STD
函数。
样本标准差函数:
VAR_POP(expression)
– 计算表达式的总体标准差。VARIANCE(expression)
– 相当于VAR_POP
函数。VAR_SAMP(expression)
– 计算表达式的样本标准差。
对应的简单案例:请戳这里~
稍加工的案例:(此案例界面为phpstudy的可视化界面)
希望对表中的数据,相同time和station对应的color_r,color_g,color_b进行求标准差计算。首先使用GROUP BY对time和station进行分组,然后调用STD()函数,来解决。代码如下:
SELECT time,station,STDDEV(color_r) FROM train_pic
GROUP BY train_pic.time,station;
结果如图:
5、常用日期函数
GETDATE()函数:用于返回当前数据库系统的日期和实践,返回值的类型为datetime。返回值舍入到最近的秒小数部分,精度为.333数据库十七偏移量不包含在内。已语法为:
SELECT GETDATE()
GETUTCDATE()函数返回当前UTC(世界标准时间)日期值,即格林尼治实践(GMT)。
YEAR()函数:以Int数据类型的格式返回特定日期的年度信息。其语法格式为YEAR(data数据)。
SELECT YEAR(GETDATE())
MONTH()函数:以int数据类型的格式返回特定日期的月份信息。其语法格式如下:
SELECT MONTH(GETDATE())
DAY()函数:以Int数据类型的格式返回特定日期的天数信息。其语法格式如下:
SELECT DAY(GETDATE())
DATEPART(dp,d)函数:返回指定日期中相应的部分的整数值。
DATEPART(datepart,date数据)
示例:
SELECT DATEPART(YEAR,GETDATE())
# 获取当前时间的年份整数
SELECT DATEPART(MONTH,GETDATE())
# 获取当前时间的月份整数
SELECT DATEPART(DAY,GETDATE())
# 获取当前时间的日整数
SELECT DATEPART(WEEK,GETDATE())
# 获取当前周数据
SELECT DATEPART(WEEKDAY,GETDATE())
# 每周星期几
SELECT DATEPART(QUARTER,GETDATE())
# 季度QUARTER
DATENAME(dp,d)函数:返回指定日期中相应的部分字符串,用发和DATEPART类似。
DATEADD(dp,num,d)函数:用于执行日期的加运算,返回指定日期值加上一个时间段的新日期。dp指定日期中进行加法运算的部分值。例如:YEARMONTH,DAY,MINUTE,SECOND,MILLISECOND等,num指定与dp相加的值,如果该值为非整数值,将舍弃该值的小数部分,d为执行加法运算的日期。
DATEADD语法格式:
dateadd(datepart,number,date数据)
number:用于与datepart相加的值。如果指定了非整数值,则将舍弃该值的小部分。
date数据:一个用于解析为time、date、smalldatetime、datetime、datetime2、或datetimeoffset值得表达式、列表达式、用户定义得变量或字符串文字。
DATEDIFF(dp,startdate,enddate)函数:将两个特定得日期数据相减,得到得结果可以按照datepart指定得格式返回特定得数据。
语法格式:
执行中用enddate-startdate,如果startdate晚于enddate将返回负值。执行结果如果超出整数范围将提示错误。
6、常用字符处理函数
(1)ASCII函数
返回字符串表达式中最左侧的ASCII代码值,示例如下:
SELECT ASCII('S'),ASCII('SQL')
# 输出为83
(2)CHAR(字符)
将整数类型的ASCII值转换为对应的字符。示例如下:
SELECT CHAR(83)
# 输出为S
(3)LEN(需要获取长度的字符串)
返回:字符串的长度。示例如下:
SELECT LEN('SQL学习之路');
# 输出7
(4)LEFT(需要被从左边截取的字符串,截取的长度)
返回:左边的字符串。示例如图:
SELECT LEFT('SQL学习之路',2)
# 输出 SQ
(5)RIGHT(需要被从右边截取的字符串,截取的长度)
返回:右边的字符串。示例如图:
SELECT RIGHT('SQL学习之路',2)
# 输出 '之路'
(6)CHARINDEX(需要查找位置的子字符串,父字符串,从哪个位置开始查找)
返回:被查找的子字符串在父字符串的位置
示例:从第一个位置查找'SQL学习之路'中的'路'字在哪个位置
SELECT CHARINDEX('路','SQL学习之路',1)
# 从第一个位置查找路,在后买你字符串的位置为7
(7)PATINDEX(%需要查找的子字符串%,父字符串)
返回指定表达式中模式第一次出现的开始位置,子字符串你可以使用通配符%,而CHARINDEX不可以。示例如下:
SELECT PATINDEX('%数据%','SQL数据库开发')
# 输出为4
SELECT PATINDEX('%_数据%','SQL数据库开发')
# 输出为3,通配符_表示任意字符
(8)LTRIM(需要被清空的字符串)
返回:被清空左边空格的字符串。示例如下:
SELECT LTRIM(' SQL学习之路')
# 输出'SQL学习之路'
(9)RTRIM(需要被清空右边的字符串)
返回:被清空右边空格的字符串。示例如下:
SELECT RTRIM('SQL学习之路 ')
# 输出SQL学习之路
(10)STUFF(字符串,开始下标,截取长度,插入的字符串)
在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串。示例如下:
SELECT STUFF('ABCDEFG',2,3,'SQL学习之路')
# 输出ASQL学习之路EFG
(11)REPLACE(指定字符串,需要被替换的字符串,替换为的字符串)
返回新字符串,示例如下:
SELECT REPLACE('SQL学习之路','学习','成长')
# 输出 ‘SQL成长之路’
(12)UPPER(需要转换为大写的字符串)
返回:字符串的大写形式。示例如下:
SELECT UPPER('abcdef')
# 输出'ABCDEF'
(13)LOWER(需要转换为小写的字符串)
返回:字符串的小写形式。示例如下:
SELECT LOWER('SQL学习之路')
# 输出'sql学习之路'
(14)SUBSTRING(字符串,起始下标,长度)
返回:提取字符串。示例如下:
SELECT SUBSTRING('SQL学习之路',4,4)
# 输出 ‘学习之路’
(15)REVERSE(需要你想取值的字符串)
返回字符串值的逆向值。示例如下:
SELECT REVERSE('SQL学习之路')
# 输出 '路之习学LQS'
(16)REPLICATE(需要重复字符串值),示例如下:
SELECT REPLICATE('SQL',3)
# 输出 'SQLSQLSQL'
7、常用数据类型
SQL常用数据类型定义:数据类型定义了存储在列中的值的类型。
SQL常用数据类型作用:数据库表中的每一列都需要有一个名称和数据类型。SQL开发人员必须在创建SQL表时决定表中的每个列将要存储的数据类型。数据类型是一个标签,是方便SQL了解每个列期望存储什么类型的数据指南,它也标识了SQL如何与存储的数据进行交互。
常用数据类型:
数据类型 | 描述 |
CHARACTER(n) | 字符/字符串,固定长度n |
VARCHAR(N)或CHARACTER VARING(n) | 字符/字符串,可变长度,最大长度 |
BINARY(n) | 二进制串,固定长度n |
BOOLEAN | 存储TRUE或FALSE值 |
VARBINARY(n)或BINARY VARYING(n) | 二进制串,可变长度。最大长度n |
INTEGER(p) | 整数值(没有小数点),精度p |
SMALLINT | 整数值(没有小数点),精度5 |
INTEGER | 整数值(没有小数点),精度10 |
BIFINT | 整数值(没有小数点),精度19 |
DECIMAL(p,s) | 精确数值,精度p,小数点后位数s。 |
NUMERIC(p,s) | 精确数值,精度p,小数点后位数s。(与DECIMAL相同) |
FLOAT(p) | 近似数值,尾数精度p。一个采用以10为基数的指数计数法的浮点数。该类型的size参数由一个指定最小精度的单一数字组成 |
REAL | 近似数值,尾数精度7 |
FLOAT | 近似数值,尾数精度16 |
DOUBLE PRECISION | 近似数值,尾数精度16 |
DATE | 存储年、月、日的值 |
TIME | 存储小时、分、秒的值 |
TIMESTAMP | 存储年、月、日、小时、分、秒的值 |
INTERVAL | 由一些整数字段组成,代表一段时间,取决于区间的类型 |
ARPAY | 元素的固定长度的有序集合 |
MULTISET | 元素的可变长度的无序集合 |
XML | 存储XML数据 |
这些数据类型通常在定义表结构,定义变量时使用,具有固定的写法。具体案例见:创建表
8、SQL语句快速参考
具体见链接。
AND/OR 并且/或
SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
ALTER TABLE修改表
ALTER TABLE table_name
ADD column_name datatype
或
ALTER TABLE table_name
DROP COLUMN column_name
AS (alias) 重命名
# 对列名进行重命名
SELECT column_name AS column_alias
FROM table_name
或
SELECT column_name
FROM table_name AS table_alias
BETWEEN... AND...在某个区间
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
CREATE DATABASE 创建数据库
CREATE DATABASE database_name
CREATE TABLE 创建表
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
...
)
CREATE INDEX 创建索引
CREATE INDEX index_name
ON table_name (column_name)
% 创建普通索引
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE VIEW 创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DELETE 删除
DELETE FROM table_name
WHERE some_column=some_vale
# 带有条件的删除
DELETE FROM table_name
# 不带条件的删除
DROP DATABASE删除数据库
DROP DATABASE database_name
DROP INDEX 删除索引
#SQL Server的写法
DROP INDEX table_name.index_name
# DB2/Orcale的写法
DROP INDEX index_name
# Mysql的写法
ALTER TABLE table_name
DROP INDEX indx_name
DROP TABLE 删除表
DROP TABLE table_name
GROUP BY分组
SELECT column_name,aggregate_function(column_name)
FROM table_name
WHERE column_name OPERATOR VALUE
GROUP BY column_name
INSERT INTO 插入表
INSERT INTO table_name
VALUES (value1,value2,...)
或
INSERT INTO table_name
(column1,column2,column3,...)
VALUES (value1,value2,value3,...)
INNER JOIN 内连接
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LEFT JOIN左连接
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN左连接
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOIN左连接
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LIKE模糊匹配
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BY排序
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
SELECT DISTINCT 查找去重字段
SELECT DISTINCT column_name(s)
FROM table_name
SELECT INTO 查询结果插入表
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name
或
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name
SELECT TOP 查询前N条记录
SELECT TOP number|percent column_name(s)
FROM table_name
UNION 去重并集
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL 不去重并集
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
UPDATE更新
UPDATE table_name
SET column1=value,column2=value,...
WHERE some_column=some_value
WHERE 条件过滤
SELECT column_name(s)
FROM table_name
WHERE column_name OPERATOR VALUE