常用函数-单行函数

root

233
文章

0
评论

2021年3月17日19:27:02 评论 11817字阅读39分23秒

常用函数-单行函数

调用函数：

SELECT 函数名（） 【FROM 表】；

特点：

叫什么（函数名）
干什么的（函数功能）

函数的分类：

单行函数
- concat
- lenght
- ifnull
- 等
分组函数
- 做统计使用，又称为统计函数，聚合函数

单行函数

1.字符函数

0.COUNT（）统计

1.显示雇员的总数，以及获得补助的雇员数

思路：获得补助的雇员数，就是comm列为非null，就是count(列)，如果该列的值为null，是不会统计

//不统计列有null的值
mysql> select count(*),count(dept_no) from dept_emp;
+----------+----------------+
| count(*) | count(dept_no) |
+----------+----------------+
|   331603 |         331603 |
+----------+----------------+
1 row in set (0.05 sec)


//count统计某列有null值
mysql> select count(*),count(IFNULL(dept_no,1)) from dept_eemp;
+----------+--------------------------+
| count(*) | count(IFNULL(dept_no,1)) |
+----------+--------------------------+
|   331603 |                   331603 |
+----------+--------------------------+
1 row in set (0.04 sec)

2.统计部门领导总人数，有些领导多次出现

mysql> select count(DISTINCT dept_no) from dept_manager;
+-------------------------+
| count(DISTINCT dept_no) |
+-------------------------+
|                       9 |
+-------------------------+
1 row in set (0.00 sec)

1.lenght 获取参数的字节个数

SELECT LENGTH('john');
SELECT LENGTH('张三丰adadad');

#查看系统的编码，UTF-8中一个汉字等于3个字节

SHOW VARIABLES LIKE '%char%'

2.concat 拼接字符

SELECT CONCAT(last_name,'_',first_name) AS name FROM employess;

3.upper(大写),lower(小写)

SELECT UPPER('john');
SELECT LOWER('john');

#实例：将姓变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

4.substr 截取指定字符长度
4.leet 从列的左边。去指定长度的字符
4.right 从列的有边，去指定长度的字符

select leet(name,2) from emp;
select right(name,2) from emp;

#显示陆展元

mysql> select substr('李莫愁爱上了陆展元',7) AS out_put;
+-----------+
| out_put   |
+-----------+
| 陆展元    |
+-----------+
1 row in set (0.01 sec)

#显示李莫愁

mysql> select substr('李莫愁爱上了陆展元',1,3) AS out_put;
+-----------+
| out_put   |
+-----------+
| 李莫愁    |
+-----------+
1 row in set (0.00 sec)

练习1，姓名中首字符大写，其它字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(list_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  姓名 FROM employees;

5.instr 用于返回子串在大的字符串中的起始位置

mysql> SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
+---------+
| out_put |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

#如果找不到信息，返回0

mysql> SELECT INSTR('杨不悔爱上了殷六侠','殷ba侠') AS out_put;
+---------+
| out_put |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

6.trim 默认去除前后的空格
LTRIM 去除左边的空格
RTRIM 去除右边的空格

select LTRIM（' xxxx'） from xx;
select  RTRIM     ('xxx  ') from xx;

mysql> select length('  xxx  ');
+-------------------+
| length('  xxx  ') |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)


mysql> select length(trim('  xxx  '));
+-------------------------+
| length(trim('  xxx  ')) |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set (0.00 sec)

#还可以去除前后其它字符

mysql> select trim('a' from 'aaaa张aaaa翠aaaa山aaaaaaaaa'); 
+-------------------------------------------------+
| trim('a' from 'aaaa张aaaa翠aaaa山aaaaaaaaa')    |
+-------------------------------------------------+
| 张aaaa翠aaaa山                                  |
+-------------------------------------------------+
1 row in set (0.00 sec)

7.lpad 左填充，指定总长度
rpad 右边填充，指定总长度

mysql> select lpad('殷素素',10,'*');
+--------------------------+
| lpad('殷素素',10,'*')    |
+--------------------------+
| *******殷素素            |
+--------------------------+
1 row in set (0.00 sec)

##rpad

mysql> select rpad('殷素素',10,'*');
+--------------------------+
| rpad('殷素素',10,'*')    |
+--------------------------+
| 殷素素*******            |
+--------------------------+
1 row in set (0.00 sec)

8.replace 替换

select enam,REPLACE (job,'MANAGER','XX') from emp;

mysql> select replace ('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
+--------------------------------------+
| out_put                              |
+--------------------------------------+
| 赵敏赵敏张无忌爱上了赵敏             |
+--------------------------------------+
1 row in set (0.00 sec)

2.数学函数

ASB（）绝对值
BIN（）十进制转二进制
CONV（）进制转换

//把数字8，认为是10进制，转换为2进制
mysql> select conv(8,10,2);
+--------------+
| conv(8,10,2) |
+--------------+
| 1000         |
+--------------+
1 row in set (0.00 sec)

mysql> select conv(8,10,10);
+---------------+
| conv(8,10,10) |
+---------------+
| 8             |
+---------------+
1 row in set (0.00 sec)

HEX（）转换为16进制
1.round （）四舍五入（默认小数点后一位）

mysql> select round(1.611);
+--------------+
| round(1.611) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql> select round(1.615,2);
+----------------+
| round(1.615,2) |
+----------------+
|           1.62 |
+----------------+
1 row in set (0.00 sec)

mysql> select round(1.614,2);
+----------------+
| round(1.614,2) |
+----------------+
|           1.61 |
+----------------+
1 row in set (0.00 sec)

2.ceil 向上取整数（值等于自己不取）

mysql> select ceil('1.11');
+--------------+
| ceil('1.11') |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql> select ceil('1.0001');
+----------------+
| ceil('1.0001') |
+----------------+
|              2 |
+----------------+
1 row in set (0.01 sec)

3.floor 向下取整（值等于自己时不变）

mysql> select floor('1.999');
+----------------+
| floor('1.999') |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> select floor('-1.999');
+-----------------+
| floor('-1.999') |
+-----------------+
|              -2 |
+-----------------+
1 row in set (0.00 sec)

4.truncate 截断（默认从小数点开始）

mysql> select truncate(1.55,5);
+------------------+
| truncate(1.55,5) |
+------------------+
|          1.55000 |
+------------------+
1 row in set (0.00 sec)

mysql> select truncate(1.55,1);
+------------------+
| truncate(1.55,1) |
+------------------+
|              1.5 |
+------------------+
1 row in set (0.00 sec)

5.mod 取余

mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select mod(10,2);
+-----------+
| mod(10,2) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

6.rand 随机

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.9024876457662206 |
+--------------------+
1 row in set (0.00 sec)

1-100以内随机值

mysql> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
|                 87 |
+--------------------+
1 row in set (0.00 sec)

mysql> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
|                 65 |
+--------------------+
1 row in set (0.00 sec)

mysql> select floor(1+rand()*99);
+--------------------+
| floor(1+rand()*99) |
+--------------------+
|                 62 |
+--------------------+
1 row in set (0.00 sec)

3.日期函数

DATE_ADD(DATE2,INTERVAL d_value d_type) 在date2中加上日期或时间

//查询在10分钟内发布的新闻
SELECT * FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
MINUTE 分钟
YEAR 天

DATE_SUN(date2,INTERVAL d_value d_type) 在date2上减去一个时间
DATEDIFF（date1,date2） date1-date2两个日期差（结果天）

//请在mysql的sql语句中求出2011-11-11 和1990-1-1相差多少天
SELECT DATEDIFF('xxx','xxx')

//如果你能活到100岁，球你还能活多少天
mysql> mysql> select DATEDIFF(DATE_ADD('1986-11-11',INTERVAL 100 YEAR), NOW());
+-----------------------------------------------------------+
| DATEDIFF(DATE_ADD('1986-11-11',INTERVAL 100 YEAR), NOW()) |
+-----------------------------------------------------------+
|                                                     23881 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

TIMEDIFF（'xx','xx'）单位时分秒
YEAR|MONTH|DAY

SELECT YEAR(NOW()) FROM DULA;
SELECT MONTH(NOW()) FROM DULA;
SELECT DAY(NOW()) FROM DULA;

UNIX_TIMESTAMP()返回1970-1-1到现在的秒数

1.now 返回当前select语句执行的时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-21 12:39:41 |
+---------------------+
1 row in set (0.00 sec)

now显示毫秒

mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2021-06-10 23:39:41.474814 |
+----------------------------+
1 row in set (0.00 sec)

当前时间+1天

hour 小时
minute 分钟

mysql> select date_add(now(),interval 1 day);
+--------------------------------+
| date_add(now(),interval 1 day) |
+--------------------------------+
| 2021-06-11 23:47:36            |
+--------------------------------+
1 row in set (0.00 sec)

当前时间-1天

mysql> select date_add(now(),interval -1 day);
+---------------------------------+
| date_add(now(),interval -1 day) |
+---------------------------------+
| 2021-06-09 23:48:16             |
+---------------------------------+
1 row in set (0.00 sec)

1.sysdate（）；执行到这个韩函数的时间

mysql> select now(),sysdate(),sleep(3),now(),sysdate();
+---------------------+---------------------+----------+---------------------+---------------------+
| now()               | sysdate()           | sleep(3) | now()               | sysdate()           |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2021-06-10 23:44:13 | 2021-06-10 23:44:13 |        0 | 2021-06-10 23:44:13 | 2021-06-10 23:44:16 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (3.00 sec)

2.curdate 返回当前系统日期，不包含时间

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-03-21 |
+------------+
1 row in set (0.05 sec)

3.curtime 返回当前时间，不包含日期

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 12:42:24  |
+-----------+
1 row in set (0.00 sec)

4.YEAR 年
month 月
day 日

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2021 |
+-------------+
1 row in set (0.00 sec)

mysql> select year('1995-1-1');
+------------------+
| year('1995-1-1') |
+------------------+
|             1995 |
+------------------+
1 row in set (0.00 sec)

还可以筛选指定表的列信息

mysql> select * from xx;
+------+---------+
| id   | name    |
+------+---------+
|    1 | Beijing |
|    2 | TianT   |
|    3 | XianT   |
|    4 | WianT   |
|    4 | 2ianT   |
+------+---------+
5 rows in set (0.01 sec)

mysql> select year(id) from xx;
+----------+
| year(id) |
+----------+
|     NULL |
|     NULL |
|     NULL |
|     NULL |
|     NULL |
+----------+
5 rows in set, 5 warnings (0.00 sec)

5.str_to_date 将日期格式的字符转换为指定格式的日期

mysql> select str_to_date('2000-1-3','%Y-%d-%m');
+------------------------------------+
| str_to_date('2000-1-3','%Y-%d-%m') |
+------------------------------------+
| 2000-03-01                         |
+------------------------------------+
1 row in set (0.00 sec)c

查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate= STR_TO_DATE('4-3 1992','%c-%d %Y');

6.date_format 将日期转换成字符

mysql> select date_format('2018/5/5','%Y年%m月%d日');
+-------------------------------------------+
| date_format('2018/5/5','%Y年%m月%d日')    |
+-------------------------------------------+
| 2018年05月05日                            |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%Y年%m月%d日') as out_put;
+-------------------+
| out_put           |
+-------------------+
| 2021年03月21日    |
+-------------------+
1 row in set (0.00 sec)

练习1，查询有降序的员工名和入职日期（xx月/xx日 xx年）

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年' 入职日期 FROM employees WHERE commission_pct IN NOT NULL ORDEY BY last_name ASC;

7.DATEIFF 算日期差值函数单位天

mysql> select datediff(now(),'1999-2-2');
+----------------------------+
| datediff(now(),'1999-2-2') |
+----------------------------+
|                       8102 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2021-4-9','2021-4-1');
+---------------------------------+
| datediff('2021-4-9','2021-4-1') |
+---------------------------------+
|                               8 |
+---------------------------------+
1 row in set (0.00 sec)

8.unix_timestamp 把当前直接转行为时间戳

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1623338914 |
+-----------------------+
1 row in set (0.00 sec)

9.FROM_UNIXTIME 将时间戳转换为指定格式的时间

其它函数

1.查看系统版本

SELECT VERSION();

2.查看当前位置

SELECT DATABASE();

3.查看当前用户

SELECT USER();

4.对用户密码进行加密

MD5()

流程控制函数

1.if函数：if else的效果

mysql> select if(10>5,'大','肖');
+----------------------+
| if(10>5,'大','肖')   |
+----------------------+
| 大                   |
+----------------------+
1 row in set (0.00 sec)c

IFNULL(expr1，expr2) 如果expr1不为空NULL,则返回expr1，否则返回expr2

mysql> SELECT IFNULL(NULL,'chain') FROM DUAL;
+----------------------+
| IFNULL(NULL,'chain') |
+----------------------+
| chain                |
+----------------------+
1 row in set (0.00 sec)

1.查询表emp，如果comm是null，则显示0.0

SELECT ename，IFNULL(COMM,0.0) FROM emp;

2.如果emp表的job是CLERK则显示职员，如果是MANAGER则显示经理，如果是SALESMAN则显示销售人员，其它正常显示

SELECT ename,(select case
                where job = 'CLERK' THEN '职员'
                where job = 'MANAGER' THEN '经理'
                where job = 'SALESMAN' THEN '销售人员'
                ELSE job END) AS job
FROM emp;

2.case函数的

使用一：函数 case的效果

查询员工的工资，要求部分号=30.，显示工资为1.1倍，部门号=40，显示员工资为1.2倍，部门号=50，显示员工资为1.3倍，其他部门，显示的工资为原工资

SELECT slary 原始工资，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 新工资；

使用二：类似于多重if

实例一：查询员工的工资的情况

如果工资>20000,显示A级别

如果工资>15000,显示B级别

如果工资>10000,显示C级别

否则，显示D级别

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

单行函数练习题

1.显示系统时间（注：日期+时间）

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2021-03-22 18:27:03 |
+---------------------+
1 row in set (0.00 sec)

2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（NEW salary）

SELECT employee_id,last_name,salary,salary*1.2 "new salary" FROM employees;

3.将员工的姓名按首字母排序，并写出姓名的长度（LENGTH）

SELECT LENTH(last_name),SUBSTR(last_name,1,1) 首字符 FROM xxx ORDER BY  首字符

4.做一个查询，产生下面的结果

<last_name> earns <salary> monthly but wants <salary*3> Dream Salary

King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary" FROM employees;

5.使用CASE-WHEN,按照下面的条件：

job grade

AD_PRES A

ST_MAN B

IT_PROG C

SELECT job_id AS job,
CASE job_id 
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
END AS grade
FROM employees ;

继续阅读

我的微信

这是我的微信扫一扫

jeesns项目代码部署and自动上线

常用函数-单行函数

常用函数-单行函数

函数的分类：

单行函数

1.字符函数

2.数学函数

3.日期函数

其它函数

流程控制函数

单行函数练习题

发表评论取消回复

微信

联系方式

常用函数-单行函数

函数的分类：

单行函数

1.字符函数

2.数学函数

3.日期函数

其它函数

流程控制函数

单行函数练习题

您可以选择一种方式赞助本站

微信钱包扫描赞助

发表评论 取消回复

登录 注册 找回密码

微信

联系方式

发表评论取消回复

登录注册找回密码