MySQL

MySQL数据库

一、原理定义概念

定义

  1. 数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库

  2. 数据库是长期储存在计算机内、有组织的、可共享的数据集合

  3. 分类：

    （1）非结构化数据：

    • 数据相对来讲没有固定的特点，这种数据非常不便于检索
    • 但是生活中很多都是非结构化数据

    （2）半结构化数据：

    • 数据之间有着相同的存储结构 属性：值

    • 每一条数据的属性可以不同

      • 张三：
        • 三号学生
      • 李四：

    （3）结构化数据

    • 创建在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据

    • 数据之间有着相同的存储结构 属性 值

    • 同一个结构模型中每一条数据的属性都是相同的，但是值有可能不同，这些数据非常便于存储和管理

    • 于是针对这些数据，专门设计一套数据存储的软件，现在我们说的数据库主要用来存储这些数据

  4. 产品

    • img
    • sqlserver -->微软
    • Mysql -->Java-->Oracle收购
      • 免费
    • Oracle-->Oracle
      • 收费
    • DB2-->IBM 份额很少

  5. 问题

    • 我们所有的数据库都放在软件中
    • 我们要获取到数据，就需要使用软件给我们提供的访问数据的接口
    • 最痛苦的就是程序员，我们就要为存储数据学习不同的数据库
    • 于是有人开始倡导：使用统一的操作数据库的方式

  6. SQL

    • sql一般指结构化查询语言（Structured Query Language）,简称SQL.

    • 全世界所有的结构化数据库都适用

    • 分类

      1. DQL：Data Query Language 数据查询语言

        定义数据库的一些组件 表 索引 视图 自增序列...

      2. DML：Data Manipulate Language 数据操纵语言

        数据库的增、删、改、查

      3. DDL：Database Definition Language 数据定义语言

        Create Drop Alter

        1. DCL：Data Control Language 数据库控制语言

        权限

        事务的提交：commit

        事务的回滚：rollback

  7. SQL语言属于第四代语言，而java c++ 才属于第三代

安装

  1. 官网

​ https://www.mysql.com/

​ https://dev.mysql.com/downloads/mysql/

  1. MySQL安装

  1. 安装路径

    • C:\Program Files\MySQL\MySQL Server 8.0

    • root密码 123456

    • 端口 3306

    • 数据库已经安装完成并可以使用了，但是现在就是操作不方便

    • 进入mysql数据库的命令

      （1）cmd 进入到doc窗口

      （2）输入 cd C:\Program Files\MySQL\MySQL Server 8.0\bin

      （3） mysql -u root -p

      ​ 输入密码：123456

      （4）show databases;

      （5）use mysql

      （6）select host,user from user;

  2. 安装mysql的访问工具

    • navicat 收费的 ，我们需要进行简单的破解
    • 破解之前必须关闭杀毒软件

  3. 数据库连接错误

    • 1045 用户名和密码不匹配 用户名root 密码 123456
    • 2003 连接不到mysql服务器，很常见的是因为启动失败

概念

关系模型中常用的概念

  • 关系：可以理解为一张二维表，每个关系都具有一个关系名，就是通常说的表名
  • 元组：可以理解为二维表中的一行，在数据库中经常被称为记录
  • 属性：可以理解为二维表中的一列，在数据库中经常被称为字段
  • 域：属性的取值范围，也就是数据库中某一列的取值限制
  • 关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或多个列组成
  • 关系模式：指对关系的描述。其格式为：关系名(属性1，属性2， ... ... ，属性N)，在数据库 中成为表结构

scott用例表

  1. emp 员工信息表
字段名 中文 类型 空？ 默认值 克其他说明
EMPNO🔑 雇员编号 int（11） 否 主键
ENAME 雇员名称 varchar（255） 是
JOB 岗位工种 varchar（255） 是
MGR 上级 int（11） 是
HIREDATE 雇佣日期 date 是
SAL 工资 decimal 是
COMM 奖金|津贴 decimal 是
DEPTNO 部门编号 int（11） 是 外键，dept表的deptno 关联
  1. dept部门信息表
字段名 中文 类型 空？ 默认值 其他说明
DEPTNO🔑 部门编号 int（11） 否 主键
DNAME 部门名称 varchar（255） 是
LOC 地址 varchar（255） 是
  1. salgrade薪资区间表
字段名 中文 类型 空？ 默认值 其他说明
GRADE 等级 int（11）
LOSAL 最低薪资 int（11）
HISAL 最高薪资 int（11）

二、SQL-DQL语句

  1. 格式

    select 列名*N from 表名 where 查询条件1 and/or 查询条件2 group by 列 Having 分组条 件 Order by 排序

  2. 规则

    • sql在书写的时候除了查询条件之外，大小写都可以

      select * from user where uname ='zs';

      SELECT * FROM USER WHERE UNAME = 'zs';

      保持大小写风格

      去公司之后需要看其他员工SQL大小写

    • -- 属于SQL语句的注释

    • 所有的查询条件为字符串时，需要用''进行修饰，否则就会当做列名去处理

  3. select查询列和别名

 -- 查询所有员工信息(*通配符，默认查询所有的列)
 select * from emp;
 -- 查询员工的姓名
 select ename from emp;
 -- 查询员工的薪资 select sal from emp;
 -- 查询员工的姓名和薪资
 select ename , sal from emp;
 select ename sal from emp;
 select ename sal comm from emp;
 -- 查询员工的姓名和薪资，推荐使用单引号
 select ename '员工姓名', sal "薪资" from emp;
 -- 查询到的数据可以直接进行运算
 select ename ,sal ,sal * 12 from emp;
 select ename ,sal ,comm ,(sal+comm) * 12 from emp;

  1. select的条件查询

    • 普通条件查询 = > < != <> >= <=

       -- 查询员工编号为7369的员工
 select ename,sal from emp where empno = 7369;
 -- 查询员工姓名叫做
 SMITH select ename,deptno,job from emp where ename = 'SMITH';
 select ename,deptno,job from emp where ename = 'smith';
 -- 查询薪资大于2000的员工姓名
 select ename from emp where sal > 2000;
 -- 查询工作为SALESMAN
 select * from emp where job = 'SALESMAN';
 -- 查询部门在20的员工
 select * from emp where deptno = 20;
 -- 查询薪资不等于2000的员工
 select * from emp where sal != 2000;
 select * from emp where sal != 2000;

    • in 在某个范围中查找

       -- 查询 员工编号 为 7369 7788 7881的员工信息
 select * from emp where empno in (7369,7788,7881);
 -- 查询 员工编号除了 7369 7788 7881之外的所有员工信息
 select * from emp where empno not in(7369,7788,7881);
 -- 查询除了10,20部门之外的所有员工
 select * from emp where deptno not in (10,20);

    • null值查询

       -- 查询不发放津贴的员工信息
 select * from emp where comm is null;
 -- 查询发放津贴的员工信息
 select * from emp where comm is not null;

    • 范围比较

       -- 查询薪资范围在1000-4000之间的员工信息 [1000.4000]
 select * from emp where sal between 1000 and 4000;

    • 模糊查询 % _

      -- 查询名字中有S的员工
select * from emp where ename like '%S%';
-- 查询名字最后一个字符是S
select * from emp where ename like '%S';
-- 查询名字第一个字符是S
select * from emp where ename like 'S%';
-- 查询名字第二个字符是A
select * from emp where ename like '_A%';
-- 查询名字中有%的员工
select * from emp where ename like '%\%%';
-- 查询名字第8 188个字符是A，这是需要一些特殊的手段-》函数
-- % 代表任意字符的任意次数 _任意字符的一次

    • 多条件联合查询 and or

      (1)and 必须前后同时满足条件才能返回结果

      (2)or前后有一个满足条件就能返回结果

      -- 查询在20部门并且薪资大于2000的员工
select * from emp where deptno =20 and sal >2000;
-- 查询在20部门或者薪资大于2000的员工
select * from emp where deptno = 20 or sal >2000;
--查询不在20部门并且薪资小于2000的员工
select * from emp where deptno <> 20 and sal <2000;

  2. select结果排序 order by

    • 使用asc是升序排列（默认），使用desc可以降序排序

    • 单列

      -- 按照薪资进行排序（默认升序）
select * from emp order by sal;
-- 按照薪资进行排序（降序）
select * from emp order by sal desc;
-- 按照薪资进行排序（升序）
select * from emp order by sal asc;
-- 按照津贴进行排序(null排在最前面)
select * from emp order by comm;

    • 多列

      -- 多个排序的列
select * from emp order by deptno,sal;
-- 多个排序的列（部门升序 薪资降序）
select * from emp order by deptno,sal desc;
-- 多个排序的列（工作，薪资）
select * from emp order by job,sal;

  3. select结果分页

    -- 每次查询前N行
SELECT
 * 
FROM
 emp 
 LIMIT 4;
 
-- 查询第N页,每页显示M个
select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;
select * from emp limit (n-1)*M,M;
-- 查询薪资大于1000的逆序排列，然后显示前5条记录
select * from emp where sal >1000 order by sal desc limit 0,5 ;

  4. SQL语句错误

    select enane from emp

    1054 - Unknown column 'enane' in 'field list'
    将来出现错误的时候，直接将错误编号输入到百度查询即可

单行函数

  1. 函数都是数据口提前给我们准备好的，所以我们可以直接可以调用，使用函数可以让指定的列计算出我们需要的数据

  2. 单行函数

    • 指的是操作一行数据返回一行数据，操作10行数据返回10行数据

    • 字符串函数

      函数名称 作用
      LENGTH 计算字符串长度函数，返回字符串的字节长度
      CONCAT 合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
      INSERT 替换字符串函数
      LOWER 将字符串中的字母转换为小写
      UPPER 将字符串中的字母转换为大写
      LEFT 从左侧截取字符串，返回字符串左边的若干个字符
      RIGHT 从右侧截取字符串，返回字符串右侧的若干个字符
      TRIM 删除字符串左右两侧的空格
      REPLACE 字符串替换函数，返回替换后的新字符串
      SUBSTRING 截取字符串，返回从指定位置开始的指定长度的字符串
      REVERSE 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 
      -- 长度
	- select ename,length(ename) from emp;
-- 截取
	- select ename,SUBSTR(ename,1,3) from emp;
	- select * from emp where substr(ename,5,1)='S';
-- 大小写
	- select ename, upper(ename),lower(ename) from emp;
-- 拼接
	- select CONCAT(empno,'=',ename) from emp;
-- 替换
	- select ename,REPLACE(ename,'T','—') from emp;

    • 日期函数

      函数名称 作用
      CURDATA 和 CURRENT_DATE 两个函数作用相同，返回当前系统的日期值
      CURTIME 和 CURRENT_TIME 两个函数作用相同，返回当前系统的时间值
      NOW 和 SYSDATE 两个函数作用相同，返回当前系统的日期和时间值
      UNIX_TIMESRAMP 获取UNIX时间戳函数，返回一个以UNIX时间戳为基础的无符号整数
      FROM_UNIXTIME 将UNIX时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
      MONTH 获取指定日期中的月份
      MONTHNAME 获取指定日期中的月份英文名称
      DAYNAME 获取知道那个日期对应的星期几的英文名称
      DAYOFWEEK 获取指定日期对应的一周的索引位置值
      WEEK 获取指定日期是一年中的第几周，返回值的范围为052或153
      DAYOFYEAR 获取指定日期是一年中的第几天，返回值范围是1~366
      DAYOFMONTH 获取指定日期是一个月中的第几天，返回值范围是1~31
      YEAR 获取年饭，返回值范围是1970~2069
      TIME_TO_SEC 将时间参数转换为秒数
      SEC_TO_TIME 将秒数转换为时间参数，与TIME_TO_SEC互为反函数
      DATE_ADD 和 ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔
      DATE_SUB 和 SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔
      ADDTIME 时间加法运算，在原始时间上添加指定时间
      SUBTIME 时间减法运算，在原始时间上减去指定时间
      DATEDIFF 获取两个日期之间间隔，返回参数1减去参数2的值
      DATE_FORMAT 格式化指定的日期，根据参数返回值那个格式的值
      WEEKDAY 获取指定日期在一周内的工作日索引 
      -- 获取当前系统时间
	- select hiredate,sysdate() from emp;
	- select hiredate,CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP() from emp;
-- 日期转换
	- select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s')
	- select hiredate, date_format(now(),'%Y年%m月%d日 %H时%i分%s秒')  from emp;
-- 分别获取 年月日 时分秒 星期
	- select
		- SECOND MINUTE HOUR DAY WEEK MONTH YEAR
-- 日期的加减操作
	- select hiredate,ADDDATE(hiredate,9),ADDDATE(hiredate,-9) from emp;
	- select DATE('2012-11-11') ;

    • 数字函数

      函数名称 作用
      ABS 求绝对值
      SQRT 求二次方根
      MOD 求余数
      CEIL 和 CEILING 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
      FLOOR 向下取整，返回值转化为一个BIGINT
      RAND 生成一个0~1之间的随机数，传入整数参数时，用来产生重复序列
      ROUND 对所传参数进行四舍五入
      SIGN 返回参数的符号
      POW 和 POWER 两个函数的功能相同，都是所传参数的次方的结果值
      SIN 求正弦值
      ASIN 求反正弦值，与函数SIN互为反函数
      COS 求余弦值
      ACOS 求反余弦值，与函数COS互为反函数
      TAN 求正切值
      ATAN 求反正切值，与函数TAN互为反函数
      COT 求余切值 
      -- 向上取整 向下取整
	- select ceil(12,1),floor(12.9)
-- mod abs pow PI rand round TRUNCATE(直接进行截取，不进行四舍五入)
-- 保留多少位有效数字
	- select round(1.4999999,2),round(1.4999999),round(1.4999999,-1)
    - select TRUNCATE(1.4999999,2)

    • 转换函数

      -- 日期--》字符串
	- date_format(date,expr)
    - select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s');
-- 字符串--》日期
	- 要注意字符串和格式的匹配
	- select STR_TO_DATE('2020-4-16 17:15:24','%Y-%c-%d %H:%i:%s');
-- 数字--》字符串
	- 直接拼接一个字符串即可，可以自动转换
-- 字符串--》数字
	- 依靠函数提供的参数

    • 转换函数

      （1）空值的处理

      if null (exp1,exp2) exp1 != null ? exp1 : exp2

      select IFNULL (comm,888) from emp;

      （2）加密算法

      select MD5('123456');

      select

      AES_ENCRYPY('123456','abcd'),AES_DECRYPT(AES_ENCRYPT('123456','abcd'),'abcd')

多行函数

  1. 不管函数处理多少条，只返回一条记录

  2. 如果你的数据可以分为多个组，那么返回的数据条数和组数相同

    • 每个部门的平均薪资
    • 10 20 30 --》3

  3. 常用的多行函数有5个

    • max最大值

      如果处理的值是字符串，将会把值按照字典序升序

    • min最小值

      如果处理的值是字符串，将会把值按照字典序排序

    • avg平均值‘

      只能用于数值型数据，求平均值

    • sum求和

      如果求和的过程中有null，那么不会计算在内

    • count求总数

      如果统计的数据中有null，不会吧null统计在内

  4. 经典的错误

    • --查询公司最低薪资的员工是谁？

    • select min(sal) , ename from emp;

      MySQL语法不可行

      Oracle语法不可行

    • 将来工作的时候不能把普通列和组函数写在一起

      虽然MySQL语法不会报错，但是给的结果是错误的

数据分组

  1. 按照某一个条件进行分组，每一组返回对应的结果
  2. group by 可以对指定的类进行分组，列尽量有相同的
  3. having 可以对分组之后的数据进行过滤，所以能出现在having中的比较项一定是被分组的列或者是组函数
  4. 底层
    • where称之为行级过滤，处理的是表中每一行数据的过滤
    • having称之为组级过滤，处理的是分组之后的每一组数据
    • 能使用where的，尽量不要使用having
-- 查询每种工作的平均薪资
select job,avg(sal) from emp group by job;
-- 查询每个部门的最高薪资和最低薪资
select max(sal),min(sal) from emp;
select deptno,max(sal),min(sal) from emp group by deptno;
-- 查询每个部门的人数和每月工资总数
select deptno,count(empno),sum(sal) from emp group by deptno;

-- 查询每个部门，每种工作的平均薪资
select deptno,job , avg(sal) from emp group by depyno,job;
select deptno,job , avg(sal) from emp group by deptno,job order by dedptno,job;

-- 查询个人姓名的平均薪资（尽量对多数据进行分组）
select ename,max(sal),min(sal) from emp group by ename;

-- 查询10,20部门中，并且在二月份入职员工中，每个部门中平均薪资高于1500的工作是什么，并按照部门，工作平均薪资进行排序
select * from emp where deptno in (10,20);
select deptno,job,avg(sal) from emp where deptno in (10,20) group by deptno,job having avg(sal)>1500;
select deptno,job,avg(sal) from emp where deptno in (10,20) group by deptno,job having avg(sal)>1500 order by deptno,avg(sal);
-- 美观写法
SELECT
	deptno,
	job,
	avg(sal) '平均薪资'
FROM
	emp
WHERE
	deptno IN ( 10, 20 )
GROUP BY
	deptno,
	job
HAVING
	avg(sal) > 1500
ORDER BY
	deptno,
	avg(sal);

DQL单表关键字执行顺序

  • select

    我们要显示哪些列的数据

  • from

    从哪张表中获取数据

  • where

    从表中获取数据的时候进行行级的数据过滤

  • group by

    对数据进行分组处理，一组获取对应的结果

  • having

    组级过滤，组级过滤的数据必须是分组条件或者是组函数

  • order by

    排序

    asc desc

  • 执行的顺序

    from —>where —>group by—>having—>select—>order by

多表查询

  1. 查询的两张表如果出现同名的列，我们需要将表名标注到列明前面

  2. 如果是非同名的列，表名可加可不加，推荐加上

    • 为了书写方便，可以给表添加别名
    • 一般情况下取首字母，特殊情况下取它所代表的含义
    • 表的别名旨在本次查询中生效

  3. 表与表进行关联查询的时候，如果不添加关联条件，查询的总记录数就是a * b = 笛卡尔积

    a 15 b 10 c 10 —> 1500条

  4. 多表查询的时候必须要加条件

    • 等值
    • 非等值
-- 查询每个员工所在的部门的名称
select ename,deptno from emp;
select deptno,dname from dept;
select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept;
-- 等值关联查询
select emp.eanme,emp.deptno,dept.deptno,depy.dname from emp,dept where emp.deptno = dept.deptno;
selept emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno;
-- 添加别名
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

表与表关联的方式

  1. 因为表的关联条件和业务查询条件放在了一起，为了防止混淆于是提供了下面三种方式

  2. 自然连接

    会自动选择列名相同并且类型相同的列

    -- 查询薪资大于2000的员工姓名和部门名称
select e.enamem,d.dname from emp e,dept d where e.deptno = d.deptno and e.sal>2000;
-- 自然连接
select e.ename,d.dname from emp e natural join dept d;
select e.ename,d.dname from emp e natural join dept d where e.sal > 2000;

  3. using

    不需要MySQL帮我们选择等值连接的列，现在我们指定等值连接的列

    -- 查询薪资大于2000的员工姓名和部门名称 using
select e.ename,d.dname from emp e join dept d using(deptno);
select e.ename,d.dname from emp e join dept d using(deptno) where e.sal > 2000;

  4. on

    我们可以指定两张表关联的条件，可以非等值的操作

    -- 查询薪资大于2000的员工姓名和部门名称 using
select e.ename,d.dname from emp e join dept d on(e.deptno = d.deptno)
select e.ename,d.dname from emo e join depy d on(e.deptno = d.deptno) where e.sal>2000;
-- 查询每个员工所对应的薪资等级
select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
select e.ename,s.grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);

  5. 查询名字中带有A字母的员工姓名，部门名称和薪资等级

    SELECT
	e.ename,
	d.dname,
	s.grade
FROM
	emp e,
	depy d,
	salgrade s
WHERE
	e.deptno = d.deptno
	AND e.sal BETWEEN s.losal AND s.hisal
	AND e.ename LIKE '%A%';
 ------------------------------------------------------------------------------------ 
SELECT
	e.ename,
	d.dname,
	s.grade
FROM
	emp e
	JOIN dept d USING ( deptno )
	JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal)
WHERE
	e.ename LIKE '%A%';

表与表的外连接

  1. 当我们对两张表进行关联查询的时候，基于数据的基因导致其中一张表中的数据没办法被完全查询出来

  2. 外连接可以让没查询出来的数据也被显示出来

  3. 因为我们写SQL的时候表总有左右之分，外连接也分

    左外连接：显示左面表所有的数据

    右外连接：显示右面表所有的数据

-- 查询每个部门的人数
select deptno,count(empno) from emp group by deptno;
select * from emp e join dept d using(deptno);
select * from emp e left join dept d using(deptno);
select * from emo e right join deot d using(deptno);



select deptno,count(e.empno) from emp e right join dept d using(deptno) group by deptno;

----------------------全外连接
SELECT
	deptno,
	e.ename,
	d.dname
FROM
	emp e LEFT JOIN dept d USINg( deptno );
	
---------------------Oracle的全外连接使用Full Join

表与表的自连接

  • 我们要查询的两个字段同时处于一张表上，我们只能将一张表当作含有不同意义的两张表去处理

  • 给相同的表取不同的简称（按照所代表的含义去取）

    -- 查询每个员工与其直属领导的名字
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
select e.ename,m.ename from emp e join emp m on(e.mgr = m.ename);

表与表的子连接

  • 把一个SQL语句的查询结果当成另外一个SQL语句的查询结果

    -- 查询公司中薪资最低的员工姓名
select ename,sal from emp where sal = (select min(sal) from emp);
-- 查询公司中谁的薪资高于平均薪资
select ename,sal from emp where sal > (select avg(sal) from emp);

-- 谁的薪资高于20部门员工的薪资
select ename,sal from emp where sal > all(select sal from emp where deptno = 20);
select ename,sal from emp where sal > some(select sal from emp where deptno = 20);
select ename,sal from emp where sal > some(select sal from emp where deptno = 20);

表与表的伪表查询

  • 如果我们所需要的查询条件需要别的SQL语句来提供

  • 如果只需要一个条件那么可以使用子查询来完成

  • 如果需要多个查询条件，这时就要将所有的查询结果当做伪表进行管理

  • 我们需要把一些含有特殊符号的列名设置别名，然后给伪表设置一个别名（见名知意）

    -- 查询高于自己部门平均薪资的员工信息
select deptno,avg(sal) avgsal from emp group by deptno;

SELECT
	e.ename,
	e.sal,
	e.deptno
FROM
	emp e,
	( SELECT deptno, avg(sal) avgsal FROM emp GROUP BY deptno ) d
SELECT
	e.deptno = d.deptno
	AND e.sal > d.avgsal;

三、SQL-DML

SQL-DML插入

  1. insert into 表名 values();

    • insert into dept values(50,’sxt’,’shanghai’)

    • 要求插入数据的数量，类型要和定义的表结构一致

      insert into dept values(50,’sxt’,’shanghai’,’liyi’);

      insert into dept values(50,’sxt’);

      insert into dept values(’abcd’,50.’sh’);

  2. insert into 表名（列名）values(值...);

    • insert into emp(empno,ename,deptno) values(6666,’ly’,50)
    • 要求插入数据的数量顺序和表名后的列要一致

  3. insert into 表名（列名）select...

    • create table dp as select * from dept where 1<>1;
    • insert into dept(deptno,danme) select empno,ename from emp;

SQL-DML删除

  • delete from 表名

    delete from dept；

  • delete from 表名 where 条件

    delete from emp where comm is null；

  • 这属于一种物理删除，删完之后理论上不能找回

  • truncate table emp；

    截断表--不要使用--不要使用

SQL-DML修改

  • updata 表名 set 列名 = value，列名 = value

    update salgrade set losal = 888 ,hisal = 999;

  • update 表名 set 列名 = value，列名 = value where 条件

    update salgrade set losal = 666 ,hisal = 1888 where grade = 3;

数据库事务

  1. 数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据口操作系统序列构成

  2. 事务指的是数据库一种保护数据的方式

  3. 事务一般由增删改操作自动调用，事务根据数据库不同提交的时机也是不同的

    • MySQL数据库默认执行增删改就会提交事务
    • 我们可以设置为手动提交begin或者start transation；

  4. 事务的特征

    • ACID原则

    • 原子性

      事务是操作数据的最小单元，不可以再分

    • 一致性

      （1）事务提交之后，整个数据库所看到数据都是最新的数据

      （2）所有人看到的数据都是一致的

    • 隔离性

      别人无法访问到我们未提交的数据，而且一旦这个数据被我修改，别人也无法修改

    • 持久性

      （1）事务一旦被提交，数据库就进入到一个全新的状态

      （2）数据再也不能返回到上一个状态

  5. 事务如何开启和提交？

    • 开启

      （1）当我们执行增删改操作的时候就会默认开启一个事务

      （2）这个事务和当前操作的窗口有关，别人是无法共享这个事务的

    • 提交

      （1）手动

      • 显式

        commit； 提交

        rollback；回滚

      • 隐式

        执行DDL操作，会默认提交当前的事务

        用户退出，事务统一进行回滚（MySQL）

      （2）自动

      • MySQL数据库执行DML操作之后会自动地提交事务

      • 好处：方便

      • 坏处：

        不能将多个SQL纳入到一个事务，不便于管理

        当我们大批量插入到数据的时候，数据库会频繁的开启关闭事务，影响插入效率

事务的隔离级别

  1. 根据数据库的不同用途，我们可以对数据库的事务进行级别的设置

  2. 设置的级别越高，数据越安全，但是效率越低

    • 读未提交

      我们可以读取到别人未提交的数据

      有可能产生脏读的问题

    • 读已提交

      只能读到别人提交后的数据

      不能达到可重复读，但是可以避免脏读

      有可能产生虚读或者幻读的情况

    • 可重复读

      当数据被我查询后，别人就不能修改这个数据了

      说明在我查询的时候已经有事务操作到这些数据，查询会开启事务

      但是不能防止别人查询别的数据

    • 序列化

      当前数据库只能存在一个事务，但我操作数据库的时候，别人是不能访问数据库的

      这时对于用户来讲数据相当安全，一般在倒库的时候才会开启这种级别

  3. 脏读

    读取别人未提交的数据，这个数据时不安全的

  4. 虚读

    第一次读取的数据，第二次在读取的时候就可能被人修改了

  5. 幻读

    第一次读取的数据，第二次多了一条或者少了一条

四、SQL-DDL

常见组成

  • 库的操作
  • 表
  • 视图
  • 存储过程
  • 事件
  • 索引
  • 触发器

数据库的操作

# 数据库创建
create database 数据库名 charset utf8;

# 查看数据库
show databases;
show create database db;
select database();

# 选择数据库
use 数据库名;

# 删除数据库
drop database 数据库名;

# 修改数据库
alter database db1 charset utf8;

数据类型

  • 数值类型

    image-20221017202736937

  • 字符串类型

    image-20221017202822706

  • 时间、日期类型

    image-20221017202852060

表table的创建

-- 我们首先要对你操作的数据有一个基础性的了解
-- 学号 姓名 性别 出生日期 入学时间 专业 院系 创建时间
-- 学号 int 姓名 varchar 性别 char 出生日期 date 入学时间 date 专业 varchar 院系 varchar 创建时间 timestamp

create table t_student(
	sno int,
	sname varchar(40),
	gender char(1),
	birthday date,
	schooltime date,
	major varchar(255),
    department varchar(255),
    createtime timestamp
);

表table的修改

-- 根据查询语句创建表
CREATE TABLE STU01 AS SELECT * FROM t_student;
-- 添加一列
alter table t_student add updatetime timestamp default now();
-- 删除一列
alter table t_student drop column email;
-- 修改一列
alter table t_student modify COLUMN birthday to birty;
-- 修改表名
rename t_student to t_s;
-- 删除一张表
drop table t_s;

表table的约束

  1. 约束指的是我们创建的表别人在插入数据的时候，对数据的约束，而不是对创建人的约束

  2. 主键约束 PRIMARY KEY

    • 主键值必须唯一标识表中的每一行，且不能为NULL，即表中不可能存在有相同主键值的两行数据

    • 主键分为单字段主键和多字段联合主键

    • 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则

      CREATE TABLE t_pk01(
	id INT(11) PRIMARY KEY,
	name VARCHAR(255),
    deptid INT(11),
    salary FLOAT
);

CREATE TABLE t_pk03(
	tid INT(11),
    CID int(11),
    salary FLOAT,
    PRIMARY KEY(tid,cid)
);

CREATE TABLE t_pk02(
	id VARCHAR(40),
    name VARCHAR(25),
    salary FLOAT
);

-- ALTER TABLE <数据表名> ADD PRIMARY KEY (<字段名>);
ALTER TABLE t_pk02 ADD PRIMARY KEY(id);

-- ALTER TABLE <数据表名> DROP PRIMARY KEY;
ALTER TABLE t_pk04 DROP PRIMARY KEY;

  3. 唯一性约束 UNIQUE

    列中的值可以为空但是不能相同

    CREATE TABLE tb_unique01(
	id INT(11) PRIMARY KEY,
    name VARCHAR(22),
    phonenum VARCHAR(11) UNIQUE,
    location VARCHAR(50)
);

-- ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
ALTER TABLE tb_unique01 ADD CONSTRAINT t_unique01_unique_location UNIQUE(location);

-- ALTER TABLE <表名> DROP INDEX <唯一约束名>;
ALTER TABLE tb_unique01 DROP INDEX unique_t_unique01_location;

  4. 非空约束 NOT NULL

    列中的值不能为null

    CREATE TABLE tb_null01(
	id INT(11) PRIMARY KEY,
    name VARCHAR(22) NOT NULL,
    location VARCHAR(50)
);

-- ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;
ALTER TABLE tb_bull01 CHANGE COLUMN location location VARCHAR(50) NOT NULL;

-- ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
ALTER TABLE tb_nul01 CHANGE COLUMN location location VARCHAR(50) NULL;

  5. 外键约束

    （1）定义外间时，需要遵循下列规则：

    • 主表必须已经纯在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表成为自参照表，这种结构成为自参照完整性。
    • 必须为主表定义主键
    • 主键不能包含空值，但允许在外键中出现空值，也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的
    • 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或唯一性键
    • 外键中列的数目必须和主表中主键的列的数目相同
    • 外键中列的数据类型必须和主表主键中对应列的数据类型相同

    （2）外键的删除

    • 级联删除

      设置为NULL

      阻止删除

      删除主数据表的时候，要保证这个ID没有被字表所使用

    -- 1位老师对应N个学生 teacher表为主表，student表为外键表 tid为外键
CREATE TABLE teacher(
	tid VARCHAR(40) PRIMARY KEY,
    name VARCHAR(40)
);

CREATE TABLE student(
	sid VARCHAR(40) PRIMARY KEY,
    name VARCHAR(40),
    tid VARCHAR(40),
    CONSTRAINT fk_teacger)student_tid FOREIGN KEY(tid) PEFERENCES teacher(tid) ON DELETE CASCADE
);

INSERT INTO teacher VALUES('a','岳不群');
INSERT INTO teacher VALUES('b','灭绝师太');
INSERT INTO teacher VALUES('c','无崖子');

INSERT INTO student VALUES('1','令狐冲','a');
INSERT INTO student VALUES('2','岳灵珊','a');
INSERT INTO student VALUES('3','依琳','b');
INSERT INTO student VALUES('4','星宿大仙','c');
-- 错误数据，因为主表主键没有d
INSERT INTO stundet VALUES('5','任我行','d');

-- 修改表
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>)
REFERENCES <主表名> (<列名>);

  6. 约束回顾

    -- 查看表中的约束
SHOW CREATE TABLE <数据表名>;

CREATE TABLE table_name(
	列名1 数据类型 (int) PRIMARY KEY AUTO_INCREMENT,
    列名2 数据类型 NOT NULL,
    列名3 数据类型 UNIQUE,
    列名4 数据类型 DEFAULT '值',
    CONSTRAINT 索引名 FOREIGN KEY(外键列) REFERENCES 主键表(主键列) ON DELETE CASCADE | ON DELETE SET NULL)
    
1.主键约束
添加: ALTER TABLE table_name ADD PRIMARY KEY(字段)
删除: ALTER TABLE table_name DROP PRIMARY KEY
2.非空约束
添加: ALTER TABLE table_name MODIFY 列名 数据类型 NOT NULL
删除: ALTER TABLE table_name MODIFY 列名 数据类型 NULL
3.唯一约束
添加: ALTER TABLE table_name ADD UNIQUE 约束名(字段)
删除: ALTER TABLE table_name DROP KEY 约束名
4.自动增长
添加: ALTER TABLE table_name MODIFY 列名 INT AUTO_INCREMENT
删除: ALTER TABLE table_name MODIFY 列名 INT
5.外键约束
添加: ALTER TABLE table_name ADD CONSTRAINT 约束名 FOREIGN KEY(外键列) REFERENCES 主键表(主键列)
删除: ALTER TABLE table_name DROP FOREIGN KEY 约束名
6.默认值
添加: ALTER TABLE table_name ALTER 列名 SET DEFAULT '值'
删除: ALTER TABLE table_name ALTER 列名 DROP DEFAULT

索引

  1. 假如我们有1000万条记录，现在查询ename = ‘zs’的员工信息

    • 原始的数据遍历
    • 让我们查询的数据有序：可以使用折半查找法

  2. 案例

    • 拼音
    • 偏旁部首

  3. 简介

    • 在MySQL中，索引（index）也叫做”键（key）“，它是存储引擎用于快速找到记录的一种数据结构

    • 索引对于良好的性能非常关键，尤其适当表中的数据量越来越大时，索引对性能的影响就愈发重要

    • 索引优化应该是对查询性能优化的最有效手段

      -- CREATE INDEX indexname ON mytable(username);
CREATE INDEX index_dept_dname ON dept(dname);
DROP INDEX index_dept_dname ON dept;

  4. 索引的分类

    • 常规索引

      常规索引，也称普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引时使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引

    • 主键索引

      主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。

      一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键并不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

    • 唯一索引

      唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

    • 外键索引

      外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联外键的主要作用是保证记录的一致性和完整性

  5. 索引是数据库中一块独立的空间，专门存储索引值的一棵B-树

    • 我们可以通过B树快速的定位到要查询的数据

    • 尽量不要对重复列添加索引

      性别

    • 优点

      索引可以让MySQL快速地查找到我们所需要的数据，但这并不是索引的唯一作用

      索引大大减少了MySQL服务器需要扫描的数据量

      索引可以帮助服务器避免排序和临时表

      索引可以将随机I/O变为顺序I/O

    • 缺点

      影响数据库的增删改速度

试图

  1. 简介

    • MySQL视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中
    • 行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的

  2. 优点

    1）定制用户数据，聚焦特定的数据

    2）简化数据操作

    3）提高数据的安全性

    4）共享所需数据

    5）更改数据格式

    6）重用SQL语句

  3. 创建查看

    -- CREATE VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名
-- <SELECT语句>：指定创建视图的SELECT语句，可用于查询多个基础表或源视图

CREATE VIEW v_swordsman AS
( SELECT tid, NAME FROM teacher ) UNION ALL
( SELECT sid, NAME FROM student );

SELECT
	*
FROM
	v_swordsman

-- DESCRIBE 视图名：
DESCRIBE v_swordsman

-- SHOW CREATE VIEW 视图名;
SHOW CREATE VIEW v_swordsman

-- ALTER VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。改名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

-- DROP VIEW IF EXISTS <视图名1> [ , <视图名2> ...]

三范式

  1. 我们创建表所需要遵循的规范

  2. 第一范式

    • 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组

      ID Name Address
      1 迪丽热巴 新疆-乌鲁木齐
      2 黄晓明 山东-青岛
      3 黄渤 山东-青岛
      ID Name Province City
      1 迪丽热巴 新疆 乌鲁木齐
      2 黄晓明 山东 青岛
      3 黄渤 山东 青岛

  3. 第二范式

    • 数据库中每一行数据必须依赖于主键，每一个行数据都要有主键

    • 主键是一行数据的唯一性标识

      ID Name Province City Good
      1 迪丽热巴 新疆 乌鲁木齐 葡萄干
      2 黄晓明 山东 青岛 啤酒
      3 黄渤 山东 青岛 啤酒
      id sname tid tname
      1 张翠山 3 灭绝
      2 宋远桥 1 张三丰
      3 令狐冲 2 岳不群

  4. 第三范式

    • 表中非主键的列要完全依赖于主键，不能出现部分属性依赖于其他属性

    • 当出现传递依赖的时候要将非依赖于主键的列专门创建一张表进行管理

      id sname tid
      1 张翠山 1
      2 宋远桥 1
      3 令狐冲 2
      tid tname
      3 灭绝
      1 张三丰
      2 岳不群

表与表的关系

但是为了维护两张表的关系，但后根据两张表的对应关系可以分为：

  1. 1:1-->

    • 两张表中的数据1条只和1条对应

    • 关联方案

      （1）让关联数据主键值相同

      （2）在一张表中存放另一张表的主键

  2. 1:N-->

    • A表中的一条数据有可能对应B表中的多条记录

    • 1老师：N学生

    • 关联方案

      在N方的表中设计一个1方的主键列，也称之为外键关联

  3. N:N-->

    • A表中的一条数据有可能对应B表中的多条记录

    • 同时B表中的一条数据有可能对应A表中的多条记录

    • 1学生：N课程

    • 1课程：N学生

    • 关联方案

      S学生表 C课程表

      一般都会创建第三张表专门管理学生与课程的关系

      ​ sid，cid 作为联合主键管理信息

      ​ id，sid，cid 以ID作为这张表的单独主键

五、存储过程

创建调用删除

-- 删除存储过程
drop procedure if exists p_hello_world;

-- 创建存储过程
create procedure p_hello_worle()
begin
	select sysdate();
end;

-- 调用存储过程
call p_hello_world();

-- 删除存储过程
drop procedure if exists p_hello_world;

-- 创建存储过程
create procedure p_hello_world(in v_emono int)
begin
	select * from emp e where e.empno = v_empno;
end;

-- 调用存储过程
call p_hello_world(7788);

变量定义与赋值

-- 删除存储过程
drop procedure if exists p_hello_world;

-- 创建存储过程
create procedure p_hello_world()
begin
	declare v_number int;
	declare v_varchar varchar(32);
	set v_number = 1;
	set v_carchar = 'hello world';
	
	select v_number;
	select v_varchar;
end;

-- 调用存储过程
call p_hello_world();

选择语句

-- 删除存储过程
drop procedure if exists p_hello_world;

-- 创建存储过程
create procedure p_hello_wrold(in v_id int)
begin
	if (v_id > 0) then
		select '> 0';
	elseif (v_id = 0) then
		select '> 0';
	else
		select '= 0';
	end if;
end;

-- 调用存储过程
call p_hello_world(-9);

循环语句

drop procedure if exists p_while_do;

create procedure p_while_do()
begin
	declare i int;
		set i = 1;
		while i <= 10 do
			select concat('incex : ',i);
			set i = i + 1;
		end while;
end;

call p_while_do();

drop procedure if exists p_for_loop;

create procedure p_for_loop()
begin
	declare i int;
		set i = 1;
		loop_example : loop
			select concat('incex ->', i);
			set i = i + 1;
			
			if  i > 10 then
				leave loop_example;
			end if;
		end loop;
end;

call p_for_loop;

静态游标

-- 删除存储过程
drop procedure if exists p_hello_world;

-- 创建存储过程
create procedure p_hello_world()
begin
	declare empno intefer;
	declare ename varchar(256);
	declare result varchar(4000) default '';
	declare cursor_emp cursor for select e.empno,e.ename from emp e;
	declare continue handler for SQLSTATE '02000' set empno = null;
	open cursor_emp;
	fetch cursor_emp into empno,ename;
	while (empno is not null) do
		set result = concat(result, 'empno:',empno,',ename:',ename,';');
		fetch cursor_emp into empno,ename;
	end while;
	close cursor_emp;
	select result;
end

-- 调用存储过程
call p_hello_world();

六、触发器

简介

触发器与数据表关系密切，主要用于保护表中的数据

特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据一致性

在MySQL中，只有执行INSERT 、UPDATE 和 DELETE 操作时才能激活触发器

优缺点

  • 优点

    触发器的执行是自动的，当对触发器相关表的数据做出相应的修改够立即执行

    触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作

    触发器可以实现表数据的级联更改，在一定程度上保证了数据完整性

  • 缺点

    使用触发器实现的业务逻辑在出现问题时候很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难

    大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性

    如果需要变动的数据量较大时，触发器的执行效率会非常低

类型

  • INSTERT 触发器

    在 INSERT 语句执行之前或之后响应的触发器

    在 INSERT 触发器代码内，可引用一个 NEW （不区分大小写）的虚拟表来访问被插入的行

    在 UPDATE 触发器代码内，可引用一个 NEW（不区分大小写）的虚拟表来访问更新的值

  • UPDATE 触发器

    在 UPDATE 触发器代码内，可引用一个名为 NEW （不区分大小写）的虚拟表来访问更新的值

    在 UPDATE 触发器代码内，可引用一个名为 OLD （不区分大小写）的虚拟表

  • DELETE 触发器

在 DELETE 触发器代码内，可以引用一个名为OLD（不区分大小写）的虚拟表来访问被删除的行

OLD中的值全部都是只读的，不能被更新

  • 对于事务性表，如果出发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚
  • 对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做任何更改依然有效

使用

  1. 语法

    CREATE TRIGGER <触发器名> < BEFORE | AFTER >
< INSERT | UPDATE | DELETE >
ON <表名> FOR EACH ROW <触发器主体>

    1）触发器名

    触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在特定的某个数据库中创建，名称前面应该加上数据库的名称

    2）INSERT | UPADTE | DELETE

    触发事件，用于指定激活触发器的种类

    注意：三种触发器的执行时间如下

    INSERT：将新行插入表时激活触发器. 例如，INSERT 的 BEFORE 触发器不仅能被 INSERT 语句激活，也能被 LOAD DATA 语句激活

    DELETE：从表中删除某一行数据时激活触发器. 例如，DELETE 和 REPLACE 语句

    UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句

    3）BEFORE | AFTER

    BEFORE 和 AFTER ，触发器被触发的时刻，表示触发器是在激活他的语句之前或之后触发

    若希望验证新数据是否满足条件，则使用 BEFORE 选项

    若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项

    4）表名

    与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来

    在该表上触发事件发生时才会激活触发器，同一个表不能拥有两个具有相同触发时刻和事件的触发器

    5）触发器主体

    触发器动作主体，包含触发器激活时将要执行的 MySQL 语句，如果要执行多个语句，可使用 BEGIN...END 复合语句结构

    6）FOR EACH ROW

    一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作

  2. 创建 BEFORE 触发器

    -- 创建，每次将新增的薪资记录到一个局部变量中
CREATE TRIGGER tr_salsum
BEFORE INSERT ON emp
FOR EACH ROW 
SET @sum = @sum + NEW.sal;

-- 创建完成后，可以在设计表对应的触发器处看到

-- 查看触发器的作用
SET @sum = 0;
INSERT INTO 'emp' VALUES ('9999','MILLER','CLERK','7782','1982-01-23','1300',NULL,'10');
SELECT @sum;

  3. 创建 AFTER 触发器

    -- 首次创建一张相同的表
CREATE TABLE dept_copy AS SELECT * FROM dept;

-- 向Dept插入数据的时候，也会向复制表中插入数据
CREATE TRIGGER tr_chinaloc
AFTER INSERT ON dept
FOR EACH ROW
INSERT INTO dept_copy
VALUES ( NEW.deptno , NEW.dname , concat( 'china-' , NEW.loc ));

-- 测试
INSERT INTO dept VALUES (88,'shop','shanghai');

查看

在 MySQL 中，若需要查看数据库中已有的触发器，则可以使用 SHOW TRIGGER 语句

-- 简单模式
SHOW TRIGGERS;

-- 指定触发器
SELECT * FROM information_schema.triggers WHERE trigger_name = 'tr_chinaloc';

修改删除

  • 修改触发器可以用过删除原触发器，再以相同的名称创建新的触发器
  • 删除一个表的同时，也会自动删除该表上的触发器
  • 触发器不能更新或覆盖，为了修改一个触发器，必须删除它，再重新创建
-- DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>
DROP TRIGGER IF EXISTS tr_chinaloc;

七、事件

简介

事件（event）是 MySQL 在相应的时刻调用的过程式数据库对象，一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”

事件取代了原先只能由操作系统的计划任务来执行的工作

MySQL 的事件调度器可以精确到每秒钟执行一个任务

操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次

开启事件

查看是否开启

-- 是否开启
SHOW VARIABLES LIKE '%event_scheduler%';

-- 如果是关闭的（临时开启）
SET GLOBAL event_scheduler = ON;

-- 如果是关闭的（长久开启）
-- 修改my.ini中[mysqld]数据 C:\ProgramData\MySQL\MySQL Server 8.0
event_scheduler = ON;

创建事件

  • 一条 create event 语句创建一个事件

  • 每个事件由两个主要部分组成

    第一部分是事件调度（event schedule），表示事件何时启动以及按什么频率启动

    第二部分是事件动作（event action），这是事件启动时执行的代码

  • 一个事件可以是活动（打开）或停止（关闭）的

    活动意味着事件调度器检查事件动作是否必须调用

    停止意味着时间的声明存储在目录中，但调度器不会检查它是否应该调用

  • 语法

    CREATE
	[DEFINER = { user | CURRENT_USER }]
	EVENT
	[IF NOT EXISTS]
	event_name
	ON SCHEDULE schedule
	[ON COMPLETION [NOT] PRESERVE]
	[ENABLE | DISABLE | DISABLE ON SLAVE]
	[COMMENT 'comment']
	DO event_body;
	
schedule:
	AT timestamp [+ INTERVAL interval] ...
	|
	EVERY interval
	[STARTS timestamp [+ INTERVAL interval] ...]
	[ENDS timestamp [+ INTERVAL interval] ...]
	
interval:
	quantity { YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND }

DEFINE：定义事件执行的时候检车权限的用户
event_name：表示事件名称
SCHEDULE：表示触发点
AT timestamp：一般用于只执行一次
EVERY interval：一般用于周期性执行，可以设定开始时间和结束时间
ON COMPLETION PRESERIVE：表示任务执行之后保留
ON COMPLETION NOT PRESERIVE：表示任务执行之后不保留
ENABLE | DISABLE：表示设置启用或禁止这个事件
COMMENT：添加注释

  • 案例

    -- 立即启动事件
CREATE EVENT event_now
ON SCHEDULE
AT now()
DO INSERT INTO event_list VALUES('event_now',now());

-- 5秒钟后自动清空表
CREATE EVENT IF NOT EXISTS event_truncate_dept_copy
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND
DO TRUNCATE TABLE dept_copy;

-- 每秒钟启动事件
CREATE event event_minute
ON SCHEDULE
EVERY 3 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 5 day
ENDS CURRENT_TIMESTAMP +INTERVAL 1 month
ON COMPLETION PRESERVE
DO INSERT INTO test2(department,time_v) VALUES('1',NOW());

-- 每秒钟调用存储过程
CREATE DEFINER = 'root'@'localhost' EVENT 'eventUpdateStatus'
ON SCHEDULE
EVERY 1 SECOND
START '2017-11-21 00:12:44'
ON COMPLETION PRESERVE
ENABLE
DO call updateStatus()

  • 启用禁用

    alter event event_name disable;
alter event event_name enable;

  • 删除

    drop event [if exists] event_name
posted @ 2022-10-23 14:22  熬夜不早睡  阅读(117)  评论(0)    收藏  举报