mysql笔迹海牛

1-MySQL 介绍 DDL,DML,DQL 操作

教程 ⋅ 薪牛 ⋅ 于 3个月前 ⋅ 172 阅读

1 数据库基本概念

1.1 数据库#

数据库产生背景：

数据库就是存储数据的仓库，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。

1.2 关系型数据库#

一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

主流的关系型数据库有 MySQL、Oracle、DB2、SQL Server等。

MYSQL ：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。

Oracle ：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。

DB2 ：IBM公司的数据库产品，收费的。常应用在银行系统中。

SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。

本课程使用MySQL

1.3 实体关系模型-ER图#

怎样将现实生活中的事物，以数据的形式存储起来，就需要一种模型将两者的关系描绘出来。这种模型就是实体关系模型

实体关系模型对现实世界进行抽象，得出实体类型和实体间的关系，用来描述现实世界中数据的组成结构。

实体关系图（Entity Relationship Diagram）是指提供了表示实体、属性和关系的图形化表示方式，用来描述现实世界的概念模型，也简称为E-R图。

实体关系模型核心的元素

1. 实体（Entity）：是具有相同特征和属性的现实世界事务的抽象，在E-R图中用矩形表示，矩形框内注明实体的名称

2. 属性（Attribute）：是指实体具有的特性，一个实体可以包含若干个实体。在E-R图中属性用椭圆形表示，并使用线条将其与相应的实体连接起来。比如员工具有工号、入职日期等属性

3. 关系（Relationship）：是指实体之间的相互联系的方式，一般具有一对一关系（1:1）、一对多关系（1:N）、多对多关系（M:N）

例子：

E-R图与数据表

E-R图	数据表
实体	表
属性	字段
实体的一个记录	表的一行记录

比如：

学生实体 对应 学生表。

一个实体有多个属性， 对应 学生表 有多个字段（二维表的多列）。

一个实体的一条记录， 对应 学生表的一行记录（二维表的一行）。

1.4 数据库三范式#

为了规范化关系型数据模型，要求数据库系统在设计时必须遵循一定的规则，这种规则就称为关系型数据库系统范式。

使用范式的主要目的是为了降低数据的冗余，设计结构合理的数据库。

三范式

第一范式（1NF）：字段必须具有单一属性特性，不可再拆分，比如姓名字段，必须具有单一的属性，不可以在一个字段中包含员工中文名或英文名，必须考虑拆分为两个字段。

第二范式（2NF）：表要具有唯一性的主键列。也就是说表中的每一行要具有一个唯一性的标识列，比如通常使用GUID或自动增长的数字编号来唯一地标识一行，或者是使用学号来唯一标识一个学生。

第三范式（3NF）：表中的字段不能包含在其他表中已出现的非主键字段，比如学生表里面不能出现班级名字字段,可以使用班级ID。

2 MySQL安装

2.1 MySQL安装#

下载地址：MySQL :: Download MySQL Community Server (Archived Versions)

# 由于网络原因，本课程采用离线安装方式
# 解压安装包
cd /public/software/database/
mkdir -p /opt/tools/mysql
tar -xf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar -C /opt/tools/mysql
​
# 删除系统自带的MySQL-libs 卸载干净
yum remove -y mysql-libs
​
# 安装server时要依赖
yum install -y net-tools
​
# 离线安装
rpm -vih /opt/tools/mysql/mysql-community-common-5.7.22-1.el7.x86_64.rpm
rpm -vih /opt/tools/mysql/mysql-community-libs-5.7.22-1.el7.x86_64.rpm
rpm -vih /opt/tools/mysql/mysql-community-client-5.7.22-1.el7.x86_64.rpm
rpm -vih /opt/tools/mysql/mysql-community-server-5.7.22-1.el7.x86_64.rpm
rpm -ivh /opt/tools/mysql/mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm
​
# 启动MySQL
systemctl start mysqld
#查看状态
systemctl status mysqld
#自动启动
systemctl enable mysqld
​
cat /var/log/mysqld.log | grep password 查看初始化密码
​
# 登录
mysql -uroot -p
# 输入初始化密码
​
# 设置校验密码的长度
set global validate_password_policy=LOW; 
# 修改密码
set password=PASSWORD('12345678');
V3lCQwknj&cl
#退出
exit
# 修改my.cnf,默认在/etc/my.cnf，执行：vim /etc/my.cnf，添加如下内容：
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
​
# 重启生效
systemctl restart mysqld
# 对外开放权限
grant all privileges on *.* to 'root'@'%' identified by '12345678';
#刷新权限
flush privileges; 
grant all privileges on *.*哭 to 'root'@'%' root用户在所有机器上均可identified by '12345678';
flush privileges; 

2.2 DBeaver连接mysql#

打开远程桌面找到Dbeaver

点击添加连接，选择mysql组件

编写连接信息

连接成功

3 SQL语句

英文：Structured Query Language，简称 SQL，结构化查询语言，操作关系型数据库的编程语言

sql的作用：

• 在数据库中检索信息。

• 对数据库的信息进行更新。

• 对数据进行删除

• 添加信息到数据库

• 改变数据库的结构。

• 控制数据访问权限

3.1 SQL语句分类#

数据定义语言（DDL）：

主要由create（创建库、表）、alter（修改结构）、drop（删除库、表） 和 truncate（摧毁重建） 四个关键字完成。

数据操作语言（DML）：

分别用于添加、修改和删除表中的行。主要由insert（添加）、update（修改） 和 delete（删除） 三个关键字完成。

数据查询语言（DQL）：

用来查询数据库中表的记录。

主要由select关键字完成，查询语句是SQL语句中最复杂、功能最丰富的语句。

数据控制语言（DCL）：

用来定义数据库的访问权限和安全级别，及创建用户。

主要由 grant 和 revoke 两个关键字 完成。

事务控制语句（TCL）：

主要讲前三种。 主要由commit 、rollback 和 savepoint 三个关键字完成。

3.2 SQL通用语法#

show databases;

use

• SQL语句可以单行或多行书写，以分号结尾

• MySQL数据库的SQL语句不区分大小写(但是数据区分)

• 注释

  • 单行注释:

-- 注释内容 或 #注释内容(MySQL 特有) 
--空格注释

• 多行注释:

/* 
注释内容
*/

3.3 MySQL的数据类型#

整型、浮点型、定点数类型、日期时间、字符串类型、文本类型

整型#

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT

浮点型#

MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE。

MySQL允许使用 非标准语法 （其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用）： FLOAT(M,D) 或 DOUBLE(M,D) 。这里，M称为 精度 ，D称为 标度 。(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。

例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。

定点数类型#

MySQL中的定点数类型只有 DECIMAL 一种类型。

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

日期与时间类型#

• YEAR 类型通常用来表示年

• DATE 类型通常用来表示年、月、日

• TIME 类型通常用来表示时、分、秒

• DATETIME 类型通常用来表示年、月、日、时、分、秒

• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

注意：TIMESTAMP（时间戳）如果将来不给这个字段赋值或者赋值为null，那么将采用系统当前时间自动填充。

字符串类型#

CHAR和VARCHAR类型都可以存储比较短的字符串。

char类型

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。

• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。

VARCHAR类型

• VARCHAR(M) 定义时， 必须指定 长度M，否则报错。

• MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。

• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

文本类型#

在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和VARCHAR类型相同。

由于实际存储的长度不确定， MySQL 不允许 TEXT 类型的字段做主键。

BLOB类型#

BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。

MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如 图片 、 音频 和 视频 等。需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到 服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中。

4 DDL操作

4.1 DDL操作之数据库#

操作数据库相关命令

-- 创建数据库,数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8
create database 数据库名;
-- 创建指定字符集编码的数据库
create database 数据库名 character set 字符集;
​
-- 查看数据库列表
show databases;
​
-- 使用数据库
use 数据库名;
​
-- 查看当前使用数据库
select database();
​
-- 删除数据库
drop database 数据库名称;

代码示例：

-- 创建hainiudb数据库
create database hainiudb;
-- 创建utf8字符集编码的数据库
create database testdb  character set utf8;
​
-- 查看数据库列表
show databases;
​
-- 使用hainiudb数据库
use hainiudb;
​
-- 查看当前使用数据库
select database();
​
-- 删除testdb数据库
drop database testdb;

4.2 DDL操作之数据表#

4.2.1 创建表#

建表格式：

-- 创建数据表
create table 表名 (
    字段名1 数据类型 [约束][缺省值][描述],
    ...
    字段名N 数据类型 [约束][缺省值][描述],
    ...
);

创建一张商品表

-- 使用hainiudb数据库
use hainiudb;
​
-- 创建商品类别表
-- 表内有 类别id， 类别名称字段
CREATE TABLE `category` (
  `cid` varchar(32) NOT NULL,
  `cname` varchar(50),
  PRIMARY KEY (`cid`)
);
-- 其中： cid 是主键， 通过 在后面标记字段是 PRIMARY KEY (`cid`)，主键可以唯一定义一行记录

4.2.2 查看表#

-- 查看表列表
show tables;
​
-- 查看表结构
desc category;

4.2.3 修改表#

1）修改表添加列

语法：alter table 表名 add 列名 类型(长度) [约束];

-- 为商品类别表添加一个新的字段为 分类描述 varchar(10)
-- 当添加列后，这个列成为表的最后的字段
alter table category add `desc` varchar(10);

2）修改表中列的类型长度及约束

语法：alter table 表名 modify 列名 类型(长度) 约束;

--为商品类别表的描述字段进行修改，类型varchar(30) 添加约束 not null(该字段的值不能为null)
 alter table category modify `desc` varchar(30) not null;

注意：在使用过程中要考虑表中已经存储的数据，所以只会修改字段类型长度扩容。

3）修改表中列名

语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;

-- 为商品类别表的分类名称字段进行更换 更换为 cdesc varchar(30)
ALTER TABLE `category` CHANGE `desc` `cdesc`  VARCHAR(30);

4）修改表删除列

语法：alter table 表名 drop 列名;

--  删除商品分类表中cdesc这列
alter table category drop cdesc;

5）修改表名

语法：rename table 表名 to 新表名;

-- 为分类表category 改名成 product_type
rename table category to product_type;

4.2.4 删除表#

是把表结构和表数据都删除

格式：drop table 表名;

-- 删除表
drop table product_type;

5 DML操作

5.1 插入表记录——insert#

语法：

-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values  (值1,值2,值3..);
​
--向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values (值1,值2,值3..);

注意：

值与字段必须对应，个数相同，类型相同

值的数据大小必须在字段的长度范围内

除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）

如果要插入空值，可以不写字段，或者插入 null。

示例：

1）重新创建商品类别表

-- 创建商品类别表
-- 表内有 类别id， 类别名称、描述 三个字段
CREATE TABLE `category` (
  `cid` varchar(32) NOT NULL,
  `cname` varchar(50),
  PRIMARY KEY (`cid`)
);

2）给表添加数据

-- 添加数据
-- 最标准写法(列与值是一一对应的)
insert into category (cid,cname) values (1,'家用电器');
​
-- 简易写法(值得顺序必须和列循序一致)
insert into category values (2,'手机/运营商/数码');
​
-- 便捷写法(一次性插入多条语句可以共享前置语法)
insert into category values (3,'电脑/办公'),(4,'家居/家具/家装/厨具'),(5,'男装/女装/童装/内衣');

5.2 修改表记录——update#

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

语法：

--更新所有记录的指定字段（一般不这么用）
update 表名 set 字段名=值,字段名=值,...;
​
--更新符合条件记录的指定字段（常用）
update 表名 set 字段名=值,字段名=值,... where 条件;

注意：

列名的类型与修改的值要一致.

修改值得时候不能超过最大长度.

除了数值类型外，其它的字段类型的值必须使用倒引号引起

更新时要加一定的筛选条件。

示例：

-- 将家用电器更新为 美妆/个护清洁/宠物
update category set cname = '美妆/个护清洁/宠物' where cname = '家用电器';
update tablename set column = int/varchar/decimal/double// where column = xxx
-- 将ID为5的数据更新为 男鞋/运动/户外
update category set cname = '男鞋/运动/户外' where cid = 5;

5.3 删除表记录——delete#

1）如果删除表中的部分数据

此种方式只是删除表中的数据

语法：delete from 表名 where 条件;

2）如果删除表中所有数据

语法：delete from 表名;

或者

truncate table 表名;

3）delete vs truncate

delete：一条一条删除表中的数据，如果表中数据非常多，那这个执行很慢。

truncate：先把表删除，再创建一个空表，也相当于删除了表数据。

在数据非常多的情况下，要删除表所有数据，建议用truncate。

示例：

-- 删除ID为1的数据
delete from category where cid = 1;
​
-- 删除商品类别表的所有数据
delete from category;
​
-- 摧毁商品类别表的表结构然后重新创建
truncate table category;

6 DQL操作

6.1 数据准备#

创建商品表：

商品表 product

商品编号 主键 自增

商品名称 字符

商品价格 浮点型

商品类别ID

create table product(
    pid int  primary key auto_increment,
    pname varchar(500),
    price double,
    c_id int
);
create table product(
    pid int  not nul （notnullbe本就不为空科技家可不加）primary key auto_increment,
    pname varchar(500),
    price double,
    c_id int
);

添加测试数据:

INSERT INTO product VALUES(null,' 联想（Lenovo）威6 14英寸商务轻薄笔记本电脑(i7-8550U 8G 256G PCIe SSD FHD MX150 Win10 两年上门)鲨鱼灰',5999,1);
INSERT INTO product VALUES(null,'联想(Lenovo)拯救者Y7000 15.6英寸游戏笔记本电脑(英特尔八代酷睿i5-8300H 8G 512G GTX1050 黑)',5999,1);
INSERT INTO product VALUES(null,'三洋（SANYO）9公斤智能变频滚筒洗衣机 臭氧除菌 空气洗 WiFi智能 中途添衣 Magic9魔力净',2499,1);
INSERT INTO product VALUES(null,'海尔（Haier) 滚筒洗衣机全自动 10公斤变频 99%防霉抗菌窗垫EG10014B39GU1',2499,1);
INSERT INTO product VALUES(null,'雷神(ThundeRobot)911SE炫彩版 15.6英寸游戏笔记本电脑（I7-8750H 8G 128SSD+1T GTX1050Ti Win10 RGB IPS）',6599,1);
INSERT INTO product VALUES(null,'七匹狼休闲裤男2018秋装新款纯棉男士直筒商务休闲长裤子男装 2775 黑色 32/80A',299,2);
INSERT INTO product VALUES(null,'真维斯JEANSWEST t恤男 纯棉圆领男士净色修身青年打底衫长袖体恤上衣 浅花灰 M',35,2);
INSERT INTO product VALUES(null,'PLAYBOY/花花公子休闲裤男弹力修身 秋季适中款商务男士直筒休闲长裤 黑色适中款 31(2.4尺)',128,2);
INSERT INTO product VALUES(null,'劲霸男装K-Boxing 短版茄克男士2018新款休闲舒适棒球领拼接青年夹克|FKDY3114 黑色 185',362,2); 
INSERT INTO product VALUES(null,'Chanel 香奈儿 女包 2018全球购 新款蓝色鳄鱼皮小牛皮单肩斜挎包A 蓝色',306830,3);
INSERT INTO product VALUES(null,'皮尔卡丹(pierre cardin)钱包真皮新款横竖款男士短款头层牛皮钱夹欧美商务潮礼盒 黑色横款（款式一）',269,3);
INSERT INTO product VALUES(null,'PRADA 普拉达 女士黑色皮质单肩斜挎包 1BD094 PEO V SCH F0OK0',28512,3);
INSERT INTO product VALUES(null,'好想你 干果零食 新疆特产 阿克苏灰枣 免洗红枣子 玛瑙红500g/袋',21.9,4);
INSERT INTO product VALUES(null,'三只松鼠坚果大礼包1588g每日坚果礼盒干果组合送礼火红A网红零食坚果礼盒8袋装',128,4);
INSERT INTO product VALUES(null,'三只松鼠坚果炒货零食特产每日坚果开心果100g/袋',32.8,4);
INSERT INTO product VALUES(null,'洽洽坚果炒货孕妇坚果零食恰恰送礼每日坚果礼盒（26g*30包） 780g/盒（新老包装随机发货)',149,4);
INSERT INTO product VALUES(null,'今之逸品【拍3免1】今之逸品双眼皮贴双面胶美目舒适隐形立显大眼男女通用 中号160贴',9.9,5);
INSERT INTO product VALUES(null,'自然共和国 原自然乐园 芦荟舒缓保湿凝胶300ml*2（约600g）进口补水保湿舒缓晒后修复面膜',72,5);
INSERT INTO product VALUES(null,'欧莱雅LOREAL 男士火山岩控油清痘洁面膏100ml（洗面奶男 清洁毛孔 祛痘 男士洗面奶）',38.9,null);
INSERT INTO product VALUES(null,'阿拉丁 aladdin 144-62-7 无水草酸 O107180 草酸，无水 500g',88.1,null);
INSERT INTO product VALUES(null,'远东电缆（FAR EAST CABLE）BVVB 2*2.5平方国标家装照明插座用2芯硬护套铜芯电线装潢明线 100米',473,null);

6.2 简单查询#

1）查询表所有记录

-- 查询所有的商品 
select * from product;
​
-- 查询所有商品的商品名和商品价格
select pname,price from product;

2）别名查询，使用的关键字是as（as可以省略）

-- 表别名: 
select p.pname,p.price from product p;
​
--列别名：
select p.pname as name1,p.price price1 from product p;

3）去掉重复值

-- 查询有多少种不同的价格
select distinct price from product;

4）查询结果是表达式（运算查询）

-- 将所有商品的价格+10元进行显示
select pname,price price1,(price+10) price2 from product;

6.3 条件查询#

格式：select ... from tablename where ...;

说明：筛选符合条件的行记录。

比较运算符	>、<、<= 、 >=、= 、!=、<>	大于、小于、大于(小于)等于、不等于
BETWEEN...AND...	显示在某一区间的值(含头含尾)
IN(set)	显示在in列表中的值，例：in(100,200)
LIKE ‘张pattern’	模糊查询，Like语句中，%代表零个或多个任意字符，代表一个字符，例如：first_name like ‘a%’;
IS NULL	判断是否为空
逻辑运算符	and	多个条件同时成立
or	多个条件任一成立
not	不成立，例：where not(salary>100);

查询商品名称为“三只松鼠坚果炒货零食特产每日坚果开心果100g/袋”的商品所有信息：

SELECT * FROM product WHERE pname = '三只松鼠坚果炒货零食特产每日坚果开心果100g/袋';

查询价格为299商品

SELECT * FROM product WHERE price = 299;

查询价格不是800的所有商品

-- 写法1 : 最正常的写法
SELECT * FROM product WHERE price != 800;
-- 写法2 : 比较诡异的写法
SELECT * FROM product WHERE price <> 800;

查询商品价格大于60元的所有商品信息

SELECT * FROM product WHERE price > 60;

查询商品价格在2000到10000之间所有商品

-- 标准写法
SELECT * FROM product WHERE price >= 2000 AND price <=10000;
-- 简易写法 效果一样
SELECT * FROM product WHERE price BETWEEN 2000 AND 10000;

查询商品价格小于2000或大于10000的所有商品

select * from product where price > 10000 or price < 2000;

查询商品价格等于 306830 、28512 的商品信息

-- 标准写法
select * from product where price = 306830 or price = 28512;
-- 简写方案
select * from product where price in (306830,28512);

查询含有 '霸' 字的所有商品

SELECT * FROM product WHERE pname LIKE '%霸%';

查询以'三'开头的所有商品

SELECT * FROM product WHERE pname LIKE '三%';

查询第二个字为'想'的所有商品 _d代表一个字符

SELECT * FROM product WHERE pname LIKE '_想%';
​
#包含21.9或128的商品
mysql> select * from product where price in(21.9,128);

商品表中没有分类的商品 is null

SELECT * FROM product WHERE c_id IS NULL;

查询商品表中有分类的商品

SELECT * FROM product WHERE c_id IS NOT NULL;

6.4 排序查询#

通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。

格式:

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
-- ASC 升序 (默认)
-- DESC 降序

1）按照价格降序排序

SELECT * FROM product ORDER BY price DESC;

3）在价格排序(降序)的基础上，以分类排序(降序)多个字段逗号隔开

SELECT * FROM product ORDER BY price DESC,c_id DESC;

3）显示商品的价格(去重)，并排序(降序)

SELECT DISTINCT price FROM product ORDER BY price DESC;

6.5 聚合查询#

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

聚合查询就是先把表的数据聚在一起，统一进行计算后，再得出一个结果的查询方式。

聚合函数：

count：

COUNT()函数进行计数使用

COUNT(*)对表中行的数目进行计数，包含空值（NULL）。

使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

1）count(*) vs count(colume)

-- 查询商品的总条数
SELECT COUNT(*) FROM product;
-- 查询结果：21， count(*) 包含 空值
​
-- 查询商品类别id的总条数
select count(c_id) from product;
-- 查询结果：18， count(c_id) 不包含c_id为空的记录

2）查询价格大于2000商品的总条数

SELECT COUNT(*) FROM product WHERE price > 2000;

3）查询分类为 1 的所有商品的总和

SELECT SUM(price) FROM product WHERE c_id = 1;

4）查询分类为2所有商品的平均价格

SELECT AVG(price) FROM product WHERE c_id = 2;

5）查询商品的最大价格和最小价格

SELECT MAX(price),MIN(price) FROM product;

6.6 分组查询#

分组查询是指使用group by字句对查询信息进行分组。

格式：

SELECT 查询字段 FROM 表名 GROUP BY 分组字段 [HAVING 分组条件];
​
-- 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
-- 其中 查询字段只能是 分组字段, 聚合函数，常量 这三种

having与where的区别:

having是在分组后对数据进行过滤

where是在分组前对数据进行过滤

having后面可以使用聚合函数(统计函数)

where后面不可以使用聚合函数

select后的接的要么是常量要么是聚合函数，字段是group by后出现过的

执行顺序

from 查询表 where筛选 group by having select order by

1）统计各个分类商品的个数

SELECT c_id ,COUNT(*) FROM product GROUP BY c_id;

2） 统计各个分类商品的个数，且只显示个数大于3的信息

SELECT c_id ,COUNT(*) FROM product GROUP BY c_id HAVING COUNT(*) > 3;
--等效于
SELECT c_id ,COUNT(*) as num FROM product GROUP BY c_id HAVING num > 3;

3）筛选价格>100 的商品，并统计 统计各个分类商品的个数，且只显示个数大于3的信息

SELECT c_id ,COUNT(*) as num FROM product where price>100 GROUP BY c_id having num > 3;

6.7 分页查询#

语法：

offset 偏移量

-- offset：指定从哪一行开始返回，注意：初始行的偏移量为0。
-- rows：返回具体行数。
​
select * from table_name limit [offset，] rows
--总结：如果limit后面是一个参数，就是检索前多少行。
-- 如果limit后面是2个参数，就是从offset+1行开始，检索rows行记录。
​
-- 查询前x条数据
SELECT 字段 FROM 表名  limit x
SELECT 字段 FROM 表名  limit x,y

示例：

1. 查询product表中前10条记录

select * from product limit 10 

2，每页显示5条数据

select * from product limit 0,5 

2-MySQL 约束,视图,索引及常见函数

教程 ⋅ 薪牛 ⋅ 于 3个月前 ⋅ 163 阅读

1 SQL约束

SQL 约束用于规定表中的数据规则。实际上就是表中数据的限制条件。是为了保证数据的完整性而实现的一套机制。

MySQL的约束种类如下：

• 非空约束：NOT NULL

NOT NULL约束强制该字段不接受 NULL 值。

• 唯一约束：UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录。 即该字段的值不能重复，但null除外。

• 主键约束：PRIMARY KEY

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

• 主键、自动增长约束：PRIMARY KEY AUTO_INCREMENT

在每次插入新记录时，自动地创建主键字段的值。开始值是 1，每条新记录递增 1。

• 外键约束：FOREIGN KEY

FOREIGN KEY约束保证一个表中的数据匹配另一个表中的值的参照完整性。

• 默认约束：DEFAULT

DEFAULT约束用于保证该字段有默认值。

1.1 主键约束#

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须 唯一、非空。

每个表都应该有一个主键，并且每个表只能有一个主键。

作用:

1）保证实体的完整性;

2）加快数据库的操作速度

3） 在表中添加新记录时，DBMS会自动检查新记录的主键值，不允许该值与其他记录的主键值重复。

4) DBMS自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

案例演示：

-- 创建学生表，将id设置为主键
CREATE TABLE student (
    s_id INT PRIMARY KEY,
    s_name VARCHAR(30),
    s_age INT
);
​
-- 显示student表的结构
DESC student;
​
-- 插入数据
INSERT INTO student(s_id, s_name, s_age) 
VALUES 
(1, "Tom", 23), 
(2, "Jerry", 24);
​
-- 查询所有数据
SELECT * FROM student;
​
-- 尝试在主键列插入重复值：报错
INSERT INTO student(s_id, s_name, s_age) 
VALUES 
(2, "Jhon", 25);
​
-- 如需撤销 PRIMARY KEY 约束
-- ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE student DROP PRIMARY KEY;
​
-- 如果在建表后需要添加主键
-- ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
ALTER TABLE student ADD PRIMARY KEY (s_id);
 alter table teacher drop primary key;

1.2 主键自增约束#

在每次插入新记录时，自动地创建主键字段的值。

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

a设置自增：uto_increment

删除：modify

案例演示：

-- 创建教师表
CREATE TABLE teacher (
    t_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增
    t_name VARCHAR(30)
);
​
-- 显示teacher表的结构
DESC teacher;
​
-- 插入数据: 主键列为空，插入数据时自动生成一个自增的数字
INSERT INTO teacher
VALUES
(NULL, "吴亦凡"),
(NULL, "王力宏");
​
SELECT * FROM teacher;
​
-- 如果需要改变自增的起始值
ALTER TABLE teacher AUTO_INCREMENT = 100;
-- 再次插入数据查看
INSERT INTO teacher
VALUES
(NULL, "玉田"),
(NULL, "王麻子");
​
-- 如需删除自增约束
-- ALTER TABLE 表名 MODIFY 列名 数据类型;
ALTER TABLE teacher MODIFY t_id INT;
​
-- 如果在建表后添加自增约束
-- ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
ALTER TABLE teacher MODIFY t_id INT AUTO_INCREMENT;
​
-- 再次插入数据测试
INSERT INTO teacher
VALUES
(NULL, "小甜甜");

1.3 非空约束#

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

案例演示：

-- 创建员工表employee
CREATE TABLE employee (
    e_id INT PRIMARY KEY AUTO_INCREMENT,    -- 设置主键自增
    e_name VARCHAR(100) NOT NULL        -- 姓名列不能为空
);
​
DESC employee;
​
-- 插入数据
INSERT INTO employee
VALUES 
(NULL, "八戒"),
(NULL, "悟空");
​
SELECT * FROM employee;
​
-- 尝试插入一条空数据：报错，此时添加数据，标记 非空的字段，必须给值，否则约束不通过。
INSERT INTO employee
VALUES 
(NULL, NULL);
​
-- 如需删除非空约束
ALTER TABLE employee MODIFY e_name VARCHAR(100) NULL;
​
-- 如需在创建表后，添加非空约束
ALTER TABLE employee MODIFY e_name VARCHAR(100) NOT NULL;

1.4 默认约束#

default：当设置约束后，插入数据时如果不想给值，可以设置默认值。

当插入时，发现你没给值，可以使用默认值填充。

案例演示：

-- 创建学生信息表
CREATE TABLE student_info (
    s_id INT PRIMARY KEY AUTO_INCREMENT,    -- 设置主键自增
    s_name VARCHAR(100) NOT NULL,       -- 姓名列不能为空
  s_modify_time datetime default now()  -- 当添加数据时，如果没有给该列赋值，默认去当前时间
);
​
insert into student_info(s_id, s_name) values (null, 'tom');
​
select * from student_info;

1.5 唯一约束#

unique：字段数据必须唯一不重复、字段可以为NULL。

案例演示：

-- 创建用户表：规定用户名唯一
CREATE TABLE tb_user (
    u_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增
    u_username VARCHAR(100) UNIQUE -- 用户名唯一
);
​
DESC tb_user;
​
-- 插入数据
INSERT INTO tb_user
VALUES
(NULL, "zhangsan"),
(NULL, "lisi");
​
SELECT * FROM tb_user;
​
-- 尝试插入重复值：报错
INSERT INTO tb_user
VALUES
(NULL, "zhangsan");
​
-- 如需删除唯一约束
-- ALTER TABLE 表名 DROP INDEX 列名
ALTER TABLE tb_user DROP INDEX u_username;
​
-- 如果在创建表后，添加唯一约束
-- ALTER TABLE 表名 ADD UNIQUE (列名)
ALTER TABLE tb_user ADD UNIQUE (u_username);
-- 注意：表中该列如果已经存在重复值，不能添加唯一约束。先删除重复值

1.6 外键约束#

一个表中的 FOREIGN KEY （外键）指向另一个表中的 UNIQUE KEY(唯一约束的键)。

我们通过一个实例来解释外键：

订单表（orderlist）：

id	number	uid
1	hn001	1
2	hn002	1
3	hn003	2
4	hn004	2
5	hn005	3
6	hn006	3

用户表（user）：

id	name
1	张三
2	李四
3	王五

订单表中的uid列指向user表中的id列，也就是说添加订单时，订单所属的用户必须是真实存在的；同理，如果要删除用户表中的数据，也必须保证订单表中已经没有该用户的订单。

外键约束能防止非法数据插入外键列**，用于预防破坏表之间连接的行为。**其他的就不能增加和删除

示例：

-- 外键约束语法：CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
​
-- 创建用户表（主表）
CREATE TABLE USER (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL
);
​
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
​
SELECT * FROM USER;
​
-- 创建订单表
CREATE TABLE orderlist (
    id INT PRIMARY KEY AUTO_INCREMENT, -- id
    number VARCHAR(20) NOT NULL, -- 订单编号
    uid INT, -- 外键列
    CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) -- 外键列uid指向user表中的主键id列
);
​
-- 订单表添加数据：外键列的值必须是主表user中存在的主键值
INSERT INTO orderlist 
VALUES
(NULL, "hn001", 1),
(NULL, "hn002", 1),
(NULL, "hn003", 2);
-- 如果不存在，添加失败
INSERT INTO orderlist VALUES (NULL,'hn005',3);
​
-- 删除李四用户，删除失败
DELETE FROM USER WHERE NAME='李四';
​
DESC orderlist;
​
-- 如需删除外键
-- 标准语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
​
-- 在创建表后，添加外键约束
-- 标准语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);
​
-- 当添加数据时，如果想取消外键约束的检查
SET FOREIGN_KEY_CHECKS = 0;
​
-- 恢复外键约束检查
SET FOREIGN_KEY_CHECKS = 1;

1.7 外键级联操作#

级联更新：ON UPDATE CASCADE;

当我们想把user用户表中的某个用户id修改，我们希望订单表中该用户所属的订单用户编号也随之修改。a表的uid和b表的uid都会变

级联删除：ON DELETE CASCADE;

当我们想把user用户表中的某个用户删掉，我们希望该用户所有的订单也随之被删除。

-- 添加外键约束，同时添加级联更新  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON UPDATE CASCADE;
​
-- 添加外键约束，同时添加级联删除  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON DELETE CASCADE;
​
-- 添加外键约束，同时添加级联更新和级联删除  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON UPDATE CASCADE ON DELETE CASCADE;

示例：

-- 继续使用上一节中的数据
​
-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
​
-- 添加外键约束，同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;
​
-- 将王五用户的id修改为5    订单表中的uid也随之被修改
UPDATE USER SET id=5 WHERE id=3;
​
-- 将王五用户删除     订单表中该用户所有订单也随之删除
DELETE FROM USER WHERE id=5;

2.1 表与表的关系#

通过之前ER图的讲解，我们知道实体与实体的关系有三种，实体对应表，所以表与表的关系也有三种。

2.1.1 一对一#

一对一关系是建立在两张表之间的关系。一个表中的一条数据可以对应另一个表中的一条数据。

例如：一个人对应一张身份证，一张身份证对应一个人。

实现方式：

在任意一个表建立外键，去关联另外一个表的主键。

示例：

-- 创建person表
CREATE TABLE person(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    NAME VARCHAR(20)                        -- 姓名
);
-- 添加数据
INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');
​
-- 创建card表
CREATE TABLE card(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    number VARCHAR(20) UNIQUE NOT NULL, -- 身份证号
    pid INT UNIQUE,                         -- 外键列
    CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id)
);
-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

注：一对一关系使用场景较少，因为在该案例的场景中，我们可以把身份证号码作为person表的一个字段。

2.1.2 一对多#

一对多关系是建立在两张表之间的关系。一个表中的一条数据可以对应另一个表中的多条数据，但另一个表中的一条数据只能对应第一张表的一条数据。

外键要设计在多的表中

例如：

一个班级拥有多个学生，一个学生只能够属于某个班级。

一个用户可以有多个订单，一个订单只能属于某个用户。

实现方式：

在多的一方，建立外键约束，来关联一的一方主键。注意：外键永远在多方。外键允许重复，允许含有空值。

示例：

-- 上一节讲解外键约束时，采用的用户和订单表即为一对多的关系
​
-- 班级和学生示例
-- 创建班级表
CREATE TABLE class (
    c_id INT PRIMARY KEY AUTO_INCREMENT,    -- 主键id
    c_name VARCHAR(100) NOT NULL,       -- 班级名称
    c_capacity INT                      -- 班级容量
);
​
-- 班级表插入数据
INSERT INTO class 
VALUES
(NULL, "大数据01", 80),
(NULL, "大数据02", 80),
(NULL, "大数据03", 60);
​
SELECT * FROM class;
​
-- 创建学生表
CREATE TABLE student (
    s_id INT PRIMARY KEY AUTO_INCREMENT,    -- 主键id
    s_name VARCHAR(100) NOT NULL,       -- 学生姓名
    cid INT,                            -- 外键列 学生所属班级
    CONSTRAINT sc_fk1 FOREIGN KEY (cid) REFERENCES class (c_id) -- 外键约束
);
​
DESC student;
​
-- 学生表插入数据（如果外键列所表示的班级id在班级表不存在，则数据插入失败）
INSERT INTO student
VALUES
(NULL, "张三", 1),
(NULL, "李四", 1),
(NULL, "王五", 2);
​
SELECT * FROM student;

2.1.3 多对多#

多对多关系是关系数据库中两个表之间的一种关系， 该关系中第一个表中的一个行可以与第二个表中的一个或多个行相关。第二个表中的一个行也可以与第一个表中的一个或多个行相关。

例如：

学生表和课程表：一个学生可以选修多门课程，一个课程可以被多个学生选修。

产品表和订单表：一个订单中可以包含多个产品，一个产品可能出现在多个订单中。

实现方式：

创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多关系。在该表中建立两个列，每个列作为外键参照各自的表的主键。

示例：

-- 创建student表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    NAME VARCHAR(20)            -- 学生姓名
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');
​
-- 创建course表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    NAME VARCHAR(10)            -- 课程名称
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');
​
-- 创建中间表
CREATE TABLE stu_course(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    sid INT,  -- 用于和student表中的id进行外键关联
    cid INT,  -- 用于和course表中的id进行外键关联
    CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
    CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
-- 上面的关系表示id为1的学生选择了id为1和2的课程，而id为1的课程也被id为1和2的两个学生选择，多对多的关系
​
-- 练习 订单表和商品表
-- 创建订单表
CREATE TABLE t_order (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    order_number VARCHAR(20) NOT NULL,  -- 订单编号
    order_time TIMESTAMP                -- 下单时间
);
​
-- 创建商品表
CREATE TABLE t_product (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    product_name VARCHAR(100),      -- 商品名称
    product_price DOUBLE            -- 商品价格
);
​
-- 创建中间表（连接表表名一般情况下，取两张表的名字用下划线连接，方便管理）
CREATE TABLE t_order_product (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    order_id INT,           -- 引用订单表id
    product_id INT,         -- 引用商品表id
    CONSTRAINT order_product_fk1 FOREIGN KEY (order_id) REFERENCES t_order (id),
    CONSTRAINT order_product_fk2 FOREIGN KEY (product_id) REFERENCES t_product (id)
);
​
-- 插入数据
INSERT INTO t_product
VALUES (NULL, "西游记", 88), (NULL, "三国演义", 77), (NULL, "水浒传", 99);
​
SELECT * FROM t_product;
​
INSERT INTO t_order
VALUES (NULL, "hn001", NULL), (NULL, "hn002", NULL), (NULL, "hn003", NULL);
​
SELECT * FROM t_order;
​
-- 插入中间表数据
INSERT INTO t_order_product
VALUES
(NULL, 1, 1),(NULL, 1, 3),(NULL, 2, 2),(NULL, 2, 3);
-- 表示：id为1的订单买了西游记和水浒传两本书；id为2的订单买了三国演义和水浒传两本书
​
SELECT * FROM t_order_product;

注意：在多对多关系中，插入数据时，先两侧再中间表；而删除数据时，要先中间后两侧。

2.2 多表查询#

2.2.1 准备数据#

重新给商品表和商品类别表初始化数据。

-- 创建category分类表
CREATE TABLE category (
  cid int comment '主键id',
  cname varchar(50) DEFAULT NULL comment '分类名称',
  PRIMARY KEY (cid)
);
​
-- 创建product 表，并声明 category 表的cid字段作为外键
CREATE TABLE product (
  pid int(11) NOT NULL AUTO_INCREMENT comment '主键id',
  pname varchar(500) DEFAULT NULL comment '产品名称',
  price DECIMAL(10,2) DEFAULT NULL comment '产品价格',
  cid int(11) DEFAULT NULL comment '产品所属类别',
  PRIMARY KEY (pid),
  CONSTRAINT `produce_cid_fk` FOREIGN KEY (`cid`) REFERENCES `category` (`cid`)
);
-- 向商品类别表中添加数据
insert into category (cid,cname) values(1,'家用电器/电脑'), (2,'男装/女装/童装/内衣'),(3,'女鞋/箱包/珠宝/钟表'),(4,'食品/酒类/生鲜/特产'),(5,'美妆/个护清洁/宠物');
​
-- 向商品表中添加数据
INSERT INTO product VALUES(null,' 联想（Lenovo）威6 14英寸商务轻薄笔记本电脑(i7-8550U 8G 256G PCIe SSD FHD MX150 Win10 两年上门)鲨鱼灰',5999,1);
INSERT INTO product VALUES(null,'联想(Lenovo)拯救者Y7000 15.6英寸游戏笔记本电脑(英特尔八代酷睿i5-8300H 8G 512G GTX1050 黑)',5999,1);
INSERT INTO product VALUES(null,'三洋（SANYO）9公斤智能变频滚筒洗衣机 臭氧除菌 空气洗 WiFi智能 中途添衣 Magic9魔力净',2499,1);
INSERT INTO product VALUES(null,'海尔（Haier) 滚筒洗衣机全自动 10公斤变频 99%防霉抗菌窗垫EG10014B39GU1',2499,1);
INSERT INTO product VALUES(null,'雷神(ThundeRobot)911SE炫彩版 15.6英寸游戏笔记本电脑（I7-8750H 8G 128SSD+1T GTX1050Ti Win10 RGB IPS）',6599,1);
INSERT INTO product VALUES(null,'七匹狼休闲裤男2018秋装新款纯棉男士直筒商务休闲长裤子男装 2775 黑色 32/80A',299,2);
INSERT INTO product VALUES(null,'真维斯JEANSWEST t恤男 纯棉圆领男士净色修身青年打底衫长袖体恤上衣 浅花灰 M',35,2);
INSERT INTO product VALUES(null,'PLAYBOY/花花公子休闲裤男弹力修身 秋季适中款商务男士直筒休闲长裤 黑色适中款 31(2.4尺)',128,2);
INSERT INTO product VALUES(null,'劲霸男装K-Boxing 短版茄克男士2018新款休闲舒适棒球领拼接青年夹克|FKDY3114 黑色 185',362,2); 
INSERT INTO product VALUES(null,'Chanel 香奈儿 女包 2018全球购 新款蓝色鳄鱼皮小牛皮单肩斜挎包A 蓝色',306830,3);
INSERT INTO product VALUES(null,'皮尔卡丹(pierre cardin)钱包真皮新款横竖款男士短款头层牛皮钱夹欧美商务潮礼盒 黑色横款（款式一）',269,3);
INSERT INTO product VALUES(null,'PRADA 普拉达 女士黑色皮质单肩斜挎包 1BD094 PEO V SCH F0OK0',28512,3);
INSERT INTO product VALUES(null,'好想你 干果零食 新疆特产 阿克苏灰枣 免洗红枣子 玛瑙红500g/袋',21.9,4);
INSERT INTO product VALUES(null,'三只松鼠坚果大礼包1588g每日坚果礼盒干果组合送礼火红A网红零食坚果礼盒8袋装',128,4);
INSERT INTO product VALUES(null,'三只松鼠坚果炒货零食特产每日坚果开心果100g/袋',32.8,4);
INSERT INTO product VALUES(null,'洽洽坚果炒货孕妇坚果零食恰恰送礼每日坚果礼盒（26g*30包） 780g/盒（新老包装随机发货)',149,4);
INSERT INTO product VALUES(null,'今之逸品【拍3免1】今之逸品双眼皮贴双面胶美目舒适隐形立显大眼男女通用 中号160贴',9.9,5);
INSERT INTO product VALUES(null,'自然共和国 原自然乐园 芦荟舒缓保湿凝胶300ml*2（约600g）进口补水保湿舒缓晒后修复面膜',72,5);

2.2.2 连接查询#

2.2.2.1 笛卡尔积查询#

笛卡尔积在SQL中的实现方式是交叉连接(Cross Join)。笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合，得到的结果集记录数是两个表记录数的乘积，这样的结果集也称笛卡尔积。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

笛卡尔积是很可怕的，比如 A，B两表 个10000条数据，得到的笛卡尔积就有 10000 0000条。

示例：

-- 商品类别 5条数据
select count(*) from category;
-- 商品 18条数据
select count(*) from product;
-- 交叉查询 90条数据
select count(*) from product,category;
-- 详细数据
select * from product,category;

2.2.2.2 内连接查询#

内链接是查询两个表的交集的部分。

显示内连接#

标准语法：

SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;

示例：

-- 查看所有的商品信息，并且展示其所属分类信息
SELECT * FROM product p INNER JOIN category c ON p.cid = c.cid;
​
-- 加条件，查询名称中包含 坚果 信息的商品及所属分类信息
SELECT * FROM product p INNER JOIN category c ON p.cid = c.cid WHERE p.pname like '%坚果%';

隐式内连接#

标准语法：

SELECT 列名 FROM 表名1,表名2 where 关联条件;

示例：

-- 查看所有的商品信息，并且展示其所属分类信息
select * from product p, category c where p.cid = c.cid;
​
-- 加条件
select * from product p, category c where p.cid = c.cid and p.pname like '%坚果%';

推荐使用显示内连接。

内链接的问题：

向商品类别表插入一条数据

INSERT into category values (6,'手机/运营商/数码');

但是当我关联查询的时候

select DISTINCT c.* from category c inner join product p on c.cid = p.cid;

只能查询出5个类别

商品表只有这5个类别的数据。如何解决？ 用外连接。

2.2.2.3 外连接查询#

外连接可以把关联查询的两张表的一张表作为主表，另外一张作为从表，而外链接始终保证主表的数据完整。

外连接分三类：左外连接（left outer join）、右外连接（right outer join）和全外连接（full outer join），可以省略outer。

左外连接（left join）#

左外连接查询从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

语法：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
-- 注：其中的OUTER关键字可以省略

案例演示：

-- 查询所有的商品分类信息，并将该分类下所有的商品展示
SELECT * FROM category c LEFT OUTER JOIN product p on c.cid = p.cid;
-- 可以看到即使商品表中没有该分类的商品，也会展示分类表中的信息

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

右外连接（right join）#

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

语法：

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

案例演示：

-- 先给商品表插入一条数据
INSERT INTO product VALUES(null,'樱桃键盘',72,7);
-- 用右连接实现上面的查询结果
select * from product p right join category c on c.cid = p.cid;

2.2.2.4 多表查询综合案例 #

1） 查询价格在一万以内名字中包含 '想' 的商品分类是什么

隐式外连接

-- 查询价格在一万以内名字中包含 '想' 的商品分类是什么
select distinct c.cname from product p,category c where p.cid=c.cid and p.price <10000 and p.pname like '%想%'

显式外连接

-- 查询价格在一万以内名字中包含 '想' 的商品分类是什么
select distinct c.cname from product p left join  category c on p.cid=c.cid where p.price<10000 and p.pname like '%想%';

2）查询所有分类商品的个数

左外连接 :

-- 先通过连接条件生成临时结果，然后再通过group by 汇总出最终结果
select c.cname,count(p.cid) num from category c
left join product p on c.cid = p.cid 
group by c.cname;

2.2.3 子查询#

有时一次查询查不出结果，需要将一次查询完的结果作为条件再进行查询。

子查询是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。

对于一个普通的查询语句，子查询可以出现在两个位置。

1）出现在 from 语句后当成数据表#

语法：

SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];

示例：

-- 通过商品表统计商品共有多少分类
select count(*) from (select DISTINCT cid from product) t1;
select count(*) from (select cid from product group by cid) t1;

2）出现在where 条件后作为过滤条件的值#

子查询的结果是单行单列的

语法：

SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);

示例：

-- 类别为 家用电器/电脑的商品名称和价格
select pname, price from product 
where cid=(select cid from category where cname='家用电器/电脑');

子查询的结果是多行单列的

语法：

 SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 

示例：

-- 查询商品价格大于5000的类别
select * from category where cid in (select cid from product where price>5000);
​
-- 获取所有商品中,平均价格大于1000的分类的全部商品
select * from product where cid in (select p.cid FROM product p group by p.cid HAVING avg(price) > 1000);

使用子查询要注意如下要点：

1）子查询要用括号括起来。

2）把子查询当成数据表时（出现在from 之后），可以为该子查询起别名，尤其是作为前缀来限定数据列时，必须给子查询起别名。

3）把子查询当成过滤条件时，将子查询放在比较运算符的右边，这样可以增强查询的可读性。

2.2.4 综合练习#

创建表：

--创建部门表
create table dept (deptid int primary key ,deptname varchar(20),address varchar(20)); 
insert into dept values(1,'研发部','北京'),(2,'人事部','上海'),(3,'销售部','深圳'),(4,'公关部','东莞');  
--创建员工表
create table emp(empid int primary key ,empname varchar(20),salary DECIMAL ,rizhidate date,mgr int,deptid int); 
insert into emp values(1001,'tom',18000,'2013-10-11',1005,1) ;
insert into emp values(1002,'jerry',13000,'2021-11-11',1005,1); 
insert into emp values(1003,'jack',10000,'2020-09-11',1001,1) ;
insert into emp values(1004,'rose',5000,'2020-10-11',1001,2) ;
insert into emp values(1005,'bob',20000,'2018-08-11',null,2);
​
需求
​
--查出部门的员工数和部门的名称 
select count(d.deptname),d.deptname from dept d join emp e on d.deptid=e.deptid group by deptname 
-- 公司最高的工资是多少，以及这个人是谁 
select empname,salary from emp where salary = (select max(salary)  from emp) 
-- 每个组最高的工资，以及是谁 
select emp.empname,emp.salary from (select max(salary) max,deptid from emp group by deptid) t join emp on t.deptid=emp.deptid and t.max=emp.salary 
--平均工资大于公司总平均工资的部门是哪个部门
select t1.deptid,t1.avg from (select avg(salary) avg,deptid from emp  group by deptid)  t1 join  (select avg(salary) avg from emp) t2 on t1.avg>t2.avg 
-- 求出哪个员工的工资大于本部门的平均工资 
select t2.empname,t2.salary,t2.deptid,t1.avg from (select avg(salary) avg,deptid from emp  group by deptid) t1 join emp t2 on t2.salary >t1.avg  and t1.deptid=t2.deptid

3 视图

什么是视图

视图是一张虚拟表

表示一张表的部分数据或多张表的综合数据

其结构和数据是建立在对表的查询基础上

视图中不存放数据

数据存放在视图所引用的原始表中

一个原始表，根据不同用户的不同需求，可以创建不同的视图

为什么使用视图

• 重用SQL语句

• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。

• 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。

规定及限制

• 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字） 名称_view

• 对于可以创建的视图数目没有限制

视图语法

-- 创建视图
create view view_name as
<select 语句>;
​
-- 查询视图数据
select 字段1,字段2, ... from view_name
​
-- 删除视图
drop view view_name;

示例：

-- 查询 类别为 家用电器/电脑 且 商品价格>5000的商品信息
select pname, price from product 
where cid=(select cid from category where cname='家用电器/电脑') and  price > 5000;
​
-- 查询 类别为 家用电器/电脑 且 商品价格<5000的商品信息
select pname, price from product 
where cid=(select cid from category where cname='家用电器/电脑') and  price < 5000;

发现上面的两个查询有公共的部分，那我们就可以把公共部分创建视图，基于这个视图在来查询相应价格的数据

-- 创建视图 （查询 类别为 家用电器/电脑 的 商品信息）
create view cp1_view as 
select pname, price from product 
where cid=(select cid from category where cname='家用电器/电脑');
​
-- 在此基础上，用视图直接筛选价格>5000的商品
select * from cp1_view where price > 5000;
​
-- 在此基础上，用视图直接筛选价格<5000的商品
select * from cp1_view where price < 5000;

删除视图

drop view cp1_view;

4 索引

4.1 索引概述#

为什么要学索引

如果新华字典没有汉语拼音、偏旁部首目录，你如何查找某个汉字？

一页一页翻找，效率低

如果带着汉语拼音、偏旁部首目录，你如何查找？

先看汉语拼音目录，找到汉字对应的页数，直接找对应页码即可。利用索引检索，效率高

索引是什么

Mysql官方对索引的定义是：索引（Index）是帮助Mysql高效获取数据的数据结构。

提取句子主干就是：索引是数据结构。

索引的目的

索引的目的在于提高查询或检索效率。（拿空间换时间）

索引的优势

提高数据检索效率，降低数据库IO成本。

降低数据排序的成本，降低CPU的消耗。

索引的劣势

索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用磁盘和内存空间

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息 。

4.2 MySQL的索引存储结构#

MySQL默认使用的是B+树存储结构，如下图所示：

其中：

非叶子节点只存储键值。

只有叶子节点才会存储数据，数据存储的是非主键的数据。

叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

优势：

范围查询特别方便，只需要在叶子节点利用有序链表查找范围即可。

4.3 MySQL索引分类#

• 普通索引： 最基本的索引，它没有任何限制。

• 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。

• 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。

• 组合索引：顾名思义，就是将单列索引进行组合。

• 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。

• 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

4.4 MySQL 索引使用#

索引常见语法

-- 创建索引
create table 表名(字段名 字段类型... index  索引名 (类名))
或者
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  -- 默认是B+TREE
ON 表名(列名...);
或者
ALTER TABLE 表名 ADD INDEX 索引名称(列名);
​
-- 查看索引
SHOW INDEX FROM 表名;
​
-- 删除索引
DROP INDEX 索引名称 ON 表名;

案例演示：

--创建普通索引 
create table index1(id int,name varchar(20) , gender varchar(20),index (id)) ; 
insert into index1 values(1,'zs','female') 
--执行计划 
explain select * from index1 where name='zs'; 
explain select id from index1 where id =10; 
--创建唯一索引 
create table index2(id int unique,name varchar(20) ,gender varchar(20), unique index un_index (id)) ; 
insert into index2 values(1,'zs','female') 
--执行计划 
explain select * from index2 where name='zs'; 
explain select id from index2 where id =1; 
--创建联合索引 
create table index3(id int unique,name varchar(20) , gender varchar(20), index n_g_index (name,gender)) ; 
insert into index3 values(1,'zs','female') 
--执行计划 
explain select * from index3 where name='zs'; 
explain select id from index3 where gender ='female'; 
explain select id from index3 where name='zs'and gender ='female';

2）查看某个表的索引

show index from product;

部分结果说明

Table：创建索引的表

Non_unique：索引是否非唯一

Key_name：索引的名称

Column_name：定义索引的列字段

Seq_in_index：该列在索引中的位置

Null：该列是否能为空值

Index_type：索引类型

3）删除索引

DROP INDEX index_product_price on product;
show create table

4.5 MySql创建索引的使用技巧#

创建索引的指导原则

(一) 按照下列标准选择建立索引的列

1. 频繁搜索的列

2. 经常用作查询选择的列

3. 经常排序、分组的列

4. 经常用作连接的列（主键/外键）

(二) 使用索引时注意事项

1. 查询时减少使用*返回全部列，不要返回不需要的列

2. 索引应该尽量小，在字节数小的列上建立索引

3. WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前

4. 避免在ORDER BY子句中使用表达式

4.6 索引失效的场景#

1）当在查询条件中出现 <>、NOT、in、not exists 时，查询时更倾向于全表扫描。

2）在查询条件中有or 也不走索引，尽量不使用。

3）查询条件使用LIKE通配符

SQL语句中，使用后置通配符会走索引，例如查询姓张的学生（select from student where name LIKE 'hai%'），而前置通配符(select from student where name LIKE '%niu')会导致索引失效而进行全表扫描。

4）在索引列上使用函数 或 计算。

5）索引列数据类型不匹配。比如：字段类型是string， 但条件值不是string。

案例演示：

--索引失效场景 
explain select * from  index1 where id <> 1 
explain select * from index1  where id in (1,2,3,4,5); 
explain select * from index1 where id = 1 or id = 2 or id = 3 
explain select * from index1 where id like "%1234%" 
explain select * from index1 where id+1 = 2 
explain select * from index1 where id = '123'