sql server常用命令汇总

http://www.w3school.com.cn/sql/sql_func_len.asp

2018-08-08
Structured Query Language 结构式查询语言

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

UPDATE Persons SET LastName='Wilson' WHERE LastName='Gates'

DELETE FROM Persons WHERE FirstName = 'Fred'

SELECT COUNT(*) FROM Persons


通过 SQL，您如何按字母顺序选取 Persons 表中 LastName 介于 Adams 和 Carter 的所有记录？

SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'

SELECT * FROM Persons WHERE FirstName LIKE 'a%'

查询不重复列
SELECT DISTINCT Company FROM Orders


从"Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人

SELECT * FROM Persons
WHERE City LIKE '[ALN]%'
从 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'

SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er'

同列多个参数查询
SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

联表查询
SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John'

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P 

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

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT（RIGHT）（FULL） JOIN Orders ON Persons.Id_P=Orders.Id_P

查询结果合在一起
SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA

查询表插入新表 
SELECT * INTO Persons_backup FROM Persons

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

SELECT LastName,FirstName INTO Persons_backup FROM Persons

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

SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P  联合查询插入新表

创建库
CREATE DATABASE my_db

创建表

CREATE TABLE Persons
(
Id_P int AUTO_INCREMENT,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)，
numeric(10,5)，
[datetime] NULL
)
添加唯一规则

ALTER TABLE Persons ADD UNIQUE (Id_P)

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

创建索引

CREATE UNIQUE INDEX PersonIndex
ON Persons (LastName, FirstName)

删除索引
DROP INDEX table_name.index_name

仅仅删除表格中的数据

TRUNCATE TABLE 表名称

ALTER 用法（alter改 table表 COLUMN列 drop 删除）

ALTER TABLE table_name ADD column_name datatype

ALTER TABLE table_name DROP COLUMN column_name

ALTER TABLE table_name ALTER COLUMN column_name datatype
ALTER实例
ALTER TABLE Persons ADD Birthday date

ALTER TABLE Persons ALTER COLUMN Birthday year

ALTER TABLE Person DROP COLUMN Birthday
自增约束
CREATE TABLE Persons ( P_Id int PRIMARY KEY IDENTITY, City varchar(255) )


要规定 "P_Id" 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10)

要在 "Persons" 表中插入新记录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）

INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')

创建视图

CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No

SELECT * FROM [Current Product List]

更新视图

SQL CREATE OR REPLACE VIEW Syntax CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName,Category FROM Products WHERE Discontinued=No

SQL DROP VIEW Syntax DROP VIEW view_name




2018-08-09

date

GETDATE() 当前日期和时间
DATEPART()当前日期/或时间单独部分

DATEDIFF() 返回两个日期之间的时间

CONVERT() 用不同的格式显示日期/时间

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

    DATE - 格式 YYYY-MM-DD
    DATETIME - 格式: YYYY-MM-DD HH:MM:SS        精确到3%秒（3.33毫秒）
    SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS    精确到分钟
    TIMESTAMP - 格式: 唯一的数字

smalldatetime占用4个字节，前2个字节存储base date（1900年1月1日）之后的天数。后2个字节存储午夜后的分钟数。

datetime占用8个字节，前4个字节存储base date（即1900年1月1日）之前或之后的天数，后4个字节存储午夜后的毫秒数。

时间差写法
WHERE DATEDIFF(MINUTE,创建时间, GETDATE())<35

SELECT DATEDIFF(DAY, '2018-08-15 15:59:21.860', GETDATE()) AS zsl


NULL 

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS（NOT） NULL


SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products


sp_help Persons   显示表结构


函数用于从文本字段中提取字符

SELECT MID(City,1,3) as SmallCity FROM Persons

SELECT LEN(City) as LengthOfCity FROM Persons

ROUND 函数用于把数值字段舍入为指定的小数位数

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

获取当前时间
SELECT ProductName, UnitPrice, getdate() as PerDate FROM Products


格式化时间
SELECT ProductName, UnitPrice, FORMAT(getdate(),'YYYY-MM-DD') as PerDate
FROM Products