11.MySQL

MySQL

安装MySQL

mysql5.7 64位压缩包下载地址:
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
安装步骤:
1、下载后得到zip压缩包.
2、解压到自己想要安装到的目录,本人解压位置是D:\Environment\mysql-5.7.19
3、添加环境变量:我的电脑->属性->高级->环境变量
选择系统变量path,在其后面添加: 你的mysql 安装文件下面的bin文件夹
4、在mysql-5.7.19文件夹下创建 my.ini 文件 ,在my.ini文件中加上一下代码:

[mysqld]
basedir=D:\Environment\mysql-5.7.19\
datadir=D:\Environment\mysql-5.7.19\data\
port=3306
skip-grant-tables

5、启动管理员模式下的cmd命令窗口,并将路径切换至mysql下的bin目录,然后输入mysqld –install (安装mysql)
6、再输入: mysqld --initialize-insecure --user=mysql 初始化数据文件
7、然后再次启动mysql(输入命令:net start mysql),然后再输入命令 mysql –u root –p 进入mysql管理界面(密码可为空)
8、进入界面后更改root用户密码:
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

9、刷新权限: flush privileges;
10、修改 my.ini文件注释最后一句 #skip-grant-tables
11、重启mysql即可正常使用:net start mysql
12、连接上测试出现以下结果就安装好了
在这里插入图片描述
几个常用的数据库操作命令(在cmd命令窗口执行的指令):
net stop mysql (停止mysql服务)
net start mysql (启动mysql服务)
flush privileges; (刷新数据库)
show databases; (显示所有数据库)
use dbname;(打开dbname数据库)
show tables; (显示数据库mysql中所有的表)
describe user; (显示mysql数据库中user表的列信息)
create database name; (创建数据库)
use databasename; (选择数据库)
exit; (退出Mysql)
mysql -h 服务器主机地址 -u 用户名 -p 用户密码 (连接指定ip地址电脑的用户数据库)
mysql -u 用户名 -p 用户密码 (连接本地用户数据库)

CRUD(增删改查)操作

1)添加数据: INSERT
语法:INSERT INTO 表名[(字段1,字段2,字段3,…)] VALUES(‘值1’,‘值2’,‘值3’)
注意 : 字段或值之间用英文逗号隔开 .’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .可同时插入多条数据 , values 后用英文逗号隔开 .

INSERT INTO `course`(`cno`,`cname`,`cpno`,`ccredit`)
VALUES(8, 'java', 7, 3);

结果为course表添加一行新数据:
在这里插入图片描述
2)修改数据: UPDATE
语法:UPDATE 表名 SET column_name=value [,column_name2=value2,…] [WHERE condition];
注意 : column_name 为要更改的数据列,value 为修改后的数据 , 可以为变量 、具体值 、 表达式或者嵌套的SELECT结果。condition 为筛选条件 , 如不指定则修改该表的所有列数据

UPDATE 	`course` 
SET cname = 'java语言' 
WHERE `cno` = 8;

结果为course表cno=8的那一行数据中的cname改为java语言:
在这里插入图片描述
3)删除数据: DELETE
语法:DELETE FROM 表名 [WHERE condition];
注意:condition为筛选条件 , 如不指定则删除该表的所有列数据

DELETE FROM `course`
WHERE `cno` = 8; 

结果为从course表中删除cno=8的那一行数据:
在这里插入图片描述

DELETE FROM `course`; -- 表示删除整个course表的数据,但不删除表的结构(即字段等)
TRUNCATE TABLE `course`; -- 表示删除整个course表的数据,但不删除表的结构(即字段等),而且也会重置自动增量为0

TRUNCATE和DELETE区别:
相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
不同 :使用TRUNCATE会重新设置AUTO_INCREMENT(自动增量)计数器为0。使用TRUNCATE TABLE不会对事务有影响 (事务后面会说)
测试:

-- 创建一个测试表
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入几个测试数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

-- 删除表数据(不带where条件的delete)
DELETE FROM test;
-- 结论:如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志.

-- 删除表数据(truncate)
TRUNCATE TABLE test;
-- 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.

-- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

4)查询数据: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 * FROM `course` WHERE `cno` = 1;
-- 查询指定列 cno和cname
SELECT `cno`, `cname` FROM `course` WHERE `cno` = 1;

AS:别名(给字段、表、计算的结果取别名)

-- AS cno别名课程编号,cname别名课程名称 ,course别名c
SELECT `cno` AS 课程编号, `cname` AS 课程名称 FROM `course` AS c
WHERE `cno` = 1;

DISTINCT:去掉指定列中重复的数据

-- 去掉ccredit列中重复的数据,只保留该重复数据中的一条数据
SELECT DISTINCT `ccredit` FROM course;

WHERE:筛选符合条件的那一行或多行数据
LIKE: 模糊查询,查找符合指定匹配字符串的那一行或多行数据
IN: 模糊查询,查找在IN中的某个符合的数据,返回那一行或多行数据

-- 查询姓刘的同学,后面只有两个字的  _匹配任意单个字符
SELECT * FROM studentWHERE studentname LIKE '刘__';
-- 查询姓名中含有嘉字的  %匹配任意多个字符
SELECT * FROM student WHERE studentname LIKE '%嘉%';
-- 查询学号为1000,1001,1002的学生
SELECT * FROM student WHERE studentno IN (1000,1001,1002);

连接查询(联表查询):
https://www.cnblogs.com/dinglinyong/p/6656315.html

为什么连接查询用on作为连接条件,而不用where?
1.on本来就是配合join使用的。2.on在左连接或则右连接时会保证左表的数据完整或则右表数据完整,而where不会考虑这个(即不保证左表数据全有或则右表数据全有)。3.where本质是对临时产生的表再进行筛选得到最终表的操作。

在这里插入图片描述
自连接:表与自身进行连接

// 一张表,取两个别名,这样就可以看成是两张表
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid

分组:GROUP BY

  -- 查询各科平均成绩中成绩大于90的
SELECT `Cno`, AVG(`Grade`) FROM `sc` GROUP BY `Cno` HAVING AVG(`Grade`)>90;

这里谈谈where和having的区别:1.where是在分组之前进行条件筛选(进行的是全表条件筛选),不满足的行直接不参与分组;having是在分组以后进行条件筛选(此时是对分好组的每组数据进行条件筛选,不满足的组就pass掉)2.where不能配合聚合函数使用,having可以。

ORDER BY: 排序
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。

LIMIT:分页

-- 从第一行数据开始,4行数据为一页,当前为第一页
SELECT * FROM course LIMIT 0,4;
-- 从第五行数据开始,4行数据为一页,当前为第二页
SELECT * FROM course LIMIT 4,4;

子查询:
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句,嵌套查询可由多个子查询组成,求解的方式是由里及外,子查询返回的结果一般都是集合,故而建议使用IN关键字;

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

SQL内置函数:
https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html

--CURTIME()函数,返回当前日期时间
SELECT current_timestamp();

– 数值函数
abs(x) – 绝对值 abs(-10.9) = 10
format(x, d) – 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) – 向上取整 ceil(10.1) = 11
floor(x) – 向下取整 floor (10.1) = 10
round(x) – 四舍五入去整
mod(m, n) – m%n m mod n 求余 10%3=1
pi() – 获得圆周率
pow(m, n) – m^n
sqrt(x) – 算术平方根
rand() – 随机数
truncate(x, d) – 截取d位小数
– 时间日期函数
now(), current_timestamp(); – 当前日期时间
current_date(); – 当前日期
current_time(); – 当前时间
date(‘yyyy-mm-dd hh:ii:ss’); – 获取日期部分
time(‘yyyy-mm-dd hh:ii:ss’); – 获取时间部分
date_format(‘yyyy-mm-dd hh:ii:ss’, ‘%d %y %a %d %m %b %j’); – 格式化时间
unix_timestamp(); – 获得unix时间戳
from_unixtime(); – 从时间戳获得时间
– 字符串函数
length(string) – string长度,字节
char_length(string) – string的字符个数
substring(str, position [,length]) – 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) – 在str中用replace_str替换search_str
instr(string ,substring) – 返回substring首次在string中出现的位置
concat(string [,…]) – 连接字串
charset(str) – 返回字串字符集
lcase(string) – 转换成小写
left(string, length) – 从string2中的左边起取length个字符
load_file(file_name) – 从文件读取内容
locate(substring, string [,start_position]) – 同instr,但可指定开始位置
lpad(string, length, pad) – 重复用pad加在string开头,直到字串长度为length
ltrim(string) – 去除前端空格
repeat(string, count) – 重复count次
rpad(string, length, pad) --在str后用pad补充,直到长度为length
rtrim(string) – 去除后端空格
strcmp(string1 ,string2) – 逐字符比较两字串大小
– 聚合函数
count(*):包含所有列,按行计数,不忽略NULL
count(1); 忽略所有列,按行计数 ,不忽略NULL
count(列名); 按列的值计数,会忽略NULL(不计算NULL的那一行)

sum();
max();
min();
avg();
group_concat()
– 其他常用函数
md5();
default();

MySQL中的MD5加密

CREATE TABLE `md5test`(		-- 创建表
  `id` INT(10) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE INNODB DEFAULT CHARSET = utf8;

INSERT INTO `md5test` --插入数据,password字段利用MD5()函数进行MD5加密
VALUE(1, '张三', MD5('111111')),	
     (2, '李四', MD5('222222')),
     (3, '老五', MD5('333333')),
     (4, '无极', MD5('444444')),
     (5, '八卦', MD5('555555')),
     (6, '太极', MD5('666666'));

SELECT * FROM `md5test` WHERE `name`='太极' AND pasword=MD5('666666'); --查找密码为666666的用户

事务

1)什么是事务
事务就是将一组SQL语句放在同一批次内去执行;如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行;MySQL事务处理只支持InnoDB和BDB数据表类型。

2)事务的ACID原则
原子性(Atomic):整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(ROLLBACK)到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性(Consist):一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性。
隔离性(Isolated):隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。
持久性(Durable):在事务完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

CREATE DATABASE `shop`CHARSET utf8 COLLATE utf8_general_ci;	
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `account`(`name`,`cash`)
VALUE('A',2000.00),('B',10000.00);

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE `account` SET cash=cash-500 WHERE `name`='A';
UPDATE `account` SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务,上面两个更新操作的结果将持久化到磁盘中(A为1500,B为10500)
ROLLBACK; -- 回滚,上面两个更新操作的结果不会存到磁盘中,又回到事物开始的状态,(A为2000,B为10000--COMMIT和ROLLBACK只能执行一个,因为操作要么都成功,要么即失败
SET autocommit = 1; -- 恢复自动提交

索引

1)索引的作用: 1.提高查询速度;2.确保数据的唯一性;3.可以加速表和表之间的连接 , 实现表与表之间的参照完整性;4.使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间;5.全文检索字段进行搜索优化.
2)索引分类: 1.主键索引 (Primary);2.唯一索引 (Unique);3.常规索引 (key);4.全文索引 (FullText);
主键索引: 最常见的索引类型,确保数据记录的唯一性,确定特定数据记录在数据库中的位置
唯一索引: 唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下,该索引可以确保索引列中每个值组合都是唯一的。例如,如果在 last_name、first_name 和 middle_initial 列的组合上创建了唯一索引 full_name,则该表中任何两个人都不可以具有相同的全名。与主键索引的区别,主键索引只能有一个唯一索引可能有多个。
常规索引: 快速定位特定数据,应加在查询找条件的字段,不宜添加太多常规索引,影响数据的插入,删除和修改操作。
全文索引: 快速定位特定数据,只能用于MyISAM类型的数据表,只能用于CHAR , VARCHAR , TEXT等数据列类型,适合大型数据集

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT ,
  `GradeName` VARCHAR(32) NOT NULL ,
  `password` VARCHAR(32) NOT NULL,
  `add` VARCHAR(32) NOT NULL,
  
   PRIMARY KEY(`GradeID`),
   UNIQUE KEY `g`(`gradename`),
   KEY `p`(`password`),
   FULLTEXT `a`(`add`)
)

在这里插入图片描述
3)索引准则: 索引不是越多越好;不要对经常变动的数据加索引;小数据量的表建议不要加索引;索引一般应加在查找条件的字段
4)索引的数据结构: 我们可以在创建上述索引的时候,为其指定索引类型,分两类。hash类型的索引:查询单条快,范围查询慢。btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;

用户授权

第一种方式:通过图新用户见面进行新建用户并授权
在这里插入图片描述
图中各权限意义:
ALL [PRIVILEGES] – 设置除GRANT OPTION之外的所有简单权限
ALTER – 允许使用ALTER TABLE
ALTER ROUTINE – 更改或取消已存储的子程序
CREATE – 允许使用CREATE TABLE
CREATE ROUTINE – 创建已存储的子程序
CREATE TEMPORARY TABLES – 允许使用CREATE TEMPORARY TABLE
CREATE USER – 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW – 允许使用CREATE VIEW
DELETE – 允许使用DELETE
DROP – 允许使用DROP TABLE
EXECUTE – 允许用户运行已存储的子程序
FILE – 允许使用SELECT…INTO OUTFILE和LOAD DATA INFILE
INDEX – 允许使用CREATE INDEX和DROP INDEX
INSERT – 允许使用INSERT
LOCK TABLES – 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS – 允许使用SHOW FULL PROCESSLIST
REFERENCES – 未被实施
RELOAD – 允许使用FLUSH
REPLICATION CLIENT – 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE – 用于复制型从属服务器(从主服务器中读取二进制日志事件)
SELECT – 允许使用SELECT
SHOW DATABASES – 显示所有数据库
SHOW VIEW – 允许使用SHOW CREATE VIEW
SHUTDOWN – 允许使用mysqladmin shutdown
SUPER – 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句,mysqladmin debug命令;允许您连接(一次),即使已达到max_connections。
UPDATE – 允许使用UPDATE
USAGE – “无权限”的同义词
GRANT OPTION – 允许授予权限

第二种方式:通过SQL代码新建用户并进行授权操作

-- 刷新权限
FLUSH PRIVILEGES;

-- 增加用户 格式:CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER kuangshen IDENTIFIED BY '123456';

-- 设置密码
SET PASSWORD = PASSWORD('123456');    -- 为当前用户设置密码
SET PASSWORD FOR kuangshen = PASSWORD('123456');   -- 为指定用户设置密码

-- 用户重命名
RENAME USER kuangshen TO kuangshen2;

-- 分配权限/添加用户
GRANT ALL PRIVILEGES ON *.* TO kuangshen2; -- 给用户kuangshen2所有库的所有表授予所有权限(但不授予授权权限)

-- 查看权限   
SHOW GRANTS FOR kuangshen2; -- 查看kuangshen2用户的权限
SHOW GRANTS FOR root@localhost; -- 查看root用户的权限,这里需要加@localhost,
				-- kuangshen2不用加是因为他是kuangshen2@%,可以不用写@%

-- 撤消权限 格式:REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES ON *.*FROM kuangshen2;   -- 撤销kuangshen2用户对所有库的所有表的所有权限

-- 删除用户
DROP USER kuangshen2;

备份

数据库备份必要性: 1.保证重要数据不丢失;2.数据转移

MySQL数据库备份方法:
1.SQLyog方式导出(这里以course表备份导出为例):如下图,点击备份表作为SQL转储,然后点击导出即可。日后若要使用备份的该文件,则直接拖拽到SQLyog软件中间的编辑器,展示的就是该表的SQL语句。
在这里插入图片描述
导入:按下图所示,将导出的备份sql文件导入即可。
在这里插入图片描述

2.直接拷贝物理文件: 拷贝如下图所示MySQL的data文件或data中的某个文件
在这里插入图片描述
3.使用命令行导出: 这里的命令行即cmd命令窗口,需要使用mysqldump指令
– 导出

  1. 导出schoo数据库的student表,导出的文件名为a.sql – mysqldump -uroot -p123456 school student >D:/a.sql
    mysqldump -uroot -p123456 school student >D:/a.sql   
  1. 导出多张表
   mysqldump -uroot -p123456 school student result >D:/a.sql
  1. 导出所有表
   mysqldump -uroot -p123456 school >D:/a.sql
  1. 导出一个库
   mysqldump -uroot -p123456 -B school >D:/a.sql

– 导入

  1. 在登录mysql的情况下
   source D:/a.sql

2.在不登录的情况下 mysql -u用户名 -p密码 库名 < 备份文件

   mysql -uroot -p123456 school <D:/a.sql

三大范式

1)第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

2)第二范式(2nd NF)
第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。
第二范式要求每个表只描述一件事情

3)第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

规范化和性能的关系
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询

JDBC

1)概念: JDBC(Java Data Base Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC提供了一种标准规范,数据库开发商只需要实现接口,开发人员调用实现类的方法即可对数据库进行操作。
2)JDBC使用步骤:
前提:导入数据库驱动jar包 mysql-connector-java-5.1.37-bin
加载驱动:Class.forName(“com.mysql.jdbc.Driver”);
连接数据库:DriverManager.getConnection(url, username, password);
创建执行sql语句的对象:connection.createStatement();
获得返回的结果集:statement.executeQuery(sql);
释放连接:close();
URL:"jdbc:mysql://localhost:3306/shool?useUnicode=true&characterEncoding=utf8&useSSL=true"
格式为,协议://主机地址:端口号/数据库名?参数1&参数2&参数3
useUnicode=true:使用unicode编码
characterEncoding=utf8:字符集为utf8
useSSL=true:使用安全连接
以mysql为例,协议://主机地址:端口号为:jdbc:mysql://localhost:3306
以oralce为例,协议://主机地址:端口号为:jdbc:oracle:thin:@localhost:1521:sid

        //1.导入数据库驱动jar包  mysql-connector-java-5.1.47.jar
        //2.注册驱动
         Class.forName("com.mysql.jdbc.Driver");
        //3.获取数据库连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/shool?useUnicode=true&characterEncoding=utf8&useSSL=true", "root", "123456");
//        Connection conn = DriverManager.getConnection("jdbc:mysql:///shool?useUnicode=true&characterEncoding=utf8&useSSL=true", "root", "123456");  //如果是本机,且mysql端口号为3306则可以简写
        //4.定义sql语句
        String sql = "SELECT * FROM course";
        //5.获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();
        //6.执行sql
        ResultSet resultSet = stmt.executeQuery(sql);
        //7.处理结果
        while (resultSet.next()) {
            System.out.print(resultSet.getObject("cno") + " ");
            System.out.print(resultSet.getObject("cname") + " ");
            System.out.print(resultSet.getObject("cpno") + " ");
            System.out.println(resultSet.getObject("ccredit"));
        }
        //8.释放资源
        resultSet.close();//释放结果集对象资源
        stmt.close();//释放sql对象资源
        conn.close();//释放数据库对象资源

3)Statement对象:(以后都是用PreparedStatement对象)
JDBC中的Statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sql语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。

executeUpdate方法,返回受影响行数
CRUD操作-create,使用executeUpdate(String sql)方法完成数据添加操作:

Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if (num > 0){
    System.out.println("插入成功");
}

CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作:

Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if (num > 0){
    System.out.println("删除成功");
}

CRUD操作-update
使用executeUpdate(String sql)方法完成数据修改操作:

Statement st = conn.createStatement();
String sql = "update user set name= '' where name=''";
int num = st.executeUpdate(sql);
if (num > 0){
    System.out.println("修改成功");
}

executeQuery方法,返回ResultSet 结果集对象
CRUD操作-read
使用executeQuery(String sql)方法完成数据查询操作:

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
if (rs.next()){
    // 根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}

4)SQL注入的问题:
sql存在漏洞,会被攻击导致数据泄漏。例如,在sql语句中加入” xxx or 1=1”的字段,就会导致原先的判断条件失效,从而造成数据泄漏。

PreparedStatement对象可以防止SQL注入,效率更高。其会预编译SQL语句,并通过传参(即传入的?)的形式设置参数值。PreparedStatement防止SQL注入的本质:把传进来的参数当做字符,假设其中存在转义字符,就直接忽略,‘’引号会被直接转义。

String sql = "inset into users(`id`, `name`, `password`) values(?, ?, ?)";
PreparedStatment st = connection.prepareStatement(sql);
st.setInt(1, 4);//给第一个?设置值
st.setString(2, 'zhangsan');//给第二个?设置值
st.setString(3, '123456');//给第三个?设置值
}

5)事务:
connection数据库连接对象除了可以获取sql操作对象,还有对事务进行操作的方法。
开启事务:connection.setAutoCommit(false);
一组业务执行完毕,提交事务:connection.commit();
在catch中显式定义回滚语句,但默认失败后就回滚:connection.rollback();

/**
 * 事务操作 ,模拟account表转账
 */
public class JDBCDemo10 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            //1.获取连接
            conn = JDBCUtils.getConnection();
            //开启事务(关闭自动提交就是开启事务)
            conn.setAutoCommit(false);
            //2.定义sql
            //2.1 张三 - 500
            String sql1 = "update account set `balance` = balance - ? where `name` = ?";
            //2.2 李四 + 500
            String sql2 = "update account set `balance` = balance + ? where `name` = ?";
            //3.获取执行sql对象
            pstmt1 = conn.prepareStatement(sql1);
            pstmt2 = conn.prepareStatement(sql2);
            //4. 设置参数
            pstmt1.setObject(1,500);
            pstmt1.setObject(2,"张三");

            pstmt2.setObject(1,500);
            pstmt2.setObject(2,"李四");
            //5.执行sql
            pstmt1.executeUpdate();
            // 手动制造异常
//            int i = 3/0;
            pstmt2.executeUpdate();
            
            //整个事务执行完毕,提交事务
            System.out.println("事务执行完成,提交事务");
            conn.commit();
        } catch (Exception e) {
            //在执行过程中发生异常,事务回滚
            try {
                if(conn != null) {
                    System.out.println("事务在执行过程中发生异常,事务回滚");
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.close(pstmt1,conn);
            JDBCUtils.close(pstmt2,null);
        }
    }
}

6)数据库连接池:
前面,我们执行增删改查的流程,数据库连接—执行完毕—释放,频繁的连接–释放十分浪费系统资源。
解决方法:利用数据库连接池,连接池中有许多数据库连接对象,使用完这些数据库连接对象,调用close方法不会释放该数据库连接对象而是归还到池中,以备下次使用。
数据库连接池必须继承实现DataSource的接口。
标准接口:DataSource javax.sql包下的
获取数据库连接对象:getConnection()。 如果数据库连接对象Connection是从连接池中获取的,那么调用Connection.close()方法,则归还到池中。

C3P0: C3P0数据库连接池技术,比较老,性能没有德鲁伊强
使用步骤:

  1. 导入jar包 (两个) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar ,
    不要忘记导入数据库驱动jar包 mysql-connector-java-5.1.37-bin
  2. 定义配置文件:
    名称: c3p0.properties 或者 c3p0-config.xml
    路径:直接将文件放在src目录下即可。
  3. 创建核心对象 数据库连接池对象 ComboPooledDataSource
  4. 获取连接: getConnection
public class C3P0Demo1 {
    public static void main(String[] args) throws SQLException {
        //1.创建数据库连接池对象,空参使用默认的配置
        DataSource ds  = new ComboPooledDataSource(); //多态,ComboPooledDataSource类是c3p0的,继承了DataSource标准数据库连接池接口
        //2. 获取数据库连接对象
        Connection conn = ds.getConnection();
        //3. 打印
        System.out.println(conn);
        conn.close(); //归还连数据库连接对象
    }
}

Druid: 德鲁伊数据库连接池实现技术
使用步骤:
1. 导入jar包:druid-1.0.9.jar 同时不要忘记导入数据库驱动jar包 mysql-connector-java-5.1.37-bin
2. 定义配置文件:是properties格式, 可以叫任意名称,可以放在任意目录下
3. 加载配置文件。Properties
4. 获取数据库连接池对象:通过工厂来来获取 DruidDataSourceFactory
5. 获取连接:getConnection

/**
 * Druid连接池的工具类
 */
public class JDBCUtils {

    //1.定义成员变量 DataSource
    private static DataSource ds ;

    static{
        try {
            //1.加载配置文件
            Properties pro = new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //2.获取DataSource
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过数据库连接池对象获取一个数据库连接对象
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 释放资源
     */
    public static void close(Statement stmt,Connection conn){
       close(null,stmt,conn);
    }

    public static void close(ResultSet rs , Statement stmt, Connection conn){
        if(rs != null){
            try {
                rs.close();//释放连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();//释放连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取连接池方法
     */
    public static DataSource getDataSource(){
        return  ds;
    }
}
public class DruidDemo2 {
    public static void main(String[] args) {
        /*
         * 完成添加操作:给account表添加一条记录
         */
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //1.获取连接
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "insert into account values(null,?,?)";
            //3.获取pstmt对象
            pstmt = conn.prepareStatement(sql);
            //4.给?赋值
            pstmt.setString(1,"王五");
            pstmt.setDouble(2,3000);
            //5.执行sql
            int count = pstmt.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6. 释放资源
            JDBCUtils.close(pstmt,conn);
        }
    }
}

Spring JDBC: 不难发现,每次对数据库进行操作,都涉及获取连接,操作sql,释放资源,这个过程唯一不同的就是操作sql的语法。Spring框架对JDBC的简单封装,提供了一个JDBCTemplate类简化JDBC的开发,开发人员不需要编写获取连接,释放资源的操作,专注于sql的编写即可。(即不用申请连接、归还连接池、也不需要关闭资源)
步骤:
1. 导入jar包
在这里插入图片描述
2. 创建JdbcTemplate对象。参数为数据源DataSource
3. 调用JdbcTemplate的方法来完成CRUD的操作
update():执行DML语句。增、删、改语句
queryForMap():查询结果将结果集封装为map集合,将列名作为key,将值作为value 将这条记录封装为一个map集合 注意:这个方法查询的结果集长度只能是1
queryForList():查询结果将结果集封装为list集合 注意:将每一条记录封装为一个Map集合,再将Map集合装载到List集合中
query():查询结果,将结果封装为JavaBean对象 q
queryForObject:查询结果,将结果封装为对象, 一般用于聚合函数的查询

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.导入jar包
        //2.创建JDBCTemplate对象
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());//需要DataSource对象,自动获取数据库连接对象,sql操作对象
        //3.调用方法
        String sql = "update account set balance = 5000 where id = ?";
        int count = template.update(sql, 3);//执行完自动释放资源
        System.out.println(count); //1
    }
}

IDEA操作数据库

步骤:
1.打开idea操作数据库的小插件
在这里插入图片描述
2.登陆并连接到mysql数据库
在这里插入图片描述
3.若mysql连接失败,则是数据库驱动找不到问题,按如下图操作
在这里插入图片描述
4.如果连接成功,如图所示如果没有找到自己想要操作的数据库,则按如下图操作
在这里插入图片描述
在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值