mysql-1-DQL查询
使用
| 关键字 | 说明 |
|---|---|
| as 或 省略as 空格后加别名 | 取别名 |
| distinct | 去重 |
| + | 只做数值加，有字符先尝试转换成数值再加，加null都等于null |
| in | 子查询 |
| group by 分组字段 | 分组 |
| order by 排序字段 【asc/desc】 | 排序 asc=升序(默认) desc=倒序 |
| having 条件 | 查询后的条件筛选，一般和group by 一起使用 |
where 后面要跟的是数据表里的字段，而having只是根据前面查询出来的是什么后面就可以接什么。
条件查询
一般在where后使用：
| 条件 | 运算符 |
|---|---|
| 条件运算符 | < 、=、 != 、<>【表示不等于】、>= 、<= |
| 逻辑运算符 | && 、|| 、! 、and 、or、not |
| 模糊查询 | like 、between and、in 、is null或is not null |
| 安全等于 | <=> 即可判断null值也可以判断普通数值 |
常见函数
函数可以嵌套使用，一个函数的结果可以作为另一个函数的参数
单行函数
字符函数：
| 方法 | 说明 |
|---|---|
| LENGTH(字段) | 字段值字节长度 |
| CONCAT(xx,xx,xx) | 拼接，用逗号分隔 |
| ISNULL(判断字段，空时取值) | 判断是否为空 |
| UPPER('xxx') | 变大写 |
| LOWER('xxx') | 变小写 |
| SUBSTR('xxx',索引) | 截取索引及其后面的字符，myslq索引从1开始 |
| SUBSTR('xxx',索引,索引2) | 截取索引1到索引2的字符 |
| INSTR('xxx','子串') | 返回子串第一次出现的索引 |
| TRIM('xxx') | 去前后空格 |
| TRIM('指定字符','xxx') | 去前后指定字符 |
| LPAD('xxx',number,'指定字符') | 左填充，用指定字符填充字符串到指定长度 |
| RPAD('xxx',number,'指定字符') | 右填充，用指定字符填充字符串到指定长度 |
| REPLACE('字符串','被替换值','替换值') | 替换 |
数学函数：
| 方法 | 说明 |
|---|---|
| ROUDN(number) | 四舍五入 |
| ROUDN(number,保留精度位数) | 四舍五入 |
| CEIL(number) | 向上取整 |
| FLOOR(number) | 向下取整 |
| TRUNCATE(number,保留位数) | 截断， |
| MOD(num1,num2) | 取余 |
日期函数：
| NOW() | 返回当前日期+时间 |
| CURADTE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| YEAR(日期 或 '年-月-日') | 返回日期中的年份 |
| MONTH(日期 或 '年-月-日') | 返回日期中的月份 |
| MONTHNAME(日期 或 '年-月-日') | 返回日期中的月份(英文) |
| 日、时、秒、分...... | |
| str_to_date()： | 将日期格式的字符转换成指定格式的日期 如：STR_TO_DATE('9-13-1999' , %m-%d-%Y') 1999-09-13 |
| date_format()： | 将日期转换成字符 如：DATE_FORMAT('2018/6/6 ,%Y年%m月%d日') 2018年06月06日 |
其他函数：
| 方法 | 说明 |
|---|---|
| VERSION() | 版本 |
| DATABASE() | 当前数据库 |
| USER() | 当前用户 |
流程控制函数：
IF('表达式',成立返回结果,不成立返回结果) 判断
CASE
case 要新的字段或表达式
when 常量1 then 要显示的值1或语句1(这里如果是值就不用分号);
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
分组函数
统计使用，又称聚合函数或统计函数或组函数
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
count() 统计个数
sum、avg不支持字符串类型
以上分组函数都忽略null值
可以和distinct搭配 如：SUM(DISTINCT xxx)
和分组函数一同查询的字段要求是group by 后的字段
效率：
MYISAM存储引擎下，COUNT(*)的效率高
INNODB存储引擎下，COUNT(* )和COUNT(1)的效率差不多，比COUNT(字段)要高一些
连接查询
完全连接 如：select * from table1，table2； 笛卡尔积
分类：
按年代：
sq192标准：仅仅支持内连接
sq199标准(推荐)：支持内、外(左外和右外)、交叉
按功能：
-
内连接：inner
- 等值连接： 多表查询通过某字段相等作为条件
# 例1 SELECT e.last_name,e.job_id,j.job_title FROM employees e,jobs j WHERE e.`job_id` = j.`job_id`; # 例2 SELECT e.last_name,e.job_id,j.job_title FROM employees e INNER JOIN jobs j ON e.`job_id` = j.`job_id`; 可接多个INNER JOIN ... ON- 非等值连接：
# 例1 SELECT e.last_name,e.job_id,j.job_title FROM employees e,jobs j WHERE e.`job_id` 等号外其余条件判断 j.`job_id`;- 自连接：
#案例:查询员工名和上级的名称 SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e, employees m WHERE e.'manager_id' =m.'employee_id`; -
外连接：outer
# 语法 select 查询列表 from 表1 别名 [连接类型] join 表2 别名 on 连接条件 [where筛选条件] 外连接查询结果=内连接结果+主表中有而从表没有的记录 没匹配的显示null- 左外连接：left [outer]
左边是主表 左表全显示 # 例： SELECT b.name, bo.* FROM beauty b LEFT OUTER JOIN boys bo ON b.'boyfriend_id' = bo.'id';- 右外连接：right [outer]
右边是主表 右表全显示 ＃ 例：和上面左外效果一样 SELECT b.name, bo.* FROM boys bo RIGHT OUTER JOIN beauty b ON b.'boyfriend_id' = bo.'id';- 全外连接：full [outer]
左表全显示 右表全显示 即：左右表字段全显示，两表重合，该null的位置就null -
交叉连接：cross join
笛卡尔乘积
可记：from后为左表，join后为右边
子查询
| 操作符 | 含义 |
|---|---|
| IN、NOT IN | 等于列表中的任意一个 |
| ANY、SOME | 和子查询返回的某一个值比较 |
| ALL | 和子查询返回的所有值比较 |
- 行子查询
# 例:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE(empioyee_id,salary) = (
SELECT MIN(employee_id), MAX(salary)
FROM employees
)
- select后子查询
# 例:查询每个都门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHEREe.department_id = d.'department_id'
）个数
FROM departments d;
select后面子查询只能返回一行一列
- from后子查询
SELECT *
FROM(
SELECT AVG(salary) avgsalary,dep_id
FROM dep
GROUP BY dep_id
) avg
WHERE avg.avgsalary > 5000;
- exists后子查询
SELECT EXTSTS(
SELECT employee_id
FRoM employees
WHERE salary=300000
);
分页查询
LIMIT 开始索引,显示条数
page：第几页
size：每页显示几条
分页公式：(page-1) * size
- 查询关键字及执行先后顺序说明
执行顺序 关键字
7--select 查询列表
1--from 表
2--[连接类型] join 表2
3--on 连接亲件
4--whee 筛选条件
5--group by 分组列表
6--having 分组的筛选
8--order by 排序列表
9--1imit 偏移,条自数
每一条字句都会生成一个虚拟表格
联合查询
union 联合，合并，将多条查询语句结果合并成一个结果
# 例:查询中国用户中男性的信息以及外国用户中男性的用户信息
SELECT id,cnam,csex FROM t_ca WHERE csex='男';
UNION
SELECT t_idy tName,tGender FROM t_ua WHERE tGender='male';
结果：
id cname csex
2 小李 男
1 john male
3 jack male
联合查询中多条查询列数需要一致
多条查询语句查询的每列类型和顺序最好一致
联合查询默认去重，要显示所有用：union all
