一、DDL查询表中数据
1. 简单查询
查询不会对数据库中的数据进行修改, 只是一种显示数据的方式。
-- 语法格式： select 字段名1,字段名2,... from 表名;
例：查询所有的员工信息, 将字段名显示为中文
select
eid as '编号', -- as可以省略
ename as '姓名',
sex as '性别',
salary as '薪资',
hire_date as '入职日期',
dept_name as '部门名称'
from emp;
查询有多少个部门（去重操作）
-- 使用去重查询 关键字 distinct select distinct dept_name from emp;
2. 条件查询
-
在实际应用中, 通常会指定查询的条件, 对记录进行过滤
-- 语法格式： select 列名 from 表名 where 条件;
2.1 比较运算符
| 运算符 | 说明 |
|---|---|
| > < <= >= = <> != | 大于 小于 小于等于 大于等于 等于 不等于 |
| between...and... | 显示在某一区间的值 |
| in(集合) | 集合表示多个值，使用逗号分隔，例如name in (悟空,八戒); in中的每个数据都会作为一次条件，只要满足条件就会显示 |
| like '%张%' | 模糊查询 |
| is null | 查询某一列为null的值，注意，不能写 = null |
2.2 逻辑运算符
| 运算符 | 说明 |
|---|---|
| and && | 多个条件同时成立 |
| or || | 多个条件任一成立 |
| not | 取反 |
通配符
| 通配符 | 说明 |
|---|---|
| % | 表示匹配任意多个字符 |
| _ | 表示匹配任意一个字符 |
3. 排序
关键字：order by
通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影响真实的数据）。
-- 语法格式： select 字段名 from 表名 [where 条件] order by 字段名[asc | desc]; -- asc 升序(默认) -- desc 降序
3.1 单列排序
只按照某一个字段进行排序。
3.2 组合排序
同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推。
例：
查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序。
-- 组合排序 select * from emp order by salary, hire_date desc;
4. 函数
函数只是对查询结果中的数据进行处理，不会改变数据的值。
单行函数:
单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。
常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。
使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。
多行函数:
我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。
-
多行函数会忽略null空值。
-
多行函数也称为分组函数, 聚合函数。
使用多行函数，是对某一列的值进行计算，然后返回一个单一的值
4.1 单行函数
4.1.1 字符函数
大小写处理函数
| 函数 | 描述 | 实例 |
|---|---|---|
| LOWER(s)|LCASE(s) | 将字符串 s 转换为小写 | 将字符串 OLDLU转换为小写：SELECT LOWER("OLDLU"); -- oldlu |
| UPPER(s)|UCASE(s) | 将字符串s转换为大写 | 将字符串 oldlu转换为大写：SELECT UPPER("oldlu"); -- OLDLU |
字符处理函数
| 函数 | 描述 | 实例 |
|---|---|---|
| LENGTH(s) | 返回字符串 s 的长度 | 返回字符串oldlu的字符数SELECT LENGTH("oldlu"); -- 5; |
| CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | 合并多个字符串SELECT CONCAT("sxt ", "teacher ", "oldlu"); -- sxt teacher oldlu; |
| LPAD(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len | 将字符串 x 填充到 oldlu字符串的开始处：SELECT LPAD('oldlu',8,'x'); -- xxxoldlu |
| LTRIM(s) | 去掉字符串 s 开始处的空格 | 去掉字符串 oldlu开始处的空格：SELECT LTRIM(" oldlu") ;-- oldlu |
| REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | 将字符串 oldlu 中的字符 o 替换为字符 O：SELECT REPLACE('oldlu','o','O'); -- Oldlu |
| REVERSE(s) | 将字符串s的顺序反过来 | 将字符串 abc 的顺序反过来：SELECT REVERSE('abc'); -- cba |
| RPAD(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len | 将字符串 xx填充到 oldlu字符串的结尾处：SELECT RPAD('oldlu',8,'x'); -- oldluxxx |
| RTRIM(s) | 去掉字符串 s 结尾处的空格 | 去掉字符串 oldlu 的末尾空格：SELECT RTRIM("oldlu "); -- oldlu |
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTR("OLDLU", 2, 3); -- LDL |
| SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("OLDLU", 2, 3); --LDL |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串 oldlu 的首尾空格：SELECT TRIM(' oldlu ');-- oldlu |
#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat('编号:', eid), concat('姓名:', ename), concat('性别:', sex) from emp;
#2 查询emp表所有数据, 将ename第二个字符都换为 某
select eid, insert(ename, 2, 1, 某'), sex from emp;
#3 查询emp表所有数据, 显示ename的长度
select eid, ename, length(ename), sex from emp;
#4 查询emp表所有数据, 将 ename有英文的改为都是大写
select eid, ename, upper(ename), sex from emp;
#5 查询emp表所有数据, 将 ename有英文的改为都是小写
select eid, ename, lower(ename), sex from emp;
#6 查询emp表所有数据, ename只显示姓
select eid, ename, substring(ename, 1, 1), sex from emp;
4.1.2 数值函数
| 函数 | 描述 | 实例 |
|---|---|---|
| ABS(num) | 返回num的绝对值 | SELECT ABS(-1) -- 返回1 |
| CEIL(num) | 返回大于num的最小整数（向上取整） | SELECT CEIL(1.5) -- 返回2 |
| FLOOR(num) | 返回小于num的最大整数（向下取整） | SELECT FLOOR(1.5) -- 返回1 |
| MOD(num1, num2) | 返回num1/num2的余数（取模） | SELECT MOD(5,2) -- 1 |
| PI() | 返回圆周率的值 | SELECT MOD(5,2) -- 1 |
| POW(num,n)/POWER(num, n) | 返回num的n次方 | SELECT POW(2,3) -- 8 |
| RAND(num) | 返回0~1之间的随机数 | SELECT RAND() --0.93099315644334 |
| ROUND(num, n) | 返回x四舍五入后的值，该值保留到小数点后n位 | SELECT ROUND(1.23456,3) --1.235 |
| TRUNCATE(num, n) | 返回num被舍去至小数点后n位的值（与 ROUND 最大的区别是不会进行四舍五入） | SELECT TRUNCATE(1.23456,3) -- 1.234 |
MySQL提供了一张虚拟表，该表名为“dual”，是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。
在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表。
select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25) from dual; select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25);
4.1.3 日期函数
| 函数 | 描述 | 实例 |
|---|---|---|
| CURDATE() | 返回当前日期 | SELECT CURDATE(); -- 2022-07-20 |
| CURTIME() | 返回当前时间 | SELECT CURTIME(); -> 19:59:02 |
| NOW() | 返回当前日期和时间 | SELECT NOW() -> 2022-07-20 20:57:43 |
| SYSDATE() | 返回该函数执行时的日期和时间 | SELECT SYSDATE() -> 2022-07-20 20:57:43 |
| DAYOFYEAR(date) | 返回日期date为一年中的第几天 | SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315 |
| WEEK(date)/WEEKOFYEAR(date) | 返回日期date为一年中的第几周 | SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6 |
| DATEDIFF(date1, date2) | 返回起始日期date1与结束日期date2之间的间隔天数 | SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32 |
| DATE() | 从日期或日期时间表达式中提取日期值 | SELECT DATE("2017-06-15"); -> 2017-06-15 |
| DAY(d) | 返回日期值 d 的日期部分 | SELECT DAY("2017-06-15"); -> 15 |
| MONTH(d) | 返回日期d中的月份值，1 到 12 | SELECT MONTH('2011-11-11 11:11:11') ->11 |
| YEAR(d) | 返回年份 | SELECT YEAR("2017-06-15"); -> 2017 |
4.1.4 流程控制函数
| 间隔类型 | 描述 |
|---|---|
| IF(condition, t, f) | 如果条件condition为真，则返回t，否则返回f |
| IFNULL(value1, value2) | 如果value1不为null，则返回value1，否则返回value2 |
| NULLIF(value1, value2) | 如果value1等于value2，则返回null，否则返回value1 |
| CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 ...] [ELSE result] END | 如果条件condition1为真，则返回result1，···，否则返回result |
#1 查询emp表所有数据, 薪资 >= 10000 高工资 其他 低工资
select eid, ename, salary, if(salary >= 10000, '高工资', '低工资') from emp;
#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, salary, salary * 12+30000 '年薪' from emp; -- 需要考虑null
select eid, ename, salary, ifnull(salary, 0) * 12+30000 '年薪' from emp;
#3 查询emp表所有数据, 薪资 >=3000 加把劲 >=5000 加油哦 >=9000 坚持住 >= 15000 优秀 其他 不及格
select eid, ename, salary,
case
when salary >= 15000 then '优秀'
when salary >= 9000 then '坚持住'
when salary >= 5000 then '加油哦'
when salary >= 3000 then '加把劲'
else '努力奋斗吧骚年'
end
from emp;
4.1.5 其他函数
| 函数 | 描述 |
|---|---|
| DATABASE() | 返回当前数据库名 |
| VERSION() | 返回当前MySQL的版本号 |
| USER() | 返回当前登录的用户名 |
| INET_ATON(IP) | 返回IP地址的数字表示 |
| INET_NTOA | 返回数字代表的IP地址 |
| PASSWORD(str) | 实现对字符串str的加密操作 |
| FORMAT(num, n) | 实现对数字num的格式化操作，保留n位小数 |
| CONVERT(data, type) | 实现将数据data转换成type类型的操作 |
4.2 多行函数
-
多行函数会忽略null空值。
-
多行函数也称为分组函数, 聚合函数。
| 多行函数 | 作用 |
|---|---|
| count(字段) | 统计指定列不为null的记录行数 |
| sum(字段) | 计算指定列的数值和 |
| max(字段) | 计算指定列的最大值 |
| min(字段) | 计算指定列的最小值 |
| avg(字段) | 计算指定列的平均值 |
4.3 经典面试题
需求：
把下面数据转换
转换成下面的效果
方式一：使用if(表达式，值1，值2)函数。如果表达式成立返回值1，否则返回值2。其中值可以是列。
select
max(if(subject='语文',score,0)) 语文,
max(if(subject='数学',score,0)) 数学,
max(if(subject='英语',score,0)) 英语,
name
from student group by name;
方式二：使用case 条件 when 条件取值 then 结果 ... else 结果 end
select
max(case subject when '语文' then score else 0 end) 语文,
max(case subject when '数学' then score else 0 end) 数学,
max(case subject when '英语' then score else 0 end) 英语,
name
from student group by name;
5. 分组
关键字：group by
分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组。
select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件];
分组的过程
注意事项:
-
分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作
-
查询其他字段没有意义
若分组时需要进行条件过滤
可以通过where和having（where效率更高，但不是通用）
select语句的执行顺序 from -- where -- group by -- having –- select -- order by
| 过滤方式 | 特点 |
|---|---|
| where | 分组之前的过滤，后边不能写多行函数 |
| having | 分组之后的过滤，后边可以写多行函数 |
6. limit
作用:
-
limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)
-
limit 语法是 MySql的方言, 用来完成分页
select 字段1, 字段2 ... from 表名 limit offset, length;
参数说明
-
offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据
-
length 返回的行数
二、SQL执行流程
组件介绍
-
连接管理与安全验证：MySQL有连接池（Connection Pool）管理客户端的连接。客户端连接后会验证用户名、密码、主机信息等
-
缓存（Cache&Buffer）：缓存中存储了SQL命令的HASH，直接比对SQL命令的HASH和缓存中key是否对应，如果对应，直接返回结果，不再执行其他操作。由于缓存的是SQL的HASH，所以根据Hash特性SQL中空格等内容必须完全一样。缓存里面包含表缓存、记录缓存、权限缓存等。查询语句执行完成后会把查询结果缓存到缓存中。在MySQL中查询缓存默认不开启。考虑到查询缓存性能瓶颈问题，从MySQL8开始已经不支持查询缓存了。
-
解析器（Parser）主要作用是解析SQL命令。将SQL命令分解成数据结构（解析树），后续的操作都是基于这个结构的。如果在分解过程中遇到错误，出现SQL解析错误。解析时主要检查SQL中关键字，检查关键字是否正确、SQL中关键字顺序是否正确、引号是否对应是否正确等。
-
预处理器：根据解析器的解析树，进一步检查表是否存在、列是否存在、名字和别名是否有歧义等。
-
优化器（Optimizer）：根据官网说明在执行SQL查询时会根据开销自动选择最优查询方案。采用“选择-投影-连接”的策略。先选择where中的行数。同时先选择要选择的列，而不是全部列，最后把内容合并到一起。
-
执行器：包含执行SQL命令。获取返回结果。生成执行计划等。
-
存储引擎：访问物理文件的媒介
详细说明
-
客户端向服务器端发送SQL命令和连接参数
-
服务器端连接模块连接并验证
-
缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。如果是MySQL 8 是没有查询缓存的。
-
解析器解析SQL为解析树，检查关键字相关问题，如果出现错误，报SQL解析错误。如果正确，继续执行
-
预处理器对解析树继续处理检查表、列别名等，处理成功后生成新的解析树。
-
优化器根据开销自动选择最优执行计划，生成执行计划
-
执行器执行执行计划，访问存储引擎接口
-
存储引擎访问物理文件并返回结果
-
如果开启查询缓存，缓存管理器把结果放入到查询缓存中。
-
返回结果给客户端
标签：返回,函数,--,DDL,查询,SQL,字符串,操作,SELECT From： https://www.cnblogs.com/giaogiaoyang/p/17680638.html