1, SQL Basic collation (base query)

Detach, attach, backup, restore

--Go heavy

Select DISTINCT column name from table name

--Update

Update Fenshu Set name = ' John Doe ' WHERE code = 9

--Change the table name

sp_rename table Name

Drop DATABASE Database name (delete databases) drop table name

Delete from table where column expression value

Select *from Xinxi where Fenshu between and 100 (two number required before small after large)

Select *from Fenshu WHERE name = ' John Doe ' and nianling = 26

Select *from Fenshu WHERE name = ' John Doe ' or nianling = 26

Select *from Fenshu where name in (' John Doe ', ' Zhao Liu ')

Select *from Fenshu where name not in (' John Doe ', ' Zhao Liu ')

--fuzzy query,% denotes any number of characters

Select *from Fenshu where name like '% four '

--Underscore any one character

Select *from Fenshu where name like ' Li _ '

--underline and bracket, equivalent to in function, any set of satisfied can be queried

Select *from Fenshu where name like ' _[John Doe, Zhao Liu] '

--Sort by age

Select *from Fenshu ORDER BY nianling ASC (default ascending)

Select *from Fenshu ORDER BY nianling Desc (Descending)

--In descending order, check the top two

Select top 2 *from fenshu ORDER BY nianling Desc

--Combined sorting

* Separated by ","

Select *from Score ORDER by Cno Asc,degree desc

--Sort by conditional query. Look for the person named John Doe, and choose the top three high

Select Top 3 *from fenshu where name = ' John Doe ' ORDER by nianling Desc

Cast (column name as data type)

* On behalf of all, change * To column name, you can display the column

Ex. Select top 1 Chinese from xuesheng where class = 1 order by Chinese

Query Chinese lowest score for Class 1 in the Xuesheng list

Delete from haha where name is null----Delete all data containing null in the name

Update haha set cid = ' 370606198703152121 ' Adds a column of data CID

Ex.

Create DATABASE Fenshu

Use Fenshu

CREATE TABLE Study

(

code int,

Name varchar (20),

Chengji Decimal (18,2),

)

Go

Sp_rename Study,xuesheng

ALTER TABLE Xuesheng add [type] varchar

--alter table Xuesheng Add [type] sysname

--alter table Xuesheng Drop column [type]

INSERT into Xuesheng values (' 1 ', ' Zhang San ', ' 90 ', ' Male ')

Insert into Xuesheng (Code,name,[type]) VALUES (' 2 ', ' John Doe ', ' female ')

INSERT into Xuesheng values (' 3 ', ' Harry ', ' 80 ', ' male ')

Select *from Xuesheng

Select *from Xuesheng WHERE name = ' Zhang San '

1, SQL Basic collation (base query)