mysql数据库基础一篇内容全掌握

1、sql语句简单易学，具有很强的操作性；
2、绝大多数重要的数据库管理系统均支持SQL语句；
3、高度非过程化；用SQL操作数据库时大部分的工作由DBMS自动完成；

4、综合统一：集数据定义、数据查询、数据操纵和数据控制等多种功能于一体。

5、两种使用方式，统一的语法结构：既是一种独立的语言，又是一种嵌入式的语言，（嵌入式是指嵌入别的高级语言）。

3、SQL语句类型的分类：

1、DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等；常用语句：CREATE（创建）、 ALTER（修改）、DROP（删除）
2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT（插入）、 UPDATE（修改）、 DELETE（删除）
3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别；常用语句：GRANT（授权）、DENY（权限设置）
4、DQL(Data Query Language) 数据查询语言，用来查询数据常用语句：SELECT（查询）

注：工作常遇见的是DDL操作和DML操作，像DCL操作和DQL操作主要是数据库运维人员在做的，也就是DBA运维同事在使用的。

4、数据库数据类型：

使用MySQL数据库存储数据时，不同的数据类型决定了 MySQL存储数据方式的不同。为此，MySQL数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型、二进制…等等数据类型。

整数类型

整数根据数值取值范围的不同，在MySQL 中的分类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的，

整数数据类型	字节数	无符号数的取值范围	有符号数的取值范围
INT	4	0~4294967295	-2147483648~ 2147483648
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775808
TINYINT	1	0~255	-128~127
SMALUNT	2	0~65535	-32768~32768
MEDIUMINT	3	0~16777215	-8388608~8388608

浮点数类型：

在MySQL数据库中使用浮点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。

浮点数类型	字节数	无符号的取值范围	有符号的取值范围
FLOAT	4	0和1.175494351E-38~3.402823466E+38	-3.402823466E+38~-1.175494351E-38
DOUBLE	8	0和2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~2.2250738585072014E-308

定点数类型：

在MySQL数据库中使用定点数来存储小数，而定点数类型只有一种即DECIMAL类型。

定点数类型	字节数	无符号的取值范围	有符号的取值范围
DECIMAL（M,D）	M+2	0和2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~2.2250738585072014E-308

注：数据类型中发现浮点数类型和定点数类型都是存储小数点数据。从浮点数和定点数图中可以看出：DECIMAL类型的取值范围与DOUBLE类型相同。但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的，数据6.5243 插人数据库后显示的结果为6.52的。

字符串数据类型：

在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串，CHAR存储不可变长度的字符串。
当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1的。

字符串类型	特点	字节数
CHAR	字符串固定大小	0~255
VARCHAR	可变字符串大小	0~65535

注：CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉，所以，我们在存储时字符串右边不能有空格，即使有，查询出来后也会被删除。在存储或检索过程中不进行大小写转换。

字符串类型	特点	字节数
TINYTEXT	微型文本	0~255
TEXT	文本串	0~65535
MEDIUMTEXT		0~16777215
LONGTEXT		0~4294967295

注：文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为如下4种： TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。

字符串类型	特点	字节数
BLOB	二进制	0~255
TINYBLOB	二进制	0~65535
MEDIUMBLOB	二进制	0~16777215
LONGBLOB	二进制	0~4294967295

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

日期与时间类型：

MySQL提供的表示日期和时间的数据类型分别是：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值：

数据类型	字节数	取值范围	日期格式	零值
YEAR	1	1901~2155	YYYY	0000
DATE	4	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIME	3	-838：59：59~ 838：59：59	HH:MM:SS	00:00:00

YEAR类型：

YEAR类型用于表示年份，在MySQL中，可以使用以下三种格式指定YEAR类型的值。
1、使用4位字符串或数字表示，范围为’1901’—'2155’或1901—2155。例如，输人 ‘2019’或2019插人到数据库中的值均为2019。
2、使用两位字符串表示，范围为’00’—‘99’。其中，‘00’—'69’范围的值会被转换为 2000—2069范围的YEAR值，‘70’—'99’范围的值会被转换为1970—1999范围的YEAR 值。例如，输人’19’插人到数据库中的值为2019。
3、使用两位数字表示，范围为1—99。其中，1—69范围的值会被转换为2001— 2069范围的YEAR值，70—99范围的值会被转换为1970—1999范围的YEAR值。例如，输人19插入到数据库中的值为2019。
请注意：当使用YEAR类型时，一定要区分’0’和0。因为字符串格式的’0’表示的YEAR值是2000而数字格式的0表示的YEAR值是0000。

TIME类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中，HH表示小时， MM表示分,SS表示秒。在MySQL中，可以使用以下3种格式指定TIME类型的值。
1、以’D HH:MM:SS’字符串格式表示。其中，D表示日可取0—34之间的值, 插人数据时，小时的值等于(DX24+HH)。例如，输入’2 11:30:50’插人数据库中的日期为59:30:50。
2、以’HHMMSS’字符串格式或者HHMMSS数字格式表示。例如，输人’115454’或115454,插入数据库中的日期为11:54:54
3、使用CURRENT_TIME或NOW()输人当前系统时间。

DATETIME类型

DATETIME类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH: MM:SS’，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS 表示秒。在MySQL中，可以使用以下4种格式指定DATETIME类型的值。
以’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式表示的日期和时间，取值范围为’1000-01-01 00:00:00’—‘9999-12-3 23:59:59’。例如，输人’2019-01-22 09:01:23’或 ‘20140122_0_90123’插人数据库中的 DATETIME 值都为 2019-01-22 09:01:23。
1、以’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’字符串格式表示的日期和时间，其中YY表示年，取值范围为’00’—‘99’。与DATE类型中的YY相同，‘00’— '69’范围的值会被转换为2000—2069范围的值，‘70’—'99’范围的值会被转换为1970—1999范围的值。
2、以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。例如，插入20190122090123或者190122090123,插人数据库中的DATETIME值都为 2019-01-22 09:01:23。
3、使用NOW来输人当前系统的日期和时间。

TIMESTAMP类型

TIMESTAMP类型用于表示日期和时间，它的显示形式与DATETIME相同但取值范围比DATETIME小。在此，介绍几种TIMESTAMP类型与DATATIME类型不同的形式：
1、使用CURRENT_TIMESTAMP输人系统当前日期和时间。
2、输人NULL时系统会输人系统当前日期和时间。
3、无任何输人时系统会输入系统当前日期和时间。

5、数据库常见字段属性：

UnSigned：

无符号的
声明了该列不能为负数

ZEROFILL：

0填充的
不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

通常理解为自增，自动在上一条记录的基础上默认+1

通常用来设计唯一的主键，必须是整数类型

可定义起始值和步长

当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

默认为NULL , 即没有插入该列的数值
如果设置为NOT NULL , 则该列必须有值

DEFAULT

默认的
用于设置默认值
例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

六、mysql数据库创建sql语句操作：

1、sql语句的基础操作：

show databases; --查看当前所有的数据库
use 数据库名; --打开指定的数据库
show tables; --查看所有的表
describe/desc 表名; --显示表的信息
create database 数据库名; --创建一个数据库
exit --退出连接

mysql> create database school DEFAULT CHARSET=utf8;
Query OK, 1 row affected (24.02 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.05 sec)
 
mysql> use school;
Database changed

2、创建表：

数据库创建成功后可在该数据库（school）中创建数据表(简称为表)存储数据。

注：在操作数据表之前应使用“USE 数据库名“，这样就是切换到指定数据库（school）下操作，否则会抛出“No database selected”错误。

创建表语法：

 create table 表名(
         字段1 字段类型,
         字段2 字段类型,
         …
         字段n 字段类型
);

创建表的时候会创建以下的表名：

（1）学生表（Student）

（2）课程表（Course）

（3）学生课程成绩表（Student_Course_Transcript）

创建一个student表，用于存储学生的数据：

---创建表：
create table student
(
	id int(9) not null primary key COMMENT '学号',
	Sname varchar(20) not  null unique COMMENT '姓名',  
	Ssex char(4) not  null COMMENT '性别',
	Sage smallint not  null COMMENT '年龄',
	Sdept varchar(5) not  null COMMENT '所在系'	
);

----注：
comment参数表示设置该字段的注释内容
not null参数表示设置该字段的属性不可为空

创建一个课程表，用于存储课程的数据：

----创建表：
create table Course
(
	Cid char(2) not null primary key COMMENT '课程id',
	Cname varchar(10) not null unique COMMENT '课程名',
	Cpno char(2) not null COMMENT '选修课' ,
	Ccredit char(2) not null COMMENT '学分'
);

创建一个学生课程成绩表：

----创建表：
create table student_CT
(
	id char(9) not null COMMENT '学号',
	Cid char(2) not null COMMENT '课程id',
	Grade int not null COMMENT '成绩分数'
);

表相关的操作：

查询数据库下的所有表：

show tables;

查看表的创建过程：

show create table student;

查看表的结构：

desc student;

3、插入数据到数据表：

在MySQL通过INSERT语句向数据表中插入数据，

（1）学生表（Student）

（2）课程表（Course）

（3）学生课程成绩表（Student_Course_Transcript）

插入数据有三种插入方式：

1.在表中所有字段插入数据

每个字段与其值是严格一一对应的。也就是说：每个值、值的顺序、值的类型必须与对应的字段相匹配。但是，各字段也无须与其在表中定义的顺序一致，它们只要与 VALUES中值的顺序一致即可。

语法如下：

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

---插入数据：
insert into student (id,Sname,Ssex,Sage,Sdept) values('202307136','刘杰','男','18','is');

2.为表中指定字段插入数据：

插入数据的方法基本和为表中所有字段插入数据，一样，只是需要插入的字段由你自己指定

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

3. 同时插入多条记录：

在该方式中：(字段名1,字段名2,…)是可选的，它用于指定插人的字段名；(值 1,值 2,…),(值 1,值 2,…)表示要插人的记录，该记录可有多条并且每条记录之间用逗号隔开。

语法：

INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;

1）学生表（Student）


---插入数据：
insert into student values('202307131','李永','男','20','cs'),('202307132','刘晨','女','19','cs'),('202307133','王敏','男','18','ma'),
('202307134','张力','女','17','is'),('202307135','芯子','女','20','it');

2）课程表（Course）


---插入数据：
insert into Course values('1','数据库','5','4'),('2','数学','1','3'),
('3','信息系统','1','6'),('4','操作系统','6','2'),('5','c++','7','5'),
('6','java','3','6'),('7','软件信息','3','5'),('8','网页开发','6','7');

3）学生课程成绩表（student_CT）


----插入数据：
insert into student_CT values('202307131','1','98'),('202307132','2','54'),('202307133','4','78'),('202307134','5','87'),
('202307135','8','73');

4、修改数据表操作：

1.增加列：

alter table student add graduation date;

student 表示指定要修改的表

add关键字，表示增加列

graduation 表示新增列的属性名

date 表示新增列的数据类

2.修改字段属性：

alter table student modify column graduation varchar(20);

MySQL 修改表字段的方法有两种：
ALTER TABLE
MODIFY COLUMN。

1、ALTER TABLE 方法
ALTER TABLE 方法用于修改表结构，包括增加、删除和修改表字段。其语法如下：
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;
其中，

表名表示要修改的表名，

字段名表示要修改的字段名，

字段类型表示修改后的字段类型。

例如，修改表 users 的字段 username 的类型为 VARCHAR(50)，可以使用以下 SQL 语句：
ALTER TABLE users MODIFY COLUMN username VARCHAR(50);

2、MODIFY COLUMN 方法
MODIFY COLUMN 方法用于修改表字段的数据类型、默认值、是否为空等属性。其语法如下：
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 [属性];
其中，

表名表示要修改的表名，

字段名表示要修改的字段名，

字段类型表示修改后的字段类型，

属性表示修改后的字段属性，

例如 DEFAULT、NOT NULL、AUTO_INCREMENT 等。

例如，将表 users 中的字段 age 的数据类型修改为 INT，并设置默认值为 0，可以使用以下 SQL 语句：
ALTER TABLE users MODIFY COLUMN age INT DEFAULT 0;
需要注意的是，如果修改表字段的数据类型或长度，可能会造成数据丢失或者修改失败。因此，在进行字段类型的修改时，需要先备份数据，避免出现意外情况。

3.删除字段：

alter table student drop column graduation;

5、更新表数据操作：

在MySQL通过UPDATE语句更新数据表中的数据

基本语法：

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

在该语法中：字段名1、字段名2…用于指定要更新的字段名称；值1、值 2…用于表示字段的新数据；WHERE 条件表达式是可选的，它用于指定更新数据需要满足的条件。

更新满足条件的数据：

将student中的id值为202307136的sage的值修改为20。

update student set Sage='20' where id='202307136';

更新所有的数据：

update student set Sage='20';

6、删除表数据操作：

在MySQL通过DELETE语句删除数据表中的数据。

基本语法：

DELETE FROM 表名 [WHERE 条件表达式];

删除部分数据：

delete from student where id='202307136';

删除全部数据：

delete from student;

注：TRUNCATE和DETELE的区别
TRUNCATE和DETELE都能实现删除表中的所有数据的功能，但两者也是有区别的：
1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录；

2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1；

3、DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

7、数据表的约束：

mysql数据库在为防止错误的数据被插入到数据表时定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：

约束条件	说明
PRIMARY KEY	主键约束用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值

注：以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。

主键（PRIMARY KEY）约束：

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。常见主键字段有ID、身份证号、学号等等。

主键约束基本语法：

字段名 数据类型 primary key;

给表的字段设置主键的方式有两种，并设置主键一般都是在表创建的时候设置，如果是在已创建的表中设置主键的话，需要确定表中的数据没有或表中这个字段的数据不存在重复，一旦存在重复的数据，则创建主键的时候时候就会提示存在重复的数据，无法创建主键。

创建表时设置主键：

create table student(
id int primary key,
name varchar(20)
);

或者

create table student(
id int
name varchar(20),
primary key(id)
);

对已创建的表设置主键：

---创建表：
create table student(
id int,
name varchar(20)
);
----设置主键
alter table student modify column id int primary key;

非空（not null）约束 :

非空约束即 NOT NULL指的是表的字段值不能为空，这个常见的约束。

基本的语法格式如下所示：

字段名 数据类型 NOT NULL;

创建表时设置：

create table student02( id int name varchar(20) not null );

对已创建的表设置，修改时需要确定该字段的不存在为空的数据

----创建表：
create table student02(
id int,
name varchar(20)
);
---设置name字段为非空属性
alter table student02 modify column name varchar(20) not null;

默认值（default）约束：

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；

基本的语法格式如下所示：

字段名 数据类型 DEFAULT 默认值；

创建时设置字段的默认值：

create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

对已创建的表设置默认值：

---创建表：
create table student03(
id int,
name varchar(20),
gender varchar(10)
);
---设置默认值：
alter table student03 modify column gender varchar(10) default 'male';

唯一性（unique）约束：

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。

基本的语法格式如下所示：

字段名 数据类型 UNIQUE;

创建表时设置唯一约束：

create table student04(
id int,
name varchar(20) unique
);

对已创建的表设置唯一约束：

----创建表：
create table student04(
id int,
name varchar(20)
);
---设置唯一约束：
alter table student04 modify column name varchar(20) unique;

主键约束 PRIMARY KRY与唯一约束 UNIQUE的差异：

主键约束 PRIMARY KRY：
PRIMARY KRY 要求值是唯一的且一张表只能有一个主键
主键后可以使用AUTO_INCREMENT设置主键值自增加一，插入表时可以不给主键列赋值，主键值自动以当前表中的最大主键值+1
但主键后也可以不使用AUTO_INCREMENT，但主键一定是非空的（NOT NULL）

唯一约束 UNIQUE KEY：
UNIQUE KEY 要求值唯一一张表可以有多个唯一约束
唯一约束可以保证数据的唯一性
唯一约束的字段可以为空

总结：
1：）UNIQUE唯一约束允许值为NULL,但只能有一个; PRIMARY KEY主键约束不能有NULL值

2：）一张表可以有多个UNIQUE约束;但是只能有一个PRIMARY KEY主键约束

相同点: 值都要是唯一的,且不可重复

外健（FOREIGN KEY）约束：

外键约束即FOREIGN KEY常用于多张表之间的约束

基本语法如下：

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

创建表时设置外健：

create table student05(
id int primary key,
name varchar(20)
);
---
create table class(
classid int primary key,
studentid int,
constraint fk_class_studentid foreign key(studentid) references student05(id)
);

对已创建的表设置外健：

---创建表：
create table student05(
id int primary key,
name varchar(20)
);
---class表：
create table class(
classid int primary key,
studentid int
);
---设置外健
alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);

注：建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。

外健删除：

语法如下：

alter table 从表名 drop foreign key 外键名；

删除fk_class_studentid外健：

alter table class drop foreign key fk_class_studentid;

注：

关于外键约束需要注意的细节：

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

8、索引的建立与删除：

建立索引的目的是加快数据查询的速度。DBA或者表的属主可以根据需要建立表的索引；但是有些DBMS可以自动建立以下索引，1）PRIMARY KEY索引（聚簇索引）2）UNIQUE索引（唯一性索引）

创建索引：

create unique index s_name on student(Sname asc);
创建唯一性索引
s_name为索引名字，创建索引必须要有一个索引名
列名后面紧跟排序类型，

ASC为升序，DESC为降序，默认为ASC，可以有多个列，用逗号隔开。
注：对于已经包含重复值的属性列不可以增加唯一性索引

创建聚簇索引（聚集）
Create  clusterid index stu on student(sage desc);
聚簇索引的关键字为clusterid
列名后面紧跟排序类型，可以有多个列，用逗号隔开。
聚簇索引严格按照物理存储位置来排序。
不可以在有主键的表中创建索引
一个表只能创建一个聚簇索引

删除索引：

删除索引必须为表名+索引名

drop index student.stu;

七、mysql数据库的查询：

1、单表简单的查询：

查询student表的所有数据：

select * from student;

查询指定字段的数据：

select Sname,Ssex from student;

常数自定义字段查询：

在SELECT中除了写列名，还可以写常数。可以用于标记.

select Sname,Ssex,'2023-07-14' from student;

从查询结果中过滤重复数据:

在SELECT查询语句中使用DISTINCT关键字只能用在第一个所查列名之前

select distinct Ssex from student;

运用运算符查询：

在SELECT查询语句中还可以使用加减乘除运算符。

select Sname,Ssex,Sage+10 from student;

2、运用函数查询：

在工作中，我们常常有类似的需求：统计某个字段的最大值、最小值、平均值等等。为此，MySQL中提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数

1.count（*）函数：

统计表中数据的行数或者统计指定列其值不为NULL的数据个数，这个是在工作中常使用到函数。

select count(*) from student;

2.max（）函数：

计算指定字段值的最大值，如果指定字段是字符串类型则使用字符串排序运算

select max(Sage) from student;

3.min（）函数：

计算指定字段的最小值，如果指定字段是字符串类型则使用字符串排序运算

select min(Sage) from student ;

4.sum（）函数：

计算指定字段的数值和，如果指定字段类型不是数值类型则计算结果为0

select sum(Sage) from student ;

select sum(Sname) from student ;

5.avg（）函数：

计算指定字段的平均值，如果指定字段类型不是数值类型则计算结果为。

select avg(Sage) from student ;

6、时间函数：

在工作是经常使用到时间函数的，如取当前的时间，安装年月日显示时间等需求。

---获取当前的时间：
SELECT NOW();

---获取当前时间的日；
SELECT DAY (NOW());

---获取当前时间的年月日：
SELECT DATE (NOW());

---获取当前时间的时分秒时间
SELECT TIME (NOW());

---获取当前的年份：
SELECT YEAR (NOW());

---获取当前的月份
SELECT MONTH (NOW());

---获取当前的日期，年月日形式：
SELECT CURRENT_DATE();

---获取当前的时间，时分秒时间：
SELECT CURRENT_TIME();

---获取当前的时间，年月日时分秒：
SELECT CURRENT_TIMESTAMP();

---显示当前时间提前一天的时间：
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);

---显示当前时间提前一月的时间：
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);

---显示当前时间推迟一天的时间：
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);

---显示当前时间推迟一个月的时间：
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);

---显示两个时间之差，单位是日：
SELECT DATEDIFF('2022-07-22','2022-05-05');

7、字符串函数：

字符串的函数，很少在工作中使用的，只有一些特殊的场景下使用，这里列举了可能使用的字符串函数。

连接函数 CONCAT ()、concat_ws：

CONCAT ()函数语法：

concat(str1,str2,…)

SELECT CONCAT (a.Sname,a.Sage) from student a;

concat_ws（）函数语法：

concat_ws(separator,str1,str2,...)

concat_ws() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。

SELECT CONCAT_ws ('-',a.Sname,a.Sage) from student a;

查找函数INSTR（）：

函数instr(filed,str)，作用是返回str子字符串在filed字符串的第一次出现的位置，如果没有找到，则返回0。备注：位置是从1开始的。函数不区分大小写。当instr(filed,str)=0时，表示子符串str不存在于字符串filed中，因此可以用来实现mysql中的模糊查询，与like用法类似。

SELECT * from student a where INSTR (a.Sname,'张');

INSTR()函数与LIKE运算符区别：

在没有索引的情况下，INSTR()函数与LIKE运算符的速度是一样的；
在有索引的情况下,使用左匹配的LIKE运算符搜索，LIKE运算符速度会更快一些。

长度统计函数LENGTH()：

在工作需要使用统计字符串的长度的场景。

SELECT LENGTH(Sname) from student a where INSTR (a.Sname,'张');

8、数字函数：

工作经常需要使用到数字函数的场景，比较取整数、绝对值等等。

取绝对值整数ABS():

SELECT ABS(-290) FROM DUAL;

向下取整 FLOOR():

SELECT FLOOR(3.14567) FROM DUAL;

向上取整CEILING():

SELECT CEILING(3.14567) FROM DUAL;

3、运行条件判断查询：

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句、in子句、BETWEEN AND子句、and字据、or子句、like子句、group by子句、ORDER BY子句等指定查询条件对查询结果进行过滤。

查询的基础表：

1、运用关系运算符查询:

在WHERE中可使用关系运算符进行条件查询，常用的关系运算符如下所示：

关系运算符	描述
<>	不等于
!=	不等于
>	大于
<	小于
>=	大于或等于
<=	小于或等于

例：

查询年龄大于18岁的学生清单：

select * from student where Sage > 18;

查询年里大于等于18岁的学生清单：

select * from student where Sage >= 18;

2、运用in或not in条件查询：

IN条件用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

语法：

select 字段1.... from 表名 where in ｜ not in 满足条件范围；

例：

查询院系在cs或ma的学生清单：

select * from student where Sdept in ('cs','ma');

查询院系不是cs的学生清单：

select * from student where Sdept not in ('cs');

3、运用BETWEEN AND或not BETWEEN AND条件查询：

BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来

语法：

select 字段1... from 表名 where 字段1 between 条件起 and 条件止;

例：

查询年龄在16-18之间的学生清单：

select * from student where Sage between 16 and 18;

查询年龄不在16-18之间的学生清单：

select * from student where Sage not between 16 and 18;

4、运用is null或is not null条件查询：

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。

请注意：空值NULL不同于0，也不同于空字符串

语法：

select 字段1.... from 表名 where 字段1 is null ｜ is not null；

例：

修改student表的Sdept字段的属性，允许为null。

alter table student modify column Sdept varchar(5) null;

查看student表结构：

insert into student (id,Sname,Ssex,Sage) values('202307136','康新','男','16');

查看student表的数据：

查看Sdept字段为空的数据：

select * from student where Sdept  is null;

查询Sdept字段不为空的数据：

select * from student where Sdept  is not null;

5、运用where and条件查询：

在MySQL中可使用where AND关键字可以连接两个或者多个查询条件。

语法：

select 字段1.... from 表名 where 字段1 条件1 and 字段2 条件2;

例：

查询student表中年龄大于17岁，并且院系是ma的学生清单：

select * from student where Sage > '17' and Sdept='ma';

查询student表中年龄大于17岁，并且院系不是ma的学生清单：

select * from student where Sage > '17' and Sdept != 'ma';

6、运用or条件查询：

在使用SELECT语句查询数据时可使用OR条件连接多个査询条件。

在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来

语法：

select 字段1...... from 表名 where 字段1 条件1 or 字段2 条件2;

例：

查询student表中年龄大于17岁或院系不是ma的学生数据：

select * from student where Sage > '17' or Sdept != 'ma';

7、运用like条件查询：

MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配，like支持模糊匹配和精确匹配。

语法：

select 字段1.... from 表名 where 字段1 like '%部分条件'｜'%部分条件%'｜'部分条件%';

精确匹配：

查询student表中院系的值为ma的学生的清单：

select * from student where Sdept like 'ma';

模糊匹配：

查询student表中院系的值带有m的学生清单：

%用于匹配任意长度的字符串。

%m：表示最后的字符为m的，前面可以是任意长度的字符。

%m%:表示只要包含字符m的，后面可以是任意长度的字符，也包含0长度的。

select * from student where Sdept like 'm%';

select * from student where Sdept like '%m%';

下划线通配符（_）只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。

例如：

字符串“ab_”匹配以字符串“ab”开始长度为3的字符串，如abc、abp等等；

字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串，如"abcd"、"atud"等等。

select * from student where Sdept like 'm_';

8、运用LIMIT限制条件查询结果的行数：

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条

语法：

select 字段1.. from 表名 order by 字段1... asc ｜desc limit 显示行数;

例：

查询student表按照年龄进行升序排序的前三行数据：

select * from student order by Sage asc limit 3;

9、运用group by分组条件查询：

GROUP BY 子句可按照一定的条件一样将表中的数据进行分组，再进行查询等操作。

换言之，可通俗地理解为：通过GROUP BY将原来的表拆分成了几张小表。

语法：

select 字段1.. from 表名 group by 字段1...;

例：

查询student表中各院系的学生数量：

select count(1),Sdept from student group by Sdept;

查询student表中年龄大于17岁的学生中各院系的数量：

select count(1),Sdept from student where Sage > '17' group by Sdept;

查询student中各院系的学生总年龄大于20：

select sum(Sage),Sdept from student group by Sdept having sum(Sage) >'20';

10、运用order by条件查询：

从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序。

语法：

SELECT 字段名1,字段名2,…  FROM 表名 ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。

例：

查询student表的数据，按照院系进行排序：

select * from student order by Sdept asc;

查询student表的数据，按照年龄进行降序排序：

select * from student order by Sage desc;

11、运用别名查询：

在査询数据时可为表和字段取別名，该别名代替表和字段的原名参与查询操作。

将表设置别名：

在查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称。

语法：

SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;

例：

select * from student a where a.Sage='17';

为字段设置别名：

在查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称

语法：

SELECT 字段名1 [AS] 别名1 , 字段名2 [AS] 别名2 , ... FROM 表名 WHERE ... ;

例：

select a.id,a.Sname 姓名,a.Ssex 性别,a.Sage 年龄,a.Sdept 院系 from student a where a.Sage='17';

4、多表关联查询：

在实际工作中数据表之间存在着各种关联关系。在此，介绍MySQL中数据表的三种关联关系。

多对一：

多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系中，应将外键建在多的一方否则会造成数据的冗余。

多对多：

多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。

一对一：

在工作过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。
接下来，我们来学习在一对多的关联关系中如果添加和删除数据。

准备基础表：

-- 创建班级表
CREATE TABLE class(
    id int(4) NOT NULL PRIMARY KEY COMMENT '班级id',
    name varchar(30) not null comment '班级名称',
		teacher varchar(50) comment '班主任老师'
);

-- 创建学生表
CREATE TABLE student(
    sid int(8) NOT NULL PRIMARY KEY comment '学号id',
    sname varchar(30) comment '学生名',
    classid int(8) NOT NULL comment '班级id',
		sage int(8) not null comment '年龄',
		ssex char(2) comment '性别'
);

ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(id);

-- 向班级表插入数据
INSERT INTO class(id,name,teacher)VALUES(1,'语文','李晓青');
INSERT INTO class(id,name,teacher)VALUES(2,'数学','汪小敏');
INSERT INTO class(id,name,teacher)VALUES(3,'英语','王晓娥');
INSERT INTO class(id,name,teacher)VALUES(4,'体育','李二');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'tom','1','18','男'),(2,'lili','2','18','女'),(3,'lucy','3','18','女'),(13,'wangni','3','18','男');
INSERT INTO student VALUES(4,'lilo','1','18','男'),(5,'tuli','2','18','女'),(6,'wangpo','3','18','男'),(14,'poli','3','18','男');
INSERT INTO student VALUES(7,'tunlo','1','18','女'),(8,'tomli','2','18','男'),(9,'wanger','3','18','女'),(15,'kaili','3','18','女');
INSERT INTO student VALUES(10,'zhangyi','1','18','男'),(11,'lisi','2','18','女'),(12,'liuyi','3','18','女'),(16,'kaiuo','3','18','女');

1、关联查询：

查询数学班级的学生清单：

select a.* from student a where a.classid=(select id from class b where b.name='数学');

删除班级是体育的学生清单：

delete from student  where classid=(select b.id from class b where b.name='体育');

2、交叉连接 cross join查询：

交叉cross join连接查询返回的结果是被连接的两个表中所有数据行的笛卡儿积；

例如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。

所以，交叉连接也被称为笛卡尔连接，

语法：

SELECT * FROM 表1 CROSS JOIN 表2;

例：

select * from student a cross join class b where b.id='2';

3、内连接查询：

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。

语法：

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

说明：在inner join该语法中：INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。

例：

查询数据班级相关的学生清单，并显示数学及班主任：

select a.sid,a.sname,a.sage,a.ssex,b.name,b.teacher from student a join class b on a.classid=b.id where b.id=2;

4、外连接查询：

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。

1、LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
2、RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

语法：

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

注：student表的数据有删除掉了体育班级相关的数据的，所以能满足外连接查询的条件。

例：

左连接查询：

左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。

select class.id,class.name,student.sid,student.sname from class left outer join student on class.id=student.classid;

右连接查询：

右(外)连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。

select class.id,class.name,student.sid,student.sname from student right join class on class.id=student.classid;

注：这里的右表是class表。

5、嵌套子查询：

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

运算符嵌套子查询：

比较运算符前面我们提到过得，就是>、<、=、>=、<=、!=等

select * from student  where classid=(select b.id from class b where b.name='数学');

EXISTS嵌套子查询：

EXISTS关键字后面的参数可以是任意一个子查询，它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行

select * from class where exists (select * from student where sid='4');

any嵌套子查询：

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

select * from class where id > any (select classid from student);

ALL嵌套子查询：

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。

select * from class where id > all (select classid from student);

说明：

查询语句的书写顺序和执行顺序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序
from ===> where ===> group by ===> having ===> select ===> order by ===> limi