mysql基础_基础查询、条件查询、排序、函数、分组查询
一、 基础查询
1. 基础查询
格式: SELECT (查询列表) FROM (表名)
- 查询列表可以是:表中的字段、常量信息、表达式、函数;
- 查询的结果是一张虚拟的表,并不会创建其他表;
- 查询字段 可以用 着重号(`)引起来,防止表中的字段是 mysql的关键字或者保留字。
例:
- SELECT * FROM employee;(使用*号查询表中所有字段,但可读性不高)
- SELECT (字段列表) FROM employee;
- SELECT 100; / SELECT ‘string’ ;(查询常量)
- SELECT 100%98 ; (查询表达式)
- SELECT VERSION(); (查询函数,VERSION()查看mysql版本函数)
2. 起别名
- 便于理解
- 如果要查询的字段有重名的情况,可以使用别名来区分开来
方式一:使用AS
- SELECT 100%98 AS 结果;
- SELECT last_name AS 姓 ,first_name AS 名 FROM employees;
方式二:使用空格
- SELECT last_name 姓 ,first_name 名 FROM employees;
例:
SELECT salary AS “out put” FROM employees; (别名有特殊字符使用单引号或者双引号引起来)
3. 去重( DISTINCT )
SELECT DISTINCT department_id
FROM employees;
(使用DISTINCT去重)
4. + 号运算符
- java中的+号
- 运算符,两个操作数都为数值型
- 连接符,有一个操作数为字符串
- mysql中的+号
仅仅只有一个功能,运算符- 如果两个数都为操作数,则做加法运算。SELECT 100+90; result: 190
- 只要期中一方为字符型,试题将字符型转换位数值型
○ 如果转换成功,则做加法运算; SELECT “100”+90; result: 190
○ 如果转换失败,则将字符串型数值转换为 0 SELECT “join”+90; result: 90 - 如果一方为 null,那结果为 null; SELECT null+90; result: null
5. 查询结果连接 ( CONCAT )
SELECT CONCAT(e.last_name,e.first_name) “name”
FROM employees e;
6. 显示表结构(DESC)
DESC employees;
二、 条件查询
语法:
SELECT 查询列表
FROM 表名
WHERE 筛选条件 ;
分类:
- 按条件表达式筛选
- 大于 >
- 小于 <
- 等于 =
- 不等于 != <>
- 大于等于、小于等于 >= <=
- 按逻辑表达式筛选
- 与 && and
- 或 || or
- 非 ! not
- 模糊查询
- 匹配 like
- 之间 between and
- 在集合中 in
- 为空 is null
1. LIKE
例:
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_a%b';
统配符 :
- % : 任意多个字符
- _ : 一个字符
转义符:
- \ : 默认转移符
- 使用 ESCAPE 关键字自定义转移符:
SELECT last_name
FROM employees
WHERE last_name LIKE ‘$_’ ESCAPE ‘$’; # 指定$为转义字符
2. BETWEEN AND
- 两个数之间,提高代码可读性
- 包含临界值
- 例: SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
3. IN
- 用于判断某字段的值属于IN列表中的某一项
- IN列表的值类型必须一致或者兼容
- IN列表中的数据不支持通配符
- 例:SELECT * FROM employees WHERE job_id IN (‘IT_PROT’ ,‘AD_VP’);
4. IS NULL
- IS NULL
- IS NOT NULL
- 例: SELECT * FROM employees WHERE commission_pct IS NULL;
- 不可以使用 commission_pct = null
5. 安全等于 <=>
- 可以判断null值。
- 也可以判断普通数值。
- 可读性差。
三、排序查询
- 语法:
SELECT 查询列表
FROM 表
[ WHERE 筛选条件 ]
ORDER BY 排序列表 [ asc | desc ] #asc:升序 desc:降序 默认升序
四、 函数
分类:
- 单行函数 如 concat、length、ifnull等
- 分组函数 功能:做统计使用,又称为统计函数、聚合函数、组函数
单行函数
- 字符函数:
- length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
- concat
- substr
- instr
- trim
- upper
- lower
- lpad
- rpad
- replace
- 数学函数:
- round
- ceil
- floor
- truncate
- mod
- 日期函数:
- now
- curdate
- curtime
- year
- month
- monthname
- day
- hour
- minute
- second
- str_to_date
- date_format
- 其他函数:
- version
- database
- user
- 控制函数:
- if
- case
字符函数
1. length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');
查看客户端使用的字符集:
SHOW VARIABLES LIKE ‘%char%’
2. concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
#示例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
4.substr、substring
注意:索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元', 7 ) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元' , 1 , 3 ) out_put;
#案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees;
5.instr 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
6.trim 去除前后空格 或 字符串
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
SELECT TRIM('aa' FROM 'aaa张aaaaaaaaaaaa翠山aaaaaaaaaaaa') AS out_put;
7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
9.replace 替换
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
###数学函数
1. round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2); #保留2位
2. ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02); # -1
3. floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99); # 10
4. truncate 截断
SELECT TRUNCATE(1.69999,1); #小数点后保留1位
5. mod取余
SELECT MOD(10,-3);
SELECT 10%3;
日期函数
1. now 返回当前系统日期+时间
SELECT NOW();
2.curdate 返回当前系统日期,不包含时间
SELECT CURDATE();
3. curtime 返回当前时间,不包含日期
SELECT CURTIME();
4. 获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
5. str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
序号 | 格式符 | 功能 |
---|---|---|
1 | %Y | 四位的年份 |
2 | %y | 两位的年份 |
3 | %m | 月份(01,02,03,04…12) |
4 | %c | 月份(1,2,3,4…12) |
5 | %H | 小时(24小时制) |
6 | %h | 小时(12小时制) |
7 | %i | 分钟(00,01…59) |
8 | %s | 秒(00,01,59) |
查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = ‘1992-4-3’;
SELECT * FROM employees WHERE hiredate = STR_TO_DATE(‘4-3 1992’,’%c-%d %Y’);
6. date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y年’) 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
7. DATEDIFF 日期相差天数
SELECT DATEDIFF ('2019-4-30','2019-2-8');
其他函数
SELECT VERSION(); 查看当前版本号
SELECT DATABASE(); 查看当前数据库
SELECT USER(); 查看当前用户
流程控制函数
1.if函数: if else 的效果
SELECT IF(10<5,'大','小');
2. case函数的使用一: switch case 的效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
3. case 函数的使用二:类似于 多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
分组函数
- 功能:用作统计使用,又称为聚合函数或统计函数或组函数
- 分类:sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
- 特点:
- sum、avg一般用于处理数值型
max、min、count可以处理任何类型 - 以上分组函数都忽略null值
- 可以和distinct搭配实现去重的运算
- count函数一般使用count(*)用作统计行数
- 和分组函数一同查询的字段要求是group by后的字段
- sum、avg一般用于处理数值型
#1、简单 的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
#2、和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
五、分组查询 GROUP BY
语法:
SELECT 分组函数,列(要求是GROUP BY 中的列)
FROM 表
[ WHERE 筛选条件 ]
GROUP BY 分组列表
[ ORDER BY 子句 ]
特点:
- 和分组函数一同查询的字段必须是group by后出现的字段
- 筛选分为两类:分组前筛选和分组后筛选
针对的表 | 位置 | 连接的关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by前 | where |
分组后筛选 | group by后的结果集 | group by后 | having |
1. 对分组后的结果进行筛选 HAVING
案例:查询哪个部门的员工个数>5
# 1查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
# 2筛选刚才1结果
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
2. 按表达式或函数分组
案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
SELECT count(*)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) >5;
3. 按多个字段分组
案例:查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;
4. 添加排序
案例:每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;