变性人做欲网站,网站体验提升思路,百度网络营销,合肥建设网官方网站文章目录 前言1. 数据库的相关概念1.1 数据1.2 数据库1.3 数据库管理系统1.4 数据库系统1.5 SQL 2. MySQL数据库2.1 MySQL安装2.2 MySQL配置2.2.1 添加环境变量2.2.2 新建配置文件2.2.3 初始化MySQL2.2.4 注册MySQL服务2.2.5 启动MySQL服务 2.3 MySQL登录和退出2.4 MySQL卸载2.… 文章目录 前言1. 数据库的相关概念1.1 数据1.2 数据库1.3 数据库管理系统1.4 数据库系统1.5 SQL 2. MySQL数据库2.1 MySQL安装2.2 MySQL配置2.2.1 添加环境变量2.2.2 新建配置文件2.2.3 初始化MySQL2.2.4 注册MySQL服务2.2.5 启动MySQL服务 2.3 MySQL登录和退出2.4 MySQL卸载2.5 MySQL数据模型 3. SQL语句3.1 SQL简介3.2 通用语法3.3 SQL分类 4. DDL- 操作数据库4.1 查询4.2 创建数据库4.3 删除数据库4.4 使用数据库 5. DDL- 操作数据表5.1 数据类型5.2 查询表5.3 创建表5.4 删除表5.5 修改表 6. 实战案例详解7. DML- 增删改数据7.1 添加数据7.2 修改数据7.3 删除数据 8. DQL- 数据的查询操作8.1 基础查询1. 基础查询语法2. 基础查询练习 8.2 条件查询1. 条件查询语法2. 条件查询练习 8.3 排序查询1. 排序查询语法2. 排序查询练习 8.4 聚合函数1. 聚合函数语法2.聚合函数练习 8.5 分组查询1. 分组查询语法2. 分组查询练习 8.6 分页查询1. 分页查询语法2. 分页查询练习 9. 约束的概念10. 约束的分类11. 非空约束12. 唯一约束13. 主键约束14. 默认约束15. 外键约束16. 约束的案例练习17. 数据库设计17.1 概念17.2 表的关系 18. 多表查询18.1 内连接查询18.2 外连接查询18.3 子查询 19. 事务19.1 概念19.2 语法19.3 特征 20. 总结 前言
这是一篇 MySQL 通关一篇过硬核经验学习路线包括数据库相关知识SQL语句的使用数据库约束设计等。整理的初衷是为了记录自己的成长同时帮助到需要学习和查看的朋友。
1. 数据库的相关概念
1.1 数据
数据是指数据库中存储的基本对象是描述事物的符号记录。
1.2 数据库
数据库是指存放数据的仓库长期存放在计算机内的有组织可共享的数据集合。 数据库技术解决了数据的持久化存储问题同时利用数据库管理系统解决了对大量数据操作繁琐的问题。
1.3 数据库管理系统
数据库管理系统是指位于用户和操作系统之间的一层数据管理软件科学的组织和存储高效的获取和维护。 我们所说的 MySQL 数据库就是指 MySQL 数据库管理系统在大家日常的使用习惯中把他叫做 MySQL 数据库。在我们的电脑安装好数据库管理系统软件以后就可以创建数据库来管理数据同时也可以对数据库中的数据进行增删改查的操作。
1.4 数据库系统
数据库数据库管理系统应用程序和数据库管理员共同组成了数据库系统。
1.5 SQL
SQL的英文是 Structured Query Language简称 SQL是一种操作关系型数据库的结构化查询语言我们在操作数据库时经常用到的操作就是查询操作。
SQL定义了操作所有关系型数据库的统一标准可以使用 SQL 操作所有的关系型数据库管理系统在使用其他的数据库管理系统时也同样可以使用 SQL 来操作。
2. MySQL数据库
2.1 MySQL安装
MySQL有很多的安装方式这里可以使用绿色版避免了安装版的一些繁琐的操作直接在官网下载与自己电脑相对应的版本的压缩文件然后将压缩文件解压到一个非中文的目录中。 这里选择下载的产品版本是 5.7.24 原因是各个库支持的最完善且相对于最新版本更加的稳定。
2.2 MySQL配置
2.2.1 添加环境变量 右键此电脑 / 属性 / 高级系统设置 / 环境变量 在系统变量中新建变量命名为 MYSQL_HOME变量值为刚才 MySQL 的存放路径。
双击系统变量中的 Path值为 %MySQL_HOME%\bin 。添加环境变量的方式与前面学习配置 Java 环境变量大致相同。
那么我们为什么要先配置环境变量呢
平时我们在命令行窗口中输入一个可执行程序的命令时Windows 会先在环境变量中的Path所指的路径中寻找如果找到就直接执行没找到就在当前工作目录中寻找如果还没找到就会报错。
我们添加环境变量的目的就是能够在任意路径下运行配置了环境变量的程序而不用总是修改工作目录大大简化了操作。
我们如何验证添加环境变量成功呢
此时我们只需要以管理员身份运行命令提示符工具执行 mysql如果提示 Cant connect to MySQL server on localhost则环境变量添加成功。 注此时必须以管理员身份运行命令提示符工具否则会报错。 2.2.2 新建配置文件
在MySQl的根目录中创建一个配置文件 my.ini ,其内容为
[mysql]
default-character-setutf8[mysqld]
character-set-serverutf8
default-storage-engineINNODB
sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION主要目的是配置数据库的默认编码集为utf-8和默认存储引擎为INNODB。
2.2.3 初始化MySQL
在命令提示符窗口中运行mysqld --initialize-insecure如果没有出现报错则证明data目录初始化成功。
mysqld --initialize-insecure此时当我们再打开查看MySQL目录已经有data目录生成。
2.2.4 注册MySQL服务
在命令提示符窗口中运行 mysqld -install此时你的电脑就成功注册了MySQL服务此时你的电脑就可以称为MySQL服务器。
mysqld -install2.2.5 启动MySQL服务
在命令提示符窗口中运行net start mysql此时我们已经成功启动MySQL服务。
net start mysql运行net stop mysql即可停止MySQL服务。
net stop mysql可以通过运行mysqladmin -u root password 1234修改默认账户密码这里的1234指默认管理员(即root账户)的密码。
mysqladmin -u root password 12342.3 MySQL登录和退出
在命令行中运行mysql -uroot -p,按照提示输入密码 即登录成功。
mysql -uroot -p密码登录命令中的参数
mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)退出MySQL时
exit
quit2.4 MySQL卸载
我们只需要简单的三步就可以完成MySQL的卸载
第一步运行net stop mysql
net stop mysql第二步运行mysqld -remove mysql
mysqld -remove mysql第三步删除MySQL目录及相关的环境变量。
2.5 MySQL数据模型
MySQl 是一个关系型数据库。关系型数据库是建立在关系模型基础上的数据库简单来说关系型数据库是由多张能互相连接的二维表组成的数据库。
与关系型数据库对应的是非关系型数据库关系型数据库有很多的优点例如都是使用表结构格式一致易于维护。使用通用的 SQL 语言操作方便快捷可用于复杂查询等。 我们通过客户端可以用数据库管理系统创建数据库在数据库中创建表在表中添加数据。创建的每一个数据库对应到磁盘上都是一个文件夹而一个数据库下可以创建多张表文件夹中的 .frm 就是表文件.MYD是数据文件通过这两个文件就可以查询到数据展示成二维表的效果。
3. SQL语句
3.1 SQL简介
SQL被称为结构化查询语言可以用于对所有的关系型数据库进行操作即我们可以通过SQL语句对数据库、表、数据进行增删改查操作。
SQL定义了操作关系型数据库的统一标准但是对于同一个需求每一种数据库操作的方式可能会存在一些不一样的地方。
3.2 通用语法 MySQL 数据库的 SQL 语句不区分大小写关键字建议使用大写。
SQL 语句可以单行或多行书写以分号结尾。
注释 单行注释: – 注释内容 或 #注释内容(MySQL 特有) 多行注释: /* 注释 */
3.3 SQL分类 DDL 数据定义语言用来定义数据库对象数据库表列等
DML 数据操作语言用来对数据库中表的数据进行增删改
DQL数据查询语言用来查询数据库中表的记录(数据)
DCL数据控制语言用来定义数据库的访问权限和安全级别及创建用户
数据是数据库中非常重要的部分所以在日后的操作中我们最常进行的是对数据的一些操作即对数据进行怎删改查所以最常用操作的是 DML 和 DQL 。
总的来说DML用于对数据增删改DQL用于对数据查询操作DDL用于操作数据库而DCL是用来控制权限。 SQL 的基本操作一般是指对数据库数据表数据的增删改查。
4. DDL- 操作数据库
首先要学习的是使用DDL来对数据库进行操作主要是对数据库的增删改查操作。
4.1 查询
查询所有的数据库
show databases;例如 4.2 创建数据库
创建新的数据库
create database 数据库名称;使用上面的方式创建新的数据库时如果该数据库已经存在则会出现错误所以我们在创建新的数据库时一般会判断该数据库是否存在如果已存在则不会创建。
创建新的数据库(判断数据库是否已经存在)
create database if not exists 数据库名称;例如 4.3 删除数据库
删除数据库
drop database 数据库名称;和前面创建新的数据库相同为了避免出现错误我们一般会先判断该数据库是否存在如果不存在则不会删除。
删除数据库(判断数据库是否已经存在)
drop database if exists 数据库名称;例如 4.4 使用数据库
现在我们已经成功创建了新的数据库接下来我们要在数据库中创建数据表首先我们要明白是对哪一个数据库进行操作此时我们要先学会使用数据库才能对数据库中的表进行操作。
使用数据库
use 数据库名称;查询当前正在使用的数据库
select database();例如 5. DDL- 操作数据表
同样对数据表的操作无非就是增删改查在学习相关操作之前我们先要熟悉一下MySQL的数据类型。
5.1 数据类型
MySQL支持多种数据类型但是大致可以分为3中分别是数值型日期型和字符串型。下面是比较常用的几种数据类型
数值
数据类型解释tinyint小整数型占1个字节int大整数型占4个字节double浮点类型
日期
数据类型解释date日期值只包含年月日datetime混合日期和时间值包含年月日时分秒
字符串
数据类型解释char定长字符串varchar变长字符串
定长字符串和变长字符串的区别字符串是我们在数据库中经常使用的数据类型使用变长字符串如果字符的长度没有达到指定的长度那么实际的长度是多少就占用几个字符这样的做法显然是使用时间换空间而使用定长字符串虽然有时会出现浪费空间的情况但是一般储存性能比较高。
5.2 查询表
查询当前数据库下所有的表
show tables;查询表结构
desc 表名称;例如 5.3 创建表
创建一个新的表
create table 表名称(字段名1 数据类型,字段名2 数据类型,...字段名n 数据类型 #这里是不需要加上,的
);例如 5.4 删除表
删除表
drop table 表名;删除表(判断表是否存在)
drop table if exists 表名;例如 5.5 修改表
修改表名
alter table 表名 rename to 新的表名;增加一列
alter table 表名 add 列名 数据类型;修改数据类型
alter table 表名 modify 列名 新数据类型;修改列名和数据类型
alter table 表名 change 列名 新列名 新数据类型;删除列
alter table 表名 drop 列名;例如 6. 实战案例详解
需求设计包含如下信息的学生表请注重数据类型、长度的合理性。
编号姓名姓名最长不超过10个汉字性别因为取值只有两种可能因此最多一个汉字生日取值为年月日成绩小数点后保留两位地址最大长度不超过 64学生状态用数字表示正常、休学、毕业…
在完成这样一个案例前首先要创建一个学生数据库在数据库中创建一张新的表创建表时注意语法格式数据类型和长度的合理性。
以管理员身份运行命令提示符cmd启动Mysql服务登录MySQL 创建学生信息数据库
create database if not exists student;使用student数据库
use student;创建数据表
create table stu(id int ,-- 编号name varchar(10),-- 姓名gender char(1),-- 性别birthday date,-- 生日score double(5,2) ,-- 分数addr varchar(50),-- 地址status tinyint-- 状态
);现在我们已经学会了写SQL来操作数据库但是我们在命令行中写SQL时往往有体验感差效率低等问题现在开始我们就要学习在MySQL的图形化客户端Navicat中执行SQL语句。 Navicat 为数据库管理、开发和维护提供了一款直观而强大的图形化界面大大的提高了工作效率建议在学习中也使用这款开发工具。接下来在Navicat中新建数据库新建查询我们就可以编写SQL并且执行SQL语句了。
7. DML- 增删改数据
7.1 添加数据
给指定列添加数据
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...)...;在开发过程中添加数据时是不建议省略字段名的这样降低了代码的可读性使效率下降。例如
查询表中的所有数据的方法是
select * from 表名;后面会用到的。
需求往下面的tb_user表中添加一条数据。 insert into tb_user(id,name) values(2,李四);添加成功 7.2 修改数据
修改表的数据
update 表名 set 列名1值1,列名2值2...[where 条件];在修改数据时也可以不使用where条件此时的操作是修改整列数据这样的操作是很危险的。
需求把下面tb_user表中的张三的密码改为abc23 update tb_user set passwor d abc123 where name张三;修改成功 7.3 删除数据
删除表的数据
delete from 表名 [where 条件];在删除某条数据时如果不使用where条件将会导致删除整个表的数据。
需求删除tb_user表中的李四记录。
delete from tb_user where name李四;操作成功 8. DQL- 数据的查询操作
查询是数据操作至关重要的一部分比如说在所有商品中查找出价格在规定范围内的所有商品要想把数据库中的数据在客户端中展示给用户一般都进行了查询的操作。
在实际开发中我们要根据不同的需求并且考虑查询的效率来决定怎样进行查询学习查询前可以先看看查询的完整语法
SELECT字段列表
FROM表名列表
WHERE条件列表
GROUP BY分组字段
HAVING分组后条件
ORDER BY排序字段
LIMIT分页限定根据查询的完整语法中的关键字我们分别来学习基础查询条件查询排序查询分组查询和分页查询。
下面的练习中使用以下的案例学习单表查询
-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int, -- 编号
name varchar(10), -- 姓名
age int, -- 年龄
gender varchar(5), -- 性别
math double(5,2), -- 数学成绩
english double(5,2) -- 英语成绩);
-- 添加数据
INSERT INTO stu(id,name,age,gender,math,english)
VALUES
(1,小张,23,男,66,78),
(2,小李,20,女,98,87),
(3,小陈,55,男,56,77),
(4,小樊,20,女,76,65),
(5,小马,20,男,86,NULL),
(6,小赵,57,男,99,99);在Navicat中选中SQL并执行 8.1 基础查询
1. 基础查询语法
查询多个字段
select 字段列表 from 表名;查询全部字段
select * from 表名;去除重复记录
select distinct 字段列表 from 表名;起别名操作
select 字段名 别名 from 表名;2. 基础查询练习
使用学生表进行基础查询练习
查询多个字段的练习
select name,math from stu;起别名操作练习
select name,english 英语成绩 from stu;8.2 条件查询
1. 条件查询语法
一般语法
select 字段列表 from 表名 where 条件列表;条件查询一般配合运行符进行下面是常见的几个运算符
运算符功能描述 大于 小于 等于 不等于between…and…在这个范围之内in(…)多选一is null / is not null是null / 不是nulland 或 并且or 或 ||或者
2. 条件查询练习
使用学生表进行条件查询练习
查询年龄大于20的学生信息
select * from stu where age20;查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于21岁的学生信息
select * from stu where age in(18,20,21);模糊查询使用like关键字可以使用通配符进行占位:
_ : 代表单个任意字符% : 代表任意个数字符
查询姓名中含有张的学生信息
select * from stu where name like %张%;8.3 排序查询
1. 排序查询语法
select 字段列表 from 表名 order by 排序字段名1 [排序方式]...;注排序方式有两种分别是升序ASC和降序DESC默认情况下是升序ASC。 2. 排序查询练习
使用学生表进行排序查询练习
查询学生信息按照数学成绩降序排列
select * from stu order by math DESC;8.4 聚合函数
1. 聚合函数语法
什么是聚合函数呢在进行查询操作时往往需要对一整列进行运算例如可以计算一整列成绩数据的平均值我们就要使用聚合函数。下面是常见的聚合函数
函数名功能count(列名)统计数量一般选用不为null的列max(列名)最大值min(列名)最小值sum(列名)求和avg(列名)平均值
一般语法
select 聚合函数 from 表名;注NULL值不参与聚合函数运算。 2.聚合函数练习
使用学生表进行聚合函数的练习
统计该表中一共有几个学生
select count(id) from stu;上面我们使用某一字段进行运算这样做可能面临的问题是某一个值可能是NULL所以我们一般使用 * 进行运算因为一行中不可能所有的字段都是NULL。
select count(*) from stu;查询数学成绩的平均分:
select avg(math) from stu;8.5 分组查询
1. 分组查询语法
select 字段列表 from 表名 [where 分组前的条件限定] group by 分组字段名 [having 分组后的条件过滤]注分组之后查询的字段为聚合函数和分组字段查询其他字段无任何意义。 2. 分组查询练习
使用学生表进行分组查询练习
查询男同学和女同学各自的数学平均分以及各自人数要求分数低于70分的不参与分组:
select gender, avg(math),count(*) from stu where math 70 group by gender;查询男同学和女同学各自的数学平均分以及各自人数要求分数低于70分的不参与分组分组之后人数大于2个的:
select gender, avg(math),count(*) from stu where math 70 group by gender having count(*) 2;注where 和 having 执行时机不一样where 是分组之前进行限定不满足where条件则不参与分组而having是分组之后对结果进行过滤。所以where 不能对聚合函数进行判断having 可以。
8.6 分页查询
1. 分页查询语法
在我们的印象中网页在展示大量的数据时往往不是把数据一下全部展示出来而是用分页展示的形式其实就是对数据进行分页查询的操作即每次只查询一页的数据展示到页面上。
select 字段列表 from 表名 limit 查询起始索引,查询条目数;在 limit 关键字中查询起始索引这个参数是从0开始的。
2. 分页查询练习
使用学生表进行分页查询练习
从0开始查询查询3条数据
select * from stu limit 0,3;起始索引 (当前页码 - 1) * 每页显示的条数
在SQL标准中一共规定了6种不同的约束包括非空约束唯一约束和检查约束等而在MySQL中是不支持检查约束的所以这篇文章先对其余5种约束做一个详解和练习。
9. 约束的概念
约束是作用于表中列上的规则用于限制加入表的数据。例如作为主键的列一定是非空的唯一的否则将无法对数据进行区分。约束的存在保证了数据库中数据的正确性、有效性和完整性。所以约束在数据库设计中是非常重要的。
10. 约束的分类
前面说到SQL标准把约束分为了6大类分别是非空约束唯一约束主键约束检查约束默认约束和外键约束添加约束时我们只需要在SQL中添加关键词便可以限制表中的数据。
约束类型功能非空约束 NOT NULL保证列中所有的数据不能有null值唯一约束 UNIQUE保证列中所有数据各不相同主键约束 PRIMARY KEY主键是一行数据的唯一标识要求非空且唯一检查约束 CHECK保证列中的值满足某一条件默认约束 DEFAULT保存数据时未指定值则采用默认值外键约束 FOREIGN KEY外键用来让两个表的数据之间建立链接保证数据的一致性和完整性
11. 非空约束
目的保证列中所有的数据不能有null值
添加约束
CREATE TABLE 表名(列名 数据类型 NOT NULL,…
);
建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;删除约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;12. 唯一约束
目的保证列中所有数据各不相同
添加约束
CREATE TABLE 表名(列名 数据类型 UNIQUE [AUTO_INCREMENT],-- AUTO_INCREMENT: 当不指定值时自动增长…
);CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [约束名称] UNIQUE(列名)
);建完表后添加唯有约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;删除约束
ALTER TABLE 表名 DROP INDEX 字段名;13. 主键约束
目的主键是一行数据的唯一标识要求非空且唯一
添加约束
CREATE TABLE 表名(列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],…
);CREATE TABLE 表名(列名 数据类型,[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);删除约束
ALTER TABLE 表名 DROP PRIMARY KEY;14. 默认约束
目的保存数据时未指定值则采用默认值
添加约束
CREATE TABLE 表名(列名 数据类型 DEFAULT 默认值,…
);建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;删除约束
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;15. 外键约束
当我们添加了外键以后就在数据库层面建立了两张表的关系。
目的外键用来让两个表的数据之间建立链接保证数据的一致性和完整性
添加约束
CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);删除约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;16. 约束的案例练习
首先我们使用以下的案例来练习约束
-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int primary key, -- 编号 主键
name varchar(10) not null unique, -- 姓名 非空唯一
age int not null, -- 年龄 非空
gender varchar(5) not null, -- 性别 非空
math double(5,2) not null, -- 数学成绩 非空
english double(5,2) default 0 -- 英语成绩 默认为0
);验证主键约束其特点是非空且唯一先添加一条数据
insert into stu(id,name,age,gender,math,english) values(1,小张,23,男,66,78);添加第二条数据时尝试 id 添加为空值
insert into stu(id,name,age,gender,math,english) values (null,小李,20,女,98,87);尝试 id 添加为重复的值
insert into stu(id,name,age,gender,math,english) values (1,小陈,55,男,56,77);我们已经验证了主键约束当我们添加不合法的数据时添加失败。
验证非空约束
当我们添加以下的数据时
insert into stu(id,name,age,gender,math,english) values (2,NULL,20,女,76,65);验证唯一约束
当我们添加以下的数据时
insert into stu(id,name,age,gender,math,english) values
(5,小张,20,男,86,NULL);验证默认约束
当我们添加以下的数据时
insert into stu(id,name,age,gender,math) values (6,小赵,23,男,99);验证外键约束
我们使用一下的案例来验证外键约束
-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
-- 部门表
CREATE TABLE dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);此时我们在员工表中添加了外键相当于在数据库层面上建立了两张表的关系此时如果员工表中有员工 a 他属于 1 号部门那么我们想要删除部门表中的 1 号部门就会删除失败因为员工 a 是属于 1 号部门的此时两张表建立了关系。 添加数据
-- 添加 2 个部门
insert into dept(dep_name,addr) values
(研发部,西安),(销售部, 成都);
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES
(张三, 20, 1),
(李四, 20, 2);此时想要是删除销售部门时发现删除失败。 17. 数据库设计
17.1 概念
数据库设计是软件研发过程中很重要的一个环节在学习数据库设计之前要先了解软件研发的步骤。
一个成熟的软件从想法的产生到成功上线要经历需求调研及分析设计编码测试和部署等过程在开始阶段产品经理会根据客户的需求设计产品原型而设计一般有架构师和开发工程师完成这其中就包括数据库设计还有软件结构设计接口设计等接着就是由开发工程师来编写代码由测试工程师来测试接着就可以部署上线。 数据库设计就是根据业务系统的具体需求结合我们所选用的 DBMS为这个业务系统构造出最优的数据存储模型。通俗的说就是建立数据库中的表结构以及表与表之间的关联关系的过程分析有哪些表哪些字段等。
数据库设计分为以下几个步骤
需求分析逻辑分析物理分析维护设计
17.2 表的关系
表和表之间的关系有一对一的关系主要用于表的拆分利于把常用数据和不常用数据分开存储提高查询的效率。一对多或者多对一的关系例如部门表和员工表的关系多对多的关系例如商品和订单的关系。
一对多的关系中在表示多的一方建立外键指向另一方的主键示例
-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
-- 部门表
CREATE TABLE tb_dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE tb_emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
);查看两表结构模型 多对多的关系中建立第三张中间表中间表至少包含两个外键分别关联两方主键例如商品表和订单表。示例
-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;-- 订单表
CREATE TABLE tb_order(id int primary key auto_increment,payment double(10,2),payment_type TINYINT,status TINYINT
);-- 商品表
CREATE TABLE tb_goods(id int primary key auto_increment,title varchar(100),price double(10,2)
);-- 订单商品中间表
CREATE TABLE tb_order_goods(id int primary key auto_increment,order_id int,goods_id int,count int
);-- 建完表后添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES
tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES
tb_goods(id);查看两表的结构模型 一对一的关系中在任意一方加入外键关联另一方主键并且设置外键为唯一(UNIQUE)例如用户和用户详情。
示例
create table tb_user_desc (id int primary key auto_increment,city varchar(20),edu varchar(10),income int,status char(2),des varchar(100)
);
create table tb_user (id int primary key auto_increment,photo varchar(100),nickname varchar(50),age int,gender char(1),desc_id int unique,
-- 添加外键
CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);18. 多表查询
前面学习查询操作时一直是从一张表中查询数据而数据库的多表查询操作也是非常重要的一部分。多表查询就是一次性从多张表中查询出需要的数据。
在MySQL中多表查询分为连接查询和子查询而连接查询又分为内连接查询和外连接查询外连接查询的方式有左外连接查询和右外连接查询。下面对多表查询做一些基础的练习
下面是演示多表查询使用的 sql:
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;# 创建部门表
CREATE TABLE dept(did INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(20)
);
# 创建员工表
CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资dep_id INT,FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES (研发部),(财务部),(销售部);
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,dep_id) VALUES
(小张,男,3000,1),
(小李,女,3600,2),
(小王,男,9000,2),
(小美,女,5000,null);执行 sql 创建表 18.1 内连接查询
内连接查询分为隐式内连接查询和显式内连接查询表示查询 A 集合和 B 集合的交集。 语法
-- 隐式内连接查询
SELECT 字段列表 FROM 表1,表2… WHERE 条件;-- 显示内连接查询
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;隐式内连接查询示例
select * from emp,dept where dep_iddid;结果 显式内连接查询示例
select * from emp inner join dept on dep_iddid;结果 18.2 外连接查询
外连接查询分为作为连接查询和右外连接查询。左外连接查询相当于查询A表所有数据和交集部分数据。 右外连接查询相当于查询B表所有数据和交集部分数据。 语法
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;左外连接查询示例
select * from emp left outer join dept on dep_iddid;结果 右外连接查询示例
select * from emp right outer join dept on dep_iddid;结果 18.3 子查询
子查询是指查询中嵌套有查询以下的几种情况通常使用嵌套查询
子查询语句结果是单行单列子查询语句作为条件值使用 ! 等进行条件判断例如查询员工中工资大于小李的员工信息
select * from emp where salary (select salary from emp where name小李);结果 子查询语句结果是多行单列子查询语句作为条件值使用 in 等关键字进行条件判断例如查询研发部和财务部员工信息 select * from emp where dep_id in (select did from dept where dname研发部 or dname 财务部);结果 子查询语句结果是多行多列子查询语句作为虚拟表例如查询员工中工资大于3000的员工的信息和部门信息
select * from (select * from emp where salary3000) t1, dept where t1.dep_iddept.did;19. 事务
19.1 概念
数据库的事务是一种机制包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求即这一组数据库命令要么同时成功要么同时失败。
例如在进行转账操作时张三转账给李四 500 元首先会验证张三账户余额是否充足如果是则张三账户减少 500李四账户增加 500 。如果在这过程中由于某种原因出现错误李四的账户没有增加 500 那么张三账户减少 500 的操作也不能完成。也就是要保证在这个过程中张三和李四账户余额总和要保持不变。 19.2 语法
开启事务
START TRANSACTION;
或者
BEGIN;回滚事务
commit;提交事务
rollback;小练习张三转账给李四
DROP TABLE IF EXISTS account;
-- 创建账户表
CREATE TABLE account(id int PRIMARY KEY auto_increment,name varchar(10),money double(10,2)
);
-- 添加数据
INSERT INTO account(name,money) values(张三,1000),(李四,1000);添加事务
-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500
-- 2. 张三账户 -500
UPDATE account set money money - 500 where name 张三;
3%0;--出现异常
-- 3. 李四账户 500
UPDATE account set money money 500 where name 李四;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;如果不添加事务张三的账户就会减少 500 而李四的账户并不会增加 500这样并不符合我们的实际生活。否则张三的 500 便不翼而飞了哈哈哈可见事务的重要性。
19.3 特征
MySQl 事务具有四大特征分别是
原子性: 事务是不可分割的最小操作单位要么同时成功要么同时失败一致性 :事务完成时必须使所有的数据都保持一致状态隔离性: 多个事务之间操作的可见性持久性: 事务一旦提交或回滚它对数据库中的数据的改变就是永久的
20. 总结
科技发展社会进步。未来对数据的定义会不断地革新生活的方方面面都会被列入数据的行列。从某种意义上来说数据就是信息只是数据不能直观地带来价值而信息可以但未来这两者之间的距离会越来越缩小直至划上等号。
未来是一个数字化的时代数据是我们最为宝贵的资源不论是哪个专业出身未来或多或少都会卷入数据时代的浪潮之中。而那些前提性的工作基本都是依靠数据库来完成。
祝你事业有成下期见。
