数据库的建立和删除
- 创建数据库
create database test;
- 删除数据库
Drop database test;
- 查询数据库
Desc database test;
- 使用数据库
Use database test;
数据库三范式
- 原子性: 每列的字段都是不可分的
- 主键依赖: 每列都和主键有依赖
- 不是主键的列之间不能相互依赖
左外连接
下面的结果中,会全部显示出来 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 代表索引的散列程度,如果大大少于数据的实际散列程度,索引无效的
全文索引(FULLTEXT)
MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有 FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
动作描述 | 使用聚集索引 | 使用非聚集索引 |
列经常被分组排序 | 使用 | 使用 |
返回某范围内的数据 | 使用 | 不使用 |
一个或极少不同值 | 不使用 | 不使用 |
小数目的不同值 | 使用 | 不使用 |
大数目的不同值 | 不使用 | 使用 |
频繁更新的列 | 不使用 | 使用 |
外键列 | 使用 | 使用 |
主键列 | 使用 | 使用 |
频繁修改索引列 | 不使用 | 使 |
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 外表名(对应的列);