1、初识MySql
javaee :企业级java开发 Web
前端(页面:展示,数据!)
后台(连接点:连接数据库JDBC,链接前端(控制,控制视图跳转,和给前端传递数据))
数据库(存数据,Txt,Excel,Word)
1.1、为什么要学习数据库
数据库是所有软件体系中最核心的存在DBA
1.2、什么是数据库
数据库(DB,DataBase)
概念:数据仓库,软件,安装在操作系统(window、Linux、mac…)之上!SQL,可以存储大量的数据。
作用:存储数据,管理数据
1.3、数据库分类
关系型数据库:(SQL)
- MySQL、Oracle、Sql Server、DB2、SQLLite
- 通过表和表之间,行和列之间的关系进行数据的存储,学员信息表,考勤表…
非关系型数据库:(NoSQL) Not Only
- Redis|MongDB
- 非关系型数据库,对象存储,通过对象的自身的属性来决定
DBMS(数据库管理系统)
- 数据库的管理软件,管理和操作数据库
- MySQL,数据库管理系统!
1.4、MYSQL简介
MYSQL是一个关系型数据库管理系统
数据库基本操作
增删改查
CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci;
#创建表
CREATE TABLE `school`.`students`(
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学员id', #表属性
`name` VARCHAR(100) NOT NULL COMMENT '学员姓名',
`age` INT(3) NOT NULL COMMENT '学员年龄',
PRIMARY KEY (`id`) #主键
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; #字符集
SHOW TABLE STATUS FROM `school` LIKE 'students';
#添加表数据
#INSERT INTO表名(字段名1,字段名2,…)VALUES (值1,值2,…);
INSERT INTO `students` (`id`, `name`, `age`) VALUES ('1', '小白', '3');
INSERT INTO `students` (`id`, `name`, `age`) VALUES ('2', '小明', '5');
#删除表的三种方式
DROP TABLE `students`;#删除表 drop 是直接删除表信息,速度最快,但是无法找回数据
TRUNCATE TABLE `students`;#truncate 是删除表数据,不删除表的结构,速度排第二,但不能与where一起使用
DELETE FROM `students`;#delete 是删除表中的数据,不删除表结构,速度最慢,但可以与where连用,可以删除指定的行
DELETE FROM `students` WHERE id=1; #删除表中id为1的用户
#三种区别
#希望删除表结构时,用 drop;
#希望保留表结构,但要删除所有记录时, 用 truncate;
#希望保留表结构,但要删除部分记录时, 用 delete。
-- 修改表名: ALTER TABLE 旧表名 RENAME AS 新表名
-- 增加表的字段: ALTER TABLE 表名 ADD 字段名 列属性
-- 修改约束:ALTER TABLE 表名 MODIFY 字段名 列属性[]
-- 字段重名: ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
-- 删除表的字段:ALTER TABLE 表名 DROP 字段名
#更新表数据
-- UPDATE表名
-- SET字段名1=值1[字段名2=值2,…]
-- [WHERE条件表达式];
#更新全部数据
#当UPDATE语句中不使用WHERE条件语句时,会将表中所有数据的指定字段全部更新。
例:
#UPDATE admin SET a_sex ='男',a_phone='1111';
UPDATE students SET `name`='小洪',age=1;
#更新部分数据
#前面讲解了更新全部数据的方法,这种需求在实际开发中…般比较少,大多数需求 是更新表中的部分数据,使用WHERE B句可以指定更新数据的条件。
#例:
#UPDATE admin SET a_sex='女'
#WHERE a_name='LI';
UPDATE students SET `name`='小明'
WHERE age='1';
#CREATE DATABASE IF NOT EXISTS day01d; --如果不存在创建数据库
# SHOW DATABASES; --查询数据库
# SHOW CREATE DATABASEday01d;-- 展示数据库day01d创建语句
# ALTER DATABASE CHARACTER SET gbk;-- 修改数据库
# DROP DATABASE IF EXISTS day01d;-- 删除数据库
# USE day01;-- 使用库
# SELECT DATABASE();-- 查看当前使用的数据库
# CREATE TABLE stu03 LIKE stu02;-- 复制表的目录结构
#SHOW TABLES;--查看创建的所有表
#SHOW CREATE TABLE stu02;-- 查看stu02的创建语句
#DESC stu07;--查询表结构
#SELECT * FROM stu02; -- 查看表的所有数据
1.5、连接数据库
命令行连接!
mysql -uroot -proot -- 连接数据库
updata mysql.user set authentication_string=password('123456') where user = 'root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
所有的语句都使用;结尾
show databases; -- 查看全部数据库
user school -- 切换数据库use
Database changed
--
show tables;-- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息
create database westos; -- 创建一个数据库
exit;-- 退出连接
-- 单行注释(SQL的本来注释)
/* (sql的多行注释)
*/
数据库***语言 CRUD 增删改查! CV程序员 API程序员 CRUD程序员!(业务程序员)
DML 操作
DDL 定义
DQL 查询
DCL 控制
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
2.1、操作数据库(了解)
关键字不区分大小写
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] westos
2、删除数据库
DROP DATABASE [IF EXISTS] westos
3、使用数据库
use `school`
4、查看数据库
SHOW DATABASES -- 查看所有的数据库
对比:SQLyog的可视化操作
学习思路:
-
对照sqlyog可视化历史记录查看sql
-
固定的语法或关键字必须要强行记住!
2.2、数据库的列类型
数值
-
tinyint 十分小的数据 1个字节
-
smallint 较小的数据 2个字节
-
mediumint 中等大小的整数 3个字节
-
int 标准的整数 4个字节(常用的 int)
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节(精度问题!)
-
decimal 字符串形式的浮点数 金融计算的时候,一般使用decimal
字符串
- char 字符串固定大小的 0-255
- varchar 可变字符串 0-65535(常用的变量) String(java中)
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保存大文本
时间日期
java.util.Date
-
date YYYY-MM-DD, 日期格式
-
time HH:mm:ss 时间格式
-
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
-
timestamp 时间戳 1970.1.1 到现在的毫秒数!较为常用
-
year 年份表示
null
- 没有值,未知
- 注意,不要使用NULL进行运算,结果为NULL
2.3、数据库的字段属性(重点)
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill:
- 0填充的
- 不足的位数,使用0来填充, int(3),5……005
自增:
- 通常理解为自增,自动在上一条的记录的基础上+1(默认)
- 通常用来设计唯一的主键~ index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null:
- 假设设置为not null,如果不给它赋值,就会报错!
- NULL,如果不填写值,默认就是null
默认:
- 设置默认值!
- sex,默认值为男,如果不指定该列的值,则会使用默认值
拓展:
/*每个表,都必须存在以下五个字段!未来做项目用的,表示一个记录存在的意义
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4、创建数据库表(重点)
-- 目标:创建一个school数据库
-- 创建学生表(列,字段) 使用SQL创建
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 注意点:使用英文(),表的名称 和 字段 尽量使用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加 , (英文的),最后一个不用加
-- PRIMARY KEY 主键,一般一个表只有唯一的主键!
CREATE TABLE IF NOT EXISTS `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 DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式:
CREATE DATABASE [IF NOT EXISTS] `表名`(
`字段名` 列类型[属性] [索引] [注释],
`字段名` 列类型[属性] [索引] [注释]
`字段名` 列类型[属性] [索引] [注释]
…………
`字段名` 列类型[属性] [索引] [注释]
)[表类型][字符集设置]
常用命令
SHOW CREATE DATABASE school -- 查看看见数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
2.5、数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规的使用操作:
- MYISAM:节约空间,速度较快
- INNODB :安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据文件都存在data目录下
本质还是文件的存储!
MySQL引擎在物理文件上的区别
-
InnoDB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
-
MYISAM对应文件
*.frm ~表结构的定义文件
*.MYD 数据文件(data)
*.MYI 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会是mysql默认的字符集编码~(不支持中文!)
MySQL的默认编码是Latin1,不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
2.6、修改删除表
修改
-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS students
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE students ADD age INT(11)
-- 修改表的字段(重命名 CHANGE,修改约束 MODIFY !)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE students MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE students CHANGE age age1 INT(11) -- 字段重命名
-- change用来字段重命名,不能修改字段类型和约束;
-- modify不用来字段重命名,只能修改字段类型和约束;
-- 删除表的字段:ALTER TABLE 表名 DROP 字段名
ALTER TABLE students DROP age1
删除
-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS students
所有的创建和删除操作尽量加上判断,以免报错
注意点:
- `` 反单引号:字段名,使用这个包裹!
- 注释 – /**/
- sql关键字大小写不敏感,建议写小写!
- 所有的符号全部用英文!
3、MySQL数据管理
3.1、外键(了解即可)
方式一:在创建表的时候,增加约束(麻烦,比较复杂)
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 注意点:使用英文(),表的名称 和 字段 尽量使用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加 , (英文的),最后一个不用加
-- PRIMARY KEY 主键,一般一个表只有唯一的主键!
-- 学生表的 gradeid 字段,要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用)REFERENCES references 引用
CREATE TABLE IF NOT EXISTS `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 DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
删除有外键关系的表的时候,必须先删除引用别人的表(从表),再删除被引用的表(主表)
方式二:创建表成功后,添加外键约束
-- 目标:创建一个school数据库
-- 创建学生表(列,字段) 使用SQL创建
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
CREATE DATABASE school
USE school
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 注意点:使用英文(),表的名称 和 字段 尽量使用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加 , (英文的),最后一个不用加
-- PRIMARY KEY 主键,一般一个表只有唯一的主键!
-- 学生表的 gradeid 字段,要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用)REFERENCES references 引用
CREATE TABLE IF NOT EXISTS `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 DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表(哪个字段)
SHOW CREATE DATABASE school -- 查看看见数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS students
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE students ADD age INT(11)
-- 修改表的字段(重命名 CHANGE,修改约束 MODIFY !)
ALTER TABLE students MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE students CHANGE age age1 INT(11) -- 字段重命名
-- change用来字段重命名,不能修改字段类型和约束;
-- modify不用来字段重命名,只能修改字段类型和约束;
-- 删除表的字段
ALTER TABLE students DROP age1
-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS students
以上的操作都是咱们的物理外键,数据库级别的外键,我们不建议使用!(避免数据库过多造成困扰,这里了解即可~)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
[强制]不得使用外键和级联,一切外键概念必须在应用层解决。
每次做DELETE或者UPDATE都必须考虑外键约束,会导致开发的时候很痛苦,测试数据级为不方便。
3.2、DML语言(全部记住)
**数据库意义:**数据存储,数据管理
DML语言:数据操作语言
- Insert
- Update
- delete
3.3、添加
insert
-- 插入语句(添加)
-- insert into 表名([字段名1,字段2,字段3])values('值1'),('值2'),('值3'),(…………)
INSERT INTO `grade`(`gradename`)VALUES('大四')
-- 由于主键自增,我们可以省略(如果不写表的字段,他就会一一匹配)
INSERT INTO `grade` VALUES('大三')
-- 一般写插入语句,我们一定要数据和字段一一对应!
-- 插入多个字段
INSERT INTO `grade`(`gradename`)
VALUES('大二'),('大一')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('张三','aaaaa','男')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('李四','aaaaa','男'),('王五','aaaaa','男')
语法:insert into 表名([字段名1,字段2,字段3])values(‘值1’),(‘值2’),(‘值3’),(…………)
注意事项:
1、字段和字段之间使用 英文逗号 隔开
2、字段是可以省略的,但是后面的值必须要一一对应
3、可以同时插入多条数据,VALUES后面的值,需要使用,隔开即可 VALUES(),(),……
3.4、修改
update 修改谁 (条件) set 原来的值=新值
-- 修改学员名字,带了简介
UPDATE `student` SET `name`='狂神' WHERE id = 1;
-- 不指定条件的情况下,会改动所有的表!
UPDATE `student` SET `name`='长江七号'
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='狂神',`email`='1228905805@qq.com' WHERE id = 1;
-- 语法:
-- UPDATE 表名 set colnum_name = value,[colnum_name = value……] where [条件]
条件:where子句 运算符 id等于某个值,在某个区间内修改……
操作符会返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或!= | 不等于 | 5<>6 | true |
> | 大于 | 5>6 | false |
< | 小于 | 5<6 | true |
<= | 小于等于 | 5<=6 | true |
>= | 大于等于 | 5>=6 | false |
BETWEEN ……and…… | 在某个范围内 | [2,5] | |
AND | 我和你 && | 5>1&&1>2 | false |
OR | 我或你 || | 5>1&&1>2 | true |
-- 通过多个条件定位数据,无上限!
UPDATE `student` SET `name`='长江七号' WHERE `name`='狂神11' AND `sex`='女'
语法:UPDATE 表名 set colnum_name = value,[colnum_name = value……] where [条件]
注意:
- colnum_name 是数据库的列,尽量带上``
- 条件,筛选的条件,如果没有指定,则会修改所有的列
- value,是一个具体的值,也可以是一个变量(时间)(CURRENT_TIME)
- 多个设置的属性之间,使用英文逗号隔开
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='狂神11' AND `sex`='女'
3.5、删除
delete 命令
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样写,会删除全部)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id = 1;
TRUNCATE 命令
作用:完全清空一个数据库表,表的结构和索引不会变
-- 清空 student 表
TRUNCATE `student`
delete from 和 TRUNCATE的区别
- 相同点:是都能删除数据,都不删除表结构
- 不同:
1)TRUNCATE 重新设置 自增列 计数器会归零
2)TRUNCATE 不会影响事务
-- 测试delete 和 TRUNCATE 区别
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('1'),('2'),('3')
DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零
了解即可:DELETE删除的问题,重启数据库,现象
- InnoDB 自增列会从1开始(存在内存当中的,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)
4、DQL查询数据(最重点)
4.1、DQL(Data Query Language)
数据查询语言:
- 所有的查询操作都用它:Select
- 简单的查询,复杂的查询它都能做
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
SELECT
[ALL | DISTINCT | DISTINROW ]
[HIGH_PRORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr[,select_expr....]
[FROM table_references
[PRATITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name| expr | posistion}
[ASC | DESC],...[WITH ROLLUP]]
[HAVING where_condition]
GROUP BY {col_name| expr | posistion}
[ASC | DESC],...]
[LIMIT {[offset,]row_count|row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [,var_name]]
[FOR UPDATE | LOCK IN SHARE MOOE]]
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`StudentNo` INT(4) NOT NULL COMMENT '学号',
`LoginPwd` VARCHAR(20) DEFAULT NULL,
`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
`GradeId` INT(11) DEFAULT NULL COMMENT '年级编号',
`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
`Address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
`Email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`StudentNo`),
UNIQUE KEY `IdentityCard`(`IdentityCard`),
KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`GradeId` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`GradeId`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`SubjectNo`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
`GradeId` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`SubjectNo`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`StudentNo` INT(4) NOT NULL COMMENT '学号',
`SubjectNo` INT(4) NOT NULL COMMENT '课程编号',
`ExamDate` DATETIME NOT NULL COMMENT '考试日期',
`StudentResult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `SubjectNo` (`SubjectNo`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
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 ....]-- 指定查询记录按一个或多个条件排序
[LINIT {[offset,]row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
注意:[]括号代表可选的,{}括号代表必选得
4.2、指定查询字段
-- 查询所有的学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM `student`
-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生名字 FROM `student` AS s
-- 函数 Concat(a,b) 拼接字符串
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
语法:SELECT 字段… FROM 表
有的时候,列名字不是那么的见名知意,我们起别名 AS 字段名 as 别名 表名 as 别名
去重 distinct
作用:去除SELECT查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询一下有哪些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result -- 发现重复数据,去重
数据库的列
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 用来查询自增的步长(变量)
-- 学员考试成绩+1分查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
数据库中的表达式:文本值、列、Null、函数、计算表达式、系统变量…
select 表达式
from 表
4.3、where条件子句
作用:检索数据中符合条件
的值
搜索的条件由一个或者多个表达式组成,返回结果 布尔值
逻辑运算符
运算符 | 语法 | 结果描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真,则结果为真 |
Not ! | not a !a | 逻辑非,真为假,假为真! |
尽量使用英文符号
-- ==================== where =====================
SELECT StudentNo,`StudentResult` FROM result
-- 查询考试成绩在95-100分之间
SELECT StudentNo,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100
-- and &&
SELECT StudentNo,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100
-- 模糊查询(区间)
SELECT StudentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT StudentNo,`StudentResult` FROM result
WHERE StudentNo!=1000;
-- != not
SELECT StudentNo,`StudentResult` FROM result
WHERE NOT StudentNo = 1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为NULL,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为null,结果为真 |
BETWEEN | a between b and c | 若a在b和c之间,则结果为真 |
Like | a like b | SQL匹配,如果a匹配b,则结果为真 |
In | a in(a1,a2,a3......) | 假设a在a1,或者a2之间,返回的结果为真 |
-- ==================== 模糊查询 =====================
-- =====LIKE======
-- 查询姓刘的同学
-- 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 (具体的一个或者多个值)=====
-- 查询1001,1002,1003号学员信息
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN(1001,1002,1003);
-- 查询在北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('安徽','河南洛阳');
-- =====null not null======
-- 查询地址为空的学生, null''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address`='' OR `Address` IS NULL
-- 查询有出生日期的同学, 不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL
-- 查询没有出生日期的同学, 不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL
4.4、联表查询
JOIN 对比
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-96m7cr4S-1638627684755)(C:/Users/lei/AppData/Roaming/Typora/typora-user-images/image-20211125211518617.png)]
-- ================ 联表查询 ======================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
SELECT * FROM result
/*思路:
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中,哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表 studentNo
*/
-- Inner Join
SELECT s.studentNo,studentName,SubjectNo,`StudentResult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo
-- Right Join
SELECT s.studentNo,studentName,SubjectNo,`StudentResult`
FROM student s
RIGHT JOIN result r
ON s.studentNo=r.studentNo
-- Left Join
SELECT s.studentNo,studentName,SubjectNo,`StudentResult`
FROM student s
LEFT JOIN result r
ON s.studentNo=r.studentNo
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
-- ================ 联表查询 ======================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
SELECT * FROM result
/*思路:
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中,哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表 studentNo
*/
-- join(连接的表) on (判断条件) 连接查询
-- where 等值查询
-- Inner Join
SELECT s.studentNo,studentName,SubjectNo,`StudentResult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo
SELECT s.studentNo,studentName,SubjectNo,`StudentResult`
FROM student AS s
INNER JOIN result AS r
ON s.studentNo=r.studentNo
-- Right Join
SELECT s.studentNo,studentName,SubjectNo,`StudentResult`
FROM student s
RIGHT JOIN result r
ON s.studentNo=r.studentNo
-- Left Join
SELECT s.studentNo,studentName,SubjectNo,`StudentResult`
FROM student s
LEFT JOIN result r
ON s.studentNo=r.studentNo
-- 查询缺考的同学
SELECT s.studentNo,studentName,SubjectNo,`StudentResult`
FROM student s
LEFT JOIN result r
ON s.studentNo=r.studentNo
WHERE StudentResult IS NULL
-- 思考题(查询了参加考试的同学信息:学号,姓名,科目编号,分数)
/*思路:
1.分析需求,分析查询的字段来自哪些表,student、result、subject(连接查询)
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中,哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表 studentNo
*/
SELECT s.studentNo,studentName,SubjectName,`StudentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo = s.studentNo
INNER JOIN `subject` sub
ON r.subjectNo = sub.SubjectNo
-- 我要查询哪些数据 select ....
-- 从哪几个表中查 FROM 表 XXX Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加
-- from a left join b
-- from a right join b
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类的关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 查询父子信息:把一张表看作一模一样的表
SELECT a.`categoryName` AS '夫栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
-- ================ 自连接 ======================
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','办公信息');
-- 查询父子信息:把一张表看作一模一样的表
SELECT a.`categoryName` AS '夫栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
-- 查询学员所属的年级 (学号,学生的姓名,年级名称)
SELECT studentNo,studentName,`GradeName`
FROM student s
INNER JOIN `grade` g
ON s.`GradeId` = g.`GradeId`
-- 查询科目所属于的年级(科目名称,年级名称)
SELECT `SubjectName`,`GradeName`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`GradeId`=g.`GradeId`
-- 思考题(查询了参加 数据库结构-1 考试的同学信息:学号,姓名,科目编号,分数
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`StudentNo`=sub.`SubjectNo`
WHERE subjectName = '数据库结构-1'
4.5、分页和排序
排序
-- =================== 分页 limit 和 排序 order by =====================
-- 排序:升序ASC,降序DESC
-- 查询的结果 成绩降序 排序
-- ORDER BY 通过哪个字段排序,怎么排
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`StudentNo`=sub.`SubjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC
分页
-- 100万
-- 为什么要分页
-- 缓解数据库压力,给人的体验更好 瀑布流
-- 分页 ,每页只显示五条数据
-- 语法:limit 起始页,页面的大小
-- 网页应用: 当前,总的页数,页面的大小
-- LIMIT 0,5 1-5
-- LIMIT 1,5 2-6
-- LIMIT 6,5 1-5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`StudentNo`=sub.`SubjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC
LIMIT 1,5
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第n页 limit ,5 (n-1)*pageSize,pageSize
-- 【pageSize :页面大小】
-- 【(n-1)*pageSize起始值】
-- 【n:当前页】
-- 【数据总数/页面大小 = 总页数】
语法: limit(查询起始值下标,pageSize)
-- 思考:
-- 查询java第一学年 课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程名称。分数)
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
ON sub.`SubjectNo`=r.`SubjectNo`
WHERE StudentName='java第一学年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10
4.6、子查询
where(值是固定的,这个值是计算出来的)
本质:在where子句中嵌套一个子查询语句
where(select * from)
-- =================== where =====================
-- 1、查询数据库结构-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 StudentNo = (
SELECT `StudentNo`
FROM `subject`
WHERE `SubjectName`='数据库结构-1'
)
ORDER BY StudentResult DESC
-- 查询所有数据库结构-1的学生学号
SELECT `StudentNo` FROM `subject` WHERE `SubjectName`='数据库结构-1'
-- 分数不小于80的学生的学号和姓名(学号,姓名,分数)
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo=s.StudentNo
WHERE `StudentResult`>=80
-- 在这个基础上增加一个科目,高等数学-2
-- 查询 高等数学-2的编号
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo=s.StudentNo
WHERE `StudentResult`>=80 AND `SubjectNo` =
(
SELECT `SubjectNo` FROM `subject`
WHERE `SubjectName` ='高等数学-2'
)
-- 查询课程为高等数学-2且分数不小于80的学号和姓名
SELECT StudentNo,StudentName
FROM `student` s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`='高等数学-2' AND `StudentResult`>=80
-- 在改造(由里及外)
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN(
SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo = {
SELECT SubjectNo FROM `subject` WHERE `SubjectName` ='高等数学-2'
}
}
-- 查询C语言-1 前5名同学的成绩的信息(学号,姓名,分数)
-- 使用子查询
4.7、分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:根据不同的课程分组
SELECT `SubjectName`,AVG(`StudentResult`) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通过什么字段来分组
HAVING 平均分>80
4.8、Select小结
顺序很重要:
select 去重 要查询的字段 from 表 (注意:表和字段可以取别名)
××× join 要连接的表 on 等值判断
where(具体的值,子查询语句)
Group By (通过哪个字段来分组)
Having (过滤分组后的信息,条件和where是一样的,位置不同)
Order By …… (通过哪个字段排序)(升序|继续)
Limit startindex ,pagesize
业务层面:
查询、跨表、跨数据库……
5、MySQL函数
5.1、常用函数
-- ====================== 常用函数 =============================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0-1的随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1,正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','爱','你们') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查找,替换
SELECT LOWER('KuangShen') -- 全部替换成小写
SELECT UPPER('KuangShen') -- 全部替换成大写
SELECT INSTR('kuangshen','h') -- 返回第一次出现子串的索引
SELECT REPLACE('狂神说坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6) -- 返回指定的子字符串(源字符串,截取的位置,截取的长度)
SELECT REVERSE('清晨我上马') -- 反转
-- 查询姓 周的同学,名字 换为邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间和日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 获取系统时间
SELECT YEAR(NOW()) -- 获取年
SELECT MONTH(NOW()) -- 获取月
SELECT DAY(NOW()) -- 获取日
SELECT HOUR(NOW()) -- 获取小时
SELECT MINUTE(NOW()) -- 获取分钟
SELECT SECOND(NOW()) -- 获取秒
-- 系统
SELECT SYSTEM_USER() -- 获取用户名
SELECT USER() -- 获取用户名
SELECT VERSION() -- 获取sqlyog版本
5.2、聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
…… |
-- ===== 聚合函数 ========
-- 都能够统计 表中的数据(想查询一个表中有多少个记录,就使用这个count())
SELECT COUNT(studentname) FROM student; -- Count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student; -- COUNT(*) 不会忽略null值 本质 计算行数
SELECT COUNT(1) FROM student; -- COUNT(1) 不会忽略所有的null值 本质 计算行数
SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均分 FROM result
SELECT MAX(`StudentResult`) AS 最高分 FROM result
SELECT MIN(`StudentResult`) AS 最低分 FROM result
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:根据不同的课程分组
SELECT `SubjectName`,AVG(`StudentResult`) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通过什么字段来分组
HAVING 平均分>80
5.3、数据库级别的MD5加密(扩展)
什么是MD5?
主要增强算法复杂度和不可逆性
MD5 不可逆,具体的值md5是一样的
MD5 破解网站的原理,背后有一个字典,MD5加密后的值 ,加密的前值
-- ================测试MD5 加密============
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
-- 明文密码
INSERT INTO testmd5
VALUES(1,'zhangshan','123456'),(2,'lisi','123456'),
(3,'wangwu','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码
-- 插入的时候加密
INSERT INTO testmd5
VALUES(4,'xiaoming',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` ='xiaoming' AND pwd=MD5('123456')
6、事务(Transaction)
6.1、什么是事务
要么都成功,要么都失败
1、SQL执行 A给B转帐 A 1000 ----->200 B 200
2、SQL执行 B收到A的钱 A 800 ---->B 400
将一组SQL放在一个批次中去执行~
事务原则:ACID原则:原子性、一致性、隔离性、持久性
**原子性(Atomicity):**要么一起成功,要么一起失败,不能只发生其中一个动作
**一致性(Consistency):**针对一个事务操作前和操作后的状态一致
**持久性(durability):**表示事务结束后的数据不会随着外界原因导致数据丢失 事务提交
事务没有提交:恢复到原来状态
事务已经提交:持久化到数据库
事务一旦提交就不可逆了,被持久化到数据库了
**隔离性(Isolation):**针对多个用户同时操作,主要是排除其他事务对本次事务大的影响
隔离所导致的一些问题
脏读:
一个事务读取了另外一个事务未提交的数据
不可重复读:
在一个事务内读取表中的某一行数据,多次读取效果不同(这个不一定是错误的,只是某些场合不对)
幻读(虚读):
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致(一般是行影响,多了一行)
执行事务
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO account(`name`,`money`)
VALUES('A',2000.00),('B',10000.00)
-- 模拟转账:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE account SET money=money-500 WHERE `name` ='A' -- A减500
UPDATE account SET money=money+500 WHERE `name` ='B' -- B加500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1 -- 恢复默认值
7、索引
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构,提取句子主干,就可以得到索引的本质:索引是数据结构。
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引(PRIMARY KEY)
- 唯一标识,主键不可重复,只能有一个列作为主键
-
唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
-
常规索引(KEY/INDEX)
- 默认的,index,key关键字来设置
- 全文索引(FullText)
- 在特点给的数据库引擎下才有,MyISAM
- 快速定位数据
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个索引(索引名) 列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
-- EXPLAIN 分析sql的执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');
7.2、测试索引
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个索引(索引名) 列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
-- EXPLAIN 分析sql的执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');
CREATE TABLE `app_user`(
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男,1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT='app用户表'
-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写,标志
CREATE FUNCTION mock_date()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'1228905805@qq.com',CONCAT('18',FLOOR(RAND()*((99999999-1000000000)+1000000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
SELECT mock_date();
SELECT * FROM app_user WHERE `name` = '用户9999';
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
-- id _ 表名 _ 字段名
-- CREATE INDX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户999999';
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
索引在小数据量的时候用处不大,但是在大数据量的时候,区别十分明显
7.3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般应该加在常用来查询的字段上!
索引的数据结构
Hash类型的索引:
Btree:INNODB默认的数据结构~
8、权限管理和备份
8.1、用户管理
SQL yog 可视化管理
SQL命令操作
用户表:mysql.user
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY'密码'
CREATE USER fanxiaolei IDENTIFIED BY'123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('root')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR fanxiaolei = PASSWORD('root')
-- 给用户重命名 RENAME USER 原来名字 TO 新名字
RENAME USER fanxiaolei TO fanxiaolei2
-- 用户授权 GRANT ALL PRIVILEGES 全部的权限,库,表
-- ALL PRIVILEGES 除了给别人授权,其他的都能干
GRANT ALL PRIVILEGES ON *.* TO fanxiaolei2
-- 查询权限
SHOW GRANTS FOR fanxiaolei2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- ROOT 用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 REVOKE 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM fanxiaolei2
-- 删除用户
DROP USER fanxiaolei2
8.2、MySQL备份
为什么备份?
- 保证重要的数据不丢失
- 数据转移A……>B
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在SQLyog这种可视化工具中手动导出
在想要导出的库中,又见选中备份导出
- 使用命令行导出, mysqldump 命令行使用导出
#mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school student >D:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表2>物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school >D:/b.sql
#mysqldump -h主机 -u用户名 -p密码 数据库>物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school >D:/c.sql
# 导入
# 登录的情况下,切换到指定的数据库
# source 备份文件
source D:/a.sql
mysql -u用户名 -p密码 库名<备份文件
假设你要备份数据库,防止数据丢失。
把数据库个朋友!sql文件给别人即可!
9、规范数据库设计
9.1、为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计:
- 分析需求:分析业务和需要处理的数据库需求
- 概要设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表(用户登录注销,用户个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 友链表(友链信息)
- 评论表
- 自定义表(系统信息,某个关键的字,或者一些主字段) key:value
- 说说表(发表心情,
- 标识实体(把需求落地到每个字段)
- 标识实体之间的关系
- 写博客:user……>blog
- 创建分类:user……>category
- 关注:user……>user
- 友链:links
- 评论表:user-user-blog
9.2、三大范式
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式(1NF):
原子性:保证每一列不可再分
第二范式(2NF):
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF):
前提:满足第一范式和第二范式
第三范式需要确保数据表中每一列数据都和主键直接相关,而不能间接相关。
(规范数据库的设计)
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
- 在规范性能的问题和时候,需要适当的考虑一下 规范性!
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
10、JDBC(重点)
10.1、数据库驱动
驱动:声卡,显卡、数据库
我们的程序会通过数据库驱动和数据库打交道!
– 业务级别的MySQL学习
– 运维级别的MySQL学习
10.2、JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC,这些规范的实现由具体的厂商去做~
对于开发人员来说,我们只需要掌握JDBC接口操作即可!
java.sql
javax.sql
还需要导入一个数据库驱动包mysql-connector-java-5.1.47.jar
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
10.3、第一个JDBC程序
创建测试数据库
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
CREATE TABLE `users`(
`id` INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
`email` VARCHAR(60),
`birthday` DATE
);
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
1、创建一个普通项目
2、导入数据库驱动
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1fGWqSE3-1638627684758)(C:/Users/lei/AppData/Roaming/Typora/typora-user-images/image-20211201221551784.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SvNbRKqw-1638627684759)(C:/Users/lei/AppData/Roaming/Typora/typora-user-images/image-20211201221619893.png)]
3、编写测试代码
package com.kuang.lesson01;
import java.sql.*;
// 我的第一个JDBC程序
public class JdbcFristDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver"); //固定写法,加载驱动
//2、连接用户信息和url
//useUnicode=true&characterEncoding=utf8&useSSL=false
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "root";
//3、连接成功,数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4、执行SQL的对象 Statement 执行sql的对象
Statement statement = connection.createStatement();
//5、执行SQL的对象 去 执行SQL,可能存在结果,查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql); //返回结果集,结果集中封装了我们全部的查询出来的结果
while(resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("NAME=" + resultSet.getObject("NAME"));
System.out.println("PASSWORD=" + resultSet.getObject("PASSWORD"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birth=" + resultSet.getObject("birthday"));
System.out.println("=============================================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1、加载驱动
2、连接数据库 DriverManager
3、获取执行sql的对象 Statement(不安全)
4、获得返回的结果集
5、释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); //固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库设置自动提交 connection.setAutoCommit();
//事务提交 connection.commit();
//事务回滚 connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//mysql -- 3306
// jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
//oralce -- 1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL的对象 PrepareStatement 执行SQL的对象
String sql = "SELECT * FROM users";
statement.executeQuery(); //查询操作返回ResultSet
statement.execute(); //执行任何SQL
statement.executeUpdate(); //更新、插入、删除、都是用这个,返回一个受到影响的行数
ResultSet 查询的结果集,封装了所有的查询结果
获得指定的数据类型
resultSet.getObject(); //在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
....
遍历,指针
resultSet.beforeFirst(); //移动到最前面
resultSet.aftrtLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行
释放资源
//6、释放连接
resultSet.close();
statement.close();
connection.close(); //耗资源,用完关掉!
10.4、statement对象
jdbc中statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sql语句,exxecuteUpdate执行完成后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示列操作:
Statement st = conn.createStatement();
string sql = "insert into user(……) value(……)";
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("修改成功!");
}
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对象中
}
代码实现
1、提取工具类
2、编写增删改的方法executeUpdate
插入Insert
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获取SQL的执行对象
String sql = " INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(4,'xiaolei','123456','1228905805@qq.com','1999-12-16')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
删除Delete
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获取SQL的执行对象
String sql = "DELETE FROM users WHERE id = 4";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
更新Update
package com.kuang.lesson02.utils;
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',`email` = '1228905805@qq.com' WHERE id = 1";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("更新成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询executeQuery
Delete
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "select * from users where id = 1"; //获取SQL
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入的问题
sql存在漏洞,会被攻击导致数据泄露,SQL会被拼接
SQL注入是比较常见的网络攻击方式之一,它不是利用操作系统的BUG来实现攻击,而是针对程序员编写时的疏忽,通过SQL语句,实现无账号登录,甚至篡改数据库。
package com.kuang.lesson02;
import com.kuang.lesson02.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("kuangshen","123456");
login("'or '1=1","123456");
}
//登录
public static void login(String username,String password){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//SELECT * FROM users WHERE `NAME` = 'kuangshen' AND `PASSWORD` ='123456';
String sql = "select * from users where `NAME`='"+username+"'AND `PASSWORD`='"+password+"'"; //获取SQL
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
System.out.println("========================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.5、PreparedStatement对象
PreparedStatement可以防止SQL注入
1、新增
package com.kuang.lesson03;
import com.kuang.lesson02.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`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译sql,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4); //id
st.setString(2,"qingjiang"); //name
st.setString(3,"123456"); //password
st.setString(4,"1228905805@qq,com"); //email
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date java new java.util.Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new java.util.Date().getTime())); //birthday
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
2、删除
package com.kuang.lesson03;
import com.kuang.lesson02.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);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
3、更新
package com.kuang.lesson03;
import com.kuang.lesson02.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 `NAME` =? where id =?;";
st = conn.prepareStatement(sql); //预编译sql,先写sql,然后不执行
//手动给参数赋值
st.setString(1,"狂神");
st.setInt(2,1);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
4、查询
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
//获取连接
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from users where id = ?"; //编写sql
st = conn.prepareStatement(sql); //预编译
st.setInt(1,1); //传递参数
rs= st.executeQuery(); //执行
if(rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5、防止SQL注入
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) {
//login("lisi","123456");
login("''or 1=1","123456"); //技巧
}
//登录
public static void login(String username,String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符
//假设其中存在转义字符,就直接忽略,比如说 ' 会被直接转义
//SELECT * FROM users WHERE `NAME` = 'kuangshen' AND `PASSWORD` ='123456';
String sql = "select * from users where `NAME`=? and `PASSWORD`=?"; //获取SQL
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();//查询完毕会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
System.out.println("========================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.7、使用IDEA连接数据库
10.8、事务
要么都成功,要么都失败
ACID原则
- 原子性:要么全部完成,要么都不完成
- 一致性:总数不变
- 隔离性:多个进程互不干扰
- 持久性:一旦提交不可逆,持久化到数据库
隔离性问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变
虚读:在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
1、开启事务 conn.setAutoCommit(false);
2、一组业务执行完毕,提交事务
3、可以在catch语句中显示的定义回滚语句,但是默认失败就会回滚
package com.kuang.lesson04;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交功能,自动会开启事务
conn.setAutoCommit(false); // 开启事务
String sql1 = "update account set money = money-100 where name = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update account set money = money+100 where name = 'A'";
conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.9、数据库连接池
数据库连接 – 执行完毕 – 释放
连接 – 释放 十分浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数:10
最大连接数:15
等待超时:100ms
编写连接池,实现一个接口 DateSource
开源数据源实现(拿来急用)
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了!
DBCP
需要用到的jar包:
commons-dbcp-1.4
<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
commons-pool-1.6
<!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency>
//测试文件
package com.kuang.lesson05;
import com.kuang.lesson02.utils.JdbcUtils;
import com.kuang.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDBCP {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils_DBCP.getConnection();
//区别
//使用?占位符代替参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译sql,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4); //id
st.setString(2,"qingjiang"); //name
st.setString(3,"123456"); //password
st.setString(4,"1228905805@qq,com"); //email
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date java new java.util.Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new java.util.Date().getTime())); //birthday
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn,st,null);
}
}
}
//数据源文件
package com.kuang.lesson05.utils;
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 JdbcUtils_DBCP {
private static DataSource dataSource = null;
static{
try{
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式---->创建对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); //从数据源获取连接
}
//释放连接资源
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
需要用到的jar包
c3p0-0.9.5.5
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
mchange-commons-java-0.2.19
<!-- https://mvnrepository.com/artifact/com.mchange/mchange-commons-java -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.19</version>
</dependency>
//配置文件
package com.kuang.lesson05.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static{
try{
//代码版配置
/*
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass();
dataSource.setUser();
dataSource.setPassword();
dataSource.setJdbcUrl();
dataSource.setMaxPoolSize();
dataSource.setMinPoolSize();
*/
//创建数据源 工厂模式---->创建对象
dataSource = new ComboPooledDataSource("MYSQL"); //配置文件写法
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); //从数据源获取连接
}
//释放连接资源
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();
}
}
}
}
//代码测试
package com.kuang.lesson05;
import com.kuang.lesson05.utils.JdbcUtils_C3P0;
import com.kuang.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestC3P0 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils_C3P0.getConnection(); //原来是主机实现的,现在是用别人实现的
//区别
//使用?占位符代替参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译sql,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,5); //id
st.setString(2,"qingjiang"); //name
st.setString(3,"123456"); //password
st.setString(4,"1228905805@qq,com"); //email
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date java new java.util.Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new java.util.Date().getTime())); //birthday
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn,st,null);
}
}
}
结论
无论使用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变!
Druid
tion;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static{
try{
//代码版配置
/*
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass();
dataSource.setUser();
dataSource.setPassword();
dataSource.setJdbcUrl();
dataSource.setMaxPoolSize();
dataSource.setMinPoolSize();
*/
//创建数据源 工厂模式---->创建对象
dataSource = new ComboPooledDataSource("MYSQL"); //配置文件写法
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); //从数据源获取连接
}
//释放连接资源
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();
}
}
}
}
```java
//代码测试
package com.kuang.lesson05;
import com.kuang.lesson05.utils.JdbcUtils_C3P0;
import com.kuang.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestC3P0 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils_C3P0.getConnection(); //原来是主机实现的,现在是用别人实现的
//区别
//使用?占位符代替参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql); //预编译sql,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,5); //id
st.setString(2,"qingjiang"); //name
st.setString(3,"123456"); //password
st.setString(4,"1228905805@qq,com"); //email
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date java new java.util.Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new java.util.Date().getTime())); //birthday
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn,st,null);
}
}
}
结论
无论使用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变!
Druid