[大数据]MySql

DDL--操作数据库?

1、查询

SHOW DATABASES;

2、创建

• 创建数据库
  • CREATE DATABASE 数据库名称;
• 创建数据库（判断，如果不存在则创建）
  • CREATE DATABASE IF NOT EXISTS 数据库名称;

3、删除

• 删除数据库
  • DROP DATABASE 数据库名称;
• 删除数据库（判断，如果存在则删除）
  • DROP DATABASE IF EXISTS 数据库名称;

4、使用数据库

• 查看当前使用的数据库
  • SELECT DATABASE();
• 使用数据库
  • USE 数据库名称;

DDL--操作表

创建（Create）

????????CREATE TABLE 表名(

? ? ? ? ????????字段名1 数据类型1,

????????????????字段名2 数据类型2,

? ? ? ? ? ? ? ? ......

????????????????字段名n 数据类型n,

? ? ? ? );

查询（Retrieve）

• 查询当前数据库下所有表名称
  • SHOW TABLES;
• 查询表结构
  • DESC 表名称;

修改（Update）

• 1、修改表名
  • ALTER TABLE 表名 RENAME TO 新的表名;
• 2、添加一列
  • ALTER TABLE 表名 ADD 数据类型;
• 3、修改数据类型
  • ALTER TABLE 表名 MODIFY 列名 新数据类型;
• 4、修改列名和数据类型
  • ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
• 5、删除列
  • ALTER TABLE 表名 DROP 列名;

删除（Delete）

• 删除表
  • DROP TABLE 表名;
• 删除表时判断表是否存在
  • DROP TABLE IF EXISTS 表名;

DML

添加（insert）

• 1、给指定列添加数据
  • INSERT INTO 表名（列名1，列名2，...） VALUES（值1，值2，...);
• 2、给全部列添加元素
  • INSERT INTO 表名 VALUES(值1，值2，...);
• 3、批量添加数据
  • INSERT INTO 表名(列名1，列名2，...) VALUES(值1，值2，...),(值1，值2，...),(值1，值2，...)...;
  • INSERT INTO 表名 VALUES(值1，值2，...),(值1，值2，...),(值1，值2，...)...;

修改（update）

• 修改表数据
  • UPDATE 表名 SET 列名1=值1，列名2=值2，...[WHERE 条件];
• 注意：如果update语句没有加where条件，则会将表中所有数据全部修改

删除（delete）

• 删除数据
  • DELETE FROM 表名 [WHERE 条件];
• 注意：删除语句中如果不加条件，则将所有数据全部删除

DQL

查询语法：

? ? ? ? SELECT

? ? ? ? ? ? ? ? 字段列表

? ? ? ? FROM

? ? ? ? ? ? ? ? 条件列表

? ? ? ? GROUP BY

? ? ? ? ? ? ? ? 分组字段

? ? ? ? HAVING

? ? ? ? ? ? ? ? 分组后条件

? ? ? ? ORDER BY

? ? ? ? ? ? ? ? 排序字段

? ? ? ? LIMIT'

? ? ? ? ? ? ? ? 分页限定

基础查询

• 1、查询多个字段
  • SELECT 字段列表 FROM 表名;
  • SELECT * FROM 表名; -- 查询所有数据
• 2、去除重复记录
  • SELECT DISTINCT 字段列表 FROM 表名;
• 3、起别名
  • AS:AS 也可以省略

条件查询（WHERE）

条件查询语法

• ? ? ? ? SELECT 字段列表 FROM 表名 WHERE 条件列表;

????????算术运算符

????????比较运算符

????????逻辑运算符

????????位运算符

聚合函数

1. 概念：将一列数据作为一个整体，进行纵向计算
2. 聚合函数分类

?????3.聚合函数语法：

? ? ? ? SELECT 聚合函数名(列名) FROM 表;

? ? ? ? 注意：null值不参与所有聚合函数运算

分组查询（GROUP BY）

• 语法：
  • SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
  • 注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
• WHERE和HAVING区别：
  • 执行时机不一样：WHERE是分组之前进行限定，不满足WHERE条件，则不参与分组，而HAVING是分组之后对结果进行过滤。
  • 可判断的条件不一样：WHERE不能对聚合函数进行判断，HAVING可以。
• 执行顺序：WHERE>聚合函数>HAVING

排序查询（ORDER BY）

• 排序查询语法
• SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] ...;
• 排序方式：
  • ASC:升序排列（默认值）
  • DESC:降序排列
• 注意：如果有多个排序条件，当前面的条件值一样时，才会根据第二条件进行排序

分页查询（LIMIT）

• 分页查询语法：
  • SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目;
  • 起始索引：从0开始
• 计算公式：起始索引 = (当前页码-1) * 每页显示的条数
• tips：
  • 分页查询limit是MySQL数据库的方言
  • Oracle 分页查询使用rownumber
  • SQL Server分页查询使用top

约束

约束的概念和分类

• 约束的概念：
  • 约束是作用于表中列上的规则，用于限制加入表的数据
  • 约束的存在保证了数据库中数据的正确性、有效性和完整性
• 约束的分类

Tips：MySQL不支持检查约束

非空约束

• 概念：
  • 非空约束用于保证列中所有数据不能有NULL值
• 语法
  • 添加约束
    • --? 创建表时添加非空约束
    • CREATE TABLE 表名(
      • 列名 数据类型 NOT NULL,
      • ...
    • );
    • -- 建完表后添加非空约束
    • ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
  • 删除约束
    • ALTER TABLE 表名 MODIFY 字段名 数据类型;

唯一约束

• 1、概念：
  • ·唯一约束用于保证列中所有数据各不相同
• 2、语法
  • （1）添加约束
  • -- 创建表时添加唯一约束
  • CREATE TABLE 表名(
    • 列名 数据类型 UNIQUE [AUTO_INCREMENT],
    • -- AUTO_INCREMENT：当不指定值时自动增长
    • ...
  • );
  • CREATE TABLE 表名(
    • 列名 数据类型,
    • ...
    • [CONSTRAINT] [约束名称] UNIQUE (列名)
  • );
  • -- 建完表后添加唯一约束
  • ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
  • （2）删除约束
  • ALTER TABLE 表名 DROP INDEX 字段名;

默认约束

• 1、概念
  • 保存数据时，未指定值则采用默认值
• 2、语法
  • （1）添加约束
  • -- 创建表时添加默认约束
  • CREATE TABLE 表名(
    • 列名 数据类型?DEFAULT 默认值,
    • ...
  • );
  • -- 建完表后添加默认约束
  • ALTER TABLE 表名 ALTER?列名?SET?DEFAULT?默认值;
  • （2）删除约束
  • ALTER TABLE 表名 ALTER 列名?DROP DEFAULT;

外键约束

• 1、概念：
  • 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性
• 2、语法
  • （1）添加约束
  • -- 创建表时添加外键约束
  • CREATE TABLE 表名(
    • 列名 数据类型,
    • ...
    • [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表（主表列名)
  • );
  • -- 建完表后添加外键约束
  • ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称)?REFERENCES 主表名称(主表列名称);
  • （2）删除约束
  • ALTER TABLE 表名 DROP?FOREIGN KEY 外键名称;

数据库设计

1、软件开发的步骤

?2、数据库设计概念

• 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
• 建立数据库中的表结构以及表与表之间的关联关系的过程。
• 有哪些表？表里有哪些字段？表和表之间有什么关系？

3、数据库设计的步骤

• 需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么？）
• 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
• 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
• 维护设计（1.对新的需求进行建表；2.表优化）

表关系

• 一对一：
  • 如：用户和用户详情
  • 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
• 一对多（多对一）：
  • 如：部门和员工
  • 一个部门对应多个员工，一个员工对应一个部门
• 多对多：
  • 如：商品和订单
  • 一个商品对应多个订单，一个订单包含多个商品

表关系之一对多

• 一对多（多对一）：
  • 如：部门和员工
  • 一个部门对应多个员工，一个员工对应一个部门
• 实现方式：在多的一方建立外键，指向一的一方的主键

?表关系之多对多

• 多对多：
  • 如：商品和订单
  • 一个商品对应多个订单，一个订单包含多个商品
• 实现方式：建立第三张中间表，中间至少包含两个外键，分别关联两方主键

表关系之一对一

• 一对一：
  • 如：用户 和 用户详情
  • 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
• 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

?总结

1、一对多实现方式

• 在多的一方建立外键关联一的一方主键

2、多对多实现方式

• 建立第三张中间表
• 中间表至少包含2个外键，分别关联双方主键

3、一对一实现方式

• 在任意一方建立外键，关联对方主键，并设置外键唯一

多表查询

• 笛卡尔积：取A,B集合所有组合情况
• 多表查询：从多张表查询数据
  • 连接查询
    • 内连接：相当于查询A B交集数据
    • 外连接：
      • 左外连接：相当于查询A表所有数据和交集部分数据
      • 右外连接：相当于查询B表所有数据和交集部分数据
  • 子查询
    • 概念：
      • 查询中嵌套查询，称嵌套查询为子查询
    • 子查询根据查询结果不同，作用不同
      • 单行单列
      • 多行单列
      • 多行多列

内连接

?1、内连接查询语法

• 隐式内连接
  • select 字段列表 from 表1，表2 . . . where 条件;
• 显示内连接
  • select 字段列表 from 表1 [INNER] JOIN 表2 ON 条件;

内连接相当于查询A B交集数据

外连接

?1、外连接查询语法

• 左外连接
  • select 字段列表 from 表1 left [outer] join 表2 on?条件;
• 右外连接
  • select 字段列表 from 表1 right [outer] join 表2 on?条件;

• 左外连接：相当于查询A表所有数据和交集部分数据
• 右外连接：相当于查询B表所有数据和交集部分数据

子查询

1.子查询根据查询结果不同，作用不同：

• 单行单列：作为条件值，使用 =? ，！= ，> ,<等进行条件判断
  • select 字段列表 from 表 where 字段名 = (子查询);
• 多行单列：作为条件值，使用in等关键字进行条件判断
  • select 字段列表 from 表 where 字段名 in (子查询);
• 多行多列：作为虚拟表
  • select 字段列表 from?(子查询)?where?条件;

事务

事务简介?

• 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令
• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
• 事务是一个不可分割的工作逻辑单元

?事务四大特征

• 原子性（Atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
• 隔离性（Isolation）：多个事务之间，操作的可见性
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

MySQL事务默认自动提交