Database basic query statement (SQL Common additions and deletions change the statement simple review mark)

SQL common additions and deletions change the statement

1 increase

1.1 "Insert single line"
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 ')

1.2 "Add an existing table data to a table that already exists"
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

1.3 "Create a new table directly from the existing table data and populate"
Select < new table column name > into < new table name > from < source table name >
Example: Select Name,address,email into Tongxunlu from strdents

1.4 "Insert multiple rows with union keyword merge data"
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**/**/** '

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2 by deleting

2.1 "Delete < meet criteria > lines"
Delete from < table name > [where < delete condition;]
Example: Delete from a where name= ' Happy friends ' (delete column values in table A are happy friends)

2.2 "Delete entire table"
TRUNCATE TABLE < name >
TRUNCATE TABLE Tongxunlu
Note: Delete all rows of the table, but the structure, columns, constraints, indexes, etc. of the table are not deleted;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3 change

Update < table name > set < column name = update value > [where < update condition;]
Example: Update Tongxunlu set age =18 where name = ' Blue Nickname '

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

4 Check

4.1 "Exact (conditional) query
Select < column name > from < table name > [where < query conditional expression] [order by < sorted column name >[ASC or DESC]]

4.1.1 "Querying all data rows and columns"
Example: SELECT * from a
Description: Query all rows and columns in a table

4.1.2 "Query section column--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

4.1.3 "using as in a query to change column names"
Example: select name as name from a where 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.1.4 "Querying 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.

4.1.5 "Using Constants in Queries"
Example: select name, ' Tangshan ' as address from Student
Description: Query Table A, display the Name column, and add an address column whose column values are ' Tangshan '

4.1.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

4.1.7 "Query Sort (keywords: order BY, ASC, DESC)"
Example: select Name
From a
where chengji>=60
ORDER BY DESC
Description: Queries all rows in a table with Chengji greater than or equal to 60, and displays the name column in descending order; ASC Ascending by default

4.2 "Fuzzy query
4.2.1 "Using like for fuzzy queries"
Note: The like operation pair is only used for 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

4.2.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

4.2.3 "using 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

4.3 ". Group queries
4.3.1 "Using GROUP by for group Queries"
Example: Select StudentID as learner number, AVG (score) as average (note: Score here are column names)
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 only allows an expression that is grouped by columns for one value returned by each grouping, such as an aggregate function with a column name as a parameter

4.3.2 "grouping filtering with having clauses"
Example: Select StudentID as learner number, AVG (score) as average (note: Score here are column names)
From score (note: Score here is the table name)
GROUP BY StudentID
Having count (score) >1
Note: Following the example above, displays the rows of Count (score) >1 after grouping, because where can only be used when there is no grouping, only after grouping can use having to restrict the condition.

4.4 ". Multi-table Join query

4.4.1 Inner joins

4.4.1.1 "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

4.4.1.2 "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.

4.4.2 Outer Joins

4.4.2.1 "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

4.4.2.2 "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

【

differences between left and right outer joins：

Left join the data in the table to the left is the baseline (not even the right table in the left table), if the left table has data on the right table without data, the data in the right table showing the data in the left table is displayed as empty.
The result set of the left join includes all rows of the left table specified in the IEFT clause, not just the rows that match the joined columns.
If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values.
A right join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.

】

Database basic query statement (SQL Common additions and deletions change the statement simple review mark)