查询常用:
---查询20条客户的购买订单记录(带个人信息)
SELECT top 20 --- 取前20条数据
ROW_NUMBER() OVER ( ORDER BY a.id ) AS 序号 , ---查询时,新增序号列
RealName AS 真实姓名 , ---AS 的意思 是将原列名重新命名为‘真实姓名’
* ---查询两个表所有字段
FROM dbo.[Order] AS b ---订单表
LEFT JOIN dbo.Member AS a ON a.Id = b.MemberId ---左关联 个人信息表,关联条件:客户Id (左关联:有个人信息就带出来,没有就放空)
---where :带条件查询
WHERE a.AddTime > '2018-01-01' ---创建时间> '2019-01-01'
AND Nation LIKE '%汉%' ---模糊查询:字段里值带有‘汉’字的
---order by 排序
ORDER BY a.Id ,
NickName DESC ---根据ID,和昵称倒序排序 (正序排序 ASC)
---------------------------------------------------------------------------------
----查询订单表用户购买次数
SELECT ReceiveName AS 姓名 ,
MemberId AS 用户ID ,
COUNT(1) AS 购买次数
FROM dbo.[order]
GROUP BY MemberId , ----分组 把订单表按用户的ID,收件人分组
ReceiveName
-----------------------------------------------------------
--- 查询日期:
select CONVERT(varchar(7), getdate() , 120) + '-1'
---获取当月1号
SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, getdate()) + 1, 0))
----获取当月月底 例如2017-08-31号23:59:59
SELECT left(convert(nvarchar(10),dateadd(m,-1,getdate()),120),8)+'21'
-----获取上月21日
-------------------------------------------------------------------
----创建临时表并插入数据
---方法1:
---创建:
CREATE TABLE #T
(
id INT NOT NULL ,
NAME NVARCHAR(50)
)
---插入:
INSERT INTO #T
( id, NAME )
VALUES ( 0, -- id - int
N'3r23t4te' -- NAME - nvarchar(50)
)
INSERT INTO #T
( id ,
NAME
)
SELECT SortNo ,
CnName
FROM dbo.QuestionReportType
---第二种方法:
---创建并插入数据
SELECT SortNo ,
CnName
INTO #T1
FROM dbo.QuestionReportType
更新或删除操作:
---删除表里字段DiscountStatus
ALTER TABLE fund.BusinessTicketFormItem DROP COLUMN DiscountStatus
---增加表里某个字段
ALTER TABLE ReportDB.dbo.EntryFlowTracking ADD TotalTime DECIMAL(10,2)
---字段改名(DiscountStatus改为DiscountState)
exec sp_rename N'fund.LetterOfCreditFormItem.DiscountStatus','DiscountState','COLUMN'
---修改替换某字段的值
UPDATE fund.BusinessTicketFormItem SET DiscountState = REPLACE(DiscountState,'50','2') WHERE DiscountState LIKE '50'
---删除表里所有的数据
DELETE FROM fund.DiscountReceiptForm where 1=1
---修改字段属性 (score原本是decimal(18,4)类型)
ALTER TABLE qn.QUESTION_ANSWER ALTER COLUMN SCORE DECIMAL(18,2)
---插入数据
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
---修改某字段的值
UPDATE REportDb.qn.OPTION_COPY SET SCORE = 0 WHERE CODE IN('c676fc52-c366-4977-b6a7-91a3061208d0')
---模糊查询
Select * from Test where Cn_Name LIKE '%1%'(只要带字符为1的都能查到)
‘%1’ 以1结尾的数据能查到; ‘1%’ 以1开头的能查到
^ 剔除字符,不包含1的数据 ; ‘1_ ‘ 以1开头并且1后面只带一个字符的数据
--- IN用法
SELECT * FROM SubjectDB.QN.[OPTION]
WHERE TEMPLATE_CODE IN (
'9e7c4a5e-e906-4541-ba03-7017a60e4f6f',
'da65dda1-5607-4e6a-a4f8-8258d854e3fd',
'c59747ac-9077-4300-9976-5844538d1e11',
'8eadc369-5d08-4225-b9f2-d597f50e21d1',
'046e9ab4-bd25-4ff4-a0d1-a727deb8d29a'
)
--- 计算天数
select datediff(dd,'2015-07-01','2016-01-01')
----数据库脚本编规范
BEGIN TRAN
USE ReportDB
Insert into ...
ROLLBack TRAN ---回滚
COMMIT TRAN ---提交
---去重 查询
SELECT DISTINCT
E_Code ,
E_LeaveDate
FROM dbo.DimissionInformation
---去重 创建序号码,获取序号码都为1的数据
---语法:row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
---表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组---内连续的唯一的)
WITH T AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY LOGON_NAME ORDER BY ALL_PATH_NAME ) AS roenumber ,
*
FROM SubjectDB.dbo.User_Syn
WHERE LOGON_NAME IN ( 'liming', 'fanhy' )
)
SELECT *
FROM T
WHERE roenumber = 1
行转列
例子:成绩表有姓名,月份,成绩,要生成左边的效果
CREATE TABLE StudentScore(NAME NVARCHAR(10),months NVARCHAR(10),score INT)
INSERT INTO StudentScore VALUES('张三','一月',85)
INSERT INTO StudentScore VALUES('张三','二月',97)
INSERT INTO StudentScore VALUES('李四','一月',77)
INSERT INTO StudentScore VALUES('李四','二月',86)
INSERT INTO StudentScore VALUES('王五','一月',86)
INSERT INTO StudentScore VALUES('王五','二月',88)
INSERT INTO StudentScore VALUES('李白','二月',90)
-- 静态SQL,指月份只有一月和二月
--方法一:
SELECT NAME AS 姓名,
MAX(CASE months WHEN '一月' THEN score ELSE 0 END) 一月,
MAX(CASE months WHEN '二月' THEN score ELSE 0 END) 二月
FROM StudentScore
GROUP BY name
--方法二(SQL Server2005 以上版本支持):
select * from StudentScore
pivot (
max(score) /*行转列后 列的值*/
for months /*需要行转列的列*/ in (一月,二月) ) /*列的值*/
b
--动态SQL,指月份不止一月和二月
--方法一:
declare @sql varchar(8000)
set @sql = 'select name '
select @sql = @sql + ' , max(case months when ''' + months + ''' then score else 0 end) [' + months + ']'
from (select distinct months from StudentScore) as a
set @sql = @sql + ' from StudentScore group by name'
exec(@sql)
--方法二(SQL Server2005 以上版本支持):
declare @sqla varchar(8000)
select @sqla = isnull(@sqla + ',' , '') + months from StudentScore group by months
exec ('select * from StudentScore pivot (max(score) for months in (' + @sqla + ')) b')