数据库索引与多表联查

数据库的建立和删除

1. 创建数据库

 create database test;

2. 删除数据库

 Drop database test;

3. 查询数据库

 Desc database test;

4. 使用数据库

Use database test;

数据库三范式

1. 原子性: 每列的字段都是不可分的
2. 主键依赖: 每列都和主键有依赖
3. 不是主键的列之间不能相互依赖

建表和删表

create table tabName(

 id int(10) primary key auto incrment,

name varchar(255) not null;

);

删表

drop table tabName;

对表结构的修改

1.添加一个新的列名:add

alter  table  tabName  add column columnName  type(比如varchar)(length) 约束(比如not null);

例子: alter table user add column role varchar(10) not null;

2.修改现有列的类型  modify 

修改现有列类型、长度和约束 语法：alter table 表名 modify 列名 类型(长度) 约束;

alter  table  tabName modify  column columnName  type () 约束;

例子: 

alter table user modify column name varchar(10) ;

3.修改现有列名称 语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;

例子 

alter table user change column name username varchar(10);

4.删除现有列 语法：alter table 表名 drop 列名 ;

对表的增删改查

增加

insert into tabName values(column,column ,....);

删除

delete from tabName where ?;

改

update tableName set column=? where =?;

查

select column from  tabName where ? ;

左外连接

下面的结果中,会全部显示出来 dept表 ,也就是左边的表的所有的信息, 而emp表中元素不知道.只会显示与dept表有关联的数据

Select * from dept d left  join emp e on d.deptno =e.deptno; 

等价于

Select * from emp e right join dept d on e.deptno =d.deptno;

索引

单独的,物理的数据库结构,某个表中一列或若干列值得集合和相应的指向表中物理标识这些值得数据页的逻辑指针清单.

优点.大大加快了数据的检索速度;

创建唯一索引,保证数据库表中的每一行数据的唯一性;

加速表和表之间的连接

分组和排序子句进行数据检索,减少查询中分组和排序的时间

Create index 命令在一列或若干列的组合上创建索引

主键索引,建表时候自动回创建主键索引,无需关注

普通(唯一)索引的创建:

Create [unique] index index_name on table_name(

Index_col_name(需要创建索引的 表的的列名 的名称),....)

Cardinality 代表索引的散列程度,如果大大少于数据的实际散列程度,索引无效的

explain select * from emp where ename ="mary";

这个 explain 可以 直接查找看索引的结构,名称,类型, 散列程度等等东西....

唯一索引:

 索引所对应的列的值一定要唯一,,但允许有 null (主键是不能存在null的) ,如果是组合索引,列的值得组合必须唯一

全文索引（FULLTEXT）

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有 FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

创建方法:

    

     create  unique index  index_name  on table(column(length));

修改表结构:

alter table  table_name add unique index_name on (column(length));

创建表的时候直接指定:

   create table table_name(

   id  int (11) not null auto_increment,

   title varchar(255) not null ,

   content  text  not null,

   time int(10) not null,

   primary key (id),

index index_name(title(255))  //这个是创建普通的索引

  

unique index_name (title(255))  //这个是创建  唯一索引(可以允许列有null)

 fulltext(context)  //这是创建全文索引

);

查询方法:

show  index from table_name; 可以直接查到表中已经创建好的索引;

show keys from table_name; 这个也可以查到表的索引

重建 索引:

  repair table  table_name quick;

删除索引:

 drop index index_name on table_name;

1.索引使用情况:
动作描述使用聚集索引使用非聚集索引列经常被分组排序使用使用返回某范围内的数据使用不使用一个或极少不同值不使用不使用小数目的不同值使用不使用大数目的不同值不使用使用频繁更新的列不使用使用外键列使用使用主键列使用使用频繁修改索引列不使用使

2. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

经过实际的测试发现 只要在    " %* ",在前面加了　% 就会导致索引不可使用.

1

6. 不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。

最后总结一下，MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的 like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比 如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。

MySQL存储引擎--MyISAM

特点

1.不支持事务: MyISAM 存储引擎不支持事务,所以对事务有要求的业务不能使用

2.表级锁定: 锁定机制是表级索引,锁定的实现成本小,但也大大降低了其并发性能

3.读写互相阻塞: 写入阻塞读取,MyISAM 读取的时候阻塞写入,读本身不会阻塞另外的读

4.只会缓存索引:

通过 key_buffer缓存以大大提高访问性能减少磁盘IO,但是缓存区只会缓存索引,不会缓存数据.

MySQL 存储引擎--InnoDB

特性:

1.支持事务,4个事务的隔离级别

2.读写阻塞与事务隔离级别相关

3.具有非常高效的缓存特性:能缓存索引,也能缓存数据

4.整个表和主键以Cluster方式存储，组成一颗平衡树

5.所有Secondary Index都会保存主键信息

事务的演示:

--  演示数据回滚  数据恢复 删除无效 ...

-- 1: 开启事务  

start transaction;

delete from employee where id  = 3;

--  事务回滚

rollback;

MySQL的多表联查 使用中间表\

多对多关系表  建立一个中间表 ,使用联合主键,快速查找数据

创建第三张关系表,在关系表中引用两个实体主键作为外键

通过关键字 constraint 主键约束  references 参照

alter table 中间表名 add constraint 外表名_中间表名_fk foreign key(中间表对应外表的列) references 外表名(对应的列);