Common additions and deletions to SQL example

First, insert data

(1)INSERT into Persons VALUES (' Gates ', ' Bill ', ' xuanwumen ', ' Beijing ')

(2) INSERT into Persons (LastName, Address)

VALUES (' Wilson ', ' champs-elysees '), (' Wilson2 ', ' champs-elysees2 ')

(3) INSERT into Persons select * from Persons2

(4) SELECT lastname,firstname to persons_backup from Persons

Ii. deletion of data

(1)DELETE from person WHERE LastName = ' Wilson '

(2)drop person

Third, modify the data

(1)UPDATE person SET Address = ' Zhongshan ", city = ' Nanjing '

WHERE LastName = ' Wilson '

(2)update P Set p.address = ' Zhongshan 23 '

from person p INNER join city c on P.cityid = c.id where c.name = ' Beijing '

Iv. Query Statements

1. General Enquiry:

(1) SELECT lastname,firstname from Persons WHERE city= ' Beijing '

ORDER by company DESC, OrderNumber ASC

(2) SELECT DISTINCT Company from Orders

(3) SELECT * from Persons WHERE (firstname= ' Thomas ' OR firstname= ' William ')

and Lastname= ' Carter '

(4) SELECT * from Persons WHERE LastName in (' Adams ', ' Carter ')

(5) SELECT * from Persons WHERE LastName not between ' Adams ' and ' Carter '

(6) SELECT TOP 2 * from Persons (SQL Server only)

(7) SELECT * from Persons LIMIT 5 (MySQL only)

(8) SELECT Po. OrderID, P.lastname, p.firstname from Persons as P,

Product_orders as Po WHERE p.lastname= ' Adams ' and p.firstname= ' John '(alias)

(9) Select column_name (s) from table_name1 UNION all SELECT column_name (s) from Table_name2 (merge result set, union All is directly connected, fetch all values,

Records may be duplicated; Union is a unique value, record is not duplicated)

2. Like query

(1) SELECT * from Persons WHERE city like '% ' + ' N ' + '% '

(2) SELECT * from Persons WHERE city like ' N% '(this method may be used for indexing)

(3) SELECT * from user where userName like ' Hair _1_2 '(_ replaces only one character)

(4) SELECT top * from user where userId like ' [23]% '

([charlist] Word columns any single character, only SQL Server is useful)

(5) SELECT top * from user where userId like ' [^23]0% '

([^charlist] any single character not in Word columns, only SQL Server is useful)

3. Related queries

SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons

INNER JOIN Orders on persons.id_p = orders.id_p ORDER by persons.lastname

(1) JOIN: Returns a row if there is at least one match in the table

(2) Left JOIN: Returns all rows from the table, even if there is no match in the right table

(3) Right JOIN: Returns all rows from the correct table even if there is no match in the left table

(4) Full JOIN: Returns a row if there is a match in one of the tables

Common additions and deletions to SQL example