常用sql语句
//SELECT 查询语句
SELECT 查询字段 AS 别名字段 FROM 表名
简单查询
SELECT * FROM pre_person
限制查询字段
SELECT person_id,person_name FROM pre_person
统计总数
SELECT COUNT(person_id) AS pid FROM pre_person
带有条件查询
//比较运算符
SELECT * FROM pre_person WHERE person_id = 1;
SELECT * FROM pre_person WHERE person_id >= 1;
SELECT * FROM pre_person WHERE person_id <= 1;
SELECT * FROM pre_person WHERE person_id < 1;
SELECT * FROM pre_person WHERE person_id != 1;
SELECT * FROM pre_person WHERE person_id <> 1;
//逻辑运算符
//AND 两个都要为真 逻辑与
SELECT * FROM pre_person WHERE person_id = 1 AND person_name = 'aaa';
//OR 只要有一个成立就行
SELECT * FROM pre_person WHERE person_id = 0 OR person_name = '张三';
//NOT person_id 除了 123 以外的
SELECT * FROM pre_person WHERE person_id NOT IN(1,2,3);
//范围运算
SELECT * FROM pre_person WHERE person_id BETWEEN 1 AND 5;
等同于:person_id >= 1 AND person_id <= 5
//列表运算
SELECT * FROM pre_person WHERE person_id IN(1,4,5,6,8);
//模糊匹配
% 匹配任意数量和任意字符
_ 匹配单个数量的任意字符
//匹配所有
SELECT * FROM pre_person WHERE person_name LIKE '%张三%';
//匹配张三结尾
SELECT * FROM pre_person WHERE person_name LIKE '%张三';
//匹配张三开头
SELECT * FROM pre_person WHERE person_name LIKE '张三%';
SELECT * FROM pre_person WHERE person_name LIKE '张三';
//匹配单个字符
SELECT * FROM pre_person WHERE person_name LIKE '_张三';
SELECT * FROM pre_person WHERE person_name LIKE '_张三_';
//连表查询
//LEFT JOIN 左连表
//RIGHT JOIN 右连表
//INNER JOIN 内连表
//LEFT JOIN 以左表为标准去查询,匹配副表数据如果没有就显示为null空 但是一定要查出左表(主表)
SELECT * FROM pre_person AS person LEFT JOIN pre_department AS department ON person.department_id = department.department_id WHERE person.person_name = '张三';
//RIGHT JOIN
//以右边表为标准,做查询 没有查到的就返回空
SELECT * FROM pre_person AS person RIGHT JOIN pre_department AS department ON person.department_id = department.department_id WHERE 1;
//inner join
//查询两边条件都成立的情况,只要有一边不成立就不查询
SELECT * FROM pre_person AS person INNER JOIN pre_department AS department ON person.department_id = department.department_id WHERE 1;
//多表连接
SELECT * FROM pre_person AS person LEFT JOIN pre_department AS department ON person.department_id = department.department_id LEFT JOIN pre_job AS job ON person.job_id = job.job_id WHERE 1;
限制查询结果 只查询5条
SELECT * FROM pre_person LIMIT 5;
从第2条开始查 查询5条 索引从0开始
SELECT * FROM pre_person LIMIT 1,5;
//查询排序 ORDER BY 字段 排序方式(ASC升 DESC降序)
SELECT * FROM pre_person ORDER BY person_id DESC LIMIT 1,5;
SELECT * FROM pre_person ORDER BY person_id ASC LIMIT 1;
//COUNT 统计个数返回一条记录
SELECT COUNT(*) AS c FROM pre_person
//min 最小值
SELECT MIN(person_id) AS pid FROM pre_person
//max 最大值
SELECT MAX(person_id) AS pid FROM pre_person
//SUM 总和
SELECT SUM(person_id) AS pid FROM pre_person
//AVG 函数返回数值列的平均值
SELECT AVG(person_id) AS pid FROM pre_person
//group by 分组
//根据author分组(把相同的author数据分到一组)
SELECT * FROM pre_person group by person_name;