MySQL从删库到跑路
1、初识MySQL
- JavaEE：企业级java开发 Web
- 前端（页面：展示，数据）
- 后台（连接点：连接数据库JDBC，连接前端（控制，控制视图跳转，和给前台传数据））
- 数据库（存取数据：TXT 、Excel、word）
只会写代码，学好数据库，基本混饭吃
操作系统，数据结构与算法！开始搬砖
离散数学 数字电路 编译原理
1.1、为什么学数据库
- 岗位需求
- 大数据时代、得数据者的天下；
- 被迫需求：存数据 去IOE
4、数据库是所有软件体系中最核心的存在 DBA
1.2、什么是数据库
- 数据库（DB，DataBase）
- 概念：数据库（Database ）：是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。（数据仓库 软件 安装在操作系统之上（Linux widowns mac） SQL ，可以存储大量的数据，500万！）
- 作用存储数据 管理数据
1.3、数据库分类
- 关系型数据库：SQL
- MySQL Oracle SqlServer DBZ SQLlite
- 通过表和表之间，行和列之间的关系进行数据的存取
- 非关系型数据库：NoSQL
- Redis MongoDB
- 非关系型数据 对象存储 通过对象的自身的属性来决定，
DBMS（数据管理系统）
- 数据库的管理软件：科学有效的管理我们的数据、维护和获取数据
- MySQL：数据库管理系统！
1.4、MySQL简介
MySQL是一个关系型数据库管理系统
前世：瑞典MySQL AB公司
今生：属于Oracle旗下产品
MySQL是最流行的开放源码SQL数据库管理系统的应用软件之一
开源的数据库软件
体积小 速度快 总体拥有成本低 招人成本比较低 所有人必须会
中小网站 或者大型网站 集群！
官网：https://www.mysql.com/
安装建议
-
尽量不要使用exe 注册表 卸载的时候不方便
-
尽可能使用压缩包安装
1.5、安装mysql
网上教程贼多自己去找 也挺香的
安装步骤
1、下载后得到zip压缩包.
2、解压到自己想要安装到的目录，本人解压到的是D:\Environment\mysql-5.7.19
3、添加环境变量：我的电脑->属性->高级->环境变量
选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹
4、编辑 my.ini 文件 ,注意替换路径位置
[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables
5、启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)
6、再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
7、然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）
8、进入界面后更改root密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
9、刷新权限
flush privileges;
10、修改 my.ini文件删除最后一句skip-grant-tables
11、重启mysql即可正常使用
net stop mysql
net start mysql
12、连接上测试出现以下结果就安装好了
一步步去做 , 理论上是没有任何问题的 .
如果您以前装过,现在需要重装,一定要将环境清理干净 .
好了,到这里大家都装好了,因为刚接触,所以我们先不学习命令.
这里给大家推荐一个工具 : SQLyog .
即便有了可视化工具,可是基本的DOS命名大家还是要记住!
1.6、SQLyog安装
可手动操作,管理MySQL数据库的软件工具
特点 : 简洁 , 易用 , 图形化
使用SQLyog管理工具自己完成以下操作 :
-
连接本地MySQL数据库
-
新建MySchool数据库
-
- 字段
- GradeID : int(11) , Primary Key (pk)
- GradeName : varchar(50)
- 数据库名称MySchool
- 新建数据库表(grade)
在历史记录中可以看到相对应的数据库操作的语句 .
连接数据库
打开MySQL命令窗口
- 在DOS命令行窗口进入 安装目录\mysql\bin
- 可设置环境变量，设置了环境变量，可以在任意目录打开！
连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码
注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !
几个基本的数据库操作命令 :
update user set password=password('123456')where user='root'; 修改密码
flush privileges; 刷新数据库
show databases; 显示所有数据库
use dbname；打开某个数据库
show tables; 显示数据库mysql中所有的表
describe user; 显示表mysql数据库中user表的列信息
create database name; 创建数据库
use databasename; 选择数据库
exit; 退出Mysql
? 命令关键词 : 寻求帮助
-- 表示注释
1.7、命令行连接数据库
mysql -u root -p --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host 'localhost';--修改用户密码
flush privileges;--刷新权限
----------------------------------------
--所有的语句都使用分号结尾
show databases;--查看所有的数据库
use 数据库名;--切换数据库
show tables;--查看数据库中所有的表
describe student;--查看表的信息
create database westos;--创建一个数据库 westos
exit;--退出连接
--单行注释（SQL本来的注释 有些地方也可以 用#）
/*
多行注释
*/
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ai7vwJje-1596981632128)(mysqlnote.assets/image-20200801085707359.png)]
2、数据库操作
操作数据库>操作数据中的表>操作表中的数据
mysql的大小写不区分
2.1、操作数据库（了解）
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
2、删除数据库
DROP DATABASE IF EXISTS westos;
3、使用数据库
use school
4、查看数据库
show dabases ;--查看所有的数据库
2.2、数据库的列类型
数值
- tinyint 十分 小的数据 一个字节
- smallint 较小的字节 2个字节
- mediumint 中等大小的数据 3个字节
- int 标椎的整数 4个字节 常用的
- big 较大的数据 8个字节
- float 浮点数 4字节
- double 浮点数 8字节
- decimal 字符串形式的浮点数 金融计算的项目使用
字符串
- char 字符串固定大小的 0~255
- varchar 可变大小的字符串 0~65535 常用的 String
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1
时间日期
java.util.Date
- date yyyy-MM-DD 日期
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970年01月01号到现在的毫秒数！
- year 年份表示
null
- 没有值 未知
- 注意不要使用 null进行运算
2.3、数据库的字段属性（重点）
- Unsigned：
- 无符号的整数
- 声明了该列不能声明为负数
- zerofill
- 0填充
- 不足的位数使用0填充 int（3） 5–005
- 自增
- 通常理解为自增 自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键~index 必须整数类型
- 可以自定义步长
- 非空 NULL not null
- 假设设置为not null 如果不给它赋值 机会报错
- null 如果不填写 默认就是null
- 默认
- 设置默认值
- sex，默认值为男 ，如果不指定该列的值 就默认为男
2.4、创建数据库表
-- 目标创建一个school数据库
-- 创建一个学生表（列 字段） 使用SQl创建
-- 学号 int 登录密码 varcahr(20) 姓名， 性别 varchar（2） 出生日期（datetime） 家庭住址 email
-- 注意点使用 英文（） 表的名称 和 字段尽量使用''括起来
-- AUTO_INCREMENT自增
-- 所有语句后面加英文逗号,最后一个不用加
use school
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '名字',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMIT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8;
格式：
CREATE TABLE [ IF NOT EXISTS] '表名'(
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释],
......
'字段名' 列类型 [属性] [索引] [注释]
)[表的类型] [表的字符集] [注释];
常用命令
show create database school;--查看创建数据库的语句
show create table student;--查看创建表的语句
DESC student;--显示表的结构
2.5、数据表的类型
/*
INNODB 默认使用
MYISAM 早些年使用
*/
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大 约为 MYISAM的两倍 |
经验 ( 适用场合 ) :
- 适用 MyISAM : 节约空间及相应速度
- 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表
数据表的存储位置
-
MySQL数据表以文件方式存放在磁盘中
-
- 包括表文件 , 数据文件 , 以及数据库的选项文件
- 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
-
注意 :
-
-
* . frm – 表结构定义文件
-
* . MYD – 数据文件 ( data )
-
* . MYI – 索引文件 ( index )
-
InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
-
MyISAM类型数据表对应三个文件 :
-
设置数据表字符集
我们可为数据库,数据表,数据列设定不同的字符集，设定方法 :
- 创建时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
- 如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定
2.6、修改删除表
修改
-- 修改表名
alter table 旧表 rename as 新表;
-- 增加表的字段
alter table 表名 add 字段名 字段列属性;
-- 修改表的字段（重命名 修改约束）
alter table 表名 modify 已有字段名 字段列属性; -- 修改约束
alter table 表名 change 原字段 新字段 字段列属性;-- 字段重命名
删除
-- 删除表的字段
alter table 表名 drop 需要删除的字段名
-- 删除表
DROP TABLE IF EXISTS exis 表名;
所有的创建删除操作 尽量加上判断 以免报错
注意
- `` 字段名 使用这个包裹！ 安全
- 注释 – 或 /**/
- sql关键字大小写不敏感
- 所有的符号 用英文 别用中文
3、Mysql数据办理
3.1 外键（知道即可 花里胡哨 没什么用）
-- 年级表
CREATE TABLE `grade` (
`gradeid` int(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` varchar(50) NOT NULL COMMENT '年级名',
PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段 要去引用 年级表的 gradeid
/*
定义外键key
给这个外键添加 约束 （执行引用）
*/
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`gradeid` int(10) NOT NULL AUTO_INCREMENT COMMENT '学生的年级',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
constraint `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建表的时候 没有外键关系
alter `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
--不想用 麻烦 ！！！！！！！！~！！！
删除有外键关系的表的时候，必须要先删除引用别人的表（从表） 在删除被引用的表（主表）
最佳实践
- 数据库就是单纯的表 只用来存数据 只有行（数据）和列（字段）
- 我们想使用多张表 用程序实现 ！！！！！！
3.2 DML语言（有时间背下来 没时间也要背）
数据库意义：数据存储 数据管理
DML语言：数据操作语言！
- insert
- update
- delete
3.3 添加
--插入语句（添加）
insert into 表名 (字段名1,字段名2,字段名3....) values('值1','值2','值3'...);
-- 由于主键自增 可以直接插入 不过必须插入全部字段
insert into 表名 values('值1','值2','值3'...);-- 不建议使用
-- 插入多个值
insert into 表名 (字段名1,字段名2,字段名3....) values('值1','值2','值3'),('值12','值22','值32')...;
注意事项：
- 字段和字段之间用英文逗号隔开
- 字段可以是省略的 但后面的值必须要一一对应
- 可以同时插入多条数据 values 后面的值 使用英文逗号 隔开即可
3.4 修改
update 修改谁 （条件） set 原来的值=新值
-- 修改student 表中学员的名字
update `student` set `name`='狂神' where id=1;
update `student` set `name`='狂神';-- 不指定条件的情况下 会改动 表中的所有该字段
-- 修改多个值 用英文逗号 隔开
-- 语法
update 表名 set colnum=value where 条件;
条件：where子句 运算符
操作符会返回布尔值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5！=6或 5 <>6 | true |
| > | 大于 | ||
| < | 小于 | ||
| >= | 大于等于 | ||
| <= | 小于等于 | ||
| between…and… | 在某个范围内 | [2,5] | |
| and | && | ||
| or | || |
注意
- colnum_name 是数据的列 尽量带上
- 条件 筛选的条件 如果没有指定 则会修改所有的列
- value 是一个具体的值 也可以是一个 变量
- 多个设置的属性之间用英文逗号隔开
3.5 删除
delete 命令
语法：delete from 表 [where 条件];
-- 删除数据 操作表 student
-- 删除指定数据
delete from `student` where id=1;
-- 删除全部数据
delete from `student`
TRUNCATE 命令
作用 ：完全清空一个数据库表 表的结构 和约束 不会变
-- 清空表 student
TRUNCATE `student`;
delete && TRUNCATE 区别
- 相同点 ：都能删除数据 都不会删除表的结构
- 不同
- TRUNCATE 重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
--测试 delete && TRUNCATE 区别
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`coll` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `test` (`coll`) values('1'),('2'),('3');
delete from `test`; --不会删除自增
TRUNCATE table `tesst`;-- 自增会归零
了解即可：DELETE 删除的问题 重启数据库， 现象
- INNODB 自增列从1开始（存在内存当中 断电即失）
- MyISAM 继续从上一个自增量开始 （存在文件中的 不会丢失的）
4、DQL查询数据（最重要！！）
4.1、DQL
（Data Query LANGUAGE：数据查询语言）
- 所有的查询操作都用它
- 简单的查询 复杂的查询它都能用
- ****数据库中最核心的语言 最重要的语句
- 使用频率最高的语句
SELECT 语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
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}];
-- 指定查询的记录从哪条至哪条
测试用例！
-- 创建一个数据库
create database `school`;
-- 创建一张表
use `school`;
create table `grade`(
`GradeID` int(4) not null auto_increment comment '年级编号',
`GradeName` varchar(50) not null comment '年级名称',
primary key (`GradeID`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- 添加数据
insert into `grade` (`GradeID`, `GradeName`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 创建reult 表
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;
-- 添加数据
insert into `result` (`StudentNo`,`SubjectNo`, `ExamDate`,`StudentResult`) values(1000,1,'2013-11-11 16:00:00',66);
-- 创建 student 表
create table `student`(
`StudentNo` int(4) not null comment '学号',
`LoginPwd` varchar(20) not null,
`StudentName` varchar(20) DEFAULT NULL comment '学生姓名',
`Sex` TINYINT(1) DEFAULT NULL comment '性别，取值0或1',
`GradeID` int(4) 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=INNODB DEFAULT CHARSET=utf8;
-- 添加数据
insert into `student` (`StudentNo`,`LoginPwd`,`StudentName`,`Sex`, `GradeID`,`Phone` ,`Address`,`BornDate`, `Email`, `IdentityCard`) values(1001,'123456','李四',1,3,'18786506942','山海','1999-12-11 00:00:00','test@qq.com','522428199912110812'),(1002,'123456','李林',0,3,'18786506942','西安','1999-02-11 00:00:00','test@qq.com','522428199912110832'),(1003,'123456','韩立',1,3,'1878655542','北京','1999-02-11 00:00:00','test@qq.com','412428199912110812');
-- subject 表
create table `subject`(
`SubjectNO` int(4) 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 DEFAULT CHARSET=utf8;
-- 添加数据
insert into subject (`SubjectNO`, `SubjectName`,`ClassHour`,`GradeID`) values (1,'高等数学-1',110,1),(2,'高等数学-2',100,2),(3,'高等数学-3',130,3),(4,'高等数学-4',130,4),(5,'C语言-1',110,1),(6,'C语言-2',100,2),(7,'C语言-3',120,3),(8,'C语言-4',130,4);
4.2、指定查询字段
查询所有的学生
语法：select 字段1… from 表 ；
select * from student;
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student;
别名 ,给结果起一个 名字 可以给字段取别名 也可以给表取 别名
SELECT `StudentNo` AS 学号,`StudentName` AS 名字 FROM student;
函数 Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student;
有的时候，列的名字不是吧那么见名知意 我们可以起别名 AS 字段 as 别名 或者 表 as 表别名
去重 distinct
作用：去除Select 查询出来的结果中重复的数据 只显示一条
-- 查询有哪些同学参加了考试
SELECT * FROM result;-- 查询全部的成绩
-- 查询有哪些同学参加了考试
SELECT `StudentNo` FROM result;
-- 发现重复 去重
SELECT DISTINCT `StudentNo` FROM result;
数据库的列 （表达式）
-- 查看 mysql的版本
SELECT VERSION();
SELECT 100*3-1 AS 计算结果;--用来计算
SELECT @@auto_increment_increment; --查询自增的步长
-- 学员成绩 +1分 查看
SELECT `StudentNo`,`StudentResult` +1 AS '提分后' FROM result
****数据库中的表达式：文本值 列 NULL 函数 计算表达式 系统的变量…
select 表达式 form 表
4.3、where 条件子句
作用：检索数据中符合条件的值
****搜索的条件由一个或者多个表达式组成 一般返回结果是一个 布尔值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与 |
| or || | a or b a ||b | 逻辑或 |
| not ! | not a !a | 逻辑非 |
尽量使用英文字母
-- ===========WHERE ==============
SELECT studentNo,`StudentResult` FROM result;-- 列不区分大小写
-- 查询成绩在95~100之间的
SELECT studentNo,`StudentResult` FROM result WHERE StudentResult>=95 AND StudentResult <=100;
-- AND 用&&
SELECT studentNo,`StudentResult` FROM result WHERE StudentResult>=95 && StudentResult <=100;
-- 模糊查询
SELECT studentNo,`StudentResult` FROM result WHERE StudentResult BETWEEN 95 AND 100;
-- 除了 1000号学生之外的成绩 NOT
SELECT studentNo,`StudentResult` FROM result WHERE studentNo !=1000;
SELECT studentNo,`StudentResult` FROM result WHERE NOT studentNo =1000;
模糊查询
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果操作符结果为null则结果为true |
| IS NOT NULL | a is not null | 如果操作符结果为null则结果为 false |
| BETWEEN | a BETWEEN b and c | 如果a在 b和c之间，则结果为真 |
| LIKE | a LIKE b | SQL 匹配 a如果匹配到b 则结果为真 |
| in | a in(a1,a2,a3…) | 如果a在a1，a2，a3其中的某一个值中 则结果为真 |
模糊查询
-- 查询名字里面 韩 字开头de
--LIKE 结合%(0或多个字符) 和_（一个字符）
SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE '韩%';-- 韩之后任意个字符
SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE '韩_';-- 韩之后只有一个字
-- 查询名字中间有千字的同学
SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE '%千%';-- 韩之后只有一个字
-- ==============================in==========================
-- 查询指定的学生 1001 1002 1003 号学生信息
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN(1001,1002,1003);
-- 查询 在北京 的学生
SELECT StudentNo,StudentName,Address FROM student WHERE Address IN('北京');
-- =========== NULL NOT NULL=======================================================
-- 查询 Address 为null的同学
SELECT StudentNo,StudentName FROM student WHERE Address ='' OR Address IS NULL;
-- 查询有出生日期的学生 BornDate 不为空
SELECT StudentNo,StudentName,BornDate FROM student WHERE BornDate IS NOT NULL ;
4.4、联表查询
JOIN 对比
!(mysqlnote.assets/image-20200804093124085.png)
-- 查询参加了考试的同学（学号 姓名 科目编号 分数）
SELECT * FROM student;
SELECT * FROM result;
/*
思路
1、分析需求 分析查询的字段来自哪些表 （连接查询）
2、确定使用哪种连接查询 ？ 七种
确定交叉点（这两个表中 哪个数据是相同的）
判断的条件 ：student.StudentNo =result.StudentNo
*/
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;
| 操作 | 描述 |
|---|---|
| INNNER JOIN | 如果表中至少有一个匹配，就返回匹配的值 |
| LEFT JION | 会从左表中返回所有的值 即使右表中没有匹配 |
| RGIGHT JION | 会从右表中返回所有的值 即使左表中没有匹配 |
-- 拓展 （查询了参加考试的同学信息 学号 学生 姓名 科目名 分数）
/*
思路
1、分析需求 ;分析查询的字段 来自哪些表 student result sunject （连接查询）
2、确定使用哪种连接查询 ？ 七种
确定交叉点（这两个表中 哪个数据是相同的）
判断的条件：student.StudentNo=result.StudentNo
*/
SELECT s.StudentNo,StudentName,SubjectName,`StudentResult`
FROM student AS s
RIGHT JOIN result AS r
ON r.StudentNo=s.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo;
自连接
/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/
-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
自连接就是自己的表和自己的表连接 核心：一张表拆成两张一样的表
父类
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类表
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作：查询父类对应的子类
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 查询父子信息
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.categoryid=b.pid;
-- C查询学员所属的年级（学号 学生的姓名 年级名称）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.GradeID=g.GradeID;
-- 查询科目所属的年级（科目名称 年级名称）
SELECT SubjectName,GradeName
FROM grade as g
INNER JOIN `subject` AS sub
ON g.GradeID=sub.GradeID;
4.5、分页和排序
测试
/*============== 排序 ================
语法 : ORDER BY
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。 */
-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC
/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处 : (用户体验,网络传输,查询压力)
推导:
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
......
第N页 : limit (pageNo-1)*pageSzie,pageSzie
[pageNo:页码,pageSize:单页面显示条数]
*/
-- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10
4.6、子查询
/*============== 子查询 ================
什么是子查询?
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/
-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;
-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;
-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80
-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-2'
)
-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
4.7、分组和过滤
-- 查询不同成绩的平均分 最高分 最低分
-- 核心：根据不同的课程分组
SELECT SubjectName ,AVG(StudentResult),MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.`SubjectNo`
GROUP BY r.SubjectNo;-- 通过什么字段来分组0
5、Mysql函数
官网：https://dev.mysql.com/doc/refman/8.0/en/
5.1、常用函数
花里胡哨
-- 数学运算
SELECT ABS(-8);-- 绝对值
SELECT CEILING(9.4);-- 向上取整
SELECT FLOOR(9.4);-- 向下取整
SELECT RAND();-- 返回一个0~1之间的一个随机数
SELECT SIGN(-56);-- 判断一个数的符号
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能返航');-- 返回字符串长度
SELECT CONCAT('我','哎','你们');-- 拼接字符串
SELECT INSERT('我爱编程HelloWrold',2,2,'超级热爱')-- 查询替换 从某个位置 替换某个长度
SELECT LOWER('AAAAA');-- 转小写
SELECT UPPER('aaaaa');-- 转大写
SELECT INSTR('dfsgdfgdfgd','d');-- 查找某个字符串第一次出现的索引
时间日期
-- 时间日期
SELECT CURRENT_DATE();-- 获取当前日期
SELECT CURRENT_TIME();-- 获取当前时间
SELECT NOW();-- 获取 当前日期 时间
SELECT LOCALTIME(); -- 获取本地时间
5.2、聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
-- 都能够统计表中的数据 COUNT()
SELECT COUNT(StudentName) FROM student;-- 统计人数 COUNT(字段) 会忽略 Null 值
SELECT COUNT(*) FROM student; -- COUNT(*) 不会忽略 Null 值
SELECT COUNT(1) FROM student; -- 不会忽略 Null 值
SELECT SUM(`StudentResult`) AS 总和 FROM result;-- 求和
SELECT AVG(`StudentResult`) AS 平均分 FROM result;-- 平均分
SELECT MAX(`StudentResult`) AS 最高分 FROM result;-- 最高得分
SELECT MIN(`StudentResult`) AS 最低分 FROM result;-- 最低得分
5.2、数据库级别的MD5加密（扩展）
什么是MD5
MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。
- 主要增强算法复杂度和不可逆性
- MD5不可逆 具体的值 MD5是一样
- MD5破解的原理 ：背后有一个字典 MD5加密后的值 加密前的值
-- ===================测试MD5 加密====================
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) not NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 明文密码
INSERT INTO testmd5
VALUES(1,'张三','123456'),
(2,'zhaoda','123456'),
(3,'lisi','123456'),
(4,'wangwu','123456');
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1;
UPDATE testmd5 SET pwd=MD5(pwd);-- 加密全部密码
-- 插入的时候加密
INSERT INTO testmd5
VALUES(6,'小明',MD5('123456'));
select * from testmd5;
-- 如何校验：将用户传递进来的密码 进行md5加密 然后对比加密后的值
SELECT *FROM testmd5 WHERE name='小明' AND pwd=MD5('123456');
6、事务
6.1、什么是事务
要么都成功 要么都失败
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。
- SQL 执行 A给B转账 A 1000 --> B 200
- SQL 执行 B收到A的钱 A 800 --> B400
将一组SQL 放在一个批次中去执行
INNODB支持
事务原则：ACID原则 原子性 一致性 隔离性 永久性 （脏读 幻读 不可重复读）
原子性(Atomic)
- 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性(Consist)
- 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。
隔离性(Isolated)
- 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性(Durable)
- 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
隔离导致的一些问题
- 脏读
指一个事务读取了另外一个事务未提交的数据
- 不可重复读
在一个事务内读取表中的某行数据，多次读取结果不同。（这个不一定是错误 只是某些场合不对）
- 幻读（虚读）
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。。。。
事务的执行流程
执行事务
-- ==========事务====================
-- mysql默认开启事务自动提交的
SET autoccommit = 0;/*关闭*/
SET autoccommit = 1;/*开启（默认）*/
-- s手动处理事务
SET autoccommit = 0; -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个开始的sql 都在同一个事务中
INSERT xx
INSERT yy
-- 提交 持久化（成功）
COMMIT
-- 回滚 回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autoccommit = 1;-- 开启自动提交
-- 了解
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
模拟场景
-- 转账
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop;
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8;
INSERT into `account` (`name`,`money`) VALUES('A',2000.00),('B',10000);
-- 模拟转账
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 KRY）
- 唯一标识 主键不可以重复 只能有一个列作为主键
- 唯一索引 （UNIQUDE KEY）
- 避免重复的列出现 唯一索引可以重复 多个列 都可以标识为唯一索引
- 常规索引 （KEY/index）
- 默认的 可以用 index 或者 key关键字
- 全文索引 （FULLTEXT）
- 在特定的数据库引擎下才有 MySAM
- 快速定位数据
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后 增加索引
-- 显示所有索引信息
SHOW INDEX FROM `student`;
-- 增加一个索引 (索引名：列名)
ALTER TABLE school.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
-- EXPLAIN 分析SQL执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('韩')
7.2、测试索引
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';
-- 插入100w条数据
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`, `password`, `age`)
VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
索引效率测试
无索引
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
测试普通索引
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
7.3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
索引的数据结构
-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
8、数据库备份
8.1、权限管理
使用SQLyog 创建用户，并授予权限演示
基本命令
/* 用户和权限管理 */ ------------------
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
- 只能创建用户，不能赋予权限。
- 用户名，注意引号：如 'user_name'@'192.168.1.1'
- 密码也需引号，纯数字密码也要加引号
- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
权限解释
-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
/* 表维护 */
-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
8.2、Mysql备份
为什么要备份
- 保证重要数据不丢失
- 数据转移 A—>B
MySQL数据库备份的方式
- 直接拷贝物理文件
-
利用Navicat for MySQL这类的可视化工具导出（ 花里胡哨 百度它不香吗）
-
使用命令行导出 mysqldump 命令行（小黑窗口）
-- 导出 1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql) 2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql) 3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql) 4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql) 可以-w携带备份条件 -- 导入 1. 在登录mysql的情况下：-- source D:/a.sql source 备份文件 2. 在不登录的情况下 mysql -u用户名 -p密码 库名 < 备份文件
mysqldump客户端
作用 :
- 转储数据库
- 搜集数据库进行备份
- 将数据转移到另一个SQL服务器,不一定是MySQL服务器
9、数据库设计
9.1、为什么需要设计数据库
当数据库比较复杂的时候 我们就需要设计了
糟糕的数据库设计
- 数据冗余 浪费空间
- 数据删除 和插入都会麻烦 异常【屏蔽物理外键】
- 程序性能差
良好的数据库设计：
- 节省内存空间
- 保证数据的完整性
- 方便我们开发
软件开发中 关于数据库的设计
-
需求分析 ：分析业务和需要处理的数据库需求
-
概要设计 设计相关的 E-R图
-
设计数据库步骤
-
收集信息
-
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
-
标识实体[Entity]
-
- 标识数据库要管理的关键对象或实体,实体一般是名词
-
标识每个实体需要存储的详细信息[Attribute]
-
标识实体之间的关系[Relationship]
-
9.2、三大范式
为什么需要数据规范化？？
- 信息重复
- 更新异常
- 插入异常
无法正常显示信息
- 删除异常
丢失有效的信息
三大范式
**第一范式 (1st NF)**
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
**第二范式(2nd NF)**
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式要求每个表只描述一件事情
**第三范式(3rd NF)**
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范化和性能的关系
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
10、JDBC（重点）
10.1、数据库驱动
驱动：声卡，显卡， 数据库
我们通过数据库驱动 和数据库打交道！！！
10.2、JDBC
-
sun公司为了简化开发人员的（对数据库的统一操作）操作，提出来一个（java操作数据库）规范俗称 JDBC
-
这些规范的实现由具体的厂商去做
-
对于开发人员来说，只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入数据库驱动包[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OJLom8GM-1596981632139)(mysqlnote.assets/image-20200807183917832.png)]
Maven仓库中下载
官网下载（MAven 比较香 官网太难找）
10.3、编写第一个JDBC程序（删库到跑路的快乐也不过如此）
- 创建一个普通项目（没啥说的）
2、创建一个测试数据库
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
`email` VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,`name`,`password`,`email`,birthday)
VALUES(1,'zhangsan','123456','zin@.com','1980-12-04'),
(2,'lisi','123456','lisi@.com','1980-12-04'),
(3,'wangwu','123456','wang@.com','1980-12-04');
3、导入数据库驱动
- 在项目下新建一个lib目录
- 将驱动复制到lib中
- 将驱动包添加到项目的类库中
4、编写测试代码
package com.kuang.lesson01;
import java.sql.*;
//我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1、加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动
// 2、用户信息和 url
String url="jdbc:mysql://localhost:3306/jdbcStudy?userUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT";
String username="root";
String password="123456";
// 3、连接成功 数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
// 4、执行SQL的对象 Statement 执行SQL 的对象
Statement statement = connection.createStatement();
// 5、执行SQL的对象执行SQL 可能存在结果 查看结果
String sql="select * from users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("password="+resultSet.getObject("password"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
System.out.println("============================");
}
// 6、关闭数据库的连接 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结
- 加载驱动
- 连接数据库DriverManager
- 获得执行SQL的对象Statement
- 获得返回结果
- 释放资源
DriverManager
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url="jdbc:mysql://localhost:3306/jdbcStudy?userUnicode= true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT";
//协议:端口号/数据库名？参数1&参数2.。。
//jdbc:mysql://主机地址:端口号/数据库名？参数1&参数2.。。
//oracle --1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL的对象 connection.prepareStatement()执行SQL的对象
String sql="select * from users";//编写SQL
statement.executeQuery();//查询 返回一个结果集
statement.execute();//执行任何SQL
statement.executeUpdate();//增删改SQL 都行 返回受影响的行数
ResultSet 查询的结果集 分装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知列类型的情况下使用
//如果知道列类型 就用相应的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat()；
.....
遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后
resultSet.next();//移动到下一个
释放资源
resultSet.close();
statement.close();
connection.close();//耗费资源 用完关掉
10.4、statement对象
jdbc中的statement对象用于向数据库发送SQL语句，向完成对数据库增删改查，只要通过这个对象发送增删改查操作即可。
Statement对象的execteUpdate()方法，用于向数据库发送增删改查的语句，execteUpdate()执行完后将会返回一个整数（即对几条数据进行了操作）。
Statement.executeQuery()方法用于向数据库发送查询语句，executeQuery()方法将返回一个结果集ResultSet对象
CRUD操作-create
使用executeUpdate（String sql）方法完成数据库添加操作：
Statement st=com.createStatement();
String sql="insert into user(...) values(...)";
int num=st.executeUpDate(sql);
if(num>0){
System.out.print("插入成功");
}
CRUD操作-delete
使用executeUpdate（String sql）方法完成数据库添加操作：
Statement st=com.createStatement();
String sql="delete from user where id=1";
int num=st.executeUpDate(sql);
if(num>0){
System.out.print("删除成功");
}
CRUD操作-update
使用executeUpdate（String sql）方法完成数据库更新加操作：
Statement st=com.createStatement();
String sql="update user set name='' where name=''";
int num=st.executeUpDate(sql);
if(num>0){
System.out.print("更新成功");
}
CRUD操作-read
使用executeUpdate（String sql）方法完成数据库读加操作：
Statement st=com.createStatement();
String sql="select *from user wher id=1";
ResultSet rs=st.executeUpdate(sql);
while(){
//根据获取的数据类型 分别调用rs的相应方法映射到java对象中
}
代码实现
1、提取工具类
db.properties
diver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?userUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
username=root
password=123456
package com.kuang.lesson02.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String diver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
diver = properties.getProperty("diver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
// 1、驱动只用加载一次
Class.forName(diver);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
// 释放连接资源
public static void release(Connection com, Statement st, ResultSet rs){
if (rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} if (com!=null){
try {
com.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
2、编写增删改的方法
// 增删改 只需要改SQL语句
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql="INSERT INTO users(id,`name`,`password`,`email`,`birthday`) VALUES\n" +"(4,'kuanmgshen','123456','245456@','1990-02-01')";
int i= st.executeUpdate(sql);
if(i>0){
System.out.println("成功插入了"+i+"行数据");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
查询
//查询
public class TestRead {
public static void main(String[] args) {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn= JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql="select * from users";
rs=st.executeQuery(sql);
while (rs.next()){
System.out.println("id="+rs.getInt("id"));
System.out.println("name="+rs.getString("name"));
System.out.println("password="+rs.getObject("password"));
System.out.println("email="+rs.getObject("email"));
System.out.println("==============================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
SQL注入问题
SQL存在漏洞，会被攻击导致数据泄露！！！！！！！！
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
10.5、PrePareStatementd对象
PrePareStatementd可以防止sql注入效率更高
1、新增
package com.kuang.lession03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
try {
conn = JdbcUtils.getConnection();
// 区别
// 使用占位符代替参数
String sql="INSERT INTO users(id,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";
st= conn.prepareStatement(sql);//预编译sql
// 手动给参数复制
st.setInt(1,4);
st.setString(2,"kuangshen");
st.setString(3,"123456");
st.setString(4,"123456@qq.com");
// 注意点 sql.Date 数据库
// util.Date java new Date().getTime())获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
// 执行
int i=st.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
2、删除
package com.kuang.lession03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestDelete {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
try {
conn = JdbcUtils.getConnection();
// 区别
// 使用占位符代替参数
String sql= "delete from users where id=?";
st= conn.prepareStatement(sql);//预编译sql
// 手动给参数复制
st.setInt(1,4);
// 执行
int i=st.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
3、更新
package com.kuang.lession03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
try {
conn = JdbcUtils.getConnection();
// 区别
// 使用占位符代替参数
String sql= "update users set name =? where id=?";
st= conn.prepareStatement(sql);//预编译sql
// 手动给参数复制
st.setString(1,"kuang");
st.setInt(2,4);
// 执行
int i=st.executeUpdate();
if (i>0){
System.out.println("更新成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
4、查询
package com.kuang.lession03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestRead {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection();
// 区别
// 使用占位符代替参数
String sql= "select * from users where id=?";
st= conn.prepareStatement(sql);//预编译sql
// 手动给参数复制
st.setObject(1,1);
// 执行
rs=st.executeQuery();
while (rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("name"));
System.out.println("password="+rs.getObject("password"));
System.out.println("email="+rs.getObject("email"));
System.out.println("birthday="+rs.getObject("birthday"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
5、防止SQL注入
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) {
// login("lisi","123456");
login("''or 1=1","123456");
}
//登录业务
public static void login(String username,String password){
Connection conn=null;
// PreparedStatement 防止注入的本质 就是把传递进来的参数当做字符
// 加入其中出现转义字符 比如说 ‘’就被转义了
PreparedStatement st=null;
ResultSet rs=null;
try {
conn= JdbcUtils.getConnection();//获取数据库连接
String sql="select * from users where `name`=? and `password`=?";
st=conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs=st.executeQuery();
while (rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getObject("password"));
System.out.println("===================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.6、使用idea连接数据库(没啥好说的)
1、连接数据
2、连接成功后可以选择数据库
10.7、事务
要么都成功 要么都失败！！！！
ACID原则
- 原子性
要么全部完成要么都不完成
- 一致性
总数不变
- 隔离性
多个进程互不干扰
- 持久性
一旦提交不可逆 持久化到数据库
隔离性的问题
- 脏读
一个事务读取了另外一个没有提交的事务
-
不可重复读
在同一个事务内，重复读取表中的数据，表数据发生了改变
-
幻读（虚读）
在一个事务内，读取到了别人插入的数据，导致前后读取的数据不一样。
代码实现
1、开启事务 ： conn.setAutoCommit(false);
2、一组业务执行完毕 提交事务
3、可以在catch语句中显示的定义 回滚语句 但默认失败 自动回滚
数据库
create table account(
id int primary key auto_increment,
name varchar(40),
money float
);
insert into account(name, money) VALUES ('A',1000);
insert into account(name, money) VALUES ('B',1000);
insert into account(name, money) VALUES ('C',1000);
业务逻辑代码
package com.kuang.lession04;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交 自动开启事务
conn.setAutoCommit(false);
String sql1="update account set money=money-100 where name='A'";
st=conn.prepareStatement(sql1);
st.executeUpdate();
// int x=1/0;//报错
String sql2="update account set money=money+100 where name='B'";
st=conn.prepareStatement(sql2);
st.executeUpdate();
// 业务完毕 提交事务
conn.commit();
System.out.println("成功");
} catch (SQLException throwables) {
try {//如果失败就回滚
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.8、数据库连接池
数据库连接-----执行完毕—释放
连接—释放 十分浪费系统资源
池化技术 准备一些预先的资源，过来就连接 预先准备好的
-
最小连接数 依情况而定（常用：100 最小连接数：100）
-
最大连接数： 业务做高承载上限
-
排队等候
-
等待超时
编写连接池 实现一个接口 DataSource
开放数据源实现
DBCP
c3p0
Druid:阿里巴巴
使用了这些数据库连接池之后 我们在项目开发中就不需要编写连接数据库的代码
DBCP
需要用到的jar包
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g2lMrRXe-1596981632142)(mysqlnote.assets/image-20200808215429045.png)]
C3P0
需要导入的jar包
结论
无论使用什么数据源 本质还是一样的 DataSource接口不会变
806
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
