1.1 [insert special]
Insert [into]
1.2 [the existing table data added to an existing table]
Insert into & lt; The existing new table & gt; (column name) select & lt; The original table column name & gt; The from & lt; The original name of the table & gt;
Example: insert into tongxunlu (' name ', 'address', 'email')
Select the name, address, email
The from Strdents
1.3 [directly with existing table data to create a new table and populate the]
Select & lt; New table column name & gt; Into & lt; The new table name & gt; The from & lt; The source table name & gt;
Example: select the name, address, email into tongxunlu from strdents
1.4 [to insert multiple rows using union keyword merge data]
Insert & lt; The table name & gt;
Select 'happy friends of friends' and' male ', '1980/6/15' union (union says the next row)
Select the 'blue xiao Ming' and 'male', '19 * */* */* *'
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
2 delete
2.1 [delete & lt; satisfy conditions & gt; line]
The delete from & lt; The table name & gt; [where & lt; remove conditions & gt;]
Example: delete from a where name='happy friends of friends' (delete a table column value for happy friends of friends)
2.2 [delete the entire table]
Truncate table & lt; The table name & gt;
Truncate table tongxunlu
Note: delete all rows of table, but the structure table, column, constraints, indexes, etc will not be deleted; Not expressions have built outside constraints reference table
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
3 change
The update & lt; The table name & gt; The set & lt; The column name=update value & gt; [where & lt; update condition & gt;]
Example: the update tongxunlu set age 18 where name=='blue nicknamed'
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
4 check
4.1 ` ` precise query (conditions)
Select & lt; The column name & gt; The from & lt; The table name & gt; [where & lt; try to express query condition & gt;] [the order by & lt; sort the column & gt; (asc or desc]]
4.4.1 [query all data rows and columns]
Example: select * from a
Description: query all rows and columns in a table
4.1.2 [query part - condition query]
Example: select the I, j, k from a where f=5
Note: f=5 a of the lookup table all rows, and display the I, j, k3 column
4.1.3 [in the query to use AS change column names]
Example: select the name as the name from a where clause xingbie='male'
Description: query a gender for male of all rows in the table, display the name column, and the name of the name column to (name) to display
4.1.4/query blank lines
Example: select the name from the where email is a null
Note: email is empty of all lines, a of the query table and display name column; SQL statement used is null or is not null to determine whether to empty lines
4.1.5 [the use of constants in queries]
Example: select the name, 'tangshan as address from Student
Description: lookup table a, show the name column, and add the address column, the column value for the 'tangshan'
4.1.6 [query returned to limit the number of rows (key words: top percent)]
Case 1: select the top 6 name from a
Description: lookup table a, show the column name before six lines, top for keyword
Example 2: select the top 60 percent name from a
Description: lookup table a, shows that 60% of the column name, percent for keyword
4.1.7 [query sort (key word: the order by asc, desc)]
Example: select the name
From a
Where chengji>=60
The order by desc
Description: query a table chengji greater than or equal to 60 all rows, and displays the name column by descending order; The default for ASC ascending
4.2 ` ` fuzzy query
2 [using the like fuzzy query]
Note: only for string like operations vice, so only with char or varchar data type to use
Example: select * from where a name like '%' zhao
Note: query shows in table a, the name field records of the first word for zhao
4.2.2 [the between within a certain range query]
Example: select * from a where clause nianling between 18 and 20
Description: query shows in table a nianling between 18 to 20 record
Holdings/use in a query within the values listed in the
Example: select the name from where a address in (' Beijing ', 'Shanghai', 'tangshan)
Description: lookup table address in a value for the Beijing or Shanghai or record of tangshan display name field
4.3 ` `. Grouping query
4.3.1 [use group by grouping query]
Example: select studentID as student number, AVG (score) as a average score (note: here is the column name)
The from score score (note: here is the name of the table)
Group by studentID
Note: the query in the score table, strdentID field group, showed strdentID fields and the average score field; Select statement only permits are grouped columns and return a value for each grouping expression, for example with a column name as a parameter of aggregation function
Having clause 4.3.2 (used for packet filtering]
Example: select studentID as student number, AVG (score) as a average score (note: here is the column name)
The from score score (note: here is the name of the table)
Group by studentID
Having a count (score) & gt; 1
Description: take the above example, shows that after the grouping count (score) & gt; 1 line, due to the where can only be used when there is no group, group can only be used after having to limit condition,
4.4 ` ` union join query
4.4.1 inside connection
4.4.1.1 [in the where clause specified in the join condition]
Example: select a.n ame, biggest hengji
From a, b
Where a.n ame=b.n ame
Note: the name field in the query table a and table b equal record, and display the name field in table a and table b
the chengji field in the
4.4.1.2 [on] using the join in the from clause...
Example: select a.n ame, biggest hengji
From a inner join b
On (a.n ame=b.n ame)
Description: ditto
4.4.2 outer join
4.4.2.1 [left outer join query]
Example: select s.n ame, Arthur c. ourseID, c.s. core
The from strdents as s
The left outer join score as c
C.s. trdentID on s.s code=
Explanation: the strdents tables and score table query meet on line conditions, conditions for score table strdentID and strdents table sconde same
4.4.2.2 [right outer join query]
Example: select s.n ame, Arthur c. ourseID, c.s. core
The from strdents as s
Right outer join score as c
C.s. trdentID on s.s code=
Explanation: the strdents tables and score table query meet on line conditions, conditions for the strdents sconde in a table with the same score table strdentID
CodePudding user response:
Content comparison basis, suitable for beginners,Found an error,
1.3 [directly with existing table data to create a new table and populate the]
SELECT & lt; New table column name & gt; INTO & lt; The new table name & gt; The FROM & lt; The source table name & gt;
Example: select the NAME, address, email INTO tongxunlu FROM strdents
It should be
The CREATE TABLE & lt; The new table name & gt; AS the SELECT & lt; The source table list & gt;