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

SQL is not only a structured query language, but also a standard language for relational databases. All kinds of databases support SQL as a query language.

T-SQL is an enhanced version of the standard SQL, with many extensions to the SQL command in addition to the standard SQL command. Provides basic functions similar to programming languages. Such as variable description, process control, function function and so on.

When we install the database, the operations we often do on it are nothing more than insert (add), delete, change, and check these four types of operations. Today we will talk about these four operations.

Insert data:

Insert into * * Table name * * (column name 1, column name 2.) where into is optional, you can omit more than # column names and multiple values lists and separate values (column values 1, column values 2.) the order in the values list is consistent with the order of the fields in the data table.

Update data (modify data):

Update * * table name * * set column name = 'update value' set can be followed by the update value of multiple data columns where * * update condition * * where is optional, used to restrict the condition, if not limited, all data rows of the entire table will be updated

Delete data:

Delete from * * Table name * * where * * deletion condition * * if no deletion condition is added, then delete all records in the entire table truncate table * * Table name * * Delete the records of the entire table faster and be used to empty the big data table Note: make sure the data can be deleted before using truncate

Select syntax structure (query data):

Select * select_list* # specify query content into * new_table_name* # store query results in a new table from * table_name* # specify query source where * search_conditions* # specify query condition group by * group_by_expression* # specify grouping condition having * search_conditions* # specify grouping search condition together with group by clause Use order by * order_expression* [asc | desc] # to specify how the query results are sorted

Conditional expression:

1. Constant: indicates that a single specified data is worth symbolizing.

Letters, numbers, or symbols.

2. Column name: the name of the column in the table

3. Unary operator: an operator with only one Operand

"+" represents a positive number and "-" represents a negative number.

4. Binary operator: an operator that combines two operands to perform an operation

Arithmetic operator, bit operator, logical operator, comparison operator

Examples of enquiries:

Select * from * * Table name * * query all columns in the table select * * column name 1, column name 2, column name 3. From * * Table name * * # query table specific column select * * column * * from table name where * search_conditions* (e.g. job = 'manager') # query table specific row select * from test where base salary between 8000 and 10000 # query test table all information about employees between 8000 and 10000 base salary select * from test where base salary 20000 # query table employees whose base salary is less than 10000 or higher than 20000 All information select * from test where base salary in (8000djj9000,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). And 10000 of employees. 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 table for information of an operation engineer surnamed Yang select * from test where remarks is not null # query table for employees whose notes are not empty. 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

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.

Insert uses the select keyword:

Insert into new1 (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 new1 table (note that the new1 table here needs to be established in advance)

Use the union keyword:

Insert into new2 (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 new2

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.