SQL Server数据库语句

获取当前时间用getdate()：

select getdate()

获取上个月第一天和最后一天的日期：

-- 获取上个月第一天的日期
select convert(date, dateadd(month, datediff(month, '2000-01-01', getdate()) - 1, '2000-01-01'))

-- 获取上个月最后一天的日期
select CONVERT(date, DATEADD(day, -1, dateadd(month, datediff(month, '2000-01-01', getdate()), '2000-01-01')))

-- 获取上个月第一天的日期 + 时分秒
select DATEADD(MONTH, DATEDIFF(month, '2000-01-01', getdate()) - 1, '2000-01-01')

-- 获取上个月最后一天的日期 + 时分秒
select DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, '2000-01-01', GETDATE()), '2000-01-01'))

获取一个日期的年，月，日

select year('2009-11-02')    // 返回2009

select month('2009-11-02')   // 返回11

select day(getdate())        // 返回当前时间的日期

查询某个时间段的数据

--create_time 是表 MyTable里的一个字段，类型为 datetime
SELECT * FROM MyTable
where create_time > '2022-03-02 00:00:00.000' and create_time < '2022-03-06 00:00:00.000'

--------------------------------------------------------------------------------------

语法：datediff(datepart,startdate,enddate)

后面日期减去前面的日期(enddate-startdate)

datepart，规定应在日期的哪一部分计算差额

日期           缩写

year           yy/yyyy

quarter      qq/q

Month        mm/m

day            dd/d

Hour          hh/h

minute       mi,n

second      ss,s

按create_time字段的时间距离当前时间的长短来排序：

select * from table_name order by DATEDIFF(HOUR, GETDATE(), create_time)

--------------------------------------------------------------------------------------

以天为单位，统计表中每一天总共有几条数据：

select convert(varchar(100), t.start_time, 23)[date], COUNT(t.id) cnt
FROM table_name as t
group by convert(varchar(100), t.start_time, 23) order by date

-- 查询结果：
--     date          cnt
--1    2020-03-25	 14
--2    2020-03-26	 41
--3    2020-04-23	 14

参考链接1   参考链接2

此时还有一条SQL语句，和上面这条差不多，多了个where条件判断

select convert(varchar(100), t.start_time, 23)[date], COUNT(t.id) cnt
FROM table_name as t
where (cmd_type = 1 or cmd_type = 2) and to_pos <= 60
group by convert(varchar(100), t.start_time, 23) order by date

如果此时要将上述两个SQL语句的执行结果一次性搜索出来，则要将上述两条SQL语句合并为一

select convert(varchar(100), t.start_time, 23)[date], 
COUNT(t.id) as total_cnt,
COUNT(case when (cmd_type = 1 or cmd_type = 2) and to_pos <= 60 then 1 else null end) as pos_cnt
FROM table_name as t with (nolock)
group by convert(varchar(100), t.start_time, 23) order by date

-------------------------------------------------------------------------------------

start_time为“yyyy-MM-dd HH:mm:ss.000”格式的datetime字段

select CONVERT(varchar(50), start_time, 23), CONVERT(varchar(50), start_time, 24) from my_table

以上语句能将datetime字段转为“yyyy-MM-dd”格式和“HH:mm:ss”格式。

-------------------------------------------------------------------------------------

查询name字段各个值的出现次数

select name, COUNT(name) as c from MyTable group by name order by c desc

参考链接：链接

-------------------------------------------------------------------------------------

创建触发器：

use MyTestDB 
go

IF (object_id('tr_insert','tr') is not null)
    drop trigger tr_insert
GO
CREATE trigger tr_insert
on [mytable]
after insert --插入触发
as
 begin
   --定义变量
   declare @name varchar(50)
   declare @age int
   --在inserted表中查询已经插入记录信息
   select @name=name, @age=age from inserted
   --[mytable2]表中插入数据
   insert INTO [mytable2] (name, score, remark) VALUES (@name, @age, @name)
   print '插入成功!'
 end

执行上述代码后将在表[mytable]上创建一个插入触发器tr_insert，在表[mytable]里插入数据时会触发tr_insert触发器，往表[mytable2]里插入相应的数据

参考链接：链接

 -------------------------------------------------------------------------------------

查看所有触发器：

use MyDB 
go

select * from sys.triggers

也可以这样：

select * from sysobjects where XTYPE = 'TR'

 查看所有存储过程，视图，表

--查看所有存储过程
select name from sysobjects where XTYPE = 'P' ORDER BY name 
--查看所有视图
select name from sysobjects where XTYPE = 'V' ORDER BY name 
--查看所有表
select name from sysobjects where XTYPE = 'U' ORDER BY name 

-------------------------------------------------------------------------------------- 

创建存储过程：

USE [DataBaseName]
go
create procedure [PROC_TEST_MYPROCEDURE]
@NAME VARCHAR(50)
AS 
BEGIN 
delete from [test_mytable] where name = @NAME
delete from [test_mytable2] where name = @NAME
END

 执行存储过程：

exec [DataBaseName].[PROC_TEST_MYPROCEDURE] 'hello'

--------------------------------------------------------------------------------------

执行存储过程并获取其返回值，在exec和存储过程名之间加上变量名和等号：

declare @a int
declare @b int
exec @a = sp_addlinkedserver 'Cake', '', 'SQLOLEDB', '127.0.0.1\Cake,1450' 
exec @b = sp_addlinkedsrvlogin 'Cake', 'false', null, 'sa', 'MyPassword'
print @a
print @b

-- 连接数据库实例成功并登录成功后，输出如下：
-- 0
-- 0

--------------------------------------------------------------------------------------

SQL定义变量和输出变量：

declare @num varchar(50)
set @num = '123456'
print @num

或者声明变量时直接赋值：

DECLARE @name NVARCHAR(128) = 'Lisa'
print @name 

-------------------------------------------------------------------------------------

将查询到的值赋值给变量，while循环：

declare @flag int
print @flag    -- 未初始化的变量输出为空
select @flag = COUNT(name) FROM [DataBaseName].[test_mytable]
while @flag > 0
	begin
	print @flag
	set @flag = @flag - 1
	end
-- 输出如下：
--  
-- 6
-- 5
-- 4
-- 3
-- 2
-- 1

 -------------------------------------------------------------------------------------

if else 逻辑控制语句：

declare @a int
declare @b int
set @a = 10
set @b = 20
-- 如果if后面只有一条语句，则if后面的begin和end可以去掉，begin和end相当于C++里if后面的{和}
if @a>1 and @b=20
begin
	print 'if'
	print 'if2'
end
else
begin
	print 'else'
	print 'else2'
end

-- 输出结果为：
-- if
-- if2

declare @a int
set @a = 4
-- 判断是否非0
if @a<>0
	print '111'
else
	print '000'

-- 输出结果为：
-- 111

-------------------------------------------------------------------------------------

insert into里嵌入select语句，将select得到的数据插入到表中：

insert into table1 select name, age, class from table2

如果插入的数据包括自增的字段，则在insert into的表名后面要把自增的字段名写上，并在插入数据前set IDENTITY_INSERT mytable1 ON，插入数据后set IDENTITY_INSERT mytable1 OFF：

set IDENTITY_INSERT mytable1 ON
insert into mytable1 (testid, name, age, class)
	select testid, name, age, class from mytable2
set IDENTITY_INSERT mytable1 OFF

insert和select的单表嵌套和多表嵌套的参考链接：链接 

-------------------------------------------------------------------------------------

把一个表里的数据加入到另一个表里，如果这两个表的列顺序和数据类型相同

INSERT INTO TableB SELECT * FROM TableA

这两个表的列顺序和数据类型不相同的话，则需要逐列指定插入

INSERT INTO TableB 
(		
	   [name]
      ,[score]
)
SELECT * FROM TableA

如果上面的insert语句执行时报错，提示INSERT 语句与 FOREIGN KEY 约束"******"冲突，通常是因为你正在尝试插入的数据在关联的外键列上找不到匹配的值，从而违反了外键约束。这可能是由于插入的数据与关联表中的数据不匹配，或者外键列上的值不存在于关联表中。

有一种解决方法是暂时禁用约束，插入数据，然后再启用约束。这种方法需要谨慎使用，因为禁用约束可能会导致数据完整性问题。

-- 禁用外键约束
ALTER TABLE TableB NOCHECK CONSTRAINT 约束名;

INSERT INTO TableB 
(		
	   [name]
      ,[score]
)
SELECT * FROM TableA

-- 启用外键约束
ALTER TABLE TableB CHECK CONSTRAINT 约束名;

-------------------------------------------------------------------------------------

表table_student：
A_id, name, class, score
表table_msg：
B_id, name, age, adress

select A.score, B.name, B.age
from table_student A 
inner join 
(select name, age from table_msg
where B_id = 1) B 
on A.name = B.name
where A.class = '3-1'

select A.score, B.name, B.age 
from table_student A, 
(select name, age from table_msg 
where B_id = 1) B 
where A.class = '3-1'
AND A.name = B.name

以上两个SQL语句执行后结果一致，一个是用了inner join，一个是用了逗号（from两个表，我的理解是这样）

-------------------------------------------------------------------------------------

给表MyTable添加一个字段isUpload，类型是bit，不允许Null值，默认值为0

alter table MyTable add isUpload bit not null default((0))

或者多加一个约束的名字：

ALTER TABLE MyTable ADD isUpload bit NOT NULL CONSTRAINT DF_MyTable_isUpload DEFAULT 0

-------------------------------------------------------------------------------------

删除表

drop table MySchema.MyTable

删除视图

DROP VIEW MySchema.MyView

删除存储过程

DROP PROCEDURE MySchema.MyStoreProcedure

-------------------------------------------------------------------------------------

删除各个架构下的MyTable表

DECLARE @TableName NVARCHAR(255) = 'MyTable'
DECLARE @Sql NVARCHAR(MAX) = ''

SELECT @Sql = @Sql + 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableName

print @Sql
EXEC sp_executesql @Sql

-- 执行结果：
-- DROP TABLE [schema1].[MyTable];DROP TABLE [schema2].[MyTable];
-- 同时把架构schema1和架构schema2里的MyTable表删了

 -------------------------------------------------------------------------------------

删除一个表里的所有触发器

DECLARE @schemaName NVARCHAR(128) = 'MySchema';
DECLARE @tableName NVARCHAR(128) = 'MyTable';
DECLARE @triggerName NVARCHAR(128);

DECLARE triggerCursor CURSOR FOR
SELECT name
FROM sys.triggers
WHERE parent_id = OBJECT_ID(QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName));

OPEN triggerCursor;

FETCH NEXT FROM triggerCursor INTO @triggerName;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'DROP TRIGGER ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@triggerName);
    EXEC sp_executesql @sql;
    -- print @sql;

    FETCH NEXT FROM triggerCursor INTO @triggerName;
END

CLOSE triggerCursor;
DEALLOCATE triggerCursor;

-------------------------------------------------------------------------------------

查询数据库里各个架构下的各个表和视图里每个字段的名字、数据类型、默认值等信息

select * from INFORMATION_SCHEMA.COLUMNS

-- 执行结果为一个表格
-- TABLE_CATALOG    TABLE_SCHEMA    TABLE_NAME       COLUMN_NAME        ORDINAL_POSITION    COLUMN_DEFAULT
-- MyTestDB         schema1         view1            name    	        1                   NULL
-- MyTestDB         schema2         view2            address	        1                   NULL
-- MyTestDB         schema2         view3            age                2                   NULL

所以要查数据库里有哪些架构，可以这样：

select distinct TABLE_SCHEMA from INFORMATION_SCHEMA.COLUMNS

查询数据库里各个架构下的各个表和视图，跟上面的写法类似，列数少一点而已

select * from INFORMATION_SCHEMA.TABLES

-- 执行结果为一个表格
-- TABLE_CATALOG    TABLE_SCHEMA    TABLE_NAME    TABLE_TYPE
-- MyTestDB         schema1         v_view1       VIEW
-- MyTestDB         schema1         t_table1      BASE TABLE
-- MyTestDB         schema1         v_view2       VIEW

而要查看数据库中哪个表有字段name，则可以这样：

SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'name';

-------------------------------------------------------------------------------------

表格MyTable里，字段id1为主键，字段id2为外键，把外键改为主键

-- 删除原有的外键约束
ALTER TABLE MyTable
DROP CONSTRAINT [FK_MyTable];
---- 删除现有的主键约束
ALTER TABLE MyTable
DROP CONSTRAINT [PK_MyTable];
---- 添加新的主键约束
ALTER TABLE MyTable
ADD CONSTRAINT [PK_MyTable] PRIMARY KEY (id2);

-------------------------------------------------------------------------------------

查看一个表的主键在数据库中的哪些其他表中被引用为外键

select fk.name,fk.object_id,OBJECT_NAME(fk.parent_object_id) as referenceTableName 
from sys.foreign_keys as fk 
join sys.objects as o on fk.referenced_object_id=o.object_id 
where o.name='MyTable'

-- 查询结果将会显示3列
-- name object_id referenceTableName

-------------------------------------------------------------------------------------

表格重命名。第一个参数为旧表名，第二个参数为新表名。旧表名前面要加上架构名字

EXEC sp_rename 'MySchema.MyTable1', 'MyTable2';

也可以直接在表名上右键重命名。

重命名后，原来引用旧表名的视图将会有问题，会显示表名无效。

-------------------------------------------------------------------------------------

查询数据库中哪些视图创建时引用到某一个表

SELECT 
    OBJECT_NAME(object_id) AS ViewName
FROM 
    sys.objects
WHERE 
    type_desc = 'VIEW'
    AND OBJECT_DEFINITION(object_id) LIKE '%MyTable%'
ORDER BY 
    ViewName;

这个SQL语句实际上是根据视图的定义（代码）中是否包含字符串 'MyTable' 来筛选出相关的视图的

-------------------------------------------------------------------------------------

把B表里所有的name写入到A表里跟B表里具有相同id的name字段里

UPDATE TableA SET name = B.name FROM TableA A
inner JOIN TableB B ON A.id = B.id;