SQL statement (add and revise)

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:insertintovalues (' 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  intoTongxunlu ('name','Address','Email')SelectName,address,email fromStrdents

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:selectinto 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  from example of the original table name: Select Identity (int,1,1 as into 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 >Cases:InsertStudents (name, gender, date of birth)Select 'Happy Friends','male','1980/6/15' Union(union represents the 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

Ii. deletion: There are 2 methods
1. Delete some data using delete

-- syntax: Delete from < table name > [where < delete condition;] Example:deletefromwhere name= ' Happy Friends '(delete the row in table A with the value of happy friends)

Note: Deleting an entire row does not delete a single field, so the field name cannot appear after the delete
2. Use TRUNCATE TABLE to delete data from the entire table

-- Syntax: TRUNCATE TABLE < 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;

Third, change
Modifying data with update updates

-- Syntax: Update < table name > set < column name = update value > [where < update condition;] Example:updateset Age =  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:Selectfromwhere 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 as name  from a whrer xingbie=' male '

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:Selectfromwhereisnull

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' tangshan ' as 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:selecttop6 from a

Description: Query Table A, display the first 6 rows of column name, top is the keyword

Example 2:selecttoppercent 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>=Orde Rbydesc　　　　　　

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*fromwhereto 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*fromwherebetween and -

Description: The query shows a record of nianling from 18 to 20 in table a
3). Use in to query within enumeration values

Example:Selectfromwhere 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 queries
1). Use GROUP by to group queries

Example:Select as learner number, avg as average score  (note: Score here is a column name) from    Score (note: Score here is the table name)  Group by StudentID

Description: Queries in table score, grouped by Strdentid field, showing the average of Strdentid fields and score fields; The SELECT statement allows only grouped columns for the expression of a value returned by each grouping, such as an aggregate function with a column name as a parameter
2). Group filtering using the HAVING clause

Example:Select as learner number, avg as average score (note: Score here is a column name) from    Score (note : Score here is the table name)  Group by StudentID  haveCount(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 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  frominnerjoin  b  on (a.name=b.name)

Description: Ibid.
2). Outer Joins
① LEFT OUTER JOIN query

Example:select  s.name,c.courseid,c.score   from as s  leftoute RJoin 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 as s  right out Erjoin 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 (add and revise)