学习视频地址

文章目录

==[学习视频地址](/video/BV1NJ411J79W?p=13)==一、虚拟机内：二、mysql内基本命令：(MySQL关键字不区分大小写)三、数据库xxx语言：四、操作数据库：[可选]，{必选} （[],{}在数据库中使用时不添加）1. 操作数据库1.1 创建数据库：1.2 删除数据库：1.3 使用数据库：1.4 查看数据库： 2. 数据库的列类型：2.1 数值：2.2 字符串：2.3 时间日期：2.4 null 3. 数据库的字段属性：4.创建数据库及其表单：5. 数据表的类型5.1 关于数据库引擎5.2 设置数据库字符集编码： 6.修改删除表：6.1 修改6.2 删除 五.MySQL数据库管理：1. 外键（了解）1.1 方法一：在创建表的时候，增加约束（麻烦，比较复杂）1.2 方法二：创建表成功后，添加外键约束 2. DML语言（全部记住）2.1 添加 ==insert==2.2 修改 ==update==2.3 删除 ==delete== 、==TRUNEATE== 六. ==DQL查询数据（*超重点）==1. DQL2. 指定查询字段2.1 查询2.2 去重 distinct2.3 数据库的列(表达式) 3. where 条件子句3.1 逻辑运算符3.2 模糊查询： 比较运算符 4. 联表查询4.1 联表查询join4.2 自连接 5.分页和排序：5.1 排序(order by)：***升序ASC ，降序DESC***5.2 分页（limit） 6. 子查询：7.分组和过滤： 七.MySQL函数1. 常用函数：1.1 数学运算1.2 字符串函数1.3 时间和日期函数1.4 系统： 2. 聚合函数(常用)：2.1 统计函数2.2 求和函数 3. 数据库级别的MD5加密（扩展） 八. 事务1. 事务原则: ACID原则 原子性，一致性，隔离性，持久性 (脏读，幻读...）2. 隔离所导致的一些问题3.执行事务4.模拟场景： 九. 索引1. 索引的分类2. 测试索引：3. 索引原则 十. 权限管理和备份1. 用户管理1.1 MySQL工具界面话操作即可1.2 sql命令2.MySQL备份 十一.规范数据库设计1. 为什么需要设计2.三大范式2.1 第一范式(1NF)2.2第二范式(2NF)2.3第三范式(3NF) 十二、JDBC(重点)1.2. JDBC3.第一个JDBC程序:3.1 创建测试数据库

一、虚拟机内：

启动mysql: systemctl start mysqld // service mysqld start重启mysql: systemctl restart mysqld //service mysqld restart停止mysql: systemctl stop mysqld //service mysqld stop查看mysql状态： systemctl status mysqld // service mysqld status登录mysql: mysql -u用户 -p密码查看mysql系统上设置: cat /etc/f

二、mysql内基本命令：(MySQL关键字不区分大小写)

连接数据库：mysql -u用户名 -p密码修改密码： alter user 'toor'@'用户IP' identified by '新密码';查看密码限制： show variables like 'validate_password&';修改密码限制： set gobal validate_password.限制名 = 新限制； 进入切换相关表库： use 表、库名；查看mysql库：show databases;查看mysql表：show tables;显示数据库中指定表信息： describe 表名；创建一个数据库：create database 数据库名；退出连接：exit 、 quit；单行注释： --多行注释： /*。。。。*/；

三、数据库xxx语言：

DDL --定义DML --操作DQL --查询DCL --控制

四、操作数据库：[可选]，{必选} （[],{}在数据库中使用时不添加）

1. 操作数据库

1.1 创建数据库：

create database [if not exists] 数据库名

1.2 删除数据库：

drop database [if exists] 数据库名

1.3 使用数据库：

--注：如果表名或者字段名是一个特殊字符，就需要带 `` 符号use 数据库名 eg: use `user`

1.4 查看数据库：

查看mysql库：show databases;查看mysql表：show tables;显示数据库中指定表信息： describe 表名

2. 数据库的列类型：

2.1 数值：

tinyint 十分小的数据 1个字节smallint 较小的数据2个字节mediumint 中等大小的数据3个字节int **标准的整数**4个字节 -- 常用的intbigint较大的数据8个字节float 浮点数 4个字节double浮点数 8个字节decimal 字符串形式的浮点数，常用用金融计算。

2.2 字符串：

char 字符串固定大小0-255varchar 可变字符串0~65535 -- 常用的变量 stringtinytext 微型文本 2^8-1text 文本串 2^16-1保存大文本

2.3 时间日期：

date YYYY-MM-DD 日期格式time HH:mm:ss 时间格式datetime YYYY-MM-DD HH:mm:ss最常用的时间格式year 年份表示

2.4 null

没有值未知注：不要使用null进行运算，结果为null

3. 数据库的字段属性：

Unsigned:-- *无符号的整数-- 声明了该列不能声明为负数zerofill:-- 0填充的-- 不足的位数，使用0来填充， int(3) , 5 --- 005自增：-- 通常理解为自增，自动在上一条记录的基础上 + 1（默认）-- 通常用来设计唯一的主键~index，必须是整数类型-- 可以自定义主键自增的起始值和步长非空 NULL not null：-- 假设设置为 not null ,如果不给他赋值，就会报错-- null,如果不填写值，默认就是null默认：-- 设置默认的值-- sex,默认的值为男，如果不指定该列的值，则会有默认的值拓展：/*每个表都必须存在以下五个字段，在java项目中，表示一个记录存在意义id主键`version` 乐观锁is_delete 伪删除gmt_create 创建时间gmt_update 修改时间*/

4.创建数据库及其表单：

-- 注意点，使用英文（），表名和字段尽量使用 `` 括起来-- AUTO_INCREMENT 自增需要与 key 主键配合-- 语句之间用 ； 隔开-- 字符串使用 单引号 '' 括起来-- 所有语句后面加英文的 ， 最后一个不用加-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键；CREATE DATABASE `student`;USE student;CREATE TABLE `student`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`birthday` datetime DEFAULT NULL COMMENT '出生日期',`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8

格式*：

CREATE TABLE [IF NOT EXISTS] '表名'（`字段名` 列类型 [属性] [索引] [注释]，………………………………………………………………………………………………，`字段名` 列类型 [属性] [索引] [注释]）[表类型][字符集设置][注释]

补充常用命令：

SHOW CREATE DATABASE 数据库名 -- 查看创建数据库的语句SHOW CREATE TABLE 数据表名 -- 查看数据表的定以语句DESC 表名 -- 显示表的结构

5. 数据表的类型

5.1 关于数据库引擎

-- 关于数据库引擎INNODB 默认使用的；MYISAM 早些年使用的；

常规使用操作：

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

在物理空间存在的位置：

所有数据库文件都在data目录下，本质还是文件的存储！MySQL引擎在物理文件上的区别· INNODB 在数据库表中只有一个*.frm文件，以及上层目录下的ibdata1文件· MYISAM 对应文件：*.frm 表结构的定以文件*.MYD 数据文件（data）*.MYL 索引文件（index）

5.2 设置数据库字符集编码：

CHARSET=utf8

不设置的话，会是mysql默认字符集编码~（不支持中文！）

MySQL的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

6.修改删除表：

6.1 修改

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名ALTER TABLE teacher RENAME AS teacher1-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性ALTER TABLE teacher1 ADD age INT(11)-- 修改表的字段（重命名，修改约束！）-- ALTER TABLE 表名 MODIFY 字段名 列属性[]ALTER TABLE teacher1 MODIFY age VARCHER(11) -- 修改约束-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]ALTER TABLE teacher1 CHANGE age age1 int(1) -- 字段重命名-- 删除表的字段：ALTER TABLE teacher DROP age1

6.2 删除

-- 删除表（如果表存在再删除）DROP TABLE IF EXISTS teacher1

所有的创建和删除尽量加上判断++++，以免报错~

注意点：

· 用 `` 包裹字段名· 注释 -- /**/· sql关键字大小写不敏感，建议小写· 所有的符号全部用英文

五.MySQL数据库管理：

1. 外键（了解）

1.1 方法一：在创建表的时候，增加约束（麻烦，比较复杂）

USE student;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-- 给这个外键添加约束 （执行引用）reference 引用CREATE TABLE IF NOT EXISTS `student`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`birthday` datetime DEFAULT NULL COMMENT '出生日期',`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',`address` VARCHAR(100) 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

删除有外键的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

1.2 方法二：创建表成功后，添加外键约束

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-- 给这个外键添加约束 （执行引用）reference 引用CREATE TABLE IF NOT EXISTS `student`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`birthday` datetime DEFAULT NULL COMMENT '出生日期',`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',`gradeid` INT(10) NOT NULL COMMENT '学生的年级',PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8;-- 创建表的时候没有外键关系ALTER TABLE `student`ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 哪个表(哪个字段);

以上的操作都是物理外键，数据库级别的外键，不建议是使用~（避免数据库过多造成困扰，了解即可！）

最佳实践

· 数据库就是单纯的表，只用来存储数据，只有行（数据）和列（字段）· 我们想使用多张表的数据，想使用外键（一般用程序去实现）

2. DML语言（全部记住）

数据库的意义：数据存储，数据管理DML语言：数据操作语言· insert· update· delete

2.1 添加insert

--插入语句(添加) -- insert into 表名([字段名1,字段2,字段3]) values ('值1'),('值2'),('值3’，....)INSERT INTO `grade` ( `gradename` ) 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` ，s`ex` )VALUES ( '李四'，'aaaaaa','男'),('王五','aaaaaa','男')

语法:

insert into 表名([字段名1,字段2,字段3])values(‘值1’),(‘值2’),(‘值3’，…)

注意事项:

1.字段和字段之间使用英文逗号隔开

2.字段是可以省略的，但是后面的值必须要要一一对应, 不能少

3.可以同时插入多条数据，VALUES 后面的值，需要使用，隔开即可 VALUES(),(),…

2.2 修改update

update 修改谁 (条件) set原来的值=新值--修改学员名字,带了简介UPDATE `student` sET `name` ='狂神' WHERE id = 1;--不指定条件的情况下，会改动所有表!UPDATE `student` SET `name` ='长江7号'--修改多个属性，逗号隔开UPDATE `student` SET `name` = '狂神'，`email` = '123456@' WHERE id = 1;--语法:-- UPDATE 表名 set colnum_ name = value, [colnum_name = value,....] where [条件]

条件: **where子句运算符**id 等于某个值，大于某个值,在某个区间内修改…

操作符会返回布尔值

语法:

UPDATE 表名 set colnum_name = value, [colnum_name = value,...] where [条件]

注意:

● colnum_name 是数据库的列，尽量带 ``● 条件,筛选的条件,如果没有指定，则会修改所有的列● value,是一个具体的值，也可以是一个变量● 多个设置的属性之间，使用英文逗号隔开

UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` ='长江7号' AND sex='女'

2.3 删除delete、TRUNEATE

delete

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

-- 删除数据 （避免这样写，会全部删除）DELETE FROM `student`-- 删除指定数据DELETE FROM `student` where id = 1;

TRUNEATE

TRUNCATE命令作用：完全清空一个数据库表，表的结构和索引约束不会变!-- 清空 student表TRUNCATE `student`

delete的TRUNCATE区别

●相同点:都能删除数据，都不会删除表结构

●不同:

· TRUNCATE 重新设置自增列计数器会归零

· TRUNCATE 不会影响事务

--测试delete和TRUNCATE 区别CREATE TABLE `test' (`id` INT(4) NOT NULL AUTO_ INCREMENT ,`co11` VARCHAR(20) NOT NULL,PRIMARY KEY ( id' ) )ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `test` (`coll`) VALUES('1'),('2'),('3')DELETE FROM `test` -- 不会影响自增TRUNCATE TABLE `test` -- 自增会归零

了解即可: DELETE删除的问题,重启数据库，现象

● InnoDB 自增列会重 1开始(存在内存当中的， 断电即失)

● MyISAM 继续从 上一个自增量开始(存在文件中的， 不会丢失)

六.DQL查询数据（*超重点）

1. DQL

(Data Query LANGUAGE :数据查询语言)

● 所有的查询操作都用它 Select

● 简单的查询，复杂的查询它都能做~

● 数据库中最核心的语言，最重要的语句

● 使用频率最高的语句

SELECT完整的语法：

注意:[]括号代表可选的，{ )括号代表必选的

SELECT [ALLI 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小结

顺序很重要:select去重 要查询的字段 from 表(注意: 表和字段可以取别名)xxx join 要连接的表 on 等值判断where (具体的值，子查询语句)Group By (通过哪个字段来分组)Having (过滤分组后的信息， 条件和where是一样的， 位置不同)Order By… (通过哪个字段排序) 升序/降序Limit startIndex, pagesize

2. 指定查询字段

2.1 查询

-- 查询全部的学生 SELECT 字段 FROM 表SELECT * FROM student--查询指定字段SELECT `StudentNo` , `StudentName` FROM student--别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名SELECT `StudentNo` AS 学号，`studentName` AS 学生姓名 FROM student AS s--函数Concat (a, b)SELECT CONCAT('姓名: ' ，StudentName) AS 新名字 FROM student

语法: SELECT 字段，… FROM 表

有的时候，列名字不是那么的见名知意。我们就可以起别名 AS字段名 as 别名表名 as 别名

2.2 去重 distinct

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

--查询一下有哪些同学参加了考试，成绩SELECT * FROM result--查询全部的考试成绩SELECT `StudentNo` FROM result --查询有哪些同学参加了考试SELECT DISTINCT `studentNo` FROM result --发现重复数据，去重

2.3 数据库的列(表达式)

SELECT VERSION() --查询系统版本 (函数)SELECT 100*3-1 AS计算结果 --用来计算 (表达式)SELECT @@auto_ _increment_ increment -- 查询自增的步长 (变量)--学员考试成绩+ 1分查看SELECT `studentNo` ，`StudentResult` +1 AS '提分后' FROM result

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

select表达式 from 表

3. where 条件子句

作用: 检索数据中符合条件的值

3.1 逻辑运算符

尽量使用英文字母

例子：-- ===================where======================SELECT studentNo, `StudentResult` FROM result-- 查询考试成绩在95~100 分之间SELECT `studentNo`,`StudentResult` FROM resultWHERE StudentResult>=95 AND StudentResult<=100-- and &&SELECT `studentNo`, `StudentResult` FROM resultWHERE StudentResu1t>=95 && StudentResult<=100-- 模糊查询(区间)SELECT studentNo, `StudentResult` FROM resultWHERE StudentResult BETWEEN 95 AND 100-- 除了1000号学生之外的同学的成绩SELECT studentNo, `StudentResult’ FROM resultWHERE studentNo!=1000;-- != notSELECT studentNo, `StudentResult` FROM resultWHERE NOT studentNo = 1000

3.2 模糊查询： 比较运算符

-- 查询姓刘的同学-- 1ike结合 %(代表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 nu11====-- 查询地址为空的学生 nu11 ''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.1 联表查询join

-- =============联表查询join ==============-- 查询参加了考试的同学(学号，姓名，科目编号，分数)SELECT * FROM studentSELECT * FROM result/*思路1.分析需求，分析查询的字段来自哪些表， (连接查询)2.确定便用哪种连接查询? 7种确定交叉点(这两个表中哪个数据是相同的)判断的条件:学生表的中 studentNo = 成绩表 studentNo*/SELECT s.studentNo, studentName，SubjectNo,StudentResultFROM student AS sINNER JOIN result AS rON s.studentNo = r.studentNo-- Right JoinSELECT s.studentNo,studentName，subjectNo , StudentResultFROM student sRIGHT JOIN result r ON S. studentNo = r.studentNo-- Left JoinSELECT s.studentNO, studentName，subjectNo，StudentResultFROM student sLEFT JOIN result rON s.studentNo = r.studentNo--查询缺考的同学SELECT s.studentNo, studentName，subjectNo，StudentResultFROM student SLEFT JOIN result rON s.studentNO = r.studentNoWHERE studentResult IS NULL--思考题(查询了参加考试的同学信息: 学号，学生姓名，科目名，分数)/*思路1.分析需求，分析查询的字段来自哪些表，student、 result、subject (连接查询)2.确定使用哪种连接查询? 7种确定交叉点(这两个表中哪个数据是相同的)判断的条件:学生表的中 studentNo =成绩表 studentNo*/SELECT s.studentNo, studentName , SubjectName, StudentResu1tFROM student sRIGHT JOIN result rON r.studentNo = s.studentNoINNER JOIN `subject` subON r.subjectNo = sub.subjectNo--我要查询哪些数据select ...--从那几个表中查FROM 表 xxx Join 连接的表 on 交叉条件--假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加-- From a 1eft join b-- From a right join b

4.2 自连接

自己的表何自己的表连接，核心：一张表拆分为两张一样的表即可

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=utf8INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)VALUES('2', '1', '信息技术'),('3', '1', '软件开发'),('4','3', '数据库'),('5', '1', '美术设计'),('6', '3', 'web开发'),('7', '5', 'ps技术'),('8', '2', '办公信息') ;

拆分后：

父类：

子类：

操作：查询父类对应的子类关系：

--查询父子信息:SELECT a.`categoryName` AS '父栏目' ,b.`categoryName` AS '子栏目'FROM `category` AS a, `category` AS bWHERE a.`categoryid` = b.`pid`

5.分页和排序：

5.1 排序(order by)：升序ASC ，降序DESC

排序: 升序ASC ，降序DESC-- ORDER BY 通过那个字段排序，怎么排-- 查询的结果根据成绩降序排序SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`FROM student SINNER JOIN `result` rON s.studentNo = r.StudentNoINNER JOIN `subject` subON r.`subjectNo` = sub.`subjectNo`WHERE subjectName = '数据库结构-1'ORDER BY StudentResult ASC

5.2 分页（limit）

-- 100万-- 为什么要分页?-- 缓解数据库压力，给人的体验更好，瀑布流-- 分页，每页只显示五条数据-- 语法:limit当前页，页面的大小.-- LIMIT 0,5 1~5-- LIMIT 1,5 2~6-- LIMIT 6,5SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`FROM student SINNER JOIN `result` rON s.studentNo = r.StudentNoINNER JOIN `subject` subON r.`subjectNo` = sub.`subjectNo`WHERE subjectName = '数据库结构-1'ORDER BY StudentResult ASCLIMIT 5,5-- 第一页 limit 0,5(1-1) *5-- 第二页 limit 5,5(2-1) *5-- 第三页 limit 10,5 (3-1) *5-- 第N页 limit 0,5(n-1) *pagesize, pagesize-- 【pagesize: 页面大小】-- 【(n-1)* pagesize :起始值】-- 【n:当前页】-- 【数据总数/页面大小=总页数】

语法:limit( 查询起始下标，pagesize)

--思考:--查询JAVA第一学年课程成绩排名前十的学生，并且分数要大于80的学生信息(学号，姓名，课程名称，分数)SELECT s.`StudentNo`,`StudentName` , `SubjectName`, `StudentResult`FROM `student` sINNER JOIN `result` rON s.StudentNo = r.StudentNoINNER JOIN `subject` subON sub.`SubjectNo` = r.`SubjectNo`WHERE SubjectName = 'JAVA第一 学年' AND StudentResult>=80ORDER BY StudentResult DESCLIMIT 0,10

6. 子查询：

where (这个值是计算出来的)：

本质： 在where语句中嵌套一个子查询语句

-- ============= where =============-- 1、查询数据库结构-1的所有考试结果(学号，科目编号，成绩)，降序排列-- 方式一:使用连接：SELECT `StudentNo`，r.`SubjectNo` , `StudentResult`FROM `result`INNER JOIN `subject` subON r.SubjectNo = sub.SubjectNoWHERE subjectName = '数据库结构-1'ORDER BY StudentResult DESC方式二:使用子查询(由里及外)-- 查询所有数据库结构-1的学生学号SELECT `StudentNo` , `SubjectNo` , `StudentResult`FROM `result`WHERE SubjectNo = (SELECT SubjectNo FROM `subject`WHERE SubjectName = ' 数据库结构-1')ORDER BY StudentResult DESC-- 查询课程为高等数学-2且分数不小于80的同学的学号和姓名SELECT s.StudentNo, StudentNameFROM student s INNER JOIN result rON s.StudentNo = r.StudentNoINNER JOIN `subject` subON r.`Subj ectNo` = sub.`SubjectNo`WHERE `SubjectName` = '高等数学-21' AND StudentResult>=80--分数不小于80分的学生的学号和姓名SELECT DISTINCT s.`StudentNo`，`StudentName`FROM student sINNER JOIN result rON r.StudentNo = s.StudentNoWHERE `StudentResult` >=80-- 在这个基础上增加一个科目， 高等数学-2-- 查询高等数学-2的编号SELECT DISTINCT s.`studentNo`，`StudentName`FROM student sINNER JOIN result rON r.StudentNo = s.StudentNoWHERE `StudentResult` >=80 AND `SubjectNo`= (SELECT subjectNo FROM `subject'WHERE `SubjectName` = '高等数学-2')-- 在改造(由里及外)SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (SELECT StudentNo FROM resu1t WHERE StudentResult>=80 AND SubjectNo = ( SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2'))

7.分组和过滤：

-- 查询不同课程的平均分，最高分，最低分，平均分大于80-- 核心: (根据不同的课程分组)SELECT subjectName, AVG(StudentResu1t) AS 平均分，MAX(StudentResu1t) AS 最高分,MIN(studentResu1t) AS 最低分FROM result rINNER J0IN `subject` subON r.`subjectNo` = sub.`SubjectNo`GROUP BY r.subjectNo -- 通过什么字段来分组HAVING 平均分>80

七.MySQL函数

官网：MySQL

1. 常用函数：

1.1 数学运算

-- 数学运算SELECT ABS(-8) -- 绝对值，SELECT CEILING(9.4) --向上取整SELECT FLOOR(9.4) -- 向下取整SELECT RAND() -- 返回一个0~1之间的随机数SELECT SIGN(10) --判断一个数的符号 0-0 负数返回-1，正数返回1

1.2 字符串函数

-- 字符串函数SELECT CHAR LENGTH('即使再小的帆也能远航') -- 字符串长度SELECT CONCAT('我， '爱", '你们) -- 拼接字符串，SELECT INSERT (我爱编程helloworld',1,2,超级热爱') -- 查询，从某个位置开始替换某个长度SELECT LOWER( 'KuangShen') -- 小写字母SELECT UPPER('KuangShen') -- 大写字母SELECT INSTR ('kuangshen', 'h') -- 返回第- -次出现的子串的索引SELECT REPLACE ('狂神说坚持就能成功，'坚持',努力) -- 替换出现的指定字符串SELECT SUBSTR(' 狂神说坚持就能成功,4,6) -- 返回指定的子字符串(源字符串,截取的位置，截取的长度)SELECT REVERSE('清晨我上马) --反转-- 例子：查询姓周的同学，名字变为 邹SELECT REPLACE (studentname,'周','邹') FROM studentWHERE studentname LIKE '周%'

1.3 时间和日期函数

--时间和日期函数(记住)SELECT CURRENT_DATE() -- 获取当前日期SELECT CURDATE() -- 获取当前日期.SELECT NOW() -- 获取当前的时间SELECT LOCALTIME() -- 本地时间SELECT SYSDATE() -- 系统时间----------------------------------------------------SELECT YEAR (NOW () )SELECT MONTH (NOW() )SELECT DAY (NOW() )SELECT HOUR (NOW() )SELECT MINUTE (NOW() ) SELECT SECOND (NOW () )

1.4 系统：

SELECT SYSTEM_USER( )SELECT USER( )-- 查看系统用户SELECT VERSION() -- 查看系统版本

2. 聚合函数(常用)：

2.1 统计函数

都能够统计表中的数据SELECT COUNT (`字段`) FROM 数据库名; -- Count(字段)， 会忽略所有的 null 值SELECT COUNT(*) FROM student; -- Count (*)，不会忽略null 值，本质 计算行数SELECT COUNT (1) FROM result; -- Count (1) ，不会忽略所有的null值，本质：计算行数

2.2 求和函数

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

3. 数据库级别的MD5加密（扩展）

什么是MD5?

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

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

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

-- ===============测试MD5加密===========CREATE TABLE `testmd5` (`id` INT(4) NOT NULL,`name` VARCHAR(20) NOT NULL，`pwd` VARCHAR(50) NOT NULL, PRIMARY KEY( `id` ))ENGINE=INNODB DEFAULT CHARSET=utf8-- 明文密码INSERT INTO testmd5 VALUES(1, 'zhangsan' ,'123456'),(2, 'lisi', '123456 '),(3, 'wangwu', '123456')--加密UPDATE testmd5 SET pwd=MD5 (pwd) WHERE id = 1UPDATE testmd5 SET pwd=MD5(pwd) --加密全部的密码--插入的时候加密INSERT INTO testmd5 VALUES(4, 'xiaoming' ,MD5('123456 '))--如何校验:将用户传递进来的密码，进行md5加密，然后比对加密后的值SELECT * FROM testmd5 WHERE `name` ='xiaoming' AND pwd=MD5('123456')

八. 事务

要么都成功，要么都失败-----------------------1、SQL执行 A给B转账 A 1000 -->200 B 200 2、SQL执行 B收到A的钱 A 800 --> B 400----------------------将一组SQL放在一个批次中去执行~

1. 事务原则: ACID原则 原子性，一致性，隔离性，持久性 (脏读，幻读…）

参考博客连接: /dengjili/article/details/82468576

原子性(Atomicity)要么都成功，要么都失败一致性 (Consistency)事务前后的数据完整性要保证一致，1000持久性(Durability)--（事务提交）事务一旦提交则不可逆，被持久化到数据库中!隔离性(Isolation)事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务, 不能被其他事务的操作数据所干扰，事务之间要相互隔离。

2. 隔离所导致的一些问题

脏读:指一个事务读取了另外一个事务未提交的数据。不可重复读:在一个事务内读取表中的某一行数据，多次读取结果不同。(这个不一 定是错误，只是某些场合不对)虚读(幻读)是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

3.执行事务

--====================事务=========================-- mysql 是默认开启事务自动提交的SET autocommit = 0 /*关闭*/SET autocommit = 1 /*开启(默认的) */-- 手动处理事务SET autocommit = 0 --关闭自动提交-- 事务开启START TRANSACTION -- 标记一个事务的开始，从这个之后的sql 都在同一个事务内INSERT xxINSERT xx--提交: 持久化(成功! )COMMIT--回滚: 回到的原来的样子(失败! )ROLLBACKI--事务结束SET autocommit = 1 -- 开启自动提交--了解SAVEPOINT 保存点名 -- 设置一个事务的保存点ROLLBACK To SAVEPOINT 保存点名 -- 回滚到保存点RELEASE SAVEPOINT 保存点名 -- 撤销保存点

4.模拟场景：

-- 转账CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci USE shopCREATE 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=utf8INSERT 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减500UPDATE account SET money=money+500 WHERE `name` = 'B' -- A加500COMMIT; -- 提交事务，就被持久化了!ROLLBACK; -- 回滚SET autocommit = 1; -- 恢复默认值

九. 索引

索引参考博文

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

提取句子主干，就可以得到索引的本质:索引是数据结构。

1. 索引的分类

主键索引(PRIMARY KEY )唯一的标识，主键不可重复，只能有一个列作为主键唯一索引(UNIQUE KEY)避免重复的列出现，唯一索引可以重复， 多个列都可以标识位唯一索引常规索引(KEY/INDEX)默认的，index。 key 关键字来设置全文索引(FullText)在特定的数据库引擎下才有，MyISAM快速定位数据

基础语法：

-- 索引的使用-- 1、在创建表的时候给字段增加索引-- 2、创建完毕后，增加索引-- 显示所有的索引信息SHOW INDEX FROM student-- 增加一个全文索引 (索引名) 列名ALTER TABLE school.student ADD FULLTEXT INDEX `studentName` ( `studentName` ) ;-- EXPLAIN 分析sql执行的状况EXPLAIN SELECT * FROM student; -- 非全文索引EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

2. 测试索引：

-- 创建数据库表CREATE TABLE `app_user` (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',`password` VARCHAR(100) NOT NULL COMMENT '密码',`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'-- 插入一百万条数据：delimiter $$set global log_bin_trust_function_creators=TRUE;-- 写函数之前必须要写，标志CREATE FUNCTION mock_data ()RETURNS INT DETERMINISTICBEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 1;WHILE i<num DOINSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'123456789@',CONCAT('18',FLOOR(RAND()*999999999)),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` = '用户123654' ; -- 0.496sEXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999' ;-- 创建索引：-- id_表名_字段名 -- CREATE INDEX 索引名 on 表（字段）CREATE INDEX id_app_user_name ON app_user(`name`);SELECT * FROM app_user WHERE `name` = '用户99999' ; -- 0.031s

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显~

3. 索引原则

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

索引的数据结构

Hash类型的索引

Btree : InnoDB 的默认数据结构~

十. 权限管理和备份

1. 用户管理

1.1 MySQL工具界面话操作即可

1.2 sql命令

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'CREATE USER kuangshen IDENTIFIED BY '123456'-- 修改密码(修改当前用户密码)SET PASSWORD = PASSWORD('123456')-- 修改密码(修改指定用户密码)SET PASSWORD FOR kuangshen = PASSWORD('123456')-- 重命名 RENAME USER 原来名字 TO 新的名字RENAME USER kuangshen TO kuangshen2-- 用户授权 ALL PRIVILEGES 全部的权限，库.表-- ALL PRIVILEGES 除了给别人授权，其他都能够干GRANT ALL PRIVILEGES ON *.* TO kuangshen2-- 查询权限SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限SHOW GRANTS FOR root@1ocalhost-- ROOT用户权限: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION-- 撤销权限 REVOKE 哪些权限. 在哪个库撇销. 给谁撤销REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2--删除用户DROP USER kuangshen

2.MySQL备份

为什么要备份:

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

MySQL数据库备份的方式

直接拷贝物理文件在Sqlyog这种可视化工具中手动导出使用命令行导出mysqldump命令行使用

# mysqldump -h主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql# mysqldump -h主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名mysqldump -hlocalhost -uroot -p123456 schoo1 student > D:/b.sql# mysqldump -h主机-u 用户名-p密码数据库> 物理磁盘位置/文件名mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql# 导入# 登录的情况下，切换到指定的数据库# source 备份文件source d:/a.sql-- 未登录的时候：mysql -u用户名-p密码 库名< 备份文件

假设你要备份数据库，防止数据丢失。

把数据库给朋友! sql文件给别人即可!

十一.规范数据库设计

1. 为什么需要设计

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

糟糕的数据库设计：

数据冗余,浪费空间数据库插入和删除都会麻烦、异常[屏蔽使用物理外键]程序的性能差

良好的数据库设计:

节省内存空间保证数据库的完整性方便我们开发系统

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

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

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

● 收集信息，分析需求

用户表(用户登录注销,用户的个人信息，写博客,创建分类)分类表(文章分类，谁创建的)文章表(文章的信息)评论表友链表(友链信息)自定义表(系统信息,某个关键的字，或者一些主字段) key: value说说表(发表心情… id… conte…create_time)

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

● 标识实体之间的关系

写博客: user–> blog创建分类: user -> category关注: user ->user友链: links评论: user-user-blog

2.三大范式

为什么需要数据规范化?●信息重复●更新异常●插入异常无法正常显示信息●删除异常丢失有效的信息

2.1 第一范式(1NF)

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

2.2第二范式(2NF)

前提:满足第一范式

每张表只描述一件事情

2.3第三范式(3NF)

前提:满足第一范式和第二范式

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

(规范数据库的设计)规范性和性能的问题关联查询的表不得超过三张表●考虑商业化的需求和目标， (成本,用户体验! )数据库的性能更加重要●在规范性能的问题的时候，需要适当的考虑一下规范性!●故意给某些表增加一些冗余的字段。 (从多表查询中变为单表查询)●故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

十二、JDBC(重点)

1.

驱动:卡,显卡,数据库

2. JDBC

SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个java操作数据库的)规范，俗称JDBC

这些规范的实现由具体的厂商去做~

对于开发人员来说，我们只需要掌握JDBC接口的操作即可！

java.sql

javax.sql

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

3.第一个JDBC程序:

3.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,'zhansan','123456','zs@','1980-12-04'),(2,'lisi','123456','lisi@','1981-12-04'),(3,'wangwu','123456','wangwu@','1979-12-04')

操作过程：/video/BV1NJ411J79W?p=38

步骤总结:

1、加载驱动

2、连接数据库DriverManager

3、获得执行sq的对象Statement

4、获得返回的结果集

5、释放连接.