MYSQL第二篇（核心技术）

没有看这篇文章的小伙伴可以戳这里—>MYSQL第一篇（基础知识）
这一章，让我们继续学习MYSQL的核心技术，本章会涉及到以下知识点：

1. MYSQL基础
2. 表数据的增删改操作
3. 数据查询
4. 常用函数
5. 索引
6. 视图
   好了，废话不多说，让我们进入主题吧。
   

一、MYSQL基础

1.1 运算符

1.1.1 算术运算符
算术运算符是MYSQL中最常用的一类运算符，主要有以下几种类型：

符 号	作用
+	加法运算
-	减法运算
*	乘法运算
/	除法运算
%	求余运算
DIV	除法运算，返回商。同"/"
MOD	求余运算，返回余数。同"%"

+、-、*可以同时运算多个操作数。/和%也可以，但是不太推荐。

1.1.2 比较运算符
比较运算符是查询数据时最常用的一类运算符，SELECT语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。
主要有以下几种类型：

运 算 符	名 称	示 例	运 算 符	名 称	示 例
=	等于	id=5	IS NOT NULL	n/a	id IS NOT NULL
>	大于	id>5	BETWEEN AND	n/a	id BETWEEN 1 AND15
<	小于	id<=5	IN	n/a	id IN(3,4,5)
>=	大于等于	id>=5	NOT IN	n/a	name NOT IN(shi,li
<=	小于等于	id<=5	LIKE	模式匹配	name LIKE (‘shi%’)
!=或<>	不等于	id!=5	NOT LIKE	模式匹配	name NOT LIKE (‘shi%’)
IS NULL	n/a	id is null	REGEXP	常规表达式	name正则表达式

• = 运算符用来判断数字、字符串、表达式是否相等。如果相等返回1、否则返回0。（判断的底层逻辑是根据ASCII码）
• <> 和 ！=，如果不相等，返回1，否则返回0； 不能用于判断NUL值
• > 用来判断左边的操作数是否大于右边，如果大于则返回1，否则返回0； 不能用于判断NUL值
• IS NULL 用于判断操作数是否为空值（NULL），如果是，返回1，否则，返回0. IS NOT NULL刚好与IS NULL相反。
• BETWEEN AND 用于判断数据是否在某个取值范围内，其表达式如下：

x1 BETWEEN m AND n      %如果m=<x1<n，返回1，否则返回0

• IN用于判断数据是否存在于某个集合中，其表达式如下：

x1 IN(值1，值2，...,值n)      %如果x1为这其中的任意一个值，返回1，否则返回0

• LIKE用来匹配字符串，其表达式如下：

x1 LIKE s1      %如果x1与字符串s1匹配，结果将返回1，否则返回0

• REGEXP同样用于匹配字符串，但其使用的是正则表达式匹配，其表达式如下：

x1 REGEXP '匹配方式'      %如果x1满足匹配方式，结果将返回1，否则返回0

1.1.3 逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回1，否则返回0；
MYSQL中支持4种逻辑运算符：与、或、非和异或。

符 号	作用
&&或AND	与
两条竖线或OR	或
!或NOT	非
XOR	异或

前三者没什么好讲的，主要讲一下XOR。
只要其中任何一个操作数为NULL时，结果返回NULL；如果两个操作数都是非0值，或者都是0，则结果返回0，如果其中一个为0，另一个为非0，结果返回1.

1.1.4 位运算符
位运算符是在二进制树上进行计算的操作符。位运算会先将操作数变成二进制数再进行位运算，然后再将计算结果从二进制数变成十进制数。

符 号	作用
&	按位与: 先将十进制数转换为二进制，然后进行与运算。1与1=1，与0相与=0
一条竖线	按位或：1与任何数或运算都是1，0与0或运算的结果为0
^	按位取反：操作数转换为二进制数之后，按位取反
<<	按位左移：“m<<n”表示m的二进制数向左移n位，右边补上n个0，例二进制数001左移1位变成0010
>>	按位右移：“m>>n”表示m的二进制数向右移n位，左边补上n个0，例二进制数011左移1位变成001，最后一个1直接被移出

1.2 流程控制语句

IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句、LEAVE语句

1.2.1 IF语句
IF语句用来进行条件判断，根据不同的条件执行不同的操作。IF语句表示形式如下：

IF search_condition THEN
	statement list
[ELSE condition THEN]
	statement list2
[ELSE]
	...
ENDIF

当IF中条件search_condition成立时，执行THEN后的 statement list语句，否则判断ELSEIF中的条件;成立则执行其后的statement list2 语句，否则继续判断其他分支。当所有分支的条件均不成立时，执行ELSE分支。search_condition是一个条件表达式，可由"=、<、<=、>，>=、!="等条件运算符组成，并且可以使用 AND、OR、NOT、对多个表达式进行组合。

IF语句示例代码如下：

create procedure example_if(in x int)   //相当于创建存储过程
begin          //开始
if x=1 then
select 1;
elseif x=2 then 
select 2;
else
select 3;
end if;
end           //结束


call example_if(2)                    //相当于调用存储过程

1.2.2 CASE语句
CASE语句为多分枝语句结构，该语句首先从WHEN后的VALUE种查找与CASE后的VALUE相等的值，如果查找到则执行该分支的内容，否则执行ELSE后的内容。CASE语句的表示形式如下：

CASE value
		WHEN value THEN ...
		[WHEN value THEN ...]
		[ELSE...]
END CASE

CASE语句示例代码如下：

create procedure example_case(in x int)   //相当于创建存储过程
begin          //开始
case x
when 1 then select 1;
when 2 then select 2; 
else select 3;
end case;
end           //结束


call example_case(3)                    //相当于调用存储过程

1.2.3 WHILE语句
WHILE循环语句执行时首先判断condition条件是否为真，如果是则执行循环体，否则退出循环体。该语句表示形式如下：

WHILE condition DO
	...
END WHILE;

例1.2.3： 下面应用WHILE语句求前100项的和。首先定义变量i和s，分别用来控制循环的次数和保存前100项和，当变量i的值小于或等于100时，使s的值加i，并同时使i的值增1.直到i大于100时，退出循环并输出结果。其代码如下所示。

//第一句相当于创建存储过程 create procedure p([in/out/inout]参数名 参数类型)
create procedure example_while (out sum int)    
begin
declare i int default 1;    //定义i一开始为1
declare s int default 0;
while i<=100 do
set s=s+i;
set i=i+1;
end while;
set sum=s;
end

call example_while(@s)       //调用该存储过程
select @s

1.2.4 LOOP循环语句
该循环没有内置的循环条件(可以理解为死循环)，但可以通过LEAVE语句退出循环。LOOP语句的表示形式如下：

LOOP 
...
END LOOP

LEAVE语句经常和BEGIN…END或循环一起使用，其表示形式如下：

LEAVE label

label是语句中标注的名字，这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

例1.2.4： 下面应用LOOP语句求前100项的和。首先定义变量i和s，分别用来控制循环的次数和保存前100项和，进入该循环体后首先使s的值加i，之后使i加1并进入下次循环，直到i大于100，通过LEAVE语句退出循环并输出结果。其代码如下所示：

create procedure example_loop (out sum int)    
begin
declare i int default 1;    //定义i一开始为1
declare s int default 0;
loop_label:loop
set s=s+i;
set i=i+1;
if i>100 then
leave loop_label;    //if条件成立，则会离开loop循环
end if;
end loop;
set sum=s;
end

call example_loop(@s1)       //前一个例子用的是s，为了区分这个例子用的是s1
select @s1

1.2.5 REPEAT循环语句
该语句先执行一次循环体，之后判断condition条件是否为真，为真则退出循环，否则继续执行循环。其表示形式如下：

REPEAT
	...
UNTIL condition
END REPEAT

例1.2.5： 下面应用REPEAT语句求前100项的和。首先定义变量i和s，分别用来控制循环的次数和保存前100项和，进入该循环体后首先使s的值加i，之后使i加1，直到i大于100时退出循环并输出结果。其代码如下所示：

create procedure example_repeat (out sum int)    
begin
declare i int default 1;    //定义i一开始为1
declare s int default 0;
repeat
set s=s+i;
set i=i+1;
until i>100 
end repeat;           //until 条件成立，则会离开loop循环
set sum=s;
end

call example_repeat(@s2)       
select @s2

二、表数据的增删改操作

2.1 插入数据

在建立一个空的数据库和数据表时，首先要考虑的是如何向数据表中添加数据，该操作可以用INSERT语句来完成。
在MYSQL中，INSTRT语句有三种语法格式：

• INSTRT … VALUES
• INSTRT …SET
• INSTRT …SELECT

2.1.1 使用INSTRT … VALUES语句插入数据
使用INSTRT … VALUES语句插入数据,是INSERT语句中最常用的语法格式。它的语法如下：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
	[INTO] 数据表名 [(字段名，...)]
	VALUES ( {值 | DEFAULT}, ...), (...), ...
	[ON DUPLICATE KEY UPDATE 字段名=表达式， ... ]

别看这上面很复杂，毕竟这是所有语法的综合体，但实际上我们向数据库、表中插入数据只会用到部分。下面简单的介绍一下里面的几个参数。

1. [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] 为可选项，三者是3选1的，通常应用在多用户访问数据库的情况下，用于指定MYSQL降低插入、删除、更新操作执行的优先级。
2. [IGNORE] ：可选项，表示再执行INSERT语句时，所出现的错误都会被当作警告处理
3. [INTO] 数据表名：可选项，用于指定被操作的数据表
4. [(字段名，…)] ：可选项，当不指定该选项时，表示要向表中的所有列插入数据，否则表示向数据表的指定列插入数据
5. VALUES ( {值 | DEFAULT}, …), (…), … ：必选项，用于指定需要插入的数据清单，其顺序必须与字段对应的顺序相对应。其中每一列的数据可以是一个常量、变量、表达式或者NULL，但是其数据类型必须要与对应字段的类型相匹配；也可以直接使用DEFAULT关键字，表示为该列插入默认值，但是使用的前提是已经明确制定了默认值，否则会出错。
6. [ON DUPLICATE KEY UPDATE 字段名=表达式， … ] 子句：可选项，用于指定向表中插入行时，如果导致UNIQUE KEY 或PRIMARY KEY出现重复值，系统会更具UPDATE后的语句修改表中原有的行数据。

INSERT … VALUES的三种使用方式
1.插入完整数据
例2.1.1： 通过INSERT … VALUES语句向数据表tb_user中插入一条完整的数据。

(1) 先通过查看表结构，以防止我们的VALUES类型出错，然后再插入数据，具体代码如下。

DESC db_admin.tb_user;

USE db_admin;
INSERT INTO tb_user VALUES(2,'zxl','654321','2022-05-29 13:42:50');

2. 插入数据记录的一部分
例2.1.2： 通过INSERT … VALUES语句向数据表tb_user中插入数据记录的一部分，即只插入user和password字段的值。具体代码如下：(注意下面的语句最好一条一条执行，如果选中全部执行的话可能会报错)

USE db_admin
INSERT INTO tb_user (username,password) VALUES('aaabbb','111000');
SELECT * FROM tb_user;   //查看修改后的数据表

细心的小伙伴会发现id自动增长了，这是因为在设计数据表时，将id字段设置为自动编号，所以即使没有指定id,MYSQL也会自动为他填上相应的编号。（下面的这段代码是创建这个数据表时用的）

USE db_admin;
CREATE TABLE tb_admin(
id int auto_increment primary key,   //id设置为自动增长的主键
user VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
createtime datetime);

3.插入多条记录

使用该方法批量的插入数据，比使用多条单行的INSERT语句的效率要高。下面通过一个例子进行演示：

例2.1.3： 通过INSERT … VALUES语句向数据表tb_user中插入多条记录，具体代码如下：(注意下面的语句最好一条一条执行，如果选中全部执行的话可能会报错)

USE db_admin;
INSERT INTO tb_user (username,password,createtime)
VALUES('A1','11111','2022-05-09 14:03:01'),
('A2','22222','2022-05-09 14:04:01'),
('A3','33333','2022-05-09 14:05:01');

SELECT * FROM tb_user;     //查看修改后的数据表

---

2.1.2 使用INSTRT … SET语句插入数据
在MYSQL中，除了使用INSTRT … VALUES语句插入数据外，还可以使用INSTRT … SET语句插入数据。还是来看个例子吧：

例2.1.2： 通过INSTRT … SET语句向数据表tb_user中插入一条记录。具体代码如下：

USE db_admin;
INSERT INTO tb_user
SET username='B1',password='10086',createtime='2022-05-30 12:05:01';

SELECT * FROM tb_user;      //查看修改后的数据表

---

2.1.3 将查询结果插入到别处
在MYSQL中，支持将查询结果插入到指定的数据表中，这可以通过INSERT…SELECT语句实现。

例2.1.3： 从数据表tb_user中查询出username和password字段的值，然后插入到数据表tb_admin中。
（1）首先我们要创建一个数据表tb_admin，用于待会存放查询结果。输入以下代码用于创建数据表tb_admin

USE db_admin;
CREATE TABLE tb_admin(
id int auto_increment primary key,  
user VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
createtime datetime);

（2）查询数据表tb_user中的数据，具体代码如下：

USE db_admin;
SELECT * FROM tb_user; 

（3）编写SQL语句，实现从数据表中查询username和password字段的值，插入到数据表tb_admin中，具体代码如下：

//插入到目的数据表tb_admin中的user,password字段，从源数据表tb_user中
INSERT INTO db_admin.tb_admin    
	(user,password)
	SELECT username,password FROM tb_user;

USE db_admin;
SELECT * FROM tb_admin; 

---

2.2 修改数据

要执行修改的操作可以使用UPDATA语句，语法如下：

UPDATE [LOW_PRIORITY] [IGNORE]
	SET 字段1 = 值1 [字段2 = 值2 ...]
	[WHERE 条件表达式]
	[ORDER BY ...]
	[LIMIT 行数]

其中，SET子句是必选项，用于指定表中要修改的字段名及其字段值；WHERE子句：可选项，用于限定表中要修改的行，如果不指定该子句，那么UPDATE语句会更新表中所有的行；ORDER BY子句用于限定表中的行被修改的次序； LIMIT子句用于限定被修改的行数；

例2.2： 将管理员信息表tb_admin中用户名为root的管理员密码123456修改为8888。具体代码如下：

UPDATE db_admin.tb_admin SET password='8888' WHERE user='root';

SELECT * FROM db_admin.tb_admin;   //查看修改后的数据表

---

2.3 删除数据

在MYSQL中，可以使用DELETE语句或者TRUNCATE TABLE语句删除表中的一行或多行数据。

2.3.1 通过DELETE语句删除数据

例2.3.1： 删除管理员数据表tb_admin中用户名为A1的记录信息。具体代码如下：

USE db_admin;
DELETE FROM tb_admin WHERE user='A1';

删除前后的数据表内容对比如下：

**注意：**在实际应用中，执行删除操作时，删除的条件一般应改为数据的id，而不是某个具体的字段值，这样可以避免一些错误发生。

2.3.2 通过TRUNCATE TABLE语句删除数据
在删除数据时，如果要从表中删除所有的行，通过TRUNCATE TABLE语句即可，基本语法如下：

TRUNCATE [TABLE] 数据表名

也可以使用“数据库.数据表名”来指定该数据表属于哪个数据库
注意： 由于TRUNCATE TABLE语句会删除数据表中的所有数据，且无法恢复，所以使用时要格外谨慎。
例 2.3.2： 使用TRUNCATE TABLE语句清空管理员数据表tb_admin，具体代码如下：

TRUNCATE TABLE db_admin.tb_admin;

---

三、数据查询

3.1 基本查询语句

SELECT是最常用的查询语句，它的基本语法如下：

select selection _ list      //要查询的内容，选择哪些列
from 数据表名                //指定数据表     
where primary _ constraint   //查询时需要满足的条件，行必须满足的条件
group by grouping _ columns  //如何对结果进行分组
order by sorting _ cloumns   //如何对结果进行排序
having secondary _ constraint  //查询时满足的第ニ条件
limit count                   //限定输出的查询结果

1.使用SELECT语句查询一个数据表

SELECT * FROM db_admin.tb_admin;   //查询db_admin数据库中的tb_admin数据表

2.查询表中的一列或多列

//针对表中的多列进行查询时，只要在select后面指定要查询的列名即可，多列之间用 , 进行隔开。如查询db_admin数据库中tb_user表中的id和password

SELECT id,password FROM db_admin.tb_user;   

3.查询表中的一列或多列
例3.1： 从tb_user表和tb_userNew表中查询出tb_user.username, tb_user.password和tb_userNew.username, tb_userNew.password的值。具体代码如下：

//SELECT 表1.字段1, 表1.字段2, 表2.字段1, 表2.字段2
	FROM 表1, 表2;
//  语法形式

USE db_admin;
SELECT tb_user.id, tb_user.username,tb_userNew.id, tb_userNew.username
	FROM tb_user,tb_userNew;

3.2 单表查询

3.2.1 查询所有字段

SELECT * FROM 表名

3.2.2 查询指定字段

SELECT 字段名 FROM 表名

3.2.3 查询指定数据
如果要从很多记录中查询出指定的记录，那么就需要一个查询的条件。设定查询条件应用的是WHERE子句。通过它可以实现很多复杂的条件查询。在使用WHERE子句时，需要使用一些比较运算符来确定查询的条件。
例3.2.3： 应用WHERE子句查询tb_user表，条件是username（用户名）为root，代码如下：

SELECT * FROM db_admin.tb_user WHERE username='root';

3.2.4 带关键字IN的查询
关键字IN可以判断某个字段的值是否在指定的集合中。其语法格式如下：

SELECT * FROM 表名 WHERE 条件 [NOT] IN(元素1，元素 2， ... ，元素n);

(1) [NOT]: 是可选项，加上NOT表示不在集合内满足条件；
(2)元素：表示集合中的元素，各元素之间用逗号隔开，字符型元素需要加上单引号。

例3.2.4： 应用IN关键字查询tb_user表中username字段为root和zxl的记录，查询语句如下：

SELECT * FROM db_admin.tb_user WHERE username IN ('root','zxl');

3.3 持续更新中

附录：常见的数据库指令

因为有些时候小写更符合国人的阅读习惯，所以有些命令我用的是小写。

//查看数据库中的所有表
show tables FROM 数据库;   

//查看数据表中所有的数据命令
USE 数据库;
select * from 表名;

或者使用  select * from 数据库.表名
       

//查看数据库中某个表的表结构
USE 数据库;
DESC 表名;

或者使用  DESC 数据库.表名

喜欢的小伙伴可以点赞收藏关注走一波，您的鼓励就是对我最大的支持，谢谢！