【数据库】第一章 数据库的分类、SQL、数据库、表和表记录的常用操作

【数据库】第一章 数据库的分类、SQL、数据库与表的常用操作

一、数据库的分类

早期发展的数据库建立在数据的紧密关系基础之上（如：父子关系、师生关系），我们称其为关系型数据库，也称为传统数据库；现今数据库建立在数据的松散关系基础之上（如：张三今天在超时买了一瓶啤酒和两包尿布；李四晚上 8 点在某某视频播放网站看了一部《悬崖之上》），我们称其为非关系型数据库 NoSQL（Not Only SQL）。原来关系型数据库一统天下的格局早被打破，领土不断被蚕食，规模一再的缩小，虽然无法全面被替代，但却早已风光不在，沦落到一偶之地，Oracle的衰落就是最好的证明，早期只要是全球大企业无一例外都是部署Oracle，但现在都在去Oracle化，阿里就已经全面排斥Oracle

概念总是拗口难懂的。那么简单来说，关系型数据库的存储就是按照表格进行的。数据的存储实际上就是对一个或者多个表格的存储。通过对这些表格进行分类、合并、连接或者选取等运算来实现对数据库的管理。常见的关系型数据库有MySQL、Oracle、DB2和SqlServer等

非关系型数据库（NoSQL）是相对于关系型数据库的一种泛指，它的特点是去掉了关系型数据库中的关系特性，从而可获得更好的扩展性。NoSQL并没有严格的存储方式，但采用不同的存储结构都是为了获得更高的性能和更高的并发

1.关系型数据库

关系型数据库，顾名思义，是指存储的数据之间具有关系。这种所谓的关系通常用二维表格中的行列来表示，即一个二维表的逻辑结构能够反映表中数据的存储关系

概念总是拗口难懂的。那么简单来说，关系型数据库的存储就是按照表格进行的。数据的存储实际上就是对一个或者多个表格的存储。通过对这些表格进行分类、合并、连接或者选取等运算来实现对数据库的管理。常见的关系型数据库有 MySQL、Oracle、DB2 和 SqlServer 等

优点：

1. 易于维护：都是使用表结构，格式一致
2. 使用方便：SQL 语言通用
3. 复杂查询：可用于一个表及多个表之间非常复杂的查询

缺点：

1. 读写性能较差，尤其是海量数据的高效率读写
2. 固定的表结构，灵活度稍有欠缺
3. 对于传统关系型数据库来说，如果有高并发读写需求，磁盘 I/O 是一个很大的瓶颈

2.非关系型数据库

非关系型数据库（NoSQL）是相对于关系型数据库的一种泛指，它的特点是去掉了关系型数据库中的关系特性，从而可获得更好的扩展性。NoSQL并没有严格的存储方式，但采用不同的存储结构都是为了获得更高的性能和更高的并发

NoSQL根据存储方式可分为四大类，键值存储数据库、列存储数据库、文档型数据库和图形数据库。这四种数据的存储原理不尽相同，因而在应用场景上也有些许的差异。一般常用的有作为数据缓存的 redis 和分布式系统的 HBase

优点：

1. 格式灵活：存储数据的格式可以是 key,value 形式、文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库只支持基础类型。
2. 速度快：NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘
3. 高扩展性
4. 成本低：NoSQL 数据库部署简单，基本都是开源软件

缺点：

1. 不提供 SQL 支持，学习和使用成本较高
2. 无事务处理
3. 数据结构相对复杂，复杂查询方面稍有欠缺

非关系型数据库的分类和比较

Key-Value 型

举例	Redis，Voldemort，Oracle BDB
典型应用场景	内容缓存，主要用于处理大量数据的高访问负载，也用于一些日志系统等等
数据模型	Key 指向 Value 的键值对，通常用 Hash table 来实现
强项	查找速度快
弱项	数据无结构化，通常只被当作字符串或者二进制数据

文档型

举例	CouchDB，MongoDB
典型应用场景	Web 应用（与 Key-Value 类似，Value 是结构化的，不同的是数据库能够了解 Value 的内容）
数据模型	Key-Value 对应的键值对，Value 为结构化数据
强项	数据结构要求不严格，表结构可变，不需要像关系型数据库一样需要预先定义表结构
弱项	查询性能不高，而且缺乏统一的查询语法

列式数据库

举例	Cassandra，HBase，Riak
典型应用场景	分布式的文件系统
数据模型	以列簇式存储，将同一列数据存在一起
强项	查找速度快，可扩展性强，更容易进行分布式扩展
弱项	功能相对局限

图形数据库

举例	Neo4J，InfoGrid，Infinite Graph
典型应用场景	社交网络，推荐系统等，专注于构建关系图谱
数据模型	图结构
强项	利用图结构相关算法，比如最短路径寻址，N 度关系查找等
弱项	很多时候需要对整个图做计算才能得出需要的信息，而且这种结构不太好做分布式的集群方案

总结

3.MySQL简介

在关系型数据库中，MySQL可以说是其中的王者。它是目前最流行的数据库之一，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL数据库具有以下几个方面的优势：

1. 体积小、速度快
2. 代码开源，采用了 GPL 协议，可以修改源码来开发自己的 MySQL 系统
3. 支持大型的数据库，可以处理拥有上千万条记录的大型数据库
4. 使用标准的 SQL 数据语言形式，并采用优化的 SQL 查询算法，有效地提高查询速度
5. 使用 C 和 C++ 编写，并使用多种编译器进行测试，保证源代码的可移植性
6. 可运行在多个系统上，并且支持多种语言
7. 核心程序采用完全的多线程编程，可以灵活地为用户提供服务，充分利用CPU资源

逻辑架构

MySQL的逻辑架构可分为四层，包括连接层、服务层、引擎层和存储层，各层的接口交互及作用如下图所示。需要注意的是，下文针对的都是InnoDB引擎下的情况

连接层： 负责处理客户端的连接以及权限的认证
服务层： 定义有许多不同的模块，包括权限判断，SQL接口，SQL解析，SQL分析优化， 缓存查询的处理以及部分内置函数执行等。MySQL的查询语句在服务层内进行解析、优化、缓存以及内置函数的实现和存储
引擎层： 负责MySQL中数据的存储和提取。MySQL中的服务器层不管理事务，事务是由存储引擎实现的。其中使用最为广泛的存储引擎为InnoDB，其它的引擎都不支持事务
存储层： 负责将数据存储与设备的文件系统中

二、SQL

1.介绍

结构化查询语言(Structured Query Language)简称 SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存放数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能

2.分类

数据查询语言（DQL）
数据查询语言（Data Query Language, DQL）是 SQL 语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。保留字 SELECT 是 DQL（也是所有 SQL）用得最多的动词，其他 DQL 常用的保留字有 FROM，WHERE，GROUP BY，HAVING和ORDER BY。这些 DQL 保留字常与其他类型的SQL语句一起使用

数据定义语言（DDL）
数据定义语言 (Data Definition Language, DDL) 是 SQL 语言集中，负责数据结构定义与数据库对象定义的语言，由 CREATE、ALTER 与 DROP 三个语法所组成，最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始，现在被纳入 SQL 指令中作为其中一个子集

数据操纵语言（DML）
数据操纵语言（Data Manipulation Language, DML）是 SQL 语言中，负责对数据库对象运行数据访问工作的指令集，以 INSERT、UPDATE、DELETE 三种指令为核心，分别代表插入、更新与删除。

数据控制语言（DCL）
数据控制语言 (Data Control Language) 在 SQL 语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL 以控制用户的访问权限为主，GRANT 为授权语句，对应的 REVOKE 是撤销授权语句

指针控制语言（CCL）
DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT 用于对一个或多个表单独行的操作

事务处理语言（TPL）
它的语句能确保被 DML 语句影响的表的所有行及时得以更新。TPL 语句包括 BEGIN TRANSACTION，COMMIT 和 ROLLBACK

注意：SQL 不区分大小写

3.数据类型

命名规则

• 字段名必须以字母开头
• 长度不能超过 30 个字符（不同数据库，不同版本会有不同）
• 不能使用 SQL 保留字
• 只能使用部分特殊字符
• 多个单词用下划线隔开

字符

• char 长度固定，不足使用空格填充，最多容纳 2000 个字符，char(11)存储 “abc”，占用 11 位。查询速度极快，但是浪费空间
• varchar 变长字符串，最多容纳 4000 个字符，varchar(11) 存储 “abc”，只占用 3 位。查询稍慢，但是节省空间
• text：存储可变长度的非 Unicode 数据，最大长度为 2^31-1(2,147,483,647) 个字符，不推荐使用，尽量使用 varchar 替代

在 MySQL 8 中
utf8:
1 char = 3 bytes,，1 汉字 = 1 char
也就是说一个字段定义成 varchar(200)，则它可以存储 200 个汉字或者 200 个字母

gbk:
1 char = 2 bytes，1 汉字=1 char
也就是说一个字段定义成 varchar(200)，则它可以存储 200 个汉字或者 200 个字母

数字

• tinyint，int 整数类型
• float，double 小数类型
• numeric 和 decimal 表示精确的整数数字
• numberic(5,2) decimal(5,2) 也可以表示小数，表示总共 5 位，其中可以有两位小数

日期

• date 包含年月日
• time 包含时分秒
• datetime 包含年月日和时分秒
• timestamp 时间戳，不是日期，而是从 1970 年 1 月 1 日到指定日期的毫秒数

图片

• blob 二进制数据，可以存放图片、声音、容量 4 G。早期有这样的设计，但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时，数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上

4.字段约束

• 主键约束 PRIMARY KEY：非空且唯一，一张表只能有一个字段是主键，主键就是表中的唯一标识
• 非空约束 NOT NULL：指示某列不能存储 NULL 值
• 唯一约束 UNIQUE：插入数据时数据库会进行检查，如果插入的值相同，就会检查报错
• 外键约束 FOREIGN KEY：保证一个表中的数据匹配另一个表中的值的参照完整性
• 默认约束 DEFAULT：规定没有给列赋值时的默认值
• 检查约束 CHECK：保证列中的值符合指定的条件

三、数据库与表的常用操作

1.数据库

建库

create database demo default character set utf8;

删库

drop database demo;

查看所有库

show databases;

选中库

use demo;

查看创建库的详细信息

show create database demo;

2.表

建表

create table demo(
id int unsigned NOT NULL AUTO_INCREMENT,
age int,
address varchar(10),
PRIMARY KEY (id)
);

删表

drop table demo;

查看所有表

show tables;

查看表结构

desc table demo;

查看创建库的详细信息

show create table demo;

修改字段类型

alter table demo modify address varchar(20); 

添加新的字段

alter table demo add subject varchar(10);

添加字段并指定位置

alter table demo add score double after subject;

删除表字段

alter table demo drop address; 

修改指定的字段

alter table demo change score grade varchar(1);

3.表记录

增加表记录

insert into demo(id,name,age,address,subject,grade) values(1,"Sisyphus",21,"Suzhou","数据库","A");

删除表记录

delete from demo where id=1;

更新表记录

update demo set name = "Demo", age = 20 where name = "Sisyphus" ;

查询表中所有记录

select * from demo;

条件查询表中指定字段记录

select name from demo where age = 20;

查询表记录总数

select count(*) from demo;

查询表记录并排序

select * from demo order by age desc;
select * from demo order by age asc;