Copy data between SQL tables, select several random data entries, delete duplicate data, and obtain auto-increment columns.

 

-- Data replication between tables
Select * into yozhu from yo -- copy a table
Select * into yozhu1 from Yo Where 1 <> 1 -- only copy the table structure, no data
Select top 0 * into yozhu2 from yo -- only copy the table structure, no data

Insert into yo (yoname) Select yoname from yo -- copy the current table
Insert into pubs. DBO. Yo select yoname from DBO. Yo -- table replication between databases

Select * from pubs. DBO. yo
Select * from yo

-- Nested query; Any other subquery can be included in the brackets.
Select * from Yo Where yoid in (select yoid from Yo Where yoid in (1, 2, 3 ))

-- Time difference (50 years of query)
-- Yyyy, yyyy
-- Quarterly QQ, Q
-- Mm, m
-- Day dy, Y in the year
-- DD, d
-- Weekly wk, WW
-- Week DW, W
-- Hour HH
-- Min Mi, n
-- Ss, S
-- Millisecond MS
-- Subtle MCS
-- Nanosecond NS

Select * From DBO. Employees where datediff (YY, DBO. Employees. Birthdate, DBO. Employees. hiredate)> 50

-- This code has not implemented the function; sp_executesql is worth studying; the Code is written as this is because the top cannot be followed by Variables
Declare @ startpage int, @ endpage int, @ vvarchar (300)
Set @ endpage = 2
Set @ startpage = 1
Set @ SQL = 'select top '+ STR (@ endpage-@ startpage + 1) + 'from DBO. Employees where RID not in (select top'
+ STR (@ startPage-1) + 'rid from DBO. Employees where RID> 1 )'
Exec sp_executesql @ SQL
 
-- Range Query
Select * From DBO. Employees where birthdate between '2017-01-01 'and getdate ()
Select * From DBO. Employees where birthdate not between '2017-01-01 'and getdate ()
 
-- Delete information not found in the table in the master table. Note: aliases cannot be used in the outer table.
Delete from DBO. Employees where not exists
(Select * From DBO. employeeterritories B where DBO. Employees. employeeid = B. employeeid)
 
-- Randomly selects 5 data entries
Select top 5 * From DBO. Employees order by newid ()
-- From this we can see that randomness is still very scientific.
Select newid ()
 
-- Delete duplicate data. This is the case where the yo table itself is an auto-incrementing primary key.
Delete from Yo Where yoid not in
(Select max (yoid) from Yo group by yoname)

-- This method is not applicable to big data.
Select distinct * into TMP from DBO. yo
Delete from DBO. yo
Insert into yozhu1 select * from TMP

-- Delete duplicate data in non-auto-incrementing column. Add a column. The initial value is 1 and the step value is 1.
Alter table dbo. yozhu1 add Shao int identity (1, 1)
Delete from DBO. yozhu1 where DBO. yozhu1.yoid not in
(Select max (yoid) from DBO. yozhu1 group by yoname)
Alter table dbo. yozhu1 drop column Shao

Select * From yozhu1

-- Find the table created by the user in the database
Select name from SYS. objects where type = 'U'
Select name from SYS. objects where type = 's' -- find the system table

-- Find out some columns in the table
Select name from syscolumns where id = object_id ('yo ')
 
-- Case is the same as swith case in C/C ++/C #.
-- Group yoid first, and then group
Select yoid, (Case yoname
When 'a 'then 'shao'
When 'B' then 'yozhu'
Else 'lil' end)
From DBO. yozhu1 group by yoid, yoname
 
-- Case can also be used after the group by statement
Select yoname, (case when yoid <72 and yoid> 65 then n 'qualified 'else n' unqualified 'end)
From yozhu1 group by case when yoid <72 and yoid> 65 then n' qualified 'else n' unqualified 'end, yoname
 
-- Delete table data with the same effect as Delete
/* Truncate: data deletion has better performance. The WHERE clause cannot be used, and transaction rollback cannot be performed */
Truncate table DBO. yozhu1
 
-- Find the auto-increment column of the table
Select name from syscolumns where id = object_id ('dbo. table_a ')
And (autoval is not null or status = 128)