PHP Mysql Common SQL statement Examples of in-depth analysis _mysql

1. Inserting data


InsertIntoTable name (column name1,Column Name2,Column name ...)Values(Value1,Value2,Value ...);

insert into Productname , Price Pic_path Values ' Li-Ning ' ,500,< span class= "str" > ' uploads/3245.jpg '

2. Update data
table name set column name 1= value 1,2= value 2[where &NBSP;

update product set Name=, Price=50where Id=;&NBSP;

3. Delete data
Delete from table name [where condition];

Delete from product where id=2;

4. Query all data  
select*from select*from Product

5. Query partial columns
name 1, column name 2 , name from table name;

Select name, price from product;

6. Condition Inquiry
# compare =, <=, >=,!=

Select*from table name where column name = value;

Select*from product where id=2;

# and and &NBSP;

selectfrom table name whereand conditions 2andn;&NBSP;

Select*from product where name=' Li-Ning' and price =;

# or OR

selectfrom table name whereor conditions 2orn

Select*from product where name=' Li-Ning 'or price>;

# not non-

selectfrom table name wherenot1

Select*from product wherenot name=' Li-Ning ';

#in Enumeration

selectfrom table name where column name in 1, value 2 , value &NBSP;

selectfrom product where ID in 23,,10&NBSP;

selectfrom product where ID not in,3,4,10&NBSP;

#like fuzzy query

Select*from table name where column name like '% value ';

Select*from product where name like '%li% ';

#between ..... Scope Query

selectfrom table name where name between &NBSP;

selectfrom order where created between and ' 2011-01-01 '

7. Query sorting
Select*from table name ORDER by column name ordering method;

#排序方式: ASC (Ascending, default), desc (Descending)

selectfrom product order < Span class= "KWD" >by created Desc

8. Limit the number of query results
select*from table name limit starting record number, number of results; select*from product limit 5

Select* from product limit 2,5;

9. Aggregate function
# Count Total Records

Select count(column name)from student;

Select count(ID)from student;

# sum total

Select sum(column name)from student;

Select sum(age)from student;

# avg mean

selectfrom Student

Select avg(age) asavg_age from student;

# max Maximum value

Select max(column name)from student;

Select Max(age)from student;

# min Minimum value

Select min(column name)from student;

selectage from student

10. Subquery
name from student where age < (select Avgage from&NBSP;

Select*from product where ID in(select ID from order );

11. Connection Query


SelectS.UsernameAsStu_name, T.name as Te_name from student S, teacher T where S.teacher_id=t .id