MySQL_学习
MySQL
1. 初识数据库
JavaEE：企业级Java开发 Web
前端（页面，展示，数据）
后台（连接点：连接数据库JDBC，连接前端（控制，视图转跳，给前端传递数据））
数据库（Txt，Excel，Word）
操作系统,数据结构,算法
离散数学,数字电路,体系结构,编译原理 + 实战
数据库是所有软件体系中最核心的存在 DBA
DB(DataBase数据库)
1.1 数据库分类
关系型数据库 (SQL)
-
MySQL , Oracle , Sql Server , DB2 , SQLlite
-
通过表与表,行与行之间的关系进行数据存储
非关系型数据库 (NoSQL -- Not Only SQL)
-
Redis , MongDB
-
对象存储,通过对象的自身属性决定
DBMS(数据库管理系统)
MySQL:
-
MySQL是最好的RDBMS(Relational Database Management System,关系数据库管理系统)应用软件之一。
-
开源
-
体积小,速度快,总体拥有成本低,招人成本低
-
中小型网站可以做集群
-
稳定版 5.7
安装:略
数据化可视化:SQLyog
1.2 数据库操作
CRUD 增删改查 ( CV程序员 API程序员 CRUD程序员 )
-
DDL 数据库定义语言
-
DML 数据库操作语言
-
DQL 数据库查询语言
-
DCL 数据库控制语言
CREATE DATABASE [IF NOT EXISTS] 数据库名; --创建数据库[如果不存在,则创建] DROP... --删除数据库 USE 数据库名; --使用数据库 SELECT... - show DATABASE; --查看数据库
1.3 数据类型
数值
-
tinyint 十分小的数据 1个字节
-
smallint 较小的数据 2个字节
-
mediumint 中等大小的数据 3个字节
-
int 标准的整数 4个字节 (常用int)
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节 (精度问题!)
-
decimal 字符串形式的浮点数 (金融计算的时候，一般是使用decimal)
字符串
-
char 字符串固定大小的 0~255
-
varchar 可变字符串 0~65535 (常用String)
-
tipytext 微型文本 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.1.1 至今 毫秒数
-
year 年份
null
-
不要使用null运算,结果为null. 没有值,未知
1.4 字段属性
Unsigned:
-
无符号的整数
-
声明则该列不能声明为负数
zerofill:
-
0填充
-
不足的位数,使用0自动填充
自增:
-
通常设计唯一主键 index 必须是整数类型
-
可自定义设计主键的起始值和步长
非空
默认
-
扩展
每一个表，都必须存在以下五个字段 !
未来做项目用的，表示一个记录存在意义!
id 主键
'version' 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
1.5 常用命令
CREATE TABLE IF NOT EXISTS `stu`( `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 COMMENT'年级', `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱', PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 --建表 ALTER TABLE stu RENAME AS stu1 --修改表名 ALTER TABLE stu1 ADD age INT(3) --新增字段 ALTER TABLE stu1 MODIFY age vARCEAR(11) --修改约束和字段类型(不能重命名) ALTER TABLE stu1 CHANGE age age1 INT(1) --字段重命名(...) ALTER TABLE stu1 DROP age1 --删除字段名 DROP TABLE IF EXISTS stu --删除表 --`所有的创建和删除操作尽量加上判断IF EXISTS，以免报错` ALTER TABLE stu1 ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`) --添加外键,CONSTRAINT约束名,FOREIGN KEY作为外键的列,REFERENCES引用那个表那一列 --物理外键,数据库级别,不建议使用(避免数据库过多) DESC stu --显示表的结构 SHOW CREATE TABLE stu --查看stu表的定义语句 SHOW CREATE DATABASE stu st --查看创建数据库的定义语句 /*数据库引擎 INNODB 默认使用 支持:事务支持,数据行锁定,外键约束 - 不支持:全文索引 安全性高,事务处理,多表多用户操作 MYISAM 早些使用 支持:全文索引 - 不支持:事务支持,数据行锁定,外键约束 节约空间,速度较快 */
2. MySQL数据管理
DML语言
DML语言:数据操作语言
-- 增添数据 insert into 表名([字段名1,字段名2,字段名3,...]) values ('值1'),('值2'),('值3',...) -- 修改数据 update 表名 set 字段名1=值1,[字段名2=值2,...] where [判断条件] -- 删除数据 delete from 表名 where [判断条件] -- 完全清空数据库表 truncate 表名
delete与truncate区别 :
-
相同 : 都可以删除数据,都不会删除表结构
-
不同 :
-
truncate重新设置自增列计数器归零 , 不会影响事务
-
delete : 重启数据库 -- 在InnoDB下 自增列从1开始(存储内存) ; 在MyISAM下继续从上一个自增量开始
-
3. DQL查询数据 ***
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}]; -- 指定查询的记录从哪条至哪条 -- []代表可选,{}代表必选 -- 有顺序要求
-
数据库最核心的语言,最重要的语句
-- 查询SELECT - 别名AS SELECT 字段名1 AS 别名1,[字段名2 别名2,...] FROM 表名 [AS 表的别名] -- 函数CONCAT(a,b) SELECT CONCAT('拼接的字符串:',字段名) AS 别名 FROM 表名 -- 去重DISTINCT SELECT DISTINCT 字段名 FROM 表名 -- 查询系统版本(函数) SELECT VERSION() -- 用来计算(表达式) SELECT 100*3-1 AS 计算结果 -- 查询自增的步长(变量) SELECT @@auto_increment_increment
3.1 where
-
AND && 与
-
OR || 或
-
NOT != 非
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如操作符结果为NULL,结果为真 |
| IS NOT NULL | a is not null | 如操作符结果为NOT 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
-- 查询姓刘的学生,且刘字后面只有一个字( _ 指代一个字符 '刘__ 代表后面三个字') SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '刘_' -- 查询名字中间是,且刘字后面只有一个字( % 指代一个字符) SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '%刘%'
IN (具体的一个值或多个值 , 不能使用 % _ )
-- 查询住在北京或上海的人 SELECT 字段名 FROM 表名 WHERE 字段名 IN('北京','上海');
3.2 联表查询
/*思路 1.分析需求,分析查询的字段来自哪些表 (连接查询) 2.确定使用哪种连接查询? (7种) 确定交叉点(这两个表中哪个数据是相同的) 判断的条件:学生表的中 studentNo = 成绩表 studentNo */ -- JOIN(连接的表) ON(判断条件) 连接查询 -- WHERE 等值查询 -- INNER JOIN SELECT s.studentNO,studentName,SubjectNo,StudentResult FROM student As s INNER JOIN result As r WHERE s.studentNO = r.studentNO; -- RIGHT JOIN --缺考(右表无数据的学生)不能查出,因为右查询不包含左表内容 SELECT s.studentNO,studentName,SubjectNo,StudentResult FROM student s -- AS可以省略 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 WHERE StudentResult IS NULL;
巩固 :
-- 查询学员所属的年级(学号，学生的姓名，年级名称) SELECT studentNo,studentName,GradeName FROM student s INNER JOIN grade g ON s.GradeID = g.GradeID -- 查询科目所属的年级（科目名称,年级名称) SELECT SubjectName,GradeName FROM subject sub INNER JOIN grade g ON sub.GradeID = g.GradeID -- 查询了参加"数据库结构-1"考试的同学信息:学号，学生姓名，科日名，分数) SALECT 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'
/*三表查询 : 学号 学生姓名 科目名 分数 三表 : student result subject */ SELECT s.studentNO,studentName,SubjectName,StudentResult FROM student s RIGHT JOIN result r ON s.studentNO = r.studentNO INNER JOIN subject sub ON r.SubjectNo = sub.SubjectNo
| 操作 | 描述 |
|---|---|
| INNER JOIN | 如果表中至少有一个匹配,就返回 |
| RIGHT JOIN | 会从左表返回所有的值,即使右表中没有匹配 |
| LEFT JOIN | 会从右表返回所有的值,即使左表中没有匹配 |
3.3 自连接(了解)
自己的表和自己的表连接，核心 : 一张表拆为两张一样的表即可
categoryid - 当前层级 pid - 父类层级 categoryName - 名字
-
父类表 :
| 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,categoryid AS b WHERE a.categoryid = b.pid
3.4 分页和排序
排序 : order by
-- 排序:升序ASC,降序DESC -- ORDER BY 通过哪个字段排序,怎么排 -- 查询的结果根据成绩升序排序 ORDER BY StudentResult ASC
分页 : limit
-- 缓解数据库压力,给人更好的体验(瀑布流) -- 语法: LIMIT 起始值,页面大小 -- LIMIT 0,5 1~5 第一页 -- LIMIT 1,5 2~6 -- LIMIT 5,5 6~10 第二页 -- LIMIT x,5 第N页 (n-1)*pageSize,pageSize -- 【pageSize:页面大小】 -- 【(n-1)*pageSize:起始值】 -- 【n:当前页】 LIMIT 1,5 -- 测试题: -- 查询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
3.5 子查询
在where语句中嵌套一个子查询语句
-- 1、查询"数据库结构-1"的所有考试结果(学号，科目编号，成绩)，降序排列 -- 方式一:使用连接查询 SELECT `tudentNo`,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 StudentNo = ( SELECT SubjectNo FROM `subject` WHERE SubjectName = '数据库结构-1' ) ORDER BY studentResult DESC -- 分数不小于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 SubjectNo FROM `subject` WHERE `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. MySQL函数
官网参考手册 : https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
4.1 常用函数
-- 数字运算 SELECT ABS(-8) -- 绝对值 SELECT CEILING(9.4) -- 向上取整 SELECT FLOOR(9.4) -- 向下取整 SELECT RAND() -- 返回一个0~1随机数 SELECT SIGN(10) -- 判断一个数的符号 - 负数返回-1,正数返回1,0返回0 -- 字符串函数 SELECT CHAR_LENGTH('xxxx') -- 长度 SELECT CONCAT('x','cc','zzz') -- 拼接 SELECT INSERT('xzcvbn',1,2,'qqq') -- 查询,从某个位置开始体换某个长度 SELECT LOWER('x') -- 小写字母 SELECT UPPER('x') -- 大写字母 SELECT INSTR('qwert','w') -- 返回第一次出现的子串的索引 SELECT REPLACE('qwertyuiop','wer','zzz') -- 替换出现的指定字符串 SELECT SUBSTR('qwer',2,3)-- 返回子字符串(源字符串,截取的位置,截取的长度) SELECT REVERSE('zx') -- 反转 -- 时间和日期函数 ** SELECT CURRENT_DATE() -- 获取当前日期 SELECT CURDATE() -- 获取当前日期 SELECT NOW() -- 获取当前的时间 ** SELECT LOCALTIME() -- 本地时间 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 ()
4.2 聚合函数 ***
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
-- COUNT SELECT COUNT (`BornDate`) FROM student; -- Count(字段),会忽略所有的null值 SELECT COUNT (*) FROM student; -- count (*),不会忽略null值,计算行数 SELECT COUNT (1) FROM result; -- Count (1）,不会忽略所有null值,本质计算行数 /* 分组过滤 - 案例 */ -- 查询不司课程的平均分,最高分,最低分,平均分大于80 -- 核心: (根据不同的课程分组) SELECT SubjectName,AVG(studentResult) AS 平均分, MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分 FROM result r INNER JOIN `subject` sub ON r.`subjectNo` = sub.`SubjectNo` GROUP BY r.subjectNo -- 通过什么宁段来分组 HAVING 平均分>80
4.3 MD5加密(扩展)
MD5 不可逆
-- 明文密码 INSERT INTO testmd5 VALUES (1,'zhangsan','123456') -- 加密 UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1 UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码 -- 插入的时候加密 INSERT INTO testmd5 VALUES (2,'wangwu',MD5('123456')) -- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值 SELECT * FROM testmd5 WHERE `name`=`zhangsan` AND pwd=MD5('123456')
5. 事务
事务原则 : ACID原则
-
原子性(Atomicity)
-
要么都成功,要么都失败
-
-
一致性(Consistency)
-
事务前后数据完整性保持一致
-
-
隔离性(Isolation)
-
针对多个用户同时操作，主要是排除其他事务对本次事务的影响
-
-
持久性(Transaction) - 事务提交
-
表示事务结束后的数据不随着外界原因导致数据丢失
-
出现意外 : 事务未提交则恢复原状 , 事务以提交则持久化到数据库 , 事务一旦提交不可逆
-
-
事务的隔离级别
-
脏读:指一个事务读取了另外一个事务未提交的数据
-
不可重复读:—个事务内读取表中的某一行数拒多次读取结果不同(这个不一定是错误)
-
虚读(幻读):指在一个事务内读到了别的事务插入的数据,导致前后读取不一致(一般是行影响,多一行)
-
-- mysql是认开启事务自动提交的 SET autocommit = 0 /*关闭*/ SET autocommit = 1 /*开启(默认的)*/ -- 手动处理事务 SET autocommit = 0 -- 关闭自动条件 -- 事务开启 START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内 INSERT XX INSERT XX -- 提交:持久化(成功!) COMMIT -- 回滚:回到的原来的样子(失败!) ROLLBACK -- 事务结束 SET autocommit = l -- 开启自动提交I -- (了解) 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(30) 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.00) -- 模拟转账:事务 SET autocommit = 0; -- 关闭自动提交 START TRANSACTION -- 开启一个事务 UPDATE account SET money=money-500 WHERE `name`= 'A' -- A减500 UPDATE account SET money=money+500 WHERE `name`= 'B' -- B加500 COMMIT; -- 提交事务,持久化 ROLLBACK; -- 回滚 SET autocommit = l; -- 恢复默认值
6. 索引
MySQL官方对索引的定义为 :
索引(Index)是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质:索引是数据结构。
6.1分类 :
在一个表中，主键索引只能有一个，唯一索引可以有多个
-
主键索引(PRIMARY KEY)
-
唯一标识,不可重复,只有一列
-
-
唯一索引(UNIQUE KEY)
-
避免重复的列,唯一索引可以重复,
-
-
常规索引(KEY/INDEX)
-
默认的 , index key关键字设置
-
-
全文索引(FullText)
-
在特定的引擎下有, MyISAM , 快速定位数据
-
6.2使用 :
-- 最示所有的索引信息 SHOW INDEX FROM student -- 增加一个全文索引(索引名)列名 ALTER TABLE 库名.student ADD FULLTEXT INDEX `studentName` (`studentName`); -- EXPLAIN 分析sql执行的状况 EXPLAIN SELECT * FROM student; -- 非全文索引 EXPLAIN SELECT * FROw student WHERE MATCH(studentName) AGAINST('刘');
6.3测试索引 :
-- 插入100万数据 DELIMITER $$ -- 写函数之前必须要写，标志 CREATE FUNCTION mock_data () RETURN 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(); -- id _ 表名 _ 字段名 -- CREATE INDEX 索引名 on 表(字段) CREATE INDEX id_app_user_name ON app_user(`name`);
索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显~
6.4索引原则
MySQL索引背后的数据结构及算法原理
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上!
7. 权限管理和备份
权限管理
SQL命令
用户表: mysql.user
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码' -- 修改密码(修改当前用户密码) SET PASSWORD = PASSWORD('123456') -- 修改密码(修改指定用户密码) SET PASSWORD FOR 用户名 = PASSWORD('111111') -- 重命名 RENAME USER 旧名字 TO 新名字 -- 用户授权 ALL PRIVILEGES全部权限,除了给别人授权 GRANT ALL PRIVILEGES ON *.* TO 用户名 -- 查询权限 SHOW GRANTS FOR 用户名 -- 查看指定用户的权限 SHOW GRANTS FOR root@localhost -- root用户权限 -- 撤销权限 REVOKE (什么权限,那个库,给谁撤销) REVOKE ALL PRIVILEGES ON *.* FROM 用户名 --删除用户 DROP USER 用户名
备份
-
命令行
-
导入 mysqldump 导出 source
-
-
SQLyog
-
拷贝物理文件
8. 规范数据库设计
糟糕的数据库设计:
-
数据冗余,浪费空间
-
数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
-
程序的性能差
良好的数据库设计:
-
节省内存空间
-
保证数据库的完整性
-
方便我们开发系统
开发中,数据库设计:
-
分析需求 : 分析业务和需要处理的数据库的需求
-
概要设计 : 设计关系图E-R图
三大范式
-
第一范式(1NF):要求数据库表的每一列都是不可分割的原子数据项。
-
原子性 : 每一列不可再分
-
-
第二范式(2NF):在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
-
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言)。
-
即每张表只描述一件事情
-
-
第三范式(3NF):在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
-
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
-
规范性和性能的问题
-
关联查询的表不得超过三张表I
-
考虑商业化的需求和目标，(成本，用户体验! ）数据库的性能更加重要
-
在规范性能的问题的时候，需要适当的考虑一下规范性!
-
故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
-
故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
-
9. JDBC ***
SUN公司为了简化开发人员的(对数据库的统一）操作,提供了一个(Java操作数据库)规范,俗称JDBC
public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动 //2.用户信息和url String url = "jdbc:mysql://localhost:3306/库名?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username = "root"; String password = "root"; //3.连接数据库 DriverManager (Connection 代表数据库) Connection conn = DriverManager.getConnection(url, username, password); //4.执行SQL的对象Statement执行sql的对象 Statement statement = conn.createStatement(); //5．执行SQL的对象去执行 执行SQL,可能存在结果,查看返回结果 String sql = "SELECT * FROM 表名"; 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")); } //6.释放连接 resultSet.close(); statement.close(); connection. close(); }
Statement 执行SQL对象
String sql = "SELECT * FROM users"; //编写SQL statement.executeQuery();//查询操作返回Resu1tSet statement.execute(); //执行任何SQL statement.executeUpdate();//更新、插入、删除。都是用这个，返回一个受影响的行数
ResultSet 查询的结果集 封装了所有查询结果
resultSet.getObject(); //如果知道列类型则换成相应类型 /***********遍历***********/ resultset.beforeFirst(); //移动到最前面 resultset.afterLast(); //移动到最后面 resultset.next(); //移动到下一个数据 *** resultset.previous(); //移动到前一行 resultset.absolute(row); //移动到指定行
9.1 statement
-
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
-
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化)。
-
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
增删改使用 executeUpdate 查一般使用 executeQuery
9.2 SQL注入
SQL注入即是指web应用程序对用户输入数据的合法性没有判斯或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非投权的任意查询，从而进一步得到相应的数据信息。即漏洞
//判断:如果账号存在且密码正确则登路 SELECT * FROM users WHERE `NAME`='"+username+"' AND `password`='"+password+"'"; //调用函数使用此语句时Login(String username,String passward) Login(" 'or' 1=1"," 'or' 1=1"); //登路成功 //导致SELECT语句变化,可能会导致数据泄露 SELECT * FROM users WHERE `Name`=''or'1=1' AND `password`=''or'1=1';
9.3 PreparedStatement
可以防止SQL注入,且效率更好
Connection conn = JdbcUtils.getConnection(); //区别 //使用 ? 占位符代替参数 String sql = "insert into users(id,`NAME`,`PASSMORD`,`DATE`) values(?,?,?)"; //预编译SQL,先写sql,然后不执行 PrepareStatement st = conn.prepareStatement(sql); //手动给参数赋值 st.setInt(1,4); //第几个参数,参数值 st.setString(2,"NAME"); st.setString(3,"PASSMORD"); //sql.Date数据库使用 ; util.Date JAVA使用 -- new Date().getTime()获得时间戳 st.setDate(4,new java.sql.Date(new java.util.Date().getTime())); //执行 st.executeUpdate(); //PreparedStatement防止注入的本质,将闯进来的参数全当作字符,转义字符将被转义 //改进登录 conn =JdbcUt1ls.getConnection(); String sql = "SELECT * FROM users WHERE `NAME`=? AND `password`=?"; //编写SQL st = conn. preparestatement(sql); //预编译 st.SetString(1,username); //传递参数 st.SetString(2,password); rs = st.executeQuery; //执行
9.4 事务
ACID
原子性:要么全部完成，要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰 (问题 : 脏读 , 不可重复读 , 幻读)
持久性:一旦提交不可逆，持久化到数据库
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.commot(); //conn.rollback(); --如果失败则回滚事务.判断 JdbcUtils.release(conn,st,rs);
9.5 数据库连接池
-
数据库连接 -- 执行完毕 -- 释放
-
连接 -- 释放十分浪费系统资源
池化技术 : 准备一些预先的资源，过来就连接预先准备好的
连接池:
最小连接数 : 10
最大连接数 : 100 业务最高承载上限
等待超时 : 100ms
编写连接池，实现一个接口DataSource
-
开源数据源实现
-
DBCP
-
C3P0
-
Druid : 阿里
-
-
使用了数据库连接池后,项目开发中就不需要编写链接数据库代码
编写连接池，实现一个接口DataSource
DBCP
需要用到的jar包:
commons-dbcp-1.4 commons-pool-1.6
//配置 文件名:dbcpconfig.properties #连接设置 driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/库名?useUnicode=true&characterEncoding=utf8&useSSL=true username=root password=123456 #<!-- 初始化连接 --> initialSize=10 #最大连接数量 maxActive=50 #<!-- 最大空闲连接 --> maxIdle=20 #<!-- 最小空闲连接 --> minIdle=5 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】 #注意：user 与 password 两个属性会被明确地传递，因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由连接池所创建的连接的自动提交（auto-commit）状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的只读（read-only）状态。 #如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix） defaultReadOnly= #driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。 #可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED private static DataSource dataSource = null; static { try { InputStream in = JdbcUtils_DBCP.class.getClassLoader( ).getResourceAsStream("dbcpconfig.properties"); Properties properties = new Properties(); properties.load(in); //创建数据源 工厂模式-->创建 dataSource = BasicDataSourceFactory.createDataSource(properties); }catch(Exception e){ e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return dataSource.getConnection();//从数据源获取连接 }
C3P0
需要用到的jar包:
commons-dbcp-1.4 commons-pool-1.6
配置 文件名:c3p0-config.xml
浙公网安备 33010602011771号