mysql加强（2）~单表查询、mysql查询常用的函数

2022-03-16 03:47:41

一、单表查询：

■ 语法：

SELECT [DISTINCT] *|字段 [别名] [,字段[别名]]

FROM 表名称 [别名]

[WHERE 条件(s)]

[ORDER BY 字段 [ASC|DESC] [, 字段 [ASC|DESC], ...]]

★ 注意：mysql报错信息，一般关键字报错，它就没有提示，但是表名、字段名出错，它会提示不存在。

★ 注意：总结一下DQL查询语句的执行顺序：【哪一张表 `from`，过滤不合法数据 `where`，显示 `select`，排序 `order by`，分页 `limit`】

1、全列/投影查询：

■ 全列查询--查询表中所有信息，投影查询-查询某些列

2、消除重复： select 后加上关键字 `distinct`

3、算术操作符(加减乘除)、常用的查询(比较、范围between...and、逻辑not and or、集合in、模糊like）:

(1) 算术操作符(加减乘除):

★ 注意数值与null直接加减乘除会变成null

解决：使用函数 IFNULL(expr1, expr2) 当expr1 不为null，则返回值为expr1，否则返回值为expr2.
至于IFNULL函数的返回值是字符串还是数字根据具体语境决定。

#注意数值与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的条件`]：

#查询职位是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`

（2）日期值格式敏感：

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。

✿ 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');
```

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);

datediff 日期差函数【单位天数】：

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');

✿（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时间格式如下表：

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');

码农公寓