python：mysql全局大览（保姆级教程）-CSDN博客

本文链接：https://blog.csdn.net/sulin123123/article/details/147964640

** MySQL是最好的 RDMS (Relational Database Management System，关系数据库管理系统) 应用软件，目前属于 Oracle 旗下产品，MySQL 是最流行的关系型数据库管理系统中的一个。**

使用mysql前，需要先下载安装mysql（通过小皮面板最方便，连接时默认用户和密码都是root），然后运行方式可以是通过datagrip，也可以是通过pycharm连接运行（连接时默认用户是root）。
mysql访问官网下载页面：https://dev.mysql.com/downloads/mysql/
一、关于数据库

数据库是存储数据的仓库，分为关系型数据库和非关系型数据库，关系型数据库遵循sql规范（一整套结构化查询语言），非关系型数据库又叫做nosql（not only sql），不需要遵循sql规范。

二、sql语言分类

数据定义语言:简称DDL(Data Definition Language)
作用: 用来定义数据库对象：数据库，表，列/字段等。
关键字: create，drop，alter等

数据操作语言:简称DML(Data Manipulation Language)
作用:用来对数据库中表的记录进行更新。
关键字: insert，delete，update等

数据查询语言:简称DQL(Data Query Language)
作用:用来查询数据库中表的记录。
关键字: select，from，where等

数据控制语言：简称DCL(Data Control Language)
用来定义数据库的访问权限和安全级别，及创建用户。

三、数据库增删改查操作

首先注意：1.每一条sql语句都要用英文分号（;）结尾；2.windows系统里，mysql不区分大小写。

创建数据库: create database [if not exists] 数据库名; 注意: 默认字符集就是utf8

删除数据库:** drop database [if exists] 数据库名**;

使用/切换数据库: use 数据库名;

查看所有的数据库名:** show databases**;

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

查看指定库的建库语句: show create database 数据库名;

例：
create database database1;#创建database1（数据库名）
use database1;                #使用database1（使用后才可以建表）
drop database database1；   #删除database1

四、库中表增删改查操作

创建表: create table [if not exists] 表名(字段1名字段1类型 [字段1约束] , 字段2名字段2类型 [字段2约束] …);

删除表:** drop table [if exists] 表名**;

修改表名: rename table 旧表名 to 新表名;
注意: 修改表中字段本质都是修改表,咱们后面演示此处略

查看所有表: show tables;

查看指定表的建表语句: show create table 表名;

添加表字段: alter table 表名 add [column] 字段名字段类型 [字段约束];

删除表字段: alter table 表名 drop [column] 字段名;

修改表字段名和字段类型: ** alter table 表名 change [column] 旧字段名新字段名字段类型 [字段约束]**;

modify只修改字段类型: alter table 表名 modify [column] 字段名字段类型 [字段约束];

查看字段信息: desc 表名;

备注：中括号内容代表可以要也可以不要,后续代码里中括号均如此功能。

create table student(        #创建student（表名）
    id int,
    name varchar(100),
    weight double,
    height double,   
);
show tables;        #查看目前所有的表
desc student;      #  查看student表结构
#添加gender字段
alter table  student   add   gender  varchar(100);
#修改gender字段为sex字段
alter table  student   change   gender   sex  varchar(100);
#修改sex字段类型为int
alter table  student   modify  sex  int;

#删除student
drop table student;

五、表中记录插入

插入数据记录: insert into 表名 (字段名1,字段名2…) values (值1.1，值2.1…) , (值1.2,值2.2…);
注意1: 具体值要和前面的字段名以及顺序一一对应上
注意2: 如果要插入的是所有字段,那么字段名可以省略(默认代表所有列都要插入数据)
注意3: 如果要插入多条记录,values后多条数据使用逗号分隔

修改数据记录: update 表名 set 字段名=值 [where 条件];
注意: 如果没有加条件就是修改对应字段的所有数据

删除数据记录:** delete from 表名 [where 条件]**;
注意: 如果没有加条件就是删除所有数据

#插入数据到student
insert into student values(1,'john',50.6,1.78),(2,'july',51,1.67),(3,'huahua',53.7,1.89);   

 #改student数据
update student set name='alice' where name='huahua'; 3]  
update student set name='alice' where id=3;

清空所有数据:
方式1: delete from 表名; 注意:此方式有警告
方式2: truncate [table] 表名; 注意: 此方式没有警告

#删除了student单列数据
delete from student where id=3;
delete from student where name='july';

#删除了student所有数据
delete from student;  
#另一种删除所有数据方法：truncate
truncate student；

备注：delete from与truncate两者区别在于：1.delete from可以删除单列数据，但truncate不可以；2.delete from不会重置表主键自增，truncate会重置表主键自增。

六、表约束：
主键约束（key primary）：每个表只设置一个主键，设置后：值非空且唯一。主键约束可设置主键自增；（auto_increment），设置后不输入值时、或者输入0或者输入null时将直接引起主键自增（即主键自己按顺序增长）；

唯一约束（unique）：设置后值唯一，不可重复；

非空约束（not null）：设置后值非空，必须有；

外键约束【construct [约束名] foreign key （字段名） references 主表（主键名）】：用于关联两个表，关联表叫子表，被关联表叫主表；

默认约束（default）：设置默认值，不输入值时默认；

注意：1.约束建议都是在建表时添加，简单方便；2.主键约束每个表只有一个，其它约束可以有多个。

例：
use database1;                #使用数据库
create table student(        #创建student（表名）
    id int primary key auto_increment,  #主键自增
    name varchar(100)  not null,     #非空约束
    weight double   unique,       #唯一约束
    height double    default 1.9      #默认约束
);

七、单表查询
基础查询：select */字段名 from 表名

条件查询：where （比较/逻辑/范围/模糊/非空判断）

分组/聚合/统计函数：sum（）、avg（）、count（）、max（）、min（）

分组查询：select 分组字段名，聚合函数（字段名）from 表名 group by 分组字段名1，分组字段名2。。。

排序查询：select 字段名 from 表名 order by 排序字段名1 （asc：升序；desc：降序），排序字段名2（asc：升序；desc：降序）。。。

Limit查询：select 字段名 from 表名 limit x，y（x为起始索引，y为一次查询数量）。

备注：被查询的字段名都可以通过as 起别名（as也可省略，字段名后面可以直接跟别名），但应避免是中文名。

#单表查询

#（1）单表查询之基础查询
select * from student;
select distinct * from student;
select distinct name as n,weight as w from student;#as可省略；

#（2）单表查询之条件查询
select * from student where name like 'j%';
select * from student where name like '%j%';#查询name带有j的
select * from student where name like 'ju__';
select * from student where weight <>51 and weight <>55;#查询weight不为51也不为55的数据
select * from student where weight not in(51,55);#查询weight不为51也不为55的数据
select * from student where weight!=51 and weight!=55;#查询weight不为51也不为55的数据
select * from student where weight not between 51 and 55;#查询weight不在51和55之间的数据
select * from student where weight  is not null;#查询有weight的数据
select count(*) from student where weight  is not null;#个数查询，推荐
#个数查询，利用其自动忽略none值，推荐
select count(weight) from student;

#（3）聚合函数查询
select count(weight) c,max(weight) ma,min(weight)  mi,
round(avg(weight),2) av,sum(weight)  s  from student；

#（4）分组查询(分组字段名height必须跟在select后面，可多个分组字段）
select height,count(*) from student  group by height ;

#（5）排序查询(可多个字段排序）
select  height,name  from student  order by weight,height ；

#（6）limit 查询
select   height,name    from   student   limit  0,2;

查询注意事项：
1.书写顺序：select-distinct-聚合函数-from-where-group by-having-order by-limit x，y；
特别地，Having也能跟聚合条件，但不建议，会造成效率低下；

2.执行顺序：from（从硬盘中扫描真实表文件加载到内存形成伪表）—where—group by（分组，切成运算区）—聚合函数—having—select distinct—order by—limit

八、多表查询
本质是多个表通过主外键关联关系连接(join)合并成一个大表,再去查询。

（一）外键约束
建表时添加外键约束: … CONSTRAINT [外键约束名] FOREIGN KEY (外键名) REFERENCES 主表名 (主表主键)

建表后添加外键约束: alter table 从表名 add CONSTRAINT [外键约束名] FOREIGN KEY (外键名) REFERENCES 主表名 (主表主键)

删除外键约束: alter table 从表名 drop FOREIGN KEY 外键约束名;

注意：如果要删除有外键约束的主从表,先删除从表,再删除主表。

例：
# 创建分类表（主表）
create  table  category1
(
cid   varchar(32) primary  key, # 分类id，设置主键
cname varchar(100)             # 分类名称
);

# 商品表（从表）
create teble  products1
(
pid         varchar(32) primary  key,  #设置主键
pname       varchar(40),
price       double,
category_id   varchar(32),
CONSTRAINT FOREIGN KEY (category_id) REFERENCES category1 (cid)   #添加外键约束
);

注意：只有innodb存储引擎支持外键约束和事务！！！小皮面板里如果要修改数据库属性为innodb，需要先关闭掉mysql服务后再修改!!!修改存储引擎后,只对后面新建的表有效!!!**

外键约束作用:

限制从表插入数据: 从表插入数据的时候如果外键值是主表主键中不存在的,就插入失败
限制主表删除数据: 主表删除数据的时候如果主键值已经被从表外键的引用,就删除失败

外键约束好处: 保证数据的准确性和完整性。

（二）连结查询
在这里插入图片描述
1.交叉连接（笛卡尔积）

显式交叉连接格式: select * from 左表 cross join 右表;

隐式交叉连接格式: select * from 左表,右表。

**注意：**笛卡尔积本身是一个错误，工作中慎用。

例：
# 隐式交叉连接格式: select 字段名 from 左表,右表;
select *  from   products, category;
# 显式交叉连接格式: select 字段名 from 左表 cross join右表;
select  *  from  products  cross join  category;

2.内连接

显式内连接格式: select * from 左表 [ inner] join 右表 on 关联条件;

隐式内连接格式: select * from 左表 , 右表 where 关联条件;

注意:inner可以省略。

例：
# 隐式内连接格式: select 字段名 from 左表,右表 where 条件;
select
    c.id   cid,
    c.name cname,
    p.id   pid,
    p.name pname
from
    products p,
    category c
where
    p.category_id = c.id;
# 显式内连接格式: select 字段名 from 左表 cross join右表;
select
    c.id   cid,
    c.name cname,
    p.id   pid,
    p.name pname
from
    products p
        inner  join  category c   on   p.category_id = c.id;

3.左外连接

左连接关键字: left [outer] join … on

左外连接格式: select * from 左表 left [outer] join 右表 on 关联条件;

注意: 1.左表和右表没有特殊含义,只是在前面是左表,在后面的是右表；2.outer可以省略。

例：
-- 为了方便演示插入一条数据
insert  into
    products(name, price, category_id)
values
    ('百草味紫皮腰果', 9, 5);

-- 需求: 分别使用左右连接查询每个分类下的所有商品,即使没有商品的分类要展示
-- 分析: 必须以分类表为主
-- 左外连接: left outer join
select
    c.id   cid,
    c.name cname,
    p.id   pid,
    p.name pname
from
    category c
        left  outer join products p  on  p.category_id = c.id;

4.右外连接
右连接关键字: right [outer] join … on

右外连接格式: select * from 左表 right [outer] join 右表 on 关联条件;

注意: 1.左表和右表没有特殊含义,只是在前面是左表,在后面的是右表；2.outer可以省略。

-- 右外连接: right outer join
select
    c.id   cid,
    c.name cname,
    p.id   pid,
    p.name pname
from
    products p
        right  outer  join  category c on  p.category_id = c.id;

5.全外连接**

注意**: mysql中没有full outer join on这个关键字,所以不能用它来完成全外连接!所以只能先查询左外连接和右外连接的结果,然后用union或者union all来实现!!!

union : 默认去重
union all: 不去重

例（全外连接）：
# union : 默认去重
select *
from
    products p
        left join category c on p.category_id = c.id
union
select *
from
    products p
        right join category c on p.category_id = c.id;

# union all: 不去重
select  *
from
    products p
        left  join  category c on p.category_id = c.id
union all
select  *
from
    products p
        right join category c on  p.category_id = c.id;

6、自连接

解释: 两个表进行关联时，如果左表和右边是同一张表，这就是自关联。

注意: 自连接必须起别名!

例：
-- 自连接查询
-- 查询'江苏省'下所有城市
select
    shi.id,
    shi.title,
    sheng.id,
    sheng.title
from
    areas sheng
        join areas shi on shi.pid = sheng.id
where
    sheng.title = '江苏省';

-- 查询'宿迁市'下所有的区县
select
    quxian.id,
    quxian.title,
    shi.id,
    shi.title
from
    areas shi
        join areas quxian on quxian.pid = shi.id
where
    shi.title = '宿迁市';

-- 查询'安徽省'下所有的市,以及市下面的区县信息
select
    quxian.id,
    quxian.title,
    shi.id,
    shi.title,
    sheng.id,
    sheng.title
from
    areas sheng
        join areas shi on shi.pid = sheng.id
        join areas quxian on quxian.pid = shi.id
where
    sheng.title = '江苏省';
    


-- 自连接的妙用
-- 需求1: 求每个月和上月的差额
select
    c.month,
    c.revenue,
    c.revenue-u.revenue as diff
from
    sales c
        join sales u on c.month = u.month + 1;

-- 需求2: 求截止到当月累计销售额
select
    c.month,
    SUM(u.revenue)
from
    sales c
        join sales u on c.month >= u.month
group  by
    c.month;

九、子查询

在一个 SELECT 语句中，嵌入了另外一个 SELECT 语句，那么被嵌入的 SELECT 语句称之为子查询语句，外部那个SELECT 语句则称为主查询。

作用: 子查询是辅助主查询的，它可以充当主查询的条件、数据源（临时表）、查询字段。

例：
--  查询'河北省'下所有城市
select * from areas where pid = (select id from areas where title = '河北省');
--  查询'邯郸市'下所有区县
select * from areas where pid = (select id from areas where title = '邯郸市');

-- **注意:子查询作为表使用必须加括号,同时起别名!!!**

十、开窗函数（mysql8.0版本以上才有此功能）

开窗函数over():设置每行数据关联的窗口数据范围，over()时，每行关联的数据范围都是整张表的数据。
over函数一般跟在查询字段COUNT、SUM、AVG、MAX、MIN、ROW_NUMBER、RANK、DENSE_RANK等后面。

（例）格式：count（字段名） over（）

over（）里面也可以加入partition by 字段名（分组）、order by 字段名等。

（例）格式1：sum（score） over（partition by gender order by id ）
备注：score、gender、id均是字段名。
（例）格式2：rank（） over（partition by gender order by id）
备注：gender、id均是字段名。

例：
针对 students 表的数据，计算每个同学的Score分数和整体平均分数的差值
-- 数据准备
create table `students`     #建表，反引号用来识别
(
`id`     int(11)     not null  auto_increment,
`name`   varchar(24)  not  null,
`gender`  varchar(8)  not  null,
`score`  decimal(5, 2)  not  null,
primary key (`id`)
);

insert   into  `students`  values
(1, 'smart', 'Male', 90.00),
(2, 'linda', 'Female', 81.00),
(3, 'lucy', 'Female', 83.00),
(4, 'david', 'Male', 94.00),
(5, 'Tom', 'Male', 92.00),
(6, 'Jack', 'Male', 88.00);
-- 查询
select   *,avg(score)   over()         as `avg`,
score - avg(score)   over (partiton by gender)   as  `difference`
from      students;


分享：
-- partition  by 和 group  by的区别
-- 使用场景不同
-- partiton by用在窗口函数中，结果是：一进一出
-- group by用在分组聚合中，结果是：多进一出

特别分享1：排名函数

RANK()：产生的排名序号，有并列的情况出现时序号不连续，如1224；
DENSE_RANK() ：产生的排序序号是连续的，有并列的情况出现时序号会重复，如1223；
ROW_NUMBER() ：返回连续唯一的行号，排名序号不会重复，如1234。

特别分享2：CTE(公用表表达式)

CTE(公用表表达式)：Common Table Expresssion，类似于子查询，相当于一张临时表，可以在 CTE 结果的基础上，进行进一步的查询操作。

基础语法
with tmp1 as (查询语句), tmp2 as (查询语句), tmp3 as (查询语句) select 字段名 from 表名;

例：
-- 需求：获取每个科目，排名第二的学生信息
-- 查询结果字段：
--  name、course、score

#代码：
with   temp  as   #基础式
 (select   *,dense_rank()   over (partition by course order by  score desc)   as  dr
from  tb_score)
select  *   from    temp    where      dr = 2;

今天的分享到此为止，欢迎交流沟通，共同进步！