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	更新权限，作用于全局，数据库，数据表，数据列