数据库基础知识（学习笔记）

文章目录

  • 数据库基础知识（学习笔记）
    • @[toc]
    • 一：基础知识（以MySQL为学习对象）
        • 1.1 什么是数据库
        • 1.1.2 相关概念
        • 1.1.3 为什么要学习数据库
        • 1.1.4 数据库的特点
        • 1.2 数据库的相关基础概念
          • 1. 表（table）
          • 2. 列（column），行（row）
          • 列（column）
          • 行（row）：
          • 3. 主键（primary key）
          • 4. 数据类型（datatype）
          • ① 串数据类型
          • ② 数值数据类型
          • ③ 日期和时间数据类型
          • ④ 二进制数据类型
          • 5. 约束
        • 1.3 查询
          • 1.3.1 基础查询
          • 1.3.2 条件查询
          • 1.3.3 排序查询
          • 1.3.4 常见函数
          • 1.3.5 分组查询
          • 1.3.6 多表连接查询
          • 1.内部连接
          • 2.外部连接
          • 3.自连接
          • 1.3.7 子查询
          • 1.3.8 分页查询
          • 1.3.9 联合查询
        • 1.4 库和表的管理(DDL语句)
          • 1.4.1 库的管理
          • 1.4.2 表的管理
        • 1.5 增删改（DML语言)
          • 1.5.1 插入
          • 1.5.2 修改
          • 1.5.3 删除
        • 1.6 语句执行流程
    • 二：关系型和非关系型数据库的区别
        • 关系型数据库
          • 特点：
          • 缺点：
        • 非关系型数据库
          • 四大分类：
          • 特点：
          • 缺点：
        • 两者的区别：

一：基础知识（以MySQL为学习对象）

1.1 什么是数据库

​ 数据库（database），简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。
​ 所谓“数据库”是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。【百度百科】
​ 关系型数据库常见的有 Oracle，SQLServer，DB2，Mysql

#ps:
数据库就是储存数据信息的仓库，但具有一定的“组织”，即有一套管理系统——DBMS,结构化查询语言——SQL。

1.1.2 相关概念

① DBMS

数据库管理系统(Database Management System,DBMS)
是一种操纵和管理数据库的大型软件，是用于建立、使用和维护数据库。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过dbms访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。它提供多种功能，可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。它使用户能方便地定义和操纵数据，维护数据的安全性和完整性，以及进行多用户下的并发控制和恢复数据库。【百度百科】

#PS：
数据库是通过DBMS创建和操作的容器。

② SQL

​ 结构化查询语言(Structured Query Language)简称SQL
​ 是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。【百度百科】

#PS：
SQL是用于和DBMS通信的语言。

特点：

​ 1.几乎适用于一切DBMS;

​ 2.简单易学；

​ 3.灵活运用了多种语言的元素。

1.1.3 为什么要学习数据库

​ 1.持久化数据到本地；
​ 2.可以实现结构化查询，方便管理。

1.1.4 数据库的特点

​ 1.将数据放到表中，表再放到库中;
​ 2.一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性;
​ 3.表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计;
​ 4.表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”;
​ 5.表中的数据是按行存储的，每一行类似于java中的“对象”。

1.2 数据库的相关基础概念

1. 表（table）

​ 某种特定类型数据的结构化清单；

​ 表是一种结构化文件，可用来存储某种特定类型的数据；

​ 数据库中每个表都有唯一的一个名字，用来标识自己，表名的唯一性取决于多个因素：如数据库名等结合（相同数据库不能出现名字相同的表，但不同数据库可以使用相同的表名）

​ 表具有一些特性，其定义了数据在表中如何存储，比如：存储什么类型的数据，数据如何分解，各部分信息如何命名等

2. 列（column），行（row）
列（column）

​ 表的一个字段；表由列组成，列中存储着表中某部分的信息,所有表都有由一个或多个列组成的

​ 分解数据：正确的将数据分解为多个列极为重要：例如省市县应该是独立的列，通过分解，才可能利用特定的列对数据进行排序和过滤，使得检索和访问更加方便（例如找出特定市县的相关数据等）

行（row）：

​ 表中的一个记录

​ 表中的数据是按行存储的，保存的每个记录存储在自己的行内；如果将表想象为网格，网格中垂直的列为表列，水平行为表行

3. 主键（primary key）

​ 一列或一组列，其值能够唯一区分表中的每一行；唯一标识表中每行的这个列（或这组列）称为主键

​ 主键用来表示一个特定的行；没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证操作只涉及相关的行

​ 主键并不总是需要，但大多数情况下都应保证每个表具有一个主键，以便于数据操作和管理

​ 表中任何列都可以作为主键，只要满足以下2个条件：

​ ①任意两行都不具有相同的主键值；

​ ②每个行都必须具有一个主键值（主键列不允许NULL值）。

​ 主键值规则:主键通常定义于表的一列上，但也可以一起使用多个列作为主键，这种情况下，上述2个条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的（单个列可以不唯一）

#PS：
主键需要注意的点：
1.不更新主键列中的值；

​ 2.不重用主键列的值；

​ 3.不在主键中使用可能会更改的值。

4. 数据类型（datatype）

定义列中可以存储什么数据以及该数据实际怎样存储的基本规则，其用于以下几个目的：
①允许限制可存储在列中的数据
②允许在内部更有效的存储数据
③允许变换排序顺序（作为数值数据类型，数值才能正确排序）

① 串数据类型

② 数值数据类型

③ 日期和时间数据类型

④ 二进制数据类型

5. 约束
NOT NULL		非空约束    插入值不能为空
DEFAULT			默认值约束  若某行无定义值，将会使用默认值
UNIQUE			唯一约束    指定某一列不能有重复值，否则INSERT INTO 时会出错 											
CHECK			限制列中的值的范围
PRIMARY KEY		主键       用来标识表中的一行
FOREIGN KEY		外键约束      一个表可以有多个外键，每个外键必须 			
REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值

1.3 查询

1.3.1 基础查询
select 要查询的东西	
from 表名;

例：select * from book;

​ 从book表中查询所有内容（ * – 查找所有内容）

1.3.2 条件查询

​ 条件查询：根据条件过滤原始表的数据，查询到想要的数据

select 
		要查询的字段|表达式|常量值|函数
from
		表
where
		条件 ;

PS：

​ where后面可接

​ 条件表达式（< > >= <= != <>）

​ 逻辑表达式（and/&& or/|| not/!）

​ 模糊查询（book_id LIKE ‘a%’ ; #查询book_id 包含字符a的数据）

1.3.3 排序查询
select
	要查询的东西
from
	表
where
	条件

order by 排序的字段|表达式|函数|别名 【asc|desc】

PS:

​ asc: 升序排序；

​ desc:降序排序。

1.3.4 常见函数

单行函数

1、字符函数
		concat拼接
		substr截取子串
		upper转换成大写
		lower转换成小写
		trim去前后指定的空格和字符
		ltrim去左边空格
		rtrim去右边空格
		replace替换
		lpad左填充
		rpad右填充
		instr 返回子串第一次出现的索引
		length 获取字节个数

2、数学函数
	round 四舍五入
	rand 随机数
	floor向下取整
	ceil向上取整
	mod取余
	truncate截断

3、日期函数
	now当前系统日期+时间
	curdate当前系统日期
	curtime当前系统时间
	str_to_date 将字符转换成日期
	date_format将日期转换成字符

4、流程控制函数
	if 处理双分支
	case语句 处理多分支
		情况1：处理等值判断
		情况2：处理条件判断

5、其他函数
	version版本
	database当前库
	user当前连接用户

分组函数

	sum 求和
	max 最大值
	min 最小值
	avg 平均值
	count 计数
	
1、以上五个分组函数都忽略null值，除了count(*)
2、 sum和avg一般用于处理数值型
	max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持：
		字段、*、常量值
1.3.5 分组查询
select 查询的字段，分组函数
from   表
group by 分组的字段

特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
		   针对的表	        位置			    关键字
分组前筛选：	原始表		    group by的前面		where
分组后筛选：分组后的结果集	  group by的后面    having

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名
1.3.6 多表连接查询

笛卡尔乘积：
由没有联结条件的表关系返回的结果称为笛卡尔积（检索出的行的数目将是第一个表中的行数乘以第二个表中的行数）；
如果连接条件省略或无效则会出现
解决办法：
添加上连接条件(如where子句，on子句)

1.内部连接

​ 基于两个表之间的相等测试，也称为内部连接（等值连接）；

select A_name,B_name,B_mobile

    from A inter join B

    on A_name = B_name;

1.等值连接的结果 = 多个表的交集
2. n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

2.外部连接

​ 用来检索包含了在相关表中没有关联行的行，这种类型的连接称为外部连接

​ 分为左外部连接 和右外部连接，唯一差别是关联表的顺序不同，左外部联结可以通过颠倒from或where子句中的表顺序转换为右外部联结

select A.cust_id, B.mobile_num

         from A left outer join B

         on A.cust_id = B.mobile_num;
        
#	关键字outer join来指定联结的类型
#	使用outer join语法时，必须使用left或right指定包括其他所有行的表
3.自连接

​ 自连接通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句

select p1.user_id, p1.user_name

         from usertable as p1, usertable as p2

         where p1.user_id = p2.user_id

         and p2.user_id = '10086';
         
#usertable表第一次出现为别名p1，第二次为别名p2，
在select语句中明确使用p1前缀给出所需列名，
否则MySQL将返回错误，因为其无法正确区分想要的具体为哪一列
1.3.7 子查询

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

	 select user_id 
		from usertable 
		where mobile_no 	
		in (select mobile_no	
            from mobile 
            where mobile_id = '10086');

#括号内为从mobile表汇总检索mobile_id为10086的所有行中的mobile_no列，
括号外为从user_table表中检索mobile_id为10086的所有行中的user_id列

特点

1.子查询都放在小括号内
2.子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3.子查询优先于主查询执行，主查询使用了子查询的执行结果
4.子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空

② 多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替

1.3.8 分页查询

​ 实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句。

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数；

特点

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.语法：select * from 表 limit （page-1*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

1.3.9 联合查询

​ 执行多个查询（多条select语句），并将结果作为单个查询结果集返回，这些组合查询称为并（union）或复合查询（compound query）

以下两种基本情况，需要使用组合查询：

①在单个查询中从不同表返回类似结构的数据；

②对单个表执行多个查询，按单个查询返回数据；

语法：
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点

1.多条查询语句的查询的列数必须是一致的
2.多条查询语句的查询的列的类型几乎相同
3.union代表去重，union all代表不去重
4.union几乎可以完成与多个where条件相同的工作。union all为union的一种形式，它完成where子句完成不了的工作（如果需要每个条件匹配行全部出现，则必须使用union all）

1.4 库和表的管理(DDL语句)

PS：创建的操作在SQLyog下完成

1.4.1 库的管理
1.创建库
create database 库名
2.删除库
drop database 库名

例如：

CREATE DATABASE IF NOT EXISTS book_star ;
#如果不存在book_star即创建book_star数据库

1.4.2 表的管理
1.创建表
CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME);
	DESC studentinfo;
	
2.修改表
ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段类型】;

①修改字段名
ALTER TABLE studentinfo CHANGE  COLUMN sex gender CHAR;

②修改表名
ALTER TABLE stuinfo RENAME [TO]  studentinfo;

③修改字段类型和列级约束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

④添加字段

ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first；

⑤删除字段
ALTER TABLE studentinfo DROP COLUMN email;

3.删除表
DROP TABLE [IF EXISTS] studentinfo;

例如：

CREATE TABLE book(
  id INT,
  #编号
  bname VARCHAR (20),
  #书名
  authorname VARCHAR (20),
  #作者
  price DOUBLE,
  #价格
  publishdate DATETIME,
  #出版日期
  form VARCHAR(20)
  #类型
);


#修改publishdate的字段类型从datetime变为date
ALTER TABLE book MODIFY publishdate DATE;

1.5 增删改（DML语言)

1.5.1 插入
insert into 表名(字段名，...)
	values(值1，...);

特点

1、字段类型和值类型一致或兼容，而且一一对应
2、可以为空的字段，可以不用插入值，或用null填充
3、不可以为空的字段，必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

往book表载入信息

INSERT INTO book 
VALUES
  (
    1,
    '纳兰词',
    '纳兰',
    '39.8',
    '2018-08-15',
    '诗歌'
  ) ;
  
INSERT INTO book
VALUES(
	2,
	'白玉苦瓜',
	'余光中',
	'40.00',
	'2018-07-08',
	'散文'
),
(
	3,
	'一直特立独行的猪',
	'王小波',
	'39.10',
	'2017-07-09',
	'文化'
),
  (
    4,
    '在绝望中寻找希望',
    '俞敏洪',
    '38.8',
    '2016-08-15',
    '教育'
  ) ,
   (
    5,
    'C程序设计',
    '唐浩强',
    '36',
    '2015-08-15',
    'IT'
  )；

1.5.2 修改
①修改单表语法：
update 表名 set 字段=新值,字段=新值
【where 条件】

②修改多表语法：
update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件
1.5.3 删除
方式1：delete语句 

单表的删除： 
	delete from 表名 【where 筛选条件】

多表的删除：
	delete 别名1，别名2
	from 表1 别名1，表2 别名2
	where 连接条件
	and 筛选条件;
方式2：truncate语句
	truncate table 表名

区别：
1.truncate不能加where条件，而delete可以加where条件

2.truncate的效率高一丢丢

3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始

4.truncate删除不能回滚，delete删除可以回滚

1.6 语句执行流程

select语句执行流程：
1. from 表
2. join类型 join 表 on 条件
3. where 条件
对select的结果进行过滤
4. group by 字段
根据指定条件把查询结果进行分组，以用做聚合运算
5. having 条件
对分组聚合运算(group by)后的结果进行过滤
6. select distinct 字段
7. order by 字段 [asc|desc]
根据指定字段对查询结果进行排序（默认升序asc）
8. top 多少行
类比limit

二：关系型和非关系型数据库的区别

关系型数据库

​ 关系型数据库，是指采用了[关系模型]来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了[数据库]。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。【百度百科】
​ 常见关系型数据库Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access、MySQL

特点：

1.原子性：记录之前的版本，允许回滚

2.一致性：事务开始和结束之间的中间状态不会被其他事务看到

3.隔离性：适当的破坏一致性来提升性能与并行度 例如：最终一致~=读未提交。

4.持久性：每一次的事务提交后就会保证不会丢失

‘因为存储在磁盘中，不会说突然断电数据就没有了，数据安全性高；
由于建立在关系模型上，所以关系型数据库容易理解；

缺点：

1.为了维护一致性所付出的巨大代价就是其读写性能比较差；
2.固定的表结构；
3.高并发读写需求；
4.海量数据的高效率读写；
5.不节省空间，因为建立在关系模型上，就要遵循某些规则，好比数据中某字段值即使为空仍要分配空间。

ps: 关系型数据库应该是以“关系模型”为基础的，利用“数学逻辑”为方法处理数据的一种数据库；
“数学逻辑”包括选择、连接、交、差、除、增删查改等…

非关系型数据库

​ 非关系型数据库，又被称为NoSQL是基于键值对的对应关系，并且不需要经过SQL层的解析。但是不适合用在多表联合查询和一些较复杂的查询中。NoSQL用于超大规模数据的存储。【百度百科】
​ 常用的有MongoDb、redis、HBase；
​ 主要分为
​ Column-Oriented（面向检索的列式存储）
​ Key-Value（面向高性能并发读/写的缓存存储）
​ Document-Oriented（面向海量数据访问的文档存储）

四大分类：

1.键值对存储（key-value）：Redis键值对存储，优势：快速查询，缺点：存储数据缺少结构化。

2.列存储：Hbase，优势：快速查询，扩展性强。缺点：功能相对于局限。

3.文档数据库存储：MongoDB，早起应用多。优势：要求不特别的严格。缺点：查询性不高，缺少统一查询语法。

4.图形数据库存储：应用于社交网络，优势：利用图结构相关算法。缺点：需要整个图计算才得出结果，不容易做分布式
集群方案。

特点：

1.易扩展，数据之间没有关系的；
2.大数据量，高性能。高性能读写非常灵活的；
3.灵活的数据模型。不需要事先对存储数据建立字段；
4.高可用。

缺点：

1.不提供sql支持，学习和使用成本较高；
2.无事务处理，附加功能bi和报表等支持也不好；

两者的区别：

  1. nosql数据库简单易部署，效率较高，数据的维护和处理比较轻松；
  2. nosql数据库将数据存储于缓存之中，关系型数据库将数据存储在硬盘中，查询速度远不及nosql数据库；
  3. nosql的存储格式是key,value形式、文档形式、图片形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，而关系型数据库则只支持基础类型
  4. 非关系数据库没有关系，没有强大的事务关系，没有保证数据的完整性和安全性；而关系型数据库的数据之间有关系，进行数据的增删改查的时候是非常方便，关系型数据库是有事务操作，可以保证数据的完整性和一致性
  5. nosql适用于处理海量数据，关系型数据库适合处理一般量级的数据。

#PS：非系型数据库的数据存储在缓存中，安全性较低，比如缓存断电会导致数据丢失，而且储存数据的成本较高；
关系型数据库底层是运行了大量的算法，效率较低，但数据库可以进行数据的事务操作与快速查改。
根据不同的需求（如数据的量级，数据的安全等级要求，数据增删查改的频率等）来选择数据库的类型。

缺点：

1.不提供sql支持，学习和使用成本较高；
2.无事务处理，附加功能bi和报表等支持也不好；

#PS：非系型数据库的数据存储在缓存中，安全性较低，比如缓存断电会导致数据丢失，而且储存数据的成本较高；
关系型数据库底层是运行了大量的算法，效率较低，但数据库可以进行数据的事务操作与快速查改。
根据不同的需求（如数据的量级，数据的安全等级要求，数据增删查改的频率等）来选择数据库的类型。

ps:

参考文档：

1.关系型数据库与非关系型数据库Nosql区别汇总

2.关系型数据库与非关系型数据库的区别和介绍
参考教程：

  1. 视频教程：尚硅谷-mysql
  2. 文本教程：《MySQL：菜鸟入门系列》

若有错误请评论指正！！！

更多推荐

数据库基础知识（学习笔记）