mysql进阶使用

发表于 | 分类于
| 字数统计: 2.9k 字

MySQL是最流行的关系型数据库管理系统之一。有很多的高级用法包括：视图view,索引操作,数据库的备份和恢复,数据控制语言(DCL),数据事务语言(DTL),存储函数, 存储过程(procedure),触发器(trigger)等

mysql高级操作

  • 视图 view

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    view是由sql的查询结果形成的一张虚拟表
    创建视图  create view 视图名 as select 语句
    create view vgoods as select goods_id,goods_name,shop_price from goods
    使用视图  基本上当成一张表用即可
    删除视图 drop view 视图名
    1.权限控制时使用 通过视图开放其中的一列或几列起到权限控制的作用
    2.简化复杂的查询
    3.如果视图的每一行是与物理表一一对应的则可以修改,如果view的行是有物理表的
      多行经过计算得到的结果view是不可以更新的
    视图的algorithm
    merge 把查询视图的语句与创建时的语句where子句等合并
    temptable是根据创建语句瞬间创建一张临时表

  • 存储引擎

    1
    2
    3
    4
    5
    engine=myisam或InnoDB(默认)
    myisam 插入速度快 支持全文索引  不支持事务 表锁
    InnoDB 支持事务安全 行锁  支持外键
    myisam 方式存储的数据表 会比InnoDB 多出2个文件 在mysql/data/
    MyISAM 对应三个文件 *.frm表信息 *.MYD 存储数据 *.MYI 存储索引信息)

  • 索引操作

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    主键索引 primary key
    一张表只能有一个主键只能有一列为auto_increment 主键不能为空
    唯一索引 unique       防止创建重复的值
    索引是数据的目录,能快速定位行数据的位置 不好的是降低了增删改的速度
    对大数据的范围内查找时,查找速度特别快 有索引和无索引查找速度区别很大
    创建原则 1.不要过度索引 2.在where条件最频繁的列上加 3.尽量索引散列值
    key 普通索引     unique 唯一索引   primary key 主键索引 fulltext全文索引
    查看索引 show index from 表名
    增加索引 alter table stu_mess add 索引名(列名)
    删除索引 drop index 索引名 on 表名或alter from 表名 drop index 索引名
    增加主键 alter table stu_mess add primary key(id)
    删除主键 alter table stu_mess drop primary key
    删除外键 alter table 表名 drop foreign key(外建名)
        添加外键时自命名的名字  或 在表结构中 查看系统命名的外建名

  • 数据库的备份和恢复

    1
    2
    3
    4
    5
    6
    7
    a.备份  将一个数据库,完整的转换为一个可以 携带和传送的文件
    mysqldump -h 服务器地址 -u 用户名 -p 数据库名 > 文件名(绝对路径)
    用于数据库的迁移 将数据库从一个服务器搬迁到另一个服务器
    用于数据的安全备份 防止数据库系统崩溃
    b.恢复  将一个备份的的数据库文件,完整的还原为一个可以使用的数据库
    mysql -h 服务器地址 -u 用户名 -p 数据库名 < 文件名(绝对路径)
    登陆后 mysql > source "备份数据库文件的完整路径"

数据控制语言(DCL)

  • 用户管理

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql中的所有用户,都存储在系统数据库mysql中的user表中
    a.创建用户  create user '用户名'@允许登录的地址/服务器 identified by '密码'
              允许登录的地址:指允许用户登录到mysql服务器的客户端的地址,本机localhost
              也可是某服务器名,或某IP地址
    b.删除用户  drop user '用户名'@'允许登录的地址'
    c.修改用户密码
        修改自己的密码  set password=password('密码');
        修改其他用户的密码(前提是有权限)
        set password for '用户名'@'允许登录的地址'=password('密码')

  • 权限管理

    1
    2
    3
    4
    5
    6
    7
    8
    9
    A. 授予用户权限(增加权限)
    grant 权限列表 on 某库.某个对象 to "用户名"@'允许登录的地址'[identified by'密码']
    a. 权限列表 各个权限之间用逗号隔开
    b. 某库 . 某对象 给指定的某个数据库中的某个下级单位
      下级单位有 表名 视图名 存储过程名 存储函数名
      * . *  所有数据库中的所有下级单位     某库 . *  代表指定库的所有下级单位
    c. [identified by '密码'] 不写表示不修改密码 写上表示授权的同时修改密码
    B. 取消用户权限(减少权限)
    revoke 权限列表 on 某库.某对象 from "用户名"@'允许登录的地址'

数据事务语言(DTL) (汇款)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
事务只针对与"增删改"语句而言
事务就是用来保证多条"增删改"语句执行的"一致性",要么都执行完成,要么都没有执行
事务只能使用engine=InnoDB存储引擎
事务特性:
原子性   指一个事务中的操作要么都执行要么都不执行
隔离性   在所有的操作没有执行完毕之前其他会话不能够看到中间改变的过程
一致性   事务发生前和发生后数据的总额依然匹配(数据保持逻辑上的合理性)
持久性   事务产生的影响不能够撤销 如果发生错误只能通过补偿性事务
事务模式
查看事务模式 show variables like "%autocommit%"
默认情况下,事务模式是开启的,称为自动提交模式
开启 set autocommit=1或on  每条增删改语句 都会立即生效
可以手动关闭,非自动提交模式,则需要人为提交
关闭set autocommit=0或off
所有增删改语句 都必须使用commit之后 才能生效
事务执行的基本流程
1.开启一个事务
start transaction //也可以写成: begin
2.执行多条增删改语句 //相当于希望这多条语句要作为一个不可分割的整体去执行
3.判断这些语句执行的结果情况
if(没有出错){
  commit;  //提交事务,此时一次性完成
}else{
  rollback;//事务回滚,此时就是都没有做
}
a.在cmd中  开启事务之后 直接看多条'增删改'是否query ok 都ok则执行 commit;  有一条失败则rollback;
b.在php中
mysql_query("start transaction") //开启事务
msyql_query(多条sql语句)
if($res1 && $res2 &&....){
  mysql_query("commit");
  echo "事务执行成功!所有任务都已完成";
}else{
  mysql_query("rollback");
  echo "事务执行失败!数据没有被修改";
}

Mysql编程

  • 语句块包含符 //相当于js或php中的大括号写法

    1
    2
    3
    [标识符]:begin
        //语句.....
    end [标识符];

  • 变量形式

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    A:普通变量
      定义  declare  变量名 类型名 [default 默认值]
      赋值  set 变量名 = 值      取值  直接使用变量名
      使用场所 a. 定义函数的内部
                b. 定义存储过程的内部
                c.定义触发器的内部
    B:会话变量
      set @变量名 = 值   //和php一样 无需定义 直接赋值
      使用场所  基本上任何环境都可以用
    C:变量赋值
      a. set 变量名 = 表达式  //针对普通变量 必须先declare声明
      b. set @变量名 = 表达式
      c. set @变量名 := 表达式
      d. set 表达式 into @变量名

  • 存储函数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    相当于php或js中有返回值的函数 它必须在完成计算后返回单个具体数据值或变量数据
    a.  create function 函数名(形参1,形参2)
    returns 返回类型
    begin
        #这里写完整的函数语句
        return XX值;
    end      #必须有return语句 返回类型必须和设定的类型一致
    b.调用函数
    可用于select语句中  select now(),函数名(实参1,实参2..)
    set @v1 = now();       set @v2 = 函数名(实参1,实参2...)
    c.删除函数
    drop function [if exists] 函数名;
    注: 在函数内部,可以有各种变量和流程控制的使用,也可以有各种增删改的语句
        但不可以有select或其他返回结果集的查询类语句,因为函数只返回单个数据

  • 存储过程(procedure)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    相当于php或js中没有返回值的函数它只执行不返回单个数据但可以得到(select结果集)
    a  形式 create procedure 存储过程名(in|out|inout 形参1 类型,...)
        delimiter //
        create procedure insert_get(p1 varchar(20),p2 varchar(20),p3 tinyint)
        begin                   //类型必须和表中的字段一致
        insert into result value(p1,p2,p3);
        select * from result order by score desc limit 0,3;
        end
        //
        注: in 用于设定该变量是用来"接受实参数据"的 即传入 默认是in
            out 用于设定该变量是用来存储存储过程中的数据的,即传出 函数必须对它赋值
            inout 是in和out的结合 具有双向作用
            对于out和inout设定,对应的参数 就必须是一个变量,要用于接受传出数据
        create procedure insert_get1(in p1 varchar(20),out p2 varchar(20),inout p3 tinyint)
        begin
        set p2=concat("建",p1);
        set p3=p3+5;
        insert into result values(p1,p2,p3);
        end
        set @s3=80    //设定变量并赋值
        call insert_get1('筑',@s2,@s3);  //调用存储过程 out和inout对应的必须是变量
        select * from result;  //查看插入结果
    b 调用存储过程  有select则返回结果集
      call insert_get("周七","高数",91)
    c 删除存储过程
      drop procedure 存储名
    在php中使用存储函数或存储过程
    调用存储函数
    $sql="insert into tab1 values(null,now(),func1($v1,$v2))"
    调用存储过程
    $sql="call insert_user($v1,$v2,$v3)"
    调用又返回结果集的存储过程
    $sql="call get_user_info($id)"

  • 触发器(trigger) 在数据库 对应表下可以看到

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    也是一段预先定义好的编程代码,不能调用,而是在某个表发生(增删改)
    事件的时候,会自动触发
    create trigger 触发器名 触发时机 触发事件 on 表名 for each row
    begin
        //触发器的内部语句
    end    //在执行之前先要用delimiter更改语句结束符
    说明: 1.触发时机 在某个事件发生之前还是之后触发   before  after
          2.触发事件 表上的什么事件发生的时候  insert update delete
          3.特殊的关键字
            old  代表当前正要执行delete的"旧行数据"
                  old.id  //获得这一旧行数据的任意字段的值
            new  代表当前正要执行insert或update的"新行数据"
                  new.id  //获得该新插入或更改行的id字段的值
    触发器的含义 在某个表上进行insert/update/delete之前或之后,会去执行
                  其中写好的语句,每个表只有6个情形可能调用该触发器,通常
                  用于在对某个表进行增删改操作的时候,需要同时去做另外一
                  件事的时候
    ex  create trigger max after insert on result for each row
        begin
        delete from maxscore;  #先删除max表中的数据
        select max(score) into @mc from result;
        insert into maxscore select * from result where score=@mc;
        end
    删除触发器
    drop trigger [if exists] 触发器名

其他

  • Mysql 常用函数

    1
    2
    1.concat('a','b'..)  连接多个字符串
    2.now()              获取当前时间

  • 如何备份某个数据库的所有表
    mysqldump -u 用户名 -p 数据库名 > e:\\文件名.sql

  • 如何备份库的表
    mysqldump –u 用户名 –p密码 数据库 表1 表2 表3.. > d:\\文件名.sql

  • 如何备份数据库,会多 CREATE DATABASE 语句.
    mysqldump –u 用户名 –p密码 –B 数据库1 数据库2 .. > d:\\文件名.sql

  • 恢复数据库[以库为单位] Source 文件名.sql

  • 恢复数据库[以表为单位] use 某个库 Source 文件名.sql
-------------本文结束感谢您的阅读-------------
0%