mysql 学习基础知识汇总

E-R实体-关系模型

对象模型

半结构化数据模型

文件：对于文件来说可以从三个层次来分析

1、表示层

   文件

2、逻辑层

   文件系统：存储引擎

3、物理层

    元数据

    数据块

E-R图的表示方法

实体：  矩形表示

属性：   椭圆表示

联系：   菱形表示

各实体间的对应关心：1:1      1：n     m:n

数据库的三级模式二级映像：

外模式 -------模式-------内模式

            外模式/模式映像

   模式/内模式映像

外模式：也称为子模式或用户模式，是对数据库用户看见和使用的部分数据的逻辑结构和特征的描述。

模式：是对数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图，数据库模式以一种数据库模型为基础。

内模式:也成存储模式，是对数据存储和结构存储的藐视，是数据在数据库内部存储的表示方法，一个数据库只能有一个内模式

三级模式的特点：1、保证数据的独立性（内模式和模式分开，保证数据物理独立性，模式和外模式独立性，保证数据逻辑独立性）

                2、简单用户接口 （按照外模式编写应用程序和输入命令，不需要知道数据库内部的存储结构）

3、有利于数据共享

4、有利于数据的安全保密

数据库管理系统应有四大功能：

1、数据定义功能，DDL语言来定义

2、数据操纵功能，DML语言来操纵

3、数据库建立和维护功能（建立：数据载入、转储、重新组织。维护：数据库结构的修改、变更、扩充）

4、数据库的运行管理功能

关系模型中常用的关系操作有两类：

1、查询操作：选择（select）投影（project）连接（join）除（divide）并（union）交（intersection）差（difference）

2、更新操作：增加（insert）删除（delete）修改（update）

完整性约束条件包括：实体完整性、参照完整性、用户自定义完整性，前两个是关系模型必须满足到的完整性条件，成为关系的两个不变性

主键：用来唯一标示一个实体（元组）的一个或多个属性，实体完整性要求主键不能为空。primary key 表示

外键：用于建立和加强两个表数据之间的连接的一列或多列。用foreign key 表示

参照完整性是对于外键而言的。若F是关系R中对应关系S的外键，则对应R中的元组在F上必须满足

1、F为空值

2、F等于S中某个元组的主键值

用户自定义完整性：有check 、unique

集合运算符：并、交、差

比较运算符：大于、小于、等于、大于等于、小于等于、不等于

逻辑运算符：非、于、或

专门关系运算符：选择、投影、连接、除、广义笛卡尔积

sql语言分类

DDL:数据定义语言

CREATE / DROP / ALTER

DML:数据操纵语言

INSERT / DELETE /SELECT /UPDATE

DCL:数据控制语言

GRANT /REVOKE

完整性约束条件的值：not null 、unique、primary key 、foreign key 、check、 default

数据库控制又叫数据库保护，包括四个方面，即安全性控制、完整性控制、并发性控制、数据库恢复

认证授权机制、

权限：系统权限、对象特权、隐含特权

库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器

约束条件：

域约束

外键约束

主键约束

唯一性约束

检查性约束

数据的存储和查询：

  存储管理器

功能：权限和完整性管理器

     事务管理器

     文件管理器

     缓冲区管理器

  查询管理器

功能：DML解释器

     DDL解释器

     查询执行引擎

mysql是单进程多线程的

守护进程

应用进程

对于数据库的查询会消耗大量的内存，往往是服务的瓶颈所在。

解决办法：1、使用缓存 2、线程重用  thread reuse

连接数据库的四种方式：1、应用程序、2、DBA(使用数据库管理工具) 3、sql用户（使用数据库客户端连接）  4、程序员（使用数据库接口API）

关系运算：

投影

选择

自然连接

笛卡尔积

并

如何使用程序设计语言和RDBMS进行交互

嵌入式sql：与动态sql类似，但其语言必须程序编译时就完全确定下来

   例如:ODBC

动态sql：程序设计语言使用函数或方法与RDBMS服务器建立连接，并进行交互，通过建立的连接相sql服务器发送查询语句，并保存知道变量中而后进行处理

   例如:JDBC

MYSQL服务器的基本组织结构：

连接管理器、解析器、缓存器、优化器、存储引擎

mysql的存储引擎在5.5.8前用MYISAM(不支持事物操作，使用于多查询的场景，如数据仓库)，而在mysql5.5.8之后，存储引擎采用innoDB(支持事物处理)

表管理器：负责创建修改读取表定义文件，维护表描述符告诉缓存，管理表锁

表修改模块：表创建修改、删除插入、移除、更新等操作

表维护模块：检查、修改、备份、恢复优化（碎片整理）及解析

行：定长，变长

文件中记录组织：

堆文件组织：一条记录可以放在文件中的任何位置

顺序文件组织：根据“搜索码”值顺序存放

散列文件组织：

表结构定义文件

表数据文件

B树索引

R树索引

mysql资料开源站点

MariaDB：MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是:

甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。    MariaDB的目的是完全兼容MySQL，

包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语:XtraDB）来代替MySQL的InnoDB。   

MariaDB由MySQL的创始人Michael Widenius（英语:Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，

此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。MariaDB基于事务的Maria存储引擎，

替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了

PrimeBase XT (PBXT) 和 FederatedX存储引擎。

Percona：Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、

为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

版本选择：

alpha：

beta：

RC:

GA:

mysql的更改密码三种方式：

1、mysqladmin -u username @ host password ‘new-password‘ (-p)

2、mysql>set password for ‘username‘@‘host‘ =password(‘new_password‘);

3、mysql>update mysql.user set password=password(‘new_password‘) where conditions;

  第三中方法需要flush privileges ；使数据库重读user表

mysql客户端和server进行同行方式：

在同一个主机上：linux主机上基于mysql.sock进行通信

               windows主机上基于memory（pipe）来进行通信

不在同一主机上通信采用TCP/IP进行通信

mysql的专用客户端工具：

    mysql、mysqldump、mysqladmin、mysqlcheck、mysqlimport

mysql非客户端工具

    myisamchk、myisampack

服务器端：

msyqld/mysqld_safe/mysqld_mutil(多实例)

muysl服务器查找配置文件可以从多个路径查找，顺序是：

/etc/my.cnf-->/etc/mysql/my.cnf-->安装路径下的/my.cnf-->--defult-extra=/path  -->~./my.cnf  如果能同时找到几个配置文件，默认是顺序靠后的文件先生效

【client】

-u -h -p --prot --protocol --database 参数DATABASE

连接使用的协议有  tcp、socket、pipe、memory

程序连接数据的方式：

动态sql：

嵌入式sql：

JDBC/ODBC

存储引擎：

myisam ：每个表中有类文件   。frm  表结构文件  。MYD 表数据文件  .MYI 表索引文件

innodb：所有表默认使用一个表空间，可以打开功能使每个表使用单独的一个表空间

show engines; 查看当前服务器支持的存储引擎

show table status [like ‘关键字‘];

总结数据库错误情况：

1、此前服务器未关闭

2、数据初始化失败

3、数据目录位置错误

4、数据目录权限

mysql数据类型：

数值型

  精确数值

    int

    decimal  十进制

  近似数值

     float

     double

     real  实数

字符型

    定长  char,  binary(区分大小写)

    变长  varchar，varbinary(区分大小写)

    enum 枚举

    set  集合

日期时间型

             date

    time

    datetime

    timestamp

数据类型的意义：

 1、存储数据值的类型

 2、数据占据的空间

 3、定长还是变长

 4、如何排序以及索引

 5、是否能够索引

常用命令：

mysql> show character set; 显示支持的字符集

mysql> show collation；显示字符集排序规则

sql模型：

服务器变量：用户变量（使用 @ 表示）和系统变量（使用 @@ 表示）

   作用域：分为两类

全局变量：show global variables

会话变量： show 【session】variables

    生效时间“

        动态：可以随时生效的变量

静态：需要写在配置文件中或者通过参数传递给mysqld

   动态调整参数的生效方式：

       对于全局的变量来说：对当前会话无效，只对新建会话有效

       对于会话变量来说：及时生效，但只对当前会话有效

  设定变量使用 set  global|session  变量名=‘值’

mysql服务器模式：

查看当前服务器模式：show global|session variables like ‘sql_mode‘;

           select @@global|session.sql_mode;

主要的服务器模式常用的几种：

INT(5) ZEROFILL

FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。M表示精度，D表示标度（即小数点后面几位，多余的采用四舍五入的方法）

为了保证最大可能的可移植性，需要使用近似数值数据值存储的代码应使用FLOAT或DOUBLE PRECISION，不规定精度或位数

DECIMAL(M,D)和NUMERIC(M,D)类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值，例如货币数据。当声明该类型的列时，可以(并且通常要)指定精度和标度；M默认值是10。

BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1到64

要指定位值，可以使用b‘value‘符。value是一个用0和1编写的二进制值。例如，b‘111‘和b‘100000000‘分别表示7和128

如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。例如，为BIT(6)列分配一个值b‘101‘，其效果与分配b‘000101‘相同

当要在一个数值列内保存一个超出该列允许范围的值时，MySQL的操作取决于此时有效的SQL模式。如果模式未设置，

MySQL将值裁剪到范围的相应端点，并保存裁减好的值。但是，如果模式设置为traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据SQL标准插入会失败

存储函数用select调用：例select database（）；

存储过程用curl调用

数值型修饰符有：not null/  mull /default/unsigned/unique/auto_increment

AUTO_INCREMENT

整形

非空

无符号

主键或唯一键

内置函数select last_insert_id（）；

mysql服务器关闭过程：

1、启动关闭进程、

2、服务器根据需要创建关闭线程

3、服务器停止接收连接

4、服务器终止当前活动

5、存储引擎被停掉或者关闭

6、服务器退出

sql语句：

show character set；查看字符集

show  collation；查看字符排序方式

创建数据库：help create database

   create database db_name character set ‘‘ collate ‘‘;

   表示创建数据库的同事指定数据库字符集合字符排序方式

例：

 create database if not exists mydb3 character set ‘gbk‘ collate ‘gbk_chinese_ci‘;

创建表：三种方式 help create table

1、直接定义空表

2、从其他白哦中查询出数据，并以之创建

3、以其他表为模板创建一个表

例：

 方式一：

 create table tb1 (id int unsigned not null auto_increment ,

                   name char(20) not null ,

                   age tinyint not null ,

  primary key(id,age),

  unique key(name),

  index(age)) engine=engine_name; 创建表的同时制定表的存储引擎

方式二：

mysql> create table testcourse select * from courses where cid <=2;

方式三：

create table test like courses;

修改表定义：help alter table

innodb存储引擎支持外键约束

创建索引

单表查询：

distinct  表示去掉重复的项

select distinct cid2 from students where cid2 is not null;

select cname from courese where cid not in (select distinct cid2 from students where cid2 is not null);

视图：

一般情况下不能向视图中插入数据

有些数据库支持物化视图：就是将视图保存下来

mysql不支持物化视图，不支持视图创建索引

show create  显示创建某个对象的时候使用的语句

连接管理器：

  接受请求

  创建线程

  认证用户

  建立安全连接

并发控制问题：指的是同一时刻多个用户访问同一资源将引发并发控制问题。

多版本并发控制概念：简称MVCC

锁类型：

读锁：共享锁 read  简称s锁

写锁：独占锁（也叫排他型封锁） write ，简称x锁

 手动添加锁：lock table table_name lock_type read或者write

 解锁： unlock table

封锁协议：按照不同的规则进行的封锁规则叫做封锁协议。可分为

1、一级封锁协议：加x锁，只能解决丢失更新问题，而不能结局污读和重读问题

2、二级封锁协议 ：在一级封锁协议基础上再加s锁，能结局污读问题，但是还不能解决重读问题

3、三级封锁协议：加上s协议后不立即释放s锁，而是在事物结束后才结束s锁，可解决重读问题。

锁粒度：从大到小，mysql服务器仅支持表级锁，行锁需要存储引擎支持完成

 表锁：

 页锁

 行锁

事物：事物是数据库系统中执行的一个独立工作单位，它由用户定义一个或多个语句构成的一组操作序列，这组序列要么全做，要么全部做

事物的三个步骤：

begin transaction

commit

rollback

事物的4个特征：简称ACID准则

1、原子性：一个事物是一个不可分割的单位，要不全做，要么全不做，如果执行事物因为某些故障导致失败，已执行的操作也会被撤销。

2、一致性：事物对数据库作用是数据库从一个一致状态转变到另一个一致状态。一致状态指的是数据库满足约束条件。一致性和原子性密不可分。

3、隔离性：如果多个事物并发的执行，应该想单个事物独立执行时一样，每个事物的执行不能被其他事物干扰，即一个事物内部操作及使用的数据对并发的其他事物是隔离的，并发控制就是为了保证事物的隔离性。

4、持久性：指事物一旦提交，对数据库中数据改变就是持久性的，即使数据库因为故障受到破坏，DBMS也应该能够恢复。

隔离性：隔离级别

read uncommitted  ：读未提交

read committed  ：读提交

repatable read：可重读

seriablizable：可串行

查看当前数据库的隔离级别：

mysql> show golbal variables like ‘%iso%‘;

数据库的并发控制和恢复控制就是为了保证事物并发执行时和在体统出现故障是扔能满足事物的ACID特性。

事物的执行方式：串行执行、并行存取

串行执行就是按事物的先后顺序依次执行。

并行存取就是多个事物在时间上交叉执行，并发存取数据库。

并发操作导致数据库数据不一致性的原因：1、丢失更新、2、污读 3、不可重读

事物集调度分为串行调度和并行调度。如果串行调度和并行调度执行的结构一致，则成为可串行化调度。

目前的DBMS普遍采用封锁方法，

事物是由存储引擎支持的，myisam 不支持事物，innodb支持事物。

事物日志：

     重做日志：redo log  每次执行前先写到日志中，在执行操作

     撤销日志：undo log   每次执行前记录下执行前的状态，

本文出自 “七颗星” 博客，请务必保留此出处http://qikexing.blog.51cto.com/7948843/1672128

mysql 学习基础知识汇总

标签：基础知识   mysql   数据库