MySQL
JavaWeb
学习Java主要可以用来干什么
开发网站-- 让人用
办公类网站
财务系统
人事管理
娱乐性网站
? B站
? 抖音
? 腾讯视频
JavaWeb技术的交互
概述
使用java + 其他技术完成 网站的开发
其他技术:
数据库
前端相关语言
数据库软件(数据库管理系统) :
? 是一个软件
数据库:
由数据库软件创建的管理数据的仓库(文件夹)
SQL:
Structured Query Language，简称 SQL，结构化查询语言
是一门语言,可以操作所有的关系型数据库
什么是数据库管理系统
什么是SQL
数据存储
数据存储的要求:
- 数据必须能够永久保存
- 高效的存储及高效的查询
- 应有权限管理功能
演变:
? 需要一个软件能帮我们存储数据, 高效管理数据
如何安装/卸载MySQL
安装
傻瓜式安装
Mysql官网
MySQL
我们安装了啥?
- mysql 是一个没有界面的后台软件(msyql 服务)
- mysql 软件自带了一个客户端 (黑窗口方式)
为啥要配置环境变量
可以再任意位置使用 cmd 命令行窗口 使用命令
如何登录 mysql
win+R —>cmd -->
登录>mysql -uroot -proot
退出: exit/quit
卸载
傻瓜式卸载
没有卸载干净的方式
- 安装 ccleaner 软件
- 清理注册表–>重启
- 以管理身份运行 cmd
mysqld remove xxxxxx
MySQL基本概述
数据库:
存储数据的仓库(文件夹), 仓库中有很多表
表:
用来存储数据的二维表
数据:
真实的数据
SQL语言
sql 是一门语言, 有它独特的语法
基础语法
1. 以分号结尾。
2. 关键字不区分大小写;
3, 注释
-- 注释内容
/* 多行注释*/
SQL语法分类
DDL :
用来定义表名称,数据库名称, 用来修改表结构
DML :
修改数据的 insert into / update /delete
DQL :
查询数据的 select 单表.多表(内外连接),子查询
DCL :
权限控制
set password=password(‘root’);
DDL
操作数据库
-- 查询所有的数据库
SHOW DATABASES;
-- 创建数据库
CREATE DATABASE 数据库名称;
-- 创建数据库(判断，如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;
-- 删除数据库
DROP DATABASE 数据库名称;
-- 删除数据库(判断，如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;
-- 使用(进入)数据库
USE 数据库名称;
-- 查看当前使用的数据库
SHOW TABLES;
注意事项
操作表
CREATE TABLE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
…
字段名n 数据类型n
);
注意事项
表名称
-
一般会加一个有意义的前缀
比如:tb_(tb=table)
-
如果多个单词需要分割 使用的是"_"
-
windows系统不区分大小写，Linux系统区分大小写
小括号
SQL里边是小括号,Java中则是大括号注意区分
其他
- 多个字段最后一个数据没有";"
- 字符串类型必须指定长度
数据类型
数值
tinyint :
小整数型，占一个字节
int：
大整数类型，占四个字节
eg ： age int
bigint :
整数类型，占八个字节
double ：
浮点类型
使用格式： 字段名 double(总长度,小数点后保留的位数)
eg ： score double(5,2)
日期
date ：
日期值。只包含年月日yyyy-mm-dd
eg ：birthday date ：
datetime ：
混合日期和时间值。包含年月日时分秒 yyyy-mm-dd HH:mi:ss
字符串
char ：
定长字符串。
优点：
存储性能高
缺点：
浪费空间
eg ： name char(10) 如果存储的数据字符个数不足10个，也会占10个的空间
varchar ：
长字符串。
? 优点：
节约空间
缺点：
存储性能底
eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间
修改表结构
修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
-- 将表名student修改为stu
alter table student rename to stu;
添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);
修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);
修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);
删除列
ALTER TABLE 表名 DROP 列名;
-- 将stu表中的addr字段 删除
alter table stu drop addr;
删除表
drop table 表名;
-- 将stu表删除
drop table stu;
DML
这里使用案例进行了解掌握
基础查询Ⅰ
DROP DATABASE
IF
EXISTS mydb;-- 如果存在mydb数据库就删除
CREATE DATABASE mydb;-- 创建mydb数据库(前边判断删除，所以这里不进行判断)
USE mydb;-- 进入mydb数据库
CREATE TABLE stu (-- 创建表
id INT,-- id(编号) 大整数类型
NAME VARCHAR ( 20 ),-- name(姓名) 可变字符串类型
gender CHAR ( 2 ),-- gender(性别) 不变字符串类型
birthday date,-- birthday(生日) 日期类型
score DOUBLE ( 5, 2 ),-- score(分数) 浮点类型，保留两位小数
email VARCHAR ( 15 ),-- email(日期) 可变字符串类型
tel VARCHAR ( 15 ),-- tel (电话) 可变字符串类型
STATUS TINYINT -- status (状态) 小整数类型,占 1个字节
);
-- 给指定列添加数据
INSERT INTO stu ( id, NAME )
VALUES
( 1, '张三' );
-- 给所有列添加数据，列名的列表可以省略的
INSERT INTO stu ( id, NAME, gender, birthday, score, email, tel, STATUS )
VALUES
( 2, '李四', '男', '1999-11-11', 88.88, 'lisi@itcast.cn', '13888888888', 1 );
INSERT INTO stu
VALUES
( 3, '李四', '男', '1999-11-11', 88.88, 'lisi@itcast.cn', '13888888888', 1 );
-- 批量添加数据
INSERT INTO stu
VALUES
( 2, '李四', '男', '1999-11-11', 88.88, 'lisi@itcast.cn', '13888888888', 1 ),
( 2, '李四', '男', '1999-11-11', 88.88, 'lisi@itcast.cn', '13888888888', 1 ),
( 2, '李四', '男', '1999-11-11', 88.88, 'lisi@itcast.cn', '13888888888', 1 );
-- 修改数据
-- UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
-- 将张三的性别改为女
UPDATE stu
SET gender = '女'
WHERE
NAME = '张三';
SELECT
*
FROM
stu;
-- 将张三的生日改为 1999-12-12 分数改为99.99
UPDATE stu
SET birthday = '1999-12-12',
score = 99.99
WHERE
NAME = '张三';
SELECT
*
FROM
stu;
-- 注意：如果update语句没有加where条件，则会将表中所有数据全部修改！
-- 删除 DELETE FROM 表名 [WHERE 条件] ;
-- 删除张三记录
DELETE
FROM
stu
WHERE
NAME = '张三';
SELECT
*
FROM
stu;
准备数据
DROP TABLE
IF
EXISTS stu;-- 如果存在删除stu表
-- 创建stu表
CREATE TABLE stu (
id INT,-- id(编号) 大整数类型
NAME VARCHAR ( 20 ),-- name(姓名) 可变字符串类型
age INT,-- age(年龄) 大整数类型
sex VARCHAR ( 5 ),-- sex(性别) 不变字符串类型
address VARCHAR ( 100 ),-- address(地址) 不变字符串类型
math DOUBLE ( 5, 2 ),-- math(数学成绩)
english DOUBLE ( 5, 2 ),-- 英语成绩
hire_date date -- 入学时间
);
-- 添加数据
INSERT INTO stu ( id, NAME, age, sex, address, math, english, hire_date )
VALUES
( 1, '马运', 55, '男', '杭州', 66, 78, '1995-09-01' ),
( 2, '马花疼', 45, '女', '深圳', 98, 87, '1998-09-01' ),
( 3, '马斯克', 55, '男', '香港', 56, 77, '1999-09-02' ),
( 4, '柳白', 20, '女', '湖南', 76, 65, '1997-09-05' ),
( 5, '柳青', 20, '男', '湖南', 86, NULL, '1998-09-01' ),
( 6, '刘德花', 57, '男', '香港', 99, 99, '1998-09-01' ),
( 7, '张学右', 22, '女', '香港', 99, 99, '1998-09-01' ),
( 8, '德玛西亚', 18, '男', '南京', 56, 65, '1994-09-02' );
基础查询Ⅱ
-- 查询所有列的数据，列名的列表可以使用*替代
-- 简单 ,但是一般不用
SELECT
*
FROM
stu;
-- 复杂,但是 灵活性高 ;
SELECT
id,
NAME,
age,
sex,
address,
math,
english,
hire_date
FROM
stu;
-- 查询name、age两列
SELECT NAME
,
age
FROM
stu;
-- 查询地址信息
SELECT
address
FROM
stu;
-- 去除重复记录
SELECT DISTINCT
address
FROM
stu;
-- 1.查询年龄大于20岁的学员信息
SELECT
*
FROM
stu
WHERE
age > 20;
-- 2.查询年龄大于等于20岁的学员信息
SELECT
*
FROM
stu
WHERE
age >= 20;
-- 3.查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
SELECT
*
FROM
stu
WHERE
age >= 20 && age <= 30; -- and
SELECT * FROM stu WHERE age >= 20
AND age <= 30;
-- 查询年龄大于等于20岁 或者 年龄小于等于 30岁 的学员信息
SELECT * FROM stu WHERE age >= 20
OR age <= 10;
-- 4.查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息
SELECT
*
FROM
stu
WHERE
hire_date BETWEEN '1998-09-01'
AND '1999-09-01';
-- 5. 查询年龄等于18岁的学员信息
SELECT
*
FROM
stu
WHERE
age = 18;
-- 6. 查询年龄不等于18岁的学员信息
SELECT
*
FROM
stu
WHERE
age != 18;
-- <> 不等于
SELECT
*
FROM
stu
WHERE
age <> 18;
-- 7. 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
SELECT
*
FROM
stu
WHERE
age = 18
OR age = 20
OR age = 22;
SELECT
*
FROM
stu
WHERE
age IN ( 18, 20, 22 );
SELECT
*
FROM
stu
WHERE
age NOT IN ( 18, 20, 22 );
-- 8. 查询英语成绩为 null的学员信息
-- 注意： null值的比较不能使用 = != 。需要使用 is is not
-- select * from stu where english = null; -- 不行的
SELECT
*
FROM
stu
WHERE
english IS NULL;-- 是null
SELECT
*
FROM
stu
WHERE
english IS NOT NULL;-- 不是null
-- 思考 查询年龄 在 20-30 之间 或 数学成绩 >60 的 人
-- 通过增加 () 提高可读性
SELECT
*
FROM
stu
WHERE
( age >= 20 AND age <= 30 )
OR math > 60;
-- 思考 应怎么用
/*
* _ 一般不用,因为几乎没有这个需求
* %马 很少用
*
* %马% 经常用
* */
SELECT
*
FROM
stu
WHERE
NAME LIKE '马_';
SELECT
*
FROM
stu
WHERE
NAME LIKE '马%';
SELECT
*
FROM
stu
WHERE
NAME LIKE '%马%';
SELECT
*
FROM
stu
WHERE
NAME NOT LIKE '%马';
复杂查询
/*排序
* 1) 如果有 where 应该先写 where 在写order by
* 2) desc 倒序,asc :正序 (从上往下)
* 3) order 是关键词,不能当做表名或字段名称,( 其他关键词也一样)
*
*/
SELECT
*
FROM
stu
ORDER BY
math ASC;
-- 汇总运算 : 聚合计算
/*
1, count 可以写* 其他函数不能写*
2, 统计类函数 只有5个,
3, count 还能写字段名称 select count(name) from stu;
4, 去重复
-- 需求,去重后 计算
select count(distinct address) from stu;
5, 统计函数计算时不会计算 null
-- 此时 先去除 null再 平均
select avg(english) from stu;
-- 补充 判断函数
-- ifnull 是mysql 内置函数 如果 参数1 不为null 使用第一个, 如果 第一个为null 使用第二个
select name , ifnull(english,0) from stu
-- 求八个人的平均english分,
select avg(ifnull(english,0)) from stu
*/
SELECT
*
FROM
stu;
-- 此时 先去除 null再 平均
SELECT
avg( english )
FROM
stu;
-- 补充 判断函数
-- ifnull 是mysql 内置函数 如果 参数1 不为null 使用第一个, 如果 第一个为null 使用第二个
SELECT NAME
,
ifnull( english, 0 )
FROM
stu;
-- 求八个人的平均english分,
SELECT
avg(
ifnull( english, 0 ))
FROM
stu;
-- 总数
SELECT
count(*)
FROM
stu;
-- 查询数学成绩的最高分
SELECT
max( math )
FROM
stu;
-- 查询数学成绩的最低分
SELECT
min( math )
FROM
stu;
-- 查询数学成绩的总分
SELECT
sum( math )
FROM
stu;
-- 查询数学成绩的平均分
SELECT
avg( math )
FROM
stu;
-- 分组 为了干啥 : 为了统计计算
/*
1. 分组一定和聚合函数(count,avg,max,min,sum) 一起出现
2, 书写原则
slect 后面的字段 要么 出现在 group by 后面,要么出现在 聚合函数中,否则报错(没有意义)
3, 过程
1) 先确定按照那个字段分组
2) 再确定要那些字段
*/-- 查询男同学和女同学各自的数学平均分
SELECT
sex,
avg( math )
FROM
stu
GROUP BY
sex;
SELECT
sex,
avg( math )
FROM
stu
WHERE
math > 50
GROUP BY
sex
HAVING
avg( math )> 90;
分页查询
/*
分页查询:
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数
* 起始索引：从0开始
*/
SELECT
*
FROM
stu;
-- 1. 从0开始查询，查询3条数据
SELECT
*
FROM
stu
LIMIT 0,
3;
-- 2. 每页显示3条数据，查询第1页数据
SELECT
*
FROM
stu
LIMIT 0,
3;
-- 3. 每页显示3条数据，查询第2页数据
SELECT
*
FROM
stu
LIMIT 3,
3;
-- 4. 每页显示3条数据，查询第3页数据
SELECT
*
FROM
stu
LIMIT 6,
3;
-- 起始索引 = （当前页码 - 1） * 每页显示的条数
SQL 完整写法
sql 必须按照如下顺序书写
select id
from
where
group by
having
order by
limit
底层执行顺序
from
where
group by
having
select
order by
limit
约束
多表查询 和字段上没有有约束 没有关系
为啥要有约束
作用:
为了保证数据存储的有效,有意义, : 正确性、有效性和完整性,防止脏数据的插入
常用的五个规则
- 非空约束: not null
- 唯一约束: unique
- 主键约束: PRIMARY KEY 非空+唯一
- 默认约束: default 给定一个默认值
- 检查约束:check检查数据给定范围
自动增长
- 不是约束, 是一个额外的功能, 用于主键字段,为了保证 主键字段的唯一和非空
- id 不给值,则自动赋值,从1 开始
- id 给值0 ,则则自动赋值
- 自增底层是一个自增序列数,每次使用后都加一(不管是否插入成功)
- 自增底层是一个自增序列数, 删除数据不会回退
- 如果插入时指定id>0 则已指定的为主,
- 如果插入时指定id > 自增序列数, 则自增序列 改为 指定的值
为啥要有主键
保证一条数据的唯一性
概念
主键列 :
一个表应当有一列是数据的唯一标识,一般 是id字段,类型 bigint
主键约束:
在主键列上的约束叫主键约束(本质是一个规则)
数据库设计原则
原则1:(第一范式)
列尽量拆分
原则2:(第二范式)
每张表应该有唯一主键
原则3:(第三范式)
表应该进行合理的拆分
字段不冗余
原则4:(反第三范式)
应该有合理的冗余
外键约束
外键列:
是一个冗余的列,用来关联两张表的数据
如果只有外键列 没有约束则会导致数据乱存储,产生脏数据
外键约束(简称外键):
是在 “外键列” 曾加的约束,
注意
外键列一定要有,外键约束不是必须的
外键约束:
优点:
可以保证数据的有效性
缺点:
降低增删改效率 (一般不加)
外键和外键列
表关系
在线产品原型
设计方式
一对一:
把一张大表 拆分成两张表 (用户—用户详情表)
一对多:
用户–部门
多对多:
描述的是两个表数据之间的关系(通过中间表描述) : 学生和课程 ,商品和订单
多表查询
笛卡尔积
笛卡尔积定义的一种数学运算, 计算集合相乘:
列如：(1,2) * (3,4,5) = [(1,3)},(1,4),(1,5),(2,3)},(2,4),(2,5)]
数据库中的 笛卡尔积
select *from emp ,dept – 结果是笛卡尔积
内连接
概述
查询的结果是两张表都有的数据(能通过外键列关联上的数据)
写法上
-- 隐式
select * from 表1 ,表2 where 表1.表2_id =表2.id
-- 显示
select * from 表1 inner join 表2 on 表1.表2_id =表2.id
带条件查询
-- 隐式
select * from 表1 ,表2 where 表1.表2_id =表2.id and 表1.表2_id =1
-- 显示: on 只用于数据关联,其他条件 加where
select * from 表1 inner join 表2 on 表1.表2_id =表2.id where 表1.表2_id =1
注意
显示 和 隐式 几乎没有区别
隐式 : 简单
显示 : 可读性强
结论:
哪个简单用那个
别名
注意:
? 一旦起了别名,就不能在使用 表名.字段名
? 起了别名 可以不使用 别名.字段 可以直接 字段名(只要mysql 能推断出你要哪个)
书写步骤
/*
请查询 所有员工的 姓名及所属部门名称, 没有部门的不展示
1) 先搭建 架子 select * from
2) 确定数据来源于那几张表, 写到from 后面 select * from emp e ,dept d
3) 确定管理字段 筛选有意义的数据
select * from emp e ,dept d where e.dep_id =d.did
4) 把 *改成 自己需要的字段
*/
select * from emp e ,dept d where e.dep_id =d.did
外连接
概述
左外连接
查询的是 左边表所有的数据 以及右表表中 关联的数据
右外连接
查询的是 右边表所有的数据 以及左表表中 关联的数据
/*
请查询 所有员工的 姓名及所属部门名称, 没有部门的也要展示
*/
select * from emp e left join dept d on e.dep_id =d.did
select * from dept d right join emp e on e.dep_id =d.did
写法
/*
请查询 所有员工的 姓名及所属部门名称, 没有部门的也要展示
先搭建 架子 select * from
确定数据来源于那几张表, 写到from 后面 select * from emp e ,dept d
确定管理字段 筛选有意义的数据
select * from emp e ,dept d where e.dep_id =d.did
把内连接改造成外连接(根据需求)
select * from emp e left join dept d on e.dep_id =d.did
把 *改成 自己需要的字段
*/
select * from emp e left join dept d on e.dep_id =d.did
内连接和外连接的区别
区别
- 数据范围不一样: 内连接查询的是两张表共有的数据,左外连接,查询左表所有数据及右表关联的数据
- 外连接 效率高于内连接
子查询
先拆分写sql 再合并
书写
-- 查询研发部所有员工
-- 子查询
select did from dept where dname ='研发部'
select * from emp where dep_id = 1
-- 改造成子查询
-- 当子sql 返回值是 单行单列时 用= ,>,<
select * from emp where dep_id = (select did from dept where dname ='研发部')
-- 查询 研发部,市场部 所有员工
-- 当子sql 返回值是 多行单列时 用 in
select * from emp where dep_id in (select did from dept where dname ='研发部' or dname ='市场部')
-- 当子sql 返回值是 多行多列时 当做表使用
select * from emp e, (select * from dept where dname ='研发部' or dname ='市场部') d where e.dep_id =d.did
策略:
子查询,内连接,外连接都能实现
如果表数据量小: 使用内连接
如果表数据量大: 建议用外连接
子查询 : 有时候效率低(内侧查询返回的数据量大),有时效率高(数据量小)
事务
概述
事务!=事物
是数据库提供的一个功能,能够保证多条sql 同时成功,同时失败
银行转账
-- 程序员需要什么?
-- 我们需要数据库提供一个特殊的功能,能够保证 多条sql 同时成功,同时失败
-- 这个功能我们叫做事务!
update emp e set e.salary =salary +2000 where e.id =1001;
update emp e set e.salary =salary -2000 where e.id =1002;
如何使用
Java版
try{
// 开启功能
start transaction;
update emp e set e.salary =salary +2000 where e.id =1001;
update emp e set e.salary =salary -2000 where e.id =1002;
commit;//提交事务;
}catch(e){
rollback; // 回滚
}
事务 的 有四个特征:
- 原子性 : 一定能保证 同时成功,同时失败
- 一致性 : 数据最终一定和期望的一样,数据 不会多,不会少
- 隔离性: 多个事务之间 相互隔离,不影响
- 持久性 : 只要执行commit成功,数据一定修改成功
总结和补充
约束
非空
唯一
主键 + 自增
默认值
外键约束
表设计原则
列要尽量拆分
表要有主键
表要合理拆分/表不能有冗余
表应该有合理的冗余
表关系
一对一:
一对多:
多对多:
多表查询
内连接:
? 显式.隐式
外连接
? 左右
子查询
? 一个值,单列多行,多行多列
如何写多表查询
搭建架子 select * from
确定需要的数据在那几张表,写在from 后面,起别名,
筛选数据(排除无效数据)
判断是否还需要写其他条件
判断是否需要改造成外连接
确定返回字段
补充
ifnull(字段,默认值)判断是否为null修改null值
-- 程序员需要什么?
-- 我们需要数据库提供一个特殊的功能,能够保证 多条sql 同时成功,同时失败
-- 这个功能我们叫做事务!
start transaction;-- 本质 修改为 手动提交 set @@autocommit=0
-- @@autocommit msyql 内置的一个参数
update emp e set e.salary =salary +2000 where e.id =1001;
update emp e set e.salary =salary -2000 where e.id =1002;
commit;
Mysql安装常见错误
错误1
错误2
错误3
错误4
错误5
数据库设计范式
第一范式
针对数据表中的列，列要具备原子性，不可再拆分
即:
设计表是列尽量不可再拆分(保证有意义)
如果数据表这样设计，住址这一列不具备原子性，因为数据可再次拆分，不拆分的话无法简单的获取用户省市的信息,拆分之后如下：
第二范式
数据表要具备唯一主键，不存在多主键，使得每一行数据具有唯一性
即:
每一表应该有一个主键
上表,我们虽然遵循了第一范式,设计的表就没有问题了吗?没有哪一个字段（列）能唯一确定一条数据
例如 是否有可能有两个张三都在航投大厦上班?
那如何来解决呢？在表中加一个唯一主键列：id
第三范式
不依赖非主键的字段及更深层次的依赖，使得每个字段都独立依赖于主键字段（独立性）
通俗解释:
- 一个表不应该表达两个意思 (要合理的拆表)
- 不应该有冗余存储
- 虽然有id ,但是 id 列, 是 张三的编号还是手机的编号,不确定
- 总价这一列 多余, 可以通过 单价和数量计算出来
第四范式
数据应该有合理的冗余
上述设计,遵循了 第一第二第三范式,但是 如下场景将会有问题
用户
3月1号 购买了商品001花了 共消费 3000
4月1号 商品001价格发生调整 变成了800
此时用户查看订单发现 2400,
“我交易记录命名是3000啊,找商家退钱去”
针对上述场景,应该合理的设计(再把总价增加至订单表)
