【SQL】SQL入门数据分析
一、My SQL基础介绍
数据库作用:可以持久化数据库至本地;实现结构化查询。
数据库常见概念:DB——数据库;DBMS——数据库管理系统;SQL——结构化查询语言。
数据库存储特点:数据库存储至表中,表再存放在数据库;一个表含多个表,每个表具有唯一的标识;表中含一个或多个列,列又称为字段。
常见的数据库管理系统:MySQL,Oracle,SQLsever
MySQL优点:开源,成本低,体积小,移植性好,属于C/S软件。
学习目录
1.1 My SQL的启动和登录
在以管理员打开的cmd命令下:
1、启动 My SQL:
net start mysql(服务名)
2、登录 My SQL:
mysql -h localhost -P 3306 -u root -p
3、退出 My SQL:
exit
或者 CTRL+C
4、关闭 My SQL:
net stop mysql(服务名)
1.2 My SQL常见命令
1、查看所有的数据库:
show databases;
2、打开指定的库:
use 库名
3、查看当前的库的所有表:
show tables;
4、查看其它库的所有表:
show tables from 库名;
5、创建表:
creat table 表名(列名 列类型,列名 列类型);
6、查看表结构:
desc 表名;
7、查看服务器的版本:
- 方式一:登录My SQL服务端
select version();
- 方式二:在cmd界面
mysql --version
mysql --V
1.3 My SQL语法规范
1、不区分大小写,但建议关键字大写,其它(表名、列名)小写
2、每条命令用分号“;"结尾
3、命令比较长,可进行缩进或者换行(回车)
4、注释:
单行注释:#注释文字 – 注释文字(注意空格)
多行注释:/* 注释文字 */
二、DQL部分(Data Query Language)
2.1 基础查询
语法:
use myemployees; //先指定库名
select
查询列表 //包括:表中的字段、常量、表达式、函数;
from
表名;
2.1.1 查询表中的单个字段
select last_name from employees;
2.1.2 查询表中的多个字段
select last_name,salary,email from employees;
2.1.3 查询表中所有字段
select * from employees;
几个查询注意事项:
- 用select语句查询前,先用"use 库名"指定库名;
- 为区分关键字和查询字段,可用``(着重号进行区分);
- 执行时和格式化时选中相应的语句。
2.1.4 查询常量值
select 100;
select ‘john’;
2.1.5 查询表达式
select 100%98;
2.1.6 查询函数
select version();
2.1.7 查询结果起别名
如查询字段有重名情况,可进行区分。
如别名中含有特殊符号,需要用引号。
1、方式一:使用as
select 查询字段 as 别名 from 表名;
select 100%98 as 结果;
select last_name as 姓,first_name as 名 from employees;
2、方式二:使用空格
select last_name 姓,first_name 名 from employees;
2.1.8 查询去重
语法:
select
distinct expression[,expression...]
from
tables
说明:
- distinct 要放在所有字段的前面;
- 如果去重的字段大于一个,则会进行组合去重,只有多个字段组合起来相同时才会被去重。
举例:
SELECT DISTINCT department_id FROM employees;
2.1.9 +号的作用
- 在My SQL中,“+”号只用作运算符;
select 100+90; //两个操作数均为数值型,则做加法运算;
select '123'+10; //如其中一个操作数为字符型,则转换为数值型,然后做加法运算;如果字符型转换失败,则将字符型数值转换为0,继续做加法运算;如其中一方为NULL,则结果为NULL;
- My SQL的字符拼接
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
- 应用concat函数,如其中值为null,则拼接后结果为null。需要使用ifnull函数。
SELECT
CONCAT(`first_name`,',',`last_name`,',',`email`,',',`salary`,',',
IFNULL(commission_pct,0)) AS OUT_PUT
FROM
employees;
2.2 条件查询
select
查询列表
from
表名
where
筛选条件
2.2.1 按条件运算符筛选
(大于>)(小于<)(不等于<>) (大于等于>=) (小于等于<=)(等于 =)
// 查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000
// 查询部门编号不等于100的员工名字和部门编号
SELECT
`first_name`,`last_name`,`department_id`
FROM
employees
WHERE
`department_id`<> 100;
2.2.2 按逻辑表达式筛选
&&(and) ||(or) !(not)
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id <=110) AND salary > 10000;
2.2.3 模糊查询
(1)like
和通配符搭配使用:
% 表示包含任意多个字符,也可含0个字符;
_ 表示包含任意单个字符;
\ 为转义字符(或者可ESCAPE指定转义字符)。
// 查找员工姓名中第二个字符为_的员工名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%';
//查找部门编号以1开头的员工名字和部门
SELECT
last_name,`department_id`
FROM
employees
WHERE
department_id LIKE '1__'
// 查找员工姓名中第二个字符为_的员工名,使用自定义转义字符
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
(2)between and
注意事项:相比and逻辑运算更加简单;包含临界值;临界值不可以颠倒顺序;
SELECT
*
FROM
employees
WHERE
salary BETWEEN 10000 AND 20000;
SELECT
last_name,salary
FROM
employees
WHERE
salary NOT BETWEEN 8000 AND 17000
ORDER BY
salary DESC;
(3)in
用于判断某字段的值是否是In列表的某一项。
特点:使用In比逻辑运算更加简洁;不支持模糊搜索
// 查询员工工种编号是AD_VP, IT_PROG,AD_PRES中的一个员工名和工种编号
SELECT
last_name
FROM
employees
WHERE
`job_id` IN('AD_VP', 'IT_PROG','AD_PRES');
(4)is null/ is not null
// 查询没有奖金的员工名和奖金率
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
(5)安全等于 <=>
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
比较:is null 仅用来判断null值;安全等与<=>既可以判断null值,也可用来判断数值.
SELECT
last_name,department_id,
`salary`*12*(1+IFNULL(commission_pct,0)) AS 'annual salary'
FROM
employees
WHERE
department_id <= 100;
2.3 排序查询
语法格式:
select //执行3
查询列表
from //执行1
表
where //执行2
筛选条件
order by //执行4
排序字段 asc/desc //asc-ascend; desc-descend
排序字段可包括:单个字段、多个字段、表达式、函数、别名;order by 一般放在查询语句的最后,但limit子句除外
示例:
// 查询部门编号>=90的员工信息,按入职顺序进行排列
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate ASC;
// 按表达式排序(年薪)
SELECT
`employee_id`,last_name,
12*salary*(1+IFNULL(commission_pct,0)) AS 'annual salary'
FROM
employees
ORDER BY
'annual salary' DESC;
// 按函数排序
SELECT
last_name,
LENGTH(last_name) AS 'namelen'
FROM
employees
ORDER BY
LENGTH(last_name) DESC;
// 按照多个字段排序
SELECT
*
FROM
employees
ORDER BY
salary ASC,`employee_id`DESC;
2.4 函数
语法:
select
函数名()
from
表
分类:
- 单行函数
- 分组函数:用于统计,又称为统计函数、聚合函数、组函数
2.4.1 单行函数
1、字符函数
length(str) //计算字符串长度
concat(str1,str2,…) // 拼接字符串
upper(str)\lower(str) //大小写
substr(str,pos) /substring(str,pos) //截取str中索引从pos开始的字符串
substr(str,frompos,len)/substring(str,frompos,len) //截取从frompos开始,长度为len的字符串
instr(str,substr) //返回substr在str中首次出现的位置
SELECT TRIM(‘f’ FROM ‘fffffemailfff’) //字符串首尾去掉字符f
lpad(str,len,padstr) //用指定的字符实现左填充至指定长度
rpad(str,len,padstr) //用指定的字符实现右填充至指定长度
replace(str,from_str,to_str) //将str中的from_str替换为to_str
// 将员工姓名按首字母排序,并写出姓名长度
SELECT
last_name,LENGTH(last_name) AS namelen,
SUBSTR(last_name,1,1) AS firlet
FROM
employees
ORDER BY firlet;
2、数学函数
round(X)/ round(X,D) // 四舍五入
ceil(X) //向上取整
floor(X) //向下取整
truncate(X,D) //截断 SELECT TRUNCATE(1.52669,2); 返回:1.52
mod(a,b) //取余 a%b a-a/b*b
3、日期函数
now() //返回当前系统日期+时间
curdate() //返回系统日期
curtime() //返回系统当前时间
year() 如:SELECT YEAR(hiredate
) FROM employees;
month() 如:SELECT MONTH(hiredate) FROM employees;
day() 如:SELECT DAY(hiredate) FROM employees;
str_to_date(str,format); //将字符串转换为日期格式
date_format(date,format) //将日期转换为字符
format 格式:
格式符 | 功能 |
---|---|
%Y | 四位数的年份 |
%y | 二位数的年份 |
%m | 月份(01,02,…,12) |
%c | 月份(1,2,…,12) |
%d | 日(01,02,…) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟(00,01,…,59) |
%s | 秒(00,01,…,59) |
SELECT
*
FROM
employees
WHERE
hiredate=STR_TO_DATE('04-03 1992','%m-%d %Y');
4、其它函数
select version();
select database();
select user();
5、流程控制函数
(1)if else函数
SELECT
last_name,commission_pct,
IF(commission_pct IS NULL,'NO','YES') AS 'attached info'
FROM
employees
ORDER BY 'attached info';
(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 'new salary'
FROM employees;
- 使用二:类似于多重if
语法:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
示例:
SELECT
last_name,salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 'degree'
FROM employees
ORDER BY degree ASC;
2.4.2 分组函数
常用函数包括:sum、avg、count、max、min(均会自动忽略null值),可与distinct联用,计算不重复值的函数值;和分组函数一同查询的字段要求是group by后的字段。
示例:
SELECT SUM(12*salary*(1+commission_pct)) FROM employees; //此处加ifnull(commission_pct,0)会报错
SELECT AVG(12*salary*(1+commission_pct)) FROM employees;
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
count函数:
// 统计行数
SELECT COUNT(*) FROM employees; //在MYISAM存储引擎下,count(*)效率高
SELECT COUNT(1) FROM employees; //在INNODB存储引擎下,count(*)和count(1)效率差不多,但比count(字段)效率高
// 查询员工表中最大入职时间和最小入职时间的天数
SELECT
DATEDIFF(MAX(`hiredate`),MIN(`hiredate`)) AS difference //datediff函数
FROM
employees;
2.3 分组查询
引入:查询每个部门的平均工资
语法一:分组前的筛选查询
select
column,group_function(column) //分组函数和依据分组的列
from
table
where
condition
group by
group_by_expression //可为列(单个字段或者多个字段)、表达式或函数
order by
column;
语法二:分组后的筛选查询
select
column,group_function(column) //分组函数和依据分组的列
from
table
group by
group_by_expression //可为列(单个字段或者多个字段)、表达式或函数
having
condition
order by
column;
示例:
// 查询员工数大于2的部门
SELECT
COUNT(*),department_id
FROM
employees
GROUP BY
department_id //筛选条件为某一列
HAVING
COUNT(*) > 2;
# 查询有奖金且最高工资高于12000的工种
SELECT
MAX(salary),job_id
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id //筛选条件为某一列
HAVING
MAX(salary)>12000;
# 按员工姓名的长度分组,查询每一组的员工个数,筛选员工数大于5的分组
SELECT
LENGTH(last_name),COUNT(*)
FROM
employees
GROUP BY
LENGTH(last_name) //按函数分组
HAVING
COUNT(*)>5;
// 按照多个字段进行分组
# 查询每个部门每个工种的员工平均工资
SELECT
AVG(salary),department_id,job_id
FROM
employees
GROUP BY
department_id,job_id; //按照多个字段分组
2.4 连接查询(多表查询)
笛卡尔乘积现象:表1有m行,表2有n行,结果为m*n行;
发生原因:没有有效的连接条件;
如何避免:添加有效的连接条件。
- 连接条件按照年代分类:
- sql92标准(仅支持内连接);
- sql99标准(支持所有内连接、支持左外+右外)、交叉连接。
- 连接条件按功能分类:
- 内连接(等值连接、非等值连接、自连接);
- 外连接(左外连接、右外连接、全外连接);
- 交叉连接。
2.4.1 内连接(sql92)
2.4.1.1 等值连接
特点:
- 多表等值连接的结果为多表的交集部分;
- n个表连接,需要n-1个连接条件;
- 多张表的顺序没有要求;
- 一般需要为表起别名;
- 连接可以搭配所有查询子句使用。
SELECT
last_name,department_name
FROM
employees,departments
WHERE
employees.`department_id`=departments.`department_id`;
# 查询员工名,工种号、工种名
SELECT
last_name,emp.`job_id`,job_title //执行3,进行筛选
FROM
employees AS emp,jobs // 执行1,先为原表起别名
WHERE
emp.`job_id`=jobs.`job_id`; //执行2,进行等值连接
# 查询有奖金的员工名及其部门名
SELECT
last_name,department_name,commission_pct
FROM
employees AS emp,departments AS dep
WHERE
emp.`department_id`=dep.`department_id`
AND
commission_pct IS NOT NULL //多表查询,添加筛选条件时,用AND连接
ORDER BY
commission_pct DESC;
# 查询每个城市的部门个数
SELECT
COUNT(*),city
FROM
departments AS dep,locations AS loc
WHERE
dep.`location_id`=loc.`location_id`
GROUP BY
city; //分组的依据可以是连接的另一个表的字段
# 查询员工名、部门名以及所在城市——多张表连接
SELECT
last_name,department_name,city
FROM
employees AS emp,departments AS dep,locations AS loc
WHERE
emp.`department_id`= dep.`department_id`
AND
dep.`location_id`= loc.`location_id`;
2.4.1.2 非等值连接
# 查询员工的工资和工资级别
SELECT
salary,grade_level
FROM
employees AS emp,job_grades AS jg
WHERE
salary BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;
2.4.1.3 自连接
# 查询员工名和上级的名称
SELECT
e.employee_id AS em_num,e.last_name AS em_name,m.employee_id AS ma_num,m.last_name AS ma_name
FROM
employees AS e,employees AS m
WHERE
e.`manager_id`=m.`employee_id`;
2.4.2 连接(sql99)
语法:
select 查询列表
from 表1 别名
【连接类型】join 表2 别名 //内连接:inner 左外连接:left [outer] 右外连接:right[outer] 全外连接:full[outer] 交叉连接:cross(注:笛卡尔积)
on 连接条件
where 筛选条件
group by 分组条件
having 筛选条件
order by 排序列表
2.4.2.1 内连接
1、等值连接
//内连接
# 查询员工名、部门名
SELECT
last_name,department_name
FROM
employees AS emp
INNER JOIN
departments AS dep
ON
emp.`department_id`=dep.`department_id`;
# 查询部门个数大于等于3的城市名和部门个数
SELECT
COUNT(*) AS dep_num,city
FROM
departments AS dep
INNER JOIN
locations AS loc
ON
dep.`location_id`=loc.`location_id`
GROUP BY
city
HAVING
dep_num >= 3;
//多表连接
# 查询员工名、部门名、工种名,并按部门名降序
SELECT
last_name,department_name,job_title
FROM
employees AS emp
INNER JOIN departments AS dep ON emp.`department_id`=dep.`department_id`
INNER JOIN jobs ON emp.`job_id`=jobs.`job_id`
ORDER BY
department_name DESC;
2、非等值连接
# 查询员工的工资级别
SELECT
salary,grade_level
FROM
employees AS emp
INNER JOIN
job_grades AS jg
ON
emp.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;
# 查询每个工资级别的员工个数(大于等于2),并按照工资级别降序
SELECT
COUNT(*) AS g_num,grade_level
FROM
employees AS emp
INNER JOIN
job_grades AS jg
ON
emp.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`
GROUP BY
grade_level
HAVING
g_num >= 2
ORDER BY
grade_level DESC;
3、自连接
#查询员工名及其经理名
SELECT
emp.last_name,emp.employee_id,man.last_name,man.employee_id
FROM
employees AS emp
INNER JOIN
employees AS man
ON
emp.`manager_id`= man.`employee_id`;
2.4.2.2 外连接
应用场景:用于查询一个表中有,另一个表中没有的记录
特点:
- 外连接的查询结果为主表中的所有记录,如从表有匹配项,则显示匹配结果,如从表中无匹配项,则显示null;
- 左外连接:左边是主表,右外连接:右边是主表;
示例:
# 查询没有员工的部门
# 右外连接
SELECT
department_name,employee_id
FROM
employees AS emp
RIGHT OUTER JOIN
departments AS dep
ON
emp.`department_id`=dep.`department_id`
WHERE
emp.`employee_id` IS NULL;
#左外连接
SELECT
department_name,employee_id
FROM
departments AS dep
LEFT OUTER JOIN
employees AS emp
ON
emp.`department_id`=dep.`department_id`
WHERE
emp.`employee_id` IS NULL;
2.5 子查询
1、含义: 出现在其它语句中的select语句,称为子查询或者内查询
2、分类:
(1)按子查询出现的位置分类:
- select后面:仅支持标量子查询
- from后面:支持表子查询
- where或having后面:支持标量子查询(单行子查询),列子查询(多行子查询),行子查询
- exists后面(相关子查询):支持表子查询
(2)按结果集的行列数不同: - 标量子查询(一行一列)
- 列子查询(一列多行)
- 行子查询(主要是一行多列)
- 表子查询(一般为多行多列)
2.5.1 where或having后面
1、特点:
- 子查询放在小括号内,小括号查询结果即为结果集;
- 子查询一般放在条件的右侧;
- 标量子查询,一般搭配单行操作符使用(如条件运算符<、>等)
- 列子查询,一般搭配多行操作符使用(如IN、any/suome、all)
2、标量子查询
# 查询谁的工资比Abel高
SELECT
salary,last_name
FROM
employees
WHERE
salary > (SELECT salary FROM employees WHERE last_name = 'Abel'); //括号中的查询结果为标量,下同
# 查询job_id与141号员工相同,salary比143号员工高的员工
SELECT
employee_id,job_id,salary
FROM
employees
WHERE
job_id = (SELECT job_id FROM employees WHERE employee_id = 141)
AND
salary > (SELECT salary FROM employees WHERE employee_id = 143);
# 查询公司工资最少的员工
SELECT
last_name,job_id,salary
FROM
employees
WHERE
salary = (SELECT MIN(salary) FROM employees);
# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT
department_id,MIN(salary)AS min_s
FROM
employees
WHERE
department_id IS NOT NULL
GROUP BY
department_id
HAVING
min_s > (SELECT MIN(salary) FROM employees WHERE department_id = 50);
3、列子查询
多行操作符:
操作符 | 含义 |
---|---|
IN/NOT IN | 等于列表中的任意一个 |
ANY/SOME | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
# 返回location_id是1400或1700的部门所有员工的姓名
// 用内连接方式
SELECT
last_name,department_name,location_id
FROM
employees AS emp
INNER JOIN
departments AS dep
ON
emp.`department_id`=dep.`department_id`
WHERE
location_id IN(1400,1700);
// 用子查询方式
SELECT
last_name
FROM
employees
WHERE
department_id IN (SELECT department_id FROM departments WHERE location_id IN(1400,1700));
# 返回比job_id为‘IT_PROG’的工种任一工资低的其它工种的员工工号、姓名、工种和工资
SELECT
employee_id,last_name,job_id,salary
FROM
employees
WHERE
salary < ANY(SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG')
AND
job_id <> 'IT_PROG';
# 查询和姓名中含u的员工在相同部门的员工的员工号和姓名
SELECT
employee_id,last_name
FROM
employees
WHERE
department_id IN(SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%');
# 查询管理者是K_ing的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE
manager_id=ANY(SELECT employee_id
FROM employees
WHERE last_name ='K_ing');
4、行子查询
特点:多个筛选条件具有相似性,可用相同的筛选表达式表示
# 查询员工编号最小且工资最高的员工信息
# 用列查询
SELECT
*
FROM
employees
WHERE
employee_id <= ALL(SELECT employee_id FROM employees)
AND
salary >= ALL(SELECT DISTINCT salary FROM employees);
# 用标量查询
SELECT
*
FROM
employees
WHERE
employee_id = (SELECT MIN(employee_id) FROM employees)
AND
salary = (SELECT MAX(salary) FROM employees);
# 用行查询
SELECT
*
FROM
employees
WHERE
(employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);
2.5.2 select后面
# 查询每个部门的员工个数
# 采用GROUP BY的方法
SELECT
COUNT(*),department_id
FROM
employees
GROUP BY
department_id; # 本方法仅能查询出有员工的部门,漏掉了无员工的部门
# 采用子查询的方法,可查询出所有部门名称
SELECT
dep.*,
(SELECT COUNT(*) FROM employees AS emp
WHERE dep.`department_id`=emp.`department_id`) AS em_num
FROM departments AS dep;
2.5.3 from后面
示例:
# 查询每个部门的平均工资的工资等级
SELECT
department_id,avs,grade_level
FROM
(SELECT ROUND(AVG(salary),2) AS avs,
department_id FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id) AS avst //将子查询结果作为一张表,必须起别名
INNER JOIN
job_grades AS jg
ON
avs BETWEEN jg.`lowest_sal`
AND jg.`highest_sal`
ORDER BY
grade_level;
# 查询各部门中工资比本部门平均工资高的员工号、姓名和工资
SELECT
employee_id,last_name,salary,avs,emp.`department_id`
FROM
employees AS emp
INNER JOIN
(SELECT ROUND(AVG(salary),2)AS avs,department_id FROM employees
WHERE department_id IS NOT NULL GROUP BY department_id) AS avst
ON emp.`department_id`= avst.`department_id`
WHERE salary > avs ;
2.5.4 exists后面
语法:
exists(完整的查询语句)
输出结果:1或0
示例:
# 查询有员工的部门名
SELECT department_name
FROM departments AS dep
WHERE EXISTS(SELECT * FROM employees AS emp
WHERE dep.`department_id`=emp.`department_id`);
2.6 分页查询
应用场景: 需要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表 #执行7
from 表1 #执行1
【join type】join 表2 #执行2
on 连接条件 #执行3
where 筛选条件 #执行4
group by 分组字段 #执行5
having 分组后筛选 #执行6
order by 排序字段 #执行8
limit [offset],size; //offset 要显示起始索引,索引从0开始;size为要显示的索引条数 #执行9
# 查询有奖金的员工信息,并且显示工资较高的前10名
SELECT * FROM employees
WHERE
commission_pct IS NOT NULL
ORDER BY
salary DESC
LIMIT 10;
# 显示页数为page,每页的条目数为size
select 查询列表
from 表
limit (page-1)*size,size;
示例:综合应用
# 查询平均工资最低的部门信息
# 普通方法
SELECT
dep.*
FROM
departments AS dep
WHERE department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
HAVING AVG(salary) =
(SELECT
MIN(avs)
FROM
(SELECT
department_id,
AVG(salary) AS avs
FROM
employees
GROUP BY department_id) AS avst)) ;
# 采用分页查询方法
SELECT
*
FROM
departments
WHERE department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1) ;
# 查询平均工资最低的部门信息和该部门的平均工资
SELECT DISTINCT *
FROM departments AS dep
INNER JOIN
(SELECT AVG(salary) AS avs,department_id
FROM employees
GROUP BY department_id) AS avst
ON dep.`department_id`=avst.`department_id`
ORDER BY avs ASC
LIMIT 1;
# 查询平均工资高于公司平均工资的部门有哪些?
SELECT
department_id,AVG(salary)
FROM
employees
GROUP BY department_id
HAVING AVG(salary) >
(SELECT
AVG(salary)
FROM
employees );
# 查询出公司中所有manager的详细信息
SELECT *
FROM employees
WHERE
employee_id=ANY(
SELECT DISTINCT manager_id FROM employees);
# 查询平均工资最高的部门的manager的详细信息
SELECT
*
FROM
employees
WHERE employee_id =
(SELECT
manager_id
FROM
departments
WHERE department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1)) ;
2.7 Union联合查询
**应用:**将多条查询语句的结果合并为一个结果。
语法:
查询语句1
union
查询语句2
特点:
- 多条查询语句的字段名一致(查询结果表头一致);
- union 查询结果会自动去重,可以使用UNION ALL包含重复项。
三、DML语言(Data Manipulation Language)
3.1 插入语句
语法一:
insert into
表名(列名,...)
values(值1,...)
注意事项:
- 插入值的类型要与列的类型一致或者兼容;
- 可以为NULL的值可以省略列名;
- 列的顺序可以改变,但值要与输入的列一一对应;
- 可以省略列名,但默认所有列,且顺序与现有表格一致。
语法二:
insert into 表名
set 列名=值,列名=值,...
应用:
- 方式一支持插入多行;
insert into employees
values(值序列1),(值序列2);
- 方式一支持子查询;
insert into employees(employee_id,last_name,salary)
select 209,'zhang',120000;
3.2 修改语句
3.2.1 修改单表的记录
语法:
update 表名
set 列=新值,列=新值,...
where 筛选条件; //筛选更新哪条数据
示例:
# 修改beauty表中姓唐的女生名电话为110
UPDATE beauty
SET phone='110'
WHERE NAME LIKE '唐%' ;
# 修改boys表中id号为2的名称为张飞,魅力值为10
UPDATE boys
SET boyname='张飞',usercp=10
WHERE id=2;
3.2.2 修改多表的记录
语法:
# sql92语法
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
# sql99语法
update 表1 别名
inner|left outer|right outer join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
示例:
# 修改张无忌的女朋友的手机号为114
UPDATE boys b
INNER JOIN beauty be ON b.'id'=be.'id'
SET be.'phone'='114'
WHERE b.'boysname'='张无忌'
3.3 删除语句
方式一:
delete from 表名 //按照整行进行删除
where 筛选条件
方式二:
truncate table 表名; //删除整张表
应用:
- delete 可以加筛选条件where。
- truncate 效率更高。
- 假如要删除的表含自增长列,如用delete删除后,再插入数据,自增长列从断点开始,而truncate删除后,再插入数据,自增长列的值从1开始。
- truncate删除无返回值,delete删除有返回值。
- truncate删除不能回滚,delete删除可以回滚。
# 删除手机号以9结尾的女生信息
DELETE FROM beauty
WHERE phone LIKE '%9';
# 删除张无忌的女朋友的信息
DELETE be //表示删除beauty表
FROM beauty as be
INNER JOIN boys as b ON b.'id'=be.'id'
WHERE be.'boyname'='张无忌';
四、DDL语言(Data Definition Language)
4.1 库的管理
包括:库的创建、库的修改、库的删除
CREATE DATABASE IF NOT EXISTS books; //创建books库
ALTER DATABASE books CHARACTER SET utf8mb4; //更改库的字符集
DROP DATABASE IF EXISTS books; //库的删除
4.2 表的管理
1、表的创建
语法:
create table 表名(
列名,列的类型[(长度)约束],
列名,列的类型[(长度)约束],
列名,列的类型[(长度)约束],
...)
示例:
# 创建book表
CREATE TABLE IF NOT EXISTS book(
id INT,#编号
bName VARCHAR(12),#书名
price DOUBLE,#价格
author_id INT,#作者编号
publishDate DATETIME#出版日期
);
SELECT * FROM book;
DESC book;
# 创建作者表
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(6),
nation VARCHAR(10)
);
DESC author;
2、表的修改
应用:
- 修改列名
- 修改列的类型或约束
- 添加新列
- 删除列
- 修改表名
# 修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
DESC book;
# 修改列的类型
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
DESC book;
# 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
DESC author;
# 删除列
ALTER TABLE author DROP COLUMN annual;
DESC author;
# 修改表名
ALTER TABLE author RENAME TO b_authors;
3、表的删除
# 删除表
DROP TABLE IF EXISTS b_authors;
SHOW TABLES;
4、表的复制
# 首先在表中插入数据
INSERT INTO author VALUES
(1,'余秋雨','中国'),
(2,'史铁生','中国'),
(3,'鲁迅','中国');
SELECT * FROM author;
# 表的复制
# 仅仅复制表的结构
CREATE TABLE copy_author1 LIKE author;
SELECT * FROM copy_author1;
# 复制表的结构和数据
CREATE TABLE copy_author2
SELECT * FROM copy_author2;
# 复制部分数据
CREATE TABLE copy_author3
SELECT id,au_name
FROM author
WHERE `au_name`='鲁迅';
SELECT * FROM copy_author3;
# 复制部分字段和数据
CREATE TABLE copy_author4
SELECT id,au_name
FROM author;
SELECT * FROM copy_author4;
# 仅复制部分字段
CREATE TABLE copy_author5
SELECT id,au_name
FROM author
WHERE 0;
SELECT * FROM copy_author5;
4.3 常见数据类型
1、分类:
(1)数值型
- 整型: tinyint(1字节),smallint(2字节),mediumint(3字节),int/integer(4字节),bigint(8字节)
# 设置无符号和有符号整型
CREATE TABLE tab_int(
t1 INT # 默认设置为有符号(可插入负值)
t2 INT UNSIGNED #设置无符号型(插入负值会报错)
t3 INT(7) ZEROFILL); #int(7)表示显示最大宽度为7位,使用ZEROFILL关键字时,位数不够会用0进行填充
- 小数:
- 定点数:decimal(M,D)/dec(M,D)
- 浮点数:float(M,D)(4字节)、double(M,D)(8字节)
注意:
- M表示整数部位+小数部位长度;D表示小数部位长度;如(5,2)表示数值总长度为5位,小数部分为2位
- M和D均可省略,decimal默认为(M,D)-(10,0),float 和double会根据数值精度确定
- 定点型相对浮点型精确度更高,如货币运算时可考虑decimal类型。
(2)字符型
- 较短文本:char(M)、varchar(M) //M表示最大的字符数
区别: char表示固定字符长度,varchar表示可变字符长度。char比较耗费存储空间,但效率高;varchar比较节省空间,相对效率低。
- Enum 类型(枚举型)
# 创建表并插入枚举型数据
CREATE TABLE tab_char(
c1 ENUM('a','b','c'),#c1取值范围为('a','b','c')
c2 ENUM('Q1','Q2','Q3','Q4'));
DESC tab_char;
INSERT INTO tab_char VALUES('a','q1'),('b','q2');
SELECT * FROM tab_char;
- set类型(集合型)
# 创建表并插入集合型的数据
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('c,d'),('a');
SELECT * FROM tab_set;
- 较长文本:text、blob(较长的二进制数据)
(3)日期型
- date(4字节)
- datetime(8字节)
- timestamp(4字节)
- time(3字节)
- year(1字节)
datetime与timestamp区别:
- timestamp支持的时间范围比较小,取值范围1970.01.01.08:00:01-2038年的某个时间;datetime的取值范围:1000-1-1————9999-12-31
- timestamp更能反映实际的日期,会受实际时区的影响。
4.4 常见约束
1、含义:
一种限制,专门用于限制表中的数据,为了保证表中数据的准确和可靠性。
2、约束分类:
- 非空约束(NOT NULL):非空,用于保证该字段不能为空;
- 默认约束(DEFAULT);
- 主键约束(PRIMARY KEY):唯一且非空;
- 唯一约束(UNIQUE):唯一但可以为空;
- 检查约束(CHECK):mysql不支持,应用:如年龄、性别;
- 外键约束(FOREIGN KEY):用于限制两个表的关系,保证该字段的值必须来自于主表的关联列的值。
主键(PRIMARY KEY)和唯一(UNIQUE)的对比
类型 唯一性 是否允许为空 个数 是否允许多列组合 主键 是 否 至多1个 允许,但不推荐 唯一键 是 是 可以多个 允许,但不推荐
外键的注意事项
- 在从表添加外键约束,用于引用主表中某列的值,如员工表的部门编号、工种编号。
- 主表的关联列必须是一个key(一般是主键或唯一键)。
*插入数据时,先插入主表,再插入从表;删除数据时,先删除从表,再删除主表。
3、添加约束的时机:
- 创建表时;
- 修改表时。
4、约束的添加分类:
- 列级约束:六大约束语法上都支持,但“外键约束”无效果;
- 表级约束:除“非空约束”、“默认约束”,其它约束都支持。
4.4.1 创建表时添加约束
1、添加列级约束
语法示例: 直接在字段名和类型名后面追加约束类型即可
# 先创建学生信息库
CREATE DATABASE students;
USE students;
# 创建学生信息表
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,# 非空
gender CHAR(1) CHECK(gender='男' OR '女'),#检查约束,不支持
seat INT UNIQUE,#唯一约束
age INT DEFAULT 18#默认约束
# major_id int foreign key references major(id)#外键,不支持
);
# 创建专业信息表
CREATE TABLE major(
id INT PRIMARY KEY,#主键
major_name VARCHAR(20)
);
2、添加表级约束
**语法示例:**在各个字段的最下面,采用"[constraint 约束名] 约束类型(字段名)"
# 添加表级约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(20) NOT NULL,#列级别约束
gender CHAR(1),
seat INT,
age INT DEFAULT 18,#列级别约束
major_id INT,
CONSTRAINT pk PRIMARY KEY(id),#主键,此处采用表级别的约束
CONSTRAINT uq UNIQUE(seat),#唯一键,此处采用表级别的约束
CONSTRAINT ck CHECK(gender='男' OR '女'),#检查键,不支持
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id) #外键
);
DESC stuinfo;
SHOW INDEX FROM stuinfo;
3、添加约束的通用写法
语法示例:
# 添加约束的通用写法
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,#主键,列级约束
stuName VARCHAR(20) NOT NULL,#非空键,列级约束
gender CHAR(1),
age INT DEFAULT 18,#默认键,列级约束
seat INT UNIQUE,#唯一键,列级约束
major_id INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id)#外键,表级约束
);
SHOW INDEX FROM stuinfo;
4.4.2 修改表时添加和删除约束
语法:
# 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束类型;
# 添加表级约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) 外键的引用;
应用示例:
# 修改表时添加和删除约束
# 先创建表
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20),
gender CHAR(1),
age INT DEFAULT 18,#默认键,列级约束
seat INT UNIQUE,#唯一键,列级约束
major_id INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id)#外键,表级约束
);
DESC stuinfo;
# 删除约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT;#删除唯一约束
ALTER TABLE stuinfo DROP INDEX;#删除唯一键
ALTER TABLE stuinfo DROP PRIMARY KEY;#删除主键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;#删除外键
SHOW INDEX FROM stuinfo;
# 添加约束
# 添加非空约束(仅能添加列级约束)
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
# 添加唯一约束(包括列级约束和表级约束)
# (1) 添加列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
# (2) 添加表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
# 添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id);
4.4 标识列(自增长列)
1、创建表时设置标识列
应用示例:
USE books;
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,#创建自增长列
NAME VARCHAR(20)
);
INSERT INTO tab_identity VALUES(NULL,'john');
INSERT INTO tab_identity VALUES(NULL,'lucy');
SELECT * FROM tab_identity;
SHOW VARIABLES LIKE '%auto_increment%'; #显示自增长列信息(起始值和步长)
SET auto_increment_increment=3; #设置步长为3
注意事项:
- 标识列需要是key列(主键列或唯一列);
- 一个表仅能有一个标识列;
- 标识列的类型仅能是数值型。
2、修改表时设置标识列
# 修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
3、修改表时删除标识列
# 修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
五、TCL语言(Transaction Control Language)
5.1 事务基本概念
1、事务:
事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个SQL语句是相互依赖的。整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态。如果单元中的所有SQL语句执行成功,则事务被顺利执行。
2、事务的ACID属性
(1)原子性(atomicity):事务操作是不可分割的工作单位;
(2)一致性(consistency):事务必须使数据库从一个一致性状态变换到另一个一致性状态;
(3)隔离性(isolation):并发执行的各个事务之间不能互相干扰;
(4)持久性(durability):事务一旦提交,改变即为永久性的。
3、事务的分类
(1)隐式事务:事务没有明显的开启和结束标记,如insert、update、delete语句;
(2)显式事务:事务具有明显的开启和结束的标记(前提:必须先设置自动提交功能为禁用)。
5.2 事务的创建
语法:
# 步骤1:开启事务
SET autocommit=0;
START TRANSACTION; #可选
# 步骤2:编写事务中的sql语句(select\insert\update\delete)
#语句1;
#语句2;....
# 步骤3:结束事务
COMMIT;#提交事务
ROLLBACK;#回滚事务
应用示例:
# 应用示例:转账--------------------------------------
# 创建表格
USE books;
CREATE TABLE IF NOT EXISTS account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account VALUES(NULL,'July',1000),(NULL,'lily',1000);
SELECT * FROM account;
# 开启事务
SET autocommit=0;
START TRANSACTION;
# 编写一组事务的语句
UPDATE account SET balance =500 WHERE username='July';
UPDATE account SET balance =1500 WHERE username='lily';
# 结束事务
COMMIT;
SELECT * FROM account;
5.3 事务的隔离级别
1、多个事务并发运行可能产生的问题
(1)脏读:对于两个事务T1和T2,T1读取了已经被T2更新但尚未提交(COMMIT)的字段之后,如果T2回滚(ROLLBACK),T2读取的内容就是临时且无效的。
(2)不可重复读:对于两个事务T1和T2,T1读取了一个字段,然后T2提交(COMMIT)更新了该字段后,T1再次读取同一个字段,两次读取结果不同。
(3)幻读:对于两个事务T1和T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行之后,如果T1再读取同一个表,则会多出几行。
2、隔离级别
# 查询当前隔离级别
SELECT @@tx_isolation;
# 设置当前隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
隔离级别 | 脏读 | 可重复读 | 幻读 |
---|---|---|---|
READ UNCOMMITTED | 是 | 是 | 是 |
READ COMMITTED | 否 | 是 | 是 |
REPEATABLE READ | 否 | 否 | 是 |
SERIALIZABLE | 否 | 否 | 否 |
注:
- My SQL默认为REPEATABLE READ;Oracle默认为READ COMMITTED。
3、回滚点
# savepoint的使用
SET autocommit=0;
START TRANSACTION; #开启事务
DELETE FROM account WHERE id=2;
SAVEPOINT a; #设置保存点
DELETE FROM account WHERE id=3;
ROLLBACK TO a;#回滚到保存点
5.4 delete 和truncate 在事务使用时的区别
# delete 和truncate在事务使用中的区别
# 使用delete删除
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK; #删除结果可以回滚,即删除可以撤销
#使用truncate删除
SET autocommit=0;
START TRANSACTION;
TRUNCATE account;
ROLLBACK;#删除结果不能回滚,即删除不能撤销
六、视图
6.1 视图的创建
语法:
create view 视图名
as
查询语句;
应用示例:
# 1、查询姓名中包含a字符的员工名、部门名和工种信息
USE myemployees;
# (1)先创建视图
CREATE VIEW em_dep_j AS
SELECT last_name,department_name,job_title
FROM employees AS em
INNER JOIN departments AS dep ON em.`department_id`=dep.`department_id`
INNER JOIN jobs ON em.`job_id`=jobs.`job_id`;
# (2)使用视图
SELECT * FROM em_dep_j WHERE last_name LIKE '%a%';
# 2、查询各部门的平均工资的级别
#(1)先创建视图查看每个部门的平均工资
CREATE VIEW avst AS
SELECT AVG(salary) AS avs,department_id
FROM employees
GROUP BY department_id;
# (2)使用连接查询
SELECT avs,grade_level
FROM avst
INNER JOIN job_grades AS jg
ON avs BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;
# 3、查询平均工资最低的部门信息
SELECT *
FROM departments
WHERE department_id=
(SELECT department_id FROM avst ORDER BY avs LIMIT 1);
视图优点:
- 实现了sql语句的重用;
- 简化复杂的sql操作;
- 保护数据,提高安全性。
6.2 视图的查看
示例:
DESC em_dep_j;
SHOW CREATE VIEW em_dep_j;
6.3 视图的修改
语法:
# 方式一:
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
# 方式二:
ALTER VIEW 视图名
AS
查询语句
应用示例:
# 视图修改方式一
CREATE OR REPLACE VIEW avst
AS
SELECT AVG(salary) AS avs,job_id
FROM employees
GROUP BY job_id;
SELECT * FROM avst;
# 视图修改方式二
ALTER VIEW avst
AS
SELECT AVG(salary) AS avs,job_id
FROM employees
GROUP BY job_id;
SELECT * FROM avst;
6.4 视图的删除
语法:
DROP VIEW 视图名,视图名,...;
6.5 视图的更新
具备以下特点的视图不允许更新:
- 包含以下关键字的SQL语句:分组函数、distinct、group by、having、union、union all;
- 常量视图;
- select 中包含子查询;
- 含连接;
- from后跟一个不能更新的视图;
- where 子句的子查询引用了from 子句中的表。
七、变量
7.1 系统变量
变量由系统提供,属于服务器层面。
1、变量分类
- 全局变量:服务器每次启动将为所有的全局变量赋初始值,针对所有会话(连接)均有效,但不能跨重启。
- 会话变量:仅仅针对当前会话(连接)有效。
2、查看系统变量
# (1)查看所有的系统变量
SHOW GLOBAL VARIABLES; #查看全局变量
SHOW SESSION VARIABLES;#查看会话变量
# (2)查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';#查看全局变量
SHOW SESSION VARIABLES LIKE '%char%';#查看会话变量
# (3)查看指定的某个系统变量的值
SELECT @@global.autocommit;#查看全局变量
SELECT @@session.autocommit;#查看会话变量
# (4)为系统变量赋值
# 方式一:
SET GLOBAL autocommit=0;#为系统变量赋值
SET SESSION autocommit=0;#为会话变量赋值
#方式二:
SET @@global.autocommit=0;#为全局变量赋值
SET @@session.autocommit=0;#为会话变量赋值
7.2 自定义变量
用户定义的变量。
1、用户变量:
针对当前会话(连接)有效,同会话变量的作用域。
语法:
(1)声明并初始化
set @用户变量名=值;#方式一
set @用户变量名:=值;#方式二
select @用户变量名:=值; 方式三
(2)赋值(更新用户变量的值)
方式一:通过set或select
set @用户变量名=值;#方式一
set @用户变量名:=值;#方式二
select @用户变量名:=值; 方式三
方式二:通过select into
select 字段 into @变量名 from 表;
(3)查看变量
select @用户变量名;
应用示例:
#自定义变量
SET @name:='john';
SET @count:=0;
USE myemployees;
SELECT COUNT(*) INTO @count FROM employees;
SELECT @name,@count;
2、局部变量:
应用在begin end中的第一句话,且仅仅在定义它的begin end中有效。
语法:
(1)声明变量
declare 变量名 类型;
declare 变量名 类型 default 值;
(2)赋值
方式一:通过set或select
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
方式二:通过select into
select 字段 into 局部变量名 from 表;
(3)查看
select 局部变量名;
八、存储过程和函数
8.1 存储过程
含义:预先编译好的SQL语句的集合。
作用:
- 提高代码的重用性;
- 简化操作;
- 减少了编译次数并减少了数据库服务器的连接次数,提高了效率。
8.2 创建语法
语法:
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的SQL语句)
end
注明:
- 参数列表包含三部分:参数模式(in\out\inout)、参数名、参数类型;
- 如果存储过程体只有一句话,begin end可以省略。存储过程体的每条SQL语句的结尾必须加分号;存储过程的结尾可以使用delimeter重新设置。
8.3 调用语法
语法:
call 存储过程名(实参列表);
8.4 应用
8.4.1 空参的存储过程
示例:
# 插入到account表中五条记录
USE books;
SELECT * FROM account;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO account VALUES(NULL,'john',1000),(NULL,'lily',2000),(NULL,'jill',1500),(NULL,'july',500),(NULL,'jack',2500);
END $
# 调用
CALL myp1()$
SELECT * FROM account;
8.4.2 带In模式的存储过程
示例:
# 创建存储过程实现根据女神名,查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyname VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyname;
END $
# 调用存储过程
CALL myp2('lily')$
# 创建存储过程实现用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0; #声明局部变量
SELECT COUNT(*) INTO result
FROM admin
WHERE admin.username=username
AND admin.password=PASSWORD;
SELECT IF(result>0,'登录成功','登陆失败'); #使用局部变量输出结果
END $
# 调用存储过程
CALL myp3 $;
九、函数
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合数据处理
9.1 创建函数
语法:
create function 函数名(参数列表:参数名、参数类型)returns 返回类型
begin
函数体
end
9.2 调用函数
语法:
select 函数名(参数列表)
应用示例:
#返回公司员工的个数
USE myemployees;
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c FROM employees;
RETURN c;
END $
SELECT myf1()$ #本函数仅能在命令行界面运行,在SQLYOG页面运行会报错
# 根据部门名,返回该部门的平均工资
CREATE FUNCTION myf4(depaName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE avgs DOUBLE DEFAULT 0;
SELECT AVG(salary) INTO avgs
FROM employees AS emp
INNER JOIN departments AS dep
ON emp.department_id=dep.department_id
WHERE dep.department_name=depaName;
RETURN avgs;
END $
SELECT myf3('IT') $
#创建函数,实现输入两个float,返回二者之和
CREATE FUNCTION add_fun(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE add_num FLOAT DEFAULT 0;
SET add_num=num1+num2;
RETURN add_num;
END $
SELECT add_fun(1,2) $
9.3 查看函数
语法:
show create function myf3;
9.4 删除函数
语法:
drop function myf3;
十、流程控制结构
顺序结构:程序从上往下依次执行
分支结构:程序从两条或者多条路径中选择一条执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码
10.1 分支结构
10.1.1 if函数
**功能:**实现简单的双分支
语法:
if(表达式1,表达式2,表达式3)
执行顺序:
表达式1为真,返回表达式2的值,否则返回表达式3的值
10.1.2 case结构
情况1: 等值判断
语法:
case 变量|表达式|字段
when 要判断的值 then 返回值1或语句1;
when 要判断的值 then 返回值2或语句2;
...
else 返回值n或语句n;
end case;
情况2: 区间判断
语法:
case 变量|表达式|字段
when 要判断的条件 then 返回值1或语句1;
when 要判断的条件 then 返回值2或语句2;
...
else 返回值n或语句n;
end case;
特点: 可以作为表达式,在其它语句中使用;也可以在begin end中作为独立语句使用。
10.1.3 if结构
功能: 实现多重分支
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;
应用示例:
# 根据传入的成绩,显示等级
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END $
SELECT test_if(75) $
10.2 循环结构
分类:
while,loop,repeat
循环控制:
iterate,类似于continue,结束本次循环,继续下次循环
leave,类似于break,结束当前循环
while语法:
[标签]while 循环条件 do
循环体;
end while[标签];
loop语法: 用来模拟简单的死循环
[标签]loop
循环体;
end loop[标签];
repeat 语法:
[标签]repeat
循环体;
until 结束循环条件
end repeat[标签];
应用示例:
# 批量插入数据
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,'password') VALUES(CONCAT('rose',i),'666')
SET i=i+1;
END WHILE;
END $
CALL pro_while(100) $
# 批量插入输入(次数超过20则停止)
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,'password') VALUES(CONCAT('jack',i),'888')
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL pro_while(100) $