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