本笔记源于【狂神说Java】
B站收UP主：遇见狂神说。即可看见教程
或者点击链接MySQL最新教程
目录
1、初始MySQL
1.1、数据库简介
为什么学习数据库
1、岗位的技能需求
2、当今世界，得数据者得天下
3、存储数据
4、数据库是几乎软件体系中最核心的存在。
什么是数据库
数据库（DataBase，简称DB）
概念：长期存放在计算机内，有组织，可共享的大量数据的集合，是一个数据仓库
作用：保存，并能安全管理数据（如：增删改查等）减少冗余
数据库总览：
-
关系型数据库（SQL）
-
MySQL，Oracle，SQL Server，SQLite，DB2，……
-
关系型数据库通过外键关联建立表与表之间的联系。
-
-
非关系型数据库
-
Redis，MongoDB，……
-
非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
-
1.2、数据库管理系统
什么是数据库管理系统
数据库管理系统（DataBase Management System）
数据库管理软件，科学的组织和存储数据，高效的获取和维护数据
1.3、MySQL简介及安装
MySQL 简介
概念：是现在流行的、开源的、免费的关系型数据库
历史：由瑞典MySQL AB 公司开发，目前属于oracle旗下产品。
特点：
-
免费，开源数据库
-
小巧，功能齐全
-
使用便捷
-
可运行于Window或Linux操作系统
-
可适用于中小型甚至大型网站应用
安装MySQL
这里建议大家使用压缩版,安装快,方便.不复杂.
软件下载
电脑是64位的就下载使用64位版本的！
安装步骤
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)
6、再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
7、然后再次启动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、连接上测试出现以下结果就安装好了
这里给大家推荐一个工具 : SQLyog .
即便有了可视化工具,可是基本的DOS命名大家还是要记住!
1.4、SQLyog
SQLyog
可手动操作,管理MySQL数据库的软件工具
特点 : 简洁 , 易用 , 图形化
在连接数据库之前，一定要打开数据库连接，步骤同上（11）
在历史记录中可以看到相对应的数据库操作的语句 .
连接数据库
打开MySQL命令窗口
-
在DOS命令行窗口进入 安装目录\mysql\bin
-
可设置环境变量，设置了环境变量，可以在任意目录打开！
连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码
注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !
几个基本的数据库操作命令 :
update user set password=password('123456')where user='root'; 修改密码
flush privileges; 刷新数据库
show databases; 显示所有数据库
use dbname；打开某个数据库
show tables; 显示数据库mysql中所有的表
describe user; 显示表mysql数据库中user表的列信息
create database name; 创建数据库
use databasename; 选择数据库
exit; 退出Mysql
? 命令关键词 : 寻求帮助
-- 表示注释
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
MySQL关键字并不区分大小写
2.1、操作数据库（了解）
1、创建数据库
CREATE DATABASE IF NOT EXISTS westos
2、删除数据库
DROP DATABASE IF EXISTS westos
3、使用数据库
-- tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带`` USE `school`
4、查看数据库
SHOW DATABASES -- 查看所有数据库
对比：SQLyog的可视化操作
学习思路：
-
对照sqlyog可视化历史记录查看sql
-
固定的语法或关键字必须强行记住！
2.2、数据库的类型
数值
-
tinyint 十分小的数据 1个字节
-
smallint 较小的数据 2个字节
-
mediumint 中等大小的数据 3个字节
-
int 标准的整数 4个字节（常用的字节）
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节（精度问题！）
-
decimal 字符串形式的浮点数 金融计算时，一般都是使用decimal
字符串
-
char 字符串固定大小的 0-255
-
varchar 可变字符串 0-65535 常用的，比如Java中的string
-
tinytext 微型文本 2^8-1
-
text 文本串 2^16-1 保存大文本
时间日期
java,util.Date
-
data YYYY-MM-DD 日期格式
-
time HH：mm：ss 时间格式
-
datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
-
timestamp 时间戳 1970.01.01到现在的毫秒数 较为常用！
-
year 年份表示
null
-
没有值，未知
-
注意，不要使用NULL进行运算，结果为NULL
2.3、数据库的字段属性（重点）
Unsigned：
-
无符号的整数
-
声明了该列不能声明为负数
zerofill：
-
0填充的
-
不足的位数，使用0来填充，int(3) 如5-->005
Auto_InCrement
-
通常理解为自增，自动在上一条记录的基础上+1（默认）
-
通常来用设计唯一的主键- index，必须是整数类型
-
可以自定义的设计主键自增的起始和步长
NULL 和 NOT NULL
-
假设设置为not NULL ,如果不给他赋值，就会报错
-
NULL 如果不填写值，默认就是null!
DEFAULT
-
设置默认的值
-
sex，默认值为男，如果不指定该列的值，则会有默认的值！
拓展：
/* 每一个表，就必须存在以下五个字段！未来做项目用的，表示一个记录存在的意义！ id 主键 `version` 乐观键 is_delete 伪删除 gmt_create 创建时间 gmt_updata 修改时间 */
2.4、创建数据库表
-- 目标 : 创建一个school数据库 -- 创建学生表(列,字段) -- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email -- 创建表之前 , 一定要先选择数据库 -- 注意点，使用英文(),表的名称和字段尽量用`` 括起来 -- 字符串使用单引号括起来 -- 所有的语句加“,“，最后一个不加 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=INNOBASE DEFAULT CHARSET=utf8
格式
create table [if not exists] `表名`( '字段名1' 列类型 [属性][索引][注释], '字段名2' 列类型 [属性][索引][注释], ... '字段名n' 列类型 [属性][索引][注释] )[表类型][表字符集][注释];
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句 -- CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ SHOW CREATE TABLE student -- 查看student的数据表的定义语句 /* 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 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 */ DESC student -- 显示表的结构
2.5、数据表的类型
-- 关于数据库引擎 /* INNOBASE 默认使用 MYISAM 早些年使用 */
MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等...
常见的 MyISAM 与 InnoDB 类型：
| 名称 | MyISAM | InnoDB |
|---|---|---|
| 事务处理 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 支持 | 较大，越为2倍 |
经验 ( 适用场合 ) :
-
适用 MyISAM : 节约空间及相应速度
-
适用 InnoDB : 安全性 , 事务处理及多用户操作数据表
数据表的存储位置
-
MySQL数据表以文件方式存放在磁盘中
-
-
包括表文件 , 数据文件 , 以及数据库的选项文件
-
位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
-
-
注意 :
-
-
* . frm -- 表结构定义文件
-
* . MYD -- 数据文件 ( data )
-
* . MYI -- 索引文件 ( index )
-
InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
-
MyISAM类型数据表对应三个文件
-
设置数据表字符集
我们可为数据库,数据表,数据列设定不同的字符集，设定方法 :
-
创建时通过命令来设置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
-
如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定
2.6、修改删除表
修改
-- 修改表名，ALTER TABLE 旧表名 RENAME AS 新表名 ALTER TABLE teacher RENAME AS teacher1 -- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性 ALTER TABLE teacher1 ADD age INT(11) -- 修改表的字段（重命名、修改约束！） -- ALTER TABLE 表名 MODIFY 字段名 列属性[] ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束 -- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[] ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名 -- 删除表的字段 ALTER TABLE 表名 DROP 字段名 ALTER TABLE teacher1 DROP age1
删除
-- 删除表（如果表存在再删除） DROP TABLE IF EXISTS teacher1
所有的创建和删除操作尽量加上判断，以免报错~
注意点：
-
字段名 用 `` 包裹
-
注释使用（-- 单行注释）（/**/ 多行注释）
-
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 -- 学生表的gradeid 字段 要去引用年级表的gradeid -- 定义一个外键 -- 给这个外键添加约束（执行引用）reference 引用 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 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
删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）
方式二、创建表成功之后，添加外键约束
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 -- 学生表的gradeid 字段 要去引用年级表的gradeid -- 定义一个外键 -- 给这个外键添加约束（执行引用）reference 引用 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 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 表(表中的字段); ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
以上操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰，了解即可）
最佳实践
-
数据库就是单纯的表，只用来存储数据，只有行（数据）和列（字段）
-
我们想使用多张表的数据，使用外键（程序实现）
3.2、DML语言（掌握）
数据库意义：数据存储，数据管理
DML语言：数据操作语言
-
insert
-
update
-
delete
3.3、添加
insert
-- 插入语句（添加）
-- insert into 表名([字段1,字段2,字段2])values('值1'),('值2'),('值3')...
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade`(`gradename`) VALUES ('大三')
-- 一般插入语句，我们一定要数据与字段一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一')
INSERT INTO `student`(`name`) VALUES ('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aabbcc','男')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('李四','aaaaaa','男'),('王五','aaaaaa','男')
语法：insert into 表名([字段1,字段2,字段2])values('值1'),('值2'),('值3')...
注意事项：
-
字段与字段之间使用英文逗号隔开
-
字段可以省略，但是后面的值必须要一一对应，不可取少
-
可以同时插入多条数据，values后面的值，需要使用，隔开即可
values(),()
3.4、修改
updata 修改谁（条件） set 原值 = 新值
-- 修改学员姓名 UPDATE `student` SET `name`='程序猿' WHERE id = 1; -- 不指定条件的情况下，会改动所有的表 UPDATE `student` SET `name`='打工人' -- 修改多个属性，逗号隔开 UPDATE `student` SET `name`='程序员',`email`='134541@qq.com' WHERE id = 1; -- 语法 -- UPDATE 表名 SET colnum_name = value,[colnum_name = value,...] WHERE id = [条件];
语法：UPDATE 表名 SET colnum_name = value,[colnum_name = value,...] WHERE id = [条件];
条件：where 子句 运算符 id等于某个值，大于某个值，在某个区间修改...
可以简单的理解为 : 有条件地从表中筛选数据
| 运算符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | fasle |
| <>或 != | 不等于 | 5!=6 | true |
| > | 大于 | 5>6 | false |
| < | 小于 | 5<6 | true |
| >= | 大于等于 | 5>=6 | false |
| <= | 小于等于 | 5<=6 | true |
| BETWEEN | 在某个范围之间 | BETWEEN 5 AND 10 | |
| AND | 并且 | 5>1 AND 1>2 | fasle |
| OR | 或 | 5>1 OR 1>2 | true |
-- 通过多条件定位数据 UPDATE `student` SET `name`='熬夜加班' WHERE `name`='打工人' AND `pwd`='aabbcc'
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='熬夜加班' AND `pwd`='aabbcc'
注意：
-
colnum_name 是数据库的列，尽量带上``
-
条件，筛选的条件，如果没有指定，则会修改所有的列
-
value 是一个具体的值，也可以是一个变量
-
多个设置的属性之间，使用英文逗号隔开
3.5、删除
delete
语法：delete from 表名[where 条件]
DELETE FROM `student` -- 删除数据（避免这样写，会全部删除） DELETE FROM `student` WHERE id = 1; -- 删除指定数据
TRUNCATE
作用：完全清空一个数据库表，表的结构和索引约束不会变
TRUNCATE `student` -- 清空student表
注意：区别于DELETE命令
-
相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
-
不同 :
-
-
使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
-
使用TRUNCATE TABLE不会对事务有影响 （事务后面会说）
-
-- 测试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 : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-
MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)
4、DQL查询数据（重点）
4.1、DQL
（Data Query Language：数据库查询语言）
-
所有的查询都用到他 select
-
简单的查询，复杂的查询都可以做
-
数据库中最核心的语言，最重要的语句
-
使用频率最高的语句
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}];
-- 指定查询的记录从哪条至哪条
创建数据库
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=INNODB 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;
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
-- 插入成绩数据 这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
4.2、指定查询字段
-- 查询全部学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 别名，给一个结果起名字 AS 可以给字段或者表名起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student
-- 函数 concat(a,b)
SELECT CONCAT('姓名：',`studentname`) AS 新名字 FROM student
语法：SELECT 字段... FROM 表
有的时候，列表名不是那么见名知意，可以别名--AS 字段名（表名） AS 别名
作用：
-
可给数据列取一个新别名
-
可给表取一个新别名
-
可把经计算或总结的结果用另一个新名称来代替
-- 这里是为列取别名(当然as关键词可以省略)
SELECT studentno AS 学号,studentname AS 姓名 FROM student;
-- 使用as也可以为表取别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;
DISTINCT关键字的使用
作用：重复数据只查询一条
-- 查询那些同学参加了考试， 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 表
应用场景 :
-
SELECT语句返回结果列中使用
-
SELECT语句中的ORDER BY , HAVING等子句中使用
-
DML语句中的 where 条件语句中使用表达式
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...中的某一个，则结果为真 |
注意：
-
数值数据类型的记录之间才能进行算术运算 ;
-
相同数据类型的数据之间才能进行比较 ;
-- 模糊查询 between and \ like \ in \ null
-- =============================================
-- 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 '刘__';
-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'
-- =============================================
-- IN
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');
-- =============================================
-- NULL 空
-- =============================================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;
4.4、联表查询
JOIN 对比
| 操作符名称 | 描述 |
|---|---|
| INNER JOIN | 若表中有至少一个匹配，则返回行 |
| LEFT JOIN | 即使右表中没有匹配，也从左表中返回所有的行 |
| RIGHT JOIN | 即使左表中没有匹配，也从右表中返回所有的行 |
测试
/*
连接查询
如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
查询两个表中的结果集中的交集
外连接 outer join
左外连接 left join
(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
右外连接 right join
(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
等值连接和非等值连接
自连接
*/
-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(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
自连接
自己的表和自己的表连接，核心：一张表拆分成两张一样的表
以此表为例：
-- 创建一个表
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','办公信息');
父类
| categoryid | catagoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | catagoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps设计 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps设计 |
/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/
-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
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'
4.5、排序和分页
排序
语法 : 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
4.6、子查询
where(这个值是计算出来的)
本质：在where语句中嵌套一个子查询语句
/*============== 子查询 ================
什么是子查询?
在查询语句中的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'
)
)
/*
练习题目:
查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)
使用子查询,查询郭靖同学所在的年级名称
*/
4.7、分组和过滤
题目：
-- 查询不同课程的平均分,最高分,最低分 -- 前提:根据不同的课程进行分组 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是从数据表中的>字段直接进行的筛选的 */
5、MySQL函数
5.1、常用函数
数据函数
SELECT ABS(-8); /*绝对值*/ SELECT CEILING(9.4); /*向上取整*/ SELECT FLOOR(9.4); /*向下取整*/ SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/ SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
字符串函数
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 '周%';
日期和时间函数
SELECT CURRENT_DATE(); /*获取当前日期*/ SELECT CURDATE(); /*获取当前日期*/ SELECT NOW(); /*获取当前日期和时间*/ SELECT LOCALTIME(); /*获取当前日期和时间*/ SELECT SYSDATE(); /*获取当前日期和时间*/ -- 获取年月日,时分秒 SELECT YEAR(NOW()); SELECT MONTH(NOW()); SELECT DAY(NOW()); SELECT HOUR(NOW()); SELECT MINUTE(NOW()); SELECT SECOND(NOW());
系统信息函数
SELECT VERSION(); /*版本*/ SELECT USER(); /*用户*/
5.2、聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| 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;
小结
-- ================ 内置函数 ================
-- 数值函数
abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数
-- 时间日期函数
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间
-- 字符串函数
length(string) -- string长度，字节
char_length(string) -- string的字符个数
substring(str, position [,length]) -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str
instr(string ,substring) -- 返回substring首次在string中出现的位置
concat(string [,...]) -- 连接字串
charset(str) -- 返回字串字符集
lcase(string) -- 转换成小写
left(string, length) -- 从string2中的左边起取length个字符
load_file(file_name) -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
ltrim(string) -- 去除前端空格
repeat(string, count) -- 重复count次
rpad(string, length, pad) --在str后用pad补充,直到长度为length
rtrim(string) -- 去除后端空格
strcmp(string1 ,string2) -- 逐字符比较两字串大小
-- 聚合函数
count()
sum();
max();
min();
avg();
group_concat()
-- 其他常用函数
md5();
default();
5.3、数据库级别的MD5加密（拓展）
MD5 加密
一、MD5简介
MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。
二、实现数据加密
新建一个表 testmd5
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,'kuangshen','123456'),(2,'qinjiang','456789')
如果我们要对pwd这一列数据进行加密，语法是：
update testmd5 set pwd = md5(pwd);
如果单独对某个用户(如kuangshen)的密码加密：
INSERT INTO testmd5 VALUES(3,'kuangshen2','123456') update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';
插入新的数据自动加密
INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）
SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');
6、事务
6.1、什么是事务
-
事务就是将一组SQL语句放在同一批次内去执行
-
如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
-
MySQL事务处理只支持InnoDB和BDB数据表类型
要么都成功，要么都失败，成败绑定
1、SQL执行 A 给 B 转账 A（1000）-->200 B（200）
2、SQL执行 B 收到 A 的钱 A（800）--> B（400）
将一组SQL放在一个批次中去执行~
事务原则：ACID原则 原子性，一致性，隔离性，持久性
原子性(Atomic)
-
整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性(Consist)
-
一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。
隔离性(Isolated)
-
隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性(Durable)
-
在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
隔离所导致的一些问题
脏读：
指一个事务读取到了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取到了表中的某一行数据，多次读取结果不同。（不一定错，只是某些场合不对）
虚读（幻读）：
是指在一个事务内读取到了别的服务插入的数据，导致前后读取不一致。
6.2、执行事务
-- ===================事务============= -- MySQL 是默认开启事务自动提交的 SET autocommit = 0 -- 关闭 SET autocommit = 1 -- 开启（默认的） -- 手动处理事务 -- 事务开启 START TRANSACTION -- 标记一个事务的开始，从在开始的sql在同一个事务中 INSERT xx INSERT xx -- 提交：持久化（成功！） COMMIT -- 回滚：回到原来的样子（失败！） ROLLBACK -- 事务结束 SET autocommit = 1 -- 开启自动提交 -- 了解 SAVEPOINT 保存点名 -- 设置一个事物的保存点 ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点 RELEASE SAVEPOINT 保存点名 -- 撤销保存点
模拟场景
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account(`name`,`money`)
VALUES('A',2000.00),('B',10000.00)
-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE account SET money = money - 500 WHERE `name` = 'A' -- A减500
UPDATE account SET money = money + 500 WHERE `name` = 'B' -- B加500
COMMIT; -- 提交(提交之后就被持久化了)
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
7、索引
索引的作用
-
提高查询速度
-
确保数据的唯一性
-
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
-
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
-
全文检索字段进行搜索优化.
7.1、索引的分类
分类
-
主键索引 (Primary Key)
-
唯一索引 (Unique)
-
常规索引 (Index)
-
全文索引 (FullText)
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引（索引名）列名
ALTER TABLE student ADD FULLTEXT INDEX `studentname`(`studentname`);
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('张');
7.2、测试索引
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=utf8mb4 COMMENT='app用户表'
-- 插入100万条数据
DELIMITER $$ -- 写函数之前必须要写，作为标志
CREATE FUNCTION mock_data()
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),'14654613@qq.com',FLOOR(CONCAT('18',RAND()*((999999999)-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data()
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.522 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
-- id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段);
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.003 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显~
7.3、索引原则
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上！
索引的数据结构
-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类 hash类型的索引：查询单条快，范围查询慢 btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它） -- 不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引； NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
8、权限管理和备份
8.1、用户管理
SQLyog可视化管理
SQL命令操作
用户表：mysql.user
本质：对这张表进行增删改查
-- 创建用户
CREATE USER kuangstudy IDENTIFIED BY '123456'
-- 修改密码（修改当前用户的密码）
SET PASSWORD = PASSWORD('123456')
-- 修改密码（修改指定用户的密码）
SET PASSWORD FOR kuangstudy = PASSWORD('147258')
-- 重命名
RENAME USER kuangstudy TO studykuang
-- 用户授权 ALL PRIVILEGES 全部权限
-- ALL PRIVILEGES 除了给别人授权，其他均可干
GRANT ALL PRIVILEGES ON *.* TO studykuang
-- 查询权限
SHOW GRANTS FOR studykuang -- GRANT ALL PRIVILEGES ON *.* TO 'studykuang'@'%'
SHOW GRANTS FOR root@localroot -- root用户权限
-- 撤销授权 REVOKE 哪些特权 ON 什么库 FROM 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM studykuang
权限解释
-- 权限列表
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] ...
8.2、MySQL备份
数据库备份必要性
-
保证重要数据不丢失
-
数据转移
MySQL数据库备份方法
-
mysqldump备份工具
-
数据库管理工具,如SQLyog
-
直接拷贝数据库文件和相关配置文件
mysqldump客户端
作用 :
-
转储数据库
-
搜集数据库进行备份
-
将数据转移到另一个SQL服务器,不一定是MySQL服务器
-- 导出 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密码 库名 < 备份文件
9、规范数据库设计
9.1、为什么需要设计
当数据库比较复杂时我们需要设计数据库
糟糕的数据库设计 :
-
数据冗余,存储空间浪费
-
数据更新和插入的异常
-
程序性能差
良好的数据库设计 :
-
节省数据的存储空间
-
能够保证数据的完整性
-
方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
-
需求分析阶段: 分析客户的业务和数据处理需求
-
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
设计数据库步骤
-
收集信息
-
-
与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
-
-
标识实体[Entity]
-
-
标识数据库要管理的关键对象或实体,实体一般是名词
-
-
标识每个实体需要存储的详细信息[Attribute]
-
标识实体之间的关系[Relationship]
9.2、三大范式（了解）
问题 : 为什么需要数据规范化?
不合规范的表设计会导致的问题：
-
信息重复
-
更新异常
-
插入异常
-
-
无法正确表示信息
-
-
删除异常
-
-
丢失有效信息
-
三大范式
第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
第二范式(2nd NF)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式要求每个表只描述一件事情
第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范化和性能的关系
关联查询的表不得超过三张！（阿里规范）
-
为满足某种商业目标 , 数据库性能比规范化数据库更重要。
-
在数据规范化的同时 , 要综合考虑数据库的性能。
-
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间（由多表查询转单表查询）
-
通过在给定的表中插入计算列,以方便查询（从大数据量查询降低为小数据量查询：索引）
10、JDBC（重点）
10.1、数据库驱动
我们的程序会通过 数据库 驱动，和数据库打交道！
10.2、JDBC
SUN公司为简化开发人员（对数据库的统一）操作，提供了一个（Java操作数据库）规范，俗称JDBC
这些规范的实现由具体的厂商去做，
而对于我们开发人员来说，只需要掌握JDBC接口的操作即可~
除了java.sql和javax.sql之外，还需要导入一个数据库驱动包：mysql-connector-java-5.1.47.jar
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,'zhangshan','123456','zs@sina.com','1980-12-04'), (2,'lisi','123456','lisi@sina.com','1989-12-04'), (3,'wangwu','123456','wangwu@sina.com','1970-12-04')
-
创建一个普通项目
-
导入数据库驱动
-
编写测试代码
package com.study.lesson01;
import java.sql.*;
// 我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和url
//userUnicode=true&characterEncoding=utf8&useSSL=true
String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//3.连接成功，数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行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("birthday=" + resultSet.getObject("birthday"));
System.out.println("=============================================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
1、加载驱动
2、连接数据库 DriverManager
3、获取执行sql的对象 Statement
4、获得返回的结果集（只有查询才有结果集）
5、释放连接
DriveManager
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?userUnicode=true&characterEncoding=utf8&useSSL=true"; //mysql -- 3306 //协议://主机地址:端口号/数据库名？参数1&参数2&参数3 //oracle -- 1521 //jdbc:oracle:thin:@localhost:1521:sid
statement 执行SQL的对象 preparestatement 执行SQL的对象
String sql = "SELECT * FROM users";
statement.executeQuery();//查询操作返回的 resultset
statement.execute();//执行任何sql
statement.executeQuery();//更新。插入，删除（返回一个受影响的行数）
ResultSet查询的结果集：封装了所有的查询结果
获得指定数据类型
resultSet.getObject();//在不知道类型的情况下使用 //如果知道列的类型就是用指定的类型 resultSet.getString(); resultSet.getInt(); resultSet.getFloat(); resultSet.getDate(); ...
遍历
resultSet.beforeFirst();//移动到最前面 resultSet.afterLast();//移动到最后面 resultSet.next();//移动到下一个数据 resultSet.previous();//移动到前一行 resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close(); statement.close(); connection.close(); //耗资源，用完就关闭
10.4、statement对象
Jdbc中的statement对象用向数据库中发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查的语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完毕后，将会返回一个整数（即增删改语句导致数据库几行数据发生变化）。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的resultset对象。
CUPD操作——create
使用executeUpdate(String sql)方法完成数据库的添加操作，示范操作：
Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功！");
}
CUPD操作——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("删除成功！");
}
CUPD操作——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("插入成功！");
}
CUPD操作——red
使用executeUpdate(String sql)方法完成数据库的查询操作，示范操作：
Statement st = conn.createStatement();
String sql = "select * from user where id = 1";
ResultSet rs = st.executeUpdate(sql);
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应方法映射到Java对象中
}
代码实现
-
提取工具类
package com.study.lesson02.utils;
import com.mysql.jdbc.Driver;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String 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");
//驱动只加载一次
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 throwables) {
throwables.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
-
编写增删改的方法
executeUpdate
添加：
package com.study.lesson02;
import com.study.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,'studykuang','123456','465465198@qq.com','2000-01-01')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
删除：
package com.study.lesson02;
import com.study.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);
}
}
}
更新：
package com.study.lesson02;
import com.study.lesson02.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` = 'study' 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);
}
}
}
-
查询
executeQuery
package com.study.lesson02;
import com.study.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();
//SQL
String sql = "select * from users where id = 1";
rs = st.executeQuery(sql);//查询
while(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入问题
sql存在问题，会被攻击导致数据泄露 sql会被拼接 or
package com.study.lesson02;
import com.study.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLin {
public static void main(String[] args) {
//login("study","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
// SELECT * FROM users WHERE `name` = 'study' AND `password` = '123456';
String sql = "select * from users where `name`='"+username+"'and `password`='"+password+"'";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("=================================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
10.5、PreparedStatement对象
PrepareStatement可以防止SQL注入，效率更高！
1、添加
package com.study.lesson03;
import com.study.lesson02.utils.JdbcUtils;
import java.util.Date;
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,5);//id
st.setString(2,"kuangtu");
st.setString(3,"123123");
st.setString(4,"4561534@qq.com");
//注意点：sql.Date 是数据库中的 java.sql.Date()
// util.Date 是Java中的 new Date().getTime()获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
2、删除
package com.study.lesson03;
import com.study.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
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,5);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("删除成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
3、更新
package com.study.lesson03;
import com.study.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
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 throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
4、查询
package com.study.lesson03;
import com.study.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 throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5、防止SQL注入
package com.study.lesson03;
import com.study.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SQLin {
public static void main(String[] args) {
//login("studykuang","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();
//SQL
// SELECT * FROM users WHERE `name` = 'study' AND `password` = '123456';
// PrepareStatement 防止SQL注入的本质，把传递进来的参数当作字符
//假设其中存在转义字符，就直接忽略，引号会被直接转转义
String sql = "select * from users where `name`=? and `password` =?";
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 throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.6、使用IDE连接数据库
导入一个数据库驱动包：mysql-connector-java-5.1.47.jar
连接数据库
至此，已连接至数据表！
可以打开控制台编写sql代码~
如果导入数据库驱动包还存在数据库连接错误，可用以下方法解决：
配置完成后即可成功。
10.7、事务
要么都成功，要么都失败
ACID原则
原子性：要么都完成，要么都不完成
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交，持久化到数据库
隔离性产生的问题：
脏读：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务中，重复读取表中的数据，表数据发生了改变
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读取结果不一致
代码实现
1、开启事务 conn.setAutoCommit(false);
2、一组事务执行完毕，提交事务。
3、可以在catch语句中显示定义回滚语句，但默认失败就会回滚。
package com.study.lesson04;
import com.study.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction02 {
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();
//int x = 1/0;模拟报错
String sql2 = "update account set money = money + 100 where name = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕，提交事务
conn.commit();
System.out.println("成功");
} catch (SQLException throwables) {
/* try {
conn.rollback();//如果失败就回滚事务
} catch (SQLException e) {
e.printStackTrace();
}*/
//如果失败，则默认回滚
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
