js mysql_[Js-MySQL]函数

数据处理函数/单行处理函数

Lower 转换小写

upper 转换大写

substr 取子串(substr( 被截取的字符串, 起始下标, 截取的长度))

length 取长度

trim 去空格

35 / 103

str_to_date 将字符串转换成日期

date_format 格式化日期

format 设置千分位

round 四舍五入

rand() 生成随机数

Ifnull 可以将 null 转换成一个具体值

1 select lower(ename) from emp;　　--查询员工，将员工姓名全部转换成小写

2

3 select * from emp where job=upper('manager');　　--查询 job 为 manager 的员工

4

5 select * from emp where substr(ename, 1, 1)=upper('m');　　--查询姓名以 M 开头所有的员工

6

7 select ename from emp where length(ename)=5 --取得员工姓名长度为 5 的

8

9 select * from emp where job=trim(upper('manager')); --取得工作岗位为 manager 的所有员工

1 /*

2 在SQL语句中查询时间的两种方式3 */

4 select * from emp where HIREDATE='1981-02-20'; --查询 1981-02-20 入职的员工(第一种方法，与数据库的格式匹配上)

5 select * from emp where HIREDATE=str_to_date('1981-02-20','%Y-%m-%d'); --查询 1981-02-20 入职的员工(第二种方法，将字符串转换成 date 类型)

6 select * from emp where HIREDATE=str_to_date('02-20-1981','%m-%d-%Y'); --str_to_date 可以将字符串转换成日期，具体格式 str_to_date (字符串，匹配格式)

1 select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp; --查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss

2 select date_format(now(),'%Y-%m-%d %H %i %s'); --获取并格式化当前时间 now() 获得当前时间

日期格式的说明

%Y：代表 4 位的年份

%y：代表 2 位的年份

%m：代表月, 格式为(01……12)

%c：代表月, 格式为(1……12)

%H：代表小时,格式为(00……23)

%h：代表小时,格式为(01……12)

%i：代表分钟, 格式为(00……59)

%r：代表 时间,格式为 12 小时(hh:mm:ss [AP]M)

%T：代表 时间,格式为 24 小时(hh:mm:ss)

%S：代表 秒,格式为(00……59)

%s：代表 秒,格式为(00……59)

1 select empno, ename, Format(sal, 0) from emp; --查询员工薪水加入千分位

2 select empno, ename, Format(sal, 2) from emp; --查询员工薪水加入千分位和保留两位小数

3

4 select round(123.56); --四舍五入 124

5 select round(123.56,1); --123.6

6 select round(123.56,-1); --120

7

8 select rand(); --生成随机数 0.2605880426807218

9 select * from emp order by rand() limit 2; --随机抽取记录数 order by必须写上

10

11 select

12 empno, ename, job, sal,13 casejob14 when 'MANAGER' then sal*1.1

15 when 'SALESMAN' then sal*1.5

16 end asnewsal17 from emp; --如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%

18

19 select

20 e.*,sal ,21 casejob22 when 'salesman' then sal*1.1

23 when 'clerk' then sal*1.2

24 elsesal25 end asnew_sal26 from emp e; --其他的工资不动，需要添加 else

27

28 select ifnull(comm,0) fromemp;29 /*

30 如果 comm 为 null 就替换为 031 在 SQL 语句当中若有 NULL 值参与数学运算，计算结果一定是 NULL32 为了防止计算结果出现 NULL，建议先使用 ifnull 空值处理函数预先处理。33 */

34 --以下 SQL 是计算年薪的：

35 select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal fromemp;36

37 数据处理函数又被称为单行处理函数，特点：输入一行输出一行

分组函数/ 聚合函数/多行处理函数

count 取得记录数

sum 求和

42 / 103

avg 取平均

max 取最大的数

min 取最小的数

注意：分组函数自动忽略空值，不需要手动的加 where 条件排除空值。

select count(*) from emp where xxx; 符合条件的所有记录总数。

select count(comm) from emp; comm 这个字段中不为空的元素总数。

注意：分组函数不能直接使用在 where 关键字后面。

mysql> select ename,sal from emp where sal > avg(sal);

ERROR 1111 (HY000): Invalid use of group function

select count(*) from emp; --取得所有的员工数，Count(*)表示取得所有记录，忽略 null，为 null 的值也会取得

select count(distinct job) from emp;　　--取得工作岗位的个数

select count(comm) from emp; --取得津贴不为 null 员工数，采用 count(字段名称)，不会取得为 null 的记录

--Sum 可以取得某一个列的和，null 会被忽略

select sum(sal) from emp;　　--取得薪水的合计

select sum(comm) from emp;　　--取得津贴的合计，null 会被忽略

select sum(sal+comm) from emp;　　/*取得薪水的合计(sal+comm)，结果不正确，原因在于 comm 字段有 null 值，所以无法计算，sum 会忽略掉，正确的做法是将 comm 字

段转换成 0，正确的写法如下*/

select sum(sal+IFNULL(comm, 0)) fromemp;select avg(sal) from emp;　　--取得平均薪水

select max(sal) from emp;　　--取得最高薪水

select max(str_to_date (hiredate, '%Y-%m-%d')) from emp;　　--取得最晚入职得员工

select min(sal) from emp;　　--取得最低薪水

select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;　　--取得最早入职得员工

--组合聚合函数

select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

Lower 转换小写upper 转换大写substr 取子串(substr( 被截取的字符串, 起始下标,截取的长度))length 取长度trim 去空格35 / 103str_to_date 将字符串转换成日期date_format 格式化日期format 设置千分位round 四舍五入rand() 生成随机数Ifnull 可以将 null 转换成一个具体值