MySQ快速基础篇_MySQL数据库基础

MySQ快速基础篇_MySQL数据库基础

MySQL数据库基础

学习目录

• 数据库介绍
• MySQL数据库安装和使用
• SQL语句
• DDL之数据库操作
• DDL之表操作
• DML数据库操作语言
• SQL约束
• DQL操作
• 多表操作

学习目标

• 掌握DDL操作
• 了解mysql的约束
• 掌握DML操作
• 掌握DQL操作
• 掌握多表联查

一、数据库概述

1、数据库介绍

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

随着互联网的高速发展，大量的数据在不断的产生，伴随而来的是如何高效安全的存储数据和处理数据，而这一问题成为了信息时代的一个非常大的问题，而使用数据库可以高效的有条理的储存数据。

• 可以结构化存储大量的数据；
• 可以有效的保持数据的一致性、完整性；
• 读写效率极高。

2、数据库分类

数据库又分为关系型数据库和非关系型数据库

☆ 关系型数据库

关系型数据库：指采用了关系模型来组织数据的数据库。

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

初学阶段，我们可以先简单的讲关系型数据库理解为一个Excel表格：

☆ 非关系型数据库NoSQL

非关系型数据库：又被称为NoSQL（Not Only SQL )，意为不仅仅是SQL，对NoSQL 最普遍的定义是“非关联型的”，强调 Key-Value 的方式存储数据。

Key-Value结构存储： Key-value数据库是一种以键值对存储数据的一种数据库，类似Java中的map。可以将整个数据库理解为一个大的map，每个键都会对应一个唯一的值。

**
**

关系型和非关系型数据库区别？

关系型：通过二维表维持数据关系（有行有列），大部分存储在硬盘，查询速度上关系型要慢一些，相对而言，安全性更高

非关系型：通过key:value键值对维持数据关系，大部分存储在内存，查询速度上要相对于关系型数据库更快一些，安全系数相对关系型而言不高

3、常见数据库介绍

☆ 关系型数据库

☆ 非关系型数据库

4、小结

① 数据库就是存储数据的仓库，用户可以对数据库中的数据进行增删改查操作

② 数据库分为关系型数据库和非关系型数据库。

③ 常用的关系型数据库有：MySQL，Oracle，DB2，SQLserver，sqlite

④ 常用的非关系型数据库有：Redis，Hbase，MongoDB

二、MySQL数据库

1、MySQL介绍

MySQL是一个关系型数据库管理系统，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件，它是由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品，MySQL 是最流行的关系型数据库管理系统中的一个。

2、MySQL的特点

MySQL是开源的，所以你不需要支付额外的费用。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
MySQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口。这些编程语言包括C、C++、Python、Java、Ruby等等。

3、MySQL版本

MySQL Community Server：社区版本，开源免费，但不提供官方技术支持。

MySQL Enterprise Edition：企业版本，需付费，可以试用30天。

MySQL Cluster：集群版，开源免费。可将几个MySQL Server封装成一个Server。

MySQL Cluster CGE：高级集群版，需付费。

MySQL Workbench（GUITOOL）：一款专为MySQL设计的ER/数据库建模工具。它是著名的数据库设计工具DBDesigner4的继任者。MySQL Workbench又分为两个版本，分别是社区版（MySQL Workbench OSS）、商用版（MySQL WorkbenchSE）。

本次课程，我们使用MySQL8.0版本， MySQL Community Server进行学习。

4、MySQL数据库下载与安装

☆ 下载

MySQL是开源免费的，可以直接去官网下载最新版MySQL，下载地址如下：

下载地址：https://dev.mysql.com/downloads/mysql/，拉到下面，选择 Windows 系统。

建议选择安装版进行安装，点击Go to Download Page进行安装。

点击 Download 后会跳转到如下页面，点击左下角的 No thanks, just start my download. 开始下载。

☆ 安装

下载完成后打开安装包，稍等片刻后，选择开发者默认的安装方式，点击下一步即可。

此后按照流程安装即可，遇到Next（下一步），和Execute（执行）直接点击即可，点击Execute后需要等待几分钟。

直至出现Account and Roles输入数据库密码，此处输入密码务必记住，用于之后登陆数据库。

继续点击Next，出现finish后点击，完成安装。

注意：MySQL软件有一个默认管理员叫做root

5、添加环境变量（Windows）

cmd（DOS窗口）=> 输入命令如mysql => 当前Windows的环境变量中去寻找

添加环境变量的目的：输入终端命令时，可以搜索对应的可执行文件。

首先找到mysql的安装目录，并且定位到mysql文件，将地址栏内容进行复制。

默认的安装路径： C:\Program Files\MySQL\MySQL Server 8.0\bin

找到此电脑右键点击选择属性，弹出如下界面，选择高级系统设置，不同版本系统位置可能不太一样，耐心寻找一下。

进入高级系统设置之后，点击环境变量按钮，即可进入环境变量配置界面。

找到系统变量中的path变量，点击编辑，进入编辑界面。

选择新建，将刚才的赋值的mysql文件的地址粘贴到文本框中，点击确定，完成环境变量配置。

6、检测环境变量是否配置成功

在底部搜索栏输入cmd，按Enter键唤出终端窗口。

输入mysql -V（V要大写），输出如下内容则配置成功。

三、Linux系统下的MySQL安装与使用

1、MySQL安装与使用

课程提供的Linux系统中已经安装好了Mysql，可以无需安装，直接使用即可

安装文档请查看：MySQL8.0安装.docx

2、登陆MySQL数据库

MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录，目前有两种登录场景：

☆ 本地（针对本地MySQL）

# mysql -uroot –p 回车  
password：输入密码1
1
2

案例演示：

说明：
-u 后面是登录的用户名
-p 后面是登录密码, 如果不填写, 回车之后会提示输入密码

☆ 远程（针对远程MySQL，MySQL服务器不再本地）

# mysql -h 远程服务器IP地址 -P 端口号 -u用户名 -p 回车
password：输入密码


案例：
# mysql -h 192.168.88.100 -P 3306 -uroot -p
Enter password:123456
1
2
3
4
5
6
7

☆ 退出

① mysql> exit
② mysql> quit
③ 快捷键Ctrl + d
1
2
3

3、小结

① MySQL的特点：免费，支持大型数据库，标准的SQL语言形式，跨平台。

② 课程使用的版本时MySQL8.0版本。

③ MySQL登录的两种方式：

方法一：本地 mysql -uroot -p123456

方法二：远程mysql -h 192.168.88.100 -P 3306 -uroot -p123456

④ 从MySQL软件中退出有3种方式

方法一：exit

方法二：quit

方法三：Ctrl + d

四、MySQL图形化开发工具DataGrip

1、DataGrip介绍

DataGrip是JetBrains公司推出的管理数据库的产品，功能非常强大，可以兼容各种数据库，另外,JetBrains公司还有一款知名的IDE开发工具IDEA，用户体验非常不错。

下载地址：https://www.jetbrains.com/datagrip/download/#section=windows

2、DataGrip安装

下载完成后打开安装程序，选择安装路径，一直点击Next即可安装。
直到Finish点击完成安装，并打开应用。
选择Evaluate for free 免费试用30天。

3、创建工程

点击File->New->Project新建DataGrip工程

输入项目名称，点击确定。

选择新项目打开方式：This Windows（在本窗口中打开），New Windows（在新窗口中打开）， Attach（附加模式）

4、连接数据库

选择Database下的➕，点击DataSource菜单下的MySQL。
填写对应的参数，连接数据库：连接名，IP，用户名，密码等，点击OK完成连接。
注意：如果第一次使用，需要下载mysql驱动文件。

设置数据库时区：

1. 点击Advanced按钮；
2. 在VM options后面写入-Duser.timezone=Asia/Shanghai，就可以啦；

设置完成后，单击Apply（应用），单击OK，数据库就连接成功了！

5、选择要使用的数据库

点击连接名称之后的按钮可以选择所要使用的数据库：

6、DataGrip软件设置

☆ 设置字体大小

设置文字大小： File—>settings—>Editor---->Font

☆ 设置关键字大写

设置关键字大写： File—>settings—>Editor---->Code Style—>SQL—>MySql(需要设置的数据库)—>Case

☆ 自动排版

自动排版布局： File—>settings—>Editor---->Code Style—>SQL—>MySql(需要设置的数据库)—>Queries
自动排版快捷键：Ctrl+ Alt + L

五、SQL语句

1、连接数据库

结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范，是数据库认识的语句。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

举例：

普通话：各数据库厂商都遵循的ISO标准。

方言：数据库特有的关键字。

2、SQL语句分类

☆ DDL

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

☆ DML

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

☆ DQL

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

☆ DCL

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

3、SQL基本语法

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

select * from students;
1

② 可使用空格和缩进来增强语句的可读性

select
  *
from students;
1
2
3

③ MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

SELECT * FROM user;
等价于
select * from user; 
1
2
3

④ 可以使用单行与多行注释

#  单行注释
-- 单行注释，注意：--后面有一个空格
/*
	...
	多行注释
	...
*/
1
2
3
4
5
6
7

4、小结

结构化查询语言（SQL），是关系型数据库管理系统都需要遵循的规范，。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

SQL语句分为：数据定义语言（DDL），数据操作语言（DML），数据查询语言（DQL），数据控制语言（DCL）。

SQL通用语法：结构灵活，使用分号结尾；不区分大小写；可以使用/**/，#，–来进行注释。

六、DDL数据库操作

1、MySQL的组成结构

注：我们平常说的MySQL，其实主要指的是MySQL数据库管理软件。

一个MySQL DBMS可以同时存放多个数据库，理论上一个项目就对应一个数据库。如博客项目blog数据库、商城项目shop数据库、微信项目wechat数据库。

一个数据库中还可以同时包含多个数据表，而数据表才是真正用于存放数据的位置。（类似我们Office软件中的Excel表格），理论上一个功能就对应一个数据表。如博客系统中的用户管理功能，就需要一个user数据表、博客中的文章就需要一个article数据表、博客中的评论就需要一个message数据表。

一个数据表又可以拆分为多个字段，每个字段就是一个属性。

一个数据表除了字段以外，还有很多行，每一行都是一条完整的数据（记录）。

2、数据库的基本操作

① 创建数据库

普及英语小课堂：

创建 => create

数据库 => database

创建 + 数据库 = create database 数据库名称(字母+数字+下划线组成，以字母开头，不能出现中文以及特殊字符)

基本语法：

mysql> create database 数据库名称 [设置编码格式];
1

特别注意：在MySQL中，当一条SQL语句编写完毕后，一定要使用分号;进行结尾，否则系统认为这条语句还没有结束。

案例：创建数据库的相关案例

创建db_itheima库
create database db_itheima;

创建db1库并指定默认字符集
create database db_itheima default charset gbk;

如果存在不报错(if not exists)
create database if not exists db_itheima default character set utf8;
说明：不能创建相同名字的数据库！
1
2
3
4
5
6
7
8
9

扩展：编码格式，常见的gbk（中国的编码格式）与utf8（国际通用编码格式）

latin1 256个字符

国内汉字无法通过256个字符进行描述，所以国内开发了自己的编码格式gb2312，升级gbk

中国台湾业开发了一套自己的编码格式big5

很多项目并不仅仅只在本地使用，也可能支持多国语言，标准化组织开发了一套通用编码utf8，后来5.6版本以后又进行了升级utf8mb4

编写SQL语句是一个比较细致工作，不建议大家直接在终端中输入SQL语句，可以先把你要写的SQL语句写入一个记事本中，然后拷贝执行。

② 查询数据库

英语小课堂：

显示 => show

数据库 => database

显示 + 所有数据库 = show databases;

基本语法：显示所有数据库

mysql> show databases;
1

③ 删除数据库

英语小课堂：

删除 => drop

数据库 => database

删除 + 数据库 = drop database 数据库名称;

基本语法：

mysql> drop database 数据库名称;
1

案例：删除db_itheima数据库

mysql> drop database db_itheima;
1

④ 选择数据库

从数据库列表中查找需要使用的数据库

格式：

mysql> use db_itheima;
1

查看正在使用的数据库（8.0以后版本需要基于select查询来获取当前数据库）

mysql> select database();
1

3、小结

创建数据库：CREATE DATABASE 数据库名；
查看数据库：SHOW DATABASES;
删除数据库：DROP DATABASE 数据库名；
使用数据库：USE 数据库名；

七、DDL数据表操作

特别注意：创建数据表必须有一个前提，首先要明确选择某一个数据库。

1、数据表的基本操作

☆ 数据表的创建

英语小课堂：

创建 => create

数据表 => table

创建 + 数据表 = create table 数据表名称

基本语法：

mysql> create table 数据表名称(
	字段1 字段类型 [字段约束],
	字段2 字段类型 [字段约束],
	...
); 
1
2
3
4
5

案例：创建一个admin管理员表，拥有3个字段（编号、用户名称、用户密码）

mysql> create database db_itheima;
mysql> use db_itheima;
1
2

use在MySQL中的含义代表选择，use 数据库名称相当于选择指定的数据库。而且use比较特殊，其选择结束后，其尾部可以不加分号；但是强烈建议所有的SQL语句都要加分号，养成一个好习惯。

mysql> create table tb_admin(
	id tinyint,
    username varchar(20),
    password char(32)
) engine=innodb default charset=utf8;
1
2
3
4
5

tinyint ：微整型，范围-128 ~ 127，无符号型，则表示0 ~ 255

表示字符串类型可以使用char与varchar，char代表固定长度的字段，varchar代表变化长度的字段。

案例：创建一个article文章表，拥有4个字段（编号、标题、作者、内容）

mysql> use db_itheima;
mysql> create table tb_article(
	id int,
	title varchar(50),
	author varchar(20),
	content text
) engine=innodb default charset=utf8;
1
2
3
4
5
6
7

text ：文本类型，一般情况下，用varchar存储不了的字符串信息，都建议使用text文本进行处理。

varchar存储的最大长度，理论值65535个字符。但是实际上，有几个字符是用于存放内容的长度的，所以真正可以使用的不足65535个字符，另外varchar类型存储的字符长度还和编码格式有关。1个GBK格式的占用2个字节长度，1个UTF8格式的字符占用3个字节长度。GBK = 65532~65533/2，UTF8 = 65532~65533/3

☆ 查询已创建数据表

英语小课堂：

显示 => show

数据表 => table

显示所有数据表（当前数据库）

mysql> use 数据库名称;
mysql> show tables;
1
2

显示数据表的创建过程（编码格式、字段等信息）

mysql> desc 数据表名称;
1

☆ 修改数据表信息

① 数据表字段添加

英语小课堂：

修改 => alter

数据表 => table

基本语法：

mysql> alter table 数据表名称 add 新字段名称 字段类型 first|after 其他字段名称;
选项说明：
first：把新添加字段放在第一位
after 字段名称：把新添加字段放在指定字段的后面
1
2
3
4

案例：在tb_article文章表中添加一个addtime字段，类型为date(年-月-日)

mysql> alter table tb_article add addtime date after content;
mysql> desc tb_article;
1
2

② 修改字段名称或字段类型

修改字段名称与字段类型（也可以只修改名称）

mysql> alter table tb_admin change username user varchar(40);
mysql> desc tb_admin;
1
2

仅修改字段的类型

mysql> alter table tb_admin modify user varchar(20);
mysql> desc tb_admin;
1
2

③ 删除某个字段

mysql> alter table tb_article drop 字段名称;
mysql> desc tb_article;
1
2

④ 修改数据表名称

rename table 旧名称 to 新名称;
1

☆ 删除数据表

英语小课堂：

删除 => drop

数据表 => table

mysql> drop table 数据表名称;
1

2、字段类型详解

① 整数类型

② 浮点类型

浮点类型（精度失真情况）和定点类型（推荐使用定点类型）

decimal(10,2) ：代表这个数的总长度为10 = 整数长度 + 小数长度，2代表保留2位小数

③ 日期类型

④ 文本

八、DML数据操作语言

1、DML包括哪些SQL语句

insert插入、update更新、delete删除

2、数据的增删改（重点）

英语小课堂：

增加：insert

删除：delete

修改：update

☆ 数据的增加操作

基本语法：

mysql> insert into 数据表名称([字段1,字段2,字段3...]) values (字段1的值,字段2的值,字段3的值...);
1

特别注意：在SQL语句中，除了数字，其他类型的值，都需要使用引号引起来，否则插入时会报错。

第一步：准备一个数据表

mysql> use db_itheima;
mysql> create table tb_user(
	id int,
	username varchar(20),
	age tinyint unsigned,
	gender enum('男','女','保密'),
	address varchar(255)
) engine=innodb default charset=utf8;
1
2
3
4
5
6
7
8

unsigned代表无符号型，只有0到正数。tinyint unsigned无符号型，范围0 ~ 255

enum枚举类型，多选一。只能从给定的值中选择一个

第二步：使用insert语句插入数据

mysql> insert into tb_user values (1,'刘备',34,'男','广州市天河区');
mysql> insert into tb_user(id,username,age) values (2,'关羽',33);
1
2

第三步：批量插入多条数据

mysql> insert into tb_user values (3,'大乔',19,'女','上海市浦东新区'),(4,'小乔',18,'女','上海市浦东新区'),(5,'马超',26,'男','北京市昌平区');
1

☆ 数据的修改操作

基本语法：

mysql> update 数据表名称 set 字段1=更新后的值,字段2=更新后的值,... where 更新条件;
1

特别说明：如果在更新数据时，不指定更新条件，则其会把这个数据表的所有记录全部更新一遍。

案例：修改username='马鹏’这条记录，将其性别更新为男，家庭住址更新为广东省深圳市

mysql> update tb_user set gender='男',address='广东省深圳市' where username='马鹏';
1

案例：今年是2020年，假设到了2021年，现在存储的学员年龄都差1岁，整体进行一次更新

mysql> update tb_user set age=age+1;
1

☆ 数据的删除操作

基本语法：

mysql> delete from 数据表名称 [where 删除条件];
1

案例：删除tb_user表中，id=1的用户信息

mysql> delete from tb_user where id=1;
1

delete from与truncate清空数据表操作

mysql> delete from 数据表;
或
mysql> truncate 数据表;
1
2
3

delete from与truncate区别在哪里？

• delete：删除数据记录
  • 数据操作语言（DML）
  • 删除大量记录速度慢，只删除数据，主键自增序列不清零，100 => 新插入 => 101
  • 可以带条件删除
• truncate：删除所有数据记录
  • 数据定义语言（DDL）
  • 清里大量数据速度快，主键自增序列清零, 100 => 新插入 => 1
  • 不能带条件删除

九、SQL约束

1、主键约束

1、PRIMARY KEY 约束唯一标识数据库表中的每条记录。
2、主键必须包含唯一的值。
3、主键列不能包含 NULL 值。
4、每个表都应该有一个主键，并且每个表只能有一个主键。

主键约束：要求主键这一列必须是唯一的、非空，另外一定要特别注意：一个表可以没有主键，但是如果有主键只能有且仅有一个主键。

遵循原则：

1）主键应当是对用户没有意义的（没创建一个数据表默认都应该拥有一个id字段）
2）永远也不要更新主键。
3）主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。（1-无穷大）
4） 主键应当由计算机自动生成。

创建主键约束：创建表时，在字段描述处，声明指定字段为主键

删除主键约束：如需撤销 PRIMARY KEY 约束，请使用下面的 SQL

alter table persons2 drop primary key;
1

补充：自动增长 => auto_increment，从1开始，依次递增

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整型，自动增长列必须为键(一般是主键)。

下列 SQL 语句把 “Persons” 表中的 “Id” 列定义为 auto_increment 主键

create table persons3(
	id int auto_increment primary key,
	first_name varchar(255),
	last_name varchar(255),
	address varchar(255),
	city varchar(255)
) default charset=utf8;
1
2
3
4
5
6
7

向persons添加数据时，可以不为Id字段设置值，也可以设置成null，数据库将自动维护主键值：

insert into persons3(first_name,last_name) values('Bill','Gates');
insert into persons3(id,first_name,last_name) values(null,'Bill','Gates');
1
2

运行效果：

特别说明：如果某个列设置为自动增长，则这一列必须设置为主键，否则系统会报错。

2、非空约束

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
下面的 SQL 语句强制 “id” 列和 “last_name” 列不接受 NULL 值：

3、唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

注：虽然唯一约束与主键约束在描述上功能类似，但是其实还是很多区别的。

相同点：都可以表示唯一性，唯一标识数据表中的每一条记录（一行）

不同点：

① 插入数据的来源不同

主键大多数都是自动编号结合primary key

唯一大多数都是手工设置（不一定都是数字类型，如身份证号也可以表示唯一）

② 主键必须非空字段，但是唯一约束不要求列必须是非空的，这一列也可以出现null空值

③ 一个数据表中如果有主键，有且仅有1个主键。但是在一个数据表中可以拥有多个唯一约束

所以数据库考察中，经常会问：主键约束与唯一约束区别

主键一定满足唯一约束，但是唯一约束不一定是主键。

请注意：
每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

4、默认值约束

default 默认值

5、外键约束(了解)

外键约束（多表关联使用）

比如：有两张数据表，这两个数据表之间有联系，通过了某个字段可以建立连接，这个字段在其中一个表中是主键，在另外一张表中，我们就把其称之为外键。

6、小结

① 主键约束：唯一标示，不能重复，不能为空。
1）主键应当是对用户没有意义的，一般都叫id
2）永远也不要更新主键，自动编号
3）主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
4）主键应当由计算机自动生成，一般配合auto_increment自动增长

自动增长：
我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整型，自动增长列必须为键(一般是主键)。

② 非空约束：
NOT NULL 约束强制列不接受 NULL 值。

③ 唯一约束：
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

④ 默认值约束

default 默认值

⑤ 外键约束：

多表关联字段、在一个表中是主键，在另外一个表中是非主键（则这个字段一般就称之外键）

十、DQL数据查询语言

1、数据集准备

CREATE TABLE product
(
    pid         INT PRIMARY KEY,
    pname       VARCHAR(20),
    price       DOUBLE,
    category_id VARCHAR(32)
) DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

插入数据：

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,'杰克琼斯',800,'c002');
INSERT INTO product VALUES (5,'真维斯',200,'c002');
INSERT INTO product VALUES (6,'花花公子',440,'c002');
INSERT INTO product VALUES (7,'劲霸',2000,'c002');
INSERT INTO product VALUES (8,'香奈儿',800,'c003');
INSERT INTO product VALUES (9,'相宜本草',200,'c003');
INSERT INTO product VALUES (10,'面霸',5,'c003');
INSERT INTO product VALUES (11,'好想你枣',56,'c004');
INSERT INTO product VALUES (12,'香飘飘奶茶',1,'c005');
INSERT INTO product VALUES (13,'海澜之家',1,'c002');
1
2
3
4
5
6
7
8
9
10
11
12
13

DataGrip软件关键字替换，可以使用Ctrl + R快捷键

2、select查询

# 根据某些条件从某个表中查询指定字段的内容
格式：select [distinct]*| 列名,列名 from 表where 条件
1
2

3、简单查询

# 1.查询所有的商品.  
select *  from product;
# 2.查询商品名和商品价格. 
select pname,price from product;
# 3.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;
1
2
3
4
5
6

4、条件查询（where子句）

☆ 比较查询

# 查询商品名称为“花花公子”的商品所有信息：
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;
# 查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
# 查询商品价格小于等于800元的所有商品信息
SELECT * FROM product WHERE price <= 800;
1
2
3
4
5
6
7
8
9
10
11

☆ 范围查询

# 查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
# 查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price IN (200,800);
1
2
3
4

☆ 逻辑查询

# 查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
# 查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
# 查询价格不是800的所有商品
SELECT * FROM product WHERE NOT(price = 800);
1
2
3
4
5
6

☆ 模糊查询

# 查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
# 查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
1
2
3
4

☆ 非空查询

# 查询没有分类的商品
SELECT * FROM product WHERE category_id IS NULL;
# 查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
1
2
3
4

5、排序查询（order by子句）

# 通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。
格式：SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
ASC 升序 (默认)
DESC 降序

# 1.使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
# 2.在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
1
2
3
4
5
6
7
8
9

6、聚合查询

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

今天我们学习如下五个聚合函数：

案例演示：

# 1、查询商品的总条数
SELECT COUNT(*) FROM product;
# 2、查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;
# 3、查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
# 4、查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE categ ory_id = 'c002';
# 5、查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;
1
2
3
4
5
6
7
8
9
10

7、分组查询与having子句

☆ 分组查询介绍

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

分组查询基本的语法格式如下：

GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]

说明:

• 列名: 是指按照指定字段的值进行分组。
• HAVING 条件表达式: 用来过滤分组后的数据。
• WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

☆ group by的使用

group by可用于单个字段分组，也可用于多个字段分组

-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;
1
2
3
4

① group by可以实现去重操作

② group by的作用是为了实现分组统计（group by + 聚合函数）

☆ group by + 聚合函数的使用

-- 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
-- 统计不同性别的人的个数
select gender,count(*) from students group by gender;
1
2
3
4

☆ group_concat()函数

group_concat()是MySQL中自带的一个函数，这个函数主要用于分组查询中。其主要功能可以某个分组中的数据中的某列（字段）值进行concat拼接操作。

案例：统计男分组中，一共有哪些人，字段值与字段值之间使用逗号隔开。

select gender,group_concat(name) from tb_student group by gender;
1

☆ with rollup回溯统计

with rollup主要应用于group by分组中，代表对所有分组数据进行回溯统计。

select gender,count(*) from tb_student group by gender;
1

但是我们希望在以上分组的基础上，在添加一行，没有分组，只有一个具体的值。这个值是以上两个分组的总记录数。

select gender,count(*) from tb_student group by gender with rollup;
1

8、having的使用

having作用和where类似都是过滤数据的，但是两者之间的执行顺序不同

① where子句 ② group by子句 ③ having子句

第一种情况：如果只是简单的查询操作（没有group by的情况），大部分时间having是可以直接替代where子句

select * from product where price > 800;
1

以上语句等价于

select * from product having price > 800;
1

第二种情况：

-- 根据gender字段进行分组，统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;
1
2

案例演示：

#1 统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;

#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;
1
2
3
4
5

9、limit子句的使用

应用场景：限制查询与分页查询

限制查询：主要限制数据查询的数量（获取数据表中的前3条数据）

select * from 数据表 limit 查询数量;

分页查询：实际特别接近，因为只要有分页的地方，底层100%都是使用limit子句实现的

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

格式：

SELECT 字段1，字段2... FROM 表名 LIMIT M,N
M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
N: 整数，表示查询多少条数据
SELECT 字段1，字段2... FROM 表明 LIMIT 0,5
SELECT 字段1，字段2... FROM 表明 LIMIT 5,5
1
2
3
4
5

10、小结

条件查询：select *|字段名 form 表名 where 条件；
排序查询：SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
聚合查询函数：count()，sum()，max()，min()，avg()。
分组查询：SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件;
分页查询：
SELECT 字段1，字段2... FROM 表名 LIMIT M,N
M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
N: 整数，表示查询多少条数据
1
2
3
4
5
6
7
8

十一、多表查询

表与表之间的关系

在SQL语句中，数据表与数据表之间，如果存在关系，一般一共有3种情况：

① 一对一关系（高级）

比如有A、B两张表，A表中的每一条数据，在B表中有一条唯一的数据与之对应。

用户表user

用户详情表user_items

我们把用户表与用户详情表之间的关系就称之为一对一关系。

② 一对多关系（重点）

比如有A、B两张表，A表中的每一条数据，在B表中都有多条数据与之对应，我们把这种关系就称之为一对多关系

产品分类表

产品信息表

我们把产品分类表与产品表之间的关系就称之为一对多关系。

③ 多对多关系（高级）

用户表

权限表

虽然从以上图解来看，两者之间好像没有任何联系，但是两者之间其实是有关系的，这种关系需要通过一张临时表进行呈现。

每个用户，应该有对应的权限，admin账号可以做增删改查，itheima账号可以做查询

反过来

每个权限都应该对应多个用户，查询权限 => admin/itheima

中间表 ：用户_权限表

交叉连接(了解)

没有意义，但是它是所有连接的基础。其功能就是将表1和表2中的每一条数据进行连接。

结果：

字段数 = 表1字段 + 表2的字段

记录数 = 表1中的总数量 * 表2中的总数量（笛卡尔积）

select * from students cross join classes;
或
select * from students, classes;
1
2
3

1、内连接

☆ 连接查询的介绍

连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。

连接查询可以分为:

1. 内连接查询
2. 左外连接查询
3. 右外连接查询
4. 自连接查询（自己查询自己）

☆ 内连接查询

查询两个表中符合条件的共有记录

内连接查询语法格式:

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
1

说明:

• inner join 就是内连接查询关键字
• on 就是连接查询条件

例1：使用内连接查询学生表与班级表:

select * from students as s inner join classes as c on s.cls_id = c.id;
1

☆ 小结

• 内连接使用inner join … on …, on 表示两个表的连接查询条件
• 内连接根据连接查询条件取出两个表的 “交集”

2、左外连接

☆ 左连接查询

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充

左连接查询语法格式:

select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
1

说明:

• left join 就是左连接查询关键字
• on 就是连接查询条件
• 表1 是左表
• 表2 是右表

例1：使用左连接查询学生表与班级表:

select * from students as s left join classes as c on s.cls_id = c.id;
1

☆ 小结

• 左连接使用left join … on …, on 表示两个表的连接查询条件
• 左连接以左表为主根据条件查询右表数据，右表数据不存在使用null值填充。

3、右外连接

☆ 右连接查询

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

右连接查询语法格式:

select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2
1

说明:

• right join 就是右连接查询关键字
• on 就是连接查询条件
• 表1 是左表
• 表2 是右表

例1：使用右连接查询学生表与班级表:

select * from students as s right join classes as c on s.cls_id = c.id;
1

☆ 小结

• 右连接使用right join … on …, on 表示两个表的连接查询条件
• 右连接以右表为主根据条件查询左表数据，左表数据不存在使用null值填充。

4、自连接查询(扩展)

自连接查询：数据表自己连接自己，前提：连接操作时必须为数据表定义别名！

左表和右表是同一个表，根据连接查询条件查询两个表中的数据。

两个实际的工作场景，求省市区信息，求分类导航信息

cid name pid

1 图书 null

2 童书 1

3 中国儿童文学 2

地域：area

pid 全称 parent id（父级ID编号），如果pid值为null代表本身就是父级，如果pid是一个具体的数值，则代表其属于子级

例1：查询省的名称为“广东省”的所有城市

创建areas表:

use db_itheima;
create table tb_area(
    aid int not null AUTO_INCREMENT,
    atitle varchar(20),
    pid int,
    primary key(aid)
) default charset=utf8;
1
2
3
4
5
6
7

执行sql文件给areas表导入数据:

insert into tb_area values (null, '广东省', null),(null, '山西省', null),(null, '深圳市', 1), (null, '广州市', 1);
1

自连接查询的用法:

select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '广东省';
1

说明:

• 自连接查询必须对表起别名

☆ 小结

• 自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
• 自连接就是一种特殊的连接方式，连接的表还是本身这张表

十二、子查询(三步走)

1、子查询（嵌套查询）的介绍

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

select * from (select * from xxx) as t;

​ 子查询

主查询

主查询和子查询的关系:

1. 子查询是嵌入到主查询中
2. 子查询是辅助主查询的,要么充当条件,要么充当数据源(数据表)
3. 子查询是可以独立存在的语句,是一条完整的 select 语句

了解：子查询的应用场景

答：在我们需求的基础上，如果这个需求需要通过多条SQL语句分步查询的情况，一般都需要基于子查询。

2、子查询的使用

例1. 查询学生表中大于平均年龄的所有学生:

需求：查询年龄 > 平均年龄的所有学生

前提：① 获取班级的平均年龄值

​ ② 查询表中的所有记录，判断哪个同学 > 平均年龄值

第一步：写子查询

select avg(age) from students;
1

第二步：写主查询

select * from students where age > (平均值);
1

第三步：第一步和第二步进行合并

select * from students where age > (select avg(age) from students);
1

例2. 查询tb_goods产品表中具有分类信息的产品

需求：查询产品表中具有分类信息的产品（没有与之对应分类信息的产品不显示）

前提：① 查询分类表中，到底有哪些分类（获取cid编号）

​ ② 到产品表中进行判断，判断这个商品的cid编号与①中的是否相等

第一步：编写子查询

select cid from tb_category;
1

第二步：编写主查询

select * from tb_goods where cid in (所有分类cid编号)
1

第三步：把主查询和子查询合并

select * from tb_goods where cid in (select cid from tb_category);
1

例3. 查找年龄最小且成绩最低的学生:

第一步：获取年龄最小值和成绩最小值

select min(age), min(score) from student;
1

第二步：查询所有学员信息（主查询）

select * from students where (age, score) = (最小年龄, 最少成绩);
1

第三步：把第一步和第二步合并

select * from students where (age, score) = (select min(age), min(score) from students);
1

注：数据表中必须有这样一条记录，否则可能查询不到结果，重点练习子查询返回多个结果情况。

3、小结

子查询是一个完整的SQL语句，子查询被嵌入到一对小括号里面

掌握子查询编写三步走

十三、外键（扩展）

主键：primary key

外键：foreign key（应用场景：在两表或多表关联的时候设置的，用于标志两个表之间的联系）

1、外键约束作用

外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性。

dage表：

xiaodi表：

外键设计原则：保证两张表的关联关系，保证数据的一致性。在选择时，一般在一个表中时关联字段，在另外一个表中是主键，则这个字段建议设置为外键。

2、对于已经存在的字段添加外键约束

-- 为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id) 
[on delete cascade| set null] [on update cascade | set];
1
2
3

3、在创建数据表时设置外键约束

-- 创建学校表
create table school(
    id int not null primary key auto_increment, 
    name varchar(10)
);

-- 创建老师表
create table teacher(
    id int not null primary key auto_increment, 
    name varchar(10), 
    s_id int not null, 
    foreign key(s_id) references school(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13

4、删除外键约束

-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table teacher;

-- 获取名称之后就可以根据名称来删除外键约束
alter table teacher drop foreign key 外键名;
1
2
3
4
5

十四、索引[了解]

1、索引概述

索引作用: 快速检索数据. (提高查询效率)，其底层主要是使用B+ Tree结构

2、普通索引使用

• 创建索引: create index index_cname on category(cname(20));
• 修改表添加索引: alter table category add index index_cname(cname(20));
• 查询索引: show index from category;
• 删除索引: drop index index_cname on category;
• 查看所有库或者表的索引:

# 了解: mysql 是系统自带的数据库。innodb_index_stats 表记录 innodb 引擎(数据库核心) 的 索引状态.
# 查看数据库的所有索引:
select *
from mysql.innodb_index_stats where database_name="bigdata_db";

# 查看数据表的所有索引:
select *
from mysql.innodb_index_stats where database_name="bigdata_db" and table_name="products";

1
2
3
4
5
6
7
8
9

3、唯一索引使用

• 创建索引

    create unique index index_cname on category(cname(20));
    alter table category add unique index index_cname(cname(20));
1
2

​

• 删除索引

  drop index index_cname on category;
  alter table category drop index index_cname;
1
2

4、索引使用注意

• 索引不是越多越好. 索引使用应该注意以下问题:

  • 磁盘空间消耗
  • 创建索引和维护索引的时间消耗
  • 经常增删改数据，索引需要动态维护，效率低下。
  • 不经常查询的字段不需要创建索引
  • 大部分值相同的字段不需要创建索引

• 扩展:

  • 开启mysql时间检测: set profiling=1;
  • 查看sql语句执行时间: show profiles;

十五、开窗函数(mysql 8.0后新的)

1、数据准备

create table employee (empid int,ename varchar(20) ,deptid int ,salary decimal(10,2));

insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(2,'张飞',10,3500.00);
insert into employee values(2,'关羽',10,4500.00);

insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);

insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2、开窗函数使用

• 格式:

select 
*,
开窗函数() over(partition by 分组字段 order by 排序字段),
...
from 表名;
1
2
3
4
5

• partition by ：相当于分组group by

• order by ：相当于前面的order by

• 使用:

select *,
row_number() over (partition by deptid order by salary) as row_n
from employee;
  
  # select *,
  #        row_number() over (order by salary) as row_n
  # from employee;
  
  # 1. 查询每一个部门的薪资排名
  select *,
         row_number() over (partition by deptid order by salary) as row_n,
         rank() over (partition by deptid order by salary) as rank_n,
         dense_rank() over (partition by deptid order by salary) as drank_n
  from employee;
  
  # 开窗函数:
  # row_number：显示排序后的行数
  # rank: 显示名次，可以并列排名，下一个排名会跳跃并列个数
  # dense_rank: 显示名次，可以并列排名，下一个排名不会跳跃
  
  # 2. 查询每个部分薪资排名第2的员工;
  select * from
  (select
      *,
      dense_rank() over (partition by deptid order by salary desc) as row_n
  from employee) c where c.row_n=2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

以下问题:

• 磁盘空间消耗

• 创建索引和维护索引的时间消耗

• 经常增删改数据，索引需要动态维护，效率低下。

• 不经常查询的字段不需要创建索引

• 大部分值相同的字段不需要创建索引

• 扩展:

  • 开启mysql时间检测: set profiling=1;
  • 查看sql语句执行时间: show profiles;

十五、开窗函数(mysql 8.0后新的)

1、数据准备

create table employee (empid int,ename varchar(20) ,deptid int ,salary decimal(10,2));

insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(2,'张飞',10,3500.00);
insert into employee values(2,'关羽',10,4500.00);

insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);

insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2、开窗函数使用

• 格式:

select 
*,
开窗函数() over(partition by 分组字段 order by 排序字段),
...
from 表名;
1
2
3
4
5

• partition by ：相当于分组group by

• order by ：相当于前面的order by

• 使用:

select *,
row_number() over (partition by deptid order by salary) as row_n
from employee;
  
  # select *,
  #        row_number() over (order by salary) as row_n
  # from employee;
  
  # 1. 查询每一个部门的薪资排名
  select *,
         row_number() over (partition by deptid order by salary) as row_n,
         rank() over (partition by deptid order by salary) as rank_n,
         dense_rank() over (partition by deptid order by salary) as drank_n
  from employee;
  
  # 开窗函数:
  # row_number：显示排序后的行数
  # rank: 显示名次，可以并列排名，下一个排名会跳跃并列个数
  # dense_rank: 显示名次，可以并列排名，下一个排名不会跳跃
  
  # 2. 查询每个部分薪资排名第2的员工;
  select * from
  (select
      *,
      dense_rank() over (partition by deptid order by salary desc) as row_n
  from employee) c where c.row_n=2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26