MySQL知识总结

基本命令

1.查看MySQL中所有数据库

(1)显示当前MySQL中包含的所有数据库

show databases;

(2)创建自定义数据库

create database mydb1;						# 创建mydb1数据库
create database mydb2 character set gbk;	# 创建mydb2数据库 并 设置编码gbk
create database if not exists mydb3;		#如果mydb3数据库不存在,则创建mydb3数据库

(3) 查看数据库信息

show create database mydb2; 	# 查看创建数据库mydb2的基本信息

(4)修改数据库

alter database mydb2 character set utf8	# 修改mydb2的字符编码为utf8
# 注意: 在mysql中 utf-8 写为 utf8

(5)删除数据库

drop database mydb1; 	# 删除数据mydb1

(6)查看当前所使用的数据库

select database();		# 查看当前使用的数据库

(7)使用数据库

use mydb1;		# 使用mydb1数据库

数据查询

1.基本查询

select 列名 from 表名

(1)查询部分列

# 查询员工表中所有员工的 编号 名字 邮箱
Select empoloyee_id, first_name, email
from t_empolotees;

(2)查询所有列

# 查询 员工表中所有员工的所有信息(所有列)
select 所有列的列名 from t_employees;
select * from t_employees;

注意: 生产环境下,优先使用列名查询
*的方法需转换成全列名,效率低,可读性差

(3)对列中的数据进行运算

# 查询员工表中所有员工的编号, 名字, 年薪
select empoloyee_id, first_name, salary*12
from t_empolotees;

注意: % 是占位符,而非模运行符

(4)列的别名

列 as '别名'

(5)查询结果去重

distinct 列名

# 查询员工表中所有经理的ID
select distinct manager_id
from t_employees;

(6) 排序查询

select 列名 from 表名 order by 排序列[排序规则]

排序规则	描述
ASC	对前面排序列做升序排列
desc	对前面排序列做降序排序

依据单列排序

# 查询员工的编号, 名字, 薪资. 按照工资高低进行降序排序.
select employee_id, first_name, salary
from t_employees
order by salary desc;

依据多列排序

# 查询员工的编号, 名字, 薪资. 按照工资高低进行降序排序.（薪资相同时,按照编号进行升序排序）
select employee_id, first_name, salary
from t_employees
order by salary desc, employee_id ASC;	# 依次满足

(7)条件查询

select 列名 from 表名 where 条件

关键字	描述
where 条件	在查询结果中,筛选符合条件的查询结果,条件为布尔表达式

等值判断(=)

# 查询薪资是11000 的员工信息(编号, 名字, 薪资)
select employee_id, first_name, salary
from t_employees
where salary = 11000;

注意: MySQL中等值判断使用 = !!!

逻辑判断(and, or ,not)

# 查询薪资是11000并且提成是0.30的员工信息(编号, 名字, 薪资)
select employee_id, first_name, salary
from t_employees
where salary = 11000 and commission_pct = 0.30;
# where not salary = 11000;

不等值判断(>, < ,>=, <=, !=, <>)

# 查询薪资在6000~10000之间的员工信息(编号, 名字, 薪资)
select employee_id, first_name, salary
from t_employees
where salary >= 6000 and salary <= 10000;

注意: != , <> 都代表不等于

区间判断(between and)

# 查询薪资在6000~10000之间的员工信息(编号, 名字, 薪资)
select employee_id, first_name, salary
from t_employees
where salary between 6000 and 10000; # 闭区间

注意: 在区间判断语法中, 小值在前,大值在后

NULL值判断(is null, is not null)

is null
	列名 is null
is not null
	列名 is not null

# 查询员工经理不为空的信息
Select empoloyee_id, first_name, manger_id
from t_empolotees
where manger_id is not null;

枚举查询(in (值1, 值2, 值3))

# 查询部门编号为70, 80, 90的员工信息(编号, 名字, 薪资, 部门编号)
select employee_id, first_name, salary, department_id
from t_employees
where department_id in (70, 80, 90);

模糊查询

like_(单个任意字符)
列名 like '张_'
% (任意长度的任意字符)
列名like '张%'

注意: 模糊查询只能和like关键字结合使用

# 查询名字以"L"开头的员工信息(编号, 名字, 薪资, 部门编号)
select employee_id, first_name, salary, department_id
from t_employees
where first_name lick 'L%';


# 查询名字以"L"开头并且长度为4的员工信息(编号, 名字, 薪资, 部门编号)
select employee_id, first_name, salary, department_id
from t_employees
where first_name lick 'L___';

分支结构查询

case
	when 条件1 then 结果1
	when 条件2 then 结果2
	when 条件3 then 结果3
	else 结果
end

注意: 通过使用 case end 进行条件判断,每条数据对应生成一个值
经验: 类似 java中的Switch

if else

select employee_id, first_name, salary,
case
	when salary > 10000 then 'A'
	when salary >= 8000 and salary < 10000 then 'B'
	when salary >= 6000 and salary < 8000 then 'C'
	eles 'E'
end as '薪资级别'
from t_employees;

(8)时间查询

select [时间参数(参数列表)]()

经验: 执行时间查询函数,会自动生成一张虚表(一行一列)

时间函数	描述
sysdate()	当前系统时间(日, 月, 年, 时, 分, 秒)
curdate()	获取当前日期
curtime()	获取当前时间
week(date)	获取指定日期为一年中的第几周
year(date)	获取指定日期的年份
hour(time)	获取指定时间的小时值
minute(time)	获取指定时间的分钟值
datediff(date1,date2)	获取date1和date2之间相隔的天数
adddate(date,n)	计算date加上n天后的日期

获取当前系统时间

select sysdate();	# 当前系统时间(日, 月, 年, 时, 分, 秒)
select curdata();	# 当前日期
select curtime();	# 当前时间

获取指定时间

# 获取指定日期为第
Select week(select sysdate());
# 获取指定日期中的年份
select year('2020-4-1');
# 获取小时值
select hour(curtime());
# 获取分钟值
select minute(curtime);
# 指定日期之间的相隔天数
select datediff('2020-4-1', '2019-4-1')
# 计算data日期加上n天后的日期
select adddate('2020-4-1', 4)

(9) 字符串查询

select 字符串函数([参数列表])

字符串函数	说明
concat(str1, str2, str3)	将多个字符串连接
insert(str,pos,len,newStr)	将str中指定pos位置开始len长度的内容替换为newStr
lower(str)	将指定字符串转换为小写
upper(str)	将指定字符串转换为大写
substring(str, num, len)	将str字符串指定num位置开始截取len个内容

字符串应用

# 拼接内容
select concat('My', 'S', 'QL');

# 字符串替换
select insert('这是一个数据库', 3, 2, 'MySQL')

注意: 数据的下标从 1 开始!!!

# 指定内容转换为小写
select lower('MYSQL');

# 指定内容转换为大写
select upper('mysql');

# 截取指定内容
select substring('javaMySQLOracle', 5, 5);

(10) 聚合函数

select 聚合函数(列名) from 表名   # (对列做操作)

经验: 对多条数据的单列进行统计,返回统计后的一行结果

聚合函数	说明
sum()	求所有行中单列结果的总和
avg()	平均值
max()	最大值
min()	最小值
count()	求总行数

单列总和

# 统计所有员工每月的工资总和(单列所有数据的和)
select sum(salary) from t_employees;
# 统计所有员工每月的工资的平均值(单列所有数据的平均值)
select avg(salary) from t_employees;
# 最大值
select max(salary) from t_employees;
# 最小值
select min(salary) from t_employees;
# 员工总人数
select count(employee_id) from t_employees;
# 统计有提成的人数	聚合函数自动忽略 null值 不进行统计
select count(commission_pct) from t_employees;

(11) 分组查询

select 列名 from 表名 where 条件 group by 分组依据(列);

关键字	说明
group by	分组依据,必须在where之后生效

查询各部门的总人数

# 思路
# 1. 按照部门编号进行分组(分组依据说 depertment_id)
# 2. 再针对各部门的人数进行统计(count)
select department_id, count(employee_id)
form t_employees
group by department_id;

查询各部门的平均工资

select department_id, avg(salary)
form t_employees
group by department_id;

查询各个部门,各个岗位的人数

# 1. 按照部门编号进行分组(分组依据 department_id)
# 2. 按照岗位名称进行分组(分组依据 job_id)
# 3. 针对每个部门中的各个岗位进行人数统计(count)

select department_id, job_id, count(employee_id)
form t_employees
group by department_id, job_id;

常见问题

# 查询各个部门id, 总人数, first_name
# 		分组依据列		聚合函数列
select department_id, count(*), first_name
from t_employees
group by department_id; # error 结果是错误的

注意: 分组查询中, select 显示的列只能说分组依据列, 或者聚合函数列,不能出现其他列.

(12) 分组过滤查询

select 列名 from 表名 where 条件 group by 分组列 having 过滤规则

关键字	说明
having 过滤规则	过滤规则定义对分组后的数据进行过滤

# 统计60, 70, 90 号部门的最高工资
select department_id,  max(salary)
from t_employees
group by department_id
having department_id in (60, 70, 90);

(13)限定查询

select 列名 from 表名 limit 起始行, 查询行数

关键字	说明
limit,offset_start, row_count	限定查询结果的起始行和总行数

查询前5行记录
```
# 查询表中前5名员工的所有信息
select * from t_employees limit 0, 5;
```
- 注意: 起始行是从0开始, 代表了第一行.
- 第二个参数代表的是从指定行开始查询几行

查询范围记录

# 查询表中从第三条开始, 查询10行
select * from t_employees limit 3, 10;

select * from t_employees limit 0, 10;
select * from t_employees limit 10, 10;
select * from t_employees limit 20, 10;

查询总结

SQL 语句编写顺序

select 列名 from 表名 where 条件 group by 分组 having order by 排序列(asc|desc) limit 起始行, 总条数

SQL语句执行顺序

from: 指定数据来源表
where: 对查询数据做有第一次过滤
group by: 分组
having: 对分组后的数据第二次过滤
select: 查询各字段的值
order by: 排序
limit: 限定查询结果

子查询

(1) 作为条件判断(一行一列)

select 列名 from 表名 where 条件(子查询结果)

查询工资大于Bruce的员工信息

# 1.先查询到 Bruce 的工资(一行一列)
select salary from t_employees where first_name = 'Bruce';		# 工资为 6000

# 2.查询工资大于Bruce的员工信息
select * from t_employees where salary > 6000;

# 3. 将 1, 2 两条语句整合
select * 
from t_employees 
where salary > (select salary from t_employees where first_name = 'Bruce');

注意: 将子查询"一行一列"的结果作为外部查询的条件,做第二次查询
子查询得到一行一列的结果才能作为外部查询的等值或 不等值 判断条件

(2) 作为枚举查询条件(多行单列)

select 列名 from where 列名 in (子查询条件)

查询与名为"king"同一部门的员工信息

# 1. 先查询 'king' 所在的部门编号(多行单列)
select department_id 
from t_empltees
where last_name = 'king' 	// 部门编号 80. 90

# 2. 再查询80. 90 号部门的员工信息
select emplotee_id, first_name, salary, department_id
from t_employees
where department_id in (80, 90);

# 3. 合并
select emplotee_id, first_name, salary, department_id
from t_employees
where department_id in (select department_id 
from t_empltees
where last_name = 'king');

将子查询"多行单列"的结果做为外部查询的枚举查询条件,做第二次查询

工资高于60部门所有人的信息

# 1.查询 60 部门所有人的工资(多行多列)
select salary from t_employees where department_id = 60;

# 2. 查询高于60部门所有人的工资的员工信息(高于所有)
select * form t_employees where salary > all(select salary from t_employees where department_id = 60);

# 3. 查询高于60部门的工资的员工信息(高于部分)
select * form t_employees where salary > any(select salary from t_employees where department_id = 60);

注意: 当子查询结果集形式为 多行单列 时可以使用 any 或 all 关键字

(3) 作为一张表

select 列名 from (子查询的结果集) where 条件;

查询员工表中工资前5名的员工信息

# 1. 先对所有员工的薪资进行排序(排序后的临时表)
select employee_id, first_name, salary
from t_employees
order by salary desc;

# 2. 再查询临时表中的前5行员工信息
select employee_id, first_name, salary
from (临时表)
limit 0, 5;

# 3. 合并
select employee_id, first_name, salary
from (select employee_id, first_name, salary
from t_employees
order by salary desc) as temp
limit 0, 5;

将子查询"多行多列"的结果作为外部查询的一张表,做第二次查询下.
注意: 子查询作为临时表, 需要为其赋予一个临时表名

(4)合并查询(了解)[纵向合并]

select * from 表名1 union select * from 表名2;
select * from 表名1 union all select * from 表名2;

合并两张表的结果(去除重复记录)
```
# 合并两张表的结果, 去除重复记录 
select * from t1 union select * from t2;
```
- 注意: 合并结果的两张表, 列数必须相同, 列的数据类型可以不同

合并两张表的结果(保留重复记录)

# 合并两张表的结果, 不去除重复记录(显示所有)
select * from t1 union all select * from t2;

经验: 使用union 合并结果集, 会去除掉两张表中重复的数据

(5) 表连接查询

select 列名 from 表1 连接方式 表2 on 连接条件;

内连接(inner join in)

# 1. 查询所有部门的员工信息(不包括没有部门的员工) SQL 标准
# 如果不指定连接条件, 则会造成笛卡尔积的结果
select * from t_employees inner join in t_jobs on t_employees.'job_id' = t_jobs.'job_id';

# 2. 查询所有部门的员工信息(包括没有部门的员工) MYSQL
select * from t_employees, t_jobs where t_employees.job_id = t_jobs.job_id;

三表连接查询

# 查询所有员工工号 姓名 部门名称 部门所在国家ID
select * from t_employees e
inner join t_departments d
on e.department_id = d.department_id
inner join t_location = l
on d.location_id = l.location_id;

左外连接(left join on)

# 查询所有员工信息,以及所有对应的部门名称(没有部门的员工, 也在查询结果中, 部门名称为 NULL 填充)
select e.employee_id, e.first_name, e.salary, d.department_name
from t_employees e
left join t_departments d
on e.department_id = d.department_id;

注意: 左外连接, 是以左表为主表, 依次向右匹配, 匹配到, 返回结果
匹配不到, 则返回 null 值填充

右外连接(right join on)

# 查询所有部门信息, 以及此部门中所有员工信息(没有员工的部门, 也在查询结果中, 部门名称为 NULL 填充)
select e.employee_id, e.first_name, e.salary, d.department_name
from t_employees e
right join t_departments d
on e.department_id = d.department_id;

注意: 右外连接, 是以右表为主表, 依次向右匹配, 匹配到, 返回结果
匹配不到, 则返回 null 值填充

DML操作 [重点]

(1)新增 (insert)

insert into 表名(列1, 列2, 列3, ...) values (值1, 值2, 值3, ...);

添加一条消息

# 添加一条工作岗位信息
insert into t_jobs(job_id, job_title, min_salary, max_salary) values ('Java_le', 'java_Lecturer', 2500, 9000);

注意: 表名后的列名和values里的值要一一对应(个数, 顺序, 类型)

(2) 修改 (update)

update 表名 set 列1 = 新值1, 列2 = 新值2, ... where 条件;

# 修改编号为 135 的员工信息岗位编号为 st_man, 工资为3500
update t_employees set job_id = st_max, salary = 3500 where employee_id = '135';

注意: set 后多个列名 = 值, 绝大多数情况下都要加where条件, 指定修改, 否则为整表更新

(3) 删除 (delete)

delete from 表名 where 条件;

# 删除姓Peter, 并且名为 Hall 的员工
delete from t_employees where first_name = 'Peter' and Last_name = 'Hail';

注意: 删除时, 如若不加where条件,删除的是整张表的数据

(4) 清空整表数据 (trunacte)

truncate table 表名;

# 清空t_countries 整张表
truncate table t_countries;

注意: 与delete 不加 where 删除整表数据不同, truncate 是把表销毁, 再按照原表的格式创建一张新表

数据表操作

mysql支持多种类型, 大致可以分为三类: 数值, 日期/时间和字符串(字符)类型. 对于我们约束数据的类型有很大的帮助

数值类型

类型	大小	范围(有符号)	范围(无符号)	用途
int	4字节	(-2 147 483 648, 2 147 483 647)	(0, 4 294 967 295)	大整数值
double	8字节	(-1.79E+308, - 2.22E-308)	(0, 22E-308, 1.797E+308)	双精度浮点数值
double(M, D)	8个字节,M表示长度,D表示小数位数	同上, 受M,D约束double(5, 2)-999.99-99.99	同上,受M,D约束	双精度浮点数值
decimal(M,d)	decimal(M,D)	依赖于M和D的值,M的最大值为65	依赖于M和D的值,M的最大值为65	小数值

日期类型

类型	大小	范围	格式	用途
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	3	'-838:59:59'/'833: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:00/2038结束时时间是2147483646秒北京时间2038-1-19 11:14:07, 格林尼治时间 2038年1月19日凌晨03:14:07	YYYYMMDDHHMMSS	混合日期和时间值,时间戳

字符串类型

类型	大小	用途
char	0-255字符	定长字符串char(10)10个字符
varchar	0-65535字符	变长字符串varchar(10)10个字符
blob(binary large object)	0-65535字符	二进制形式的长文本数据
text	0-65535字符	长文件数据

char和varchar类型类似, 但它们保存和检索的方式不同.它们的最大长度和是否尾部有空格被保留方面也不同. 在存储或检索过程中不进行大小写转换.
blob是一个二进制大对象,可以容纳可以变数量的数据. 有4种blob类型: tinyblob, blob, mediumblob和longblob.它们只是容纳值的最大长度不同.

数据表的创建(create)

create table 表名(
列名 数据类型 [约束],
列名 数据类型 [约束],
列名 数据类型 [约束],
....
列名 数据类型 [约束]		// 最后一列的末尾不加逗号
)[charset=utf8]			// 可根据需要指定表的字符编码集

数据表的修改(alter)

alter table 表名 操作;

向现有表中添加列

# 在课程表的基础上添加gradeId 列
alter table subject add gradeId int;

修改表中的列

# 修改课程表中课程名称长度为10个字符串
alter table subject modify subjectName varchar(10)

注意: 修改表中的某列时, 也要写全列的名字, 数据类型, 约束

删除表中的列

# 删除课程表中 gradeId 列
alter table subject drop gradeId

注意: 删除列时, 每次只能删除一列

修改列名

# 修改课程表中 subjectHours 列为 classHours
alter table subject change subjectHours classHours int;

注意: 修改列名时, 在给定列新名称时, 要指定列的类型合约束

修改表名

# 修改学生表的 subject 为 sub
alter table subjetc to sub;

数据表的删除(drop)

drop table 表名

# 删除学生表
drop table subject;

查看表的结构

describe 表名

1 实体完整性约束

表中的一行数据代表一个实体, 实体完整性的作用即是标识每一行数据不重复,实体唯一.

1.1主键约束

primary key 唯一, 标识表中的一行数据, 此列的值不可重复,且不能为null

# 为表中适用主键的列添加主键约束
create table subject(
	subjectId int primary key, 		# 添加主键约束
	subjectName varchar(20),
	subjectHours int
)charset=utf8;

insert into subject(subjectId, subjectName, subjectHours) values (1, 'java', 40);
insert into subject(subjectId, subjectName, subjectHours) values (1, 'java', 40);  # error 主键1 已存在

1.2唯一约束

unique 唯一, 标识表中的一行数据, 不可重复, 可以为 null

# 为表中列值不允许重复的列添加唯一约束
create table subject(
	subjectId int primary key, 		# 添加主键约束
	subjectName varchar(20) unique,		# 课程名称唯一!
	subjectHours int
)charset=utf8;

insert into subject(subjectId, subjectName, subjectHours) values (1, 'java', 40);
insert into subject(subjectId, subjectName, subjectHours) values (2, 'java', 40);  # error 课程名 已存在

1.3 自动增长列

auto_increMent 自动增长, 给主键数值列添加自动增长. 从1开始每次加1.不能单独使用, 和主键配合

# 为表中主键添加自动增长, 避免忘记主键 ID 序号
create table subject(
	subjectId int primary key auto_increment, # 添加主键和自增
	subjectName varchar(20) unique,		# 课程名称唯一!
	subjectHours int
)charset=utf8;

insert into subject(subjectName, subjectHours) values ('java', 40);
insert into subject(subjectName, subjectHours) values ('java', 40);

2域完整性约束

限制列的单元格的数据正确性

2.1非空约束

not null 非空, 此列必须有值

create table subject(
	subjectId int primary key auto_increment, # 添加主键和自增
	subjectName varchar(20) unique not null,		# 课程名称唯一!
	subjectHours int
)charset=utf8;

2.2 默认值约束

default 值 为列赋予默认值当新增数据不指定值时, 书写default, 以指定的默认值进行填充

create table subject(
	subjectId int primary key auto_increment, # 添加主键和自增
	subjectName varchar(20) unique not null,		# 课程名称唯一!
	subjectHours int default 20
)charset=utf8;

insert into subject(subjectName, subjectHours) values ('java', default);

2.3 引用完整性约束(外键)

语法: constraint 引用名 foreign key (列名) reference 被引用表名(列名)
详解: foreign key 引用外部表的某个列的值, 新增数据时, 约束此列的值必须说引用表中存在的值

# 创建专业表
create table Speciality(
	id int primary key auto_increment,
	SpecialName varchar(20) unique not null
)charset=utf8;

# 创建课程表(课程表的specialId 引用专业表的 id)
create table subject(
	subjectId int primary key auto_increment,
	subjectName varchar(20) unique not null,
    subjectHours int default 20,
    specialId int not null,
    # 				命名						需要添加外键的字段		来自哪个表 的 哪个字段
    constraint fk_subject_specialId foreign key (specialId) references speciality(id)	# 引用专业表里的id作为外键,新增课程信息时,约束课程所属的专业.
)charset=utf8;

# 添加专业表数据
insert into subject(subjectName, subjectHours) values ('Java');
insert into subject(subjectName, subjectHours) values ('C++');

# 课程信息表添加数据
insert into subject(subjectName, subjectHours) values('Java', 30, 1); # 专业 id 为 1 , 引用的是专业表的java
insert into subject(subjectName, subjectHours) values('C#', 30, 2);	  # 专业 id 为 2, 引用的是专业表的C#

posted @ 2023-02-26 14:20 余丸阅读(26) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 1_MySQL

· MySQL基础

· MySQL 基本语句总结

· MySQL基础

· MySQL数据库基础知识点总结

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？

公告

昵称：余丸
园龄： 3年6个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

随笔档案

2023年2月(1)

阅读排行榜

1. MySQL知识总结(26)

bailegebai

MySQL知识总结

MySQL知识总结

基本命令

1.查看MySQL中所有数据库

数据查询

1.基本查询

(1)查询部分列

(2)查询所有列

(3)对列中的数据进行运算

(4)列的别名

(5)查询结果去重

(6) 排序查询

(7)条件查询

(8)时间查询

(9) 字符串查询

(10) 聚合函数

(11) 分组查询

(12) 分组过滤查询

(13)限定查询

查询总结

子查询

(1) 作为条件判断(一行一列)

(2) 作为枚举查询条件(多行单列)

(3) 作为一张表

(4)合并查询(了解)[纵向合并]

(5) 表连接查询

DML操作 [重点]

(1)新增 (insert)

(2) 修改 (update)

(3) 删除 (delete)

(4) 清空整表数据 (trunacte)

数据表操作

数据表的创建(create)

数据表的修改(alter)

数据表的删除(drop)

查看表的结构

1 实体完整性约束

1.1主键约束

1.2唯一约束

1.3 自动增长列

2域完整性约束

2.1非空约束

2.2 默认值约束

2.3 引用完整性约束(外键)

公告

搜索

常用链接

随笔档案

阅读排行榜