03 - SQL高级

目录

1. SQL高级编程技巧
  • 视图基本介绍
  • 事务的引入
  • 提高查询效率-索引
  • 账户管理

课堂笔记

1. SQL高级编程技巧
1.1 视图基本介绍
  • 视图(View):一种虚拟存在的表,对使用视图的用户来说是透明的,视图中的行列来自于创建视图时所选择的表,并不会独立占用内存,会在使用时动态生成。

  • 基本语句

    • 创建视图

      create view v_name as select 字段名.. from tbname [where];
      
    • 查看视图

      show tables;								-- 查看表时,视图也会显示
      
    • 查询视图

      select 字段名.. from v_name [where];			-- 与查询表一致
      
    • 删除视图

      drop view v_name;
      
  • 视图的作用

    • 简单:可以将表的各种复杂查询定义成视图,简化了查询语句。
    • 安全:当用户需求不同时,可以根据不同用户设置不同视图。
    • 独立的数据:当视图的结构确定后,修改表或是增加字段不会对视图结构产生影响,而当修改字段名后,需修改视图确保一致。
  • 视图的修改-视图不能修改情况:

    • 定义视图的select中含有distinct、group by、order by、组函数等情况;
    • 定义视图的where条件语句中包含相关子查询;
    • 定义视图的字段来自多个表,即from语句后有多张表时;
    • 定义视图时的字段列作为计算而存在的列。
1.2 事务的引入
  • 事务:数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。

  • 为什么要有事务:

    • 事务主要保证某一操作序列具有一致性,要么都执行要么都不执行。
    • 例如:银行转账,A账户给B账户转账200,正常需经历一下三步:
      • 检查A账户余额是否>=200,是则进行下一步,否则失败
      • A账户扣除200
      • B账户增加200
    • 可能出现当A账户扣除200后,系统出现故障,此时A账户明显转账成功,但B却并未收到钱。此时就需要事务的控制,确保该过程要么都执行,要么都不执行。
  • 事务的特性

    • 原子性(Atomicity):一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。
    • 一致性(Consistency):数据库总是从一个一致性的状态转换到另一个一致性的状态,即该过程要么都执行,要么都不执行。
    • 隔离性(Isolation):通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的,即修改过程是透明的,其他事务只能看到提交后的数据。
    • 持久性(Durability):一旦事务提交,则其所做的修改会永久保存到数据库。(此时即使系统崩溃,修改的数据也不会丢失)
  • 事务的状态

    • 活动的,即事务所对应的数据库操作正在执行中时的状态。
    • 部分提交的(partially committed),即当数据库的最后一个操作执行完成,数据从内存写入到磁盘中的过程。
    • 失败的(failed),当事务处于活动或部分提交状态时,由于某些错误导致的无法继续执行或人为中断的状态。
    • 终止的:当出现操作失败状态后执行事务回滚使数据回到修改前的状态。
    • 提交的:内存数据同步到磁盘上的状态。

    事务状态转化:

在这里插入图片描述


  • 事务的命令 表的引擎类型必须是innodb类型才可以使用事务,这是mysql表的默认引擎

    • 开启事务

      begin;/start transaction [操作类型];
      
    • 提交事务

      commit;
      
    • 回滚事务 回到修改前的状态

      rollback;
      
  • 保存点(savepoint):rollback使数据回到上一次提交之后,而savepoint则指定某一处修改点使数据无需完全恢复如初,只从该savepoint处重新修改即可,降低了大量的重复工作。

    • 设置保存点

      savepoint sp_name;
      
    • 回滚到指定的保存点

      rollback [work] to [savepoint] sp_name;
      
    • 释放保存点

      release savepoint sp_name;
      
1.3 提高查询效率-索引
  • 索引,一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。

  • 索引可提高查询效率,如使用字典查询汉字,会根据偏旁或拼音字母进行索引,而不是毫无规律的去查,数据表中主键会被默认建立隐式索引,也可根据需要对字段建立索引。

  • 基本原理-B+树

在这里插入图片描述

  • 索引命令

    • 创建索引

      create index index_name on tbname(字段名(长度));				-- 当字段为字符串时需指定长度,否则可以不写
      
    • 查看索引

      show index from tbname;
      
    • 删除索引

      drop index index_name on tbname;
      
    • 测试-开启时间测试

      set profiling=1;
      
    • 测试-再次查看运行时间

      show frofiles;
      
  • 不适合建立索引情况

    • 频繁更新的字段不适合建立索引
    • where条件里面用不到的字段不创建索引
    • 表记录太少,当表中数据量超过三百万条数据,可以考虑建立索引
    • 数据重复且平均的表字段,比如性别,国籍
1.3 账户管理
  • MySQL账户体系:
    • 服务实例级账号:启动了一个mysqld,即为一个数据库实例,为默认存在的账户,该账户可以对其他级别的账户进行授权;
    • 数据库级别账号:对特定数据库执行增删改查的所有操作;
    • 数据表级别账号:对特定表执行增删改查等所有操作;
    • 字段级别的权限:对某些表的特定字段进行操作;
    • 存储程序级别的账号:对存储程序进行增删改查的操作。

账户操作

  • 主要包括 创建账户、删除账户、密码修改及权限授予。

  • 基本命令

    • 创建账户

      create user 'username'@'host' identified by 'password';			-- host指定主键名称或IP
      
    • 修改密码

      alter user user() identified by 'password';						-- 修改当前用户的密码
      
    • 更改用户名

      rename user 'username'@'host' to 'new_username'@'new_host';
      
    • 授予权限

      grant [权限] on dbname.tbname to 'username'@'localhost';
      grant all privileges on dbname.tbname to 'username'@'localhost';	-- 授予所有权限
      
    • 查看用户权限

      show grants for 'username'@'host';
      
    • 回收用户权限

      revoke grant [权限/all] on dbname.tbname from 'username'@'host';	-- all回收所有权限
      
    • 查看user表结构

      desc mysql.user;
      
    • 查询用户表信息

      select user, host, authentication_string from mysql.user;		-- authentication_string为加密的密码
      
    • 删除用户

      drop user [if exits] username[,username];						-- 判断用户存在
      
  • 权限列表

    权限权限说明及作用的级别
    ALTER[PRIVILEGES]除了GRANT OPTION和PROXY之外,以指定的访问级别授予所有特权。
    ALTER修改权限,作用于全局,数据库,数据表
    ALTER_ROUTINE修改存储过程,作用于全局,数据库,存储过程
    CREATE创建权限,作用于全局,数据库,数据表
    CREATE_ROUTINE创建存储过程的权限,作用于全局,数据库
    CREATE_TABLESPACE表空间和日志文件组的创建、更改、删除,全局权限
    CREATE_TEMPORARY_TABLES创建临时表的权限,作用于数据库,数据表
    CREATE_USER创建、删除,重命用和移除用户权限的权限,全局权限
    CREATE_VIEW创建视图权限,作用于全局,数据库,数据表
    DELETE删除数据权限,作用于全局,数据库,数据表
    DROP删除数据库、数据表、视图的权限,作用于全局,数据库,数据表
    EVENT使用事件的权限,作用于全局,数据库
    EXECUTE执行存储过程的权限,作用于全局,数据库,存储过程
    FILE读取或写入文件的权限,全局权限
    GRANT_OPTION允许授权或取消授权的权限,作用于全局,数据库,数据表,存储过程,代理
    INDEX使用索引的权限,作用于全局,数据库,数据表
    INSERT写入权限,作用于全局,数据库,数据表,数据列
    LOCK_TABLES在执行SELECT时可以启动LOCK_TABLES的权限,全局或数据库级别的权限
    PROCESS使用SHOW PROCESSLIST查询全部存储过程的权限,全局权限
    PROXY启用用户代理,作用级别从用户到用户
    REFERENCES创建外健权限,作用于全局,数据库,数据表,数据列
    RELOAD启动FLUSH操作,全局权限
    REPLICATION CLIENT使用户能够询问主服务器或从服务器在哪里,全局权限
    REPLICATION SLAVE启用复制从属服务器以从主服务器读取二进制日志事件,全局权限
    SELECT查询权限,作用于全局,数据库,数据表,数据列
    SHOW_DATABASES查询全部数据库,全局权限
    SHOW_VIEWS启用使用SHOW CREATE VIEW,作用于全局,数据库,数据表
    SHUTDOWN关闭数据库服务器权限,全局权限
    SUPER启用其他管理操作的使用,例如CHANGE MASTER TO,KILL,PURGE BINARY LOGS,SET GLOBAL和mysqladmin debug命令。 全局权限
    TRIGGER启用触发器的权限,作用于全局,数据库,数据表
    UPDATE更新权限,作用于全局,数据库,数据表,数据列
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值