MySQL知识点

1、前置知识

1.1 java的版本介绍

  • JavaSE Java Standard Edition
    • Java Java的标准版本,提供了很多的核心API;lang util io 包等
  • javaEE Java enterprise edition
    • java 的企业版本,针对网络应用做出了很多的扩展API , servlet Listener Filter Jsp
  • javaME Java Micro endition
    • java 的微小版本,适合硬件设备的嵌入设备的编程。例如,机顶盒,打卡机等。有用户界面和网络编程,但是整体的性能比较差。
    • 现在机顶盒一般用的是Android

1.2 web开发的常见模式

  • c/s client/server

    • 客户端/服务器模式:
    • 缺点:推广费劲,软件的开发成本高。
    • 优点:客户端可以分担服务器的压力
  • B/s browser/server

    • 浏览器/服务器模式
    • 缺点:对服务器的要求比较高,压力比较大。
    • 优点:开发成本低,开发的周期较短,无序下载额外的内容。

1.3数据的存储方式

  • 运行内存存储:
    • 运行过程中的存储方式:
      • 读写速度块,但是不够持久
  • 磁盘内存存储:
    • 将数据存储到硬盘之中
      • 读写速度慢,但是持久化存储,成本更加低。
    • 能存储的类容
      • 文本文件:无规则,不方便存储
      • excle:有规则,但是他面向的是用户,不能直接使用程序或者语言直接读取文件。
      • 数据库:数据库的内部格式是二维表格,一种有格式的持久话的存储方式。面向的程序,不能直接对其中的数据进行操作,只能通过sql语句进行对数据的操作,更加适合web应用存储数据!

1.4 数据库的分类

  • 数据库的存储方式:

    • 数据库内部格式就是二维表格!一种有格式的持久化存储方式!
    • 数据库面向的不是用户!必须通过SQL语句进行数据库的操作!更加合适web应用存储数据!
  • 数据库的分类:

    • 网状结构数据库:以节点形式存储数据和访问数据
    • 层次结构数据库:IBM[IMS]。定向有序的树状结构实现存储和访问。
    • 关系结构数据库:Oracle、MySQL、DB2、SQL Server,以表格(Table)形式存储,多表之间建立关联关系,通过分类、合并、连接、选取等方式实现访问。
    • 非关系型数据库:MongoDB、Redis,使用哈希表,表中以键值(key-value)的方式实现特定的键和一个指针指向的特定数据
    • ElastecSearch
  • 数据库管理系统(DBMS):指的是一种操作和管理数据库的大型软件,用于建立、使用、维护数据库,对数据库进行统一的管理和控制,保证数据库的安全性和完整性。

    • 数据库文件不能直接操作,如果想要管理数据库文件,就需要使用数据库管理系统(软件 DataBase Management System)
  • 数据库管理系统的分类

    • 关系型数据库的管理系统:Oricle ,MySQL 、SQLLite

    • 非关系型数据库管理系统:Redis MongoDB es

    • 常见的数据库管理系统:

      • Oracle:可以运行在UNIX、Windows等主流操作系统,支持所有的工业标准,并获得了最高级别的ISO标准安全性认证。
      • DB2:IBM公司的,满足中大公司的需要
      • SQL Server:微软退出的。
      • SQLLite:手机端的数据库
      • Mysql:免费、适合中小型企业
  • 关系型数据库的常见存储单位

    • 库:最大的数据库的容器单位,内部包含表。
    • 表:一类数据库的集合,对应的是Java的类。
    • 行(元组):一个数据实体,对应的是Java的具体对象。
    • 列(域):真正用于存储数据属性的(类型)!对应的是Java的具体对象的属性。
    • 注:在创建库表的时候,应该根据对应的类创建对应 的库表,类的属性要与表的列值完全一致。

2、sql语句的介绍

  • sql概念:结构化查询语言,用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。

    • 各数据库厂商都支持ISO的SQL标准。普通话
    • 各数据库厂商在标准的基础上做了自己的扩展。方言
    • 经验之谈:通常对数据库的操作为C(create) R(Read) U(update) D(delete) 。即增删改查。
  • SQL语句的分类:

    • 数据定义语言DDL:create, drop,alter (针对的是数据库以及数据库中的表)
      • create ( 创建) drop ( 删除) alter (修改)
    • 数据查询语言DQL: select (针对的是 关于表中数据的操作)
    • 数据操作语言DML:update 、delete 、inset (针对的是表中的数据)
      • update(修改) 、delete(删除) 、inset(插入)
    • 事务处理语言TPL:commit rollback(用于操作数据库的事务 的语言)
    • 数据控制语言DCL:grant 、revoke
  • 基本命令:

    • 服务器的默认IP地址是本机 的IP地址:127.0.0.1
    • 数据库的默认端口号:3306
    • 以上两个在登录的时候通常是不写的
    #1、开启和关闭服务:
    net start/stop mysql57(服务器的名称,我的是MySQL57);
    #2、登录连接MySQL
    mysql -h 服务器的IP地址 -u用户名 -p密码 -P端口号
    #3、退出MySQL
    exit
    

3、sql语句的使用

3.1DDL 操作数据库以及表格 create, drop,alter

3.1.1操作数据库:
  1. 添加一个新的数据库:
    • create database 数据库的名称
    • create database 数据库的名称 character set 编码格式
  2. 修改数据库
    • alter database 数据库的名字 character set 编码格式
  3. 查看数据库
    • show databases (查看所有的数据库)
    • select database() (查看当前使用的数据库)
  4. 切换数据库
    • use 数据库的名称
  5. 删除数据库
    • drop database 数据库的名称
3.1.2操作数据库表
  1. 创建数据库表格

    • create table 表名(

      列名 类型 约束 comment “列的含义” , //列与列之间用 ,隔开

      列名 类型 约束 comment “列的含义” //约束以及comment可以不用写。

      )charset= utf8 ;

  2. 修改数据库表格 都是关于修改的 关键字(alter)

    • 表中添加列

      • alter table 表名 add 列名 类型
    • 表中删除列

      • alter table 表名 drop 列名
    • 修改
      • 修改列名

        alter table 表名 change 原列名 新的列名 新的类型 (不推荐修改列 的类型,因为原来的表中如果存在数据,那么修改数据类型容易造成错误)

      • 修改列的类型

        alter table 表名 modify 列名 新的数据类型

      • 修改表名

        alter table 旧的表名 rename 新的表名

  3. 查看数据库表

    • desc
      • desc 表名 查看当前的表中包含的列
    • show
      • show tables 查看当前的库中包含哪些表
  4. 删除数据库表

    • drop table 表名
3.1.3数据库中的常用数据类型
  • 数字类型
    • int
    • double(M, D)(多少位,小调点后面的位数) 注意有效15位!
    • decimal(M , D )(多少位,小调点后面的位数)注意有效25位!
  • 字符类型
    • char(0-255)
    • varchar(0-65535)
  • 时间类型
    • date 年-月-日

    • time 时:分:秒

    • datetime 年-月-日 时:分:秒

    • timestamp 自动时间赋值 年-月-日 时:分:秒 时间戳

      时间格式就是字符串!真正开发存储时间字符串存时间戳

3.2DML 操作的是数据 insert 、update 、 delete

  • 插入数据

    • insert into 表名 (列名 , 列名,…) value/values (值,值,值),(值,值,值);
      • value 一行数据
      • values 多行数据
      • 列名 — 值 需要一 一对应(位置,类型)
    • insert into 表名 value/values(值,值,值),(值,值,值);
      • 全部插入!顺序按照之前创建表的列顺序!
  • 修改数据

    • update 表名 set 列 = 新值 ,列 = 新值; 全表修改
    • update 表名 set 列 = 新值 ,列 = 新值 where 列 = 值;
  • 删除数据

    • delete from 表名; 全表删除,但是不会改变他的结构格式,例如自增的不会清零
    • delete from 表名 where 条件; 条件删除
    • **truncate table 表名; **
      • 该删除分为两个步骤:
      • 删除表格
      • 创建一个和他结构一样的表,他的一切都是新的

3.3DQL

3.3.1DQL查询介绍
  1. 查询的基础介绍

    • 介绍:数据库执行DQL语句不会对数据进行改变,而是让数据库发送一个结果集给客户端 ,不会影响原来的数据,返回 的是一张虚拟的表格。
    • 执行的步骤:查询时,会在内存中生成一张临时的数据表,将满足要求的数据进行记录
      • 首先,光标会移动到首行
      • 进行判断,看数据是否匹配
      • 若是不满足要求,则跳转下一行
      • 将所有满足条件的数据返回到客户端
  2. DQL的基础语法

    • select * from 表名 ; 查询全表
    • 关键字:
      • where 条件 ;
      • group by 列名; 对结果按照列名进行分组
      • order by 列名 ; 对结果进行排序 asc(升序,默认) / desc(降序)
      • having 条件 ;
      • limit 开始行数 ,查询的行数;
  3. 基本查询(全表查询)

    • 查询所有列 : select * from 表名
    • 查询指定的列 : select 列名 from 表名 ;
  4. 条件查询

    • 条件查询的基础语法:select * from 表名 where 条件

    • 逻辑符号:

      • = 、!= 、<>(这是不等于)、 < 、 >、 <=、 >=
      • between 值 and 值 ; 两值之间的所有
      • in ( set ) ; 取是set的值,set值可以是多个
      • is null ; 是null
      • and
      • or
      • not ;注:is not null 不是空,不能是not is null
    • 注意:null 不能用 = 进行比较 ,null代表的是未知的数,不一定是字符null 。 在orical 中,只要是空的地方,会自己填入null

      SELECT * FROM stu WHERE sid <> 's_1001' AND sid <> 's_1002' AND sid <> 's_1003' ;
      SELECT * FROM stu WHERE sid NOT IN ('s_1001' ,'s_1002' ,'s_1003') ;
      SELECT * FROM stu WHERE sid NOT BETWEEN 's_1001' AND 's_1003';
      
  5. 模糊查询

    • 关键字:like
    • 关键的占位符:**%**代表可变的长度 _ 代表单个
    #例如张姓开头的所有人
    select * from stu where s_name like  '张%' ;
    #例如姓名中含有   蒯   的所有人
    select * from stu where s_name like '%蒯%' ;
    
  6. 分支查询

    • 关键字

      case

      when 条件 then 值

      when 条件 then 值

      else 值

      end as 别名

    • 注意:分支查询也是查询,在 from前,即 分支查询写在列的位置!可以添加原数据表中不存在的数据!也是开发中比较常用的查询手段!

  7. 时间查询

    • 常用的函数:

      • sysdate(); 当前系统时间(日、月、年、时、分、秒)now() 函数也是获取当前系统的时间
      • curdate(); 获取当前日期
      • curtime(); 获取当前时间
      • week(date); 获取指定日期为一年中的第几周
      • year(date) ; 获取指定日期的年份
      • hour(Time); 获取指定时间的小时值
      • minute(Time); 获取时间的分钟值
      • datediff(date1,date2) ; 获取DATE1 和 DATE2 之间相隔的天数 ,该方法比较重要
      • adddate(date , n) ; 计算DATE 加上 N 天后的日期
    • 时间函数的使用:

      • 他放在列的位置,没有后续的追加,直接输出时间

      • 也可以作为 值

      • 亦可以 作为 条件

    #做为值进行逻辑运算
    SELECT ename , 
    ROUND(( DATEDIFF(NOW() ,hiredate ) / 365),1) AS workage FROM emp;
    #作为条件进行查询
    select * from 表名 where 列名 = 时间函数
    
    • 数学函数:
      • ceil() ; 向上取整
      • floor() ; 向下取整
      • round() ; 四舍五入
  8. 聚合函数

    • sum() ;

    • avg() ;

    • max() ;

    • min () ;

    • count() ; 统计总的行数 ,计数时 null 值不计入

    • mod( 要取余的数 , 取余的数) ; 取余

      mod(6 , 4)    结果为2
      
  9. 字段控制

    • 关键字:

      • distinct ;去重

      • ifnull ( 列的名字 , 如果为null时的值) ;

        数字类型+null = null ,因此有的需要去掉null值

      • as 列的别名 ; 取别名

        也可以用空格代表as

    • 字段控制根据字面理解,用在对数据进行操作的时候

  10. 排序查询

    • 排序的关键字 order by 列名 asc/ desc ;

      • asc 升序 默认为升序
      • desc 降序
    • 排序不依托于where,他和where的顺序为:

      where在前,order by 在后。

      比如在一个班级中,需要女生进行排队。

  11. 分组查询

    • 关键字:group by 列名 ,列名 … ;

    • 语法:select 分组列,聚合函数 from 表名 group by 列名;

    • 作用:根据给定的条件,将数据分组。

    • 顺序:

      where 条件 group by 列名 order by 列名 asc/desc

  12. 分组条件查询

    • 分组后的筛选,对数据 进行过滤。
    • 关键字:having 聚合函数
    • 语法:group by 列 having 聚合函数 逻辑运算符 值;
  13. 限定查询

    • 关键字:LIMIT 起始行,查询行数()

    • 分页查询的基础

      数据库中的数据是从0开始的

  14. 查询的总结

    • 查询中各个语句的顺序

      SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列(asc|desc)LIMIT 起始行,总条数

3.3.2约束限制
  1. 实体约束

    • 主键:主键就是唯一并且不为null的列

      • 关键字:primary key
    • 主键的分类:

      • 自然主键:本身实体具备的属性,例如身份证号。
      • 自定义主键:专门为了主键而创建,为了区分数据
    • 主键的使用

      • 主键约束

        create table 表名(

        主键名 int primary key

        )

      • 联合约束

        create table 表名(

        列名 int ,

        列名 int,

        primary key (列名,列名)

      • 联合约束也可以只写一个列,作用和主键约束一样

  2. 唯一约束

    • 概念:唯一(即不可以重复)但是可以为null的列

    • 关键字:unique

  • 和主键的区别:唯一约束可以为null
  1. 自增长

    • 自动增长,给主键数值列添加自动增长。从 1 开始,每次加 1。不能单独使用,必须和主键配合
  • 关键字:auto_increment
  • 当主键是字符串类型时 varchar(64) primary key uuid
    • UUID 是 通用唯一识别码(Universally Unique Identifier)的缩写,是一种软件建构的标准,亦为开放软件基金会开放软件基金会/1223731)组织在分布式计算分布式计算/85448)环境领域的一部分。其目的,是让分布式系统中的所有元素,都能有唯一的辨识信息,而不需要通过中央控制端来做辨识信息的指定。如此一来,每个人都可以创建不与其它人冲突的UUID。在这样的情况下,就不需考虑数据库创建时的名称重复问题。
  1. 域约束

    • 非空约束
    • 列名 类型 not null unique
    • 默认值约束
      • 列名 类型 default 'xxx’
    • 不为负数约束
      • unsigned 默认数字类型有负数!加上此约束没有负数部分!
  2. 参照引用完整性约束(主外键约束)

    • 主键在另外的表中作为外键,外键可以重复

      • 主外键:

        在引用的表中添加一列,这一列是被引用的主键(主键可以代表一行数据,即一个实例)引用的列,叫外键。

      • 主外键约束:外键的内容不能超过主键的值

    • 的添加主外键约束

      • 直接添加:

        constraint 约束名称(一个库内不能重复) foreign key (外键的列名) references 主表的表名(主键的列名)

      • 外部修改:

        alter table 子表名 add constraint 约束名称(一个库内不能重复) foreign key(外键) references 主表(主键)

      • 添加约束的表:

        • 被引用的表称为主表,引用表称为子表

        • 创建的时候需要先创建主表,再创建子表,然后再添加约束,添加约束的时候,添加在子表中。

        • 如果想强硬删除主表:需要设置外键失效!针对的是整个数据库。

          设置失效 SET FOREIGN_KEY_CHECKS = 0 ,设置生效 SET FOREIGN_KEY_CHECKS = 1

3.3.3 多表查询
  1. 表的关系

    • 一对一
      • 有主外键,有子表、主表,必须添加主外键的约束,
      • 需要给子表的外键添加唯一约束(unique)或者 把外键当作子表主键使用
    • 一对多
      • 有主外键,有子表、主表,必须添加主外键的约束,
      • 一对一,一对多 都是先创建主表,再创建子表,添加约束条件,但是一对多的时候,外键可以重复,添加一个非空约束。
    • 多对多
      • 没有主表和子表的区分,但是需要创建一个中间表,用来联系两个表,中间表最少有三列,一个中间表主键,两个外键,外键最好添加非空约束。
  2. 纵向查询

    • 将多个结果集垂直拼接

    • 不要求有主外键的约束,但是列数以及列的类型需要一样

    • 关键字:union /union all 前者会自动去重,后者不会

    • 语法:

      SELECT * FROM jiaoxue
      UNION ALL
      SELECT * FROM xingzheng
      UNION ALL
      SELECT * FROM caiwu;
      
  3. 横向查询

    • 将多个结果集水平拼接到一起!拼接的实体之间需要‘互相认领’
    • 必须存在主外键关系 , 多对多的类型需要联合中间表进行查询
    • 语法:
      • 99查询 ( mysql 独有):from 表1,表2…
      • 内连接 : 表1 [inner] join 表2 on 主 = 外
      • 外连接
        • 左外连接:表1 left [outer] join 表2 on
        • 右外连接:表1 right [outer] join 表2 on
  4. 自然连接(了解就行)

    • 因为自然连接自动匹配‘主外键相等’ 匹配的原则两个表同名的列!

      我们不能保证只有主外键名称一致!

    • 自然内连接 表1 natural join 表2

    • 自然外连接

      • 自然左外连接 表1 natural left join 表2
      • 自然右外连接 表1 natural right join 表2
  5. 子查询

    • 将一个查询的结果集作为另一个查询的条件或者虚拟表

    • 语法:

      单行单列 :可以在 WHERE 的条件中作比较

      单行多列 : WHERE 列名 IN (set) ; 因为有多个数据

      多行单列 WHERE ANY ALL () --> 单行单列 MAX MIN

      多行多列 FROM 虚拟表 FROM (SELECT * FROM xx) 别名

  6. 自连接

    • 所谓的自连接!就是需要查询的数据在一个表中!需要同一个表在一次查询中使用多次!这就用到自己连接自己!这就是所谓的自连接!
  7. 多表查询总结

    • 内连接和99查询 查询的方式和结果一致,只是形式不同,两个相互连接!on判断主外键!如果再有表再join即可!以此类推!

    • 内连接两个表是平等的!查询的结果根据两个表的数据决定!

      外连接可以通过left/right指明逻辑上的主表!当逻辑主表的数据在另一个表不存在的时候!结果也会显示主表的全部数据!

      小技巧:开发中一旦使用了外连接!整条sql语句的方向是一致的!

    • 查询的总结:

      • 了解表的结构

      • 分析需求

      • 提取要查询的列

      • 分析查询的条件

      • 常见情况:

        • 结果 ——> 一张表

          条件 – > 一张表 单表查询

        • 结果 ——> 一张表

          条件 – > 多张表 子查询

        • 结果 ——> 多张表

          条件 – > 一张表/多张表 /没有条件 连接查询

        • 结果 ——> 多张表

          条件 – > 已有表中没有的数据 连接查询+子查询

3.4TPL

  1. 事务 :

    • 概念: 事务就是数据库中的一系列操作的动作的集合,事务有着高度的一致性。成功需要事务中的每一条数据都成功,而只要任何一条数据失败,则整个事务都失败。成功则会改变数据,若失败,则回滚到事务开始之前的状态,不会改变数据库的数据;
  2. 事务的类型:

    • 自动提交

      • mysql自动默认的有开始事务以及自动提交;

      • MySQL默认就是自动提交事务!每句SQL语句前后都会嵌套事务!

        自动开启!自动提交!报错自动回滚!

    • 显示提交:

      • 非自动提交!手动的开启一个事务,加入数据库操作动作(sql),手动提交或者回滚
      • 取消自动提交 每次执行完操作动作序列以后,手动提交或者回滚!
  3. 操作命令:

    • 查看自动提交的
      • show variables like ‘%autocommit%’
    • 关闭自动提交、
      • set autocommit = off ;
      • 执行事务的sql语句
      • commit 或者 rollback
    • 手动开启一个事务
      • start transaction ;
      • 执行sql语句
      • commit 或者 rollback
  4. 事务的作用:

    • 事务提供了一个在失败情况下数据回滚的机会!当一个业务的一系列动作中有一个失败!整个事务状态失败!调用回滚!将数据回滚到开启事务之前的状态!保证数据不会出错!保证数据安全!
    • 事务之间有隔离性!保证并发事务操作数据库同一个表的时候!互相不受干扰!
3.4.2事务的ACID
  • Atomicity(原子性) 一个事务中的一系列动作是不可切割的!
  • Consistency(一致性) 事务开启和事务结束的前后数据总状态一致!
  • Isolation(隔离性) 事务和事务之间互相隔离!互不干扰!注意:只有并发情况才会考虑隔离性!
  • Durability(持久性) 事务一旦提交或者回滚!数据持久化改变!
3.4.3事务的隔离性
  • 事务的隔离级别

    1. READ UNCOMMITTED: 赃读. 重复读、虚读都有可能发生。
    2. READ COMMITTED: 避免赃读。重复读、虚读有可能发生。(oracle默认的)
    3. REPEATABLE READ:避免赃读、重复读。虚读有可能发生。(mysql默认)
    4. SERIALIZABLE: 避免赃读、不可重复读、虚读。
  • 事务的隔离级别操作命令

    1. 查看隔离级别 select @@tx_isolation;
    2. 修改隔离级别 set transaction isolation level 四个级别
  • 事务的隔离级别造成的影响

    1. 脏读:一个事务读取了另一个事务没有提交的数据! - READ UNCOMMITTED会发生脏读!
    2. ​ 不可重复读:一个事务读取了另一个事务提交的修改数据!- READ COMMITTED或者以及下级别会发生该问题!提升到REPEATABLE READ或者以上级别会避免问题!
    3. 虚读(幻读):一个事务读取了另一个事务已提交的插入数据!REPEATABLE READ或者一下级别会发生问题!提升到SERIALIZABLE以上级别可以避免!
  • 总结:只有脏读是数据错误!不可重复读和虚读属于不符合事务的一致性,但是事务已经提交事务!数据确实持久化改变!不可重复读和虚读不算错误!可以不用避免!

    • 隔离级别越高!数据越安全!性能越差!
    • 建议使用第二个隔离级别!

3.5备份以及恢复

  • 数据备份

    ​ mysqldump -u用户名 -p密码 要备份的数据库>磁盘位置 .sql

  • 数据恢复

     mysql -u用户名 -p密码 要恢复的数据库<磁盘位置 .sql
    

3.6 DCL

  • 介绍:维护账号和账号权限管理!

  • 创建用户

    CREATE USER zhangsan IDENTIFIED BY ‘123’;

  • 用户赋权

    1. 授权: grant 权限(all,select) on 库名.表名(*全部) to ‘用户名’
    2. 查看权限:show grants for ‘账号’
  • 用户权限撤销

  1. 撤销权限:revoke 权限 on 库.表 from ‘用户名’;
  • 删除用户

drop user ‘用户名’

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值