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.SELECT1.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=12.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.CurrentOrgID3. 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!
DotNetFamily Published 50 original articles, won praise 9, visited 30000+ Private letter follow