MySQL crud Operations (database additions and deletions)

CRUD Operation:

Create Create (ADD)

Read Read

Update Modify

Delete Delete

1 , add Data

INSERT into Info values (' p009 ', ' Zhang San ', 1, ' n001 ', ' 2016-8-30 12:9:8 ');

Add data to a specific column

INSERT INTO Info (code,name) VALUES (' p010 ', ' John Doe ' );

Handling of self-growing columns

INSERT into family values (' ', ' p001 ', ' data ' , ' T001 ', ' data ' , 1);

Insert into Table name Values ( value )

2 , delete data

Delete all data

Delete from family

Delete a specific data

Delete from Info where code= ' p001 '

Delete from Table name where conditions

3 , modify Data

Modify all data

Update Info set name= ' Xu Yepeng '

Modify specific data

Update Info set name= ' Lu Yongle ' where code= ' P002 '

Modify multiple columns

Update Info set name= ' Lu Yongle ', sex=1 where code= ' p003 '

Update Table name Set What to modify where conditions

4 , read data

(1) Simple Read , Querying all Columns (*)  all rows (without conditions)

SELECT * FROM Info

(2) reading a specific column

Select Code,name from Info

(3) Conditional Query

SELECT * from Info where code= ' p003 '

(4) Multi-Criteria Query

SELECT * from Info where code= ' p003 ' or nation= ' n002 ' # or the relationship

SELECT * from Info where sex=0 and nation= ' n002 ' # with the relationship

(5) keyword query (fuzzy query)

Check all cars that contain Audi

SELECT * from car where name '% Audi % '; percent percent % represents any number of characters

Check to ' Crown ' all cars at the beginning

SELECT * from car where name is like ' Crown % ';

The second character in the query car name is ' Horse ' of the

SELECT * from car where name like ' _ Horse % '; Underline _ represents any one character

(6) Sort Queries

SELECT * from Car order by powers # Default ascending order

SELECT * from car ORDER by powers Desc # Ascending ASC Descending desc

First Press Brand sort in ascending order, then follow Price Descending Row

SELECT * FROM car ORDER BY brand,price Desc

(7) Scope Query

SELECT * from car where price>40 and price<60

SELECT * from car where price between and 60

(8) Discrete Query

SELECT * from car where price=30 or price=40 or price=50 or price=60;

SELECT * from car where price in (30,40,50,60)

SELECT * from car where price not in (30,40,50,60)

(9) aggregate functions (statistical queries)

Select COUNT (*) from car

Select COUNT (code) from car # take all of the data bar number

Select SUM (price) from car # find the sum of prices

Select AVG (price) from car # to find the average price

Select Max (price) from car # To find the maximum value

Select min (price) from car # To find the minimum value

(Ten) Paging Query

select * from car limit 0,10 # paged Query, skip a few data (0) take a few (Ten)

specify a number of bars to display per page: m

Current page: N

SELECT * FROM car limit (n-1) *m,m

(one) go to re-query

Select distinct brand from car

(a) Group Queries

Check the number of cars under each series in the car table

Select Brand,count (*) from car GROUP by brand

After grouping, only the column or aggregate function can be queried

the price average value of the series is greater than + the series Code

Select brand from car GROUP by Brand had AVG (price) >40

the maximum fuel consumption of the series is greater than 8 the series Code

Select brand from car GROUP by Brand have Max (oil) >8

Advanced query:

1. Connection Query

SELECT * FROM Info,nation

To form a Cartesian product

SELECT * FROM Info,nation where Info.nation=nation.code

Select Info.code,info.name,info.sex,nation.name as ' ethnic ', info.birthday from info,nation where Info.nation=nation.code

SELECT * from Info join Nation on Info.nation=nation.code

2. Joint Queries

Select Code,name from Info

Union

Select Code,name from Nation

3. Sub- query

The result of a subquery query as a condition of the parent query

( 1 unrelated subqueries: When a subquery executes, it has no relation to the parent query

Check the Nation for ' Han ' of all student information

SELECT * from Info where nation= (select code from Nation where Name= ' han ')

Query the manufacturer for ' FAW Volkswagen ' all the car information

SELECT * FROM car where brand= ()

Select Brand_Code from Brand where prod_code= ()

Select Prod_code from Productor where Prod_name= ' FAW Volkswagen '

SELECT * from car where brand in (select Brand_Code from Brand where prod_code= (select Prod_code from Productor where Prod_name= ' faw Volkswagen ' )

( 2 ) Related sub-query

A subquery needs to use the content of the parent query when it executes

Check the car table, car fuel consumption is less than the series average fuel consumption of all vehicle information

SELECT * from car where oil< ( Average fuel consumption for this series )

Select AVG (oil) from car where brand = ( this series )

SELECT * from Car a where oil< (select Avg. from car b where B.brand =a.brand)

MySQL crud Operations (database additions and deletions)