一篇入门MySQL基础

create table tb_user(
  id int primary key auto_increment comment 'ID,唯一标识',
  username varchar(20) not null unique comment '用户名',
  name varchar(10) not null comment '姓名',
  age int comment '年龄',
  gender char(1) default '男' comment '性别'
) comment '用户表';

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确性、有效性和完整性。

分类：

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

见下表。

MySQL数据类型.xlsx

设计表结构的基本流程

1.3 数据库操作-DML

DML：数据库操作语言，用来对数据库中表的数据记录进行增、删、改操作

添加数据：insert

语法：insert into 表名 (字段1，字段2) values (值1，值2),(值1,值2);

更新数据：update

语法：update 表名 set 字段1=值1，字段2=值2,…. [where 条件];

若没有增加条件，则更新整张表的所有数据

删除数据：delete

语法：delete from 表名 [where 条件];

若没有增加条件，则删除整张表的所有数据

1.4 数据库操作-DQL(重点掌握)

DQL：数据查询语言，用来查询数据库中表的记录

DQL-基本查询

语法：

查询多个字段：select 字段1，字段2，字段3 from 表名;
查询所有字段（通配符）:select * from 表名;
设置别名：select 字段1 [as 别名1],字段2[as 别名2] from 表名;
去除重复记录：select distinct 字段列表 from 表名;

注意事项：*号代表查询所有字段，在实际开发中尽量少用（不直观，影响效率）。

DQL-条件查询

语法：

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

DQL-分组查询

聚合函数:不对null值进行计算

介绍：将一列数据作为一个整体，进行纵向计算。

语法：select 聚合函数（字段列表）from 表名;

max,min,count,avg,sum

统计数量可以使用：count() count(字段) count(常量) ，推荐使用count()

分组查询

语法：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where和having的区别(面试题)

1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

2.判断条件不同：where不能对聚合函数进行判断，而having可以

注意事项：

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

执行顺序：where>聚合函数>having。

-- 先查询入职时间在'2015-01-01'（包含）以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位
select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;

排序查询

语法：select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式1,字段2, 排序方式2 …;

排序方式：

ASC:升序（默认值）

DESC:降序

注意事项：

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

语法:

分页查询：select 分段列表 from 表名 limit 起始索引,查询记录数;

注意事项：

1.起始索引从0开始，起始索引=(查询页码-1)*每页展示记录数。

2.分页查询时数据库的方言，不同的数据库有不同的实现，MySQL中是limit。

3.如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 查询记录数

案例

-- 按需求完成员工管理的分页条件查询-根据输入条件，查询第一页数据，每页展示10条记录
-- 输入条件：
  --姓名：张
  --性别：男
  --入职时间：2000-01-01 2015-01-01
  select * from tb_emp 
  where name like '%张%' 
  and gender=1 
  and entrydate between '2001-01-01' and '2015-01-01' 
  order by update_time desc
  limit 0,10;

--案例2-1：根据需求，完成员工性别信息的统计-count(*)
--if(条件表达式,true取值,false取值)
select if(gender=1,'男性员工','女性员工'),count(*) from tb_emp group by gender;

--案例2-2：根据需求，完成员工职位信息的统计
select
(case job 
 when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) 职位 
count(*)
from tb_emp group by job;

2.多表设计

2.1 多表设计-概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模板之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多（多对一）

多对多

一对多

2.1.1 一对多

一对多关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键。

外键

物理外键

概念：使用foreign key定义外键关联另外一张表。

缺点：

①影响增、删、改的效率（需要检查外键关系）。

②仅用于单节点数据库，不适用于分布式，集群场景

③容易引发数据库的死锁问题，消耗性能。

逻辑外键

概念：在业务层逻辑中，解决外键关联

通过逻辑外键，就可以很方便的解决上述问题。（推荐）

2.1.2 一对一

案例：用户和身份证信息的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

2.1.3 多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

3.多表查询

3.1 多表查询

3.1.1 概述

多表查询：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合（A和B）的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

分类

连接查询

内连接：相当于查询A、B交集部分数据

外连接

左外连接：查询左表所有数据（包括两张表交集部分数据）

右外连接：查询右表所有数据（包括两张表交集部分数据）

子查询

3.1.2 连接查询

内连接查询

语法

隐式内连接：select 字段列表 from 表1,表2 where 条件(消除笛卡尔积)…
显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件…；

外连接查询

语法

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

3.1.3 子查询

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > ≥ < ≤

列子查询

子查询返回的结果是一列（可以是多行）

常用的操作符：in、not in等

行子查询

子查询返回的结果是一行（可以是多列）

常用的操作符：= <> in not in

表子查询

子查询返回的结果是多行多列，常作为临时表

常用的操作符：in

4.事务

4.1 概念

事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

注意事项：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务

4.2 事务控制

开启事务：start transaction;/begin;
提交事务：commit;
回滚事务：rollback;

4.3 事务四大特性（ACID）

原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败

一致性：事务完成时，必须使所有的数据都保持一致状态

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

5.数据库优化

5.1 索引

5.1.1 介绍

索引是帮助数据库高效获取数据的数据结构
优缺点

优点：

1>提高数据查询的效率，降低数据库的IO成本

2>通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：

1>索引会占用存储空间

2>索引大大提高了查询效率，同时却也降低了insert,update,delete的效率

5.1.2 结构

MySQL数据库支持的索引结构有很多，如：Hash索引，B+索引，Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是默认的B+Tree结构组织的索引。

B+Tree（多路平衡搜索树）

每一个节点，可以存储多个key（有n个key，就有n个指针）。
所有的数据都存储在叶子节点，非叶子节点仅用于索引数据。
叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询。

5.1.3 语法

创建索引

create [unique] index 索引名 on 表名(字段名,…);

查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;

注意事项：

主键字段,在建表时，会自动创建主键索引（效率最高）
添加唯一约束时，数据库实际上会添加唯一索引

Amanda Green

关注

23
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
一篇入门MySQL基础

MySQL官方提供了两种不同的版本：商业版本：收费，可以试用30天，官方提供技术支持社区版本：免费，MySQL不提供任何技术支持（初学者安装社区版即可）安装及配置步骤可以参考以下文档MySQL安装文档.pdfSQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模板之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多（多对一）多对多一对多。
复制链接

扫一扫