MySQL

第一章 数据库基础概述

一、概念

数据(data)是事实或观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的的原始素材。
数据可以是连续的值，比如声音、图像，称为模拟数据。也可以是离散的，如符号、文字，称为数字数据。

数据可以说是企业的灵魂。
数据库，字面意思就是数据的仓库
概念：数据库（DB）是一种专门存储信息和维护信息的容器，严格地说数据库是“按照数据结构来组织、存储和管理信息的仓库”

二、相关定义

1、数据库管理系统（Database Management System－DBMS）管理数据库的软件。具有对数据存储、安全、一致性、并发操作、恢复和访问等功能。
2、数据词典（系统目录），也是一种数据，只不过这些数据记录的是数据库中存放的各种对象的定义信息和其他一些辅助管理信息，包括名字、结构、位置、类型等。这些数据被称为元数据（metadata）。

三、历程

数据管理主要经历过程：
手工管理阶段 ：应用程序管理数据、数据不保存、不共享、不具有独立性。
早期的数据处理都是通过手工进行的，那时的计算机多用于科学计算，一个程序的数据一般不能被其他程序使用，此阶段没有专门的数据管理软件，程序员既要考虑数据的逻辑结构，还要设计存储数据的物理结构及存取方法等。
文件管理阶段 ：文件系统管理数据、数据可长期保存、但共享性差、冗余度大、独立性差。
随着操作系统的诞生，应用程序可以通过文件系统将的数据组织成一个文件。文件系统提供对文件的访问和管理接口，这种方式多用于早期的单机信息管理系统。
数据管理阶段：数据库系统管理数据、数据结构复杂、冗余小、易扩充、较高的独立性、统一数据控制。

四、特征

数据库的特征：
数据结构化
实现数据共享
减少数据冗余
数据独立性

五、分类

按数据模型特点分：
网状型数据库 采用记录类型为节点的网状数据模型
层次型数据库 采用层次模型模拟现实世界中按层次组织起来的事物
关系型数据库 采用二维表结构组织和管理数据，并规定了表内和表间数据的依赖关系

六、关系型数据库

关系数据库是指一些相关的表和其他数据库对象的集合。对于关系数据库来说，关系就是表的同义词。
表是由行和列组成（类似二维数组的结构）。
列包含一组命名的属性（也称字段）。
行包含一组记录，每行包含一条记录。
行和列的交集称为数据项，指出了某列对应的属性在某行上的值，也称为字段值。
列需定义数据类型，比如整数或者字符型的数据。

七、数据库管理系统

数据库管理系统（DBMS）是用来管理数据的计算机软件，它能使用户方便地定义和操纵数据、维护数据的安全性和完整性，以及进行多用户下的并发控制和恢复数据库。
关系型数据库管理系统（RDBMS）是应用最广泛的一种数据库管理系统，关系型数据库管理系统以表、字段和记录等结构来组织数据。表用来保存数据，每个表由一组字段来定义其结构，记录则是表中的一条数据。本章介绍的MySQL就是一款常用的关系型数据库管理系统。
MySQL是由瑞典 MySQL AB公司开的一种开放源代码的关系型数据库管理系统（RDBMS），目前属于 Oracle 旗下产品。MySQL数据库系统使用最常用的数据库管理语言——结构化查询语言（SQL）进行数据库管理。

八、安装

1、双击

image.png

2、选择Custom(自定义)

image.png

九、MySQL结构

image.png
image.png

与多进程的Oracle数据库相比（并发性好），MySQL是一种单进程多线程的数据库，好处是上下文切换的代价比较小（性能好）。
下面来介绍一下MySQL服务器的组成部分
Connectors:用来与客户端应用程序建立连接的数据库接口
Management Services & Utilities:系统管理和服务控制相关的辅助工具
Connection Pool：负责处理与用户访问有关的各种用户登录、线程处理、内存和进程缓存需求
Sql Interface：提供从用户接受命令并把结果返回给用户的机制
Parser:对SQL语句进行语法分析和解析，构造一个用来执行查询的数据结构
Optimizer:优化查询语句，以保证数据检索动作的效率达到或者非常接近最最优。使用一种“选取-投影-联结”策略来处理查询，即先根据有关的限制条件进行选取(Select 操作)以减少将要处理的元组个数，再进行投影以减少被选取元组列的属性字段的个数，最后根据连接条件生产最终的查询结果
Caches & Buffers：保证使用频率最高的数据或结构能够以最有效率的方式被访问，缓存的类型有：表缓存、记录缓存、键缓存、权限缓存、主机名缓存等。
MySQL存储引擎是可插拔的，这里提到存储引擎。
存储引擎，用来处理数据库的CRUD操作，即读写修改删除，类似于文件系统，介绍两种存储引擎
InnoDB存储引擎的特点:
支持外键（Foreign Key）
支持事务（Transaction）：如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。

MyISAM存储引擎的特点：
MyISAM具有检查和修复表的大多数工具。
MyISAM表可以被压缩
MyISAM表最早支持全文索引
但MyISAM表不支持事务
但MyISAM表不支持外键（Foreign Key）。
如果需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择。

十、MySQL字符集

字符(Character)是指人类语言最小的表义符号，例如'A'、'B'等。
给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这个数值就是字符的编码(Character Encoding)。
给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集(Character Set)。MySQL中提供了多种字符集，例如latin1、utf8、gbk等。

第五章

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (

DEPTNO int(2) NOT NULL,

DNAME varchar(14) DEFAULT NULL,

LOC varchar(13) DEFAULT NULL,

PRIMARY KEY (DEPTNO)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- Records of dept

---

INSERT INTO dept VALUES ('10', 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept VALUES ('20', 'RESEARCH', 'DALLAS');

INSERT INTO dept VALUES ('30', 'SALES', 'CHICAGO');

INSERT INTO dept VALUES ('40', 'OPERATIONS', 'BOSTON');

---

-- Table structure for emp

---

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (

EMPNO int(4) NOT NULL,

ENAME varchar(10) DEFAULT NULL,

JOB varchar(9) DEFAULT NULL,

MGR int(4) DEFAULT NULL,

HIREDATE date DEFAULT NULL,

SAL int(7) DEFAULT NULL,

COMM int(7) DEFAULT NULL,

DEPTNO int(2) DEFAULT NULL,

PRIMARY KEY (EMPNO),

KEY FK_DEPTNO (DEPTNO),

CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES dept (DEPTNO)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- Records of emp

---

INSERT INTO emp VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');

INSERT INTO emp VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');

INSERT INTO emp VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');

INSERT INTO emp VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');

INSERT INTO emp VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');

INSERT INTO emp VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');

INSERT INTO emp VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');

INSERT INTO emp VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', null, '20');

INSERT INTO emp VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');

INSERT INTO emp VALUES ('7844', 'TURNER', 'SALESMAN',

'7698', '1981-09-08', '1500', '0', '30');

INSERT INTO emp VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', null, '20');

INSERT INTO emp VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');

INSERT INTO emp VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');

INSERT INTO emp VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, '10');
DDL
一、数据类型
数字 int(4)最多4位整数
float小数
字符 varchar(32) 最多32个英文16个中文，有3个字符就显示3个字符
char(32)32个英文16个中文，3个字符显示32，缺的地方用空格补全
时间 date年月日
timestamp 时间戳精确到毫秒
time 时分秒
datetime 年月日时分秒
二、1、建表语句
创建book表，列有bookno（int），bookname（varchar）
create table tname
2、修改表
①增加一列
alter table tname add (cname 类型,cname 类型)
新增规格列，列名叫size，可容纳16个汉字
②修改一列类型
alter table tname modify cname 类型
将一列修改为其他类型要求列必须存在，且兼容已有数据
③删除一列
alter table tname drop cname
④更改表的名字
alter table tname rename to newtname
⑤更改列的名字
alter table tname change cname cname2 类型
3、删除表
drop table tname
4、截断表
truncate table tname
三、约束
1、默认值
当用户对使用默认值的字段不插入值的时候，就使用默认值
默认值的字段允许为空，只有当不插入值的时候才生效，允许插入null
2、非空
限制字段必须赋值
非空字段必须赋值，不能赋null
3、唯一
对字段的值不能重复，唯一的字段可以null，且可以多个字段插入null
4、主键（非空+唯一）
通常情况下，每张表都会设置一个主键字段，用以标记每条记录的唯一性，建议不要选择表包含的业务字段作为主键，设计独立的非业务含义的id作为主键
5、自增长
自动递增
delete from不能影响自增长约束
truncate可以影响truncate约束
6、外键
约束两张表的数据
create table tname(
cname 类型 primary key
)
create table tname1(
cname1 类型
constraint tname1_tname_fk foreign key(cname1) references tname(cname)
)
alter tname1 add constraint tname1_tname_fk foreign key(cname1) references tname(cname)
四、视图、索引
1、视图
from子句中的子查询就是视图，所以非常“像”表
视图就是透过窗口看到的数据组合或数据的部分，并不是真正的数据，就像桌面的快捷方式，视图本身不会存储数据
为何要用视图
比如银行有工程师要维护用户表，不能让其看到敏感字段，比如密码
例提供给7566员工，20号部门的员工信息，不包含月薪和奖金
创建视图需要赋予权限
grant create view to scott
create or replace view emp20_view
as
select empno,ename,job,mgr,hiredate,deptno
from emp
where deptno = 20
2、索引
作用：有可能提升数据的查询速度，但同时必定会降低DML的速度，有点伤人1000，自损800的意味
自动创建索引
为一张表的某个列设置了主键约束或唯一约束，会自动为这个列生成索引
手动创建索引
create index emp_ename_index on emp(ename)
删除索引
drop index emp_ename_index
适合做索引的列
1、经常做查询但很少改动的列
2、表数据量要够大，数据量少加索引反而更慢
3、数据中存在的null值比较多
4、经常查询的数据的数量占总量的1%-4%
五、用户和权限
装完mysql后默认会有一个root用户，拥有所有权限，为所欲为，实际开发当中这个账户不能让所有人都知道，因此每个人的权限是不同的只能拥有部分权限，例只能操作某个数据库的某张表
1、如何修改root的密码
update user set password = password('kkk') where user = 'root'
2、分配权限账户
grant 权限 on 数据库.表 to '账户名'@'账户类型' identified by '密码'
权限有 select、insert、update、delete、drop、create、all
3、收回权限
REVOKE ALL PRIVILEGES,GRANT OPTION
FROM 'username'@'hostname'
六、分区
大数据存储时代，需要对数据进行优化
目前主要有两种方式
第一种用SQL语句优化
第二种分区管理
常用
range
给定区间作为参考点去分区
list
属于range的特殊情况，列是固定值，比如性别只有男和女
hash
根据值随机的分配不同的区域
key