inserting data using T-SQL inserting data using Insert1 inserting a row of data
INSERT into table name ( column list ) VALUES ( List of values )
Into can be omitted
List of column names can omit "NOTE" Insert all columns at one time if omitted
Multiple column names and multiple value lists separated by commas
Insert multiple rows of data at once1 Adding data to a table using the INSERT SELECT statement
INSERT into new table name ( column name )
SELECT Column Name
From the original table name
"Attention" multiple column names are separated by "," after Select
The number, order, data type, etc. of the data to be queried must be consistent with the inserted item
The new table name must be created first.
2 Adding data from an existing table to a new table using the SElECT into statement
SELECT the original table name . Column Name
into new table name
From the original table name
"Attention" the original table name after SELECT can be replaced by an as alias
3 specifying an identity column using SELECT identity
SELECT identity ( data type, identity seed, identity growth ) as column name
into new table name
From the original table name
4 inserting data through the Union keyword
INSERT table name ( column name )
SELECT information UNION
SELECT Information
"Note" The last line of information can be omitted UNION
updating Data with T-SQL updating Data with update
Update table name set column name = Update value
WHERE Update Condition
"Attention" Set can be followed by multiple "column name = Update value", modify the values of multiple data columns, any one, using the Tease number segmentation. Cannot appear second SET
The WHERE clause is optional to restrict the updated data condition. If not, all data rows for the entire table will be updated
Delete data using T-SQL Delete data using delete,TRUNCATE TABLE 1. Delete data using Delete
DELETE from table name
WHERE Delete Condition
2. Delete data using TRUNCATE TABLE
TRUNCATE Table name
"Note" In actual work, it is not recommended to use TRUNCATE TABLE statement because the data deleted using it cannot be recovered.
querying data using T-SQL querying using the SELECT statement
1. SELECT Column name
From table name
WHERE query Condition expression
Order by sort column name ASC ( Ascending )/DESC ( descending )
"Attention" The WHERE condition is optional and, if not limited, the query returns data items for all rows.
2. Search All information
SELECT Column Name
From table name
WHERE Condition
3. Query between two values
SELECT Column Name
From table name
WHERE Column name between value 1 and value 2
4. Alias The column name of the query
SELECR column name as alias
From table name
5. Querying column names for null -valued information
SELECT *
From table name
WHERE Column name is NULL
the "note"* number represents all columns
6. Check the top three exam results
SELECT TOP 3 *
From table name
ORDER by column name DESC// put the top three grades in descending order
7. Query information by percentage
SElECT TOP Value PERCENT *
From table name
ORDER by column name DESC// in descending order
8. Querying a column in a table
SELECT Column Name
From table name
WHERE Condition
"Attention" Select column names can have multiple and separated by ","
From indicates that there can be multiple and separated by ","
9. Check the student's test scores for each subject
SELECT S.studentname,sub.subjetname
From Student S,result R,[subject] Sub// alias for table name
WHERE S.studentno = R.studentno
and R.subjectid = Sub.subjectid
Get started MySQL command summary!!!