[MySQL]从小白到入门学习笔记

本笔记为作者个人学习MySQL的记录，突然在文件夹中看到，所以想着分享给大家。

1、适合已经学习过数据库原理，但尚未接触MySQL的JAVA练习生(●'◡'●)，有繁有简，基本涵盖所有要点，我有信心看完它后你可以跳过视频课而继续向前学习。

2、如有疑惑，配套学习视频见

【狂神说Java】MySQL最新教程通俗易懂_哔哩哔哩_bilibili

3、具体的sql文件篇幅过长，此处不放，可点击↑上方链接ctrl+f搜索相关数据库文件。

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

• MySQL本质是一个数据库管理系统

MySQL :: MySQL 5.7 Reference Manual :: 1.1 About This Manual

1、数据库类型

关系型数据库（SQL）

• MySQL、Oracle、Sql Server、DB2、SQLlite

• 通过表与表之间、行和列之间的关系进行数据的存储 如考勤表、学生信息表

非关系型数据库（NoSQL）

Not Only SQL

• Redis、MongDB

• 对象存储，通过对象的自身的属性来决定

2、Navicat

默认用户名密码

root/123456

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

新建表配置

新建查询

3、操作数据库

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

操作数据库

• 创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

• 使用数据库

-- 如果表名或字段名是一个特殊字符，就需要带``
USE `school`;

• 删除数据库

DROP DATABASE [IF EXISTS] westos;

• 查看数据库

SHOW DATABASES;

列类型

• 数值

• 字符串

• 时间日期

• null

字段属性

• Unsigned：无符号整数。声明该列不能为负数

• zerofill：零填充。根据设计的位数，不足设计位数的补0

• 自增：自动在上一条记录的基础上默认+1，通常用来设计唯一的主键，可以自定义设计主键自增的起始值和步长

• 非空：not null

创建数据库表

• 建表（列、字段）

CREATE TABLE IF NOT EXISTS `student1` (
        `id` INT(4) NOT NULL COMMENT '学号' PRIMARY KEY,
        `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
        `birthday` DATETIME DEFAULT NULL COMMENT '出生日期'
)

格式：

• 查看建表语句

SHOW CREATE TABLE student
​
结果：
CREATE TABLE `student` (
  `id` int(11) NOT NULL COMMENT '学生id',
  `name` varchar(100) NOT NULL COMMENT '学生姓名',
  `age` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• 查看表结构

DESC student

引擎 MyISAM 与 InnoDB

修改删除表

所有的创建和删除尽量加上判断 IF EXISTS 以免报错

-- 修改表
ALTER TABLE student1 RENAME AS student2

-- 增加表的字段
ALTER TABLE student ADD sex1 VARCHAR(2)  --注意：有时候报错，可能是查询里不能和上一段代码一起运行的原因

change重命名，modify改字段属性

-- change用来给字段重命名，不能修改字段类型和约束
-- modify不能给字段重命名，只能修改字段类型和约束
ALTER TABLE student modify age VARCHAR(11)
ALTER TABLE student CHANGE age age1 INT(1)

-- 删除表字段
ALTER TABLE student DROP sex1

-- 如果表存在，就删除
ALTER TABLE IF EXISTS student2

注意：

• 为避免不必要的关键字冲突，所有字段名用 ``包裹

• sql关键字大小写不敏感，尽量小写

• 注释：-- 或 /**/

4、MySQL数据管理

外键

仅了解。外键级联最好在应用层解决（java）

DML语言

即数据操纵语言。

• insert

• update

• delete

添加

-- 插入语句
insert into `student` (`id`,`name`,`age`) values('1002','陈辉','80')

-- 字段可以省略，但值必须一一对应
insert into `student` 
values('1088','陈辉','80')

-- 可以同时插入多条信息
values ('','',''),('','','')

修改

-- 修改： update 修改谁  (条件)  set  原来的值 = 新值
update `student` set `age` = 88 where id = 1002

-- values的值可以是一个变量
update `student` set `age` = CURRENT_TIME where id = 1022  --结果：160724           （16：07：24）

删除

-- delete from 表名 [where 条件]
delete from `student` where id = 1022

TRUNCATE `student`

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

*truncate与delete：

• 都能删除数据，都不会改变表结构

• truncate删除后不会影响事务

• truncate删除后回重新设置自增列 计数器会清零

*而delete删除后再重启数据库：

• InnoDB：自增列会从1开始（存在内存中的，断电即失）

• MyISAM：继续从上一个自增量开始（存在文件中的，不会丢）

5、DQL查询语言

严格注意顺序！

指定查询字段

简单查询、重命名、查询结果拼接字符串

-- 查询全部学生
select * from student

-- 查询指定字段
select `studentno`,`studentname` from student


-- 用AS起别名，可以给字段也可以给表
select `studentno` AS 学号,`studentname` AS 学生姓名 from student

-- 函数 Concat (a,b) 拼接字符串
select CONCAT('姓名：',studentname) AS 新名字 from student -- 在查询结果里添加字符串，结果      姓名：刘伟

去重distinct

-- 去重distinct
select distinct `studentno` from result  -- 查询有哪些同学参加了考试

计算查询

-- 查询系统版本
select VERSION()

-- 用来计算
select 100*3-1 AS 计算结果

-- 查询自增的步长（变量）
select @@auto_increment_increment

-- 查询所有学生考试成绩—+1分的结果
select `studentno`,`studentresult` +1 AS '加分后' from result

where

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

• 逻辑运算符

&& || !=

• 模糊查询

like、in（%配合like使用，in要用在具体的值）

-- 查询姓赵的同学
-- like结合 %（代表n个字符）  、   _(一个字符)

select `studentno`,`studentname` from `student`
where `studentname` like '赵%'
-- 查询姓赵的同学，名字俩字的
select `studentno`,`studentname` from `student`
where `studentname` like '赵_'


-- 查询1001,1002,1003号学员
select `studentno`,`studentname` from `student`
where `studentno` in (1001,1002,1003);

-- 查询有出生信息的同学
select `studentno`,`studentname` from `student`
where `borndate` is not null

联表查询

JOIN

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）    INNER JOIN
select s.studentno,`studentname`,`subjectno`,`studentresult`  -- 注意：s.的不能加进飘号内
from student AS s
inner join result AS r
where s.studentno = r.studentno

-- 查询参加了考试的同学+result表中的数据（学号，姓名，科目编号，分数）
select s.studentno,`studentname`,`subjectno`,`studentresult`  -- 注意：s.的不能加飘号
from student s           -- 省略AS 改为空格
right join result r
on s.studentno = r.studentno

-- 查询参加了考试的同学+student表的数据（学号，姓名，科目编号，分数）
select s.studentno,`studentname`,`subjectno`,`studentresult`  -- 注意：s.的不能加飘号
from student s           -- 省略AS 改为空格
left join result r
on s.studentno = r.studentno

• inner join查询两个表中的交集数据；left join查询交集数据+左表全部数据；right join查询交集数据+右表全部数据；

• from 表 XXX join 连接的表 on 连接（交叉）条件

• 如果存在多张表查询，先从两张表开始慢慢交叉，不着急

自连接（了解）

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

如：要将如下的表修改为 父类名称|子类名称

select a.`categoryname` as '父栏目',b.`categoryname` as '子栏目'
from `category` AS a ,`category` AS b
where a.`categoryid` = b.`pid`

分页和排序

limit | order by

排序order by——ASC升序排列、DESC降序排列

-- ASC 升序排列
select *
from result
order by studentresult ASC  -- 按照哪个字段排序

分页limit——limit 起始值，页面大小

select *
from result
order by studentresult DESC
limit 0,3    -- 得到前三条数据
limit 2,3  -- 得到3，4，5这三条数据

注意：limit 后起始值从0开始，0为第一条数据

当前页数limit怎么写？
对于第n页的limit写法： (n-1) * pagesize
 

子查询

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

-- 查询 高等数学-1 的所有考试结果（学号，科目编号，成绩） 降序排列
select `studentno`,r.`subjectno`,`studentresult`
from result r
inner join subject sub
on r.subjectno = sub.subjectno
where `subjectname` = '高等数学-1'
order by studentresult DESC

-- 使用子查询
select `studentno`,r.`subjectno`,`studentresult`
from result r
where `subjectno` = (
select `subjectno`
from `subject`
where subjectname = '高等数学-1')

6、MySQL函数

常用函数

-- 数学运算
select abs(-8)   -- 绝对值
select celling(9.4)   -- 向上取整
select floor(9.4)   -- 向下取整
select floor()   -- 返回一个 0~1之间的随机数
select sign(10)   -- 判断一个数的符号 0-0 负数返回-1，正数返回1

-- 字符串函数
select CHAR_LENGTH('哦嘎嘎嘎嘎')   -- 求字符串长度
select concat('一给','我里giaogiao')   -- 拼接字符串
select insert('我爱编程helloworld',1,2,'超级喜欢')   -- 查询，从某个位置开始替换某个长度
select lower('HELLO')  -- 转换为小写
select upper('hello')  -- 转换为大写

-- 时间和日期
select CURRENT_DATE() -- 获取当前日期
select CURDATE()  -- 获取当前日期
select now() -- 获取当前时间
select localtime() -- 本地时间

聚合函数

-- 统计表中的数据
select count(`studentresult`) from result; -- count(字段)，忽略所有null

select count(`*`) from result; -- 不会忽略null
select count(`1`) from result; -- 不会忽略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

group by | Having :

-- 查询不同课程的平均分，最高分，最低分（根据不同的课程分组）,平均分大于80分
select subjectname,avg(studentresult),max(studentresult),min(studentresult)
from result r
inner join `subject` sub
on r.`subjectno` = sub.`subjectno`
group by r.subjectno
having avg(studentresult)>80

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

MD5加密：

-- 调用MD5函数进行加密
update testmd5 set pwd=MD5(pwd) 

还可以在插入的时候加密：

-- 插入的时候加密
insert into testmd5 values(4,'chenhui',MD5('fhgdt43e52'))

如何校验：将用户传递进来的密码进行md5加密，然后比对加密后的值

select * from testmd5 where `name`='chenhui' and pwd=MD5('fhgdt43e52')

7、事务

要么都成功，要么都失败

• 将一组SQL放到一个批次中一起执行

• 事务原则：ACID原则，原子性（要么都成功，要么都失败）、（最终）一致性、隔离性（互不干扰）、持久性（事务一旦提交就不可逆）

-- 事务自动提交
set autocommit = 0 /* 关闭*/
set autocommit = 1 /* 开启（默认）*/

-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后的sql语句都在同一个事务内
insert xx
insert xx

-- 提交：持久化（成功了）
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; -- 回复默认值

8、索引

索引是帮助MySQL高效获取数据的数据结构

索引的分类

• 主键索引（PRIMARY KEY）

  • 主键索引只能有一个，唯一索引可以有多个

• 唯一索引（UNIQUE KEY）

• 常规索引（KEY/INDEX）

  • 默认的

• 全文索引（FUllText）

  • 再特定的数据库引擎下才有

-- 显示所有的索引信息
show index from student

-- 增加一个全文索引  （索引名） 列名
alter table school.student add fulltext index `studentname`(`student`);

-- explain分析sql执行的状况
explain select * from student where

索引创建与作用

-- 创建索引
-- 索引命名：id_表名_ 字段名
-- create index 索引名 on 表（字段）
create index id_app_user_name on app_user(`name`);
-- 加了索引后，再执行查询语句速度会大大的快

索引原则

• 索引不是越多越好

• 不要对经常变动的数据加索引

• 小数据量的表不需要加索引

• 索引一般用来加在常用来查询的字段上

9、权限管理和备份

用户管理

-- 创建用户 create user 用户名 identified by '密码'
create user minhang identified by '123456'

-- 修改当前用户密码
set password = password('123456')

-- 修改指定用户密码
set password for minhang = password('123456')

-- 重命名 rename user 原来名字 to 新名字
rename user minhang to minshuaihang

-- 用户授权    all privileges 除了给别人授权，别的都能干
grant all PRIVILEGES *.*-- 授予全部权限，库.表
grant all PRIVILEGES *.* to minhang-- 授予全部权限，库.表

-- 查询权限
show grants for minhang  -- 查看指定用户的权限
show grants for root@localhost

-- 撤销权限 revoke 哪些权限， 在哪个库撤销，给谁撤销
revoke all privileges on *.* from minhang

-- 删除用户
drop user minhang

MySQL备份

备份的方式：

• 直接拷贝物理文件

• 在Sqlyog这种可视化工具中手动导出

  • 对于 表 右键 转储 结构和数据

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

  • #导出
    # mysqldump -h 主机 -u 用户名 -p 密码  数据库 表名 > 物理磁盘位置/文件名
    mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
    
    
    #导入
    #登录的情况下，切换到指定的数据库
    #source 备份文件
    source d:/a.sql
    #未登录（不建议，容易覆盖）
    mysql -u用户名 -p密码 库名< 备份文件

10、规范数据库设计

数据库设计

• 分析需求

• 概要设计：设计关系图E-R图

以博客网站为例：

• 收集信息，分析需求

  • 用户表（用户注销登录，用户的个人信息，写博客，创建分类）

  • 分类表（文章分类，谁创建的）

  • 文章表（文章的信息）

  • 友链表（友链信息）

  • 自定义表（系统信息，某个关键的字，或者一些主字段） key:value

  • 说说表（发表心情 id content create_time）

• 标识实体（把需求落实到每个字段）

• 标识实体之间的关系

  • 写博客：user —> blog

  • 创建分类：user —>category

  • 关注：user —> user

  • 友链：links

  • 评论：user —user—blog

11、三大范式

数据规范化

第一范式

要求数据库表的每一列都是不可分割的原子数据项

如：家庭信息与学校信息就可再分

第二范式

在1NF的基础上，确保每一列都和主键相关，而不是只和主键的某一部分相关

第三范式

在2NF的基础上，任何非主属性不依赖于其他非主属性（消除传递依赖）

12、JDBC

jdbc是java操作数据库的一个规范，规范由具体的厂商实现。对于开发人员来说，只需要掌握jdbc接口即可

java.sql

javax.sql

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

第一个JDBC程序

1、创建测试数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

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

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

2、导入数据库驱动

驱动下载地址：MySQL :: Download MySQL Connector/J (Archived Versions)

创建lib文件夹—>将驱动jar包复制进去—>右键lib文件夹添加为库

3、创建java类，执行jdbc程序

package com.11.study1;

import java.sql.*;

//我的第一个JDBC程序
//相当于用java代码来实现可视化的navicat操作
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 支持中文编码&设置中文字符集utf8&使用安全的连接
        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，可能存在结果，查看返回结果
        String sql = "SELECT * FROM `users`";
        //执行的SQL语句，删除、插入等调用statement.executeUpdate()函数
        ResultSet resultSet = statement.executeQuery(sql);    //放回的结果集
        while (resultSet.next()){
            System.out.println("id="+ resultSet.getObject("id"));    //不知道结果是个什么类型，就用object类：resultSet.getObject
            System.out.println("name="+ resultSet.getObject("NAME"));
            System.out.println("pwd="+ resultSet.getObject("PASSWORD"));
            System.out.println("email="+ resultSet.getObject("email"));
            System.out.println("birthday="+ resultSet.getObject("birthday"));
        }

        //6.释放连接
        //一定要关闭，不然浪费资源
        resultSet.close();
        statement.close();
        connection.close();

    }
}

4、得到结果

Statement对象

实现驱动只加载一次，且只需要写一个jdbc工具类（静态代码），再写一个增删改的测试类即可操纵数据库。

1、配置properties文件

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

注意位置在源文件src下

2、提取工具类

即写获取连接资源、释放连接等公共部分代码

package com.11.study2.utils;

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


//Jdbc工具类，以后每个jdbc程序都可以用这里当公共部分
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 {
        System.out.println(url);
        return DriverManager.getConnection(url,username,password);
    }


    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    };
}

3、增删改测试类

package com.11.study2;

import com.11.study2.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 conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement();  ///获取SQL的执行对象
            String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "values(4,'jetman','123456','123213@qq.com','2000-01-01')";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }

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

SQL注入问题

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

PreparedStatement对象

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

package com.11.study3;

import com.11.study2.utils.JdbcUtils;

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

//更安全的插入方法，可以避免sql注入
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try{
            conn = JdbcUtils.getConnection();

            //区别
            //使用 ? 占位符代替参数
            String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";

            st = conn.prepareStatement(sql);   //预编译SQL，险些sql，然后不执行

            //手动给参数复制
            st.setInt(1,5);  //id
            st.setString(2,"shuaige");
            st.setString(3,"123456");
            st.setString(4,"1231253342@qq.com");
            //注意点： sql.Date 数据库       java.sql.Date()
            //       util.Date Java       new Date().getTime()  获得时间戳
            st.setDate(5, Date.valueOf("2001-11-11"));

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


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

    }
}

使用idea连接数据库

连接成功后，可以选择数据库

此时可以查看数据库表

可以可视化更改数据，但一定要记得提交

可以从默认控制台写sql

jdbc操作事务

数据库连接池