MySQL 知识梳理

MySQL 知识梳理

大纲：

• 数据库的一些基本概念
• 数据库设计流程
• 数据库定义语言
• 数据库操纵语言
• 数据库索引
• 数据库常用示例

---

数据库的一些基本概念

1. 什么是数据库管理系统（DataBase-Management System,DBMS）?
   答：数据库管理系统是由一个相互关联的数据的集合和一组用以访问这些数据的程序组成。
   

2. 数据抽象的三个层次?
   答：1.物理层（physical level）：最低层次的抽象，描述数据库实际上是怎么储存的；
   2.逻辑层（logical level）：描述数据库中储存什么数据及这些数据间存在什么关系；
   3．视图层（view level）:最高层，只描述整个数据库的某个部分。

3. 什么是实例？什么是模式?数据库可以分为几种不同的模式？
   答：1.特定时刻储存在数据库中的信息的集合称作数据库的一个实例（instance）；
   2.数据库的总体设计称作数据库模式（schema）；
   3．数据库分为 物理模式（physical schema）、逻辑模式（logical schema）、视图层的子模式（subschema）。

4. 什么是数据模型？数据模型可被分为哪四类？
   答：数据库结构的基础是数据模型（data model），数据模型是一个描述数据、数据联系、数据语义及一致性约束的概念工具的集合。
   四类：

   1. 关系模型（relation model）
   2. 实体-联系模型（entity-relationship model）
   3. 基于对象的数据模型（object-base data model）
   4. 半机构化数据模型（semistruvtured data model）

5. 文件处理系统的主要弊端。
   答：1.数据的冗余和不一致（data redundancy and inconsistency）
   2.数据访问困难（difficulty in accessing data）
   3．数据孤立（data isolation）
   4.完整性问题（integrity problem）
   5原子性问题（atomicity problem）
   6.并发访问异常（concurrent-access anomaly）
   7.安全性问题（security problem）

6. 数据库语言包括那些？
   答：数据库语言包括：

   1. 数据库定义语言（data-definition language）：用于定义数据库模式
      一致性约束：
      1） 域约束（domain constraint），每个属性都必须对应一个所有可能的取值构成的域；
      2） 参照完整性（referential integrity），一个关系集中的取值也在另一关系的某一属性的取值中出现；
      3） 断言（assertion），一个断言就是数据库需要时刻满足的某一条件
      4） 授权（authorization）

   2. 数据库操纵语言（data-manipulation language）：用于表达数据库的查询和更新
      访问类型：
      1) 对储存在数据库中的信息进行检索；
      2) 向数据库中插入新的信息；
      3) 从数据库中删除信息；
      4) 修改数据库中的储存信息。
      通常的两类数据操纵语言：
      过程化 DML（procedural DML）：要求用户指定需要什么数据以及如何获取这些数据
      声明式 DML（declarative DML）：只要求用户指定需要什么数据，而不指定如何获取

7. E-R图中什么是实体？什么是映射基数？什么是联系？什么事实体集？什么是联系集？
   答：1.实体是现实世界中可区别于其他对象的一样事情或者一个事物
   2.映射基数表示通过某个联系集能与一尸体进行关联的实体数目
   3.联系是几个实体之间的关联
   4.统一类型的多有实体的集合为实体集
   5．同一类型的所有联系的集合称为联系集

8. 三范式
   答：
   第一范式：1NF 无重复的列
   第二范式（2NF）属性完全依赖于主键[消除部分子函数依赖]
   第三范式（3NF）属性不依赖于其它非主属性[消除传递依赖]

9. 什么是事务？
   答：事务时一组有序的数据库操作，只有这一组数据库操作都正确执行，事务才算完成，只要其中有一个操作失败，操作就会回滚。
   ACID 四大属性，原子、隔离、一致性、持久

10. 存储引擎
    答：MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
    MySQL支持三个引擎：ISAM、 MyISAM和HEAP。另外两种类型InnoDB和Berkley（BDB），也常常可以使用。

数据库设计流程

1.制定用户需求的规格文档，用于说明数据库用户的数据需求；
2.概念设计（conceptual-design），提供企业的详细概述（描述数据以及数据之间的联系）；
3.将概念设计映射到数据库系统的实现数据模型上；
4.将数据模型应用到物理设计阶段

数据库定义语言

create、alter、drop、create/drop index、grant、revoke
1）create 创建数据库 or 创建数据表；

CREATE DATABASE studysql; # 创建数据库
USE studysql;  #使用 studysql 数据库
CREATE TABKLE teacher2(     # 创建数据表
    id int(11) AUTO_INCREMENT,
    user_name varchar(64) DEFAULT "",
    sex int(1) DEFAULT 1,
    PREMARY KEY(id)
); 

2）alter 数据表定义信息

# 添加属性栏
ALTER TABLE teacher2 ADD email VARCHAR(64) DEFAULT ""; 
# 修改属性栏
ALTER TABLE teacher2 MODIFY email VARCHAR(64) DEFAULT "Email";
# 删除属性栏
ALTER TABLE teacher2 DROP email;

3）drop 表，数据库

DROP TABLE teacher2;
DROP DATABASE studysql;

4）create/drop index 创建和删除索引

CREATE INDEX email ON commant(email);
DROP INDEX email ON commant(email);

5）grant、revoke
grant：为数据库用户添加各种操纵权限（SELECT、UPDATE、DELETE、INSERT）
revoke：撤销数据库用户的权限（revoke … from …）

GRANT SELECT ON studysql.teacher2 TO root@localhost;
REVOKE SELECT ON studysql.teacher2 FROM root@localhost;

数据库操纵语言

select、insert、delete、update

# 选择
select * from teacher2 where id=3;
# 插入
insert into teacher2 ('user_name','sex')values('hello kity',0),('xiao ming',1);
# 删除
delete from teacher2;
# 修改
update teacher2 set user_name="rose" where id=2;

数据库索引

MySQL索引原理及慢查询优化

数据库常用示例

1. 树型结构的数据库表怎么设计与遍历

表结构

create table tableName(
id int(11) auto_increment,
parent_id int(11) not null,
path_name varchar(128) default "",
primary key(id)
);

获取某节点的所有子孙节点

CREATE FUNCTION ‘getChildIds’( root INT)
RETURNS VARCHAR(1000);
BEGIN
DECLARE ids VARCHAR(1000);
DECLARE tmpIds VARCHAR(1000);
SET ids = cast(root as char);
SET tmpIds = cast(root as char)
SELECT GROUP_CONTACT(id) INTO tmpIds FROM tableName WHERE FIND_IN_SET(parent_id,tmpIds);
WHILE tmpIds IS NOT NULL DO
SET ids = concat(ids,’,’,tmpIds);
SELECT GROUP_CONTACT(id) INTO tmpIds FROM tableName WHERE FIND_IN_SET(parent_id,tmpIds);
END WHILE
RETURN ids;
END

Select * from tableName where find_in_set(id,getChildIds(n));

2.group by， max，order by 合用例子

根据数据表中取得每个用户的最高得分，并根据得分排序：

create table score(
id int(11) auto_increment,
score int(3) 100,
user_id int(11) ,
primary key(id)
);
insert into score (score,user_id)valuse(80,1),(90,2),(100,3),(79,1),(89,1),(90,2);
select user_id,max(score) as max_score from score group by user_id order by max_score;

3.having 与 where 的区别
having子句可以让我们筛选成组后的各种数据，where子句在聚合前先筛选记录，也就是说作用在group by和having子句前。而 having子句在聚合后对组记录进行筛选。