本笔记源于【狂神说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); } } }