PHP之Mysql常用SQL语句示例

1.插入数据
insert into 表名(列名 1 , 列名 2 , 列名..) values (值 1 , 值 2 , 值...);  

insert into product ( name , price , pic_path ) values ( 'Nike' , 500 , 'uploads/3245.jpg' );

2.更新数据
update 表名 set 列名 1 =值 1 , 列名 2 =值 2 [ where 条件];  

update product set name = 'LiNing' , price = 50 where id = 2 ;  

3.删除数据
delete from 表名 [ where 条件];  

delete from product where id = 2 ;

4.查询所有数据 
select * from 表名; select * from product ;

5.查询部份列
select 列名 1 , 列名 2 , 列名 N from 表名;

select name , price from product ;

6.条件查询
# 比较 =, <, >, <=, >=, !=

select * from 表名 where 列名=值;

select * from product where id = 2 ;

# and 与  

select * from 表名 where 条件 1 and 条件 2 and 条件 N ;  

select * from product where name = 'Nike' and price = 50 ;

# or 或 

select * from 表名 where 条件 1 or 条件 2 or 条件 N ;

select * from product where name = 'Nike' or price > 50 ;  

# not 非 

select * from 表名 where not 条件 1 ;

select * from product where not name = 'Nike' ;  

#in 枚举  

select * from 表名 where 列名 in (值 1 , 值 2 , 值 N );  

select * from product where id in ( 2 , 3 , 4 , 10 );  

select * from product where id not in ( 2 , 3 , 4 , 10 );  

#like 模糊查询  

select * from 表名 where 列名 like '%值%' ;  

select * from product where name like '%Li%' ;  

#between...and... 范围查询  

select * from 表名 where 列名 between 值 and 值;  

select * from order where created between '2010-01-01' and '2011-01-01' ;

7.查询排序
select * from 表名 order by 列名 排序方式;

#排序方式: asc(升序，默认)，desc(降序) 

select * from product order by created desc ;

8.限制查询结果数量
select * from 表名 limit 开始记录数, 结果数量; select * from product limit 5 ;

select * from product limit 2 , 5 ;

9.聚合函数
# count 总记录数  

select count (列名) from student ;  

select count ( id ) from student ;  

# sum 总共  

select sum (列名) from student ;  

select sum ( age ) from student ;  

# avg 平均值  

select avg (列名) from student ;

select avg ( age ) as avg_age from student ;  

# max 最大值  

select max (列名) from student ;  

select max ( age ) from student ;  

# min 最小值  

select min (列名) from student ;  

select min ( age ) from student ;

10.子查询
select name from student where age <( select avg ( age ) from student );  

select * from product where id in ( select id from order );

11.连接查询
select s . username as stu_name , t . name as te_name from student s , teacher t where s . teacher_id = t . id ;