Mysql 学习

最新推荐文章于 2022-10-10 21:38:32 发布

原创最新推荐文章于 2022-10-10 21:38:32 发布 · 206 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

本文全面介绍了MySQL数据库的基础操作，包括数据库的创建、管理、数据表的创建与修改、数据的增删改查、索引的使用及事务处理。涵盖了SQL语言的DML、DQL部分，以及JDBC在Java中操作MySQL的方法。

1. 初始MySQL

Java EE：企业级Java开发 web

前端（页面：展示，数据）

后台：（连接点：连接数据库JDBC，连接前端：控制，控制视图跳转，和给前端传数据）

数据库(DataBase)：存数据

1.1 连接数据库

mysql -u root -p	--连接数据库
--------------------------------------------
--所有语句使用;结尾
show databases;		-----查看所有数据库   use 切换数据库

describe student;	----显示数据库中所有的表的信息
create database db1 -----创建数据库

2. 操作数据库

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

2.1操作数据库

创建数据库

CREATE DATABASE [IF not EXISTS] db1;

使用数据库

USE db1;

删除数据库

DROP DATABASE [IF EXISTS] db1;

查看数据库

SHOW DATABASES;

2.2 数据库的列类型

数组

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整形 4个字节
bigint 较大的数据 8个字节
float[M,D] 浮点数 4个字节 M表示数字总数，D表示小数点后的位数
double[M,D] 浮点数 8个字符
decimal 字符串形式的浮点数金融计算使用

字符串

char 字符串固定大小 0-255
varchar 可变字符串
tinytext 微型文本
text 文本串

时间日期

date YYYY-MM-DD 日期
time HH：MM；SS 时间
datetime YYYY-MM-DD HH：MM：SS
timestamp 时间戳
year 年份表示

null

没有未知

2.3 数据库的字段属性

UNSIGNED：

无符号的整数
不能为负数

ZEROFILL：

0填充
不足的位数，使用0填充，int(3) ,6 — 006

自动填充 AUTO_INCREMENT

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

非空 NILL NOT NULL

设置为NOT NULL，不给值报错
NULL 不填写，默认为NULL

默认 DEFAULT：

设置默认值
如果不指定该列的值，则会有默认的值

2.4 创建数据表

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

常用命令：

SHOW CREATE DATABASE db1;  --- 查看数据库创建命令
SHOW CREATE TABLE tb1;    ---- 查看数据表的创建命令
DESC tb1;				---- 查看数据表的结构信息

2.5 数据库引擎

： MYSIAM 和INNODB的区别

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大(2倍的MYISAM)
优点	节约空间，速度快	安全性高，事务处理，多表多用户操作

2.6 数据库引擎在文件上的区别

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

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

DEFAULT CHARSET = utf8

可以在my.ini 中配置默认编码

character-set-server=utf8

2.8 修改表

--修改表名		ALTER TABLE 旧表名  RENAME AS 新表名
--增加表的字段	ALTER TABLE 表名 ADD 字段名  列属性
--修改表的字段	ALTER TABLE 表名 MODIFY 字段名 列属性
--				ALTER TABLE 表名 CHANGE 旧表名 新表名 列属性
--删除表的字段	ALTER TABLE 表名 DROP 字段名

2.9 删除表

-- 删除表
DROP TABLE [IF EXISTS] tb1;

3. MySQL数据管理

3.1 外键（了解即可）

方式一，在创建表的时候，增加约束

KEY `FK_gradeid` (gradeid),
CONSTRAINT `FK_gradeid` FOREIGN (gradeid) REFERENCES grade (gradeid)

删除有外键约束的表时，需要先删除从表，在删除主表

方式二，添加外键约束

ALTER TABLE student 
ADD CONSTRAINT FK_gradeid FOREIGN KEY(geadeid) REFERENCES grade (gradeid)
-- ALTER TABLE 表名 
ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 引用的表(引用的字段)

最佳实践

数据库就是单纯的表，只用来存数据，只有行和列
我们想使用多种表的数据，使用程序去实现

3.2 DML语言数据库操作语言

DML语言：数据操作语言

INSERT
ALTER
DROP

3.3 插入

语法：INSERT INTO 表名([字段名1,字段2，字段3....]) VALUES('值1'),('值2')；

注意事项：

字段之间用逗号隔开
不写字段是，但是后面的值必须要一一对应
可以同时插入多条数据，VALUES后面可以输入多个值，（‘值1’）,(‘值2’)…

3.4 更新

UPDATE

语法：UPDATE 表名 SET 列名 = VALUE[,列名 = VALUE,.....] WHERE 条件

条件：where 子句

操作符有：

=
<>或!=
>
<
<=
>=
BETWEEN…AND… 在某个范围内
AND 相当于&&
OR 相当于||

3.5 删除

delete命令

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

TRUNCATE 命令

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

TRUNCATE student;

delete与TRUNCATE区别

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

4. DQL查询数据

4.1 DQL 数据查询语言

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

所有查询都使用： select

完整语法：

select [ALL | DISTINCT]	---默认为查询全部也可以选择去重
--要查询的字段
from table_name			----查询的表（表和字段都可以取别名）
[left | right | inner JOIN table2_name]	---链表查询
[where ...]		---指定结果满足的条件
[group by ...]  ---指定结果按照哪几个字段分组
[having ....]   ---过滤分组结果满足的条件
[order by ...]  ---指定查询记录按一个或多个条件排序 ASC 升序 DESC 降序
[limit ...]     ---指定查询的结果从哪条至哪条显示

4.2 指定查询

语法：SELECT 字段,...FROM 表

去重 distinct

作用：去除SELECT查询结果中重复的数据

select distinct *** from ***;

数据库的列（表达式）

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

select 表达式 from表

4.3 where条件子句

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

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

逻辑运算符

and &&
or ||
Not ！

尽量使用英文字母

比较运算符

IS NULL
IS NOT NULL
BETWEEN…AND… 区间
LIKE 如果a匹配b，则结果为真
- %代表0到任意一个字符
- _代表一个字符
IN a in(a1,a2,a3…) a在a1,a2,a3… 中的某一个结果为真

4.4 连接查询

操作	描述
INNER Join	如果表中至少有一个匹配，就返回
LEFT JOIN	会从左表中返回所有的值，即使右表没有匹配
RIGHT JOIN	会从右表中返回所有的值，即使左表没有匹配

自连接

SELECT a.categoryName AS '父结点', b.categoryName AS '子节点'
form category AS a,category AS b
WHERE a.categoryid = b.pid;

4.5 分页和排序

分页：limit
排序：order by
- 升序：ASC
- 降序：DESC

排序

语法：ORDER BY 字段排序顺序

分页

语法：LIMIT 起始位置结束位置（记录从0开始）

分页原理：

pageSize：页面大小

起始值：(n-1)*pageSize

当前页：n

总页数：数据总数/页面大小

第N页：(n-1)*pageSize,pageSize

4.6 子查询

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

4.7 分组和分组后的条件过滤

分组：GROUP BY 字段

过滤：HAVING 条件

5.MySQL函数

5.1 常用函数

数学运算函数
- ABS（）绝对值
- ceiling（）向上取整
- floor（）向下取整
- rand（）随机数0-1之间
- sign（）判断数的符号负数返回 -1 正数返回 1 0返回0
字符串函数
- char_length() 字符串长度
- concat（）拼接字符串
- insert（）插入
- replace（）替换
- lower（）转小写
- upper（）转大写
- substr（）返回指定字符串
时间和日期函数
- CURRENT_DATE() 获取当前日期
- now() 获取当前时间
- LOCALTIME() 本地时间
系统
- UESR() 当前用户
- VERSION() 版本号

5.2 聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

6. 事务

6.1 什么是事务

要么都成功，要么都失败

将一组SQL放在一个批次中去执行

6.2 事务原则

ACID原则：

原子性
- 要么都成功，要么都失败
一致性
- 事务前后的数据完整性要保持一至
隔离性
- 事务的隔离性是多个用户并发访问数据库，数据库为每个用户开启的事务，不能被其他事务的财政数据所干扰，所以事务之间要相互隔离
持久性
- 事务一旦提交不可逆，被持久化到数据库中

隔离导致的问题

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

6.3 事务创建

---Mysql是默认开启事务自动提交
SET autocommit = 0 --- 关闭
SET autocommit = 1 ---开启
----创建事务流程
--- 1 关闭自动提交
SET autocommit = 0
--- 2 开启事务
START TRANSACTION 
--- 3 事务内容
INSERT xxx
--- 4 提交或回滚
COMMIT --- 提交
ROLLBACK --- 回滚
--- 5 事务结束 开启自动提交
SET autocommit = 1

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

6.4 事务练习

--- 模拟A给B转账
--- 先关闭自动提交
SET autocommit = 0;
--- 开启事务
STRAT TRANSACTION;
--- 开始转账
UPDATE 表 SET money = money-500 where name = 'A' --- A减500
UPDATE 表 SET money = money+500 where name = 'B' --- B加500
--- 提交事务 持久化
COMMIT;
--- 回滚
ROLLBACK;
--- 恢复默认值
SET autocommit = 1;

7. 索引

7.1 索引分类

主键索引（PRIMARY KYE）
- 唯一标识，主键不可重复，只有一个列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
常规索引（KEY/INDEX）
- 默认的
全文索引（FULLTEXT）
- 在特定的数据库引擎下才有，快速定位数据

--- 显示索引信息
show index from table_name
--- 添加一个索引
ALTER TABLE table_name ADD 索引名称 INDEX 索引别名 (字段名)
--- 方法二
CREATE INDEX 索引名 ON 表(字段)
--- EXPLAIN 分析SQL执行情况
EXPLAIN SELECT * FROM student   --- 分析SQL语句执行情况

7.2 索引原则

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

8. 权限管理和备份

8.1 用户管理

--- 创建用户
CREATE USER 用户名 IDENTIFIED BY '123456'
---修改密码
SET PASSWORD = PASSWORD('123456')
--- 修改指定用户密码
SET PASSWORD FOR ROOT = PASSWORD('123456')
--- 重命名
RANAME USER 原名 TO 新名
--- 用户权限授予  ALL PRIVILEGES 全部权限
GRANT ALL PRIVLEGES ON *.* TO uesr_name
--- 查看权限
SHOW GRANTS FOR user_name
--- 撤销权限
REVOKE ALL PRIVLEGES ON *.* FROM user_name

8.2 MySQL数据备份

方式：

拷贝物理文件
可视化工具中导出

使用命令行导出 mysqldump

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -pxakxak school student > D:/1.sql
#导入
#登录的情况下导入
source d:/1.sql

9. 规范数据库设计

9.1 数据库设计

良好的数据库设计

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

数据库设计

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

9.2 三大范式

10.JDBC

10.1 数据库驱动

程序需要导入数据库驱动，但每个数据库都有不同的驱动，需要编写多套程序

SUN公司提供了一个Java操作数据库规范：JDBC

现在开发人员只需要学习JDBC，数据库驱动会

10.2 第一个JDBC程序

package com.xu.Demo1;

import java.sql.*;

/**
 * 第一个JDBC程序
 */

public class JDBCFirstDemo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        // 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 用户信息
        // useUnicode=true&characterEncoding=utf8&useSSL=true 参数一 支持中文编码 参数二 设置字符集为utf-8 参数三 使用安全链接
        String url = "jdbc:mysql://localhost:3306/t1?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
        // 用户名
        String user = "root";
        String password = "xakxak";
        // 连接信息，数据库对象
        // DriverManager 驱动程序管理器
        // Connection 连接
        Connection connection = DriverManager.getConnection(url,user,password);
        // 执行SQL的对象 Statement
        Statement statement = connection.createStatement();
        // 执行SQL
        //SQL 语句
        String sql = "SELECT * FROM chn_city";
        // 得到结果集 resultset 结果集
        // executeQuery 执行查询
        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("district_id=" + resultSet.getObject("district_id"));
        }
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

加载驱动
连接数据库 DriverManager 得到一个connection(连接)对象
通过connection对象创建一个statement对象 statement可以执行SQL语句
获取返回的ResultSet(结果集)
关闭连接

10.3 对象分析

DriverManager 驱动管理

// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver);
Class.forName("com.mysql.cj.jdbc.Driver"); // 加载驱动

URL

String url = "jdbc:mysql://localhost:3306/t1?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
// mysql ---3306
// jdbc:mysql://主机地址：端口号/数据库名？参数1&参数2&

connection 连接

// connection 代表数据库
// 就是一个数据库对象
// 可以设置自动提交
connection.setAutocommit();
// 提交
connection.commit();
// 回滚
connection.rollback();

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

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

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

遍历：

使用next() 移动到下一个数据

获得指定数据类型

getString();

getInt();

getFloat();

…

10.4 代码练习

提取工具类

配置文件

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/t1?useUnicode=ture&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8
username = root
password = xakxak

工具类

package com.xu.utils;

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

/**
 * JDBC工具类
 */

public class Jdbcutils {
    private static String driver;
    private static String username;
    private static String url;
    private static String password;
    static {
        try{
            InputStream inputStream = Jdbcutils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // 加载驱动
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取连接
    public static Connection gerconnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    // 释放资源
    public static void release(Connection con, Statement st, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(con != null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

10.6 SQL注入

package com.xu.Demo2;

import com.xu.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("徐安康","123456");
        //SQL注入问题，下面这个输入可以获取数据库里面所有的记录
        login("' or '1=1","' or '1=1");
    }
    // 登录业务
    public static void login(String name,String password){
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 工具类 获取连接
            connection = Jdbcutils.gerconnection();
            // 获取Statement对象
            st = connection.createStatement();
            // 编写SQL
            String sql = "select * from users where name = '" + name + "'and password = '" + password + "';";
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
}

10.7 PreparedStatement对象

因为SQL注入问题，需要采用PreparedStatement来执行SQL

 public static void loginPlus(String name,String password){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbcutils.gerconnection();
            String sql = "select * from users where name = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            Jdbcutils.release(connection,preparedStatement,resultSet);
        }
    }

10.8 事务

ACID原则：

原子性
- 同时成功，同时失败
一致性
- 总和不变
隔离性
- 多个进程之间互不干扰
持久性
- 提交不可逆

隔离性问题：

脏读
- 一个事务读取了另一个没有提交的事务
不可重复读
- 同一个事务，重复读取了表中的事务，表的数据发生了改变
虚读(幻读)
- 在一个事务内，读取到别人插入的数据，导致前后不一致

package com.xu.Demo2;

import com.xu.utils.Jdbcutils;

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

public class TestTransaction {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;

        try {
            connection = Jdbcutils.gerconnection();
            // 关闭自动提交 打开事务
            connection.setAutoCommit(false);
            
            String sql1 = "update users set name = '123456' where id = 1";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            
            String sql2 = "update users set name = 'xakxak' where id = 2";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            // 提交
            connection.commit();
            // 失败默认回滚
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
}