Mysql——内外连接,事物,索引
表的内外连接:
表的连接分为内连和外连
内连接:
内连接实际上就是利用where语句对俩个表形成的笛卡儿积进行筛选,我们前面学的所有连接都是内链接
语法:select 字段名 from 表1 inner join 表2 on 连接条件 and 其他条件
例:显示SMITH的名字和部门名称
- select ename,dname from EMP,DEPT where EMP.deptno = DEPT.depton and ename = 'SMITH';
- select ename,dname from EMP inner join DEPT on EMP.deptno = DEPT.depton and ename = 'SMITH';
外连接:
外连接分为左外连接和右外连接
- 左外连接:如果联合查询,左侧的表完全显示,即左外连接
- 语法:select 字段名 from 表名1 left join 表名2 on 连接条件
- 右外连接:如果联合查询,右侧的表完全显示,即右外连接
- 语法:select 字段名 from 表名1 right join 表名2 on 连接条件
创建俩张表:
//学生表
- create table stu(id int,name varchar(30));
- insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
//成绩表:
- create table exam(id int,grade int);
- insert into exam values(1,56),(2,76),(11,28);
查询所有学生的成绩,如果这个学生没有成绩,也要将学生的个人信息显示出来
- select* from stu left join exam on stu.id = exam.id;
- //当右边的表没有匹配项时,左边表中的数据也会显示出来
查询所有成绩,即使没有对应的学生信息,也要将成绩显示出来
- select* from stu right join exam on stu.id = exam.id;
索引:
- 提高数据库的性能,索引是物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行正确的create index,查询速度就可能提高成百上千倍。但是天下没有免费的午餐,查询速度的提高是以插入,删除,更新的速度为代价的,这些操作增加了大量的IO,所以他的价值在于,提高一个海量数据的检索速度
- 常见的索引分为:主键索引,唯一索引,普通索引,全文索引
索引的基本原理:
对于没有索引的表,我们查找时,他会扫描整个表;而添加了索引以后,索引会形成一颗二叉树
索引说明:
- 占用磁盘空间
- 当添加一条记录,除了添加到表中,还要维护二叉树,速度有影响,但不大
- 当我们添加一个索引,不能够解决所有查询问题时,需要分别给字段建立索引:select* from EMP where ename='abcde';
- 索引是以空间换时间
创建索引:
1.主键索引:
1)第一种方式:在建表的时候,直接在字段后面指定primary key
- create table user1(id int primary key ,name varchar(30));
2)第二种方式:在创建表的最好,指定某列为主键索引
- create table user2(id int,name varchar(30),primary key(id));
3)第三种方式:建立表以后再添加索引
- create table user3(int id,name varchar(30));
- alter table user3 add primary key(id);
主键索引的特点:
- 一个表中,最多只有一个主键索引,当然可以使用复合主键
- 主键索引效率高(主键不可重复)
- 创建主键索引的列,它的值不能为null,且不能重复
- 主键索引的列基本上是int
2.唯一索引的创建:
1)第一种方式:在表定义是,在某列后直接指定unique唯一属性
- create table user4(id int primary key,name varchar(30) unique);
2)第二种方式:创建表示,在表后指定
- create table user5(id int primary key ,name varchar(30),unique);
3)第三种方式:表创建好以后添加
- create table user6(id int primary key,name varchar(30));
- alter table user6 add unique(name);
唯一索引的特点:
- 一个表中可以有多个唯一索引
- 查询效率高
- 如果在某一列建立唯一索引,必须保证这列数据不能重复
- 如果一个唯一索引上指定了no nul,等价于主键索引
3.普通索引的创建
1)第一种方式:在表的定义最后,指定某列为索引
- create table user7(id int primary key,
- name varchar(20),
- email varchar(30),
- index(name) //——在表定义的最后,指定为索引
- );
2)第二种方式:创建完表以后指定
- create table user8(id int primary key, name varchar(30),email varchar(30));
- alter table user8 add index(name);//——创建完表以后指定某列值为普通索引
3)第三种方式:创建一个名字叫 idx_name 的索引
- create table user9(id int primary key,name varchar(20),email varchar(30));
- create index idx_name on user9(name);
普通索引的特点:
- 一个表可以有多个普通索引,普通索引在实际开发中用的比较多
- 如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引
4.全文索引的创建
当对文章字段或者大量文字的字段进行检索时,需要用到全文索引。默认全文索引支持英文,不支持中文
如果对中文进行全文检索,可以使用sphinx的中文版
创建全文索引:
- create table articles(id int primary key,
- title varchar(200),
- boby text
- fulltext (title,boby)
- )engine=MyISAM;
如何使用全文索引:
- select * from articles where match(title,boby)against(‘database’);
查询索引:
方法1:show keys from 表名;
- show keys from goods\G
方法2:show index from 表名;
方法3:desc 表名
删除索引:
方法1:删除主键:alter table 表名 drop primary;
方法2:删除其他索引:alter table 表名 drop index 索引名;
- alter table user10 drop index idx_name
方法3:drop index 索引名 on 表名
- drop index name on user 8;
创建索引的原则:
- 比较频繁作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合作为索引,即使频繁作为查询条件
- 更新非常频繁的字段不适合做为创建索引
- 不会出现在where子句中的字段不该创建索引
事务:
事务是由一组dml语句组成的,在逻辑上具有相关性。这一组dml语句要么全部成功,要么全部失败,是一个整体
事物的使用:
- 开始一个事物:start transaction;/ set autocommit = 0;
- 做一个保存点:savepoint 保存点名
- 回滚到保存点:rollback to 保存点名
注:
- 没有设置保存点也可以回滚,只是会回滚到事务的开始,直接使用rollback(前提是还没有提交)
- 如果一个事务被提交(commit)了,则不可以回退
- 可以选择回退到哪个保存点
事务的隔离级别:
- 脏读:一个客户端(事务)读取另一个客户端(事务)没有提交的修改数据
- 幻读:同一个查询在同一个事务里多次进行,由于其它提交事务所做出的插入操作,每次返回不同的结果集,此时发生幻读
- 不可重复读:同一个查询在同一个事务中多次进行,由于其他提交事务所做的修改/删除,每次返回不同的结果集
如何设置事务的隔离级别:
- set session transaction isolation level read uncommitted;
查看当前的隔离级别:
- select @@tx_isolation
MySQL默认的隔离级别是可重复读,一般情况下不要修改
事务的ACID特性
- 原子性:事务是一个不可分割的工作单位,事务中的操作要么全都发生,要么全不发生
- 一致性:事务必须使数据库从一个一致性状态变到另一个一致性状态
- 隔离性:多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据感干扰,多个事务直接要相互隔离
- 持久性:事务一旦被提交,他对数据库的修改时永久的