数据库基础知识复习

1.数据库索引的作用：

(1)提高查询速度

(2)确保数据的一致性

(3)可以加速表和表之间的连接，实现表与表之间的参照完整性

(4)使用分组和排序字句进行数据检索时，可以显著减少分组和排序的时间

(5)全文检索字段进行搜索优化

2.分类：

(1)主键索引（PRIMARY  KEY）

(2)唯一索引（UNIQUE）

(3)常规索引（INDEX）

(4)全文索引（FULLTEXT）

3.主键索引：

某一个属性组能唯一标识一条记录

特点：

最常见的索引类型

确保数据记录的唯一性

4.唯一索引：

作用：避免同一个表中某数据列中的值重复

与主键索引的区别：

主键索引只能有一个

唯一索引可有多个

5.常规索引：

作用：快速定位特定数据

注意：

index和key关键字都可以设置常规索引

应加在查找条件的字段

不宜添加太多常规索引，影响数据的插入，删除和修改操作

6.全文索引：

作用：快速定位特定数据

注意：

只能用于MyISAM类型的数据表

只能用于CHAR,VARCHAR,TEXT数据列类型

适合大型数据集

7.管理索引

(1)创建索引

创建表时添加

建表后追加

语法：ALERT  TABLE  表名  ADD  索引类型  （数据列名）

(2)删除索引:

DROP  INDEX  索引名  ON  表名

ALTER  TABLE  表名  DROP  INDEX  索引名

ALTER  TABLE  表名  DROP  PRIMARY  KEY

(3)查看索引

SHOW  INDEX（或KEYS）  FROM  表名

8.索引准则：

（1）索引不是越多越好

（2）不要对经常变动的数据加索引

（3）小数据量的表建议不要加索引

（4）索引一般应加在查找条件的字段

9.SQL语句的执行性能

EXPLAIN  表名  （DESC表名）

EXPLAIN  SELECT语句

提示：使用\G结尾能竖排显示

10.什么时候添加索引：

在WHERE,ORDER  BY 字句中经常使用的字段

字段的值是多个

字段内容不是经常变化的

*经常变化的字段，添加索引反而降低性能

不宜过多添加索引

*每添加一条索引都会占用磁盘空间

索引有好有坏,索引中的字段被更新的时候,不仅仅要更新表中的数据,还要更新索引数据,以确保索引信息是准确的,这个问题致使IO访问量较大增加,不仅仅影响还要更新索引数据,以确保索引信息是准确的,还影响了整个存储系统的资源消耗,加大了整个存储的负载.

常用的聚合函数

 1  count    2   sum    3   avg    4   max    5   min 

使用规范时 默认的是all   

distinct   指定所有的唯一非空值行

count  (行的数目)

select count (  列或者*)  from  表

sum  (总和)

select  sum（计数规范）from

 

分组  （group by）

select  列a  ， 聚合函数 from 表      where 过滤条件      group  by  列a （以列a来分组）

 

过滤聚合函数（having）

 select  列a  ， 聚合函数 from 表      where 过滤条件      group  by  列a    having  聚合函数过滤条件 

 

 

要注意的是  SQL语句的执行顺序

 (5)select  列a  ， 聚合函数  （1） from 表    （2）  where 过滤条件    （3）  group by  列a  （4）  having   聚合函数过滤条件  （6） order   by

 

课后练习

select * from nobel

-- 1获奖总人数
select count (distinct winner) from nobel

-- 2 诺贝尔 物理奖的获奖总次数
select count(subject)from nobel where subject='Physics'

-- 3 显示每个奖项的获奖总次数
select subject,count(subject)from nobel group by subject

-- 4显示每个奖项第一次获得的年份
select min(yr) 年份,subject 奖项 from nobel group by subject

-- 5 显示每个奖项在2000年来获得的人数
select subject 奖项,yr 年份 from nobel where yr=2000 group by subject ,yr

-- 6 显示每个奖项不同获奖者的人数
select subject 奖项,count (distinct winner)from nobel group by subject

-- 7  显示每个奖项 有多少年获奖
select subject 奖项,count (distinct yr)from nobel group by subject

-- 8 显示当年有三个Physic的年份
select subject 奖项,yr 年份 from nobel where  subject='Physics' group by yr, subject having count(winner)=3

-- 9 得奖大于1的winner
select winner 获奖者 from nobel group by winner having count(subject)>1

--10 得到多个奖项的winner
select winner 获奖者 from nobel group by winner having count(distinct subject)>1