sql server 常用命令

sql server常用操作命令

sp_help 表名----查看表名

exec sp_indexhelp 表名 -----查看索引

exec sp_spaceused 表名 -----查看某个表的大小

查看某个库里面所有表的大小

create table tmptest1
(
name varchar(500),
rowscount int,
reserved varchar(500),
data varchar(500),
index_size varchar(500),
unused varchar(500)
);

insert tmptest1(name, rowscount, reserved, data, index_size, unused)
exec sp_MSforeachtable @command1="sp_spaceused '?'";

select concat(sum(cast(replace(reserved,'KB','') as int)),'KB') as 'SIZE' from tmptest1;(统计该库的总大小)；

--查询所有表及注释
SELECT
A.name ,C.value
FROM sys.tables A

LEFT JOIN sys.extended_properties C ON C.major_id = A.object_id
WHERE C.minor_id=0
group by A.name ,C.value

----查询表字段注释

SELECT
A.name AS table_name,
B.name AS column_name,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
WHERE A.name = 'TABLE'

 

今天的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=0
昨天的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=1
7天内的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=7
30天内的所有数据：select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=30
本月的所有数据：select * from 表名 where DateDiff(mm,datetime类型字段,getdate())=0
本年的所有数据：select * from 表名 where DateDiff(yy,datetime类型字段,getdate())=0

查询今天是今年的第几天： select datepart(dayofyear,getDate())
查询今天是本月的第几天：1. select datepart(dd, getDate())
2.select day(getDate())
查询本周的星期一日期是多少 （注意：指定日期不能是周日，如果是周日会计算到下周一去。所以如果是周日要减一天） SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)

查询昨天日期：select convert(char,dateadd(DD,-1,getdate()),111)  //111是样式号，（100-114）

查询本月第一天日期：Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as firstday
查询本月最后一天日期：Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) as lastday      //修改-3的值会有相应的变化

本月有多少天：select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast((cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' ) as datetime ))))

求两个时间段相差几天：select datediff(day,'2012/8/1','2012/8/20') as daysum
在指定的日期上±N天：select convert(char,dateadd(dd,1,'2012/8/20'),111) as riqi    //输出2012/8/21
在指定的日期上±N分钟：select dateadd(mi,-15,getdate())  //查询当前时间15分钟之前的日期​

 

REPLACE ( string_replace1 , string_replace2 , string_replace3 )

参数解析：
string_replace1 待搜索的字符串表达式。string_replace1 可以是字符数据或二
进制数据
string_replace2 待查找的字符串表达式。string_replace2 可以是字符数据或二
进制数据。
string_replace3 替换用的字符串表达式。string_replace3 可以是字符数据或二
进制数据。

CAST 方法

CAST（任何有效的表达试 AS 要转换的数据类型 [数据类型的长度，可选]）

例：SELECT CAST(10000 as varchar(10))

SELECT CAST(10000 as varchar)

Convert 方法

Convert(要转换的数据类型[数据类型长度，可选],任何有效的表达式[,指定显示样式，可选])

例：SELECT CONVERT(datetime,'2017-01-01')

SELECT CONVERT(varchar, CONVERT(datetime,'2017-01-01'),104)

Convert和CAST的区别是可以指定转换的样式

隐式转换数据类型

例： SELECT CAST(10000 as money) * CAST(10 as int)

 

like

select * from test where city like '%N'

select * from test where city like 'g%'

select * from test where city not like '%n'

 

 

top --相当于mysql的limit

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。

SQL 通配符必须与 LIKE 运算符一起使用。

在 SQL 中，可使用以下通配符：

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

 

多表连接查询：

左连接： 从左边的表返回所有行，即使右边的边没有匹配到

内连接： 返回两边中匹配到的行

右连接： 从右边的表返回所有行，即使左边的边没有匹配到

IF not EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE dbid=db_ID('test_sql'))
create database test_sql COLLATE Chinese_PRC_CI_AS;
go
IF EXISTS (SELECT * FROM test_sql.sys.objects WHERE object_id = (select object_id from test_sql.sys.objects where name = 'tmptest1')
AND type in (N'U')) DROP TABLE test_sql.dbo.tmptest1;
go
IF not EXISTS (SELECT * FROM test_sql.sys.objects WHERE object_id = (select object_id from test_sql.sys.objects where name = 'tmptest1')
AND type in (N'U')) create table test_sql.dbo.tmptest1
(
name varchar(500),
rowscount int,
reserved varchar(500),
data varchar(500),
index_size varchar(500),
unused varchar(500)
);
go

 

 

exec sp_who  查看所有登陆的用户

select * from master.dbo.sysprocessess  查看所有登陆的用户，更详细

union all 联合查询，可以联合两个select语句，但是两个select语句中的结构需要一致，且查询的列的顺序和列的数值结构要相似，结果显示的列为第一个select语句的结果列

 

SQL SELECT INTO 实例 - 制作备份复件

下面的例子会制作 "Persons" 表的备份复件：

SELECT *
INTO Persons_backup
FROM Persons

IN 子句可用于向另一个数据库中拷贝表：

SELECT *
INTO Persons IN 'Backup.mdb'
FROM Persons

如果我们希望拷贝某些域，可以在 SELECT 语句后列出这些域：

SELECT LastName,FirstName
INTO Persons_backup
FROM Persons

SQL SELECT INTO 实例 - 带有 WHERE 子句

我们也可以添加 WHERE 子句。

下面的例子通过从 "Persons" 表中提取居住在 "Beijing" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表：

SELECT LastName,Firstname
INTO Persons_backup
FROM Persons
WHERE City='Beijing'

SQL SELECT INTO 实例 - 被连接的表

从一个以上的表中选取数据也是可以做到的。

下面的例子会创建一个名为 "Persons_Order_Backup" 的新表，其中包含了从 Persons 和 Orders 两个表中取得的信息：

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P

 

SQL FOREIGN KEY 约束

外键约束

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

让我们通过一个例子来解释外键。请看下面两个表：

"Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1

请注意，"Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列。

"Persons" 表中的 "Id_P" 列是 "Persons" 表中的 PRIMARY KEY。

"Orders" 表中的 "Id_P" 列是 "Orders" 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。