Shulou(Shulou.com)05/31 Report--

This article mainly explains "the usage of adding, deleting, changing and checking SQL sentences". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the usage of adding, deleting, changing and checking SQL sentences".

Keyword: sql statement (add, delete, change and check)

1. Increase: there are 4 ways

1. Use insert to insert a single row of data:

Syntax: insert [into] [column name] values

Ex.: insert into Strdents (name, sex, date of birth) values ('happy friends', 'male', '1980Accord 6Comp15')

Note: into can be omitted; column values are separated by commas; column values are caused by single quotes; if table names are omitted, all columns are inserted in turn.

two。 Use the insert select statement to add data from an existing table to an existing new table

Syntax: insert into

Select from

Example: insert into tongxunlu ('name', 'address', 'email')

Select name,address,email

From Strdents

Note: into cannot be omitted; the number, order and type of data obtained by the query must be consistent with the inserted items.

3. Use the select into statement to add data from an existing table to a new table

Syntax: select into from

Example: select name,address,email into tongxunlu from strdents

Note: the new table is created when the query statement is executed and cannot exist in advance.

Insert the identity column (keyword 'identity') into the new table:

Syntax: select identity (data type, identification seed, identification growth) AS column name

Into new table from original table name

Example: select identity (int,1,1) as identity column, dengluid,password into tongxunlu from Struents

Note: keyword 'identity'

4. Insert multiple rows by merging data using the union keyword

Syntax: insert select tnion select

Example: insert Students (name, sex, date of birth)

Select 'Happy friend', 'male', '1980 union' (union represents the next line)

Select 'Blue Xiaoming', 'male', 'nineteen thousand dollars, one hundred percent, one hundred percent.

Note: the inserted column value must be the same as the number, order and data type of the inserted column name.

2. Delete: there are 2 ways

1. Use delete to delete some data

Syntax: delete from [where]

Example: delete from a where name=' Happy Friends'(delete the rows listed in Table a with values of Happy Friends)

Note: deleting the entire row does not delete a single field, so the field name cannot appear after the delete

two。 Use truncate table to delete data from an entire table

Syntax: truncate table

Example: truncate table tongxunlu

Note: delete all rows of the table, but the table structure, columns, constraints, indexes, etc., will not be deleted. You cannot use tables referenced by external constraints.

III. Reform

Use update to update and modify data

Syntax: update set [where]

Example: update tongxunlu set age = 18 where name = 'blue nickname'

Note: set can be followed by update values of multiple data columns; the where clause is optional to restrict conditions, and if not selected, all rows of the entire table are updated

4. Check

1. General query

Syntax: select from [where] [order by [asc or desc]]

1)。 Query all data rows and columns

Example: select * from a

Description: query all rows and columns in table a

2)。 Query part of the row-conditional query

Example: select iJournal jjjjjrek from a where fumig5

Description: query all the rows of fend5 in table an and display the iPercentjPowerk3 column.

3)。 Use AS to change column names in a query

Example: select name as name from a whrer xingbie=' male'

Description: query all rows of male gender in table a, display name column, and rename name column to (name) display

4)。 Query blank lines

Example: select name from a where email is null

Description: query all rows in table a where email is empty and display the name column; use is null or is not null in the SQL statement to determine whether it is a blank row or not

5)。 Use constants in queries

Example: select name 'Tangshan' as address from a

Description: query table a, display name column, and add address column, whose values are all 'Tangshan'

6)。 The query returns a limited number of rows (keyword: top percent)

Example 1:select top 6 name from a

Description: query table a, showing the first six rows of the column name, with top as the keyword

Example 2:select top 60 percent name from a

Description: query table a, showing that 60% of the columns name are keywords

7)。 Query sorting (keywords: order by, asc, desc)

Example: select name

From a

Where chengji > = 60

Order by desc

Description: query all rows in the table with chengji greater than or equal to 60, and display name columns in descending order; default is ASC ascending order

two。 Fuzzy query

1)。 Fuzzy query using like

Note: like operators only use strings, so they are only used in conjunction with char and varchar data types

Example: select * from a where name like 'Zhao%'

Description: query display table a, the first word in the name field is Zhao's record

2)。 Use between to query within a range

Example: select * from a where nianling between 18 and 20

Description: query shows the records of nianling between 18 and 20 in table a

3)。 Use in to query within enumerated values

Example: select name from a where address in ('Beijing', 'Shanghai', 'Tangshan')

Description: query table a with an address value of Beijing or Shanghai or Tangshan, showing the name field

3. Grouping query

1)。 Use group by for grouping queries

Example: select studentID as student number, AVG (score) as average score (Note: score here is the column name)

From score (Note: score here is the table name)

Group by studentID

Description: query in table score, grouped by strdentID field, showing the average of strdentID field and score field; only the columns to be grouped and the expression of a value returned for each group are allowed in the select statement, such as an aggregate function with a column name as a parameter

2)。 Group filtering using the having clause

Example: select studentID as student number, AVG (score) as average score (Note: score here is the column name)

From score (Note: score here is the table name)

Group by studentID

Having count (score) > 1

Note: following the example above, the line with count (score) > 1 after grouping is displayed. Since where can only be used when there is no grouping, having can only be used to restrict the condition after grouping.

4. Multi-table join query

1)。 Inner connection

① specifies the join condition in the where clause

Example: select a. Name not b. Chengji

From a,b

Where a.name=b.name

Description: query the records with equal name fields in table an and table b, and display the name field in table an and the chengji field in table b

② uses join in the from clause. On

Example: select a. Name not b. Chengji

From an inner join b

On (a.name=b.name)

Description: same as above

2)。 Outer connection

① left outer join query

Example: select s. Name _ department c.

From strdents as s

Left outer join score as c

On s.scode=c.strdentID

Description: query the rows that meet the on condition in the strdents table and the score table if the strdentID of the score table is the same as the sconde in the strdents table

② right outer join query

Example: select s. Name _ department c.

From strdents as s

Right outer join score as c

On s.scode=c.strdentID

Description: query rows that meet the on condition in the strdents table and the score table if the sconde in the strdents table is the same as the strdentID in the score table

Thank you for your reading, the above is the content of "the usage of SQL sentence addition, deletion, change and query". After the study of this article, I believe you have a deeper understanding of the usage of SQL sentence, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.