Add, delete, change, check in SQL
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 Students (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 are not deleted; cannot be used for tables with external constraint references
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, and the SQL statement to determine if it is null or not as NULL.
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 column name, top is the keyword (no top keyword in Oracle replaces 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 ,
SQL Sever use of T-SQL statements in database (add, delete, change, check)