企业官网建设流程全解析

数据库(DataBase)简介

• 解释
  • 存储和管理数据的系统
• 作用
  • 对数据库中的数据进行增、删、改、查
  • CURD: create(新增)、update(修改)、read(查询)、delete(删除)
• 分类
  • 关系型数据库(SQL数据库) RDBMS(Relation Database Management System)
    • 数据库、数据表
    • 数据存储形式: 数据以行列表格的形式进行存储
    • MySql、Oracle、SQLServer、DB2、SQLLite
  • 非关系型数据库
    • 数据存储形式: 键值对存储、文档存储、列式存储、图式存储
    • MongoDB、Redis、Hbase、Neo4j

MySql数据库简介

• 解释
  • 是目前最流行的关系型数据库管理系统（RDBMS）之一
  • 版本: 5.7 - 8.0 增加了窗口函数
  • 发展史: MySQL AB - SUN - Oracle
  • 特点
    • MySql数据库是c/s软件: client客户端 和 server服务端
    • 使用标准的SQL数据语言形式(增、删、改查)
• 查看版本
  • mysql --version / mysql -V
  • 连接MySql
    • 命令行形式
      • mysql -h(IP) -P(端口) -u(用户名) -p(密码)
      • mysql -u (用户名) -p
• DataGrip
  • DataGrip是JetBrains公司推出的管理数据库的产品（数据库客户端软件）
  • 添加驱动、连接服务器

MySql语法

• 结构化查询语言(Structured Query Language)
  • 关系型数据库管理系统都需要遵循的规范
  • 操作关系型数据库的语言
  • 关系型数据库，都支持SQL
• 作用
  • 对数据库中的数据进行管理操作(增、删、改、查)

DDL(Data Definition Language) 数据定义语言

• 解释
  • 数据定义语言, 用来定义数据库对象: 数据库、表、列的增、删、改
• 关键字
  • create创建 drop删除 alter修改
• 数据库增删改查

  #增createdatabase[ifnotexists]数据库名[charset"码表"];#删dropdatabase数据库名称;#改alterdatabase数据库名[charset'新的码表'];#查showdatabases;selectdatabase();use数据库名称;showcreatedatabase表名;

• 数据表增删改查

  #增createtable[ifnotexists]表名(列名 类型[约束]);#删droptable[ifexists]表名;#改altertable旧表名renameto新表名;renametable旧表名to新表名;#查showtables;desc表名;

• 表字段增删改

  #增altertable表名add列名 类型[约束]#删altertable表名drop列名#改altertable表名modify旧列名 类型[约束]altertable表名 change 旧列名 新列名 类型[约束]

• 数据类型

  #整数tinyint(1字节)# 性别、状态、开关smallint(2字节)# 小数量int(4字节)# 普通 ID、数量、年龄（最常用）bigint(8字节)# 订单号、主键 ID#小数floatdoubledecimal(M,N)# M：总有效数字长度（整数位 + 小数位总和）N：小数点后面保留几位小数 金额、价格、薪资（必须用）#字符串varchar(N)# 姓名、标题、地址、手机号（最常用）char()# 身份证、固定长度编码text# 文章内容、备注、详情enum# 固定值：男 / 女、正常 / 禁用#日期datetimedatetime

• 数据约束
  • 解释
    • 在数据类型限制的基础上，给存储的数据增加额外的要求
  • 主键和主键自增

    # 添加主键createtable表名(idintprimarykey)# 建表时添加主键createtable表名(idint,primarykey(id))# 建表时添加主键createtable表名(idint)altertable表名addprimarykey(id)# 建表后添加主键# 删除主键altertable表名dropprimarykey# 删除主键altertable表名modifyidint# 清空非空# 添加主键自增createtable表名(idintprimarykeyauto_increment)# 建表时添加altertable表名modifyidintauto_increment# 建表后添加# 删除主键自增altertable表名modifyidint# 删除自增altertable表名dropprimarykey# 删除主键altertable表名modifyidint# 删除非空

  • 非空 not null

    # 创建createtable表名(namevarchar(20)notnull)# 删除altertable表名modifynamevarchar(20)

  • 唯一 unique

    # 创建createtable表名(card_idvarchar(20)unique)altertable表名adduniqueindexcard_id(card_id)# 删除altertable表名dropindexcard_id

  • 默认 default

    # 创建createtable表名(gendervarchar(20)default1)# 删除altertable表名modifygendervarchar(20)

  • 外键 foreign key
    • 多表关系中，有外键列的表 称为 外表（从表） 有主键列的表 称为主表 外表的外键列 不能出现主表的主键列 没有的数据

    # 添加外键约束createtableemployee(idintprimarykeyauto_increment,dept_idint,# 外键约束格式: [constraint 外键约束名] foreign key (外键列名) references 主表名(主键列名)constraintfk_dept_idforeignkey(dept_id)referencesdept(id));# 从表# 删除语法：altertable表名dropforeignkey外键约束名;

DML(Data Manipulation Language) 数据操作语言

• 解释
  • 数据操作语言, 用来对数据库的表记录进行增、删、改
  • 关键字
    • insert插入 delete删除 update修改
  • 数据表增删改

    # 增insertinto表名values(值1,值2,值3);# 单条不指定字段insertinto表名(字段1,字段2,字段3)values(值1,值2,值3);# 单条指定字段insertinto表名values(值1,值2,值3),(值1,值2,值3);# 多条不指定字段insertinto表名(字段1,字段2,字段3)values(值1,值2,值3),(值1,值2,值3);# 多条指定字段# 删deletefrom表名where支持多条件;# 删除指定数据deletefrom表名;# 删除所有数据 不清空主键自增truncate表名;# 删除所有数据 清空主键自增# 改update表名set字段名=字段值wherecid=8;

DQL(Data Query Language) 数据查询语言

• 解释
  • 数据查询语言, 用来查询数据中表的记录
• 关键字
  • select 字段 from 表 where 条件
• 执行顺序
  1. FROM/JOIN → 先找表、关联表，生成临时表
  2. WHERE → 过滤原始行（分组前筛选）
  3. GROUP BY → 按字段分组
  4. 聚合函数计算 → COUNT/SUM/AVG/MAX/MIN
  5. HAVING → 过滤分组后的结果
  6. 窗口函数 OVER () 执行
  7. SELECT → 选择字段、计算表达式、起别名
  8. DISTINCT → 去重
  9. ORDER BY → 排序
  10. LIMIT → 限制返回条数
• where
  • 解释
    • 组前查询,筛数据行,不能用聚合
  • 比较运算符

    • < = >= <= !=

  • 逻辑运算符
    • and or not
  • 范围查询
    • select * from 表名 字段名 between a and b --> [a,b] # 适用于连续值
    • select * from 表名 字段名 in (a,b) --> a or b # 适用于非连续值
  • 模糊查询

    # % 任意多个任意字符select*from表名where字段名like'a%'select*from表名where字段名like'%a'select*from表名where字段名like'%a%'# _任意一个字符select*from表名where字段名like'a_'select*from表名where字段名like'_a'select*from表名where字段名like'a______'

  • 空值判断
    • is null
    • is not null
• 分组查询 group by
  • 解释
    • 根据谁分组，就根据谁查询，即：分组查询的查询列 只能出现 分组字段和聚合函数
  • 语法
    • select 分组列, count(主键) from 表名 group by 分组列;
  • 扩展

    # distinct去重, 多列组合起来完全相同才算重复，才会去重selectdistinct列1,列2from表名;​# group by去重select列1,列2from表名groupby列1,列2;​

• 聚合查询
  • count()
    • count(*) count(1) count(列) 有什么区别?
      • 1.效率问题: count(主键列) ≈ count(1) ≈ count(*) > count(列)
      • 2.是否统计null值: count(列)不统计,count(*) count(1)统计
  • sum(列名)
  • avg(列名)
  • max(列名)
  • min(列名)
• 组后查询 having
  • 解释
    • 筛分组结果，专门配聚合
• 排序查询 order by
  • 语法
    • select * from 表名 order by 列名1[asc|desc], 列名2[asc|desc]
  • 细节
    • 按照多列排序时,前列值相同的数据,则按照后列值排序
    • asc升序,desc降序 升序时asc可以省略不写
• 分页查询 limit M,N
  • 语法
    • select * from product limit 0,3; # 第1页
  • 分页
    • 总页数： (数据总条数 + 每页数据条数 - 1) // 每页数据条数
    • 每页的起始索引：(当前页数 - 1) * 每页数据条数
    • 数据总条数：count(主键列)
• 表关系和外键约束
  • 一对多
    • 用户和订单
  • 多对多
    • 学生和选修课
    • 一定有一个中间关系表
  • 一对一
    • 个人和身份证
• 交叉查询
  • 查询结果 = 笛卡尔积 即a的总条数 * b的总条数 会产生大量的脏数据，实际开发一般不用
  • 语法
    • select * from 表1, 表2;
    • select * from 表1 join 表2;
    • select * from 表1 cross join 表2;
• 连接查询
  • 内连接(只留两边都有的)
    • 查询结果 = 表的交集
    • 语法
      • select * from 表1 [inner] join 表2 on 关联条件
  • 左外连接(哪怕一边没有，也要全部带上)
    • 查询结果 = 左表全集 + 交集
    • 语法
      • select * from 表1 left [outer] join 表2 on 关联条件
  • 右外连接(哪怕一边没有，也要全部带上)
    • 查询结果 = 右表全集 + 交集
    • 语法
      • select * from 表1 right [outer] join 表2 on 关联条件
  • 全连接
    • 查询结果 = 左外连接 + 右外连接的结果
    • select * from 表1 left join 表2 on 条件
      union [distinct|all] # 合并去重/不去重
      select * from 表1 right join 表2 on 条件;
• 自关联(自连接)查询
  • 把一张表拆成两张表用，核心是起两个不同的别名

    # 求月销售额差值# 自连接方式selects2.month,s2.revenue,s1.revenue,(s1.revenue-s2.revenue)difffromsales s1joinsales s2ons1.month=s2.month+1;# 窗口函数select*,revenue-lag(revenue,1,revenue)over(orderbymonth)difffromsales;# 求月销售额累加# 自连接方式selectcurr.month,sum(prev.revenue)accu_salesfromsales currleftjoinsalesprevonprev.month<=curr.monthgroupbycurr.month;# 窗口函数select*,sum(revenue)over(orderbymonth)astotalfromsales;

• left join 和 join 使用场景
  • 想查全部数据（比如所有商品、所有客户） → LEFT JOIN
  • 只想查有关系、能匹配的数据 → JOIN
• 子查询
  • 解释
    • 一个sql语句的查询条件，需要依赖另一个sql语句的查询结果，这种写法就叫：子查询.
    • 作用
      • 充当主查询的条件
      • 充当主查询的数据源(临时表)
      • 充当主查询的查询字段
• CTE(Common Table Expression) 公用表表达式
  • 解释
    • 把一段查询结果临时当成一张虚拟表，简化嵌套子查询，代码更好读
  • 语法

    WITH临时表名AS(-- 这里写正常查询SQLSELECT字段FROM表)-- 外层直接使用这个临时表SELECT*FROM临时表名;

  • 优点
    • 可读性极强，层次清晰
    • 可多次复用
    • 支持多个 CTE 并列
  • 注意
    • 多个with条件用 逗号 分隔
• 窗口函数
  • 解释
    • 它是MySQL8.x的新特性,主要用于给表新增1列,治愈新增的内容是什么,取决于你用什么窗口函数
  • 语法
    • 窗口函数 over([partition by 分组字段 order by 排序字段 asc | desc])
  • 常用窗口函数

    row_number()# 做行号标记的 1 2 3 4rank()# 做稀疏排名的 1 2 2 4dense_rank()# 做密集排名的 1 2 2 3

  • 细节
    • 窗口函数 = 给表新增1列,至于新增的是什么,取决于和什么函数一起使用
    • 如果不写partition by,则统计的是全表数据,如果写了,则统计的是组内的数据
    • 如果不写order by,则统计的是组内所有的数据,如果写了,则统计的是组内从第一行截止到当前行的数据
• 条件判断
  • 单条件判断

    ifnull(exp1,exp2)# exp1不为空就是 exp1，否则是exp2if(条件1,满足条件值,不满足条件值)

  • 多条件判断

    case字段when值1then结果1when值2then结果2else其他结果endas别名

  • 统计新增一列
    • count/sum + case when/if​
    • 细节
      • 使用count统计时,空值为null; 使用sum统计时,空值为0
• 扩展

  • 数据备份

    # 备份表不存在createtable备份表select*from原表# 备份表存在insertinto备份表select*from原表

  • 行转列 列转行

    # 行转列createtable`score`(`学号`varchar(24)DEFAULTNULL,`科目`varchar(24)DEFAULTNULL,`成绩`int(11)DEFAULTNULL)engine=InnoDBdefaultcharset=utf8;insertinto`score`values('s001','语文',90),('s001','数学',100),('s001','英语',93),('s002','语文',98),('s002','数学',99),('s002','英语',96);​/** 1. 先写一个固定值添加上需要的列 2. 查找每一列对应的值, 通过if关键字进行判断,不满足填null 3. 分组,使用max()聚合 */select学号,max(if(科目='语文',成绩,null))'语文',max(if(科目='数学',成绩,null))'数学',max(if(科目='英语',成绩,null))'英语'fromscoregroupby学号;

    # 列转行createtable`w_score`(`学号`varchar(24)DEFAULTNULL,`语文`bigint(11)DEFAULTNULL,`数学`bigint(11)DEFAULTNULL,`英语`bigint(11)DEFAULTNULL)engine=InnoDBdefaultcharset=utf8;insertinto`w_score`values('s001',90,100,93),('s002',98,99,96);select学号,'语文'科目,语文 成绩from`w_score`unionselect学号,'数学'科目,数学 成绩from`w_score`unionselect学号,'英语'科目,英语 成绩from`w_score`;

DCL(Data Control Language) 数据控制语言

• 解释
  • 数据控制语言, 用来定义数据库的访问权限和安全级别及创建用户