MySQL笔记

菜鸟雷

发布于 2020-10-23 14:49:15

1.3K0

文章被收录于专栏：菜鸟雷的学习笔记菜鸟雷的学习笔记

简介

描述事物的符号称为数据
数据库（Database）指长期存储在计算机内的、有组织的、可共享的数据集合。数据库实际上就是一个文件集合，是一个存储数据的仓库，本质就是一个文件系统，数据库是按照特定的格式把数据存储起来，用户可以对存储的数据进行增删改查操作
数据库管理系统（DBMS）是数据库系统的核心软件之一，是位于用户与操作系统之间的数据管理软件，用于建立，使用和维护数据库。它的主要功能包括数据定义、数据操作、数据库的运行管理、数据库的建立和维护等几个方面

数据库类型

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单说，关系型数据库是由多张能互相连接的表组成的数据库
优点：
- 都是使用表结构，格式一致，易于维护
- 使用通用的 SQL 语言操作，使用方便，可用于复杂查询
- 数据存储在磁盘中，安全
缺点：
- 读写性能比较差，不能满足海量数据的高效率读写
- 不节省空间。因为建立在关系模型上，就要遵循某些规则，比如数据中某字段值即使为空仍要分配空间
- 固定的表结构，灵活度较低

非关系型数据库

非关系型数据库又被称为NoSQL（Not Only SQL )，意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
优点：
- 非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活，应用场景广泛，而关系型数据库则只支持基础类型
- 速度快，效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘
- 海量数据的维护和处理非常轻松
- 非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势
- 可以实现数据的分布式处理
缺点：
- 非关系型数据库暂时不提供 SQL 支持，学习和使用成本较高
- 非关系数据库没有事务处理，没有保证数据的完整性和安全性。适合处理海量数据，但是不一定安全
- 功能没有关系型数据库完善

数据类型

MySQL 的数据类型有大概可以分为 5 种，分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等
- 注意：整数类型和浮点数类型可以统称为数值数据类型。
数值类型：
- 整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL
日期/时间类型：
- 包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP
字符串类型：
- 包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等
二进制类型：
- 包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

整数类型

整数类型又称数值型数据，数值型数据类型主要用来存储数字
MySQL 提供了多种数值型数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大

小数类型

MySQL使用浮点数和定点数来表示小数
浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL
浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。
浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。DECIMAL 的默认 D 值为 0、M 值为 10。

日期和时间类型

==字符串类型==

基本操作

操作数据库(DDL)

C(Create)：创建
- 创建数据库
  - create database 数据库名称
- 创建数据库，判断不存在，再创建
  - create database if not exists 数据库名称
- 创建数据库，并指定字符集
  - create database 数据库名称 character set 字符集名
R(Retrieve)：查询
- 查询所有数据库的名称
  - show databases
- 查询某个数据库的字符集：查询某个数据库的创建语句
  - show create database 数据库名称
U(Update)：修改
- 修改数据库的字符集
  - alter database 数据库名称 character set 字符集名称
D(Delete)：删除
- 删除数据库
  - dorp database 数据库名称
- 判断数据库存在，存在再删除
  - drop database if exists 数据库名称
使用数据库
- 查询当前正在使用的数据库名称
  - select database()
- 使用数据库
  - use 数据层名称

操作表(DDL)

C(Create)：创建
- 创建表
  - create table 表名( 列名1 数据类型1, 列名2 数据类型2, ... 列名n 数据类型n )；
复制表
- create table 表名 like 被复制的表名
R(Retrieve)：查询
- 查询某个数据库中所有的表名称
  - show tables
- 查询表结构
  - desc 表名
U(Update)：修改
- 修改表名
  - alter table 表名 rename to 新的表名
- 修改表的字符集
  - alter table 表名 character set 字符集名称
- 添加一列
  - alter table 表名 add 列名数据类型
- 修改列名称类型
  - alter table 表名 change 列名新列别新数据类型
- 删除列
  - alter table 表名 drop 列名
D(Delete)：删除
- drop table if exists 表名

增删改(DML)

添加数据
- insert into 表名(列名1,列名2..) values(值1,值2...)
删除数据
- delete from 表名 [where 条件]
修改数据
- update 表名 set 列名1=值1,列名2=值2,... [where 条件]

查询语句(DQL)

去重复：distinct
条件查询：where子句后跟条件
模糊查询：like '%字符%'
排序查询(order by )
- order by 排序字段1 排序方式1
  - ASC：升序，默认
  - DESC：降序
聚合函数
- count：计算个数
- max：计算最大值
- min：计算最小值
- sum：计算和
- avg：计算平均数
分组查询
- 语法：group by
- having：分组之后的条件
分页查询
- 语法：limit 开始的索引，每页查询的条数
- 公式：开始的索引=（当前的页码-1）*每页显示的条数

管理用户、授权(DCL)

管理用户
- 添加用户
  - create user '用户名'@'主机名' identified by '密码'
- 删除用户
  - drop user '用户名'@'主机名'
- 修改用户密码
  - set password for '用户名'@'主机名' =password('新密码')
- 查询用户
  - 切换到mysql数据库
    - use mysql
  - 查询user表
    - select * from user
  - 通配符：% 标识可以在任意主机使用用户登录数据库
权限管理
- 查询权限
  - show grants for '用户名'@'主机名'
- 授予权限
  - grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'
  - grant all on *.* to '用户名'@'主机名'
撤销权限
- revoke 权限列表 on 数据库.表名 from '用户名'@'主机名'

修改root密码

管理员运行cmd：
- net stop mysql
使用无验证方式启动mysql服务：
- mysqld --skip-grant-table
打开新的cmd窗口，直接输入mysql命令，敲回车。就可以登录成功
use mysql
update user set password = password('你的新密码') where user = 'root';
关闭两个窗口
打开任务管理器，手动结束mysqld.exe的进程
管理员运行cmd，启动mysql服务
使用新密码登录

约束

使用 SHOW CREATE TABLE <数据库表名>语句来查看表中的约束

非空约束：not null，值不能为null
- 创建表时添加约束
  - create table 表名( 字段类型 not null );
- 创建表完后，添加非空约束
  - alter table 表名 modify 字段类型 not null
- 删除字段的非空约束
  - alter table 表名 modify 字段类型
唯一约束：unique，值不能重复
- 注意
  - 唯一约束可以有null值，但是只能有一条记录为null
- 在创建表时，条件唯一约束
  - create table 表名( 字段类型 unique );
- 删除唯一约束
  - alter table 表名 drop index 字段
- 在表创建完后，添加唯一约束
  - alter table 表名 modify 字段类型 unique
主键约束：primary key
- 注意
  - 含义：非空且唯一
  - 一张表只能有一个字段为主键
- 在创建表时，添加主键约束
  - create table 表名( 字段类型 primary key );
- 删除主键
  - alter table 表名 drop primary key
- 创建完表后，添加主键
  - alter table 表名 add primary key(字段)
- 自动增长
  - 在创建表时，添加主键约束，使用auto_increment可以来完成值的自动增长
  - 在创建表时，添加主键约束
    - create table 表名( 字段类型 primary key auto_increment );
  - 删除自动增长
    - alter table 表名 modify 字段类型
  - 添加自动增长
    - alter table 表名 modify 字段类型 auto_increment
外键约束：foreign key
- 在创建表时，添加外键
  - create table 表名( 外键列名类型 constraint 外键名称 foreign key (从表列名称) references 主键名称(主表列名称) )
- 删除外键
  - alter table 表名 drop foreign key 外键名称
- 创建表之后，添加外键
  - alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称)
- 级联操作
  - 添加级联操作
    - alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade
  - 级联更新：on update cascade
  - 级联删除：on delete cascade

数据库的设计

索引

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

多表之间的关系

实现关系
- 一对多(多对一)
  - 在多的一方建立外键，指向一的一方的主键
- 多对多
  - 需要借助第三方中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
- 一对一
  - 可以在任意一方添加唯一外键指向另一方的主键

范式

第一范式(1NF)：每一列都是不可分割的原子数据项
第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
- 函数依赖：A--->B，如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
- 完全函数依赖：A--->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值
- 部分函数依赖：A--->B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可
- 传递函数依赖：A--->B，B--->C，如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性(属性组)的值可以确定唯一C属性的值，则称C传递函数依赖于A
- 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
第三范式(3NF)：在2NF基础上，任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)

多表查询

内连接查询
- 隐式内连接：使用where条件消除无用数据
- 显式内连接：
  - select 字段列表 from 表名1 inner join 表名2 on 条件
外连接查询
- 左外连接
  - select 字段列表 from 表1 left join 表2 on 条件
- 右外连接
  - select 字段列表 from 表1 right join 表2 on 条件
子查询
- 查询中嵌套查询，称嵌套查询为子查询

事务

事务的概念
- 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
操作
- 开启事务：start transaction
- 回滚：rollback
- 提交：commint

事务四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
持久性：当事务提交或回滚后，数据库会持久化的保存数据
隔离性：多个事务之间。互相独立
一致性：事务操作前后，数据总量不变

事务的隔离级别

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题

存在问题：

脏读：一个事务，读取到另一个事务中没有提交的数据
不可重复(虚读)：在同一个事务中，两次读取到的数据不一样
幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
隔离级别：
- read uncommitted：读未提交
  - 产生的问题：脏读、不可重复读、幻读
- read committed：读已提交
  - 产生的问题：脏读、不可重复读
- repeatable read：可重复读
  - 产生的问题：幻读
- serializable：串行化
  - 解决所有问题
- 注意：隔离级别从小到大安全性越来越高，但效率越来越低

JDBC

DriverManager：驱动管理对象
- 注册驱动
  - Class.forName("com.mysql.jdbc.Driver")
- 获取数据库连接(getConnection)
  - url：jdbc:mysql://IP地址:端口号/数据库名
  - user：数据库用户名
  - password：数据库密码
Connection：数据库连接对象
- 获取执行sql对象
  - Statement createStatement()
  - PreparedStatement prepareStatement(String sql)
- 管理事务
  - 开启事务:setAutoCommint(boolean autoCommit)，调用该方法设置参数为false，即开启事务
  - 提交事务：commit()
  - 回滚事务：rollback()
Statement：执行sql的对象
- 执行sql
  - int executeUpdate(String sql)：执行DML(insert、update、delete)语句
  - ResultSet executeQuery(String sql)：执行DQL(select)语句
ResultSet：结果集对象，封装查询结果
- next()：游标向下移动一行
- getXxx(参数)：获取数据
  - int：代表列的编号，从1开始
  - String：代表列名称
PreparedStatement：预编译执行sql的对象
- 参数使用**?**作为占位符
- 给?赋值
  - 方法：setXxx(参数1，参数2)
    - 参数1：?的位置编号从1开始
    - 参数2：?的值

JDBCUtils

创建配置文件jdbc.properties

url=jdbc:mysql:///数据库名
user=root
password=root
driver=com.mysql.jdbc.Driver

具体代码

public class JdbcUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    //使用静态代码块，随着类的加载而执行，而且只执行一次
    static {
        try {
            //创建Properties集合类
            Properties pro = new Properties();
            //获取src路径下的文件的方式--->ClassLoader 类加载器
            ClassLoader loader = JdbcUtils.class.getClassLoader();
            URL res = loader.getResource("jdbc.properties");
            String path = res.getPath();
            pro.load(new FileReader(path));
            //pro.load(new FileReader("src/jdbc.properties"));
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

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

    //释放资源
    public static void close(Statement stmt, Connection conn) {

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

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

    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

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

数据库连接池

概念：一个容器，存放数据库连接的容器。当系统初始化后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器
标准接口：DataSource
- 方法：
  - 获取连接：getConnection()
  - 归还连接：Connection.close()

C3P0

步骤：
- 导入jar包(三个)：c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar 数据库驱动jar包
- 定义配置文件：
  - 名称：c3p0.properties 或者 c3p0-config.xml
  - 路径：直接将文件放在src目录下
- 创建核心对象：数据库连接池对象
  - ComboPooledDataSource
获取连接：getConnection

Druid

步骤
- 导入jar包(两个)：druid-1.0.9.jar 数据库驱动jar包
- 定义配置文件：
  - 是properties形式的
  - 可以叫任意名称，可以放在任意目录下
- 加载配置文件：Properties
- 通过工厂类来获取数据库连接池对象：
  - DruidDataSourceFactory.createDataSource(数据源)
- 获取连接：getConnection

Spring JDBC

Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发

步骤：

导入jar包
创建JdbcTemplate对象。依赖于数据源DataSource
调用JdbcTemplate的方法来完成CRUD的操作
- update()：执行DML语句。增、删、改
- queryForMap()：查询结果将结果集封装为map集合，
  - 将列名作为key，将值作为value，将这条记录封装为一个map集合
- queryForList()：查询结果将结果集封装为list集合
  - 将每一条记录封装为一个map集合，再将map集合装在到list集合中
- query()：查询结果，将结果封装为JavaBean对象
  - new BeanPropertyRowMapper<泛型类型>(泛型类型.class)
- queryForObject()：查询结果，将结果封装为对象
  - 用于聚合函数的查询