MySql学习笔记

MySQL

1. 初始数据库

1.1什么是数据库

A database is an organized collection of data, generally stored and accessed electronically from a computer system.

1.2 数据库分类

关系型：SQL

  • MySQL，Oracle，SQL Server，DB2

  • 通过行与列之间，表与表之间的关系进行数据储存。

非关系型：NoSQL(Not Only)

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

DBMS：数据库管理系统

  • 数据库管理软件，科学有效的管理我们的数据

1.3 MySQL简介

  • MySQL是目前实际上最好的关系型数据库管理软件(RDBMS)之一

  • 体积小，速度块，使用成本低，适合中小型网站、大型网站、集群的使用

    网站：https://www.mysql.com/

1.4 MySQL安装

  1. 解压安装包

  2. 配置环境变量 Path = xxx/bin

  3. 解压目录下新建配置文件my.ini

  4. 编辑配置文件my.ini

    [mysqld]
basedir = F:\Environment\mysql-5.7.34-winx64\
datadir = F:\Environment\mysql-5.7.34-winx64\data\
port = 3306 # 选择其他端口后，进入管理界面时需要在命令后加上端口号 -P port_number
skip-grant-tables

  5. 管理员模式运行控制台，运行命令mysqld -install

  6. 输入命令mysqld --initialize-insecure --user=mysql初始换数据文件

  7. 再次启动mysql使用命令net start mysql，使用命令mysql -u root -p进入mysql管理界面

  8. 执行命令update mysql.user set authentication_string=password('123456') where user = 'root' and host = 'localhost';，再输入flush privileges;刷新权限（注意，sql语句结尾必须加分号）

  9. 编辑配置文件my.ini ，删除skip-grant-tables

  10. 重启服务,测试是否连接成功。

1.4 安装SQLyog

  1. 默认安装即可

  2. 输入设置的数据库参数

  3. 新建数据库

  4. 建表

  5. 填数据

  6. 查看表

    ........

1.7 连接数据库

  1. 命令行连接

    mysql -u user -p[password] -p host -P port

  2. 数据库操作

    update mysql.user set authentication_string=password('123456') where user = 'root' and host = 'localhost'; --修改密码
flush privileges; -- 刷新权限

use school;  -- 切换数据库 use databaseName
show tables; -- 查看数据库中所有表
describe student; -- 查看数据库所有表信息

create database BaseName; -- 创建数据库
exit; -- 退出数据库
-- 单行注释
/*
多行注释
*/

  3. 数据库语言

    DDL 定义

    DML 操作

    DQL 查询

    DCL 控制

2. 操作数据库

操作数据库>操作表>操作表中内容

2.1 操作数据库

mysql不区分大小写

  1. 创建数据库

    CREATE DATABASE [IF NOT EXISTS] mydatabase;

  2. 删除数据库

    DROP DATABASE [IF EXISTS] mydatabase;

  3. 使用数据库

    USE `school`; -- 如果表名或字段名是特殊字符使用``包裹

  4. 查看数据库

    SHOW DATABASES; -- 查看所有数据库

2.2 数据库列的类型

数值

  • 整数

    • tinyint 1byte
    • samllint 2byte
    • mediumint 3byte
    • int 4byte
    • bigint 8byte

  • 浮点数

    • float 4byte

    • double 8byte

  • 字符串浮点数

    • decimal 为了解决大数的精度问题，使用字符串浮点数，一般用于金融计算

字符串

  • char 字符串固定大小 0~255

  • varchar 可变字符串 0~65535

  • tinytext 微型文本 2^8 -1

  • text 文本串 2^16 -1

时间日期

  • date YYYY-MM--DD
  • time HH:mm:ss
  • datatime YYYY-MM-DD HH:mm:ss
  • timestamp 时间戳
  • year 年

null

没有值，一般不用于计算

2.3数据库字段属性*

  • unsigned

    • 无符号整数
    • 声明了改行不能为负数

  • zerofill

    • 0填充
    • 不足的位数，使用0来填充

  • 自增

    • 自定在上一条记录的基础上加一
    • 通常用来设计唯一的主键
    • 可自定义自增的初始值和步长

  • 非空 not null

    • 设置为not null必须设置值

    • null时，不填值默认为null

  • 默认值 default

    • 设置一列的默认值后，如果不指定实际值会使用默认值

2.4 创建表

CREATE TABLE IF NOT EXISTS `students`(
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'students id',
`name` VARCHAR(30) NOT NULL DEFAULT 'NULL' COMMENT 'name',
`pwd` VARCHAR(20) NOT NULL DEFAULT '12345678' COMMENT 'password',
`sex` VARCHAR(6) NOT NULL DEFAULT 'male' COMMENT 'gender',
`birthday` DATETIME COMMENT 'birthday',
`adddress` VARCHAR(100) DEFAULT NULL COMMENT 'address',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'email',
PRIMARY KEY (`id`)
 
)ENGINE INNODB DEFAULT CHARSET= utf8

格式

CREATE TABLE [IF NOT EXISTS] `tableName`(
`field` vartype(varlength) [attribute][index][comment],
`field` vartype(varlength) [attribute][index][comment],
    .........
`field` vartype(varlength) [attribute][index][comment],
    
 [keySet]   ,
    
)[tableType][charset][comment]

常用命令

SHOW CREATE DATABASE school; -- 查看创建数据库

SHOW CREATE TABLE students; -- 查看创建表

DESC students; -- 查看表的结构

2.5 数据表的类型

INNODB是现在默认使用的表

MYISAM早些年使用

MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
占用空间 较小 较大约为2倍
  • MYISAM 节约空间，速度快
  • INNODB 安全性好，支持事务处理，多表多用户操作

数据库磁盘存储位置

  • INNODB :data目录下的*.frm文件以及上级目录下的ibdata1文件
  • MYISAM：
    • *.frm -表结构定义问文件
    • .*MYD -索引文件
    • *.MYI - 索引问文件

设置编码字符集

Mysql默认字符集不支持中文

修改默认字符集

  • 方法1：创建表时修改

    CHARSET=utf8

  • 方法2，在my.ini配置文件上设置默认编码

    character-set-server=utf8

2.6 修改删除数据表

修改表

-- 修改表名 ALTER TABLE TableName RENAME AS NewName
ALTER TABLE student RENAME AS teacher;
-- 增加表的字段 ALTER TABLE TableName ADD FieldName Property
ALTER TABLE teacher ADD age INT(3);
-- 修改表字段

-- 修改字段的类型约束 : ALTER TABLE TableName MODIFY FieldName NewProperty;
-- 
ALTER TABLE teacher MODIFY age VARCHAR(3);

-- 修改字段名以及约束 : ALTER TABLE TableName CHANGE FieldName NewName Property;
ALTER TABLE teacher CHANGE vage v1age INT(3);

-- MODIFY和CHANGE用来改变column的属性，change和modify执行成功后都会这本次设置的属性替换column原属性

-- 删除字段:ALTER TABLE tb_name DROP FieldName;
ALTER TABLE teacher DROP  vlage;



-- 查看表中约束
SHOW KEYS FROM result;

-- 添加和删除主键约束
ALTER TABLE result ADD PRIMARY KEY (`studentno`,subjectno);
ALTER TABLE result DROP PRIMARY KEY;

-- 添加唯一约束
ALTER TABLE result UNIQUE KEY (`studentno`,subjectno);

-- 删除唯一索引(约束)
DROP INDEX studentno_5 ON result;
ALTER TABLE result DROP INDEX studentno_5;

删除表

-- DROP TABLE [IF EXISTS]  TableName;
DROP TABLE IF EXISTS  teacher;

MySQL 数据管理

3.1 外键

创建方式一：创建表时，创建约束

CREATE TABLE `students` (
  `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'students id',
  `name` VARCHAR(30) NOT NULL   COMMENT 'name',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '12345678' COMMENT 'password',
  `sex` VARCHAR(6) NOT NULL DEFAULT 'male' COMMENT 'gender',
  `birthday` DATETIME DEFAULT NULL COMMENT 'birthday',
  `adddress` VARCHAR(100) DEFAULT NULL COMMENT 'address',
  `email` VARCHAR(50) DEFAULT NULL COMMENT 'email',
  `score` INT(3)  NOT NULL COMMENT 'score',
  
  CONSTRAINT FK_id FOREIGN KEY (id) REFERENCES grade(id),  -- 创建外键约束
  -- 格式 CONSTRAINT 约束名 FOREIGN KEY (FKfield) REFERENCES RefTable(RefField)
    -- [on delete cascade] [on update cascade]
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

外键一定时另一个表的主键，删除主表前，必须要删除所有从表

创建方式二：创建表后再添加

ALTER TABLE students
ADD CONSTRAINT FK_id FOREIGN KEY (id) REFERENCES grade(id);
-- ALTER TABLE tableName
-- ADD CONSTRAINT fk_name FOREIGN KEY (Fk_Field) REFERENCES Ref_Table(Ref_Field);

以上创建方式是物理的外键，数据库级别外键，不建议使用，可以通过程序实现外键用于维护多张表的关联。

3.2 DML

DML：数据操纵语言

  • insert
  • update
  • delete

INSERT 插入

-- insert into tableName([feild1],[field2],....)value(val1_1,val1_2,.....),(val2_1,val2_2,.....).....
INSERT INTO grade (id,score) VALUE ('0001','87')
-- 插入语句一定要保证一一对应
-- 插入多个字段
INSERT INTO grade (id,score) VALUES ('00023','60'),('24','69'),('25','75');

语法：insert into tableName([feild1],[field2],....) value(val1_1,val1_2,.....),(val2_1,val2_2,.....).....

注意事项：

  • 可以省略字段，但必须保证后面的值必须一一对应
  • 可以同时插入多行数据，使用 ', '隔开

UPDATE 修改

update tableName set Property='xxx' where Property='xxx';

UPDATE students SET `name`='liu',sex='female' WHERE id = 1;

语法：

UPDATE tableName SET property1=value,property2=xx WHERE [condition1] AND [condition2] AND ......;

where后接条件运算符

  • = ,> ,<,!= 或 <>,<=,>=
  • between x and y [x,y]
  • AND 和（&&）
  • OR 或（||）

注意：

  • 如果未指定修改条件，数据库将修改所有列
  • SET property1=value，value既可以是常量也可以是变量

DELETE 删除

delete 命令

语法: DELETE FROM TableName WHERE [condition];

-- 删除全部数据
DELETE FROM grade;

-- 删除指定数据
DELETE FROM grade WHERE id = 23;

TRUNCATE 命令

完全清空一个数据表，表结构和索引约束不变

TRUNCATE TABLE test;

truncate 和 delete的区别

truncate 不会影响自增，新插入数据自增重新开始

delete 后再插入数据自增会从上次自增的末尾继续自增

4. DQL 数据查询**

4.1 DQL

​ DQL：数据查询语言

格式

SELECT [ALL | DISTINCT]{* | table.*|table_field1[AS alias1],table_field2[AS alias2].....}
FROM TableName [AS table_alies] [left|right|inner|natural JOIN tableName2][AS table_alies2] [ON..]
[WHERE..]
[GROUP BY..]
[HAVING..]
[ORDER BY]
[LIMIT {[offset,]row_count|row_countOFFSET offset}]

基本查询

    -- 查询全部
    SELECT * FROM student
    SELECT * FROM result

    -- 查询指定字段
    SELECT studentno ,studentname FROM student

    -- 指定字段别名 AS (表也可指定别名)
    SELECT studentno AS 学号 ,studentname AS 姓名 FROM student

    -- 函数 concat(a,b)拼接函数
    SELECT CONCAT ('Name:',studentName) AS NewName FROM student
    ```

查询去重 distinct

作用：去除查询出来的重复数据

-- 去重
SELECT studentno FROM result;  -- 含有重复
SELECT DISTINCT studentno  FROM result;  -- 不含重复

表达式

SELECT VERSION(); -- 查版本
SELECT @@auto_increment_increment; -- 查自增步长
SELECT 10<<10; -- 查询计算结果

-- 查询列计算结果
SELECT  studentno ,studentresult +1 AS 'grade+1' FROM result

4.2 Where 条件字句

作用：检索数据中符合条件的值

逻辑运算符

运算符 语法 描述
and && a and b a&&b 逻辑与
or || a or b a||b 逻辑或
Not ! not a !a 逻辑非 
-- and 
SELECT studentno ,studentresult FROM result WHERE studentresult>=90 AND studentresult <=100;
-- 模糊查询
SELECT studentno ,studentresult FROM result WHERE studentresult BETWEEN 90 AND 100;

-- != 等效 not
SELECT studentno ,studentresult FROM result WHERE studentno != 1000;
SELECT studentno ,studentresult FROM result WHERE ! (studentno = 1000);
-- 等效于
SELECT studentno ,studentresult FROM result WHERE studentno != 1000;

模糊查询：比较运算符

运算符 语法 描述
IS NULL a IS NULL 结果为空，结果为真
IS NOT NULL a IS NOT NULL 结果为非空，结果为真
BETWEEN a IS BETWEEN b and c 结果b c之间为真
LIKE a LIKE b SQL 匹配,如果a匹配b,结果为真
IN a IN (b,c,d....) a属于b,c,d....其中之一为真 
-- 模糊查询
-- 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
-- 查询学号为1000,1001的学生
SELECT  studentno ,studentname FROM student WHERE studentno IN (1000,1001);
-- 查询北京的同学
SELECT  studentno ,studentname FROM student WHERE address IN ('北京朝阳');
-- 查询生日为空的学生
SELECT  studentno ,studentname FROM student WHERE borndate IS NULL;
-- 查询性别不为空的同学
SELECT  studentno ,studentname FROM student WHERE sex IS NOT NULL;

4.3 联表查询

内连接,外连接

-- 联表查询

-- 内连接
-- 条件连接及等连接
-- 条件连接查询
SELECT * FROM student AS s INNER JOIN result AS r WHERE r.`studentno` < s.`studentno`;
-- 等连接查询
SELECT * FROM student AS s INNER JOIN result AS r WHERE r.`studentno` = s.`studentno`;
-- 自然连接查询,等连接查询特例,等连接的基础上保证字段名相等
SELECT * FROM student AS s NATURAL JOIN result AS r ;
-- 逗号连接属于本质生上就是内连接
SELECT *
FROM employee,department
WHERE employee.DepartmentID = department.DepartmentID
-- 等价于:
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID


-- 外连接:有空值的自然连接
-- 左外连接 :选取左表的全部,左表部分不满足条件则将对应字段置空
-- SELECT field1,field2.... FROM table1 LEFT JOIN table2 ON [交叉条件] WHERE [筛选条件]
SELECT * FROM student AS s LEFT JOIN result AS r ON s.`studentno`=r.`studentno`;

-- 右外连接 
-- SELECT field1,field2.... FROM table1 RIGHT JOIN table2 ON [交叉条件] WHERE [筛选条件]
SELECT s.`studentname` ,s.`studentno` ,r.`studentresult`,r.`subjectno` FROM student AS s RIGHT JOIN result AS r ON s.`studentno`=r.`studentno`;

-- 查询 成绩大于90的同学的学号以及科目
SELECT s.`studentname` ,s.`studentno` ,r.`studentresult`,r.`subjectno` FROM student AS s RIGHT JOIN result AS r ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub ON sub.`subjectno`=r.`subjectno`
WHERE r.`studentresult`>=90;

注意:自然连接是特殊的内联结（等值联结），自然联结不能有where和on去限制筛选

自联结

所谓自连接，指的是一个表自己和自己连接.

SELECT catA.`categoryname` AS '父类',catB.`categoryname` AS '子类'
FROM category AS catA , category AS catB
WHERE catB.`pid`=catA.`categoryid`

查询结果:区分混合在一起并且存在内部联系的一组数据

4.5 分页和排序

order by 排序 ASC 升序, DESC降序

-- 根据学生高数成绩升序 ORDER BY res DESC
SELECT r.`studentno`,sub.`subjectname`,s.`studentname`,r.`studentresult` AS res
FROM result AS r , `subject` AS sub ,student AS s
WHERE sub.`subjectno`=r.`subjectno` AND r.`studentno`=s.`studentno` AND r.`subjectno`=1
ORDER BY res ASC

limit

LIMIT : 分页查询,将查询结果分页

语法: limit (起始数据 , 每页数据条数)

SELECT r.`studentno`,sub.`subjectname`,s.`studentname`,r.`studentresult` AS res
FROM result AS r , `subject` AS sub ,student AS s
WHERE sub.`subjectno`=r.`subjectno` AND r.`studentno`=s.`studentno` 
ORDER BY res ASC
LIMIT 0,4

-- 格式 limit (N-1)pageSize,pageSize
-- 第一页 limit 0,4
-- 第二页 limit 4,4
-- 第三页 limit 8,4
-- 第N页 limit 4*(N-1),4

-- 查询案例 查询高等数学学科大学80分前3的学生信息
SELECT s.`studentno`,sub.`subjectname`,s.`studentname`,r.`studentresult`
FROM student AS s 
INNER JOIN result AS r 
ON  s.`studentno`=r.`studentno` 
INNER JOIN `subject` AS sub 
ON r.`subjectno`= sub.`subjectno` 

WHERE sub.`subjectname` LIKE '高等数学%' AND r.`studentresult`>='80'
ORDER BY r.`studentresult` DESC
LIMIT 0,3

where子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，为了让读者更加清楚子查询的概念。

子查询可以返回的数据类型一共分为四种：

  1. 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；

    -- 查询最低的成绩
SELECT * 
FROM result r
WHERE r.`studentresult`=(
SELECT MIN(r.`studentresult`)
FROM result r
);
-- 查询大于80的成绩
SELECT * 
FROM result r
WHERE r.`studentresult`<(
SELECT r.`studentresult` 
FROM result r
WHERE  r.`studentresult`= 80
);
-- 查询大于平均分的成绩
SELECT * 
FROM result r
WHERE r.`studentresult`>(
SELECT AVG(r.`studentresult` )
FROM result r
);

  2. 单行多列：返回一行数据中多个列的内容；

    -- 查询和第一个学科的学时和年级相同的科目的信息
SELECT *
FROM `subject`
WHERE (classhour,gradeid)=(
SELECT classhour ,gradeid
FROM `subject`
WHERE  subjectno=1
);

  3. 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；

    主要使用三种操作符：IN、ANY、ALL

    -- IN
-- 查询课时大于等于110的全部课程信息
SELECT *
FROM `subject`
WHERE (classhour,gradeid) IN(
SELECT classhour ,gradeid
FROM `subject`
WHERE classhour >= 110
);

    ANY在使用中有如下三种使用形式：
    =ANY：表示与子查询中的每个元素进行比较，功能与IN类似（然而<>ANY不等价于NOT IN）
    >ANY：比子查询中返回结果的最小的要大（还包含了>=ANY）
    <ANY：比子查询中返回结果的最大的要小（还包含了<=ANY）

    -- 查询学时大于120的学科
SELECT *
FROM `subject`
WHERE classhour = ANY(
SELECT classhour
FROM `subject`
WHERE classhour >= 120
);
-- 查询大于等于一年级课程中最小课时的课程
SELECT *
FROM `subject`
WHERE classhour > ANY(
SELECT classhour 
FROM `subject` su
WHERE su.`gradeid` = 1
);
-- 查询小于等于二年级课程中最大课时的课程
SELECT *
FROM `subject`
WHERE classhour < ANY(
SELECT classhour 
FROM `subject` su
WHERE su.`gradeid` = 2
);

    ALL操作符有以下三种用法：
    <>ALL：等价于NOT IN（但是=ALL并不等价于IN）
    >ALL：比子查询中最大的值还要大（还包含了>=ALL）
    <ALL：比子查询中最小的值还要小（还包含了<=ALL）

    -- 查询课时不等于二年级任何课程的课时
SELECT *
FROM `subject`
WHERE classhour <> ANY(
SELECT classhour 
FROM `subject` su
WHERE su.`gradeid` = 2
);
-- 查询大于等于一年级课程中最大课时的课程
SELECT *
FROM `subject`
WHERE classhour > ALL(
SELECT classhour 
FROM `subject` su
WHERE su.`gradeid` = 1
);
-- 查询小于等于二年级课程中最小课时的课程
SELECT *
FROM `subject`
WHERE classhour < ALL(
SELECT classhour 
FROM `subject` su
WHERE su.`gradeid` = 2
);

  4. 多行多列：查询返回的结果是一张临时表；

  5. 空数据判断

    在SQL之中提供了一个exists结构用于判断子查询是否有数据返回。如果子查询中有数据返回，则exists结构返回true，反之返回false。

    -- 子查询返回空,EXISTS返回false,无数据输出
SELECT * 
FROM `subject`
WHERE EXISTS(
SELECT * FROM `subject`
WHERE gradeid = 5	
);
-- 子查询返回有值,EXISTS返回true,返回内容
SELECT * 
FROM `subject`
WHERE EXISTS(
SELECT * FROM `subject`
WHERE gradeid = 4	
);

    参考文章:子查询一(WHERE中的子查询) - TonySoprano - 博客园 (cnblogs.com)

4.7 分组

GROUP BY .... HAVING ....

GROUP BY 字句的作用:对查询结果进行分组或分组统计,如对分组进行求和,求最大值,最小值,平均值和行数.

GROUP BY 字句用法: 用于指定分组的列,要求select字句中除了使用聚簇函数的列外,其余各列(同一表中)都应该出现在group by 字句的列表中.

HAVING字句:一般与GROUP BY字句连用,用于对分组过后的数据进行筛选

-- 查询 学号大于1000,成绩大于70的同学的平均成绩,学号,姓名
SELECT AVG(r.`studentresult`) 平均成绩 , r.studentno 学号 ,s.`studentname` 姓名
FROM result r inner JOIN student s
ON s.`studentno` = r.`studentno`
where r.`studentno` > 1000
group by r.`studentno`
having 平均成绩 > 70

注意: WHERE 子句对FROM字句的结果设置过滤条件,而HAVING字句对GROUP BY字句结果进行进行再次过滤.

5 .SQL函数

5.1常用函数

MySQL :: MySQL 5.7 Reference Manual :: 12.1 SQL Function and Operator Reference

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

-- 字符串函数
SELECT CHAR_LENGTH ('草泥马,小逼崽子真没见过黑
社会啊,让你见识见识什么叫黑手!!') ; -- 字符串长度
SELECT CONCAT('我','爱','你','!');  -- 字符串拼接
SELECT INSERT('hello motherfuker!',1,5,'hi'); -- 字符串插入 ('str',pos,len,'newStr')
SELECT LOWER('FuckK'); -- 小写
SELECT UPPER('FuckK'); -- 大写
SELECT INSTR('fuck','c') -- 返回第一次出现字串的索引
SELECT REPLACE('坚持与努力','与','&'); -- 替换 ('str','subStr','newStr')
SELECT SUBSTR('hello you',7,1); -- 取字串 ('str',pos,len)
SELECT REVERSE('iiiiiamY'); -- 反转

-- 查询替换
SELECT REPLACE(s.`studentname`,'王','刘')
FROM student s


-- 时间和日期

SELECT CURRENT_DATE(); -- 当前日期
SELECT CURDATE(); -- 当前日期
SELECT NOW(); --  现在时间和日期
SELECT LOCALTIME(); -- 本地时间和日期
SELECT SYSDATE(); -- 系统时间和日期

-- 获取一个时间和日期的部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());


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

5.2 聚合函数

--  聚簇函数
SELECT COUNT(gradeid) FROM student;  -- 统计指定列的行数,忽略NULL
SELECT COUNT(1) FROM student; -- 1相当于行数,查询行数,不会忽略null
SELECT COUNT(*) FROM student; -- 查询所有列,相当于行数,不会忽略NULL

SELECT SUM(studentresult) FROM result; -- 列求和
SELECT AVG(studentresult) FROM result; -- 求均值
SELECT MIN(studentresult) FROM result; -- 最小值
SELECT MAX(studentresult) FROM result; -- 最大值

5.3 数据库级别的MD5加密

-- MD5加密
-- 直接对表中数据加密
UPDATE test SET pwd = MD5(pwd);
-- 插入是加密
INSERT 	INTO test VALUE('0129',MD5('123222'));
-- 对加密后的密码进行校验
SELECT * FROM test WHERE id = '0129' AND pwd = MD5(123222);

6. 事务

事务具有4个特征，分别是原子性、一致性、隔离性和持久性，简称事务的ACID特性；

1.原子性（atomicity)

一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性.

2.一致性（consistency)

事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。

如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态.

3.隔离性（isolation）

事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。

4. 持久性（durability）

一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。--即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态

4个事务隔离级别

在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同，分别是：未授权读取，授权读取，可重复读取和串行化

1、读未提交（Read Uncommited），该隔离级别允许脏读取，其隔离级别最低；比如事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取

2、授权读取也称为已提交读（Read Commited），授权读取只允许获取已经提交的数据。比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C是将数据项从10加到20，此时事务B也同样可以读取到20，即授权读取允许不可重复读取。

3、可重复读（Repeatable Read)

就是保证在事务处理过程中，多次读取同一个数据时，其值都和事务开始时刻是一致的，因此该事务级别禁止不可重复读取和脏读取，但是有可能出现幻影数据。所谓幻影数据，就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。在上面的例子中，可重复读取隔离级别能够保证事务B在第一次事务操作过程中，始终对数据项读取到1，但是在下一次事务操作中，即使事务B（注意，事务名字虽然相同，但是指的是另一个事务操作）采用同样的查询方式，就可能读取到10或20；

4、串行化

是最严格的事务隔离级别，它要求所有事务被串行执行，即事务只能一个接一个的进行处理，不能并发执行。

参考文章:ACID - 简书 (jianshu.com)

隔离性导致的一些问题

脏读:

指的是一个事务读取到另一个事务为提交的数据

不可重复读:

一个事务前后读取某行数据的结果不一致

虚度(幻读):

一个事务读取到另外一个事新插入的数据

MySQL事务常用操作

-- mysql 默认开启事务的自动提交

SET autocommit = 0; -- 关闭事务自动提交
SET autocommit = 1; -- 开启事务自动提交

START TRANSACTION -- 标记事务的开始

COMMIT -- 提交事务成功,持久化
ROLLBACK -- 回滚事务

SET autocommit = 1

SAVEPOINT -- 设置一个保存点
ROLLBACK SAVEPOINT --  回滚到保存点
RELEASE SAVEPOINT -- 撤销保存点

模拟转账

-- 转账

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop;
CREATE TABLE account(
id INT(4) ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
money DECIMAL(9,2) NOT NULL
)CHARSET=utf8;

INSERT INTO account(`name`,`money`) VALUES('A','2000.00'),('B','1000.00');
COMMIT;

-- 模拟事务转账
SET autocommit = 0; -- 关闭事务自动提交

START TRANSACTION -- 开启事务的执行

UPDATE account SET money = money - 1000 WHERE `name` = 'A';  -- 执行事务
UPDATE account SET money = money + 1000 WHERE `name` = 'B';

COMMIT -- 提交事务成功,持久化
ROLLBACK -- 回滚事务

SET  autocommit = 1; -- 恢复事务自动提交

7. 索引的分类

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

7.1 索引的分类

  • 主键索引(PRIMARY KEY)

    它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

  • 唯一索引(UNIQUE KEY)

    它与前面的普通索引类似，不同的就是：普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

  • 常规索引(KEY/INDEX)

    这是最基本的索引，它没有任何限制。普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。

  • 全文索引(FULLTEXT)

    全文索引时将存储在数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获取全文中有关章，节，段，句，词等信息，也可以进行各种统计和分析。

参考:MySQL索引总结 - 知乎 (zhihu.com)

索引的基本操作

-- 显示索引信息
SHOW index from `subject`;

-- 创建索引 

-- 方法1:创建表时指定
UNIQue KEY -- 列创建时,加上索引
UNIQue key [`indexName`] (`Field`)

-- 方法2: CREATE IndexType indexName ON Table|View(Field[ASC|DESC])
CREATE fulltext INDEX nameIndex ON `subject`(subjectname) ;

-- 删除索引
drop index nameindex on `subject`

-- 分析sql执行状况explain
explain select subjectno from `subject`

7.2 测试索引

create table `app_user`(
`id` BIGINT(20) UNSIGNED NOT NULL ,
`name` VARCHAR(50) DEFAULT '',
`eamil` VARCHAR(50) default NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT 0,
`password` VARCHAR(100) NOT NULL DEFAULT '0',
`age` TINYINT(4) DEFAULT '0',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

alter table `app_user` change `eamil` email ;
alter table `app_user` modify id BIGINT(20) UNSIGNED NOT NULL;

-- 插入100万数据
delimiter $$ -- 写函数之前的标志
create function mock_data1() 
returns INT
begin
declare num int default 1000000;
declare i int default 1;
while i < num Do
	INSERT INTO app_user(`id`,`name`,`phone`) VALUEs(i,concat('用户',i),i+10000);
	SET i = i+1;
end while;
return i;
end;	

TRUNCATE TABLE app_user; -- 清除数据

select mock_data1();

insert into app_user(`id`)value(1);


-- 测试索引

-- 无索引
select * from app_user where `name` = '用户889977'; -- 0.451 sec

-- 创建索引
create unique index nameIndex on app_user(`name`); 

-- 有索引
SELECT * FROM app_user WHERE `name` = '用户889977'; -- 0.001 sec

explain SELECT * FROM app_user WHERE `name` = '用户889977'; -- 0.001 sec

-- 删除索引 
drop index nameindex on app_user;

7.3 索引原则

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

索引的数据结构

Hash类型

B-Tree

一文搞懂MySQL索引（清晰明了）_我本可以-CSDN博客_mysql索引

8. 权限管理和备份

8.1 权限管理

  1. 图像化

  1. sql命令

    -- 创建用户
CREATE  USER wang IDENTIFIED BY '1';

-- 修改密码 (当前用户)
SET PASSWORD = PASSWORD('1');

-- 修改密码 (指定用户)
SET PASSWORD FOR wang = PASSWORD('0');

-- 重命名
RENAME USER wang TO wang1;

--  用户授权
GRANT ALL PRIVILEGES ON *.* TO  wang1;

-- 权限查询
SHOW GRANTS FOR wang1; -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost ; -- 查看root用户权限

-- 撤销权限 REVOKE Privileges ON Database FROM user

REVOKE ALL PRIVILEGES ON *.* FROM wang1;

-- 删除用户
DROP USER wang1;

8.2 数据库备份

  1. 可视化导入导出

  2. 命令行(速度快)

    mysqldump -u User -p[Password] [-h Host] [-P port] database [table1] [table2].... >FileOut

    # 导出
mysqldump -u root -p123456 school1 student>D:/test1.sql # 导出school1数据库student表
mysqldump -u root -p123456 school1 subject student>D:/test2.sql # 导出school1和subject数据库student表
mysqldump -u root -p123456 school1 >D:/test3.sql # 导出school1数据库
# 导入
# 方法1:
# mysql -u user -p[password] [-p host] [-P port] 进入登录状态
# 切换到指定数据库
# source 备份文件
mysql> source d:/testb.sql
# 方法2
# 直接导入 mysql -p -u user [-P port][-h host] database < backup_File
mysql -p -u Admin test < d:/test11.sql

9. 数据库设计范式

软件开发中的数据库设计

  • 用户需求分析
  • 数据库概念模式设计 E-R关系图

数据库设计规范 - swarb - 博客园 (cnblogs.com)

数据库设计3大范式

出现原因：

关系模式中存在如下异常，需要通过逐步规范化来消除异常：

  • 插入异常
  • 删除异常
  • 冗余
  • 更新异常

第一范式（1NF）

满足原子性：保证每列不可分

第二范式（2NF）

满足第一范式，第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）即一张表只能描述一件事。

第三范式（3NF）

满足一二范式，每张表每个字段都和主键直接相关，而不能间接相关。

参考：如何理解关系型数据库的常见设计范式？ - 知乎 (zhihu.com)

10. JDBC

10.1JDBC概述

  • JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。是Java访问数据库的标准规范
  • JDBC提供了一种基准,据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。
  • JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。
  • 我们使用的是mysql 的驱动 mysql-connector-java-5.1.39-bin.jar

10.2 JDBC程序

import java.sql.*;

public class FirstJDBCDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 加载驱动的固定写法
        // 2. 用户信息和url
        String url = "jdbc:mysql://localhost:3306/school1?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";
        // 3. 连接数据库 connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        // 4. 执行sql对象
        Statement statement = connection.createStatement();
        String sql = "select * from result";
        ResultSet resultSet = statement.executeQuery(sql); // 返回查询的结果集

        while(resultSet.next()){  // 获取查询结果
            System.out.println("学号："+resultSet.getObject("studentNo"));
            System.out.println("科目："+resultSet.getObject("subjectNo"));
            System.out.println("成绩："+resultSet.getObject("studentResult"));
            System.out.println("===========================");
        }
        // 5. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

DriverManager

Class.forName("com.mysql.jdbc.Driver"); // 加载驱动的固定写法

// DriverManager.registerDriver(new com.mysql.jdbc.Driver);

Class.forName(xxx.xx.xx)的作用是要求JVM查找并加载指定的类，也就是说JVM会执行该类的静态代码段,此处加载DriverManager.registerDriver(new Driver());

而在JDBC规范中明确要求这个Driver类必须向DriverManager注册自己，即任何一个JDBC Driver的 Driver类的代码都必须类似如下：

public class MyJDBCDriver implements Driver {
static {
DriverManager.registerDriver(new MyJDBCDriver());
}
}

参考：Class.forName()用法详解_Kaiwii的专栏-CSDN博客_classforname用法

Connection

// 3. getConnection(url,userName,password)方法获取数据库 connection代表获取到的数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
// collection对象可用于执行事务
collection.rollback();  // 回滚
collection.commit();	// 提交
collection.setAutoCommit(); //  设置自动提交

Url

String url = "jdbc:mysql://localhost:3306/school1?useUnicode=true&characterEncoding=utf8&useSSL=false";
// jdbc:mysql://Host:Port/Database?param1&parm2......

Statement 执行SQL对象

Statement 和 Prestatement都可用于执行SQL对象当你需要执行Statement对象多次的时候，PreparedStatement对象将会大大降低运行时间，当然也加快了访问数据库的速度 -- Java数据库JDBC——prepareStatement的用法和解释_nnzhuilian的博客-CSDN博客_preparestatement

statement.executeQuery();// 执行查询 返回一个结果集合
statement.excute();      // 执行sql语句
statement.excuteUpdate();// 执行修改（删除，更新，插入）

ResultSet

ResultSet resultSet = statement.executeQuery(sql); // 返回查询的结果集

resultSet 执行查询返回的结果集

resultSet.getObject("field"); // 获取类型未知类型的列
// 获取已知类型
resultSet.getString("field");
resultSet.getInt("field");
resultSet.getDate("field");
.....

指针查找结果集合

resultSet.beforeFirst(); // 指针移动到头部
resultSet.afterLast();   // 指针移动到尾部
resultSet.next();        // 指针移动到下一个数据
resultSet.previous();    // 指针向前移动
resultSet.absolute(int row); // 指针移动到指定行

释放资源

resultSet.close();
statement.close();
connection.close();

10.3 Statement

当你创建了一个 Statement 对象之后，你可以用它的三个执行方法的任一方法来执行 SQL 语句。

boolean execute(String SQL) :如果 ResultSet 对象可以被检索，则返回的布尔值为 true ，否则返回 false 。当你需要使用真正的动态 SQL 时，可以使用这个方法来执行 SQL DDL 语句。

int executeUpdate(String SQL) :返回执行 SQL 语句影响的行的数目。使用该方法来执行 SQL 语句，是希望得到一些受影响的行的数目，例如，INSERT，UPDATE 或 DELETE 语句。

ResultSet executeQuery(String SQL) :返回一个 ResultSet 对象。当你希望得到一个结果集时使用该方法，就像你使用一个 SELECT 语句。

简化查询

  • 创建查询工具类用于简化查询

    数据库信息文件

    db.properties

    # 数据库配置信息
driver=com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/school1?useUnicode=true&characterEncoding=utf8&useSSL=false
userName = root
passWord = 123456

    数据库配置工具类

    import java.io.IOException;
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 passWord = null;
    private static String userName = null;

    static {
         // 加载数据库配置
        InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            passWord = properties.getProperty("passWord");
            userName = properties.getProperty("userName");
            // 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    // 连接数据库
    public static Connection connectBD() throws SQLException {
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        return connection;
    }

    // 释放资源
    public static void releaseBD(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (connection!=null)
            connection.close();
        if (statement!=null)
            statement.close();
        if (resultSet!=null)
            resultSet.close();
    }
}

executeUpdate

package JDBC.demo02;

import JDBC.demo02.utils.JdbcUtils;

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

public class TestDB {
    public static void main(String[] args) {
        ResultSet rs = null;
        Connection connection = null;
        Statement upStatement = null;
        try {
            // 连接数据库
            connection = JdbcUtils.connectBD();
            // 创建sql声明
            upStatement = connection.createStatement();
            // 插入sql
            String testInsetSql = "INSERT INTO result VALUES(1002,'1','2013-11-11 16:00:00',70), " +
                    "(1003,1,'2013-11-11 16:00:00',82)";
            // 删除sql
            String testDeleteSql = "DELETE FROM result  WHERE studentno = 1002 AND subjectno = 1";
            // 修改sql
            String testUpdateSql = "UPDATE result SET studentresult = 90 WHERE studentno = 1001 AND subjectno = 1";

            if (upStatement.executeUpdate(testUpdateSql) > 0)
                System.out.println("更新成功！");
            else
                System.out.println("跟新失败！");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            try {
                // 关闭连接
                JdbcUtils.releaseBD(connection,upStatement,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

executeQuery

package JDBC.demo02;

import JDBC.demo02.utils.JdbcUtils;

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

public class TestDBQuery {
    public static void main(String[] args) {
        ResultSet rs = null;
        Connection connection = null;
        Statement quStatement = null;
        try {
            // 连接数据库
            connection = JdbcUtils.connectBD();
            // 创建sql声明
            quStatement = connection.createStatement();
            // 查询sql语句
            String testQuerySql = "select * from result";
            rs = quStatement.executeQuery(testQuerySql);
            while (rs.next()){
                System.out.println(rs.getObject("studentno"));
                System.out.println(rs.getObject("subjectno"));
                System.out.println(rs.getObject("examdate"));
                System.out.println(rs.getObject("studentresult"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            try {
                // 关闭连接
                JdbcUtils.releaseBD(connection,quStatement,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

SQL 注入

SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序，而这些输入大都是SQL语法里的一些组合，通过执行SQL语句进而执行攻击者所要的操作，其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。

sql注入实例

import JDBC.demo02.utils.JdbcUtils;

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

public class TestSQLInjection {
    public static void main(String[] args) {
        // 无论账号密码填什么都能登录成功
        login(" addcad 'or '1=1","dascdd 'or' 1=1");
    }
    public static void login(String usrN ,String pwd){
        ResultSet rs = null;
        Connection connection = null;
        Statement quStatement = null;
        try {
            // 连接数据库
            connection = JdbcUtils.connectBD();
            // 创建sql声明
            quStatement = connection.createStatement();
            // 查询sql语句
            String testQuerySql = "select * from `user` where `user_id` = '"+usrN+"'AND `pwd` = '"+pwd+"'";
            rs = quStatement.executeQuery(testQuerySql);
            if (rs.next()){
                System.out.println("登录成功！");
            }
            else
                System.out.println("账号或密码错误！");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            try {
                // 关闭连接
                JdbcUtils.releaseBD(connection,quStatement,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

扩展：

sql注入：三种数据库的 SQL 注入详解_YT的博客-CSDN博客_sql注入

sql注入防御：SQL注入详解 - myseries - 博客园 (cnblogs.com)

10.4 PreparedStatement

更新（删，改，增）

import JDBC.demo02.utils.JdbcUtils;

import java.sql.*;
import java.util.Date;

public class TestPreStatement {
    public static void main(String[] args) {
        ResultSet rs = null;
        Connection connection = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        PreparedStatement pst3 = null;
        try {
            // 连接数据库
            connection = JdbcUtils.connectBD();

            // 插入sql
            String testInsetSql = "INSERT INTO result VALUES(?,?,?,?)"; // 使用预编译是sql语句中参数使用占位符?
            // 删除sql
            String testDeleteSql = "DELETE FROM result  WHERE studentno = ? AND subjectno = ?";
            // 修改sql
            String testUpdateSql = "UPDATE result SET studentresult = ? WHERE studentno = ? AND subjectno = ?";

            pst1 = connection.prepareStatement(testInsetSql); // 预编译sql

            pst1.setInt(1,1005); // 给参数赋值
            pst1.setInt(2,1);
            pst1.setDate(3, new java.sql.Date(new Date().getTime()));
            pst1.setInt(4,89);

            if (pst1.executeUpdate()>0)
                System.out.println("插入成功！");
            else System.out.println("插入失败");

            pst2 = connection.prepareStatement(testDeleteSql);
            pst2.setInt(1,1003);
            pst2.setInt(2,1);
            if (pst2.executeUpdate()>0){
                System.out.println("删除成功");
            }
            else System.out.println("删除失败");

            pst3 = connection.prepareStatement(testUpdateSql);
            pst3.setInt(1,80);
            pst3.setInt(2,1000);
            pst3.setInt(3,2);
            if (pst3.executeUpdate()>0){
                System.out.println("修改成功");
            }
            else System.out.println("修改失败");


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            try {
                // 关闭连接
                JdbcUtils.releaseBD(connection, null,pst1,pst2,pst3);

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

查询

import JDBC.demo02.utils.JdbcUtils;

import java.sql.*;

public class TestPreStaQuery {
    public static void main(String[] args) {
        ResultSet rs = null;
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            // 连接数据库
            connection = JdbcUtils.connectBD();
            // 查询sql语句
            String testQuerySql = "select subjectno,studentresult from result where studentno = ?";
            pst = connection.prepareStatement(testQuerySql);

            pst.setInt(1,1001);

            rs = pst.executeQuery();

            while (rs.next()){
                System.out.print(rs.getObject("subjectno")+"\t");
                System.out.println(rs.getObject("studentresult"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            try {
                // 关闭连接
                JdbcUtils.releaseBD(connection,rs,pst);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

防sql注入

import java.sql.*;

public class TestPreventSQLInjection {
    public static void main(String[] args) {
        // 账号密码必须正确才能登录
        login(" addcad' or '1=1","dascdd 'or' 1=1");
    }
    public static void login(String usrN ,String pwd){
        ResultSet rs = null;
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            // 连接数据库
            connection = JdbcUtils.connectBD();
            // 查询sql语句
            String testQuerySql = "select * from `user` where `user_id` = ? AND `pwd` = ?";

            pst = connection.prepareStatement(testQuerySql);  // 预编译会将转义字符全部忽略，故无法进行sql字符
            pst.setString(1,usrN);
            pst.setString(2,pwd);

            rs = pst.executeQuery();

            if (rs.next()){
                System.out.println("登录成功！");
            }
            else
                System.out.println("账号或密码错误！");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            try {
                // 关闭连接
                JdbcUtils.releaseBD(connection,rs,pst);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

10.5 事务

事务的ACID准则

原子性：

一致性：

隔离性：

持久性：

JDBC 事务处理

import JDBC.demo02.utils.JdbcUtils;

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

public class TestTransaction01 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            connection = JdbcUtils.connectBD();
            // 关闭自动提交
            connection.setAutoCommit(false);

            String ATrans = "UPDATE bank SET money = money - 1000 WHERE account = 'A'";
            String toB = "UPDATE bank SET money = money + 1000 WHERE account = 'B'";

            pst = connection.prepareStatement(ATrans);
            pst.executeUpdate();

            int a = 1/0;

            pst = connection.prepareStatement(toB);
            pst.executeUpdate();

            // 事务提交
            connection.commit();
            System.out.println("转账成功");


        } catch (SQLException throwables) {
            try {

                assert connection != null;
                // 如果事务失败回滚（不显示说明也会执行回滚）
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            try {
                JdbcUtils.releaseBD(connection,null,pst);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

10.6数据库连接池

1. 数据连接池

为什么使用数据库连接池

数据库连接是一种关键的有限的昂贵的资源，这一点在多用户的网页应用程序中体现得尤为突出。 一个数据库连接对象均对应一个物理数据库连接，每次操作都打开一个物理连接，使用完都关闭连接，这样造成系统的性能低下。

执行一个SQL命令，如果不使用连接池，需要经过的流程

  1. TCP建立连接的三次握手
  2. MySQL认证的三次握手
  3. 真正的SQL执行
  4. MySQL的关闭
  5. TCP的四次握手关闭

缺点：

  • 网络IO较多
  • 数据库的负载较高
  • 响应时间较长及QPS较低
  • 应用频繁的创建连接和关闭连接，导致临时对象较多，GC频繁
  • 在关闭连接后，会出现大量TIME_WAIT 的TCP状态（在2个MSL之后关闭）

使用数据库连接池后执行SQL语句：第一次访问的时候，需要建立连接。 但是之后的访问，均会复用之前创建的连接，直接执行SQL语句。

优点：

  • 较少了网络开销
  • 系统的性能会有一个实质的提升
  • 没了麻烦的TIME_WAIT状态
什么是数据库连接池

为解决上述问题，可以采用数据库连接池技术。数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。我们可以通过设定连接池最大连接数来防止系统无尽的与数据库连接。更为重要的是我们可以通过连接池的管理机制监视数据库的连接的数量﹑使用情况，为系统开发﹑测试及性能调整提供依据。

数据库连接池工作原理

  • 连接池的建立

    一般在系统初始化时，连接池会根据系统配置建立，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销。

  • 连接池中连接的使用管理

    连接池管理策略是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。其管理策略是：

    当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没达到就重新创建一个连接给请求的客户；如果达到就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。

    当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过就从连接池中删除该连接，否则保留为其他客户服务。

    该策略保证了数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销。

  • 连接池的关闭

    当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，该过程正好与创建相反。

2.常用JDBC连接池

DBCP

需要Commons-dbcp.jar和Commons-pool.jar

  1. 配置文件dbcp-config.properties

    # DBCP 数据库连接池配置文件
driver=com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/school1?useUnicode=true&characterEncoding=utf8&useSSL=false
userName = root
passWord = 123456
# 初始化连接池大小
initialSize = 10
# 最大连接数 DBCP2.xx为max
maxActive = 40
# 最大和最小空闲连接数
minIdle = 5
maxIdle = 20
# 最大等待时延ms
maxWait=30000

  2. 工具类配置

    import JDBC.demo02.utils.JdbcUtils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class DbpcUtils {
    private static BasicDataSource bds = new BasicDataSource();
    private static DataSource ds = null;

    static {
//        DataSourceConfig();

        try {
            // 加载连接池配置
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcp-config.properties");
            Properties properties = new Properties();
            properties.load(in);
            // 创建数据源BasicDataSourceFactory.createDataSource()
            ds = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

        // 连接数据库
        public static Connection connectDB () throws SQLException {
            return ds.getConnection();
        }

        // 释放资源
        public static void release (Connection connection, ResultSet resultSet, Statement...statements) throws
        SQLException {
            if (connection != null)
                connection.close();
            if (resultSet != null)
                resultSet.close();

            for (Statement statement : statements) {
                if (statement != null)
                    statement.close();
            }
        }

        //以代码形式，设置数据源各属性值
        private static void DataSourceConfig () {
            bds.setDriverClassName("com.mysql.jdbc.Driver");
            bds.setUsername("root");
            bds.setPassword("123456");
            bds.setUrl("jdbc:mysql://localhost:3306/school1?useUnicode=true&characterEncoding=utf-8&useSSL=false");
            bds.setInitialSize(20);
            bds.setMaxActive(80);
            bds.setMaxIdle(40);
            bds.setMinIdle(20);
            bds.setMaxWait(6000);
        }
    }
C3p0

需要包mchange-commons-java-0.2.19.jar，c3p0-0.9.5.5.jar

  • 配置文件c3p0-config.xml

    <?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>
   <!-- 使用默认的配置读取连接池对象 -->
   <default-config>
      <!--  连接参数 -->
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3306/school1</property>
      <property name="user">root</property>
      <property name="password">123456</property>

      <!-- 连接池参数 -->
      <!--初始化的申请的连接数量-->
      <property name="initialPoolSize">5</property>
      <!--最大的连接数量-->
      <property name="maxPoolSize">10</property>
      <!--连接超时时间-->
      <property name="checkoutTimeout">3000</property>
   </default-config>

   <named-config name="otherc3p0">
      <!--  连接参数 -->
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test</property>
      <property name="user">root</property>
      <property name="password">123456</property>

      <!-- 连接池参数 -->
      <property name="initialPoolSize">5</property>
      <property name="maxPoolSize">8</property>
      <property name="checkoutTimeout">1000</property>
   </named-config>
</c3p0-config>

  • 工具类配置

    import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class C3p0Utils {
    private static ComboPooledDataSource cpds = new ComboPooledDataSource();// ComboPooledDataSource()内可指定连接池的配置方案，例如使用方法“Mysql”则为，ComboPooledDataSource("Mysql")


//       static{
//        try {
//            DataSourceConfig();
//        } catch (PropertyVetoException e) {
//            e.printStackTrace();
//        }
//        }

    public static Connection getConnection() throws SQLException {
        return cpds.getConnection();
    }
    // 释放资源
    public static void release (Connection connection, ResultSet resultSet, Statement...statements) throws
            SQLException {
        if (connection != null)
            connection.close();
        if (resultSet != null)
            resultSet.close();
        for (Statement statement : statements) {
            if (statement != null)
                statement.close();
        }
    }

    //以代码形式，设置数据源各属性值
    private static void DataSourceConfig () throws PropertyVetoException {
       cpds.setDriverClass("com.mysql.jdbc.Driver");
       cpds.setUser("root");
       cpds.setPassword("123456");
       cpds.setJdbcUrl("jdbc:mysql://localhost:3306/school1?useUnicode=true&characterEncoding=utf-8&useSSL=false");
       cpds.setInitialPoolSize(20);
       cpds.setMaxPoolSize(80);
       cpds.setMinPoolSize(20);
       cpds.setMaxIdleTime(6000);
    }
}

    详解C3P0（数据库连接池） - 菜鸟++ - 博客园 (cnblogs.com)

posted @ 2021-05-08 01:13  DDeann  阅读(69)  评论(0)    收藏  举报