MySQL入门

例如：Microsoft Visual C++ 2019 Redistributable Package (x64) is not installed. Latest binary compatible version will be installed if agreed to resolve this requirement.

需要安装环境：

选择自定义 Custom 安装

2.3MySQL配置

端口配置

若端口3306被占用，可以采用以下两种解决方法：

法一：更改MySQL服务的端口（如：改成3307，但是不建议这样做，显得很特殊。3306是普遍公认的）

法二：结束占有3306端口的进程（具体操作如下）：

账号密码设置

服务名称

2.4MySQL服务的启动与停止

MySQL是以服务的形式运行在系统中的。

启动与停止服务的两种方式

计算机管理窗口：

此电脑--右键--管理--服务--MySQL80--启动/停止

Windows命令行:

用Windows命令行启动与停止MySQL服务需要管理员身份。

以管理员身份打开命令行：win+s--输入cmd--选择以管理员身份运行。

注意：默认mysql是开机自动启动的。

2.5MySQL卸载

1.关闭服务

2.卸载软件

打开控制面板
点击“程序和功能”
卸载MySQL

3.删除目录

MySQL的安装目录
MySQL的数据文件目录（默认隐藏）：C:\ProgramData\MySQL（如果不允许删除，强制删除）

4.删除注册表

打开注册表：win+r--输入regedit--回车
删除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL80

三、MySQL的管理工具

当完成数据库的安装之后，mysql是以服务的形式运⾏在windows/linux系统。

⽤户是通过DBMS⼯具来对MySQL进⾏操作的。

安装完成MySQL之后，系统会默认安装一个命令行形式的DBMS工具，即MySQL Commcand line Client。

通常我们会单独安装可视化的DBMS⼯具：

SQLyog
Navicat for MySQL

MySQL Commcand line Client使用

打开此工具：开始菜单--MySQL Commcand line Client
连接MySQL：输入默认账号root的密码（如果密码错误或者MySQL服务未启动，窗口就会闪退）

（退出窗口输入exit即可）

windows命令行使用（需要提前设置path环境变量）

Navicat使用

打开此工具
创建连接：

四、MySQL逻辑结构

MySQL可以存储数据，但是存储在MySQL中的数据需要按照特定的结构进行存储。

数据--数据库就像：学生--学校

4.1逻辑结构

4.2记录/元组

五、SQL结构化查询语言

5.1SQL概述

SQL （Structed Query Language）结构化查询语言，用于存取、查询、更新数据以及管理关系型数据库系统。

在不同的数据库产品中遵守SQL的通用规范，但是对SQL有一些不同的改进，形成了一些数据库的专有指令：

MySQL：limit
SQL Server：top
Oracle：rownum

5.2SQL分类

根据SQL指令完成的数据库操作的不同，可以将SQL指令分为4类：

DDL（Data Definition Language）数据定义语言
- 用于完成对数据库对象（数据库、数据表、视图、索引等）的创建、修改、删除。
DML（Data Manipulation Language）数据操纵/操作语言
- 用于完成对数据表中的数据的添加、修改、删除
DQL（Data Query Language）数据查询语言
- 用于将数据表中的数据查询出来
DCL（Data Control Language）数据控制语言
- 用于完成事务管理等控制性操作

5.3SQL基本语法

在MySQL Command Line Client 或者Navicat等工具中都可以编写SQL指令。

SQL指令不区分大小写
每条SQL表达式结束之后都以;结束
SQL关键字之间以空格进行分隔
SQL之间可以不限制换行（可以有空格的地方就可以有换行）

5.4DDL数据定义语言

5.4.1DDL-数据库操作

使用DDL语句可以创建数据库、删除数据库、查询数据库、修改数据库。

创建数据库：

##创建数据库 dbName表示创建的数据库名称，可以⾃定义
create database <dbName>;

##创建数据库，当指定名称的数据库不存在时执⾏创建
create database if not exists <dbName>;

## 在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采⽤的编码格式
utf8 gbk）
create database <dbName> character set utf8;

删除数据库（删除数据库时会删除当前数据库中所有的数据表以及数据表中的数据）：

## 删除数据库
drop database <dbName>;

## 如果数据库存在则删除数据库
drop database if exists <dbName>;

查询数据库：

## 显示当前mysql中的数据库列表
show databases;

## 显示指定名称的数据的创建的SQL指令
show create database <dbName>;

修改数据库（修改数据库字符集）：

## 修改数据库的字符集
alter database <dbName> character set utf8; # utf8 gbk

使用/切换数据库：

use <dbName>

5.4.2DDL-数据表操作

创建数据表：

数据表实际上就是一个二维表。一个表格是由多列组成，表格中的每一类称之为表格的一个字段。

create table student(
 stu_num char(8) not null unique,
 stu_name varchar(20) not null,
 stu_gender char(2) not null,
 stu_age int not null,
 stu_tel char(11) not null unique,
 stu_qq varchar(11) unique
);

not null:非空约束
unique:唯一性约束
not null+unique=primary key
字段与字段名逗号隔开，最后一个字段不需要加标点符号

查询数据表：

show tables;

查询表结构：

desc <tableName>;

删除数据表：

## 删除数据表
drop table <tableName>;

## 当数据表存在时删除数据表
drop table if exists <tableName>;

修改数据表：

## 修改表名
alter table <tableName> 
rename to <newTableName>;

## 修改表的字符集（数据表也是有字符集的，默认字符集和数据库⼀致）
alter table <tableName> 
character set utf8;

## 添加列（字段）
alter table <tableName> 
add <columnName> <type>;

## 修改列（字段）的列表和类型
alter table <tableName> 
change <oldColumnName> <newCloumnName> <type>;

## 只修改列（字段）类型
alter table <tableName> 
modify <columnName> <newType>;

## 删除列（字段）
alter table stus 
drop <columnName>;

5.5MySQL数据类型

数据类型，指的是数据表中的列中支持存放的数据的类型。

5.5.1数值类型

在MySQL中有多种数据类型可以存放数值，不同的类型存放的数值的范围或者形式是不同的。

类型	大小	范围	说明
tinyint	1byte（8bit）	有符号 -128~127 无符号 0~255	特小型整数（年龄）
smallint	2bytes（16bit）	有符号 -32768~32767 无符号 0~65535	小型整数
mediumint	3bytes（24bit）	有符号 -2^23~2^23-1 无符号 0~2^24-1	中型整数
int/integer	4bytes（32bit）	有符号 -2^31~2^31-1 无符号 0~2^32-1	整数
bigint	8bytes（64bit）	有符号 -2^63~2^63-1 无符号 0~2^64-1	大型整数
float	4bytes	有符号 -2^31~2^31-1 无符号 0~2^32-1	单精度
double	8bytes	有符号 -2^63~2^63-1 无符号 0~2^64-1	双精度
decimal	依赖标度和精度的值	依赖标度和精度的值	decimal(10,2)表示数值共有10位，小数位有2位

5.5.2字符串类型

存储字符序列的类型。

类型	大小	描述
char	0~255 bytes	定长字符串
varchar	0~65535 bytes	变长字符串
tinyblob	0~255 bytes	存储⼆进制字符串
blob	0~65535 bytes	存储⼆进制字符串
mediumblob	0~16777215 bytes	存储⼆进制字符串
logblob	0~4294967295 bytes	存储⼆进制字符串
tinytext	0~255 bytes	⽂本数据（字符串）
text	0~65535 bytes	⽂本数据（字符串）
logtext	0~4294967295 bytes	⽂本数据（字符串）

char(10)与varchar(10)：

若char存储了1个字符，则char会占用10个存储空间（9个空格字符填充）；若varchar存储了1个字符，则 varchar只会占用1个存储空间。
char性能较好，varchar性能较差。

5.5.3日期类型

在MySQL数据库中，我们可以使用字符串来存储时间，但是如果我们需要基于时间字段进行查询操作（查询在某个时间段内的数据）就不便于查询实现。因此引入日期类型。

类型	格式	说明
date	2022-07-04	日期，只存储年月日
time	11:37:15	时间，只存储时分秒
datetime	2022-07-04 11:37:15	日期+时间，存储年月日时分秒
timestamp	20220704 113715	日期+时间（时间戳）
year	2022	年份

5.6字段约束

5.6.1约束介绍

在创建数据表的时候，指定的对数据表的列的数据限制性的要求。

为什么要给表中的列添加约束？

保证数据的有效性（unique）
保证数据的完整性（not null）
保证数据的正确性（default）

字段常见的约束有哪些？

非空约束（not null）：此列必须要有值，不能为null。

唯一约束（unique）：此列的值不能重复。

主键约束（primary key）：非空+唯一，能够唯一标识数据表中的一条数据。

外键约束（foreign key）：建立不同表之间的关系。

5.6.2非空约束

创建表：设置图书表的 book_name not null

create table book(
 book_isbn char(4),
 book_name varchar(10) not null,
 book_author varchar(6) 
);

添加数据：

book_name不允许为空，否则会报错。其它两个允许为空，若不给值则默认为Null。

5.6.3唯一约束

创建表：设置图书表的book_isbn为 unique

create table book(
 book_isbn char(4) unique,
 book_name varchar(10) not null,
 book_author varchar(6) 
);

添加数据：

book_isbn的值是唯一的，不能重复。

5.6.4主键约束

主键能够唯一标识数据表中的一条数据。在一张表中只能有一个主键（主键可以是一个列，也可以是多个列的组合）。

创建表时添加主键约束

create table book(
 book_isbn char(4) primary key,##列级约束
 book_name varchar(10) not null,
 book_author varchar(6) 
);

或者

create table book(
 book_isbn char(4),
 book_name varchar(10) not null,
 book_author varchar(6),
 primary key(book_isbn) ##表级约束
);

当一个字段（或多个字段，这里以一个字段为例）声明为主键之后，添加数据时：

此字段数据不能为空。

此字段数据不能重复。

删除数据表主键约束

alter table book 
drop primary key;##后面不需要加字段名

创建表之后添加主键约束

## 创建表时没有添加主键约束
create table book(
 book_isbn char(4),
 book_name varchar(10) not null,
 book_author varchar(6) 
);

## 创建表之后添加主键约束
alter table book 
modify book_isbn char(4) primary key;

5.6.5主键自动增长

当我们创建数据表时，如果数据表中有列可以作为主键（如：学生表的学号、图书表的isbn），我们可以直接将其设为主键。
当我们创建数据表时，如果数据表中没有列可以作为主键，我们可以额外定义一个与记录本身无关的列（id）作为主键。此列数据无具体含义，主要用来标识一条记录。在MySQL中，我们可以将此列定义为int型，设置为主键，同时设置为自动增长。这样，当我们向数据表中添加一条新的记录时，无需给出id列的值，它会自动生成。

定义主键自动增长

定义int类型字段自动增长：auto_increment

create table type(
 type_id int primary key auto_increment,
 type_name varchar(20) not null,
 type_remark varchar(100) 
);

添加第一条记录：

添加第二条记录：

删除第二条记录：

添加一条新纪录：

我们发现编号是3而不是2，这是因为我们添加记录的次数是3，type_id也更新为3。

5.6.6联合主键

联合主键--将数据表中的多列组合在一起设置为表的主键。

定义联合主键

create table grade(
 stu_num char(8),
 course_id int,
 score int,
 primary key(stu_num,course_id)#联合主键只能设为表级约束
);

错误写法(设置了两个主键是错误的，因为一个表中只能有一个主键)
create table grade(
 stu_num char(8) primary key,
 course_id int primary key,
 score int,
 primary key(stu_num,course_id)
);

注意：在实际企业项⽬的数据库设计中，联合主键使⽤频率并不⾼；当⼀个数据表中没有

明确的字段可以作为主键时，我们可以额外添加⼀个 ID 字段作为主键。

create table grade(
 grade_id int primary key auto_increment,
 stu_num char(8),
 course_id int,
 score int
);

5.6.7外键约束

见6.2

5.7DML数据操纵语言

5.7.1插入数据

语法

insert into <tableName>(columnName1,columnName2....)
values(value1,value2....);

实例（向图书表插入数据）

图书表的表结构：

## 向数据表中指定的列添加数据（允许为空的列可以不提供数据）
insert into book(book_isbn,book_name)
values('1001','Java');

## 数据表名后的字段名列表顺序可以不与表中⼀致，但是values中值的顺序必须与表名后字段
名顺序对应
insert into book(book_name,book_isbn)
values('C','1002');

## 当要向表中的所有列添加数据时，数据表名后⾯的字段列表可以省略，但是values中的值的
顺序要与数据表定义的字段保持⼀致;
insert into book
values('1003','PHP','王五');

## 不过在项⽬开发中，即使要向所有列添加数据,也建议将列名的列表显式写出来(增强SQL的
稳定性)
insert into book(book_isbn,book_name,book_author)
values('1004','Python','钱六');

##也可以向数据表中添加多行数据，每条记录之间逗号隔开
insert into book(book_isbn,book_name)
values('1005','JavaScript'),('1006','Linux');

5.7.2删除数据

语法

delete from <tableName> 
[where conditions];

实例

##删除图书编号为1002的图书信息（如果满足where子句的记录有多条，则删除多条记录）
delete from book
where book_isbn='1002';

#删除图书表中的所有图书信息（如果删除语句中没有where子句，则表示清空数据表<敏感操作>）
delete from book;

5.7.3修改数据

语法

update <tableName> 
set columnName=value 
[where conditions];

实例

##将图书编号为1002的图书的图书名称修改为C++（只修改一列）
update book
set book_name='C++'
where book_isbn='1002';

##将图书编号为1003的图书的图书名称修改为MySQL，作者修改为赵七（修改多列,列与列之间逗号隔开）
update book
set book_name='MySQL',book_author='赵七'
where book_isbn='1003';

##将图书表中所有图书的作者修改为张三（如果修改语句没有update子句，则表中所有记录会受到影响）
update book
set book_author='张三';

5.8DQL数据查询语言

从数据表中提取满足特定条件的记录

单表查询
多表联合查询

5.8.1查询基础语法

## select 关键字后指定要显示查询到的记录的哪些列
select colnumName1[,colnumName2,colnumName3...] from <tableName> [where
conditions];

## 如果要显示查询到的记录的所有列，则可以使⽤ * 替代字段名列表 （在项⽬开发中不建议
使⽤*）
select * from stus;

5.8.2where子句

在删除、查询、修改的语句可以使用where子句，用于在操作之前筛选满足条件的数据。

delete from tableName 
where conditions;

update tabeName 
set ... 
where conditions;

select .... 
from tableName 
where conditions;

条件关系运算符

## = 等于    查询学生表中学号为1001的学生信息
select *
from student
where stu_num = '1001';

## != <> 不等于    查询学生表中学号不为1001的学生信息
select *
from student
where stu_num != '1001';

select *
from student
where stu_num <> '1001';

## > ⼤于    查询学生表中年龄大于20的学生信息
select * 
from student 
where stu_age > 20;

## < ⼩于    查询学生表中年龄小于20的学生信息
select * 
from student 
where stu_age < 20;

## >= ⼤于等于    查询学生表中年龄大于等于20的学生信息
select * 
from student 
where stu_age >= 20;

## <= ⼩于等于    查询学生表中年龄小于等于20的学生信息
select * 
from student 
where stu_age <= 20;

## between and 区间查询 查询学生表中年龄在18到20之间的学生信息
select * 
from student 
where stu_age between 18 and 20;

条件逻辑运算符

在where子句中，可以将多个条件通过逻辑运算符（and or not）进行连接。

## and 并且 筛选多个条件同时满⾜的记录    查询学生表中性别为女且年龄小于20的学生信息
select * 
from student 
where stu_gender = '女' and stu_age < 20;

## or 或者 筛选多个条件中⾄少满⾜⼀个条件的记录    查询学生表中性别为女或者年龄小于20的学生信息
select * 
from student 
where stu_gender = '女' or stu_age < 20;

## not 取反    查询学生表中年龄小于18或者大于20的学生信息
select * 
from student
where stu_age not between 18 and 20;

5.8.3like子句

在where子句的条件中，我们可以使用like关键字来实现模糊查询。

语法

select * 
from tableName 
where columnName like 'reg';

在like关键字后的reg表达式中
- % 表示一个或多个字符【 %o% 包含字⺟o】
- _ 表示任意⼀个字符【 _o% 第⼆个字⺟为o】

实例

# 查询电话包含'123'的学⽣信息
select * 
from student 
where stu_tel like '%123%';
+---------+----------+------------+---------+-------------+-----------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel     | stu_qq    |
+---------+----------+------------+---------+-------------+-----------+
| 1001    | 张三     | 男         |      18 | 13512345678 | 624512347 |
| 1002    | 李四     | 男         |      21 | 14712345678 | NULL      |
| 1006    | 吴八     | 女         |      19 | 15545611234 | NULL      |
+---------+----------+------------+---------+-------------+-----------+

# 查询姓'张'的学⽣信息
select * 
from student 
where stu_name like '张%';
+---------+----------+------------+---------+-------------+-----------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel     | stu_qq    |
+---------+----------+------------+---------+-------------+-----------+
| 1001    | 张三     | 男         |      18 | 13512345678 | 624512347 |
+---------+----------+------------+---------+-------------+-----------+

# 查询学⽣姓名最后⼀个字为'六'的学⽣信息
select * 
from student 
where stu_name like '%六';
+---------+----------+------------+---------+-------------+-----------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel     | stu_qq    |
+---------+----------+------------+---------+-------------+-----------+
| 1004    | 钱六     | 女         |      22 | 14895127536 | 356842611 |
+---------+----------+------------+---------+-------------+-----------+

# 查询学生qq中第⼆个号码为'5'的学⽣信息
select * 
from student 
where stu_qq like '_5%';
+---------+----------+------------+---------+-------------+-----------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel     | stu_qq    |
+---------+----------+------------+---------+-------------+-----------+
| 1004    | 钱六     | 女         |      22 | 14895127536 | 356842611 |
| 1005    | 赵七     | 男         |      21 | 15614238462 | 258654126 |
+---------+----------+------------+---------+-------------+-----------+

5.8.4对查询结果的处理

设置查询的列

声明显示查询结果的指定列。

select colnumName1,columnName2,... 
from student 
where stu_age>20;

计算列

对从数据表中查询的记录的列进⾏⼀定的运算之后显示出来。

##出⽣年份 = 当前年份 - 年龄 查询学生表中所有学生的出生年份
select 2022-stu_age 
from student;
+--------------+
| 2022-stu_age |
+--------------+
|         2004 |
|         2001 |
|         2002 |
|         2000 |
|         2001 |
|         2003 |
+--------------+

as字段取别名

我们可以为查询结果的列名取⼀个语义性更强的别名 ( as 关键字也可以省略)

##出⽣年份 = 当前年份 - 年龄 查询学生表中所有学生的出生年份
select 2022-stu_age as '出生年份'
from student;
+----------+
| 出生年份 |
+----------+
|     2004 |
|     2001 |
|     2002 |
|     2000 |
|     2001 |
|     2003 |
+----------+

distinct消除重复行

##查询学生表中学生的年龄有哪些（需要去除重复行）
错误写法：
select stu_age 
from student;
+---------+
| stu_age |
+---------+
|      18 |
|      21 |
|      20 |
|      22 |
|      21 |
|      19 |
+---------+

正确写法：
select distinct stu_age from student;
+---------+
| stu_age |
+---------+
|      18 |
|      21 |
|      20 |
|      22 |
|      19 |
+---------+

5.8.5排序-order by

将查询到的满⾜条件的记录按照指定的列的值升序/降序排列。

语法

select * from tableName 
[where conditions] 
order by columnName asc|desc;

order by columnName 表示将查询结果按照指定的列排序
- asc 按照指定的列升序（默认）
- desc 按照指定的列降序

实例

#单字段查询 查询年龄大于16的学生信息，查询结果按照性别降序
select * 
from student 
where stu_age > 16 
order by stu_gender desc;
+---------+----------+------------+---------+-------------+-----------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel     | stu_qq    |
+---------+----------+------------+---------+-------------+-----------+
| 1001    | 张三     | 男         |      18 | 13512345678 | 624512347 |
| 1002    | 李四     | 男         |      21 | 14712345678 | NULL      |
| 1005    | 赵七     | 男         |      21 | 15614238462 | 258654126 |
| 1003    | 王五     | 女         |      20 | 15898744561 | 623612345 |
| 1004    | 钱六     | 女         |      22 | 14895127536 | 356842611 |
| 1006    | 吴八     | 女         |      19 | 15545611234 | NULL      |
+---------+----------+------------+---------+-------------+-----------+

##多字段排序（先满⾜第⼀个排序规则，当第⼀个排序的列的值相同时再按照第⼆个列的规则排序）
查询学生表中所有学生的学生信息，查询结果先按照性别降序，后按照年龄升序
select * 
from student 
order by stu_gender desc,stu_age;
+---------+----------+------------+---------+-------------+-----------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel     | stu_qq    |
+---------+----------+------------+---------+-------------+-----------+
| 1001    | 张三     | 男         |      18 | 13512345678 | 624512347 |
| 1002    | 李四     | 男         |      21 | 14712345678 | NULL      |
| 1005    | 赵七     | 男         |      21 | 15614238462 | 258654126 |
| 1006    | 吴八     | 女         |      19 | 15545611234 | NULL      |
| 1003    | 王五     | 女         |      20 | 15898744561 | 623612345 |
| 1004    | 钱六     | 女         |      22 | 14895127536 | 356842611 |
+---------+----------+------------+---------+-------------+-----------+

5.8.6聚合函数

SQL中提供了⼀些可以对查询的记录的列进⾏计算的函数——聚合函数

count
max
min
sum
avg

count() 统计函数，统计满⾜条件的指定字段值的个数（记录数）

#统计学生表中学生总数
select count(*) from student;#'*'表示任意行
+----------+
| count(*) |
+----------+
|        6 |
+----------+


select count(stu_num) from student;
+----------------+
| count(stu_num) |
+----------------+
|              6 |
+----------------+


#统计学生表中男学生总数
select count(*) 
from student 
where stu_gender =  '男';
+----------+
| count(*) |
+----------+
|        3 |
+----------+

max() 计算最⼤值，查询满⾜条件的记录中指定列的最⼤值

##查询学生表中最大的年龄
select max(stu_age)
from student;
+--------------+
| max(stu_age) |
+--------------+
|           22 |
+--------------+

min() 计算最⼤值，查询满⾜条件的记录中指定列的最小值

##查询学生表中最小的年龄
select min(stu_age)
from student;
+--------------+
| max(stu_age) |
+--------------+
|           18 |
+--------------+

sum() 计算和，查询满⾜条件的记录中指定的列的值的总和

# 计算所有学⽣年龄的总和
select sum(stu_age) 
from student;
+--------------+
| sum(stu_age) |
+--------------+
|          121 |
+--------------+

avg() 求平均值，查询满⾜条件的记录中指定的列的值的平均值

# 计算所有男学⽣年龄的平均值
select avg(stu_age) 
from student 
where stu_gender =  '男';
+--------------+
| avg(stu_age) |
+--------------+
|      20.0000 |
+--------------+

5.8.7日期函数和字符串函数

日期函数

当我们向⽇期类型的列添加数据时，可以通过字符串类型赋值（字符串的格式必须为yyyy-MM-dd hh:mm:ss）
如果我们想要获取当前系统时间添加到⽇期类型的列，可以使⽤ now() 或者 sysdate()

实例

desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| stu_num    | char(8)     | NO   | PRI | NULL    |       |
| stu_name   | varchar(20) | NO   |     | NULL    |       |
| stu_gender | char(2)     | NO   |     | NULL    |       |
| stu_age    | int         | NO   |     | NULL    |       |
| stu_tel    | char(11)    | NO   | UNI | NULL    |       |
| stu_qq     | varchar(11) | YES  | UNI | NULL    |       |
+------------+-------------+------+-----+---------+-------+


##添加学生入学时间字段，数据类型为datetime
alter table student
add stu_entrance datetime;

## 通过字符串类型 给⽇期类型的列赋值
insert into student
values('1007','张小三','女',19,'15878944561','456174125','2022-09-01 09:00:00');


## 通过now()获取当前时间
insert into student
values('1008','李小四','女',20,'15145214561','475314925',now());


# 通过sysdate()获取当前时间
insert into student
values('1009','王小五','男',18,'15144568261','145314423',sysdate());



select * from student;
+---------+----------+------------+---------+-------------+-----------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel     | stu_qq    | stu_entrance        |
+---------+----------+------------+---------+-------------+-----------+---------------------+
| 1001    | 张三     | 男         |      18 | 13512345678 | 624512347 | NULL                |
| 1002    | 李四     | 男         |      21 | 14712345678 | NULL      | NULL                |
| 1003    | 王五     | 女         |      20 | 15898744561 | 623612345 | NULL                |
| 1004    | 钱六     | 女         |      22 | 14895127536 | 356842611 | NULL                |
| 1005    | 赵七     | 男         |      21 | 15614238462 | 258654126 | NULL                |
| 1006    | 吴八     | 女         |      19 | 15545611234 | NULL      | NULL                |
| 1007    | 张小三   | 女         |      19 | 15878944561 | 456174125 | 2022-09-01 09:00:00 |
| 1008    | 李小四   | 女         |      20 | 15145214561 | 475314925 | 2022-07-05 17:25:07 |
| 1009    | 王小五   | 男         |      18 | 15144568261 | 145314423 | 2022-07-05 17:26:40 |
+---------+----------+------------+---------+-------------+-----------+---------------------+


## 通过now和sysdate获取当前系统时间
select now();
+---------------------+
| now()               |
+---------------------+
| 2022-07-05 17:27:23 |
+---------------------+


mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2022-07-05 17:27:31 |
+---------------------+


## 通过curtime获取当前系统时分秒
select curtime();
+-----------+
| curtime() |
+-----------+
| 17:27:40  |
+-----------+

字符串函数

就是通过SQL指令对字符串进⾏处理。

实例

# concat(colnum1,colunm2,...) 拼接多列
select concat(stu_name,'-',stu_gender) 
from student;
+---------------------------------+
| concat(stu_name,'-',stu_gender) |
+---------------------------------+
| 张三-男                         |
| 李四-男                         |
| 王五-女                         |
| 钱六-女                         |
| 赵七-男                         |
| 吴八-女                         |
| 张小三-女                       |
| 李小四-女                       |
| 王小五-男                       |
+---------------------------------+


insert into student(stu_num,stu_name,stu_gender,stu_age,stu_tel)
values('1010','Jack','男',21,'15145278952');


# upper(column) 将字段的值转换成⼤写
select upper(stu_name) 
from student 
where stu_num = '1010';
+-----------------+
| upper(stu_name) |
+-----------------+
| JACK            |
+-----------------+
1 row in set (0.00 sec)


# lower(column) 将指定列的值转换成⼩写
select lower(stu_name) 
from student 
where stu_num = '1010';
+-----------------+
| lower(stu_name) |
+-----------------+
| jack            |
+-----------------+


# substring(column,start,len) 从指定列中截取部分显示 start从1开始
select substring(stu_name,2,3) 
from student 
where stu_num = '1010';
+-------------------------+
| substring(stu_name,2,3) |
+-------------------------+
| ack                     |
+-------------------------+

5.8.8分组查询-group by

分组——就是将数据表中的记录按照指定的列进⾏分组。

语法

select 分组字段/聚合函数
from 表名
[where 条件]
group by 分组列名 [having 条件] 
[order by 排序字段];

select 后使⽤ * 显示对查询的结果进⾏分组之后，显示每组的第⼀条记录（这种显示通常是⽆意义的）
select 后通常显示分组字段和聚合函数(对分组后的数据进⾏统计、求和、平均值等)
语句执行属性：

from选择查询操作对应的表
where从表中筛选满足条件的记录
group by对记录进行分组
having对分组后的数据进行筛选
select显示查询结果
order by对显示的查询结果进行排序

实例

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再分别统计每组学⽣的个数
select stu_gender,count(*) as '性别人数' 
from student 
group by stu_gender;
+------------+----------+
| stu_gender | 性别人数 |
+------------+----------+
| 男         |        5 |
| 女         |        5 |
+------------+----------+

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再计算每组的平均年龄
select stu_gender,avg(stu_age) as '平均年龄' 
from student 
group by stu_gender;
+------------+----------+
| stu_gender | 平均年龄 |
+------------+----------+
| 男         |  19.8000 |
| 女         |  20.0000 |
+------------+----------+

# 先对学⽣按年龄进⾏分组（分了18、19、20、21、22六组），然后统计各组的学⽣数量，最后对查询结果进行升序排序
select stu_age,count(*) as '年龄人数' 
from student 
group by stu_age 
order by stu_age;
+---------+----------+
| stu_age | 年龄人数 |
+---------+----------+
|      18 |        2 |
|      19 |        2 |
|      20 |        2 |
|      21 |        3 |
|      22 |        1 |
+---------+----------+

# 查询所有学⽣，按年龄进⾏分组，然后分别统计每组的⼈数，再筛选当前组⼈数>1的组，再按年龄升序显示出来
select stu_age,count(*) as '年龄人数' 
from student 
group by stu_age having count(*)>1 
order by stu_age;
+---------+----------+
| stu_age | 年龄人数 |
+---------+----------+
|      18 |        2 |
|      19 |        2 |
|      20 |        2 |
|      21 |        3 |
+---------+----------+



# 查询性别为'男'的学⽣，按年龄进⾏分组，然后分别统计每组的⼈数，再筛选当前组⼈数>1的组，再按年龄升序显示出来
select stu_age as '男生年龄',count(*) as '年龄人数' 
from student 
where stu_gender = '男' 
group by stu_age having count(*)>1 
order by stu_age;
+----------+----------+
| 男生年龄 | 年龄人数 |
+----------+----------+
|       18 |        2 |
|       21 |        3 |
+----------+----------+

5.8.9分页查询-limit

当数据表中的记录⽐较多的时候，如果⼀次性全部查询出来显示给⽤户，⽤户的可读性/

体验性就不太好，因此我们可以将这些数据分⻚进⾏展示。

语法

select ...
from ... 
where ...
limit param1,param2

param1(int 型)，表示获取查询语句的结果中的第一条数据的索引。（表中数据的索引从0开始）

param2(int 型)，表示获取的查询记录的条数（如果剩下的数据条数小于param2，则返回余下的数据而不是param2条数据）。

实例

对数据表中的学⽣信息进⾏分⻚显示，总共有10条数据，我们每⻚显示3条。

总记录数 count 10

每页显示 pageSize 3

总页数 pageCount = count%pageSize==0 ? count/pageSize : count/pageSize+1;

# 查询第⼀⻚：
select * 
from stus 
[where ...] 
limit 0,3;     (1-1)*3,3

# 查询第⼆⻚：
select * 
from stus 
[where ...] 
limit 3,3;     (2-1)*3,3

# 查询第三⻚：
select * 
from stus 
[where ...] 
limit 6,3;     (3-1)*3,3

# 查询第四⻚：
select * 
from stus 
[where ...] 
limit 9,3;     (4-1)*3,3


# 如果在⼀张数据表中：
# pageNum表示查询的⻚码
# pageSize表示每⻚显示的条数
# 通⽤分⻚语句如下：
select * 
from <tableName> 
[where ...] 
limit (pageNum- 1)*pageSize,pageSize;

六、数据表的关联关系

6.1关联关系介绍

MySQL是一个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系--通过在数据表中添加字段建立外键约束。

数据与数据之间的关联关系分为四种：

一对一关联
一对多关联
多对一关联
多对多关联

6.1.1一对一关联

人--身份证一个人只有一张身份证、一张身份证也只对应一个人

学生--学籍一个学生只有一个学籍、一个学籍也只对应一个人

用户--用户详情一个用户只有一个详情、一个详情也只对应一个用户

方案一:主键关联--两张数据表中主键相同的数据为相互对应的数据

方案二：唯一外键--在任意一张表中添加一个字段（将其添加外键约束），与另一张表主键关联，并且将外键列添加唯一约束。

6.1.2一对多与多对一

班级--学生（一对多）一个班级包含多个学生，一个学生只能属于一个班级

学生--班级（多对一）多个学生可以属于同一个班级，一个学生只能属于一个班级

方案：在多的一端添加外键，与一的一端主键进行关联。

6.1.3多对多关联

学生--课程一个学生可以选多门课、一门课程也可以由多个学生选择

会员--社团一个会员可以参加多个社团、一个社团也可以招纳多个会员

方法：额外创建一张关系表来维护多对多关联--在关系表中定义两个外键，分别与两个数据表的主键进行关联。

6.2外键约束

6.2.1添加外键约束

外键约束--将一个列添加外键约束与另一张表的主键进行关联后，这个外键约束的列添加的数据必须在关联的主键字段中存在。

案例

1.创建班级表

create table class(
    class_id int primary key auto_increment,
    class_name varchar(20) not null unique,
    class_remark varchar(20)
);

2.创建学生表（在学生表中添加外键与班级表的主键进行关联）

# 【⽅式⼀】在创建表的时候，定义class_id字段，并添加外键约束
# 由于student表中的class_id 列要与class表的class_id进⾏关联，因此student表中的class_id字段类型和⻓度要与class表中的class_id⼀致（字段名称可以不相同）
create table student(
    stu_num char(8) primary key,
    stu_name varchar(8) not null,
    stu_gender char(2) not null,
    stu_age int not null,
    class_id int,
    constraint FK_STUDENT_CLASS foreign key(class_id) references class(class_id)
);

#【⽅式⼆】先创建表，再添加外键约束
create table student(
    stu_num char(8) primary key,
    stu_name varchar(8) not null,
    stu_gender char(2) not null,
    stu_age int not null,
    class_id int
);
# 在创建表之后，为cid添加外键约束
alter table student 
add constraint FK_STUDENT_CLASS foreign key(class_id) references class(class_id);

# 删除外键约束
alter table student 
drop foreign key FK_STUDENT_CLASS;

3.向班级表中添加班级信息

insert into class(class_name)
values('Java1001'),('Java1002'),('Python1001'),('Python1002');

select * from class;
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
|        1 | Java1001   | NULL         |
|        2 | Java1002   | NULL         |
|        3 | Python1001 | NULL         |
|        4 | Python1002 | NULL         |
+----------+------------+--------------+

4.向学生表中添加学生信息

insert into student
values('s1001','张三','男',21,1);


#错误添加
#添加学⽣时，设置给学生表中的class_id外键列的值必须在其关联的主表class的classs_id列存在
insert into student
vlaues('s1002','李四','女',19,5);

可视化错误添加：

6.2.2级联操作

当学生表（子表）中存在学生信息引用班级表（母表）的某条记录时，就不能对班级表的这条记录进行修改和删除操作。

#班级表
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
|        1 | Java1001   | NULL         |
|        2 | Java1002   | NULL         |
|        3 | Python1001 | NULL         |
|        4 | Python1002 | NULL         |
+----------+------------+--------------+

#学生表
+---------+----------+------------+---------+----------+
| stu_num | stu_name | stu_gender | stu_age | class_id |
+---------+----------+------------+---------+----------+
| s1001   | 张三     | 男         |      21 |        1 |
| s1002   | 李四     | 男         |      20 |        1 |
| s1003   | 王五     | 女         |      19 |        1 |
| s1004   | 钱六     | 女         |      20 |        2 |
+---------+----------+------------+---------+----------+

#修改班级表中班级名称为Java1001的class_id为5（报错，无法修改）
update class 
set class_id = 5 
where class_name = 'Java1001';

#删除班级表中班级名称为Java1001的班级信息（报错，无法删除）
delete from class 
where class_name = 'Java1001';

如果一定要修改或删除Java1001班的班级信息，该怎么办呢？

解决方案一

修改

#修改学生表中引用Java1001班的class_id为null
update student 
set class_id = null 
where class_id = 1;
#查看学生表
+---------+----------+------------+---------+----------+
| stu_num | stu_name | stu_gender | stu_age | class_id |
+---------+----------+------------+---------+----------+
| s1001   | 张三     | 男         |      21 |     NULL |
| s1002   | 李四     | 男         |      20 |     NULL |
| s1003   | 王五     | 女         |      19 |     NULL |
| s1004   | 钱六     | 女         |      20 |        2 |
+---------+----------+------------+---------+----------+


#修改班级表中Java1001班的class_id为5
update class 
set class_id = 5 
where class_id = 1;
#查看班级表
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
|        2 | Java1002   | NULL         |
|        3 | Python1001 | NULL         |
|        4 | Python1002 | NULL         |
|        5 | Java1001   | NULL         |
+----------+------------+--------------+


#将学生表中class_id为null的记录的class_id重新修改为5
update student 
set class_id = 5 
where class_id is null;

#查看学生表
select * from student;
+---------+----------+------------+---------+----------+
| stu_num | stu_name | stu_gender | stu_age | class_id |
+---------+----------+------------+---------+----------+
| s1001   | 张三     | 男         |      21 |        5 |
| s1002   | 李四     | 男         |      20 |        5 |
| s1003   | 王五     | 女         |      19 |        5 |
| s1004   | 钱六     | 女         |      20 |        2 |
+---------+----------+------------+---------+----------+

删除

#先删除学生表中class_id为1的学生信息
delete 
from student 
where class_id = 1;

#再删除班级表中Java1001的班级信息
delete 
from student 
where class_name = 'Java1001';

总结(当两个表中的数据有关联时，牵扯到对母表或者子表操作时的先后顺序)：

添加数据：先主再外

删除数据：先外再主

修改数据：先外再主最后外

解决方案二

#添加外键时，设置级联修改和级联删除(对母表的数据进行修改/删除，子表中与之关联的数据也会修改/删除)

alter table student 
add constraint FK_STUDENT_CLASS foreign key(class_id) references class(class_id) 
on update cascade on delete cascade;


#测试级联修改
update class 
set class_id = 1 
where class_id = 5;
#查看班级表
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
|        1 | Java1001   | NULL         |
|        2 | Java1002   | NULL         |
|        3 | Python1001 | NULL         |
|        4 | Python1002 | NULL         |
+----------+------------+--------------+
#查看学生表（学生表中前三条记录的class_id由5自动修改为1）
+---------+----------+------------+---------+----------+
| stu_num | stu_name | stu_gender | stu_age | class_id |
+---------+----------+------------+---------+----------+
| s1001   | 张三     | 男         |      21 |        1 |
| s1002   | 李四     | 男         |      20 |        1 |
| s1003   | 王五     | 女         |      19 |        1 |
| s1004   | 钱六     | 女         |      20 |        2 |
+---------+----------+------------+---------+----------+

#测试级联删除
delete 
from class 
where class_name = 'Java1001';
#查看班级表
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
|        2 | Java1002   | NULL         |
|        3 | Python1001 | NULL         |
|        4 | Python1002 | NULL         |
+----------+------------+--------------+
#查看学生表（学生表中前三条记录自动删除）
+---------+----------+------------+---------+----------+
| stu_num | stu_name | stu_gender | stu_age | class_id |
+---------+----------+------------+---------+----------+
| s1004   | 钱六     | 女         |      20 |        2 |
+---------+----------+------------+---------+----------+

七、多表查询

7.1概述

在企业的应⽤开发中，我们经常需要从多张表中查询数据（例如：我们查询学⽣信息的时候需要同时查询学⽣的班级信息），可以通过连接查询从多张数据表提取数据。
在MySQL中可以使⽤join实现多表的联合查询——连接查询，join按照其功能不同分为三个操作：
- inner join 内连接
- left join 左连接
- right join 右连接
在MySQL中可以使用子查询先进⾏⼀次查询，第⼀次查询的结果作为第⼆次查询的条件（第⼆次查询是基于第⼀次的查询结果来进⾏的）。

7.2内连接-inner join

语法

select ... 
from tableName1 
inner join tableName2 
on 匹配条件 
[where 条件];

7.2.1笛卡尔积

笛卡尔积（A集合&B集合）：使⽤A中的每个记录依次关联B中每个记录，笛卡尔积的总数=A总数*B总数。
如果直接执⾏ select ... from tableName1 inner join tableName2; 会获取两种数据表中的数据集合的笛卡尔积（依次使⽤tableName1 表中的每⼀条记录去匹配 tableName2的每条数据）。

7.2.2内连接条件

两张表时⽤inner join连接查询之后⽣产的笛卡尔积数据中很多数据都是⽆意义的，我们如何消除⽆意义的数据呢？ —— 添加两张进⾏连接查询时的条件

#使⽤where设置过滤条件：先⽣成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
select *
from student inner join class
where student.class_id=class.class_id;


#使⽤on设置连接查询条件：先判断连接条件是否成⽴，如果成⽴两张表的数据进⾏组合⽣成⼀
条结果记录
select *
from student inner join class
on student.class_id=class.class_id;

结果：只获取两种表中匹配条件成⽴的数据，任何⼀张表在另⼀种表如果没有找到对应匹配则不会出现在查询结果中（例如：⼩红和⼩明没有对应的班级信息，Java2106和Python2106没有对应的学⽣）。
两种方式的结果相同，但是原理不同：

7.3左连接-left join

需求：请查询出所有的学⽣信息（如果学⽣有对应的班级信息，则将对应的班级信息也查询出来，如果学⽣无对应的班级信息，则对应的班级信息显示为空）。

语法

select * from 
leftTabelName left join rightTableName
on 匹配条件 
[where 条件];

实例

select *
from student left join class
on student.class_id=class.class_id;

结果

7.4右连接right join

需求：请查询出所有的班级信息（如果班级有对应的学生信息，则将对应的学生信息也查询出来，如果班级无对应的学生信息，则对应的学生信息显示为空）。

语法

select * 
from leftTabelName right join rightTableName
on 匹配条件 
[where 条件];

实例

select *
from student right join class
on student.class_id=class.class_id;

结果

7.5数据表别名

如果在连接查询的多张表中存在相同名字的字段，我们可以使⽤表名.字段名来进⾏区分，如果表名太⻓则不便于SQL语句的编写，我们可以使⽤数据表别名。

实例

select s.*,c.class_name
from student s inner join class c
on s.class_id=c.class_id

结果

7.6子查询（嵌套查询）

7.6.1返回单个值-单行单列

实例

查询班级名称为'Java2104'班级中的学生信息

#传统方式
#先查询'Java2104'班的班级编号,再查询此班级编号下的学生信息
select class_id
from class
where class_name = 'Java2104'; 

select *
from student
where class_id = 1;

#子查询
#如果⼦查询返回的结果是⼀个值（单列单⾏），条件可以直接使⽤关系运算符
select *
from student
where class_id = (
	select class_id
	from class
	where class_name = 'Java2104'
);

结果

7.6.2返回多个值-多行单列

实例

查询所有Java班级中的学生信息

#传统方式
#先查询所有Java班级的班级编号，再查询这些班级编号中的学生信息（union整合查询结果）
select class_id
from class
where class_name like 'Java%';
+----------+
| class_id |
+----------+
|        1 |
|        2 |
|        3 |
+----------+
select * from student where class_id = 1
union
select * from student where class_id = 2
union
select * from student where class_id = 3;

#子查询
#如果⼦查询返回的结果是多个值（单列多⾏），条件使⽤IN / NOT IN
select *
from student
where class_id in (
	select class_id
	from class
	where class_name like 'Java%'
);

结果

7.6.3返回多个值-多行多列

实例

查询学生表中class_id=1且性别为男的学生信息

#传统方式
#多条件查询
select *
from student
where class_id = 1 and stu_gender = '男';

#子查询
#先查询class_id=1的所有学⽣信息，将这些信息作为⼀个整体虚拟表(多⾏多列),基于这个虚拟表查询性别为男的学⽣信息（‘虚拟表’需要别名）
select *
from (
    select *
	from student
	where class_id = 1
) temp
where temp.stu_gender = '男';

结果

ahao~

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL入门

目录一、数据库介绍1.1数据库概念1.2关系型数据库与非关系型数据库1.3常见的数据库产品1.4数据库术语二、MySQL数据库环境准备2.1MySQL下载2.2MySQL安装2.3MySQL配置2.4MySQL服务的启动与停止2.5MySQL卸载三、MySQL的管理工具四、MySQL逻辑结构4.1逻辑结构4.2记录/元组五、SQL结构化查询语言5.1SQL概述5.2SQL分类5.3SQL基本语法5.4DDL数据定义语言5.4.1DDL-数据库操作5.4.2DDL-数据表操作5.5MySQL数据类型5.5.1
复制链接

扫一扫