MySQL

1.初识数据库

JavaEE:企业级开发 web

前端

后台（连接点：链接数据库JDBC，链接前端）

数据库

数据库是所有软件体系中最核心的存在

1.1 什么是数据库

数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统上！SQL，可以存储大量的数据。

作用：存储数据

1.2 数据库分类

关系型数据库：

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

非关系型数据库：

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

DBMS（数据库管理系统）

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

1.3MySQL简介

MySQL是一个关系型数据库管理系统

前世：瑞典MySQL AB公司

今生：属于Oracle旗下产品

MySQL是最好的关系数据库管理系统应用软件之一

开源的数据库软件

体积小、速度快、总体拥有成本低，

中小型、大型网站都有使用

安装建议：

1. 尽量不要使用exe进行安装
2. 尽可能使用压缩包安装

1.4安装MySQL

1. 下载MySQL压缩包，并解压。

   https://dev.mysql.com/downloads/mysql/

2. 配置环境变量，将mysql文件夹下的bin目录添加到path中

   

3. 新建配置文件my.ini

   [mysqld]
   # 目录要换成自己mysql文件目录
   basedir=F:\mysql\mysql-5.7.32
   datadir=F:\mysql\mysql-5.7.32\dataport=3306
   skip-grant-tables
   

   要注意的是目前mysql文件夹中没有打他文件夹，但是我们并不需要创建data文件夹，在下面的步骤中会自动生成data文件夹。

   配置文件的最后一行表示的是跳过密码验证。

4. 管理员模式下启动CMD，进入mysql文件夹下的bin目录，运行

   mysqld -install
   

   运行结束后会出现

   Service successfully installed.
   

   

5. 再输入以下代码来初始化数据文件

   mysqld --initialize-insecure --user=mysql
   

   这时候mysql文件夹下就会自动生成data目录，命令行没有报错就表示初始化完成。

   

6. 然后输入以下命令启动mysql，

   net start mysql
   

   

7. 使用以下命令进入mysql

   mysql -u root -p
   

   会出现让你输入密码，由于密码没有设置，所以直接enter即可

   

8. 使用命令来修改密码，注意mysql中每一条命令都是分号;结尾的。

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

   

   然后使用命令flush privileges;来刷新权限

9. 修改my.ini文件

   将my.ini文件的最后一行注释掉，最后一行前面加# 即可

10. 重启mysql。输入exit退出MySQL，然后关掉服务net stop mysql,再重新启动mysql服务，输入mysql -u root -p,然后输入密码进入mysql即可。

1.5安装SQLyog

https://github.com/webyog/sqlyog-community/wiki/Downloads

上面的GitHub中有许多版本，对应下载即可。

下载完成后安装。

打开后填写相应的连接信息即可。localhost表示连接自己的主机，密码是之前设置的密码。3306表示的是数据库的端口。

然后创建数据库，右键创建数据库

再sqlyog中每用鼠标执行一次操作，实际上就是对应了一个sql语句，可以在软件中的历史记录查看

在新建的数据库里新建表，右击新建,其中基字符集和数据排序规则要与图片中的一致，不然可能会出现乱码的情况。

右击表，点击打开表，然后可以在表里添加数据。

点击右上的刷新按钮即可保存数据。

1.6连接数据库

命令行连接数据库

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

update;    -- 修改命令
flush privileges;  -- 刷新权限


 show databases;  -- 查看所有数据库
 
 use school; -- 使用school数据库
 -- 会输出Database changed，表明数据库已经切换
 
 show tables; -- 查看该数据库中所有的表
 describe student; -- 查看表的结构，而不是表中的所有内容
 
 create databases westos; -- 创建数据库
 
 exit; -- 退出数据库
 
 -- 单行注释
 
/*
多行注释
*/

数据库定义语言 DDL

数据库操作语言 DML

数据库查询语言 DQL

数据库控制语言 SCL

2.操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql不区分字母大小写

2.1操作数据库

create database if not exists westos;  -- 创建数据库

drop database if exists westos;  -- 删除数据库

-- `` 符号，当你的表明或者字段名是一个特殊字符时，就需要使用``，以免该特殊字符编程高亮
use `school`;   -- 使用数据库

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

可以对比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 ** 保存大文本

时间日期

• data YYYY-MM-DD,日期
• time HH:mm:ss 时间格式
• datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳，1970.1.1到现在的毫秒数
• year 年份表示

null

• 没有值，未知
• 尽量不要使用null进行运算，结果为null

2.3数据库的字段属性（重点）

Unsigned :

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

zerofill

• 0填充的
• 不足的位数，使用0来填充

自增

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

非空

• 假设设置为not nul，如果不赋值，就会报错
• null，如果不填写值，默认就是null

默认

• 设置默认的值
• 如果不赋值，就会自动赋值为你设置的默认值。

拓展：

每一个表都必须存在以下五个字段

1. id 主键
2. version 乐观锁
3. is_delete 伪删除
4. gmt_create 创建时间
5. gmt_update 修改时间

2.4创建数据库表

-- 创建一个school数据库
-- 创建学生表
-- 学号、登陆密码、姓名、性别、出生日期、家庭住址、email

-- 注意点：全程符号使用英文，表的名称和字段尽量使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用单引号括起来
-- 所有的语句后面加，最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
Create Table if not exists `student`(
`id` Int(4) not null Auto_Increment comment ‘学号‘,
`name` varchar(20) not null Default ‘匿名‘ comment ‘姓名‘,
`pwd` varchar(20) not null default ‘123456‘ comment ‘密码‘,
`sex` varchar(5) not null default ‘男‘ comment ‘性别‘,
`birthday` date default null comment ‘出生日期‘,
`address` varchar(100) default null comment ‘家庭地址‘,
primary key(`id`)
)engine=INNOdb default charset=utf8

格式

create table `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
)[表类型] [字符集类型] [注释]

常用命令：

show create database school  -- 查看创建数据库的语句
show create table student    -- 查看student数据表的定义语句

DESC student   -- 显示表的结构

2.5数据表的类型

-- 数据库的引擎
/*
INNODB 默认使用的
MYISAM 早些年使用的
*/

常规使用：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库。

数据库的本质还是文件的存储。

MySQL引擎在物理文件上的区别

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

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

charset=utf8

如果没有设置的话，会自动设置mysql默认的字符集编码（不支持中文）

MySQL的默认编码是Latin1

另外可以在my.ini中配置默认的编码(不建议使用)

character-set-server=utf8

2.6修改删除表

修改表

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS teacher

-- 增加表的字段  ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher ADD email VARCHAR(20)

-- 修改表的字段，包括两个：重命名和修改约束
ALTER TABLE teacher MODIFY age VARCHAR(20)  -- 修改约束
ALTER TABLE teacher CHANGE age age1 INT(5)  -- 字段重命名

-- 删除表的字段
ALTER TABLE teacher DROP age1

删除表

-- 删除表
DROP TABLE IF EXISTS teacher

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

注意点：

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

3.MySQL数据管理

3.1外键（物理外键，了解即可）

方式一：在创建表的时候，增加约束（麻烦、复杂）

要删除有外键关系的表，需要先删除引用自己的其他表，在删除该表。即该表被别的表引用，要先删除别的表。

-- 年纪表
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘年纪id‘,
`gradename` VARCHAR(20) NOT NULL COMMENT ‘年纪名称‘,
PRIMARY KEY(`gradeid`)		
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid 要引用年纪表的gradeid
-- 要定义外键
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
`name` VARCHAR(20) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘姓名‘,
`pwd` VARCHAR(20) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
`sex` VARCHAR(5) NOT NULL DEFAULT ‘男‘ COMMENT ‘性别‘,
`gradeid` INT(10) NOT NULL COMMENT ‘学生的年纪‘,
`birthday` DATE DEFAULT NULL COMMENT ‘出生日期‘,
`address` VARCHAR(100) DEFAULT NULL COMMENT ‘家庭地址‘,
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
`name` VARCHAR(20) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘姓名‘,
`pwd` VARCHAR(20) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
`sex` VARCHAR(5) NOT NULL DEFAULT ‘男‘ COMMENT ‘性别‘,
`gradeid` INT(10) NOT NULL COMMENT ‘学生的年纪‘,
`birthday` DATE DEFAULT NULL COMMENT ‘出生日期‘,
`address` VARCHAR(100) 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 constratint 约束名 foreign key(作为外键的列) references 那个表(那个表的字段)

该外键的操作均属于物理外键，数据库级别的外键，不建议使用，避免数据库过多造成困扰。

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行和列
• 要使用多张表的数据，用程序来实现外键的功能。

3.2DML语言

数据库的意义：数据存储和数据管理

DML语言：数据操作语言

• insert 插入
• update 修改
• delete 删除

3.3 插入（添加）

Insert

-- 插入语句
-- insert into 表名(字段1，字段2，字段3) values (‘值1‘,‘值2‘,‘值3‘,),(‘值1‘,‘值2‘,‘值3‘,),(‘值1‘,‘值2‘,‘值3‘,),....

-- 主键是自增的，不主动添加的话主键也会自动添加值
INSERT INTO `grade` (`gradename`) VALUES (‘大三‘);

-- 添加多行
INSERT INTO `grade` (`gradename`) 
VALUES (‘大一‘),(‘大二‘),(‘大四‘);

-- 字段名可以省略，但是值必须与表一一对应，包括主键也要手动添加，不能省略。
INSERT INTO `student` VALUES (2,‘xh‘,‘123465‘,‘男‘,‘1‘,‘1997-6-11‘,‘px‘);

注意点：

• 字段和字段之间使用英文逗号隔开
• 字段是可以省略的，但是后面的值必须与表一一对应，且不能缺少
• 可以同时插入多条数据，values后面的值需要使用逗号隔开。(‘大一‘),(‘大二‘),(‘大四‘);

3.4修改

update

-- 修改
-- 修改学生姓名,有指定的条件 where
UPDATE `student` SET `name`=‘lzp‘ WHERE id=2;

-- 如果没有指定条件，会改动所有的表的值
UPDATE `student` SET `name`=‘lzp‘

-- 可以一次修改多个属性
UPDATE `student` SET `name`=‘lll‘,`gradeid`=2 WHERE id=3;

-- 语法
-- update 表名 set cloume_name = value,[cloume_name = value],... where [条件]

条件：where 子句 运算符

操作符会返回布尔值

-- 通过多个条件定位数据
update `student` set `name`=‘lzp‘ where `name`=‘lll‘ and sex=‘女‘

注意点：

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

3.5删除

delete 命令

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

-- 删除数据
delete from `student` where id=1

truncate 命令

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

truncate 表名

delete 与truncate区别

• 相同点：都能删除数据，都不会删除表的结构
• 不同：
  • truncate 重新设置自增列，计数器会归零，delete不会
  • truncate 不会影响事务

4.DQL查询数据（重点）

4.1 DQL

数据查询语言：

• 所有的查询操作都要用
• 简单的查询和复杂的查询都能做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

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}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE ‘file_name‘
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE ‘file_name‘
      | INTO var_name [, var_name]]
    [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]]

4.2指定查询字段

SELECT * FROM `student`

SELECT * FROM `result`

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

-- 起别名，给结果起别名，AS 也可以给字段起别名，给表起别名
SELECT `studentno` AS ‘学号‘,`studentname` AS ‘姓名‘ FROM `student`

-- 函数,concat(a,b)
SELECT CONCAT(‘姓名:‘,studentName) AS 新名字 FROM student
/*
新名字
姓名:张伟
姓名:赵强
*/

语法：select 字段 from 表

去重distinct

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

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

数据库的列（表达式）

-- 查询系统的版本号
SELECT VERSION()
SELECT 100*3-1 AS 计算结果  -- 用来计算
SELECT @@auto_increment_increment -- 查询自增的步长

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

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

select 表达式 from 表

4.3where条件子句

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

搜索的条件由一个或多个表达式组成！结果是一个布尔值

逻辑运算符

and(&&) a and b 逻辑与

or(||) a or b 逻辑或

Not(!) not a 逻辑非

-- ========================== where ============================
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=95 AND `studentresult`<=100;

SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`<=95 OR `studentresult`>=100;

SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000 

模糊查询：比较运算符

-- ===========================模糊查询===================
-- 查询姓刘的同学
-- like结合  %(代表0到任意个字符)  _(代表一个字符)
-- 查询姓张，且名字只有两个字的，
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
-- 查询地址为空的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address`=‘‘ OR `address` IS NULL

4.4联表查询

JOIN 对比

-- ===========================模糊查询===================
-- 查询姓刘的同学
-- like结合  %(代表0到任意个字符)  _(代表一个字符)
-- 查询姓张，且名字只有两个字的，
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
-- 查询地址为空的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address`=‘‘ OR `address` IS NULL


-- ==================联表查询 join======================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*
思路：
1.分析需求，分析查询的字段来自哪些表，使用联结查询
2.确定使用哪种联结查询？一共有七种
确定表间的交叉点，也就是两个表中有哪些数据是相同的
判断的条件：学生表中的studentNO = 成绩表中的studentNo
*/

-- Inner join 以两个表的交集为基准
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER 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 以右边的表为基准进行查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`

-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL

-- 查询参加了考试的同学的信息：学号，学生姓名，科目名，分数）
/*
使用的表  `student`,`result`,`subject`
*/
SELECT stu.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS stu
RIGHT JOIN `result` AS r
ON stu.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON sub.`subjectno`=r.`subjectno`

SELECT stu.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS stu
LEFT JOIN `result` AS r
ON stu.`studentno`=r.`studentno`
LEFT JOIN `subject` AS sub
ON sub.`subjectno`=r.`subjectno`
WHERE `studentresult` IS NULL

要注意inner join、left join和right join之间的区别，主要看上面的七种查询的图

自连接

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

总表：

总表中pid为1表示为父类数据，pid为其他的为子类数据。pid等于父类的categoryid表示该类为父类的子类。

父类：

子类:

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

SELECT c1.`categoryName` AS ‘父类‘,c2.`categoryName` AS ‘子类‘
FROM `category` AS c1, `category` AS c2
WHERE c1.`categoryid`=c2.`pid`

4.5分页和排序

排序

-- 排序: 升序 asc  降序 desc
-- order by 通过哪个字段排序，怎么排
SELECT stu.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS stu
RIGHT JOIN `result` AS r
ON stu.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON sub.`subjectno`=r.`subjectno`
ORDER BY `studentresult` DESC

分页

-- 分页
-- 可以缓解数据库压力，给人的体验更好

-- 分页，每页只显示五条数据
-- 语法：limit 其实值，页面的大小
-- limit 0,2

SELECT stu.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS stu
RIGHT JOIN `result` AS r
ON stu.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON sub.`subjectno`=r.`subjectno`
ORDER BY `studentresult` DESC
LIMIT 0,2

4.6 子查询

where 后面嵌套查询语句

SELECT stu.`studentno`,`studentname`,`studentresult`
FROM `student` AS stu
INNER JOIN `result` AS r
ON stu.`studentno`=r.`studentno`
WHERE `subjectno` = (
    SELECT `subjectno` FROM `subject`
    WHERE `subjectname`=‘高等数学-1‘
)

4.7分组和过滤

SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
GROUP BY r.`subjectno`               -- 通过什么字段来分组
HAVING AVG(`studentresult`)>80

group by 分组

having 过滤

5.MySQL函数

5.1常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH(‘阿斯蒂芬爱的色放‘)  -- 字符串长度
SELECT CONCAT(‘阿斯蒂芬‘ , ‘阿三士大‘,‘asd‘)  -- 拼接字符串
SELECT INSERT(‘阿斯蒂hello‘, 1,2,‘超级‘)      -- 从某个位置开始，替换某个长度
SELECT UPPER(‘asdfadsf‘)       -- 转换为大写字母
SELECT LOWER(‘ASFD‘)           -- 转换为小写字母
SELECT INSTR(‘asdsfe‘,‘s‘)     -- 查询第一次出现的位置
-- replace , 
-- substr(字符串，截取的位置，截取的长度)

-- 时间和日期函数
SELECT CURRENT_DATE()   -- 获取当前日期
SELECT CURDATE()    -- 获取当前日期
SELECT NOW()       -- 获取当前日期及时间
SELECT LOCALTIME()  -- 获取当前日期及时间
SELECT SYSDATE()    -- 获取系统日期及时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

5.2 聚合函数

-- =========================聚合函数====================
SELECT COUNT(`studentname`) FROM student    -- count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student;               -- 不会忽略null值
SELECT COUNT(1) FROM result;                -- 不会忽略所有的null值

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

5.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,‘lll‘,‘123456‘),(2,‘zzz‘,‘123456‘),(3,‘ppp‘,‘123456‘),(4,‘ldfl‘,‘123456‘)

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

-- 插入的时候加密
INSERT INTO testmd5 VALUES(5,‘asdf‘,MD5(‘123456‘))

6.事物

6.1 什么是事物

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

将一组sql放在一个批次去执行。

事物原则：ACID原则：原子性、一致性、隔离性、持久性

1. 原子性（Atomicity）：是指一个事务要么全部执行，要么完全不执行。
2. 一致性(Consistency): 事务在开始和结束时，应该始终满足一致性约束。比如系统要求A+B=100,那么事务如果改变了A的数值，则B的数值也要相应修改来满足这样一致性要求；与CAP中的C代表的含义是不同的。
3. 隔离性（Isolation）：如果有多个事务同时执行，彼此之间不需要知晓对方的存在，而且执行时互不影响，事务之间需要序列化执行，有时间顺序。
4. 持久性(Durability)：事务的持久性是指事务运行成功以后，对系统状态的更新是永久的，不会无缘无故回滚撤销。

隔离所导致的一些问题

1. 脏读

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

2. 不可重复读

   在一个事物内读取表中的某一行数据,多次读取结果不同。

3. 虚度（幻读）

   是指在一个事物内读取到了别的事物插入的数据，导致前后读取不一致

执行事物

-- ==========================事务========================
-- mysql 是默认开启事务自动提交的
SET autocommit = 0;    -- 关闭
SET autocommit = 1;    -- 开启

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

-- 事务开启
START TRANSACTION    -- 标记一个事务的开始，从这个之后的sql都在同一个事物内

INSERT xx
INSERT xx   -- 两个插入

-- 提交：持久化（成功)
COMMIT
-- 回滚：回滚到原来（失败）

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

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

7. 索引

7.1 索引的分类

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

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

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
 USE school
SHOW INDEX FROM student

-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)

-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM `student`

EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST(‘张‘)

7.2 测试索引

-- 插入100万条数据
DELIMITER $$    -- sql写函数之前必须要写的标志
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),‘user@163.com‘,CONCAT(‘13‘,FLOOR(RAND()*((999999999-10000000)+10000000))),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`=‘用户99999‘;  -- 1.463 sec
SELECT * FROM `app_user` WHERE `name`=‘用户9999‘;   -- 1.038 sec

EXPLAIN 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.164 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`=‘用户99999‘;

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

7.3

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

索引的数据结构

https://blog.codinglabs.org/articles/theory-of-mysql-index.html

8.权限管理和备份

8.1 用户管理

SQLyog 视图操作

sql 命令行

-- 创建用户 create user 用户名 identified by ‘密码‘
CREATE USER lzp IDENTIFIED BY ‘123456‘

-- 修改密码
SET PASSWORD = PASSWORD(‘1234567‘)

-- 修改指定用户的密码
SET PASSWORD FOR lzp = PASSWORD(‘123456‘)

-- 重命名
RENAME USER lzp TO lzp2

-- 用户授权 all privileges 全部权限除了给别人授权,可以给库，也可以给表设置权限
GRANT ALL PRIVILEGES

-- 查看指定权限
SHOW GRANTS FOR lzp

SHOW GRANTS FOR root@localhost   -- GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ WITH GRANT OPTION


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

-- 删除用户
DROP USER lzp

8.2MySQL备份

MySQL数据库备份的方式

• 直接拷贝物理文件

• 在sqlyog这种可视化工具中手动到处

  • 在想要导出的表或者库中，右键，选择备份或导出

  

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

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 shcool student > D:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名

# mysqldump -h 主机 -u 用户名 -p 密码 数据库> 物理磁盘位置/文件名

# 数据库的导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql

9.规范数据库设计

9.1 良好的数据库设计

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

9.2三大范式

数据规范化：

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

三大范式

第一范式

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

第二范式

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

第三范式

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

阿里文档规定：关联查询的表不能超过三张表

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

10.JDBC

10.1 数据库驱动

10.2JDBC程序

新建数据库

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‘)

导入数据库驱动（导入jar包）

import java.sql.*;

public class jdbcTest01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2. 用户信息和url
        //?useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        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的对象statement来执行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("pwd=" + 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. 执行sql，获得返回的结果集
5. 释放连接

10.4 statement对象

jdbc中的statement对象用于向数据库发送sql语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句。

statement对象的executeUpdate方法，用于向数据库发送增删改的sql语句，执行完后会返回一个整数，该整数表示有几行数据被影响

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

代码实现

1. 提取工具类

   package com.lzp.jdbc.util;
   
   import com.sun.org.apache.regexp.internal.RE;
   
   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.*;
   import java.util.Properties;
   
   public class jdbcUtils {
       private static String driver = null;
       private static String url = null;
       private static String username = null;
       private static String password = null;
   
       static{
           try {
               InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
               Properties properties = new Properties();
               properties.load(in);
   
               driver = properties.getProperty("driver");
               url = properties.getProperty("url");
               username = properties.getProperty("username");
               password = properties.getProperty("password");
   
               Class.forName(driver);
           } catch (IOException | ClassNotFoundException e) {
               e.printStackTrace();
           }
   
   
       }
   
       public static Connection getConnection() throws SQLException {
           return DriverManager.getConnection(url,username,password);
       }
   
       public static void release(Connection conn, Statement stat, ResultSet rs){
           if(rs!=null){
               try {
                   rs.close();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }
   
           if(stat != null){
               try {
                   stat.close();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }
   
           if(conn!=null){
               try {
                   conn.close();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }
       }
   }
   
   

   //db.properties
   driver=com.mysql.jdbc.Driver
   url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
   username=root
   password=123456
   

2. 编写增删改查的代码

   //增

   package com.lzp.jdbc.util;
   
   
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   public class TestInsert {
       public static void main(String[] args) {
           Connection conn = null;
           Statement stat = null;
           ResultSet res = null;
   
   
   
           try {
               conn = jdbcUtils.getConnection();
               stat = conn.createStatement();
   
               String sql = "Insert into users(id,NAME,PASSWORD,email,birthday)values(4,‘lzp‘,‘123456‘,‘132456@qq.com‘,‘2020-12-02‘)";
               int i = stat.executeUpdate(sql);
               if(i>0){
                   System.out.println("插入成功!");
               }
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }finally {
               jdbcUtils.release(conn,stat,res);
           }
   
       }
   }
   
   

   删

   package com.lzp.jdbc.util;
   
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   public class TestDelete {
       public static void main(String[] args) {
           Connection conn = null;
           Statement stat = null;
           ResultSet res = null;
   
   
   
           try {
               conn = jdbcUtils.getConnection();
               stat = conn.createStatement();
   
               String sql = "delete from users where id=4";
               int i = stat.executeUpdate(sql);
               if(i>0){
                   System.out.println("删除成功!");
               }
           } catch (
                   SQLException throwables) {
               throwables.printStackTrace();
           }finally {
               jdbcUtils.release(conn,stat,res);
           }
       }
   
   }
   
   

   改

   package com.lzp.jdbc.util;
   
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   public class TestUpdate {
       public static void main(String[] args) {
           Connection conn = null;
           Statement stat = null;
           ResultSet res = null;
   
   
   
           try {
               conn = jdbcUtils.getConnection();
               stat = conn.createStatement();
   
               String sql = "update users set name = ‘lll‘ where id = 4";
               int i = stat.executeUpdate(sql);
               if(i>0){
                   System.out.println("修改成功!");
               }
           } catch (
                   SQLException throwables) {
               throwables.printStackTrace();
           }finally {
               jdbcUtils.release(conn,stat,res);
           }
       }
   }
   
   

   查

   package com.lzp.jdbc.util;
   
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   public class TestSelect {
       public static void main(String[] args) {
           Connection conn = null;
           Statement stat = null;
           ResultSet res = null;
   
   
   
           try {
               conn = jdbcUtils.getConnection();
               stat = conn.createStatement();
   
               String sql = "select name from users where id=4";
               res = stat.executeQuery(sql);
               while(res.next()){
                   System.out.println(res.getString("NAME"));
   
               }
   
           } catch (
                   SQLException throwables) {
               throwables.printStackTrace();
           }finally {
               jdbcUtils.release(conn,stat,res);
           }
       }
   }
   
   

SQL注入问题

sql存在漏洞，会被攻击导致数据泄露，就是SQL语句用or来拼接。

10.5PreparedStatement对象

PreparedStatement可以防止SQL注入。效率更好

package com.lzp.jdbc.lesson03;

import com.lzp.jdbc.util.jdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;

        try {
            conn = jdbcUtils.getConnection();
            String sql = "update users set name = ‘lllzzzppp‘ where id = ?";
            stat = conn.prepareStatement(sql);

            stat.setInt(1,4);

            int i = stat.executeUpdate();

            if(i>0){
                System.out.println("修改成功!");
            }
        } catch (
                SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,stat,res);
        }
    }
}

package com.lzp.jdbc.lesson03;

import com.lzp.jdbc.util.jdbcUtils;

import java.sql.*;

public class sqlRelease2 {
    public static void main(String[] args) {
        login("zhansan","123456");

    }

    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet res = null;

        try {
            conn = jdbcUtils.getConnection();
            //PreparedStatement 放置SQL注入的本质就是把传递进来的参数当作字符
            // 假设其中存在转义字符 ‘ 会被直接转义
            String sql = "select * from users where NAME=? and PASSWORD=?";
            stat = conn.prepareStatement(sql);

            stat.setString(1,username);
            stat.setString(2,password);


            res = stat.executeQuery();
            while(res.next()){
                System.out.println(res.getString("NAME"));
                System.out.println(res.getString("password"));
                System.out.println("==========================");

            }

        } catch (
                SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,stat,res);
        }
    }
}

10.6数据库连接池

池化技术：准备一些预先的资源，过来就连接预先准备好的。

最小连接数：10

最大连接数：15

等待超时：100ms

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

开源数据源实现

1. DBCP
2. C3P0
3. 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接口不会变，方法就不会变。

MySQl

原文：https://www.cnblogs.com/1026804235Lzp/p/14087748.html