MySQL

2024-09-19 17:54:14   1  举报





AI智能生成

MySQL是一种流行的开源关系型数据库管理系统，它使用SQL语言来管理数据。它广泛应用于各种规模的企业、网站和应用程序中，提供高性能、高可靠性和易于使用的数据存储和检索功能。MySQL支持多种操作系统，包括Linux、Windows和macOS，同时提供了丰富的工具和驱动程序，以便与其他应用程序集成。得益于其强大的功能和可扩展性，MySQL已成为全球最流行的开源数据库解决方案之一。

MySQL笔记

数据分析

MySQL

作者其他创作

大纲/内容

练习用的数据库

exam

girls

myemployees

简介

数据库介绍

数据库的相关概念

DB: 数据库：存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来。
用户可以对数据库中的数据进行增加，修改，删除及查询操作。

DBMS：数据库管理系统

概念

数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB

分类

RDBMS: 关系型数据库

关系型数据库也叫结构化数据库，以表格的方式存放数据

NOSQL：非关系型数据库

SQL：结构化查询语言

用于和数据库通信的语言，不是某个数据库软件特有的，
而是几乎所有的主流数据库软件通用的语言

数据库存储的好处

将数据持久化到本地，以结构化方式存储

方便查询，以结构化方式查询

数据库存储的特点

数据存放到表中，然后表再放到库中，MySQL中可以创建多个数据库，用图书馆类比

一个库中可以有多张表，每张表具有唯一的表名用来标识自己

表中有一个或多个列，列又称为“字段”，相当于java中“属性

表中的每一行数据，相当于java中“对象”

常见的关系型数据库

MySQL

Oracle

DB2

SQL Server

..........

MySQL介绍

历史

优点

安装

MySQL启动和停止

命令行

图形化界面

MySQL数据库的连接

MySQL客户端的介绍

SQL语法分类

DDL

数据定义语言

对数据库的操作，对表的操作

DML

数据操作语言

向表中增加删除修改数据，对数据的增删改的操作

DQL

数据查询语言

DCL

数据控制语言

对权限进行控制，对权限的操作

DDL：数据定义语言

创建数据库

show databases :显示数据库

切换数据库： use 数据库名

创建数据库： create database 数据库名

删除数据库：drop database 数据库名

创建表结构

创建表

语法

create table 表名(
字段1 数据类型,
字段2 数据类型,
.....
字段n 数据类型
)

案例

create table student(
stuid int,
stu_name varchar(20),
stu_age int
)

查看数据库中所有的表： show tables;

desc 表名：查看表结构

删除表： drop table 表名

修改表：alter table 表名开头

修改表的名字

alter table 表名 rename to 新的表名

添加字段

alter table 表名 add 字段名字字段的类型(size)

删除字段

alter table 表名 drop 字段的名字

修改字段

修改字段名

alter table 表名 change 原来字段的名字要修改成的名字数据类型(size)

修改字段数据类型

alter table 表名 modify 列名新的类型

change和modify的区别

change 可以更改列名和列类型 (每次都要把新列名和旧列名写上, 即使两个列名没有更改,只是改了类型)
modify 只能更改列属性只需要写一次列名, 比change 省事点

约束

含义

约束就是限制，规定，就是允许你干什么，不允许你干什么

六种约束

非空： not null

表示这个字段不能为空

唯一：unique

表示这个字段不能重复，但是可以为空

主键： primary key

主键是一条记录跟其他记录进行区分标志，主键不能为空，必须唯一，不能重复

默认值：default

用来设置这列的默认值

外键：foreign key

用来表示这个表和其他表关联的字段

检查约束： check

用来规定这个字段只能输入指定的值，但是mysql不支持check约束

分类

列级约束

含义

直接将约束定义在每一列的后面，这种约束叫做列级约束

包含

非空

唯一

默认

主键

检查

表级约束

含义

将约束直接定义到表中，跟字段平级，这种约束叫做表级约束

包含

主键

外键

唯一

添加约束

创建表时添加

列级约束

字段名数据类型(size) 约束名称

cid int primary key,
cname varchar(20) unique not null

表级约束

【constraint 约束名】约束的类型(字段名)

外键

constraint 约束名约束类型(字段名) references 表名（主键）

案例

primary key(sid),
constraint un_student_phone unique(phone),
constraint fk_student_class foreign key(cid) references class(cid)

修改表时添加

添加列级约束

alter table 表名 modify 字段名数据类型(size) 约束类型

添加表级约束

alter table 表名 add constraint 约束名字约束的类型(字段) 【references 表名(主键)】，注意，【】中的只有添加外键时才会用到

DML：数据操作语言

插入数据

insert into 表名（字段1，字段2，.....） values(值1，值2，.....);

注意：值的数量和数据类型要和字段的数量和数据类型应该保持一致

insert into 表名 values(值1，值2，......)

注意：如果省略了字段，默认插入所有字段

更新语句

update 表名 set 字段 = 值, 字段 = 值，.... where 条件

案例

update person set name = '小龙女',age = 38,sex = '女' where sex is null;

删除语句

删除所有记录

delete from 表名;

删除符合某些条件的记录

delete from 表名 where 条件

DQL：数据查询语言

基础查询

语法

select 查询列表
from 表名;

特点

1、查询列表可以是字段、常量、表达式、函数，也可以包含多个

2、查询结果是一个虚拟表

查询语句不会对表中的数据进行任何的改变

示例

查询单个字段

select 字段名 from 表名;

查询多个字段

select 字段名，字段名,..... from 表名;

查询所有字段

select * from 表名

查询常量

select 常量值;

注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

查询函数

select 函数名(实参列表);

查询表达式

select 1000*5;

起别名

as关键字

select 字段名 as 别名，字段名 as 别名 from 表名；
as可以省略

条件查询

语法

select 查询列表
from 表名
where 筛选条件

筛选条件分类

简单条件运算符

< = <> != >= <= <=>安全等于

<> 表示不等于

!= 也表示不等于

null值的判断运算符

is not

between and

<=> 安全等于

判断普通值

判断 null

模糊查询

like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%：任意多个字符，_：任意单个字符

去除重复行

distinct

select distinct tsex,depart from teacher;

去除重复行

distinct

select distinct tsex, depart from teacher;

排序查询

语法

select 查询列表
from 表
where 筛选条件
order by 排序列表【asc | desc】

特点

排序规则

asc : 升序，可以省略

desc : 降序

排序列表支持单个字段、多个字段、函数、表达式、别名

order by的位置一般放在查询语句的最后（除limit语句之外）

常见函数

什么是函数

类似于Java中的方法，将一组语句封装到方法体内部，对外暴露方法名

调用方式

select 函数名(实参列表) 【from 表】;

单行函数

字符串函数

length

获取参数值的字节个数

SELECT length('john'); 4
SELECT LENGTH('张三丰hahaha'); 15

concat

拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

upper、lower

将字符转换为大写和小写

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

案例

将姓变大写，名变小写，然后拼接

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

substr

sbustr(字符串, 索引)

截取从指定索引处后面所有字符，注意：索引是从1开始

SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;

substr(字符串, 索引, 要截取的长度)

截取从指定索引处指定字符长度的字符

SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

案例

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

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

instr

语法

instr(字符串，要查找的子串)

含义

返回子串第一次出现的索引，如果找不到返回0

举例

SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷六侠') AS out_put;

trim

简单用法

语法

trim(字符串)

含义：去掉字符串两边的空格

案例： SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;

ltrim : 去掉左边的空格

rtrim : 去掉右边的空格

去除指定的字符串

trim(要去除字符串 from 从这个字符串去除)

SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;

复杂用法

去除指定的字符串

完整格式

语法：RIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

{}中的内容是选择一个

[] 表示可以省略

BOTH ：表示去除两边的指定字符，默认值

LEADING ：去除左边的指定字符

TRAILING：去除右边的指定字符

remstr：可以省略，默认值是空格

SELECT TRIM(LEADING FROM ' ，，barxxx ');

去除左边的空格

简化格式

TRIM([remstr FROM] str)

默认值为：both, 去除两边的指定字符

lpad、rpad

lpad(字符串,设置长度，要填充的字符串)

rpad(字符串,设置长度，要填充的字符串)

用指定的字符实现左右填充指定长度

SELECT LPAD('殷素素',10,'*') AS out_put;

SELECT RPAD('殷素素',12,'ab') AS out_put;

replace

replace(母字符串,子串,要替换成的字符串)

替换（全部替换）

SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

locate

语法

locate(subStr,string)

含义

函数返回subStr在string中出现的位置

说明

// 如果字符串 string 包含 subStr
locate(subStr,string) > 0

// 如果字符串 string 不包含 subStr
locate(subStr,string) = 0

数学函数

round(浮点数[,小数位数])

四舍五入

小数位数默认为 0

SELECT ROUND(1.55); 2

ceil（浮点数）

向上取整,返回>=该参数的最小整数

SELECT CEIL(1.02); 2

floor（浮点数）

向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.99); -10

truncate(小数,保留的小数位数)

截断

SELECT TRUNCATE(1.69999,1);

mod(a,b)

取余

日期函数

now()

当前时间：日期 + 时间

curdate()

当前系统日期，不包含时间

curtime()

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

获取时间的指定部分

year(日期或者日期格式字符串)

SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;

month(日期或者日期格式字符串)

SELECT month(NOW()) 年;
SELECT month('1998-1-1') 年;

str_to_date(符合模式格式的字符串, 模式)

将字符串通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-03-02','%Y-%m-%d') AS out_put;

模式

date_format（日期,模式）

将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

聚合函数(分组函数)

count : 数量

max ：最大值

min ：最小值

sum ：求和

avg ：平均数

分组查询

group by

以某个或多个字段作为条件进行分组

having

以分组结果作为查询的条件

注意：分组之前的条件写在 where中，分组之后的条件加在 having后

案例：

#查询每个部门中销售员和经理的人数大于4个人的部门信息
select
deptno,count(*)
from emp
where job in ('销售员','经理')
group by deptno
having count(*) > 4

连接查询

含义

当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2,...;

笛卡尔乘积

当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接

如何解决：添加有效的连接条件

分类

内连接

SQL92

语法

等值连接

select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

非等值连接

select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

查询员工的工资和工资级别：

SELECT salary,grade_level

FROM employees e,job_grades g

WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`

AND g.`grade_level`='A';

SQL99

语法

select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件

特点

①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件

案例

需求：查询员工编号，姓名，部门名称，工资等级

select

empno,ename,dname,d.deptno,job,grade

from emp e

inner join dept d on e.deptno = d.deptno

join salgrade s on e.sal between s.losal and s.hisal

外连接

语法

select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表

特点

①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
②left join 左边的就是主表，right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行

分类

左外连接

left join

左边表是主表

查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null

右外连接

right join

右边的就是主表

查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null

全连接

full join

两边都是主表

MySQL 不支持

子查询

含义

嵌套在其他语句内部的select语句称为子查询或内查询，
外面的select语句，则此语句称为外查询或主查询

外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多

出现位置分类

select后面

查询结果只能是一行一列

案例

查询员工编号，姓名，薪水，部门编号，部门名称 ,部门所在地
select
empno,ename,sal,deptno,
(select dname from dept where deptno = emp.deptno) dname,
(select loc from dept where deptno = emp.deptno) loc
from emp;

from后面

查询结果可以是多行多列

where，having后面

查询结果可以是一行一列也可以是多行一列

分页查询

应用场景

当要查询的条目数太多，一页显示不全

语法

select 查询列表
from 表
limit 【offset，】size;
注意：
offset代表的是起始的条目索引，默认从0开始
size代表的是显示的条目数

分页计算公式

假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

联合查询

union all

将两个查询结果直接进行相加

union

将两个查询结果相加，去掉重复的记录，只留下一个

语法

查询语句1
union 【all】
查询语句2
union 【all】
...

流程控制语句

if函数： if else 的效果

SELECT IF(10<5,'大','小');

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;

ifnull

ifnull(字段,0)

含义：如果字段的值为null,函数返回的结果就是 0

case

类似 switch case 的效果

语法

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

案例

案例：查询员工的工资，要求

部门号=10，显示的工资为1.1倍
部门号=20，显示的工资为1.2倍
部门号=30，显示的工资为1.3倍
其他部门，显示的工资为原工资

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary1.1
WHEN 40 THEN salary1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

类似于多重if

语法

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end

案例

查询员工的工资的情况
如果工资>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;

查询总结

查询语句的执行顺序

select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引，条目数; ⑨

DCL：数据控制语言

创建用户

语法：CREATE USER 用户名@地址 IDENTIFIED BY '密码';

固定ip地址：表示这个用户只能在固定的ip地址访问我这个数据库

% ：表示允许这个用户在任何ip地址上访问这个数据库

案例：

create user 'weiwei'@'localhost' identified by 'weiwei';

create user 'weiwei'@'%' identified by 'weiwei';

给用户授权

语法

GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP

注意： @后的ip地址应该跟创建用户时，设置的ip地址一样

案例

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON exam.* TO 'weiwei'@'localhost';

GRANT ALL ON exam.* TO 'kaven'@'%';

FLUSH PRIVILEGES;

刷新权限

查看用户权限

语法

SHOW GRANTS FOR用户名@IP

案例：

SHOW GRANTS FOR 'weiwei'@'localhost';

撤销授权

语法

REVOKE 权限1, … , 权限n ON 数据库.* FORM 用户名@IP

案例

REVOKE CREATE,ALTER,DROP ON exam.* FROM 'weiwei'@'localhost';

FLUSH PRIVILEGES;

刷新权限

FLUSH PRIVILEGES;

当给用户修改密码或者设置权限后需要刷新权限。

删除用户

语法

DROP USER 用户名@IP

案例

drop user 'kaven'@'%';

修改用户密码

语法

Use mysql;
UPDATE USER SET authentication_string=PASSWORD(‘密码’) WHERE User=’用户名’;
flush privileges;

set password for 'weiwei'@'localhost' = password('123456')

案例

Use mysql;
UPDATE USER SET authentication_string=PASSWORD('123456') WHERE User='weiwei';
flush privileges;

set password for 'weiwei'@'localhost' = password('123456')

其他

变量

系统变量

定义：

变量由系统定义，不是用户定义，属于服务器层

注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别

分类

全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

使用

查看所有系统变量

show global variables;

查看满足条件的部分系统变量

show global variables like '%char%';

查看指定的系统变量的值

select @@global .系统变量名;

select @@global.autocommit;

为某个系统变量赋值

方法1：set global 系统变量名=值;

方法2：set @@global .系统变量名=值;

会话变量

作用域：针对于当前会话（连接）有效

使用

查看所有系统变量

show 【session】variables;

查看满足条件的部分系统变量

show 【session】 variables like '%char%';

查看指定的系统变量的值

select @@【session.】系统变量名;

select @@session.autocommit;

为某个系统变量赋值

方法1：set 【session】系统变量名=值;

方法2：set @@【session.】系统变量名=值;

自定义变量

定义：

变量由用户自定义，而不是系统提供的

使用步骤

1、声明
2、赋值
3、使用（查看、比较、运算等）

分类

用户变量

作用域

针对于当前会话（连接）有效，作用域同于会话变量

赋值操作符

声明并初始化

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
注意：如果是通过select进行赋值一定要使用 :=

SELECT 字段 INTO @变量名
FROM 表;

赋值（修改）

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

SELECT 字段 INTO @变量名
FROM 表;

使用（查看变量的值）

SELECT @变量名;

局部变量

作用域

仅仅在定义它的begin end块中有效
只能应用在 begin end中的第一句话

声明

DECLARE 变量名类型;
DECLARE 变量名类型【DEFAULT 值】;

赋值（更新变量的值）

SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;

SELECT 字段 INTO 局部变量名
FROM 表;

使用（查看变量的值）

SELECT 局部变量名;

案例：声明两个变量，求和并打印

用户变量

SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

局部变量

DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

注意：通常begin-end用于定义一组语句块，在各大数据库中的客户端工具中可直接调用，但在mysql中不可用。

begin-end、流程控制语句、局部变量只能用于函数、存储过程内部、游标、触发器的定义内部。

用户变量和局部变量的对比

作用域

用户变量：当前会话

局部变量:定义它的BEGIN END中

定义位置

用户变量：会话的任何地方

局部变量：BEGIN END的第一句话

语法

用户变量：不用指定类型

局部变量：一般不用加@,需要指定类型

函数

报错

名称

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

原因

原因：
mysql的设置默认是不允许创建函数
2.这是因为我们开启了bin-log, 我们就必须指定我们的函数是否是
1） DETERMINISTIC 不确定的
2） NO SQL 没有SQl语句，当然也不会修改数据
3 ）READS SQL DATA 只是读取数据，当然也不会修改数据
4） MODIFIES SQL DATA 要修改数据
5） CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

解决办法

第一种

在mysql中创建函数时出现这种错误的解决方案：
set global log_bin_trust_function_creators=1;

缺点：数据库重启后失效

第二种

在my.ini里面设置
log-bin-trust-function-creators=1
不过这个需要重启服务

windows操作系统下：

1. 由于我们使用MySql 时，需要修改mysql 的 my.ini 的配置文件。

2. 但是 mysql 5.7 的 my.ini 位置并不在 C:\Program Files\MySQL\MySQL Server 5.7 此目录的my-default.ini 并不是我们要的：

3. 如果安装在C盘的：我们要进入 C:\ProgramData\MySQL\MySQL Server 5.7 此目录即可看到my.ini 此时你可以修改次配置啦！

含义

一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

跟存储过程的区别

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END;

注意

1.参数列表包含两部分：
参数名参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

3.函数体中仅有一句话，则可以省略begin end

4.使用 delimiter语句设置结束标记

调用语法

SELECT 函数名(参数列表)

案例

无参函数案例

返回公司的员工个数

实现

CREATE FUNCTION myf1() RETURNS INT
BEGIN

DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c#赋值
FROM employees;
RETURN c;

END $

调用

SELECT myf1()$

有参函数案例1

根据员工名，返回它的工资

实现

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;#定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;

RETURN @sal;

END $

调用

SELECT myf2('k_ing') $

有参函数案例2

根据部门名，返回该部门的平均工资

实现

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE ;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $

调用

SELECT myf3('IT')$

不查表案例

创建函数，实现传入两个float，返回二者之和

实现

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $

调用

SELECT test_fun1(1,2)$

查看函数信息

SHOW CREATE FUNCTION myf3;

删除函数

DROP FUNCTION myf3;

视图

含义

虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
只是保存了sql逻辑

语法

语法：
create view 视图名
as
查询语句;

案例

案例1

查询刘备的员工编号，姓名，薪水，部门名称，工资等级

存储过程

含义

语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END

参数列表中每个参数包括三部分

参数模式

该参数可以作为输入，也就是该参数需要调用方传入值

out

该参数可以作为输出，也就是该参数可以作为返回值

inout

该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

参数名

参数类型

in stuname varchar(20)

说明

如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置。

有些客户端看到【;】直接会提交，但是写在存储过程中的SQL语句，在我们创建存储过程的过程中是不能执行的，就像是
我们在声明方法时，方法里面的代码不能执行一样，只有调用的时候才能执行呢。

语法：
delimiter 结束标记
案例：
delimiter $

调用方法

call 存储过程名(实参列表)

案例

空参列表

案例

插入到admin表中五条记录

实现

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $

调用

CALL myp1()$

带in模式参数

案例1

创建存储过程实现根据女神名，查询对应的男神信息

实现

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('柳岩')$

案例2

创建存储过程判断用户是否登录成功

实现

CREATE PROCEDURE myp4(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('张飞','8888')$

带out 模式参数

案例1

根据输入的女神名，返回对应的男神名

实现

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyname
FROM boys bo
RIGHT JOIN
beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName ;

END $

调用

call myp6('柳岩',@boyN);
select @boyN;

案例2

根据输入的女神名，返回对应的男神名和魅力值

实现

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;

END $

调用

CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp $

带inout模式参数

案例

传入a和b两个值，最终a和b都翻倍并返回

实现

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $

调用

SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

删除存储过程

drop procedure 存储过程名；

查看存储过程信息