- 数据库
- 什么是数据库
- 数据库的分类
- Mysql的简介
- Mysql的安装
- SQLyog
- 连接数据库
- *** 作数据库
- 数据库的 *** 作
- 数据库的数据类型
- 数据库的字段属性（*重点*）
- 创建数据库
- 数据表的类型
- MySql数据管理
- 外键（了解）
- DML语言
- 添加
- 删除
- DQL数据查询
- DQL
- Where查询语句
- 分页和排序
- 子查询：
- MySql函数
- 常用函数
- 聚合函数
- 数据库的级别MD5加密（拓展）
- 事务
- 索引
- 索引的分类
- 索引原则
- 权限管理和备份
- 用户管理
- Mysql备份
- 规范数据库的设计
- 为什么需要设计
- 为什么需要数据规范化
- JDBC(重点)
- 第一个JDBC接口
- statement对象
- PreparedStatement对象
- 数据库连接池
- 调优
- 性能监控
数据库 ( DataBase , 简称DB )
概念 : 长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据 “仓库”
作用 : 保存,并能安全管理数据(如:增删改查等),减少冗余…
数据库的分类1、关系型数据库 ( SQL )
MySQL , Oracle , SQL Server , SQLite , DB2 , …
通过表和表之间，行和列之间的关系进行数据的存储，，学员信息表，考勤表…
2、非关系型数据库 ( NOSQL ) Not Only
Redis , MongoDB , …
非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
DBMS 数据库管理系统：
数据库管理软件 , 科学组织和存储数据 , 高效地获取和维护数据
MySQL，数据库管理系统
为什么要说这个呢?
因为我们要学习的MySQL应该算是一个数据库管理系统.
Mysql的简介概念 : 是现在流行的开源的,免费的 关系型数据库
历史 : 由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。
特点 :
1、免费 , 开源数据库
2、小巧 , 功能齐全
3、使用便捷
4、可运行于Windows或Linux *** 作系统
5、可适用于中小型甚至大型网站应用
官网 : https://www.mysql.com/
压缩包地址：https://dev.mysql.com/get/Downloads/MySQL-5.7.19-winx64.zip
下载后得到zip压缩包.
解压到自己想要安装到的目录，本人解压到的是D:\Environment\mysql-5.7.19
添加环境变量：我的电脑->属性->高级->环境变量
选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹
编辑 my.ini 文件 ,注意替换路径位置
[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables
启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)
再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）
进入界面后更改root密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
刷新权限：
flush privileges;
修改 my.ini文件删除最后一句skip-grant-tables
重启mysql即可正常使用
net stop mysql
net start mysql
连接上测试出现以下结果就安装好了
这里给大家推荐一个工具 : SQLyog .
即便有了可视化工具,可是基本的DOS命名大家还是要记住!
SQLyog可手动 *** 作,管理MySQL数据库的软件工具
特点 : 简洁 , 易用 , 图形化
使用SQLyog管理工具自己完成以下 *** 作 :
连接本地MySQL数据库
新建MySchool数据库
字段
GradeID : int(11) , Primary Key (pk)
GradeName : varchar(50)
数据库名称MySchool
新建数据库表(grade)
在历史记录中可以看到相对应的数据库 *** 作的语句
连接数据库使用命令行连接：
mysql -u root -p123456 --连接数据库
alter user root@localhost identified by '123456'; --修改用户密码
flush privileges; --刷新权限
------------------------------------
--所有的语句都用;结尾
show databases;--查看所有的数据库
use school; --use 数据库名 切换数据库
show tables;--查看所有的表
desc student;--查看名为student表信息
create database westos; --创建一个名为westos的数据库
exit;--退出链接
-- 单行注释
/*
多行注释
*/
创建数据库
CREATE DATABASE IF NOT EXISTS westos
删除数据库
DROP DATABASE IF EXISTS westos
使用数据库
USE `school` --加单引号会变成字段 特殊的字符一眼加这个符号
查看数据库
SHOW DATABASES --查看所有的数据库
整数：
tinyint ：十分小的数据 1个字节
smallint ：较小的数据 2个字节
mediumint： 中等大小的数据 3个字节
int： 标准的整数 4个字节 常用的
bigint： 较大的数据 8个字节
float ：浮点数 4个字节
double ：浮点数 8个字节
decimal ：字符串形式的浮点数 金融计算的时候会用
字符串：
char ：字符串固定大小 0~255
varchar： 可变字符串大小 0~65535 对应String
tinytest ：微型文本 2^8-1
test ：文本串 2^16 -1 保存大文本
时间日期：
data YYYY-MM-DD：日期格式
time HH:mm:ss
datetime YYYY-MM-DD HH:mm:ss: 最常用的时间格式
timestamp :时间戳 1970.1.1到现在的毫秒数 较为常用
year 年份标识
Unsigned：无符号的整数，声明了这里面的列不能为负数。
zirofill:0填充的
不足的位数，使用0来填充， int（3）， 5— 005
自增：通常理解为自增，自动在上一条记录的基础上+1（默认）
通常用来设计唯一的逐渐~ index，必须是证书类型
可以自定义设计主键自增的起始值和步长
null和not null：假设设置为not null，如果不给它赋值，就会报错！
null 如果不填写值，默认就是null！
默认：设置默认的值sex，默认值为男，如果不指定该列的值，则会有默认的值！
/* 每个表，都需要存在以下五个字段 未来做项目用的，表示一个记录存在的意义 拓展
id 主键
'version' 乐观锁
is_delete 伪删除 认为被删除 实际没有
gmt_create 创建时间
gmt_update 修改时间
*/
--AUTO_INCREMENT 自增
--PRIMARY KEY 主键
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT'姓名',
`paw` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
`sex` VARCHAR(2) NOT NULL DEFAULT'女' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
`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 -- 查看student数据表定义的语句
desc student -- 显示表的结构
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年是用的
*/
常规使用 *** 作：
MYISAM 节约空间，速度较快
INNODB 安全性高，事务的处理，多表多用户 *** 作
修改：
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(11) -- 重命名，
-- 删除表的字段 表名 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
删除：
-- 删除表 DROP TABLE 表名(如果表存在再删除)
DROP TABLE [if exists] teacher1
方式一：在创建表的时候，增加约束（麻烦复杂）
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
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT'姓名',
`paw` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码',
`sex` VARCHAR(2) NOT NULL DEFAULT'女' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`gradeid` INT(10) NOT NULL COMMENT '年级id',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`), -- FK_ 外键约束规定
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
删除有关外键表的时候必须先删除应用别人的表在删除引用的表
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 哪个表(引用的列)
以上的 *** 作都是物理外键，数据库级别的外键，不建议使用！（避免数据库过多困扰）
DML语言数据库的意义：数据存储，数据管理
DML语言：数据 *** 作语言
insert 添加
update 修改
delete 删除
insert:
语法：INSERT INTO 表名 ([列1],[列2],[列3]) VALUES (‘字段1’,‘字段2’,‘字段3’),(‘字段1’,‘字段2’,‘字段3’)
-- 一般写插入语句，我们一定要数据和字段一一对应！
-- INSERT INTO 表名 ([列1],[列2],[列3]) VALUES ('字段1','字段2','字段3'),('字段1','字段2','字段3')
INSERT INTO `grade` (`gradename`) VALUES ('大二'),('大三')
修改：
update 修改谁 （条件） set原来的值=新值
-- 修改学院名字，带了简介
UPDATE `student` SET `name`='狂神'WHERE id=1;
-- 不指定条件的情况下，会改动所有的值
UPDATE `student` SET `name`='长江7号'
-- 语法：
-- update 表名 set 列=值,[列=值],[列=值] where [条件]
-- 删除数据（避免这样写，会全部删除）
delete from `student`
-- 删除指定数据
delete from `student` where id=1;
TRUNCATE命令
作用：完全清空数据库，表的结构和索引约束不会变！
-- 清空student表
TRUNCATE `student`
delete和TRUNCATE不同
不同
TRUNCATE 重新设置自增列 计数器会归零
TRUNCATE 不会影响事务
DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `table` -- 自增会归零
了解即可：DELETE，重启数据库，现象
innoDB 自增列会从1开始（存在内存中的，断电即失）
MyISAM 继续从上一个自增量开始。（存在文件中，不会丢失）
(Data QueryLANGUAGE：数据查询语言）
所有的查询 *** 作都用它 Select
简单的查询，复杂的查询它都能做~
数据库中最核心的语言，最重要的语句
使用频率最高的语句
select完整的语法
select查询：
SELECT [ALL | DISTINCT]
{*l table.* l [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 l row_countOFFSET offset]];--指定查询的记录从哪条至哪条
指定字段查询：
-- 查询全部的学生 select 字段 from 表
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 别名，给结果起一个名字 AS
SELECT `studentno` AS 学号,`studentname`
AS 学生姓名 FROM student -- 表也可以加AS 给表起一个别名
-- 函数 concat(a,b)
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student
语法：selectzi 字段 from 表
有的时候表的名字不容易看出来 所以起一个别名好看出来AS 字段名 as 别名 表名 as 别名
去重复：
-- 查询一下有哪些同学参加了考试
SELECT * FROM result -- 查询全部的考试成绩
SELECT `studentno` FROM result
-- 去重复
SELECT DISTINCT `studentno` FROM result
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长（变量）
作用：检索数据中符合条件的值
逻辑运算符：
模糊查询：比较运算符
-- 模糊查询
-- like结合 %(代表0到人一个字符) _(代表一个字符)
-- in(具体的一个值或者多个值)
-- 联表查询 join
-- join（连接的表） on（判断的条件） 连接查询
-- where 等值查询
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno
联表查询：
在这里插入代码片======================联表查询 join ==============================
-- 查询参加考试的同学 （学号，姓名，考试编号，分数）
SELECT * FROM student
SELECT * FROM result
/*
1. 分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo
*/
-- JION（表） ON （判断的条件）连接查询
-- where 等值查询
SELECT 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,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL
-- 查询了参加考试同学的信息：学号：学生姓名：科目名：分数
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
-- 我要查询哪些数据 SELECT ....
-- 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
-- 假设存在一种多张表查询，先查询两张表，然后再慢慢增加
--FROM a LEFT JOIN b 左为准
--FROM a RIGHT JOIN b 右为准
自连接：
自己跟自己连接，核心：一张表拆分为两张一样的表
父类：
子类：
*** 作：查询父类和子类的关系
-- 查询父子信息
SELECT a.`categroryName` AS `父栏目`,b.`categroryName` AS `子栏目`
FROM `catgroy` AS a,`catgroy` AS b
WHERE a.`categoryid`=b.`pid`
-- 查询学员所属的年级（学号，学生的姓名，年级）
SELECT studentNo,studentName,gradeName
FROM student s
INNER JOIN `grade` g
ON s.`GradeId`=g.`GradeId`
-- 为什么要分页
-- 缓解数据库压力，给人的体验更好
-- 分页，每页显示五条数据
-- 语法： limit 当前页，页面的大小
-- limit 0,5 1-5
-- limit 1,5 1-5
-- limit 6,5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student 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 ASC
LIMIT 0,5
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit(n-1)*pageSize,pageSize
where (这个值是计算出来的)
本质：在where语句中嵌套一个子查询语句
-- ===========================where=========================
-- 1.查询 数据库结构-1的所有考试结构（学号，科目编号，成绩） 降序
-- 方式一： 连接查询
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC
-- 方式二：使用子查询(由里及外)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE StudentNo=(
SELECT SubjectNo FROM `subject`
WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC
-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80
-- 在这个基础上 增加一个科目 ，高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(
SELECT Subject FROM `subject`
WHERE SubjectName='高等数学-2'
)
-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='高等数学-2'
WHERE `SubjectaName`='高等数学-2' AND StudentResult >=80
-- 再改造 (由里即外)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等数学-2'
)
)
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT `SubjectName`,AVG(StudentResult),MAX(StudentResult)
FROM result r
INNER JOIN `Subject` sub
ON r.SubjectNo=sub.SubjectNo
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING AVG(StudentResult)>80
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
SELECT INSERT('java',1,2,'cccc') -- 从某个位置开始替换某个长度
SELECT UPPER('abc')
SELECT LOWER('ABC')
SELECT REPLACE('坚持就能成功','坚持','努力')
-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
什么是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,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1 -- 加密全部
-- 插入时加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'红',MD5('123456'))
-- 如何校验，将用户传递过来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123456')
要么都成功要么都失败
1、sql执行 A给B 转账 A1000–>200 B200
2、sql执行 B收到A的钱 A800–> B400
将一组SQL放在一个批次中 去执行~
事物的原则：ACID原则（原子性，一致性，隔离性，持久性）
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质:索引是数据结构。
索引的分类主键索引 (PRIMARY KEY)
主键不可重复
唯一索引 (UNIQUE KEY)
避免重复的列出现，唯一索引可以重复，多个列都可以标识位
常规索引（KEY/INDEX）
全文索引 FULLTEXT
SHOW INDEX FROM student -- 显示所有的索引信息
-- 增加一个全文索引 （索引名）列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX `wuhu`(`studentname`)
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH
测试索引
SELECT * FROM app_user WHERE `name` = '用户99999' -- 1.191 sec
SELECT * FROM app_user WHERE `name` = '用户99999' -- 1.070 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999' -- 1.070 sec
EXPLAIN SELECT * FROM app_user
-- id_表名_字段名 规范
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户99999' -- 0.006 sec
1、 索引不是越多越好
2、 不要对进程变动数据加索引
3、 小数据量的表不要加索引
4、 索引一般加在常用来查询的字段上
SQL可视化管理
SQL命令
-- 创建用户
CREATE USER shijie IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR shijie = PASSWORD('123456')
-- 重命名 RENAME USER 原用户名 TO 新用户名
RENAME USER shijie TO shijie2
-- 用户授权 GRANT ALL PRIVILEGES（全部权限）库.表 TO 用户
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES *.* TO -- 全部库，全部表
-- 查询权限 SHOW GRANTS FOR 用户
SHOW GRANTS FOR kuangshen -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- root用户要加@地址
-- 撤销权限 REVOKE 权限，在哪个库，哪个表撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM shijie
-- 删除用户
DROP USER shijie
为什么要备份：
保证重要的数据不丢失
数据转移
MySQL数据库备份方式
直接拷贝物理文件
在可视化工具上导出
使用命令行导出 mysqldump 命令行 cmd才叫命令行
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表 > 物理磁盘位置/文件名
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名
# mysqldump -h 主机 -u 用户名 -p密码 数据库> 物理磁盘位置/文件名
# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:a.sql
糟糕的数据库设计：
数据冗余，浪费空间
数据的插入和删除都会麻烦、异常【避免使用物理外键】
程序的性能差
良好的数据库设计：
节省内存空间
保证数据库的完整性
方便开发系统
软件开发中，关于数据库的设计
分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系图E-R图
设计数据库的步骤：（个人博客）
为什么需要数据规范化信息重复
更新异常
插入异常
无法正常显示信息
删除异常
丢失有效的信息
范式
第一范式（1NF）
原子性：保证每个列不可以再分
第二范式（2NF）
前提：满足第一范式
每张表只表示一件事
第三范式（3NF）
前提：满足第一范式
前提：满足第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范性 和 性能的问题
关联查询的表不要超过三张
考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
在规范性能的问题的时候，需要适当考虑一下规范性！
故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
SUN公司为了简化开发人员的(对数据库的统一） *** 作，提供了一个(Java *** 作数据库的）规范，俗称JDBC
这些规范的实现由具体的厂商去做!
对于开发人员来说，只需要掌握JDBC接口的 *** 作即可
第一个JDBC接口创建测试数据库
步骤总结：
1、加载驱动
2、链接数据库DriverManager
3、执行sql的对象
4、获得返回的结果集
5、释放链接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//http://主机地址：端口
//mysql 3306
//协议//主机地址：端口/数据库名?参数1&参数2
//jdbc:mysql://localhost:3306/数据库名?参数1&参数2
//oralce 1521
//jdbc:oralce：thin:@localhost:1521:sid
Connection connection = DriverManager.getConnection(url, username, password);
// 设置自动提交
// 事务提交
// 事务回滚
connection.setAutoCommit();
connection.rollback();
connection.commit();
statement.executeQuery();//查询 *** 作返回ResultSet
statement.execute();//执行任何sql
statement.executeUpdate();//更新、插入、删除都用这个，返回一个受影响的行数
resultSet.getObject();//不知道列类型用Object
resultSet.getString();//知道的时候用指定类型
resultSet.beforeFirst();// 移动到最前面
resultSet.afterLast();// 移动到最后面
resultSet.next(); //移动到下一个
resultSet.previous();//移动到前一行
resultSet.absolute();//移动到指定行
resultSet.close();
statement.close();
connection.close(); // 耗资源，用完关掉
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
SQL注入问题
sql存在漏洞，会被攻击导致数据泄露，SQL会被拼接 or
PreparedStatement可以防止SQL注入吗，效率更好！
PreparedStatement防止.SQL注入的本质，把传递进来的参数当做宁符假设其中存在转义字符,比如说·会被直接转义
隔离性的问题：
脏读：一个失误读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致
数据库连接池数据库连接 – 执行完毕 —释放
连接-- 释放 十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
---- 开门–业务员：等待 – 服务—
常用连接数 10个
最小连接数 10
最大连接数 15 业务最高承载上线
超过 排队等待
等待超时 100ms
编写连接池，实现一个接口 DataSource
DBCP：
C3P0
Druid：阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了!
调优 性能监控PROFILES 已经被替代
SHOW PROFILES 查看性能监控
SET profiling=1; 开启性能监控 开启之后可以查看
在这里插入图片描述
优化器 基于成本优化 和 基于规则优化 一般是基于规则优化
SHOW PROFILE 查看每步消耗的时间
①ALL：显示所有的开销信息。
②BLOCK IO：显示块IO开销。
③CONTEXT SWITCHES：上下文切换开销。
④CPU：显示CPU开销信息。
⑤IPC：显示发送和接收开销信息。
⑥MEMORY：显示内存开销信息。
⑦PAGE FAULTS：显示页面错误开销信息。
⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
⑨SWAPS：显示交换次数开销信息。
SHOW PROFILE cpu FOR QUERY 10 查询第10次查询时CPU耗时
performance_schema MYSQL自带的性能监控表 可以了解
schema与数据类型优化
数据类型的优化
更小的通常更好
尽量使用可以正确存储数据的最小数据类型
因为他们占用更小的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少
简单就好
简单的数据类型 *** 作通常需要更少的CPU周期
案列
整形比字符串 *** 作代价更低
使用mysql自建的数据类型而不是字符串类型存储日期和时间
用整形存ip地址
SELECT INET_ATON (‘192.168.68.60’) 3232252988
SELECT INET_ATON (‘3232252988’) 192.168.68.60
尽量避免空值
null != null
合理使用范式反范式
主键使用
字符集选择 utf-8 -> utf-8mb4
存储引擎的选择
MyISAM： 数据和索引分开存储
不支持事务，但是每次查询都是原子的；
支持表级锁，即每次 *** 作是对整个表加锁；
存储表的总行数；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
InnoDb： 数据和索引在一起存储
支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
不存储总行数；
一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受 *** 作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受 *** 作系统文件大小限制，一般为2G），受 *** 作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。
在这里插入图片描述
适当的数据冗余
适当拆分
执行计划
explain select * from emp;
id:选择标识符
id
SELECT识别符。这是SELECT的查询序列号
我的理解是SQL执行的顺序的标识，SQL从大到小的执行
id相同时，执行顺序由上至下
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
select_type:表示查询的类型。
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table:输出结果集的表
显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称
partitions:匹配的分区
type:表示表的连接类型
对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys:表示查询时，可能使用的索引
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
key:表示实际使用的索引
key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len:索引字段的长度
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好
ref:列与索引的比较
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows:扫描出的行数(估算的行数)
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by *** 作，而且无法利用索引完成的排序 *** 作称为“文件排序”
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句
总结
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN只能解释SELECT *** 作，其他 *** 作要重写为SELECT后查看执行计划。
索引数据类型
常见的两种数据引擎用的是B+树
MEMORY用的是hash index
索引匹配方式
查询优化
分区表
# 面试
回表查询,先定位主键值，再定位行记录。多扫了一遍索引树。
在这里插入图片描述
覆盖索引
如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
最左匹配
索引下推
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）