SQL基础：
SQL基础：
1、SQL语句是用命令来管理数据的语句，类似EXCEL命令化。
2、SQL是学习所有编程语言的基础,编写大型程序必须使用到数据库。
3、SQL不同于编程语言，无法使用变量、循环结构、条件逻辑等方式对过程进行定义，SQL以获得结果为目的，如获取某一行数据或者某一行至另一行中间的数据。
4、SQL大致分为三类语句：方案语句、数据语句、事务语句。
5、SQL基础语法SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT等。
6、SQL数据库类型：HIVESQL、MYSQL、SQLSERVER、ORACLE等
SQL常用函数：
平均值：AVG
计数（不含Null）:COUNT
第一个记录的值：FIRST
最大值：MAX
最小值：MIN
样本标准差：STDEV
总体标准差：STDEVP
求和：SUM
样本方差：VAR
总体方差：VARP
转化为全大写字母：UCASE
转化为全小写字母：LCASE
取中值：MID
计算字符串长度：LEN
获得子字符串在母字符串的起始位置：INSTR
取字符串左边子串：LEFT
取字符串右边子串：RIGHT
数值四舍五入取整：ROUND
取余：MOD
获得当前时间的值：NOW
字符串格式化：FORMAT
获得两个时间的差值：DATEDIFF
注释：--，有些sql为#
块注释：/* test */ 中间为注释部分
SQL重要命令：
SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
SQL学习网站：
SQL课程及课题：http://xuesql.cn/
知乎自学SQL导航：https://www.zhihu.com/question/30357711
SQL学习正文：
查询命令：SELECT
1、SELECT * FROM ID
注：查询表名为ID中所有资料，*为通配符，表示全部列，如要查询指定列可用列名表示逗号间隔，如id1,id2。
2、SELECT * FROM ID WHERE ID1>10 and ID1<30，亦可用BETWEEN。
注：查询表名为ID中ID1列大于10且小于30的数据，此处数字代表数值非代表行号。
WHERE筛选
|
Operator（关键字）
|
Condition（意思）
|
SQL Example(例子）
|
|
=, !=, < <=, >, >=
|
Standard numerical operators 基础的 大于，等于等比较
|
col_name != 4
|
|
BETWEEN … AND …
|
Number is within range of two values (inclusive) 在两个数之间
|
col_name BETWEEN 1.5 AND 10.5
|
|
NOT BETWEEN … AND …
|
Number is not within range of two values (inclusive) 不在两个数之间
|
col_name NOT BETWEEN 1 AND 10
|
|
IN (…)
|
Number exists in a list 在一个列表
|
col_name IN (2, 4, 6)
|
|
NOT IN (…)
|
Number does not exist in a list 不在一个列表
|
col_name NOT IN (1, 3, 5)
|
条件查询
|
Operator（操作符）
|
Condition（解释）
|
Example（例子）
|
|
=
|
Case sensitive exact string comparison (notice the single equals)完全等于
|
col_name = "abc"
|
|
!= or <>
|
Case sensitive exact string inequality comparison 不等于
|
col_name != "abcd"
|
|
LIKE
|
Case insensitive exact string comparison 没有用通配符等价于 =
|
col_name LIKE "ABC"
|
|
NOT LIKE
|
Case insensitive exact string inequality comparison 没有用通配符等价于 !=
|
col_name NOT LIKE "ABCD"
|
|
%
|
Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符
|
col_name LIKE "%AT%"
(matches "AT", "ATTIC", "CAT" or even "BATS") "%AT%" 代表AT 前后可以有任意字符
|
|
_
|
Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似，代表1个字符
|
col_name LIKE "AN_"
(matches "AND", but not "AN")
|
|
IN (…)
|
String exists in a list 在列表
|
col_name IN ("A", "B", "C")
|
|
NOT IN (…)
|
String does not exist in a list 不在列表
|
col_name NOT IN ("D", "E", "F")
|
在字符串表达式中的字符串需要用单引号 ' 包含，如果不用单引号，SQL会认为是一个属性列的名字，如：col_name = color 表示 col_name和color两个属性一样的行 col_name = 'color' 表示 col_name 属性为字符串 'color'的行.
查找包含：
1、SELECT * FROM movies WHERE title like '%Toy%'
注：查找表movies中title列中包含toy的数据，%为通配符代表前后任意字符，通配查找必须用'，NOT LIKE为不包含。
2、SELECT * FROM movies where capacity is not null
注：代表capacity字段值为null（空），加not代表不为空，like 'null' 代表数据为null。
2、SELECT DISTINCT Director FROM movies
注：DISTINCT去重movies表中Director列。
3、SELECT Top 4 * FROM movies order by year DESC;
注：查找movies中year列从大到小排列，然后取前四行，order by year DESC 按照降序排列，DESC为降序，ASC为升序，TOP 4取4行。
5、SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER(order by makeTime ASC) AS rno
FROM DL_Annexs
) a WHERE rno BETWEEN 100 AND 400
注：取100-400区间，rno为别名，rno不能和列名相同。
SELECT TOP 40 * FROM
(SELECT *,ROW_NUMBER() OVER(PARTITION BY maker ORDER BY maker DESC) AS DD
FROM DL_Annexs
)d where DD=1
注：取DL_Annexs表中maker列，对maker列进行排序去重。 PARTITION BY maker进行分组， ORDER BY maker DESC对maker进行排序，TOP 40取40行，DD=1取分组值的第一个值达到去重目的。
ROW_NUMBER()的用法
--语法形式： ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
--解释： 根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
--常用的使用场景： 取每个学科的前3名
select * from
(
select subject,name,score,ROW_NUMBER() over(PARTITION by subject order by score desc) as num from #score
) T where T.num <= 3 order by subject
多表联合查询：
在表A 连接 B, LEFT JOIN保留A的所有行，不管有没有能匹配上B 反过来 RIGHT JOIN则保留所有B里的行。最后FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行
INNER JOIN原理图
用INNER JOIN 连接表的语法
SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
ON mytable.id = another_table.id (想象一下刚才讲的主键连接，两个相同的连成1条)
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;
1、SELECT * FROM Movies INNER JOIN Boxoffice ON Movies.id=Boxoffice.Movie_id where Domestic_sales<International_sales;
注：联合查询找出International_sales大于Domestic_sales的项。Movies INNER JOIN Boxoffice联合两个表查询，ON Movies.id=Boxoffice.Movie_id匹配两个主键中对应的外键，往后继续使用基础查询命令。
INNER JOIN 语法：
INNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
INNER JOIN 连接四个数据表的用法：
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号
INNER JOIN 连接五个数据表的用法：
SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号
LEFT JOIN原理图
用LEFT/RIGHT/FULL JOINs 做多表查询
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table ON
mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;
1、SELECT * FROM Movies LEFT JOIN Boxoffice ON Movies.id=Boxoffice.Movie_id where Domestic_sales<International_sales;
注：联合查询找出International_sales大于Domestic_sales的项。Movies INNER JOIN Boxoffice联合两个表查询，ON Movies.id=Boxoffice.Movie_id匹配两个主键中对应的外键，往后继续使用基础查询命令。
DELETE用法
示例：
delete from dl_SO_TransVouchre where cTVCode_C='tb2203020001'
UPDATE用法
示例:
update RdRecord09 set cBusCode='T2204190045' where cBusCode='T2204190046'
字符串连接
示例：
select 'ID' + '(' + LTRIM(execSql) + ')' from DL_temp_print
SELECT *,'hl'+cast(ROW_NUMBER() OVER(order by cola ASC) as varchar) as num from myTable
TRIM函数
RTRIM()去掉字符串右边的字符
LTRIM()去掉字符串左边的字符
TRIM()去掉字符串左右两边的字符
多字符串计算
计算：加+,减-，乘*，除/
示例：
select iquantity*iUnitPrice,iquantity,iUnitPrice,* from SO_SODetails where AutoID='2246531'
查询所有表名及行数
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC
多select嵌套查询
select cBusCode,* from rdrecord08 inner join dbd on rdrecord08.cBusCode=dbd.type
where rdrecord08.id in(select max(rdrecord08.id) from rdrecord08 group by rdrecord08.cBusCode)
查询列并判断并计数包含数量
select cBusCode,COUNT (cBusCode) from rdrecord09 where cBusCode in(select type from dbd) group by cBusCode
判断重复项数量
select cBusCode,COUNT (cBusCode) from rdrecord09 where cBusCode in(select type from dbd) group by cBusCode having COUNT (cBusCode)>2
文本处理函数
Left（）：返回串左边的值
Right（）：返回串右边的值
Length（）：返回串的长度
Locate（）：找出串的一个子串
Upper（）：将串转换为大写
Lower（）：将串转换为小写
Trim（）：去除串两边的空格
LTrim（）：去除串左边的空格
RTrim（）：去除串右边的空格
SubString（）：返回子串的字符
Soundex（）：返回串的SOUNDEX值 （注意：SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法）
Soundex（）函数举例：
city表 的 Name列 有一个名字为 ' Sang ' ，但是如果这是输入错误的值，此名字的正确值实际上是 ' Song ' ，使用Soundex（）函数进行搜索，匹配所有发音类似 ' Song ' 的名字：
select id,'name' from city where Soundex('name') = Soundex('song')
Length（）函数举例：
检索 city表 中的所有 ID序号，Name值 以及 Name值的长度，检索的所有信息全部输出。
select id,'name',Length('name') from city
查找指定日期
查找RdRecords11中年份为2020，月份为12的值
SELECT dbkeepdate,* FROM RdRecords11 WHERE YEAR(dbkeepdate)='2020' AND MONTH(dbkeepdate)='12'
SELECT dbkeepdate,* FROM RdRecords11 where DATEPART(YY,dbkeepdate) = 2020 AND MONTH(dbkeepdate)='12'
常用数值处理函数
abs返回一个数的绝对值
exp返回一个数的指数值
sqrt返回一个数的平方根
mod返回除操作的余数
pi返回圆周率π的值
rand返回一个随机数
sin返回一个角度的正弦
cos返回一个角度的余弦
tan返回一个角度的正切
HAVING的用法
HAVING的作用是给分组设置条件
需求：查询平均成绩大于60分的同学的学号和平均成绩;
错误示范：
select sid from sc where avg(score) > '60'
正确示范:
select sid,AVG(score) FROM sc GROUP BY sid HAVING AVG(score) >60
此处对平均成绩设置分组，设置平均条件为大于60。
where 和having之后都是筛选条件，但是有区别的：
1.where在group by前， having在group by 之后
2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后
--存储过程查询被调用（不通用）
SELECT NAME FROM sysobjects o, syscomments s
WHERE o.id = s.id
AND TEXT LIKE '%DLSO_SOMainTrack_DLSO_JTPJ%'
AND o.xtype = 'P'
事务返回：
begin tran w
语句
rollback tran w
检查数据库死锁（不通用）：
exec master.dbo.p_lockinfo 0,0
U8删单记录表
OperationReasonLogTemporary
自增ID
ROW_NUMBER() over(order by (select 1)
ROW_NUMBER() over(order by ID)
把多个结果显示到一个表
select * from name
union
select * from movies
SQL视图
1.什么是视图？
视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。
2.视图的特点。
视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作。
视图不能被修改，表修改或者删除后应该删除视图再重建。
视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。
视图可以被嵌套，一个视图中可以嵌套另一个视图。
视图不能索引，不能有相关联的触发器和默认值，sql server不能在视图后使用order by排序。
判断视图是否存在
if exists(select * from sysobjects where id=OBJECT_ID(N'v_Stu_Cou') and objectproperty(id,N'IsView')=1)
drop view v_Stu_Cou --删除视图
建立视图的语法：
Create view 视图名称[(字段1) (字段2) (字段3)…]
AS
Select 查询语句
[with check option]
参数：[with check option]可选项，防止用户对数据插入、删除、更新是操作了视图范围外的基本表的数据。
create view dbo.v_Stu_Cou as
Select st.学号,st.姓名,st.所属院系 from student as st,course as co,score as sc
Where co.课名=’心理学’ and sc.考试成绩>80 and st.学号=sc.学号 and co.课号=sc.课号 --创建视图
3.视图的功能
1.简化用户操作
2.能以不同的角度观察同一个数据库
3.对重构数据库提供了逻辑独立性：
利用视图将需要的数据合并或者筛选，但是不影响原表的数据和结构
3.对机密数据提供安全保护：
可以建立不同的视图对用不同的用户，以达到安全的目的。
创建索引
CREATE INDEX index_name
ON table_name ( column1, column2.....);
index_name 是索引的名字。
通配符转义 escape
escape指定的字符后面紧挨着的第一个字符被看作是普通字符而非通配符，如果转义符后面的字符不是通配符，则将放弃转义符并将该转义符后面的字符作为该模式中的常规字符处理。
SELECT * FROM user WHERE username LIKE '%1%%' ESCAPE '1';
开窗函数
select * from(
select CINSPECTCODE,DARRIVALDATE,ROW_NUMBER()over(PARTITION BY CINSPECTCODE order by DARRIVALDATE) as a
from
QMCHECKVOUCHER
) b
where a='1'
判断字段为空值
IsNULL(cCloser,N'')=N'' 同where cCloser is null
判断数字为某个值则替换
Num为字段或者值，0为判断字符是否为0，1为替换字符
SELECT ISNULL(NULLIF(Num, 0), 1)
多表联合更新
update Ele_Customer set a.sReserve1=b.t_name
from Ele_Customer as a
inner join kut$ as b
on a.scode=b.code
表添加字段
alter table 表名 add 字段名 varchar(100) null
查询视图源码：
sp_helptext '视图名'
修改表字段可为空
alter table 表名 alter column 列名 varchar(20) null
时间函数：
指定当月日期：convert(varchar(10),convert(varchar(8),dateadd(month,0,getdate()),23))+'09'
上月第一天 CONVERT(varchar(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),23)
上月最后一天 CONVERT(varchar(10),dateadd(mm,datediff(mm,0,getdate()),-1),23)
当月第一天 CONVERT(varchar(10),DATEADD(month,0,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),23)
当月最后一天 EOMONTH(GETDATE())
字段当月或上月 DATEDIFF(month,字段名,(GETDATE()))=0
0=当月，1=上月，2=上上月，-1下月，以此类推
取第一个括号（后最后一个括号)前的数据
select SUBSTRING('1(456(123))',PATINDEX('%（%','1(456(123))')+1,PATINDEX('%）%','1(456(123))')-PATINDEX('%（%','1(456(123))'))
取历史之和函数
SUM(T1) OVER(ORDER BY num)
例：
SELECT NUM,SN,T1,T2-SUM(T1) OVER(ORDER BY num) AS T3 FROM #tm1
删除一个表中的一列
ALTER TABLE kc$ DROP COLUMN f5
替换指定字符
from_str：需要替换的字符
to_str：替换字符
UPDATE Sis_MaterialsClass SET SCODE= REPLACE(SCODE, from_str, to_str)
去掉小数后面多余的0
CAST --FLOAT
CAST(ROUND([A].[dMQty] / [C].[dMaterialLength],0,1) AS FLOAT)
查找表的时间戳列
SELECT name FROM sys.columns WHERE object_id=OBJECT_ID('Ele_FlexProduct') AND system_type_id=189
查找表的自增列
SELECT name FROM sys.columns WHERE object_id=OBJECT_ID('Ele_FlexProduct') AND is_identity=1
查找第一个括号前的字符
SELECT substring(sname, 1, charindex('(', sname)-1),* FROM Ele_Materials where sMaterialStyle='1' and sname like '%(%'
截取括号内内容
SELECT SUBSTRING(column_name, CHARINDEX('(', column_name)+1, CHARINDEX(')', column_name)-CHARINDEX('(', column_name)-1) AS content FROM table_name;
substring(C.SNAME, charindex('(', C.SNAME)+1,charindex(')', C.SNAME)-charindex('(',C.SNAME)-1) AS NSNAME
SUBSTRING(B.SNAME, CHARINDEX('(', B.SNAME), CHARINDEX(')', B.SNAME)) AS NNAME
数据库抛出异常提示
RAISERROR ('主料材料规格不能为空或有误', -- Message text.
16, -- Severity.
1 -- State.
)
END
查找字段类型
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'Ele_FlexProduct'
and column_name='dWidth'
在表中插入值
查找表中所有列插入
1、INSERT INTO #TM3
SELECT *
FROM #TM3
2、INSERT INTO #TM3('','')--指定列名
SELECT '','' FROM #TM3
3、INSERT INTO #TM3('','')
VALUES('ID','NAME')
文章末尾固定信息
我的微信
我的微信
微信扫一扫
评论