MySQL学习

最新推荐文章于 2024-05-22 11:19:23 发布

飞翔的远方

最新推荐文章于 2024-05-22 11:19:23 发布

阅读量33

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_73952669/article/details/132763865

版权

MySQL数据模型

关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

使用表存储数据，格式统一，便于维护

使用SQL语言操作，标准统一，使用方便，可用于复杂查询

SQL简介

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

通用语法

SQL语句可以单行或多行书写，以分号结尾。

SQL语句可以使用空格/缩进来增强语句的可读性。

MySQL数据库的SQL语句不区分大小写。

注释：

1.单行注释：-- 注释内容或 # 注释内容(MySQL特有)

2.多行注释： /* 注释内容 */

SQL

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

数据库操作

查询所有数据库：show databases;

查询当前数据库：select database/schema();

使用数据库：use 数据库名 ;

创建数据库：create database/schema [ if not exists ] 数据库名 ;

删除数据库：drop database/schema [ if exists ] 数据库名 ;

表操作

create table  表名(

字段1 字段类型 [ 约束 ] [ comment  字段1注释 ] ,

......

字段n 字段类型 [ 约束 ] [ comment  字段n注释 ]

) [ comment  表注释 ] ;

约束

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

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

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key (auto_increment自增)
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

-- 创建student表包含id, name, birthday字段
create table tb_student(
    id int primary key auto_increment,
    name varchar(20) unique not null ,
    birthday varchar(30)
);

查询当前数据库所有表：show tables;

查询表结构：desc 表名;

查询建表语句：show create table 表名;

添加字段：alter table 表名 add 字段名类型(长度) [comment 注释] [约束];

修改字段类型：alter table 表名 modify 字段名新数据类型(长度);

修改字段名和字段类型：alter table 表名 change 旧字段名新字段名类型 (长度) [comment 注释] [约束];

删除字段：alter table 表名 drop column 字段名;

修改表名： rename table 表名 to 新表名;

删除表：drop table [ if exists ] 表名;

DML

Insert

指定字段添加数据：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.字符串和日期型数据应该包含在引号中。

3.插入的数据大小，应该在字段的规定范围内。

-- 插入数据,所有的字段名都写出来
insert into student(id【主键自增】, name, birthday, sex, address) values (null, '蔡徐坤', '2023-09-05', '鸡', '广州');

-- 插入数据,插入所有字段可以不写字段名
insert into student values (null, '真爱粉', '2023-09-05', '无', '深圳');

-- 插入部分数据，往学生表中添加 部分数据
insert into student(name,  address) values ('小黑子', '中国纽约');

Update

修改数据：update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ] ;

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

-- 不带条件修改数据，将所有的性别改成女
update student set sex = '女';

-- 带条件修改数据，将id号为1的学生性别改成男
update student set sex = '男' where id = 1;

-- 一次修改多个列，把id为3的学生，生日改成1988-08-08，address改成北京
update student set birthday = '1988-08-08',address = '广州' where id = 3;

Delete

删除数据：delete from 表名 [ where 条件 ];

1.DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

2.DELETE 语句不能删除某一个字段的值(如果要操作，可以使用UPDATE，将该字段的值置为NULL)。

-- 带条件删除数据，删除id为3的记录
delete from student where id = 3;

-- 不带条件删除数据,删除表中的所有数据
delete from student;

DQL

基本查询

查询多个字段：select 字段1, 字段2, 字段3 from 表名;

查询所有字段（通配符）：select * from 表名;

设置别名：select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;

去除重复记录：select distinct 字段列表 from 表名;

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

-- 查询student表中所有字段
select id, name, age, sex, address, math, english
from student;

-- 查询所有字段,使用*代表所有列
select * from student;

-- 查询student表中的 name 和 sex 列
select name, sex from student;

-- 查询sudent表中name 和 sex 列，
-- name列的别名为 姓名，sex列的别名为 性别
select name as '姓名', sex 性别
from student;

-- 取别名时AS关键字可以省略


-- 查询address列并且结果不出现重复的address
select distinct address from student;

条件查询

条件查询：select 字段列表 from 表名 where 条件列表 ;

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between ... and ...	在某个范围之内(含最小、最大值)
in(...)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
is null	是null

逻辑运算符	功能
and 或 &&	并且 (多个条件同时成立)
or 或 \|\|	或者 (多个条件任意一个成立)
not 或 !	非 , 不是

-- 查询math分数大于80分的学生
select id, name, age, sex, address, math, english
from student
where math >= 80;
-- 查询english分数小于等于90分的学生
select id, name, age, sex, address, math, english
from student
where english <= 90;

-- 查询age不等于20岁的学生
select id, name, age, sex, address, math, english
from student
where age != 20;

-- 查询英语成绩不是null的
select id, name, age, sex, address, math, english
from student
where english is not null ;

-- 逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
select id, name, age, sex, address, math, english
from student
where age > 35 and sex = '男';

-- 查询age大于35或性别为女的学生(两个条件其中一个满足)
select * from student
where age > 35 or sex = '女';

-- 查询id是1或3或5的学生
select id, name, age, sex, address, math, english
from student
where id in(1, 3, 5);

-- in: 在...里面,只要是满足()里面的数据都可以
-- 查询id是1或3或5的学生


-- 查询id不是1或3或5的学生
select id, name, age, sex, address, math, english
from student
where id not in(1, 3, 5);

-- 范围: BETWEEN 值1 AND 值2 -- 表示从值1到值2范围，包头又包尾
-- 查询english成绩大于等于75，且小于等于90的学生
select * from student
where english between 75 and 90;
-- 注意: between 值1 and 值2, 小的写前面大的写后面



-- 模糊查询like
-- 查询姓马的学生: 第一个是马,后面无所谓
select * from student
where name like '马%';

-- 查询姓名中包含'德'字的学生
select id, name, age, sex, address, math, english
from student
where name like '%德%';

-- 查询姓马，且姓名有三个字的学生
select * from student
where name like '马__';

分组查询

聚合函数

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

语法：select 聚合函数(字段列表) from 表名 ;

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

注意

null值不参与所有聚合函数运算。

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

分组查询：

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

where和having区别

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

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

注意

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

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

-- 查询男女各多少人
-- 1.按照性别分为男女两组
-- 2.统计每组的人数
select sex, count(sex)  from student group by  sex;


-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
select sex, count(sex) from student
where age > 25 group by sex;


-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
-- 并只显示性别人数大于2的数据分组后的条件，
-- 分组后的条件使用having
select sex, count(sex) from student
where age > 25 group by sex having count(sex) > 2;
-- 查询男女每组的数学总分
select sex, sum(math) from student group by sex;
-- 查询男女每组的数学平均分
select sex, avg(math) from student group by sex;
-- 查询男女每组的数学最高
select sex, max(math) from student group by sex;
-- 查询男女每组的数学低
select sex, min(math) from student group by sex;
-- 查询男女每组的数学最高分和英语最高分
select sex, max(math), max(english) from student group by sex;

排序查询

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

ASC：升序（默认值）

DESC：降序

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

-- 排序
-- 单列排序
-- 查询所有数据,使用年龄升序排序
select * from student
order by age asc ;
-- 查询所有数据,使用年龄降序排序
select * from student
order by age desc ;


-- 组合排序
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
select id, name, age, sex, address, math, english
from student
order by age desc , math desc ;

分页查询

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

1.起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

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

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

-- 查询学生表中数据，跳过前面2条，显示6条
select id, name, age, sex, address, math, english
from student
limit 2, 6;

-- 假设我们一每页显示5条记录的方式来分页，SQL语句如下：
-- 第一页: 跳过0条, 获取5条
select id, name, age, sex, address, math, english
from student
limit 5;
-- 如果跳过的条数是0,可以省略


-- 第二页: 跳过5条, 获取5条
select id, name, age, sex, address, math, english
from student
limit 5, 5;

-- 第三页: 跳过10条, 获取5条
select id, name, age, sex, address, math, english
from student
limit 10, 5;

函数

if(表达式, tvalue, fvalue)：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue

case expr when value1 then result1 [when value2 then value2 ...] [else result] end

-- 案例2
-- 男性与女性员工的人数统计 (1: 男性员工, 2: 女性员工)
-- 函数: if(条件表达式 , true显示的值, false显示的值)
select if(gender = 1, '男性', '女性'), count(gender)
from emp
group by gender;

-- 案例3
-- 员工职位信息
-- 函数: case ... when ... then ... when ... then ... else ... end
select (case job
            when 1
                then 'UI'
            when 2
                then 'java'
            when 3
                then '前端'
            when 4
                then '测试'
            else '运维'
    end
           ),
       count(job)
from emp
group by job;

多表设计

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

一对多(多对一)

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

-- 创建表时指定

create table 表名(

字段名数据类型,

...

[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)

);

-- 建完表后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

外键：

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

缺点：

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

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

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

-- 员工表(已有)
create table tb_emp
(
    id          int unsigned primary key auto_increment comment 'ID',
    username    varchar(20)      not null unique comment '用户名',
    password    varchar(32) default '123456' comment '密码',
    name        varchar(10)      not null comment '姓名',
    gender      tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image       varchar(300) comment '图像',
    job         tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
    entrydate   date comment '入职时间',
    dept_id int, 【外键关联tb_dept】
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '修改时间'
) comment '员工表';

-- 建完表后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

-- 部门表(需要设计)
create table tb_dept(
    id int primary key auto_increment,
    name varchar(10) not null ,
    create_time datetime,
    update_time datetime
);

一对一

案例: 用户与身份证信息的关系

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

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

-- 用户基本信息表(需要设计)

create table tb_user(
    id int primary key auto_increment,
    name varchar(20) not null ,
    gender tinyint unsigned not null ,
    phone char(11),
    degree varchar(5)
);


-- 用户身份信息表((需要设计)
create table tb_user_card(
    id int primary key auto_increment,
    nationality varchar(20) not null ,
    birthday date ,
    idcard char(18) not null ,
    issued varchar(20) not null ,
    expire_begin date,
    expire_end date,
    user_id int unique 【外键关联tb_user】
);

-- 建完表后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

多对多

案例: 学生与课程的关系

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

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

-- 学生表(已有)

create table tb_student
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '学号'
) comment '学生表';


-- 课程表(已有)
create table tb_course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';


-- 学生课程中间表(需要设计)
create table tb_student_course(
    id int primary key auto_increment,
    student_id int, 【外键关联tb_student】
    course_id int 【外键关联tb_course】
);

-- 建完表后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

多表查询

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

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

内连接

隐式内连接：select 字段列表 from 表1 , 表2 where 条件 ... ;

显式内连接：select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;

-- A.查询员工的姓名, 及所属的部门名称 (隐式内连接实现)
select *
from tb_emp,
     tb_dept
where tb_emp.dept_id = tb_dept.id;


-- B.查询员工的姓名, 及所属的部门名称 (显式内连接实现)
select *
from tb_emp
         inner join tb_dept on tb_emp.dept_id = tb_dept.id;

外连接

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

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

-- A.查询员工表所有员工的姓名, 和对应的部门名称 (左外连接)
select *
from tb_emp e
         left join tb_dept d on e.dept_id = d.id;

select *
from tb_dept d
         right join tb_emp e on e.dept_id = d.id;

-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select *
from tb_dept d
         left join tb_emp e on d.id = e.dept_id;

select *
from tb_emp e
         right join tb_dept d on d.id = e.dept_id;

子查询

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

形式：select * from t1 where column1 = ( select column1 from t2 … );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

分类

标量子查询：子查询返回的结果为单个值

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

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

列子查询：子查询返回的结果为一列

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

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

行子查询：子查询返回的结果为一行

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

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

表子查询：子查询返回的结果为多行多列

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

常用的操作符：in

-- 标量子查询
-- A.查询 "教研部" 的所有员工信息
select id,
       username,
       password,
       name,
       gender,
       image,
       job,
       entrydate,
       dept_id,
       create_time,
       update_time
from tb_emp
where dept_id = (select id
                 from tb_dept
                 where name = '教研部');

-- B.查询在 "方东白" 入职之后的员工信息
select id,
       username,
       password,
       name,
       gender,
       image,
       job,
       entrydate,
       dept_id,
       create_time,
       update_time
from tb_emp
where entrydate > (select entrydate from tb_emp where name = '方东白');

-- 列子查询
-- A.查询 "教研部" 和 "咨询部" 的所有员工信息
select id,
       username,
       password,
       name,
       gender,
       image,
       job,
       entrydate,
       dept_id,
       create_time,
       update_time
from tb_emp
where job in (select id from tb_dept where name in ('教研部', '咨询部'));

-- 行子查询
-- A.查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息;
select *
from tb_emp
where (job, entrydate) = (2, '2007-01-01');

select id,
       username,
       password,
       name,
       gender,
       image,
       job,
       entrydate,
       dept_id,
       create_time,
       update_time
from tb_emp
where (job, entrydate) = (select job, entrydate from tb_emp where name = '韦一笑')
  and name != '韦一笑';

-- 表子查询
-- A.查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select *
from (select * from tb_emp where entrydate > '2006-01-01') temp
         left join tb_dept
                   on temp.dept_id = tb_dept.id;

事务

delete from tb_dept where id = 1;

delete from tb_emp where dept_id = 1;

如果删除部门成功了，而删除该部门的员工时失败了，就造成了数据的不一致。

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

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

开启事务：start transaction; / begin ;

提交事务：commit;

回滚事务：rollback;

start transaction; #begin

delete from tb_dept where id = 1;

delete from tb_emp where dept_id = 1;

commit ;


start transaction;

delete from tb_dept where id = 2;

# delete from tb_emp where dept_id == 2; 【错误】

rollback ;

四大特性【重点】

原子性

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

一致性

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

提交后修改表数据与事务中数据一致

隔离性

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

在没有提交情况下不对表数据操作

持久性

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

索引

索引（index）是帮助数据库 高效获取数据 的数据结构。

优点

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

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

缺点

索引会占用存储空间。

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

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

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

show index from 表名;

drop index 索引名 on 表名;

注意事项

主键字段，在建表时，会自动创建主键索引。

添加唯一约束时，数据库实际上会添加唯一索引。

select * from system_user where user_uuid = '4357abc4-be4d-448d-a1bc-f7d22c1610bc'; -- 4 s 860 ms

create index uuid_index on system_user(user_uuid); -- 创建索引

select * from system_user where user_uuid = '4357abc4-be4d-448d-a1bc-f7d22c1610bc'; -- 19 ms

show index from system_user;

drop index uuid_index on system_user;

飞翔的远方

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL学习

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。通用语法SQL语句可以单行或多行书写，以分号结尾。SQL语句可以使用空格/缩进来增强语句的可读性。MySQL数据库的SQL语句不区分大小写。注释：1.单行注释：-- 注释内容或 # 注释内容(MySQL特有)2.多行注释： /* 注释内容 */SQL分类全称说明DDLDataDefinitionLanguage数据定义语言，用来定义数据库对象数据库，表，字段DMLDataML。
复制链接

扫一扫