MySQL

1、初识MySQL

1.1、什么是数据库

数据库（DB，Database）

概念：存储数据的仓库

作用：存储数据，管理数据

数据模型：

内容：
- 数据结构：用来描述实体信息和实体之间的联系。
- 数据操作：用来对数据库的数据进行查询和更新操作。
- 完整性约束：用来对数据库的数据添加各种限制和规则。
种类：
- 结构模型：网状、层次、关系
- 信息模型：E-R模型

1.2、数据库分类

关系型数据库：

MySQL, Oracle [ˈɔrək(ə)l],Sql Server,DB2,SQLlite
通过表与表之间，行与列之间的关系进行数据的存储。

非关系型数据库：

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

DBMS（数据库管理系统）

管理数据库的大型软件，科学有效的管理数据、维护和获取数据。
MySQL, 数据库管理系统！

1.3、MySQL简介

MySQL是一个关系型数据库管理系统

SQL:操作关系型数据库的编辑语言，定义一套操作关系型数据库的统一标准。

前世：瑞典MySQL AB公司

今生：属于Oracle旗下产品

特点 :

免费 , 开源数据库
小巧 , 功能齐全
使用便捷
可运行于Windows或Linux操作系统
可适用于中小型甚至大型网站应用

官网：MySQL

安装：mysql5.7 64位下载地址:

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

1.4、MySQL安装

安装步骤

1、下载后得到zip压缩包.

2、解压到自己想要安装到的目录，

3、添加环境变量：我的电脑->属性->高级->环境变量

选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹

4、编辑 my.ini 文件 ,注意替换路径位置

[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables

5、启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)

CSDN

缺少.dll注意版本

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 stop mysql
net start mysql

12、连接上测试出现以下结果就安装好了

如果您以前装过,现在需要重装,一定要将环境清理干净 .

sc delete mysql 清空服务

这里给大家推荐一个工具 : SQLyog .

即便有了可视化工具,可是基本的DOS命名大家还是要记住!

1.5、SQLyog 安装

SQLyog是可视化管理工具

连接数据库

打开MySQL命令窗口

在DOS命令行窗口进入 安装目录\mysql\bin
可设置环境变量，设置了环境变量，可以在任意目录打开！

连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码

注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !

几个基本的数据库操作命令 :

update user set password=password('123456')where user='root' and Host='localhost'; 修改密码
flush privileges; 刷新数据库
show databases; 显示所有数据库
use dbname；打开某个数据库
show tables; 显示数据库mysql中所有的表
describe user; 显示表mysql数据库中user表的列信息
create database name; 创建数据库
use databasename; 选择数据库

exit; 退出Mysql
? 命令关键词 : 寻求帮助
-- 表示注释

2、操作数据库DDL

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql关键字不区分大小写

2.1、操作数据库

1、创建数据库

CREATE DATABASE IF NOT EXISTS school 
character[ˈkerəktər]set utf8  --字符集
collate utf8_general_ci -- 核对

2、删除数据库

DROP DATABASE IF EXISTS westos

3、使用数据库

USE `school` --切换

4、查看所有数据库

SHOW DATABASES

2.2、数据库的列类型

数值

tinyint 十分小的数据 1字节
smallint 较小的数据 2字节
mediumint 中等大小的数据 3字节
int 标准的整数 4字节常用
bigint 较大的数据 8字节
float 单精度 4字节
double 双精度 8字节
decimal ( a,b)字符串形式的浮点数金融计算的时候使用 b代表小数

字符串

char 定长 0-255
varchar 变长 0-65535
tinytext 微型文本 2^8-1
text 文本串 2^16-1

时间日期

date YYYY-MM-DD,日期格式
time HH: mm: ss 时间格式
datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式
timestamp 时间戳 1970.1.1到现在的毫秒数！
- 每当行被更改时，时间戳字段将获取当前时间戳
year 年份表示

NOW（）和 CURRENT_DATE（）有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。CURRENT_DATE（）仅显示当前年份，月份和日期。

null

没有值，未知
注意，不要使用null进行运算，结果为null

2.3、数据库的字段属性（重点）

Unsigned:

无符号的整数
声明了该列不能申明为负数

zerofill:

0填充的
不足的位，使用0来填充， int（3）， 5 ----005

自增 Auto_increment

通常理解为自增，自动在上一条记录的基础上+1(默认)
通常用来设计唯一的主键~INDEX,必须是整数类型 b
可以自定义设计主键自增的起始值和步长

非空  not  null

假设设置为 not null ,如果不给它赋值，就会报错！
null ，如果不填写值，默认就是null!

默认 default

设置默认的值！

唯一 unique

值唯一

/*每一个表，都必须存在以下五个字段！

id  主键
`version`  乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间
*/

2.4、创建数据库表

CREATE DATABASE IF NOT EXISTS school
USE school
--AUTO_INCREMENT   [ˈɪŋkrəmənt] 自增
--comment  备注 [ˈkɑˌment]
CREATE TABLE `student`(
`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 COMMENT '性别',
`brithday` DATETIME DEFAULT NULL  COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
`emaill`VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)

)ENGINE=INNODB  DEFAULT CHARSET=utf8

格式

create table [if not exists]`表名`(
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]

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

2.5、数据表的类型

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

MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等...

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

MySQL 中有哪几种锁？

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并
发度一般。

锁的优化策略
- 读写分离
- 分段加锁
- 减少锁持有的时间
- 多个线程尽量以相同的顺序去获取资源
- 不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

常规使用操作：

MYISAM 节约空间，速度较快
INNODB 安全性高，事务的处理，多表多用户操作
- MySQL数据表以文件方式存放在磁盘中
- - 包括表文件 , 数据文件 , 以及数据库的选项文件
  - 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
- 注意 :
- - * . frm -- 表结构定义文件
  - * . MYD -- 数据文件 ( data )
  - * . MYI -- 索引文件 ( index )
  - InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

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

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码（不支持中文）

创建表时设置
在my.ini 中设置默认的编码

character-set-server=utf8

2.6、修改删除数据库表

修改
-- 修改表名
-- alter table 旧表名 rename as 新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表字段
-- alter table 表名 add 字段名 列类型[属性]
ALTER TABLE teacher1 ADD sex VARCHAR(2)

-- 修改表字段名、类型
-- 类型 alter table 表名 modify 字段名 列类型[属性]
ALTER TABLE teacher1 MODIFY sex VARCHAR(13)
-- 名 alter table 表名 change 旧字段名 新名 [新]列类型[属性]
ALTER TABLE teacher1 CHANGE sex sex1 VARCHAR(4)

-- 删除表字段
ALTER TABLE teacher1 DROP sex1

删除
drop table if exists 表名

其他
1. 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！

2. 每个库目录存在一个保存当前数据库的选项文件db.opt。

3. 注释：
  单行注释 # 注释内容
  多行注释 /* 注释内容 */
  单行注释 -- 注释内容       (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）)
   
4. 模式通配符：
  _   任意单个字符
  %   任意多个字符，甚至包括零字符
  单引号需要进行转义 \'
   
5. CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。

6. SQL对大小写不敏感 （关键字）

7. 清除已有语句：\c

3、MySQL数据库管理

3.1、外键（了解即可）

-- 创建外键的方式一 : 创建子表同时创建外键
KEY `FK_gradeid` (`gradeid`),-- 定义外键名
CONSTRAINT[kənˈstreɪnt]`FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

注意 : 删除具有主外键关系的表时 , 要先删子表 , 后删主表

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

4、DML语言

数据库意义 ：数据存储、数据管理

管理数据库数据方法：

通过SQLyog等管理工具管理数据库数据
通过DML语句管理数据库数据

DML语言 ：数据操作语言

用于操作数据库对象中所包含的数据
包括 :
- INSERT (添加数据语句)
- UPDATE (更新数据语句)
- DELETE (删除数据语句)

4.1、添加数据

INSERT命令

语法：

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1'),('值2'),('值3')

4.2、修改数据

update命令

语法：

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

注意 :

column_name 为要更改的数据列
value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果
condition 为筛选条件 , 如不指定则修改该表的所有列数据

where条件子句

可以简单的理解为 : 有条件地从表中筛选数据

测试：

-- 修改年级信息
UPDATE grade SET gradename = '高中' WHERE gradeid = 1;

4.3、删除数据

DELETE命令

语法：

DELETE FROM 表名 [WHERE condition];

注意：condition为筛选条件 , 如不指定则删除该表的所有列数据

-- 删除最后一个数据
DELETE FROM grade WHERE gradeid = 5

TRUNCATE命令

作用：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;

语法：

TRUNCATE [TABLE] table_name;

-- 清空年级表
TRUNCATE[ˈtrʌŋˌkeɪt]grade

注意：区别于DELETE命令

相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
不同 :
- 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器自增
- 使用TRUNCATE TABLE不会对事务有影响（事务后面会说）

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

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

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

5、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}];
   -- 指定查询的记录从哪条至哪条

注意 : [ ] 括号代表可选的 , { }括号代表必选得

5.1、指定查询字段

AS 子句作为别名

作用：

可给数据列取一个新别名
可给表取一个新别名
可把经计算或总结的结果用另一个新名称来代替

语法：select 字段  from  表  
select   *   from   student -- * 代表所有 

别名： AS
select `name` as 姓名 from  student as 学生表

函数：字符串 Concat (a,b)
select concat ('姓名:',`name`) as 新名字 from student

去重： distinct
作用：去除select 查询出结果中重复的数据
-- 查询一下有哪学学生参加了考试 ，成绩
select * from result as 成绩表  --  查询所有学生的成绩
select `studentNO` from result --  查询参加考试的学生
select distinct `studentNO`  from result  -- 发现重复数据，去重

数据库中的表达式 : 一般由文本值 , 列值 , NULL , 函数和操作符等组成
应用场景 :
SELECT语句返回结果列中使用
SELECT语句中的ORDER BY , HAVING等子句中使用
DML语句中的 where 条件语句中使用表达式

select version()  -- 查询系统版本
select 100*3-1 as 计算结果
select @@auto_increment_increment -- 查询自增的步长

-- 学员考试成绩 + 1 分查看
select `syudentNO` , `syudentresult` +1 as '提分后' from result

数据库中的表达式：文本值，列，null, 函数，计算表达式，系统变量 ........

select 表达式 from 表

5.2、where 条件子句

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

逻辑运算符

运算符	语法	描述
and && 与	a and b a&&b	逻辑与，两个都为真，结果为真
or \|\| 或	a or b a\|\| b	逻辑或，其中一个为真，结果为真
Not !	not a ! a	逻辑非，真为假，假为真！

尽量使用英文字母

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null ,结果为真
is not null	a is not null	如果操作符为not null,结果为真
between	a between b and c
like	a like ''	匹配
in	a in ()

--  查询姓刘的同学
--  like 结合  %(代表0到任意个字符)  _ (一个字符)
select `studentNo`,`studentName` from  `stdent`
where`studentName` like '刘%'

注意：

数值数据类型的记录之间才能进行算术运算 ;
相同数据类型的数据之间才能进行比较 ;

5.3、联表查询

5.3.1、连接查询

JOIN On =WHERE :Left Join 、inner join 、Right join

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
left join	即使右表中没有匹配，也从左表中返回所有的行
right join	即使左表中没有匹配，也从右表中返回所有的行

/*
连接查询
   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
   查询两个表中的结果集中的交集
外连接 outer join
   左外连接 left join
       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
   右外连接 right join
       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
-- Join  On 交叉条件  连接查询
-- where 等值连接
  */

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno



SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'

5.3.2、自连接

/*
自连接
   数据表与自身进行连接

需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
    查询父栏目名称和其他子栏目名称
*/

-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

5.4、子查询

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

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,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 subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '高等数学-2'
)

-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
       SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
  )
)

6、分页和排序

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

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC

/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处 : (用户体验,网络传输,查询压力)

推导:
   第一页 : limit 0,5
   第二页 : limit 5,5
   第三页 : limit 10,5
   ......
   第N页 : limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:页码,pageSize:单页面显示条数]
   
*/

-- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10

7、函数

官网：https://dev.mysql.com/doc/

7.1、不常用函数

7.1.1、数值函数

 SELECT ABS(-8);  /*绝对值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
 SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

7.1.2、字符串函数

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/
 SELECT LOWER('KuangShen'); /*小写*/
 SELECT UPPER('KuangShen'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
 SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
 SELECT REVERSE('狂神说坚持就能成功'); /*反转
 
 -- 查询姓周的同学,改成邹
 SELECT REPLACE(studentname,'周','邹') AS 新名字
 FROM student WHERE studentname LIKE '周%';

7.1.3、日期和时间函数

 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());

7.1.4、系统信息函数

 SELECT VERSION();  /*版本*/
 SELECT USER();     /*用户*/

7.2、常用函数

7.2.1、聚合函数

函数名称	描述
COUNT()	返回满足Select条件的记录总和数，如 select count() 【不建议使用，效率低】
SUM()	返回数字字段或表达式列作统计，返回一列的总和。
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值。
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值。

 -- 聚合函数
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推荐*/
 
 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
 -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
 -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
 /*
 很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
 
 下面它们之间的一些对比：
 
 1）在表没有主键时，count(1)比count(*)快
 2）有主键时，主键作为计算条件，count(主键)效率最高；
 3）若表格只有一个字段，则count(*)效率较高。
 */
 
 SELECT SUM(StudentResult) AS 总和 FROM result;
 SELECT AVG(StudentResult) AS 平均分 FROM result;
 SELECT MAX(StudentResult) AS 最高分 FROM result;
 SELECT MIN(StudentResult) AS 最低分 FROM result;

题目：

 -- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
 
 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;
 
 /*
 where写在group by前面.
 要是放在分组后面的筛选
 要使用HAVING..
 因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的
 */

8、数据库级别的MD5加密（扩展）

什么是MD5？

主要增强算法复杂度和不可逆性。

-- 测试MD5 加密
create table `testMD5`(
`id` int(4) not null comment '编号',
`name` varchar(20) not null,
`pwd` varchar(50) not null,
 primary key (`id`)
)ENGINE=InnoDB  Default charset=utf8


-- 明文密码
insert into `testMD5` 
valuse (1,'lisi','123456'),
       (2,'zhangsan','123456'),
       (3,'wangwu','123456')
       
-- 加密
update `testMD5` set pwd=MD5(pwd)-- 加密所有
update `testMD5` set pwd=MD5(pwd) where id= 1 -- 加密第一条

-- 插入的时候加密
insert into `testMD5` 
valuse (4,'xiaoming',MD5('123456'))

-- 如何校验，将用户传递进来的密码，进行MD5加密，然后对比加密后的值

select * from testMD5 where name='lisi' and pwd=MD5('123456')

9、事务

9.1、什么是事务

要么都成功，要么都失败

SQL执行 A给 B 转账 A 1000 ....> 200 B 200
SQL执行 B 收到 A 的钱 A 800 ....> B 400

将一组SQL放在一个批次中去执行

事务原则：ACID

- 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性：事务完成时，必须使所有程序保持一致状态。
- 隔离性：数据库提供隔离机制，保证事务不受外部并发影响在独立环境下运行。
- 持久性：事务一旦提交对数据库中的数据影响是永久的。

隔离级别：
   读到未提交数据
   脏读，不可重复读
   可重读
   串行事务

9.2、执行事务

-- mysql 是默认开启事务自动提交的
set autocommit = 0 /*关闭*/
set autocommit = 1 /*开启*/

--手动处理事务
set autocommit = 0 /*关闭自动条件*/
--事务开启
start transaction[trænˈzækʃ(ə)n]-- 标记一个事务的开始，从这个之后的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'
update `account` set money=money+500 where name = 'B'
-- 一组事务，一个一个执行
commit ; -- 提交事务
rollback; -- 回滚

set autocommit = 1 ;

10、索引

10.1、索引的作用

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
全文检索字段进行搜索优化.

10.2、分类

主键索引 (Primary Key)
唯一索引 (Unique)
常规索引 (Index)
全文索引 (FullText)

10.2.1、主键索引

作用：主键 : 某一个属性组能唯一标识一条记录

特点 :

最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置
创建表时自动创建

10.2.2、唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

主键索引只能有一个
唯一索引可能有多个

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

10.2.3、常规索引

作用 : 快速定位特定数据

注意 :

index 和 key 关键字都可以设置常规索引
应加在查询找条件的字段
不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

10.2.4、全文索引

百度搜索：全文索引

作用 : 快速定位特定数据

注意 :

只能用于MyISAM类型的数据表
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集

10.2.5、语法：

/*
#方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );


#方法二：CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;
                           
                           
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student[表名];
*/

/*增加全文索引*/   
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
 -- 索引名  （列名）
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

10.3、索引的原则

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

10.4、索引数据结构：

哈希表结构：
- 对应哈希索引（Hash索引）：是基于哈希表实现的
B+树结构：
- 对应 B+树索引：所有的记录节点都是按照键值大小的顺序放在叶子节点上
- B+树所引：聚簇索引、非聚簇索引：使用非聚簇索引进行查询时，会先找到主键值，然后到根据聚簇索引找到主键对应的数据域。
- 聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

10.4.1、Hash索引与B+树索引的区别：

哈希索引一般多用于精确的等值查找，B+索引则多用于除了精确的等值查找外的其他查找。

哈希索引不支持排序，因为哈希表是无序的。
哈希索引不支持范围查找。
哈希索引不支持模糊查询及多列索引的最左前缀匹配。
因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点

10.5、无法使用索引的情况：

以“%”开头的LIKE语句，模糊匹配
OR语句前后没有同时使用索引
数据类型出现隐式转换

10.6、索引的底层原理和优化：

B+树，经过优化的B+树
在所有的叶子节点添加指向下一个叶子节点的指针

11、权限管理和备份

11.1、用户管理

SQL命令操作：

用户表：mysql.user

本质：对这张表增删改查

-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  - 只能创建用户，不能赋予权限。
  - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  - 密码也需引号，纯数字密码也要加引号

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

-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示除了给别人授权，其他都能干
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表

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

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

-- 删除用户 DROP USER kuangshen2
DROP USER 用户名

权限解释

-- 权限列表
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    -- 允许授予权限


/* 表维护 */

-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

11.2、备份

为什么要备份：

保证重要数据不丢失
数据转移

MySQL数据库备份方法

使用命令mysqldump备份 -- 命令行下使用Cmd
数据库管理工具,如SQLyog
直接拷贝数据库文件和相关配置文件物理文件Data

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source 备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

12、规范数据库设计

12.1、为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

数据冗余,存储空间浪费
数据更新和插入的异常
程序性能差

良好的数据库设计 :

节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

需求分析阶段: 分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

收集信息
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
标识实体[Entity]
- 标识数据库要管理的关键对象或实体,实体一般是名词
标识每个实体需要存储的详细信息[Attribute]
标识实体之间的关系[Relationship]

12.2、三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

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

三大范式关系型数据库设计：三大范式的通俗理解 - 景寓6号 - 博客园 (cnblogs.com)

第一范式 (1st NF)

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

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询

12.3、SQL 注入漏洞产生的原因？如何防止？

SQL 注入产生的原因：程序开发过程中不注意规范书写 sql 语句和对特殊字符进行过滤，导致客户端可以通过全局变量 POST 和 GET 提交一些 sql 语句正常执行。

防止 SQL 注入的方式：
开启配置文件中的 magic_quotes_gpc 和 magic_quotes_runtime 设置
执行 sql 语句时使用 addslashes 进行 sql 语句转换Sql 语句书写尽量不要省略双引号和单引号。
过滤掉 sql 语句中的一些关键词： update、insert、delete、select、 * 。
提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。

13、优化

MySQL 数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？
1、设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
2、选择合适的表字段数据类型和存储引擎，适当的添加索引。
3、MySQL 库主从读写分离。
4、找规律分表，减少单表中的数据量提高查询速度。5、添加缓存机制，比如 memcached， apc等。
6、不经常改动的页面，生成静态页面。
7、书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

实践中如何优化 MySQL
最好是按照以下顺序优化：
1、SQL 语句及索引的优化
2、数据库表结构的优化
3、系统配置的优化
4、硬件的优化

优化数据库的方法
1、选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL，例如’ 省份’、’ 性别’ 最好适用 ENUM
2、使用连接(JOIN)来代替子查询
3、适用联合(UNION)来代替手动创建的临时表
4、事务处理
5、锁定表、优化事务处理
6、适用外键，优化锁定表
7、建立索引
8、优化查询语句

14、完整性约束

完整性约束包括哪些？
数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。
分为以下四类：
1、实体完整性：规定表的每一行在表中是惟一的实体。
2、域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
3、参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
4、用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语
义要求。
与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE)

15、JDBC （重点）

15.1、导包

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

15.2、第一个JDBC程序

创建测试数据库

create database `jdbcStudy` character set utf8 collate utf8_general_ci;
use `jdbcStudy`;
create table `users`(
`id` int(20) not null,
`name` varchar(30) default null,
`pwd`varchar(30),
`email`varchar(60),
`birthday` date,
primary key (`id`)    
)engine=innoDB default charset=utf8;

insert into users(id,name,pwd,email,birthday)
values
(1,'zhansan','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、导入数据库驱动：Add as Library 添加为库

3、编写测试代码

package com.kuangshen.jdbcDemo;

import java.sql.*;

public class FirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

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

        //3.连接成功，数据库对象 ：DriverManager
        Connection connection = DriverManager.getConnection(url, name, password);

        //4.创建sql对象 Statement
        Statement statement = connection.createStatement();

        //5.执行SQL语句
        String sql ="select * from users";

        //6.返回结果
        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("pwd"));
            System.out.println("email="+ resultSet.getObject("email"));
            System.out.println("birthday="+ resultSet.getObject("birthday"));


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

}

步骤总结：

1、加载驱动

2、连接数据库DriverManager

3、获得执行sql的对象 Statement

4、获得返回的结果集

5、释放连接

        //5.执行SQL语句
        String sql ="select * from users";

        //6.返回结果
        ResultSet resultSet = statement.executeQuery(sql); //查询操作返回 ResultSet
        statement.execute();//执行任何sql
        statement.executeUpdate();//更新、插入、删除，都有这个，返回一个受影响的行数

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

//mysql----3306
//协议：//主机地址：端口号/数据库？参数1&参数2&参数3
//oracle----1521
//jdbc:oracle:thin:@localhost:1521:sid

  //3.连接成功，数据库对象 ：DriverManager
  Connection connection = DriverManager.getConnection(url, name, password);
  
  //事务处理
 // connection.rollback();
 // connection.commit();
//  connection.setAutoCommit();

while(resultSet.next()){
    System.out.println("id="+ resultSet.getObject("id"));
    System.out.println("name="+ resultSet.getObject("name"));
    System.out.println("pwd="+ resultSet.getObject("pwd"));
    System.out.println("email="+ resultSet.getObject("email"));
    System.out.println("birthday="+ resultSet.getObject("birthday"));


}
//ResultSet 查询的结果集：封装了所有的查询结果
//获得指定的数据类型
//resultSet.getString();
//resultSet.getInt();
//resultSet.getFloat();
//resultSet.getObject();//不知道类型

遍历，指针 resultset.
  beforeFirst();//移动到最前面
  afterLast();//移动到最后面
  next();//移动到下一个数据
  previous();//移动到前一行
  absolute(row);//移动到指定行

15.3、Statement对象

提取工具类

package com.kuang.jdbcDemo02.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 (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

查

package com.kuang.jdbcDemo02;

import com.kuang.jdbcDemo02.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(); //获得sql的执行对象
            String sql ="select * from users where id =1";

            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println("name:"+rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}

删

package com.kuang.jdbcDemo02;

import com.kuang.jdbcDemo02.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.kuang.jdbcDemo02;

import com.kuang.jdbcDemo02.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,pwd,email,birthday)"+
                    "VALUES (4,'kuangshen','123456','ks@sina.com','1988-12-04')";

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

改

package com.kuang.jdbcDemo02;

import com.kuang.jdbcDemo02.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 ='kuangshen' 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);
        }
    }
}

15.4、Sql注入的问题

package com.kuang.jdbcDemo02;

import com.kuang.jdbcDemo02.utils.JdbcUtils;

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

public class SQL {
    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;
            Statement st = null;
            ResultSet rs = null;
            try {
                conn = JdbcUtils.getConnection(); //获取数据库连接
                st = conn.createStatement(); //获得sql的执行对象
                String sql = "select * from users where name='" + username + "'and pwd ='" + password + "'";

                rs = st.executeQuery(sql);
                while (rs.next()) {
                    System.out.println("name:" + rs.getString("name"));
                    System.out.println("pwd:" + rs.getString("pwd"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(conn, st, rs);
            }
        }

}

15.5、PreparedStatement对象

防止SQL注入

增

package com.kuang.jdbcDemo03;

import com.kuang.jdbcDemo02.utils.JdbcUtils;

import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {


        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;



        try {
           conn = JdbcUtils.getConnection();

           //使用？占位符
           String sql = "INSERT INTO users(id,NAME,pwd,email,birthday)values (?,?,?,?,?)";

            st = conn.prepareStatement(sql);//预编译sql,先写sql，不执行

            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"zhangsan");
            st.setString(3,"123456");
            st.setString(4,"ks@sin.com");
            //注意点 sql.date 数据库类型      new java.util.Date().getTime()  获得时间戳
            //      java.util.Date  java类型
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

           //执行sql
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

改

package com.kuang.jdbcDemo03;

import com.kuang.jdbcDemo02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {


        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;



        try {
            conn = JdbcUtils.getConnection();

            //使用？占位符
            String sql = "update users set email ='ks@sina.com' where id =?";

            st = conn.prepareStatement(sql);//预编译sql,先写sql，不执行

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

            //执行sql
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("修改成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

删

package com.kuang.jdbcDemo03;

import com.kuang.jdbcDemo02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {


        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;



        try {
            conn = JdbcUtils.getConnection();

            //使用？占位符
            String sql = "DELETE FROM users WHERE id =?";

            st = conn.prepareStatement(sql);//预编译sql,先写sql，不执行

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


            //执行sql
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

查

package com.kuang.jdbcDemo03;

import com.kuang.jdbcDemo02.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); //第一步：预编译sql

            st.setInt(1,1); //第三步：传递参数

            // 第四步：执行sql
            rs = st.executeQuery();

            //第五步
            if(rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入

package com.kuang.jdbcDemo03;

import com.kuang.jdbcDemo02.utils.JdbcUtils;

import java.sql.*;

public class SQL {
    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(); //获取数据库连接

            String sql = "select * from users where name=? and pwd =?";//编写sql

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

            //传递参数
            st.setString(1,username);
            st.setString(2,password);
            //执行sql
            rs = st.executeQuery();

            while (rs.next()) {
                System.out.println("name:" + rs.getString("name"));
                System.out.println("pwd:" + rs.getString("pwd"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }

}

15.6、IDEA连接数据库

绿色箭头提交

15.7、事务

package com.kuang.jdbcDemo04;

import com.kuang.jdbcDemo02.utils.JdbcUtils;

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

public class TestTransaction {
    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 acount set money=money-500 where name='A'";
            st= conn.prepareStatement(sql1);//预编译sql
            st.executeUpdate();

            int x= 1/0; //报错

          String sql2 ="update acount set money=money+500 where name='B'";
            st = conn.prepareStatement(sql2);//预编译sql
            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);
        }
    }
}

15.8、数据连接池

导jar包

数据库连接–执行完毕–释放
连接–释放十分浪费资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
常用连接数 100
最少连接数：100
最大连接数： 120 业务最高承载上限
排队等待，
等待超时：100ms
编写连接池，实现一个接口 DateSource

开源数据源实现(拿来即用)

DBCP

C3P0

Druid: 阿里巴巴

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

15.8.1、DBCP

dbcpconfig.properties


#连接设置,这里面的名字是DBCP数据源中定义好的
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8useSSL=false

username=root
password=root
#<!-- 初始化连接-->
initialSize=10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待时间以毫秒为单位/s
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意：“user” 与 “password” 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

JdbcUtils_DBCP工具类

package com.thb.study4.utils;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

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

/**
 * @Author 天红
 * @Date 2021/7/23 14:28
 * @Version 1.0
   */
   public class JdbcUtils_DBCP {
   private static DataSource dataSource=null;

   static {
       try {
           InputStream is = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
           Properties properties = new Properties();
           properties.load(is);
    //创建数据源 工厂模式-->创建
    dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
    e.printStackTrace();
 }
}
//2.获取连接
public static Connection getConnection() throws SQLException {
    return dataSource.getConnection();//从数据源中获取连接
}
//3.释放资源
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();
        }
    }
}

}

DBCP测试代码

package com.thb.study4;

import com.thb.study2.Utils.JdbcUtils;
import com.thb.study4.utils.JdbcUtils_DBCP;

import java.sql.*;

/**

 * @Author 天红
 * @Date 2021/7/24 21:33
 * @Version 1.0
   */
   public class TestDBCP {
   public static void main(String[] args) {
       Connection conn = null;
       PreparedStatement ps = null;
       ResultSet rs = null;
try {
    conn = JdbcUtils_DBCP.getConnection();
    String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
    ps = conn.prepareStatement(sql);

    ps.setInt(1, 24);
    ps.setString(2, "无言");
    ps.setString(3, "999");
    ps.setString(4, "th@hhh.com");
    ps.setDate(5, new Date(new java.util.Date().getTime()));

    int i = ps.executeUpdate();
    if (i > 0) {
        System.out.println("插入成功o(*////▽////*)q");
    }

} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JdbcUtils_DBCP.release(conn, ps, rs);
   }
  }
}

15.8.2、C3P0

jar包看上图 --> c3p0和mchange开头的

c3p0-config.xml配置文件（名字不要变）

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
<!--
C3P0的缺省（默认）配置，
如果在代码中是    ComboPooledDataSource cp = new ComboPooledDataSource();  这样写是default-config的配置
        -->
        
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/student?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">root</property>
    
    
    
    <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
    <property name="acquireIncrement">3</property>

    <!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 10 这些都是自己定义 -->
    <property name="initialPoolSize">10</property>

    <!-- 连接的最大空闲时间，default: 30  -->
    <property name="maxIdleTime">30</property>

         <!--连接池中保留的最大连接数。Default: 100 -->
    <property name="maxPoolSize">100</property>

    <!--连接池中保留的最小连接数。Default: 15 -->
    <property name="minPoolSize">15</property>
</default-config>
<!--
C3P0的命名配置，
如果在代码中是    ComboPooledDataSource cp = new ComboPooledDataSource("MySQL");  这样写是default-config的配置
-->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/student?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="root">root</property>

        <property name="acquireIncrement">3</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </named-config>

<!-- 所以下面想什么配置就写什么配置   -->
</c3p0-config>

工具类

package com.thb.study4.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

/**

 * @Author 天红

 * @Date 2021/7/23 14:28

 * @Version 1.0
   */
   public class JdbcUtils_C3P0 {
   private static ComboPooledDataSource dataSource=null;

   static {
       try {

/*            //代码配置，麻烦不用
           dataSource = new ComboPooledDataSource();
           dataSource.setDriverClass("com.mysql.jdbc.Driver");
           dataSource.setUser("root");
           dataSource.setPassword("root");
           dataSource.setMaxPoolSize(100);
           dataSource.setMinPoolSize(10);*/
//      配置文件的配法，我们只要导入xml文件，写个一句就好了
      dataSource= new ComboPooledDataSource("MySQL");
     
     
     } catch (Exception e) {
         e.printStackTrace();
    }

}

//2.获取连接
public static Connection getConnection() throws SQLException {

        return dataSource.getConnection();//从数据源中获取连接

}

//3.释放资源
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();
        }
    }
 }
}

测试C3P0类

package com.thb.study4;

import com.thb.study4.utils.JdbcUtils_C3P0;
import com.thb.study4.utils.JdbcUtils_DBCP;

import java.sql.*;

/**

 * @Author 天红
 * @Date 2021/7/24 21:33
 * @Version 1.0
   */
   public class TestC3P0 {
   public static void main(String[] args) {
       Connection conn = null;
       PreparedStatement ps = null;
       ResultSet rs = null;
try {
    conn = JdbcUtils_C3P0.getConnection();
    String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
    ps = conn.prepareStatement(sql);

    ps.setInt(1, 27);
    ps.setString(2, "我爱你");
    ps.setString(3, "1314");
    ps.setString(4, "804328977@qq.com");
    ps.setDate(5, new Date(new java.util.Date().getTime()));

    int i = ps.executeUpdate();
    if (i > 0) {
        System.out.println("插入成功o(*////▽////*)q");
    }

} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JdbcUtils_DBCP.release(conn, ps, rs);
}
}
}

posted @ 2023-01-22 13:38 雪断残阳阅读(2) 评论(0) 收藏举报

刷新页面返回顶部

lou-xdcy