Database·MySQL·基本查询语句
- 一、常规查询
- 二、条件查询
- 1.比较运算符:>, <, >=, <=, =, !=, <>
- 2.逻辑运算符:and, or, not
- 3.模糊查询:like, rlike
- 4.范围查询:in,not in,between…and,not between…and
- 5.空判断
- 6.order_by
- 7.聚合函数:count(), max(), min(), sum(), avg(), round()
- 8.分组:group_by, group_concat():查询内容, having
- 9.分页: limit放在最后面
- 10.连接查询 :inner join, left join, right join
- 三、一文一哲理
用到的表:
user
class
一、常规查询
1.查询单表所有字段
SELECT * FROM `user`;
2.查询单表部分字段
SELECT `name`,age FROM `user`;
3.使用as给字段起别名
#AS 可省略
SELECT `name` AS '姓名',age AS '年龄' FROM `user`;
SELECT `name` '姓名',age '年龄' FROM `user`;
4.查询指定表的某字段
SELECT `user`.`name` '姓名',`user`.age '年龄' FROM `user`;
5.使用as给表起别名
SELECT u.`name` AS '姓名',u.age AS '年龄' FROM `user` AS u;
6.distinct 消除重复行,必须放在第一个字段之前,作用在后面所有字段
SELECT DISTINCT u.`name` AS '姓名',u.age AS '年龄' FROM `user` AS u;
二、条件查询
1.比较运算符:>, <, >=, <=, =, !=, <>
1.>大于
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age>32;
2.<小于
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age<32;
3.>=大于等于
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age>=32;
4.<=小于等于
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age<=32;
5.=等于
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age=32;
6.!=不等于
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age!=32;
7.<>不等于
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age<>32;
2.逻辑运算符:and, or, not
1.and同时
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE id>2 AND age>=32;
2.or或者
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE id>2 OR age>=32;
3.not不
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE NOT age>=32;
3.模糊查询:like, rlike
1.like 模糊
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE `name` LIKE '%李%';#name包含‘李’的
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE `name` LIKE '李%';#name以‘李’开头的
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE `name` LIKE '李__';#name以‘李’开头,后面跟两个占位符的
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE `name` LIKE '__';#name包含两个字的
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE `name` LIKE '__%';#name至少有两个字的
2.rlike 正则
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE `name` RLIKE '^李.*';#以‘李’开头
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE `name` RLIKE '^李.*思$';#‘李’开头‘思’结尾
4.范围查询:in,not in,between…and,not between…and
1.in 在某集合中
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age IN (21,32,30);
2.not in 不在某集合中
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age NOT IN (21,32,30);
3.between…and 在某范围中
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age BETWEEN 11 AND 30 ;
4.not between…and 不在某范围中
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` WHERE age NOT BETWEEN 11 AND 30 ;
5.空判断
1.null 空
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄',`user`.class '类别' FROM `user` WHERE class IS NULL ;
2.not null 非空
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄',`user`.class '类别' FROM `user` WHERE class IS NOT NULL ;
#只有重复记录完全一样的时候去除重复行,显然这里的类别不一样
6.order_by
1.asc 顺序排序
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` ORDER BY age ASC ;
2.desc 逆序排序
SELECT DISTINCT `user`.`name` '姓名',`user`.age '年龄' FROM `user` ORDER BY age DESC ;
7.聚合函数:count(), max(), min(), sum(), avg(), round()
1.count() 数量
SELECT COUNT(*) FROM `user`;
2.max() 最大值
SELECT MAX(age) FROM `user`;
3.min() 最小值
SELECT MIN(age) FROM `user`;
4.sum() 总和
SELECT SUM(age) FROM `user`;
5.avg() 平均值
SELECT AVG(age) FROM `user`;
6.round() 四舍五入
#计算所有人都平均年龄并保存两位小数
SELECT ROUND(SUM(age)/COUNT(*),2) FROM `user`;
8.分组:group_by, group_concat():查询内容, having
- group by 必须放在orderby和limit之前
#去重,实现distinct实现不了的功能,这里只要name相同即可去重
SELECT * FROM `user` GROUP BY `name`;
#查看相同名字的人数
SELECT name,COUNT(*) FROM `user` GROUP BY `name`;
- group_concat()
#连接一些信息
SELECT `name`,COUNT(*),GROUP_CONCAT(id,':',age) FROM `user` GROUP BY `name`;
- having
#在分组之后的再进行having条件判断
SELECT `name`,COUNT(*),GROUP_CONCAT(id,':',age) FROM `user` GROUP BY `name` HAVING COUNT(*)>=2;
9.分页: limit放在最后面
#限制查询数量
SELECT DISTINCT name '姓名',age '年龄' FROM `user` LIMIT 2 ;
#从(1+1)第二条开始,显示两条记录
SELECT DISTINCT name '姓名',age '年龄' FROM `user` LIMIT 1,2 ;
10.连接查询 :inner join, left join, right join
1.inner join 内连接
SELECT `user`.name '姓名',`user`.age '年龄',`class`.classname '类别' FROM `user` INNER JOIN `class` ON `user`.class=class.id;
2.left join 左连接
SELECT `user`.name '姓名',`user`.age '年龄',`class`.classname '类别' FROM `user` LEFT JOIN `class` ON `user`.class=class.id;
3.right join 右连接
SELECT `user`.name '姓名',`user`.age '年龄',`class`.classname '类别' FROM `user` RIGHT JOIN `class` ON `user`.class=class.id;
注:利用好别名可以做很多事,如自连接