MySQL笔记

2020-10-23 14:49:15 浏览数 (1)

简介

  • 描述事物的符号称为数据
  • 数据库（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
代码语言：javascript复制
url=jdbc:mysql:///数据库名
user=root
password=root
driver=com.mysql.jdbc.Driver
  • 具体代码
代码语言：javascript复制
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()：查询结果，将结果封装为对象
      • 用于聚合函数的查询
数据库 nosql sql 云数据库SQLServer

0 人点赞