MySQL笔记第三天(从小白到入门)

文章目录

  • MySQL笔记
    • SQL语言介绍
    • 数据库系统
      • 关系型数据库
      • 非关系型数据库
      • SQL和数据库系统的关系
      • 数据库系统架构
    • MySQL的介绍
      • 概念
      • MySQL的版本
    • MySQL的DDL操作-重点
      • 基本数据库操作
      • 基本表操作
    • MySQL的DML操作-重点
      • insert-插入数据
      • update-更新数据
      • delete-删除数据
    • MySQL的约束-了解
      • 概述
      • 约束的分类
      • 主键约束
      • 自增约束
      • 其他约束
  • 插入以下数据,发现主键从100开始,然后依次加1
    • MySQL的DQL操作-重点
      • 语法
      • 简单查询
      • 条件查询
        • 条件-算术运算符
        • 条件-比较运算符
        • 条件-逻辑运算符
      • 排序查询
      • 聚合查询
      • 分组查询
      • 分页查询
      • 保存查询结果
    • MySQL的多表关系
      • 介绍
      • 多表关系
      • 操作-一对一关系
      • 操作-一对多关系
        • 介绍
        • 创建外键约束
        • 删除外键约束
        • 外键约束下的数据操作
      • 操作-多对多关系
        • 介绍
        • 特点
        • 代码
    • MySQL的多表查询
      • 分类
      • 交叉连接查询
      • 内连接查询
      • 外连接查询
        • 左外连接
        • 右外连接
        • 满外连接
      • 案例
    • MySQL的子查询
      • 概念
      • 代码

MySQL笔记

SQL语言介绍

  • SQL语句概念

    1.SQL全称: Structured Query Language,是结构化查询语言,用来访问结构化数据(类似Excel文本数据)
    2.SQL用来操作数据库系统
    3.SQL发展到现在,制定了很多的标准,有:SQL-92、SQL-99标准,不同的标准表达方式不同
    
    
  • SQL语言的分类

    数据定义语言-DDL (create/drop/show) 
    数据操作语言-DML (insert/delete/update) 
    数据查询语言-DQL (select/group by/order by) !!!!!!!
    数据控制语言-DCL
    
  • SQL语法特点

    SQL 对关键字的大小写不敏感
    
    SQL语句可以以单行或者多行书写,以分行结束
    
  • SQL的注释

    -- 单行注释
    # 单行注释
    
    /*
      多行注释 
      多行注释 
      多行注释 
      多行注释 
    */
    

数据库系统

关系型数据库

  • 介绍

    关系型数据库最大的特点是:数据库中存储的是一张张的表格,表格与表格之间存在的某种关系
    
    
  • 分类

    1、Oracle数据库 (老大,最挣钱的数据库)
    2、MySQL数据库 (最流行中型数据库))
    3、SQL server数据库 (Windows上最好的数据库)
    4、PostgreSQL(功能最强大的开源数据库)
    5、SQLite(最流行的嵌入式数据库
    
    #黑马的课程设计的数据库:MySQL数据库、Oracle数据库、PostgreSQL数据库
    

非关系型数据库

  • 介绍

    菲关系型数据库中存储的是键值对,大部分都不是表
    
  • 分类

    1、Redis(最好的缓存数据库)
    2、MongoDB(最好的文档型数据库)
    3、Elasticsearch(最好的搜索服务)
    4、Cassandra(最好的列式数据库)
    5、HBase(优秀的分布式、列式数据库)
    
    #黑马的课程设计的数据库:Elasticsearch、HBase、MongoDB
    

SQL和数据库系统的关系

1、SQL是一种用于操作数据库的语言,SQL适用于所有关系型数据库。
2、MySQL、Oracle、SQLServer是一个数据库软件,我们使用SQL可以操作这些软件,不过每一个数据库系统会在标准SQL的基础上扩展自己的SQL语法。

数据库系统架构

在这里插入图片描述

数据库系统 --->数据库 --->--->数据

MySQL的介绍

概念

1、MySQL现在归属于Oracle公司(甲骨文)、该公司旗下还有一个Oracle数据库,
2、MySQL底层是C语言
3、MySQL支持多种操作系统,多种编程语言访问()

MySQL的版本

MySQL的经典数字版本: MySQL 8.x 和 MySQL 5.x  
可以安装 社区版MySQL 8.x 或者 商业版 MySQL 8.x

MySQL的DDL操作-重点

基本数据库操作

·············

-- 1、查看所有的数据库
show databases ;

-- 2、创建数据库
#不推荐使用以下命令,如果数据库存在,则报错
create database  mydb1;

#推荐使用以下命令,如果数据库存在,则什么也不做,如果数据库不存在,则创建
create database if not exists  mydb1;

-- 3、选择当前要使用哪个数据库
use mydb1;

-- 4、删除数据库
#以下命令不推荐,因为如果数据库不存在,则报错
drop database mydb1;
#以下命令推荐,因为如果数据库不存在,则什么也不做
drop database if exists mydb1;

-- 5、修改数据库编码
#我们目前的数据库默认就是utf-8编码
-- alter database mydb1 character set utf8;



在这里插入图片描述
d=img-5BSVIfZf-1715992255382)

基本表操作

  • 数据类型

    外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

  • 创建表语法

    create table [if not exists]表名(
      字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
      字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
      字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
    )[表的一些设置];
    
    
  • 创建表操作

    -- 1、创建学生表
    use mydb1;
    -- comment是给表字段(列)加注释,可以通过表结构查看注释
    -- decimal(10,2) :10表示最多是10为有效数字(整数+小数) ,小数保留2位,整数最多是8位
    -- 12345678.34 :整数8位 + 小数2位
    -- varchar(20) 这里边最多存入20个字符,超过会截断
    create table  if not exists student2(
        sid int comment '学生学号',
        name varchar(20) comment '学生名字',
        gender varchar(2) comment '学生的性别',
        age int comment '学生的年龄',
        birth date comment '学生的生日',  -- 2000-12-23
        address varchar(50) comment '学生的地址',
        score decimal(10,2) comment '学生的成绩'
    );
    
    
    use mydb1;
    -- 查看所有的表
    show  tables ;
    
    -- 查看表创建时的sql语句
    show create table  student2;
    
    
    -- 查看表结构,表字段名字和类型
    desc student;
    
    -- 删除表
    drop  table student;
    

MySQL的DML操作-重点

insert-插入数据

  • 语法

    insert into(列名1,列名2,列名3...) values (1,2,3...)#向表中插入某些
    insert intovalues (1,2,3...);     #向表中插入所有列
    
  • 代码

    #------------------DML操作-insert插入数据-----------------------
    -- 添加一行数据
    insert into student(sid,name,gender,age,birth,address,score)
    values (1,'宝玉','男',19,'2000-12-23','北京',87);
    
    -- 添加一行数据
    #前边跟几个字段,后边就要跟几个值,类型要相同
    insert into student(sid,name,gender) values (2,'黛玉','女');
    
    -- 添加一行数据
    # 如果表的后边没有加字段,则values后边需要给所有字段赋值
    insert into student values (3,'宝钗','女',16,'2000-12-23','上海',98);
    
    -- 添加多行数据
    insert into student values
                            (4,'晴雯','女',17,'2002-12-23','深圳',56),
                            (5,'贾琏','男',34,'2023-10-23','广州',98),
                            (6,'贾雨村','男',156,'2008-02-21','杭州',45);
    

update-更新数据

  • 语法

    update 表名 set 字段名=,字段名=...;
    update 表名 set 字段名=,字段名=... where 条件;
    
  • 代码

    #------------------DML操作-update更新数据-----------------------
    -- 将所有学生的地址改为:吉山村
    update student  set address = '吉山村';
    
    -- 将所有学生的age改为18,score改为100
    update student  set age = 18,score = 100;
    
    -- 将所有学生的age在原来基础加上10岁
    update student  set age = age + 10 ;
    
    -- 将 晴雯 的address改为广州
    update student  set address = '广州' where name = '晴雯';
    update student  set address = '广州' where sid = 4;
    

delete-删除数据

  • 语法

    delete from 表名 [where 条件];
    truncate table  表名 或者 truncate 表名
    
    
  • 代码

    #------------------DML操作-delete删除数据-----------------------
    -- 删除操作,如果后边不加where条件,则会将该表的所有数据全部删除,比较危险
    delete from student ;
    
    -- 删除宝玉这个学生
    delete from student where name  = '宝玉';
    
    -- 清空表的所有内容
    #注意:delete和truncate原理不同,delete只删除内容,而truncate类似于drop table ,可以理解为是将整个表删除,然后再创建该表;
    truncate  table student;
    truncate   student; -- 作用同上
    
    

MySQL的约束-了解

概述

约束就是给某一列加限制,让该列的值复合某种要求,英文:constraint

约束的分类

主键约束(primary key) PK
自增长约束(auto_increment)
非空约束(not null)
唯一性约束(unique)
默认约束(default)
外键约束(foreign key) FK

主键约束

  • 特点

    1、MySQL主键约束是一个列或者多个列的组合,其值能唯一地标识表中的每一行,方便在数据库中尽快的找到某一行。
    2、主键约束相当于 非空 + 唯一 的组合,主键约束列不允许重复,也不允许出现空值(null值)。
    3、每个表最多只允许一个主键
    4、主键约束的关键字是:primary key
    
    
  • 语法

    #----------------方式1-----------------------
    -- 在 create table 语句中,通过 PRIMARY KEY 关键字来指定主键。
    --在定义字段的同时指定主键,语法格式如下:
    create table 表名(
       ...
       <字段名> <数据类型> primary key 
       ...
    )
    #----------------方式2-----------------------
    --在定义字段之后再指定主键,语法格式如下:
    create table 表名(
       ...
        primary key(字段名)
    );
    
    
  • 代码

    #----------------方式1-----------------------
    drop table if exists student;
    create table  if not exists student(
        sid int primary key comment '学生学号', #  !!!添加主键-sid这一列的值不能为空,而且必须唯一
        name varchar(20) comment '学生名字',
        gender varchar(2) comment '学生的性别',
        age int comment '学生的年龄',
        birth date comment '学生的生日',  -- 2000-12-23
        address varchar(50) comment '学生的地址',
        score decimal(10,2) comment '学生的成绩'
    );
    
    #----------------方式2-----------------------
    drop table if exists student;
    create table  if not exists student(
        sid int comment '学生学号',
        name varchar(20) comment '学生名字',
        gender varchar(2) comment '学生的性别',
        age int comment '学生的年龄',
        birth date comment '学生的生日',  -- 2000-12-23
        address varchar(50) comment '学生的地址',
        score decimal(10,2) comment '学生的成绩',
        primary key (sid)  # !!!添加主键 ,sid这一列的值不能为空,而且必须唯一
    );
    
    
    # 插入成功
    insert into student values (1,'宝钗','女',16,'2000-12-23','上海',98);
    # 插入失败,因为主键不能重复
    insert into student values (1,'宝玉','男',16,'2000-12-23','上海',98);
    # 插入失败,因为主键不能为空
    insert into student values (null,'宝玉','男',16,'2000-12-23','上海',98);
    # 插入失败,因为主键列没有指定值,默认是null值,则不符合主键的要求
    insert into student(name,gender) values ('黛玉','女');
    
  • 删除主键

    # 删除主键,但是还剩下一个非空约束
    alter table student drop primary key;
    #再删除非空约束,这样主键的所有影响全部消除
    ALTER TABLE student MODIFY sid INT NULL;
    

自增约束

  • 概念

    1、在 MySQL 中,当主键定义为自增长后,这个主键的值就不再需要用户输入数据了,而由数据库系统根据定义自动赋值。每增加一条记录,主键就会自动加1
    2、自动增长必须要加在主键的后边,加了自动增长之后,该列的值可以自己在每次添加数据时加1
    3、自动增长的列默认是1开始增长,每次加1
    4、主键列必须是数字列
    5、自动增长的关键字:auto_increment
    
  • 代码

    #------------------DML操作-自动增长-----------------------
    # 自动增长必须要加在主键的后边,加了自动增长之后,该列的值可以自己在每次添加数据时加1
    # 自动增长的列默认是1开始增长,每次加1
    # 主键列必须是数字列
    
    drop table if exists student;
    create table  if not exists student(
        sid int primary key auto_increment,  # 给主键添加自增长约束auto_increment
        name varchar(20) ,
        gender varchar(2) ,
        age int ,
        birth date ,
        address varchar(50) ,
        score decimal(10,2)
    );
    
    # 你可以执行以下命令多次,发现主键会自增
    insert into student values (null,'宝玉','男',16,'2000-12-23','上海',98);
    
    # 删除所有数据,主键的历史记录不会被清空,下一次添加依然在之前最大的主键值之上加1
    delete  from student;
    
    # 你可以执行以下命令多次,发现主键会在最大值之上加1
    insert into student values (null,'宝玉','男',16,'2000-12-23','上海',98);
    
    # 如果你想让主键重新回到1开始自增,则可以使用truncate命令(先删除表,再建表)
    truncate table student;
    # 你会发现,主键又从1开始增长
    insert into student values (null,'宝玉','男',16,'2000-12-23','上海',98);
    
    # 我们也可以设置主键从某个值开始增长
    drop table if exists student;
    create table  if not exists student(
        sid int primary key auto_increment, # 给主键添加自增长约束auto_increment
        name varchar(20) ,
        gender varchar(2) ,
        age int ,
        birth date ,
        address varchar(50) ,
        score decimal(10,2)
    )auto_increment = 100; # 我们也可以设置主键从100开始增长
    
    # 插入以下数据,发现主键从100开始,然后依次加1
    insert into student values (null,'宝玉','男',16,'2000-12-23','上海',98);
    

其他约束

create table  if not exists student(
    sid int primary key auto_increment,
    name varchar(20) not null,  # 非空约束,该列的值不能为空
    gender varchar(2) unique ,  # 唯一约束,该列的值必须唯一
    age int ,
    birth date ,
    address varchar(50) default '北京',  # 默认值约束,该列如果没有给值,则模式是北京
    score decimal(10,2)
);

,
score decimal(10,2)
)auto_increment = 100; # 我们也可以设置主键从100开始增长

插入以下数据,发现主键从100开始,然后依次加1

insert into student values (null,‘宝玉’,‘男’,16,‘2000-12-23’,‘上海’,98);




### 其他约束

```sql
create table  if not exists student(
  sid int primary key auto_increment,
  name varchar(20) not null,  # 非空约束,该列的值不能为空
  gender varchar(2) unique ,  # 唯一约束,该列的值必须唯一
  age int ,
  birth date ,
  address varchar(50) default '北京',  # 默认值约束,该列如果没有给值,则模式是北京
  score decimal(10,2)
);

MySQL的DQL操作-重点

语法

  • 通用语法

    select 
      [all|distinct]
      <目标列的表达式1> [别名],
      <目标列的表达式2> [别名]...
    from <表名或视图名> [别名],<表名或视图名> [别名]...
    [where<条件表达式>]
    [group by <列名> 
    [having <条件表达式>]]
    [order by <列名> [asc|desc]]
    [limit <数字或者列表>];
    
  • 简化语法

    select 字段 fromwhere 条件
    
    # where是用来筛选那些行, select后边是来筛序哪些列
    

简单查询

-- 创建数据库
create database if not exists mydb2;
use mydb2;
-- 创建商品表:
drop table if exists product;
create table product(
 pid int , -- 商品编号
 pname varchar(20) not null , -- 商品名字
 price double,  -- 商品价格
 category_id varchar(20) -- 商品所属分类
);


insert into product values(1,'海尔洗衣机',5000,'c001');
insert into product values(2,'美的冰箱',3000,'c001');
insert into product values(3,'格力空调',5000,'c001');
insert into product values(4,'九阳电饭煲',5000,'c001');
insert into product values(5,'啄木鸟衬衣',300,'c002');
insert into product values(6,'恒源祥西裤',800,'c002');
insert into product values(7,'花花公子夹克',440,'c002');
insert into product values(8,'劲霸休闲裤',266,'c002');
insert into product values(9,'海澜之家卫衣',180,'c002');
insert into product values(10,'杰克琼斯运动裤',430,'c002');
 
insert into product values(11,'兰蔻面霜',300,'c003');
insert into product values(12,'雅诗兰黛精华水',200,'c003');
insert into product values(13,'香奈儿香水',350,'c003');
insert into product values(14,'SK-II神仙水',350,'c003');
insert into product values(14,'SK-II神仙水',350,'c003');
insert into product values(15,'资生堂粉底液',180,'c003');

insert into product values(16,'老北京方便面',56,'c004');
insert into product values(17,'良品铺子海带丝',17,'c004');
insert into product values(18,'三只松鼠坚果',88,null);

use mydb2;
select 字段 fromwhere 条件
-- 1.查询所有的商品.
# * 表示所有字段
select * from product ;
select  pid,pname,price,category_id  from product ;

-- 2.查询商品名和商品价格.
select pname,price from product ;

-- 3.别名查询.使用的关键字是as(as可以省略的).
select pname as 商品名,price as 价格 from product ;
select pname  商品名, price  价格 from product ;

-- 4.对price列进行去重 distinct
select distinct price from product;

# 对一模一样的整行去重
select distinct * from product;


-- 5.将所有商品的价格+10元进行显示,并给新的price起别名
-- 字段名可以当成变量来用,字段名可以参与数学运算
select pname,price + 10 as new_price from product ;
select pname,price * 0.8 as new_price from product ;

条件查询

条件-算术运算符

select 6 + 2;
select 6 - 2;
select 6 * 2;
select 10 / 3; # 3.3333
select 10 % 3;   # 取余数/取模  1   商为3
select 99 % 100; # 取余数/取模  99  商为0
条件-比较运算符

在这里插入图片描述

#-----------------------------------------
-- 查询商品名称为“海尔洗衣机”的商品所有信息:
select * from product where pname = '海尔洗衣机';

-- 查询价格为800商品所有信息
select * from product where price = 800;

-- 查询价格不是800的所有商品
select * from product where price <> 800;
select * from product where price != 800;

select * from product where not price = 800;

-- 查询商品价格大于60元的所有商品信息
select * from product where  price > 60;

-- 查询商品价格在200到1000之间所有商品(包含200 和1000)
select * from product where  price  between 200 and 1000;  # 包含边界值

-- 查询商品价格是200或800的所有商品
select * from product where  price  in(200,800);
-- select * from product where  province  in('广东省','湖南省','台湾省');

-- 查询商品价格不是200或800的所有商品
select * from product where  price not  in(200,800);
-- select * from product where  province not in('广东省','湖南省','台湾省');

-- 查询含有‘裤'字的所有商品
-- %匹配任意个字符,0个或者多个都行
-- _匹配一个字符,什么字符都可以
select * from product where  pname like '%裤%';

-- 查询第3个字为京的商品信息
select * from product where  pname like '__京%';
-- 查询4个字的商品信息
select * from product where  pname like '____';

-- 查询姓李的学生
-- select * from student where  name like '李%';
-- 查询最后一个字是杰
-- select * from student where  name like '%杰';

-- 查询以'海'开头的所有商品
select * from product where  pname like '海%';
-- 查询第二个字为'蔻'的所有商品
select * from product where  pname like '_蔻%';

-- 查询category_id为null的商品
-- null六亲不认,null和任何值都不同,甚至 null 不等于 null
select * from product where category_id = null; # 不能用=来判断是否等于null
select * from product where category_id is null; # 不能用=来判断是否等于null

-- 查询category_id不为null分类的商品
select * from product where category_id is not null;

-- 使用least求最小值
-- 如果有一个值为null,则结果就是null
select least(price);  #错误的,least不能求一列的最小值
select least(23,66,12,5);
select least(23,66,null,5);  # 结果是null

-- 使用greatest求最大值
-- 如果有一个值为null,则结果就是null
select greatest(23,66,12,5);
select greatest(23,66,null,5);  # 结果是null
条件-逻辑运算符
  • 分类

  • 语法

    # 多个条件必须同时成立,结果才为真,只要有一个条件不成立,结果就假
    select 字段 fromwhere 条件1  and 条件2  and 条件3  
    select 字段 fromwhere '性别为女'  and '美艳动人'  and '温柔贤惠'  
    select 字段 fromwhere '高'  and '富'  and '帅'  
    
    
    # 多个条件只要有一个条件成立,则结果就为真,多个条件全部为假,结果才为假
    select 字段 fromwhere 条件1  or 条件2  or 条件3  
    select 字段 fromwhere '性别为女'  or '美艳动人'  or '温柔贤惠'  
    select 字段 fromwhere '高'  or '富'  or '帅'  
    
    
    # ! 就是把真变成假,假变为真
    select 字段 fromwhere !('性别为女'  and '美艳动人'  and '温柔贤惠') 
    select 字段 fromwhere not('性别为女'  and '美艳动人'  and '温柔贤惠') 
    
  • 代码

    -- 查询价格大于500,并且属于c002分类的商品
    select * from product where price > 500 and category_id = 'c002' ; # 建议用这个
    select * from product where price > 500 && category_id = 'c002';
    
    -- 查询价格大于500,或者属于c002分类的商品
    select * from product where price > 500 or category_id = 'c002';  # 建议用这个
    select * from product where price > 500 || category_id = 'c002';
    
    -- -- 查询分类不属于c002分类的商品
    select * from product where not (category_id = 'c002');  # 建议用这个
    select * from product where ! (category_id = 'c002');
    
    
    select * from product where (条件1 and 条件2) or (条件3 and 条件4);
    select * from product where ('高' and '富') or ('富' and '帅');
    

排序查询

  • 语法

    select 
     字段名1,字段名2,……
    from 表名
    order by 字段名1 [asc|desc],字段名2[asc|desc]……
    
  • 代码

    #------------------排序查询----------------
    -- 1.使用价格排序(降序:从大到小)
    select * from product order by  price desc;#topn
    
    -- 2.使用价格排序(升序:从小到大)吧
    select * from product order by  price asc;
    select * from product order by  price ;  #默认就是升序
    
    -- 2.在价格排序(降序)的基础上,以分类排序(降序)
    # 先按照price进行降序排序,如果price相同,则按照category_id降序排序
    select * from product order by price desc,category_id desc;
    
    -- 3.显示商品的价格(去重复),并排序(降序)
    select distinct price from  product order by price desc ;
    

聚合查询

  • 语法

    在这里插入图片描述

  • 代码

    #------------------------------------------
    -- 1 查询商品的总条数
    # 统计一张表的行数,有三种方式,以下方式都可以
    select count(pid) from product; # 统计pid列有多少不为null的行
    select count(*) from product;   # 统计整个表有多少行
    select count(1) from product;   # 统计整个表有多少行
    
    -- 2 查询价格大于200商品的总条数
    select count(*) from product where price > 200;
    
    -- 3 查询所有商品的总和
    select sum(price) from product;
    
    -- 4 查询分类为'c001'的所有商品的总和
    select sum(price) from product where category_id = 'c001';
    
    # sum不会统计null值,就当null值不存在
    create table test1(
        id int,
        price int
    );
    insert into test1 values (1,10);
    insert into test1 values (2,null);
    insert into test1 values (3,10);
    
    select sum(price) from test1;  # 20
    
    -- 4 查询商品的最大价格
    # max不会统计null值,就当null值不存在
    select  max(price) from product;
    select  max(price) from test1;
    
    -- 5 查询商品的最小价格
    # min不会统计null值,就当null值不存在
    select  min(price) from product;
    select  min(price) from test1;
    
    -- 6 查询所有商品的平均价格
    # avg,就当null值不存在,
    select  avg(price) from product;
    select  avg(price) from test1;
    

分组查询

  • 语法

    select 字段1,字段2from 表名 group by 分组字段 [ having 分组条件];
    
  • 代码

    #----------------分组查询-----------------
    # 分组之后一般要对同一组的数据进行统计:求和、求数量,求平局值
    # 分组之后一般都要进行统计
    # 分组之后group by的后边只能跟分组字段和聚合函数
    
    # 1、统计每一类商品的个数
    select category_id,count(*) from product group by  category_id;
    select category_id,count(*) from product group by  category_id having  count(*) > 5;
    
    # 1、统计价格大于10的每一类商品的个数,并输出大于大于5的分类信息,并降序排序: Ctrl + Alt + l
    select
        category_id,count(*)
    from product
    where price > 10     # where是在分组之前进行筛选
    group by  category_id
    having  count(*) > 5  # having是在分组统计之后进行筛选
    order by count(*) desc;
    
    #-----------------------------------
    select
        category_id,count(*) as cnt
    from product
    where price > 10     # where是在分组之前进行筛选
    group by  category_id
    having  cnt > 5  # having是在分组统计之后进行筛选
    order by cnt desc;
    
    
    # 2、统计每一类商品的平均价格
    select category_id,avg(price) from product group by  category_id;
    
    # 3、统计每个省的学生都多少人
    -- group by的后边可以跟多个字段,多个字段完全相同才能分到同一组
    -- 同一个省分别有多少同学
    select province,count(*) from mydb1.student group by province;
    -- 同一个省,同一个市分别有多少同学
    select province,city,count(*) from mydb1.student group by province,city;
    
    -- 同年,同月,同日的同学都多少人(伪代码)
    select year,month,day,count(*) from mydb1.student group by year,month,day;
    
    
    

分页查询

  • 语法

    -- 方式1-显示前n条
    select 字段1,字段2... from 表明 limit n
    -- 方式2-分页显示
    select 字段1,字段2... from 表明 limit m,n
    m: 整数,表示从第几条索引开始,索引是从0开始,计算方式 (当前页-1*每页显示条数
    n: 整数,表示查询多少条数据
    
  • 特点

    limit 以后主要用于求TopN:求部门薪水最高的前n为员工
    
  • 代码

    #----------------------------------------
    select  * from product limit 5;   # 显示前5条数据
    
    # 显示1 - 5 行
    select  * from product limit 0,5; # 从索引为0(第1行)的行开始显示,一共显示5行
    
    # 显示 6- 10 行
    select  * from product limit 5,5; # 从索引为5(第6行)的行开始显示,一共显示5行
    
    # 显示 11- 15 行
    select  * from product limit 10,5; # 从索引为10(第11行)的行开始显示,一共显示5行
    
    -- 显示 76 - 83 行
    select  * from product limit 75,8;
    

保存查询结果

  • 语法

    insert into Table2(field1,field2,) select value1,value2,from Table1 
    
  • 特点

    1、该语句用来保存查询后的结果
    2、结果表的字段一定要和查询的字段数量和类型一致
    
  • 代码

    # 1、创建目标表、用来保存辛苦查询的结果
    create table mydb1.t_avg_price_per_category(
        category_id varchar(20),
        avg_price decimal(12,2)
    );
    
    # 2、将查询的结果插入到结果表
    insert into mydb1.t_avg_price_per_category
    select category_id,avg(price) from product group by  category_id;
    

MySQL的多表关系

介绍

1、在实际的项目中,我们MySQL表一般是很多张,必须要学会多表查询
2、MySQL的多表查询包含:多表join查询,子查询、自关联查询

多表关系

一对一关系(A表和B表): A表的一行对应B的一行,反之B表的一行对应A的一行
一对多关系(A表和B表): A表的一行对应B的多行,反之B表的多行对应A的一行
多对多关系(A表和B表): A表的一行对应B的多行,反之B表的一行对应A的多行

操作-一对一关系

一对一关系由于两张表可以合成一张表,我们不做考虑

操作-一对多关系

介绍
1、一对多描述的表数据之间的包含关系:省对市,部门对员工,领导对员工
2、一对多关系(A表和B表): A表的一行对应B的多行,反之B表的多行对应A的一行
创建外键约束

在这里插入图片描述

#------------------------------------方式1-----------------------------
create database if not exists mydb3;
use mydb3;

# 1、创建主表 - 部门表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

# 2、创建从表 - 员工表
create table if not exists emp
(
    eid     varchar(20) primary key, -- 员工编号
    ename   varchar(20),             -- 员工名字
    age     int,                     -- 员工年龄
    dept_id varchar(20)            -- 员工所属部门
);

# 3、创建外键约束
#alter table <从表名字> add constraint <外键名> foreign key(<外键列名字>) references <主表名字> (<主键列名字>);\
# 给从表emp添加一个外键约束,约束的名字是fk_dept_emp,从表外键列是dept_id,该列要依赖主表dept的deptno主键列
alter table emp add constraint fk_dept_emp foreign key(dept_id) references dept (deptno);

# 4、删除表
# 必须先删除从表,再删主表
drop table emp;
drop table dept;

#------------------------------------方式2-----------------------------
# 1、创建主表 - 部门表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

# 2、创建从表 - 员工表
create table if not exists emp
(
    eid     varchar(20) primary key, -- 员工编号
    ename   varchar(20),             -- 员工名字
    age     int,                     -- 员工年龄
    dept_id varchar(20),            -- 员工所属部门
    constraint fk2_dept_emp foreign key (dept_id) references dept (deptno) -- 外键约束
);

# 4、删除表
# 必须先删除从表,再删主表
drop table emp;
drop table dept;
删除外键约束
alter table emp drop foreign key fk2_dept_emp;
外键约束下的数据操作
#添加数据: 一定要先给主表添加数据,再给从表添加数据
#删除数据:一定要先删从表数据,再删主表数据

# 1、创建主表 - 部门表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

# 2、创建从表 - 员工表
create table if not exists emp
(
    eid     varchar(20) primary key, -- 员工编号
    ename   varchar(20),             -- 员工名字
    age     int,                     -- 员工年龄
    dept_id varchar(20),            -- 员工所属部门
    constraint fk2_dept_emp foreign key (dept_id) references dept (deptno) -- 外键约束
);

# 3、添加数据
# 一定要先给主表添加数据,再给从表添加数据
insert into dept values ('1001','研发部');
insert into emp values (1,'乔峰',20,'1001');


# 4、删除数据
# 一定要先删从表数据,再删主表数据
delete from emp;
delete from dept;

操作-多对多关系

介绍
多对多关系(A表和B表): A表的一行对应B的多行,反之B表的一行对应A的多行

特点
多对多关系必须有中间表,通过中间表将两个表的关系进行定义
代码
# 1、创建左侧主表-学生表
create table if not exists student(
    sid int primary key auto_increment,
    name varchar(20),
    age int,
    gender varchar(20)
);

# 2、创建右侧主表-学科表
  create table course(
   cid  int primary key auto_increment,
   cname varchar(20)
  );

# 3、创建中间表-成绩表
  create table score(
    sid int,
    cid int,
    score double
  );

# 4、创建左侧外键约束
alter table score add constraint fk1 foreign key(sid) references student (sid);

# 5、创建右侧外键约束
alter table score add constraint fk2 foreign key(cid) references course (cid);


# 6、插入数据
# 插入数据时,必须先给两侧的主表插入
insert into student values (1,'小龙女',18,'女');
insert into course values (1,'语文');

# 给中间表插入数据
insert into score values (1,1,88);

# 7、删除数据
# 先删除从表数据
delete  from score;
# 再删除主表数据
delete  from student;
delete  from course;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

MySQL的多表查询

分类

#交叉连接查询 [产生笛卡尔积,了解]
      语法:select * from A,B;  

#内连接查询(使用的关键字 inner join  -- inner可以省略)
    隐式内连接(SQL92标准):select * from A,B where 条件;
    显示内连接(SQL99标准):select * from A inner join B on 条件;

#外连接查询(使用的关键字 outer join -- outer可以省略)
        左外连接:left outer join
            select * from A left outer join B on 条件;
        右外连接:right outer join
            select * from A right outer join B on 条件;
        满外连接: full outer join
             select * from A full outer join B on 条件;
#子查询
       select的嵌套

交叉连接查询

  • 语法

    select * from1,2;  # 92标准写法
    select * from1 cross join2;  # 99标准写法
    
  • 特点

    1、内连接求两张表笛卡尔积,会产生大量的冗余数据,后续所有的查询都是在笛卡尔积基础上进行数据筛选
    
  • 代码

    use mydb3;
    
    -- 创建部门表
    drop table if exists dept3;
    create table if not exists dept3(
      deptno varchar(20) primary key ,  -- 部门号
      name varchar(20) -- 部门名字
    );
     
    -- 创建员工表
    drop table if exists emp3;
    create table if not exists emp3(
      eid int primary key , -- 员工编号
      ename varchar(20), -- 员工名字
      age int,  -- 员工年龄
      dept_id varchar(20)  -- 员工所属部门
    );
    
    
    -- 给dept3表添加数据
    insert into dept3 values('1001','研发部');
    insert into dept3 values('1002','销售部');
    insert into dept3 values('1003','财务部');
    insert into dept3 values('1004','人事部');
    
    
    -- 给emp表添加数据
    insert into emp3 values(1,'乔峰',20, '1001');
    insert into emp3 values(2,'段誉',21, '1001');
    insert into emp3 values(3,'虚竹',23, '1001');
    insert into emp3 values(4,'阿紫',18, '1001');
    insert into emp3 values(5,'扫地僧',85, '1002');
    insert into emp3 values(6,'李秋水',33, '1002');
    insert into emp3 values(7,'鸠摩智',50, '1002');
    insert into emp3 values(8,'天山童姥',60, '1003');
    insert into emp3 values(9,'慕容博',58, '1003');
    insert into emp3 values(10,'丁春秋',71, '1005');
    
    # 交叉连接是将两张表相乘:用左表的每一行去匹配右边的所有行
    # 交叉连接产生了笛卡尔积,会产生大量的冗余数据,后续所有的查询都是在笛卡尔积基础上进行数据筛选
    select * from dept3,emp3;  # 92标准写法
    select * from dept3 cross join emp3;  # 99标准写法
    

内连接查询

  • 语法

    隐式内连接(SQL92标准):select * from A,B where 条件;
    显示内连接(SQL99标准):select * from A inner join B on 条件;
    
  • 特点

    求两张表的交集,重合的部分,就是在笛卡尔积基础上筛选
    
  • 代码

    # 求两张表的交集,重合的部分,就是在笛卡尔积基础上筛选
    # 92标准:select * from A,B where 条件;
    select * from dept3,emp3 where deptno = dept_id ;
    select * from dept3 as t1 ,emp3 as  t2 where t1.deptno = t2.dept_id ;
    
    #99标准:select * from A inner join B on 条件;
    select * from dept3 inner join emp3 on deptno = dept_id ;
    select * from dept3  join emp3 on deptno = dept_id ;
    
    select * from dept3 as t1  inner  join emp3 as  t2 on t1.deptno = t2.dept_id ;
    select * from dept3 as t1   join emp3 as  t2 on t1.deptno = t2.dept_id ;
    
    # 三张表的内连接
    select t1.sid,name,score,cname
    from student t1
                    join score t2 on t1.sid = t2.sid
                    join course t3 on t2.cid = t3.cid ;
    

外连接查询

左外连接

![外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传](https://img-home.csdnimg.cn/images/20230724024159.png?origin_url=images%2Fimage-20240403164050300.png&pos_id=img-y1ub4YFG-1716282724979)

  • 语法

    左外连接:left outer join
     select * from A left outer join B on 条件;
    
    
  • 特点

    # 左外连接:以左表为主,是把左表的输出全部输出,右表有交集部分的输出,没有交集部分就是输出NULL
    # 左外连接会丢失右表没有交集的数据
    # 关键字: left outer join ---> left  join
    
  • 代码

    
    # 左外连接:以左表为主,是把左表的输出全部输出,右表有交集部分的输出,没有交集部分就是输出NULL
    # 左外连接会丢失右表没有交集的数据
    # 关键字: left outer join ---> left  join
    select * from dept3 left join emp3 on deptno = dept_id ;
    
    select t1.sid,name,score,cname
    from student t1
                   left join score t2 on t1.sid = t2.sid
                   left  join course t3 on t2.cid = t3.cid ;
    
右外连接

![外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传](https://img-home.csdnimg.cn/images/20230724024159.png?origin_url=images%2Fimage-20240403164238744.png&pos_id=img-yFsBPIEU-1716282724980)

  • 语法

    左外连接:right outer join
     select * from A right outer join B on 条件;
    
    
  • 特点

    # 右外连接:以右表为主,是把右表的输出全部输出,左表有交集部分的输出,没有交集部分就是输出NULL
    # 右外连接一般用的很少,因为对于左外连接来讲,交换两张表的位置也可以实现右外的效果
    # 右外连接会丢失左表没有交集的数据
    # 关键字: right outer join ---> right  join
    
  • 代码

    select * from dept3 right join emp3 on deptno = dept_id ;
    
满外连接

  • 特点

    1、mysql8.0不支持满外连接,可以使用union关键字来实现
    2、满外连接,将左表和右边的数据全部输出,没有对应的部分都输出NULL,相当于左外和右外的并集
    
  • 代码

    # 满外连接,将左表和右边的数据全部输出,没有对应的部分都输出NULL
    # MySQL不支持满外
    select * from dept3 full join emp3 on deptno = dept_id ;
    
    # 使用union关键字将左外和右外求并集,union关键字是将两张表上下拼接在一起
    # union 去重,  union all:不去重
    select * from dept3 left join emp3 on deptno = dept_id
    union
    select * from dept3 right join emp3 on deptno = dept_id ;
    

案例

# 求每个部门的员工总数

select
   deptno,name,count(eid) as cnt
from dept3
         left join emp3 on deptno = dept_id
group by deptno,name
having cnt > 2
order by cnt desc
limit 3;

MySQL的子查询

概念

子查询就是select的嵌套

代码

#---------------子查询-----------------
# 查询员工年龄最大的信息
select max(age) from emp3;
select * from emp3 where age = (select max(age) from emp3);

# 查询大于平均年龄的员工信息
select avg(age) from emp3;
select * from emp3 where age > (select avg(age) from emp3);

# 查询研发部和销售部的员工信息
#------------------------------------------------
select
  *
from dept3
         left join emp3 on deptno = dept_id
where name in('研发部','销售部');

#------------------------------------------------
select deptno from dept3 where name in('研发部','销售部');

select * from emp3 where dept_id in(select deptno from dept3 where name in('研发部','销售部'));

#------------------------------------------------
# 查询研发部和销售部的员工年龄大于30岁的员工信息
select * from dept3 where name in('研发部','销售部');  # 在左表查询'研发部','销售部'信息
select * from emp3 where age > 30; # 在右表查询age大于30的员工信息

select * from  (select * from dept3 where name in('研发部','销售部')) t1
            join (select * from emp3 where age > 30) t2 on t1.deptno = t2.dept_id;

#删除研发部和销售部的员工
delete from emp3
       where dept_id in (select  deptno from dept3  where name = '研发部' or name = '销售部' );

union
select * from dept3 right join emp3 on deptno = dept_id ;




### 案例

```sql
# 求每个部门的员工总数

select
deptno,name,count(eid) as cnt
from dept3
      left join emp3 on deptno = dept_id
group by deptno,name
having cnt > 2
order by cnt desc
limit 3;

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/643766.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

变量与常量

1.变量 数学中的定义&#xff1a; 变量&#xff1a;可变的数据称为变量 Java中定义&#xff1a; 内存中一块存储区域 该区域的数据可以在同一类型范围内不断变化 变量是程序中最基本的存储单元&#xff0c;包含了变量类型、变量名和存储的值 变量的作用就是在内存中保存…

element-plusDate Picker 日期选择器获取年月日

代码逻辑 对选择日期选择后进行搜索 &#xff1a; function dataValue(value) {console.log(value);scenic_list.value arrlist.value.filter(function (item) {// 判断是否满足搜索条件if (String(item.create_time).indexOf(String(value)) > -1) {return scenic_list}}…

零基础HTML教程(33)--HTML5表单新功能

文章目录 1. 背景2. HTML5新增表单元素2.1 number (数字输入框)2.2 email (邮箱输入框)2.3 url (链接输入框)2.4 tel (电话输入框)2.5 range (范围选择框)2.6 color (颜色选择框)2.7 datetime (日期时间选择框)2.8 search (搜索框) 3. placeholder &#xff08;占位属性&#x…

vue3封装ElementUI plus Dialog弹窗

因为ElementuiPlus的dialog弹框的初始样式不太好看,而公司要求又要好看,本来是已经实现了,但是后来想想了发现封装完dialog的其他功能也要,所以特此记录一下 方案一 思路:封装一个组件,将所有新增的参数引入el-dialog 参数中,实现参数共用 新建一个组件,将官网暴露的属性全部引…

flutter开发实战-外接纹理texture处理图片展示

flutter开发实战-外接纹理处理图片展示 在Flutter中&#xff0c;如果你想要创建一个外接纹理的widget&#xff0c;你可以使用Texture widget。Texture widget用于显示视频或者画布&#xff08;canvas&#xff09;的内容。该组件只有唯一入参textureId 通过外接纹理的方式&…

机器视觉-硬件

机器视觉-硬件 镜头焦距凸透镜焦点不止一个相机镜头由多个镜片组成对焦和变焦 镜头光圈光圈的位置光圈系数F 镜头的景深景深在光路中的几何意义 远心镜头远心镜头的种类远心镜头特性应用场景 镜头的分辨率镜头反差镜头的MTF曲线镜头的靶面尺寸镜头的几何相差相机镜头接口螺纹接…

深度学习设计模式之桥接模式

文章目录 前言一、介绍二、详细分析1.核心组成2.实现步骤3.代码示例4.优缺点优点缺点 5.使用场景 总结 前言 桥接模式是将抽象部分与实现部分分离&#xff0c;使它们都可以独立的变化。 一、介绍 桥接模式是结构型设计模式&#xff0c;主要是将抽象部分与实现部分分离&#x…

APISIX-简单使用

APISIX-简单使用 这个工具还是很不错的&#xff0c;可视化的配置很清晰 &#xff0c; 想用NGINX的配置模式也是可以的&#xff0c;就是要去修改配置文件了。 APISIX&#xff0c;一个很不错的可视化工具&#xff0c;用来代替Nginx相当不错&#xff0c;可作为Nginx的平替方案&…

【单元测试】如何让单元测试的价值最大化

如何让单元测试的价值最大化 1.背景2.用例设计问题3.边界测试问题4.Mock 测试问题5.与集成测试的分工问题6.单测度量问题7.总结 1.背景 关于 “什么是单元测试”、“为什么要做单元测试”、“怎么做单元测试”&#xff0c;网络上相关的技术文章汗牛充栋。尽管如此&#xff0c;…

大数据之Hive函数大全

&#x1f527; Hive函数大全 更多大数据学习资料请关注公众号“大数据领航员"免费领取 一、数学函数 1、取整函数: round 1.函数描述 返回值语法结构功能描述doubleround(double a)返回double类型的整数值部分&#xff08;遵循四舍五入&#xff09; 2.例程 hive>…

DOS学习-目录与文件应用操作经典案例-copy

欢迎关注我&#x1f446;&#xff0c;收藏下次不迷路┗|&#xff40;O′|┛ 嗷~~ 目录 一.前言 二.使用 三.案例 一.前言 copy命令的功能是复制一个或多个已经存在的文件到新的位置&#xff0c;或者将多个文件的内容整合后保存为一个单独的文件&#xff0c;亦或者用于创建批…

SQL基础交互

第二章 检索数据 例如&#xff0c;我们从数据库表 products 中查询 prod_id 和 vend_id,各个列之间以逗号分隔&#xff0c;最后一列的后面不加逗号。 SELECT prod_id, vend_id FROM products; 我们还可以从数据库表中查询所有列。例如: SELECT prod_id, vend_id, prod_name, …

【openlayers系统学习】00官网的Workshop介绍

00Workshop介绍 官方文档&#xff1a;https://openlayers.org/workshop/en/ openlayers官网Workshop学习。 通过官网Workshop&#xff0c;系统学习openlayers的使用。 基本设置 这些说明假定您从最新Workshop版本的 openlayers-workshop-en.zip​ 文件开始。此外&#xff…

继“三级淋巴结”之后,再看看“单细胞”如何与AI结合【医学AI|顶刊速递|05-25】

小罗碎碎念 24-05-25文献速递 今天想和大家分享的是肿瘤治疗领域的另一个热点——单细胞技术&#xff0c;我们一起来看看&#xff0c;最新出炉的顶刊&#xff0c;是如何把AI与单细胞结合起来的。 另外&#xff0c;今天是周末&#xff0c;所以会有两篇文章——一篇文献速递&…

【LeetCode:2769. 找出最大的可达成数字 + 模拟】

&#x1f680; 算法题 &#x1f680; &#x1f332; 算法刷题专栏 | 面试必备算法 | 面试高频算法 &#x1f340; &#x1f332; 越难的东西,越要努力坚持&#xff0c;因为它具有很高的价值&#xff0c;算法就是这样✨ &#x1f332; 作者简介&#xff1a;硕风和炜&#xff0c;…

C++—数组

数组是由一批相同类型的元素&#xff08;element&#xff09;的集合所组成的数据结构&#xff0c;分配一块连续的内存来存储。 语法&#xff1a; <数据类型> <数组名>[<数组长度>]; 数据类型&#xff1a;数组内存放的数据类型&#xff0c;如int、char&…

高仿百度网页(附带源码)

高仿百度网页 效果图部分源码及素材领取源码下期更新预报 效果图 部分源码及素材 <script language"javascript">function show_date_time() {window.setTimeout("show_date_time()", 1000);BirthDay new Date("1/20/2023 16:52:21");//…

Mongodb分布式id

1、分布式id使用场景 分布式ID是指在分布式系统中用于唯一标识每个元素的数字或字符串。在分布式系统中&#xff0c;各个节点或服务可能独立运行在不同的服务器、数据中心或地理位置&#xff0c;因此需要一种机制来确保每个生成的ID都是全局唯一的&#xff0c;以避免ID冲突。 …

FreeRTOS 源码概述

FreeRTOS 目录结构 使用 STM32CubeMX 创建的 FreeRTOS 工程中&#xff0c;FreeRTOS 相关的源码如下: 主要涉及2个目录&#xff1a; Core Inc 目录下的 FreeRTOSConfig.h 是配置文件 Src 目录下的 freertos.c 是 STM32CubeMX 创建的默认任务 Middlewares\Third_Party…

FreeRTOS任务间通信“IPC”

---------------信号量--------------- 信号量的定义&#xff1a; 操作系统中一种解决问题的机制&#xff0c;可以实现 “共享资源的访问” 信号&#xff1a;起通知作用量&#xff1a;还可以用来表示资源的数量当"量"没有限制时&#xff0c;它就是"计数型信…