文章目录
前言
什么是数据库?
数据库:DataBase(DB),是存储和管理数据的仓库。
数据库管理系统:DataBase Management System (DBMS),操纵和管理数据库的大型软件。
SQL:Structured Query Language,操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准。
提示:以下是本篇文章正文内容,下面案例可供参考
一、MySQL概述
MySQL连接
语法:
mysql -u用户名 -p密码 [ -h数据库服务器IP地址 -P端口号 ]
MySQL-企业开发使用方式
MySQL数据模型
关系型数据库(RDBMS): 建立在关系模型基础上,由多张相互连接的二维表组成的数据库。
特点:
使用表存储数据,格式统一,便于维护
使用SQL语言操作,标准统一,使用方便,可用于复杂查询
SQL简介
SQL:一门操作关系型数据库的编程语言,定义操作所有关系型数据库的统一标准。
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写。
- 注释:
①单行注释:-- 注释内容 或 # 注释内容(MySQL特有)
②多行注释: /* 注释内容 */
SQL分类
SQL语句通常被分为四大类:
二、数据库设计-DDL
DDL 英文全称是 Data Definition Language,数据定义语言,用来定义数据库对象(数据库、表)。
数据库
- 查询所有数据库:show databases;
- 查询当前数据库:select database();
- 使用数据库:use 数据库名 ;
- 创建数据库:create database [ if not exists ] 数据库名 ;
- 删除数据库:drop database [ if exists ] 数据库名 ;
注意事项:上述语法中的database,也可以替换成 schema。如:create schema db01;
MySQL客户端工具-图形化工具
介绍:DataGrip是JetBrains旗下的一款数据库管理工具,是管理和开发MySQL、Oracle、PostgreSQL的理想解决方案。
官网: https://www.jetbrains.com/zh-cn/datagrip/
安装: 参考资料中提供的《DataGrip安装手册》
IntelliJ IDEA连接数据库
表(创建、查询、修改、删除)
创建表
create table 表名(
字段1 字段类型 [ 约束 ] [ comment 字段1注释 ] ,
…
字段n 字段类型 [ 约束 ] [ comment 字段n注释 ]
) [ comment 表注释 ] ;
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确性、有效性和完整性。
数据类型
MySQL中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型。
1.数值类型
2.字符串类型
3.日期时间类型
参照 E:\java\javaweb\day06-MySQL\资料\04. MySQL数据类型
案例:根据页面原型/需求创建表(设计合理的数据类型、长度、约束)
请参考资料中提供的《黑马-tlias智能学习辅助系统》页面原型,设计员工管理模块的表结构(暂不考虑所属部门字段)
注意事项:create_time:记录的是当前这条数据插入的时间。 update_time:记录当前这条数据最后更新的时间。
1.查询
查询当前数据库所有表:show tables;
查询表结构:desc 表名;
查询建表语句:show create table 表名;
2.修改
- 添加字段:alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
- 修改字段类型:alter table 表名 modify 字段名 新数据类型(长度);
- 修改字段名和字段类型:alter table 表名 change 旧字段名 新字段名 类型 (长度) [comment 注释] [约束];
- 删除字段:alter table 表名 drop column 字段名;
- 修改表名: rename table 表名 to 新表名;
3.删除
删除表:drop table [ if exists ] 表名;
注意:在删除表时,表中的全部数据也会被删除。
三、数据库操作-DM
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。
- 添加数据(INSERT)
- 修改数据(UPDATE)
- 删除数据(DELETE)
DML(INSERT)
- 指定字段添加数据: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, …);
注意:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。`
DML(UPDATE)
修改数据:update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ] ;
注意事项:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
DML(DELETE)
删除数据:delete from 表名 [ where 条件 ];
注意:
- DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
- DELETE 语句不能删除某一个字段的值(如果要操作,可以使用UPDATE,将该字段的值置为NULL)。
小结:
四、数据库操作-DQL
1.DQL-基本查询
- 查询多个字段:select 字段1, 字段2, 字段3 from 表名;
- 查询所有字段(通配符):select * from 表名;
- 设置别名:select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
- 去除重复记录:select distinct 字段列表 from 表名;
* 号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)。
-- 1.查询指定字段 name,entrydate 并返回
select name,entrydate from tb_emp;
-- 2.查询返回所有字段
-- 推荐
select id,username,password,name,gender,image,job,entrydate,
create_time,update_time from tb_emp;
-- 不推荐(不直观,性能低)
select * from tb_emp;
-- 3.查询所有员工的name,entrydate,并起别名(姓名、入职日期)
select name as 姓名,entrydate as 入职日期 from tb_emp;
SELECT NAME 姓名,entrydate 入职日期 FROM tb_emp;
-- 4. 查询已有的员工关联了哪几种职位(不重复)
SELECT DISTINCT job FROM tb_emp;
2.DQL-条件查询
条件查询:select 字段列表 from 表名 where 条件列表 ;
代码如下(示例):
-- 1.查询 姓名为杨逍 的员工
select * from tb_emp where name='杨逍';
-- 2.查询 id小于5 的员工信息
select * from tb_emp where id <= 5;
-- 3.查询 没有分配职位 的员工信息
SELECT * FROM tb_emp WHERE job is null;
-- 4.查询 有职位 的员工信息
SELECT * FROM tb_emp WHERE job IS not NULL;
-- 5.查询 密码不等于123456 的员工信息
SELECT * FROM tb_emp WHERE password !='123456';
-- 6.查询 入职日期在'2000-01-01'(包含)到‘2010-01-01’之间 的员工信息
SELECT * FROM tb_emp WHERE entrydate between '2000-01-01' and '2010-01-01';
-- 7.查询 入职日期在'2000-01-01'(包含)到‘2010-01-01’之间且性别为女 的员工信息
SELECT * FROM tb_emp WHERE entrydate between '2000-01-01' and '2010-01-01' and gender=2;
-- 8.查询 职位是2(讲师)、3(学工主管)、4(教研主管) 的员工信息
SELECT * FROM tb_emp WHERE job in(2,3,4);
-- 9.查询姓名为两个字的员工信息
SELECT * FROM tb_emp WHERE name like '__';
-- 10.查询姓‘张’的员工信息
SELECT * FROM tb_emp WHERE name like '张%';
-- 11.查询姓名包含‘一’的员工信息
SELECT * FROM tb_emp WHERE NAME LIKE '%一%';
3.DQL-分组查询
介绍:将一列数据作为一个整体,进行纵向计算。
语法:select 聚合函数(字段列表) from 表名 ;
注意:
- null值不参与所有聚合函数运算。
- 统计数量可以使用:count( * ) count(字段) count(常量),推荐使用 count( * )
代码如下(示例):
-- 聚合函数
-- 1.统计该企业员工数量 -- count
-- A.count(字段)
#select count(id) from tb_emp;
-- B.count(常量)
#SELECT COUNT(1) FROM tb_emp;
-- C.count(*)
#SELECT COUNT(*) FROM tb_emp;
-- 2.统计该企业最早入职的员工
#SELECT min(entrydate) FROM tb_emp;
-- 3.统计该企业最晚上入职的员工
#SELECT max(entrydate) FROM tb_emp;
-- 4.统计该企业员工id平均值
#SELECT avg(id) FROM tb_emp;
-- 5.统计该企业员工id之和
SELECT SUM(id) FROM tb_emp;
语法:
分组查询:select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];
where与having区别
1.执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
2.判断条件不同:where不能对聚合函数进行判断,而having可以。
代码如下(示例):
-- 分组
-- 1.根据性别分组,统计男性和女性员工的数量 -- count(*)
#select gender,count(*) from tb_emp group by gender;
-- 2.先查询入职时间在‘2015-01-01’以前的员工,并对结果根据职位分组,获取员工数量大于等于2的职位
SELECT job,COUNT(*) FROM tb_emp WHERE entrydate <= '2015-01-01' GROUP BY job HAVING COUNT(*) >=2;
注意:
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
- 执行顺序: where > 聚合函数 > having 。
4.DQL-排序查询
条件查询:select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ]
order by 字段1 排序方式1 , 字段2 排序方式2 … ;
排序方式:
- ASC:升序(默认值)
- DESC:降序
代码如下(示例):
-- 排序查询
-- 1.根据入职时间,对员工进行升序排序 -asc
#select * from tb_emp order by entrydate;
-- 2.根据入职时间,对员工进行倒序排序 -asc
#SELECT * FROM tb_emp ORDER BY entrydate desc;
-- 3.根据 入职时间 对员工进行升序排序,入职时间相同,再按照 更新时间 降序排序
SELECT * FROM tb_emp ORDER BY entrydate ,update_time DESC;
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
5.DQL-分页查询
分页查询:select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
代码如下(示例):
-- 分页查询
-- 1.从 起始索引0 开始查询员工数据,每页展示5条记录
#select * from tb_emp limit 0,5;
-- 2.查询第1页员工数据,每页展示5条数据
#SELECT * FROM tb_emp LIMIT 5;
-- 3.查询第2页员工数据,每页展示5条数据
#SELECT * FROM tb_emp LIMIT 5,5;
-- 4.查询第3页员工数据,每页展示5条数据
SELECT * FROM tb_emp LIMIT 10,5;
-- 起始索引=(页码 -1)* 每页展示记录数
注意事项:
- 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
案例
案例一:根据需求完成员工管理的条件分页查询
代码如下(示例):
-- 案例1:按需求完成员工管理的条件分页查询- 根据输入条件,查询第一页数据,每页展示10条记录
-- 输入条件:
-- 姓名:张
-- 性别:男
-- 入职时间:2000-01-01 2015-12-31
SELECT * FROM tb_emp WHERE NAME LIKE '%张%'
AND gender=1
AND entrydate BETWEEN '2000-01-01' AND '2015-12-31'
ORDER BY update_time DESC LIMIT 0,10;
案例二、根据需求,完成员工信息的统计
函数:
- if(表达式, tvalue, fvalue):当表达式为true时,取值tvalue;当表达式为false时,取值fvalue
- case expr when value1 then result1 [when value2 then value2 …] [else result] end
代码如下(示例):
-- 案例2-1:根据需求完成员工性别信息的统计 - count(*)
-- if(条件表达式,true取值,false取值)
#select if(gender=1,'男性员工','女性员工') 性别,count(*)
from tb_emp group by gender;
-- 案例2-2:根据需求完成员工职位信息的统计 - count(*)
-- case 表达式 when 值1 then 结果1 when 值2 then 结果2 ...else..end
SELECT (CASE job WHEN 1 THEN '班主任'
WHEN 2 THEN '讲师'
WHEN 3 THEN '学工主管'
WHEN 4 THEN '教研主管'
ELSE '未分配职位' END) 职位,COUNT(*)
FROM tb_emp GROUP BY job;
小结
五、多表设计
1.一对多
需求:根据 页面原型 及 需求文档 ,完成部门及员工模块的表结构设计。
一对多关系实现:在数据库表中多的一方,添加字段,来关联一的一方的主键。
多表问题分析
部门数据可以直接删除,然而还有部分员工归属于该部门下,此时就出现了数据的不完整、不一致问题。
目前上述的两张表,在数据库层面,并未建立关联,所以是无法保证数据的一致性和完整性的------->(外键)
外键约束
-- 创建表时指定
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);
-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
2.一对一
案例: 用户 与 身份证信息 的关系
关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率
3.多对多
案例: 学生 与 课程的关系
关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
案例
参考页面原型及需求,设计合理的表结构
需求:参考资料中提供的 《苍穹外卖_管理后台》 页面原型,设计分类管理、菜品管理、套餐管理模块的表结构
步骤:
①. 阅读页面原型及需求文档,分析各个模块涉及到的表结构,及表结构之间的关系。
②. 根据页面原型及需求文档,分析各个表结构中具体的字段及约束
小结
六、多表查询
1.内连接
语法
- 隐式内连接:select 字段列表 from 表1 , 表2 where 条件 … ;
- 显式内连接:select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 … ;
代码如下(示例):
-- ============================= 内连接 ==========================
-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
#select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;
-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
#SELECT tb_emp.name,tb_dept.name FROM tb_emp inner join tb_dept on tb_emp.dept_id=tb_dept.id;
-- 起别名
#SELECT e.name,d.name FROM tb_emp e INNER JOIN tb_dept d ON e.dept_id=d.id;
2.外连接
语法
●左外连接:select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;
●右外连接:select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;
代码如下(示例):
-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
#select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id=d.id
-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
SELECT e.name,d.name FROM tb_emp e right JOIN tb_dept d
ON e.dept_id=d.id
SELECT e.name,d.name FROM tb_dept d left JOIN tb_emp e
ON e.dept_id=d.id
3.子查询
概述:
- 介绍:SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
- 形式:select * from t1 where column1 = ( select column1 from t2 … );
- 子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。
分类:
- 标量子查询:子查询返回的结果为单个值
- 列子查询:子查询返回的结果为一列
- 行子查询:子查询返回的结果为一行
- 表子查询:子查询返回的结果为多行多列
①标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式
常用的操作符:= <> > >= < <=
-- 标量子查询
-- A. 查询 "教研部" 的所有员工信息
-- 1.查询 教研部 部门id - tb_dept
#select id from tb_dept where name='教研部';
-- 2.再查询该id下的员工信息
select * from tb_emp where
dept_id=(SELECT id FROM tb_dept WHERE NAME='教研部');
-- B. 查询在 "方东白" 入职之后的员工信息
-- 1.查询方东白入职时间
#select entrydate from tb_emp where name='方东白';
-- 2.查询在 "方东白" 入职之后的员工信息
select * from tb_emp where
entrydate>(SELECT entrydate FROM tb_emp WHERE NAME='方东白');
②列子查询
子查询返回的结果是一列(可以是多行)
常用的操作符:in 、not in等
-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
#select id from tb_dept where name in('教研部','咨询部');
#select * from tb_emp where dept_id
in(SELECT id FROM tb_dept WHERE NAME IN('教研部','咨询部'));
③行子查询
子查询返回的结果是一行(可以是多列)。
常用的操作符:= 、<> 、in 、not in
-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
#SELECT entrydate,job FROM tb_emp WHERE NAME='韦一笑';
#select * from tb_emp where
(entrydate,job)=
(SELECT entrydate,job FROM tb_emp WHERE NAME='韦一笑');
④表子查询
子查询返回的结果是多行多列,常作为临时表
常用的操作符:in
-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门名称
-- a. 查询入职日期是 "2006-01-01" 之后的员工信息
#select * from tb_emp where entrydate>'2006-01-01';
-- b.查询这部分员工信息及其部门名称- tb_dept
SELECT e.*,d.name FROM
(SELECT * FROM tb_emp WHERE entrydate>'2006-01-01') e ,
tb_dept d WHERE e.dept_id=d.id;
#select e.*,d.name from
(SELECT * FROM tb_emp WHERE entrydate>'2006-01-01') e join
tb_dept d on e.dept_id=d.id;
案例
根据需求,完成多表查询的SQL语句编写
●将资料中准备好的多表查询的数据准备的SQL脚本导入数据库中。
需求:
1.查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称
2.查询所有价格在 10元(含)到50元(含)之间 且 状态为"起售"的菜品名称、
价格及其分类名称 (即使菜品没有分类 , 也要将菜品查询出来)
3.查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
4.查询各个分类下 菜品状态为 “起售” , 并且该分类下菜品总数量>=3的分类名称
5.查询出 “商务套餐A” 中包含了哪些菜品 (展示出套餐名称、价格, 包含的菜品名称、价格、份数)
6.查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格)
代码如下(示例):
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
-- 表:dish,category
-- SQL:
#select d.name,c.name,d.price from dish d,category c
where d.category_id=c.id and d.price<10;
-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
-- 表:dish,category
-- SQL:
SELECT d.name,c.name,d.price
FROM dish d
left join category c on d.category_id=c.id
where d.price between 10 and 50
and d.status=1 ;
-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
-- 表:dish,category
-- SQL:
#select c.name,max(d.price) from dish d,category c
where d.category_id=c.id group by c.name ;
-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
-- 表:dish,category
-- SQL:
#select c.name,count(*) from dish d,category c
where d.category_id=c.id group by c.name having count(*)>=3;
-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 (展示出套餐名称、价格, 包含的菜品名称、价格、份数).
-- 表:setmeal,setmeal_dish,dish
-- SQL:
select s.name,s.price,d.name,d.price,sd.copies
from dish d,setmeal_dish sd,setmeal s
where d.id=sd.dish_id and sd.setmeal_id=s.id
and s.name='商务套餐A';
-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
-- 表:dish
-- SQL:
#select avg(price) from dish
SELECT NAME,price FROM dish
WHERE price<(SELECT AVG(price) FROM dish)
小结
七、事物
场景:学工部 整个部门解散了,该部门及部门下的员工都需要删除了
-- 删除学工部
delete from tb_dept where id = 1;
-- 删除学工部的员工
delete from tb_emp where dept_id = 1
问题:如果删除部门成功了,而删除该部门的员工时失败了,就造成了数据的不一致
1.介绍 & 操作
介绍
概念:
事务 是一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
注意:默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
操作
事务控制
- 开启事务:start transaction; / begin ;
- 提交事务:commit;
- 回滚事务:rollback;
2.四大特性
四大特性(ACID)
代码如下(示例):
-- 删除部门
#delete from tb_dept where id=1;
-- 删除部门下的员工
#delete from tb_emp where dept_id=1;
-- 开启事务
START TRANSACTION;
-- 删除部门
DELETE FROM tb_dept WHERE id=3;
-- 删除部门下的员工
#delete from tb_emp where dept_id=3;
-- 提交事务
#commit;
-- 回滚事务
#rollback;
SELECT * FROM tb_dept;
SELECT * FROM tb_emp;
八、索引
介绍
概念:索引(index)是帮助数据库 高效获取数据 的 数据结构
优缺点
结构
MySQL数据库支持的索引结构有很多,如:Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引,如果没有特别指明,都是指默认的
B+Tree
结构组织的索引。
思考:存在什么问题?
大数据量情况下,层级深,检索速度慢。
B+Tree(多路平衡搜索树)
- 每一个节点,可以存储多个key(有n个key,就有n个指针)。
- 所有的数据都存储在叶子节点,非叶子节点仅用于索引数据。
- 叶子节点形成了一颗双向链表,便于数据的排序及区间范围查询。
语法
创建索引
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
查看索引
show index from 表名;
删除索引
drop index 索引名 on 表名;
代码如下(示例):
-- 创建索引
#create index idx_emp_name on tb_emp(name);
-- 查看索引
#show index from tb_emp;
-- 删除索引
#drop index idx_emp_name on tb_emp;
注意事项
- 主键字段,在建表时,会自动创建主键索引。
- 添加唯一约束时,数据库实际上会添加唯一索引。