Explain SQL basic syntax in detail

Time:2020-6-1

1. Create database

Create database database name

2. Delete database

Drop database database name

3. Backup SQL Server

Create device for backup data

use master

exec sp_ Addumpdevice ‘name’, ‘new name’, ‘path’

Start backup

Backup database pubs to new name

4. Create table

Create table table name (column name 1 type, column name 2 type)

5. Create a new table based on the existing table

Create table new table name like old table name

Create table new table name as select column name from old table name definition only

6. Add a column

Alter table table name add column name data type

7. Add primary key

Alter table table name add primary key (column name)

8. Add 1 every time from 1 for the auto increment ID attribute

identity (1,1)

9. Create index

Create index index name on table name (column name)

10. Delete index

drop index idx_name

11. CTE query


			;with t as(
			select openid,ROW_NUMBER()over(partition by openid order byopenid)as row
	from #temp)delete t where row>1

12. Use of case when (modify the table name ID, if the name of T is not empty, it is still the name of T, otherwise it will change to the name of the table, and output the modified data to the temporary table)

update pn
			set pn.id=case when t.id>'' then t.id else pn.id end
			output  deleted.id  Into temporary table
			From table name PN with (nolock)
			join #temp t

13. Query, insert, delete, sum, average, maximum

select * from table
			insert into new_table(id,name)values(1,'Tom')
			Delete from table where
			select sum(field1) as sumvalue from table1
			select avg (field1) as avgvalue from table1
			select max(field1) as maxvalue from table1

Through the above content to give you a detailed explanation of SQL basic syntax, I hope this introduction can help you.