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-23address 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 into 表 values (值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-23address 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-23address 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_incrementname 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_incrementname 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 字段 from 表 where 條件# 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 字段 from 表 where 條件
-- 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 字段 from 表 where 條件1  and 條件2  and 條件3  
select 字段 from 表 where '性別為女'  and '美艷動人'  and '溫柔賢惠'  
select 字段 from 表 where '高'  and '富'  and '帥'  # 多個條件只要有一個條件成立，則結果就為真，多個條件全部為假，結果才為假
select 字段 from 表 where 條件1  or 條件2  or 條件3  
select 字段 from 表 where '性別為女'  or '美艷動人'  or '溫柔賢惠'  
select 字段 from 表 where '高'  or '富'  or '帥'  # ！ 就是把真變成假，假變為真
select 字段 from 表 where !('性別為女'  and '美艷動人'  and '溫柔賢惠') 
select 字段 from 表 where 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,字段2… from 表名 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
selectcategory_id,count(*)
from product
where price > 10     # where是在分組之前進行篩選
group by  category_id
having  count(*) > 5  # having是在分組統計之后進行篩選
order by count(*) desc;#-----------------------------------
selectcategory_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 joinselect * from A left outer join B on 條件;右外連接：right outer joinselect * from A right outer join B on 條件;滿外連接: full outer joinselect * from A full outer join B on 條件;
#子查詢select的嵌套

交叉連接查詢

語法

select * from 表1,表2;  # 92標準寫法
select * from 表1 cross join 表2;  # 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 t1join score t2 on t1.sid = t2.sidjoin 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 joinselect * 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 t1left join score t2 on t1.sid = t2.sidleft  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 joinselect * 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 ;

案例

# 求每個部門的員工總數selectdeptno,name,count(eid) as cnt
from dept3left 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 dept3left 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('研發部','銷售部')) t1join (select * from emp3 where age > 30) t2 on t1.deptno = t2.dept_id;#刪除研發部和銷售部的員工
delete from emp3where 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 dept3left join emp3 on deptno = dept_id
group by deptno,name
having cnt > 2
order by cnt desc
limit 3;