SQL additions and deletions change

drop table Student Delete tables
ALTER TABLE Renyuan add cc int Modify tables to add an int type called CC column to the People table
ALTER TABLE Renyuan drop column cc Delete cc columns
Drop datebase Ceshi Delete database
Create Add data
Read reading data *
Update modifies data
Delete Deletes data
1 Adding data
INSERT into Nation values (' n002 ', ' hui ') add a single quotation mark to the contents of the parentheses, change it to a string type, insert one row at a time
INSERT into Nation values (' n003 ', ') only add symbols do not add people, column add cannot be empty
Insert into Nation (code,name) vlaues (' n004 ', ' Uighur ')
2 Deleting data
Delete from Nation deletes all data in Nation
Delete from Friends where ids = 5 Deletes data from IDs 5 in Friends
3 Modifying Data
Update Friends set fcode= ' p016 ' modifies all
Update Friends set fcode= ' p006 ' where IDs = 6 modifies the line of IDs 6 for FCode to p006
Update Friends set fcode= ' p006 ', mcode= ' p002 ' where ids = 6,

Inquire
1 Simple Query
SELECT * FROM info query all data in Info table * represents all columns
Select Code,name from info query the Code and Name column in the info table
Select Code as ' Code ', name as ' name ' from Info assign alias to column
2 Conditional Query
SELECT * from Info where code= ' p001 '
SELECT * from Info where sex= ' true ' and nation= ' n001 '
3 Range queries
SELECT * from Car where price>40 and price<50 (between 50) the notation in parentheses achieves the same function as before, which is simple
4 Discrete queries
Select *from Car where Code in (' c001 ', ' c005 ', ' c010 ') can be reversed by adding not to in front
5 Fuzzy Query
SELECT * from Car where Name '% BMW% ' Check contains BMW% for any number of characters, limited to use in fuzzy queries
SELECT * from Car where Name like ' BMW% ' look at the beginning of the BMW
SELECT * from Car where Name like '% BMW ' check end of BMW
SELECT * from Car where Name ' __e% ' query the third character is E of _ represents a character
6 Sorting queries
SELECT * FROM-Car ORDER BY-price order BY-ORDER by the following keywords, by default ascending
SELECT * from Car ORDER BY price desc desc Descending
SELECT * from Car ORDER by oil desc,price ASC Front is the main, first according to the main row, and then according to the secondary
7 Paged Query
Select Top 5 * from Car
Select Top 5 * from car where Code not in (select Top 5 Code from car)
Current page: page=2 Show row =10 per page
Select Top row * from car where Code not in (select Top (page-1) *row Code from car)
8 go to re-query
SELECT DISTINCT brand from Car remove duplicate Brand values
9 Group queries
SELECT * FROM Car GROUP BY Brand has count (*) >2 is grouped according to brand, provided the quantity is greater than 2 count (*) for the number of delegates
10 aggregate functions (statistical query)
Select COUNT (*) from Car queries all data bars
Select count (code) from car query Code number of bars
The select SUM (Price) is calculated from Car and
Select AVG (price) from Car average
Max Max
Min min

SQL additions and deletions change