MySQL【定制化】~ 基础篇

R.quite

已于 2022-06-12 14:37:11 修改

阅读量410

点赞数

分类专栏： MySQL【定制化】文章标签： mysql 数据库 mariadb

于 2022-05-30 18:57:34 首次发布

本文链接：https://blog.youkuaiyun.com/qq_41135440/article/details/125051778

版权

MySQL【定制化】专栏收录该内容

1 篇文章

订阅专栏

1、概括

1.1、数据库

英文单词DataBase，简称DB。
存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

1.2、数据库管理系统

DataBaseManagement，简称DBMS。
数据库管理系统是专门用来管理数据库中数据的，它可以对数据库当中的数据进行增删改查（CRUD）。

1.3、常见的数据库管理系统

MySQL、Oracle、MS SqlServer、DB2等等......

2、数据库基本操作

show databases; -> 查看MySQL中所有数据库；

use study_db; -> 指定一个使用数据库（例如：指向 -> study_db库）；

create database study_db01; -> 创建数据库（例如：创建一个study_db01库）；

show tabls; -> 查看某个数据库下拥有的表；

select version(); -> 查看MySQL数据库版本；

select database(); -> 查看当前使用的数据库；

3、SQL语句分类

3.1、DQL（数据库查询）

select（查询数据）

3.2、DML（数据库操作）

insert（插入数据）

delete（删除数据）

update（更新数据）

3.3、DDL（数据库定义）

create（创建，例如：创建数据库、创建表）

drop（删除，例如：删除数据库、删除表）

alter（修改，例如：修改数据库、修改表）

3.4、TCL（事务控制）

事务控制：要么都成功，要么都失败。

事务提价：commit

事务回滚：rollback

3.5、DCL（数据控制）

数据控制：例如：授权grant、撤销权限revoke....

4、MySQL ~ 数据类型

4.1、varchar（可变长字符串）

可变长字符串，节省空间
会根据数据长度动态分配空间
优点：节省空间
缺点：需要动态分配空间，速度慢

4.2、char（固定长度字符串）

定长字符串，分配固定长度的空间去存储数据
使用不恰当的时候，可能会导致空间的浪费
优点：不需要动态分配空间，速度快
缺点：使用不当可能会导致空间的浪费
varchar和char我们应该怎么选择？
- 例如：性别字符串长度固定的（char）
- 例如：姓名之类不固定字符串长度的（varchar）

4.3、int（整数型）

整数型

4.4、bigint（长整型）

长整型（等同于Java中 long 类型）

4.5、float（单精度浮点型）

单精度浮点型

4.6、double（双精度浮点型）

双精度浮点型

4.7、date（短日期类型）

短日期类型（2022-05-30）

4.8、datetime（长日期类型）

长日期类型（2022-05-30 19:14 00）

4.9、clob（字符大对象）

字符大对象
最大可以存储 4G 字符串
超过 255 个字符都要采用clob字符大对象存储

4.10、blob（二进制大对象）

二进制大对象
用于存储：图片、声音、视频等流媒体之类数据
需要使用 IO 流经行数据的插入

5、MySQL ~ 约束

not null（非空约束）
unique（唯一性约束）
primary key（主键约束）
foreign key（外键约束）
check（检查约束，MySQL不支持，Orache支持）

5.1、not null（非空约束）

作用：not null 表示字段不能为NULL

drop table if exists test_table;

create table test_table
(
    `id` int not null comment '该字段不能为NULL',
    `username` varchar(11) not null comment '该字段不能为NULL'
);

5.2、unique（唯一性约束）

作用：unique 表示字段内容不能重复（可以为NULL）

drop table if exists test_table;

create table test_table
(
    `id` int unique comment '该字段是唯一的（不能出现重复的值）',
    `username` varchar(11) unique comment '该字段是唯一的（不能出现重复的值）'
);

5.3、primary key（主键约束）

作用：primary key 表示字段是唯一的（类似身份证）

drop table if exists test_table;

create table test_table
(
    `id` int primary key comment '主键',
    `username` varchar(11)
);

5.3.1、复合主键

复合主键：使用多个字段组成的主键（例如：id和username联合起来一起做为主键）

drop tbale if exists test_table;

# id-username 组成一个复合主键
create table test_table
(
    `id` int,
    `username` varchar(11),
    `nick_name` varchar(255),
    primary key(`id`, `username`)
);

5.3.2、主键自动递增

drop table is exists test_table;

create table test_table
(
    `id` int primary key auto_increment,
    `username` varchar(11)
);

insert into test_db(`username`) values('test01');
insert into test_db(`username`) values('test02');
insert into test_db(`username`) values('test03');

5.3.3、主键分类

自然主键：主键值是一个自然数，和业务没关系。
业务主键：例如拿身份证号码做主键值，这就是业务主键！

5.4、foreign key（外键约束）

作用：foreign key 表示某个表的某个字段指向某个表的主键字段（这就是外键）

个人：我一般不用外键，所以简单介绍。

删除表的顺序 -> 先删子，再删父
创建表的顺序 -> 先创建父，再创建子
删除数据的顺序 -> 先删子，再删父
插入数据的顺序 -> 先插入父，再插入子

6、MySQL ~ 表的基本操作

6.1、创建表

# 检查 test_table 表是否存在，存在就删除
drop table if exists test_table;

# 创建 test_table 表
create table test_table
(
    `id` int primary key auto_increment unique comment '主键（自动递增）', 
    `name` varchar(255) not null comment '名字（不能为NULL）',
    `email` varchar(50) comment '邮箱（可以为NULL）'
);

6.2、删除表

# 删除 test_table 表
drop table if exists test_table;

7、MySQL ~ CRUD

7.1、insert（添加数据）

# 单条记录插入
insert into test_table(`name`, `email`) values('test01', '77777777@qq.com');

# 多条记录插入
insert into test_table('name') values
('test02', '77777777@qq.com'),
('test03', '77777778@qq.com'),
('test04', '77777779@qq.com'),

7.2、delete（删除数据）

1、delete

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！
缺点：删除效率比较低。
优点：支持回滚，可以再恢复数据！

# 删除数据（根据条件删除）
delete from test_table where id=1;

# 全部删除
delete from test_table;

2、truncate

删除效率高，表被一次截断，物理删除。
缺点：不支持回滚。
优点：快速。
只能操作表，将表中数据全部删除，在功能上和不带where子句的delete语句相同！！！

# truncate 清空表数据（永久删除，不支持回滚！！！）
truncate table test_table;

7.3、update（更新数据）

# 更新数据（根据条件更新）
update test_table set `name`='the test', `email`='000000000@qq.com' where id=1;

7.4、select（查新数据）

# 查询数据（根据条件查询）
select * from test_table where id=1;

# 查询数据（queryAllData）
select * from test_table;

# 查询指定字段（queryAllData）
select `id`, `name`, `email` from test_table;

8、MySQL ~ 函数

8.1、测试表

# 创建测试：员工表 sys_emp
drop table if exists sys_emp;
create table sys_emp
(
	`id` int primary key auto_increment unique comment '主键',
	`emp_name` varchar(55) comment '员工名',
	`emp_sal` double comment '员工工资',
	`emp_comm` double comment '员工津贴',
	`lnduction_time` datetime comment'入职时间',
	`resign_time` datetime comment '离职时间'
);

insert into sys_emp(`emp_name`,`emp_sal`,`emp_comm`,`lnduction_time`)
values
('张三', 38888.88, 0.05, now()),
('李四', 1800.00, 0.03, now()),
('佳佳', 4300, 0.02, now()),
('小明', 3888.88, 0.08, now()),
('小红', 5000, 0.06, now()),
('RuanJia', 88888888.88, 8, now()),
('R-quite', 8888888, 0.8, now()),
('CaiCai', 77777, 0.07, now()),
('test_r01', 5000, 0.05, now()),
('test_r02', 6000, 0.06, now()),
('test_r03', 6000, 0.07, now()),
('test_r04', 7000, 0.08, now()),
('test_r05', 8000, 0.09, now());

select * from sys_emp;

8.2、单行函数

特点：一个输入===一个输出

8.2.1、字符串函数

# 字符串长度（length）
select length('stringtest') as `str_length`; -- 输出：10
select length('测试test') as `str_length`; -- 输出：10 一个汉字代表3个字符（注意：这个汉字长度取决于你使用MySQL字符集类型）

# 字符串拼接（concat）
select concat('R', '_', 'quite') as `name`; -- 输出：R_quite

# 字符串大小写转换（upper/lower）
select upper('ruanjia') as `name`; -- 输出：RUANJIA
select lower('RUANJIA') as `name`; -- 输出：ruanjia

# 字符串截取（substr/substring）
select substr('原来是这样，Ruanjia', 7) as `nick_name`; -- 索引从 7 的位置开启截取，输出：Ruanjia
select substr('study Java 啊！', 7,4) as `nick_name`; -- 从 7 的位置开启截取，截取长度为 4，输出：Java
select substring('原来是这样，Ruanjia', 7) as `nick_name`; -- 输出：Ruanjia
select substring('study Java 啊！', 7,4) as `nick_name`; -- 输出：Java

# 返回字符串第一次出现位置索引（instr）
select instr('test Ruanjia 啊！', 'Ruanjia') as `idnex`; -- 例如：返回字符串 R 出现索引，输出：6

# 取出字符串前后空格（trim）
select length('  string  ') as `str_length`; -- 长度为：10
select length(trim('  string  ')) as `str_length`; -- 长度为：6
select trim('x' from 'xxxxRuanjiaxxx') as `str_name`; -- 输出：Ruanjia

# 使用指定字符‘左’填充（lpad）
select lpad('Ruanjia', 11, '*') as `str_name`; -- 11：代表整个填充后字符串的长度，输出：****Ruanjia

# 字符串替换（replace）
select replace('非常好的城大！', '非常好', 'SB') as `name`; -- 输出：SB的城大！

8.2.2、日期函数

# 日期函数
select now() as 日期; -- 2022-05-31 20:38:25
select curdate() as 日期; -- 2022-05-31
select curtime() as 当前时间; -- 20:39:24
select year(now()) as 年; -- 2022
select month(now()) as 月; -- 5
select month(now()) as 月; -- 2022
select date_format(now(), '%Y年%m月%d日') as 日期格式化; -- 2022年05月31日
select date_format(now(), '%Y-%m-%d %H:%i %s') as 日期格式化; -- 2022-05-31 20:44 47

# 计算两个日期之间间隔天数
select concat(datediff('2022-5-31', '2022-1-10'), '天') as 间隔天数; -- 141天

8.2.3、数学函数（常用）

# 四舍五入计算
select round(3.14); -- 输出：3
select round(3.55); -- 输出：4
select round(3.145, 2); -- 输出：3.15
select round(3.554, 2); -- 输出：3.55

# 向上取整
select ceil(1.11); -- 输出：2
# 向下取整
select floor(1.99); -- 输出：1

# 保留多少位小数
select truncate(1.8888888, 2); -- 输出：1.88
select truncate(1.1111111, 1); -- 输出：1.1

# 获取 0-1 之间的小数
select rand(); -- 输出：0-1 之间的小数
select rand()*100; -- 输出：100内随机数
select rand()*1000; -- 输出：1000内随机数

8.2.4、流程控制函数

# 流程控制语句
select if(20>=18, '你成年了，该努力了少年！', '小屁孩，玩泥巴去！') as `message`; -- 输出：你成年了，该努力了少年！

8.3、分组函数

特点：最终结果只有一个

count -> 统计
sum -> 求和
avg -> 平均值
max -> 最大值
min -> 最小值

# count   ->   计数
select count(1) from sys_emp; -- 统计员工个数

# sum   ->   求和          
select sum(`emp_sal`) from sys_emp; -- 统计所有员工工资总和

# avg   ->   平均值
select avg(`emp_sal`) from sys_emp; -- 所有员工工资平均值

# max   ->   最大值
select max(`emp_sal`) from sys_emp; -- 所有员工中最高工资

# min   ->    最小值
select min(`emp_sal`) from sys_emp; -- 所有员工中最低工资

9、MySQL ~ 基础查询操作（重点）

9.1、单表查询

# 1、查询 sys_emp 表所有字段的所有数据
select * from sys_emp;

# 2、条件查询
select id, emp_name, emp_sal from sys_emp where id=1;

# 3、=（等于）
select emp_name, emp_sal from sys_emp where emp_sal=5000;

# 4、!=、<>（不等于）
select * from sys_emp where emp_sal <> 5000;
select * from sys_emp where emp_sal != 5000;

# 5、<=（小于等于）
select * from sys_emp where emp_sal <=5000;

# 6、>=（大于等于）
select * from sys_emp where emp_sal >=5000;

# 7、is null、is not null（是null、不是null）
select * from sys_emp where resign_time is null;
select * from sys_emp where resign_time is not null;

# 8、and、or（并且、或者）
select * from sys_emp where emp_sal > 3000 and emp_comm >=0.08;
select * from sys_emp where id=1 or id=2;

# 9、in（包含）
select * from sys_emp where id in(1, 2, 3, 4, 5);

# 10、not（取非，例如：not in（不包含））
select * from sys_emp where id not in(1, 2, 3, 4, 5);

# 11、like（模糊查询）
select * from sys_emp where emp_name like 'R%'; --  右通配
select * from sys_emp where emp_name like '%小%'; -- 左右通配
select * from sys_emp where emp_name like '%红'; -- 左通配

# 12、distinct（去重）
insert into sys_emp(`emp_name`, `emp_sal`, `emp_comm`, `lnduction_time`) values
('test001', 9999, 0.55, '2022-06-01'),
('test001', 9999, 0.55, '2022-06-01');
select distinct `emp_name` from sys_emp where emp_name='test001';

9.2、排序分页

# 排序
select emp_sal from sys_emp order by emp_sal; -- 默认：升序，从低到高（asc）
select emp_sal from sys_emp order by emp_sal desc; -- 降序，从高到低（desc）

# 分页
select * from sys_emp limit 1; -- 查询：第一页第一条数据
select * from sys_emp limit 0,10; -- 查询：从索引 0 开启查询 10 条数据
select * from sys_emp limit 3,10; -- 查询：从索引 3 开启查询 10 条数据
select * from sys_emp limit 0,5; -- 查询第一页5条记录：(1 - 1)*5, 5
select * from sys_emp limit 5,5; -- 查询第二页5条记录：(2 - 1)*5, 5
select * from sys_emp limit 10,5; -- 查询第三页5条记录：(3 - 1)*5, 5

9.3、分组查询

9.3.1、group by

# 创建分组测试表 sys_group_by
drop table if exists sys_group_by;
create table sys_group_by
(
	`id` int primary key auto_increment unique comment '主键',
	`name` varchar(55) comment '姓名',
	`job_name` varchar(55) comment '工作岗位',
	`sal` double comment '工资'
);
insert into sys_group_by(`name`, `job_name`, `sal`) values
('燕子01', 'Q-zxc', 8888.88),
('燕子02', 'Q-zxc', 9999.88),
('燕子03', 'Q-zxc', 7777.88),
('燕子04', 'Q-zxc', 6666.88),
('燕子05', 'Q-zxc', 5555.88),
('Ruanjia-01', 'W-vbn', 1111.88),
('Ruanjia-02', 'W-vbn', 2222.88),
('Ruanjia-03', 'W-vbn', 3333.88),
('Ruanjia-04', 'W-vbn', 4444.88),
('Ruanjia-05', 'W-vbn', 1234.88);
select * from sys_group_by;

# group by（分组）
select job_name, sum(sal) as 部门总工资 from sys_group_by group by job_name; -- 按部门分组：统计部门总工资
select job_name, max(sal) as max_sal from sys_group_by group by job_name order by max_sal asc; -- 按部门分组：统计部门总工资(按工资升序)

9.3.2、联合查询

特点：就是根据多个字段分组，得到统计结果！

# 联合查询
select `name`, `job_name`, max(`sal`) from sys_group_by group by `name`, `job_name`;

9.3.3、having

特点：对分组后的数据进一步过滤（having不能单独使用，必须联合group by一起使用）。

# having
select `name`, `job_name`, max(`sal`) as 最高工资 from sys_group_by group by `name`, `job_name` having 最高工资 > 5000;

9.3.4、SQL执行流程

流程：select -> from -> where -> group by -> having -> order by（SQL执行流程是有规定的）

9.4、连表查询

9.4.1、创建测试表

-- sys_emp（员工表）
drop table if exists sys_emp;
create table sys_emp
(
	`emp_id` int primary key auto_increment unique comment '员工编号',
	`emp_name` varchar(20) not null comment '员工姓名',
	`emp_job` varchar(20) comment '员工岗位',
	`emp_mgr` varchar(20) comment '上级领导',
	`emp_hire_date` date comment '入职时间',
	`emp_comm` int comment '奖金',
	`emp_deptno` int comment '部门编号'
);

-- sys_salgarde（工资等级表）
drop table if exists sys_salgrade;
create table sys_salgrade
(
	`grade_id` int primary key auto_increment unique comment '主键',
	`losal` double comment '开启范围',
	`hisal` double comment '结束范围'
);

-- sys_dept（部门表）
drop table if exists sys_dept;
create table sys_dept
(
	`dept_id` int primary key auto_increment unique comment '部门编号',
	`dept_name` varchar(30) comment '部门名称',
	`dept_loc` varchar(30) comment '部门地址'
);

9.4.2、内连接 ~ 非等值连接

特点：连接条件不是等量关系，两张表是平等的！

# 找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？
select 
	se.emp_name, se.emp_sal, ss.grade_id 
from 
	sys_emp se 
inner join 
	sys_salgrade ss 
on 
	se.emp_sal between ss.losal and ss.hisal; # 非等值连接（条件不是一个等量关系）

9.4.3、内连接 ~ 等值连接

特点：连接条件是等量关系，两张表是平等的！

# （sql199语法）查询每个员工所在部门名称，显示员工名和部门名
select
	se.emp_name, sd.dept_name
from 
	sys_emp se inner join sys_dept sd on se.dept_id=sd.dept_id; # 连接条件是等量关系

9.4.4、内连接 ~ 自连接

-- 内连接（自连接）
select 
	se1.emp_name 员工名, se2.emp_name 领导名 
from 
	sys_emp se1 
join 
	sys_emp se2 
on 
	se1.emp_mgr = se2.emp_id; # 自连接（自己连接自己）

9.4.5、左外连接（左边为主表）

-- 外连接（左外连接（左边为主表）/右外连接（右边为主表））
select 
	e.emp_name, d.dept_name 
from 
	sys_emp e
left join 
	sys_dept d
on 
	e.dept_id=d.dept_id; # 左外连接（左边为主表）

9.4.6、右外连接（右边为主表）

-- 外连接（左外连接（左边为主表）/右外连接（右边为主表））	
select 
	e.emp_name, d.dept_name 
from 
	sys_emp e
right join 
	sys_dept d
on 
	e.dept_id=d.dept_id; # 右外连接（右边为主表）

9.4.7、多表连接

特点：理清楚连接条件！

-- 多表连接
# 找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？
select 
	e.emp_name, e2.emp_name, d.dept_name, e.emp_sal, s.grade_id
from 
	sys_emp e
join 
	sys_dept d
on 
	e.dept_id=d.dept_id 
join 
	sys_salgrade s
on 
	e.emp_sal between s.losal and s.hisal
left join 
	sys_emp e2
on
	e.emp_mgr=e2.emp_id;