目录
索引特性、事务管理、视图、用户管理
索引:
提高数据的检索速度
常见索引:
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
创建主键索引
主键索引的特点:
- 一个表中,最多有一个主键索引,当然可以使符合主键
- 主键索引的效率高(主键不可重复)
- 创建主键索引的列,它的值不能为null,且不能重复
- 主键索引的列基本上是int
- 第一种方式
- 创建表的时候,直接在字段名后指定
primary key create table user1(id int primary key,name varchar(30));
- 第二种方式
在创建表的最后,指定某列或某几列为主键索引
create table user2(id int, name varchar(30),primary key(id));
- 第三种方式
create table user3(id int, name varchar(30));
- 创建表以后再添加主键
alter table user3 add primary key(id);
普通索引的创建
普通索引的特点:
- 一个表中可以有多个普通索引,普通索引在实际开发中用的比较多
- 如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引
- 第一种方式
在表的定义最后,指定某列为索引
create table user8(
id int primary key,
name varchar(20),
email varchar(30),
index(name)
);
- 第二种方式
创建完表以后指定某列为普通索引
create table user9(
id int primary key,
name varchar(20),
email varchar(30)
);
alter table user9 add index(name);
- 第三中方式
创建一个索引名为 idx_name 的索引
create table user10(
id int primary key,
name varchar(20),
email varchar(30)
);
create index idx_name on user10(name);
全文索引的创建
注意:
要求 表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文。
- 创建全文索引
select * from articles where body like '%database%';
- 通过explain分析sql语句
explain select * from articles where match (title,body) against ('database')\G
查询索引
- 第一种方法:
show keys from 表名
- 第二种
show index from 表名;
第三种
desc 表名;
删除索引
- 第一种方法-删除主键索引:
alter table 表名 drop primary key;
- 第二种方法-其他索引的删除:
alter table 表名 drop index 索引名; 索引名就是show keys from 表名 中的 Key_name 字段
alter table user10 drop index idx_name;
- 第三种方法方法: drop index 索引名 on 表名
drop index name on user8;
创建索引原则
- 比较频繁作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
- 更新非常频繁的字段不适合作创建索引
- 不会出现在where子句中的字段不该创建索引
事务管理(重点)
事物就是一组DML语句组成,这些语句在逻辑上存在相关性,这一组DML语句要么全部成功,要么全部失败,是一个整体。
事务基本操作
- 创建测试表
create table account( id int primary key, name varchar(50)
not null default '', balance decimal(10, 2) not null default 0.0 );
- 开启一个事物
start transaction;
- 创建一个保存点
savepoint 保存点名;
- 回到保存点
- 提交事物
commit
事物操作注意事项
- 如果没有设置保存点,也可以回滚,只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)
- 如果一个事务被提交了(commit),则不可以回退(rollback)
- 可以选择回退到哪个保存点
- InnoDB支持事务,MyISAM不支持事务
- 开始事务可以使 start transaction
事物的隔离级别
当MySQL表被多个线程或者客户端开启各自事务操作数据库中的数据时,MySQL提供了一种机制,可以让不同的 事务在操作数据时,具有隔离性。从而保证数据的一致性。
无隔离性问题
- 脏读
是指当一个事务正在访问数据,并对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
一个事物读到了另外一个事物没有提交的数据
- 不可重复读
是指在一个事务内,多次读同一数据
- 幻读
是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中 的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发 生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。
注:不可重复读的重点是修改:同样的条件, 你读取过的数据,再次读取出来发现值不一样了 幻读的重点在 于新增或者删除:同样的条件, 第1次和第2次读出来的记录数不一样
事务隔离级别
- 设置事务隔离级别
set session transaction isolation level read uncommitted;
- 查看当前的隔离级别:
select @@tx_isolation;
事物的ACID特性
- 原子性
事务是应用中最小的执行单位,具有不可再分的特征,要么成功,要么失败。
- 一致性
事务执行的结果,必须使数据库从一个一致性状态,变到另一个一致性状态。此时数据库就处于一种不正确(不一致)的状态。
- 隔离性
并发执行的事务之间 不能看到对方的中间状态,并发执行的事务之间不能互相影响。
- 持久性
持久性是指一个事务一旦被提交,它对数据库所做的改变都要记录到永久存储其中(如:磁盘)。
视图
是一个虚拟表,内容由查询定义。和真实的表一样,视图包含一系列带有名称的列和行数据。视图的数据变 化会影响到基表,基表的数据变化也会影响到视图。
基本使用
- 创建视图
create view 视图名 as select语句;
create view v_ename_dname as select ename, dname from emp,
dept where emp.deptno=dept.deptno;
删除视图
drop view 视图名;
视图规则与限制
- 与表一样,必须唯一命名(不能出现同名视图或表名)
- 创建视图数目无限制,但要考虑复杂查询创建为视图之后的性能影响
- 视图不能添加索引,也不能有关联的触发器或者默认值
- 视图可以提高安全性,必须具有足够的访问权限
- order by 可以用在视图中,但是如果从该视图检索数据 select 中也含有 order by ,那么该视图中的 order by 将被覆盖
- 视图可以和表一起使用
用户管理
用户信息
MySQL中的用户,都存储在系统数据库mysql的user表中
select host,user,authentication_string from user;
字段解释:
- host:表示这个用户可以从哪个主机登陆,如果是localhost,表示只能从本机登陆
- user:用户名
- authentication_string:用户密码通过password函数加密后的
- *_priv:用户拥有的权
- 创建用户
create user '用户名'@'登陆主机/ip' identified by '密码';
- 删除用户
drop user '用户名'@'主机名'
修改用户密码
- 自己改自己的密码
set password=password('新的密码');
- root用户修改指定用户的密码
set password for '用户名'@'主机名'=password('新的密码');
给用户的权限
- 刚创建的用户没有任何权限,需要给用户授权
grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']
- 回收权限:
revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'