MySQL基本用法(IDEA中操作MySQL)

1.初始数据库

数据库分类

关系型数据库

MySQl，Oracle，Spl Server
通过表与表之间，行和列之间的关系进行数据的存储，学员信息表……

非关系型数据库

Redis，MongDB
对象存储，通过对象的自身属性决定

DBMAS(数据库管理系统)
- 数据库管理软件，科学有效的管理数据

MySQL简介

MySQL数据库管理系统

安装MySQL

安装SQLyog

连接数据库

命令行连接

mysql -u root -p123456	--连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';	--修改密码

fulsh privileges	--刷新权限

--所有语句;结尾
show databases;	--查看所有数据库

use school	--切换数据库 use 数据库名

show tables;	--查看数据库所有表
describe student;	--显示数据库中所有表的信息

create database;	--创建一个表

exit;	--退出

--单行注释
/**/多行注释

数据库xxx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

2.数据库操作

不区分大小写

操作数据库->操作数据库中的表->操作数据库中的表的信息

2.1、操作数据库

1、创建数据库

1	CREATE DATABASE [IF NOT EXISTS] westos

2、删除数据库

1	DROP DATABASE IF EXISTS westos

3、使用数据库

1	USE `school` --如果你的表名或者字段名是一个特殊的字符，就要带`字段名`

4、查看数据库

1	SHOW DATABASES --查看数据库数据

2.2数据库类型

数值

tinyint 十分小的数据 1字节
smallint 2字节
mediumint 3字节
int 4字节
bigint 8字节
float 浮点数 4字节
double 浮点是 8字节
decimal 字符串新形式的浮点数金融计算

字符串

char 固定大小 0-255
vatchar 可变字符串 0-65535
tinytext 微型文本 2^8-1
text 文本串 2^16-1

时间日期

data YYYY-MM-DD 日期格式
time HH：mm：ss 时间格式
datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
timestamp 时间戳，1970.1.1到现在的秒数比较常用
year 年份表示

null

没有值，未知
注意，不要用NULL进行计算，否则结果为NULL

2.3数据库的字段属性(重点)

Unsigned：
- 无符号整数
- 声明该列不能为负数
zerofill：
- 0填充
- 不足的位数，使用0来填充
自增
- 自动在上条记录加一
- 设计唯一的主键~index，必须是整数类型
- 可自定义设计主键自增的起始值和步长
非空 NULL not null
- 假设设置为not null，如果不赋值就会报错
- NULL，如果不填值，默认为NULL
默认
- 设置为默认的值
- sex，默认值为男，如果不指定该列的值，则会有默认值

-- 使用英文标点符号，表名和字段用``
-- AUTO_INCREMENT 自增
-- 字符串使用单引号
-- 所有的语句后面加,(英文)，最后一个不加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS `students` (
`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 COMMENT '地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS] `表名` (
`字段名` 列类型 [属性] [索引] [注释]，
`字段名` 列类型 [属性] [索引] [注释]，
    ......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设计] [注释]

2.5、数据表类型

1
2
3

SHOW CREATE DATABASE student -- 查看创建数据库的语句
SHOW CREATE TABLE students -- 查看创建表的语句
DESC students -- 显示表的结构

-- 关于数据库引擎
/*
INNODB  默认使用
MYISAM 早些年使用
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作：

MYISAM 节约空间，速度快
INNODB 安全性高，多表用户操作

所有的数据库文件在data目录下

本质还是文件

设置数据库表的字符集编码

1	CHARSET=utf8

2.6、修改删除表

修改

-- 修改表
ALTER TABLE students1 RENAME AS students
-- 添加字段
ALTER TABLE students1 add age INT(11)
-- 修改表的字段
ALTER TABLE students1 MODIFY age VARCHAR(11)
-- 重命名
ALTER TABLE students1 CHANGE age age1 INT(1)
-- 删除字段
ALTER TABLE students1 DROP age1

删除

1	DROP TABLE IF EXISTS student1

==所有的创建与删除尽量加上判断==

==啊啊啊啊==

3、MySQL数据管理

3.1、外键（了解）

方式一：建表时直接加

CREATE TABLE `students` (
  `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 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

方式二：建表后加

1 2	ALTER TABLE `students`, ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

3.2、DML语言（全记）

DML语言

Insert
update
delete或truncate

3.3、添加

-- 插入语
-- INSERT INTO 表明(字段名1，字段名2.....) VALUES('值一')，('值二')，.....
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 一般可以不写插入语，但是必须要一一对用
-- 擦插入多条数据
INSERT INTO `students1`(`name`,`pwd`,`sex`) 
VALUES('李四','aaaaaa','男'),('王五','111111','男')

3.4、修改

1
2
3

UPDATE `students1` SET `name`='gx' WHERE id=1;
UPDATE `students1` SET `name`='gx' `email`='11111' WHERE id=1;
-- UPDATE 表名 SET 字段名1='value1' [字段名2='value2'......] WHERE [条件];

条件：where子句运算符id等于大于某个值

操作值返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>或！=	不等于	5<>6	true
>
<
<=
>=
>=
BETWEEN…AND…	在某个范围	[2,5]
AND	和		false
OR	或		true

3.5、删除

-- 删除
DELETE FROM `students1` WHERE id=1;
-- DELETE FROM 表明 WHERE [条件]
-- 清空
TRUNCATE students1;

DELETE与TRUNCATE区别

相同：都会删除数据，不会删除表结构
不同：
- TRUNCATE 重新设置自增列计数器会归零
- TRUNCATE 不会影响事务

DELETE删除问题，重启数据库

INNIODB 自增列会从1开始（存在内存中，断电即失）
MYISAM 继续从上一个自增量开始

4、DQL查询数据（重点）

4.1、DQL

所有的查询都用他
简单、复杂的都会用
数据库中最核心、最重要的语句
使用频率最高的语句

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 别名表名 as 别名

去重distinct

作用：去除SELECT查询中重复的数据，重负的数据只显示一条

-- 查询一下有哪些学生参加了考试
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result -- 发现重复数据，去重

数据库的列(表达式)

-- 查询版本号
SELECT VERSION()
SELECT 100*3-1 AS 计算结果
SELECT @@auto_increment_increment -- 查询自增的步长

-- 所有学员成绩加一分
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result

==数据库的表达式：文本值、列、Null、计算表达式、系统变量==

select 表达式 from 表

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
-- and   &&
-- 查询成绩在95~100
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100

SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 && StudentResult<=100

SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- not  !=
-- 除了学号1000以外的成绩
SELECT studentNo,`StudentResult` FROM result
WHERE studentno!=1000

SELECT studentNo,`StudentResult` FROM result
WHERE NOT 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,a3…)	假设a在a1,或者a2…..其中的一个值中,结果为真

-- 模糊查询
-- 查询姓张的同学
-- 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 '张__'

-- 查询姓名中有张字的同学
SELECT `studentNo`,`Studentname` FROM `student`
WHERE studentname LIKE '%张%'

-- in(具体的一个或多个值)
-- 查询1000号学生
SELECT `studentNo`,`Studentname` FROM `student`
WHERE studentno IN (1000,1001)

SELECT `studentNo`,`Studentname` FROM `student`
WHERE `Address` IN ('北京')

-- null  not null
-- 查询地址为空的学生
SELECT `studentNo`,`Studentname` FROM `student`
WHERE `Address`='' OR address IS NULL

-- 查询有出生日期的同学
SELECT `studentNo`,`Studentname` FROM `student`
WHERE `BornDate` IS NOT NULL

4.4、联表查询

JOIN 对比

-- 联表查询join
-- 查询参加考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*思路
1.分析需求，分析查询的字段来自那些表（连接查询）
2.确定使用哪种连接查询
确定交叉点（这两张表中哪些数据相同）
判断的条件：学生表中的studentno=成绩表中的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 s
RIGHT JOIN result r
ON s.studentno=r.studentno

-- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno

操作	描述
Inner join	如果表中至少有一个匹配就返回
left join	会从左表中返回值,不管右表有没有
right join	会从右表中返回值,不管左表有没有

-- 联表查询join
-- 查询参加考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*思路
1.分析需求，分析查询的字段来自那些表（连接查询）
2.确定使用哪种连接查询
确定交叉点（这两张表中哪些数据相同）
判断的条件：学生表中的studentno=成绩表中的studentno
*/

-- join (连接的表） on （判断条件） 连接查询
-- where     等值查询
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 s
RIGHT JOIN result r
ON s.studentno=r.studentno

-- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
WHERE studentresult IS NULL

-- 查询参加考试的同学（学号，姓名，科目名称，分数）
/*思路
1.分析需求，分析查询的字段来自那些表（连接查询）
2.确定使用哪种连接查询
确定交叉点（这两张表中哪些数据相同）
判断的条件：学生表中的studentno=成绩表中的studentno
*/
SELECT s.`StudentNo`,`studentname`,`Subjectname`,`StudentResult`
FROM student s
RIGHT JOIN result r
ON r.StudentNo=s.studentno
INNER JOIN `subject` sub
ON r.studentno=sub.subjectno

-- 我要查询哪些数据   select....
-- 从那几张表中查询 from 表 XXX  join  连接表  on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢添加

-- from a left join b	以a表为标准
-- from a right join b	以b表为标准

自连接

自己的表和自己的表连接：一张表拆为；两张一样的表

父类

categioyid	categioyName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categioyid	categioyName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

1
2
3

SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

4.5、分页和排序

排序

-- 排序：升序ASC，排序DESC
-- order by 通过那个字段排序  怎么排
-- 查询的结果数据 成绩降序 排序
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 `StudentName`='高等数学-1'
ORDER BY StudentResult DESC

分页

-- 缓解数据库压力

-- 分页每页只显示五条数据
-- 语法：limit 起始页，页面的大小
-- limit 0,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 `StudentName`='高等数学-1'
ORDER BY StudentResult DESC
LIMIT 5,5

-- 第n页    limit 0，5   （n-1）*5
-- 【pageSize：页面大小】
-- 【（n-1）*pageSize:起始值】
-- 【n：当前页】
-- 【数据总数/当前页==总页数】

语法：==limit（查询其实下标，pageSize）==

-- 查询JAVA第一学年课程成绩排名前十的学生，并且分数大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo`=r.`SubjectNo`
WHERE `SubjectName`='JAVA第一学年' AND `StudentResult`>=80
ORDER BY `StudentResult` DESC
LIMIT 0,10

4.6、子查询

where（这个值是计算出的）

本质：在where语句中嵌套一个子查询语句

where（select*from）

-- 分数不小于80分的学生的姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.`StudentNo`=s.`StudentNo`
WHERE `StudentResult`>=80 

-- 在这个基础上增添一个科目 高等数学-2
-- 查询高等数学-2 的编号
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.`StudentNo`=s.`StudentNo`
WHERE `StudentResult`>=80 AND `SubjectNo`=(
	SELECT `SubjectNo`FROM `subject`
	WHERE `SubjectName`='高等数学-2'
	)
	
-- 查询高等数学-2 分数不少于80的同学的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.`StudentNo`=s.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `StudentResult`>=80 AND `SubjectName`='高等数学-2'

-- 再改造
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`-- 通过什么分组
HAVING AVG(`StudentResult`)>80

5、MySQL函数

5.1、常用函数

-- 常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4)-- 向上取整
SELECT FLOOR(9.4)-- 向下取整
SELECT RAND()-- 返回一个随机数
SELECT SIGN(-10) -- 判断一个数的符号0-0负数返回-1，正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('啊啊啊啊啊啊')
SELECT INSERT('啊啊啊啊啊啊啊'1,2'等等')
SELECT LOWER('gao')
SELECT UPPER('Gao')
SELECT INSTR('gao','a')

-- 时间日期
SELECT CURRENT_DATE()-- 获取当前时间
SELECT CURDATE()
SELECT NOW()
SELECT LOCALTIME()
SELECT SYSDATE()

SELECT YEAR(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.2、聚合函数

函数名	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

5.3、数据库级别的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,'zs','111111'),(2,'ls','222222'),(3,'ww','333333')
-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1 

-- 插入时加密
INSERT INTO `testmd5` VALUES(4,'gx',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='gx' AND pwd=MD5('123456')

6、事务

6.1、什么是事务

要么都成功，要么都失败

原则：ACID原则：原子性，一致性，隔离性，永久性（百度）

参考连接 ACID原则

-- 事务
-- mysql 是默认开启事务自动提交
SET autocommit=0 -- 关闭 
SET autocommit=1 -- 开启（默认）

-- 手动处理事务
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

INSERT xx
INSERT xx

-- 提交：持久化
COMMIT
-- 回滚：回到原来样子
ROLLBACK

-- 事务结束
SET autocommit=1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

模拟场景

-- 模拟转账：事务
SET autocommit=0
START TRANSACTION -- 开启一个事务(一组事务)

UPDATE account SET money=money-500 WHERE `name`='A' -- 减500
UPDATE account SET money=money+500 WHERE `name`='B' -- 加500

COMMIT; -- 提交事务
ROLLBACK; -- 回滚

SET autocommit=1

7、索引

7.1、索引分类

主键索引 (PRIMARY KEY)
- 唯一标识，主键不可重复，只能有一个列作为主键
唯一索引 (UNIQUE KEY)
- 避免重复的列出现，唯一索引可以重复，多个列可以标识唯一索引
常规索引 (KEY/INDEX)
- 默认的，index，key关键字来设置
全文索引 (FULLTEXT)
- 在特定数据库引擎下才有，MyISAM
- 快速定位数据

-- 索引使用
-- 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、测试索引

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),'11111111',CONCAT('18',FLOOR(RAND()*((9999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;

END;

SELECT mock_data()
/*
insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
values(concat('用户1'),'11111111',
concat('18',floor(rand()*((9999999999-100000000)+100000000))),
FLOOR(RAND()*2),uuid(),floor(rand()*100))
*/
SELECT * FROM app_user WHERE `name`='用户9999';
SELECT * FROM app_user WHERE `name`='用户9999';
SELECT * FROM app_user WHERE `name`='用户9999';

EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';

-- 创建索引
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name`='用户9999';
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';

7.3、索引原则

索引不是越多越好
不要对进程变动数据加索引
小数据量不要加索引
索引一般加在常用来查询的字段上

8、权限管理与备份

8.1、用户管理

SQL yog 可视化管理

SQL命令操作

用户表：mysql.user

本质：读这张表进行增删改查

-- 创建用户
CREATE USER gaoxing IDENTIFIED BY '123456'

-- 修改密码（当前用户）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（指定用户）
SET PASSWORD FOR gaoxing = PASSWORD('123456')

-- 重命名
RENAME USER gaoxing TO gaoxing2


-- 用户授权ALL PRIVILEGES 全部的授权
-- ALL PRIVILEGES除了给别人授权，其他的都可以干
GRANT ALL PRIVILEGES ON *.* TO gaoxing2

-- 查询权限
SHOW GRANTS FOR gaoxing2
SHOW GRANTS FOR root@localhost

-- 撤销权限REVOKE
REVOKE ALL PRIVILEGES ON *.* FROM gaoxing2

-- 删除用户
DROP USER gaoxing

8.2、MySQL备份

MySQL数据库备份方式

直接拷贝物理文件
在Sqlyog这种可视化工具中手动导出
- 在想要导出的表或者库中，右键，选择备份或导出
使用命令行导出mysqldump命令行使用

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql

# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql

mysql -u用户名 -p密码 库名< 备份文件

假设你要备份文件库，防止数据丢失

把数据库给朋友，sql文件给别人即可

9、规范数据库设计

9.1、为什么需要设计

当数据库比较复杂的时候，我们就需要设计

糟糕的数据库设计

数据冗余，浪费时间
数据库插入和删除都会麻烦，异常
程序性能差

良好的数据库设计

节约内存空间
保证数据库的完整性
方便我们开发系统

软件开发中，关于数据库设计

分析需求：分析业务和需要处理的数据库的需求
概要分析：设计关系图E-R图

设计数据库的步骤：

收集信息，分析需求
- 用户表（用户登录注销，用户个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者一些主字段） key ：value
标识实体（把需求落地到每个字段）
标识实体之间的关系
- 写博客：user ->blog
- 创建分类：user ->category
- 关注：user ->user

9.2、三大范式

为什么需要数据规范化

信息重复
更新异常
插入异常
- 无法正常显示信息
删除异常
- 丢失有效信息

三大范式

第一范式（1NF）

原子性：保证每一列不可分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事

第一三式（3NF）

前提：满足第二范式

确保数据表中的每一列数据都和主键直接相关，而不能间接相关

规范性和性能关系

关联查询的表不能超过三张表

考虑商业化的需求和目标，（成本，用户体验）数据库的性能更重要
在规范性能的问题的时候，需要适当考虑一下规范性
故意给某些表增加一些冗余的字段，
故意增加一些计算列（从大数据将为小数据的查询：索引）

10、JDBC

10.1、数据库驱动

驱动：声卡，显卡，数据库

10.2、JDBC

sun公司为了简化操作，提供一个规范，号称JDBC

java.sql

javax.sql

10.3、第一个JDBC程序

创建测试数据库

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','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')

1、新建一个项目

2、导入驱动

3，编写测试代码

package com.gx.lesson01;

import java.sql.*;

//第一个JDBC程序
public class JdbcFirstDome {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法

        //2.用户信息和
        // urluseUnicode=true&characterEncoding=utf8&useSSL=true
        String ur1="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="123456";

        //3.连接成功，数据库对象  Connection  代表数据库
        Connection connection=DriverManager.getConnection(ur1,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.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("=========================");
        }


        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤：

1、加载驱动

2、连接数据库 DriverManager

3、获取执行sql的对象 Statement

4、获取返回结果

5、释放连接

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法
Connection connection=DriverManager.getConnection(ur1,username,password);

//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

 String ur1="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql -- 3306
//协议://主机地址：端口号/数据库名？参数1&参数2&参数3

//oralce -- 1521
//jdbc：oralce：thin：@localhost：1521：sid

Statement 执行sql的对象 PrepareStatement 执行sql的对象

String sql="SELECT * FROM users";	//编写sql

statement.executeQuery();   //查询操作返回ResultSet
statement.execute();    //执行任何sql
statement.executeUpdate();  //更新 插入 删除 都是用这个 返回一个受影响的行数

ResultSet 查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultSet.getObject();  //在不知道列类型的情况下使用
//如果知道列的类型就是用指定类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
......

遍历，指针

resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到下一行
resultSet.absolute();//移动到指定行

释放资源

//6.释放连接
resultSet.close();
statement.close();
connection.close();

10.4、statement对象

代码实现

1、提取工具类

//db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

package com.gx.lesson02.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {

    private static String driver=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);

            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();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try{
                rs.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(st!=null){
            try{
                st.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try{
                conn.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

2、编写增删改的方法executeUpdate

package com.gx.lesson02;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    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(4,'gx','123456','11111','2020-01-01')";

            int i=st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);

        }
    }
}

package com.gx.lesson02;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    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="DELETE FROM users WHERE id=4";

            int i=st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);

        }
    }
}

package com.gx.lesson02;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    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="UPDATE users SET `NAME`='gao',`email`='111111' WHERE id=1";

            int i=st.executeUpdate(sql);
            if(i>0){
                System.out.println("改变成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);

        }
    }
}

3、查询executeQuery

package com.gx.lesson02;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {
        Connection conn =null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn= JdbcUtils.getConnection();
            st=conn.createStatement();

            String sql="select * from users where id=1";
            rs=st.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}

SQL注入的问题

sql存在漏洞，会被攻击导致数据泄露，==SQL会被拼接==

package com.gx.lesson02;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLzhuru {
    public static void main(String[] args) {
        //正常登录
        login("gao","123456");
        //异常登录
        //login("'or '1=1","'or '1=1");
    }

    //登录业务
    public static void login(String username,String password){

        Connection conn =null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn= JdbcUtils.getConnection();
            st=conn.createStatement();

            //SELECT *FROM users WHERE `NAME`='gao' AND `PASSWORD`='123456'
            String sql="select * from users where NAME='"+username+"'AND password ='"+password+"'";
            rs=st.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.5、PreparedStatement对象

PreparedStatement防止SQL注入，效率更高

1、新增

package com.gx.leason03;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

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，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,4);//id
            st.setString(2,"gx");
            st.setString(3,"123456");
            st.setString(4,"111111");
            //注意点：sql.Date    数据库   java.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 e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

2、删除

package com.gx.leason03;

import com.gx.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，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,4);//id

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

3、查询

package com.gx.leason03;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    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=?";//编写sql

            st=conn.prepareStatement(sql);//预编译

            st.setInt(1,1);//传递参数

            rs=st.executeQuery();//执行

            if(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

4、更新

package com.gx.leason03;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

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，先写sql，然后不执行

            //手动给参数赋值
            st.setString(1,"gx");
            st.setInt(2,1);

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

5、防止SQL注入

package com.gx.leason03;

import com.gx.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQLzhuru {
    public static void main(String[] args) {
        //正常登录
        //login("lisi","123456");
        //异常登录
        login("'or '1=1","'or '1=1");
    }

    //登录业务
    public static void login(String username,String password){

        Connection conn =null;
        PreparedStatement st=null;
        ResultSet rs=null;
        try {
            conn= JdbcUtils.getConnection();
            //PreparedStatement 防止SQL注入的本质，把传递的参数当作字符
            //假设其中存在转义字符，比如说‘会被直接转义
            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"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.6、使用IDEA连接数据库

社区版不好用

10.7、事务

要么成功，要么失败

ACID 原则

原子性：要么全部成功，要么全部失败

一致性：总数不变

隔离性：多个进程互补干扰

持久性：一旦提交不可逆，持久化到数据库

隔离性的问题

脏读：一个事务读取另一个没有提交的事务

不可重复读：在一个事务内，重复读取表中的数据，表数据发生了改变

幻读（虚读）：在一个事务内，读取到别人插入的数据，导致前后读出的结果不一致

package com.gx.lesson04;

import com.gx.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();

            String sql2="update account set money=money+100 where name='B'";
            st= conn.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕提交事务
            conn.commit();
            System.out.println("成功");

        }catch (SQLException e){
            //如果失败就会回滚
            try {
                conn.rollback();
            }catch (SQLException e1){
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}

1、开启事务conn.setAutoCommit(false);

2、一组业务执行完毕，提交事务

3、可以在catch语句中显示的定义回滚，但默认失败就会回滚

10.8、数据库连接池

数据库连接—-执行完毕—-释放

连接—-释放十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接预备好的

最小连接数：10

最大连接数：15

等待超时：100ms

编写连接池，实现一个接口 DataSource

开源数据源实现

使用这些数据库连接池之后，在项目开发中就不需要编写连接数据库的代码了

DBCP

需要jar包