Mysql 学习随笔(1) 函数

一、基础

数据库相关概念：

1、DB：数据库，保存一组有组织的数据的容器
2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3、SQL:结构化查询语言，用于和DBMS通信的语言

登录

启动：mysql -h localhost -P3306 -u root -p
mysql 【-h主机名 -P端口号】 -u用户名 -p密码
关闭：exit

常见命令：

1.查看当前所有数据库: show databases;
2.打开指定的库：use 库名;
3.查看当前库的所有表： show tables；
4.查看其他库的所有表： show tables from 库名；
5.创建表： create table 表名（
列名 列类型，
列名 列类型，
…
）;
6.查看表结构： desc 表名;
7.查看服务器版本
方式一：登录到mysql服务器
select version();
方式二：没有登录到mysql服务器
mysql --version 或 mysql --V

Mysql的语法规范

1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释
单行注释：#注释文字
单行注释：-- 注释文字 【–后有空格】
多行注释：/* 注释文字 */

转义

ESCAPE转义

SELECT
		last_name 
FROM
		employees
WHERE
		last_name Like '_$_%' ESCAPE '$';

安全等于 <=>

IS NULL:仅仅可以判断NULL值，可读性较高
<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低

例：查询没有奖金的员工名和奖金率

SELECT
		last_name,
		commission_pct
FROM
		employees
WHERE
		commission_pict <=> NULL;

二、单行函数

一、字符函数

1.length 获取参数值的字节个数
2.concat 拼接字符串
3.upper、lower 大写、小写
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
instr(str1,str2) 返回str2在str1中首次出现的索引，如果找不到返回0

6.TRIM
去左右两端空格
例：

SELECT TRIM （'a' FROM 'aaaaaaa张aa三aaa') as out_put
# out_put:张aa三

7.LPAD 用指定的字符实现左填充指定长度

SELECT LPAD('殷素素',10,'*') as out_put
# out_put:*******殷素素
SELECT LPAD('殷素素',2,'*') as out_put
#output:殷素 （截断右边）

8.RPAD 用指定的字符实现右填充指定长度
9.REPLACE 替换

SELECT REPLACE ('张无忌爱上周芷若','周芷若','赵敏') as out_put
out_put:张无忌爱上赵敏

数学函数

1.round 四舍五入 SELECT ROUND（1.567,2)小数点后保留两位
2.ceil 向上取整，返回>=该参数的最小整数
3.floor 向下取整，返回<=该参数的最大整数
4.truncate 截断 SELECT TRUNCATE(1.69999,1)直接截断保留小数点后一位
5.mod 取余
6.rand:获取随机数，返回0-1之间的小数

日期函数

1.now 返回当前系统日期+时间
2.curdate 返回当前系统日期，不包含时间
3.curtime 返回当前系统时间，不包含日期
4.可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年；
SELECT YEAR(‘1998-1-1’) 年；
SELECT MONTH(NOW()) 月; 11
SELECT MONTHNAME(NOW()) 月; November

5.str_to_date: 将日期格式的字符转换成指定格式的日期
STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) 1999-09-13

6.date_format:将日期转换成字符
DATE_FORMAT(‘2020/6/6’,’%Y年%m月%d日’) 2018年6月6日

其他函数

version()
database()
user()
password(‘字符’):返回该字符的加密形式
md5（‘字符’)：返回该字符的md5加密形式

流程控制函数

1.if函数: if else 的效果
2.case函数的使用一： switch case 的效果 适合用于等值判断
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end

SELECT
	salary 原始工资,
	department_id,
CASE
		department_id 
		WHEN 30 THEN
		salary * 1.1 
		WHEN 40 THEN
		salary * 1.2 
		WHEN 50 THEN
		salary * 1.3 ELSE salary 
	END AS 新工资 
FROM
	employees

3.case函数的使用二：类似于 多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end

案例：查询员工的工资情况

SELECT
	salary 原始工资,
CASE
		
		WHEN salary > 20000 THEN
		'A' 
		WHEN salary > 15000 THEN
		'B' 
		WHEN salary > 10000 THEN
		'C' ELSE 'D' 
	END AS 工资级别 
FROM
	employees;

三、分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和
avg 平均值
max 最大值
min 最小值
count 计算个数

特点：
1.sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算
4.count函数的单独介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees; #系统生成一个每一行都为常量值的字段并统计

效率：

MYISAM 存储引擎下,count(*)的效率高。

INNODB 存储引擎下,count(*)和count(1)的效率差不多，比count(字段)要高一些。

5.和分组函数一同查询的字段要求是group by后的字段