MS SQL Server Additions and deletions

Data insertion

Syntax: INSERT into table_name (FIELD1,FIELD2......FIELDN) VALUES (value1,vlaue2,... Valuen)

Single-line Insert user type

INSERT into Usertype (typename,typedescription) VALUES (' admin ', ' admin ')

MultiRow Insert User Type

INSERT into Usertype (typename,typedescription) VALUES (' Input clerk ', ' data Entry clerk '), (' finder ', ' can only do data query operation ')

Inserting user Information

INSERT into UserInfo (username,userlogin,userpassword,useremail,usertype) VALUES

(' Zhang San ', ' Zhangsan ', ' Zhangsan ', ' [email protected] ', 1),

(' John Doe ', ' Lisi ', ' Lisi ', ' [email protected] ', 2),

(' Harry ', ' Wangwu ', ' Wangwu ', ' [email protected] ', 1),

(' Zhao Liu ', ' Zhaoliu ', ' Zhaoliu ', ' [email protected] ', 3),

(' Zhao Liu ', ' Zhaoliu ', ' Zhaoliu ', ' [email protected] ', 3)

Data deletion

Drop Table table_name

Delete from table_name [where conditional expression]

Truncate Table table_name

Delete Zhang San user information

DELETE from UserInfo WHERE username= ' Zhang San '

Delete all user data

DELETE from UserInfo

Clear User Data Sheet

TRUNCATE TABLE UserInfo

Delete a user table

Drop Table UserInfo

The difference between Delete and truncate

Truncate cannot have a WHERE condition statement;

Truncate deleted data can not be recovered, faster than delete speed.

Delete deleted data can be recovered; The statement deletes one row at a time and records each row in the transaction log for the deletion.

Truncate Table: Delete content, free space but do not delete definition.

Delete Table: Deletes the definition without deleting the content and does not free up space.

Drop Table: Removes content and definitions and frees up space.

The information for a particular condition does not require the use of delete; if all data in the table is not required, use TRUNCATE; if the table itself is not required, use the drop

Modify

Update table_name Set Field1=new-value1,field2=new-value2 ... [Where Condition expression]

Modify a statement to update one or more fields at the same time.

Use where to qualify, most of the time is limited.

Modify user information for the second name called Zhao Liu

UPDATE userinfo SET userlogin= ' zhaoliu1 ', userpassword= ' zhaoliu1 ', useremail= ' [email protected] ' WHERE id=5

Data query

Grammar:

Select column_name1,column_name2 ... from table_name [WHERE Condition][group by property name [having conditional expression]] [ORDER BY property [ASC | desc]] [l Imit<offset, row Count>]

SQL query statements can use one or more tables to query, and the where statement to set the query condition, the result of the query is a collection.

Use an asterisk (*) to replace all other fields.

GROUP BY statement: Group by the field specified by the property name

Having: A group by can use two calculations after having a group.

ORDER BY: Sorts the specified fields according to the property name ASC Ascending "desc" descending; default ASC

Use the Limit property to set the number of bars that return records, starting with 0, similar to top in SQL Server.

Query Example:

--Query all user types

SELECT * from Usertype

--Query all user information

SELECT * from UserInfo

--Query the top two user information

SELECT Top 2 * from UserInfo

--Query user information in descending order by user number

SELECT * from UserInfo ORDER by ID DESC

--Query user information in ascending order by user number

SELECT * from UserInfo ORDER by ID

--Query user information for surname "Wang"

SELECT * from UserInfo WHERE userName like ' King% '

--Query user information with "a" in the login name

SELECT * from UserInfo WHERE userlogin like '%a% '

--Querying user information for user type Administrator

SELECT * from UserInfo where usertype in (SELECT ID from usertype where typename= ' Administrator ')

--Query the number of users per user type

Select (select TypeName from usertype WHERE usertype.id= userinfo.usertype), COUNT (*) as Typenum from UserInfo GROUP by use Rtype

--Query the name of the names in the user information

SELECT UserName from UserInfo GROUP by UserName have COUNT (*) > 1

MS SQL Server Additions and deletions