1---模糊查询
执行:select * from waimai.user where nickname like'%新%'
2--统计个数
执行:SELECT COUNT (* ) FROM 学生表
执行:SELECT COUNT (* ) as 学生总人数 FROM 学生表
3---计算某一列值的总和
执行:select sum (column_name) from table_name
4----查询不是部门10或20的员工:
执行:SELECT ename, job FROM emp WHERE deptno NOT IN (10, 20);
5---查询薪水在1500-3000之间的职员信息:
执行:SELECT ename, sal FROM emp WHERE sal BETWEEN 1500 AND 3000;
6--例如查询哪些职员的奖金数据为NULL:
执行:SELECT ename, sal, comm FROM emp WHERE comm IS NULL;
7-根据数据库 的读取处理情况统计并分类 0 未处理 1 已处理
SELECT t,COUNT(*) as num FROM 数据库名.system_feedback WHERE t in(0,1) GROUP BY t;
8-根据数据库 的读取处理情况统计并分类 并按照时间进行处理
$data =M()->query("SELECT t,COUNT(*) as num FROM 数据库名.system_feedback WHERE ctime LIKE '%{$dtime}%' AND t IN(0,1) GROUP BY t ");
9-获得第一名总金额
$sql = "select user_id,sum(money) as total from wemall_pay_records where order_id =$auction_order_id and is_complete=1 and status=1 group by user_id order by sum(money) desc limit 0,1";
$list = $mysqli->query($sql);
10-基本查询语句
SELECT * FROM `test` WHERE 1 //简单查询
SELECT id,uid FROM newdb.`test` WHERE 1 //查询ID、UID等字段
SELECT remark as r FROM `test` WHERE 1 //别名查询
SELECT * FROM `test` WHERE id=1 //条件查询,相等
SELECT * FROM `test` WHERE id<>2 //条件按查,不相等
SELECT * FROM `test` WHERE id in (1,2,4) //in查询,即查询ID为1,2,4的数据
SELECT * FROM `test` WHERE not in (2,3) //in查询,查询ID不是2,3的数据
SELECT * FROM `test` WHERE `uid` like '%王%' //like模糊查询,%*%前后匹配
SELECT * FROM `test` WHERE id BETWEEN 1 and 3 //条件查询,中间数据
SELECT * FROM `test` WHERE id NOT BETWEEN 1and3 //条件查询
SELECT * FROM `test` WHERE id=1 and `remark`='学生' //多个条件
SELECT * FROM `test` group by `remark` //查询排序
SELECT * FROM `test` order by `regdate` ASC //order by升序排序,放到limit之前
SELECT * FROM `test` order by `regdate` ASC,id DESC //order by按照注册时间升序,ID降序
11-ASC 升序、DESC降序。
SELECT sex,count(*) AS total FROM user GROUP BY sex ; // 分组查询
SELECT * FROM `test` limit 0,3 //数据条数限制,输出三条
SELECT count(*) FROM `test` WHERE 1 //统计查询,可以查询单个统计,例如count(name)
SELECT max(id) FROM `test` WHERE 1 //统计ID最大值是多少
SELECT A.*,B.* FROM news AS A LEFT JOIN news_info AS B ON A.id=B.id; //左连接查询
SELECT A.*,B.* FROM news AS A RIGHT JOIN news_info AS B ON A.id=B.id; //右连接查询
SELECT A.*,B.* FROM news AS A,news_info AS B WHERE A.id=B.id; //内连接查询
SELECT A.*,(SELECT content FROM news_info AS B WHERE B.id=A.id) AS content FROM news as A ; //子查询
以下三个和以上max用法类似
MIN(*)最小值函数
AVG(*)平均值函数
SUM(*)累计值函数
12--基本插入语句:
insert into test (`id`,`uid`,`regdate`,`remark`) values ('','PHP100','2008-07-26','工人') //ID自增,
insert into test (`id`,`uid`,`regdate`,`remark`) values ('','PHP100','now()','工人')
insert into test (`uid`,`regdate`,`remark`) values ('PHP100','2008-07-26','工人') //ID自增省略
insert into test values ('','PHP200','now()','工人') //简便写法,但不提倡
now() 数据库函数,获取当前日期时间函数
13- 更新语句:
update test set uid='php200' where id=6 //set 后是要改后的内容。where 后是更改位置
update test set uid='php200',uname='lisi' where id=6 //set 后是要改后的内容,修改多个值之间使用英文逗号“,”隔开。where 后是更改位置
14-删除语句:
Delete from dbname.`test` where id=3; // 删除一条记录
Delete from dbname.`test` where id in(1,2,3) ; // 删除多条记录
DELETE news_class,news FROM news_class,news WHERE news_class.id=news.cid AND news_class.id=1; // 多表删除
---------------------
更多SQl语句查询地址
http://www.cnblogs.com/W--Jing/p/5923402.html
https://blog.csdn.net/sinat_29581293/article/details/54908847
https://blog.csdn.net/markarch/article/details/78287021