Summary of Sql basic syntax

This article introduces the relevant knowledge of "Sql basic Grammar Summary". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Create a database

Create database database name

two。 Delete database

Drop database database name

3. Backup sql server

Create a device for backing up data

Use master

Exec sp_addumpdevice 'name', 'new name', 'path'

Start backup

New name of backup database pubs to

4. Create a tabl

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

5. Create a new table based on an existing table

Create table new table name like old table name

Create table new table name as select column name from old table name defintion 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 each time the id attribute is added from 1

Identity (1 dint 1)

9. Create an 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 byopenid order byopenid) as rowfrom # temp) delete t where row > 1

The use of 12.case when (modify the table name id, if the name of t is not empty, it is still the name of t, otherwise change to the name of the table, and output the modified data to the temporary table)

Update pnset pn.id=case when t.id >''then t.id else pn.id endoutput deleted.id into temporary table from table name pn with (nolock) join # temp t

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

Select * from tableinsert into new_table (id,name) values (1JI 'Zhang San') delete from table where range select sum (field1) as sumvalue from table1select avg (field1) as avgvalue from table1select max (field1) as maxvalue from table1 "Sql basic Grammar Summary" ends here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!