目录
经过142天辉仔终于初步认识了数据库,在此将介绍数据库的查询内容。
一、查询格式
1、语法
SELECT 查询列表
FROM 表
WHERE 筛选条件
ORDER BY 排序列表【ASC\DESC】
2、特点
1、ASC:升序,默认升序
2、DESC:降序
3.排序列表 支持单个字段、多个字段、函数、表达式、别名
4.order BY 的位置一般在查询语句的最后(除LIMIT语句之外)
二、常用函数
1、概述
功能:类似于java中的方法
好处:提高重用性和隐藏实现细节
调用:SELECT 函数名(参数列表)
2、单行函数
1、字符函数
CONCAT:连接
SUBSTR:截取子串
UPPER和LOWER:变大写,变小写
REPLACE:替换
LENGTH:长度
TRIM:去前后空格
LPAD和RPAD 左填充和右填充
INSTR:获取子串第一次出现的索引
2、数学函数
CEIL:向上取整
ROUND:四舍五入
MOD:取模
FLOOR:向下取整
TRUNCATE:截断
RAND:获取随机数,返回0~1之间的小数,到不了1
3、日期函数
NOW:返回当前年月份加时间
YEAR:返回年
MONTH:返回月
DAY:返回日
DATE_FORMAT:将日期转换成字符
CURDATE:返回当前日期
STR_TO_DATE:将字符转换成日期
CURTIME:返回当前时间
HOUR:小时
MINUTE :小时
SECOND:秒
MONTHNAME:以英文形式返回月
DATEDIFF:返回两个日期的相差天数
4、其他函数
VERSION:当前数据库服务器版本
DATABASE:当前打开的数据库
SELECT PASSWORD('sfsdf');:随机加密
MD5('gdsg'):返回该字符MD5加密方式
5、流程控制函数
IF(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
CASE 情况1
CASE 变量或表达式或字段
WHEN 常量1 THEN 值1
WHEN 常量2 THEN 值2
。。。
ELSE 值n
END
CASE 情况2
CASE
WHEN 条件1 THEN 值1
WHEN 条件2 THEN 值2
。。。
ELSE 值n
END
3、分组函数
(1)分类
MAX 最大值
MIN 最小值
SUM 和
AVG 平均值
COUNT 计算个数
(2)特点
①语法
SELECT MAX(字段) FROM 表名
②支持的类型
SUM和AVG处理数值型
其他可以处理任何型
③以上分组函数都忽略NULL值
④都可以倒赔DISTINCT(去重)使用,实现去重的统计
SELECT SUM(DISTINCT 字段) FROM 表;
⑤、COUNT函数
COUNT(字段):统计该字段非空值的个数
COUNT(*):统计结果的行数
案例:查询每个部门的员工个数
SELECT department_id,COUNT(*)
FROM emPloyees
GROUP BY department_id;
COUNT(1);统计结果集的个数
效率上:
INNODB存储引擎 1和*一样
⑥和分组函数一同查询的字段,要求是GROUP BY后出现的字段
分组函数
SELECT 分组函数,分组后的字段
FROM 表
【WHERE 筛选条件】
GROUP BY分组的字段
【HAVING 分组后的筛选】
【ORDER BY 排序列表】
where 和 having 的位置
使用关键字 筛选的表 位置
分组前的筛选 WHERE 原始表 GROUP BY的前面
分组后的筛选 HAVING 分组后的结果 GROUP BY 的后面
三、连接查询
1、含义:
当查询中涉及到多个表的字段
SELECT 字段1,字段2.。。。
FROM 表1,表2.。。。
笛卡尔乘积:当查询多个表时。没有添加有效的连接条件,导致多个表所有行实现完全连接
2、分类
按年代分类
sql92:
等值
非等值
自连接
sql99:
内连接:
等值
非等值
自连接
外连接:
左外
右外
全外(mysql)
交叉连接
3、sql92语法
(1)等值连接
语法:
SELECT:查询列表
FROM 表1 别名 表2 别名
WHERE 表1.key=表2.key
【AND 筛选条件】
【GROUP BY分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
特点:
①一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
(2)非等值
语法:
SELECT:查询列表
FROM 表1 别名 表2 别名
WHERE 非等值的条件
【AND 筛选条件】
【GROUP BY分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
(3)自连接
语法:
SELECT:查询列表
FROM 表1 别名 表2 别名
WHERE 非等值的条件
【AND 筛选条件】
【GROUP BY分组字段】
【HAVING 分组后的筛选】
【ORDER BY 排序字段】
4、Sql99语法
(1)内连接
语法:
select 查询列表
from 表1 别名
inner join 表2 别名 on连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
分类:
等值连接
非等值连接
自连接
(2)外连接
语法:
select 查询列表
from 表 1 别名
left|right|full[outer] join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点
①查询的记过=主表中所有的行,其中从表和它匹配的显示匹配行,如果从表没有匹配的显示null
②left join 左边的就是主表,right join 右边主表
fulljoin 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行
(3)交叉连接
语法:
select 查询列表
from 表1 别名
cross join表2 别名
特点:类似于笛卡尔乘积
四、子查询
1.含义:
嵌套在其他语句内部的select语句称为子查询或者内查询
外面的语句可以是insert、update、delete、select等
外面如果为select语句,则此语句称为外查询或者主查询
2.分类
(1)按出现的位置
select 后面
标量子查询
from后面
表子查询
where 或 having后面
标量子查询(单行子查询)
列子查询(多行子查询)
行子查询
exists 后面
都可以
(2)按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果为多行多列
表子查询:结果为多行多列
3.实例
(1)标量子查询
查询最低工资员工姓名和工资
select min(salary) from employees
select last_name,salary
from employees
where salary=(
select min(salary) from employees
);
(2)列子查询
案例:查询所有是领导的员工姓名
查询员工的manager——id
select manager_id
from employees
查询姓名,employee_id属于①列表的一个
select last_name
from employees
where employee_id in (
select manager_id
from employees);
五、分页查询
1.语法:
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
gruop by 分组字段
having 分组后的筛选
order by 排序后的字段】
limit offset,size;
offset 要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数
2.特点
①放在查询语句的最后
②公式
要显示的页数 page,每页的条目数size
select 查询列表
from 表
limit (page-1)*size;
#案例1:查询的前五条员工信息
SELECT*FROM employees LIMIT 0,5;
SELECT*FROM employees LIMIT 5;
#案例2:查询第11条——到第25条
SELECT*FROM employees LIMIT 10,15;
#案例3:有奖金的员工的信息,并且工资较高的前十名显示出来
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;