SQL statement additions and deletions change

First, Increase: there are 4 ways
　　 1. Insert a single line of data using insert:
syntax: Insert [into] < table name > [column Name] values < column values >
Example: INSERT into strdents (name, gender, date of birth) values (' Happy friends ', ' Male ', ' 1980/6/15 ')
Note: into can be omitted; Column name values are separated by commas; column values are quoted as single quotes; If you save token, all columns are inserted sequentially

2. Use the Insert SELECT statement to add data from an existing table to a new table
syntax: INSERT INTO < existing new table > < column name >
Select < original table column name > from < original table name >
Example: INSERT into Tongxunlu (' name ', ' address ', ' email ')
Select Name,address,email
From Strdents
Note: into cannot be omitted; The number, order, data type, etc. of the data being queried must be consistent with the inserted item

　　 3. Use the SELECT INTO statement to add data from an existing table to a new table
Syntax: Select < new table column name > into < new table name > from < source table name >
Example: Select Name,address,email into Tongxunlu from strdents
Note: The new table is created when executing a query statement and cannot be pre-existing
Insert the identity column (keyword ' identity ') in the new table:
Syntax: Select identity (data type, identity seed, identity growth) as column name
into new table from the original table name
Example: Select Identity (int,1,1) as identity column, Dengluid,password into Tongxunlu from struents
Note: The keyword ' identity '

4. Inserting multiple rows using the Union keyword to merge data
syntax: Insert < table name > < column name > select < column value > tnion select < column value >
Example: Insert Students (name, gender, date of birth)
Select ' Happy friends ', ' Male ', ' 1980/6/15 ' union (union = next line)
Select ' Blue xiaoming ', ' Male ', ' 19**/**/** '
Note: The inserted column value must be the same as the number, order, and data type of the inserted column name

Two, delete: There are 2 methods
1. Use Delete to delete data some data
                 syntax: Delete from < table name > [where < Delete condition]
     Example: Delete from a where name= ' Happy friends ' (delete column values in table A are happy friends)
                 NOTE: Deleting an entire row does not delete a single field, so the field name cannot appear after delete
2. Use truncate table  to delete data for an entire table
                 Syntax: TRUNCATE TABLE < tables name;
    example: TRUNCATE TABLE tongxunlu 
    Note: Delete all rows of the table, but the structure, columns, constraints, indexes, etc. of the table are not deleted; You cannot use a table with an external constraint reference

Third, change
　　modifying data with update updates
Syntax: Update < table name > set < column name = update value > [where < update condition;]
Example: Update Tongxunlu set age =18 where name = ' Blue Nickname '
Note: The set can be followed by updated values for multiple data columns, where clauses are optional, to restrict conditions, and if not selected, all rows of the entire table are updated

Iv. Check
　　 1. General Enquiry
syntax: Select < column name > from < table name > [where < query conditional expression] [order by < sorted column name >[ASC or DESC]]
1). Querying all data rows and columns
Example: SELECT * from a
Description: Query all rows and columns in a table
2). Query part rows and columns--conditional query
Example: Select I,j,k from a where f=5
Description: Queries all rows of f=5 in table A and displays the I,J,K3 column
3). Use as to change column names in queries
Example: select name as name from a whrer xingbie= ' man '
Description: Queries all rows in a table where sex is male, displays the name column, and renames the Name column (name) to display
4). Query for empty rows
Example: select name from a where e-mail is null
Description: Query all rows with empty e-mail in Table A, and display the Name column, and the SQL statement to determine if it is null or not as NULL.
5). Use Constants in queries
Example: select Name ' Tangshan ' as address from a
Description: Query Table A, display the Name column, and add an address column whose column values are ' Tangshan '
6). Query returns the limit number of rows (keyword: top percent)
Example 1:select Top 6 name from a
Description: Query Table A, display the first 6 rows of column name, top is the keyword
Example 2:select top percent name from a
Description: Query table A, display column name of 60%,percent as the keyword
7). Query Sort (keywords: order BY, ASC, DESC)
Example: select Name
From a
where chengji>=60
ORDER BY DESC
Note: The query table Chengji all rows that are greater than or equal to 60, and displays the name column in descending order; ASC ascending ORDER By default

2. fuzzy query 　　
1). Use like for fuzzy queries
Note: The LIKE operator uses only strings, so it is only used in conjunction with char and varchar data types
Example: SELECT * from a where name like ' Zhao% '
Description: The query shows a record of the first word in Table A, the Name field, for Zhao

2). Use between to query within a range
Example: SELECT * from a where nianling between and 20
Description: The query shows a record of nianling from 18 to 20 in table a
3). Use in to query within enumeration values
Example: select name from a where address in (' Beijing ', ' Shanghai ', ' Tangshan ')
Note: Query table A in the address value of Beijing or Shanghai or Tangshan records, display the Name field

3. Group query
1). Use GROUP by for group queries
Example: Select StudentID as learner number,         AVG (score) as average score &nb sp;  (Note: Score here is the column name)
from score  (Note: Score here is the table name)
GROUP BY StudentID
Description: Query in table score, press STRD The Entid field is grouped to show the average of the Strdentid field and the Score field; The SELECT statement allows only the grouped columns to be used for the expression of a value returned by each grouping, such as an aggregate function with a column name as a parameter
2). Grouping filtering with a HAVING clause
Example: Select StudentID as learner number,         AVG (score) as average score   (note:　　　　The score here is the column name)
from score  (Note: Score here is the table name)
GROUP by StudentID
have count (score) >1
Note: Following the example above, displays the rows after grouping count (score) >1, because where can only be used when there is no grouping, only after grouping can use having to restrict the condition,

　　4. Multi-table Join query
1). INNER JOIN
① specifying join conditions in the WHERE clause
Example: Select A.name,b.chengji
From a A B
where A.name=b.name
Description: Queries table A and table B for records with the same name field and displays the Name field in Table A and the Chengji field in table B
② using Join...on in the FROM clause
Example: Select A.name,b.chengji
From a INNER join b
On (A.name=b.name)
Description: Ibid.
2). Outer Joins
① LEFT OUTER JOIN query
Example: Select S.name,c.courseid,c.score
From Strdents as S
Left OUTER JOIN score as C
On S.scode=c.strdentid
Description: Queries for rows that meet on conditions in the Strdents and score tables, with the same condition as strdents in Sconde table for score table Strdentid
② RIGHT outer join query
Example: Select S.name,c.courseid,c.score
From Strdents as S
Right outer join score as C
On S.scode=c.strdentid
Description: Queries for rows that meet on conditions in the Strdents and score tables, with the same sconde as score tables in the Strdents table

SQL statement additions and deletions change