MySQL学习笔记整理（上部）

关注

原创 2023-05-01 14:09:16 3.4w 阅读 AI 写同款· GEO 优化 ›

Mysql考试整理 mysql整理 MySQL_Major_test 1、Which one of the following keyword is used if a sub-query is likely to return a list of values and you need to match a column value with any of the values in this list to obtain the final result? 如果子查询可能返回值列表，并且您需要将列值与该列表中的任何值进行匹阅读详情

在这里插入图片描述

前言

以下是我整理了关于MySQL数据库的常用命令和使用场景，一些基本的概念，比如索引、约束和事务，以及常见的问题和解决方案，欢迎大家浏览并留言，若有错误的地方请大家指正。

下部链接

登录命令

-- 打开cmd命令窗口, 输入登录命令
mysql -h localhost -P 3306 -uroot -p123456	
# -h[ip地址]  -P[端口]  -u[用户名]  -p[密码]

mysql -uroot -proot	 # -u[用户名] -p[密码]  用户名+密码登录

登录成功界面：

mysql: [Warning] Using a password ON the command line interface can be insecure.
Welcome to the MySQL mONitor.  Commands END with ; or \g.
Your MySQL cONnectiON id is 3
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle CorporatiON and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

登录命令参数解析：

mysql
关键字、固定写法。（类似于java、jdk中的javac命令或java命令）

不可省略。

-h localhost
参数-h代表主机的ip地址（即host），此处的localhost即为该参数的值，表示主机ip为localhost。

-h与主机ip地址之间的空格，可有可无。

登录本地数据库时，参数-h可以省略不写。当连接远程主机的数据库时，主机ip不可省略。

-P 3306
参数-P代表数据库的端口号（注意是大写的P），此处的3306即为该参数的值，表示该数据库的端口号为3306。

-P与端口号之间的空格，可有可无。

当MySQL数据库的端口号为默认的3306时，该参数可以省略不写。如果端口号做过修改，不是默认的3306，那么端口号不可省略。

-u root
参数-u代表数据库的用户名（user），此处的root即为该参数的值，表示用户名为root。

-u与用户名之间的空格，可有可无。

-p root
参数-p代表对应用户的密码（password），此处的root即为该参数的值，表示密码为root。

-p与密码之间的空格不可省略。

补充：-p后也可以不写密码，此时回车后再输入密码，密码为不可见状态。

参数顺序
mysql固定在最前面，-h、-P、-u、-p的位置可随意调换。
应用举例
PS：设用户名密码均为root/123456。

①连接本地端口号默认3306的数据库：

mysql -hlocalhost -P3306 -uroot -p123456

mysql -uroot -p
#出现密码输入提示时输入密码

mysql -p -uroot
#出现密码输入提示时输入密码

②连接远程主机10.123.45.17，端口号默认3306的数据库：

mysql -h 10.123.45.17 -P3306 -u root -p123456

mysql -h 10.123.45.17 -uroot -p
#出现密码输入提示时输入密码

mysql -uroot -p -h10.123.45.17
#出现密码输入提示时输入密码

③连接远程主机172.16.0.25，端口号8808的数据库：

mysql -h 172.16.0.25 -P 8808 -u root -p123456

mysql -h 172.16.0.25 -P8808 -uroot -p
#出现密码输入提示时输入密码

mysql -uroot -p -h 172.16.0.25 -P8808
#出现密码输入提示时输入密码

DDL数据定义语言

一、操作库

-- 创建库
create database db1;
-- 创建库是否存在，不存在则创建
create database if not exists db1;
-- 查看所有数据库
show databases;
-- 查看某个数据库的定义信息 
show create database db1; 
-- 修改数据库字符信息
alter database db1 character set utf8; 
-- 删除数据库
drop database db1; 

-- 查看db1库下所有表名
use db1; 
show tables;

二、操作表

创建表

-- 语法
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

注意：表中的最后一个字段不要加逗号

字符编码问题

mysql> create database db1 charset latin1;
mysql> use db1;
mysql> create table t1(name varchar(20));
mysql> show create table t1; #查看表，发现表默认与数据db1的字符编码一致
mysql> insert into t1 values('张'); #插入中文出错，因为latin1不支持中文
ERROR 1366 (HY000): 
mysql> 

#解决方法一：删除库db1，重建db1，字符编码指定为utf8
mysql> create database db1 charset utf8;

#解决方法二：修改编码
mysql> alter table t1 charset utf8; #修改表t1的编码
mysql> insert into t1 values('张'); #虽然t1的编码改了，但是t1的字段name仍然是按照latin1编码创建的
ERROR 1366 (HY000): 
mysql> alter table t1 modify name varchar(20); #需要重新定义下字段name
mysql> insert into t1 values('张');
mysql> SELECT * FROM t1;
+------+
| name |
+------+
|  张  |
+------+

ps：不要忘记将数据库编码也改成utf8，这样以后在该数据库下创建表时，都默认utf8编码了

MySQL字符编码的设置以及MySQL中文乱码的详细解决方法

查看字符编码

首先，将中文插入到数据库乱码是因为没有将数据库编码设置为支持中文的编码，mysql的默认编码是Latin1，不支持中文，应该设置为utf8查看自己的数据库编码是否已设置好，进入数据库，输入命令查看编码是否为utf8

show variables like “char%”;

mysql> show variables like "char%";
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_cONnectiON | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

说明你的数据库编码正确，无需修改，应该是页面或者其他编码的问题。如果跟上面的结果不同，说明需要修改数据库的编码。解决方案如下：

在windows系统下

方案一

set names utf8;

mysql> set names utf8;

方案二

1、在mysql的安装目录下找到my.ini文件(如果没有的话就把my-medium.ini复制，然后重命名为my.ini即可)

2、在my.ini文件中找到[client]和[mysqld]字段，在下面均加上default-character-set=utf8，保存并关闭

3、重启mysql服务

在linux系统下

1、打开配置文件，我使用的linux版本是ubuntu，配置文件在/etc/mysql/my.cnf

2、在[client]和[mysqld]字段下面均添加default-character-set=utf8，保存并关闭

3、重启mysql服务

注意：
如果重启成功，查看数据库编码，结果改为utf8则说明修改成功了

如果在linux下重启mysql服务的时候出现Job failed to start，在window下重启失败，这是因为安装了高版本的mysql(mysql5.5以上)，在高版本对字符编码方式修改的办法中，在[mysqld]下的修改发生了变化，正确方式如下：

[mysqld]下添加的应该为：

-- mysql5.5以上：修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

重启mysql，再次查看字符编码，如果跟上面一致，说明成功了

查看表结构

-- 语法
describe [表名];
-- 查看表结构，可简写为desc 表名
desc [表名];

-- 查看表详细结构，可加\G
show create table [表名]\G;

数据类型

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

表完整性约束

完整性约束（或简称“约束”）是数据库的内容必须随时遵守的规则，它们描述了对数据库的哪一次更新是被允许的。一旦定义了完整性约束，MySQL就会负责在每次更新后，测试新的数据内容是否符合相关的约束。

完整性约束的分类

主键约束
主键就是表中的一列或多个列的一组，它们能够唯一地标识表中的每一行。

通过定义PRIMARY KEY约束来创建主键，而且PRIMARY KEY约束中的列不能取空值。

可以用两种方式定义主键，作为列或者表的完整性约束。作为列的完整性约束时，只需要在列定义的时候加上关键字PRIMARY KEY 。作为表的完整性约束时，需要在定义的最后列后加上一条PRIMARY KEY(列名,…)语句。

原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则：

1、每个表只能定义一个主键。

2、关系模型理论要求必须为每一个表定义一个主键。mysql中并没有这样的要求，可以创建一个没有主键的表。为了安全起见，建议为每个基础表指定一个主键。

3、表中的两个不同的行在主键上不能具有相同的值。即“唯一性原则”。

4、如果从一个复合主键中删除一列后，剩下的列构成的主键仍然满足唯一性原则，那么，这个复合主键是不正确，即“最小化规则”。

5、一个列名在一个主键的列的列表中只能出现一次。

替代键约束
在关系模型中，替代键像主键一样是表的一列或者一组列，它们的值在任何时候都是唯一的。

替代键是没有被选作主键的候选键。

定义替代键的关键字是UNIQUE。

替代键与主键的主要区别：

1、一个表只能有一个主键，但可以有多个候选键，甚至可以重合。

2、主键字段的值不能为空，候选键可以。

3、都可以自动产生索引。

参照完整性约束
参照完整性约束是一种特殊的完整性约束，表现为一个外键。

当指定一个外键的时候，应遵循以下规则：

1、必须为被参照表定义主键

2、必须在被参照表的表名后面指定列名。这个列必须是这个表的之间或替代键。

3、外键中列的数目必须和被参照表的主键中列数目相同。

4、外键列的数据类型必须和被参照表主键中列的数据类型相同。

CHECK完整性约束
当我们需要限定一些数据的范围时，可以使用check完整性约束。

CHECK完整性约束在创建表的时候定义。可以定义为列完整性约束，也可以定义为表完整性约。

命名完整性约束
如果一条INSERT、UPDATE或DELETE语句违反了完整性约束，则MySql返回一条出错的消息并且拒绝更新，一个更新可能会导致多个完整性约束的违反。在这种情况下，应用程序获取几套出错消息。为了确切地表示出是违反了哪一个完整性约束，可以为每个完整性约束分配一个名字，随后，出错消息包含这个名字，从而使得消息对于应用程序更有意义。

案例演示

主键定义：创建表xs1，将姓名定义为主键：

create table wfx.xs1
(
    学号 varchar(6) null,
    姓名 varchar(8) not null primary key,
    出生日期 datetime
);

复合主键的定义：创建course表，学号、课程号、毕业日期定义为主键：

create table course
(
    学号 varchar(6) not null,
    姓名 varchar(8) not null,
    毕业日期 date   not null,
    课程号varchar(3),
    学分tinyint,
    primary key(学号，课程号，毕业日期)
)

通常MySQL自动地为主键创建一个索引primary。我们可以重新给这个索引起名：

primary key index_primary(学号，课程号，毕业日期)

定义一个替代键：

create table xs1
(
    学号 varchar(6) null,
    姓名 varchar(8) not null unique,
    出生日期 datetime
);

定义为表的完整性约束：

create table xs1
(
    学号 varchar(6) null,
    姓名 varchar(8) not null,
    出生日期 datetime
    unique(姓名)
);

创建表xs2，xs1表中所有学生学号都必须出现在xs2表中：

create table xs2(
    学号 varchar(6) null,
    姓名 varchar(8) nut null,
    出生日期 datetime null,
    primary key(姓名)
    foreign key(学号)
    references xs1(学号)
    on delete restrict
    on update restrict
);

与外键相关的被参照表和参照表可以是同一个表，这样的表成为自参照表：

create table wfx.xs2(
    学号 varchar(6) not null,
    姓名 varchar(8) not null,
    出生日期 datetime null,
    primary key(学号),
    foreign key(学号)
    references xs2(学号)
)

创建表student，性别只能是男或女：

create table wfx.student(
    学号 varchar(6) primary key,
    gender char(6) not null
    check(gender in('男','女'))
);

创建表xs3，将姓名定义为主键：

create table xs3
(
    学号 varchar(6) null,
    姓名 varchar(8) not null ,
    出生日期 datetime
    constraint primary_key_xs3 primary key(姓名)
);

修改表

-- 修改表名语法
ALTER TABLE 表名 RENAME [TO] 新表名;

-- 增加字段语法
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…];
                            
-- 删除字段语法
ALTER TABLE 表名 DROP 字段名;

-- 修改字段语法
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

-- 示例
-- 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

-- 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name; #添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first; #添加到最前面

-- 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

-- 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment; #修改为主键

-- 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

-- 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

-- 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

-- 删除主键
# 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

# 删除主键
mysql> alter table student10                                 
    -> drop primary key;

复制表

-- 复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service SELECT * FROM service;

-- 只复制表结构
mysql> SELECT * FROM service WHERE 1=2; #条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service SELECT * FROM service WHERE 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;

删除表

-- 删除列
ALTER TABLE 表名 DROP 列名;
-- 删除表
DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;

DML数据操作语言

一、新增数据

-- 写全所有列名
INSERT INTO 表名(列名1,列名2,...列名n) VALUES(值1,值2,...值n);
-- 不写列名（所有列全部添加）
INSERT INTO 表名 VALUES(值1,值2,...值n);
-- 插入部分数据
INSERT INTO 表名(列名1,列名2) VALUES(值1,值2);

二、删除数据

-- 删除表中数据
DELETE FROM 表名 WHERE 列名 = 值;
-- 删除表中所有数据
DELETE FROM 表名;
-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate table 表名;

三、修改数据

-- 不带条件的修改(会修改所有行)
UPDATE 表名 SET 列名 = 值;
-- 带条件的修改
UPDATE 表名 SET 列名 = 值 WHERE 列名=值;

DQL数据查询语言

关于查询语句有很多，这里基础的不再介绍。主要介绍排序查询、聚合函数、模糊查询、分组查询、分页查询、内连接、外连接、子查询

一、基础关键字

BETWEEN…AND（在什么之间）和 IN（集合）

-- 查询年龄大于等于20 小于等于30				
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
				
-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);

IS NOT NULL（不为null值）、 LIKE（模糊查询）和 DISTINCT（去除重复值）

-- 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;
	
 _ :单个任意字符
 % :多个任意字符
-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE name LIKE '马%';
-- 查询姓名第二个字是化的人			
SELECT * FROM student WHERE name LIKE "_化%";				
-- 查询姓名是3个字的人
SELECT * FROM student WHERE name LIKE '___';					
-- 查询姓名中包含德的人
SELECT * FROM student WHERE name LIKE '%德%';

-- 关键词 DISTINCT 用于返回唯一不同的值。
-- 语法：SELECT DISTINCT 列名称 FROM 表名称
SELECT DISTINCT name FROM student;

CASE WHEN 条件表达式函数：类似IF ELSE语句

建表插入数据

CREATE TABLE course ( id INT ( 20 ), sid INT ( 20 ), course VARCHAR ( 255 ), score INT ( 20 ) );

INSERT INTO `course` VALUES (1, 1, '语文', 43);
INSERT INTO `course` VALUES (2, 1, '数学', 55);
INSERT INTO `course` VALUES (3, 2, '语文', 77);
INSERT INTO `course` VALUES (4, 2, '数学', 88);
INSERT INTO `course` VALUES (5, 3, '语文', 98);
INSERT INTO `course` VALUES (6, 3, '数学', 65);

1, 基本用法

-- 语法
CASE 
	WHEN condition THEN result
	[WHEN...THEN...]
	ELSE result
END
-- condition是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值,
-- 如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。

CASE    
    WHEN 条件1 THEN 条件成立的内容
    WHEN 条件2 THEN 条件成立执行的内容
    ...
    ELSE 其他条件
END

2, 在update更新中使用

UPDATE TABLE 
  SET 字段 1 =
   		CASE
   		    WHEN 条件 1 THEN 值 1 
   		    WHEN 条件 2 THEN 值 2 
   		    ELSE 值 3 
   		END 
   AND

3, 在SELECT查询中使用

SELECT 字段1, 字段2,       
        CASE 字段3     
            WHEN 值1 THEN 新值       
            WHEN 值2 THEN 新值  
            ELSE 新值
        END AS 重新命名字段3的名字       
FROM table

4, 在AND条件中使用

SELECT 字段1,字段2,字段3      
FROM table
WHERE
    CASE
        WHEN 条件1 THEN AND后的条件
        WHEN 条件2 THEN AND后的条件
        ELSE AND后想加的条件
    END

举例说明：语文成绩找到及格的，数学成绩找出来不及格的,相当于找到偏科偏语文的学生的学生

SELECT *       
FROM course
WHERE 
    CASE 
        WHEN course = '语文' THEN score>60
        WHEN course = '数学' THEN course<60
    END

5, 在GROUP BY分组中使用

SELECT 字段1,字段2,字段3      
FROM table
WHERE 条件
GROUP BY 
    CASE
        WHEN 条件1 THEN 字段x
        WHEN 条件2 THEN 字段y
    END

说明：满足条件1以字段x分组，满足条件2以字段y分组。

6, 在ORDER BY排序中使用

SELECT 字段1,字段2,字段3      
FROM table
WHERE 条件
ORDER BY 
    CASE
        WHEN 条件1 THEN 字段x
        WHEN 条件2 THEN 字段y
    END

说明：满足条件1以字段x排序，满足条件2以字段y排序。</font

演示: 所有数学课程成绩大于语文课程成绩的学生的学号
第1步, 使用CASE WHEN来进行行列转换

SELECT sid 学号,
	CASE 
		WHEN course='语文' THEN score ELSE 0
		END AS 语文分数,
	CASE 
		WHEN course='数学' THEN score ELSE 0
		END AS 数学分数
FROM course

第2步, 根据学号进行合并，将同一个学生的数学和语文成绩放在一行显示
" CASE WHEN course=‘语文’ THEN score ELSE 0 END "是完整的CASE WHEN语句

SELECT
	sid 学号,
	MAX(CASE WHEN course = '语文' THEN score ELSE 0 END) 语文分数,
	MAX(CASE WHEN course = '数学' THEN score ELSE 0 END) 数学分数 
FROM
	course 
GROUP BY
	sid

第3步, 从行列转换的表中取出题目中要求的数据

SELECT a.* FROM 
(
SELECT
	sid 学号,
	MAX(CASE WHEN course = '语文' THEN score ELSE 0 END) 语文分数,
	MAX(CASE WHEN course = '数学' THEN score ELSE 0 END) 数学分数 
FROM
	course 
GROUP BY
	sid
) AS a
AND a.语文分数<a.数学分数

IF NULL 判断如果为空

1, IFNULL(expr1,expr2)用法

假如expr1不为NULL，则 IFNULL() 的返回值为expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语句

 SELECT IFNULL(1,0);         -- 1                       
 SELECT IFNULL(NULL,10);     -- 10                            
 SELECT IFNULL(1/0,10);      -- 10
 SELECT IFNULL(1/0,'yes');   -- 'yes'  
 SELECT IFNULL('','yes');    -- ''

2, ISNULL(expr) 的用法

如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0

SELECT ISNULL(1+1);    -- 0
SELECT ISNULL(1/0);    -- 1

3, NULLIF(expr1,expr2)用法

如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同

SELECT NULLIF(1,1);    -- NULL
SELECT NULLIF(1,2);    -- 1

EXPLAIN 查看当前查询使用那个索引(多索引优化，判断索引失效)

EXPLAIN SELECT surname,first_name form a,b WHERE a.id = b.id

如果使用的索引比较慢,或者自动匹配到慢的索引,可以强制选择特定的索引

force index(IX_SkuID)

二、排序查询

语法：ORDER BY 子句

ORDER BY 排序字段1 排序方式1 , 排序字段2 排序方式2...

注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

-- 例子
SELECT * FROM persON ORDER BY math; 	  --默认升序
SELECT * FROM persON ORDER BY math asc;   --升序
SELECT * FROM persON ORDER BY math desc;  --降序

三、聚合函数

将一列数据作为一个整体，进行纵向的计算

1, count：计算个数

2, max：计算最大值

3, min：计算最小值

4, sum：计算和

5, avg：计算平均数

6, having：先通过SQL语句把所有数据查询出来，再用 group by 进行分组，然后把分完组的数据用聚合函数进行统计，只不过查询语句和聚合函数之间需要用having连接；（group by 、having、聚合函数通常一起使用）

语法：查询语句 + group by + having + 聚合函数统计

直接上例子：

SELECT emp_no, COUNT(salary) t     #在工资表里查找员工编号、统计发工资的次数（取别名t）
FROM salaries 
WHERE creat_date BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY emp_no         #通过员工编号编组
HAVING t > 15       	#查询发工资次数大于15次的

常用聚合函数是sum()、avg()、count()；
聚合函数是用来统计每个分组的统计信息，它们要跟 group by 一起使用，用来将每个分组所有数据聚合成一条统计数据。聚合函数： max() min() count() avg() sum() 等。

语句执行的顺序
SELECT 语句执行顺序大致是：
where（数据查询） => group by（数据编组） => having（结果过滤） => order by（排序）

四、分组查询

语法：GROUP BY 分组字段;

注意：分组之后查询的字段：分组字段、聚合函数

-- 按照性别分组。分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM student GROUP BY sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数
 SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

五、分页查询

1, 语法：LIMIT 开始的索引,每页查询的条数;

2, 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

3, LIMIT 是一个 MySQL “方言”

-- 每页显示3条记录 
SELECT * FROM student LIMIT 0,3; 	-- 第1页
SELECT * FROM student LIMIT 3,3; 	-- 第2页
SELECT * FROM student LIMIT 6,3; 	-- 第3页

六、自查询/自连接

要通过自关联进行查询时，当前自关联的表当中一定会存在两个相关联的字段

自关联要用别名

语法格式如下

SELECT * FROM 表名 AS 别名1 INNER JOIN 表名 AS 别名2 ON 别名1.列=别名2.列;

-- 查询出河南省所有的市
SELECT * FROM areas AS a1 INNER JOIN areas AS a2 ON a1.aid=a2.pid WHERE a1.atitle='河南省';

-- 查询出郑州市所有的区
SELECT * FROM areas AS a1 INNER JOIN areas AS a2 ON a1.aid=a2.pid WHERE a1.atitle='郑州市';

-- 查询出河南省所有的市区信息
SELECT * FROM areas AS a1 INNER JOIN areas AS a2 ON a1.aid=a2.pid 
LEFT JOIN 
areas AS a3 ON a2.aid=a3.pid WHERE a1.atitle='河南省';

在这里插入图片描述

多表查询(重点)

多表的对应关系

一对一关系

任意一方作为从表,在从表创建外键列指向主表的主键列,从表外键列设置唯一

例：人和身份证号：一个人只能有一个身份证号

一对多关系

在从表创建外键列指向主表的主键列

例：用户和订单：一个用户可以有多个订单

多对一关系

例：订单和用户：多个订单可以属于同一个用户

Tips：但是对于特定的一个订单只能属于一个用户，所以多对一也可以理解为一对一

多对多关系
2张表都要作为主表,需要创建第三张表作为从表,第三张表需要指定2列分别作为2个主表的外键列指向2个主表的主键列,建议让从表的那两列作为联合主键

例：学生选课, 一个学生可以选择多门课程, 一门课程也可以被多个学生选择

七、内连接查询

1, 从哪些表中查询数据

2, 条件是什么

3, 查询哪些字段

1.隐式内连接：使用AND条件消除无用数据

-- 语法： 
SELECT * FROM 表1 t1,表2 t2 WHERE 关联条件 AND 普通条件;

-- 查询员工表的名称，性别。部门表的名称
SELECT emp.name,emp.gENDer,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

SELECT 
    t1.name, -- 员工表的姓名
    t1.gENDer,-- 员工表的性别
    t2.name -- 部门表的名称
FROM
    emp t1,
    dept t2
WHERE 
    t1.`dept_id` = t2.`id`;

2.显式内连接

-- 语法： 
SELECT * FROM 表1 t1
			[INNER] JOIN 表2 t2 ON 关联条件
			WHERE 普通条件;

-- 例如：
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;   
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;

八、外连接查询

1.左外连接 – 查询的是左表所有数据以及其交集部分。

-- 语法：
SELECT * FROM 表1 t1
			LEFT [OUTER] JOIN 表2 t2 ON 关联条件
			WHERE 普通条件;
-- 查询左表全部，再去匹配右表，有就显示，没有就显示null

-- 例子：
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT  t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

2.右外连接 – 查询的是右表所有数据以及其交集部分。

-- 语法：
SELECT * FROM 表1 t1
			RIGHT [OUTER] JOIN 表2 t2 ON 关联条件
			AND 普通条件;
-- 查询右表全部，再去匹配左表，有就显示，没有就显示null

-- 例子：
SELECT  * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

九、子查询：查询中嵌套查询

子查询结果只要为单列，肯定在AND后作为条件使用
子查询结果只要为多列，肯定在FROM后作为虚拟表使用

-- 查询工资最高的员工信息
-- 1.查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;

-- 2.查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.`salary` = 9000;

 -- 用一条sql就完成这个操作。这就是子查询
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

1, 子查询的结果是单行单列的
子查询可以作为条件，使用运算符进行条件判断。运算符： > >= < <= =

-- 语法：
SELECT * FROM 表 WHERE id = (子查询);

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

2, 子查询的结果是多行单列的
子查询可以作为条件，使用in关键字来判断

-- 语法：
SELECT * FROM 表 WHERE id in (子查询);

-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE name = '财务部' OR name = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept AND name = '财务部' OR name = '市场部');

3, 子查询的结果是多行多列的
子查询可以作为一张虚拟表参与查询

-- 语法：
SELECT * FROM 表1 t1
			JOIN (子查询) t2 ON 关联条件
			WHERE 普通条件;

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t2 JOIN 
(SELECT * FROM emp WHERE JOIN_date > '2011-11-11') AS t1 ON t1.dept_id = t2.id;

-- 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`JOIN_date` >  '2011-11-11'

DCL数据控制语言

管理用户

添加用户

-- 语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

删除用户

-- 语法
DROP USER '用户名'@'主机名';

权限管理

查询权限

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';

授予权限

-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

撤销权限

-- 撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

开启MySQL远程访问权限(允许远程连接)

登录并查看权限信息

-- 登录数据库
mysql -u root -p

-- 切换user表
mysql> use mysql;
database changed

-- 查看ip和权限信息
mysql> SELECT host,user FROM user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.sessiON |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

可以看到在user表中已创建的root用户。host字段表示登录的主机，其值可以用IP，也可用主机名，

有时想用本地IP登录，那么可以将以上的Host值改为自己的IP即可。

实现远程连接(授权法)

将host字段的值改为%就表示在任何客户端机器上能以root用户登录到mysql服务器，建议在开发时设置为%

update user set host = ’%’ AND user = ’root’;

将权限改为ALL PRIVILEGES

-- 切换user表
mysql> use mysql;
database changed

-- 设置权限
mysql> grant all privileges ON *.* to root@'%' identified by "password";
Query OK, 0 rows affected (0.00 sec)

-- 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

-- 查看ip和权限信息
mysql> SELECT host,user FROM user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.sessiON |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

这样机器就可以以用户名root密码root远程访问该机器上的MySQL

实现远程连接(改表法)

-- 切换user表
mysql> use mysql;

-- 修改表内的权限信息
mysql> update user set host = ‘%’ AND user = ‘root’;
Query OK, 0 rows affected (0.00 sec)

这样在远端就可以通过root用户访问MySQL

MySQL函数

字符函数

①length(str)函数

获取参数值的字节个数

对于utf-8字符集来说，一个英文占1个字节；一个中文占3个字节；

对于gbk字符集来说，一个英文占1个字节；一个中文占2个字节；

SELECT length('演示') AS 长度;

②concat(str1,str2,…)函数

将字符串拼接，通过输入的参数str1、str2等，将他们拼接成一个字符串

SELECT concat('演示','-','Sheenky') AS 演示信息;

③upper(str)、lower(str)函数

upper(str):将字符中的所有字母变为大写

lower(str)将字符中的所有字母变成小写

SELECT upper('sheenky') AS 大写;
SELECT lower('SHEENKY') AS 小写;

④substr(str,start,len)函数

str为输入字符串，从start位置开始截取字符串，len表示要截取的长度；没有指定len长度：表示从start开始起，截取到字符串末尾。指定了len长度：表示从start开始起，截取len个长度

SELECT substr('演示sheenky',3,7) AS out_put;

⑤substring_index(string,sep,num)字符串截取函数

语法：substring_index(string,sep,num)，即substring_index(字符串,分隔符,序号)

参数说明

string：用于截取目标字符串的字符串。可为字段，表达式等。

sep：分隔符，string存在且用于分割的字符，比如“，”、“.”等。

num：序号，为非0整数。若为整数则表示从左到右数，若为负数则从右到左数。比如“www.mysql.com”截取字符‘www’，分割符为“.”，从左到右序号为1，即substring_index(“www.mysql.com”,‘.’,1)；若从右开始获取“com”则为序号为-1即substring_index(“www.mysql.com”,‘.’,-1)

演示
（1）从某字段截取目标字符串。
例：现有一个学生信息表student，详细地址address储存省、市、县等由逗号隔开的地址信息，比如“XX省，XX市，XX区，…，XXX号”。由于某种原因没有学生所在省信息需要获取，同时获取学生姓名name，性别sex，年龄age。

select name,sex,age,
substring_index(address,',',1) as province
from student;

（2）与cast函数结合使用截取某字符串并转为目标格式。
例：现有一张订单信息data，由于2022-03-03日期之后存储日期信息的格式出错，日期前加了一些前缀，并用空格隔开，比如“13d 2022-02-01”，需要获取该表2022-03-03之后具体日期信息，同时获取单子offer_id，以及产品名name。

select cast(substring_index(ctime,' ',1) as date) as dt,
offer_id,name    
FROM data
WHERE substring_index(ctime,' ',1) >= '2022-03-03';

⑥instr(str,要查找的子串)函数

返回子串第一次出现的索引，如果找不到，返回0；当查找的子串存在于字符串中：返回该子串在字符串中【第一次】出现的索引。当查找的子串不在字符串中：返回0

SELECT instr('演示sheenky','s') AS 第一次出现;

⑦trim(str)函数

去掉字符串前后的空格；该函数只能去掉字符串前后的空格，不能去掉字符串中间的空格

SELECT trim(' SQL 演示 sheenky ') AS 空格去除;

⑧lpad(str,len,填充字符)、rpad(str,len,填充字符)函数

lpad（左填充）：用指定的字符，实现对字符串左填充指定长度

rpad（右填充）：用指定的字符，实现对字符串右填充指定长度

SELECT lpad('编程秃头',10,'tu') AS out_put;
SELECT rpad('编程秃头',10,'tu') AS out_put;

⑨replace(str,子串,另一个字符串)函数

将字符串str中的字串，替换为另一个字符串

注：replace()方法只能查找指定列，而不能使用全局查找,想要全局查找替换，可以试试循环语句

SELECT replace(NAME,'符兴','符强') AS 替换之后 FROM tb_teacher;

数学函数

①round(x,保留位数)函数

四舍五入；当对正数进行四舍五入：按照正常的计算方式，四舍五入即可。当对负数进行四舍五入：先把符号丢到一边，对去掉负号后的正数进行四舍五入，完成以后，再把这个负号，补上即可

SELECT round(1.595658,3) AS out_put;

②ceil(x)函数

向上取整，返回>=该参数的最小整数。求的是大于等于这个数字的最小整数

SELECT ceil(1.9) AS out_put;
SELECT ceil(1.1) AS out_put;

③floor(x)函数

向下取整，返回<=该参数的最大整数，求的是小于等于这个数字的最大整数

SELECT floor(1.99) AS out_put;
SELECT floor(1.1) AS out_put;

④truncate(x,D)函数

此函数叫截断函数，顾名思义就是就是截取不要的部分，然后删掉（断掉）它。在小数点的D位置处，截取数字直接删去数字，若在左边就是位置取整不使用任何法则

这个函数理解起来也不难，我们把truncate当作小数点（.）x是要截取的数字。D为正数时是小数点的右侧部分，D为0时则不要小数部分，D为负数时是小数点左边部分

SELECT truncate(314159.2673525,5) AS 截取之后;
SELECT truncate(314159.2673525,0) AS 截取之后;
SELECT truncate(314159.2673525,-4) AS 截取之后;

在这里插入图片描述

⑤mod(被除数,除数)函数

取余；当被除数为正数，结果就是正数。当被除数为负数，结果就是负数

SELECT mod(10,3) AS out_put;

⑥pow(x,D)函数

此函数是用于计算指数函数，x为底，D为指数

SELECT pow(5,2) AS 平方运算;

聚合函数

①功能与分类

功能：用作统计使用，又称为聚合函数或统计函数

分类：sum 求和 avg 平均值 max 最大值 min 最小值 count 计算个数

在这里插入图片描述

②聚合函数的传入参数，数据类型

1、sum()函数和avg()函数：传入整型/小数类型才有意义；

2、sum()函数和avg()函数对于字符串类型、日期/时间类型的计算都没有太大意义。因此，sum()函数和avg()函数，我们只用来对小数类型和整型进行求和。跳过空值行。

3、max()函数和min()函数：传入整型/小数类型、日期/时间类型意义较大

4、可以传入任何数据类型，但是碰到null要注意，空值跳过，不计数。

注：sum() / count(*)方法计算平均值时，有时候得到的结果和AVG()函数不一定一样。当存在某计算列空值但其它列不是空值的时候就会出现不一样的结果，因为分母并没有跳过空值列。

时间与日期函数

①时间与日期函数含义

日期的含义：指的是我们常说的年、月、日

时间的含义：指的是我们常说的时、分、秒

序号	格式符	含义
1	%Y	四位的年份
2	%y	2位的年份
3	%m	月份（01,02，…11,12）
4	%c	月份（1,2,3…11,12）
5	%d	日（01,02，…）
6	%H	小时（24小时）
7	%h	小时（12小时）
8	%i	分钟（00,01，…59）
9	%s	秒（00,01，…59）

②now()函数

返回当前系统的日期和时间

SELECT now() AS 当前时间;

③curdate()函数

只返回系统当前的日期，不包含时间

SELECT curdate() AS 当前日期;

④curtime()函数

只返回当前的时间，不包含日期

SELECT curtime() AS 当前时间;

⑤获取日期和时间中的年、月、日、时、分、秒

获取年份：year()

获取月份：mONth()

获取日：day()

获取小时：hour()

获取分钟：minute()

获取秒数：secONd()

⑥weekofyear()函数

获取当前时刻所属周数

SELECT weekofyear(now()) AS 当前时间下的周数;

⑦quarter()函数

获取当前时刻所属的季度

SELECT quarter(curdate()) AS 当前季度;

⑧str_to_date()函数

将日期格式转换为字符串，转换成指定格式的日期

SELECT str_to_date("2022.2.2",'%Y,%c,%d') AS 转换后格式;

⑨date_format()函数

将日期转换成日期字符串

SELECT date_format("2022/2/2",'%Y年,%c月,%d日') AS 转换格式;

DATE_FORMAT(bt.create_time, '%Y-%m-%d %H:%i:%s')
DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')

⑩date_add(date,INTERVAL expr type)时间相加函数

date_sub(date,INTERVAL expr type)时间相减函数

向前、向后偏移日期和时间，注意调用方法和正负号

date 参数是合法的日期表达式。expr 参数是希望添加的时间间隔。

type 参数可以是下列值：

type	含义
MICROSECOND	微秒
SECOND	秒
MINUTE	分钟
HOUR	小时
DAY	天
WEEK	周
MONTH	月
QUARTER	季度
YEAR	年
DAY_MINUTE	日分钟
DAY_HOUR	日时
YEAR_MONTH	年_月

SELECT curdate() AS 当前时间,
date_add(curdate(),interval 1 year) AS 一年后,
date_add(curdate(),interval 1 month) AS 一月后,
date_add(curdate(),interval 1 day) AS 一天后;

date_sub(curdate(),interval 1 year) AS 一年前,
date_sub(curdate(),interval 1 month) AS 一月前,
date_sub(curdate(),interval 1 day) AS 一天前;

⑪last_day()函数

提取某个月最后一天的日期

SELECT last_day(now()) AS 当月最后一天的日期;

⑫datediff(END_date,start_date)函数

计算两个时间相差的天数

SELECT CONCAT('我出生',datediff(curdate(),'2000-01-01'),'天了!') AS 出生天数

⑬timestampdiff(unit,start_date,END_date)函数

计算两个时间返回的年/月/天数；

unit参数是确定(start_date,END_date)结果的单位，表示为整数，以下是有效单位：

year：年份、mONth：月份、day：天、hour：小时、minute 分钟、secONd：秒、microsecONd：微秒、week：周数、quarter：季度

SELECT timestampdiff(year,'2000-01-01',curdate()) AS 我出生到现在的年数

系统信息函数

①version()函数

查看MySQL系统版本信息号

SELECT version\();

②connection_id()函数

查看当前登入用户的连接次数数

直接调用connection_id()函数–不需任何参数–就可以看到当下连接MySQL服务器的连接次数，不同时间段该函数返回值可能是不一样的

SELECT connection_id();

③processlist

查看用户的连接信息

show processlist;

Id列：登录MySQL的用户标识，是系统自动分配的CONNECTION ID；
User列：显示当前的“用户名”；
Host列：显示执行这个语句的IP，用来追踪出现问题语句的用户；
db列：显示这个进程目前连接的是哪个数据库；
Command列：显示当前连接执行的命令，一般是休眠（Sleep）、查询（Query）、连接（CONnect）；
Time列：显示这个状态持续的时间，单位是秒；
State列：显示使用当前连接的SQL语句的状态，包含有：Copying to tmptable、Sorting result、SENDing data等状态；
Info列：显示当前SQL的内容，如果语句过长可能无法显示完全。

④database(),schema()函数

查看当前使用的数据库

SELECT database().schema();

⑤user(),current_user(),system_user()函数

获取当前用户

sekect user().current_user(),system_user();

⑥charset()函数

使用CHARSET()函数返回字符串使用的字符集

SELECT charset('wASd');

⑦collation()函数

使用COLLATION()函数返回字符串排列方式

SELECT collation('abc');

下部链接

标签

#mysql #学习 #笔记

MySQL定期整理磁盘碎片通过本博客，我们深入探讨了为什么需要定期整理MySQL磁盘碎片，以及如何进行这一操作。合理使用这些方法可以有效提升数据库性能，确保系统长时间稳定运行。希望这些信息对读者在实际应用中有所帮助。在下一篇博客中，我们将继续探讨MySQL数据库的其他方面，敬请期待！阅读详情

MySQL学习笔记整理（上部）

文章目录

前言

登录命令

DDL数据定义语言

一、操作库

二、操作表

创建表

字符编码问题

查看表结构

数据类型

数值类型

日期和时间类型

字符串类型

表完整性约束

完整性约束的分类

案例演示

修改表

复制表

删除表

DML数据操作语言

一、新增数据

二、删除数据

三、修改数据

DQL数据查询语言

一、基础关键字

二、排序查询

三、聚合函数

四、分组查询

五、分页查询

六、自查询/自连接

多表查询(重点)

多表的对应关系

七、内连接查询

八、外连接查询

九、子查询：查询中嵌套查询

DCL数据控制语言

管理用户

添加用户

删除用户

权限管理

查询权限

授予权限

撤销权限

开启MySQL远程访问权限(允许远程连接)

登录并查看权限信息

实现远程连接(授权法)

实现远程连接(改表法)

MySQL函数

字符函数

数学函数

聚合函数

时间与日期函数

系统信息函数

相关推荐