sql的增删改查关键字:
- INSERT INTO 语句,用于向表格中增加新的行;
- DELETE 语句,用于删除表中的行;
- Update 语句,用于修改表中的数据;
- SELECT 语句,用于从表中选取数据。
1、初识MySQL
JavaEE:企业级Java开发、Web
前端(页面:展示,数据!)
后台(连接点:连接数据库JDBC,链接前端(控制,控制图跳转,和给前端传递数据)
数据库(存数据,Txt,Excel、Word)
1.1、为什么学习数据库
- 岗位需求
- 现在的世界,大数据时代,得数据库者得天下
- 被迫需求:存数据
- 数据库是所有软件体系中最核心的存在 DBA
1.2、什么是数据库
数据库(DB,DataBase)
概念:数据仓库,软件,安装在操作系统(window、Linux、Mac、…)之上
作用:存储数据,管理数据
1.3、数据库分类
**关系型数据库:**SQL
-
MySQL、Oracle、Sql Server、DB2、SQLite
-
通过表与表之间,行和列之间的关系进行数据的存储,学员信息表,考勤表,…
**非关系型数据库:**NoSQL—Not only SQL
-
Redis、MongoDB
-
非关系型数据库,对象存储,通过对象的自身属性来决定。
数据库管理系统DBMS
- 数据库的管理软件,科学有效的管理数据,维护和获取数据;
- MySQL,本质是数据库管理系统!
DB:只存储数据
DBMS:管理和操作数据
1.4、MySQL
MySQL是一个关系型数据库管理系统RDBMS
前世:瑞典MySQL AB公司
今生:属于Oracle旗下产品
MySQL是最好的RDBMS(Rational Database Management System,关系数据库管理系统)应用软件之一。
开源的数据库软件
提交小、速度快、总体拥有成本低
中小型网站,或者大型网站,集群!
官网:https://www.mysql.com
版本:5.7稳定,其它新版
下载地址:https://dev.mysql.com/downloads/mysql/
安装建议:
- 尽量不要使用exe,会写入注册表,卸载麻烦
- 尽可能使用压缩包安装
1.5、安装MySQL
-
解压(使用压缩包安装)
-
把解压后的目录放到电脑软件目录下
-
配置环境变量
-
新建mysql配置文件(.ini),注意替换路径
[mysqld] # These are commonly set, remove the # and set as required. basedir = D:\software\mysql-5.7.16\ datadir = D:\software\mysql-5.7.16\data\ port = 3306 skip-grant-tables
-
启动管理员模式下的CMD,并将路径切换至mysql下的bin目录,
-
输入
mysqld -install
安装mysql服务 -
再输入
mysqld --initailize-insecure --user=mysql
初始化数据文件(实际执行时去掉了 --user=mysql) -
启动mysql,
net start mysql
-
进入管理界面,
mysql -u root -p
(初始无密码,-p后不要有空格,直接回车) -
修改密码,
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
(sql语句后不要忘记分号) -
刷新权限,
flush privileges;
-
注释掉配置文件中的 skip-grant-tables
-
重启mysql即可正常使用
net stop mysql
net start mysql
-
使用用户名、密码,连接测试
C:\WINDOWS\system32>d:
D:\>cd D:\software\mysql-5.7.16\bin
D:\software\mysql-5.7.16\bin>mysqld -install
Service successfully installed.
D:\software\mysql-5.7.16\bin>mysqld --initialize-insecure
D:\software\mysql-5.7.16\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
D:\software\mysql-5.7.16\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
......
D:\software\mysql-5.7.16\bin>mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dImtkk1S-1657464539342)( https://songjie001.oss-cn-hangzhou.aliyuncs.com/typora_pictures/image-20220313162440677.png)]
若出现安装失败,想重装:
sc delete mysql 清空服务
1.6、安装SQLyog
可视化操作软件
-
安装软件
-
连接mysql
-
右键,新建数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-neZVykaH-1657464539343)( https://songjie001.oss-cn-hangzhou.aliyuncs.com/typora_pictures/image-20220313164847219.png)]
每个sqlyog的执行操作,本质都是执行的sql语句
-
右键,新建表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NfiTYxUk-1657464539344)( https://songjie001.oss-cn-hangzhou.aliyuncs.com/typora_pictures/image-20220313165356939.png)]
-
右键,打开表,新建记录
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zMkjmCXB-1657464539344)( https://songjie001.oss-cn-hangzhou.aliyuncs.com/typora_pictures/image-20220313165631882.png)]
1.7、连接数据库
命令行连接
mysql -u root -p123456 --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改用户密码
flush privileges; --刷新权限
----------------------------------------------------
--所有语句都要使用;结尾
show databases; --查看所有数据库(注意结尾s)
use school; --切换数据库 use 数据库名
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息
create databse westos; --创建一个数据库
exit; --退出连接
-- sql的单行注释
/*
sql的
多行注释
*/
MySQL的四种语言(数据库语言):
- DDL:数据库定义语言
- DML:数据库操作语言
- DQL:数据库查询语言
- DCL:数据库控制语言
**核心:**CRUD,增删改查!
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的可视化历史记录查看
- 固定的语法或者关键字必须强行记住
2.2、数据库的列类型
数值
整数:
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等小的数据 3个字节
- int 标准的整数 4个字节 常用
- bigint 较大的数据 8个字节
小数:
- float 浮点数 4个字节
- double 浮点数 8个字节(存在精度问题)
- decimal 字符串形式的浮点数 金融计算的时候,一般使用decimal
字符串
- char 字符串固定大小的 0~255
- varchar 可变字符串 0~65535 常用的 String
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保存大文本
时间日期
- date YYYY-MM-DD,日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳,1970.1.1到现在的毫秒数,也比较常用
- year 年份表示
null
- 没有值,未知
- 注意:不要使用NULL值进行运算,结果为NULL
2.3、数据库的字段属性(重点)
Unsigned:
- 无符号整数
- 声明了该列不能声明为负数
zerofill:
- 0填充的
- 不足位数的数字,前面使用0来填充,int(3),5—> 005
自增:
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键,index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空:NULL not null:
- 假设设置为not null,如果不给它赋值,就会报错
- NULL,如果不填写值,默认就是NULL!
默认:
- 设置默认的值
- sex,默认值 为 *,如果不指定该列的值,则会使用默认的值 *
拓展:
/*每一个表都必须存在以下五个字段,未来做项目适用的
id 主键
`version` 乐观锁
id_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4、创建数据库表(纯命令行)
-- `school`
-- 目标:创建一个school数据库
-- 创建学生表(列,字段),使用SQL语句创建
-- 学号int,登录密码varchar(20),姓名,性别varchar(2),出生日期(datetime),家庭住址,email
-- 注意点:使用英文(),表的名称盒子端尽量使用``括起来
-- AUTO INCREMENT 自增
-- COMMENT 注释
-- 字符串使用 单引号 括起来 '红色的'
-- 所有语句后加,(英文的)最后一个不用加
-- PRIMARY KEY 主键,一般一个表只有一个唯一的主键!推荐写在最后,比较清晰
-- 注意中间的一些空格
-- 最后是引擎和默认编码
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE= INNODB DEFAULT CHARSET=utf8
格式:
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性 索引 注释],
`字段名` 列类型 [属性 索引 注释]
`字段名` 列类型 [属性 索引 注释],
`字段名` 列类型 [属性 索引 注释],
`字段名` 列类型 [属性 索引 注释]
)[表类型] [字符集设置] [注释]
常用命令:
SHOW CREATE DATABASE school -- 查看创建数据库的语句
-- CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
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、数据表的类型(Engine)
-- 关于数据库引擎
/*
INNODB 现在默认使用
MYISAM 早些年使用
*/
MYISAM和INNODB的区别
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约为MYISAM的2倍 |
常规操作:
-
MYISAM:节约空间,速度较快
-
INNODB:安全性高,事务的处理,多表多用户操作。
在物理空间存在的位置
- 所有数据库文件都存在在data目录下,一个文件件就对应一个数据库
- 本质还是文件的存储
MySQL引擎在物理文件上的区别:
- InnoDB在数据库表中只有一个*.frm文件以及上层目录下的ibdata文件
- MYISAM对应的文件:
- *.frm:表结构的定义文件
- *.MYD:数据文件(data)
- *.MYI:索引文件
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会使用mysql的默认字符编码,不支持中文
设置编码方式:
- 建表时设置字符集编码
- mysql.ini配置文件中设置编码(不推荐)
2.6、修改数据表
修改
-- 修改表名 : ALTER TABLE 旧名 RENAME AS 新名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 : ALTER TABLE 表名 ADD 字段名 属性
ALTER TABLE teacher1 ADD sex VARCHAR(3)
-- 修改表的字段(修改约束,重命名)
-- ALTER TABLE 表名 MODIFY 字段名 属性 -- 修改字段约束 MODIFY
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 属性 -- 字段重命名
ALTER TABLE teacher1 MODIFY sex INT(11) -- 修改字段约束 MODIFY
ALTER TABLE teacher1 CHANGE sex sex1 VARCHAR(11) -- 字段重命名 CHANGE(好像现在也可以改约束)
ALTER TABLE teacher1 CHANGE sex1 sex1 INT(11)
-- 删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP sex1
删除
-- 删除表(如果表存在)
DROP TABLE IF EXISTS teacher1
-- 所有的创建和删除操作尽量加上判断,以免报错
注意点:
- 尽量使用``包裹所有字段名
- 注释 – 、/**/
- sql关键字大小写不敏感,建议小写
- 所有符号全部用英文
3、MySQL数据管理
3.1、外键(了解)
方式一:创建表时直接添加外键约束
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 '邮箱',
`gradeid` INT(10) NOT 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 IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE= INNODB DEFAULT CHARSET=utf8
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 '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
PRIMARY KEY(`id`)
)ENGINE= INNODB DEFAULT CHARSET=utf8
-- 创建表的时候可以先不考虑外键关键,表创建后再添加
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
-- ALTER TABLE 表 ADD CONSTRAINT 约束名字 FOREIGN KEY(作为外键的列) REFERENCES 引用的哪个表(哪个字段)
注意:以上操作都是物理外键,是数据库级别的外键,我们不建议使用!(避免数据库过多造成困扰)
使用外键时,在做删除与更新操作时必须考虑外键约束,会很痛苦!!
最佳实践:
- 数据库就是单纯的表,只用来存数据只有行(数据)和列(字段)。
- 我们想使用多张表的数据,想使用外键,通过程序去实现。
3.2、MDL语言(全部记住)
数据库管理语言DML
数据库意义:数据存储、数据管理。
- insert
- update
- delete
3.3、添加Insert into
-- 插入语句 (添加)
-- inser into 表名([字段1,字段2,字段3]) values('值1','值2','值3')
INSERT INTO `grade` (`gradename`) VALUES('大四')
-- 主键自增,可以省略
-- 一般写插入语句,一定要字段和值一一对应,否则按照默认顺序匹配,易出问题
-- 若一个字段都不写,后面的值要与表中字段顺序完全对应
-- 插入多个字段,values后可以跟多个值:(),()多个括号,逗号隔开
INSERT INTO `grade` (`gradename`)
VALUES('大三'),('大二'),('大一')
INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES('张三','1234','f'),('李四','6543','m')
语法:inser into 表名([字段1,字段2,字段3]) values('值1','值2','值3')
注意事项:
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但是后面的值要一一对应
- 可以同时插入多条数据,VALUES后面的值,需要使用英文逗号隔开 VALUES(),(),(),()
提示:特别注意区分飘符号 ` 与单引号 ‘’ 的区别。。
3.4、修改Update
update 修改谁 条件 set 原来的值=新值
-- 按照条件修改
UPDATE `student` SET `name`='songsong' WHERE id = 1 -- 特别注意区分飘符号与单引号的区别。。
-- 不指定条件的情况下会修改所有表
UPDATE `student` SET `name`='songsong'
-- 修改多个属性
UPDATE `student` SET `name`='songsong',sex='男',email='122212' WHERE id = 1
-- 通过多个条件定位数据,无上限
UPDATE `student` SET `name`='wangwu',sex='女',email='122212' WHERE `name`='songsong' AND sex='男'
-- 可以设置值也可以设置一个变量
UPDATE `student` SET `name`='wangwu',sex='女',birthday=CURRENT_TIME WHERE `name`='songsong'
条件:where 子句 运算符 某个值或区间
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或!= | 不等于 | 5<>6 | true |
> | 大于 | ||
< | 小于 | ||
>= | 大于等于 | ||
<= | 小于等于 | ||
BETWEEN… AND… | 闭合区间,某个范围内 | ||
AND | 和 | 5 > 1 AND 1>2 | false |
OR | 或 | 5 > 1 OR 1>2 | true |
语法:UPDATE 表名 SET 字段名=新值 WHERE [条件]
注意事项:
- 字段名是数据库的列column,尽量戴上``
- 条件,是筛选的条件
- value,可以是一个具体的值,也可以是一个变量(譬如时间)
- 多个设置的属性,使用英文逗号隔开
- 注意``与’'的区别(值用英文引号,不用飘)
3.5、删除Delete
DELETE命令
-- 删除数据
DELETE FROM `student` -- 会删除数据表中所有信息,要避免这样写
-- 删除指定数据
DELETE FROM `student` WHERE id=1;
语法:delete fromr 表名 [where 条件]
TRUNCATE 命令:
作用:完全清空一个数据库表,表的结构和索引约束不会变。
-- 清空数据库表
TRUNCATE `student`
delete与TRUNCATE的区别:
- 相同点:都能删除数据,都不会删除表结构
- 不同点:
- TRUNCATE 重新设置 自增列,计数器会归零
- TRUNCATE 不会影响事务
- delete 删除的问题,正常不影响自增;但是如果
重启数据库
,- 对于INNODB,自增也会归零从1开始(存在内存中的,断电即失)
- 对于MYISAM,继续从上一个自增开始(存在文件中,断电不消失)
-- 测试delete和TRUNCATE
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`color` VARCHAR(20) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`color`) VALUES ('red'),('blue'),('gree'),('pink')
DELETE FROM `test` -- 不会影响自增
INSERT INTO `test`(`color`) VALUES ('yellow'),('green'),('black')
TRUNCATE `test` -- 自增会归零
INSERT INTO `test`(`color`) VALUES ('orange'),('blue'),('white')
原始:
delete删除:不影响自增
TRUNCATE删除,自增奇数归零
4、DQL查询语言(重点)select
4.1、DQL
Data Query Language:数据库查询语言
- 所有的查询操作都用它:select
- 简单的查询,复杂的查询都能做
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
使用例子
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入学生数据 其余自行添加 这里只添加了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'),
(1002,'123456','张三',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011290'),
(1003,'123456','赵四',1,1,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011256'),
(1004,'123456','张五',0,5,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011232'),
(1005,'123456','赵流',1,4,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011288');
-- 插入成绩数据 这里仅插入了一组,其余自行添加
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查询完整语法
SELECT [ALL | DISTINCT | DISTINCTROW ]
{* | table.* | table.field as alias1 [, table.field2 as alias2,.....]}
[FROM tablename1 as alias1
[left | right | inner join tablename2 as alias2 on alias1.filed = alias2.field] -- 联合查询
[WHERE where_condition] -- 指定结果需要满足的条件
[GROUP BY {col_name | expr | position} -- 指定结果要按照哪些字段来分组
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition] -- 过滤分组的记录必须要满足的次要条件
[ORDER BY {col_name | expr | position} -- 指定查询记录按一个或多个条件排序
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}] -- 指定查询记录从哪条到哪条
[FOR UPDATE | LOCK IN SHARE MODE]]
[]是可选的,{}是必选的
注意:顺序是要严格按照以上顺序的。
select 去重 要查询的字段 from 表(可以起别名)
XXX join 要连接的表 on 等值判断
where 具体的值或者子查询语句
group by (通过哪个字段分组)
having (过滤分组后的信息,条件与where相同,只是位置不同)
order by (通过哪个字段排序) [升序、降序]
limit startindex,pagesize
查询:跨表、跨数据库、跨地区
查询字段
-- 查询全部的学生的全部信息
SELECT * FROM `student`
-- 查询全部的学生的指定字段
-- SELECT 字段 FROM 表
SELECT `studentname` FROM `student`
SELECT `studentname`,`sex` FROM `student`
-- 给查询结果的字段起别名,别名无需打引号
-- AS 可以给字段起别名,也可以给表起别名
SELECT `studentname` AS 姓名,`studentno` AS 学号 FROM `student` AS s
-- 函数 Concat(a,b)拼接,可以对查询结果做拼接,组成一个
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM `student`
语法:SELECT 字段... FROM 表
有的时候,列名不是那么见名知意,可以起别名:
- 字段名 AS 别名
- 表名 AS 别名
去重distinct
作用:去除select查询出来的结果中重复的数据
-- 查询一下哪些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT studentno FROM result -- 查询有哪些同学参加了考试
-- 发现重复数据,去重
-- DISTINCT 去重关键字
SELECT DISTINCT studentno FROM result
数据库的列(表达式)
-- 查看系统版本
SELECT VERSION() -- 查询版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 用来查询自增的步长(变量)
-- 学生考试成绩+1分查看
SELECT `studentno`,`studentresult` AS 加分前, `studentresult`+1 AS 加分后 FROM result
- 数据库中的表达式:文本值、列、Null、函数、计算表达式、系统变量…
- select选择的就是表达式
4.3、where条件子句
作用:检索数据中符合条件(操作符)的值
搜索的条件由一个或多个表达式组成!结果 :布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a || b | 逻辑或,其中一个为真,则结果为真 |
Not ! | not a !a | 逻辑非,真为假,假为真 |
尽量都使用英文字母!
-- 查询全部的学生成绩
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
-- 模糊查询(区间)between
SELECT studentno,studentresult FROM result
WHERE studentresult BETWEEN 95 AND 100
-- 除了学号1000学生之外的同学成绩
SELECT studentno,studentresult FROM result
WHERE studentno != 1000
-- not != (not放在前面取反)
SELECT studentno,studentresult FROM result
WHERE NOT (studentno = 1000 OR studentno = 1001)
SELECT studentno,studentresult FROM result
WHERE NOT studentno = 1000 AND NOT studentno = 1001
比较运算符–模糊查询
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果a为null,则结果为真 |
IS NOT NULL | a is not null | 如果a不为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,a3…则结果为真 |
-- ========= Like =============
-- 查询姓赵的同学
-- Like结合通配符使用 %(代表0到任意个字符) _(代表一个字符)
-- 注意通配符必须与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 '赵__'
-- 查询名字中间有嘉字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%'
-- ======= in =======
-- 查询1001,1002,1003号学生
SELECT studentno,studentname FROM student
WHERE studentno IN (1001,1002,1003)
-- 查询在某些地址的学生
SELECT studentno,studentname FROM student
WHERE address IN ('北京朝阳')
-- ======= null not null =====
-- 查询地址为空的学生 null,''
-- NULL与空字符串''有区别
SELECT studentno,studentname FROM student
WHERE address ='' OR address IS NULL
-- 查询有出生日期的学生(不为空)
SELECT studentno,studentname FROM student
WHERE borndate IS NOT NULL
重点:是否为空的判断不用=,用IS!!!
4.4、联表查询
联表查询
JOIN
-- =========================联表查询===========================
-- JOIN(连接的表) ON (查询条件) 连接查询
-- where 等值查询
-- 查询参加了考试的同学(学号、姓名、科目编号、分数)
/*
思路:
1、分析需求,分析查询的字段来自哪些表(连表查询)
2、确定使用哪种连接查询?7种
确定交叉点(两个表中哪个数据是相同的)
判断的条件:学生表中的studentno = 成绩表中的studentno
*/
-- INNER JOIN 并集
-- 两个表中都存在的列要指明用哪个表的,否则出现错误
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno
-- Right JOIN 并集
-- 把参加了考试所有学生的成绩查出来
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
-- LEFT JOIN 并集
-- 把所有学生的考试成绩查出来(包括没参加考试的没有成绩的学生)
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
操作 | 描述 |
---|---|
Inner join | 如果(两个)表中至少有一个匹配,就返回行 |
left join | 会返回左表中所有的值,即使右表中没有匹配 |
right join | 会返回右表中所有的值,即使左表中没有匹配 |
复杂的联表查询:
-- 查询参加了考试的同学(学号、姓名、科目名、分数)
/*
思路:
1、分析需求,分析查询的字段来自哪些表(连表查询)
student、result、subject
2、确定使用哪种连接查询?7种
确定交叉点(两个表中哪个数据是相同的)
判断的条件:
学生表中的studentno = 成绩表中的studentno
成绩表中的subjectno = 科目表中的subjectno
*/
SELECT s.studentno,studentname,su.subjectname,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS su
ON r.subjectno = su.subjectno
-- 要查询那些数据 select...
-- 从哪几个表查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在多张表查询,慢慢来,先查询两张表在增加
-- From a left join b 以左边的表为基准,左边有的就可以显示
-- From a right join b 以右边的表为基准,右边有的就可以显示
-- 查询科目所属的年级(科目名称、年级名称)
SELECT subjectname, gradename
FROM `subject` AS s
INNER JOIN `grade` AS g
ON s.gradeid = g.gradeid
-- 查询参加了高等数学-1考试的同学的信息(学号、姓名、科目名、分数)
SELECT r.studentno,studentname,subjectname,studentresult
FROM `subject` AS s
RIGHT JOIN `result` AS r
ON s.subjectno = r.subjectno
LEFT JOIN `student` AS stu
ON r.studentno = stu.studentno
WHERE subjectname='高等数学-4'
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表
原始表:
categoryid | pid | categoryname |
---|---|---|
2 | 1 | 信息技术 |
3 | 1 | 软件开发 |
4 | 3 | 数据库 |
5 | 1 | 美术设计 |
6 | 3 | web开发 |
7 | 5 | ps技术 |
8 | 2 | 办公信息 |
父类:
categoryid | categoryname |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类:
pid | categoryid | categoryname |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
父类与子类的对应关系:
父类名称 | 子类名称 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 创建表
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`(
`categoryid` INT(11) NOT NULL COMMENT '目录编号',
`pid` INT(11) NOT NULL COMMENT '父级目录编号',
`categoryname` VARCHAR(50) NOT NULL COMMENT '目录名称',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO `category`(categoryid,pid,categoryname)
VALUES (2,1,'信息技术'),
(3,1,'软件开发'),
(4,3,'数据库'),
(5,1,'美术设计'),
(6,3,'web开发'),
(7,5,'ps技术'),
(8,2,'办公信息')
-- 查询父子信息
-- 核心就是把一张表拆成两种一样的表
SELECT a.`categoryname` AS '父栏目', b.`categoryname` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`
4.5、分页和排序
排序
-- ========================分页 limit 和排序 order by ================================
-- 排序: 升序 ASC ;降序 DESC
-- 查询参加了高等数学-1考试的同学的信息(学号、姓名、科目名、分数)
-- 按照成绩排序
-- order by 通过哪个字段,怎么排
SELECT r.studentno,studentname,subjectname,studentresult
FROM `subject` AS s
RIGHT JOIN `result` AS r
ON s.subjectno = r.subjectno
LEFT JOIN `student` AS stu
ON r.studentno = stu.studentno
WHERE subjectname='高等数学-4'
ORDER BY studentresult ASC
分页
-- 分页
-- 为什么要分页?减缓数据库压力
-- 每页指显示五条数据
-- 语法:LIMIT 起始值 页面显示大小
-- LIMIT 0,5 实际显示1-5的记录
-- LIMIT 1,5 实际显示2-6的记录
-- LIMIT 5,5 实际显示6-10的记录(第二页)
-- LIMIT 10,5 实际显示11-15的记录(第三页)
SELECT r.studentno,studentname,subjectname,studentresult
FROM `subject` AS s
RIGHT JOIN `result` AS r
ON s.subjectno = r.subjectno
LEFT JOIN `student` AS stu
ON r.studentno = stu.studentno
WHERE subjectname='高等数学-4'
ORDER BY studentresult ASC
LIMIT 0,2
-- LIMIT 0,5 实际显示1-5的记录(第一页)
-- LIMIT 5,5 实际显示6-10的记录(第二页)
-- LIMIT 10,5 实际显示11-15的记录(第三页)
-- LIMIT (n-1)*pagesize,pagesize (第n页)
-- 【pagesize:页面大小】
-- 【(n-1)*pagesize:起始值】
-- 【n:当前页】
-- 【数据总数/页面大小=总页数】
-- 查询 高等数学-4课程成绩排名前十,并且成绩要大于90分的同学的信息(学号、姓名、课程名称、分数)
SELECT stu.studentno,studentname,subjectname,studentresult
FROM result AS r
LEFT JOIN student AS stu
ON r.studentno = stu.studentno
LEFT JOIN `subject` AS s
ON r.subjectno = s.subjectno
WHERE s.subjectname='高等数学-4' AND studentresult > 90
ORDER BY studentresult DESC
LIMIT 0,10
语法:LIMIT 起始值 页面大小
4.6、子查询
where (这个值是计算出来的)
本质:在where语句中嵌套一个查询,执行过程由里及外!
用 = 或者 IN
-- 查询参加了高等数学-1考试的考试结果(学号、科目编号、分数),降序排列
-- 方式一:使用连接查询
SELECT studentno,r.subjectno,studentresult
FROM `subject` AS s
RIGHT JOIN `result` AS r
ON s.subjectno = r.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
-- 方式二:子查询(由里及外的查询)
SELECT studentno,r.subjectno,studentresult
FROM `result` AS r
WHERE subjectno = (
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
)
ORDER BY studentresult DESC
-- 里面子查询差的高等数据-1的科目编号
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
-- 查询分数不小于80分的学生的学号与名字
-- 不是一个值,是很多个值时,不要用等号,用IN
SELECT DISTINCT studentno,studentname
FROM student WHERE studentno IN (
SELECT studentno FROM result
WHERE studentresult >= 80
)
-- 查询分数不小于80分的学生的学号与名字
SELECT DISTINCT s.studentno,studentname
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult >= 80
-- 在这查询基础上,再增加一个条件,查'高等数学-4'
-- 要根据另一张表获得subjectno
SELECT DISTINCT s.studentno,studentname
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult >= 80 AND subjectno = (
SELECT subjectno FROM `subject`
WHERE subjectname='高等数学-4')
子查询不需要联表,可能效率更高一点??
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` -- 通过什么字段分组
-- 查询不同课程的平均分、最高分、最低分
-- 增加,平均分大于80分
-- 分组之后再过滤要用having(分组之后不能再用where)
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
4.8、select小结
注意:顺序是要严格按照以上顺序的。
select 去重 要查询的字段 from 表(可以起别名)
XXX join 要连接的表 on 等值判断
where 具体的值或者子查询语句
group by (通过哪个字段分组)
having (过滤分组后的信息,条件与where相同,只是位置不同)
order by (通过哪个字段排序) [升序、降序]
limit startindex,pagesize
查询:跨表、跨数据库、跨地区
5、MySQL函数
官网:https://dev.mysql.com/doc/refman/5.7/en/functions.html
5.1、常用函数
-- =====================常用函数========================
-- 数学运算
SELECT ABS(-8) -- 绝对值运算
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1之间的随机数
SELECT SIGN(11) -- 判断一个数的符号 0返回0,正数返回1,负数返回-1
-- 字符串
SELECT CHAR_LENGTH('你好啊啊啊啊') -- 字符串长度
SELECT CONCAT('Hello','world','!') -- 拼接合并字符串
SELECT INSERT('我爱你',2,0,'真的') -- 插入、查询替换
SELECT LOWER('Wangsan') -- 小写字母
SELECT UPPER('Zhangsan') -- 大写字母
SELECT INSTR('zhangsan','h') -- 返回子串第一次出现的索引
SELECT REPLACE('zhansan','san','si') -- 替换
SELECT REVERSE('zhansan') -- 反转
-- 查询姓张的学生,改成行宋
SELECT REPLACE(studentname,'张','宋') 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 SYSTEM_USER()
SELECT USER()
SELECT VERSION()
5.2、聚合函数(常用)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
-- =================聚合函数========================
-- 以下三种都能统计表中的数据
-- 想查询一个表中有多少记录,就用count
SELECT COUNT(studentname) FROM student; -- count(指定列),会忽略指定列中为null的值
SELECT COUNT(*) FROM student; -- count(*),不会忽略null值,本质计算行数
SELECT COUNT(1) FROM student; -- count(1),不会忽略null值,本质计算行数
SELECT SUM(studentresult) AS 总分 FROM result;
SELECT AVG(studentresult) AS 平均分 FROM result;
SELECT MAX(studentresult) AS 最高分 FROM result;
-- 查询不同课程的平均分、最高分、最低分
-- 核心:要根据不同的课程分组
SELECT 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` -- 通过什么字段分组
-- 查询不同课程的平均分、最高分、最低分
-- 增加,平均分大于80分
-- 分组之后再过滤要用having
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
5.3、数据库级别的MD5加密(扩展)
什么是MD5?
主要增强算法复杂度和不可逆性。MD5算法因其普遍、稳定、快速的特点,仍广泛应用于普通数据的加密保护领域。
MD5不可逆,具体的值MD5是一样的。
MD5破解网站的原理:背后有一个字典,Md5加密后的值 加密前的值(实际不能破解复杂的)
-- ======================测试MD5加密==========================
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES
(1,'zhangssan','123456545'),
(2,'wangwu','123456545'),
(3,'zhaoliu','12345654'),
(4,'zhangli','12345634');
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
-- 注意,此处密码长度不能太短,太短不能加密
-- 加密全部的密码
UPDATE testmd5 SET pwd=MD5(pwd)
-- 正常加密操作,应该在插入时就加密
INSERT INTO testmd5 VALUES (5,'小明',MD5('123456'))
-- 如何校验:将用户传递的密码,进行MD5加密,然后对比加密后的值。
SELECT * FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('123456')
6、事务
6.1、什么是事务
要么都成功,要么都失败
————————
- SQL执行 A给B转账:A1000 —>200 —B200
- SQL执行 B给A转账:A 800 -------------B 400
————————
要将一组SQL放在一个批次中去执行!
事务原则:ACID原则
-
原子性(atomicity,或称不可分割性)
这两个步骤一起成功或者一起失败,不能只发生一个动作
-
一致性(consistency):
事务前后数据的完整性要保持一致性
-
隔离性(isolation,又称独立性):
多个用户并发访问数据库时,数据库为每个用户开启的事务,不能被其他事务的操作数据所干扰,事务之间要相互隔离。
事务的隔离级别:(针对多个用户操作,不隔离可能出现的问题)
- 脏读:一个事务读取了另一个事务未提交的数据
- 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同(不一定错误)(主要是值发生了修改)
- 虚读(幻读):是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。(主要是结果数量发生变化,发生了新增或删除)
-
持久性(durability):—事务提交
事务一旦提交,就不可逆,被持久化到数据库中。
表示事务结束后的数据不会随着外界原因导致数据丢失:
- 若事务还没有提交,服务器宕机或断电后,重启数据库,恢复到原来
- 若事务已经提交,服务器宕机或断电后,重启数据库,提交的事务已经持久化到数据库
6.2、测试事务实现转账
-- =================事务===================
-- mysql是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认的)
-- 手动处理事务的完整流程:
-- 1、关闭自动提交
SET autocommit = 0
-- 2、事务开启
START TRANSACTION -- 标记一个事务的开始,从这之后sql都在同一个事务内
-- 3.1、提交:持久化(成功!)
COMMIT
-- 3.2、回滚:回到原来的样子(失败)
ROLLBACK
-- 4、事务结束
-- 5、开启自动提交
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),('B',10000)
-- 模拟转账
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 =10; -- 开启自动提交
7、索引
MySQL官方对索引的定义:索引(Index)是帮助MySQL高效获取数据的数据结构
提取句子主干,就可以得到索引的本质,索引是数据结构。
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引(PRIMARY KEY)
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
- 常规索引(KEY、INDEX)
- 默认的,index,key关键字设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有,MYISAM
- 快速定位数据
-- ===========================索引的使用=================================
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引,alert 表命名 add 索引
-- 显示所有的索引信息
USE school;
SHOW INDEX FROM student
-- 增加一个全文索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);
-- 分析sql执行的状况:EXPLAIN
EXPLAIN SELECT * FROM student; -- 非全文索引
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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- 插入100万条记录
DELIMITER $$ -- 写函数之前必须要写该行标志
CREATE FUNCTION mock_data2()
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),'19224305@qq.com','123456789',FLOOR(RAND()*2),'123456',24);
SET i=i+1;
END WHILE;
RETURN i;
END;
-- 执行函数
SELECT mock_data2()
-- 测试索引
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.890 sec
SELECT * FROM app_user; -- 0.001 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- rows =9999 查了9999才找到
-- 索引的创建方式:
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引,alert 表命名 add 索引
-- 3、创建索引 create indextype 索引名 on 表(字段)
-- 常规索引命名方式: id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.036 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- rows = 1 ,被唯一定位
索引在小数据量的时候,用处不大;但是在大数据的时候,区别十分明显!
7.3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash类型的索引
Btree----数据库引擎INNODB的默认数据结构
8、权限管理和备份
8.1、用户管理
可视化管理
命令管理
用户表:mysql.user
用户管理就是对这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER zhangsan IDENTIFIED BY '123456'
-- 修改密码(修改当前登录用户的密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码(修改指定用户的密码)
SET PASSWORD FOR zhangsan = PASSWORD('123456')
-- 用户重命名 rename user 原名字 to 新名字
RENAME USER zhangsan TO zhangsan2
-- 用户授权 (给用户授予所有库的所有表的所有权限)
-- 几乎所有权限,除了给别人授权的权限
GRANT ALL PRIVILEGES ON *.* TO zhangsan2
-- 查询权限
SHOW GRANTS FOR zhangsan2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- 查看根用户权限
-- root用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限(撤销哪些权限,在哪个库,给谁撤销)
REVOKE ALL PRIVILEGES ON *.* FROM zhangsan2
-- 删除用户
DROP USER zhangsan2
8.2、MySQL备份
为什么要备份?
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方式:
-
直接拷贝物理文件(data目录)
-
在Sqlyog这种可视化工具中手动导出
-
在库或表中右键选择备份
-
-
使用命令行导出(mysqldump命令)
导出:
# mysqldump -h 主机 -u 用户 -p 密码 数据库 表名 >物理磁盘位置:/表名
# mysqldump -h 主机 -u 用户 -p 密码 数据库 表1 表2 表3 >物理磁盘位置:/表名
# mysqldump -h 主机 -u 用户 -p 密码 数据库 >物理磁盘位置:/表名
mysqldump -hlocalhost -uroot -p123456 school student >E:/back.sql
mysqldump -hlocalhost -uroot -p123456 school >E:/back.sql
导入:
# 先登录
C:\Users\songjie>mysql -hlocalhost -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 再导入
# 若导入表,要切换到数据库
# 若导入表,无需切换
mysql> use school;
Database changed
mysql> source E:/back.sql
要备份数据库,防止数据丢失。
9、规范数据库设计
9.2、为什么需要设计
当数据库比较复杂的时候,需要设计
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都很麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便开发系统
软件开发中关于数据库的设计:
- 分析需求,分析业务和需求处理的数据库需求
- 概要设计:设计关系图E-R图
设计数据库的步骤(个人博客):
- 收集信息,分析需求
- 用户表(用户登录注销,用户个人信息,写博客,创建分类)
- 分类表(文章分类,谁建的)
- 文章表(文章的信息)
- 评论表
- 友链表(友链信息)
- 自定义表(系统信息,某个关键的字,或一些主字段)key:value
- 说说表(发表心情,时间)
- 用户关系中间表(user_follow)
- 标识实体(把需求落到每个字段)
- 标识实体之间的关系
- 写博客:user–>blog
- 创建分类:user–>category
- 关注:user–>user_follow
- 友链:links
- 评论:user–user–blog
拓展(粉丝数的实现):
-
关系型数据库:建立一个中间表
- id:主键,唯一标识
- user_id:被关注用户id
- follow_id:关注用户id
- 每次关注一个人,增加一条记录,显示粉丝数时是查询的某个user_id=“**”的记录数
-
非关系型数据库:字符串
- 被关注用户id:[关注用户1,关注用户2,关注用户3,…]
9.2、三大范式
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 删除异常
三大范式:
第一范式(1NF)
- 原子性:保证每一列不可再分。
- 如某字段家庭信息(山东,4口人),可以再分成籍贯和家庭人口数两个字段,不符合第一范式。
第二范式(2NF)
- 前提:满足第一范式
- 每张表只描述一件事情:需要确保数据库中的每一列都和主键相关,而不能只和主键的某一部分相关
- 如订单表和产品表拆开
第三范式(3NF)
- 前提:满足第一范式和第二范式
- 确保数据表中的每一列数据和主键都直接相关,不能间接相关。(在第二范式的基础上消除依赖性)
(规范数据库设计)
面试时遇到三大范式的问题,一定要说完之后提一下性能问题!!!
规范性 和 性能 的问题:
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,(成本,用户体验)数据库的性能更加重要
- 在规范性能的问题的时候,需要适当考虑一下 规范性!
- 故意给某些表增加一些冗余的字段(从多表查询变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
10、JDBC(重点)
10.1、数据库驱动
驱动:声卡、显卡、数据库
我们的程序会通过数据库驱动,和数据库打交道!
10.2、JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库)规范,俗称JDBC
这些规范的实现由具体的厂商去做。
对于开发人员来说,只需要掌握JDBC规范即可。
(驱动与JDBC关系)
java.sql
javax.sql
数据库驱动包
10.3、第一个JDBC程序
创建测试数据库:
CREATE DATABASE `jdbcStudy`;
USE `jdbcStudy`;
CREATE TABLE `users`(
`id` INT(2) NOT NULL,
`NAME` VARCHAR(40) NOT NULL,
`PASSWORD` VARCHAR(40) NOT NULL,
`email` VARCHAR(60) NOT NULL,
birthday DATE NOT NULL,
PRIMARY KEY(`id`)
)ENGINE= INNODB DEFAULT CHARSET=utf8;
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(4,'zhangsan2','123456','zs@sina.com','1980-12-04'),
(5,'lisi2','123456','lisi@sina.com','1981-12-04'),
(6,'wangwu2','123456','wangwu@sina.com','1979-12-04');
- 创建一个普通项目
- 导入数据库驱动(jar包):新建lib目录;把jar包拷贝进去;对lib目录右键Add as library(添加到项目库中,否则不生效)
- 编写测试代码
package com.song.lesson01;
import java.sql.*;
public class JdbcFirstDemo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
//2.用户信息和url
//基础:jdbc:mysql://host:port/database
// useUnicode=true 支持中文编码
// characterEncoding=utf8 设置字符集
// useSSL=true 使用安全的连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=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,可能存在结果,可以查看返回结果
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"));
}
//6.释放连接:按照创建的相反顺序释放(一定要释放,否则浪费资源)
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动 Class.forName
- 连接数据库 DriverManager
- 或者执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
JDBC中对象的解释
DriverManagement
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());//正常注册驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动 //已经包含new一个驱动
//connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//可以干数据库相关的事情:事务提交、回滚、设置自动提交
connection.commit();
connection.rollback();
connection.setAutoCommit(true);
URL
//基础:jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
//--- MySQL默认端口是3306
//基础:jdbc:oracle:thin@主机地址:端口号:sid
//----Oracle默认端口是1521
// useUnicode=true 支持中文编码
// characterEncoding=utf8 设置字符集
// useSSL=true 使用安全的连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
Statement 执行SQL的对象;PrepareStatement 执行SQL的对象;
//执行SQL对象
Statement statement = connection.createStatement();
String sql = "SELECT * FROM users;";//编写sql
statement.executeQuery(sql);//执行查询操作,返回一个结果集
statement.execute(sql);//执行所有sql语句,包括增删改查
statement.executeUpdate(sql);//执行更新、插入、删除操作,都使用这个,返回一个事务影响行数
statement.executeBatch();//执行多个sql
ResultSet 查询返回的结果集:封装了所有的查询结果
获得指定的数据类型
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了全部的查询结果
resultSet.getObject("id");//在并不知道列的类型时使用
//如果知道列的类型,要指定具体的获得
resultSet.getString("id");
resultSet.getInt("id");
resultSet.getFloat("id");
resultSet.getDate("id");
....
遍历,指针:
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个
resultSet.previous();//移动到上一个
resultSet.absolute(2);//移动到指定行
释放资源
//6.释放连接:按照创建的相反顺序释放(一定要释放,否则浪费资源)
resultSet.close();
statement.close();
connection.close();
10.4、statement对象–不安全(sql注入问题)
jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
- Statement对象的executeUpdate()方法,用于向数据库发送增、删、改的sql语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
- Statement对象的executeQuery()方法用于向数据库发送查询语句,executeQuery执行完后,会返回代表查询结果的ResultSet对象。
CRUD操作-create
String sql = " INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(8,'apo','123456','zs@sina.com','1980-12-04')";//编写sql
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功!");
}
CRUD操作-delete
String sql = "delete from users where id=1";//编写sql
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("删除成功!");
}
CRUD操作-update
String sql = "update users set name='wangwu' where id=4";//编写sql
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("修改成功!");
}
CRUD操作-read—特别,用executeQuery
String sql = "select * from users";//编写sql
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"));
}
代码实现:
1、提取工具类
解耦,把数据库相关配置都写到文件中(db.properties)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&charcaterEncoding=utf8&useSSL=true
username=root
password=123456
写一个工具类JdbcUtils.java读取配置文件,封装建立连接与释放资源
package com.song.lesson01.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//建一个工具类,读取配置文件
public class JdbcUtils {
private static String driver = null;//下面是static,上面一定是static
private static String url = null;//下面是static,上面一定是static
private static String username = null;//下面是static,上面一定是static
private static String password = null;//下面是static,上面一定是static
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");//拿到流
//把流加载到Propertie类中
Properties properties = new Properties();
properties.load(in);//配置文件被读到流中,再加载到Properties对象中
driver = properties.getProperty("driver");//获得配置文件中具体的某项资源
url = properties.getProperty("url");//获得配置文件中具体的某项资源
username = properties.getProperty("username");//获得配置文件中具体的某项资源
password = properties.getProperty("password");//获得配置文件中具体的某项资源
//1、驱动只用加载一次 所以考虑放在static里一起加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);//返回一个数据库连接对象
}
// 释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs) {
//按顺序依次释放连接
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、编写增删改的方法,executeUpdate
插入测试:
package com.song.lesson01;
import com.song.lesson01.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) {
//写在外面=null,是为了最后能够拿到这个资源去释放他们
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获取数据库连接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(8,'test','123456','zs@sina.com','1980-12-04');";//SQL语句
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//在finally中释放资源
JdbcUtils.release(connection, statement, resultSet);
}
}
}
删除测试:
package com.song.lesson01;
import com.song.lesson01.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) {
//写在外面=null,是为了最后能够拿到这个资源去释放他们
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获取数据库连接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(8,'test','123456','zs@sina.com','1980-12-04');";//SQL语句
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//在finally中释放资源
JdbcUtils.release(connection, statement, resultSet);
}
}
}
修改测试:
package com.song.lesson01;
import com.song.lesson01.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) {
//写在外面=null,是为了最后能够拿到这个资源去释放他们
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获取数据库连接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "update `users` set `name`='mile' where `id`=7;";//SQL语句
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("修改成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//在finally中释放资源
JdbcUtils.release(connection, statement, resultSet);
}
}
}
3、编写查询的方法,executeQuery
查询测试:
package com.song.lesson01;
import com.song.lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestRead {
public static void main(String[] args) {
//写在外面=null,是为了最后能够拿到这个资源去释放他们
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获取数据库连接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "select * from `users` where `id`=7;";//SQL语句
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"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//在finally中释放资源
JdbcUtils.release(connection, statement, resultSet);
}
}
}
SQL注入问题
SQL存在漏洞会被攻击,导致数据泄露。本质就是sql会被拼接(只要or一个一定满足的条件,总会为true)。
- SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。
存在的问题:
package com.song.lesson01;
import com.song.lesson01.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLTest {
public static void main(String[] args) {
//正常使用方式
login("test", "123456");//结果,指定的用户记录获得,登录
//问题:违法的字符串
login("'or '1=1", "'or '1=1");//结果:所有数据记录都被获得
}
//登录业务
public static void login(String username, String password) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
//SELECT * FROM users WHERE `name`='test' AND `password`='123456'
//SELECT * FROM users WHERE `name`='' or '1=1' AND `password`='123456' //name=空或1=1,1=1永远成立,所以永远为true
//改成加变量后,注意单引号的连接
String sql = "SELECT * FROM users WHERE `name`='" + username + "' AND `password`='" + password + "'";
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("用户登录:");
System.out.println("name=" + resultSet.getString("name"));
System.out.println("password=" + resultSet.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
10.5、PrepareStatement对象–安全
- PrepareStatement对象可以防止SQL注入,并且效率更高!!
1、新增
package com.song.lesson02;
import com.song.lesson01.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//区别
//1、使用问号占位符代替参数,预编译
String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);//里面需要一个与编译参数:预编译sql,先写sql,然后不执行
//2、手动给参数赋值:参数下标,从1开始;参数值
preparedStatement.setInt(1, 10);//id
preparedStatement.setString(2, "pete");//name
preparedStatement.setString(3, "123456");//password
preparedStatement.setString(4, "song@123.com");
;//email
/*注意点:
sql.Date 数据库用的
util.Date Java用的 new Date().getTime()获得时间戳
*/
preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
//3、执行
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
}
}
}
2、删除
package com.song.lesson02;
import com.song.lesson01.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//区别
//1、使用问号占位符代替参数,预编译
String sql = "delete from users where id=?";
preparedStatement = connection.prepareStatement(sql);//里面需要一个与编译参数:预编译sql,先写sql,然后不执行
//2、手动给参数赋值:参数下标,从1开始;参数值
preparedStatement.setInt(1, 10);//id
//3、执行
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
}
}
}
3、修改
package com.song.lesson02;
import com.song.lesson01.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//区别
//1、使用问号占位符代替参数,预编译
String sql = "update users set `name`=? where id=?";
preparedStatement = connection.prepareStatement(sql);//里面需要一个与编译参数:预编译sql,先写sql,然后不执行
//2、手动给参数赋值:参数下标,从1开始;参数值
preparedStatement.setString(1, "Bible");//
preparedStatement.setInt(2, 10);
//3、执行
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("修改成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
}
}
}
4、查询
package com.song.lesson02;
import com.song.lesson01.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestRead {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//区别
//1、使用问号占位符代替参数,预编译
String sql = "select * from users where id=?";
preparedStatement = connection.prepareStatement(sql);//预编译
//2、传递参数
preparedStatement.setInt(1, 10);
//3、执行
resultSet = preparedStatement.executeQuery();
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"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
}
}
}
5、防止SQL注入问题
package com.song.lesson02;
import com.song.lesson01.utils.JdbcUtils;
import java.sql.*;
public class SQLTest {
public static void main(String[] args) {
//正常使用方式
login("test", "123456");//结果,指定的用户记录获得,登录
//问题:违法的字符串
login("'or '1=1", "'or '1=1");//结果:空
}
//登录业务
public static void login(String username, String password) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//PrepareStatement防止SQL注入的本质:把传递过来的参数当做字符
//假设其中存在转义字符,比如 ' 会被直接转义
String sql = "select * from users where `name`=? and `password`=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();//注意,此处无需再传入sql
while (resultSet.next()) {
System.out.println("用户登录:");
System.out.println("name=" + resultSet.getString("name"));
System.out.println("password=" + resultSet.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, resultSet);
}
}
}
10.6、使用IDEA连接数据库
填写数据库信息,测试连接(前提,必须导入了jar包)
选择数据库
查看数据库内容:双击数据库。
修改数据库信息:表格中修改后,要点击上面的submit保存,否则修改无效
编写sql语句
创建表,插入数据
10.8、事务
事务:要么都成功,要不都失败
ACID原则:
- A:原子性 要么全部完成,要不全部失败
- C:一致性 总数不变
- I:隔离性 多个进程互不干扰
- D:持久性 一旦提交,不可逆,持久化到数据库
隔离性的问题:
- 脏读:一个事务读取到另一个没有提交的事务
- 不可重复读:在同一个事务内,重复读取表中的数据,表中数据发生改变(数据修改)
- 虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出不一致(数据增删)
代码实现:
- 开启事务
conn.setAutoCommit(false);
- 一组业务执行完毕,提交事务
- 可以在catch语句中显示的定义回滚语句;但是默认失败就会回滚,所以可以不显示定义
package com.song.lesson03;
import com.song.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTranscation {
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= !!
st.executeUpdate();
String sql2 = "update `account` set `money`=`money`+100 where `name`='B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//事务完毕,提交事务
conn.commit();
System.out.println("成功!");
conn.setAutoCommit(true);//开启自动提交事务
} catch (SQLException e) {
//一旦事务失败,自动也会回滚
// try {
// conn.rollback();//显示定义,如果失败则回滚事务
// } catch (SQLException ex) {
// ex.printStackTrace();
// }
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}
10.9、数据库连接池
数据库连接----执行完毕----释放
连接—释放 十分浪费资源!!
池化技术:准备一些预先的资源,过来就可连接预先准备好的。
(提前准备好一些connection,过来就可以拿来用,用完放回去即可)
-----开门–业务员:等待—服务----
- 常用连接数:10
- 最小连接数:10
- 最大连接数:100 业务最高承载上限
- 排队等待:超过100
- 等待超时:100ms
编写连接池,实现一个接口 DataSource
数据库数据源
- 数据源(Data Source)顾名思义,数据的来源,是提供某种所需要数据的器件或原始媒体。在数据源中存储了所有建立数据库连接的信息。就像通过指定文件名称可以在文件系统中找到文件一样,通过提供正确的数据源名称,你可以找到相应的数据库连接。
**开源数据源实现:**拿来即用
- DBCP
- C3P0
- Durid:阿里巴巴
使用了这些数据库连接池后,我们在项目开发中就不需要编写连接数据库的代码了(JdbcUtil.java)!!
DBCP连接池
**需要用的jar包:**commons-dbcp-1.4、commons-pool-1.6
导入以上jar包
新建dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
编写读取配置文件的等的类:
(相比之前,无需手动去读取各个配置项,会统一从数据源获取)
package com.song.utils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");//拿到流
//把流加载到Propertie类中
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式(模式之一,创建对象)
dataSource = BasicDataSourceFactory.createDataSource(properties);//所有参数配置都由它读取
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源中获取连接
}
// 释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs) {
//按顺序依次释放连接
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试插入:
(相比之前,改变的是从数据源获取连接)
package com.song.lesson04;
import com.song.utils.JdbcUtils;
import com.song.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class TestDBCP {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils_DBCP.getConnection();//本质无区别,只是换了数据源获取连接
//区别
//1、使用问号占位符代替参数,预编译
String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);//里面需要一个与编译参数:预编译sql,先写sql,然后不执行
//2、手动给参数赋值:参数下标,从1开始;参数值
preparedStatement.setInt(1, 12);//id
preparedStatement.setString(2, "pete");//name
preparedStatement.setString(3, "123456");//password
preparedStatement.setString(4, "song@123.com");
;//email
/*注意点:
sql.Date 数据库用的
util.Date Java用的 new Date().getTime()获得时间戳
*/
preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
//3、执行
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils_DBCP.release(connection, preparedStatement, resultSet);
}
}
}
C3P0连接池
**需要用的jar包:**c3p0-0.9.5.5、mchange-commons-java-0.2.19
放到lib目录,导入为库
新建c3p0-config.xml:
(可以配置多个数据源)
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省(默认)配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省(默认)
-->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&
useSSL=true
</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!-- c3p0的命名配置
自己定义一个单独
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写就表示使用的是mysql的缺省(默认)
-->
<named-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>
编写读取配置文件的工具类:
package com.song.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static {
try {
//方式一:代码版配置
// dataSource=new ComboPooledDataSource();
// dataSource.setDriverClass();
// dataSource.setUser();
// dataSource.setPassword();
// dataSource.setJdbcUrl();
// dataSource.setMaxPoolSize();
// dataSource.setMinPoolSize();
//方式二:配置文件配置
//读取xml文件 -- xml文件无需读取,自动匹配
dataSource = new ComboPooledDataSource("MySQL");//配置文件写法
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源中获取连接
}
// 释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs) {
//按顺序依次释放连接
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试连接:
package com.song.lesson04;
import com.song.utils.JdbcUtils_C3P0;
import com.song.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class TestC3P0 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils_C3P0.getConnection();//本质无区别,只是换了数据源获取连接
//区别
//1、使用问号占位符代替参数,预编译
String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);//里面需要一个与编译参数:预编译sql,先写sql,然后不执行
//2、手动给参数赋值:参数下标,从1开始;参数值
preparedStatement.setInt(1, 13);//id
preparedStatement.setString(2, "pete");//name
preparedStatement.setString(3, "123456");//password
preparedStatement.setString(4, "song@123.com");
;//email
/*注意点:
sql.Date 数据库用的
util.Date Java用的 new Date().getTime()获得时间戳
*/
preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
//3、执行
int num = preparedStatement.executeUpdate();
if (num > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils_C3P0.release(connection, preparedStatement, resultSet);
}
}
}
结论:
- 无论使用什么数据源,本质还是一样的,DataSource接口不变,getConnection方法不变