MySQL学习笔记

笔记整理 数据库 MySQL SQL
数据库
发布日期:   2024-02-20
文章字数:   13.3k
阅读次数:  

MySQL学习笔记

Hello大家好呀，我是林风。好长时间没复习过MySQL了，这次就来整理下先前学过的MySQL的笔记吧(●ˇ∀ˇ●)

Tips：

本文较长，加载时间可能也会比较长

推荐使用PC端查看，因为有目录

理论知识

enmm，我就不从头整理笔记了，像什么MySQL的历史、什么是数据之类的，感觉完全浪费时间，只整理一些比较有必要的理论知识吧

一些专业性的英语单词与缩写

缩写 英文 中文 备注
DB database 数据库 存储数据的仓库
其本质就是一个文件管理
就是以文件的形式将数据保存到电脑的磁盘中 。
DBMS database manage system 数据库管理系统 管理及维护数据库的大型软件
MySQL就是数据库管理系统软件
SQL Structured Query Language 结构化查询语言 是一种数据库查询和程序设计语言
同时也是目前使用最广泛的关系型数据库操作语言
NoSQL Not Only SQL 非关系型数据库 格式灵活、使用灵活、速度快、高拓展性
学习成本高、不适合做复杂查询、不适合持久海量数据存储
DQL Data Query Language 数据查询语言 主要用于数据的查询
DML Data Manipulation Language 数据操作语言 主要用于对数据 增加、修改和删除的操作
DDL Data Definition Language 数据定义语言 主要用针对是数据库对象
（数据库、表、索引、视图）
进行创建, 修改和删除操作
TCL Transaction Control Language 事务控制语言 用于数据库的事务管理
DCL Data Control Language 数据控制语言 用来授予或回收访问数据库的权限

数据类型

MySQL的数据类型有很多，细说的话又是一大坨没用的东西，这里只是简单的列举几个常用的吧。

数据类型 经常用来干啥
int 存储数值
double 存储浮点数
text 存储长文本
varchar 存储字符串
datetime 存储时间和日期
longtext 存储富文本

表关系

一对多

  • A表中的一条数据再B表中有N条数据相关，繁殖B表中的一条数据在A表中只有一条数据相关
  • 在设计表的时候，在B表中新家列存储A表中的主键字段值。
  • 注意：不一定非要使用外键约束

多对多

  • A表中的一条数据在B表总有N条数据相关，反之B表中的一条数据在A表中有B条数据相关
  • 在设计表的时候专门创建中间表，存储A表和B表的关联数据

一对多

  • A表中的一条数据在B表中有一条相关，反之亦然
  • 一般来讲设计为一张表，如果特殊需要

Linux安装MySQL

有人可能会问，为啥不写Windows安装MySQL的？

enmmm，林风搅得莫得必要，因为Windows的都是图形化的，非常简单，英语稍微好点的就可以根据提示装好了，英语不好的也可以拍照翻译啥的就能看懂。(●’◡’●)

本文将列出两种Linux的安装MySQL方式，一种是rpm安装，另一种则是使用docker

rpm方式安装MySQL

  1. 下载镜像库

    wget https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm

  2. 安装镜像库

    rpm -ivh mysql80-community-release-el8-1.noarch.rpm

  3. 安装MySQL服务

    yum install -y mysql-server

  4. 设置开机自启动

    systemctl enable mysqld.service

  5. 启动MySQL

    systemctl start mysqld.service

  6. 修改root密码

    # 链接mysql，默认密码为空，回车即可
mysql -u root -p
    -- 使用mysql数据库
use mysql
-- 这里的linfeng为root密码，可以自行定义。
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'linfeng';

  7. 设置root允许远程连接

    -- 修改root的客户端访问模式是任意
update user set host='%' where user='root';
-- 刷新权限
flush privileges;
-- 退出MySQL命令行
exit

  8. 重启MySQL服务

    systemctl restart mysqld

  9. 关闭防火墙

    systemctl stop firewalld

  10. 设置防火墙开机不启动

    systemctl disable firewalld

  11. 然后即可在你客户端进行远程访问了

docker方式安装MySQL

docker方式安装的话非常简单，就是有一些为docker的命令，这里就不过多解释了，我尽量以简短易理解的注释写上去。

  1. 下载MySQL镜像

    docker pull mysql

  2. 创建容器并启动

    # docker run --name 自定义名称 -p 外部端口:内部端口  [-e 参数名=参数值] -d  镜像名:tag
docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:latest

    Tips：

    这里的latest表示最新版本，上面第一步中默认下载最新版，若想下载指定版本可以先：

    docker search mysql

    然后pull下载指定版本，例如：docker pull mysql:5.7

搞定了，后面就可以直接连接数据库了

其它的docker命令这里不多说了，到时候我单独的整理一个docker的文章吧

客户端工具

  • navicat：自行搜索，收费，不过多解释
  • dbeaver：免费

DDL

别问DDL是啥，你要想问的话就代表没认真看上面的理论知识，回去看去看完再回来(￢︿￢☆)

数据库基本操作

-- 查看所有的数据库
show databases;

-- 创建新数据库
create database database_name;

-- 使用数据库
use database_name;

-- 删除数据库
drop database database_name;

创建数据库表

格式

create table 表名(
	字段1	数据类型 comment '字段描述，类似java中的注释文本',
    字段2 数据类型(长度)
);
-- 注意：最后一个字段不要加逗号，可以给字段的数据类型指定长度。其中的comment注释可以省略

练习

-- 创建数据库、表
create table table_name(
		id int comment "这是id，唯一标识",
		field1 varchar(10),
		field2 int,
		primary key (id)
);

查看数据库表

-- 显示当前数据库的所有表
show tables;

-- 显示某个表的结构 desc 表名
desc lf_login;

-- 查看某个完整的建表语句 show create table 表名
show create table table_name;

删除数据库表

-- 删除数据库表 drop table 表明。如果表格不存在，提示错误信息，表格不存在
drop table table_name;

-- 删除表格时，判断，动态执行删除表格，表格存在时才删除
drop table if exists table_name;

-- 数据库.表名可删除指定数据库的某表格
drop table if exists database_name.table_name;

修改数据库表

修改表名

-- 修改数据库表
-- 语法格式：rename table 旧表名 to 新表名
rename table old_table_name to new_table_name;

-- 显示所有表
show tables;

向表中添加字段

-- 向表中添加字段
-- 语法格式：alter table 表明 add 字段名 字段类型
alter table table_name add field3 varchar(10); -- 添加到最后
alter table table_name add field0 varchar(10) first; -- 添加到第一个
alter table table_name add field4 varchar(10) after field3; -- 添加到field3后面

-- 查看表结构
desc table_name;

修改表中字段

-- 修改表中字段
-- 语法格式：alter table 表名 modify 字段名 字段类型；（修改字段类型和长度）、
alter table table_name modify field0 varchar(80);

-- 查看表结构
desc table_name;

-- 语法格式：alter table 表名 change 旧字段名 新字段名 类型(长度); (修改字段名 )
alter table table_name change field0 field varchar(6);

删除表中的字段

-- 语法格式：alter table 表名 drop 字段名;
alter table table_name drop field;

DML

DML我就不多解释了，上面的理论知识中有讲到

插入数据

-- 语法格式：
-- 1. 插入部分或全部字段 insert into 表名(字段名1,字段名2,...,字段名n) values(值1,值2,...,值n);
-- 2. 插入全部字段 insert into 表名 values(值1,值2,...,值n);

-- 方式一：插入全部字段并写出全部字段
insert into table_name (id, field) values(1, "aaa");

-- 方式二：插入全部字段不写字段
insert into table_name values (2, "bbb");

-- 方式三：插入指定字段其它字段不管
insert into table_name (id) values(3);

-- 方式四：插入全部字段的多个数据
insert into table_name values(4, "ccc"),(5, "ddd");

-- 方式五：插入指定字段的多个数据
insert into table_name (id) values(6), (7);

-- 方式六：使用value插入
insert into table_name value(8, "eee");

​ 注意事项：

  1. 值与字段必须要对应，个数相同、数据类型相同
  2. 值的数据大小，必须在字段指定的长度范围内
  3. varchar char date类型的值必须使用单引号，或者双引号包裹。最好不用双引号，SQL标准要求中是单引号。
  4. 如果要插入空值，可以忽略不写，或者写null
  5. 如果插入指定字段的值，必须要上写列名
  6. 字符串日期类型支持 “2000-12-23” “2000/12/23” “2000.12.23” 或数字 20001223 。不建议使用数字。

修改数据

-- 语法格式
-- update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;
-- update 表名 set 字段1=值1,字段2=值2,...,字段n=值n; -- 慎用：不加条件会修改所有的数据

-- 更新id为1数据的字段
update table_name set field = "asd" where id = 1;
-- 更新id为9数据的记录，将id改为10且field改为dsa
update table_name set id = 10, field = "dsa" where id = 9;
-- 更新所有记录的field为sss
update table_name set field = "sss";

删除数据

-- 删除数据
-- 语法格式
-- delete from 表名 where 条件;
-- delete from 表名; -- 慎用：删除表中的所有数据
-- truncate table 表名; -- 慎用：删除表中的所有数据

-- 删除 表中所有的数据
delete from table_name;

-- 彻底删除表中所有的数据，无法回滚，不推荐
truncate from table_name;

-- 删除field为eee的数据
delete from table_name where field = "eee";

truncate 和 delete的对比：

  1. 这两种操作都可以删除表中的数据
  2. delete为数据操作语言DML；truncate 为数据定义语言DDL。
  3. delete操作是将表中所有记录一条一条删除直到删除完；truncate 操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，truncate 操作的效率更高。
  4. delete操作可以回滚；truncate 操作会导致隐式提交，因此不能回滚（后面会讲解事务的提交和回滚）。
  5. delete操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“affected rows：4”）；truncate 操作不会返回已删除的行量，结果通常是“affected rows：0”。delete操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；truncate 操作会重新从1开始自增。

表的约束

约束分类

约束名 关键字 作用
主键 primary key 不可重复, 不能为空，即唯一 + 非空
唯一 unique 不可重复
非空 not null 不能为空
默认值 default 赋予默认值
检查(Mysql8提供) check 取值范围
外键 foreign key 表与表之间的关系

主键约束

  • 主键约束primary key
  • 非空
  • 不可重复

添加主键约束

-- 语法格式：字段名 字段类型 primary key

-- 删除表
drop table table_name;

-- 方式一：在创建表时直接设置约束
create table table_name(
	id int primary key,
	field varchar(10),
	field2 varchar(10)
);

-- 方式二：在创建表时，指定某个字段为主键约束
create table table_name(
	id int,
	field varchar(10),
	field2 varchar(10),
	primary key(id)
);

-- 方式三：创建表后指定主键约束
create table table_name(
	id int,
	field varchar(10),
	field2 varchar(10)
);
alter table table_name add primary key(id);

-- 查看表结构
desc table_name;

哪些字段可以作为主键

  • 通常不会针对业务去设计主键, 每张表都设计一个主键id, 并且只能有一个主键。如果针对业务，则会让主键含有特定的意义，在一些特殊操作下，会有很大的局限性。比如：银行卡号是唯一且非空的，但是不能作为主键，因为可能会丢失补办，这是卡号很可能会变化。
  • 主键是给数据库和程序使用, 所以主键最好没有意义没有关系, 只要能保证不重复就好, 比如一个连续的递增自然数(1,2,3,4…)就可以作为主键。

删除主键约束

-- 使用DDL，删除主键约束
alter table table_name drop primary key;

-- 删除唯一约束
-- 在此之前已经添加了field为not null约束
alter table table_name modify field varchar(10);

-- 查看表结构
desc table_name;

主键自增

auto_increment

-- 语法格式: 关键字 auto_increment	 表示自增长(字段类型必须为整数类型)

-- 创建表并设置主键和主键自增
create table table_name(
	id int primary key auto_increment, -- 主键自增
	field varchar(10),
	field2 varchar(10)
);

-- 测试自增
insert into table_name values 
	(default, "aaa", "asd"),
	(default, "bbb", "dsa"),
	(default, "ccc", "cas");
	
select * from table_name;

非空约束

not null

-- 语法格式: 字段名 字段类型 not null

-- 创建表并设置非空约束
create table table_name(
	id int primary key auto_increment,
	field varchar(10) not null, -- 非空约束
	field2 varchar(10)
)

-- 查看表结构
desc table_name;

-- 测试非空结构
insert into values(default, null, "aaa"); -- 会报错

唯一约束

unique

-- 语法格式： 字段名 字段类型 unique
create table table_name(
	id int primary key auto_increment,
	field varchar(10) unique, -- 唯一约束
	field2 varchar(10)
)

-- 查看表结构
desc table_name;

默认值约束

default '值'

-- 语法格式： 字段名 字段类型 default '值'
create table table_name(
	id int primary key auto_increment,
	field varchar(10) default "aaa",
	field2 varchar(10)
)

-- 测试数据
insert into table_name (field2) value("sss");
select * from table_name;

-- 查看表结构
desc table_name;

检查约束

语法格式：

字段名 字段类型 check(字段='值' or 字段='值')
字段名 字段类型 check(字段>n or 字段<m)
字段名 字段类型 check(字段>n and 字段<m)

案例：

-- 检查约束
create table table_name(
	id int primary key auto_increment,
	-- field只能存sss或者aaa
	field varchar(10) check(field = "sss" or field = "aaa"),
	-- 存储10>field2>20的值
	field2 int check(field2 > 10 and field2 < 20)
);

DQL

上面理论知识中有DQL的解释，这里不解释了

数据准备

因为DQL是查询嘛，查询的话就需要数据了，这里我们就以员工表为例，提前准备以下数据吧

创建表

创建一个员工表格，需求如下：

表格名称： emp

字段名 字段含义 字段类型
eid 员工主键 int
ename 姓名 varchar(20)
sex 性别，check约束，‘男’或‘女’ char(1)
salary 薪资 double(7,1)
hire_date 入职时间 date
dept_name 部门名称 varchar(20) 
-- 创建员工表
create table emp(
	eid int primary key,
	ename varchar(20),
	sex char(1) check (sex='男' or sex='女'),
	salary double(7,1),
	hire_date date,
	dept_name varchar(20)
);

插入若干测试数据

insert into emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部'); 
insert into emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
insert into emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部'); 
insert into emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部'); 
insert into emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部'); 
insert into emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部'); 
insert into emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
insert into emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部'); 
insert into emp VALUES(9,'吴承恩','男',20000,'2000-03-14',null); 
insert into emp VALUES(10,'孙悟饭','男', 10,'2020-03-14','财务部');
insert into emp VALUES(11,'兔八哥','女', 300,'2010-03-14','财务部');
insert into emp VALUES(12,'Tom','男', null,'2010-03-14','财务部');

简单查询

查询不会对数据库中的数据进行修改, 只是一种显示数据的方式。

语法格式：

select 字段名1,字段名2,... from 表名;

案例1: 查询emp中所有的数据

-- 将要查询的字段全部列出
select eid ,ename, sex, salary, hire_date, dept_name from emp;

-- 查询所有的字段可以使用 * , * 代表所有的字段。不推荐使用。
select * from emp;

案例2: 查询emp表中所有的记录, 只显示eid和ename字段

查询时，可以根据所需只查询表格中的部分字段。

select eid, ename from emp;

案例3: 查询所有的员工信息, 将字段名显示为中文

查询结果中显示的并不是表格的字段名称，而是查询结果的列名，此名称是可以自定义的，称为查询字段别名。注意：如果查询字段别名是中文或包含“空格”，则一定要用英文半角单/双引号括起来！

select 
	eid as "编号",      -- as可以省略
	ename as "姓名",
	sex as "性别",
	salary as "薪资",
	hire_date as "入职日期",
	dept_name as "部门名称"
from emp;

案例4: 查询部门名称

如果需要对查询结果的重复数据做排重处理，使用关键字 distinct ，此关键字是把重复的行合并成一行，一定要记住，是重复的行，不是 distinct 关键字后的列。

-- 查询所有的部门(出现重复部门)
select dept_name from emp;

-- 使用去重查询 关键字 distinct
select distinct dept_name from emp;

-- 验证排重范围 只有当查询一行记录一样时才合并去重
select distinct salary, dept_name from emp;

将所有的员工薪资加1000显示

查询语法中，可以在select 子句后的字段列表中做数学计算，支持的运算符有 + - * / % 。（加减乘除模） MySQL数据库中的字段类型并非强约束类型，

-- 支持算术运算符 + - * / % 的运算
select eid, ename, salary, salary+1000 from emp;

条件查询

  • 如果查询语句中没有设置条件, 就会查询所有的行信息
  • 在实际应用中, 通常会指定查询的条件, 对记录进行过滤
  • 作用：where条件用来筛选行，符合要求的记录才会被查询，结合select子语句查询列
  • 格式：select 列 from 表明 where 筛选条件

语法格式：

select 列名 from 表名 where 条件;

比较运算符

运算符 说明
> < <= >= = <> != 大于 小于 小于等于 大于等于 等于 不等于
between…and… 显示在某一区间（闭区间）的值，包含开始和结束的值。
in(集合)
not in(集合)		 集合表示多个值，使用逗号分隔，例如name in (悟空,八戒); in中的每个数据都会作为一次条件，
只要满足条件就会显示
like ‘%张%’ 模糊查询
is null | is not null 查询某一列是否为null值，注意，不能写 = null 或 != null

逻辑运算符

运算符 说明
and && 多个条件同时成立
or || 多个条件任一成立
not 取反

案例1：精准查询

-- where 条件查询
-- 比较运算符
-- 1 查询员工姓名为黄蓉的员工信息 
select * from emp where ename = "黄蓉"; -- = 表示等值比较
-- 2 查询薪水价格为5000的员工信息
select * from emp where salary = 5000;

-- 3 查询薪水价格不是5000的所有员工信息 
select * from emp where salary != 5000; -- != 和 <> 都表示不等于
select * from emp where salary <> 5000;

-- 4 查询薪水价格大于6000元的所有员工信息 
select * from emp where salary > 6000;

-- 5 查询薪水价格在5000到10000之间的员工信息 
select * from emp where salary >= 5000 and salary <= 10000;
select * from emp where salary between 5000 and 10000;

-- 6 查询薪水价格是3600或7200或者20000的员工信息
select * from emp where salary = 3600 or salary = 7200 or salary = 20000;
select * from emp where salary in (3600, 7200, 20000);

案例2：模糊查询

模糊查询，通配符 。语法结构：like "通配符语句"

通配符 说明
% 表示匹配任意多个字符
_ 表示匹配任意一个字符 
-- 模糊查询
-- 1 查询含有'八'字的员工信息
select * from emp where ename like "%八%";

-- 2 查询以'孙'字开头的员工信息 
select * from emp where ename like "孙%";

-- 3 查询第二个字为'兔'的员工信息 
select * from emp where ename like "_兔%";

案例3：空值处理

再强调一次，null 在MySQL中是一个很特殊的值，它很任何数据都不匹配，即不相等，也不不等，在处理 null 值时，一定要用 is null 或 is not null 。

-- 空值处理
-- 1 查询没有部门的员工信息 ， dept_name字段的值是null
select * from emp where dept_name is null;

-- 2 查询有部门的员工信息 ， dept_name字段的值不是null
select * from emp where dept_name is not null;

排序

在查询时，可能需要对查询的结果针对某数据最排序处理，这时可以通过order by子语句实现, 此子句只对查询出的结果进行排序(排序只是显示效果), 并不会影响表格中真实的数据的存储顺序。

语法格式：

select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc  升序(默认)
-- desc 降序

单列排序

只按照某一个字段进行排序。

案例: 查询所有的员工信息, 使用salary进行排序。

-- 排序
-- 升序排序(默认 asc) 从小到大
select * from emp order by salary;

-- 降序排序(desc) 从大到小
select * from emp order by salary desc;

组合排序

同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推。

案例: 查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序。

-- 组合排序
select * from emp order by salary asc, hire_date desc;

函数

MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。函数只是对查询结果中的数据进行处理，不会改变数据的值。

MySQL中的函数主要分为单行函数和多行函数两大类:

  1. 单行函数:
    单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。
    常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。
    使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。

  2. 多行函数:
    我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。
    多行函数会忽略null空值。
    多行函数也称为分组函数, 聚合函数。
    使用多行函数，是对多行某一列的值进行计算，然后返回一个单一的值

注意：MySQL数据库中提供了大量的函数，我们很难全部记住，也不会在工作中全部使用。下述文档中列举了MySQL中的大部分函数，表格中加粗的函数为常用函数。

单行函数

字符串函数
函数 描述
CONCAT(str1, str2, ···, strn) 将str1、str2···strn拼接成一个新的字符串
INSERT(str, index, n, newstr) 将字符串str从第index位置(从1开始计算)开始n个字符替换成字符串newstr
LENGTH(str) 获取字符串str的长度(字节)
LOWER(str) 将字符串str中的每个字符转换为小写
UPPER(str) 将字符串str中的每个字符转换为大写
LEFT(str, n) 获取字符串str最左边的n个字符
RIGHT(str, n) 获取字符串str最右边的n个字符
LPAD(str, n, pad) 使用字符串pad在str的最左边进行填充，直到长度为n个字符为止
RPAD(str, n, pad) 使用字符串pad在str的最右边进行填充，直到长度为n个字符为止
LTRIM(str) 去除字符串str左侧的空格
RTRIM(str) 去除字符串str右侧的空格
TRIM(str) 去除字符串str左右两侧的空格
REPLACE(str,oldstr,newstr) 用字符串newstr替换字符串str中所有的子字符串oldstr
REVERSE(str) 将字符串str中的字符逆序
STRCMP(str1, str2) 比较字符串str1和str2的大小
SUBSTRING(str,index,n) 获取从字符串str的index(从1开始)位置开始的n个字符

  • 拼接字符串 concat(str1, str2, str3....)

    -- 小测试
select concat("aaa", "bbb", "ccc") as test;

    结果为：aaabbbccc

    练习题：

    -- 1 字符串拼接 查询emp表, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat("编号：", eid) as id,
			concat("姓名：", ename),
			concat("性别：", sex)
			from emp;

  • 替换字符串replace(str, oldStr, newStr)

    -- 小测试
select replace("hello world", "hello", "hi");

    结果为：hi world

    练习题：

    -- 2 字符串替换 查询部门名, 并将 市场部 替换成 销售部
select ename, dept_name, replace(dept_name, "市场", "销售") as dept_replace_name from emp;

  • 获取字符串长度length(str)

    -- 小测试
select length("aaa") as aaa; -- 3
select length(trim(" hel lo ")) as hello; -- 6
select length("你干嘛") as 你干嘛; -- 9

    练习题：

    -- 3 计算字符串长度 查询emp表, 显示ename的长度
select 	eid, ename, length(ename) from emp;

  • 转为大写upper(str)和小写lower(str)

    -- 小测试
select upper("Hello World") as test; -- HELLO WORLD
select lower("Hello World") as test; -- hello world

    练习题：

    -- 4 英文大小写 查询emp表, 将 ename有英文的改为都是大写或小写
select eid, ename, upper(ename), lower(ename) from emp;

  • 提取字符串substring(str, startNum, num)

    -- 小测试
select substring("hello world", 1, 5); -- hello
select substring("hello world", 7, 5); -- world

    练习题：

    -- 5 查询emp表, ename只显示姓
select eid, ename, substring(ename, 1, 1), sex from emp;

数值函数

函数 描述
ABS(num) 返回num的绝对值
CEIL(num) 返回大于num的最小整数（向上取整）
FLOOR(num) 返回小于num的最大整数（向下取整）
MOD(num1, num2) 返回num1/num2的余数（取模）
PI() 返回圆周率的值
POW(num,n)/POWER(num, n) 返回num的n次方
RAND(num) 返回0~1之间的随机数，参数是种子，采用相同种子得到相同随机数，
但是多行时，随机数会变化
ROUND(num, n) 返回x四舍五入后的值，该值保留到小数点后n位
TRUNCATE(num, n) 返回num被舍去至小数点后n位的值
SQRT(num) 返回num的平方根

MySQL提供了一张虚拟表，该表名为“dual”，是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。 在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表。

select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25) from dual;
select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25);
-- 绝对值
select abs(-3), abs(-3.1415926), abs(8); -- 3	3.1415926	8

-- 取整 ceil向上取整 floor向下取整
select ceil(3.9), ceil(3.2); -- 4 4
select floor(3.9), floor(3.2); -- 3 3

-- 取余
select mod(5, 3); -- 2

-- 获取圆周率
select pi(); -- 3.141593

-- 2的5次方
select pow(2, 5); -- 32

-- 返回25的平方根
select sqrt(25); -- 5

-- 会产生 [0-1) 的随机小数
select rand();
-- rand括号中的值比较特殊后面细说

如果使用相同的随机数种子值来生成随机数，那么结果将是相同的随机数。

mysql> select rand(10);
+--------------------+
| rand(10)           |
+--------------------+
| 0.6570515219653505 |
+--------------------+
1 row in set (0.00 sec)
 
mysql> select rand(10);
+--------------------+
| rand(10)           |
+--------------------+
| 0.6570515219653505 |
+--------------------+
1 row in set (0.00 sec)
 
mysql> select rand(5);
+---------------------+
| rand(5)             |
+---------------------+
| 0.40613597483014313 |
+---------------------+
1 row in set (0.00 sec)
 
mysql> select rand(5);
+---------------------+
| rand(5)             |
+---------------------+
| 0.40613597483014313 |
+---------------------+
1 row in set (0.00 sec)

注意：随机数种子相同，但是行不同，得到的随机数会不相同。

mysql> SELECT i, RAND(3) FROM t;
+------+------------------+
| i    | RAND(3)          |
+------+------------------+
|    1 | 0.90576975597606 |
|    2 | 0.37307905813035 |
|    3 | 0.14808605345719 |
+------+------------------+

日期与时间函数

函数 描述
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
SYSDATE(num) 返回该函数执行时的日期和时间，参数num为秒后的精度，最多6位
DAYOFYEAR(date) 返回日期date为一年中的第几天
WEEK(date)/WEEKOFYEAR(date) 返回日期date为一年中的第几周
DATE_FORMAT(date, format) 返回按字符串format格式化后的日期date。
%Y 4位年 %y 2位年 %M 月英文名 %m 月份
%d 月中的天(01-31) %e 月中的天（1-31）
%H 24小时制 %h 12小时制 %i 分钟 %s 秒 %f 微秒(6位)
DATE_ADD(date, INTERVAL expr unit) /ADDDATE(date, INTERVAL expr unit) 返回date加上一个时间间隔后的新时间值
DATE_SUB(date, INTERVAL expr unit) /SUBDATE(date, INTERVAL expr unit) 返回date减去一个时间间隔后的新时间值
DATEDIFF(date1, date2) 返回起始日期date1与结束日期date2之间的间隔天数 
-- 时间与日期
select curdate(), curtime(), now(), sysdate(), sysdate(6);
-- 2023-07-26	14:56:39	2023-07-26 14:56:39	2023-07-26 14:56:39	2023-07-26 14:56:39.827753

流程控制函数

间隔类型 描述
IF(condition, t, f) 如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2) 如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2) 如果value1等于value2，则返回null，否则返回value1
CASE value WHEN [value1] THEN result1 [WHEN [value2] THEN result2 …] [ELSE result] END 如果value等于value1，则返回result1，···，否则返回result
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 …] [ELSE result] END 如果条件condition1为真，则返回result1，···，否则返回result

例子1：

-- 1 查询emp表所有数据, 薪资 >= 10000 高工资  其他 低工资
select eid, ename, salary, if(salary >= 10000, "高工资", "低工资") from emp;

-- 2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, salary, ifnull(salary, 0)*12+30000 as 年薪 from emp; -- 需要考虑null

例子2：

-- 3 查询emp表所有数据, 薪资 >=3000 加把劲  >=5000 加油哦  >=9000 坚持住 >= 15000 优秀  其他 不及格
select eid, ename, salary,
			case
					when salary >= 15000 then '优秀'
					when salary >= 9000 then '坚持住'
					when salary >= 5000 then '加油哦'
					when salary >= 3000 then '加把劲'
					else '努力奋斗吧，骚年'
			end as 评价
from emp;

其它函数

函数 描述
DATABASE() 返回当前数据库名
VERSION() 返回当前MySQL的版本号
USER() 返回当前登录的用户名
INET_ATON(IP) 返回IP地址的数字表示
INET_NTOA(num) 返回数字代表的IP地址
PASSWORD(str) 实现对字符串str的加密操作
FORMAT(num, n) 实现对数字num的格式化操作，保留n位小数
CONVERT(data, type) 实现将数据data转换成type类型的操作 
select database() as "当前数据库名",
				user() as "当前登录的用户名",
				version() as "当前MySQL版本号",
				inet_aton("192.168.10.1") as "返回IP地址的数字表示",
				inet_ntoa(3232238081) as "返回数字代表的IP地址";

多行函数

  • 多行函数会忽略null空值。
  • 多行函数也称为分组函数, 聚合函数。
多行函数 作用
count(字段) 统计指定列不为null的记录行数
sum(字段) 计算指定列的数值和
max(字段) 计算指定列的最大值
min(字段) 计算指定列的最小值
avg(字段) 计算指定列的平均值 
-- 1 查询员工的总数
-- 根据某个字段查询，聚合函数会忽略null, 需要注意为null的字段
select count(eid) from emp; -- 12
-- 所有字段匹配查询
select count(*) from emp; -- 12
-- 增加一列
select 1 from emp; -- 1 1 1 1...
-- 效率更高推荐使用
select count(1) from emp; -- 12

-- 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ... 
select 
		sum(salary) as "总薪水",
		max(salary) as "最高薪水",
		min(salary) as "最小薪水",
		avg(salary) as "平局薪水"
from emp;

-- 3 查询薪水大于4000员工的个数 
select count(1) from emp where salary > 4000;

-- 5 查询部门为'市场部'所有员工的平均薪水
select avg(salary) from emp where dept_name = "市场部";

-- 6 查询部门的个数，去重
select count(distinct dept_name) from emp; -- 3

分组

分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组。

语法要求：

select子句后，只能有分组(group by)子句的分组字段或被聚合函数处理的值。 虽然在MySQL中，select子句后的可查询显示内容没有严格的限制，但是此时的查询结果是没有意义的，所以大家不要写错了，不要觉得MySQL可以运行，自己写的SQL就没有问题。

语法格式：

select 分组字段或聚合函数 from 表名 group by 分组字段 [having 条件];

基础演示即流程分析

需求: 通过性别分组.

select * from emp group by sex; -- 能正确查到结果，但是没有意义
select sex from emp group by sex; -- 正确的操作

注意事项:

  • 分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作
  • 查询其他字段没有意义

案例1

-- 1 查询每个部门的名称
select dept_name from emp group by dept_name;

-- 2 查询每个部门名称及部门的平均薪资 
select dept_name, avg(salary) from emp group by dept_name;

-- 3 查询每个部门名称及部门的平均薪资, 部门名称不能为空
select dept_name, avg(salary) from emp where dept_name is not null group by dept_name;

案例2

-- 查询每个部门名称及部门的平均薪资 , 只显示平均工资在4000以上的 
select dept_name, avg(salary) from emp where avg(salary) > 4000 group by dept_name;
-- Invalid use of group function 报错

分析:

  1. 需要在分组后, 对数据进行过滤, where的作用是在分组前过滤
  2. select语句的执行顺序
    from从表格中查询 – where每行数据的筛选条件 – group by对筛选后的数据集分组 – having分组后的筛选 –- select查询需要的列 – order by对查询结果排序
  3. 分组操作中的having子语句, 适用于对分组后的数据进行过滤的, 作用类似于where
select dept_name, avg(salary) from emp group by dept_name having avg(salary) > 4000;

where和having的区别：

过滤方式 特点
where 分组之前的过滤，后边不能写多行函数
having 分组之后的过滤，后边可以写多行函数

案例3

-- 1 统计每个部门中的最小工资, 列出最小工资小于4000的部门名称及最小薪资
select dept_name, min(salary) from emp group by dept_name having min(salary) < 4000;

-- 2 统计平均工资大于6000的部门名称 
select dept_name, avg(salary) from emp group by dept_name having avg(salary) > 6000;

-- 3 统计人数小于4个人部门的平均工资
select 
		dept_name as "部门",
		count(1) as "人数",  
		avg(salary) as "平均工资"
from emp group by dept_name 
having count(1) < 4;

-- 4 统计每个部门最高工资, 排除最高工资小于10000的部门
select 
		dept_name as "部门", 
		max(salary) as "最高工资"
from emp group by dept_name
having max(salary) < 10000;

limit

作用:

  • limit是限制的意思, 限制返回的查询结果的函数(通过limit，控制查询返回多少行数据)
  • limit 语法是 MySQL的方言, 用来完成分页。所以此语法在其他数据库管理系统中未必有效。

语法格式；

select 字段... from 表名 limit offset, length;

参数说明

  1. offset 起始行数脚标, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据
  2. length 返回的行数
-- 不指定跳过多少个，直接写表示从零开始查询
select ename, sex, dept_name from emp limit 5;
-- 跳过0个取五个
select ename, sex, dept_name from emp limit 0, 5;
-- 跳过三个取五个
select ename, sex, dept_name from emp limit 3, 5;

多表

笛卡尔积

  • 笛卡尔积 = 上一个表 * 另一个表 = 组合的总共查询结果个数 = 正确数据 + 垃圾数据
  • 通过链接条件将笛卡尔积进行筛选后拿到查询结果 = 正确数据
  • 超过两个表的笛卡尔积 = 上一个笛卡尔积进行筛选后拿到查询结果 * 另一个表…以此类推

外键约束

作用：保证A表中的数据在B表中一定有对应的数据

语法

  1. 新建表时添加外键约束

    -- 其中constraint 外键约束名称  是可以省略的，MySQL会自动提供一个名字
create table 从表(
    字段名 字段类型,
    外键字段名 字段类型,
	constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段)
)

  2. 为已创建好的表添加外键约束

    alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名) 
[on delete cascade # 从连操作，将关联的数据全删掉，不推荐
 on delete set null # 将原来的外键值设为null，不推荐
 ];

需求: 为employee表的 dep_id字段添加外键约束。

使用

创建表，先创建父表，再创建子表，外键是加在子表中的

创建表结构和外键约束并录入数据
-- 创建父表
create table department(
	dep_id int primary key auto_increment,
	dep_name varchar(20),
	dep_location varchar(20)
);
-- 插入父表数据
insert into department values(default, "教学部", "北京");
insert into department values(default, "财务部", "上海");
-- 创建子表：员工信息表
create table employee(
	eid int primary key auto_increment,
	ename varchar(20),
	age int,
	dep_id int,
    -- test为外键名，有外键名才可以被删除
	constraint test foreign key(dep_id) references department(dep_id)
);
-- 插入数据
insert into employee values(default, "linfeng", 22, 1);
-- 错误无法录入
insert into employee values(default, "zhangsan", 22, 100);
删除外键约束

语法格式:

alter table 从表名 drop foreign key 外键约束的名称;

需求: 删除employee表中的外键约束.

alter table employee drop foreign key emp_dep_fk;
外键约束的注意事项

  1. 从表的外键类型必须和主表的主键类型保持一致

  2. 添加从表数据时

    从表中添加的外键值, 必须在主表的主键中存在

  3. 删除和变更数据主表数据时

    先删除从表中的数据或将外键设置为null, 再删除主表中的数据

    -- 删除部门表中主键为1的部门信息
delete from department where dep_id = 1;
-- 报错信息如下
-- Cannot delete or update a parent row: a foreign key constraint fails

  4. 通过navicat设置外键约的束变更和删除的级联操作

使用说明:

选项 作用
RESTRICT(默认) 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
No action 同RESTRICT, 都是立即检查外键约束
CASCADE 在主表上update/delete记录时，同步update/delete掉从表匹配的记录
SET NULL 在主表上update/delete记录时，将子表上匹配记录的列设为null 注意: 子表的外键列不能为not null

多表查询

需要的数据分散在多张表中存储，考虑使用多表联合查询

数据准备

  1. 创建分类表与商品表

    create database bjsxt04;
use bjsxt04;
-- 分类表 (一方 主表)
create table category (
	cid varchar(32) primary key,
	cname varchar(50)
);
-- 商品表 (多方 从表)
create table products(
	pid varchar(32) primary key,
	pname varchar(50),
	price int,
	flag varchar(2),  -- 是否上架标记为：1表示上架、0表示下架
	cid varchar(32),
	-- 添加外键约束
	foreign key (cid) references category (cid)
);

  2. 插入数据

    #分类数据
 insert into category(cid,cname) values('c001','家电');
 insert into category(cid,cname) values('c002','鞋服');
 insert into category(cid,cname) values('c003','化妆品');
 insert into category(cid,cname) values('c004','汽车');
 
 #商品数据
 insert into products values('p001','小米电视机',5000,'1','c001');
 insert into products values('p002','格力空调',3000,'1','c001');
 insert into products values('p003','美的冰箱',4500,'1','c001');
 insert into products values('p004','篮球鞋',800,'1','c002');
 insert into products values('p005','运动裤',200,'1','c002');
 insert into products values('p006','T恤',300,'1','c002');
 insert into products values('p007','冲锋衣',2000,'1','c002');
 insert into products values('p008','神仙水',800,'1','c003');
 insert into products values('p009','大宝',200,'1','c003');

笛卡尔积

交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。

笛卡尔积不管是否匹配，都连接。没有实际意义，有理论意义 。理论上，任何多表查询结果都是笛卡尔积的子集。

笛卡尔积便于理解连接查询的原理。

多表查询分类

内连接查询

语法格式：

select 字段名 from 表1 cross join 表2;

例如：

-- 笛卡尔积：A表*B表
select * from products cross join category;
select * from products, category; 

-- 去除垃圾数据
select * from products as p, category as c where p.cid = c.cid;

-- 查询分类信息以及对应的商品信息， 只显示类别名称，商品名称，价格
select c.cname, max(p.pname), p.price from products as p, category as c where p.cid = c.cid group by c.cname order by p.price;

-- 显式：select 字段 from 表名 [corss] join 表名 on 等值连接条件
select * from products p cross join category c on p.cid = c.cid;

-- 查询鞋服分类下，价格大于500的商品名称和价格以及对应的分类名称
select p.pname,p.price, c.cname from products p join category c on p.cid = c.cid where c.cname = '鞋服' and p.price > 500;

自连接查询

自连接就是某张表自己和自己关联。

比如员工表，它可以认为是员工表，也可以认为是领导表。

所以我们可以通过关联，查询员工的信息及他的领导的信息。

-- 创建表
create table emp(
	empno int primary key auto_increment comment '员工编号',
	ename varchar(10) comment '员工姓名',
	mgr int comment '领导编号',
	hiredate date comment '入职日期',
	sal double(7,2) comment '月薪',
    foreign key (mgr) references emp(empno)
);

-- 插入数据
INSERT INTO `emp` VALUES (1, '宋江', null, '2021-12-28', 9000.00);
INSERT INTO `emp` VALUES (2, '林冲', 1, '2021-10-08', 3500.00);
INSERT INTO `emp` VALUES (3, '李逵', 1, '2022-01-05', 2500.00);
INSERT INTO `emp` VALUES (4, '孙二娘', 2, '2021-12-30', 2000.00);
INSERT INTO `emp` VALUES (5, '时迁', 2, '2021-12-29', 7800.00);

-- 查询员工名称和上级领导的名称
select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;

非等值连接查询

非等值连接就是两张表进行关联的时候不是使用关联的id相等进行连接的。

-- 创建工资等级表
create table salgrade(
	grade char(1) primary key comment '等级',
	losal double comment '此等级的最低工资',
	hisal double comment '此等级的最高工资'
);

-- 插入数据
INSERT INTO `salgrade` VALUES ('A', 0, 3000);
INSERT INTO `salgrade` VALUES ('B', 3001, 8000);
INSERT INTO `salgrade` VALUES ('C', 8001, 10000);
INSERT INTO `salgrade` VALUES ('D', 10001, 50000);

-- 查询员工的工资和工资级别
select sal, grade from emp e, salgrade s where sal between s.losal and s.hisal;

外连接查询

通过指定的条件去匹配两张表中的数据，匹配上就显示, 匹配不上也可以显示，此时两张表格存在一个基准表格，基准表格的数据一定全部显示。

左外连接查询

使用 left outer join , outer 可以省略。左侧为基准表。

语法格式：

select 字段名 from 表1 left join 表2 on 条件;
-- 外连接：可以查询不匹配的数据
-- 左外连接：select 字段 from 表A(基准表) left join 表B on 链接条件
-- 查询商品分类及其商品信息，没有商品的分类信息也要
select * from category;
select * from products;
select * from category c left join products p on c.cid = p.cid;
右外连接查询

使用 right outer join, outer可以省略。右侧为基准表

语法格式：

select 字段名 from 表1 right join 表2 on 条件;

案例：

-- 右外连接：select 字段 from 表A right join 表B(基准表) on 链接条件
select * from products p right join category c on c.cid = p.cid;
全链接查询

所谓全外连接，就是左右两侧表格都是基准表，MySQL不支持全外连接。

假设有两张表，表1和表2。如果它们进行全外连接的话，结果=内连接的结果 + 表1中有但是表2中没有的 + 表2中有但是表1中没有的。语法如下：

select e.*, d.* from emp e full outer join dept d 
on e.dept_id=d.id;

例子：

-- 全外链接：MySQL不支持
select * from products p full outer join category c on c.cid = p.cid;

其它问题

对于使用navicat的小盆友们可能遇到过连接乱码的情况。这个玩意，林风曾经遇到过一次，后面就没遇到过了（可能是因为后面电脑没重装过MySQL的原因吧，反正服务器安装MySQL后远程连接没遇到过这种情况）。总之是加密规则的不同。

解决方法也很简单，一种是更新客户端（navicat），另一种是修改MySQL的加密规则

这里只演示第二种

首先进入MySQL，然后输入下面命令

-- 设置密码永不过期
alter user 'root'@'localhost' identified by '密码' password expire never;
-- 设置加密规则为mysql_native_password
alter user 'root'@'localhost' identified with mysql_native_password by '密码';

然后重新点击navicat测试，就可以惊喜的发现，呀？TNND终于可以用了。

综合练习

准备数据

-- 创建数据库
create database bjsxt05;

-- 切换到指定的数据库
use bjsxt05;

-- 创建部门表格
create table dept(
	deptno int primary key auto_increment comment '部门编号',
	dname varchar(10) comment '部门名称'
);

-- 插入部门数据
INSERT INTO dept VALUES (1, '开发部');
INSERT INTO dept VALUES (2, '人事部');
INSERT INTO dept VALUES (3, '行政部');
INSERT INTO dept VALUES (4, '财务部');
INSERT INTO dept VALUES (5, '公关部');

-- 创建员工表
create table emp(
	empno int primary key auto_increment comment '员工编号',
	ename varchar(10) comment '员工姓名',
	mgr int comment '领导编号',
	hiredate date comment '入职日期',
	sal double(7,2) comment '月薪',
	comm double(7,2) comment '奖金率',
	deptno int comment '部门编号',
    constraint emp_dept_fk foreign key(deptno) references dept(deptno)
);

-- 插入员工数据
INSERT INTO emp VALUES (1, '曹操', 0, '2021-12-28', 30000.00, 0.30, 3);
INSERT INTO emp VALUES (2, '吕布', 1, '2021-12-29', 20000.00, 0.20, 3);
INSERT INTO emp VALUES (3, '刘备', 0, '2022-01-12', 35000.00, 0.40, 3);
INSERT INTO emp VALUES (4, '关羽', 3, '2021-12-31', 16000.00, 0.20, 1);
INSERT INTO emp VALUES (5, '赵云', 3, '2021-08-09', 12000.00, 0.20, 2);
INSERT INTO emp VALUES (6, '张飞', 3, '2022-01-01', 8000.00, 0.20, 4);
INSERT INTO emp VALUES (7, '宋江', 0, '2021-12-31', 25000.00, 0.30, 3);
INSERT INTO emp VALUES (8, '李逵', 7, '2021-09-17', 8000.00, 0.20, 4);
INSERT INTO emp VALUES (9, '林冲', 7, '2022-01-22', 3000.00, 0.10, 4);
INSERT INTO emp VALUES (10, '孙二娘', 9, '2022-01-06', 7000.00, 0.20, 1);
INSERT INTO emp VALUES (11, '吴用', 7, '2021-12-27', 2500.00, 0.10, 2);
INSERT INTO emp VALUES (12, '贾宝玉', 0, '2021-12-30', 12000.00, 0.30, 3);
INSERT INTO emp VALUES (13, '元春', 12, '2022-01-01', 9000.00, 0.20, 4);
INSERT INTO emp VALUES (14, '惜春', 12, '2021-08-12', 3000.00, 0.10, NULL);
INSERT INTO emp VALUES (15, '迎春', 12, '2021-12-28', 4200.00, 0.10, 2);
INSERT INTO emp VALUES (16, '探春', 12, '2022-01-12', 9600.00, NULL, 1);
INSERT INTO emp VALUES (17, '王熙凤', 0, '2021-08-11', 18000.00, 0.20, 3);
INSERT INTO emp VALUES (18, '秦可卿', 0, '2022-01-20', 16000.00, NULL, 3);

-- 创建薪资等级表格
create table salgrade(
	grade char(1) primary key comment '等级',
	losal double comment '此等级的最低工资',
	hisal double comment '此等级的最高工资'
);

-- 插入薪资等级数据
INSERT INTO salgrade VALUES ('A', 0, 3000);
INSERT INTO salgrade VALUES ('B', 3001, 8000);
INSERT INTO salgrade VALUES ('C', 8001, 10000);
INSERT INTO salgrade VALUES ('D', 10001, 50000);

自己做的答案

我自己写的答案，结果一样，但是思路和文档的答案不一样，可以当作另一种思路给大家吧

-- SQL语句练习

-- 1 查询没有奖金，且工资小于15000的员工的姓名
select ename as "姓名", sal as "工资" from emp where sal < 15000 and (comm = 0 or comm is null);

-- 2 查询员工表中部门编号不是3或者工资是12000的员工信息
select ename, sal, deptno from emp where deptno != 3 or sal = 12000;

-- 3 查看员工表中涉及到了哪些部门
select distinct d.dname from emp as e join dept as d on e.deptno = d.deptno;

-- 4 查询员工的姓名和部门号和年薪，按年薪降序，按部门号升序
select ename, deptno, (sal+ifnull(comm,0))*12 as nx from emp order by nx desc, deptno;

-- 5 选择工资不在8000到17000的员工的姓名和工资，按工资降序
select ename, sal from emp where sal < 8000 or sal > 17000 order by sal desc;

-- 6 查询名字中包含春的员工信息，按照工资降序
select * from emp where ename like "%春%" order by sal desc;

-- 7 查询公司员工工资的最大值、最小值、平均值(保留两位小数)、总和
select max(sal), min(sal), round(avg(sal), 2), sum(sal) from emp;

-- 8 查询部门编号为3的员工个数
select count(1) from emp where deptno = 3;

-- 9 查询各部门的员工工资的最大值、最小值、平均值、总和，并按部门编号升序
select deptno, max(sal), min(sal), avg(sal), sum(sal) from emp where deptno is not null group by deptno order by deptno;

-- 10 查询各个领导手下员工的最低工资，其中最低工资不能低于6000的
select min(sal) as msal from emp group by mgr having msal >= 6000;

-- 11 查询每个部门的编号，员工数量和工资平均值，并按平均工资降序
select deptno, count(1) as num, avg(sal) as average from emp where deptno is not null group by deptno order by average desc;

-- 12 查询哪些部门的员工个数大于2，并按照个数降序排序
select deptno, count(1) as num from emp where deptno is not null group by deptno order by num desc;

-- 13 查询每个部门有奖金的员工的最高工资>12000的部门编号和最高工资
select deptno, max(sal) as masal from emp where deptno is not null and comm is not null group by deptno having masal > 12000;

-- 14 查询领导编号 > 3 的每个领导手下的员工最低工资 > 5000的领导编号是哪些，以及其手下的最低工资
select mgr, min(sal) as misal from emp group by mgr having mgr > 3 and misal > 5000;

-- 15 查询员工名和对应的部门名
select d.dname, e.ename from emp as e join dept as d on e.deptno = d.deptno;

-- 16 查询有奖金的员工名、部门名及奖金
select ename, dname, comm from emp as e join dept as d on comm is not null and e.deptno = d.deptno;

-- 17 查询姓名中包含'春'的员工的名字、上级的名字
select e.ename as "员工名", ee.ename as "上级名" from emp as e join emp as ee on e.mgr = ee.empno where e.ename = "%春%";

-- 18 查询没有部门的员工信息及部门信息
select e.*, d.* from emp as e left join dept as d on e.deptno = d.deptno where e.deptno is null;

文档中的答案

-- 1. 查询没有奖金，且工资小于15000的员工的姓名
select ename from emp where comm is null and sal < 15000;

-- 2. 查询员工表中部门编号不是3或者工资是12000的员工信息
select ename from emp where deptno != 3 or sal = 12000;

-- 3. 查看员工表中涉及到了哪些部门
select distinct deptno from emp;

-- 4. 查询员工的姓名和部门号和年薪，按年薪降序，按部门号升序
select ename,deptno,(1+ifnull(comm,0))*sal*12 年薪 from emp order by 年薪 desc, deptno asc;

-- 5. 选择工资不在8000到17000的员工的姓名和工资，按工资降序
select ename, sal from emp where sal not between 8000 and 17000 order by sal desc;

-- 6. 查询名字中包含春的员工信息，按照工资降序
select * from emp where ename like '%春%' order by sal desc;

-- 7. 查询公司员工工资的最大值、最小值、平均值(保留两位小数)、总和
select max(sal),min(sal),round(avg(sal),2),sum(sal) from emp;

-- 8. 查询部门编号为3的员工个数
select count(*) from emp where deptno=3;

-- 9. 查询各部门的员工工资的最大值、最小值、平均值、总和，并按部门编号升序
select deptno,max(sal),min(sal),avg(sal),sum(sal) from emp group by deptno order by deptno asc;

-- 10. 查询各个领导手下员工的最低工资，其中最低工资不能低于6000的
select mgr,min(sal) from emp group by mgr having min(sal)>=6000;

-- 11. 查询每个部门的编号，员工数量和工资平均值，并按平均工资降序
select deptno,count(*),avg(sal) from emp group by deptno order by avg(sal) desc;

-- 12. 查询哪些部门的员工个数大于2，并按照个数降序排序
select deptno, count(*) from emp group by deptno having count(*)>2 order by count(*) desc;

-- 13. 查询每个部门有奖金的员工的最高工资>12000的部门编号和最高工资
select deptno,max(sal) from emp where comm is not null group by deptno having max(sal)>12000;

-- 14. 查询领导编号 > 3 的每个领导手下的员工最低工资 > 5000的领导编号是哪些，以及其手下的最低工资
select mgr,min(sal) from emp where mgr>3 group by mgr having min(sal)>5000;

-- 15. 查询员工名和对应的部门名
select ename,dname from emp e,dept d where e.deptno=d.deptno;

-- 16. 查询有奖金的员工名、部门名及奖金
select ename,dname,comm from emp e, dept d where e.deptno=d.deptno and comm is not null;

-- 17. 查询姓名中包含'春'的员工的名字、上级的名字
select e.ename, m.ename from emp e, emp m where e.mgr=m.empno and e.ename like '%春%';

-- 18. 查询没有部门的员工信息及部门信息
select * from emp e left join dept d on e.deptno=d.deptno where e.deptno is null;

一些废话

到这里，MySQL的一些基本的实操类知识就整理完毕了，虽然大部分都是粘贴的以前的笔记，但是整理起来也是很麻烦的，毕竟以前学习记的笔记都是杂乱无章，而这次综合起来归纳到一个文件中了。

在这个文章中并没有总结面试常问的问题，只是些基础的MySQL实操累知识，所以还有很多更加深入的知识等着我们去学习，加油(ง •_•)ง！！！

文章作者: 林风
文章链接: https://linfeng12138.github.io/2024/02/20/mysql-xue-xi-bi-ji/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 林风 !
笔记整理 数据库 MySQL SQL
赏
  目录