逛公众号的时候看到了一篇有意思的技术文章----一行SQl代码能做什么? 在此记录一下,尊重原创,故:
SELECT * INTO Customers_bak FROM Customers WHERE 1=2;
或者
SELECT TOP 0 * INTO Customers_bak FROM Customers;
可以得到与Customers 表结构一样的Customers_bak ,而且Customers_bak 中没有数据。
SELECT TOP 1 1 FROM Customers
SELECT TOP 1 * FROM Customers ORDER BY NEWID();
SELECT TOP 5 * FROM (SELECT TOP 10 * FROM Person ORDER BY ID ) a ORDER BY ID DESC
SELECT ISNULL(Price,0) FROM Orders
注:Price为空值时会默认赋0
SELECT CONVERT(VARCHAR(10),CreateDate,120) FROM Students
注:CreateDate为带时间的完整日期格式,执行后会得到不带时间的字符串,如'2020-05-31'
表Person数据如下:
删除其中除ID以为其他都重复的记录
DELETE FROM Person WHERE ID NOT IN (SELECT MAX(ID) FROM Person GROUP BY Name,Age,Sex,Phone)
SELECT CAST(Amount as NUMERIC(18,2)) FROM Orders
注:Amount是带2位以上的小数格式,转换后的小数会四舍五入。
SELECT NAME FROM SYSOBJECTS WHERE TYPE='U'
SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('Person')
UPDATE A SET A.Name=(SELECT B.Name FROM B WHERE A.ID=B.ID)
SELECT A.*,B.* FROM A,B
SELECT Name FROM Person ORDER BY Name Collate Chinese_PRC_Stroke_CI_AS
WAITFOR DELAY '0:0:3' SELECT * FROM Person
SELECT * FROM Person WHERE ID=1 FOR XML PATH
SELECT CAST(CAST((0.8888*100) AS NUMERIC(18,2)) as VARCHAR(20)) + '%'
SELECT ISNULL(A/NULLIF(B,0),1) FROM TABLEA
或者
SELECT CASE WHEN B=0 THEN 1 ELSE A/B END FROM TABLEA
注:A为任意数值,B为0