1初识MySQL
JavaEE: 企业级Java开发 Web
只会写代码,学习数据库,基本混饭吃;
还需要学习: 数据结构+算法!
离散数学,数字电路,体系结构,编译原理. +实战经验,高级程序员
1.1为什么要学数据库
- 岗位需求
- 现在的世界,大数据时代,得数据者得天下.
- 被迫需求: 存数据, 去IOE不用国外的产品
- 数据库是所有软件体系中最核心的存在 DBA:专门管理数据库的
1.2什么是数据库
数据库(DB,DataBase)
概念:数据仓库,软件,安装在操作系统之上的
作用:存数据,管理数据
1.3数据库分类
-
关系型数据库:行,列
- MySQL
- Oracle
- Sql Server
- DB2
- SQLite
通过表和表之间,列和列之间的关心进行存储
-
非关系型数据库
- Redis
- MongoDB
以对象存储,通过对象自身的属性来决定.
DBMS(数据库管理系统)
- 数据库的管理软件
- MySQL, 数据库管理系统!
1.4MySQL简介
- MySQL是一个**关系型数据库管理系统****,**由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
- MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
- MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。
1.5连接数据库
命令行连接!
mysql -uroot -p123456 --连接数据库
---------------------------
--所有的语句都用;(英文)结尾
show databases; --查看所有数据库
mysql> user *** --切换到数据库***
----
show tables; --查看数据库中所有的表
describe ***; --显示这张表的信息,和Navicat中的设计表相似
----
create database westos; --创建一个数据库
exit; --退出数据库连接
-- 单行注释
/*
多行注释
*/
1.6数据库语言
- DDL: 数据库定义语言
- DML:数据库操作语言
- DQL:数据库查询语言
- DCL:数据库控制语言
2操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
2.1操作数据库(了解)
-
创建数据库
create DATABASE [if not EXISTS] school; --[]可选
-
删除数据库
drop database if EXISTS school;
-
使用数据库
--如果表名或者字段名是一个特殊字符,就需要带 ` ` use mybatis_plus
-
查看数据库
show databases; --查看所有的数据库
学习思路:
- 对照Navicat可视化历史记录查看sql
- 固定的语法或者关键字必须要强行记住
2.2数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节(最常用)
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串类型的浮点数 金融计算的时候一般使用decimal(精度问题)
字符串
- char 字符串大小 0-255
- varchar 可变字符串 0-65535(常用)
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1
时间日期
- data YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数
- year 年份表示
null
- 没有值,空值
- 注意不要使用NULL进行运算,结果为NULL
2.3数据库的字段属性(重点)
Unsigned:
-
无符号的整数
-
声明了该字段不能声明为复数
零填充:
- 0填充的
- 不足位数的,使用0来填充, int(3), 输入5 存储的就是005
自增:
- 自动在上一条的基础上+1(默认)
- 通畅用来设计唯一的主键 ~index,必须是整数类型
- 可以自定义主键的起始值和步长
非空:
- 假设设置为 nut null, 如果不赋值们就会报错
- Null,如果不填写值,默认就是null
2.4使用语句建表
注意点:
- 使用英文(), 标的名称和字段尽量使用``括起来
- 字符串使用 ‘ ‘ 括起来
- 所有语句后面加 , 最后一个不用加
- primary key (‘id’)
drop table if EXISTS `student`;
create table if not EXISTS `student`(
`id` int(4) not null AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) COMMENT '姓名',
`password` VARCHAR(20) DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) not null DEFAULT '女' comment '性别',
`birthday` TIMESTAMP not null DEFAULT '0000-00-00 00:00:00' COMMENT '出生日期',
--CURRENT_TIMESTAMP
PRIMARY key (id)
)ENGINE=INNODB charset=utf8 comment = '学生基础表'
格式:
create table [if not exists] `表名`(
'字段名' 列类型 [属性] [索引] [注释],
.....
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释] --最后一个没有逗号
)[表类型] [字符集设置] [提示]
-- 一些操作
show create database school --查看数据库的创建语句
show create table `student` --查看表的创建语句
DESC `student` --显示表的结构
2.5数据表的类型
- INNODB (默认)
- MYISAM(早些年使用的)
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 比较小 | 较大,约为2倍MYISAM |
常规使用操作:
- MYISAM: 节约空间,速度较快
- INNODB: 安全性高,事务的处理,多表用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下
本质还是文件的存储
MySQL引擎在物理文件上的区别
-
INNODB 在数据库表中只有一个 *.frm文件 以及上级目录下的ibdata1 文件
-
MYISAM对应文件
- *.frm -表结构的定义文件
- *.MYD -数据文件(data)
- *.MYI -存放索引的
设置数据库表的字符集编码
CHARSET=utf8
不设置的话会是MySQL默认的字符集编码 (不支持中文!)
默认 : Latin1编码集
在my.ini中配置默认的编码
character-set-server = utf8
2.6修改删除表
修改
-- 修改表名
alter table `student` rename as `student1`;
-- 增加表的字段
alter table `student1` add email VARCHAR(20) not null comment '邮箱';
-- 修改表的字段 (重命名,修改约束!)
-- 修改约束
alter table `student1` MODIFY sex VARCHAR(3) DEFAULT '男';
-- 重命名 + 修改约束 实测是可以修改约束的
alter table `student1` change sex sex1 varchar(2);
-- 删除表的字段
alter table `teacher1` drop email
删除
-- 如果表存在就删除
drop table if exists `teacher1`
所有的创建和删除操作最好加上判断,以免报错
注意点:
- 注意使用``,字段名使用这个包裹
- 注释的使用
- sql关键字大小写不敏感,最好小写
- 所有符号用英文,用中文就报错
3.Mysql的数据管理
3.1外键(了解)
3.2DML语言(背下来)
数据库意义: 数据存储,数据管理
DML语言: 数据操作语言
- insert
- update
- delete
3.3添加 insert
-- 插入语句
-- 由于主键自增,所以可以省略主键
insert into `grade` (gname) values ('17信工2班');
-- 如果不写表的字段,就会一一匹配
insert into `grade` values (2,'17信工2班');
-- 一次插入多个值
insert into `grade` (gname) values ('17信工2班'),('17信工1班'),('16信工2班');
语法: insert into 表名
([字段1],[字段2],[字段3]) values (‘值1’),(‘值2’),(‘值3’)
注意事项:
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但是后面的值必须要一一对应,不能少
- 可以同时插入多条数据 values 后面的值,用逗号隔开即可
3.4修改 update
-- 不指定条件会修改所有数据
update `student` set `sname` = '大啵啵' where sid = 1;
-- 修改多个
update `student` set sname = '大啵啵',`ssex`='男' where sid = 1;
--语法
update 表名 set column_name1 = value1 , column_name2 = value2 where [条件]
条件 where子句 运算符 id = ? , id>? , 在某个区间
where后面是条件判断,返回值是个Boolean值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或者!= | 不等于 | <>2 | 除了2 ,都会变 |
> | |||
< | |||
or | 我或者你 | 5>1 and 1>2 | true |
>= | |||
<= | |||
between … and … | 在某个区间 | between 2 and 5 | 区间在2到5包含2和5 |
and | 我和你 | 5>1 and 1>2 | false |
-- 通过多个条件定位数据
update `student` set sname = '小啵啵', ssex = '男' where sid<2 and ssex = '女';
update `student` set sbirthday = CURRENT_TIMESTAMP, ssex = '男' where sid<2 ;
注意:
- column_name 是数据库的列,尽量带上``
- 筛选条件,如果没有指定,则会修改所有的列
- value ,是一个具体的值,也可以是一个变量
- 多个设置的属性之间,使用英文逗隔开
3.5删除 delete
delete命令
-- 删除数据(避免这样写,这样子会全部删除)
delete from `student`
delete from `student` where id = 1;
truncate命令
-- 作用: 完全情况一个数据库表
-- 标的结构和索引约束不会变
truncate `student`
- 相同点: 都能删除数据,不会删除表结构
- 不同点:
- truncate 重新设置自增,自增会归零
- truncate 不会影响事务
- delete 不会影响自助,上次到5.后面再加还是从6开始
DELETE删除的问题
,重启数据库,现象:
这里是数据库版本问题,新版已经修复这个问题了
- INNODB 自增列会从1开始.断点及失
- MYISAM 继续从上一个自增量(存在文件中的,不会丢失)
4DQL查询数据(最重要)
4.1DQL
(Data Query Language: 数据查询语言)
- 所有的查询操作都用它 select
- 简单的查询,复杂的查询都能做~
- 数据库中最核心的语言,是重要的语句
- 使用频率最高的
操作要用到的表
学生表
create table `student`(
`studentno` int(4) not null comment '学号',
`loginpwd` varchar(20) default null,
`studentname` varchar(20) default null comment '学生姓名',
`sex` tinyint(1) default null comment '性别,0或1',
`gradeid` int(11) default null comment '年级编号',
`phone` varchar(50) not null comment '联系电话,允许为空',
`address` varchar(255) not null comment '地址,允许为空',
`borndate` datetime default null comment '出生时间',
`email` varchar (50) not null comment '邮箱账号允许为空',
`identitycard` varchar(18) default null comment '身份证号',
primary key (`studentno`),
unique key `identitycard`(`identitycard`),
key `email` (`email`)
)engine=myisam default charset=utf8;
年级表
-- 创建年级表
drop table if exists `grade`;
create table `grade`(
`gradeid` int(11) not null auto_increment comment '年级编号',
`gradename` varchar(50) not null comment '年级名称',
primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;
科目表
-- 创建科目表
drop table if exists `subject`;
create table `subject`(
`subjectno`int(11) not null auto_increment comment '课程编号',
`subjectname` varchar(50) default null comment '课程名称',
`classhour` int(4) default null comment '学时',
`gradeid` int(4) default null comment '年级编号',
primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;
select 完整语法
sql关键字的优先级
from > where > group by > having > order by
select [ALL|DISTINCT]
{table.foeld[as a alias1]}
from table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[where ...] -- 指定结果需要满足的条件
[group by ...] -- 制定结果按照哪几个字段来分组
[having] -- 过滤分组后必须满足的次要条件
[order by ...] -- 指定查询记录按照一个或者几个条件排序
[limit {[offset,]row_count| row_countOFFSET offset}]; -- 指定查询的记录从哪一条开始
成绩表
-- 创建成绩表
drop table if exists `result`;
create table `result`(
`studentno` int(4) not null comment '学号',
`subjectno` int(4) not null comment '课程编号',
`examdate` datetime not null comment '考试日期',
`studentresult` int (4) not null comment '考试成绩',
key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;
4.2 指定查询字段
-- 查询全部学生 select 字段 from 表
select * from `student`;
-- 查询指定字段
select studentno,studentname from `student`;
-- 别名,给结果起个名字 AS 可以给字段起别名也可以给表起别名
select `studentno` as 学号,`studentname` as 学生姓名 from `student` as s ;
-- 函数 Concat (a,b)
SELECT CONCAT('姓名: ',studentname) as 新名字 from `student`;
语法: select 字段,...,字段 from 表
有的时候列名不是那么的见名知意,那我们就可以起别名
字段名 as 别名 表名 as 别名
去重 distinct
作用: 去除select 语句查询出来的结果中重复的数据,只显示一条
-- 查询有哪些同学参加了考试,哪些同学有成绩
select * from `result`; -- 查询全部的成绩
select `studentno` from `result`;-- 发现重复数据,需要去重
select distinct `studentno` from `result`; -- 最后只出现了一个学号
数据库的列 表达式
select version(); -- 查询mysql的系统版本
select 3*7-2 as 计算结果 -- 用来计算
select @@AUTO_INCREMENT_INCREMENT;-- 查询自增的步长 (变量)
-- 学员考试成绩+1分查看
select `studentno`,`studentresult`as '提升分数之前' from `result`;
select `studentno`,`studentresult`+1 as '提升分数之后' from `result`;
数据库中的表达式: 文本值,列,Null,函数表达式,系统变量
select 表达式
from 表
4.3where条件子句
作用: 检索数据中**符合条件
**的值
搜索的条件一般是一个或者多个表达式构成
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与 |
or || | a or b a||b | 逻辑或 |
Not ! | not a ! a | 逻辑非 |
最好使用英文符号
where 使用
-- 查询考试成绩大于90分的成绩
select `studentno`,`studentresult` from `result` where studentresult BETWEEN 80 and 100 ;
-- 并且学生的id不等于1000
select `studentno`,`studentresult` from `result` where studentresult BETWEEN 80 and 100 and studentno != 1000;
模糊查询: 本质也是比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,则结果为真 |
is not null | a is not null | 如果操作符不为null,则结果为真 |
between | a between b and c | 如果a在b和c之间,结果为真 |
like | a like b | SQL匹配,如果a匹配b,则结果为真 |
in | a in (a1 , a2 …) | 假设a 在 (a1 , a2 …)其中某个,结果为真 |
-- ====模糊查询===
-- 查询姓刘的同学
-- like 结合 % 代表0-任意一个字符 _ 代表一个字符
select * from student where studentname like '张%';
select * from student where studentname like '_强';
-- 查询名字当中有 伟 字的同学
select * from student where studentname like '%伟%';
-- ====in==== (具体的一个或者多个值)
-- 查询1001, 1002 , 1003号学员
-- 查询不在1001, 1002 , 1003号学员
select * from student where studentno in (1001,1002,1003);
select * from student where studentno not in (1001,1002,1003);
-- 查询地址在北京的学生
select * from student where address in ('北京朝阳');
-- ====null not null====
select * from student where address is not null;
select * from student where address is null;
4.4联表查询
-- ====连表查询====
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
select * from student ;
select * from result ;
/*
1.分析需求,分析查询的字段来自哪些表
2.确定使用哪种连接查询 7种
3.确定一个交叉点,两个表中哪个数据是相同的
4.判断条件 学生表中的studentno = 成绩表的 studentno
*/
-- join on 连接查询
-- where 等值查询
-- inner join
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno;
-- right join
select s.studentno,studentname,subjectno,studentresult
from student as s
right join result as r
on s.studentno = r.studentno;
-- left join
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno = r.studentno;
-- 查询缺考的同学
select s.studentno,studentname from student as s
left join
result as r
on s.studentno = r.studentno
where studentresult is null;
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 即使右表中没有匹配,也会从左表中返回所有的值 注意上面的left join的执行结果 |
right join | 即使左表中没有匹配,也会从右表中返回所有的值 注意上面的right join的执行结 |
-- 查询参加考试的同学信息: 学号,学生姓名,科目名称,分数
select s.studentno,studentname,subjectname,studentresult
from student as s
right join result as r
on s.studentno = r.studentno
left join subject as t
on r.subjectno = t.subjectno;
-- 查询学院所属的年级(学号,姓名,年级名称)
select studentno,studentname,gradename
from student as stu
inner join grade as gra
where stu.gradeid = gra.gradeid;
-- 查询科目所属的年级(科目名称,年级名称)
select sub.subjectname,gra.gradename
from `subject` as sub
inner join grade as gra
where sub.gradeid = gra.gradeid;
-- 查询参加了高等数学1的考试同学信息: 学号,姓名,科目名,分数
select stu.studentno,stu.studentname,sub.subjectname,res.studentresult
from student as stu
inner join result as res , `subject` as sub
where stu.studentno = res.studentno and sub.subjectno = res.subjectno
and subjectname = '高等数学-1';
/*
关键点:
我要查询哪些数据select ...
从哪几个表中查FROM表 XXX Join 连接的表 on 交叉查询
假设查询多张表,慢慢来,先查询两张表,然后再慢慢增加
*/
-- From a left join b 以左边的表为基准
-- From a right join b 以右边的表为基准
自连接(了解)
自己的表和自己的表连接, 核心:一张表拆分为两张一样的表
父类:
categoryid | categoryname |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类:
pid | categoryid | categoryname |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类id
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 查询父子信息
-- 把一张表看成两张一模一样的表
select a.categoryname as '爹',b.categoryname as '儿'
from category as a, category as b
where a.categoryid = b.pid;
4.5分页和排序
排序:
语法 order by 排序字段 [排序规则 asc or desc] 默认升序asc
-- 成绩降序排序
select stu.studentno,stu.studentname,sub.subjectname,res.studentresult
from student as stu
inner join result as res , `subject` as sub
where stu.studentno = res.studentno and sub.subjectno = res.subjectno
and subjectname = '高等数学-1' ORDER BY studentresult DESC;
-- 查询高等数学-3 课程成绩排名前十的学生,并且分数大于80的学生信息(学号,姓名,课程名称,课程成绩)
select stu.studentno,stu.studentname,sub.subjectname,res.studentresult
from student as stu
inner join `subject` as sub , `result` as res
where stu.studentno = res.studentno and sub.subjectno = res.subjectno and subjectname = '高等数学-3' and studentresult>80 ORDER BY studentresult DESC limit 10;
分页:
为什么要分页:
- 降低数据库压力
- 提升用户体验
瀑布流: 不断加载, 不断加载(一般图片这样加载)
-- 分页
-- limit 从第几条数据开始,显示几条 0,1 从第1个显示1条数据 0是第一条数据
select stu.studentno,stu.studentname,sub.subjectname,res.studentresult
from student as stu
inner join result as res , `subject` as sub
where stu.studentno = res.studentno and sub.subjectno = res.subjectno
and subjectname = '高等数学-1' ORDER BY studentresult DESC limit 0,1;
4.6子查询
之前的判断条件都在 where里面 , 并且值是确定的. 假设要让值变为计算出来的
本质: 在where 语句中嵌套一个子查询语句 where (select * from)
-- 查询 高等数学-1 的所有考试结果(学号,科目编号,成绩) 降序排列
-- 方式一 使用连接查询
select studentno,r.subjectno,studentresult
from result r
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '高等数学-1'
order by studentresult asc
-- 方式二: 使用子查询()
select studentno,r.subjectno,r.studentresult
from `result` r
where subjectno = (
select subjectno from `subject` where subjectname = '高等数学-1'
)
order by r.studentresult;
-- 查询分数不小于68分的 高等数学-2 学号 姓名
-- 方式一 : 连接查询
select stu.studentno,studentname,sub.subjectname,r.studentresult
from student stu
inner join result r
on stu.studentno = r.studentno
inner JOIN `subject` sub
on sub.subjectno = r.subjectno
where subjectname = '高等数学-2' and studentresult>=68 order by studentresult desc ;
-- 方式二 : 子查询
select stu.studentno,studentname,r.studentresult
from student stu
inner join result r
on stu.studentno = r.studentno
where studentresult>=68 and subjectno = (select subjectno from `subject` where subjectname = '高等数学-2')
-- 查询 高等数学-1 成绩高的同学的学生信息. 姓名 学号 课程名称 课程分数
SELECT s.studentno ,s.studentname, subjectname ,studentresult
from student as s
inner join result r
on s.studentno=r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '高等数学-1' ORDER BY studentresult DESC limit 1;
5MySQL函数
官方文档:https://dev.mysql.com/doc/refman/5.7/en/
5.1 一般函数
-- 一般函数
-- 数学运算
select abs(-8); -- 绝对值
select ceiling(9.4); -- 向上取整
select floor(9.4); -- 向下取证
select rand(); -- 返回一个0-1的随机数 最最常用
select sign(-2); -- 判断一个数的符号 返回-1 0 1
-- 字符串函数
select CHARACTER_LENGTH("啵啵啵啵啵"); -- 字符串长度
select concat('我','爱','你们'); -- 拼接字符串
select insert("我爱你们,啵啵啵",1,2,'滴滴滴'); -- 插入,替换 从第一个位置替换2个长度为滴滴滴
select upper('sosososoAAAA');-- 转为大写
select lower('sosososoAAAA');-- 转为小写
select instr('bobo','b'); -- 后面再前面的位置
select replace('坚持就能成功','坚持','努力'); -- 指定位置更换
select substr('坚持就能成功',2,5); -- 第二个开始截取5个
select REVERSE('ABCD'); -- 反转
-- 时间和日期函数(记住)
select CURRENT_DATE; -- 2021-08-12
select CURRENT_TIME; -- 14:49:23
select NOW(); -- 2021-08-12 14:49:54
select LOCALTIME; -- 2021-08-12 14:50:18
select LOCALTIMESTAMP; -- 2021-08-12 14:50:18
select SYSDATE(); -- 2021-08-12 14:50:47
select YEAR(now());-- 2021
select MONTH(now());-- 8
select day(now());-- 12
select HOUR(now());-- 14
select MINUTE(now());-- 53
select SECOND(now());-- 31
-- 系统
select USER();-- root@localhost
select CURRENT_USER; -- root@localhost
select VERSION(); -- 5.7.25
5.2聚合函数(常用)
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
-- 查询不同课程的平均分,最高分,最低分,平均分大于80分的
-- 核心: 分组 GROUP BY r.subjectno 通过什么来分组
-- having :过滤分组后需要满足的次要条件
select s.subjectno,subjectname, avg(studentresult) as '平均分',max(studentresult) as '最高分',min(studentresult) as '最低分'
from result r
inner join `subject` s
on r.subjectno = s.subjectno
GROUP BY r.subjectno
having avg(studentresult)>80;
where 和 having的区别
where:
- where是一个约束声明,使用where来约束来自数据库的数据;
- where是在结果返回之前起作用的;
- where中不能使用聚合函数。
having:
- having是一个过滤声明;
- 在查询返回结果集以后,对查询结果进行的过滤操作;
- 在having中可以使用聚合函数。
5.3 数据库级别的MD5加密(扩展)
什么是MD5?
-
MD5不可逆,具体的值的MD5是一样的
-
增强算法复杂度和不可逆性
-
MD5破解网站的原理,背后有一个庞大的数据字典做匹配
-- ====测试MD5加密====
create table if not exists `testmd5`(
`id` int(4) not null,
`name` VARCHAR(20) not null,
`pwd` varchar(50) not null,
PRIMARY key (id)
)ENGINE=INNODB CHARSET=utf8
-- 明文密码
INSERT into testmd5 values(1,'zhangsan','123456'),
(2,'lisi','123456'),
(3,'dd','123456'),
(4,'ww','123456'),
(5,'33','123456'),
(6,'ff','123456'),
(7,'hh','123456');
-- 加密密码
update testmd5 set pwd=MD5(pwd) where id = 1;
update testmd5 set pwd=MD5(pwd);-- 加密全部的密码
INSERT into testmd5 values(8,'名明',MD5('123456')); -- 插入的时候加密
-- 如何校验: 把用户传递进来的密码进行md5加密,然后对比加密后的值
6事务(Transation)
6.1什么是事务
要么都成功,要么都失败
- SQL执行 A给B转账
- SQL执行 B收到A的转账
把一组SQL放到一个批次中去执行~
事务原则: ACID 原子性,一致性,隔离性,持久性 (脏读,幻读,不可重复读)
- 原子性 — Atomicity
- 针对同一个事务,要么都完成,要么都不完成
- 一致性 — Consistency
- 最终一致性: 转账的结果,总价值一定和开始是不变的
- 隔离性 — Isolation
- A C 同时给 B转100元, 他们之间不会相互影响
- 一旦出现问题就会出现以下问题
- 脏读: 一个事务读取到了另一个事务还未提交的数据
- 不可重复读: 在一个事务内读取某一行的数据,多次读取结果不同,例如生成报表的时候,有人转入300元(已提交)
- 幻读: 一个事务内读取到了别的事务插入的数据,导致前后读取不一样
- 持久性 — Durablity
- 事务没有提交,恢复到原状
- 事务已经提交,持久化到数据库
执行事务
-- mysql是自动提交事务的
set autocommit = 0; -- 关闭
set autocommit = 1; -- 提交(默认开启)
-- 手动处理事务
set autocommit = 0; -- 关闭自动提交
-- 事务开启
start TRANSACTION -- 标记一个事务的开始,从这之后的SQL语句,都在同一个事务内,要么一起成功,要么一起失败
insert xxx
insert xxx
-- 这个时候上面两个事务但凡有一个失败,都会一起提交不上去
-- 提交: 持久化(成功!)
commit
-- 回滚: 回到原来的样子
rollback
-- 事务结束
set autocommit = 1; -- 提交(默认开启)
-- 了解
savepoint 保存点名 -- 设置一个事务的保存点(当一个事务很长的时候)
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 删除指定的保存点
模拟场景
-- 转账
create database shop CHARSET utf8 ;
use shop;
-- 建表
create table `account`(
`id` int(3) not null AUTO_INCREMENT,
`name` VARCHAR(30) not null,
`money` DECIMAL(8,2) not null,
primary key (id)
)ENGINE = INNODB CHARSET=utf8;
insert into account(`name`,`money`)
value ('A',2000.00),('B',11000.00);
-- 模拟转账: 事务
set AUTOCOMMIT = 0; -- 关闭自动提交
start TRANSACTION ; -- 开启一个事务
update account set money = money -500 where `name` = 'A';-- A减少500元
update account set money = money +500 where `name` = 'B';-- B增加500元
Commit -- 提交事务 事务一旦提交就持久化了 无法回滚
RollBack -- 回滚
Set AUTOCOMMIT = 1 -- 回复默认值
7索引
MySQL官方对索引的定义为: 索引(index)是帮组MySQL高效获取数据的数据结构. 提取句子的主干,就可以获得索引的本质:索引是数据结构
7.1 索引的分类
- 主键索引 PRIMARY KEY
- 唯一标识,主键不可重复
- 唯一索引 UNIQUE KEY
- 避免重复的列出现,唯一索引可以有多个,多个列都可以表示为唯一索引
- 常规索引 KEY 或者 index
- 默认的, index/key关键字来设置
- 全文索引 FULLTEXT
- 在特定的数据库引擎下才有 MYISAM
- 快速定位数据
基础语法:
-- 索引的使用
-- 再创建表的时候给字段增加索引
-- 创建完毕后增加索引
-- 显示所有的索引信息
show index from student;
-- 增加一个全文索引
alter table `student` add FULLTEXT index `studentname` (studentname);
-- EXPLAIN 分析SQL执行的状况
EXPLAIN select * FROM `student`; -- 非全文索引
explain select * FROM `student` where MATCH(studentname) against ('张'); -- 全文索引
7.2 测试索引
准备工作
DROP table if EXISTS app_user;
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '',
`email` varchar(50) NOT NULL,
`phone` varchar(20) DEFAULT '',
`gender` tinyint(4) unsigned DEFAULT 0,
`password` varchar(100) NOT NULL DEFAULT '123456',
`age` tinyint(4) DEFAULT 0,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 信任子程序的创建者,禁止创建、修改子程序时对SUPER权限的要求,设置log_bin_trust_routine_creators全局系统变量为1
SET GLOBAL log_bin_trust_function_creators = 1;
-- 插入100万数据.
drop function if exists mock_data ;
DELIMITER $$
-- 写函数之前必须要写,标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user` (`name`,`email`,`phone`,`gender`,`age`)
VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2),FLOOR(RANd()*100));
SET i=i+1;
END WHILE;
RETURN i;
END;
-- 这种删除只删除数据,不会删除表的结构
delete FROM `app_user`;
select mock_data();
-- id_表名_字段名
create INDEX id_app_user_name on app_user (`name`);
EXPLAIN select * from `app_user` where name = '用户999999';
EXPLAIN select * from `app_user`;
索引在小数据量的时候用处不大, 大数据的时候区别十分明显
7.3索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表完全不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
文章地址链接:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
Hash 类型的索引
BTree: INNODB的默认数据结构
8权限管理和备份
8.1用户管理
Navicat可视化管理
SQL命令操作
用户表: mysql.user
本质: 读这张表进行CRUD
-- 创建用户 create user 用户名 identified by '密码';
create user bobo identified by '123456';
select * from mysql.`user`;
-- 修改当前用户密码
set password = password('123456');
-- 修改指定用户密码
set password for bobo = password('123456');
-- 重命名 rename 原名 bobo to 新的名字
rename user bobo to bobo2
-- 用户授权 ALL PRIVILEGES 授予全部权限 库·表 唯一不会授予给别人授权的权限
GRANT ALL PRIVILEGES on *.* to bobo2 -- 给bobo2 授予所有的权限
-- 查看一个人有什么权限
show grants for bobo2;
show grants for root@localhost; -- 查看系统权限
-- 撤销权限 revoke 哪些权限 在哪个库 给谁撤销
revoke all PRIVILEGES on *.* from bobo2;
-- 删除用户
drop user bobo2;
8.2 MySQL备份
为什么要备份:
- 保证数据不丢失
- 数据转移
MySQL备份的方式:
-
直接拷贝物理文件 data
-
使用Navicat 可视化工具手动导出
-
使用命令行导出mysqldump
# mysqldomp -h主机 -u用户名 -p密码 数据库名 表名 导出单表 C:\Users\ECCOM>mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. #导出多表 C:\Users\ECCOM>mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. #导入sql文件 #1. 命令行登录数据库 C:\Users\ECCOM>mysql -uroot -p #2.选择数据库 use school #3.选择执行SQL文件 mysql> source d:/a.sql
备份数据库,防止数据丢失
9规范数据库设计
9.1为什么需要设计
当数据库比较复杂的时候,就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据插入和删除都会麻烦,可能会产生异常[屏蔽使用物理外键]
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据的完整性
- 方便开发系统
软件开发中,关于数据库的设计
- 分析需求,业务和需要处理的数据
- 概要设计: 设计关系图 E-R图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表(用户登录注销, 用户个人信息, 写博客, 创建分类)
- 分类表(文章分类, 谁创建的)
- 文章表(文章的信息)
- 友情链接表
- 自定义表(系统信息, 某个关键的字,或者一些关键的字段 两列 key : value)
- 标识实体类(把需求落地到每个字段)
- 标识实体之间的关系(表与表之间的关系)
9.2三大范式
为什么要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 信息删除不干净
三大范式
- 第一范式(1NF):
- 原子性: 保证每一列不可再分
- 第二范式(2NF):
- 前提,满足1NF
- 每张表只描述一件事情
- 第三范式(3NF):
- 前提,满足1NF,2NF
- 表中,每列数据都和主键之间相关,而不间接相关
(作用: 规范数据库的设计)
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标(成本,用户体验(响应速度))
- 在考虑性能问题的时候,适当考虑规范性!
- 故意给一些表增加冗余字段(多表联查变为单表查询)
- 复议增加一些计算列(从大数据量降低为小数据量 : 增加索引)
10JDBC
10.1数据库驱动
我们的程序会通过数据库驱动,才能和数据库打交道!
10.2JDBC
SUN公司为简化开发,对数据库操作提供了一个统一规范 Java database connect
这些规范由具体的厂商去做~
对于开发人员来说,我们只需要学习JDBC接口操作即可!
10.3第一个JDBC程序
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
drop table if EXISTS `users`;
CREATE TABLE `users`(
`id` INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
`email` VARCHAR(60)
);
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`)
VALUES(1,'zhangsan','123456','zs@sina.com'),
(2,'lisi','123456','lisi@sina.com'),
(3,'wangwu','123456','wangwu@sina.com');
/**
* 我的第一个JDBC程序
*/
public class JdbcDemo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
//3.连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL的对象去执行SQL,可能存在结果,查看返回结果
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.print("id=" + resultSet.getObject("id")+" ");
System.out.print("name=" + resultSet.getObject("name")+" ");
System.out.print("pwd=" + resultSet.getObject("password")+" ");
System.out.println("email=" + resultSet.getObject("email")+" ");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
依赖:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
步骤总结
- 加载驱动
- 连接数据库DriverManager
- 获得执行SQL的对象statement
- 获得返回的结果集
- 释放连接
DriverManager
//固定写法加载驱动
Class.forName("com.mysql.jdbc.Driver");
//数据库对象
Connection connection = DriverManager.getConnection(url, username, password );
//数据库设置自动提交
//事务提交
//事务滚回
connection.rollback();
connection.commit();
connection.setAutoCommit(true);
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
//mysql 3306 jdbc:mysql:
//oracle 1521 jdbc:oracle:thin:@localhost:1521:sid
Statement,PreparedStatement对象,执行SQL的类
Statement statement = connection.createStatement();
statement.executeQuery(); //查询操作,返回一个ResultSet
statement.execute(); //执行任何sql
statement.executeUpdate(); //更新,插入,删除都用它,返回受影响的行数
Resultset结果集,封装了所有的结果集
resultSet.getObject() //在不知道类型的情况下使用, 如果知道类型就是用指定的类型
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一个数据
resultSet.absolute(row); //移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();
10.4statement对象
配置工具类JdbcUtils.java
public class JdbcUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
try {
InputStream stream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(stream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.获取连接
public static Connection getConnector() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//3.释放链接资源
public static void release(Connection con, Statement sta, ResultSet set){
if (set!=null){
try {
set.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con!=null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(sta!=null){
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
编写配置文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
编写测试类test.java
public class Test {
public static void main(String[] args) throws SQLException {
String s = "select * from users";
Connection connector = JdbcUtils.getConnector();
Statement statement = connector.prepareStatement(s);
ResultSet resultSet = statement.executeQuery(s);
while (resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
}
}
}
SQL注入问题
SQL存在漏洞, 会被攻击,导致用户数据泄露 SQL会被拼接 or 1=1”
10.5PreparedStatement对象
PreparedStatement 可以防止SQL注入!并且效率更高!
String s = "select * from users";
Statement statement = connector.prepareStatement(s);
//本质,把传递进来的参数当做字符
需要先写SQL但是不执行,存在一个预编译的过程
10.6JDBC操作事务
事务:要么都成功要么都失败
ACID:原子性,一致性,隔离性,持久性
10.7数据库连接池
数据库连接- - - 执行完毕- - - 释放
连接- - -释放 这个过程很浪费资源的
池化技术: 准备一些预先的资源,过来就连接预先准备好的
- 最小连接数:100
- 常用连接数:10
- 最大连接数:100 业务最高承载上线,超过100的连接, 就等待
- 存在一个等待超时: 1000ms
开源数据源实现
DBCP
C3P0
Druid: 阿里巴巴
使用了这些数据库连接池之后,我们再项目开发中,就不需要编写连接数据库的代码了
DBCP(拿来即用)
引入依赖:
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
编写工具类:
DbcpUtils.java
public class DbcpUtils {
private static DataSource dataSource = null;
static {
try {
InputStream stream = com.bo.lession02.utils.JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpConfig.properties");
Properties properties = new Properties();
properties.load(stream);
//创建数据源 工厂模式 --- >创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.获取连接
public static Connection getConnector() throws SQLException {
return dataSource.getConnection(); //从数据源中获取连接
}
//3.释放链接资源
public static void release(Connection con, Statement sta, ResultSet set) {
if (set != null) {
try {
set.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (sta != null) {
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
编写测试类:
test.java
public class Test {
public static void main(String[] args) throws SQLException {
String s = "select * from users";
Connection connector = DbcpUtils.getConnector();
Statement statement = connector.prepareStatement(s);
ResultSet resultSet = statement.executeQuery(s);
while (resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
}
DbcpUtils.release(connector,statement,resultSet);
}
}
C3P0(拿来即用)
导入依赖
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.19</version>
</dependency>
编写工具类
C3p0Utils.java
public class Test {
public static void main(String[] args) throws SQLException {
String s = "select * from users";
Connection connector = C3p0Utils.getConnector();
Statement statement = connector.prepareStatement(s);
ResultSet resultSet = statement.executeQuery(s);
while (resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
}
C3p0Utils.release(connector,statement,resultSet);
}
}
编写配置文件
c3po-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省(默认)配置
如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省(默认)---->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--
C3P0的命名配置,
如果代码中的ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就代表使用的是name= mysql的
-->
<named-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>
结论
无论使用什么数据源, 本质还是一样的,DataSource接口是不会变的,方法就不会变