SQL additions and deletions check and change statement

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 >
Insert INTO Sheet1
VALUES (' 000000 ', ' 000000 ', ' 0 ', ' Zhang San ', ' 000000 ', ' 000000 ', ' 000000 ', ' 000000 ', ' 0 ', ' 0 ', ' 000000 ', ' 1900-1-1 0:00:00 ', ' Police station ' , ' 0 ', ' Quanshan branch ')
(If you do not write the properties of the table, you need to add the columns in sequence)

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 an identity column in a new table (keyword? identity?):
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: Key words?
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

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= ' 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 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;

To delete data with duplicate fields:

Delete from table name where OrgID in (select OrgID from table name GROUP by OrgID have Count (orgid>1))

Third, change the data using 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

Four, check
1. Normal query
Syntax: Select < column name > from < table name > [where < query conditional expression] [order by < sorted column name >[ASC or DESC]] br> 1). Query all data rows and columns
Example: SELECT * from a description: query all rows and columns in Table A (
2). Query section column--conditional query
Example: Select I,j,k from a WHERE f=5 Description: query Table A f= 5 of all rows, and displays the I,j,k3 column
3). Use as in a query to change the column name example: select name as name from a whrer xingbie= ' man ' Description: Query all rows in a table with sex as male, display the Name column, and rename the Name column (name ) displays
4). Query for Empty rows example: Select name from a where e-mail is null Description: Query all rows in table A that are empty e-mail, and display the name column; The SQL statement uses is NULL or
person is not NULL to determine No is a blank line
5). Use a constant example in a query: 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 return limit number of rows (keyword: top percent) Example 1 : Select Top 6 name from a Description: Query table A, display column name of the first 6 rows, top for the key example 2:select top percent name from a Description: Query table A, display column name of the 60%,percent keyword
7). Query Sort (keywords: order BY, ASC, DESC) Example: Select name from a where chengji>=60 ORDER by desc Description: Chengji All rows in the query table that are greater than or equal to 60. The Name column is displayed in descending order, and the default is ASC ascending

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: Query shows Table A, name field first word for Zhao's record
2). Use between to query an example in a range: SELECT * from a where nianling between and 20 Description: Query shows records from 18 to 20 in table a nianling
3). Use in to query within enumeration values: Select name from a where address in (' Beijing ', ' Shanghai ', ' Tangshan ') Description: Query table A for the address value in 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 is a column name) from score (note: The score here is the table name) group BY StudentID Description: in table score Query, grouped by Strdentid field, displays the average of the Strdentid field and score field; Only the grouped columns is allowed 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 StudentID as learner number, AVG (score) as average (note: Score here is a column name) from score (note: The score here is the table name) group BY StudentID have Coun T (Score) >1 Description: After the example above, displays the row 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. 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 the records with the name field equal, and displays the Name field in Table A and the Chengji field in table B
② using the Join...on example in the FROM clause: 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 ieft outer join score as C on S.scode=c.strdentid description: in Strden Rows in TS tables and score tables that satisfy the on condition are the same as Sconde in the Strdents table for the strdentid of the score table
② RIGHT OUTER join query example: Select S.name,c.courseid,c.score from strdents as S R outer join score as C on S.scode=c.strdentid description: in Strde Rows in the NTS table and the score table that satisfy the on condition are the same as score in the Strdentid table in the Strdents table Sconde

Example of the establishment of the table deletion table: Create TABLE users (uName varchar (primary) key, Ugander varchar (50), uage varchar (+), Ucareer varchar, Unote varchar (ulog), upwd varchar (50))

Vi. deletion of tables: DROP table Users

SQL additions and deletions check and change statement