MySQL数据库-基础知识

一、数据库

• 数据库：即存储数据的仓库。

• 数据库类型：(主流)

  • 关系型数据库：【类似于"表格"】
    • 例如：MySQL、Orcale、SQL Server等
  • 非关系型数据库：【简化了数据库的结构，类似于键值对key-value】
    • 例如：NoSQL

• 数据库使用排行榜

  • https://db-engines.com/en/ranking

• 数据库服务器的层级关系

  • 一个数据库服务器中，有多个数据库；
  • 一个数据库中，有多个数据表；
  • 一张数据表中，有多个字段(即行/列)；
  • 字段中保存的才是数据。

二、SQL语言

• SQL语言：

  • (Structured Query Language)结构化查询语言，简称SQL语言。

• SQL用途：

  • 用于关系型数据库的查询、更新、管理的一种语言【即通过sql语言，可使数据库明白我们想要做什么操作。】
    • 目前所有关系型数据库(例如Oracle、MySQL、SQL server等)都支持使用SQL语言。(即可以通过SQL语言操作Oracle、MySQL、SQL server等所有关系型数据库)。

• SQL分类：

  • DDL：数据定义语言

    • create：创建数据库、数据表
    • alter：修改数据表的结构
    • drop：删除数据库、数据表

  • DML：数据操纵语言

    • insert：向数据表插入数据
    • delete：删除表中数据
    • update：修改表中数据

  • DQL：数据查询语言

    • select：查询语句

  • DCL：数据控制语言

    • grant：分配权限给用户
    • revoke：废除数据库中某用户的权限
    • rollback：取消对数据库中数据进行的变更(回滚）
    • commit：确认对数据库中数据进行的变更

三、MySQL

• MySQL定义

  • 一种开源的关系型数据库管理系统DBMS
  • 本质：使关系型数据库具体化的一种软件(现被Oracle公司收购)。

• MySQL用途

  • 使用SQL语言来操作、管理关系型数据库。

四、SQL语言与MySQL的关系

• 目前，所有关系型数据库(例如Oracle、MySQL、SQL server等)都支持使用SQL语言。

• 补充：

  • 数据库本质上就是一个理论概念，指用于存储大量数据的仓库。
  • 在数据库这种理论概念下，产生了具体的各种数据库管理软件(Oracle、MySQL等)。
  • 对于关系型数据库而言，SQL语言属于关系型数据库操作的通用语言，而MySQL是属于关系型数据库的一种具体化实现的软件而已。

五、数据库中的数据类型

• 数字型：

  • 整形：int
  • 浮点型：float

• 字符串型：

  • char()
  • varchar(可变长度)

• 时间日期型：

  • date：年月日
  • datetime：年月日时分秒
  • timestamp：时间戳

六、MySQL数据库的连接

1、通过命令行模式连接MySQL数据库

• MySQL的连接

  • mysql -h本机(或远程)IP地址 -u用户名 -p密码

• MySQL的退出

  • exit
  • quit
  • \q
• 特点：只能以命令行的方式开发、管理数据库。

2、通过DataGrip连接到MySQL数据库

• DataGrip：

  • 是一个关系数据库集成开发环境。
  • 支持多种关系型数据库系统(例如MySQL、Microsoft SQL Server、Oracle等)。
  • 为开发人员、数据库管理员提供一个统一界面，用于管理和开发各种关系型数据库。

• 特点：

  • 可使用图形化界面的方式来开发、管理数据库。
  • (即可通过鼠标的点击操作来开发、管理数据库。)

七、MySQL数据库操作

1、MySQL数据库-表的操作

• <1、数据库

  • 创建数据库

    • create database 数据库名；
    • create database if not exists 数据库名 charset utf8;
      • if not exists：(可省略)若待创建的数据库不存在，则创建。
      • charset utf8：(可省略)设置编码为utf8(默认)

  • 删除数据库

    • drop database 数据库名；

• <2、数据表

  • 创建表

    • 注意：创建表之前，需要进入某个数据库(use 数据库名)，否则将报错。
    • use 数据库名 # 进入到某个数据库
    • create table 表名(
      • 字段名1 字段类型，
      • 字段名2 字段类型，
      • ......
      • 字段名x 字段类型)；

  • 删除表

    • drop table 表名；

  • 修改表名

    • rename table 旧表名 to 新表名；
    • 随后刷新即可

  • 修改表结构：alter

    • <1、向表中添加1列字段
      • alter table 表名 add 字段名(列名) 字段类型；
        • 例如：向user表中增加一个class字段
        • alter table user add class varchar(10);
    • <2、向表中删除1列字段
      • alter table 表名 drop 字段名；
        • 例如：删除user表中的class字段
        • alter table user drop class;
    • <3、修改表中的字段名
      • change：即可修改字段名，也可修改字段类型
        • alter table 表名 change 旧字段名 新字段名 新字段类型；
      • modify：仅能修改字段类型
        • alter table 表名 modify 旧字段名 新字段类型；

2、MySQL数据库-表数据的操作

• <1、插入表中记录：insert

  • 一次性插入一条记录
    • insert into 表名(字段名1，字段名2，...) value(值1，值2，...);
  • 一次性插入多条记录
    • insert into 表名(字段名1，字段名2，...) value(值1，值2，...)，(值x，值y，...)，...(值a，值b，...);
  • 注意: 如果插入的记录是为表中所有字段,则可省略为以下形式
    • insert into 表名 value(值1，值2，...);

• <2、修改表中记录：update

  • 修改部分记录:
    • update 表名 set 字段名=值 wehre 条件;
  • 修改所有记录(慎用):
    • update 表名 set 字段名=值;

• <3、删除表中记录：delete

  • 删除部分记录:
    • delete from 表名 where 条件;
  • 删除所有记录：
    • delete from 表名;
    • truncate [table] 表名; # table 可以省略

• 补充：delete和truncate区别

  • 共同点: 都能删除所有数据
  • 不同点：
    • delete方式删除所有数据,但是自增顺序会保留(即再次插入数据,继续自增)
    • truncate方式删除所有数据,自增顺序也会被重置(再次插入数据,从1开始重新自增)

3、MySQL数据库-SQL约束

• <1、主键约束:primary key

  • 特点：
    • 非空、唯一
    • (即限制主键字段的数据不能为空、且不能重复)

• <2、自增约束:auto_increment

  • 特点：每次自动加1

• <3、非空约束：not null

  • 特点：约束字段对应的数据不能为空

• <4、唯一约束：unique

  • 特点：约束字段对应数据不能重复，但允许存在空值

• <5、默认约束：default

  • 特点：可提前为某字段设置默认值
  • 即当插入数据不指定该字段值时,自动使用默认值。

• <6、外键约束：foreign key

  • 特点：约束的是两张表的关系
    • 限制主表的删除
    • 限制从表的添加
  • [constraint 外键名称]  FOREIGN  KEY(外键)  references  主表名(主键)
  • constraint 外键名称：给外键起别名
• 补充：
  • 主键约束(primary key)是唯一约束(unique)和非空约束(not null)的集合体。

MySQL数据库-查询

1、基础查询

• <1、简单查询：

  • select  [distinct]  字段名  from  表名;
  • distinct关键字：去重

• <2、条件查询：where

  • select 字段名 from 表名 where 条件;

  • 范围查询：

    • between x and y: x到y的连续范围
    • in(x,y,z): x或y或z

  • 模糊查询：like

    • %：表示0个或者多个字符
    • _: 表示1个字符
    • 例如：查询商品名称中包含'旺旺'的商品信息
    • select * from 表名 where name like '%旺旺%';

  • 空判断:

    • is null: 判断为空
    • is not null: 判断不为空

• <3、排序查询：order by

  • select 字段名 from 表名 order by 待排序字段名 asc|desc；

  • 排序规则

    • asc: 升序 (默认升序：即order by后不写排序规则默认就是升序）
    • desc: 降序
  • 例如： 查询所有商品信息,并且按照价格升序price排序
    • select * from 表名 order by price; # 默认升序

• <4、聚合查询：

  • select 聚合函数 from 表名;

  • 聚合函数：

    • count()：求行数/个数
    • sum()：求和
    • avg()：求平均值
    • max()：求最大值
    • min()：求最小值
    • round(数据,保留位数)：设置小数点后保留几位
  • 例如：查询product表中所有商品的个数
    • select count(*) from product;

• <5、分组查询：group by

  • select 分组字段名,聚合函数(字段名) from 表名 group by 分组字段名;
  • 例如：查询每个商品分类内商品的个数
    • select category_id,count(*) from product
    • group by category_id;
  • 例如：查询每个商品分类内商品的最大价格
    • select category_id,max(price) from product
    • group by category_id;
  • 例如：查询每个商品分类内商品的最大价格，但无商品类别的不统计。
    • select category_id,max(price) from product
    • where category_id is not null
    • group by category_id;
    • 【先执行where条件后，在group by分组。】
  • 例如：查询每个商品分类内商品的最大价格，但无商品类别的不统计、且只显示分组价格大于2000的分组信息。
    • select category_id,max(price) from product
    • where category_id is not null
    • group by category_id
    • having max(price) > 2000;

• <6、分页查询：limit

  • select 字段名 from 表名 limit x,y;
    • x: 整数,代表查询的起始索引【索引默认从0开始】
    • y: 整数,代表查询的条数(即每页展示的数量)
  • 例如：查询表中前五条数据
    • 方式一：select * from 表名 limit 0,5;
    • 方式二：select * from 表名 limit 5;
  • 区分：
    • limit 0,5：指查询从第1条数据开始的5条数据
    • limit 1,5：指查询从第2条数据开始的5条数据
  • 例如：数据共有21条，每页显示7条,依次查询出每页展示的数据
    • 查询第1页数据
      • select * from product limit 0,7;
    • 查询第2页数据
      • select * from product limit 7,7;
    • 查询第3页数据
      • select * from product limit 14,7

• <7、topN问题

  • topN问题即：排序order by + 分页查询limit
  • 例如：查询商品价格price最低的3件商品
    • select * from 表名 order by price limit 3;
    • # 先升序排序，在取前3。
  • 例如：查询商品价格最高的3件商品
    • select * from 表名 order by price desc limit 3;
    • # 先降序排序，在取前3。

2、单表查询

• 单表查询：即仅在一个表中进行查询操作
• 注意：上述的查询，都为单表查询。

3、多表查询

多表查询：仅用一句sql代码来对多个表进行查询操作

3.1、内连接

• 内连接关键字：inner join ... on
  • 注意:inner可以省略
• 书写格式：
  • select 字段名 from 左表表名 inner join 右表表名 on 左右表关联条件;
• 特点：只查询显示两个表中有交集的数据,其他数据直接过滤。
• 例如：查询有对应功夫的英雄,要求展示英雄名hname和他的招式名kname
  • select hname,kname from 表1 inner join 表2 on 表1.kid = 表2.kid;

3.2、外连接

• 外连接：
  • 左外连接：left outer join ... on
  • 右外连接：right outer join ... on
  • 注意：outer可省略
• 左外连接：
  • select 字段名 from 左表 left join 右表 on 左右表关联条件;
  • 特点：以左表为主，左表所有数据都保留，右表只保留交集的部分(右表其余未对应上的显示空)
• 右外连接：
  • select 字段名 from 左表 right join 右表 on 左右表关联条件;
  • 特点：以右表为主,右表所有数据都保留,左表只保留交集的部分(左表其余未对应上的显示空)
• 例如：查询所有英雄对应的招式名，注意:没有对应招式名的用null补全
  • select hname,kname from 英雄表1 left join 招式表2 on 英雄表1.kid = 招式表2.kid;
• 例如：查询所有招式名,注意:没有对应英雄名的用null补全
  • 方法一：
    • select hname,kname from 英雄表1 right join 招式表2 on 英雄表1.kid = 招式表2.kid;
  • 方法二：
    • select hname,kname from 招式表2 left join 英雄表1 on 英雄表1.kid = 招式表2.kid;

3.3、子查询

• 子查询：
  • 将一个完整的select语句作为另外一个select语句的条件使用
  • 这个完整的select语句就是子查询语句
• 特点：子查询语句嵌套在主查询语句中
• 例如：查询化妆品分类(分类表)下的所有商品详情(商品表)【其中：商品的类别id是分类表的分类id的外键foreign key】
  • select * from 商品表
  • where 商品类别id =（select 分类id from 分类表 where 分类名称='化妆品'）；

3.4、自连接

• (了解即可)
• 自连接：将一个表与其自身进行连接。
• 特点：自连接语法和内、外连接的语法一样，只不过左表和右表是同一张表,仅起了不同的别名。
• 注意：使用自连接时需为表指定多个别名，且对所有列的引用均要用别名区分。

八、MySQL存储引擎

• 存储引擎：

  • 即如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法
  • 针对不同的存储需求选择最合适的存储引擎
    • (即可以根据应用的需求选择如何存储、是否索引，是否使用事务等，根据业务环境去配置适合自己的存储引擎)

• 查询MySQL支持的存储引擎：

  • show engines；

• 常见的MySQL存储引擎

  • MyISAM：
    • 特点：
      • 不支持外键约束、事务、行级锁的功能。
      • 插入数据快，空间和内存使用比较低。
    • 适用于：
      • 主要是用于插入新记录和读出记录(则选择MyISAM能实现处理高效率)。
      • 对完整性、并发性要求比较低的应用。
  • InnoDB：【MySQL5.5以后默认使用InnoDB存储引擎】
    • 特点：
      • 支持外键约束、事务、行级锁的功能。
    • 注意：
      • MySQL支持外键的存储引擎只有InnoDB。
    • 适用于：
      • 对事务的完整性要求比较高(E.G、银行)
      • 要求实现并发控制(E.G、售票)
      • 需要频繁的更新、删除操作的数据库
  • MEMORY：
    • 特点：
      • 所有的数据都在内存中，数据的处理速度快；但安全性不高。
      • (将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。)
    • 适用于：
      • 需要很快的读写速度，对数据的安全性要求较低，且数据表不大的场景。
  • archive：
    • 特点：仅支持select、insert语句；不支持索引。
    • 适用于：日志记录和聚合分析方面。