mysql学习笔记-单表学习

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

1、基础知识

关键字建议大写，mysql中-- 和#都是注释，一般的文档中[]中的内容都是选填

常见的关系型数据库：Oracle，MySQL，SQL server，postgresql，db2，sqlite，mariadb
常见的非关系型数据库：MongoDB，Redis

对于一个数据库，最重要的就是里面的数据，以及如何存储会提升数据crud的性能，如何操作不重要，现在可视化软件已经很发达了，ChatGPT、文心一言等Ai对于操作数据库也是手到擒来，基本上不用一直写sql语句，可以直接操作可视化表格或者问Ai。

数据库就是一个用来存储数据的地方，大体上分为关系型数据库和非关系型数据库，非关系型数据库也叫NoSQL。sql可以操作几乎所有的关系型数据库，非关系型数据库需要单独学习。

关系型数据库可以理解为一张表格，里面的内容在创建表格的时候就已经限制了具体的类型、长度，是由多张能互相连接的二维表(由行和列组成的表)组成的数据库。
SQL是结构化查询语句，操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准，但是对于同一个需求，每一种数据库操作的方式可能存在一些不一样的地方，我们称之为方言，例如mysql和oracle的分页查询不一样。
MySQL是一个开源的关系数据库管理系统（RDBMS）。它使用SQL作为其主要查询语言，允许用户通过SQL语句来访问、管理和操作数据库。

SQL是一种语言，用于与关系数据库进行交互。它本身并不存储数据，而是用于从数据库检索、插入、更新和删除数据。
MySQL是一个具体的数据库管理系统，它实现了SQL语言，并提供了用于存储、检索和管理数据的各种工具和功能。

总之，MySQL和SQL是紧密相关的，但它们各自扮演不同的角色。SQL是一种用于与关系数据库交互的编程语言，而MySQL是一个使用SQL作为其查询语言的关系数据库管理系统。

MySQL数据模型：客户端-->数据库管理系统-->数据库-->数据表-->数据

2、基础操作

启动mysql：

services.msc是Windows操作系统中的服务管理器，它提供了一个图形界面来管理和控制系统中的各种服务。

要打开services.msc，可以按下win+r，输入services.msc，这将打开服务管理器窗口，其中列出了系统中安装的各种服务。

net start mysql -- 打开
mysqladmin -u root password 1234 -- 注册 
MySQL -u root -p 1234 -- 登录
exit/quit -- 退出
net stop mysql  -- 停止服务
Mysql -remove mysql  -- 卸载（后续还要删除目录及相关环境变量）

在客户端（navicat，idea等）登录：

mysql -u用户名 -p密码 [-h数据库服务器的IP地址 -P端口号]

mysql -h127.0.0.1  -p3306 -uroot -p -- 连接远程服务器

select version();             -- 查看版本
select now();                 -- 显示当前时间

3、数据类型

不用在这里浪费太多时间，只要知道存数字用int，存字符串用varchar，存时间用datatime就可以。

3.1 数值类型

类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
TINYINT	1byte	(-128，127)	(0，255)	小整数值
SMALLINT	2bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3bytes	(-8388608，8388607)	(0，16777215)	大整数值
INT/INTEGER	4bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8bytes	(-2^63，263-1)	(0，2^64-1)	极大整数值
FLOAT	4bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
DOUBLE	8bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

3.2 字符串类型

类型	大小	描述
CHAR	0-255 bytes	定长字符串(需要指定长度)
VARCHAR	0-65535 bytes	变长字符串(需要指定长度)
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

3.3 日期时间类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

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

空值在数学运算中的使用：包含空值的数学表达式值都为空值

Char存储性能高浪费空间，varchar存储性能低节省空间

create_time：记录的是当前这条数据插入的时间。

update_time：记录当前这条数据最后更新的时间。

例子：

birthday date,	-- 生日只需要年月日  
createtime  datetime, -- 需要精确到时分秒
username varchar(50), -- 长度不定, 最长不会超过50
phone char(11),--固定长度为11
age tinyint unsigned -- 0-255,不会出现负数, 而且人的年龄不会太大
score double(4,1), -- 总分100分, 最多出现一位小数

4、DDL-数据定义语言

数据定义语言是对数据库中表的创建，使用，查看，删除等操作

4.1 对库的操作

进入页面，首先要查看有哪些数据库，某个数据库下有哪张表：

select database();  #查询当前数据库
show databases;     #查询所有数据库

如果此时，需要操作某一个数据库下的表时，就需要通过指令，切换到对应的数据库下：

use 数据库名;

如果没有想要找的库，可以创建一个：

create database 数据库名;
create database if not exists 数据库名;

如果某个库没有用了，可以删除，删除数据库里面的表也会清空

drop database if exists 数据库名 ;

4.2 对表的操作

对表的操作有创建表、查询表的内容、修改表的结构或其中的数据、删除表或其中的数据。这里主要是对表本身的操作，不涉及对数据的操作，此时，查询表意味着查看这个表的样子、表的字段等，而不是表中的数据，修改表意味着修改表中某一列的字段名或类型、约束，比如把姓名改成性别，而不是张三的成绩换成100；

进入一个库，可以先看有哪些表：

show tables;  -- 查看数据库中存在哪些表

如果没有想要用的表，可以创建表：

create table  表名(
	字段1  字段1类型 [约束]  [comment  字段1注释 ],
	字段2  字段2类型 [约束]  [comment  字段2注释 ],
	......
	字段n  字段n类型 [约束]  [comment  字段n注释 ] 
) [ comment  表注释 ] ;

点击跳转约束

点击跳转主键自增

直接看个例子，这样清楚一点：

CREATE TABLE 模型测试单表 (
    ID VARCHAR(60) NOT NULL PRIMARY KEY,
    业务编号 VARCHAR(60),
    文本框1 VARCHAR(50),
    文本框2 VARCHAR(100),
    ADD_TIME DATETIME,
    LAST_UPDATE_TIME DATETIME,
    DELETED_MARK INT,
);

这句sql运行后应该会生成这样一张表，对应的字段下面只能存对应的类型：

ID	业务编号	文本框1	文本框2	ADD_TIME	LAST_UPDATE_TIME	DELETED_MARK

其中，按照阿里开发规范手册，一张表应该有物理主键，业务主键，新增时间，修改时间，删除表也应该使用逻辑删除；

物理主键就是这张表的唯一标识，可以通过这个标识来获取表，一般由雪花算法生成，是唯一的（哈希算法可能会重复）；

业务主键是自定义的一个主键标识，例如上面这张表“模型测试单表”，id为“hdq8c9ry89n3cyro2c893yrc829hfhcr2y”，业务编号可以为“mxcs-20240326-0001”，一个用来在底层唯一标识，一个用来用户进行查询或者分类等；

逻辑删除是增加一个DELETED_MARK的字段，可以规定1为可用，进行删除操作时同时进行，update 模型测试单表 set deleted_mark = 0;在后续查询的时候增加一个条件where DELETED_MARK = 1;，这样就可以过滤删除的表

表创建好之后或者没有建表，单纯想要看表的结构，例如想看一下人物表中性别存储的是0、1还是男女，姓名的存储上限是10还是100，可以通过以下语句：

desc 表名;  -- 可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息
show create table 表名;	-- 查询指定表的建表语句

如果表已经创建好，但是发现现在有一个新需求或者忘了有一个字段，可以是年龄或存款什么的，可以修改表，给表增加一个字段（当然学习的时候可以把表删了重新建一个如果里面的数据不重要，实际开发不建议这样）

-- 添加字段
alter table 表名 add  字段名  类型(长度)  [comment 注释]  [约束];
-- 修改数据类型
alter table 表名 modify  字段名  新数据类型(长度);
alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];
-- 修改表名
rename table 表名 to  新表名;

如果表中现在只需要一个文本框，可以通过删除字段“文本框2”使表只有“文本框1”这个字段；

删除表是把整个表删除，不是清空表中数据；

-- 删除字段
alter table 表名 drop 字段名;
-- 删除表
drop  table [ if exists ]  表名;

5、DML-数据操作语言

数据操作不涉及查，只有增删改，严格区分DML、DQL等没有很大的意义

添加数据（INSERT）、修改数据（UPDATE）、删除数据（DELETE）

5.1 添加数据

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, ...);	-- 批量添加数据（全部字段）

例子（其中create_time、update_time一般存储当前时间，主键(id)一般自动生成）：

insert into tb_emp(username, name, gender, create_time, update_time)
values ('weifuwang', '韦一笑', 1, '2024-03-26', now()),
       ('fengzi', '张三疯', 1, now(), now());

5.2 修改数据

修改数据不加条件会把整张表的所有数据修改；

在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;

例子：将emp表中id为1的员工，姓名name字段更新为'张三'

update emp 
set name = '张三' , update_time = now() 
where id = 1;

如果不加条件where id = 1会把员工表所有员工姓名改为"张三"。

5.3 删除数据

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

DELETE 语句不能删除某一个字段的值，只能删除整行数据，但是可以使用UPDATE，将指定字段的值置为NULL

delete from 表名  [where  条件] ;

例子：删除emp表中id为1的员工

delete from emp where id = 1;

6、DQL-数据查询语言

在一个正常的业务系统中，查询操作的使用频次是要远高于增删改操作的。而在这个查询过程中，还会涉及到条件、排序、分页等操作；

一般的系统中，影响性能的主要因素也是查询，因此要做好数据库的优化就要写好查询语句

查询为NULL的数据时，不能使用 = null，要使用where job is null;

空值在数学运算中的使用：包含空值的数学表达式值都为空值

查询语句语法结构：

SELECT 字段列表
FROM 表名
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数

6.1 基本查询

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

6.2 条件查询

select 字段列表 from 表名 where 条件列表 ; -- 条件列表：意味着可以有多个条件

学习条件查询就是学习条件的构建方式，而在SQL语句当中构造条件的运算符分为两类：

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

例子：查询入职日期在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息

-- 方式1: between...and
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp where entrydate between '2000-01-01' and '2010-01-01' 

-- 方式2:
select * from tb_emp where entrydate>='2000-01-01' and entrydate<='2010-01-01';

-- 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 且性别为女,名字第二个字是“大”的;
where job in (2,3,4) and gender = 2 and name like '_大%';

6.3 分组查询

分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行计算；

分组查询通常会使用聚合函数进行计算；

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

select * from 表名
WHERE 分组前条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表;;

where与having区别：

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

例子：根据性别分组 , 统计男性和女性员工的数量

select gender,count(*) from tb_emp
group by gender; -- 按照gender字段进行分组（gender字段下相同的数据归为一组）

例子：查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位

select job, count(*) from tb_emp
where entrydate <= '2015-01-01'   -- 分组前条件
group by job                      -- 对结果根据职位分组
having count(*) >= 2;             -- 分组后条件,获取员工数量大于等于2的职位

6.4 排序查询

排序方式：ASC ：升序（默认值）、DESC：降序

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

select * from 表名    
order by 字段1  排序方式1 , 字段2  排序方式2 … ;

例子：根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序

select * from tb_emp
order by entrydate ASC, update_time DESC;

6.5 分页查询

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

select * from 表名 limit  起始索引, 查询记录数;

例子：tb_emp表中共有1000行数据，我想看第30-40个数据

select * from tb_emp 
order by id ASC
limit 3,10;

6.0 -----聚合函数

之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）；

聚合函数会忽略空值，对NULL值不作为统计

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

常用聚合函数：

函数	功能
count	统计数量；按照列去统计有多少行数据。在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。
max	最大值；计算指定列的最大值
min	最小值；计算指定列的最小值
avg	平均值；计算指定列的平均值
sum	求和；计算指定列的数值和，如果不是数值类型，那么计算结果为0

在 SQL 查询中，使用 COUNT() 函数通常是为了计算某个列或者表达式的非空值的数量。常见的用法是 COUNT(*)，它计算所有行的数量。而在某些情况下，只是想要知道结果集中有多少行数据，并不关心具体的列或者表达式的值，可以使用 COUNT(常量)，其中的常量可以是任何非空的值，比如 COUNT(0)、COUNT(1) 或者 COUNT('x') 等。

COUNT(*) 可能需要读取表中的实际数据，而 COUNT(常量) 则可以更快地完成计算，因为它只需要统计行数而不关心具体值。所以在某些情况下，使用 COUNT(常量) 可能会更加高效。

例子：

select count('A') from tb_emp; -- 计算tb_emp表中的行数
select count(*) from tb_emp; -- 推荐此写法（MySQL底层进行了优化）

例子：员工性别统计

-- if(条件表达式, true取值 , false取值)
select if(gender=1,'男性员工','女性员工') AS 性别, count(*) AS 人数
from tb_emp
group by gender;

例子：员工职位统计：

-- case 表达式 when 值1 then 结果1  when 值2  then  结果2 ...  else  result  end
select (case job
             when 1 then '班主任'
             when 2 then '讲师'
             when 3 then '学工主管'
             when 4 then '教研主管'
             else '未分配职位'
        end) AS 职位 ,
       count(*) AS 人数
from tb_emp
group by job;

约束

想要限制字段所存储的数据，就需要用到数据库中的约束，可以在创建表/修改表的时候添加约束。

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

主键自增

每次插入新的行记录时，数据库自动生成id字段(主键)下的值

具有自增的数据列是一个正数序列开始增长(从1开始自增)

create table tb_user (
    id int primary key auto_increment, -- 主键自增
    username varchar(20) not null unique,
    name varchar(10) not null,
    age int,
    gender char(1) default '男'
);

为已经创建完成的表字段设置为自动增长列

ALTER TABLE 表名 MODIFY 字段名 数据类型 PRIMARY KEY AUTO_INCREMENT;

posted @ 2024-03-26 19:59 燕子去了阅读(63) 评论(0) 收藏举报

刷新页面返回顶部

lm02