MySQL笔记

简介

• 描述事物的符号称为数据
• 数据库（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 提供了多种数值型数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大

QQ截图20200825182338.jpg

小数类型

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

QQ截图20200825182350.jpg

日期和时间类型

QQ截图20200825182358.jpg

==字符串类型==

QQ截图20200825182405.jpg

基本操作

操作数据库(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()：查询结果，将结果封装为对象
    • 用于聚合函数的查询