学习笔记 - MySQL

最新推荐文章于 2025-12-18 10:34:05 发布
原创 最新推荐文章于 2025-12-18 10:34:05 发布 · 404 阅读 · 6 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#学习 #笔记 #mysql #java

数据库分类

  • 关系型数据库
    • SQL : Structured Query Language
    • 通过表和表, 行和列之间的关系进行数据存储
    • mysql, oracle, DB2, SQL Server, SQLLite
  • 非关系型数据库
    • NoSQL: Not Only Structured Query Language
    • 以对象进行存储, 通过对象自身的属性进行存储
    • 是一种数据结构化存储方法的集合，可以是文档、键值对等形式
    • Redis, MongDB

数据库是数据库, MySQL是数据库管理系统(DBMS)

安装MySQL

mysql 5.7 64位下载地址:

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

操作步骤:

1.下载后得到zip压缩包

2.解压到自己想要安装到的目录, 最后是英文路径下, 并且所有的开发工具最好都在同一个目录下

假设路径为: D:\Environment\mysql-5.7.19

3.添加环境变量

  1. 我的电脑 -> 属性 -> 高级 -> 环境变量

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

  3. 在D:\Environment\mysql-5.7.19 目录下新建my.ini文件

  4. 编辑my.ini文件, 注意替换路径地址

    [mysqld]
#basedir=D:\Environment\mysql-5.7.19
basedir=D:\Program Files\mysql-5.7
#data目录会自动生成, 不用新建
datadir=D:\Program Files\mysql-5.7\data\
port=3306
#跳过密码验证
skip-grant-tables

  5. 启动管理员模式下的CMD, 并将路径切换至mysql下的bin目录, 然后输入mysqld -insrall (安装mysql)

在这里插入图片描述

  1. 在输入 mysqld --initialize-insecure --user=mysql 初始化数据文件

  2. 然后再次启动mysql, 然后用命令 mysql -u root -p(-p后面不能有空格) 进入mysql管理界面(密码可为空)
    在这里插入图片描述

  3. 进入界面后更改root密码

    update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

    (最后输入 flush privileges; 刷新权限)

  4. 修改 my.ini 文件, 删除最后一句skip-grant-tables

  5. 重启mysql即可正常使用

    net stop mysql

    net start mysql

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

    sc delete mysql; 清空服务

基本的命令行操作

mysql -u root -p123456 -- 连接数据库

-- 修改用户密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
-- 刷新权限
flush privilege;

-- 展示所有的表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xxl_job            |
+--------------------+
5 rows in set (0.00 sec)

-- 切换数据库
mysql> use mysql;
Database changed

-- 查看数据库中所有的表
show tables;
-- 显示数据库中所有的表的信息
describe student;

-- 创建数据库
create database 数据库名称;

-- 退出mysql
exit;

-- 单行注释

/*
多行注释
*/

数据库语言分类

名称解释命令
DDL: 数据定义语言 Data Definition Language定义和管理数据对象, 如数据库, 数据表等CREATE, DROP, ALERT
DML: 数据操作语言 Data Manipulation Language用于操作数据库对象中所包含的数据INSERT, UPDATE, DELETE
DQL: 数据查询语言 Data Query Language用于查询数据库数据SELECT
DCL: 数据控制语言 Data controller Language用于管理数据库的语言, 包括管理权限及数据更改GRANT, COMMIT, ROLLBACK

操作数据库

  1. 创建数据库

    create database [if not exists] westos;

  2. 删除数据库

    drop database [if exists] westos;

  3. 使用数据库

    use test;

  4. 查看数据库

    -- 查看所有的数据库
show databases;

数据库的值和列类型

  1. 数值类型

    类型说明存储需求
    tinyint非常小的数据1字节
    smallint较小的数据2字节
    mediuint中等大小的数据3字节
    int标准整数4字节
    bigint较大的整数8字节
    float单精度浮点数4字节
    double双精度浮点数8字节
    decimal字符串形式的浮点数金融计算的时候, 一般使用decimal

  2. 字符串类型

    类型说明长度
    char固定长度字符串0 ~ 255
    varchar可变长度字符串0 ~ 65535
    tinytext微型文本2^8 - 1
    text文本串2^16 - 1, 保存大文本

  3. 日期和时间格式

    类型说明格式
    DateYYYY-MM-DD日期格式
    timeHH:mm:ss时间格式
    DateTimeYYYY-MM-DD HH:mm:ss最常用的时间格式
    timestamp1970.1.1到现在的毫秒数时间戳
    yearyyyy年份表示

  4. null

    • 没有值, 或者未知的值
    • 不能使用null进行运算, 结果一定为null

数据库的字段属性

  • Unsigned
    • 无符号的整数
    • 声明了该列不能声明为负数
  • zerofill
    • 0填充的
    • 不足的位数, 使用0来填充
  • 自增: AutoIncrement
    • 通常理解为自增, 自动在上一条记录的基础上 + 1 (默认)
    • 通常用来设计唯一的主键, 必须是整数类型
    • 可以自定义设计主键自增的起始值和步长
      • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
      • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
  • NULL 和 NOT NULL
    • NULL: 默认为NULL , 即没有插入该列的数值
    • NOT NULL: 如果设置为NOT NULL , 则该列必须有值
  • 默认
    • 用来设置默认值, 如果存储数据时, 该列没有值, 就使用默认值
  • 每张表中必须存在以下五个字段
    • id - 主键
    • version - 用来做乐观锁
    • is_delete - 逻辑删除
    • gmt_create - 创建时间
    • gmt_update - 修改时间

创建数据库表

-- 目标"：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL 创建
-- 学号int 登录密码varchar(20) 姓名,性别varchar（2）,出生日期(datatime),家庭住址,email
-- 注意点，使用英文（），表的名称 和 字段 尽量使用``括起来
-- AUTO INCREMENT 自增
-- 字符串使用 单引号括起来！
-- 所有的语么面加, （英文的）,最后一个不用加
-- PRIMARY KEY 主键 一般一个表只有一个唯一的主键！

CREATE TABLE IF NOT EXISTS `student`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
   `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
   `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
   `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
   `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
   `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

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

常用命令

-- 查看创建数据库的语句
show create database 数据库名称;
-- 查看创建数据表的定义语句
show create table 表名;
-- 显示表的结构
desc 表名;

数据表的类型

设置数据表的类型

CREATE TABLE 表名(
   -- 省略一些代码
   -- Mysql注释
   -- 1. # 单行注释
   -- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)

-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;
MySQL支持的引擎类型: CSV, ARCHIVE, BLACKHOLE, MRG_MYISAM, MYISAM, MEMORY, PERFORMANCE_SCHEMA, InnoDB(默认)

MYISAM和InnoDB的区别

名称MYISAMInnoDB
事务处理不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大, 越是MYISAM 2倍
使用场合:
  • MyISAM: 节约空间及相应速度
  • InnoDB: 安全性高, 事务处理及多用户操作数据表, 多表多用户操作
在物理空间中存储的位置
  • 所有的数据库文件都存在data目录下, 数据库的本质还是文件的存储, 一个文件夹对应一个数据库
  • MYSQL引擎在物理文件上的区别
    • InnoDB在数据库表中只有一个*.frm文件, 以及上级目录下的ibdata1文件
    • MYISAM对应文件
      • *.frm 表结构的定义文件
      • *.MYD 数据文件(D指的是data)
      • *.MYI 索引文件(I指的是index)
DELETE删除对于两种引擎的影响
  • InnoDB, 使用delete删除数据后, 重启数据库, 自增列会从1开始, 因为这个自增列是存储在内存中的, 断电即失
  • MyISAM, 使用delete删除数据后, 重启数据库, 自增列会继续从上一个自增量开始, 因为这个自增列是存储在文件中的, 不会丢失

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

CHARSET=utf8

不设置的话, 会是mysql默认的字符集编码: Latin1, 这个编码是不支持中文的

  • 可以在创建表的语句中添加CHARSET=utf8

  • 也可以在my.ini文件中配置默认的编码

    character-set-server=utf8

修改删除表

修改表

-- 修改表名
alert table 旧表名 rename as 新表名
-- 增加表的字段
alert table 表名 add 字段名 列属性 
-- 修改表的字段
	-- 重命名
	alert table 表名 change 旧字段名 新字段名 新的列属性
	-- 修改约束
	alert table 表名 modify 字段名 新的列属性
	
	-- change用来字段重命名, 也可以修改字段类型和约束
	-- modify不用来字段重命名, 只能修改字段类型和约束
	
-- 删除表的字段
alert table 表名 drop 要删除的字段名

删除表

-- 删除表(如果表存在的话, 再删除)
drop table if exists 表名

所有的创建和删除操作尽量加上判断, 以免报错

MySQL数据管理

外键(了解即可)

  • 概念

    • 如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

      在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

  • 作用

    • 保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值

  • 创建外键

    • 方式1

      -- 创建外键的方式一 : 创建子表同时创建外键

CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '',
   `gradename` VARCHAR(50) NOT NULL COMMENT '',
   PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid 字段 要去引用年级表的gradeid
-- 定义外键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 '出生日期',
   `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

    • 方式2

      -- 创建外键方式二 : 创建子表完毕后,修改子表添加外键

CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '',
   `gradename` VARCHAR(50) 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 '出生日期',
   `gradeid` INT(10) NOT NULL COMMENT '学生的年年级',
   `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
   `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

--ALTER TABLE 表
-- ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 那个表 (`哪个字段`);
  1. 删除有外键关系的表的时候, 必须要先删除引用别人的表(从表), 再删除被引用的表(主表)
  2. 以上操作的外键都是物理外键, 属于是数据库级的外键, 不建议使用, 避免数据库表过多造成困扰
  3. 最佳实践
    1. 数据库就是单纯的表, 只用来存数据, 只有行(数据和列(字段)
    2. 如果我们想使用多张表的数据, 可以通过程序去实现
-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

DML语言

添加
-- 插入语句（添加）
-- insert into 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略(如果不写表的字段，他就会一一匹配)
INSERT INTO `grade` VALUES(`大三`)
INSERT INTO `grade` VALUES(`大四`)

-- 般写插入语句，我们一定要数据和字段一一对应！

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一')

INSERT INTO `student`(`name`) VALUES('张三')

INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES('张三','aaaa','男')

INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES('李四','aaaaa','男'),('王五','bbbbbb','女')

注意 :

  • 字段或值之间用英文逗号隔开 .
  • ’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .
  • 可同时插入多条数据 , values 后用英文逗号隔开 .
修改
-- 语法
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

注意 :

  • column_name 为要更改的数据列，尽量带 ``
  • value 为修改后的数据 , 可以为变量 , 具体值 , 表达式或者嵌套的SELECT结果
  • condition 为筛选条件 , 如不指定则修改该表的所有列数据
  • 多个设置的属性之间，使用英文逗号隔开
where子句
运算符含义范围结果
=等于5=6false
<>或者!=不等于5!=6true
>大于5>6false
<小于5<6true
>=大于等于5>=6false
<=小于等于5<=6false
between A and B在A和B范围之间[A, B]between 5 and 10
and并且5>1 and 1>2false
or或5>1 or 1>2true
删除
delete命令
-- 语法, condition为筛选条件 , 如不指定则删除该表的所有列数据
DELETE FROM 表名 [WHERE condition];
truncate命令
-- 语法, 用于完全清空表数据 , 但表结构 , 索引 , 约束等不变
TRUNCATE [TABLE] table_name;
delete和truncate区别
  • 相同点: 都能删除数据, 不删除表结构, 但是truncate速度更快
  • 不同点:
    • 使用truncate, table会重新设置auto_increment计数器, 自增列会归0
    • 使用truncate, table不会对事务有影响

DQL语言

测试数据

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

-- 插入成绩数据  这里仅插入了一组，其余自行添加
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);

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}];-- 指定查询的记录从哪条至哪条
   
-- 注意 : [ ] 括号代表可选的 , { }括号代表必选得

指定查询字段

-- 查询表中所有的数据列结果 , 采用 **" \* "** 符号; 但是效率低，不推荐 .

-- 查询所有学生信息
SELECT * FROM student;

-- 查询指定列(学号 , 姓名)
SELECT `studentno`,`studentname` FROM student;

  1. 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;

  2. distinct关键字的使用(去重)

    • 作用 : 去掉 select 查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条

      -- # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT `studentno` FROM result; -- 查看哪些同学参加了考试
SELECT DISTINCT `studentno` FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

  3. 使用表达式的值

    -- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT `studentno`, `StudentResult`+1 AS '提分后' FROM result;
    • 数据库中的表达式, 一般由文本值, 列值, NULL, 函数和操作符等组成
    • 应用场景:
      • select语句返回结果列中使用
      • select语句中的order by, having等子句中使用
      • DML语句中的where条件语句中使用表达式
      • 避免SQL返回结果中包含’.', ‘*’ 和括号等干扰开发语言程序

where条件语句

作用: 用于检索数据表中符合条件的记录

搜索条件可由一个活多个逻辑表达式组成, 结果一般为真或假

  1. 逻辑操作符

    操作符名称语法描述
    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;

  2. 模糊查询: 比较操作符

    操作符名称语法描述
    IS NULLa IS NULL若操作符为NULL, 则结果为真
    IS NOT NULLa IS NOT NULL若操作符不为NULL, 则结果为真
    BETWEENa BETWEEN b AND c若a在b和c 之间, 则结果为真
    LIKEa like bSQL模糊匹配, 若a匹配b, 则结果为真
    INa IN (a1, a2,…)若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;

连接查询(联表)

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
自连接
 /*
自连接
   数据表与自身进行连接

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

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

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

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

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
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'

排序和分页

/*============== 排序 =================
语法 : 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

子查询

/*============== 子查询 ================
什么是子查询?
   在查询语句中的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名学生的成绩信息(学号,姓名,分数)
   使用子查询,查询郭靖同学所在的年级名称
*/

MySQL函数

官方网站: https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

常用函数

数据函数

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

字符串函数

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

日期和时间函数

 SELECT CURRENT_DATE();   /*获取当前日期*/
 SELECT CURDATE();   /*获取当前日期*/
 SELECT NOW();   /*获取当前日期和时间*/
 SELECT LOCALTIME();   /*localtime 获取当前日期和时间*/
 SELECT SYSDATE();   /*sysdate 获取当前日期和时间*/
 
 -- 获取年月日,时分秒
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

系统信息函数

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

小结

 -- ================ 内置函数 ================
 -- 数值函数
 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();

聚合函数

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

分组和过滤

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

数据库级别得MD5加密（扩展）

MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。

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

  • MD5不可逆，具体的值的md5是一样的

  • MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密的前值

实现数据加密

  1. 新建一个测试表

     -- 测试 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

  2. 明文密码

    -- 插入一些数
INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')

  3. 加密语法

    UPDATE testmd5 SET pwd = MD5(pwd);

    插入数据自动加密

    INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));

事务

什么是事务?

  • 事务就是将一组SQL语句放在同一批次内去执行
  • 如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
  • MySQL事务处理只支持InnoDB和BDB数据表类型

MySQL默认开启事务

事务的ACID原则

原子性: Atomic

  • 整个事务中的所有操作, 要么全部完成, 要么全部不完成, 不可能停滞在中间某个环节.
  • 事务在执行过程中发生错误, 会被回滚(rollback)到事务开始前的状态, 就像这个事务从来没有执行过一样

一致性: Consisitency

  • 一个事物可以封装状态改变(除非他是一个只读的)
  • 事务必须始终保持系统处于一直的状态, 不管在任何给定的时间并发事务有多少, 也及时说: 如果事务是并发多个, 系统也必须如同串行事务一样操作, 其主要特性是保护性和不变性
  • 以转账案例为例, 假设有五个账户, 每个账户余额是100元, 那么五个账户总额是500元, 如果在5个账户同时发生多个转账, 无论并发多少个, 比如在A和B账户之间转账5元, 在C和D账户之间转账10元, 在A和E账户之间转账15元, 五个账户总额也应该还是500元, 这就是保护性和不变性

隔离性: Isolated

  • 隔离状态执行事务, 使它们好像是系统在给定的时间内执行的唯一操作
  • 如果有两个事务, 运行在相同的时间内, 执行相同的功能, 事务的隔离性将确保每一个事务在系统中认为只有该事务在使用系统
  • 这种属性有时称为串行化, 为了防止事务操作间的混淆, 必须串行化或序列化请求, 使得在同一时间仅有一个请求用于同一数据

持久性: Durability

  • 在事务完成之后, 该事务对数据库所作的更改便持久化保存在数据库之中, 并不会被回滚

隔离性导致的问题

脏读

  • 是指在一个事务处理过程里读取了另一个未提交事务中的数据

不可重复读

  • 是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了
  • 在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据

虚读(幻读)

  • 幻读是事务非独立执行时发生的一种现象。指在一个事务内读取到了别的事务插入的数据,导致前后读取不—致
  • 例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）

基础语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
-- 1.MySQL中默认是自动提交
-- 2.使用事务时应先关闭自动提交

SET autocommit =0    -- 关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION   -- 标记一个事务的开始,从这个之后的sq1都在同一个事务内

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

测试 - 转账

/*
=============课堂测试题目===========================

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交

索引

MySQL官方对索引的定义为: 索引(Index)是帮助MySQL高效获取数据的数据结构, 即索引是数据结构

索引的作用

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

索引分类

  1. 主键索引 (Primary Key)

    • 某一个属性组能唯一标识一条记录
    • 唯一的标识, 主键不可重复, 只能有一个列作为主键
    • 特点
      • 最常见的索引类型
      • 确保数据记录的唯一性
      • 确定特定数据记录在数据库中的位置

  2. 唯一索引 (Unique Key)

    • 避免同一个表中某数据列中的值重复
    • 避免重复的列出现, 唯一索引可以重复, 多个列都可以标识为唯一索引
    • 与主键索引的区别
      • 主键索引只能有一个
      • 唯一索引可能有多个
    CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

  3. 常规索引 (Index)

    • 作用: 快速定位特定数据
    • 默认的就是常规索引, Key关键字来设置
    • 注意:
      • index和key关键字都可以设置常规索引
      • 应加在查询条件的字段
      • 不宜添加太多常规索引, 影响数据的插入, 删除和修改操作
    CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

  4. 全文索引 (FullText)

    • 在特定的数据库引擎下才有, MyISAM
    • 快速定位数据
    • 注意:
      • 只能用于MyISAM类型的数据表
      • 只能用于char, varchar, text数据列类型
      • 适合大型数据集

  5. 测试各种索引创建方式

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


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


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


#显示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

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

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

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

测试索引

建表app_user:

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用户表'

批量插入100w条数据:

DROP FUNCTION IF EXISTS mock_data;
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), '24736743@qq.com', CONCAT('18', FLOOR(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'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

创建索引: CREATE INDEX idx_app_user_name ON app_user(name);

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

索引准则

  • 索引并不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

Todo: 索引的数据结构

MySQL索引背后的数据结构及算法原理

权限管理和备份

权限管理

/* 用户和权限管理 */ ------------------
用户信息表：mysql.user

-- 刷新权限
FLUSH PRIVILEGES

-- 增加用户  CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  -- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  -- 只能创建用户，不能赋予权限。
  -- 用户名，注意引号：如 'user_name'@'192.168.1.1'
  -- 密码也需引号，纯数字密码也要加引号
  -- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
CREATE USER blue IDENTIFIED BY '123456'

-- 重命名用户  RENAME USER old_user TO new_user
RENAME USER blue TO blue2

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

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

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有权限
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表

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

-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE 权限列表 ON 表名 FROM 用户名

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON *.* FROM blue2
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名

权限解释

-- 权限列表
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] ...

备份

数据库备份的必要性

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

MySQL数据库备份的方法

  • mysqldump备份工具
  • 数据库管理工具
  • 直接拷贝数据库物理文件和相关配置文件

mysqldump命令行

  • 作用:

    • 转存数据库
    • 搜集数据库进行备份
    • 将数据转移到另一个SQL服务器, 不一定能够是MySQL服务器

  • 具体使用

    #导出一张表
mysqldump -h mysql主机 -u mysql用户名 -p mysql密码 想要导出的数据库 想要导出的表名 > 物理磁盘位置/导出之后的文件名.sql
#导出多张表
mysqldump -h mysql主机 -u mysql用户名 -p mysql密码 想要导出的数据库 想要导出的表名1 想要导出的表名2 ... > 物理磁盘位置/导出之后的文件名.sql
#导出数据库
mysqldump -h mysql主机 -u mysql用户名 -p mysql密码 想要导出的数据库 > 物理磁盘位置/导出之后的文件名.sql

#导入
#登录mysql的情况下, 切换到想要导入的数据库
# source 想要导入的sql文件

规范化数据库设计

三大范式

  1. 第一范式: 1st NF

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

  2. 第二范式: 2nd NF

    第二范式的前提是满足第一范式;

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

  3. 第三范式: 3rd NF

    第三范式的前提是满足第二范式和第一范式;

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

规范化和性能的关系

  • 关联查询的表不能超过三张
  • 为满足某种商业目标, 数据库性能比规范化数据库更重要
  • 在数据规范化的同时, 要综合考虑数据库的性能
  • 通过在给定的表中添加额外的字段, 以大量减少需要从中搜索信息所需的时间
  • 通过在给定的表中插入计算列, 以便查询

JDBC

什么是JDBC

  • 数据库厂商为了开发人员能够更好地使用各自的数据库, 会开发各种驱动
  • sun公司将各自厂商开发的驱动整合之后, 规定一个规约: JDBC
  • 因此开发人员只需要学习jdbc即可
  • 在这里插入图片描述

第一个JDBC程序

  1. 创建测试数据库

    CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')

  2. 程序代码

    必须要导入mysql-connector-java依赖

    <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    // 1. 加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    // 2. 用户信息和url
    String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
    String 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();
}

  3. 对象讲解

    • url

      String url = “jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true”;

      协议/主机名称:端口号/想要连接的数据库?参数1&参数2&参数3

      jdbc:mysql://localhost:3306/jdbcstudy?支持中文编码&设置中文字符集&`使用安全连接

      如果是oracle: jdbc:oracle:thin:@localhost:1521:orcl

    • DriverManager

      //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");  // 固定写法，加载驱动

Connection connection = DriverManager.getConnection(url, username, password);
//  Connection 代表数据库
// 数据库设置自动提交
    connection.setAutocommito();
// 事务提交
    connection.commit();
// 事务滚回
    connection.rollback();

    • Statement 执行SQL的对象 PrepareStatement 执行SQL的对象

      String sql = "SELECT * FROM users";  // 编写SQL

statement.executeQuery();  // 查询操作返回 ReasultSet
statement.execute();   // 执行任何SQL
statement.executeUpdate();   // 更新、插入、删除 。 都用这个，返回一个受影响的行数

    • Resultset: 查询的结果集：封装了所有的查询结果

      // 获得指定的数据类型
resultset. getobjectO;//在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultset.getString();
resultse. getInt();
resultset.getFloat();
resultset.getDate();
resultset.getObject();

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

    • 释放资源

      //6、释放连接
resultSet.close();
statement.close();
connection.close();//耗资源,用完关掉

statement 对象

Jdbc中的 statement对象用于向数据库发送sqL语句，想完成对数据库的增删改査,只需要通过这个对象向数据库发送増删改查语句即可

Statement对象的 executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)

Statement.executeQuery方法用于向数据库发送查询语句， executeQuery方法返回代表查询结果的 Resultset对象

代码实现

  1. insert

    Statement st = conn. createStatement();
String sql = " insert into user(…) values(…)"
int num= st.executeUpdate(sq1);
if(num>0){
    system.out. printIn("插入成功!!!")
}

    package com.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

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

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

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)    " +
                    "VALUES(4,'blue','123456','24736743@qq.com','2020-01-01')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }

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

    }
}

  2. delete

    Statement st = conn.createStatement();
String sql = "delete from user where id=l";
int num = st.executeUpdate(sql);
if(num > 0){
   system.out.printIn("删除成功!!!"); 
}

    package com.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

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

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

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();
            String sql = "DELETE FROM users WHERE id = 4";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功！");
            }

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

    }
}

  3. update

    Statement st = conn.createStatement();
String sql = "update user set name='' where name =''";
int num = st.executeUpdate(sql)
if(num > 0){
	system.out.printIn("修改成功!!!");
}

    package com.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

public class TextUpdate {

    public static void main(String[] args) {


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

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();
            String sql = "UPDATE users SET `name` = 'blue',`email`='24736743@qq.com`jdbcstudy`' WHERE id=1";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }

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

  4. select

    Statement st = conn.createStatement();
String sql = "select * from user where id=l";
ResultSet rs = st.executeQuery(sql)
while(rs.next()){
    //根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}

    package com.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

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

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

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();
            String sql = "select * from users where id = 1";

            rs = st.executeQuery(sql);
            if (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.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

public class SQL注入 {

    public static void main(String[] args) {
        login("blue","123456");
        login("'' or '1=1 ","123456");
    }

    // 登录业务
    public static void login(String username, String password){

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

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();
            String sql = "select * from users where `NAME`= '"+username+"' AND `password`='"+password+"'";

            rs = st.executeQuery(sql);
            if (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }

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

    }

}

PreparedStatement 对象

  • PreparedStatement可以防止SQL注入。

代码实现

  • insert

    package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TextInsert {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;


        try {
            conn = JdbcUtils.getConnection();

            // 区别
            //使用 ? 占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            st = conn.prepareStatement(sql);  //预编译SQL,先sqL,然后不执行

            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"blue");
            st.setString(3,"123456");
            st.setString(4,"24736743@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,null);
        }

    }

}

  • delete

    package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TextDelete {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            //使用 ? 占位符代替参数
            String sql = "delete from users where id=?";
            st = conn.prepareStatement(sql);  //预编译SQL,先sqL,然后不执行

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

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

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }

    }

}

  • update

    package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TextUpdate {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();

            // 区别
            //使用 ? 占位符代替参数
            String sql = "update users set `NAME` =?  where id=? ; ";
            st = conn.prepareStatement(sql);  //预编译SQL,先sqL,然后不执行

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

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("更新成功！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

  • select

    package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

public class TextSelect {

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

}

防止SQL注入问题

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQL注入 {

    public static void main(String[] args) {
        //正常登录
        login("lisi","123456");
        login("'' or 1=1","'' or 1=1");
    }

    // 登录业务
    public static void login(String username, String password){

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            // Preparedstatement 的 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"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
云的学习笔记-云的学习笔记系统-云的学习笔记系统源码-云的学习笔记管理系统-基于ssm的云的学习笔记系统-ssm-java代码
04-16
云的学习笔记-云的学习笔记系统-云的学习笔记系统源码-云的学习笔记管理系统-云的学习笔记管理系统java代码-云的学习笔记系统设计与实现-基于ssm的云的学习笔记系统-基于Web的云的学习笔记系统设计与实现-云的学习...
云的学习笔记-云的学习笔记系统-云的学习笔记系统源码-云的学习笔记管理系统-基于Web的云的学习笔记系统设计与实现-java代码
04-16
云的学习笔记-云的学习笔记系统-云的学习笔记系统源码-云的学习笔记管理系统-云的学习笔记管理系统java代码-云的学习笔记系统设计与实现-基于ssm的云的学习笔记系统-基于Web的云的学习笔记系统设计与实现-云的学习...
Docker学习笔记-部署MySQL-命令解读
m0_73927143的博客
09-06 1850
Docker部署MYSQl和命令解读
MYSQL学习笔记-SQL
Hhg66hh的博客
01-11 657
学习笔记 - MySQL8.0.21 Windows部署
MySQL
09-19 1万+
1、资源 安装文档地址：https://dev.mysql.com/doc/refman/8.0/en/windows-install-archive.html 版本下载地址：https://download.youkuaiyun.com/download/mchdba/12863529，下载名字为mysql-8.0.21-winx64.zip的安装包，是zip包，不是EXE的安装包。 2、准备安装 解压到D\:MySQL目录下面，目录如下。 建立data数据目录： ...
学习笔记 - MySQL自增主键值
MySQL
10-02 1万+
1、有朋友给我留言 为什么他的库表里面，有些是连续的，有些不是连续的，而且他的表都是自增（`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id'），他的潜意识里这个id应该是连续的才对。因此准备梳理梳理mysql自增主键的值的变化情况，让朋友有个比较全面的了解。 2、自增值存储说明 2.1 MyISAM 引擎的自增值保存在数据文件中。 2.2 InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本...
学习笔记-MySql数据库介绍与连接
wangzi的博客
01-12 1299
MySQL是一个关系型数据库管理系统，在 WEB 应用方面，MySQL是最好的 RDMS (Relational Database Management System，关系数据库管理系统) 应用软件，它是由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品，MySQL 是最流行的关系型数据库管理系统中的一个。
MYSQL学习笔记
2301_79997310的博客
11-03 1571
在默认情况下,当查询两个表时,会将第一个表中的一行和另一个表中的每一行组合,返回结果,包含两张表的所有列,一个返回的记录数是两张表的行数乘积,这样多表查询默认处理方式返回的结果, 称为笛卡尔积`在RAND()中添加一个数字时将产生一个固定的随机数, 例如 : RAND(3) 不添加数字, 只是RAND()时,每次执行都会产生不同的随机数。不同的数据库用户登录到DBMS后根据相应的权限可以操作的数据库和数据对象(表, 视图,触发器)是不同的。MySQL中判断是否相等是一个等号'=', 不等有两种 :!
MySQL数据库学习笔记 --- 多表查询
2301_77574443的博客
11-26 2229
本文系B站《黑马程序员 MySQL数据库入门到精通》课程学习笔记，视频课网址：https://www.bilibili.com/video/BV1Kr4y1i7ru?spm_id_from=333.788.player.switch&vd_source=23c0566fd2a5745f9a55b8adfc460734&p=27
宋红康 MySQL高级篇 学习笔记
weixin_39408609的博客
07-18 1308
查询缓存：有就直接返回了。解析器进行解析：检查sql合不合语法 优化器：对sql语句进行逻辑优化，看是否使用索引，生成执行计划。存贮引擎：myisam ,innodb去执行上述计划 当然返回的时候 也会在缓存一下结果。
mysql增删改查操作语句_学习笔记-mysql增删改查操作语句
热门推荐
weixin_35626308的博客
01-21 1万+
1.mysql增删改查操作语句：1)如果想在一个已经建好的表中添加一列，可以用以下代码：alter table t1 add column addr varchar(20) not null;2)这条语句会向已有的表t1中加入一列，这一列在表的最后一列位置。如果我们希望添加在指定的一列，可以用：alter table t1 add column addr varchar(20) not null ...
Mysql学习笔记-Mysql基础（基础看这篇就够了）
cyh0503的博客
07-06 2354
数据库的基本概念 1. 数据库的英文单词： DataBase 简称 ： DB 2. 什么数据库？ * 用于存储和管理数据的仓库。 3. 数据库的特点： 1. 持久化存储数据的。其实数据库就是一个文件系统 2. 方便存储和管理数据 3. 使用了统一的方式操作数据库 -- SQL MySQL数据库软件 1. 安装 请自行去mysql官网现在数据库软件。 2. 卸载 1. 去mysql的安...
运放学习笔记
Z97371539的博客
12-13 586
单电源运放是指仅使用单一电源电压（例如，+5V）和地（0V）作为电源供给的运放。也就是说，它只需要一个正电压和地作为参考电压，而不需要负电压。
Javaweb 学习笔记——html+css
最新发布
hssfscv的博客
12-18 438
以上是Javaweb中关于前端html和css的相关内容，主要制作了两个页面，了解了如何使用AI生成我们需要的内容，有利于了解前端知识，接下来将进行剩余前端知识的学习
大模型学习基础（五） 强化学习（Reinforcement Learning，RL）初步
qq_56095294的博客
12-13 336
前面的文章简单介绍过，传统的监督学习所使用的数据集是（特征，标签），有“标签”即明确的知晓正确的输出应该是什么。而实际的情况是，环境的状态S是由多个Si构成的，每训练一组S-a即训练一个多分类问题，把这些问题的损失函数（交叉熵）加在一起，即可训练出在不同的状态下应该使用什么动作。模型在选择一个动作之后，这个动作实际是对Environment发生，相应的Environment会给模型一个回馈Reward，然后再给模型一个新的Observation，模型继续选择新的动作，循环此过程。
Android学Dart学习笔记第十四节 库和导库
weixin_44656996的博客
12-15 771
其他语言中的访问修饰符关键字提供了更细粒度的控制，而Dart使用下划线和基于库的隐私提供了直接的配置机制，有助于高效实现动态访问，并改进了树抖动（死代码消除）。库不仅提供api，还是隐私的单位：以下划线（_）开头的标识符只在库内部可见。当你导入的多个库中，使用了相同的类名时，可以为库起个别名，使用 别名.类名 明确指定所引用的类。使用import来指定如何在另一个库的作用域中使用来自一个库的命名空间。带有通配符_的导入前缀是不绑定的，但可以访问该库中的非私有扩展。
Java 学习路线及学习周期
前端金牌摸鱼达人
12-17 706
Java 学习路线及学习周期
学习笔记——Linux 进程管理笔记
2301_78295956的博客
12-13 907
本文总结了Linux进程管理的核心知识点：1.父子进程关系采用写时复制技术优化内存使用；2.进程终止的8种情况（正常/异常终止）；3.僵尸进程（需主动回收）和孤儿进程（自动接管）的区别；4.exit()与_exit()函数的关键差异；5.wait()函数回收子进程状态的机制及检查宏使用；6.进程空间回收的重要性及策略。重点强调正确处理僵尸进程的必要性，避免系统资源泄漏。
后端springboot框架入门学习--第一篇
m0_65853019的博客
12-15 571
的。
MySQL学习笔记 - 数据库知识深入理解
资源摘要信息: "MySQL学习笔记.zip" MySQL是一种流行的开源关系型数据库管理系统（RDBMS），被广泛应用于网站后台数据存储和管理。本学习笔记涵盖了MySQL的基础知识和一些进阶概念，旨在帮助读者快速掌握MySQL的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值