mysql

数据库

DBMS数据库管理系统(C/S架构)

• 数据库DB1

  • 表1

    • 存储方式: 表的一行称为记录,在java对应一个对象

  • 表2…

• 数据库DB2

• 数据库DB…

• 网络程序在3306监听

客户端

• 用来操作数据库: web程序,用java JDBC操作mysql数据库

SQL

DDL

• 数据定义语句: 创建表,库

  • 字符集: 默认utf8 校对规则(collate),utf8_bin区分大小写,utf8_general_ci不区分大小写, 默认是utf8_general_ci 表默认使用数据库的, 表的列默认使用表的, 这个三个都可以单独设置, 修改不影响之前设置的规则

  • DB

    • 查看DB

      show databases
      show create databases db_name
      show create database db01;
      

    • 创建DB

      1
      2
      create database [if not exists] db_name
      create database if not exists db03 character set utf8 collate utf8mb3_bin
      

    • 删除DB

      1
      drop database if exists db03;
      

  • table

    • 查看表

      1
      2
      3
      show tables //查看数据库所有表
      show create table user;// 查看表定义
      desc table_name
      

    • 创建table

      create table table_name(
      field1 datatype,
       field2 datatype, ...
      ) character set 字符集 collate 校对规则 engine 存储引擎
            
      field:列名
      datatype:数据类型
      character set 字符集 如果不指定默认为数据库的字符集
      collate:如过不指定默认为数据库的字符集
      engin: Innodb支持事物
      

      • 自增长

        • id int primary key auto_increment,

        • 一般自增长配合primary key来使用

        • 自增长也可以单独使用, 但是需要一个unique

        • 自增长默认从1开始, 也可以通过 table tbl_name auto_increment = 新的开始值

        • 如果添加数据指定值, 则以指定值为准, 如果指定了自增长, 就按自增长的规则

    • 修改表

      • 添加列

        alter table table_name ADD (column datatype [default exper] [, column datatype]...);
        

      • 修改列

        alter table table_name MODIFY (column datatype [default exper] [, column datatype]...);
        

      • 修改列名

        alter table employee change column old_name new_name datatype)
        

      • 删除列

        1
        alter table table_name DROP (column);
        

      • 修改表名

        rename table old_table_name to new_table_name
        

      • 修改字符集

        alter table table_name character set charset_name
        

    • 删除表

      drop table tbl_name
      

    • 复制表/常用于备份表

      • 自我复制

      • 复制其他表的数据结构

        create table user_bak like user;
        insert into user_bak select * from user;
        

      • 如何对一张表进行去重

# 去除一张表重复的记录 创建一张新表
create table emp_bak_2025_2_15_13_07 like emp;
# 复制旧表数据到新表时, 用distinct 关键字去重
insert into emp_bak_2025_2_15_13_07 select distinct * from emp;
# 清空又重复数据的表
delete from emp;
# 复制去重的数据到原表
insert into emp select * from emp_bak_2025_2_15_13_07;
# 删除临时表
drop table emp_bak_2025_2_15_13_07;

DML

• 数据操作语句:insert,delete,update

  • insert

    • 指定列insert into table_name(column1,column2,…) values(val1,val2,…)

      或者不指定列,插入所有字段 insert into table_name values(val1,val2,..)

    • 细节

      • 1插入的数据类型应该和字段的数据类型相同

      • 2数据的长度不要超过字段定义的返回

      • 3values的值的列表应该和定义的字段顺序一致

      • 4字符和日期数据类型应该用单引号引起来

      • 5列可以插入空值,只要约束非not null

      • 6添加多条插入 insert into table_name(col1,col2,..) values(val1,val2,..),(val1,va2,..), …, ;

      • 7.如果是给表中的所有字段添加数据,可以不用写前面的字段名称

      • 8默认值的使用, 如果field设置为not null, 插入时确没有设置值, 则会报错 如果field可以为null则,插入时没有赋值,默认值就是null, 一般not null需要给配置一个默认值

  • update

    • update tab_name set column1 = val1, column2 = val2 ,… where id = 1 没有where条件会对所有记录进行修改

  • delete

    • delete from tbl_name where id = 1 没有where条件会删除整张表中记录

DQL

• 数据库查询语句: select

  • SELECT [DISTINCT] *

    {column1, column2, column3..} from tbl_name

    • distinct 查询结果的每个字段都相同才去重

  • where子句中常用运算符

    • 比较运算符

      • > < >= <= = <> !=

      • between a and b 是[a,b]闭区间

      • in (e1,e2,…)

      • like ‘模糊匹配’

        • %匹配任意个字符 _匹配一个字符

      • is null 判空

    • 逻辑运算符

      • and

      • or

      • not

  • order by 子句

    • order by col1 desc, col2 asc, col3 desc, …

    • order by 指定排序的列, 可以是列名, 也可以是查询结果列

    • ASC升序(默认), DESC降序

    • order by位于select语句末尾

  • 统计函数(sum,avg,max/min只对数值起作用)

    • count

      • select count(*)

        count([distinct] 列名) from tbl_name where condition

      • 返回总行数

      • count(*) 和count(列名)区别 : * 返回总记录数 count(列名): 返回总记录数, 排除列的值为null的

    • sum

      • select sum(col1),sum(col2),.. from tbl_name where condition

      • sum仅对数值起作用

    • avg

    • max/min

  • 分组子句group by 和having子句

    • select col1,col2,…from tbl_name group by column having ….

    • group by分组, having对分组的结果过滤

    • 确保 SELECT 子句中的所有非聚合列都包含在 GROUP BY 子句中，或者对这些列进行聚合

  • 分页查询

    • limit 每页显示记录数*(第几页-1), 每页显示记录数

  • sql顺序

    • group by having > order by >limit

• 多表查询语句

  • 笛卡尔集:多表查询的结果就是笛卡尔集, 要获取正确的结果, 需要利用where语句进行过滤

  • 多表查询条件不能少于表的个数-1,否则会出现笛卡尔积集

  • 自连接

    • 在同一张表上进行连接查询,将同一张表当作两张表来查询

  • 子查询

    • 在其他sql中嵌入其他sql语句中select语句

      • select * from emp where emp_id in (select distinct id from emp where sal > 2000)

    • 可以将子查询当作一张临时表

      # 查询所有商品类别中价格最高商品, 其中利用了子查询作为了一张临时表来提供类别最大价格的数据,然后外层通过连接子查询临时表来获取结果
      select goods_name, goods.shop_price
      from ecs_goods goods,
       (select t.cat_id, max(t.shop_price) as max_price from ecs_goods t group by t.cat_id) temp
      where goods.cat_id = temp.cat_id
      and goods.shop_price = temp.max_price;
      

    • all和any

      # 显示比部门30的所有员工工资都高的员工信息
      select * from emp t2 where t2.sal > all (select t.sal from emp t where t.deptno = 30);
      

      # 显示比部门30的任意一个员工工资都高的员工信息
      select * from emp t2 where t2.sal > any(select t.sal from emp t where t.deptno =30);
      

    • 多列子查询

      • (字段1,字段2,..)=(select 字段1, 字段2,.. from)

        # 查询作弊的学生, 学生各科成绩完全和学生'宋江'一样
        select *from student s 
        where (s.chinese, s.math, s.english) = (select chinese, math, english from student where name = '宋江');
        

      • 每个部门最高工资的人 多列子查询

        select deptno,max(sal) max_sal from emp group by deptno;
        # 方法1
        select * from emp
        where (deptno, sal) in (select deptno, max(sal) max_sal from emp group by deptno);
        # 方法二, 子查询作为临时表
        select * from emp t,
             (select deptno, max(sal) max_sal from emp group by deptno) temp
        where temp.deptno = t.deptno
          and temp.max_sal = t.sal;
        

合并查询

​ union 不包括重复的

mysql连接查询语法

内连接: 驱动表和被驱动表都有记录才显示

​ 等价语法

​ select … from tab1, tab2, …

​ inner join on

​ join on

​ cross join on

外连接

​ 等价语法

​ 左外连接

​ left join

​ left outer join

右外连接/与左一样

驱动表有,被驱动表没有的记录时, 显示驱动表 + 被驱动表null

DCL

• 数据控制语句: 管理数据库, grant, revoke(详见mysql用户与权限)

mysql函数

字符串相关函数

• charset(str)返回字符集

• concat(string,..)连接字符串

• instr(string,substring)返回substring在string出现的位置,没有返回0

• ucase:转换成大写

• lcase转换成小写

• left(string, len)从string的左边取len个字符串

• length(string)string长度,按字节

• replace(str, new_str,old_str),用new_str替换str中的old_str

• strcmp(str1,str2)比较两个字符串大小,字典顺序 substring(str, position,len), 从str的position开始(从1开始计算),去len长度

• ltrim(string),rtrim(string),trim(string) 去除对应的空格

数学函数

• abs(num) 绝对值

• bin(十进制数)将十进制数转换二进制数

• celling(num),向上取整,得到比num大的数

• conv(n1, from_base, to_base) 进制转换

• floor(num)向下取整,得到比比num2小的整数

• format(number,decimal_places)保留小数位数(四舍五入)

• HEX(Decimal_number)转换成十六进制

• least(num1,num2,num3,…)求最小值

• MOD(numberator, denominator), 求余

• RAND([seed]) 返回一个随机浮点值,范围在0-1之间[0,1]闭区间,若他指定一个整数参数N,则他被用作种子值, 用来产生重复序列

日期日期函数

• current_date() 返回当前时间

• current_time()当前时间

• current_timestamp当前时间戳

• date(datetime)返回datetime的日期部分

• date_add(date1, interval_d_value_type) 在date2中加上日期或者时间

• date_sub(date1, interval_d_value_type) 在date2中减去日期或者时间

• datediff(date1,date2) 两个日期相差天数

• Timesdiff(date1,date2)两个时间差, (多少小时,多少分,多少秒)

• now() 当前时间

• Year

  Month

  Date(datetime)年月日

• From_unixTime()

  • 把unix时间戳转换为指定格式的日期

• unix_timestamp()

  • 1970-1-1到现在的秒数

加密和系统函数

• user()查询用户

• database()数据库名称

• MD5()为字符串计算一个32位的字符串,加密

• PASSWORD(str)从原文密码str计算并返回字符串,通常用于对mysql数据库的用户密码加密

流程控制函数

• if(expr1,expr2,expr3)

  • 如果expr1为真,返回expr2,否则返回expr3

    • 类似java中的三元运算符

• ifnull(expr1,expr2)

  • 如果expr1不为为null,则返回expr1,否则返回expr2

• case when expr1 then expr2 when expr3 then expr4 else expr5 end; 类似于多重分支

约束

主键primary key(主键)

• 用于唯一标识记录的数据, 当定义主键后, 该字段不能重复

• 主键不能重复且不能为null

• 一张表只能有一个主键,但可以是复合主键

• 使用方式

  • 直接在字段名后指定 字段名 primary key

  • 在表的定义的最后写 primary key(字段名)

not null

• 插入数据时不能为null, 或者提供了默认值

unique

• 定义了唯一约束后, 该列值不能重复

• 如果给没有指定not null 则unique字段可以有多个null

• 一张表可以有多个unique字段

• unique + not null 效果相当于主键

• 会创建一个索引

foreign key

• 使用案例:foreign key (stu_id) references tbl_father(id)

• 子表和父表

  • 如果A表中的某个列或者某些列依赖表B, 那么A表就称为B表的子表

• 外键: 子表中依赖父表的列可以设置外键约束

  • 当向子表插入数据时, 会在父表中检查一下外键在父表中是否存在

• 父表中被子表依赖的列表必须建立索引,unique和主键默认有索引

• innodb引擎才支持外键

• 外键字段要和父表主键字段一致

• 一旦建立外键后数据, 父表数据就不能随便删除了

• 外键字段可以设置not null约束,默认可以为null

check

• 使用例子 gender varchar(8) check ( gender in (‘m’,’f’)), sal double check ( sal > 1000 and sal < 2000 )

• 用于强制进行数据必须满足的条件, 假定在sal列上定义了check约束,并且要求sal值在1000-2000之间如果不再1000-2000之间就会提示出错

• mysql5.7还不支持,只做语法校验, 但不会生效 mysql 8.0生效了

• 其他数据库支持check, oracle, sqlserver

索引

实例

#查看查询的时间 ,太慢了
select * from emp where empno = 1234567; -- 2s
#使用索引来优化
create index empno_index on emp(empno);
select * from emp where empno = 1234567; -- 51ms,提升了400倍

创建索引 emp.ibd 512mb, 创建后 640mb, 创建有空间开销

创建的索引后, 只对创建索引的列有效 emp.ibd 640mb –> 808mb

select * from emp where ename = 'HQfdaS'; -- 2s --> 55ms
create index enameno_index on emp(ename);

删除索引

drop index empno_index on emp;

索引原理

• 没有索引为什么慢? 因为全表扫描,线性搜索

• 索引为什么快? 形成一个索引的数据结构, 比如B+树

• 索引代价

  • 磁盘占用

  • dml(update,insert,delete)变慢

  • 当然select使用率更高

• 索引类型

  • 主键索引 主键自动为主索引

  • 唯一索引 unique

  • 普通索引 index

  • 全文索引 fulltext 适用于MyISAM

  • 一般开发, 不使用mysql自带的全文索引, 而使用全文搜索solr和elasticsearch

使用索引

• 添加索引

  • create [unique] index index_name on table_name(col_name)

    • unique索引等价于唯一约束

  • alter table tbl_name add index index_name (col_name)

  • 添加主键: alter table tbl_name add primary key (col_name)

• 删除索引

  drop index index_name on tbl_name
  alter table tbl_name drop index index_name
  #删除主键:
  alter table tbl_name drop primary key
  

• 查询索引

  show index(es) from tbl_name
  show keys from tbl_name
  desc tbl_name
  

什么时候创建索引

• 使用频繁的作为查询字段应该创建索引 select * from emp where empno = 1

• 唯一性太差的字段不适合创建索引, 即使频繁作为查询条件

• 更新非常频繁的字段不适合创建索引

• 不会出现在where子句中的字段不应该创建索引

mysql事务

事物用于保存数据的一致性, 他有相关dml语句组成, 该组的dml语句要么全部成功, 要么全部失败, 比如转账的例子

事务和锁

• 当执行(事务操作时)dml语句, mysql会在表上加锁, 防止其他用户来修改数据

mysql控制事务的重要操作

• start transaction 开启事务

• save point 设置保存点

  • rollback前面的保存点, 会自动删掉后面的保存点 commit后会删除保存点

• rollback to 保存点名称

  • 回退事务至保存点

• rollback

  • 回退所有事务

• commit

  • 提交事务, 不能回退

    提交事务, 删除保存点,释放锁数据生效,其他会话可以看到事务提交后的数据,数据正式生效

• 事务细节

  • 1.默认情况下, dml操作是自动提交的, 不能回滚

  • 2.如果开始一个事务,没有创建保存点, 可以执行rollback, 默认就是会退到事务开始的状态

  • 3.可以在事务中创建多个保存点

  • 4.在事务没有提交前, 可以选择回退到指定的保存点

  • 5.msyql点事务机制需要innodb的存储引擎才可以使用

  • 6.开始一个事务 start transaction 或者 set autocommit =off

mysql事务的隔离级别

• https://zhuanlan.zhihu.com/p/117476959

• 1.多个连接开启各自的事物操作数据库中数据时, 数据库系统要负责隔离操作, 以保证各个连接在获取数据时的准确性 2.如果不考虑隔离性,可能会引发: 脏读, 不可重复读, 幻读

• 脏读

  • 定义: 一个事务读取了另一个未提交事务修改的数据。

  • 场景:

    事务 A 修改了某行数据（未提交）。

    事务 B 读取了该行数据。 事务 A 回滚了修改，

    导致事务 B 读到了无效的“脏数据”。

  • 解决: READ COMMITTED 及以上

• 不可重复读

  • 定义: 同一事务内，多次读取同一行数据，结果不一致（因其他事务修改并提交了该行数据）

  • 场景:

    事务 A 第一次读取某行数据。

    事务 B 修改该行数据并提交。

    事务 A 再次读取该行数据，结果不同。

  • 解决: REPEATABLE READ 及以上

• 幻读

  • 定义: 同一事务内，多次查询同一范围的数据，结果集的行数不同（因其他事务插入或删除了符合该范围的数据并提交）

  • 场景:

    事务 A 第一次查询某个范围的数据。

    事务 B 插入或删除了符合该范围的数据并提交。

    事务 A 再次查询，发现结果集中多了或少了行（“幻影行”）。

  • 解决: SERIALIZABLE 隔离级别或者MySQL 的 REPEATABLE READ 结合间隙锁（Gap Locks）避免幻读。

• msyql的四种隔离级别

  • 读未提交

    • 存在问题: 脏读,不可重复读,幻读

      • 不加锁

  • 读已提交

    • 存在问题:不可重复读,幻读

      • 不加锁

  • 可重复读(默认)

    • 存在问题:幻读

      • 不加锁

  • 可串行化

    • 无

      • 加锁

• 查看事务隔离级别

   select @@transaction_isolation
  

• 设置事务隔离级别

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  

mysql的锁

1.SELECT … FOR UPDATE 是显式加锁机制，用于保护现有行的修改权

2.其他事务无法对被锁定的行执行修改（如 UPDATE、DELETE）或加其他排他锁。

3.必须配合事务使用

4.若查询未使用索引，可能升级为 表锁（InnoDB 会退化为锁全表)

BEGIN;
SELECT * FROM orders WHERE user_id = 100 FOR UPDATE; -- 锁定 user_id=100 的订单行
UPDATE orders SET status = 'paid' WHERE user_id = 100;
COMMIT;

间隙锁: 间隙锁 是 InnoDB 在 REPEATABLE READ 下自动加的锁，用于防止幻读

事务ACID

• Atomicity原子性

  • 要么同时成功, 要么同时失败, dml操作是一个整体

• Isolation隔离性

  • 多个用户并发访问数据库时, 数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰, 多个并发事务之间要相互隔离

• durability持久性

  • 事务一旦提交, 对数据改变就是永久的

• Consistency一致性(前三种特性的结果)

  • 事务必须是数据从一个一致性状态转移到另一个一致性状态, 比如: 转账时, 钱的总数保持不变

mysql备份/恢复数据库

备份

1
2
#在终端执行
mysqldump -u username -p  数据库1 数据库2 数据库n > 文件名.sql

恢复

在mysql命令行内执行: source 备份的sql文件名.sql

mysql数据类型

• 数值类型(默认有符号,可以指定无符号的)

create table t4(
    id tinyint unsigned
);

• 整数类型

• bit(bit_len)位类型,指定位数,默认1,范围1-64, 只表示正数

• tinyint 1byte

• smallint 2byte

• mediumint 3byte

• int 4byte

• bigint 8个字节

• 浮点类型

  • float 单精度4byte

  • double 双精度4byte

  • decimal[M, D] 大小不确定, 由你指定

    • M（精度）：总位数，范围是 1 到 65。 D（标度）：小数位数，范围是 0 到 30，且必须小于或等于 M。 不指定M,D默认是 DECIMAL(10, 0)

字符串类型

• char 0-255字符

  固定大小,字符不够尾部补充空格,默认大小是char(1),范围是char(0-255)

  缺点: 空间浪费

• varchar 0-65536字节 utf8最大65536-3/-3字符(预留字节用来记录字符串长度) 我使用的utf8mb4,所以varchar最多保留(65536-4)/4=16383个字符 gbk 65535-3/2字符

  • varchar是变长的, 按照实际存储的字符串长度来分配空间, varchar需要额外1-3个字节来记录长度

• text 0-65535

• longtext 0-2^32-1

• 最佳实践

  如果数据定长,使用char md5编码, 手机号, 身份证 如果字段长度不确定,使用varchar 查询速度 char > varchar

  存放文本时,也可以使用text数据类型,可以将text视为varchar,注意text不能有默认值,大小为0-65536 如果希望存更多的字符, 可以使用mediumtext 0-2^24, 或者longtext 0-2^ 32

二进制类型

• blob 65535 longblog 2^32

日期类型

• data 年月日 对应java.sql.Date

• time 时分秒 java.sql.time

• datetime 年月日时分秒:YYYY-DD-MM HH:MM:SS 对应 java.sql.Timestamp

• timestamp(时间戳) 可用在在insert,update自动更新时间

  create table t14(
  birthday date,
  job_time datetime,
  login_time timestamp not null default current_timestamp on update current_timestamp
  );
  

• year

• datetime和时间戳(timestamp)的区别

  字节数不同, timestamp更短, 但是支持范围为2038年, 会存在千年虫问题, 所有尽量使用datetime, 他也支持自动更新为当前时间戳

满足需求的情况下, 尽量选择占用空间小的

mysql数据类型和java数据类映射

常见问题

• int, int(10)和int(11)的区别

  • 正常来说，没有区别。后面带的数字，并不是长度的概念。Int 类型就是占用4个字节（32位）， Int(1), int(10) 都一样是4个字节/32位，数据范围也还是 -2^31 ~ 2^31-1 为了做编号什么的，保持一致长度，不够位数就前面补0的这种情况，就可以带数字来指定“长度”了。但这种场景下，要配合 zerofill 一起使用才可以

mysql安装

参考官网

命令行连接

1
mysql -uroot -pPASSWORD

安装mysql community server, 默认服务端口是3306

安装连接客户端

• navicat

• sqlyog

• datagrip

存储引擎

mysql的表类型是由存储引擎决定的主要包括MyISAM,innoDB,Memory

MySQL数据库表主要支持六种类型分别是CSV,Memory,ARCHIVE,MRG,MYISAM,InnoDB

这几种只有InnoDB是事务安全性的

MyISAM

• 不支持事务,外键,但是访问速度快,对事务完整性没有要求

InnoDB

• 支持事务,外键,行级锁
• InnoDB存储引擎支持事务: commit,rollback,但是比起MyISAM,innodb的写的效率差一点, 并且会占用更多的内存,来保留数据和索引

Memory

• 在内存中数据库, 访问快, 使用hash索引, 但是mysql关闭, 数据就会丢失, 但表的结构还在

修改存储引擎

alter table tbl_name engine = egine_name

查看存储引擎

show engines 

如何选择存储引擎

• 如果不需要事务,只处理基本的crud,那么使用MyISAM引擎, 速度快,

• 需要事务,只能选InnoDB

• memory, 内存数据库,速度极快, 服务器重启后数据消失(经典用法, 用户在线状态), 对于数据量大频繁修改的数据可以放在缓存数据库中

视图

视图是一个虚拟表, 其内容由查询定义. 同真实表一样, 视图包含列, 其数据来自对应的真实表(基表)

通过视图可以修改基表的数据, 基表的改变,也会影响到视图的数据

可以在视图上再创建视图

视图更新的条件

• 如果一个视图是可更新的==>那么这个视图中每一条记录必须与底层表中每一条记录是一一对应的, 否则该视图不可更新

• 如果生成视图的语句包含了汇总函数(count,min,max,avg), distinct, group by having, union, union all,放在查询列表的子查询, 那么视图不可更新

视图的使用

• 创建视图

  create view view_name AS select语句
  

• 查看视图

  -- (可见视图相当一张表)
  show tables
  -- (创建视图时的sql)
  show create view emp_view
  

• 删除视图

  drop view view_name
  

mysql用户与权限

用户

• mysql所有用户都放在mysql库中user表中

  • user表重要字段 host :允许登陆的位置,localhost表示本机,也可以指定ip地址 user 用户名 host + user形成组合主键 authentication_string 密码

• 创建用户

  • 创建用户和密码

    create user '用户名'@‘登陆ip’ identified by '密码‘
    

• 删除用户

  drop user ‘用户名’ @ ‘允许登陆ip’;
  

• 修改用户密码

  • 修改自己密码 set password = password(‘密码’) 修改他人密码 set password for ‘用户名’@‘登陆ip’ = password(‘密码’) 或者 alter USER ‘用户名’@‘登陆ip’ identified by ‘新密码’

• 细节

  • 创建用户时,如果不指定host, 则为%,表示所有ip都有连接权限

  • 也可以创建用户只能在特定ip段登录

  mysql create user ‘xxx'@’192.168.1.%‘ 表示在192.168.1.*ip段可以登陆mysql

  • 如果在删除用户时,host不是%,则需要明确指定 用户@host

权限

• 权限名称

  • all alter, 修改表,库 create, 创建表库, insert, delete,select,update, …一大堆

• 授权语法

  • 授权

      grant 权限列表 on 库.表名 to  '用户名'@‘登陆ip’ [identified by ‘密码’]
  

• 例子: grant select on … grant select,delete,create on .. grant all [privileges] on … // 表示该用户在该对象的所有权限 .表示系统中所有数据库对象,表,视图,存储过程 库.*表示该库内所有数据对象(表,视图,存储过程) identified可以省略, 也可以加上, 如果用户存在就是修改密码, 如果不存在就是创建用户

  • 撤销权限

    revoke  权限列表 on 库.对象 from 用户名'@‘登陆ip’
    

  • 刷新权限:flush privileges ;

允许远程登录

• 登录语法

  mysql -h 192.168.5.116 -P 3306 -u root -p123456 
  

  在mysql库的user表中中修改root的host为’%’==允许root远程登录,

  修改后刷新权限: flush privileges ;

存储程序(批处理)

存储例程

stored routine

存储函数stored function

• 变量

  • 用户自定变量(命令行中)语法

    @varname = 值
    

  • 在会话关闭后自动销毁或者置为NULL

  • 用户变量也可以在函数体中去调用修改,也可以在函数体中去创建用户变量,然后可以在函数体外访问定义变量

    set @a = 10;
    set @b = 10.5;
    

赋值

set @b = @a;

用into将查询结果赋值给变量

select count(*) from emp into @a;
select max(sal),min(sal) from emp into @a,@b;

查看变量值

select @a,@b;

函数体内的局部变量

declare var1,var2,...  数据类型 [default value]

变量未设值时,默认值为null 在存储函数体中,declare语句必须放在其他语句的前面

#声明变量
declare var1,var2 int default 0;
#定义函数
create function fun_name([参数列表])
returns 返回值类型
begin
     函数体内容
end
# 设置分割符为$$, 替代分号;默认分割符
delimiter $$

定义计算平均数学成绩的函数

create function avg_math_score()
returns float
begin
    return (select avg(math) from student );
end $$

重新改回分割符为;

delimiter ;

调用函数

select avg_math_score();

查看函数

# 查看函数定义了多少函数
show function status [like 需要匹配的函数名];

查看函数的定义

show create function avg_math_score;

删除函数

drop function avg_math_score;

选择语句

# if语句
create function condition_demo(i int)
    returns varchar(20)
begin
    declare result varchar(20);
    if i = 1 then
        set result = 'result is 1';
    elseif i = 2 then
        set result = 'result is 2';
    elseif i = 3 then
        set result = 'result is 3';
    else
        set result = 'invalid!';
    end if;
    return result;
end;

循环语句

#while循环
# 计算 1~n的累加和
create function sum_all(n int unsigned)
    returns int
begin
    # 声明sum变量,初始值0
    declare sum int default 0;
    # 声明累加变量i, 初始值1
    declare i int unsigned default 1;
    while i <= n
        do
            set sum = sum + i;
            set i = i + 1;
    end while;
    return sum;
end;

# repeat-unitl(do-while) 类似do-while

create function sum_all2(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    repeat
        set sum = i + sum;
        set i = i + 1;
    until i > n end repeat;
    return sum;
end;

loop(死循环型) 通过return结束循环

create function sum_all3(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    loop
        if i > n then
            return sum;
        end if;
        set sum = i + sum;
        set i = i + 1;
    end loop;
end;

通过leave 标签结束循环
create function sum_all4(n int unsigned)
returns int
begin
    declare sum int default 0;
    declare i int default 1;
    flag:
    loop
        if i > n then
            leave flag;
        end if;
        set sum = i + sum;
        set i = i + 1;
    end loop;
    return sum;
end;

存储过程stored procedure

• 存储过程与存储函数的区别是, 存储过程没有返回值

• 定义存储过程

  create procedure tbl_emp_operation(
  eno mediumint unsigned,
  ena varchar(20)             )
  begin
  select * from emp;
  insert into emp(empno,ename,hiredate,sal) values (eno,ena,CURRENT_DATE(),0);
  select * from emp;
  end;
  

• 调用存储过程

  call tbl_emp_operation(10086,'motherfather');
  

• 查看存储过程

  show procedure status [like 需要匹配的存储过程名];
  

• 查看存储过程定义

  show create procedure tbl_emp_operation;
  

• 删除存储过程

drop procedure tbl_emp_operation;

存储过程参数前缀(局部变量的前缀) in(默认) 类似java中值传递, 方法内修改变量, 在外面变量不会改变 out out不可读, 但是可以写入数据,而且写入的值可以被调用者看到 inout 结合in和out,相当于指针, 在函数里面修改外面可以看到

• 存储函数与存储过程的区别

  • 1.存储函数中必须显式的使用returns语句表明返回的类型, 而且必须使用return语句来显示指定返回的值
• 2.存储函数不支持in, out,inout参数前缀, 存储过程支持
  • 3.存储函数在执行中产生的结果集并不会被显示到客户端, 存储过程执行过程中产生的结果集会被显示到客户端
• 1. 存储函数以函数调用的方式进行调用, 存储过程只能通过call语句来调用

触发器trigger

• 应用场景

  • 在向表插入/删除/修改前/后,让mysql服务自动额外的执行一些语句

• 创建触发器

  create trigger trigger_name
  {before|after}
  {insert|delete|update}
  on tbl_name
  for each row
  begin
   触发器内容
  end
  

  例子: 在插入学生表时,校验英语成绩

  create trigger trigger_for_stu
  before insert
  on student
  for each row
  begin
  if NEW.english < 60 then
    set NEW.english = 60;
  elseif NEW.english < 80 then
    set NEW.english = 80;
  else
    set NEW.english = 99;
  end if;
  end;
  

  • NEW/OLD

    mysql提供两个用来访问修改前后的内容变量 对于insert操作 NEW代表插入的内容, OLD无效 delete: OLD代表删除前的记录, NEW无效 update: NEW代表修改后的内容, OLD代表修改前的记录

• 查看触发器

  # 查看当前数据库定义的所有触发器
  show triggers ;
  

查看触发器定义

show create trigger trigger_for_stu;

• 删除触发器

  drop trigger trigger_for_stu;
  

• 注意事项

  • 触发器内容中不能有输出结果集的语句

  • 触发器内容NEW代表的记录可以修改,NEW影响insert和update的结果, OLD代表修改或删除之前的记录,无法被修改

  • 在before中可以使用set NEW.val = val来修值, 但是在after触发器中不能使用, 因为after时已经插入完成了

事件evnet(定时执行任务)

• 创建事件

  create event event_name on schedule
  {
   at  某个确定时间 |
   every 期望的时间间隔 [starts 开始日期和时间] [ end 结束日期和时间]
   }
  do
  begin
  insert into student(id, name, chinese, english, math) values (12,'caiqingsong',100,0,100);
  end;
  

  实例

  create event insert_student_event on schedule
  at '2025-02-16 15:20:00'
  do
  begin
  insert into student(id, name, chinese, english, math) values (12,'caiqingsong',100,0,100);
  end;
  

• 查看事件

  show events
  show create event event_name
  

• 删除事件

  drop event event_name
  

• 注意

  • 使用事件要把事件功能开启

  mysql set global event_scheduler = on

  • 事件过期后会自动删除

游标

• 创建游标

  create procedure cursor_demo()
  begin
  declare temp_empno mediumint unsigned;
  declare temp_ename varchar(30);
  declare record_len int;
  declare i int default 1;
  

  创建游标

  declare emp_record_cursor cursor for select empno,ename from emp;
  

  打开游标 –> 关联结果集

  open emp_record_cursor;
  set record_len = (select count(*) from emp);
  while i <= record_len do
      # 通过游标遍历结果集
     fetch emp_record_cursor into  temp_empno, temp_ename;
     select temp_ename,temp_empno;
     set  i = i + 1;
  end while;
  

  关闭游标 –> 释放游标相关资源, 存储过程结束后也会自动关闭

  close emp_record_cursor;
  end;
  

• 打开游标

  declare emp_record_cursor cursor for select empno,ename from emp;
  open emp_record_cursor;
  

• 通过游标获取记录

  fetch emp_record_cursor into  temp_empno, temp_ename;
  

• 关闭游标

  close emp_record_cursor;
  

• Previous
  javaweb
• Next
  Hello 2024