SQL函数
函数介绍
函数是 SQL 的一个非常强有力的特性,函数能够用于下面的目的:
- 执行数据计算
- 修改单个数据项
- 操纵输出进行行分组
- 格式化显示的日期和数字
- 转换列数据类型
SQL 函数有输入参数,并且总有一个返回值。
函数分类
-
单行函数
单行函数仅对单个行进行运算,并且每行返回一个结果。常见的函数类型:
- 字符
- 数字
- 日期
- 转换
-
多行函数
多行函数能够操纵成组的行,每个行组给出一个结果,这些函数也被称为组函数。
单行函数
单行函数分类
字符函数
大小写处理函数
函数 | 描述 | 实例 |
---|---|---|
LOWER(s)|LCASE(s) | 将字符串 s 转换为小写 | 将字符串 OLDLU转换为小写: SELECT LOWER(“OLDLU”); – oldlu |
UPPER(s)|UCASE(s) | 将字符串s转换为大写 | 将字符串 oldlu转换为大写:SELECT UPPER(“oldlu”); – OLDLU |
示例:
显示雇员 Davies 的雇员号、姓名和部门号,将姓名转换为大写。
select employee_id,UPPER(last_name),department_id from employees where last_name = 'davies';
字符处理函数
示例:
显示所有工作岗位名称从第 4 个字符位置开始,包含字符串 REP的雇员的ID信息,将雇员的姓和名连接显示在一起,还显示雇员名的的长度,以及名字中字母 a 的位置。
SELECT employee_id, CONCAT(last_name,first_name) NAME,job_id, LENGTH(last_name),INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';
数字函数
ROUND(column|expression, n) 函数
ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个参数是 0 或者缺少,值被四舍五入为整数。如果第二个参数是 2值被四舍五入为两位小数。如果第二个参数是–2,值被四舍五入到小数点左边两位。
SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1);
TRUNCATE(column|expression,n) 函数
TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者缺少,值被截断为整数。如果第二个参数是 2,值被截断为两位小数。如果第二个参数是–2,值被截断到小数点左边两位。与 ROUND最大的区别是不会进行四舍五入。
SELECT TRUNCATE(45.923,2);
使用MOD(m,n) 函数
MOD 函数找出m 除以n的余数。
示例:
所有job_id是SA_REP的雇员的名字,薪水以及薪水被5000除后的余数。
SELECT last_name, salary, MOD(salary, 5000) FROM employees
WHERE job_id = 'SA_REP';
日期函数
在MySQL中允许直接使用字符串表示日期,但是要求字符串的日期格式必须为:‘YYYY-MM-DD
HH:MI:SS’ 或者‘YYYY/MM/DD HH:MI:SS’;
示例一:
向 employees 表中添加一条数据,雇员ID:300,名字:kevin ,email:kevin@sxt.cn ,入职时间:
2049-5-1 8:30:30,工作部门:‘IT_PROG’。
insert into employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(300,'kevin','kevin@sxt.cn','2049-5-1 8:30:30','IT_PROG');
转换函数
隐式数据类型转换
隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。如:可以将标准格式的字串日期自动转换为日期类型。
MySQL字符串日期格式为:‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’;
显示数据类型转换
显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。
如:
DATE_FORMAT(date,format) 将日期转换成字符串;
STR_TO_DATE(str,format) 将字符串转换成日期;
示例一:
向 employees 表中添加一条数据,雇员ID:400,名字:oldlu ,email:oldlu@sxt.cn ,入职时间:
2049 年 5 月 5 日,工作部门:‘IT_PROG’。
insert into employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(400,'oldlu','oldlu@sxt.cn', STR_TO_DATE('2049 年 5 月 5 日','%Y 年%m 月%d日'),'IT_PROG');
示例二:
查询 employees 表中雇员名字为 King 的雇员的入职日期,要求显示格式为 yyyy 年 MM 月 dd 日。
select DATE_FORMAT(hire_date,'%Y 年%m 月%d 日') from employees where last_name ='King';
通用函数
示例一:
查询部门编号是50或者80的员工信息,包含他们的名字、薪水、佣金。在income列中,如果有佣金则
显示‘SAL+COMM’,无佣金则显示’SAL’。
SELECT last_name, salary, commission_pct, if(ISNULL(commission_pct),
'SAL','SAL+COMM') income
FROM employees
WHERE department_id IN (50, 80);
示例二:
计算雇员的年报酬,你需要用 12 乘以月薪,再加上它的佣金 (等于年薪乘以佣金百分比)。
SELECT last_name, salary, IFNULL(commission_pct, 0), (salary*12) +(salary*12*IFNULL(commission_pct, 0)) AN_SAL
FROM employees;
示例三:
查询员工表,如果 JOB_ID 是 IT_PROG,薪水增加 10%;如果 JOB_ID 是 ST_CLERK,薪水增加 15%;
如果 JOB_ID 是 SA_REP,薪水增加 20%。对于所有其他的工作角色,不增加薪水。
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
多表查询
SQL92标准中的查询
等值连接
为了确定一个雇员的部门名,需要比较 EMPLOYEES 表中的 DEPARTMENT_ID 列与DEPARTMENTS 表
中的 DEPARTMENT_ID 列的值。在 EMPLOYEES 和DEPARTMENTS 表之间的关系是一个相等 (equijoin)
关系,即,两 个 表 中DEPARTMENT_ID 列的值必须相等。
示例:
显示每个雇员的 last name、departmentname 和 city。
SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
非等值连接
一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES 表 和JOB_GRADES A 表之间的关系有
一个非等值连接例子。在两个表之间的关系是EMPLOYEES 表中的 SALARY 列必须是 JOB_GRADES 表的
LOWEST_SALARY 和HIGHEST_SALARY 列之间的值。使用不同于等于 (=) 的操作符获得关系。
示例:
查询所有雇员的薪水级别。
select e.last_name,j.grade_level
from employees e ,job_grades j
where e.salary
between j.lowest_sal and j.highest_sal;
自连接
连接一个表到它自己。有时需要连接一个表到它自己。为了找到每个雇员的经理的名字,则需要连接
EMPLOYEES 表到它自己,或执行一个自连接。
图片中的例子连接 EMPLOYEES 表到它自己。为了在 FROM 子句中模拟两个表,对于相同的表
EMPLOYEES,用两个别名,分别为 worker 和 manager。在该例中,WHERE 子句包含的连接意味着
“一个工人的经理号匹配该经理的雇员号”。
示例一:
查询每个雇员的经理的名字以及雇员的名字,雇员名字列别名为W,经理列别名为M。
SELECT
worker.LAST_NAME W,manager.LAST_NAME M
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID;
示例二:
查询Fox的经理是谁?显示他的名字。
SELECT
worker.LAST_NAME,manager.LAST_NAME
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID
AND
worker.LAST_NAME = 'Fox';
SQL99标准中的查询
SQL99中的交叉连接(CROSS JOIN)
示例:
使用交叉连接查询 employees 表与 departments 表。
select * from employees cross join departments;
SQL99中的自然连接(NATURAL JOIN)
连接只能发生在两个表中有相同名字和数据类型的列上。如果列有相同的名字,但
数据类型不同,NATURAL JOIN 语法会引起错误。
在图片例子中,LOCATIONS 表被用 LOCATION_ID 列连接到 DEPARTMENT表,这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列,自然连接会使用等值连接的方式连接他们,连接条件的关系为and。
自然连接也可以被写为等值连接:
示例:
使用自然连接查询所有有部门的雇员的名字以及部门名称。
select e.last_name,d.department_name from employees e natural join departments d;
SQL99中的内连接(INNER JOIN)
用ON子句指定更多的连接条件
示例:
查询雇员名字为 Fox 的雇员 ID ,薪水与部门名称。
select e.employee_id,e.salary,d.department_name
from employees e inner JOIN departments d
on e.department_id = d.department_id
where e.last_name = 'Fox';
外连接查询(OUTER JOIN)
左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行,该查询也会取回 EMPLOYEES 表中所有的行。
示例:
查询所有雇员的名字以及他们的部门名称,包含那些没有部门的雇员。
select e.last_name,d.department_name
from employees e LEFT OUTER JOIN departments d
on e.dept_id = d.department_id;
右外连接(RIGTH OUTER JOIN)
右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行,该查询也会取回 DEPARTMENTS 表中所有的行。
示例:
查询所有雇员的名字以及他们的部门名称,包含那些没有雇员的部门。
select e.last_name,d.department_name \
from employees e RIGHT OUTER JOIN departments d
on e.DEPARTMENT_ID = d.department_id;
全外连接(FULL OUTER JOIN)
注意: MySQL 中不支持 FULL OUTER JOIN 连接
可以使用 union 实现全完连接。
- UNION: 可以将两个查询结果集合并,返回的行都是唯一的,如同对整个结果集合使用了DISTINCT。
- UNION ALL: 只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据, 那么返回的结果集就会包含重复的数据了。
语法结构
(SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名 ON 连接条件)
UNION
(SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件)
示例:
查询所有雇员的名字以及他们的部门名称,包含那些没有雇员的部门以及没有部门的雇员。
(select e.last_name,d.department_name
from employees e LEFT OUTER JOIN departments d
on e.department_id = d.department_id)
UNION
(select e1.last_name,d1.department_name
from employees e1 RIGHT OUTER JOIN departments d1
on d1.department_id = e1.department_id)
聚合函数
聚合函数也称之为多行函数,组函数或分组函数。聚合函数不象单行函数,聚合函数对行的分组进行操
作,对每组给出一个结果。如果在查询中没有指定分组,那么聚合函数则将查询到的结果集视为一组。
聚合函数类型
AVG 和 SUM 函数
AVG(arg)函数
对分组数据做平均值运算。
arg:参数类型只能是数字类型。
SUM(arg)函数
对分组数据求和。
arg:参数类型只能是数字类型。
示例:
计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。
SELECT AVG(salary),SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
MIN 和 MAX 函数
MIN(arg)函数
求分组中最小数据。
arg:参数类型可以是字符、数字、 日期。
MAX(arg)函数
求分组中最大数据。
arg:参数类型可以是字符、数字、 日期。
示例:
查询员工表中入职时间最短与最长的员工,并显示他们的入职时间。
SELECT MIN(hire_date), MAX(hire_date) FROM employees;
COUNT 函数
返回分组中的总行数。
COUNT 函数有三种格式:
- COUNT(*):返回表中满足 SELECT 语句的所有列的行数,包括重复行,包括有空值列的行。
- COUNT(expr):返回在列中的由 expr 指定的非空值的数。
- COUNT(DISTINCT expr):返回在列中的由 expr 指定的唯一的非空值的数。
使用 DISTINCT 关键字
- COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数
- 显示 EMPLOYEES 表中不同部门数的值
示例一:
显示员工表中部门编号是80中有佣金的雇员人数。
SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;
示例二:
显示员工表中的部门数。
SELECT COUNT(DISTINCT department_id) FROM employees;
组函数和 Null 值
在组函数中使用 IFNULL 函数
SELECT AVG(IFNULL(commission_pct, 0)) FROM employees;
数据分组(GROUP BY)
创建数据组
在没有进行数据分组之前,所有聚合函数是将结果集作为一个大的信息组进行处理。但是,有时,则需
要将表的信息划分为较小的组,可以用 GROUP BY 子句实现。
原则
- 使用 WHERE 子句,可以在划分行成组以前过滤行。
- 如果有WHERE子句,那么GROUP BY 子句必须在WHERE的子句后面。
- 在 GROUP BY 子句中必须包含列
示例:
计算每个部门的员工总数。
SELECT DEPARTMENT_ID, COUNT(*) FROM employees GROUP BY DEPARTMENT_ID;
在多列上使用分组
示例:
计算每个部门的不同工作岗位的员工总数。
SELECT e.DEPARTMENT_ID, e.JOB_ID,COUNT(*)
FROM employees e
GROUP BY e.DEPARTMENT_ID,e.JOB_ID;
约束分组结果(HAVING)
HAVING 子句是对查询出结果集分组后的结果进行过滤。
约束分组结果
用 WHERE 子句约束选择的行,用 HAVING 子句约束组。为了找到每个部门中的最高薪水,而且只显示
最高薪水大于 $10,000 的那些部门,可以象下面这样做:
- 用部门号分组,在每个部门中找最大薪水。
- 返回那些有最高薪水大于 $10,000 的雇员的部门
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
示例:
显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位,并且用合计月薪排序列表。
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
子查询
子查询是一个 SELECT 语句,它是嵌在另一个 SELECT 语句中的子句。使用子查询可以用简单的语句构建功能强大的语句。
可以将子查询放在许多的 SQL 子句中,包括:
- WHERE 子句
- HAVING 子句
- FROM 子句
使用子查询的原则
- 子查询放在圆括号中。
- 将子查询放在比较条件的右边。
- 在单行子查询中用单行运算符,在多行子查询中用多行运算符。
示例:
查询与Fox同一部门的同事,并显示他们的名字与部门ID。
select e.LAST_NAME,e.DEPARTMENT_ID
FROM employees e
where e.DEPARTMENT_ID =
(select e1.DEPARTMENT_ID from employees e1 where e1.last_name = 'Fox');
单行子查询
单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。
示例:
查询 Fox的同事,但是不包含他自己。
select empl.last_name
from employees empl
where empl.department_id =
(select e.department_id from employees e where e.last_name = 'Fox') and empl.last_name <> 'Fox';
多行子查询
子查询返回多行被称为多行子查询。对多行子查询要使用多行运算符而不是单行运算符。
使用ANY运算符
ANY 运算符比较一个值与一个子查询返回的任意一个值。
- < ANY 意思是小于最大值。
- > ANY 意思是大于最小值。
- = ANY 等同于 IN。
使用ALL运算符
ALL 运算符比较一个值与子查询返回的全部值。
- < ALL 意思是小于最小值。
- > ALL 意思是大于最大值,
NOT 运算符可以与 IN运算符一起使用。
子查询中的空值
内查询返回的值含有空值,并因此整个查询无返回行,原因是用大于、小于或不等于比较Null值,都返回null。所以,只要空值可能是子查询结果集的一部分,就不能用 NOT IN 运算符。NOT IN 运算符相当于 <> ALL。
注意,空值作为一个子查询结果集的一部分,如果使用 IN 操作符的话,不是一个问题。IN 操作符相当于 =ANY。
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);
示例:
查找各部门收入为部门最低的那些雇员。显示他们的名字,薪水以及部门 ID。
select em.last_name,em.salary,em.department_id
from employees em
where em.salary in(select min(e.salary) from employees e group by e.department_id)
group by em.department_id;
MySQL中的索引
索引是对数据库表中的一列或多列值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。索引是一种特殊的文件,它们包含着对数据表里所有记录的位置信息。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。MySQL 索引的建立对于MySQL 的高效运行是很重要的,索引可以大大提高 MySQL 的检索速度。
MySQL中的索引类型
- 普通索引:
最基本的索引,它没有任何限制。 - 唯一索引:
索引列的值必须唯一,但允许有空值,如果是组合索引,则列值的组合必须唯一。 - 主键索引:
特殊的索引,唯一的标识一条记录,不能为空,一般用primary key来约束。 - 联合索引:
在多个字段上建立索引,能够加速查询到速度。
普通索引
是最基本的索引,它没有任何限制。在创建索引时,可以指定索引长度。length 为可选参数,表示索引
的长度,只有字符串类型的字段才能指定索引长度,如果是 BLOB 和 TEXT 类型,必须指定 length。
创建索引时需要注意:
如果指定单列索引长度,length 必须小于这个字段所允许的最大字符个数。
查询索引
SHOW INDEX FROM table_name;
直接创建索引
CREATE INDEX index_name ON table(column(length));
示例:
为 emp3 表中的 name 创建一个索引,索引名为 emp3_name_index;
create index emp3_name_index ON emp3(name);
修改表添加索引
ALTER TABLE table_name ADD INDEX index_name (column(length));
示例:
修改 emp3 表,为 addrees 列添加索引,索引名为 emp3_address_index;
alter table emp3 add index emp3_address_index(address);
创建表时指定索引列
CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY (`id`),
INDEX index_name (column(length))
);
示例:
创建 emp4 表,包含 emp_id,name,address 列, 同时为 name 列创建索引 ,索引名为emp4_name_index。
create table emp4(
emp_id int primary key auto_increment,
name varchar(30),
address varchar(50),
index emp4_name_index(name));
删除索引
DROP INDEX indexname ON tablename;
示例:
删除 mep3 表中索引名为 emp3_address_index 的索引。
drop index emp3_address_index on emp3;
唯一索引
唯一索引与普通索引类似,不同的就是: 索引列的值必须唯一,但允许有空值。
创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length));
示例:
为 emp 表中的 name 创建一个唯一索引,索引名为 emp_name_index。
create unique index emp_name_index on emp(name);
修改表添加唯一索引
ALTER TABLE table_name ADD UNIQUE indexName (column(length));
示例:
修改 emp 表,为 salary 列添加唯一索引,索引名为 emp_salary_index。
alter table emp add unique emp_salary_index(salary);
创建表时指定唯一索引
CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY (`id`),
UNIQUE index_name (column(length)));
示例:
创建 emp5 表,包含 emp_id,name,address 列,同时为 name 列创建唯一索引。索引名为emp5_name_index。
create table emp5(
emp_id int primary key ,
name varchar(30),
address varchar(30),
unique emp5_name_index(name));
主键索引
主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。
修改表添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
示例:
修改 emp 表为 employee_id 添加主键索引。
alter table emp add primary key(employee_id);
创建表时指定主键索引
CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY(column));
示例:
创建 emp6 表,包含 emp_id,name,address 列,同时为 emp_id 列创建主键索引。
create table emp6(
employee_id int primary key auto_increment,
name varchar(20),
address varchar(50));
组合索引
组合索引是指使用多个字段创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用(最左前缀原则)。
最左前缀原则
就是最左优先。
如: 我们使用表中的 name ,address ,salary 创建组合索引,那么想要组合索引生效, 我们只能使
用如下组合:
name/address/salary
name/address
name/
如果使用 addrees/salary 或者是 salary 则索引不会生效。
添加组合索引
ALTER TABLE table_name ADD INDEX index_name (column(length),column(length));
示例:
修改 emp6 表,为 name ,address 列创建组合索引。
alter table emp6 add index emp6_index_n_a(name,address);
创建表时创建组合索引
CREATE TABLE `table` (
COLUMN TYPE ,
INDEX index_name (column(length),column(length)));
示例:
创建 emp7 表,包含 emp_id,name,address 列,同时为 name,address 列创建组合索引。
create table emp7(
emp_id int primary key auto_increment ,
name varchar(20),
address varchar(30),
index emp7_index_n_a(name,address));