MySQL数据库知识点简易总结篇

SQL数据库简易版笔记

SQL（结构化查询语句）：可操作世界上所有的关系型数据

关系数据库

建立在关系模型基础上的数据库。简单来说，关系型数据库是多张能互相连接的二维表组成的数据库。其优势有：

• 都是表结构，格式一致，易于维护。
• 使用通用的SQL语言操作，使用方便，可用于复杂查询。
• 数据存储在磁盘中，安全。

和【包】有一点类似，数据库在MySQL数据库管理系统中以文件夹的方式呈现

SQL语句语法

通用语法

• SQL语句可以单行或多行书写，以分号结尾。
• MySQL数据库的SQL语句不区分大小写，关键字建议大写。
• 注释
  • 单行注释：–注释内容 或 #注释内容（MySQL特有）
  • 多行注释：/* 注释 */

SQL分类

• DDL(Data Definition Language) 数据定义语言: 用来定义数据库对象：数据库，表，列等
• DML（Data Mainpulation Language）数据操作语言: 用来对数据库中表的数据进行增删改查
• DQL（Data Query Language）数据查询语言： 用来查询数据库中表的记录（数据）
• DCL（Data Control Language）数据控制语言： 用来定义数据库的访问权限和安全级别，及创建用户

DDL 数据定义语言

操作数据库篇

查询数据库

SHOW DATABASES;

创建数据库

CREATE DATABASE 数据库名称;

创建数据库（判断，如果不存在则创建）

CREATE DATABASE IF NOT EXISTS 数据库名称;

删除数据库

DROP DATABASE 数据库名称;

删除数据库（判断，如果存在则删除）

DROP DATABASE IF EXISTS 数据库名称;

查看当前使用的数据库

SELECT DATABASE();

使用数据库

USE 数据库名称;

操作表篇

查询当前数据库下所有表的名称

SHOW TABLES;

查询表结构

DESC 表结构;

创建表

CREATE TABLE 表名(
  字段名1 数据类型1, #name varchar(30)
  字段名2 数据类型2, #score double(3,2) 前面为整数最大程度，后面为小数保留位数
  ...                #day date
  字段名n 数据类型n  #grade int
); # 最后一行末尾，不能加逗号

其中MySQL支持多种数据类型，可以分为三类：1.数值 2.日期 3.字符串

删除表

DROP TABLE 表名;

删除表（判断，如果表格存在）

DROP TABLE IF EXISTS 表名;

修改表名

ALTER TABLE 表名 RENAME TO 新的表名;

添加一列

ALTER TABLE 表名 ADD 列名 数据类型;

修改数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

推荐图形化客户端工具 Navicat for MySQL 查询中可以写SQL语句

DML 数据操作语言

对象为表中的数据

添加数据

给指定列添加数据

INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...);

给全部列添加数据

INSERT INTO 表名 VALUES(值1,值2,...);

批量添加数据

INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),..;
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;

修改数据

修改表数据

UPDATE 表名 SET 列名1=值1,列名2=值2,...[WHERE 条件];
# 注意如果修改数据语句中，不增加条件，将修改所有数据！

删除数据

DELETE FROM 表名 [WHERE 条件];

DQL（数据查询语言）

基础查询

查询多个字段

SELECT 字段列表 FROM 表名; # select name,age from stu;
SELECT * FROM 表名;

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

起别名

AS: #AS也可以省略
select name as 姓名 from stu;

条件查询

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

模糊查询

SELECT 字段列表 FROM 表名 WHERE 
/* 通配符：
        _:代表单个任意字符
        %:代表任意个数字符
*/
select name from stu where name like '马%'; #含有马的情况 '%马%'

排序查询

排序查询语法

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

分组查询

聚合函数：将一列数据作为一个整体，进行纵向计算。

聚合函数包括：

count(列名) # 用于统计数量
max(列名)        min(列名)        sum(列名)        avg(列名)

聚合函数语法

SELECT 聚合函数名（列名） FROM 表; # null值不参与所有聚合函数运算

分组查询

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
# 注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
select name,avg(math),count(*) from stu group by sex;
/*where和having区别：
执行时机不一样：where是分组之前进行限定，不满足where条件，则不参与分组，而having:是分组之后对结果进行过滤。
可判断的条件不一样：where不能对聚合函数进行判断，having可以。*/
# 执行顺序：where > 聚合函数 > having

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引 ,查询条目数;
# 起始索引：从0开始，后面页面根据计算公式计算
# 计算公式：起始索引=（当前页码-1）*每页显示的条数
/*
tips:
    分页查询limit是MySQL数据库的方言，仅在MySQL数据库中可以使用
    Oracle分页查询使用rownumber
    SQL Server分页查询使用top
*/

约束

对数据进行约束，例如分数不能为负数

约束的概念

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

约束的分类

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null值	NOT NULL
唯一约束	保证列中所有数据各不相同	UNIQUE
主键约束	逐渐使一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时，未指定值则采用默认值	DEFAULT
外键约束	外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性	FOREIGN KEY

tips：MySQL不支持检查约束

CREATE TABLE stu(
    sid INT PRIMARY KEY auto_increment, --主键且自增
    name VARCHAR(50) NOT NULL UNIQUE, --非空且唯一
    math DOUBLE(3,2) DEFAULT 0 --默认为0
);
# auto_increment修饰的必须是数字类型且有唯一约束
# 添加外键关联信息
CONSTRAINT fk_stu_course FOREIGN KEY(stu_id) REFERENCES stu(sid)
# 注意表的创建和加入信息的先后关系，这里需要创建stu表，才有课程表，添加课表信息，必须现有stu信息

数据库设计

软件研发步骤

需求分析（产品原型：产品经理）—>设计（软件结构设计，数据库设计，接口设计，过程设计：架构师）—>编码（开发工程师）—>测试（测试工程师）—>运维工程师

数据库设计

• 根据业务需求，为业务系统构建最优的数据存储模型
• 简历数据库中的表结构以及表与表之间的关联关系的过程
• 表当中有哪些字段

数据库设计的步骤

• 需求分析
• 逻辑分析（通过ER图对数据库进行逻辑建模）
• 物理设计
• 维护设计
  • 对新的需求建表
  • 表优化

表关系

• 一对一：学生与学号
  • 在任意一方简历外键，关联对象的主键，并设置外键唯一
• 一对多（多对一）：班级与学生
  • 在多的一方添加外键关联另一张表的主键
• 多对多：老师和学生
  • 建立中间表，包含至少两个外键，分别关联双方的主键

多表查询

从多张表里面查询数据

连接查询

• 内连接（相当于查询A B两个交集数据）

  --隐式内连接
  SELECT 字段列表 FROM 表1,表2... WHERE 条件
  
  --显示内连接
  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
  -- 内连接相当于A B的交集
  

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

    -- 左外连接
    SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件
    -- 右外连接
    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件
    

子查询

单行单列：作为条件值，使用=、!=、>、<等进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

多行单列：作为条件值，使用in等关键字进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

多行多列：作为虚拟表

SELECT 字段列表 FROM (子查询) WHERE 条件;

事务

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

事务操作

-- 开启事务
START TRANSACTION;
-- 或者
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 举例
BEGIN;
UPDATE account set money = money - 500 where name = 'lisi';
UPDATE account set money = money + 500 where name = 'lisi';
-- 没有问题则是提交事务
COMMIT;
-- 出错了，不会更改数据库数据，但自己看到的是出错的数据，需要回滚
ROLLBACK;
-- MySQL的SQL语句是默认提交事务的，其他数据需要注意

事务四大特征

• 原子性：不可切割
• 一致性：所有数据保持状态一致
• 隔离性：多个事务之间，操作的可见性
• 持久性：事务一旦提交或回滚，它对数据库中的数据的改变是永久的