MySQL数据库
一.数据库
1.数据库的概念
- 存储数据的仓库，数据是有组织的进行存储。
- 英文：DataBase，简称DB
2.数据库管理系统
- 管理数据库的大型软件
- 英文：DataBase Management System，简称DBMS
常见的关系型数据库管理系统
- Oracle：收费的大型数据库，Oracle公司的产品
- MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Sun公司又被Oracle公司收购。
- SQL Server：Microsoft收费的中型的数据库。C#、.net等语言常使用。
- PostgreSQL：开源免费中小型的数据库
- DB2：IBM公司的大型收费数据库产品
- SQLite：嵌入式的微型数据库。如：作为Android内置数据库
- MariaDB：开源免费中小型的数据库
3.SQL
- 英文：Structured Query Language，简称SQL
- 结构化查询语言，一门操作关系型数据库的编程语言
- 定义操作所有关系型数据库的统一标准
- 对于同一个需求，每一种数据库操作的方式可能存在一些不一样的地方，我们称为“方言”
二.MySQL数据库
1.MySQL数据模型
MySQL属于关系型数据库
关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的二维表组成的数据库
优点：
- 都是使用表结构，格式一致，易于维护
- 使用通用的SQL语言操作，使用方便，可用于复杂查询
- 数据存储在磁盘中，安全
2.MySQL命令行中的常用命令
- 将MySQL创建为服务的命令：mysqld -install
- MySQL服务启动命令（管理员身份运行）：net start mysql
- 修改默认账户密码：mysqladmin -u root -p root
- 登录MySQL：mysql -uroot -proot
- 登录参数：mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -p端口号(默认3306)
- 卸载MySQL：mysqld -remove mysql
扩展（忘记了root密码怎么办？）
- 首先以管理员身份打开一个cmd，输入 net stop mysql 停止mysql服务，然后输入mysqld --console --skip-grant-tables --shared-memory 此参数的作用：跳过权限认证
- 再用管理员身份打开一个cmd，这时直接输入mysql -uroot -p，弹出Enter password:直接回车，就进入mysql了
- 然后输入
use mysql;（使用数据库名为mysql），输入ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newPassword';设置的新密码’; - 刷新权限表，使密码生效：
flush privileges;
3.图形化管理工具
MySQL的管理维护工具非常之多，除了系统自带的命令行管理工具之外，还有许多其它的图形化管理工具：
小编我就主要介绍几个常用的：
| 图形化管理工具 | 特点 | 官网链接 |
|---|---|---|
| phpMyAdmin | 最常用的MySQL维护工具，是一个用PHP开发的基于Web方式架构在网站主机上的MySQL管理工具 | 官网地址 |
| MySQLDumper | 使用PHP开发的MySQL数据库备份恢复程序 | 官网地址 |
| Navicat | Navicat是一个桌面版MySQL数据库管理和开发工具。和微软SQLServer的管理器很像，易学易用。Navicat使用图形化的用户界面，可以让用户使用和管理更为轻松。支持中文，有免费版本提供。 | 官网地址 |
| MySQL GUI Tools | MySQL官方提供的图形化管理工具，功能很强大，值得推荐，可惜的是没有中文界面。 | 官网地址 |
| MySQL ODBC Connector | MySQL官方提供的ODBC接口程序，系统安装了这个程序之后，就可以通过ODBC来访问MySQL，这样就可以实现SQLServer、Access和MySQL之间的数据转换，还可以支持ASP访问MySQL数据库。 | 官网地址 |
小编主要来介绍一下自己使用的Navicat（感觉很好用!!）
Navicat
- Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案。
- 这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面
- 官网：Navicat官网链接
4.SQL
1.SQL通用语法
- SQL语句可以单行或多行书写，以分号结尾
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
- 注释：
- 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
- 多行注释：/* 注释 */
2.SQL分类
- DDL（Data Definition Language）数据定义语言，用来定义数据库对象：数据库，表，列等
- DML（Data Manipulation Language）数据操作语言，用来对数据库中表的数据进行增删改
- DQL（Data Query Language）数据查询语言，用来查询数据库中表的记录（数据）
- DCL（Data Control Language）数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户
数据类型
MySQL支持多种数据类型，可以分为三类：
-
数值
数值类型 大小 描述 TINYINT 1 byte 小整数型 SMALLINT 2 bytes 大整数型 MEDIUMINT 3 bytes 大整数型 INT或INTEGER 4 bytes 大整数型 BIGINT 8 bytes 极大整数型 FLOAT 4 bytes 单精度浮点数值 DOUBLE 8 bytes 双精度浮点数值 DECIAML 小数值 -
日期
数值类型 大小 描述 DATE 3 日期 TIME 3 时间值或持续时间 YEAR 1 年份值 DATETIME 8 混合日期和时间值 TIMESAMP 4 混合日期和时间值，时间戳 -
字符串
数值类型 大小 描述 CHAR 0-255 bytes 定长字符串 VARCHAR 0-65535 bytes 变长字符串 TINYBLOB 0-255 bytes 不超过255个字符的二进制字符串 TINYTEXT 0-255 bytes 短文本字符串 BLOB 0-65 535 bytes 二进制形式的长文本数据 TEXT 0-65 535 bytes 长文本数据 MEDIUMTEXT 0-16 777 215 bytes 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据 LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295 bytes 极大文本数据
DDL
作用：操作数据库，表等
-
查询
-- 查询所有数据库 SHOW DATABASES; -- 查询当前数据库下所有表名称 SHOW TABLES; -
创建
-- 创建数据库 CREATE DATABASE 数据库名称; -- 创建数据库（判断，如果不存在则创建） CREATE DATABASE IF NOT EXISTS 数据库名称; -- 创建表 CREATE TABLE 表名( 字段名1 数据类型， 字段名2 数据类型， 字段名3 数据类型 ) -
删除
-- 删除数据库 DROP DATABASE 数据库名称; -- 删除数据库（判断，如果存在则创建） DROP DATABASE IF EXISTS 数据库名称; -- 删除表 DROP TABLE 表名; -- 删除表时判断是否存在 DROP TABLE IF EXISTS 表名; -- 删除列 ALTER TABLE 表名 DROP 列名; -
查看
-- 查看当前使用的数据库 SELECT DATABASE(); -- 使用数据库 use 数据库名称; -- 查询当前数据库下所有表名称 SHOW TABLES; -- 查询表结构 DESC 表名称; -
修改
-- 修改表名 ALTER TABLE 表名 RENAME TO 新的表名; -- 添加一列 ALTER TABLE 表名 ADD 列名 数据类型; -- 修改数据类型 ALTER TABLE 表名 MODIFY 列名 新数据类型; -- 修改列名和数据类型 ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型
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 表名; -- 去除重复记录 使用 distinct 去除 SELECT DISTINCT 字段列表 FROM 表名; --起别名 AS: AS 也可以省略 -
条件查询
-- 条件查询语法 SELECT 字段列表 FROM 表名 WHERE 条件列表;符号 功能 > 大于 < 小于 >= 大于等于 <= 小于等于 = 等于 <>或!= 不等于 BETWEEN…AND… 在某个范围之类(都包含) IN(…) 多选一 LIKE 占位符 模糊查询 _代表单个任意字符，%代表多个任意字符 IS NULL 是NULL IS NOT NULL 不是NULL AND 或 && 并且 OR 或 || 或者 NOT 或 ! 非，不是 -
排序查询
-- 排序查询语法 -- 排序方式 ASC：升序排序（默认） DESC：降序排序 SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2]...; -- 注：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序 -
聚合函数使用
-
概念
将一列数据作为一个整体，进行纵向计算 -
聚合函数分类
函数名 功能 count(列名) 统计数量（一般选用不为null的值） max(列名) 最大值 min(列名) 最小值 sum(列名) 求和 avg(列名) 平均值 -
聚合函数语法
SELECT 聚合函数名(列名) FROM 表;
-
-
分组查询
-- 分组查询语法 -- 注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义 SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件查询];where 和 having 区别：
- 执行时机不一样：where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
- 可判断的条件不一样：where不能对聚合函数进行判断，having可以。
执行顺序： where>集合函数>having
-
分页查询
-- 分页查询语法 使用limit关键字进行分页
-- 起始索引从0开始 计算公式：起始索引=(当前页码-1)*每页显示的条数
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数
tips：
- 分页查询limit是MySQL数据库的方言
- Oracle分页查询使用 rownumber 关键字
- SQL Server分页查询使用 top 关键字
DCL
因为DCL是对数据库进行进行权限控制的，在日常生活中主要用到的是MySQL的CRUD（增删改查），所以无需去仔细研究。
4.约束
1.约束的概念
- 约束是作用在表中列上的规则，用于限制加入表的数据
- 约束的存在保证了数据库中数据的正确性，有效性和完整性
2.约束的分类
| 约束名称 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 保证列中的所有数据不能有null值 | NOT NULL |
| 唯一约束 | 保证列中的所有数据各不相同 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 检查约束 | 保证列中的值满足某一条件 | CHECK |
| 默认约束 | 保存数据时，未指定值则采用默认值 | DEFAULT |
| 外键约束 | 外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性 | FOREIGN KEY |
主要去说一下比较常用的外键约束语法
--创建表时添加外键约束
CREATE TABLE 表名(
列名,数据类型
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
)
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称)
-- 删除约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
5.多表查询
从多张表中查询数据，就是多表查询
多表查询的分类
- 连接查询
- 内连接查询
-- 隐式内连接 SELECT 字段列表 FROM 表1,表2... WHERE 条件; -- 显示内连接 SELECT 字段列表 FROM 表1 [inner] JOIN 表2 ON 条件; - 外连接查询
-- 左外连接：查询表1所有数据和交集部分数据 SELECT 字段名称 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件; -- 右外连接：查询表2所有数据和交集部分数据 SELECT 字段名称 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
- 内连接查询
- 子查询（嵌套查询）
子查询根据查询结果不同，作用不同： -- 单行单列：作为条件值，使用=!=><等进行条件判断 SELECT 字段列表 FROM 表 WHERE 字段名=(子查询); -- 多行单列：作为条件值，使用in等关键字进行条件判断 SELECT 字段列表 FROM 表 WHERE 字段名 in(子查询); -- 多行多列：作为虚拟表 SELECT 字段列表 FROM (子查询) WHERE 条件;
6.事务
1.事务简介
- 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令
- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
- 事务是一个不可分隔的工作逻辑单元
2.事务操作
-- 开启事务
START TRANSACTION;
或者 BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
注意：MySQL事务是默认自动提交，Oracle事务是手动提交
-- 查看事务的默认提交方式
SELECT @@autocommit;
-- 1 自动提交 0 手动提交
-- 修改事务提交方式
set @@autocommit=0;
3.事务四大特征
- 原子性（Atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
- 隔离性（Isolation）：多个事务之间，操作的可见性
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
三.数据库设计
1.数据库设计简介
1.软件研发步骤
2.数据库设计概念
- 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
- 建立数据库中表结构以及表与表之间的关联关系的过程
- 用大白话来说就是设计：有哪些表？表里有哪些字段？表和表之间有什么关系？
3.数据库设计的步骤
- 需求分析（数据是什么？数据具有哪些属性？数据和属性的特点是什么）
- 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
- 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
- 维护设计（1.对新的需求进行建表，2.表优化）
2.表关系
1.一对多（多对一）
特点
- 多的一方叫从表，一的一方叫主表
- 外键是存在从表中，在从表中设置一个字段作为外键关联主表的主键id字段
- 增加数据的时候，需要先添加主表中的数据，再添加从表数据
- 删除数据的时候，需要先删除从表数据，再删除主表数据。
实现方式：在多的一方建立外键，指向一的一方的主键
一对多图解：
2.多对多
特点
- 需要一个中间的外键表，存储两个表的关联外键
- 外键所在表叫从表，两个多对多关系表是主表
- 增加数据的时候，需要先添加主表中的数据，再添加从表数据
- 删除数据的时候，需要先删除从表数据，再删除主表数据。
实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两个主表主键
图解
3.一对一
特点
一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
实现方式：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一（UNIQUE）
图解
1648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
