1.常用命令和语句
1.登录MySQL数据库以及常用的命令
2.mysql -uroot(u用户名) –p 回车 再输入密码
3.mysql -uroot(u用户名) –p密码
4.mysql –host=ip地址 –user=用户名 –password=密码
5.use 数据库名(切换数据库);
查看当前数据中所有的数据表. show tables;
6.desc (describe 的缩写) 数据表名; 查看表结构
7.数据表重新命名 1 rename table student(旧名字) to stu(新名字);（mysql 官方推荐，功能更通用）2 alter table 旧名字 rename to 新名字（兼容性更好，支持更多场景）
2.数据定义语言：简称DDL(Data Definition Language)
1.用来定义数据库对象：数据库，表，列等关键字：create，alter，drop等
2.create database [if not exists] 数据库名；（中括号内的内容表示: 可选）
3.create database 数据库名 charset utf8;引号可加可不加 最好不加
4.创建商品表.
create table product(
pid int primary key auto_increment, # 商品id, 主键`
pname varchar(20), # 商品名`
price double, # 商品单价`
category_id varchar(32) # 商品的分类id
);
5.drop database [if exists] 数据库名;
6.alter database 数据库名 charset 新码表名;（修改码表类型）
7.show create database 数据库名 charset ；'码表 这里是utf8 而Python是utf-8 '数据表 增加字段 格式: alter table 数据表名 add 列名 数据类型 [约束];
8.alter table student add column sphone varchar(11) unique comment '学生号码';
9.改sphone字段的数据类型为varchar(20)（不保留原有约束）。
alter table student modify `sphone` varchar(20);
10.删除列名字段
alter table 数据表名 drop 列名;
alter table users drop desc_info;
3.数据操作语言：简称DML(Data Manipulation Language)
1.DML语句主要是操作表数据, 对其进行更新操作 关键字：insert，delete，update等
2.格式1: 添加单条数据. insert into 表名(列名1, 列名2, …) values(值1, 值2, …); 其中值的个数, 类型 要和 列的个数及类型 一致, values 和 value效果一致.
3.格式2: 如果是全列添加, 则: 列名可以省略不写. 格式可以优化为: insert into 表名 values(值1, 值2, …);
4.格式3: 如果要给指定列添加数据, 则: 列名不能省略. 格式为: insert into 表名(列名1, 列名2, …) values(值1, 值2, …);
insert into product(pid,pname,price,category_id) values(null,'联想',5000,'c001');
5.在进行修改 或者 删除操作前, 一定一定一定要加where条件, 一个过来人的含泪忠告. 或者备份数据表.
6.格式 update 数据表名 set 列名1=值1, 列名2=值2, … where 条件;
update student set sclass='高一 (3) 班' where sid=3;
#精准修改数据库中「student（学生）表」里，
学号（sid）为 3 的学生的班级信息，把他 / 她的班级改成「高一 (3) 班」。
7.格式 delete from 表名 where 条件;
8.关键点:
是否会重置主键id, delete from: 不会, 而是删除数据. truncate table: 会, 它相当于把表摧毁了然后再创建一张和之前一模一样的表.
9.primary key 本身就隐含了 NOT NULL 约束；
复制表时，sid 的 NOT NULL 约束被继承了，但 auto_increment 自增属性丢失了；所以 student_back 的 sid 只有 NOT NULL 约束，没有自增，你传 null 就会直接触发 Column 'sid' cannot be null 报错。
10.备份表数据
场景1: 备份表不存在
create table product_bak select * from product;
场景2: 备份表存在.
insert into product_bak select * from product;
4.!!!数据查询语言：简称DQL(Data Query Language)
1.关键字：select，from，where等
2.简单查询格式
需求1: 查询所有的商品信. select *from product;
需求2: 查询商品名 和 商品价格 select pid,price from product;
需求3: 别名查询 格式为: as 别名, 其中 as 可以省略不写
select pname as 商品名, price as 商品单价 from product;
一个完整的单表查询语句的 <完整> 格式如下:
select
[distinct] 列名1 [as 别名], 列名2 [as 别名], …
from
数据表名
where # 组前筛选
group by# 分组字段
having#租后筛选
order by# 排序字段 [asc | desc]
limit #起始索引, 数据条数;
3.条件查询的格式:
select * from 数据表名 where 条件;
where后边的条件可以是
比较运算符: >, <, >=, <=, =, !=, <>
范围筛选: 连续区间: between 值1 and 值2
不连续区间: in (值1, 值2, 值3…)
模糊查询: like '_ 或者 %' _ 表示任意一个字符, % 表示任意多个字符
逻辑运算符:and 逻辑与, 并且的意思, 要求条件都要满足, 即: 有False则整体为False,
例如: 条件1 and 条件2 and 条件3…
or 逻辑或, 或者的意思, 只要任意1个条件满足即可, 即: 有True则整体为True,
例如: 条件1 or 条件2 or 条件3… not 逻辑非, 取反的意思, 以前是True取反后是False, 以前是False取反后是True.
空筛选:不为空: is not null 为空: is null
#查询姓 “王” 的学生信息（模糊查询，% 搭配like）
select * from student where sname like '王%';
4.单表查询之排序查询
select * from 数据表名 order by 列名1 [asc | desc], 列名2 [asc | desc];
按照商品价格降序排列, 价格一致的情况下, 按照分类id升序排列.
select * from product order by price desc, category_id; # descending, 降序
5.单表查询之聚合函数
涉及到的聚合函数如下:
count() 适用于: 统计SQL表中的总条数(总行数)
sum() 适用于: 对某列数据 求和
max() 适用于: 对某列数据 求最大值
min() 适用于: 对某列数据 求最小值
avg() 适用于: 对某列数据 求均值
面试题: count(*), count(1), count(列名)的区别是什么? 答案:
是否统计空值. count(*), count(1) 会统计空值(null), count(列名) 只统计该列的非空值
效率的差异: count(主键列) > count(*), count(1) > count(列名)
6.分组查询
6.1格式:
select
性别, 聚合函数
from
数据表名
where
组前筛选
group by
分组字段1, 分组字段2, … 性别
having
组后筛选;
6.2细节
#需求4(总和): 统计各分类商品的总价,
只统计单价在100(不包括)以上的商品信息,
只显示总价大于500的分类信息,
且按照总价升序排列. 且只要前2条数据
```sql
select
category_id, sum(price) as total_price # 分组字段 + 聚合函数
from
product
where
price > 100 # 组前筛选
group by
category_id # 分组字段
having
total_price > 500 # 组后筛选
order by
total_price # 排序
limit
0, 2; # 分页查询
7.单表查询之分页查询(掌握)
7.1分页查询的好处:
1. 降低服务器的压力, 每次只需要返回 一少部分 数据即可.
2. 降低浏览器端的压力, 每次只需要展示 少量数据即可.
3. 提高用户体验.
7.2分页查询的格式:
select * from 数据表名 limit 起始索引, 数据条数;
7.2.1细节:
每页的起始索引: (当前页数 – 1) * 每页的数据条数 假设: 5条/页
总页数: (数据总条数 + 每页的数据条数 – 1) // 每页的数据条数 注意: 整除
8.单表查询之去重查询(掌握)
8.1格式: select distinct 列名 from 数据表名;
8.2案例
#需求1: 查看商品表中, 所有的分类id
select distinct category_id from product;
#需求2: 查看商品表中, 所有的分类id及商品价格.
select distinct category_id, price from product; # 把 分类id 和 价格 当做1个整体进行去重, 即: 'c001',5000 和 'c001',3000 不是同一条数据
#需求3: 把 整行数据当做1个整体进行去重.
select distinct * from product;
#扩展: 看看就行了, 分组也能达到上述 去重 的效果.
select category_id from product group by category_id;
9.多表建表_一对多(了解)
9.1一对多建表原则:
在<<多>>的一方新建1列(充当外键列),
去关联<<一>>的一方的主键列.
外键约束: 属于约束的一种, 用于多表之间, 设置在 从表中, 用来保证数据的完整性 和 安全性.
规定: 外表的外键列, 不能出现, 主表的主键列没有的数据.
案例: 部门表和员工表, 班级表和学生表, 用户表和订单表…
生产环境中(实际开发中), 研发期间外键约束几乎不加,
为了提高开发效率, 要做到: 虽然无约束, 心中要有约束.
当项目测试, 部署, 上线的时候, 才会添加约束, 或者直接从前端对数据做校验, 这样传到数据库的数据都是合法的.
9.2添加外键约束格式
#格式: alter table 外表名 add
[constraint '外键约束名'] foreign key(外键列名)
references 主表名(主键列名);
#建表后添加外键约束(推荐使用)
alter table employee add constraint fk_dept_emp foreign key(dept_id)
references dept(id);
alter table employee add foreign key(dept_id) references dept(id);
# 由程序默认生成: 外键约束名
9.3删除外键约束
#格式: alter table 外表名 drop foreign key 外键约束名;
alter table employee drop foreign key fk_dept_emp;
#设置联合主键.
alter table stu_course add primary key(sid, cid);
10.多表查询
10.1记忆(背诵):
多表查询的精髓是 -> 按照组合条件把多张表组成<<一张表>>, 然后进行 单表查询.
10.2交叉查询
查询结果: 笛卡尔积, 表A的总条数 * 表B的总条数, 无意义, 会有大量的脏数据, 一般不用.
#格式: select * from 表A, 表B;
select * from hero, kongfu;
10.3内连接
内连接的查询结果: 表的交集.
#格式1(显式内连接): select * from 表A inner join 表B on 关联字段 where ….;
#注意: inner可以省略不写.
# 格式2(隐式内连接): select * from 表A, 表B where 关联字段 ….;
select * from hero inner join kongfu on hero.kongfu_id = kongfu.kid;
10.4外链接
10.4.1(左外连接)查询结果:
左表的全集 + 表的交集 关于左外连接和右外连接, 掌握一种即可, 因为把表的顺序交换一下, 就会得到另一种结果.; left
10.4.2(右外连接)查询结果:
右表的全集 + 表的交集 right
10.4.3注意:
查询所有学生的完整信息，展示学生 id、姓名、年龄、班级名称、班主任姓名（无班级 / 班主任则显示 null） left join 才都显示
# 格式: select * from 表A left outer join 表B on 关联字段 where …;
# outer可以省略不写.
select * from hero h left outer join kongfu kf on h.kongfu_id = kf.kid;
select * from hero h left join kongfu kf on h.kongfu_id = kf.kid;
# 效果同上, outer可以省略不写.
10.5子查询
解释：
一个SQL语句的查询条件 依赖 另一个SQL语句的查询结果, 这种写法就叫: 子查询.
# 外边的查询叫: 父查询(主查询), 里边的查询叫(子查询)
# 格式:
select * from 表A where 字段 = (select 字段 from 表B where 条件);
实际开发中(生产环境), 为了提高开发效率, 研发期间可以先用子查询做, 之后项目测试,部署阶段阶段再把<<子查询>>替换成<<内连接查询>>.
10.6自(关联)查询
省市县案例 查询看下一个博客
11.窗口函数_排序函数
11.1介绍
窗口函数 = 给表新增1列, 至于新增的内容是什么, 取决于 你用什么函数.
格式:
#可以和窗口函数用的函数 over(partition by 分组字段 order by 排序字段 asc|desc)
#可以结合窗口函数一起用的函数(以下简称: 开窗函数, 窗口函数), 假设数据为: 100, 90, 90, 80, 则:
row_number() 行编号, 无论数据是啥, 都是从1开始, 往后逐个编号, 例如: 1, 2, 3, 4
rank() 稀疏排名, 如果数据一致则排名相同, 会跳跃数字, 例如: 1, 2, 2, 4
dense_rank() 密集排名, 如果数据一致则排名相同, 不会跳跃数字, 例如: 1, 2, 2, 3
11.2细节:
1. 窗口函数 = 函数 + over(), 用什么函数, 就给表新增什么数据.
2. 如果不写partition by, 则: 统计全表的所有数据, 如果写了, 则统计: 组内的数据.
3. 如果不写order by, 则: 统计组内所有数据, 如果写了, 则统计: 组内第一行至当前行的数据.
4. 关于窗口函数, 你掌握2点即可, 分别是: 分组排名, 分组排名求TopN
11.3 案例分组排名
# 需求: 按照部门id分组, 组内按照工资降序排名.
select
*,
row_number() over(partition by deptid order by salary desc) as row_number1,
rank() over(partition by deptid order by salary desc) as rank2,
dense_rank() over(partition by deptid order by salary desc) as dense_rank3
from
employee;
11.4 案例(分组排名求TopN掌握)
# 需求: 求各个部门的薪资的前Top2.
# 如下这个SQL的思路是OK的,
#但是写法报错. 因为: where后边只能跟 表中已有字段, 而rk是我们的'新增'字段.
select
*,
rank() over(partition by deptid order by salary desc) as rk
from
employee
where
rk <= 2;
# 解决方案: 子查询, 把上述的结果封装成一张新表, 然后从中查询即可.
select * from (
select
*,
rank() over(partition by deptid order by salary desc) as rk
from
employee
) as t1
where
rk <= 2;
5.SQL常用内置函数
1.help '函数名';
可以帮助我们快速查询 函数的 用法(详细信息)
help 'count'; # 查看 count()函数的介绍信息
help 'mod'; # 查看 mod()函数的介绍信息
2.数字函数
2.1round(): 四舍五入, 保留n位小数, 用法: round(number, n)
select round(13.12345, 2); # 13.12
select round(13.12345, 4); # 13.1235
2.2floor() 地板数, 即: 向下取整, 获取比这个数字小的所有整数中, 最大的那个整数.
select floor(13.12345); # 13
select floor(13.0); # 13
select floor(5.3); # 5
select floor(-3.2); # -4
2.3ceil() 天花板数, 即: 向上取整, 获取比这个数字大的所有整数中, 最小的那个整数
select ceil(13.000001); # 14
select ceil(13.0); # 13
select ceil(-3.2); # -3
2.4mod() 取余函数, 用法: mod(N, M), 结果是 N % M
select mod(10, 3); # 1
select mod(3, 10); # 3
2.5pow() 指数函数, 用法: pow(N, M), 结果是 N 的 M 次方、
select pow(2, 3); # 2 ^ 3 -> 8
select pow(3, 2); # 3 ^ 2 -> 9
2.6rand() 随机函数, 获取 0.0 ~ 1.0 之间的随机数, 包左不包右 左闭右开.
select rand();
# 默认用当前时间戳作为随机种子(每次都不一样), 每次获取的随机数都不一样.
select rand(200);
# 传入的200是随机种子(随便写), 如果随机种子一致, 则每次获取的随机数一致.
# 需求: 获取1个 1 ~ 100之间的随机数,包括1和100
# rand() * 100 -> 0 ~ 100之间 包左不包右
# rand() * 100 + 1 -> 1 ~ 101之间 包左不包右
select floor(rand() * 100) + 1;
# 需求: 获取1个 20 ~ 50之间的随机数, 包括20和50
# 解题思路(归零大法): 把 20 ~ 50 -> 0 ~ 30之间, 然后 再加上20
select floor(rand() * 31) + 20;
2.7abs() 绝对值函数, 返回一个数的绝对值
select abs(-10); # 10
select abs(13); # 13
3.字符串函数
3.1lower(): 把字符串中所有的字母 -> 转成 小写形式
select lower('HeLLo WORld!你好123!@#'); # hello world!你好123!@#
3.2upper(): 把字符串中所有的字母 -> 转成 大写形式
select upper('HeLLo WORld!你好123!@#'); # HELLO WORLD!你好123!@#
3.3reverse(): 字符串翻转
select reverse('abc'); # cba
3.4concat(): 拼接字符串, 直接拼接到一起, 连接符是'', 格式为: concat(str1, str2, str3, …)
select concat('蔡徐坤', '贾乃亮', '马嘉祺');
3.5concat_ws(): 拼接字符串, 可以指定连接符, 格式为: concat_ws('连接符', str, str1, str2, str3, …)
select concat_ws('|', '蔡徐坤', '贾乃亮', '马嘉祺');
3.6replace(): 替换, 格式为: replace(str, old, new) 把字符串(str)中, 旧字符串(old) 用 新字符串(new) 替换.
select replace('hello world python', ' ', '|');
3.7substr(), substring(): 功能一致, 格式为: substr(str, start, length)
select substr('hello python world java!', 2);
# 从2位置开始截取, 截取到字符串末尾
select substr('hello python world java!', 2, 5);
# 从2位置开始截取, 截取5个字符
select substring('hello python world java!', 2, 5);
# 效果同上.
3.8left(): 从字符串的左边(索引位置1开始)开始, 截取指定长度的字符串. 格式为: left(str, length)
select left('hello python world java!', 5); # hello
3.9right(): 从字符串的右边(最后1个字符)开始, 截取指定长度的字符串.
select right('hello python world java!', 5); # java!
select reverse(right('hello python world java!', 5)); # !avaj
3.10char_length(): 以字符的形式, 统计字符串的长度.
select char_length('你好aB1!'); # 6
select char_length('你好'); # 2
3.11length(): 以 码表的形式, 统计字符串的长度. 中文 -> utf8码表中占3个字节, gbk码表中占2个字节. 数字,字母,特殊符号 -> 无论什么码表, 都只占1个字节.
select length('你好aB1!'); # 10
select length('你好'); # 6
4.日期函数
4.1now(): 获取当前系统时间.
select now(); # 2026-03-08 11:56:56
4.2current_date(): 获取当前系统日期.
select current_date(); # 2026-03-08 年月日
4.3current_time(): 获取当前系统时间.
select current_time(); # 11:57:30 时分秒
4.4date_add(): 日期加法, 对指定日期, 加上指定的时间间隔, 获取新的日期.
# 需求1: 当前时间, 往后推3天
select date_add(now(), interval 3 day);
# 需求2: 当前时间, 往前推2天.
select date_add(now(), interval -2 day);
select date_sub(now(), interval 2 day); # 效果同上, date_sub()是 日期减法.
# 需求3: 2025-05-26 日期, 往后推10个月
select date_add('2025-05-26', interval 10 month);
select date_add('2025-05-26 13:14:21', interval 10 month);
4.5datediff(时间1, 时间2): 比较两个时间的不同, 即: 计算时间1 和 时间2的 间隔, 结果是: 结果1 – 结果2
select datediff('2025-05-26', '2025-05-29'); # -3
4.6获取当前时间的: 年月日, 时分秒
select year('2025-05-26 13:14:21'); # 2025
select year(now()); # 2026
select month(now()); # 3
select day(now()); # 8
select hour(now()); # 12
select minute(now()); # 7
select second(now()); # 22
4.7获取今天是: 周中的第几天.
select weekday(now()); # 6, 周日, 用 0 ~ 6表示 周1 ~ 周日
select weekday('2026-03-09 13:14:21'); # 0, 周1
4.8获取今天是: 年中的第几天
select dayofyear(now());
4.9获取今天是: 年中的第几周
select week(now());
5.条件判断(case.when)
5.1执行流程:
1. 先判断条件1, 看其是否成立, 如果成立, 则返回结果1, 然后整个case.when句子结束.
2. 如果条件1不成立, 则判断条件2, 看其是否成立, 如果成立, 则返回结果2, 然后整个case.when句子结束.
3. 否则继续判断条件3, ……重复执行即可.
4. 如果有的判断条件都不成立, 则返回结果: n
5.2格式:
case
when 条件1 then 结果1
when 条件2 then 结果2
……
else 结果n
end
5.3语法糖写法
即: 如果判断条件用的字段是同一个, 且都是等于的判断, 则可以写成(如下形式):
case 字段名
when 值1 then 结果1
when 值2 then 结果2
……
else 结果n
end [as 别名]
5.4案例
# 需求1: 查看 商品表的信息.
select * from product;
# select distinct category_id from product; # c001 ~ c005, null
# 需求2: 假设 c001 -> '电脑', c002 -> '服装', c003 -> '美妆', c004 -> '零食', c005 -> '饮品', null -> '其他'
select
*,
case
when category_id='c001' then '电脑'
when category_id='c002' then '服装'
when category_id='c003' then '美妆'
else '其它'
end as category_name
from
product;
# 需求3: 我们观察发现 when的字段是同一个, 且都是等于的判断, 则可以写成(如下形式): 语法糖写法.
select
*,
case category_id
when 'c001' then '电脑'
when 'c002' then '服装'
when 'c003' then '美妆'
else '其它'
end as category_name
from
product;
6.CTE表达式
6.1CTE的核心:
把查询结果临时的<<存储>>起来, 后续可以直接使用这个<<临时表>>, 简化操作.
6.2格式:
with 临时表名1 as (select …. 查询语句),
临时表名2 as (select …. 查询语句),
…..
临时表名3 as (select …. 查询语句)
select * from 临时表名1….;
6.3案例
# 需求1: 查看 有分类id 和 无分类id的商品信息.
select * from product where category_id is not null;
select * from product where category_id is null;
# 如下是CTE表达式写法.
with t1 as (select * from product where category_id is not null),
t2 as (select * from product where category_id is null)
select * from t1;
# 需求2: 回顾 窗口函数的 分组排名, 求TopN需求.
# 写法1: 子查询.
select * from (
select
*,
rank() over(partition by deptid order by salary desc) as rk
from day04.employee
) t1
where rk <= 2;
# 写法2: CTE表达式
with t1 as (select *, rank() over(partition by deptid order by salary desc) as rk from day04.employee)
select * from t1 where rk <= 2;
