mysql加强(2)~单表查询、mysql查询常用的函数
欢迎大家来一乐乐的博客园
一、单表查询:
■ 语法:
SELECT [DISTINCT] *|字段 [别名] [,字段[别名]]
FROM 表名称 [别名]
[WHERE 条件(s)]
[ORDER BY 字段 [ASC|DESC] [, 字段 [ASC|DESC], ...]]
★ 注意:mysql报错信息,一般关键字报错,它就没有提示,但是表名、字段名出错,它会提示不存在。
★ 注意:总结一下DQL查询语句的执行顺序:【哪一张表 from
,过滤不合法数据 where
,显示 select
,排序 order by
,分页 limit
】
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093248586-174509030.png)
1、全列/投影查询:
■ 全列查询--查询表中所有信息, 投影查询-查询某些列
2、消除重复: select 后加上关键字 distinct
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093318354-973340556.png)
3、算术操作符(加减乘除)、常用的查询(比较、范围between...and、逻辑not and or、集合in、模糊like):
(1) 算术操作符(加减乘除):
★ 注意数值与null直接加减乘除会变成null
-
解决:使用函数
IFNULL(expr1, expr2)
当expr1 不为null, 则返回值为expr1, 否则返回值为expr2. -
至于IFNULL函数的返回值是字符串还是数字根据具体语境决定。
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093342828-1916405606.png)
#注意数值与null直接加减乘除会变成null---使用IFNULL函数解决,当COMM为空时,返回0
SELECT ENAME, SAL, COMM, (IFNULL(COMM, 0) + SAL) 'year_income' FROM emp;
■ 空值(不可用、未分配的值,即没有值):
- 空值不等于0,也不等于空格
- 任意类型度都支持空值,即任意类型的字段都可以允许有空值作为值的存在。
- 注意:
包含空值的任何算术表达式都等于空
。
(2) 常用的查询(比较、范围between...and、逻辑not and or、集合in、模糊like查询):
-
比较运算符 = != <> > < >= <=
-
范围查询 between ... and (包含开头和结尾) not between ... and
-
逻辑查询 not and or (注意优先级:not > and > or )
-
集合查询 in 列出匹配的值,在元素集合内【
in 还常用来批量删除
】 not in -
模糊查询 like 匹配字符串模式,
必须搭配通配符号一起使用
【%:任意字符, _:占一个字符位置】 -
空值查询 is null 是否为空 is not null
#============between ... and (包含开头和结尾) not between ... and 的查询情况===============
#查询1981年之后入职的员工信息
select * from emp where hiredate >= '1982-01-01';
#查询工资不在2000-3000之间的员工信息
select * from emp where sal not between 2000 and 3000;
#=============in 列出匹配的值,在元素集合内【in 还常用来批量删除】的查询情况==============
#查询员工工资为800或1600或3000
select * from emp where sal = 800 or sal = 1600 or sal = 3000;
select * from emp where sal in (800, 1600, 3000); #in 在元素集合内
select * from emp where sal not in (800, 1600, 3000); #not in 在元素集合内
# in 还常用来批量删除
delete from ** from where id in (1, 2, 3, 4, 5, 6);
#==============like搭配通配符% | _ 使用的查询情况==============
#查询出所有员工名字以A开头的所有员工信息
select ename from emp where ename like 'A%';
#查询出所有员工名字第二个字母是M的所有员工信息
select ename from emp where ename like '_M%';
#查询出所有员工名字任意位置上包含字母A的所有员工信息
select ename from emp where ename like '%A%';
■ 优先级 and 大于 or的例子[查询条件有or同时有and的要注意,or是不是要括号括起来,避免and优先级过高,抢了or的条件
]:
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093410709-1245522795.png)
#查询职位是clerk或是salesman的全部信息,并且要求工资在1000以上
select * from emp where (job = 'clerk' or job = 'salesman') and sal > 1000;
4、空值判断 is null | is not null
■ 注意不是用的等号,而是关键字 is
SELECT `ENAME` FROM emp WHERE COMM IS NULL; #SELECT `ENAME` FROM emp WHERE COMM IS NOT NULL;
5、限定查询(条件查询)where:
■ 注意:
-
字符串和日期要用单引号括起来
。 -
数字类型的直接书写
-
字符串大小写不敏感,字符串想要大小写敏感--加关键字
binary
-
日期值格式敏感
(1)字符串想要大小写敏感--加关键字 binary
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093437673-1797043347.png)
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093457534-612033637.png)
(2)日期值格式敏感:
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093518678-1586777521.png)
6、结果排序 order by:
■ 执行顺序在select 之后
-
asc 升序 desc 降序
-
order by 可以使用别名
#查询所有员工信息,按照部门序号升序,年薪降序 select deptno, (sal * 12) year_income from emp order by deptno asc, year_income desc;
★ 注意:总结一下DQL查询语句的执行顺序:【哪一张表 from
,过滤不合法数据 where
,显示出来 select
,进行排序 order by
,分页 limit
】
二、mysql查询常用的函数
1、多行函数(统计函数
/聚集函数):sum()、avg()、min()、max()
- count()函数,在计算时,空值不计入。
■ 注意:分组函数在计算时省略列中的空值,不能在where语句中使用分组函数。
2、单行函数(字符函数、数字函数、日期函数、转换函数)
(1)字符函数:【重点是concat()
拼接字符串函数】
lower/upper、`concat`、length、char_length、lpad/rpad(取到固定字符串,不够补满)、trim/ltrim、
❀ concat:返回结果为连接参数产生的字符串
。若任何一个参数是null,则返回结果为null。
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093559272-669465663.png)
-
✿ concat 常用于拼接字符串, keyword = abc, where name like? ?: concat('%', keyword, '%'); --> where name like '%abc%';
select concat('my', 'name', 'is', 'kangkang'); select concat(ename, '的工作是', job) from emp;
-
replace(str, from_str, to_str) 将字符串str中的子字符串to_str替换成from_str.
select replace('www.mysql.com', 'w', 'hi');
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093627726-463833673.png)
-
substring(str, pos) 从哪个位置开始截取子字符串 substring(str, pos,len) 从哪个位置开始截取多长的子字符串
select substring('www.baidu.com', 4); select substring('www.baidu.com', 4, 3);
(2)数字函数:
abs 绝对值函数、mod 取模、ceil、floor、round、truncate 舍去小数点后几位
✿(3)日期函数:
① now、adddate/date_add
日期加法函数、subdate/date_sub 日期减法函数、current_date/current_time 当前系统日期/时间、
datediff
日期天数差
② 获取日期时间中的某个段:day、hour、minute、month、year、last_day
■ 例如:select day(日期); select day(now()); select sname from t_stu where year(birthday) = '2000';
③ unix_timestamp 时间撮(将日期转成秒的形式) from_unixtime 将时间撮转成日期形式
-
date_add方法 日期加法函数 date_add(date, interval expr type):
select date_add('1981-01-22', interval 10 day); select date_add('1981-01-22', interval 10 month); select date_add('1981-01-22', interval 10 year);
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093652222-23886397.png)
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093717663-2105586101.png)
- datediff 日期差函数【单位天数】:
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093740292-2069615928.png)
- unix_timestamp 时间撮(将日期转成秒的形式) from_unixtime 将时间撮转成日期形式
select unix_timestamp(now()), from_unixtime(1643127519, '%y-%m-%d %h:%m:%s'), from_unixtime(1643127519, '%Y-%M-%D %H:%M:%S');
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093803543-1022607779.png)
✿(4)转换函数:
① 数字和字符串 format函数:将数字转成字符串
format(x,d) x是数字,d是要保留的小数位(四舍五入)
② ✿ 日期和字符串 date_format 和 str_to_date
-
date_format(date, format)
日期转成字符串:select date_format(now(), '%Y-%m-%d');
■ 注意: 格式 'yyyy-MM-dd HH:mm:ss' 是java的时间格式,mysql时间格式如下表:
![](https://img2022.cnblogs.com/blog/2282448/202201/2282448-20220126093825380-82612099.png)
-
str_to_date(str, format)
将字符串转成日期函数# 注意细节:字符串的时间使用的连接符与format格式连接符要一致 select str_to_date('2022/01/25', '%Y/%m/%d'), str_to_date('2022-01-25', '%Y-%m-%d');
● 转载请注明出处
本文来自博客园,作者:一乐乐,转载请注明原文链接:https://www.cnblogs.com/shan333/p/15845227.html