MySQL知识点_2024版

MySQL知识点_2024版，MySQL是一个流行的开源关系型数据库管理系统，广泛应用于各种业务场景。

MySQL 参考

• 数据库
  • 概述
    • 数据库（DataBase，DB）：指长期保存在计算机的存储设备(硬盘)上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合. 还是以文件的方式存在服务器的电脑上的。 说白了就是数据的仓库, 用来持久化保存数据的.
  • 常见的关系型数据库
    • MySql ：开源免费的数据库，中小型的数据库，已经被Oracle收购了。MySql6.x版本也开始收费。后来Sun公司收购了MySql，而Sun公司又被Oracle收购 Oracle：收费的大型数据库.Oracle公司的产品.Oracle收购SUN公司，收购MySql. DB2：IBM公司的数据库产品,收费的.银行系统中. SQLServer：MS公司.收费的中型的数据库. SyBase：已经淡出历史舞台.提供了一个非常专业数据建模的工PowerDesigner. SQLite: 嵌入式的小型数据库,应用在手机端.
• SQL
  • 什么是SQL？
    • SQL：Structure Query Language。（结构化查询语言）,通过sql操作数据库(操作数据库,操作表,操作数据) SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准 各数据库厂商(MySql,oracle,sql server)都支持ISO的SQL标准。 各数据库厂商在标准的基础上做了自己的扩展。 各个数据库自己特定的语法
  • 语法
    • 每条语句以分号结尾(命令行里面需要)，如果在navicat,java代码中不是必须加的。 SQL在window中不区分大小写，关键字中认为大写和小写是一样的
  • 分类
    • Data Definition Language (DDL数据定义语言) 如：操作数据库，操作表 Data Manipulation Language(DML数据操纵语言)，如：对表中的记录操作增删改 Data Query Language(DQL 数据查询语言)，如：对表中的记录查询操作 Data Control Language(DCL 数据控制语言)，如：对用户权限的设置
• DDL
  • 操作数据库
    • 创建数据库
      • create database 数据库名 [character set 字符集][collate 校对规则] 注: []意思是可选的意思
    • 查看所有数据库
      • show databases
    • 查看数据库的定义结构
      • show create database 数据库名;
    • 删除数据库
      • show create database 数据库名;
    • 修改数据库
      • alter database 数据库名 character set 字符集;
    • 切换数据库
      • use 数据库名
    • 查看正在使用的数据库名
      • select database()
  • 操作数据表
    • 创建表
      • create table 表名( 字段名 字段类型 [约束], 字段名 字段类型 [约束], ........ 字段名 字段类型 [约束] );
      • 类型
        • 1. 整型 一般使用int 或者bigint 2. 浮点/双精度型 默认的范围 float或者double 指定范围 float(M,D) eg: float(4,2) 表达的范围: -99.99~99.99 3. 字符串 固定长度 char(n) eg: char(20), 最大能存放20个字符. ‘aaa’, 还是占20个字符的空间 可变长度 varchar(n) eg:varchar(20), 最大能存放20个字符. ‘aaa’, 占3个字符的空间 一般使用varchar(n) 节省空间; 如果长度(eg:身份证)是固定的话 可以使用char(n) 性能高一点 4. 关于大文件 一般在数据库里面很少存文件的内容, 一般存文件的路径 一般不使用二进制存, 使用varchar(n)存文件的路径 5. 日期 DATE 只有日期 DATETIME 日期和时间
      • 约束
        • 非空 not null
          • 这个字段不能为空,必须要有数据
        • 主键 primary key
          • 主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为 唯一标识的
        • 唯一 unique
          • :唯一约束, 后面的数据不能和前面重复;
        • auto_increment:
          • 自动增长,必须是设置了primary key之后,才可以使用auto_increment
    • 修改表
      • 增加一列；alter table 表 add 字段 类型 约束； 修改列的类型约束； alter table 表 modify 字段 类型 约束 ； 修改列的名称，类型，约束；alter table 表 change 旧列 新列 类型 约束; 删除一列； alter table 表名 drop 列名； 修改表名 ; rename table 旧表名 to 新表名；
    • 删除表
      • drop table 表名；
• DML
  • 插入
    • 语法
      • insert into 表(列，列..) values(值，值..)； 插入特定的值
      • insert into 表名 values(值,值,值....） 插入所有的列
  • 更新
    • update 表 set 列 =值, 列 =值 [where 条件]
  • 删除
    • delete from 表 [where 条件] 注意: 删除数据用delete,不用truncate
• DQL
  • 分组查询
    • SELECT 字段1,字段2... FROM 表名 [where 条件] GROUP BY 列 [HAVING 条件];
  • 分组后筛选 having
    • group by 列 [having 条件]
  • 限制查询记录
    • select ... from .... limit 起始行数,查询的记录条数.
  • 基本查询语法
    • select [*][列名 ,列名][列名 as 别名 ...] [distinct 字段] from 表名 [where 条件]
  • 查询所有的列的记录
    • select * form 表
  • 查询某张表特定列的记录
    • select 列名,列名,列名... from 表
  • 去重查询
    • SELECT DISTINCT 字段名 FROM 表名; //要数据一模一样才能去重
  • 别名查询
    • select 列名 as 别名 ,列名 from 表 //列别名 as可以不写 select 别名.* from 表 as 别名 //表别名(多表查询, 明天会具体讲)
  • 运算查询
    • select pname ,price+10 from product;
  • 条件查询
    • select ... from 表 where 条件 //取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回
  • between...and... 区间查询
  • like 模糊查询 一般和_或者%一起使用
    • _ 占一位 % 占0或者n位
  • 排序查询
    • SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC]; //ASC: 升序，默认值; DESC: 降序
  • 组合查询
    • SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
  • 聚合函数
    • max(列名) 求这一列的最大值 min(列名) 求这一列的最小值 avg(列名) 求这一列的平均值 count(列名) 统计这一列有多少条记录 sum(列名) 对这一列求总和
  • 连接查询
    • 交叉查询
      • 交叉查询把若干张表(>=2)没有条件的连接在一起,进行展示
      • 交叉查询其实是一种错误.数据大部分是无用数据,叫笛卡尔积.
    • 内连接查询
      • 隐式内连接
        • 隐式里面是没有inner关键字
      • 显示内连接
        • 显示里面是有inner关键字
  • 外连接
    • 左外连接
      • 以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示.
      • 语法
        • select [字段][*] from a left [outer] join b on 条件
    • 右外连接
      • 以join右边的表为主表,展示右边表的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示
      • select 字段 from a right [outer] join b on 条件
  • 子查询
    • 一个查询语句的结果作为另一个查询语句的条件 有查询的嵌套，内部的查询称为子查询 子查询要使用括号---(）
• 外键约束
  • 缺点
    • 缺点:表中出现了很多重复的数据(数据冗余)，如果要修改研发部的地址需要修改3个地方。
  • 外键: 一张从表中的某个字段引用主表中的主键 主表： 约束别人 副表/从表： 使用别人的数据，被别人约束
  • 添加外键
    • 1. 新建表时增加外键： [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) 关键字解释： CONSTRAINT -- 约束关键字 FOREIGN KEY(外键字段名) –- 某个字段作为外键 REFERENCES -- 主表名(主键字段名) 表示参照主表中的某个字段
    • 2. 已有表增加外键： ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
  • 删除外键
    • alter table 表 drop foreign key 外键名称;
• 多表间关系
  • 1. 一对多
    • 一对多建表原则: 在从表(多方的一方)创建1一个字段,字段作为外键指向主表(一方)的主键
  • 2. 多对多
    • 多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
  • 3. 一对一
    • 在实际的开发中应用不多.因为一对一可以创建成一张表。
• 事务
  • 概述
    • 事务指逻辑上的一组操作，组成这组操作的单元要么全部成功，要么全部失败。
  • 语法
    • start transaction;开启事务 commit；提交 rollback；回滚
  • 回滚点
    • 设置回滚点：savepoint 名字; 回到回滚点：rollback to 名字;
  • 特性
    • 原子性（Atomicity）
      • 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要 么都不发生。
    • 一致性（Consistency）
      • 事务前后数据的完整性必须保持一致.
    • 持久性（Durability）
      • 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的， 接下来即使数据库发生故障也不应该对其有任何影响。
  • 隔离的四个级别