MySQL数据库常用操作：从入门到进阶的复习笔记

时间：2024-11-21 18:57:48

????MySQL数据库常用操作：从入门到进阶的复习笔记????

文章目录

  • ????MySQL数据库常用操作：从入门到进阶的复习笔记????
    • 一、数据库设计(DDL)
    • 二、知识表
    • 三、表设计(DDL)
    • 四、增删改(DML)
    • 五、查询(DQL)
      • 5.1 基本查询
      • 5.2 条件查询
      • 5.3 聚合查询
      • 5.4 分组查询
      • 5.5 排序查询
      • 5.6 分页查询
      • 5.7 流程控制函数
    • 六、多表查询
      • 6.1 内连接(A、B交集)
      • 6.2 外连接
      • 6.3 子查询

???? 开篇寄语：
还在为MySQL数据库操作头疼吗？别担心，这篇复习笔记将带你从登录数据库开始，一步步掌握数据库设计、表设计、增删改查等核心操作，让你的MySQL技能瞬间起飞！

???? 精彩预告：
登录数据库： 教你如何快速登录MySQL，隐藏密码更安全！
数据库设计(DDL)： 创建、删除数据库，轻松管理你的数据世界。
数据类型详解，从数值到字符串，再到日期时间，一网打尽。
表约束讲解，非空、唯一、主键等，让你的表结构更严谨。
表设计(DDL)： 创建、查询、修改、删除表，一切尽在掌握。
字段添加、修改、删除，表名更改，操作简便快捷。
增删改(DML)： 数据插入、删除、更新，让你的数据随心所欲地变化。
查询(DQL)： 基本查询、条件查询、聚合查询、分组查询、排序查询、分页查询、多表查询，满足你所有查询需求。流程控制函数，让你的查询结果更加智能、人性化。

???? 为什么值得一读：
精炼内容：去除冗长，直击要点，让你快速掌握MySQL核心操作。
实战导向：大量实例，边学边练，快速提升你的实践能力。
大学生气质：语言简洁明了，符合大学生口味，让你一读难忘。
???? 结语：
别再犹豫了，赶快跟随这篇复习笔记，一起开启MySQL数据库操作的大门吧！相信在不久的将来，你也能成为数据库操作的高手！

登录数据库

  • 普通登录：
mysql -u你的账号 -p你的密码
  • 隐藏密码登录：
mysql -u你的账号 -p
Enter password:你的密码

一、数据库设计(DDL)

  1. 查询
-- 查询当前所有数据库
show databases;
  1. 使用
use 数据库名;
  1. 创建
create database [if not exists] 数据库名;
  1. 删除
drop database [if exists] 数据库名;

二、知识表

数据类型

  1. 数值类型
类型 大小byte 描述
tinyint 1 小整数数值
smallint 2 大整数数值
mediumint 3 大整数数值
int 4 大整数数值
bigint 8 极大整数数值
float 4 单精度浮点数
double 8 双精度浮点数
decimal 小数值(精度更高)
  1. 字符串类型
类型 大小byte 描述
char 0-255 定长字符串
varchar 0-65535 变长字符串
tinyblob 0-255 不超过255个字符的二进制数据
tinytext 0-255 短文本字符
blob 0-65535 二进制长文本数据
text 0-65535 长文本数据
mediumblob 0-16777215 二进制中等长文本
mediumtext 0-16777215 中等长文本
longblob 0-4294967295 二进制极大文本
longtext 0-4294967295 极大文本数据
  1. 日期时间类型
类型 大小byte 格式 描述
date 3 YYYY-MM-DD 日期值
time 3 HH:MM:SS 时间值
year 1 YYYY 年份值
datetime 8 YYYY-MM-DD HH:MM:SS 混合日期和时间值
timestamp 4 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳

表约束

约束 描述 关键字
非空约束 字段不能为null not null
唯一约束 字段唯一、不能重复 unique
主键约束 非空且唯一 primary key
默认约束 未指定值采用默认 default
外键约束 连接两张表 foreign key

id 自增：primary key 后加auto_increment

三、表设计(DDL)

  1. 创建
create table 表名(
字段1 字段类型 [约束] [comment 字段1注释],
字段2 字段类型 [约束] [comment 字段1注释],
...
)[comment 表注释]
  1. 查询
-- 查询当前数据库所有表
show tables; 

-- 查询表结构
desc 表名; 

-- 查询建表语句
show create table 表名;
  1. 修改
-- 添加字段
alter table 表名 add 字段名 类型 [comment 注释] [约束]; 

-- 修改字段类型
alter table 表名 modify 字段名 新数据类型; 

-- 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型 [comment 注释] [约束];

-- 删除字段
alter table 表名 drop column 字段名；

-- 修改表名
rename table 表名 to 新表名;
  1. 删除
drop table [if exists] 表名;

四、增删改(DML)

  1. 增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. 删delete
delete from 表名 [where 条件];
  1. 改 update
update 表名 set 字段名1=值1, 字段名2=值2, ...[where 条件];

五、查询(DQL)

select 字段列表 from 表名列表
where  条件列表 
group by 字段分组列表
having 分组后条件列表 
order by排序字段列表
limit 分页参数

5.1 基本查询

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

-- 查询所有字段
select * from 表名;

-- 设置别名
select 字段 [as 别名] from 表名;

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

5.2 条件查询

select 字段列表 from 表名 where 条件列表;
-- eg
-- 1.查询姓名为小明的员工
select * from emp where name = '小明';

-- 2.查询id小于5的员工信息
select * from emp where id<5;

-- 3.查询没有分配职位的员工信息
select * from emp where job is null;

-- 4.查询入职日期在'2000-01-01'到'2010-01-01'之间的员工信息
select * from emp where entrydate>='2000-01-01' and entrydate<='2010-01-01';
select * from emp where entrydate between '2000-01-01' and '2010-01-01';

-- 5.查询职位是 2(讲师),3(学生主管),4(教研主管)的员工信息
select * from emp where job in (2,3,4);

5.3 聚合查询

聚合函数

函数 功能
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和 
select 聚合函数(字段列表) from 表名;
-- eg
-- 1.统计员工数量
select count(*) from emp;

-- 2.统计最早入职员工
select min(entrydate) from emp;

-- 3.统计最迟入职员工
select max(entrydate) from emp;

-- 4.统计企业员工id的平均值
select avg(id) from emp;

-- 5.统计企业员工的id之和
select sum(id) from  emp;

5.4 分组查询

select * from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
-- eg
-- 1.根据性别分组，统计男性和女性员工的数量
select gender, count(*) from emp group by gender;

-- 2.先查询入职时间在'2015-01-01'及以前的员工，并且根据职位进行分组，获取各职位员工数
select job,count(*) from emp where entrydate <= '2015-01-01' group by job;

-- 3.先查询入职时间在'2015-01-01'及以前的员工，并且根据职位进行分组，获取员工数量大于等于2的职位
select job,count(*) from emp where entrydate <= '2015-01-01' group by job having  count(*)>=2;

5.5 排序查询

-- ASC:升序(默认)
-- DESC:降序
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2, ...;

-- eg
-- 1.根据入职时间，对员工进行升序排序
select * from emp order by entrydate asc;

-- 2.根据入职时间降序排序
select * from emp order by entrydate desc;

-- 3.根据 入职时间 对员工进行 升序排序， 如果入职时间想同，按照 更新时间 进行 降序排序
select * from emp order by entrydatea asc , update_time desc ;

5.6 分页查询

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

-- eg
-- 1.查询第1页数据，每页展示5条记录
select * from emp limit 0,5;

-- 2.查询第2页数据，每页展示5条记录
select * from emp limit 5,5;

-- 3.查询第3页数据，每页展示5条记录
select * from emp limit 10,5;
-- 查询第n页起始索引计算公式
-- 起始索引 = (页码-1) * 每页展示记录数

5.7 流程控制函数

if(条件表达式, true取值,false取值)
-- 1.统计员工性别信息,并且用中文表示(gender 1：'男性员工'，2：'女性员工')
select if(gender = 1, '男性员工', '女性员工')性别 , count(*) from  emp group by gender;

-- 2.统计各职位员工数 1(班主任),2(讲师),3(学生主管),4(教研主管)
select 
    (case job
         when 1 then '班主任'
         when 2 then '讲师'
         when 3 then '学生主管'
         when 4 then '教研主管'
         else '未分配职位' end) as 职位,
    count(*)
from emp group by job;

六、多表查询

6.1 内连接(A、B交集)

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

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

-- eg
-- 1.查询员工姓名，及所属的部门名称(隐式内连接)
select emp.name,dept.name from emp,dept where  emp.dept_id = dept.id;

-- 2.查询员工姓名，及所属的部门名称(显式内连接)
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

6.2 外连接

-- 左外连接：以左表为基准，完全包含左表数据
select 字段 from 表1 left [outer] join 表2 on 连接条件;

-- 右外连接：以右表为基准，完全包含左表数据
select 字段 from 表1 right [outer] join 表2 on 连接条件;

-- eg
-- 1.查询员工表 所有 员工的姓名，和对应的部门名称(左外连接)
select emp.name,dept.name from emp left join dept  on emp.dept_id = dept.id;

-- 2.查询部门表 所有 部门的名称，和对应的员工名称(右外连接)
select emp.name,dept.name from emp right join dept  on emp.dept_id = dept.id;

6.3 子查询

-- 形式如下：
select * from t1 where column1 = (select column1 from t2 ...);
-- 子查询的外部语句可以是insert/update/delete/select的任何一个，最常见是select
-- 分类：
-- 标量子查询：子查询返回结果是单个值
-- 列子查询： 子查询返回结果为一列
-- 行子查询：子查询返回结果为一行
-- 表子查询：子查询返回结果为多行多列

-- 标量子查询
-- 1.查询”教研部“的所有员工子信息
-- a.先拿到教研部id
select id from dept where name = '教研部';
-- b.根据得到的id即可查询该部门所有员工信息
select * from emp where dept_id = 2;
-- 使用标量子查询
select * from emp where dept_id = (select id from dept where  name = '教研部');

-- 2.查询在”方东白“入职后的员工信息
-- a.获取方东白入职时间
select entrydate from emp where name = '方东白';
-- b.获取该时间之后入职的员工信息
select * from emp where entrydate > '2012-11-01';
-- c.合并为一条语句
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

-- 列子查询
-- 1.查询”教研部“和”咨询部“的所有员工信息
-- a.获取教研部和咨询部的id
select id from dept where name = '教研部' or name = '咨询部';
-- b.根据id获取员工信息
select * from emp where id in (2,3);
-- c.合并
select * from emp where id in  (select id from dept where name = '教研部' or name = '咨询部');

-- 行子查询
-- 1.查询与”韦一笑“的入职日期及职位都相同的员工信息;
select * from emp where (entrydate,job)=(select entrydate,job from emp where name = '韦一笑');

-- 表子查询
-- 1.查询入职日期是”2006-01-01“之后的员工信息，及部门名称
-- a.获取入职日期在”2006-01-01“之后的员工信息
select * from emp where entrydate > '2006-01-01';
-- b.查询这部分的员工以及部门名称
select e.*,d.name from (select * from emp where entrydate > '2006-01-01') as e, dept as d where e.dept_id = d.id;

???????????? 欢迎批评指正????????????

相关文章