SQL SQL statement additions and deletions change

First, Increase: there are 2 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 (' Wang Weihua ', ' Male ', ' 1983/6/15 ')

Note: If you save the token name, all columns will be 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 AddressList (' name ', ' address ', ' email ') Select Name,address,email

From Strdents

Note: The number of data, order, data type, etc. of the query must be consistent with the inserted item

Ii. deletion: There are 2 methods

1. Delete some data using delete

Syntax: Delete from < table name > [where < delete condition;]

Example: Delete from a where name= ' Wang Weihua ' (Delete rows in table A with column values of Wang Weihua)

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 AddressList

Note: Delete all rows of the table, but the structure, columns, constraints, indexes, etc. of the table will not be deleted;

Table for reference with an external constraint

Third, change the data using update updates

Syntax: Update < table name > set < column name = update value > [where < update condition;]

Example: Update AddressList set age =18 where name = ' Wang Weihua '

Note: The set can be followed by the updated values of multiple data columns (non-numeric to quotation marks), the WHERE clause is optional (not a numeric quotation mark), is used to constrain the condition, 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: Queries all rows in a table and

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 where gender= ' 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; SQL statement with IS NULL or is not NULL

To determine if the line is empty

5). Use Constants in queries

Example: select Name ' Beijing ' as address from a

Description: Query Table A, display the Name column, and add an address column whose column values are ' Beijing '

　　　6). Query returns the limit number of rows (keyword: top)

Example 1:select Top 6 name from a

Description: Query Table A, display the first 6 rows of the column name, top is the keyword (there is no top keyword in Oracle

Replace with RowNum)

SELECT * from a where rownum<6

7). Query Sort (keywords: order BY, ASC, DESC)

Example: select Name

From a

where grade>=60

ORDER BY DESC

Note: Query the table for all rows with a score greater than or equal to 60, and display the Name column in descending order; ASC Ascending by default

2. Fuzzy query

　　　1). Use like for fuzzy queries

Note: The LIKE operator is a string of words,

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 age between and 20

Description: The query displays a record of age 18 to 20 in table a

　3). Use in to query within the enumeration (in the back is more data)

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 queries

1). Use GROUP by to 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

2). Group filtering using the HAVING clause

　　　　Example: Select StudentID as learner number, AVG

From score

GROUP BY StudentID

Having count (score) >1

　　　Description: After the example above, display the line after grouping count (score) >1, because where can only be in no group

Only use having to restrict the condition after grouping,

4. Multi-table Join query

1). INNER JOIN

① specifying join conditions in the WHERE clause

Example: Select A.name,b.mark

From a A B

where A.name=b.name

Note: Query table A and table B for records that have the same name field and display the Name field in table A and table B

Mark Field

SQL SQL statement additions and deletions change