MySQL入门
数据库概述【了解】
数据库概念: 存储数据的仓库,本质是一个文件系统
数据库分类: 关系型数据和非关系型数据库
关系型数据库: 必须遵循SQL规范,强调以二维表格的形式存储数据
举例: MySQL ORACLE DB2 SqlServer SQLite
非关系型数据: NoSQL不仅仅是SQL,强调以key-value形式存储数据
举例: HBase Redis MongoDB
MySQL是最好的 RDMS (Relational Database Management System，关系数据库管理系统) 应用软件，目前属于 Oracle 旗下产品，MySQL 是最流行的关系型数据库管理系统中的一个
本次使用MySQL8.0版本进行学习
SQL规范【熟悉】
SQL简介
SQL: 结构化查询语言, 是所有关系型数据库都要遵循的规范
大白话解释: 可以理解成sql是普通话,mysql,oracle等是方言
SQL分类
DDL: 数据定义语言:简称DDL(Data Definition Language)
作用: 用来定义数据库对象：数据库，表，列/字段等。
关键字: create，drop，alter等
DML: 数据操作语言:简称DML(Data Manipulation Language)
作用:用来对数据库中表的记录进行更新。
关键字: insert，delete，update等
DQL: 数据查询语言:简称DQL(Data Query Language)
作用:用来查询数据库中表的记录。
关键字: select，from，where等
DCL: 数据控制语言：简称DCL(Data Control Language)
用来定义数据库的访问权限和安全级别，及创建用户。
SQL通用语法【掌握】
1、SQL语句可以单行或多行书写，以分号结尾。
举例: select * from 表名 where 条件;
2、可使用空格和缩进来增强语句的可读性
select *
from 表名
where 条件;
3、MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
例如:select * from 表名 where 条件; SELECT * FROM 表名 WHERE 条件;
大小写切换快捷键: ctrl+shift+u
4、可以使用 /**/，--，# 的方式完成注释
/**/：多行注释，在注释区域内可以随意换行
-- 和# ：单行注释，写在语句开头，换行后注释截止。
注意: -- 后面必须有一个空格
单行注释快捷键: ctrl+/
多行注释快捷键: ctrl+shift+/
数据库增删改查操作【掌握】
==数据库英文单词: database==
知识点
创建数据库: create database [if not exists] 数据库名; 注意: 默认字符集就是utf8
删除数据库: drop database [if exists] 数据库名;
使用/切换数据库: use 数据库名;
查看所有的数据库名: show databases;
查看当前使用的数据库: select database();
查看指定库的建库语句: show create database 数据库名;
示例
# 创建数据库语法一
create database db1;
# 创建数据库语法二：如果不存在才创建；如果存在不会做任何的操作
create database if not exists db1;
create database if not exists db2;
# 创建数据库语法三：指定数据库的编码方法，推荐utf8，为了防止中文乱码
create database db3 charset="utf8";
# 注意：数据库中编码方式叫utf8，没有UTF-8的写法
create database db4 charset="UTF-8";
# 【推荐的写法】：同时有if not exists和编码器的设置
create database if not exists db5 charset="utf8";
# 列出MySQL服务器中有哪些数据库
show databases;
# 使用某个具体的数据库
use db1;
use db2;
# 查看目前使用的数据库是哪一个
select database();
# 【慎重】删除数据库
drop database db1;
# if exists如果数据库存在，我们才删除
drop database if exists db2;
drop database if exists db3;
drop database if exists db5;
常用数据类型【掌握】
可变（只能变小）长度的字符串类型: varchar(字符长度)。字符长度表示最大能存储多少个字符
整数类型: int 注意: 默认长度是11,如果int不够用就用bigint
浮点类型: float(python默认) 或者 double(java默认) decimal(默认是有效位数是10,小数后位数是0)
例如: decimal(5,2) 234.99 1.99。一般用在金额方面，因为float、double是有损精度。
https://blog.csdn.net/2301_79835671/article/details/137939219
日期时间: date datetime year
参考: https://cloud.tencent.com/developer/article/1023546
注意:
1- 针对数字类型的字段值，可以有引号也可以没有。推荐不带引号
2- 这对字符串类型的字段值，必须要有引号。单引号和双引号都行
总结：针对字段选择数据类型的时候，不需要非常的精细，合理选择即可。推荐数据范围选的大一点，千万不要过小。
create table db1.stu3(
id int,
age int,
high float,
name varchar(100),
birthday date,
money decimal(6,2)
);
表增删改查操作【掌握】
==表的英文单词: table==
知识点
创建表: create table [if not exists] 表名(字段1名 字段1类型 [字段1约束] , 字段2名 字段2类型 [字段2约束] ...);
删除表: drop table [if exists] 表名;
修改表名: rename table 旧表名 to 新表名;
注意: 修改表中字段本质都是修改表,咱们后面演示此处略
查看所有表: show tables;
查看指定表的建表语句: show create table 表名;
示例
# 创建一个演示的数据库
create database if not exists db1 charset="utf8";
# MySQL服务器 -> 多个数据库
# 每个数据库 -> 多张表
# 每张表 -> 多个字段、多条数据
use db1;
create table stu(
id int,
age int,
name varchar(100)
);
# 工作中的推荐写法：数据表名称的前面加上数据库的前缀
create table db1.stu2(
id int,
age int,
name varchar(100) # 最后一行的最后面不能加逗号
);
# 查看当前数据库中有哪些表
use db1;
show tables;
# 查看表结构
# 这里的desc是describe单词的缩写
desc db1.stu2;
# 下面的desc是descending 降序单词的缩写
# select * from db1.stu order by id desc ;
# 对表名进行重命名操作
# 语法 rename table 旧名称 to 新名称;
rename table db1.stu2 to db1.stu_2;
# 谨慎操作：删除表
drop table if exists db1.stu_2;
修改表中字段【掌握】
==列/纵队的英文单词:column==
知识点
注意: 操作字段本质就是在修改表
添加字段: alter table 表名 add [column] 字段名 字段类型 [字段约束];
删除字段: alter table 表名 drop [column] 字段名;
修改字段名和字段类型: alter table 表名 change [column] 旧字段名 新字段名 字段类型 [字段约束];
modify只修改字段类型: alter table 表名 modify [column] 字段名 字段类型 [字段约束];
查看字段信息: desc 表名;
示例
use db1;
# 准备数据表
create table if not exists db1.stu3(
id int,
age int,
high float,
name varchar(100),
birthday date,
money decimal(6,2)
);
# 1- 添加表字段
alter table db1.stu3 add addr varchar(200);
# 如果新字段的名称是关键词，需要加上反引号`
alter table db1.stu3 add `table` varchar(200);
# 后续使用也需要加反引号
select `table` from db1.stu3;
# 2- 修改表字段
# 2.1- 只改名称：原始字段的数据类型要写上
alter table db1.stu3 change addr address varchar(200);
# 2.2- 只改数据类型：原始字段的名称要写上
alter table db1.stu3 change high high double;
# 2.3- 同时改名称和数据类型
alter table db1.stu3 change `table` tb varchar(300);
# modify：只能修改字段数据类型，不能改名称
alter table db1.stu3 modify tb varchar(10);
# modify：不能改名称
# alter table db1.stu3 modify tb new_tb varchar(10);
# 3- 删除字段：一个个删
alter table db1.stu3 drop tb;
alter table db1.stu3 drop address;
表中记录操作DML【掌握】
知识点
插入数据记录: insert into 表名 (字段名...) values (具体值...) , (具体值...);
注意1: 具体值要和前面的字段名以及顺序一一对应上
注意2: 如果要插入的是所有字段,那么字段名可以省略(默认代表所有列都要插入数据)
注意3: 如果要插入多条记录,values后多条数据使用 逗号 分隔
修改数据记录: update 表名 set 字段名=值 [where 条件];
注意: 如果没有加条件就是修改对应字段的所有数据
删除数据记录: delete from 表名 [where 条件];
注意: 如果没有加条件就是删除所有数据
清空所有数据:
方式1: delete from 表名; 注意:此方式有警告
方式2: truncate [table] 表名; 注意: 此方式没有警告
注意: delete与truncate的区别
delete删除数据，会保留之前的自增的信息，继续往后增加
truncate删除数据，不会保留之前的自增的信息，重新从1开始自增。另外不支持带where过滤语句
示例
# 准备环境
create database if not exists db2 charset="utf8";
create table db2.student(
id int,
age int,
high float,
addr varchar(200)
);
# 1- 添加数据
# 写法一：一次给所有的字段添加一条数据
insert into db2.student values(1,18,1.78,"吉山村1024号"); # 写双引号没有问题，只是pycharm识别不了
insert into db2.student values(2,28,2.78,'吉山村2024号');
select * from db2.student;
# 注意：目前的写法必须给所有的字段传递值。因此下面的写法是错误的
insert into db2.student values(38,3.78,'吉山村3024号');
# 写法二：一次给指定的字段添加一条数据
insert into db2.student (age,high,addr) values(38,3.78,'吉山村3024号');
# 空字符串和null不是一个东西
insert into db2.student (id,addr) values(66,'');
# 写法三：一次给所有的字段添加多条数据
insert into db2.student
values
(4,44,4.12,'吉山村412号'),
(5,55,5.12,'吉山村512号'),
(6,66,6.12,'吉山村612号');
# 写法四：一次给指定的字段添加多条数据
insert into db2.student
(age,addr)
values
(34,'吉山村3012号'),
(35,'吉山村3012号'),
(36,'吉山村3012号');
# 2- 修改数据
# 对所有数据行的某些字段进行更新
update db2.student set high=1.99, addr='珠江新城珠江帝景';
# 对指定数据行的某些字段进行更新
update db2.student set age=200 where id<=5;
# 3- 删除数据
# 删除满足条件的数据
delete from db2.student where id<=5;
delete from db2.student where id is null;
# 清空表：写法一，一次删除整张表的所有数据
delete from db2.student;
# 清空表：写法二
/*
1- delete from 表名：只会删除表数据，但是表的元信息还会保留
2- truncate table 表名：是先彻底删除整张表，然后再重建。数据、元信息都会被删除。
如果确定要删除整张表的所有数据，推荐使用truncate table，效率很高。
*/
truncate table db2.student;
MySQL单表查询
DDL表中约束
==约束作用: 限制数据的插入和删除==
主键约束【掌握】
知识点:
主键约束关键字: primary key = 非空约束 + 唯一约束 + 索引(index)
主键约束特点: 限制主键插入的数据不能为空,不能重复
建表的时候添加主键约束: create table 表名(主键名 主键类型 primary key , 其他字段...);
注意: 一个表中最多有一个主键
示例:
# 演示各类约束
# 注意: 所有的约束都建议建表的时候添加
# 比如: 如果stu表中已经有重复数据,再去添加主键限制不能重复,添加失败!!!
insert into stu(id,name,age) values (1,'李四',18),(1,'王五',28),(1,'赵六',38);
alter table stu add PRIMARY KEY (id); # 报错
desc stu;
# 主键约束: 关键字: primary key 特点: 限制值非空唯一
# 添加主键约束方式1: 建表时候添加
create table stu1(
id varchar(100) PRIMARY KEY,
name varchar(100),
age int,
cls varchar(100)
);
# 查看表结构
desc stu1;
# 添加主键约束方式2: 建表后添加
create table stu2(
id varchar(100),
name varchar(100),
age int,
cls varchar(100)
);
# 添加主键约束
alter table stu2 add PRIMARY KEY (id);
# 查看表结构
desc stu2;
# 了解删除主键约束
# 删除非空约束
# 注意: 使用change直接删除主键,非空和唯一约束都没有删除
alter table stu1 change id id VARCHAR(100);
# 查看表结构
desc stu1;
# 注意: 使用drop直接删除主键,只是把唯一键删除,不能为空的约束保留
alter table stu2 drop PRIMARY KEY;
# 查看表结构
desc stu2;
# 再使用change删除非空约束
alter table stu2 change id id VARCHAR(100);
# 查看表结构
desc stu2;
# 了解主键约束的特点: 限制值不能为空,不能重复
insert into stu1(id,name) values (null,'张三'); # 报错Column 'id' cannot be null
insert into stu1(id,name) values (1,'张三'); # 成功
insert into stu1(id,name) values (1,'李四'); # 报错 Duplicate entry '1' for key 'PRIMARY'
图解:
主键自增
知识点:
自增关键字: auto_increment
自增特点: 默认从1开始,每次自动加1
注意: 如果插入数据的时候指定了id字段,可以使用null或者0占位表示自动使用自增
建表的时候添加主键自增约束: create table 表名(主键名 主键类型 primary key auto_increment , 其他字段...);
示例:
# 主键自增
# 添加主键自增方式1: 建表后添加
# 注意: 如果要设置主键自增,字段类型需要是整数类型
# 可以同时添加主键和自增,但是前提是表中没有主键
alter table stu2 change id id int PRIMARY KEY auto_increment;
# 注意: 可以单独添加自增,但是前提是表中已经有主键
alter table stu1 change id id int auto_increment;
# 添加主键方式2: 建表时候添加
create table stu3(
id int PRIMARY KEY auto_increment,
name varchar(100),
age int,
cls varchar(100)
);
# 查看表结构
desc stu3;
# 了解主键自增的特点: 每次自增1
# 如果设置了主键后,null或者0代表使用主键自增
insert into stu3(id,name) values (null,'张三'); # 成功 默认第一次编号自动生成了1
insert into stu3(id,name) values (0,'张三'); # 成功
insert into stu3(id,name) values (1,'李四'); # 报错 Duplicate entry '1' for key 'PRIMARY'
insert into stu3(name) values ('王五');
# 了解自增的删除
# 删除stu2表的自增,主键保留
alter table stu2 change id id int ;
# 删除stu1表的自增,主键保留
alter table stu1 change id id int ;
/*
结论: 如果想要删除主键和自增
1.先用change删除自增
2.再用drop删除主键中的唯一约束
3.最后用change删除非空约束
*/
# 删除stu3的主键和自增
alter table stu3 change id id int ;
alter table stu3 drop PRIMARY KEY ;
alter table stu3 change id id int ;
图解:
delete和truncate区别
知识点:
delete和truncate的区别?
共同点: 都能删除表中所有数据
不同点:
delete删除所有数据: 自增顺序保留,下次再插入的时候继续自增
truncate删除所有数据: 自增顺序重置,下次再插入数据的时候从1重新开始自增
示例:
# delete删除所有数据: 自增顺序保留,下次再插入的时候继续自增
delete from stu3; -- 警告2次(慎用)
# 如果再次插入数据,自增顺序按照删除前最后值继续
insert into stu3(name, age) VALUES ('王五',38);
insert into stu3(name, age) VALUES ('王五',38);
insert into stu3(name, age) VALUES ('王五',38);
# truncate删除所有数据: 自增顺序重置,下次再插入数据的时候从1重新开始自增
truncate stu3; -- 没有警告(慎用)如果真要清空所有数据建议用truncate
# 如果再次插入数据,自增重新从1开始自增
insert into stu3(name, age) VALUES ('王五',38);
insert into stu3(name, age) VALUES ('王五',38);
insert into stu3(name, age) VALUES ('王五',38);
非空约束
知识点:
非空约束关键字: not null
非空约束特点: 限制对应数据不能为空null
建表的时候添加非空约束: create table 表名(主键名 主键类型 primary key ,字段名 字段类型 not null , 其他字段...);
注意: 一个表中可以有多个非空约束
示例:
# 3.非空约束
# 创建表
create table stu4(
id int not null,
name varchar(100) not null,
age int
);
# 查看表结构
desc stu4;
# 演示非空约束特点: 限制数据不能为空null
insert into stu4 (id, name, age) VALUES (1,'张三',18); # 成功
insert into stu4 (id, name, age) VALUES (null,null,null); # 报错,因为id和name不能为空
# 了解建表后添加和删除非空约束
# 添加非空约束
alter table stu4 change age age int not null;
# 删除非空约束
alter table stu4 change age age int;
图解
唯一约束
知识点
唯一约束关键字: unique
唯一约束特点: 限制对应的数据不能重复
建表的时候添加唯一约束: create table 表名(主键名 主键类型 primary key ,字段名 字段类型 unique, 其他字段...);
注意: 一个表中可以有多个唯一约束
示例
# 4.唯一约束
# 创建表
create table stu5(
id int not null unique ,
name varchar(100) unique ,
age int
);
# 查看表结构
desc stu5;
# 演示唯一约束特点: 限制数据不能重复
insert into stu5 (id, name, age) VALUES (1,'张三',18); # 成功
insert into stu5 (id, name, age) VALUES (1,'李四',28); # 报错,因为id不能重复
insert into stu5 (id, name, age) VALUES (null,'王五',null); # 报错,因为id设置唯一约束同时也设置了非空约束
图解
默认约束
知识点:
默认约束关键字: default
默认约束特点: 可以提前给字段设置默认值
建表的时候添加默认约束: create table 表名(主键名 主键类型 primary key ,字段名 字段类型 default 默认值, 其他字段...);
注意: 一个表中可以有多个默认约束
示例:
# 5.默认约束
# 创建表
create table stu6(
id int primary key auto_increment,
name varchar(100) default 'admin',
pwd varchar(100) default '123456'
);
# 查看表结构
desc stu6;
# 演示默认约束特点: 如果用户没有指定字段值,可以使用提前设置好的默认值
insert into stu6(id) values (1);
insert into stu6(name) values ('张三');
insert into stu6(name,pwd) values ('李四','abcde');
# 了解建表后添加和删除默认约束
create table stu7(
id int primary key auto_increment,
name varchar(100) ,
pwd varchar(100)
);
# 添加默认约束
alter table stu7 change name name varchar(100) default 'admin';
alter table stu7 change pwd pwd varchar(100) default '123456';
# 删除默认约束
alter table stu7 change name name varchar(100);
alter table stu7 change pwd pwd varchar(100);
图解
单表查询操作【掌握】
准备数据
# 创建数据库: create database 库名;
CREATE DATABASE IF NOT EXISTS db2 CHARSET=utf8;
# 使用数据库: use 库名;
USE db2;
# 创建表: create table 表名(字段名 字段类型 [约束],...);
# 建测试表
CREATE TABLE IF NOT EXISTS products
(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品ID
name VARCHAR(24) NOT NULL, -- 商品名称
price DECIMAL(10, 2) NOT NULL, -- 商品价格
score DECIMAL(5, 2), -- 商品评分，可以为空
is_self VARCHAR(8), -- 是否自营
category_id INT -- 商品类别ID
);
CREATE TABLE IF NOT EXISTS category
(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品类别ID
name VARCHAR(24) NOT NULL -- 类别名称
);
# 插入数据: insert into 表名 (字段名,字段名) values(字段值,字段值),(字段值,字段值);
# 添加测试数据
INSERT INTO category
VALUES (1, '手机'),
(2, '电脑'),
(3, '美妆'),
(4, '家居');
INSERT INTO products
VALUES (1, '华为Mate50', 5499.00, 9.70, '自营', 1),
(2, '荣耀80', 2399.00, 9.50, '自营', 1),
(3, '荣耀80', 2199.00, 9.30, '非自营', 1),
(4, '红米note 11', 999.00, 9.00, '非自营', 1),
(5, '联想小新14', 4199.00, 9.20, '自营', 2),
(6, '惠普战66', 4499.90, 9.30, '自营', 2),
(7, '苹果Air13', 6198.00, 9.10, '非自营', 2),
(8, '华为MateBook14', 5599.00, 9.30, '非自营', 2),
(9, '兰蔻小黑瓶', 1100.00, 9.60, '自营', 3),
(10, '雅诗兰黛粉底液', 920.00, 9.40, '自营', 3),
(11, '阿玛尼红管405', 350.00, NULL, '非自营', 3),
(12, '迪奥996', 330.00, 9.70, '非自营', 3);
基础查询
知识点
基础查询关键字: select:查什么 from: 从哪儿查
基础查询格式: select [distinct] 字段名 | * from 表名;
[]:可以省略
|: 或者
*: 对应表的所有字段名
distinct: 去除重复内容
as : 可以给表或者字段起别名
示例
-- 需求1: 查看所有商品
select * from products;
select
id,
name,
price,
score,
is_self,
category_id
from products;
-- 需求2: 查看所有商品的名称和价格
select name,price from products;
-- 需求3: 查看所有商品的名称和价格,要求给字段名起别名展示
select name as 姓名,price as 价格 from products;
select name 姓名,price 价格 from products;
-- 需求4: 查看所有商品的名称和价格,要求给表名起别名并使用
select products.name,products.price from products;
-- 如果给表起了表名,必须用别名调用字段
select p.name,p.price from products as p;
select name,price from products as p;
-- 需求5: 查看所有的分类编号,要求去重展示
select DISTINCT category_id from products;
条件查询
知识点
条件查询关键字: where
条件查询基础格式: select 字段名 from 表名 where 条件;
比较运算符: > < >= <= != <>
逻辑运算符: and or not
范围 查询: 连续范围:between x and y 非连续范围: in(x,y)
模糊 查询: 关键字:like %:0个或者多个字符 _:一个字符
非空 判断: 为空: is null 不为空:is not null
比较查询
# 1.比较运算符: > < >= <= != <>
-- 需求1: 查询所有'自营'的商品
select * from products where is_self = '自营';
-- 需求2: 查询评分在'9.50'(不含)以上的商品
select * from products where score > 9.50;
-- 需求3: 查询评分在'9.50'(含)以上的商品
select * from products where score >= 9.50;
-- 需求4: 查询价格在999(不含)以下的商品
select * from products where price < 999;
-- 需求5: 查询价格在999(含)以下的商品
select * from products where price <= 999;
-- 需求6: 查询评分不等于9.30的商品
select * from products where score != 9.3;
select * from products where score <> 9.3;
逻辑查询
-- and: 并且 or:或者 not:取反
-- 需求1: 查询自营商品中所有价格大于2000的商品信息
select * from products where is_self = '自营' and price > 2000;
-- 需求2: 查询商品评分在9.0(含)-9.5(含)之间的商品信息
select * from products where score >= 9 and score <= 9.5;
-- 需求3: 查询商品价格在1000(含)到3000(含)之间的商品信息
select * from products where price >= 1000 and price <= 3000;
-- 需求4: 查询价格是999或者2199或者2399的商品
select * from products where price = 999 or price = 2199 or price = 2399;
-- 需求5: 查询商品是'华为Mate50'或者'荣耀80'的商品
select * from products where name = '华为Mate50' or name = '荣耀80';
-- 需求6: 查询商品不是自营的商品
select * from products where not is_self = '自营';
select * from products where is_self = '非自营';
-- 需求7: 查询商品不在1000到3000之间的商品
select * from products where not (price >= 1000 and price <= 3000);
select * from products where price < 1000 or price > 3000;
范围查询
-- 需求1: 查询商品价格在1000(含)到3000(含)之间的商品信息
select * from products where price BETWEEN 1000 and 3000;
-- 注意: 以下语法是错误的
select * from products where 1000 <= price <= 3000;
-- 需求2: 查询商品不在1000到3000之间的商品
select * from products where price not BETWEEN 1000 and 3000;
-- 需求3: 查询价格是999或者2199或者2399的商品
select * from products where price in(999,2199,2399);
-- 需求4: 查询商品是'华为Mate50'或者'荣耀80'的商品
select * from products where name in('华为Mate50','荣耀80');
模糊查询
-- 关键字: like 符号 %:任意多个字符 _:任意1个字符
-- 需求1: 查询商品名称以'华'开头的商品信息
select * from products where name like '华%';
-- 需求2: 查询商品名称以'华'开头并且8个字符的商品信息
select * from products where name like '华_______';
-- 需求3: 查询商品名称以'66'结尾商品信息
select * from products where name like '%66';
-- 需求4: 查询商品名称中包含'兰'字的商品信息
select * from products where name like '%兰%';
-- 需求5: 查询商品名称中第3个字是'兰'字的商品信息
select * from products where name like '__兰%';
非空判断
/*
null在sql中代表空的,没有任何意义的意思
如果数据中有空字符串'',字符串'null',一定要注意,他们和sql中的null不是一回事!!!
*/
-- 需求1:查询未评分的商品信息
select * from products where score is null;
-- 注意: 以下方式是错误的
select * from products where score = null;
select * from products where score = '';
select * from products where score = 'null';
select * from products where score is 'null';
-- 为了方便演示null和'','null'的区别,可以插入部分测试数据
insert into products(name,price) values('拯救者Y9000','9999');
insert into products(name,price) values('null','99');
insert into products(name,price) values('',0);
-- 需求2:查询商品名称是'null'的商品信息
select * from products where name = 'null';
-- 需求3:查询商品名称是''的商品信息
select * from products where name = '';
排序查询
知识点
排序查询关键字: order by
排序查询基础格式: select 字段名 from 表名 order by 排序字段名 asc|desc;
asc : 升序(默认)
desc: 降序
排序查询进阶格式: select 字段名 from 表名 order by 排序字段1名 asc|desc , 排序字段2名 asc|desc;
注意: 如果order by后跟多个排序字段,先按照前面的字段排序,如果有相同值的情况再按照后面的排序规则排序
示例
-- 示例1：查询所有商品，并按照评分从高到低进行排序
SELECT * FROM products ORDER BY score DESC;
-- 示例2：查询所有商品，先按照评分从高到低进行排序，评分相同的再按照价格从低到高排序
SELECT * FROM products ORDER BY score DESC, price;
聚合函数
知识点
聚合函数: 又叫统计函数,也叫分组函数
常用聚合函数: sum() count() avg() max() min()
聚合查询基础格式: select 聚合函数(字段名) from 表名; 注意: 此处没有分组默认整个表就是一个大的分组
注意: 聚合函数(字段名)会自动忽略null值,以后统计个数一般用count(*)统计因为它不会忽略null值
示例
# 注意: 别名不建议用中文,以下仅仅为了演示
-- 示例1：统计当前商品一共有多少件
SELECT count(id) FROM products;
SELECT count(*) FROM products;
-- 示例2：对商品评分列进行计数、求最大、求最小、求和、求平均
SELECT
COUNT(score) AS cnt,
MAX(score) AS max_score,
MIN(score) AS min_score,
SUM(score) AS total_score,
AVG(score) AS avg_score
FROM products;
-- 示例3：统计所有非自营商品评分的平均值
SELECT
is_self,
AVG(score)
FROM
products
WHERE
is_self = '非自营';
-- 注意：count(0)和count(null)的结果不相同
select
count(id) as cnt_id_1,
count(*) as cnt_2,
count(1) as cnt_3,
count(0) as cnt_4,
count(null) as cnt_5,
max(price) as max_price,
min(price) as min_price,
sum(price) as sum_price,
avg(price) as avg_price
from db2.products;
select
count(name) as cnt,
max(name) as max_val,
min(name) as min_val
from db2.products;
分组查询
==什么时候需要使用分组？==
==遇到每个、每次、每种、各个、各自等，就需要使用分组group by。==
知识点
分组查询关键字: group by
分组查询基础格式: select 分组字段名,聚合函数(字段名) from 表名 group by 分组字段名;
注意: select后的字段名要么在group by后面出现过,要么写到聚合函数中,否则报错...sql_mode=only_full_group_by
分组查询进阶格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];
where和having的区别?
书写顺序: where在group by 前,having在group by后
执行顺序: where在group by 前,having在group by后
分组函数: where后不能跟聚合条件,只能跟非聚合条件,having后可以使用聚合条件,也可以使用非聚合条件(不建议)
应用场景: 建议大多数过滤数据都采用where,只有当遇到聚合条件的时候再使用having
使用别名: where后不能使用别名,having后可以使用别名
示例
-- 示例1：统计每个分类的商品数量
SELECT
category_id,
-- ② 再聚合：对每一组的id进行count计数
COUNT(id) AS cnt
FROM products
-- ① 先分组：按照category_id进行分组
GROUP BY category_id;
-- 示例2：统计每个分类中自营和非自营商品的数量
SELECT
category_id,
is_self,
COUNT(id) AS cnt
FROM products
GROUP BY category_id, is_self;
-- 示例3：统计每个分类商品的平均价格，并筛选出平均价格低于1000的分类
SELECT category_id,
-- 再聚合
AVG(price)
FROM products
-- 先分组
GROUP BY category_id
-- 对分组聚合的结果进行筛选
HAVING AVG(price) < 1000;
-- 示例4：统计自营商品中，每个分类的商品的平均价格，并筛选出平均价格高于2000的分类
SELECT category_id,
AVG(price) AS avg_price
FROM products
WHERE is_self = '自营' -- where在分组之前对数据进行过滤
GROUP BY category_id
HAVING AVG(price) > 2000; -- having在分组聚合之后对数据进行过滤
SELECT category_id,
AVG(price) AS avg_price
FROM products
WHERE is_self = '自营'
GROUP BY category_id
HAVING avg_price > 2000; -- MySQL在HAVING中可以使用聚合函数结果的别名！
limit查询
知识点
分页查询关键字: limit
分页查询基础格式: select 字段名 from 表名 limit m,n;
m: 起始索引,默认从0开始 m = (页数-1)*n
n: 本次查询的条数。表示每页展示多少条数据。
注意: limit能完成topN需求,但是不能考虑到并列情况,此问题可以使用后期学习的开窗函数解决
示例
- 示例1：获取所有商品中，价格最高的商品信息
SELECT
*
FROM products
ORDER BY price DESC
LIMIT 1; -- LIMIT 0, 1;
-- 示例2：将商品数据按照价格从低到高排序，然后获取第2页内容（每页3条）
SELECT
*
FROM products
ORDER BY price
LIMIT 3, 3;
-- 示例3：当分页展示的数据不存在时，不报错，只不过查询不到任何数据
SELECT * FROM products LIMIT 20, 10;
SQL顺序
书写顺序: SELECT -> DISTINCT -> 聚合函数 -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
执行顺序: FROM -> WHERE -> GROUP BY -> 聚合函数 -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT
-- 执行顺序：知道不同语法的编写顺序是什么样；理解复杂SQL的执行结果是什么样的
select
category_id,
sum(price) as sum_price
from db2.products
where price<=6000
group by category_id
having sum_price<=12000
order by sum_price
limit 1;
MySQL多表查询
外键【了解】
外键约束
外键概念: 在从表(多方)创建一个字段，引用主表(一方)的主键,对应的这个字段就是外键。
外键特点：
1:从表外键的值是对主表主键的引用。
2:从表外键类型，必须与主表主键类型一致。
存储引擎
==注意: 只有innodb存储引擎支持外键约束!!!==
如何查看当前的引擎是啥？
使用show engines;
注意1: 关闭mysql服务,再去修改!!!
注意2: 修改存储引擎后,只对后面新建的表有效!!!
默认已经是Innodb了，不用改。可以按照如下步骤操作
1- 停止MySQL服务
systemctl stop mysqld
2- 修改配置文件
在配置文件/etc/my.cnf中的[mysqld]的最后面，增加如下内容
default-storage-engine=InnoDB
3- 启动MySQL服务
systemctl start mysqld
外键约束添加和删除
注意: 修改只对之后建表生效!!!
建表时添加外键约束: CONSTRAINT [外键约束名] FOREIGN KEY (外键字段名) REFERENCES 主表名 (主表主键字段名)
建表后添加外键约束:alter table 从表名 add CONSTRAINT [外键约束名] FOREIGN KEY (外键字段名) REFERENCES 主表名 (主表主键字段名)
删除外键约束: alter table 从表名 drop FOREIGN KEY 外键约束名;
查看依赖图
外键约束特点
外键约束关键字: foreign key
外键约束语法: CONSTRAINT [外键约束名] FOREIGN KEY (外键字段名) REFERENCES 主表名 (主表主键字段名);
外键约束作用:
限制从表插入数据: 从表插入数据的时候如果外键值是主表主键中不存在的,就插入失败
限制主表删除数据: 主表删除数据的时候如果主键值已经被从表外键的引用,就删除失败
外键约束好处: 保证数据的准确性和完整性
示例
# 外键总结：了解，工作中用不到。因为会导致数据的添加、删除、修改的效率会变低
# 1- 查看MySQL支持的存储引擎（数据的存储格式）
show engines;
# 2- 创建有外键约束的两张表
create database if not exists db4 charset='utf8';
# 写法一：建表的时候指定外键约束
# 主表：商品分类表
create table if not exists db4.category(
cid varchar(100) primary key,
cname varchar(100)
);
# 从表：商品信息表
create table if not exists db4.products(
pid varchar(100) primary key,
pname varchar(100),
price double,
category_id varchar(100) comment '外键',
# 外键约束：外键约束的名称自动生成
constraint foreign key (category_id) references db4.category(cid)
);
create table if not exists db4.products2(
pid varchar(100) primary key,
pname varchar(100),
price double,
category_id varchar(100) comment '外键',
# 外键约束：外键约束的名称手动设置
constraint my_fk_1 foreign key (category_id) references db4.category(cid)
);
# 写法二：建完表以后再添加外键约束
# 从表：商品信息表
create table if not exists db4.products3(
pid varchar(100) primary key,
pname varchar(100),
price double,
category_id varchar(100) comment '外键'
);
alter table db4.products3 add constraint foreign key (category_id) references db4.category(cid);
# 3- 删除外键约束
alter table db4.products2 drop foreign key my_fk_1;
alter table db4.products3 drop foreign key products3_ibfk_1;
# 4- 有外键约束的情况下：添加数据
# 正确顺序：先主表，再从表
insert into db4.category values ('1','手机');
insert into db4.products values ('1001','滑为',99.9,'1');
# 错误顺序
insert into db4.products values ('1002','大米',199.9,'2');
insert into db4.category values ('2','食品');
# 5- 有外键约束的情况下：删除数据
# 正确顺序：先从表，再主表
delete from db4.products where pid='1001';
delete from db4.category where cid='1';
# 错误顺序
delete from db4.category where cid='2';
delete from db4.products where pid='1002';
多表查询【掌握】
连接查询
数据准备
-- 创建数据库
create database if not exists db5 charset='utf8';
# 使用库
use db5;
# 建测试表
CREATE TABLE products
(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品ID
name VARCHAR(24) NOT NULL, -- 商品名称
price DECIMAL(10, 2) NOT NULL, -- 商品价格
score DECIMAL(5, 2), -- 商品评分，可以为空
is_self VARCHAR(8), -- 是否自营
category_id INT -- 商品类别ID
);
CREATE TABLE category
(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品类别ID
name VARCHAR(24) NOT NULL -- 类别名称
);
# 添加测试数据
INSERT INTO category
VALUES (1, '手机'),
(2, '电脑'),
(3, '美妆'),
(4, '家居');
INSERT INTO products
VALUES (1, '华为Mate50', 5499.00, 9.70, '自营', 1),
(2, '荣耀80', 2399.00, 9.50, '自营', 1),
(3, '荣耀80', 2199.00, 9.30, '非自营', 1),
(4, '红米note 11', 999.00, 9.00, '非自营', 1),
(5, '联想小新14', 4199.00, 9.20, '自营', 2),
(6, '惠普战66', 4499.90, 9.30, '自营', 2),
(7, '苹果Air13', 6198.00, 9.10, '非自营', 2),
(8, '华为MateBook14', 5599.00, 9.30, '非自营', 2),
(9, '兰蔻小黑瓶', 1100.00, 9.60, '自营', 3),
(10, '雅诗兰黛粉底液', 920.00, 9.40, '自营', 3),
(11, '阿玛尼红管405', 350.00, NULL, '非自营', 3),
(12, '迪奥996', 330.00, 9.70, '非自营', 3),
(13, '百草味紫皮腰果',9,NULL,NULL,NULL);
交叉连接【了解】
交叉连接关键字: cross join
显式交叉连接格式: select * from 左表 cross join 右表;
隐式交叉连接格式: select * from 左表,右表;
注意: 交叉连接了解即可,因为它本质就是一个错误,又叫笛卡尔积(两个表记录数的乘积)
注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表
-- 1.交叉连接(笛卡尔积): 工作中慎用!!!
# 关键字: cross join
# 隐式交叉连接格式: select 字段名 from 左表,右表;
SELECT *
FROM
products,
category;
# 显式交叉连接格式: select 字段名 from 左表 cross join右表;
SELECT *
FROM
products
CROSS JOIN category;
内连接
各种连接条件放置地方总结，适用于所有的连接方式：
什么放在on里面？如果是多张表之间的关联过滤，放在on中
什么放在where里面？如果是对某张表进行单独的过滤，放在where中
内连接关键字: inner join ... on
显式内连接格式: select * from 左表 inner join 右表 on 关联条件;
隐式内连接格式（了解）: select * from 左表 , 右表 where 关联条件;
注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表
# 需求：将类别对应的商品信息查询出来
# 写法一：【推荐写法】inner join关键词
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c
inner join
db5.products p
on c.id=p.category_id;
# 写法二：省略inner关键词
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c
join
db5.products p
on c.id=p.category_id;
# 写法三：省略inner join关键词。不能写on，需要改成where
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c, db5.products p
where c.id=p.category_id;
左外连接
左右连接总结：
1- 核心表（主要表）是商品详情表
2- 次要表是分类信息表
3- 在左连接的场景下，将核心表放在left join关键词左边；次要表放在右边
4- 在右连接的场景下，将核心表放在right join关键词右边；次要表放在左边
5- 左连接和右连接实际效果都是一样，实际工作里基本都是在左连接
内连接关键字: left outer join ... on
左外连接格式: select * from 左表 left outer join 右表 on 关联条件;
右外连接
内连接关键字: right outer join ... on
左外连接格式: select * from 左表 right outer join 右表 on 关联条件;
# 1- 左连接
/*
左连接查询总结
1- 以左边的表为主，也就是左表中的所有数据都查询出来
2- 右表中能够与左表关联上的数据，能够查出来
3- 右表中不能够与左表关联上的数据，不查出来，字段值以null展示
*/
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c
left join
db5.products p
on c.id = p.category_id;
# 2- 右连接
# 右连接总结：与左连接相似，核心区别是以右表为主。写的很少
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c
right join
db5.products p
on c.id = p.category_id;
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.products p
right join
db5.category c
on c.id = p.category_id;
全外连接
注意: mysql中没有full outer join on这个关键字,所以不能用它来完成全外连接!
所以只能先查询左外连接和右外连接的结果,然后用union或者union all来实现!!!
union : 默认去重
union all: 不去重
# 3- union和union all的区别
# union会去重
select 10
union
select 10;
# union all不会去重
select 10
union all
select 10;
# 4- 全连接 full join
/*
MySQL的全连接总结：MySQL中不支持full join；可以通过 左连接 union 右连接 实现
*/
/*select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c
full join
db5.products p
on c.id = p.category_id;*/
# 左连接
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c left join db5.products p on c.id=p.category_id
union
# 右连接
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c right join db5.products p on c.id=p.category_id;
# union前后左连接、右连接 都行
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.products p right join db5.category c on c.id=p.category_id
union
select
c.id as cid,
c.name as cname,
p.id as pid,
p.name as pname
from db5.category c right join db5.products p on c.id=p.category_id;
自连接查询【熟悉】
解释: 两个表进行关联时，如果左表和右边是同一张表，这就是自关联。
注意: 自连接必须起别名!
- 代码
-- 查询'广东省'下所有城市
# select * from db4.areas where title='广东省';
# select * from db4.areas where pid='440000';
select
p.id as pid,
p.title as pt,
c.title as ct,
c.id as cid
from db4.areas p # 当 省用
join db4.areas c # 当 市用
on c.pid = p.id and p.title='广东省';
-- 查询'广州市'下所有的区县
select
p.id as pid,
p.title as pt,
c.title as ct,
c.id as cid
from db4.areas p # 当 市用
join db4.areas c # 当 区县用
on c.pid = p.id and p.title='广州市';
-- 查询'广东省'下所有的市,以及市下面的区县信息
select
p.title as `省`,
c.title as `市`,
r.title as `县`
from db4.areas p /*当 省用*/
join db4.areas c /*当 市用*/ on c.pid=p.id and p.title='广东省'
join db4.areas r /*当 县用*/ on r.pid=c.id;
-- 需求1: 求每个月和上月的差额
select
s1.month,
s1.revenue - s2.revenue as diff
from db4.sales s1 # 当前月
join db4.sales s2 # 当前月的上个月
on s1.month - s2.month=1;
-- 需求2: 求截止到当月累计销售额
select
s1.month,
sum(s2.revenue) as result
from db4.sales s1 # 当前月
join db4.sales s2 # 前面的月份
on s1.month>=s2.month
group by s1.month;
子查询【熟悉】
子查询:在一个 SELECT 语句中，嵌入了另外一个 SELECT 语句，那么被嵌入的 SELECT 语句称之为子查询语句，外部那个SELECT 语句则称为主查询。
作用: 子查询是辅助主查询的。
子查询的结果充当主查询的条件
子查询的结果充当主查询的数据源(临时表)
子查询的结果充当主查询的查询字段
-- 需求: 求商品价格大于平均价的商品信息
/*
需求分析
1- 计算所有商品的平均价
2- 所有商品的价格与平均价进行对比，过滤出满足要求的数据
*/
select avg(price) as avg_price from db5.products;
select
*
from db5.products where price>2638.530769;
select
*
from db5.products where price>(select avg(price) as avg_price from db5.products);
-- 需求: 求商品价格最高的商品信息(考虑并列情况)
/*
1- 统计所有商品的最高价是多少
2- 所有商品的价格与最高价进行对比，过滤出满足要求的数据
*/
select
*
from db5.products where price=(select max(price) as max_price from db5.products);
-- 查询'广东省'下所有城市
select id from db4.areas where title='广东省';
select * from db4.areas where pid='440000';
select
*
from db4.areas where pid=(select id from db4.areas where title='广东省');
-- 需求: 计算每个学生的分数和整体平均分的差值
/*
1- 计算所有学生的平均分
2- 每个学生的分数；与平均分进行减法运算
*/
select avg(score) as avg_score from db4.students;
select
name,
score - (select avg(score) as avg_score from db4.students) as diff
from db4.students;
-- 需求: 查询各个分类中商品的平均价格,要求结果中包含分类名称
/*
1- 对 商品表 按照类别ID进行分组统计，得到分类和平均价
2- 上面的结果 和 分类表进行关联查询。商品表为主表
*/
select
category_id,
avg(price) as avg_price
from db5.products
group by category_id;
# 写法一：子查询的写法
select
t1.avg_price,
t1.category_id,
c.name
from (
select
category_id,
avg(price) as avg_price
from db5.products
group by category_id
) t1
left join db5.category c
on t1.category_id=c.id;
# 写法二：with as语法。如果子查询很多的时候推荐使用该写法
with t1 as (
select
category_id,
avg(price) as avg_price
from db5.products
group by category_id
)
select
t1.avg_price,
t1.category_id,
c.name
from t1 left join db5.category c
on t1.category_id=c.id;
快速建表复制数据【了解】
# 只复制表结构
create table if not exists db4.new_stu_1 like db4.students;
# 只复制数据
insert into db4.new_stu_1 select * from db4.students;
# 复制表结构 + 复制数据：只要是一个DQL就行
create table if not exists db4.new_stu_2
as
select
name,score,
score - (select avg(score) from db4.students) as diff
from db4.students;
-- 需求: 把db4中的products商品表根据分类拆分成多个表
# 单张表的数据量很大的时候，需要分库分表
create table db5.c1 as select * from db5.products where category_id = 1;
create table db5.c2 as select * from db5.products where category_id = 2;
create table db5.c3 as select * from db5.products where category_id = 3;
MySQL函数【熟悉】
内置函数简介
在MySQL中有很多内置函数，除了之前学习的聚合函数之外，还有很多其他内置函数：数值函数、字符串函数、时间日期函数、流程控制函数、加解密函数、开窗函数等。
问题：内置函数该如何学习？
熟悉常用的内置函数，其他用到再查帮助文档
官网文档：https://dev.mysql.com/doc/refman/8.0/en/functions.html
在mysql命令行或DataGrip软件中通过 HELP ‘函数名’ 查看指定函数的帮助文档
示例: help ‘count’;
数值函数
数值函数分类：
小数位数处理
ROUND、FORMAT、TRUNCATE、FLOOR、CEIL
求余数、求幂、随机数
MOD、POW、RAND常用：ROUND
select
# round四舍五入
round(1.999),
round(1.234),
# 地板。向下取整。取比参数值小的 最大整数
floor(1.999),
floor(1.234),
# 天花板。向上取整。取比参数值大的 最小整数
ceil(1.999),
ceil(1.234),
# format对数值进行四舍五入的处理成货币的表示形式
format(123456.789654, 4),
format(123456.789654, 4, 'zh_CN'),
# 截断。不会进行四舍五入，第2个参数表示的是保留的小数位数
truncate(123456.789654, 4),
# 求余数
mod(9,4),
# 幂次方。下面的含义是2³
pow(2,3),
# 生成随机数。rand(seed)seed是随机数种子。如果设置了随机数种子，那么产生的随机数会固定不变。控制变量法
rand(),
rand(516)
;
字符串函数
字符串函数分类：
大小写转换、反转
LOWER、UPPER
字符串反转、拼接、局部替换
REPEAT、CONCAT、CONCAT_WS、REPLACE
字符串截取，字符串的字符个数以及存储长度
SUBSTR、SUBSTRING、LEFT、RIGHT
字符串长度
CHAR_LENGTH、LENGTH
select
# 字符串拼接
concat('h','e','l','l','o'),
# 使用指定的分隔符对字符串内容进行拼接。with separator
concat_ws('_','h','e','l','l','o'),
# 替换。replace(原始内容,要替换的,新内容)
replace('heLLo','L','Y'),
# 字符串截取。substr(原始内容,截取的开始位置)，注意位置从1开始数。substring与substr的作用完全相同
substr('abcdefg',3),
substring('abcdefg',3),
# 全部转小写
lower('heLLo'),
# 全部转大写
upper('heLLo'),
# 将前面的字符串内容重复多少次
repeat('heima',3),
# 从左边开头开始数，截取指定长度
left('abcdefg',4),
# 从右边尾部开始数，截取指定长度
right('abcdefg',4),
# 获得字符的个数
char_length('abc'),
# 字符串占用的内存字节大小
length('abc'),
char_length('黑马'),
length('黑马')
;
时间日期函数
时间日期函数分类：
获取当前时间的函数，比如当前的年月日
NOW、CURRENT_DATE、CURRENT_TIME
计算时间差的函数，比如两个日期之间相差多少天，一个日期90天后是几月几号
DATE_ADD、DATE_SUB、DATEDIFF、TIMESTAMPDIFF
获取年月日的函数，从一个时间中提取具体的年份、月份等
YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEKDAY
时间转换的函数，比如将2021-10-05转换为时间戳
字符串和时间的转换： DATE_FORMAT、STR_TO_DATE
时间戳和时间的转换： UNIX_TIMESTAMP、FROM_UNIXTIME
时间戳（数字）：某个时间距离UTC时区的1970-01-01 00:00:00 过去了多久（通常以秒或毫妙为单位）。
比如：东八区的2023-06-01 17:20:44距离UTC时区的1970-01-01 00:00:00 已经过去了 1685611244 秒
select
-- 当前的日期时间
now(),
-- 当前的日期
current_date(),
-- 当前的时间
current_time(),
-- 获取当前时间戳
unix_timestamp(),
-- 日期时间加上一部分时间
date_add('2025-09-09 17:03',interval 9 day),
date_add('2025-09-09 17:03',interval -9 day),
date_sub('2025-09-09 17:03',interval 9 day),
-- 两个时间的天数差值：大的日期在前，小的日期在后
datediff('2025-09-09 17:03','2025-09-01 10:03'), # 8
datediff('2025-09-01 10:03','2025-09-09 17:03'), # -8
-- 指定内容的差值：小的在前，大的在后
timestampdiff(day,'2025-09-09 17:03','2025-09-01 10:03'), # -8
-- 获取年份
year('2025-09-09 17:03'),
-- 需求 将日期时间变成 01/09/25 17:03:26
date_format('2025-09-01 17:03:26','%d/%m/%y %k:%i:%S');
条件判断操作
case
when 条件1 then 值1
when 条件2 then 值2
….
else 值n
end
IF(条件, 值1, 值2)：条件成立，IF的结果就是值1，否则结果就是值2
==注意：CASE…END中只有两种条件选择时，可以使用IF函数替代==
/* 成绩等级如下： 优秀：90分及以上 良好：80-90，包含80 中等：70-80，包含70 及格：60-70，包含60 不及格：60分以下 */ -- 注意：case when中表示等于，不能使用两个=，只能使用一个 select name,score, case when score>=90 then '优秀' when score>=80 and score<90 then '良好' when score>=70 and score<80 then '中等' when score>=60 and score<70 then '及格' else '不及格' end as level from db4.students; /* if(判断条件,True情况下执行的内容,False情况下执行的内容) */ select name,score, if(score>=90,'优秀','良好') as level from db4.students;
开窗函数
==作用：查询每一行数据时，使用指定的窗口函数对每行关联的一组数据进行处理。==
基本语法：
OVER (ORDER BY 列名, …)
OVER(…)的作用就是设置每一行数据关联的一组数据范围，OVER()时，每行关联的数据范围都是整张表的数据。表示使用的窗口函数，窗口函数可以使用之前已经学过的聚合函数，比如COUNT、SUM、AVG、MAX、MIN等，也可以ROW_NUMBER、RANK、DENSE_RANK等，后面会依次介绍。 常用排序函数:
**RANK()**：产生的排名序号 ，有并列的情况出现时序号不连续
DENSE_RANK() ：产生的排序序号是连续的，有并列的情况出现时序号会重复
ROW_NUMBER() ：返回连续唯一的行号，排名序号不会重复
# 需求：计算每个学生的 Score 分数和所有学生整体平均分的差值。
select
name,
avg(Score) over() as avg_score,
score-avg(Score) over() as diff
from db4.students;
# 需求：计算每个值占整体之和的占比
select
name,
score,
sum(Score) over() as sum_score,
Score/(sum(Score) over()) as rate
from db4.students;
# 需求：计算每个学生的 Score 分数和同性别学生平均分的差值
# partition by对数据进行分组，得到多个独立的窗口
select
name,
Score,
avg(Score) over(partition by Gender) as avg_score,
Gender,
score - avg(Score) over(partition by Gender) as diff
from db4.students;
# 需求：根据学生的成绩进行降序排序，然后取TOP4的成绩（考虑成绩并列的情况）
select
*
from db4.students
order by Score desc
limit 4;
/*
row_number：不管元素值是否相同，就是单调的进行编号
rank：如果元素值相同，序号相同，而且会占用后面的序号资格
dense_rank：如果元素值相同，序号相同，而且不会占用后面的序号资格
*/
select * from (
select
name,
score,
row_number() over (order by Score desc) as rs1,
rank() over (order by Score desc) as rs2,
dense_rank() over (order by Score desc) as rs3
from db4.students
) as tmp
where rs3<=4;
# 需求：获取每种性别中考试成绩排名第二高的学生（不考虑成绩并列的情况）
/*
1- partition by功能与group by的功能是一样的，对整张表的数据进行分组，得到多个窗口
2- 窗口里面的order by，只对对应的窗口进行局部的排序。跟在from后面的order by对整张表的所有数据进行排序
*/
select * from (
select
name,
Gender,
Score,
row_number() over (partition by Gender order by Score desc) as rs
from db4.students
)tmp
where rs=2;
python与MySQL交互【熟悉】
pymysql是一个纯python实现的mysql客户端库,提供了python程序中操作mysql数据库的操作
为什么要学习PyMySQL
如何实现将100000条数据插入到MySQL数据库?
答案:
如果使用之前学习的MySQL客户端来完成这个操作，那么这个工作量无疑是巨大的，我们可以通过使用程序代码的方式去连接MySQL数据库，然后对MySQL数据库进行增删改查的方式，实现100000条数据的插入，像这样使用代码的方式操作数据库就称为数据库编程。
数据准备
-- 创建 "京东" 数据库
create database if not exists jing_dong charset=utf8;
-- 使用 "京东" 数据库
use jing_dong;
-- 创建一个商品goods数据表
create table if not exists goods(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0,
is_show bit not null default 1,
is_saleoff bit not null default 0
);
-- 向goods表中插入数据
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
# 创建用户表
create TABLE if not exists user(
id int PRIMARY KEY AUTO_INCREMENT,
user varchar(30),
pwd varchar(30)
);
insert into user(user,pwd) VALUE ('root','123456');
CREATE TABLE account (
id INT PRIMARY KEY,
balance DECIMAL(10, 2)
);
# 插入测试数据
INSERT INTO account VALUES (1, 1000.00);
INSERT INTO account VALUES (2, 8000.00);
INSERT INTO account VALUES (3, 5000.00);
INSERT INTO account VALUES (4, 3000.00);
安装pymysql库
方式一
在自己电脑上执行
命令: pip install pymysql==1.1.1 -i https://pypi.tuna.tsinghua.edu.cn/simple/
方式二
pymysql使用
知识点:
pymysql使用步骤:
1.导入模块
2.创建连接
3.创建游标，理解为session会话，就是一个具体与数据库的交换的对象
4.执行sql
5.关闭游标
6.关闭连接
查询操作
知识点:
总结：
1- fetchone、fetchall、fetchmany内部有个指针，指向到你目前取到的数据行
2- fetchone：一次只取一条
3- fetchall：一次取出所有的数据
4- fetchmany(size)：一次最多取size条数据
示例:
# 1- 导包
import pymysql
if __name__ == '__main__':
# 2- 创建与数据库的连接
# 注意：charset可以设置为utf8。没有utf-8
conn = pymysql.connect(
user="root",
password="root",
host="127.0.0.1",
port=3306,
database="jing_dong",
charset="utf8"
)
# 3- 通过连接创建游标
cur = conn.cursor()
# 4- 通过游标执行SQL语句
sql = """
select
name,cate_name,
avg(price) as avg_price
from jing_dong.goods
where id<=18
group by name,cate_name
having avg_price>=2999
order by name
limit 2,3
"""
cur_result = cur.execute(sql)
# print(cur_result)
# 5- 取值：通过游标去取数据
"""
总结：
1- fetchone、fetchall、fetchmany内部有个指针，指向到你目前取到的数据行
2- fetchone：一次只取一条
3- fetchall：一次取出所有的数据
4- fetchmany(size)：一次最多取size条数据
"""
# result = cur.fetchone()
# print(type(result))
# print(result) # 元组
# print(result[0],result[1],result[2])
# result = cur.fetchone()
# print(result)
#
# print("-" * 30)
# result = cur.fetchall()
# print(result)
# print(type(result))
print("-"*30)
result = cur.fetchmany(2)
print(result)
print(type(result))
print("-" * 30)
result = cur.fetchmany(2)
print(result)
# 6- 关闭游标
cur.close()
# 7- 关闭连接
conn.close()
增删改操作
知识点:
拓展: 事务: 多个操作要么都成功,都失败 (特性: ACID原理，原子性 一致性 隔离性 持久性)
存储引擎: innodb支持事务 myisam不支持事务
增删改操作: 如果数据库底层是innodb引擎,必须手动commit提交
数据提交:commit()
数据回滚:rollback()
增:
# 1- 导入模块
import pymysql
import time
def insert():
# 2- 创建连接
conn = pymysql.connect(
user="root",
password="123456",
host="192.168.88.100",
port=3306,
database="jing_dong",
charset="utf8"
)
# 3- 创建游标
cur = conn.cursor()
# 4- 通过游标执行具体的SQL语句
try:
# 4.1- 执行SQL
sql = "insert into user(user,pwd) VALUE ('lisi','123456')"
result = cur.execute(sql)
print(f"sql执行结果：{result}")
# time.sleep(6)
# 模拟发生异常的情况
# a = 1/0
# print("上面模拟了异常")
except:
# 4.3- 如果无法成功的提交事物，那么需要将前面的SQL操作撤销掉
conn.rollback()
print("回滚事物")
else:
# 4.2- 提交事物
conn.commit()
finally:
print("关闭资源")
# 5- 关闭游标
cur.close()
# 6- 关闭连接
conn.close()
if __name__ == '__main__':
insert()
改:
import pymysql
# 将分散的代码抽取为独立的函数/方法的快捷键：ctrl+alt+M
def my_insert():
# 创建与对应数据库的连接
# host如果是本地，可以写localhost或者127.0.0.1
conn = pymysql.connect(
host="192.168.88.100",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8"
)
# 创建游标
cur = conn.cursor()
try:
# 执行SQL语句
sql = "insert into user values (null,'zhangsan','999')"
result_num = cur.execute(sql)
print(result_num)
# 模拟异常的发生
# a = 1/0
except:
# 如果数据操作失败，需要回滚事务
print("数据操作失败，准备回滚事务")
conn.rollback()
else:
# 注意：增删改中需要手动提交事务
print("数据操作成功，没有异常发生，准备提交事务")
conn.commit()
finally:
# 关闭连接/释放资源
print("关闭连接")
cur.close()
conn.close()
def my_delete():
# 建立与数据库的连接
conn = pymysql.connect(
host="192.168.88.100",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8"
)
# 获取游标
cur = conn.cursor()
try:
sql = "delete from user where id in (3,6,7)"
result_num = cur.execute(sql)
print(result_num)
except:
conn.rollback()
else:
conn.commit()
finally:
cur.close()
conn.close()
def my_update():
# 创建与对应数据库的连接
# host如果是本地，可以写localhost或者127.0.0.1
conn = pymysql.connect(
host="192.168.88.100",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8"
)
# 创建游标
cur = conn.cursor()
try:
# 执行SQL语句
sql = "update user set user='张三' where id=9"
result_num = cur.execute(sql)
print(result_num)
except Exception as e:
# 如果数据操作失败，需要回滚事务
print(e)
print("数据操作失败，准备回滚事务")
conn.rollback()
else:
# 注意：增删改中需要手动提交事务
print("数据操作成功，没有异常发生，准备提交事务")
conn.commit()
finally:
# 关闭连接/释放资源
print("关闭连接")
cur.close()
conn.close()
if __name__ == '__main__':
# my_insert()
# my_delete()
my_update()
删:
import pymysql
# 将分散的代码抽取为独立的函数/方法的快捷键：ctrl+alt+M
def my_insert():
# 创建与对应数据库的连接
# host如果是本地，可以写localhost或者127.0.0.1
conn = pymysql.connect(
host="192.168.88.100",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8"
)
# 创建游标
cur = conn.cursor()
try:
# 执行SQL语句
sql = "insert into user values (null,'zhangsan','999')"
result_num = cur.execute(sql)
print(result_num)
# 模拟异常的发生
# a = 1/0
except:
# 如果数据操作失败，需要回滚事务
print("数据操作失败，准备回滚事务")
conn.rollback()
else:
# 注意：增删改中需要手动提交事务
print("数据操作成功，没有异常发生，准备提交事务")
conn.commit()
finally:
# 关闭连接/释放资源
print("关闭连接")
cur.close()
conn.close()
def my_delete():
# 建立与数据库的连接
conn = pymysql.connect(
host="192.168.88.100",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8"
)
# 获取游标
cur = conn.cursor()
try:
sql = "delete from user where id in (3,6,7)"
result_num = cur.execute(sql)
print(result_num)
except:
conn.rollback()
else:
conn.commit()
finally:
cur.close()
conn.close()
def my_update():
# 创建与对应数据库的连接
# host如果是本地，可以写localhost或者127.0.0.1
conn = pymysql.connect(
host="192.168.88.100",
port=3306,
user="root",
password="123456",
database="jing_dong",
charset="utf8"
)
# 创建游标
cur = conn.cursor()
try:
# 执行SQL语句
sql = "update user set user='张三' where id=9"
result_num = cur.execute(sql)
print(result_num)
except Exception as e:
# 如果数据操作失败，需要回滚事务
print(e)
print("数据操作失败，准备回滚事务")
conn.rollback()
else:
# 注意：增删改中需要手动提交事务
print("数据操作成功，没有异常发生，准备提交事务")
conn.commit()
finally:
# 关闭连接/释放资源
print("关闭连接")
cur.close()
conn.close()
if __name__ == '__main__':
# my_insert()
my_delete()
rollback事物回滚演示【了解】
import pymysql
import time
def simulate_transaction(conn, amount):
cur = conn.cursor()
try:
# 从账户1扣款
cur.execute("UPDATE account SET balance = balance - %s WHERE id = 1", (amount,))
# 模拟失败
if amount > 100:
raise Exception("金额过大，交易失败")
# 向账户2入账
cur.execute("UPDATE account SET balance = balance + %s WHERE id = 2", (amount,))
conn.commit()
print(f"✅ 交易成功: {amount} 元")
except Exception as e:
print(f"❌ 交易失败: {e}")
# conn.rollback() # <<< 这里非常关键！否则事务残留会影响下一次操作
print(" 回滚完成")
finally:
cur.close()
if __name__ == '__main__':
# 长生命周期连接（模拟连接池）
conn = pymysql.connect(
host="192.168.88.100",
port=3306,
user="root",
password="123456",
charset="utf8",
database="jing_dong",
autocommit=False
)
try:
for i in range(3):
print(f"\n--- 第{i + 1}次交易 ---")
if i == 1:
simulate_transaction(conn, 150) # 模拟失败
else:
simulate_transaction(conn, 100) # 成功
time.sleep(1)
finally:
conn.close()
优化后的代码【掌握】
import pymysql
def modify_data(sql):
# 1- 创建与数据库的连接
conn = pymysql.connect(
user="root",
password="root",
host="127.0.0.1",
port=3306,
database="jing_dong",
charset="utf8"
)
# 2- 创建游标
cur = conn.cursor()
try:
# 3- 通过游标执行SQL
cur.execute(sql)
# 演示执行出错
# i = 1/0
except Exception as e:
# 回滚事物
conn.rollback()
print(f"异常信息是：{e}")
else:
# 4- 提交事务
conn.commit()
finally:
# 5- 关闭游标
cur.close()
# 6- 关闭连接
conn.close()
def insert():
sql = "insert into user (user,pwd) values ('lisi8888','123456')"
modify_data(sql)
def update():
sql = "update user set pwd=6666888 where user='lisi8888'"
modify_data(sql)
def delete():
sql = "delete from user where user='lisi8888'"
modify_data(sql)
if __name__ == '__main__':
# 1- 增加数据
insert()
# 2- 修改数据
update()
# 3- 删除数据
delete()
登录案例【理解】
需求: 自己创建一个数据库,里面有user表,用于存储用户名密码(咱们直接写固定 用户名root 密码123456 )
需要完成的功能是用户键盘录入用户名和密码,使用python代码连接数据库判断是否登录成功
分析: 现实生活中如果要登录,必须先注册,用户注册完后,对应的信息就存储到对应网站后台数据库中
用户登录: 用户新输入的用户名密码和数据库中的用户名和密码比较,都相同就登录成功,否则失败
演示sql注入问题和解决办法
import pymysql
if __name__ == '__main__':
# 0- 获取用户的账号、密码
user = input("账号：")
pwd = input("密码：")
# 1- 创建与数据库的连接
conn = pymysql.connect(
user="root",
password="root",
host="127.0.0.1",
port=3306,
database="jing_dong",
charset="utf8"
)
# 2- 创建游标
cur = conn.cursor()
# 3- 执行SQL
# SQL注入演示
# 如何演示：user随便输入，pwd输入 ' or 1=1 or '
# sql = f" select * from user where user='{user}' and pwd='{pwd}' "
# print(f"格式化后的SQL：{sql}")
# result = cur.execute(sql)
# 解决：SQL注入
sql = " select * from user where user=%s and pwd=%s "
result = cur.execute(sql,args=(user,pwd))
if result>=1:
print("恭喜，登录成功")
else:
print("登录失败")
# 4- 关闭
cur.close()
conn.close()
