单表查询
/*
select 列的列表/* |函数 from 表名
[where 条件] --条件
[group by 列名] --分组
[having 条件] --分组条件
[order by 列名...] --排序
[limit m,n] ---分页
*/
# 顺序: where - group by -having-order by-limit
1.简单查询
# 查询stu表所有的列所有行的数据
# * 所有列, 实际开发中, 尽量不要写*
# 所有行, where 条件恒成立, 不写where ,默认:条件恒成立
# 查询返回的是一个结果集(表(虚拟表))
SELECT * FROM stu;
#查询部分列
# 查询所有学生的 学号,姓名
SELECT sid, sname FROM stu;
#给列取别名, 给表取别名
# 列 as 别名 as可以省略
SELECT sid AS "学号", sname 姓名 FROM stu;
# 如果别名之间使用到特殊符号, 比如 空格
# 一定要使用 双引号 引起
# 在数据库中, 值只能使用单引号引起
SELECT sid AS "学 号", sname "姓 名" FROM stu;
#表使用别名 后期表关联查询使用
# 列名: 表名.列名
# 如果列名很长 简化 表名.列名 --> 表别名.列名
SELECT stu.sid , s.sname FROM stu s;
2.条件查询
# where 条件
# 比较运算符 =、!=、<>(不等于)、<、<=、>、>=
# 逻辑运算符 and(与) or(或) not(非)
# BETWEEN…AND 大于等于 值 并且小于等于值
# in(值1, 值2,...)
# not in (值1, 值2,...)
# is null
# is not null
/*
写sql步骤:
1. 找表(需求,数据来源那些表)
2. 找查询的列(数据库的列,函数列,计算列)
3. 找条件(where中的条件, 表连接的条件, 分组的having条件)
4. 对select语法进行拼接
*/
SELECT * FROM stu;
#1.查询性别为女,并且年龄50的记录
SELECT * FROM stu WHERE gender ='female' AND age = '50';
#2.查询学号为S_1001,或者姓名为liSi的记录
SELECT * FROM stu WHERE sid = 'S_1001' OR sname='liSi';
#3.查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE sid = 'S_1001' OR sid='S_1002' OR sid='S_1003';
# in(列表) --> 值1,值2,...
# 只要符合列表的任意一个值,
# sql执行的顺序:1. from 表 全表
# 2. where 子句: 从第一步中结果筛选
# 3. select *
SELECT * FROM stu WHERE sid IN('S_1001','S_1002','S_1003');
#4.查询学号不是S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE sid != 'S_1001' AND sid!='S_1002' AND sid!='S_1003';
SELECT * FROM stu WHERE NOT(sid = 'S_1001' OR sid='S_1002' OR sid='S_1003');
SELECT * FROM stu WHERE sid NOT IN('S_1001','S_1002','S_1003');
#null: 这一列没有录入值
# null 与任何值进行计算,结果永远为null
SELECT sid,sname,age,age+10 newAge FROM stu;
# null与任何比较,结果都false
# 比较null, = null 错误 != null 错误
# is null 正确 is not null 正确
#5.查询年龄为null的记录
SELECT * FROM stu WHERE age = NULL; #错误
SELECT * FROM stu WHERE age IS NULL; #正确
#查询年龄不为null的记录
SELECT * FROM stu WHERE age IS NOT NULL; #正确
SELECT * FROM stu WHERE age != NULL; #错误
#6. 查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age >=20 AND age <=40;
#BETWEEN 小值 AND 大值: 大于等于小值 并且 小于等于大值
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
#7.查询性别非男的学生记录
SELECT * FROM stu WHERE gender != 'male';
SELECT * FROM stu WHERE gender <> 'male';
SELECT * FROM stu WHERE NOT(gender = 'male');
3.模糊查询
#like 相似的匹配 通配符: %: 任意多个任意字符 _: 一个任意字符
#= 相等,精确匹配
# 查询姓名以l开头的
# l% 张% li%
SELECT * FROM stu WHERE sname LIKE 'l%';
# 查询姓名以i结尾
# %i
SELECT * FROM stu WHERE sname LIKE '%i';
# 查询姓名第二个字符是i
# _i%
SELECT * FROM stu WHERE sname LIKE '_i%';
# 查询姓名中包含i的
# %i%
SELECT * FROM stu WHERE sname LIKE '%i%';
# 查询姓名由5个字符组成
# _____
SELECT * FROM stu WHERE sname LIKE '_____';
4.字段控制
# 1. 去重 distinct 去掉重复的记录
#查询学生性别
# select distinct 列集合
# distinct 只能写一个, 对select 查询的所有列组合去重
SELECT DISTINCT gender , sid FROM stu;
5.常数列
## 常数列: 人为给查询添加n个指定值的列
# s.* s对应表的所有列
SELECT s.*, '学生' AS role FROM stu s;
6.计算列
## 计算列: 有表中的列计算(+-*/ 求余)的结果
SELECT * FROM emp;
#查询员工月收入= 薪水+ 奖金
SELECT e.*, e.sal + e.comm AS 月收入 FROM emp e;
# 注意: null与任何值计算结果都是null
# 对null 进行转换: 0
#IFNULL(值1,值2) 如果值1是null, 返回值2,如果不为null,返回值1
SELECT e.*, e.sal + IFNULL(e.comm,0) AS 月收入 FROM emp e;
7.排序
/*
order 列名|函数列|计算列 排序方式,列名1|函数列1|计算列1 排序方式1,....
排序方式: 默认升序: asc
降序: desc
Order by 后面可以接多个列, 代表的含义:
首先按照order by的第一个列进行主排序,
如果order by的第一个列中值重复,
order by的第一列的重复值按照order by的第二列排序
*/
#1.查询所有学生记录,按年龄升序排序
SELECT * FROM stu ORDER BY age ;
SELECT * FROM stu ORDER BY age ASC ;
#2.查询所有学生记录,按年龄降序排序
SELECT * FROM stu ORDER BY age DESC ;
#3.查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序
SELECT * FROM emp ORDER BY sal DESC, empno DESC;
SELECT * FROM emp ORDER BY empno DESC, sal ASC;
8.聚合函数
/*
count(列名|*|常数列) 求记录数, 统计有多少行记录
sum(列名|计算列) 求总和
avg(列名|计算列) 求平均
max(列名|计算列) 求最大值
min(列名|计算列) 求最小值
*/
#聚合函数对null忽略
#5个聚合函数都是对某列的统计, 得到结果是总结果
SELECT COUNT(age) FROM stu;
#4.查询emp表中记录数
#count(列名) 选择这一列有null, 忽略, 结果不对
# 要求选择的列一定不能包含null
SELECT * FROM emp;
SELECT COUNT(empno) FROM emp;
SELECT COUNT(comm) FROM emp;
#count(*) 对所有列来统计, 不会出现一行的所有列都为null
#一行的所有列都为null 才忽略 统计的结果正确
# 效率太低:
SELECT COUNT(*) FROM emp;
SELECT e.*,1 FROM emp e;
# count(1) 正确, 效率高 推荐的
SELECT COUNT(1) FROM emp;
#5.查询emp表中有奖金的人数:
SELECT COUNT(1) FROM emp WHERE comm IS NOT NULL;
SELECT COUNT(comm) FROM emp;
#6.查询emp表中月薪大于2500的人数
SELECT COUNT(1) FROM emp WHERE sal > 2500;
#7.统计月薪与佣金之和大于2500元的人数
SELECT COUNT(1) FROM emp WHERE sal+IFNULL(comm,0) > 2500;
SELECT sal+IFNULL(comm,0) FROM emp
#8.查询有佣金的人数,以及有领导的人数:
SELECT COUNT(1) FROM emp WHERE comm IS NOT NULL AND mgr IS NOT NULL;
#9.查询所有雇员月薪和 sum
SELECT SUM(sal) FROM emp;
#10.查询所有雇员月薪和,以及所有雇员奖金和
SELECT SUM(sal) 月薪和, SUM(comm) 奖金和 FROM emp;
#11.查询所有雇员月薪+奖金和
SELECT SUM(sal + IFNULL(comm,0)) "月薪+奖金和" FROM emp;
## sum() avg() 列是数值列
#12.统计所有员工平均工资
SELECT AVG(sal) FROM emp;
SELECT SUM(sal) / COUNT(1) FROM emp;
#13.查询最高工资和最低工资:
# max() min()
SELECT MAX(sal) 最高工资, MIN(sal) 最低工资 FROM emp;
9.分组查询
## group by 列名1,列名2,...
## 使用group by , select 能出现的 分组中出现列,聚合函数
## 聚合函数使用分组,表示对分组之后的数据的统计
## 聚合函数 没有使用分组,对整张表的统计
SELECT deptno,COUNT(1) FROM emp GROUP BY deptno;
#14.查询每个部门的部门编号和每个部门的工资和
SELECT deptno,SUM(sal) 工资和 FROM emp GROUP BY deptno;
#15.查询每个部门的部门编号以及每个部门的人数:
SELECT deptno,COUNT(1) 人数 FROM emp GROUP BY deptno;
10.where 、 having
## where : 筛选, 对整张表的筛选
## having: 筛选, 对分组之后的筛选
/*
where 与having的区别:
1. where对整张表的筛选,having对分组之后的筛选
2. where 位于group by前面, havig位于group by 后面
3. where 独立存在, having 必须跟着group by
4. where 条件使用到表中所有列, 不能使用聚合函数
having条件使用到分组出现列, 聚合函数, 表中其他列不能使用
*/
#16.查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT
deptno,
COUNT(1) 人数
FROM
emp
GROUP BY deptno
HAVING sal > 1500; #错误
SELECT
deptno,
COUNT(1) 人数
FROM
emp
WHERE sal > 1500
GROUP BY deptno ;
SELECT * FROM emp WHERE sal > 1500
#17.查询工资总和大于9000的部门编号以及工资和
SELECT
deptno,
SUM(sal)
FROM
emp
WHERE SUM(sal) > 9000
GROUP BY deptno ; #错误
SELECT
deptno,
SUM(sal)
FROM
emp
GROUP BY deptno
HAVING SUM(sal) > 9000; #正确
11.分页查询
## 前端页面传递数据:
## 查询第几页: 页码
## 一页显示多少条记录: 页容量
##分页的SQL: limit (mysql) oracle: rownum
## limit 语法: limit [x,] y
## x: 开始序号: 从0开始, 如果x=0, 可以省略不写
## y: 往后查询几条数据
SELECT COUNT(1) FROM emp;
## 总记录数: 14
## 每一页显示5条数据: 页容量
## 总页数(整数) = 总记录数 % 页容量 ==0?总记录数/页容量:(总记录数/页容量[整除])+1
SELECT * FROM emp;
## 页容量:5 页码: 1
#查询第一页: limit x , y
SELECT * FROM emp LIMIT 0,5;
SELECT * FROM emp LIMIT 5;
#查询第二页: limit x , y
SELECT * FROM emp LIMIT 5,5;
#查询第三页: limit x , y
SELECT * FROM emp LIMIT 10,5;
## x = (页码-1)*页容量
## y = 页容量