MySQL

已于 2023-02-12 21:34:44 修改
阅读量484 收藏 1
点赞数 1
分类专栏： 数据库 文章标签： 数据库
于 2020-10-27 15:35:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/XXMRXXX/article/details/108515034
版权

文章目录

1 初识MySQL

JavaEE:企业级Java开发 Web
前端(页面展示数据)
后台(连接点：连接数据库JDBC， 连接前端：控制视图跳转，给前端传递数据)
数据库(存数据，txt excel word)

只会写代码，学好数据库，基本混饭吃
操作系统，数据结构与算法~一个不错的程序猿
离散数学，数字电路，体系结构，编译原理+实战经验~优秀程序猿

1.1 为什么学习数据库

1.岗位需求
2.大数据时代
3.存数据
4.数据库是所有软件体系中最核心的存在

1.2 什么是数据库

数据库(DB DataBase)
概念：数据仓库，软件，安装在操作系统(windows,linux,mac,…)之上的 SQL语句，存储大量数据
作用：存储数据，管理数据

1.3 数据库分类

关系型数据库：(SQL)

  • MySQL, Oracle, Sql Server, DB2, SQLite
  • 通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库：(NoSQL——not only SQL)

  • Redis， MongDB
  • 非关系型数据库，对象存储，通过对象自身的属性来决定。

DBMS(数据库管理系统)

  • 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
  • MySQL， 数据库管理系统！

1.4 MySQL

  • MySQL是一个关系型数据库管理系统， 由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。
  • MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统)应用软件之一。
  • 开源的数据库软件
  • 体积小、速度快、总体拥有成本低
  • 适用于中小型网站或者大型网站，集群。
    官网：https://www.mysql.com/

1.5 安装MySQL

安装建议：

1.尽量不要使用exe，使用exe安装会加入注册表中，不好卸载
2.尽可能使用压缩包安装

安装步骤

  1. 解压
  2. 把这个包放到自己的电脑环境目录下 E:\
  3. 配置环境变量
    在Path中添加 E:\mysql-5.7.19\bin
  4. 新建MySQL配置文件 .ini文件
    内容：
[mysqld]
# 目录一定要换成自己的
basedir=E:\mysql-5.7.19\
datadir=E:\mysql-5.7.19\data\
port=3306
skip-grant-tables
  1. 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld -install(安装mysql)
  2. 再输入mysqld --initialize-insecure --user=mysql初始化数据文件
  3. 然后输入net start mysql再次启动mysql然后用命令mysql -u root -p进入mysql管理界面〔密码可为空)
    启动命令为net start mysql
  4. 进入界面后更改root密码
update mysql.user set authentication_string=password('123456') where user='root' and Host
='localhost';

注意：

这里的指令有一个大错误！！！！
这里的权限设置会导致以后的sqlyog连接虚拟机mysql时出现1130权限错误[解决办法](https://blog.csdn.net/u012151556/article/details/47603925?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.add_param_isCf&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.add_param_isCf)
再输入flush privileges; 刷新权限

  1. 修改my.ini文件删除最后一句skip-grant-tables
  2. 重启mysql即可正常使用
net stop mysql
net start mysql
  1. 连接上测试出现以下结果表示安装成功
    在这里插入图片描述

1.6 安装SQLyog

  1. 无脑安装
  2. 注册信息
  3. 打开连接数据库
  4. 新建一个数据库school
    在这里插入图片描述
  5. 新建一张表student
    在这里插入图片描述

每一个sqlyog的执行操作，本质是对应了一个sql，可以再软件的历史记录中查看

=字段 id, name, age=

  1. 查看表
  2. 自己尝试添加多条记录
    在这里插入图片描述

1.7 连接数据库

命令行连接
SQL语句注释格式
–单行注释
/* 多行注释*/

mysql -uroot -p123456  --连接数据库

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

flush provileges;  -- 刷新权限

----------------------------

--所有的语句都使用分号结尾

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

use school;  --切换数据库 use 数据库名;
--输出 Database changed

show tables;  -- 查看数据库中所有的表

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

create database neuq;  -- 创建一个数据库neuq

exit;  -- 退出连接

-- 单行注释

/*
多
行
注
释
*/

数据库xx语言 CRUD增删改查
DDL:定义
DML:操作
DQL:查询
DCL:控制

2 操作数据库

操作数据库->操作数据库中的表->操作数据库中的表的数据
mysql关键字不区分大小写

2.1 操作数据库

  1. 创建数据库
create database [if not exists] neuq;
  1. 删除数据库
drop database [if exists] neuq;
  1. 使用数据库
-- table 键的上面,如果你的表名或者字段名是一个特殊字符
USE `school`;
  1. 查看数据库
show databases -- 查看所有的数据库

对比：SQLyog的可视化操作
学习思路：

  • 对照sqlyog可视化历史记录查看sql
  • 固定的语法或关键字必须强行记住！

2.2 数据库的列类型(数据类型)

数值

类型描述字节数
tinyint十分小的数据1个字节
smallint较小的数据2个字节
mediumint中等大小的数据3个字节
int(常用)标准的整数4个字节
bigint较大的数据8个字节
float浮点数4个字节
double浮点数8个字节
decimal字符串形式的浮点数金融计算使用

字符串

类型描述大小
char字符串固定大小的0~255
varchar(常用)可变长字符串0~65535
tinytext微型文本2^8-1
text(保存大文本)文本串2^16-1

时间日期

java.util.Date

类型格式描述
dateYYYY-MM-DD日期
timeHH:mm:ss时间格式
datetimeYYYY-MM-DD HH:mm:ss最常用的时间格式
timestamp1970.1.1到现在的毫秒数时间戳
yera数字年份表示

null

  • 没有值，未知
  • 注意，不要使用NULL进行运算，结尾为NULL

2.3 数据库的字段属性(重点)

unsigned

  • 无符号的整数
  • 声明了该列不能声明为负数

zerofill

  • 0填充
  • 不足的位数使用0填充
  • 例：int(3) 输入5 实际显示005

自增

  • 自动在上一条记录的基础上+1（默认）
  • 通常用来设置唯一的主键~index，并且必须是整数
  • 可以自定义主键自增的起始值和步长

非空

  • 如果设置not null，如果不给其赋值，就会报错
  • 如果不设置not null，即为null，不填写值默认为null

default

  • 设置默认的值
  • 例：sex 默认值为男，如果不指定该列的值，则会有默认的值

拓展

/* 
每一个表都必须存在以下五个字段：(未来做项目用的，表示一个记录存在的意义)
	id  主键
	`version`  乐观锁
	is_delete  伪删除
	gmt_create  创建时间
	gmt_update  修改时间
*/

2.4 创建数据库表

-- 目标：创建一个school数据库
-- 创建学生表(列，字段) 使用SQL创建
-- 学号int,登录密码varchar(20),姓名,性别varchar(2),出生日期(datetime),家庭住址,email

-- 注意,使用英文(),表的名称和字段尽量使用``括起来
-- auto_increment自增 
-- 字符串使用''括起来
-- 所有的语句后面加英文逗号,最后一个字段不用加 
-- default 后面跟的字符串是默认内容
-- primary key主键,一般一个表只有一个唯一的主键
create table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default  '匿名' comment '姓名',
	`password` varchar(20) not null default '123456' comment '密码',
	`gender` varchar(2) not null default '女' comment '性别',
	`birthday` datetime default null comment '出生日期',
	`address` varchar(100) default null comment '家庭住址',
	`email` varchar(30) default null comment '邮箱',
	primary key(`id`)
)engine=Innodb default charset=utf8

格式

create table if not exists `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
	`字段名` 列类型 [属性] [索引] [注释],
	...
	`字段名` 列类型 [属性] [索引] [注释],
	primary key(`字段名`)
)[表类型][字符集设置][注释]

常用命令

-- 查看创建数据库的语句
SHOW CREATE DATABASE `school`; 
-- CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */

-- 查看创建表的语句
SHOW CREATE TABLE `student`;
/*
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gender` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(30) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

-- 查看表的具体结构
DESC `student`;

在这里插入图片描述

2.5 数据表的类型

-- 关于数据库引擎
/*
INNODB  默认使用
MYISAM  早期使用
*/
MYISAMINNODB
事务支持不支持支持
数据行锁定支持表锁不支持行锁支持行锁
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为MYISAM两倍

常规使用操作：

  • MYISAM 节约空间，速度较快
  • INNODB 安全性高，事务的处理，支持多表多用户操作

在物理存在的位置

所有的数据库文件都存在data目录下 ，一个文件夹就对应一个数据库
本质还是文件存储
MySQL引擎在物理文件上的区别：

  • INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
  • MYISAM对应文件
    *.frm 表结构的定义文件
    *.MYD 数据文件(data)
    *.MYI 索引文件(index)

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

CHARSET=utf8  
-- 不设置的话，会是mysql默认的字符集编码(不支持中文)
-- MySQL的默认编码是Latin1，不支持中文
-- 在my.ini中配置默认的编码
character-set-server=utf8

修改

-- 修改表名  ALTER TABLE `旧表名` RENAME AS `新表名`;
ALTER TABLE `teacher` RENAME AS `teacher1`;
 
-- 增加表的字段  ALTER TABLE `表名` ADD `字段名` 列属性;
ALTER TABLE `teacher1` ADD `age` INT(10);
ALTER TABLE `teacher1` ADD `sex` VARCHAR(2);

-- 修改表的字段(1.重命名 2.修改约束)
-- ALTER TABLE `表名` MODIFY `字段名` 列属性[]; 
ALTER TABLE `teacher1` MODIFY `age` VARCHAR(3);  -- 修改约束
-- ALTER TABLE `表名` CHANGE `旧字段名` `新字段名` 列属性[];
ALTER TABLE `teacher1` CHANGE `age` `age1` INT(3);  -- 字段重命名
/*
change一般用来字段重命名，但是也可以修改字段类型和约束
modify不能用来字段重命名，只能修改字段类型和约束
一般情况下不通过代码修改！！！
*/

-- 删除表的字段
-- ALTER TABLE `表名` DROP `字段名`;
ALTER TABLE `teacher1` DROP `age1`;

删除

-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS teacher1;

所有的创建和删除操作尽量加上判断是否存在，以免报错

注意：

数据库名、表名、字段名等字符串建议使用``包裹
注释 – /**/
sql关键字大小写不敏感，建议大家写小写
所有的符号都用英文符号

3 MySQL数据管理

3.1 外键

方式一、在创建表的时候，增加约束(比较复杂)

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

-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用)
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT  '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(30) 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 `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束(执行引用) references 引用
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT  '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(30) 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 `约束名(一般是FK_外键字段名)` FOREIGN KEY (`外键字段名`) REFERENCES `引用到的表名`(`外键字段名`);
*/

以上的操作都是物理外键，即数据库级别的外键，不建议使用！(避免数据库过多，删除或者更新有关联表必须考虑外键约束，在开发的时候很痛苦，测试数据极为不便！！)
最佳实现：

  • 数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)
  • 对多表或者外键的操作用程序实现

3.2 DML语言(重点)

数据库意义：数据存储，数据管理
DML语言：数据操作语言

  • insert 添加
  • update 修改
  • delete 删除

3.3 添加insert

insert

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

-- 由于主键自增我们可以省略主键(如果不写表的字段，会一一匹配)
INSERT INTO `grade` VALUES(2, '大三');  
INSERT INTO `grade`(`gradeid`, `gradename`) VALUES(3, '大二'); 
-- 此处为第一个值对应gradeid  第二个值对应gradename 等价于 INSERT INTO `grade`(`gradeid`, `gradename`) VALUES(2, '大三');
-- 注意：INSERT INTO `grade` VALUES('大三'); 会报错 等价于 INSERT INTO `grade`(`gradeid`, `gradename`) VALUES('大三', null);

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

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



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

INSERT INTO `student`(`name`, `password`, `gender`) VALUES('张三', '123', '男');

INSERT INTO `student`(`name`, `password`, `gender`) 
VALUES ('李四', '456', '男'), ('李3', '1456', '男');

-- 省略字段名后面的values必须按顺序写全
INSERT INTO `student` VALUES(7, '李四', 'aaaa', '男', '2000-01-01', 1, '西安', 'email');

语法格式：INSERT INTO表名(字段名1，字段名2，字段名3)VALUES('值1'), ('值2'), ('值3') ,(...)
注意事项：

  1. 字段和字段之间使用英文逗号隔开
  2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
  3. 可以同时插入多条数据，values后面的值，需要使用英文逗号隔开即可values(),(),...

3.4 修改update

update 修改对象 (条件) set 原来的值 = 新值

-- 修改学员名字
-- 把id=1的这个人的名字修改成菜鸟
UPDATE `student` SET `name`='菜鸟' WHERE id = 1;

-- 不指定条件的情况下，会改动表的所有行数据
UPDATE `student` SET `name`='长江七号';

-- 修改多个属性，英文逗号隔开
UPDATE `student` SET `name`='菜鸟', `email`='xxx@qq.com' WHERE id = 1;

语法格式：update 表名 set column_name=value, [column_name=value,...] where [条件]
条件：where 子句 运算符 id等于某个值，大于某个值，在某个区间内修改
操作符返回

操作符含义举例结果
=等于5=6false
<>或=!不等于5<>6或5!=6true
>
<
>=
<=
BETWEEN…AND…在某个范围内BETWEEN 2 AND 5[2, 5]
AND和，类似java的&&5>1 AND 1>2false
OR或，类似java的||5>1 OR 1>2true
IN在字段中值为()的所有数据name IN (‘张三’, ‘李四’)选中姓名为张三李四的所有人
-- 通过多个条件(无上限)定位数据  trim
UPDATE `student` SET `name`='长江' WHERE `name`='菜鸟' AND `gender`='女';

语法格式：update 表名 set column_name = value, [colunm_name = value, …] where [条件]
注意：

  • column_name是数据库的列，尽量带上``
  • 条件，筛选的条件，如果没有指定，则会修改所有的列
  • value,可以是一个具体的值，也可以是一个变量
  • 多个设置的属性之间使用英文逗号隔开
update `student` set `birthday` = CURRENT_TIME WHERE `name` = '长江七号' AND `gender` = '女';

3.5 删除delete

delete命令

语法格式：delete from 表名 [where 条件];

-- 全部删除，避免使用！
DELETE FROM `student`;

-- 删除id=1的数据
DELETE FROM `student` 
WHERE id = 1;

truncate命令

作用：完全清空一个数据库表，表的结构和索引约束不会变

-- 清空 student表
-- truncate 表名
TRUNCATE `student`;

delete 和 truncate 的区别

相同点：

  • 都能删除数据不能删除表结构

不同点：

  • truncate 重新设置自增列 计数器归零

  • truncate 不会影响事务

-- 测试delete 和 truncate 的区别
CREATE TABLE `test` (
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`a` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8
 
INSERT INTO `test` (`a`) VALUES ('1'),('2'),('3'),('4');

DELETE FROM `test`;  -- 不会影响自增

TRUNCATE `test``test`;  -- 自增会归零

了解即可：delete 删除的问题，删除后重启数据库，会出现：

  • INNODB 自增列会从1开始(存在内存中的，断电即失)
  • MYISAM 继续从上一个自增量开始(存在文件中的，不会丢失)

4 DQL查询数据(最重点)

数据库建立

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表

-- 创建年级表
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;

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 创建成绩表
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;

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


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;

-- 插入学生数据 其余自行添加 这里只添加了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');


-- 创建科目表
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;

-- 插入科目数据
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.1 DQL——Data Query Language数据查询语言

  • 所有的查询操作 select
  • 简单复杂的查询
  • 数据库中最核心的语言
  • 使用频率最高

select完整语法：

SELECT
	[ALL] DISTINCT / DISTINCTROW]
		[HIGH_PRIORITY]
		[STRAIGHT_JOIN]
		[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL__BUFFER_RESULT]
		[SQL_CACHE / SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
	select_expr [, select_expr ...]
	[FROM table_references
		[PARTITION partition_list]
	[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}]
	[PROCEDURE procedure_name(argurent_1ist)]
	[INTO OUTFILE 'file_name '
		[CHARACTER SET charset_name]
		export_options
	  |	INTO DUMPFILE 'file_name '
	  |	INTO var_name [， var_name]]
	[FOR UPDATE | LOCK IN SHARE MODE]]

在这里插入图片描述

where group by having order by limit等顺序不可改变

4.2 指定查询字段

-- 查询全部的学生
-- 格式  SELECT `字段名` FROM `表名`;
SELECT * FROM `student`;

SELECT * FROM `result`;

-- 查询指定字段
SELECT `studentno`, `studentname` FROM `student`;

-- 使用别名，就是给结果起一个名字 AS 可以给字段或表起别名
SELECT `studentno` AS 学号, `studentname` AS 学生姓名 FROM `student` AS 学生表;
-- 或者使用空格代替as效果一样
SELECT `studentno` 学号, `studentname` 学生姓名 FROM `student` 学生表;

-- 函数 concat(a, b)
SELECT CONCAT('姓名：', studentname) AS 姓名 FROM `student`;

语法格式：

select `字段`,... from `表名`

有的时候，列名字不能完全传达其意义时，我们可以起别名
格式：

字段名 AS 别名
或 字段名 空格 别名

去重 distinct

作用：去除select查询出来的结果中重复的数据，重复数据只显示一条

-- 查询哪些同学有成绩
SELECT * FROM `result`;  -- 查询全部的考试成绩

SELECT `studentno` FROM `result`;  -- 查询哪些同学参加了考试

-- 发现重复数据，去重 distinct
SELECT DISTINCT `studentno` FROM `result`;

数据库的列(表达式)

-- 查询SQL版本(函数)
SELECT VERSION();

-- 基本运算(表达式)
SELECT 100*3-25 AS 计算结果;

-- 查询自增的步长(变量)
SELECT @@auto_increment_increment;

-- 学员考试成绩 +1 查看
SELECT `studentno`, `studentresult`+1 AS '提分后成绩' FROM `result`;

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量…

select 表达式 from 表名;

4.3 where 条件子句

作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成。

逻辑运算符

运算符语法描述
and &&a and b a&&b逻辑与，两个都为真，结果为真
or ||a or b a||b逻辑或，其中一个为真，结果为真
Not !not a !a逻辑非，真为假，假为真
-- =================================where=================================
-- 查询所有学生成绩
SELECT `studentno`, `studentresult` FROM `result`;

-- 查询考试成绩在95~100分之间的
SELECT `studentno`, `studentresult` FROM `result`
WHERE `studentresult` >= 95 AND `studentresult` <= 100;

-- and 换成 && 
SELECT `studentno`, `studentresult` FROM `result`
WHERE `studentresult` >= 95 && `studentresult` <= 100;

-- 模糊查询(区间)
SELECT `studentno`, `studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100;

-- 上面三句表达的意思是一样的

-- 除了1000号学生之外的学生的成绩
SELECT `studentno`, `studentresult` FROM `result`
WHERE `studentno` != 1000;

-- != 换成 not 注意位置！！！
SELECT `studentno`, `studentresult` FROM `result`
WHERE NOT `studentno`=1000;

模糊查询：比较运算符

运算符语法描述
IS 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, a3)如果a是(a1, a2, a3)其中某一个值，则结果为真
-- =================================模糊查询=================================

-- ==============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 '_洋%';


-- ==============in==============
-- 查询1001,1002,1003号学员信息
SELECT `studentno`, `studentname` FROM `student` 
WHERE `studentno` IN (1001, 1002, 1003);

-- 查询在北京的学生
SELECT `studentno`, `studentname` FROM `student` 
WHERE `address` IN ('北京');


-- ==============null 和 not null==============
-- 查询地址为空的学生 此处空表示 null 或 ''(空字符串)
SELECT `studentno`, `studentname` FROM `student` 
WHERE `address`='' OR `address` IS NULL;

-- 查询有出生日期的同学 -- 出生日期不为空
SELECT `studentno`, `studentname` FROM `student` 
WHERE `borndate` IS NOT NULL;

-- 查询没有出生日期的同学 -- 出生日期、为空
SELECT `studentno`, `studentname` FROM `student` 
WHERE `borndate` IS NULL;

4.4 联表查询

join

在这里插入图片描述
七种join
在这里插入图片描述

-- ================================= 联表查询 join =================================
-- 查询参加了考试的同学的学号 姓名 科目编号 成绩
SELECT * FROM `student`;
SELECT * FROM `result`;

/*
	思路：
		1.分析需求：分析查询的字段来自哪些表(连接查询)
		2.确定使用哪种连接查询 7种
		3.确定交叉点(两个表中哪个字段是相同的) 判断的条件：学生表中的 `studentno` = 成绩表中的 `studentno`
		
*/

SELECT S.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` AS S
INNER JOIN `result` AS R
WHERE S.`studentno` = R.`studentno`;

-- RIGHT JOIN
SELECT S.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` 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`;
操作描述
inner join如果表中至少有一个匹配，就返回行
left join返回左表中所有的值，即使右表中没有匹配
right join返回右表中所有的值，即使左表中没有匹配
-- ================================= 联表查询 join =================================
-- 查询参加了考试的同学的学号 姓名 科目编号 成绩
SELECT * FROM `student`;
SELECT * FROM `result`;

/*
	思路：
		1.分析需求：分析查询的字段来自哪些表(连接查询)
		2.确定使用哪种连接查询 7种
		3.确定交叉点(两个表中哪个字段是相同的) 判断的条件：学生表中的 `studentno` = 成绩表中的 `studentno`
		
*/

SELECT S.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` AS S
INNER JOIN `result` AS R
WHERE S.`studentno` = R.`studentno`;

-- join (连接的表) on (判断的条件) 连接查询
-- where   等值查询

-- 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`;

/*
	right join 查询右边的表中的所有数据
	left join  查询左边的表中的所有数据
	连接的原因：因为字段并非来自同一张表，所以需要联表
*/
-- 查询缺考的同学
SELECT S.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` AS S
LEFT JOIN `result` AS R
ON S.`studentno` = R.`studentno`  -- 到这里查询的是所有同学的信息(因为字段并非来自同一张表，所以需要联表)
WHERE `studentresult` IS NULL;

-- 思考题(查询了参加考试的同学的信息： 学号 学生姓名 科目名 分数)
-- 遵循思路
/*
	思路：
		1.分析需求：分析查询的字段来自哪些表 `result` `student` `subject`(连接查询)
		2.确定使用哪种连接查询 7种
		3.确定交叉点(两个表中哪个字段是相同的) 
		判断的条件：
			学生表中的 `studentno` = 成绩表中的 `studentno`
			成绩表中的 `subjectno` = 科目表中的 `subjectno`
		
*/

-- 自己写的
SELECT ST.`studentno`, `studentname`, `subjectname`, `studentresult`
FROM `result` AS R 
INNER JOIN `student` AS ST
ON R.`studentno` = ST.`studentno` 
INNER JOIN `subject` AS SB 
ON SB.`subjectno` = R.`subjectno`;

-- 教的
SELECT ST.`studentno`, `studentname`, `subjectname`, `studentresult`
FROM `student` AS ST
RIGHT JOIN `result` AS R
ON R.`studentno` = ST.`studentno`  -- 到这里查询的是参加考试的人的 studentno studentname studentresult 信息
INNER JOIN `subject` AS SB
ON SB.`subjectno` = R.`subjectno`;

-- 我要查询哪些数据 select...
-- 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件 
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表
创建表

CREATE TABLE `school`.`category`( 
	`categoryid` INT(3) NOT NULL COMMENT 'id', 
	`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
	`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
	PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB AUTO_INCREMENT=9 CHARSET=utf8;

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
INSERT INTO `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
INSERT INTO `school`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
INSERT INTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES ('4', '3', '数据库');
INSERT INTO `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
INSERT INTO `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES ('6', '3', 'web开发');
INSERT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('7', '5', 'ps技术');

父类表

categoryidcategoryName
2信息技术
3软件开发
5美术设计

子类表

pidcategoryidcategoryName
34数据库
28办公信息
36web开发
57ps技术

在这里插入图片描述
操作：查询父类对应的子类关系

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术
-- 查询父子信息
SELECT c1.`categoryname` AS '父栏目', c2.`categoryname` AS '子栏目'
FROM `category` AS c1, `category` AS c2
WHERE c1.`categoryid` = c2.`pid`;

练习：

-- ================================= 练习 =================================
-- 查询学员所属的年级(学号， 学生姓名， 年级名称)
SELECT s.`studentno`, `studentname`, `gradename`
FROM `student` s 
INNER JOIN `grade` g
ON s.`gradeid` = g.`gradeid`;

SELECT s.`studentno`, `studentname`, `gradename`
FROM `student` s 
LEFT JOIN `grade` g
ON s.`gradeid` = g.`gradeid`;

SELECT s.`studentno`, `studentname`, `gradename`
FROM `grade` g
RIGHT JOIN `student` s 
ON s.`gradeid` = g.`gradeid`;


-- 查询科目所属的年级(科目名称， 年级名称)
SELECT `subjectname`, `gradename`
FROM `subject` s
INNER JOIN `grade` g
ON s.`gradeid` = g.`gradeid`;

SELECT `subjectname`, `gradename`
FROM `subject` s
LEFT JOIN `grade` g
ON s.`gradeid` = g.`gradeid`;

SELECT `subjectname`, `gradename`
FROM `grade` g
RIGHT JOIN `subject` s
ON s.`gradeid` = g.`gradeid`;

-- 查询参加了 '高等数学-1' 考试的同学的信息(学号， 学生姓名， 科目名， 分数)
SELECT s.`studentno`, `studentname`, `subjectname`, `studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
WHERE `subjectname` = '高等数学-1';

4.5 分页和排序

分页

-- ================================= 分页 limit 和 排序 order by =================================
-- 假设有100万条数据
/*
	为什么要分页？
		1.缓解数据库压力
		2.给人体验更好
*/

-- 瀑布流分页————应用百度图片 抖音视频

-- 每页只显示1条数据
-- 语法格式：limit 起始页, 页面大小
-- 网页应用：当前页，总的页数，页面大小
SELECT s.`studentno`, `studentname`, `subjectname`, `studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` DESC
LIMIT 1, 1;
-- limit 0, 1;	-- 表示第一页	
-- 如果一共有11条数据每页显示五条数据则第一页为 limit 0, 5;  第二页为 limit 5, 5; 第三页为 limit 10, 5;
-- 第N页为 limit (N-1) * PageSize, 
-- [PageSize  PageSize页面大小]  
-- [(N-1) * PageSize 起始值] 
-- [N 第N页]
-- [(数据总数 / 页面大小)做上取整 = 总页数]

语法格式：

limit (查询起始下标， PageSize)

排序

-- 排序：升序 ASC 降序 DESC
-- 格式 ORDER BY 字段 ASC/DESC
-- 查询参加了 '高等数学-1' 考试的同学的信息(学号， 学生姓名， 科目名， 分数)，并按成绩降序输出
SELECT s.`studentno`, `studentname`, `subjectname`, `studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`																	
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` DESC;

-- 练习
-- 查询 java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息(学号，姓名， 课程名称， 分数)
SELECT s.`studentno`, s.`studentname`, su.`subjectname`, r.`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
WHERE `subjectname` = 'java第一学年' AND `studentresult` > 80 -- 课程为java第一学年并且分数大于80
ORDER BY `studentresult` DESC -- 降序排名
LIMIT 0, 10;  -- 成绩排名前十

4.6 子查询

where(值是固定的，这个值是计算出来的)
本质：在where语句中嵌套一个子查询语句
where(select … )

-- ================================= where(子查询) =================================
-- 1.查询数据库结构-1的所有考试结果(学号，科目名称，成绩)，降序排列
-- 方式一 使用连接查询
SELECT `studentno`, r.`subjectno`, `studentresult`
FROM `result` r
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC;

-- 方式二 使用子查询 由里及外
SELECT `studentno`, `subjectno`, `studentresult`
FROM `result` r
-- 这里要找的有关联的字段就是应该在联表的时候的字段 ON su.`subjectno` = r.`subjectno`
WHERE `subjectno` = (
	SELECT `subjectno` 
	FROM `subject` 
	WHERE `subjectname` = '数据库结构-1'
	)
ORDER BY `studentresult` DESC;

-- 查询所有参加 数据库结构-1 课程的学生的学号
SELECT `subjectno` FROM `subject` WHERE `subjectname` = '数据库结构-1';

-- 查询分数不小于80分的的学生的学号和姓名
SELECT DISTINCT s.`studentno`, `studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` >= 80;

-- 在这个基础上增加一个科目，课程必须是 高等数学-2
SELECT DISTINCT s.`studentno`, `studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` su
ON su.`subjectno` = r.`studentno`
WHERE `studentresult` >= 80 AND `subjectname` = '高等数学-2';

-- 在这个基础上增加一个科目，课程必须是 高等数学-2
-- 查询 高等数学-2 的编号
-- 使用子查询，减少联表
SELECT DISTINCT s.`studentno`, `studentname`
FROM `student` s
INNER JOIN `result` r
ON su.`subjectno` = r.`studentno`
-- 这里要找的有关联的字段就是应该在联表的时候的字段 ON su.`subjectno` = r.`subjectno`
WHERE `studentresult` >= 80 AND `subjectno` = (
	SELECT `subjectno` 
	FROM `subject` 
	WHERE `subjectname` = '高等数学-2'
	);

-- 进一步改造 对 >= 80 使用子查询
-- 就可以不用联表
SELECT DISTINCT s.`studentno`, `studentname`
FROM `student` s
WHERE `subjectno` IN (
	-- 这里要找的有关联的字段就是应该在联表的时候的字段 ON s.`studentno` = r.`studentno`
	SELECT DISTINCT `studentno` 
	FROM `result` 
	WHERE `studentresult` >= 80
	AND
	-- 这里要找的有关联的字段就是应该在联表的时候的字段 ON su.`subjectno` = r.`subjectno`
	`subjectno` = (
		SELECT `subjectno` 
		FROM `subject` 
		WHERE `subjectname` = '高等数学-2'
		)
	);


-- 整合写舒服的很
-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`studentno`, `studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
WHERE `subjectname` = '高等数学-2' AND `studentresult` >= 80;


-- 练习:查询 C语言-1 成绩前5名的同学的信息（学号，姓名，分数)
-- 方式一 联表
SELECT s.`studentno`, `studentname`, `studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
WHERE `subjectname` = 'C语言-1' 
ORDER BY `studentresult` DESC
LIMIT 0, 5;

-- 方式二 子查询(不知道自己写的对不对)
SELECT r.`studentno`, `studentname`, `studentresult`
FROM `student` s
WHERE `studentno` = (
	SELECT `studentno` 
	FROM `result`
	WHERE `subjectno` = (
		SELECT `subjectno`
		FROM `subject`
		WHERE `subjectname` = 'C语言-1' 
		)
	ORDER BY `studentresult` DESC
	)
LIMIT 0, 5;

4.7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分， 平均分大于80分
SELECT `subjectname`, AVG(`studentresult`) AS '平均分', MAX(`studentresult`) '最高分', MIN(`studentresult`) '最低分'
FROM `result` r
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
-- where AVG(`studentresult`) > 80 
/*错误代码： 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE AVG(`studentresult`) > 80 LIMIT 0, 1000' at line 7*/
GROUP BY r.`subjectno`  -- 通过课程号分组
HAVING AVG(`studentresult`) > 80 ; -- HAVING '平均分' > 80 ; -- 效果一样 
-- 使用group by后进行条件筛选使用having 不能在group by上面使用where 来进行筛选

4.8 select小结

在这里插入图片描述
注意：where后面不能使用聚合函数，使用group by时只能搭配having进行条件筛选

5 MySQL函数

函数和操作符

5.1 常用函数

-- ================================= 常用函数 =================================
-- 数学运算
SELECT ABS(-8);  -- 绝对值
SELECT CEILING(5.4); -- 向上取整
SELECT FLOOR(5.4); -- 向下取整
SELECT RAND(); -- 返回一个0-1之间的随机数
SELECT SIGN(-5);  -- 返回参数的符号 0返回0  正数返回1  负数返回-1

-- 字符串
SELECT CHAR_LENGTH('即使再小的帆也能远航')  -- 字符串长度
SELECT CONCAT('我', '爱', '你们')  -- 拼接字符串
SELECT LOWER('Kuangshen')   -- 小写字母
SELECT UPPER('KuangShen')  -- 大写字母
SELECT INSTR ('kuangshen' ,'h')   -- 返回第一次出现的子串的索引
SELECT REVERSE('清晨我上马')  -- 反转
SELECT INSERT('我爱编程helloworld', 1, 2, '超级热爱')  -- 查询，从某个位置开始替换某个长度 从第一个字符开始选区长度问2的字符串 '我爱' 被 '超级热爱' 替换
SELECT REPLACE('狂神说坚持就能成功', '坚持', '努力')  -- 替换出现的指定字符串 '坚持' 替换成 '努力'
SELECT SUBSTR('狂神说坚持就能成功', 4, 6)  -- 返回指定的子字符串（源字符串，截取的位置，截取的长度) 从第四个开始截取六个

-- 把姓周的同学改成姓邹的同学
SELECT REPLACE(`studentname`, '张', '邹')
FROM `student`
WHERE `studentname` LIKE '张%';

-- 时间和日期函数 
SELECT CURRENT_DATE()  -- 获取当前日期  2020-09-12
SELECT CURDATE()  -- 获取当前日期  2020-09-12
SELECT NOW()  -- 获取当前时间 2020-09-12 22:51:00
SELECT LOCALTIME()  -- 获取当地时间 2020-09-12 22:51:10
SELECT SYSDATE()  -- 获取系统时间 2020-09-12 22:51:20
SELECT YEAR(NOW());  -- 获取当前年份 2020 
SELECT MONTH(NOW());  
SELECT DAY(NOW());  
SELECT HOUR(NOW());  
SELECT MINUTE(NOW());  
SELECT SECOND(NOW());  

-- 系统
SELECT SYSTEM_USER()  -- 系统账户root@localhost
SELECT USER()  -- 简写版root@localhost
SELECT VERSION() -- SQL版本 5.7.19

5.2 聚合函数(常用)

函数名描述
count()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
……
-- ================================= 聚合函数 =================================
-- 统计表中数据条数
SELECT COUNT(`studentname`) FROM `student`  -- count(指定列) 会忽略所有的null值
SELECT COUNT(*) FROM `student`  -- count(*) 不会忽略所有的null值  本质计算行数
SELECT COUNT(1) FROM `student`  -- count(1) 不会忽略所有的null值  本质计算行数


SELECT SUM(`studentresult`) AS '总和' FROM `result`
SELECT AVG(`studentresult`) AS '平均分' FROM `result`
SELECT MAX(`studentresult`) AS '最高分' FROM `result`
SELECT MIN(`studentresult`) AS '最低分' FROM `result`

-- 查询不同课程的平均分，最高分，最低分
-- 核心：根据不同课程分组
SELECT `subjectname`, AVG(`studentresult`), MAX(`studentresult`), MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
GROUP BY r.`subjectno`;  -- 通过课程号分组



-- 查询不同课程的平均分，最高分，最低分， 平均分大于80分
SELECT `subjectname`, AVG(`studentresult`) AS '平均分', MAX(`studentresult`) '最高分', MIN(`studentresult`) '最低分'
FROM `result` r
INNER JOIN `subject` su
ON su.`subjectno` = r.`subjectno`
-- where AVG(`studentresult`) > 80 
/*错误代码： 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE AVG(`studentresult`) > 80 LIMIT 0, 1000' at line 7*/
GROUP BY r.`subjectno`  -- 通过课程号分组
HAVING AVG(`studentresult`) > 80 ; -- HAVING '平均分' > 80 ; -- 效果一样 
-- 使用group by后进行条件筛选使用having 不能在group by上面使用where 来进行筛选

5.3 数据库级别的MD5加密

什么是MD5？
主要增强算法复杂度和不可逆性
MD5不可逆，具体的值的MD5是一样的
MD5破解网站的原理：背后有一个字典，MD5加密后的值，加密前的值

-- ================================= 测试MD5加密 =================================
CREATE TABLE IF NOT EXISTS `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, 'zhangsan', '123456'), (2, 'lisi', '123456'), (3, 'wangwu', '123456');

-- 加密
UPDATE `testmd5` SET `pwd` = MD5(`pwd`) WHERE id = 1;
UPDATE `testmd5` SET `pwd` = MD5(`pwd`)  -- 加密全部密码

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4, 'xiaoming', MD5('123456'));

-- 如何校验
-- 将用户传递进来的密码进行md5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='xiaoming' AND `pwd`=MD5('123456')

6 事务

6.1 什么是事务

要么都成功，要么都失败。
————————————
1.SQL执行， A给B转账
2.SQL执行， B收到A的转账
————————————
将一组SQL放在一个批次中执行

事务原则：ACID原则 原子性，一致性，隔离性，持久性 (脏读，幻读)

参考博客：事务ACID理解
原子性（Atomicity）
要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
事务一旦提交就不可逆转

隔离所导致的一些问题：

脏读：
指一个事务读取了另外一个事务未提交的数据。

不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

执行事务

-- ================================= 事务 =================================
-- MySQL是默认开始事务自动提交的
-- 事务自动提交设置
SET autocommit = 0  -- 关闭
SET autocommit = 1  -- 开启

-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交

-- 事务开启
START TRANSACTION  -- 标记一个事务的开始，从这个之后的SQL都在同一个事务内
  
-- 提交：持久化  成功
COMMIT

-- 回滚：回到原来的样子  失败
ROLLBACK

-- 事务结束
SET autocommit = 1  -- 开启自动提交

-- 了解
SAVEPOINT 保存点名  -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

模拟场景

-- 转账
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8__general_ci;
USE `shop`

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


INSERT INTO `account`(`name`, `money`)
VALUES('a', 2000.00), ('b', 10000.00);

-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启事务
UPDATE `account` SET money = money - 500 WHERE `name` = 'a';  -- a减500
UPDATE `account` SET money = money + 500 WHERE `name` = 'b';  -- b加500

COMMIT;  -- 提交事务

ROLLBACK;  -- 回滚

SET autocommit = 1;  -- 恢复自动提交

7 索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质：索引是数据结构。

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

7.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可能有多个

  • 主键索引 primary key
    唯一的标识，主键不可重复，只能有一个列作为主键
  • 唯一索引 unique key
    避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • 常规索引 key/index
    默认的，index、key关键字来设置
  • 全文索引 fulltext
    在特定的数据库引擎下才有
    快速定位数据

MySQL优化

-- ================================= 索引 =================================
-- 1.在创家标的时候给字段增加索引
-- 2.创建完毕后，增加索引


-- 显示所有的索引信息
 SHOW INDEX FROM `student`;

-- 增加一个全文索引  ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(列名);
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);

-- explain 分析sql的执行情况
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 '用户昵称',
`eamil` 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=utf8mb4 COMMENT 'app用户表'


-- 插入100万条数据
DELIMITER $$  -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data1()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		-- 插入语句
		INSERT INTO app_user(`name`, `eamil`, `phone`, `gender`, `password`, `age`)
		VALUES(CONCAT('用户', i), '1234567@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_data1();


SELECT * FROM app_user WHERE `name` = '用户99999';

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999';

-- 索引名称格式 id_表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

在这里插入图片描述
索引在小数据量的时候用处不大，但是在大数据的时候，区别十分明显！

7.3 索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不要加索引
  • 索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引
Btree:INNODB使用的数据结构
阅读：MySQL索引背后的数据结构及算法原理

8 权限管理和数据库备份

8.1 用户管理

SQLyog可视化管理

在这里插入图片描述

SQL命令操作

-- ================================= 权限操作 =================================
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码';
CREATE USER dongyang IDENTIFIED BY '123456';

-- 修改密码(修改当前用户密码)
SET PASSWORD  = PASSWORD('123456');  

-- 修改指定用户密码
SET PASSWORD FOR dongyang = PASSWORD('111111');

-- 重命名 RENAME USER 原名字 TO 新名字;
RENAME USER dongyang TO fangfang;

-- 用户授权
-- ALL PRIVILEGES 不包括给别人授权的grant权限
GRANT ALL PRIVILEGES ON *.* TO fangfang;

-- 查询权限
SHOW GRANTS FOR fangfang  -- 查看指定用户的权限

-- 查询root权限
SHOW GRANTS FOR root@localhost;  
-- ROOT用户的权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 revoke
REVOKE ALL PRIVILEGES ON *.* FROM fangfang;

-- 删除用户
DROP USER dongyang;

用户表：mysql.user
本质：对这张表进行增删改查

8.2 MySQL备份

为什么要备份？

  • 保证重要的数据不丢失

  • 数据转移
    MySQL数据库备份的方式

  • 直接拷贝物理文件 E:\mysql-5.7.19\data

  • 在SQLyog这种可视化工具中手动导出
    在想要导出的表或库中，右键，选择备份/导出，选择备份数据库，转储到SQL…
    在这里插入图片描述

  • 使用命令行导出 mysqldump 命令行使用

-- mysq1dump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql
-- 导出多张表
-- mysq1dump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 ... > 物理磁盘位置/文件名
 mysqldump -hlocalhost -uroot -p123456 school student result  > D:/a.sql  -- 多张表导入到同一个文件
-- 导入 登录的情况下，先切换到要导入的数据库 use 数据库名; 然后 source 备份文件
source d:/a.sql

-- 登录的同时导入
mysql -u用户名 -p密码 库名 < 备份文件地址

9 规范数据库设计

9.1 为什么需要设计？

当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计:

  • 数据冗余，浪费空间
  • 数据差如何删除都会麻烦，产生异常[屏蔽使用物理外键]
  • 程序性能差
    良好的数据库设计
  • 节省内存空间
  • 保证数据的完整性
  • 方便我们开发

软件开发中关于数据库的设计

  • 分析需求：分析业务和需要处理的数据库的需求
  • 概要设计：设计关系图 E-R图

设计数据库的步骤(个人博客)

  • 收集信息，分析需求
    用户表(用户登录注销，用户的个人信息，写博客，创建分类)
    分类表(文章分类，谁创建的)
    文章表(文章信息)
    评论表
    友链表(友链信息)
    自定义表(系统信息，某个关键的字，或者一些主字段) key：value

  • 标识实体类(把需求落地到每个字段)

  • 标识实体之间的关系
    写博客user–>blog
    创建分类user–>category
    关注user–>user
    友链links
    评论user–>user–>blog

9.2 三大范式

为什么需要数据规范化？

  • 信息重复
  • 更新异常
  • 插入异常 无法正常显示信息
  • 删除异常 丢失有效信息

三大范式

三大范式的理解
第一范式(1NF)

原子性，保证每一列不可再分

第二范式(2NF)

前提：满足第一范式，
每张表只描述一件事情

第三范式(3NF)

前提：满足第一范式和第二范式，
需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范数据库的设计

规范性和性能的问题
关联查询的表不得超过三张表(阿里)

  • 考虑商业化的需求(成本， 用户体验)，数据库性能更加重要
  • 在规范性能问题的时候，需要适当的考虑一下规范性
  • 故意给某些表增加一下冗余的字段(从多表查询变为单表查询)
  • 故意增加一些计算列(从大数据量降低为小数据量的查询，索引)

10 JDBC(重点)

10.1 数据库驱动

驱动：声卡，显卡，数据库
在这里插入图片描述
我们的程序会通过数据库驱动跟数据库打交道。

10.2 JDBC

SUN公司为了简化开发人员对数据库的统一的操作，提供了一个Java操作数据库的规范——JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC接口的操作即可。
在这里插入图片描述

java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

10.3 第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

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

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

1.创建一个普通项目
2.导入数据库驱动
在这里插入图片描述
3.编写测试代码

/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson01;

import java.sql.*;

/*
    我的第一个JDBC程序
 */
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");  //固定写法，加载驱动

        // 2. 用户信息和URL
        // useUnicode=true&characterEncoding=utf8&useSSL=true
        // 三个参数分别表示支持中文编码，设置中文字符集为utf-8，使用安全的连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        // 3. 连接成功，数据库对象 Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4. 执行SQL对象 Statement执行SQL的对象
        Statement statement = connection.createStatement();

        // 5. 执行SQL的对象，去执行SQL
        String sql = "SELECT * FROM `users`";
        ResultSet resultSet = statement.executeQuery(sql);  // 返回的结果集，结果集中封装了我们全部查询出来的结果

        while (resultSet.next()) {
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("NAME"));
            System.out.println("password=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
            System.out.println("====================================");
        }

        // 6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

  1. 加载驱动
  2. 连接数据库 DriverManager
  3. 获得执行sql的对象 Statement
  4. 获得返回的结果集
  5. 释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver()); 原来的加载驱动方式，实际跟反射获得类对象效果一样
Class.forName("com.mysql.jdbc.Driver");  //固定写法，加载驱动

// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚 
connection.rollback();
connection.commit();
connection.setAutoCommit(true);

URL

// useUnicode=true&characterEncoding=utf8&useSSL=true
// 三个参数分别表示支持中文编码，设置中文字符集为utf-8，使用安全的连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

// mysql -- 3306
// jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
// oracle -- 1521
// jdbc:oracle:thin:@localhost:8521:sid

Statement 执行SQL的对象 prepareStatement执行SQL的对象

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

ResultSet 查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultSet.getObject();  //在不知道列类型的情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getArray();
...

遍历，指针

resultSet.beforeFirst();  // 光标移动到最前面，表示重新从头开始
resultSet.afterLast();  // 光标移动到最后面，表示从最后开始
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对象。

CRUD操作-create

使用execuUpdate(String sql)方法完成数据添加操作，示例操作

Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0) {
	System.out.println("插入成功！！！");
}

CRUD操作-delete

使用execuUpdate(String sql)方法完成数据添加操作，示例操作

Statement st = conn.createStatement();
String sql = "delete from user where id = 1";
int num = st.executeUpdate(sql);
if(num>0) {
	System.out.println("删除成功！！！");
}

CRUD操作-update

使用execuUpdate(String sql)方法完成数据添加操作，示例操作

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

CRUD操作-read

使用execuUpdate(String sql)方法完成数据添加操作，示例操作

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

代码实现

  1. 提取工具类
/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson02.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1. 驱动只用加载一次
            Class.forName(driver);
            } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
  1. 编写增删改的方法 executeUpdate()

insert

/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson02;

import pers.dongyang.lesson02.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection 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(10, 'dongyang', '123456', '76305015@qq.com', '2020-01-02');";

            int i = statement.executeUpdate(sql);  // 返回结果为受影响的行数
            if (i > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

delete

/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson02;

import pers.dongyang.lesson02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();  //获取数据库连接
            statement = connection.createStatement();  //获得SQL的执行对象
            String sql = "DELETE FROM `users` WHERE `id`=10;";

            int i = statement.executeUpdate(sql);  // 返回结果为受影响的行数
            if (i > 0) {
                System.out.println("删除成功！");
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

update

/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson02;

import pers.dongyang.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();  //获取数据库连接
            statement = connection.createStatement();  //获得SQL的执行对象
            String sql = "UPDATE `users` SET `NAME`='fangfang', `email`='gjgja@163.com', `birthday`='2021-.-02-02' WHERE `id`=1;";

            int i = statement.executeUpdate(sql);  // 返回结果为受影响的行数
            if (i > 0) {
                System.out.println("修改成功！");
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}
  1. 查询
/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson02;

import pers.dongyang.lesson02.utils.JdbcUtils;

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

public class TestQuery {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            //SQL
            String sql = "SELECT * FROM `users` WHERE `id`=1;";
            resultSet = statement.executeQuery(sql);  // 返回查询结果

            while (resultSet.next()) {
                System.out.println(resultSet.getString("NAME"));  //输出id=1的用户的名字
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

SQL注入的问题

SQL存在漏洞，会被攻击导致数据泄露，SQL会被拼接 or

/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson02;

import pers.dongyang.lesson02.utils.JdbcUtils;

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

/*
    SQL注入
 */
public class SQLInjection {
    public static void main(String[] args) {
        // login("fangfang", "123456");  //正常登录
        login(" 'or 'version()>0", " 'or '1=1" );  //'or 'version()>0  或者 '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();

            //SQL
            // SELECT * FROM `users` WHERE `NAME`='" + username + "' AND `PASSWORD`='" + password + "';
            // SELECT * FROM `users` WHERE `NAME`='" + " 'or '1=1" + "' AND `PASSWORD`='" + " 'or '1=1" + "';
            String sql = "SELECT * FROM `users` WHERE `NAME`='" + username + "' AND `PASSWORD`='" + password + "';";
            resultSet = statement.executeQuery(sql);  // 返回查询结果

            while (resultSet.next()) {
                System.out.println(resultSet.getString("NAME"));  //输出id=1的用户的名字
                System.out.println(resultSet.getString("PASSWORD"));  //输出id=1的用户的密码
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

10.5 PrepareStatement对象

PrepareStatement可以防止SQL注入，并且效率更高！

  1. 增加
/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson03;

import pers.dongyang.lesson02.utils.JdbcUtils;

import java.sql.*;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;  // 使用PreparedStatement可以避免SQL注入
        // ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符代替参数
            String sql = "INSERT INTO `users`(`id`, `NAME`, `PASSWORD`, `email`, `birthday`) VALUES(?, ?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);  // 预编译SQL，先写SQL，不执行

            // 手动给参数赋值
            preparedStatement.setInt(1, 4);  // id
            preparedStatement.setString(2, "dongdong");  // name
            preparedStatement.setString(3, "123456");  // password
            preparedStatement.setString(4, "123456@qq.com");  // email
            /* 注意点:  sql.Date 数据库 java.sql.Date()
                       util.Date Java  new Date().getTime() 获得时间戳
             */
            // new java.sql.Date() 需要一个时间戳参数 通过new Date().getTime() 获得时间戳
            preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));  // birthday

            // 执行
            int i = preparedStatement.executeUpdate();  //在使用Statement的时候需要传入参数sql语句
            if (i > 0) {
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }
    }
}
  1. 删除
/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson03;

import pers.dongyang.lesson02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;  // 使用PreparedStatement可以避免SQL注入
        // ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符代替参数
            String sql = "DELETE FROM `users` WHERE `id`=?;";
            preparedStatement = connection.prepareStatement(sql);  // 预编译SQL，先写SQL，不执行

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

            // 执行
            int i = preparedStatement.executeUpdate();  //在使用Statement的时候需要传入参数sql语句
            if (i > 0) {
                System.out.println("删除成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }
    }
}
  1. 修改
/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson03;

import pers.dongyang.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;  // 使用PreparedStatement可以避免SQL注入
        // ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符代替参数
            String sql = "UPDATE `users` SET `NAME`=?, `email`=?, `birthday`=? WHERE `id`=?;";
            preparedStatement = connection.prepareStatement(sql);  // 预编译SQL，先写SQL，不执行

            // 手动给参数赋值
            preparedStatement.setString(1, "dongdong");  // name
            preparedStatement.setString(2, "123456@qq.com");  // email
            /* 注意点:  sql.Date 数据库 java.sql.Date()
                       util.Date Java  new Date().getTime() 获得时间戳
             */
            // new java.sql.Date() 需要一个时间戳参数 通过new java.util.Date().getTime() 获得时间戳
            preparedStatement.setDate(3, new java.sql.Date(new java.util.Date().getTime()));  // birthday
            preparedStatement.setInt(4, 1);  // id


            // 执行
            int i = preparedStatement.executeUpdate();  //在使用Statement的时候需要传入参数sql语句
            if (i > 0) {
                System.out.println("修改成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }
    }
}
  1. 查询
/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson03;

import pers.dongyang.lesson02.utils.JdbcUtils;

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


public class TestQuery {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;  // 使用PreparedStatement可以避免SQL注入
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            // 区别
            // 使用 ? 占位符代替参数
            String sql = "SELECT * FROM `users` WHERE `id`=?;";  // 编写SQL
            preparedStatement = connection.prepareStatement(sql);  // 预编译SQL，先写SQL，不执行

            // 手动给参数赋值
            preparedStatement.setInt(1, 1);  // id

            // 执行
            resultSet = preparedStatement.executeQuery();  // 返回查询结果

            while (resultSet.next()) {
                System.out.println(resultSet.getString("NAME"));  //输出id=1的用户的名字
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}
  1. 防止SQL注入
/* Java

author:GrandNovice  

time:2020/9/14

*/
package pers.dongyang.lesson03;

import pers.dongyang.lesson02.utils.JdbcUtils;

import java.sql.*;

/*
    SQL注入
 */
public class SQLInjection {
    public static void main(String[] args) {
        // login("fangfang", "123456");  //正常登录
        // login(" 'or 'version()>0", " 'or '1=1" );  //'or 'version()>0  或者 'or '1=1都可以实现

        // login("dongdong", "123456");
        login(" ''version()>0", "123456" );
    }

    // 登录业务
    public static void login(String username, String password) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            /*
                PreparedStatement防止SQL注入的本质，把传递进来的参数当作字符
                假设其中存在转移字符，就直接忽略 引号会被直接转义
             */

            //SQL
            // SELECT * FROM `users` WHERE `NAME`='" + username + "' AND `PASSWORD`='" + password + "';
            // SELECT * FROM `users` WHERE `NAME`='" + " 'or '1=1" + "' AND `PASSWORD`='" + " 'or '1=1" + "';
            String sql = "SELECT * FROM `users` WHERE `NAME`=? AND `PASSWORD`=?"; //mybatis

            statement = connection.prepareStatement(sql);  // 预编译
            statement.setString(1, username);
            statement.setString(2, password);


            resultSet = statement.executeQuery();  // 返回查询结果

            while (resultSet.next()) {
                System.out.println(resultSet.getString("NAME"));  //输出id=1的用户的名字
                System.out.println(resultSet.getString("PASSWORD"));  //输出id=1的用户的密码
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

10.7 使用IDE连接数据库

在这里插入图片描述

  1. 在这里插入图片描述
    踩坑1：
    Driver：mysql
    首先记得导入jar包
    在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

  1. 成功连接后选择数据库
    在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

  1. 双击users表
    在这里插入图片描述
  2. 双击密码输入提交
    在这里插入图片描述
  3. 在IDEA编写SQL
    在这里插入图片描述
    在这里插入图片描述
    点击console下方的service可以看到操作数据库时的SQL语句
    在这里插入图片描述

10.8 事务

要么都成功，要么都失败！

ACID原则

原子性：要么全部完成，要么都不完成
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

  • 脏读：一个事务读取了另一个没有提交的事务
  • 不可重复读：在一个事务内，两次重复读取表中数据过程中，表中数据发生改变
  • 虚读(幻读)：在一个事务内，读取到了别的事务插入的数据，导致前后读出来结果不一致

代码实现

  1. 开启事务
// 关闭数据库的自动提交功能， 自动会开启事务
            connection.setAutoCommit(false);
  1. 一组业务执行完毕，提交事务
  2. 可以再catch语句中显式的定义回滚语句，但默认失败也会回滚

10.9 数据库连接池

数据库连接——执行完毕——释放
连接——释放
十分浪费系统资源
池化技术：
准备一些预先的资源，程序过来就连接原先准备好的
没有池化技术前：
n个用户去银行办业务
用户1去银行开门——服务——服务结束——关门——用户2开门——服务——服务结束——关门——…——用户n去银行开门——服务——服务结束——关门
有了池化技术后：
n个用户去银行办业务
x个用户一块去银行——银行开门——x个业务员就绪——x个用户同时办理业务——y个用户服务结束——关门——新的y个用户进去——办理业务——z个用户服务结束——新的z个用户进去…——最后一个用户服务结束——银行关门(x个用户不会同时服务结束，只要有服务结束的就有新的用户进入)

最小连接数：等于常用链接数
最大连接数：业务最高承载上限
等待超时：100ms

编写连接池只需要实现一个接口DataSource

开源数据源实现

DBCP
C3P0
Druid：阿里
使用了这些数据库连接池后，我们在项目开发中就不需要编写连接数据库的代码了

DBCP

需要用到的jar包
commons-dbcp-1.4.jar
commons-pool-1.6.jar

C3P0

需要用到的jar包
c3p0-0.9.5.5.jar
mchange-commons-java-0.2.19.jar

结论
无论是用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变。

GrandNovice
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
想转行数据方向，苦苦找不到方法？看这里，数据方向的完整知识体系送给你！
weixin_41432734的博客
10-30 4万+
数据分析/挖掘-岗位要求 阿里巴巴数据挖掘工程师岗位要求： 1.熟练使用统计分析工具例如SQL, excel, SPSS, SAS, R, python等等； 2.掌握常用的数据分析方法，例如数据预处理，单变量描述性统计，多元统计分析如回归，聚类等； 3.良好的数据敏感度,能从海量数据提炼核心结果，有丰富的数据分析、挖掘、清洗和建模的经验； 4.有成本优化效能提升数据分析经验，尤其是供应链相关的优先。 腾讯数据挖掘工程师的岗位要求： 严谨的逻辑思维能力和扎实的梳理专业基础知识。 较强的动手能力，熟练
zset 怎么get_如何使用RedisTemplate访问Redis数据结构之Zset
weixin_39689700的博客
12-21 429
Redis的ZSet数据结构Redis 有序集合和无序集合一样也是string类型元素的集合,且不允许重复的成员。不同的是每个元素都会关联一个double类型的分数。redis正是通过分数来为集合中的成员进行从小到大的排序。有序集合的成员是唯一的,但分数(score)却可以重复。public interface ZSetOperationsZSetOperations提供了一系列方法对有序集合进行...
StringRedisTemplate操作redis数据
qq_43670845的博客
06-04 454
转自: https://www.cnblogs.com/slowcity/p/9002660.html
cpp map 获取所有 key_「Java」基础27：Map集合
weixin_39628186的博客
11-20 1687
Map，这个单词很多人都认识，不过第一反应应该是“地图”，其实它还有一个意思叫“映射”。地图从某种意义上就是映射的意思。什么叫做映射呢？地图上的一个点，就对应着现实生活中的一个地方，这种一一对应的关系就叫映射。在Java里面，Map就是满足这种一一映射关系的集合，也叫双列集合。前几天学的集合叫Collecion，是单列集合。Map存储元素是一对一对的存的(就像夫妻一样)，所以又叫夫妻集合。Coll...
Redis缓存操作（添加缓存，删除缓存，修改缓存）的代码实现
qq_28945537的博客
03-14 2589
看文章之前你需要了解Redis的数据类型 和数据类型的 相关方法 我这边简单介绍一下： Redis支持五种数据类型：String（字符串），hash（哈希），list（列表），set（集合），zset（有序集合） 相关方法： 1.String（字符串）：String是resid最基本的类型，一个key对应一个value。String类型是二进制安全的。string中可以包含任何数据。 set：添加...
MySql 8.0.37
最新发布
05-30
MySql 8.0.37
mysql8.0升级版本
12-12
MySQL 8.0 升级版本 在本文中，我们将详细介绍 MySQL 8.0 升级版本的步骤和注意事项。MySQL 是一个流行的开源关系数据库管理系统，广泛应用于各种Web 应用程序中。升级 MySQL 版本可以提高数据库性能、修复 bugs 和...
深入浅出MySQL全文
06-29
深入浅出MySQL全文 MySQL 是一种关系型数据库管理系统，广泛应用于各种行业和领域。本资源将从基础篇开始，详细讲解 MySQL 的安装、配置、启动和关闭服务、SQL 基础等知识点。 MySQL 的安装与配置 MySQL 的安装是...
MySQL5.5.62
11-06
MySQL 5.5.62 是 MySQL 数据库管理系统的一个重要版本，主要针对 Windows 操作系统提供了32位和64位的安装包。MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由 Oracle 公司维护。它以其高效、稳定和...
mysql 5.7 perl
08-08
离线安装mysql文件包，包含依赖，以及peal全套安装包
mysql $用法_Mysql用法汇总
weixin_42513269的博客
01-18 1516
mysql用法总结及整理1、group by ...HAVING 获取分组大于某个总数的的前多少位SELECT user_id, count(*) as total FROM `blog_index_tbl` WHERE add_time BETWEEN UNIX_TIMESTAMP('2016-01-01 00:00:00') AND UNIX_TIMESTAMP('2016-12-31 23...
对redis实现hash的增删改查
m0_37808871的博客
06-05 3381
由于服务端需要对部分数据进行频繁的查找，用redis增加效率，我在后台管理系统中写了对该部分数据的增删改查功能。 //列表显示 Map<Object,Object> gameIdMap=stringRedisTemplate.opsForHash().entries(key); //增加hashKey-value //判断该hashKey与value在redis中是否已存在，如果...
基于Redis的String实现文章PV统计
mutf7的博客
08-10 982
Redis String数据类型实战开发 - 文章的PV统计 1、场景分析 使用redis的计数器来完成文章的统计计数 新浪的文章访问 微信公众号的文章访问 都有一个浏览数、评论数、转发数等，数量的统计，可以使用string数据结构中的命令incr来进行处理和累加。 2、具体实现分析 每篇文章都有一个文章ID ：可以用作为的key 值就是每篇文章的浏览数即可 redisTemplate.incrment("文章ID") 3、具体实现 3.1 使用Redis的计数命令 incr key 采用inc
Java mybatis 实现 mysql 存储json格式 ，用 -> "$.XX" 获取
风兮雨露的博客
03-16 1479
1、Mysql 字段存储类型可以为varchar 类型：比如 姓名 name 有json格式为： name : {"xing":"吴","ming":"雷"} 2、mybatis 使用方式用 -> "$.XX"，比如name 的字段要获取xing的数据：name->'$.xing' 这样就能获取到“吴”的信息了 name->'$.xing' = 吴 3、可以在进行wher...
mysql 运算符
weixin_45441862的博客
11-16 5279
运算符是用来连接表达式中各个操作数的符号，其作用是用来指明对操作数所进行的运算。MySQL 数据库支持使用运算符。通过运算符，可以使数据库的功能更加强大。而且，可以更加灵活的使用表中的数据。MySQL运算符包括4类，分别是算术运算符、比较运算符、逻辑运算符和位运算符。 1、运算符简介 当数据库中的表定义完成后，表中的数据代表的意义就已经定下来了。通过使用运算符进行运算，可以得到包含另一层意义的数据。例如，学生表中存在一个birth字段，这个字段是表示学生的出生年份。如果，用户现在希望查找这个学生的年龄。
redis自增，定时任务更新评论数和阅读数
m0_52914401的博客
04-22 1499
利用redis自增完成阅读数和评论数的定时更新
【MySQL】MySQL高级
qq_44530108的博客
03-25 1174
简介 Mysql服务 启动和停止 查看状态：service mysql status 启动服务：service mysql start 停止服务：service mysql stop 重启服务：service mysql restart 启动之后，查看进程：ps -ef|grep mysql 安装位置 修改字符集 常用命令 show databases 列出所有数据库 create database 库名 创建一个数据库 create database 库名 character set utf8 创
mysql、表操作、数据的增删改查
qq_58862767的博客
03-01 704
对MySQL数据库的增删改查进行了总结，罕见的有：load data infile 将文件数据读取到表中，链表查询等，包含了大量的select查询语句，你想要的查询语句都可以在这里找到，此外还有76条查询语句进行参考和学习
MySQL高级语句
sukapulai的博客
06-01 512
显示表格中一个或数个字段的所有数据记录不显示重复的数据记录有条件查询且 或显示己知的值的数据记录显示两个值范围内的数据记录语法:SELECT"字段"TROM"表名"WHERE"字段"BETWEEN值1AND1值2; SELECT * FROM store_info WHERE Date BETWEEN '2022-5-05' AND '2022-5-08';通常通配符都是跟 LIKE 一起使用的匹配一个模式来找出我们要的数据记录按关键字排序数学函数#如sql_mode开启了PIPES_AS_CONCAT,"
完整卸载MySQL指南
"本文将详细介绍如何彻底卸载MySQL，包括停止服务、卸载程序、删除相关文件夹以及清理注册表，以确保完全移除MySQL并为重新安装做好准备。" 彻底卸载MySQL的过程是一个系统性的操作，需要确保所有与MySQL相关的组件...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值