数据库
数据库相关概念
数据库
- 存储数据的仓库，数据是由组织的进行存储
- 英文：DataBase，简称DB
数据库管理系统
- 管理数据库的大型软件
- 英文：DataBase Management System，简称DBMS
SQL
- 英文：Structured Query Language，简称SQL，结构化查询语言
- 操作关系型数据库的编程语言
- 定义操作所有关系型数据库的统一标准
常见的关系型数据库管理系统
- Oracle：收费的大型数据库，Oracle公司的产品
- MySQL：开源免费的中小型数据库，后来Sun公司收购的MySQL，而Sun公司又被Oracle收购
- SQL Server：MicroSoft公司收费的中型的数据库，C#、.net等语言常使用
- PostgreSQL：开源免费中小型的数据库
- DB2：IBM公司的大型收费数据库产品
- SQLite：嵌入式的微型数据库，如：作为Android内置数据库
- MariaDB：开源免费中小型的数据库
MySQL数据模型
关系型数据库
关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的二维表组成的数据库
- 优点
- 都是使用表结构，格式一致，易于维护
- 使用通用的SQL语言操作，使用方便，可用于复杂查询
- 数据存储在磁盘中，安全
SQL
SQL简介
- 英文：Structured Query Language，简称SQL，
- 结构化查询语言，一门操作关系型数据库的编程语言
- 定义操作所有关系型数据库的统一标准
- 对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为”方言“
SQL通用语法
-
SQL语句可以单行或多行书写，以分号结尾
-
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
-
注释
- 单行注释：-- 注释内容（中间有个空格）或 #注释内容（MySQL特有）
- 多行注释：/* 注释 */
数据类型
- 数值类型
| 数据类型 | 大小 | 描述 |
|---|---|---|
| TINYINT | 1 byte | 小整数值 |
| SMALLINT | 2 bytes | 大整数值 |
| MEDIUMINT | 3 bytes | 大整数值 |
| INT或INTEGER | 4 bytes | 大整数值 |
| BIGINT | 8 bytes | 极大整数值 |
| FLOAT | 4 bytes | 单精度浮点数值 |
| DOUBLE | 8 bytes | 双精度浮点整数值double(总长度,小数点后保留的位数) |
| DECIMAL | 小数值 |
- 日期和时间类型
| 数据类型 | 大小 | 描述 |
|---|---|---|
| DATE | 3 | 日期值1998-6-7 |
| TIME | 3 | 时间值或持续时间 |
| YEAR | 1 | 年份值 |
| DATETIME | 8 | 混合日期和时间值 |
| TIMESTAMP | 4 | 混合日期和时间值，时间戳 |
- 字符串类型
| 数据类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255 bytes | 定长（空格补齐）字符串char(最长字符个数) 存储性能高 浪费空间 |
| VARCHAR | 0-65535 bytes | 变长字符串char(最长字符个数) 存储性能低 节约空间 |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16777215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16777215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-42294967295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-42294967295 bytes | 极大文本数据 |
SQL分类
- DDL（Data Denfinition Language）数据定义语言，用来定义数据库类型：数据库，表，列等
- DML（Data Manipulation Language）数据操作语言，用来对数据库中表的数据进行增删改
- DQL（Data Query Language）数据查询语言，用来查询数据库中表的记录（数据）
- DCL（Data Control Language）数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户
DDL
操作数据库
查询
SHOW DATABASES;
创建
- 创建数据库
CREATE DATABASE 数据库名称;
- 创建数据库（判断，如果不存在则创建）
CREATE DATABASE IF NOT EXISTS 数据库名称;
删除
- 删除数据库
DROP DATABASE 数据库名称;
- 删除数据库（判断，如果存在则删除）
DROP DATABASE IF EXISTS 数据库名称;
使用数据库
- 查看当前使用的数据库
SELECT DATABASE();
- 使用数据库
USE 数据库名称;
操作表
查询
- 查询当前数据库下所有表名称
SHOW TABLES;
- 查询表结构
DESC 表名称;
创建
CREATE TABLE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
...
字段名n 数据类型n
);
注意：最后一行末尾不能加逗号
修改
- 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
- 修改数据类型
ALTER TABLE 表名 MODIFY 列名 新的数据类型;
- 修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
- 删除列
ALTER TABLE 表名 DROP 列名;
删除
- 删除表
DROP TABLE 表名;
- 删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
DML
添加
- 给指定列添加数据
INSERT INTO 表名（列名1,列名2,...） VALUES(值1,值2,...);
- 给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,...);
- 批量添加数据
INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
修改
- 修改表数据
UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, ... WHERE 条件;
注意：修改语句中如果不加条件，则将所有数据都修改
删除
- 删除数据
DELETE FROM 表名 WHERE 条件;
注意：删除语句中如果不加条件，则将所有数据都删除
DQL
查询语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组查询
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定
基础查询
- 查询多个字段
SELECT 字段列表 FROM 表名;
SELECT * 表名; -- 查询所有数据，不要使用
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
- 起别名
AS AS -- `math AS 数学成绩` 或者 `math 数学成绩` 也可以省略
条件查询
- 条件查询语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
- 条件
| 符号 | 功能 | ||
|---|---|---|---|
| 大于 | |||
| < | 小于 | ||
| >= | 大于等于 | ||
| <= | 小于等于 | ||
| = | 等于（无法判断NULL） | ||
| <> 或 != | 不等于 | ||
| BETWEEN...AND... | 在某个范围之内（都包含） | ||
| IN(...) | 多选一 | ||
| LIKE 占位符 | 模糊查询 _单个任意字符 %多个任意字符 | ||
| IS NULL | 是NULL | ||
| IS NOT NULL | 不是NULL | ||
| AND 或 && | 并且 | ||
| OR 或 | 或者 | ||
| NOT 或 ！ | 非、不是 | ||
排序查询
- 排序查询语法
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 排序方式1,排序字段名2 排序方式2,...;
排序方式：
- ASC：升序排列（默认值）
- DESC：降序排列
注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序
聚合函数
- 概念
将一列数据作为一个整体，进行纵向计算
- 聚合函数分类
| 函数名 | 功能 |
|---|---|
| count(列名) | 统计数量（一般选用不为null的列） |
| max(列名) | 最大值 |
| min(列名) | 最小值 |
| sum(列名) | 求和 |
| avg(列名) | 平均值 |
- 聚合函数语法
SELECT 聚合函数语法(列名) FROM 表;
注意：null值不参与所有聚合函数运算
分组查询
- 分组查询语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
where 和 having 区别：
- 执行时机不一样：where 是分组之前进行限定，不满足 where 条件，则不参与分组，而 having 是分组之后对结果进行过滤
- 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以
执行顺序：where > 聚合函数 > having
分页查询
- 分页查询语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
-
起始索引：从0开始
-
计算公式：起始索引 = （当前页码 - 1） 每页显示的条数*
-
tips：
- 分页查询 limit 是 MySQL 数据库的方言
- Oracle 分页查询使用 rownumber
- SQL Server 分页查询使用 top
