SqlServer common commands

Article directory

1, SQL Basics

1.query
SELECT Column name FROM Table name

2.Duplicate removal
SELECT DISTINCT Column name FROM Table name

3.WHERE clause
SELECT Column name FROM Table name WHERE Column operator value

4.AND and OR operator
SELECT * FROM User WHERE UserName='Zhang San' AND UserID=10

5.ORDER BY Sentence
SELECT OrgID, OrgName FROM Organization ORDER BY Company

6.INSERT
INSERT INTO Table name VALUES (Value 1, Value 2,....)

7.Update 
UPDATE Table name SET Column name = New value WHERE Column name = Certain value

8.DELETE
DELETE FROM Table name WHERE Column name = value

Two, enhance

1.IN Operator multiple value query
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

2.INNER JOIN Return table_name1 All data
SELECT column_name(s) 
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

3.INNER JOIN Return table_name2 and table_name1 Intersection data
SELECT column_name(s) 
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

4.RIGHT JOIN Return table_name2 All data
SELECT column_name(s) 
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

5.FULL JOIN 
SELECT column_name(s) 
FROM table_name1
FULL  JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

6.SELECT INTO  Copy table data

SELECT * INTO table_name1  FROM table_name2

7.LIKE Fuzzy query
SELECT column_name FROM table_name WHERE column_name LIKE 'Zhang%'

8.BETWEEN What range is the value of a field

SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2

9.TOP Query the first few data in the database

SELECT TOP 10 * FROM table_name

10.IS NULL/IS NOT NULL Query a field that is not null

SELECT TOP 10 * FROM users where users_name is not null 

SELECT TOP 10 * FROM users where users_name is  null 

Three, function

1.COUNT How much data is there in the query table
SELECT COUNT( * )   FROM table_name

2.AVG Query average
SELECT AVG(column_name) FROM table_name

3.MAX Query the maximum value of a field
SELECT MAX(column_name) FROM table_name

3.Min Query the minimum value of a field
SELECT Min(column_name) FROM table_name

4.SUM  Sum a field
SELECT SUM(column_name) FROM table_name

5.HAVING Clause is generally used for de duplication

SELECT Customer,SUM(qty) FROM Orders GROUP BY Customer HAVING SUM(qty)<100

6.FORMAT Time format conversion

SELECT FORMAT(column_name,format) FROM table_name

Four, actual combat

1.SELECT

1.FOR XML merges multiple data into one data

	SELECT d.DriverName+','
	FROM dbo.TaskDriver td WITH(NOLOCK)    
	INNER JOIN dbo.Driver d WITH(NOLOCK) ON td.DriverID=d.DriverID   
	WHERE td.IsValid=1 AND td.TaskID=384186 FOR  XML PATH('')

2.FOR XML plus subquery plus INNER JOIN

	SELECT A.RoleID,r.RoleName,LEFT(ColumnStr,LEN(ColumnStr)-1) AS ColumnStr 
	FROM(	
		SELECT pr.RoleID  
			,  (SELECT ColumnName+',' 
				FROM PageColumnSetting p3 
				WHERE p3.IsValid=1 AND p3.ParentID>0  
				AND NOT EXISTS(
					SELECT 1 FROM PageColumnSetting p4 
					WHERE p4.IsValid=1 AND p4.ParentID=p3.ID
				)
			FOR XML PATH('')) AS ColumnStr
		FROM PageColumnSetting p1 
		INNER JOIN PageRoleDim pr ON p1.ID=pr.ColumnID AND pr.IsValid=1
		WHERE p1.IsValid=1 AND p1.ParentID>0  
		AND NOT EXISTS(
			SELECT 1 FROM PageColumnSetting p2 
			WHERE p2.IsValid=1 AND p2.ParentID=p1.ID
		)
		GROUP BY pr.RoleID
	)A
	INNER JOIN dbo.[Role] r  WITH(NOLOCK) ON A.RoleID=r.RoleID AND r.IsValid =1

3.FULL JOIN merges two pieces of data into one piece

	SELECT ISNULL(b1.TaskID,b2.TaskID) AS TaskID,ISNULL(b1.BookingID,b2.BookingID) AS BookingID, ISNULL(b1.BookingType,b2.BookingType) AS BookingType,
		   b1.OperationType AS PickOperationType,b1.FirstUpdateConfirmTime AS PickRollbackUpdateConfirmTime ,b1.FirstUpdateReasonType AS PickRollbackUpdateReason, --Regression  
		   b2.OperationType AS UnloadOperationType,b2.FirstUpdateConfirmTime AS PickUpdateConfirmTime ,b2.FirstUpdateReasonType AS PickUpdateReason --Revised delivery/Discharge cargo
	FROM BookingRecordOperation b1
	FULL JOIN BookingRecordOperation b2 ON  b1.BookingID=b2.BookingID AND b1.TaskID=b2.TaskID AND b2.OperationType=2
	AND   b1.OperationType=1

2.Update

1. Update data that does not exist in PageData table

	UPDATE pd
	SET pd.IsDump = 1
	FROM #PageData pd 
	WHERE EXISTS(
		SELECT 1 
		FROM dbo.DeliveryDetail dd WITH(NOLOCK)
		WHERE dd.DeliveryNo = pd.DeliveryNo 
		AND dd.TotalNum <> dd.ResidueNum 
		AND dd.IsValid = 1
	) 
	AND pd.CurrentOrgID = pd.SendOrgID

2. Use update with INNER JOIN

	UPDATE pd 
	SET pd.TotalVolume = a.TotalVolume,
		pd.TotalWeight = a.TotalWeight,
		pd.NumberOfUnits = a.NumberOfUnits,    
		pd.RevenueTon = dbo.GetRevenueTon(a.TotalVolume,a.TotalWeight)
	FROM #PageData pd
	INNER JOIN (    
		SELECT dt.DeliveryNo,
				SUM(dt.ResidueNum) AS NumberOfUnits,    
				SUM((dt.Weight/dt.TotalNum)*dt.ResidueNum) AS TotalWeight,    
				SUM((dt.Volume/dt.TotalNum)*dt.ResidueNum) AS TotalVolume   
		FROM dbo.DeliveryDetail dt WITH(NOLOCK)    
		INNER JOIN #PageTemp pt ON pt.DeliveryNo = dt.DeliveryNo     
		WHERE dt.IsValid = 1 
		AND dt.TotalNum > 0
		GROUP BY dt.DeliveryNo    
	) a ON a.DeliveryNo = pd.DeliveryNo
	WHERE pd.DeliveryNo NOT IN(SELECT * FROM #XqDeliveryNo)
	AND pd.SendOrgID = pd.CurrentOrgID	

3. table / field

Add table
create table tablename(col1 type1 [not null] [primary key],col2 type2 [not null],..)

Delete table
drop table tablename

Modify table
 sp_rename tablename,newtablename / / modify the table name

New field:
ALTER TABLE ADD NVARCHAR (50) NULL

ALTER TABLE [table name] ADD [field name] INT NOT NULL DEFAULT (0) / / ADD

To delete a field:
ALTER TABLE DROP COLUMN

Modify field:
ALTER TABLE [table name] ALTER COLUMN [field name] NVARCHAR (50) NULL

ALTER TABLE [table name] ADD DEFAULT [default] FOR [field name] / / modify the default

4. Index (add, delete, modify and check)

Newly added:
1. General creation syntax:
Create (unique / cluster) index < index name > on < table name >
The types of [extended] indexes include UNIQUE (UNIQUE index), CLUSTERED (CLUSTERED index), NONCLUSTERED (NONCLUSTERED index), and Index_property (index property).

The UNIQUE index can adopt either the clustered index structure or the nonclustered index structure. If the index structure is not specified, the SQL SERVER system adopts the nonclustered index structure by default.

Delete:
1. General deletion syntax
 Drop index < index name >

2. Brief deletion syntax
 drop index [index name]

3. Delete index in a table
 drop index [index name] ON [table name]

Amendment:
1. Use the system stored procedure to modify the index name:
Exec sp \ rename [original name], [new name], [index]
[note] SP [rename] can not only modify the index name, but also the table name, column name and type name.

Check:
1. Using system stored procedure queries:
EXEC sp_helpindex [table name]

2. Use the system's view query:
SELECT * FROM sysindexes WHERE name = 'index name'

Common operations
 1. Determine whether the index exists, and delete if it exists
if exists(select * from sysindexes where name 'soyn')
drop index [soyn] on test1

Like 👍 pay attention ❤ don't get lost

If you think my article is helpful to you, welcome to like, follow, share!
If there are any mistakes in this blog, please comment and advice, thank you very much!

Published 50 original articles, won praise 9, visited 30000+
Private letter follow

Tags: xml SQL P4 Stored Procedure

Posted on Thu, 16 Jan 2020 07:32:51 -0500 by kaumilpatel