Shulou(Shulou.com)06/02 Report--

Insert data syntax format:

Insert [into] [column name] values [values list]

[into]: optional, can be ignored. The table name is required, and the column name of the table is optional. If omitted, the order in the values list is the same as the order of the fields in the data table. Multiple column names and multiple values lists are separated by commas.

If you insert a row of data into a test table, you can use the following T-SQL statement:

Insert into test (name, number, title, date of birth, base salary)

Values ('Zhang San', '123465789 engineer', 'Operation and maintenance engineer', '1995-10-01 engineer 12000) # insert the information of employee Zhang San into the table

Note that the symbols in the SQL statement are entered in the English input method, otherwise an error will be reported.

Change the data in the table

Update test set basic salary = 12000 where name ='Li Si'# change Li Si's salary in the test table to 12000 yuan.

Delete data from the table

Delete from test where name = 'Zhang San' # delete Zhang San's row records in the table

Delete from test # Delete all rows in the test table

Truncate table test # Delete all rows in the test table

Both truncate and delete statements are used to delete records, and the differences are as follows:

Truncate can only empty the entire table data without a where clause, while delete can take a where clause to allow certain records to be deleted conditionally. The Truncate table statement does not record the transaction log, and the delete statement records a transaction log for each row deleted no matter how many records are deleted, so it cannot be recovered from the transaction log after the data is deleted by the Truncate table statement. The Truncate table statement deletes all rows in the table, the identity column is reset to 0, and the delete statement does not reset the identity column. The Truncate table statement cannot delete a table that has a foreign key constraint reference, in which case the delete statement can be used.

Select query syntax structure

In SQL server, the syntax of the select statement is as follows: the column to be queried by select [into generates the name of the new table] from [table name] [where query condition] [group by specifies the grouping condition for query results] [having] specifies the grouping search condition, usually with the group by clause [order by specifies collation desc&asc] # desc for descending order and asc for ascending order Ascending order if it is not specified by default

Conditional expression

Logical expression

Query examples

Select * from test # lists all the column information in the test table

Select name, title, basic salary from test # query table name, job title, base salary column

Select name from test where Job = 'Operation personnel' # names of all operation and maintenance personnel in the query table

Select * from test where Base salary between 8000 and 10000 # query all the information of employees in the test table for the base salary between 8000 and 10000

Select * from test where basic salary 20000 # query all the information of employees whose basic salary is less than 10000 or higher than 20000 in the query table

Select * from test where Base salary in (8000pc9000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,

Select * from test where × × like'66%'# query all the information of employees whose numbers begin with 66 in the test table.

Select * from test where name like 'Yang%' and Job = 'Operation engineer' # query the information of the operation and maintenance engineer surnamed Yang in the table

Select * from test where remarks all information about employees whose comments are not empty in the is not null # query table.

Select top 5 * from test # queries the first five rows of data in the table.

Select * from test order by Base salary desc # queries all the information in the test table and displays the query results according to the base salary from high to low.

Select distinct Job from test # query which jobs are in the test table

Use SELECT to generate new data

1. Select uses the into keyword:

Select name, × × number, Job into new01 from test # generates a new table new01 for the names, × × numbers and jobs of all employees in the test table.

2. Insert uses the select keyword:

Insert into Table_1 (name, title, date of birth) select name, title, date of birth from test where Base salary > = 15000 # Save the names, titles, and dates of birth of all employees in the test table whose base salary is greater than or equal to 15000 in the Table_1 table (note that the Table_1 table here needs to be established in advance)

3. Use the union keyword:

Insert into table_1 (name, title, date of birth) select 'Zhang San', 'Operation and maintenance', '1995-01-01' union select'Li Si', 'Operation and maintenance', '1996-01-01' union select name, title, date of birth from test # will save the name, title and date of birth of all employees in the test table, as well as the information about the two newly entered employees, to the new table table_01

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.