数据库总结

目录

数据库理论

数据库介绍

定义:

类型:

功能:

数据库操作

数据表操作

创建数据表

查询数据表

修改数据表

删除数据表

单表操作

增加数据

修改数据

删除数据

查询数据

基本查询

条件查询

排序

分页

多表查询

交叉连接

内连接

左外连接

右外连接

子查询

外键约束

事务

补充

常见报错：

---

数据库理论

数据库介绍

数据库是一种用于组织、存储和管理数据的电子系统。它可以被看作是一组相互关联的数据集合，这些数据可以被高效地访问、管理和更新。以下是关于数据库的一些基本介绍：

定义:

数据库是一个长期存储在计算机硬件平台上的、有组织的、可共享的数据集合。
它旨在最小化数据冗余，并确保数据的一致性和准确性。

类型:

关系型数据库 (RDBMS)

特点：
使用表格形式存储数据。
表格之间通过外键关联。
支持ACID（原子性、一致性、隔离性、持久性）事务。
使用SQL（Structured Query Language）作为标准查询语言。
示例：
MySQL：开源的关系型数据库管理系统，广泛用于Web应用。
Microsoft SQL Server：微软提供的企业级数据库服务器。
Oracle Database：功能强大的企业级数据库管理系统。
PostgreSQL：功能丰富且高度可扩展的开源数据库。
SQLite：轻量级的文件系统数据库，常用于嵌入式系统和移动应用。
IBM DB2：支持多种操作系统的企业级数据库。
Sybase ASE：高性能的关系型数据库管理系统。

非关系型数据库 (NoSQL)

特点：
不使用固定的表格模式，提供灵活的数据模型。
更易于横向扩展。
通常不支持完整的ACID特性。
类型：
文档型数据库：以文档形式存储数据，每个文档可以有不同的字段。
MongoDB
Couchbase
键值型数据库：数据以键值对的形式存储。
Redis
Memcached
Amazon DynamoDB
列族数据库：以列族的形式存储数据，适合大数据和高并发场景。
Apache Cassandra
HBase
图形数据库：以图形结构存储数据，适合处理复杂的关系数据。
Neo4j
ArangoDB
搜索引擎：具有全文搜索和分析功能。
Elasticsearch
Apache Solr
时间序列数据库：专门用于存储时间序列数据，适用于监控和物联网应用。
InfluxDB
OpenTSDB

其他数据库类型

对象数据库：存储面向对象程序设计中的对象。
XML 数据库：专门用于存储 XML 格式的数据。
NewSQL：结合了NoSQL的可扩展性和SQL的事务处理能力。

功能:

数据存储: 保存数据。
数据检索: 快速查找所需数据。
数据更新: 修改已存在的数据。
数据安全性: 保护数据免受未授权访问。
并发控制: 管理多个用户同时访问数据的情况。
事务处理: 确保数据操作的完整性和一致性。

数据库操作

-- 创建数据库
	create database [IF NOT EXISTS] 数据库名;    create database db1;

-- 查询数据库
	show databases;

-- 删除数据库
	drop database [IF EXISTS] 数据库名;    drop database db1;

-- 查看正在使用的数据库
	select database();

-- 切换数据库
	use 数据库名;    use db1;

数据表操作

创建数据表

基本语法

-- 语法
    create table 表名(    
        字段名1  字段类型1(字段长度) [ comment  字段1注释 ],  -- 不是;号    
        字段名2  字段类型2(字段长度) [ comment  字段2注释 ],	
         ....	
        字段名n  字段类型n(字段长度) [ comment  字段n注释 ]-- 最后一列后面是没有,的
    ) [ comment  表注释 ];

数据类型：MySQL中主要有三大类数据类型： 分别是数值、字符、日期

大分类	类型	描述
数值类型	tinyint	小整数
	int	整数
	bigint	大整数
	float	浮点数类型
	double	浮点数类型
字符类型	varchar(30)	可变长度字符串，如果插入的长度小于定义长度时，插入多长就存多长
	char(11)	固定长度字符串，如果插入的长度小于定义长度，则可以用空格进行填充
日期类型	date	日期，格式：yyyy-MM-dd
	datetime	日期时间，格式：yyyy-MM-dd HH:mm:ss

数据约束

约束	描述	关键字
主键约束	主键是一行数据的唯一标识，要求非空且唯一；一张表只能有一个主键列	primary key (auto increment自增)
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的; 可以为null,多列都可以为null	unique
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

查询数据表

-- 查看库中所有数据表
	show tables;

-- 查看表结构
	desc 数据表名;

-- 查看表的建表语句
	show create table 数据表名;

修改数据表

-- 添加字段
	alter table 表名 add  字段名 类型(长度) 

-- 修改字段类型
	alter table 表名 modify 字段名  新数据类型(长度)

-- 修改字段名和字段类型
	alter table 表名 change 旧字段名 新字段名 类型 (长度) 

-- 删除字段
	alter table 表名 drop column 字段名

-- 修改表名
	rename table 表名 to 新表名

删除数据表

-- 删除数据表
	drop table [ if exists ] 表名

单表操作

增加数据

-- 指定字段添加数据 
	insert into 表名 (字段名1, 字段名2)  values (值1, 值2)

-- 全部字段添加数据 
	insert into 表名 values (值1, 值2, ...)

-- 批量添加数据（指定字段）
	insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2)

-- 批量添加数据（全部字段）
	insert into 表名 values (值1, 值2, ...), (值1, 值2, ...)

注意：

1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的

2. 字符串和日期型数据应该包含在引号中

3. 插入的数据大小，应该在字段的规定范围内

修改数据

-- 修改数据
	update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where  条件 ]

注意：修改语句如果没有where条件，则会修改整张表的所有数据

删除数据

-- 删除数据
	delete  from  表名  [ where  条件 ]

注意：删除语句中如果不加条件，则将所有数据都会被删除！

查询数据

基本查询

-- 查询指定字段
	select 字段1, 字段2, 字段3  from 表名

-- 查询所有字段
	select  *  from 表名

-- 设置别名
	select  字段1  [ as ] 别名1  , 字段2  [ as ] 别名2    from   表名

-- 去除重复记录
	select  distinct  字段列表  from  表名

条件查询

条件查询：select 字段列表 from 表名 where 条件列表

比较运算符	功能	逻辑运算符	功能
>	大于	and	并且 (多个条件同时成立)
>=	大于等于	or	或者 (多个条件任意一个成立)
<	小于	!	非 , 不是
<=	小于等于
=	等于
<> 或 !=	不等于
is null	是null
between ... and ...	在某个范围之内(含最小、最大值)
in(...)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)

聚合函数

将一列数据作为一个整体，进行纵向计算，语法为: select 聚合函数(字段名) from 表名

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

分组过滤

-- 分组
	select 分组字段，聚合函数() from 表名 group by 分组字段名

-- 分组后过滤
	select 分组字段，聚合函数() from 表名 group by 分组字段名 having 分组后过滤条件

排序

-- 排序
	select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2
	
-- 注意
    ASC：升序（默认值）
    DESC：降序

分页

-- 分页
	select 字段列表  from 表名 limit 起始索引, 查询记录数

多表查询

表间关系：一对一（夫妻），一对多（班级，学生），多对多(学生，选修课)

交叉连接

-- 解释
    使用左表中的每一条数据分别去连接右表中的每一条数据, 将所有的连接结果都展示出来
-- 语法
     select * from 左表,右表

内连接

-- 解释 
    使用左表中的每一条数据分别去连接右表中的每一条数据, 仅仅显示出匹配成功的那部分
-- 语法
    隐式内连接: select * from  左表,右表 where 连接条件
    显示内连接: select * from 左表 [inner] join 右表 on 连接条件

左外连接

-- 解释
    首先要显示出左表的全部, 然后使用连接条件匹配右表,能匹配中的就显示,匹配不中的显示为null

-- 语法
    select * from 左表 left [outer] join 右表 on 连接条件

右外连接

-- 解释
    首先要显示出右表的全部, 然后使用连接条件匹配左表,能匹配中的就显示,匹配不中的显示为null
-- 语法
    select * from 左表 right outer join 右表 on 连接条件

子查询

-- 解释:
    一个查询使用了另一个查询的结果
-- 子查询根据查询结果不同，作用不同
    单个值，使用 = > <  等进行条件判断 :  SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
    多个值，使用in、not in进行条件判断 : SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
    一张表，将此结果声明为临时表来使用 :SELECT * FROM (子查询) AS 表 JOIN 表 ON 条件;

外键约束

作用：限定两张表有关系的数据，保证数据的正确性、有效性和完整性。

-- 创建表的时候添加
	create table 表名
(列名  列类型,[constraint 约束名: 外键列_fk]  foreign key (列名)  references  主表(主键) )
-- 创建表之后单独添加
	alter table 表名 add [constraint 约束名]  foreign key (列名)  references  主表(主键)

事务

概念：

一个业务操作包含多个子操作，如果这个业务操作被事务管理了，那么这些子操作要么同时成功，要么同时失败。

在MySQL数据库中，默认情况下，一条DML语句就是一个独立的事务。

SQL语法	描述
begin; (start transaction)	开启手动控制事务
commit;	提交事务
rollback;	回滚事务

补充

常见报错：

• 经典错误1: ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061) 表示数据库服务停止了
• 经典错误2: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 表示你的用户名和密码不对

• Duplicate entry '1' for key 'PRIMARY' id重复会报错

• Duplicate entry '孙美美' for key 'name' 姓名重复会报错

• Column 'age' cannot be null age为null，会报错