常用函数-单行函数

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 ;

 

 

 

 

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2021年3月17日19:27:02
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Mysql 创建表 MySQL

Mysql 创建表

Mysql 创建表   Mysql创建数据库 CREATE DATABASE db_name //字符集 CHARACTER SET charset_name //校验规则 COLLATE ...
Mysql数据类型 MySQL

Mysql数据类型

Mysql数据类型   INT整形类型---属性 unsigned/signed 无符号/有符号 unsigned无符号,创建一个无符号的列 mysql> create table z...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: