MYSQL数据库高级SQL语句详解

tech2022-08-16 139

文章目录

一：MyAQL进阶查询1.1：按关键字排序1.11：按单字短排序1.12：按多字段排序 1.2：对结果进行分组1.21：常用的聚合函数包括：1.22：GROUP BY分组1.22：GROUP BY集合ORDER BY 1.3：限制结果条目1.4：设置别名1.41：AS的用法1.42：进行多表相连1.43：定义列别名 1.5：通配符1.51:通配符的用法1.52：两者集合使用 1.6：子查询1.61：子查询的用法1.62：mysql算数运算符号 1.7：NULL值1.71：查询name字段不为空的记录1.72：查询name字段为NULL值的记录二：正则表达式2.1：正则表达式匹配表2.11：包含指定字符串的记录2.12：以"."代替字符串的任意一个字符的记录2.13：匹配包含或者关系的记录2.14：匹配前面字符的任意多次2.15：”+“匹配前面字符至少一次2.16：匹配指定字符集的任意一个三：运算符3.1：算数运算符3.1：运算符号分类3.11：MYSQL支持使用的运算符号 3.2：比较运算3.21：等于运算符3.22：不等于运算符3.23：大于、大于等于、小于、小于等于运算符3.24： IS NULL、IS NOT NULL3.25：BETWEEN AND3.26：LEAST、GREATEST3.27：IN、NOT IN3.28：LIKE、NOT LIKE 3.3：逻辑运算符3.31：常用的逻辑运算符号3.32：逻辑非3.33：逻辑与3.34：逻辑或3.35:逻辑异或 3.4：位运算符3.41：MYSQL支持的运算符3.42：运算符的优先级 3.4：连接查询3.41：内连接3.42：左连接3.43：右连接四：数据库函数4.1：常用的函数分类4.2：数学函数4.4：字符串函数4.5：日期时间函数4.51：常用的日期时间函数五：存储过程简介5.1：创建存储过程的语法结构5.11：创建存储过程5.12存储过程的语法结构5.13：存储过程的参数

一：MyAQL进阶查询

在对 MySQL 数据库的增、删、改、查操作有一定了解之后，就可以学习一些 SQL 语句的高级使用方法。SQL 语句的熟练使用，在平时的运维工作中可以提供不小的帮助，尤其是在一些规模较小的公司，运维身兼数职，可能会有不少数据库的相关工作。本章将从不同的方面出发介绍 SQL 语句的高级运用方法。

1.1：按关键字排序

使用ORDERBY语句来实现排序

排序可针对一个或多个字段

ASC:升序，默认排序方式【升序是从小到大】

DESC:降序【降序是从大到小】

ORDER BY的语法结构

ORDER BY后面跟字段名

SELECT column1, column2,....FROM table_name ORDER BY column1,column2,...ASC|DESC;

1.11：按单字短排序

我们这边新建一个数据库tt #创建tt数据库 mysql> cerate databases tt; #切换到tt库 mysql> use tt; #创建表结构tt mysql> create table tt (id int(10) not null primary key auto_increment,name char(20) not null,score decimal(5,2),adddress varchar（40）default '未知')engine=innodb; #查看表结构 mysql> desc tt; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | score | decimal(5,2) | YES | | NULL | | | address | varchar(40) | YES | | 未知 | | +---------+--------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) #插入数据记录 mysql> insert into tt (name,score,address) values ('wangwu',70,'beijing'),('lisi',90,'nanjing'); mysql> insert into tt (name,score,address) values ('zhangsan',80,'beijing'),('zhaoliu',60,'nanjing'); #查看数据记录 mysql> select * from tt; +----+----------+-------+---------+ | id | name | score | address | +----+----------+-------+---------+ | 1 | wangwu | 70.00 | beijing | | 2 | lisi | 90.00 | nanjing | | 3 | zhangsan | 80.00 | beijing | | 4 | zhaoliu | 60.00 | nanjing | +----+----------+-------+---------+ 4 rows in set (0.00 sec) 筛选分数大于70分的 mysql> select name,score from tt where score>=70; +----------+-------+ | name | score | +----------+-------+ | wangwu | 70.00 | | lisi | 90.00 | | zhangsan | 80.00 | +----------+-------+ 3 rows in set (0.00 sec) #筛选大于70分的进行升序排序【对score字段排序】 mysql> select name,score from tt where score>=70 order by score; +----------+-------+ | name | score | +----------+-------+ | wangwu | 70.00 | | zhangsan | 80.00 | | lisi | 90.00 | +----------+-------+ 3 rows in set (0.00 sec) #进行降序排序 mysql> select name,score from tt where score>=70 order by score desc; +----------+-------+ | name | score | +----------+-------+ | lisi | 90.00 | | zhangsan | 80.00 | | wangwu | 70.00 | +----------+-------+ 3 rows in set (0.00 sec)

【SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤.】

1.12：按多字段排序

这边我们再次插入新的数据内容 mysql> insert into tt (name,score,address) values ('tianqi',80,'beijing'),,('shuaige',70,'suzhou'); #查询数据记录 mysql> select * from tt; +----+----------+-------+---------+ | id | name | score | address | +----+----------+-------+---------+ | 1 | wangwu | 70.00 | beijing | | 2 | lisi | 90.00 | nanjing | | 3 | zhangsan | 80.00 | beijing | | 4 | zhaoliu | 60.00 | nanjing | | 5 | tianqi | 80.00 | beijing | | 6 | shuaige | 70.00 | suzhou | +----+----------+-------+---------+ 6 rows in set (0.00 sec) #单字段匹配【降序】 mysql> select id,name,score from tt where score>=70 order by score desc; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 2 | lisi | 90.00 | | 3 | zhangsan | 80.00 | | 5 | tianqi | 80.00 | | 1 | wangwu | 70.00 | | 6 | shuaige | 70.00 | +----+----------+-------+ 5 rows in set (0.00 sec) #修改lisi成绩为80分 mysql> update tt set score=80 where id=2; mysql> select id,name,score from tt where score>=70 order by score desc; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 2 | lisi | 80.00 | | 3 | zhangsan | 80.00 | | 5 | tianqi | 80.00 | | 1 | wangwu | 70.00 | | 6 | shuaige | 70.00 | +----+----------+-------+ 5 rows in set (0.00 sec) #进行多段排序 mysql> select id,name,score from tt where score>=70 order by score desc,id desc; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 5 | tianqi | 80.00 | | 3 | zhangsan | 80.00 | | 2 | lisi | 80.00 | | 6 | shuaige | 70.00 | | 1 | wangwu | 70.00 | +----+----------+-------+ 5 rows in set (0.00 sec) #这边把id字段也做了一个排序

1.2：对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现。

GROUP BY 从字面上看，是以 BY 后面的内容对查询出的数据进行分组，就是将一个“数据集”划分成若干个“小区域”，然后针对这些个“小区域”进行数据处理。

使用GROUP BY语句来实现分组

通常结合聚合函数一起使用

可以按一个或多个字段对结果进行分组

GROUP BY的语法结构

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;

1.21：常用的聚合函数包括：

count （字段名称）计数函数

count （*）技术

sum （*）求和

avg （*）平均值

max （*）最大

min （*）最小值

1.22：GROUP BY分组

统计70跟80分的人数 #查询所有数据记录 mysql> select * from tt; +----+----------+-------+---------+ | id | name | score | address | +----+----------+-------+---------+ | 1 | wangwu | 70.00 | beijing | | 2 | lisi | 80.00 | nanjing | | 3 | zhangsan | 80.00 | beijing | | 4 | zhaoliu | 60.00 | nanjing | | 5 | tianqi | 80.00 | beijing | | 6 | shuaige | 70.00 | suzhou | +----+----------+-------+---------+ 6 rows in set (0.00 sec) #进行分组 mysql> select count(name),score from tt where score >=70 group by score; +-------------+-------+ | count(name) | score | +-------------+-------+ | 2 | 70.00 | | 3 | 80.00 | +-------------+-------+ 2 rows in set (0.00 sec) 求班级的学生的平均成绩 avg （*）平均值 mysql> select avg(score) from tt; +------------+ | avg(score) | +------------+ | 73.333333 | +------------+ 1 row in set (0.00 sec)

1.22：GROUP BY集合ORDER BY

#分组降序 mysql> select count(name),score from tt where score >=70 group by score order by score desc; +-------------+-------+ | count(name) | score | +-------------+-------+ | 3 | 80.00 | | 2 | 70.00 | +-------------+-------+ 2 rows in set (0.00 sec) #分组升序 mysql> select count(name),score from tt where score >=70 group by score order by score; +-------------+-------+ | count(name) | score | +-------------+-------+ | 2 | 70.00 | | 3 | 80.00 | +-------------+-------+ 2 rows in set (0.01 sec)

1.3：限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句。

LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。

只返回SELECT查询结果的第一行或前几行

使用LIMIT语句限制条目

LIMIT语法结构

SELECT column1,column2,...FROM table_name LIMIT[offset,] number; number：返回记录行的最大数目 [offset,]：位置偏移量，从0开始查看前三行的记录 mysql> select * from tt limit 3; +----+----------+-------+---------+ | id | name | score | address | +----+----------+-------+---------+ | 1 | wangwu | 70.00 | beijing | | 2 | lisi | 80.00 | nanjing | | 3 | zhangsan | 80.00 | beijing | +----+----------+-------+---------+ 3 rows in set (0.00 sec) 查看3到5行的记录 #2代表索引往下数3行 mysql> select * from tt limit 2,3; +----+----------+-------+---------+ | id | name | score | address | +----+----------+-------+---------+ | 3 | zhangsan | 80.00 | beijing | | 4 | zhaoliu | 60.00 | nanjing | | 5 | tianqi | 80.00 | beijing | +----+----------+-------+---------+ 3 rows in set (0.00 sec) 列出班级前三名

在插入一些数据记录

mysql> insert into tt (name,score,address) values ('tom',87,'shanghai'),('shuaige',76,'hangzhou'); #查询数据记录 mysql> select * from tt; +----+----------+-------+----------+ | id | name | score | address | +----+----------+-------+----------+ | 1 | wangwu | 70.00 | beijing | | 2 | lisi | 80.00 | nanjing | | 3 | zhangsan | 80.00 | beijing | | 4 | zhaoliu | 60.00 | nanjing | | 5 | tianqi | 80.00 | beijing | | 6 | shuaige | 70.00 | suzhou | | 7 | tom | 87.00 | shanghai | | 8 | shuaige | 76.00 | hangzhou | +----+----------+-------+----------+ 8 rows in set (0.00 sec) #列出前三名先排序 mysql> select * from tt order by score desc limit 3; +----+----------+-------+----------+ | id | name | score | address | +----+----------+-------+----------+ | 7 | tom | 87.00 | shanghai | | 2 | lisi | 80.00 | nanjing | | 3 | zhangsan | 80.00 | beijing | +----+----------+-------+----------+ 3 rows in set (0.00 sec) 方法2：只要条件成立就行 mysql> select * from tt where 1=1 order by score desc limit 3; +----+----------+-------+----------+ | id | name | score | address | +----+----------+-------+----------+ | 7 | tom | 87.00 | shanghai | | 2 | lisi | 80.00 | nanjing | | 3 | zhangsan | 80.00 | beijing | +----+----------+-------+----------+ 3 rows in set (0.00 sec)

1.4：设置别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性。

使用AS语句设置别名，关键字AS可省略设置别名时，保证不能与库中其他表或字段名称冲突别名的语法结构 SELECT column_name AS alias_name FROM table_name; SELECT column_name(s)FROM table_name ASalias_name;

1.41：AS的用法

示例

mysql> select count(*) from tt; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) as 数量 from tt; +--------+ | 数量 | +--------+ | 8 | +--------+ 1 row in set (0.00 sec) 多表相连 #这边我们只有tt一张表我们在创建一张表命名为gg create table gg (id int(5) not null primary key auto_increment,gg_name varchar(20) not null); #显示所有表 mysql> show tables; +--------------+ | Tables_in_tt | +--------------+ | gg | | tt | +--------------+ 2 rows in set (0.00 sec) #查看表结构 mysql> desc gg; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(5) | NO | PRI | NULL | auto_increment | | gg_name | varchar(20) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) #插入数据 mysql> insert into gg (gg_name) values ('小当家'),('小神龙'),('小福贵');; #查看gg表数据记录 mysql> select * from gg; +----+-----------+ | id | gg_name | +----+-----------+ | 1 | 小当家 | | 2 | 小神龙 | | 3 | 小福贵 | +----+-----------+ 3 rows in set (0.00 sec) #为了对应我们给tt表增加一列 mysql> alter table tt add column hobby int(3) not null; #查看表结构 mysql> desc tt; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | score | decimal(5,2) | YES | | NULL | | | address | varchar(40) | YES | | 未知 | | | hobby | int(3) | NO | | NULL | | +---------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) #查询表数据记录 mysql> select * from tt; +----+----------+-------+----------+-------+ | id | name | score | address | hobby | +----+----------+-------+----------+-------+ | 1 | wangwu | 70.00 | beijing | 0 | | 2 | lisi | 80.00 | nanjing | 0 | | 3 | zhangsan | 80.00 | beijing | 0 | | 4 | zhaoliu | 60.00 | nanjing | 0 | | 5 | tianqi | 80.00 | beijing | 0 | | 6 | shuaige | 70.00 | suzhou | 0 | | 7 | tom | 87.00 | shanghai | 0 | | 8 | shuaige | 76.00 | hangzhou | 0 | +----+----------+-------+----------+-------+ 8 rows in set (0.00 sec) 定义了不允许为空 null 这边显示跌是默认值如果定义的是允许为空就是显示的是null 什么都没雨

小结：

数据库中' ':是空的字符串是有值的

null：是什么都没有没有值

1.42：进行多表相连

把80分以上的hobby字段设为1 80以下的人设为2 mysql> update tt set hobby=1 where score >=80; mysql> select * from tt; +----+----------+-------+----------+-------+ | id | name | score | address | hobby | +----+----------+-------+----------+-------+ | 1 | wangwu | 70.00 | beijing | 0 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 60.00 | nanjing | 0 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 70.00 | suzhou | 0 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 76.00 | hangzhou | 0 | +----+----------+-------+----------+-------+ 8 rows in set (0.00 sec) #再把0全部改为2 mysql> mysql> update tt set hobby=2 where ; #查询表数据记录 mysql> select * from tt; +----+----------+-------+----------+-------+ | id | name | score | address | hobby | +----+----------+-------+----------+-------+ | 1 | wangwu | 70.00 | beijing | 2 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 60.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 70.00 | suzhou | 2 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 76.00 | hangzhou | 2 | +----+----------+-------+----------+-------+ 8 rows in set (0.00 sec) 进行两连查询inner join 【内连接】 #tt表的外键对应的是gg表的主键 mysql> select * from tt inner join gg on tt.hobby=gg.id; +----+----------+-------+----------+-------+----+-----------+ | id | name | score | address | hobby | id | gg_name | +----+----------+-------+----------+-------+----+-----------+ | 1 | wangwu | 70.00 | beijing | 2 | 2 | 小神龙 | | 2 | lisi | 80.00 | nanjing | 1 | 1 | 小当家 | | 3 | zhangsan | 80.00 | beijing | 1 | 1 | 小当家 | | 4 | zhaoliu | 60.00 | nanjing | 2 | 2 | 小神龙 | | 5 | tianqi | 80.00 | beijing | 1 | 1 | 小当家 | | 6 | shuaige | 70.00 | suzhou | 2 | 2 | 小神龙 | | 7 | tom | 87.00 | shanghai | 1 | 1 | 小当家 | | 8 | shuaige | 76.00 | hangzhou | 2 | 2 | 小神龙 | +----+----------+-------+----------+-------+----+-----------+ 8 rows in set (0.00 sec) #只看兴趣爱好组的人 mysql> select tt.name,gg.gg_name from tt inner join gg on tt.hobby=gg.id; +----------+-----------+ | name | gg_name | +----------+-----------+ | wangwu | 小神龙 | | lisi | 小当家 | | zhangsan | 小当家 | | zhaoliu | 小神龙 | | tianqi | 小当家 | | shuaige | 小神龙 | | tom | 小当家 | | shuaige | 小神龙 | +----------+-----------+ 8 rows in set (0.00 sec) 设置别名 #设置gg别名为G tt别名为T mysql> select T.name,G.gg_name from tt as T inner join gg as G on T.hobby=G.id; +----------+-----------+ | name | gg_name | +----------+-----------+ | wangwu | 小神龙 | | lisi | 小当家 | | zhangsan | 小当家 | | zhaoliu | 小神龙 | | tianqi | 小当家 | | shuaige | 小神龙 | | tom | 小当家 | | shuaige | 小神龙 | +----------+-----------+ 8 rows in set (0.00 sec) #as是自定义的可以取消 mysql> select T.name,G.gg_name from tt T inner join gg G on T.hobby=G.id; +----------+-----------+ | name | gg_name | +----------+-----------+ | wangwu | 小神龙 | | lisi | 小当家 | | zhangsan | 小当家 | | zhaoliu | 小神龙 | | tianqi | 小当家 | | shuaige | 小神龙 | | tom | 小当家 | | shuaige | 小神龙 | +----------+-----------+ 8 rows in set (0.00 sec)

1.43：定义列别名

mysql> select T.name 姓名,G.gg_name 兴趣爱好 from tt T inner join gg G on T.hobbyy=G.id; +----------+--------------+ | 姓名 | 兴趣爱好 | +----------+--------------+ | wangwu | 小神龙 | | lisi | 小当家 | | zhangsan | 小当家 | | zhaoliu | 小神龙 | | tianqi | 小当家 | | shuaige | 小神龙 | | tom | 小当家 | | shuaige | 小神龙 | +----------+--------------+ 8 rows in set (0.00 sec)

1.5：通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。

用于替换字符串的部分字符

通常配合LIKE一起使用，并协同WHERE完成查询

常用的通配符

%表示零个、一个或多个

_表示单个字符

1.51:通配符的用法

1.52：两者集合使用

1.6：子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

也称作内查询或者嵌套查询先于主查询被执行，其结果将作为外层主查询的条件在增删改查中都可以使用子查询支持多层嵌套IN语句是用来判断某个值是否在给定的结果集中

1.61：子查询的用法

查看小当家的兴趣爱好表 mysql> select * from tt; +----+----------+-------+----------+-------+ | id | name | score | address | hobby | +----+----------+-------+----------+-------+ | 1 | wangwu | 70.00 | beijing | 2 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 60.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 70.00 | suzhou | 2 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 76.00 | hangzhou | 2 | +----+----------+-------+----------+-------+ 8 rows in set (0.00 sec) mysql> select * from gg; +----+-----------+ | id | gg_name | +----+-----------+ | 1 | 小当家 | | 2 | 小神龙 | | 3 | 小福贵 | +----+-----------+ 3 rows in set (0.00 sec) #筛选小当家的兴趣爱好组的人 mysql> select name,hobby from tt where hobby in(select id from gg where gg_name=''小当家'); +----------+-------+ | name | hobby | +----------+-------+ | lisi | 1 | | zhangsan | 1 | | tianqi | 1 | | tom | 1 | +----------+-------+ 4 rows in set (0.02 sec) 方法二：后面输出的结果赋予了前面的值 mysql> select name,hobby from tt where hobby=(select id from gg where gg_name=' 小当家'); +----------+-------+ | name | hobby | +----------+-------+ | lisi | 1 | | zhangsan | 1 | | tianqi | 1 | | tom | 1 | +----------+-------+ 4 rows in set (0.00 sec) 筛选出小当家与小神龙的兴趣爱好的人 mysql> select name,hobby from tt where hobby in (select id from gg); +----------+-------+ | name | hobby | +----------+-------+ | wangwu | 2 | | lisi | 1 | | zhangsan | 1 | | zhaoliu | 2 | | tianqi | 1 | | shuaige | 2 | | tom | 1 | | shuaige | 2 | +----------+-------+ 8 rows in set (0.00 sec) 查看小神龙兴趣爱好的人 mysql> select name,hobby from tt where hobby<>(select id from gg where gg_name=' 小当家'); +---------+-------+ | name | hobby | +---------+-------+ | wangwu | 2 | | zhaoliu | 2 | | shuaige | 2 | | shuaige | 2 | +---------+-------+ 4 rows in set (0.00 sec) mysql> select name,hobby from tt where hobby in(select id from gg where gg_name='小神龙'); +---------+-------+ | name | hobby | +---------+-------+ | wangwu | 2 | | zhaoliu | 2 | | shuaige | 2 | | shuaige | 2 | +---------+-------+ 4 rows in set (0.00 sec) mysql> select name,hobby from tt where hobby=(select id from gg where gg_name=' 小神龙'); +---------+-------+ | name | hobby | +---------+-------+ | wangwu | 2 | | zhaoliu | 2 | | shuaige | 2 | | shuaige | 2 | +---------+-------+ 4 rows in set (0.00 sec) mysql> select name,hobby from tt where hobby!=(select id from gg where gg_name=' 小当家'); +---------+-------+ | name | hobby | +---------+-------+ | wangwu | 2 | | zhaoliu | 2 | | shuaige | 2 | | shuaige | 2 | +---------+-------+ 4 rows in set (0.00 sec) mysql> select name,hobby from tt where hobby<>(select id from gg where gg_name=' 小当家'); +---------+-------+ | name | hobby | +---------+-------+ | wangwu | 2 | | zhaoliu | 2 | | shuaige | 2 | | shuaige | 2 | +---------+-------+ 4 rows in set (0.00 sec)

总结：经过测试发现mysql中用<>与!=都是可以的，但sqlserver中不识别!=,所以建议用<>

1.62：mysql算数运算符号

mysql> select 1+5; +-----+ | 1+5 | +-----+ | 6 | +-----+ 1 row in set (0.00 sec) mysql> select 1*5; +-----+ | 1*5 | +-----+ | 5 | +-----+ 1 row in set (0.00 sec) mysql> select 1=2; +-----+ | 1=2 | +-----+ | 0 | +-----+ 1 row in set (0.00 sec) mysql> select 1!=2; +------+ | 1!=2 | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> <> or != 用于判断数字，字符串，表达式不相等的判断,如果不相等，返回值为1，否则返回值为0.

创建一个新表，把nanjing导入到新表中

#克隆tt表新表命名为xi表 mysql> create table xi like tt; #显示所有表 mysql> show tables; +--------------+ | Tables_in_tt | +--------------+ | gg | | tt | | xi | +--------------+ 3 rows in set (0.01 sec) #xi表没有表结构我们创建一下 mysql> select * from xi; Empty set (0.00 sec) #把tt表的beijing导入到xi表中 mysql> insert into xi select * from tt where id in (select id from tt where address='beijing'); mysql> select * from xi; +----+----------+-------+---------+-------+ | id | name | score | address | hobby | +----+----------+-------+---------+-------+ | 1 | wangwu | 70.00 | beijing | 2 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 60.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | +----+----------+-------+---------+-------+ 5 rows in set (0.00 sec) 把没有达到80分的人的分数每人加5分 mysql> select * from tt; +----+----------+-------+----------+-------+ | id | name | score | address | hobby | +----+----------+-------+----------+-------+ | 1 | wangwu | 70.00 | beijing | 2 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 60.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 70.00 | suzhou | 2 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 76.00 | hangzhou | 2 | +----+----------+-------+----------+-------+ # mysql> update tt set score=score+5 where score<80; Query OK, 4 rows affected (0.01 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from tt; +----+----------+-------+----------+-------+ | id | name | score | address | hobby | +----+----------+-------+----------+-------+ | 1 | wangwu | 75.00 | beijing | 2 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 65.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 75.00 | suzhou | 2 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 81.00 | hangzhou | 2 | +----+----------+-------+----------+-------+ 8 rows in set (0.00 sec)

1.62：EXIST 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空，则返回 TRUE；反之，则返回 FALSE。

mysql> select name,score from tt where exists (select id from gg where gg_name=' 小当家'); +----------+-------+ | name | score | +----------+-------+ | wangwu | 75.00 | | lisi | 80.00 | | zhangsan | 80.00 | | zhaoliu | 65.00 | | tianqi | 80.00 | | shuaige | 75.00 | | tom | 87.00 | | shuaige | 81.00 | +----------+-------+ 8 rows in set (0.00 sec) mysql> select name,score from tt where exists (select id from gg where gg_name=' 位置'); Empty set (0.00 sec)

1.7：NULL值

表示缺失的值与数字0或者空白(spaces）是不同的使用IS NULL或IS NOT NULL进行判断NULL值和空值的区别空值长度为0，不占空间;NULL值的长度为NULL，占用空间 .IS NULL无法判断空值空值使用“="或者“<>"来处理 .cOUNT()计算时，NULL会忽略，空值会加入计算

NULL里面没有任何值，占用一部分空间

示例

创建一个表 mysql> create table num (id int(4) not null primary key auto_increment,name char(10)); #查看表结构 mysql> desc num; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) #插入内容 mysql> insert into num (id,name) values (2,'shuai'); Query OK, 1 row affected (0.00 sec) #查询数据记录 mysql> select * from num; +----+-------+ | id | name | +----+-------+ | 2 | shuai | +----+-------+ 1 row in set (0.00 sec) #再次插入 mysql> insert into num (id) values (3); Query OK, 1 row affected (0.00 sec) mysql> select * from num; +----+-------+ | id | name | +----+-------+ | 2 | shuai | | 3 | NULL | +----+-------+ 2 rows in set (0.00 sec) #在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL 进行统计 mysql> select count(id) from num; +-----------+ | count(id) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) mysql> select count(name) from num; +-------------+ | count(name) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) #cOUNT()计算时，NULL会忽略，空值会加入计算` #插入空值 mysql> insert into num (id,name) values (4,''); #查询num数据记录 mysql> select * from num; +----+-------+ | id | name | +----+-------+ | 2 | shuai | | 3 | NULL | | 4 | | +----+-------+ 3 rows in set (0.00 sec) mysql> select count(name) from num; +-------------+ | count(name) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) #可以看出控制会被加入计算 null不会

1.71：查询name字段不为空的记录

mysql> select * from num where name is not null; +----+-------+ | id | name | +----+-------+ | 2 | shuai | | 4 | | +----+-------+ 2 rows in set (0.00 sec)

1.72：查询name字段为NULL值的记录

mysql> select * from num where name is null; +----+------+ | id | name | +----+------+ | 3 | NULL | +----+------+ 1 row in set (0.00 sec)

二：正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式。

根据指定的匹配模式匹配记录中符合要求的特殊字符使用REGEXP关键字指定匹配模式常用匹配模式

2.1：正则表达式匹配表

匹配模式描述实例^匹配文本的开始字符‘^bd’ 匹配以 bd 开头的字符串$匹配文本的结束字符‘qn$’ 匹配以 qn 结尾的字符串.匹配任何单个字符‘s.t’ 匹配任何s 和t 之间有一个字符的字符串*匹配零个或多个在它前面的字符‘fo*t’ 匹配 t 前面有任意个 o+匹配前面的字符 1 次或多次‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串字符串匹配包含指定的字符串‘clo’ 匹配含有 clo 的字符串p1|p2匹配 p1 或 p2‘bg|fg’ 匹配 bg 或者 fg[…]匹配字符集合中的任意一个字符‘[abc]’ 匹配 a 或者 b 或者 c[^…]匹配不在括号中的任何字符‘[^ab]’ 匹配不包含 a 或者 b 的字符串{n}匹配前面的字符串 n 次‘g{2}’ 匹配含有 2 个 g 的字符串{n,m}匹配前面的字符串至少 n 次，至多m 次‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

了解正则表达式的匹配规则之后，就可以将这些规则技巧应用于 SQL 语句中，从而可以更准确、更快速的查出所需的记录。下面通过示例的方式详细介绍 MySQL 正则表达式的使用方法。

示例：

打印以wa为开头的字段 mysql> select * from tt where name regexp '^wa'; +----+--------+-------+---------+-------+ | id | name | score | address | hobby | +----+--------+-------+---------+-------+ | 1 | wangwu | 70.00 | beijing | 2 | +----+--------+-------+---------+-------+ 1 row in set (0.01 sec) 打印以结尾的字段 mysql> select * from tt where name regexp 'm$'; +----+------+-------+----------+-------+ | id | name | score | address | hobby | +----+------+-------+----------+-------+ | 7 | tom | 87.00 | shanghai | 1 | +----+------+-------+----------+-------+ 1 row in set (0.00 sec)

2.11：包含指定字符串的记录

在tt表中查询包含an字符串的name字段并打印 mysql> select * from tt where name regexp 'an'; +----+----------+-------+---------+-------+ | id | name | score | address | hobby | +----+----------+-------+---------+-------+ | 1 | wangwu | 70.00 | beijing | 2 | | 3 | zhangsan | 80.00 | beijing | 1 | | 5 | tianqi | 80.00 | beijing | 1 | +----+----------+-------+---------+-------+ 3 rows in set (0.00 sec) #测试一下不连续的的是否能打印这边我们就测试hi把【shuai里的字符】 mysql> select * from tt where name regexp 'hi'; Empty set (0.00 sec) #测试是必须连续的字母不连续是不可以的

2.12：以"."代替字符串的任意一个字符的记录

在tt表查询包含字符串zh与ng，且两个字符之间只有一个字符的name字段并打印出对应的记录 mysql> select * from tt where name regexp 'zh.ng'; +----+----------+-------+---------+-------+ | id | name | score | address | hobby | +----+----------+-------+---------+-------+ | 3 | zhangsan | 80.00 | beijing | 1 | +----+----------+-------+---------+-------+ 1 row in set (0.00 sec) #查询以shu后面单个字符 mysql> select * from tt where name regexp 'shu..'; +----+---------+-------+----------+-------+ | id | name | score | address | hobby | +----+---------+-------+----------+-------+ | 6 | shuaige | 70.00 | suzhou | 2 | | 8 | shuaige | 76.00 | hangzhou | 2 | +----+---------+-------+----------+-------+ 2 rows in set (0.00 sec)

2.13：匹配包含或者关系的记录

列如，在tt表中查询包含字符串zh或者ti字段并打印对应的字段记录 mysql> select * from tt where name regexp 'zh|ti'; +----+----------+-------+---------+-------+ | id | name | score | address | hobby | +----+----------+-------+---------+-------+ | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 60.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | +----+----------+-------+---------+-------+ 3 rows in set (0.00 sec)

2.14：匹配前面字符的任意多次

列如，在tt表中查询包含三个或者更多的连续的o的name字段并打印对应的字段记录 #这边我们首先先插入数据 mysql> insert into tt (name,score,address,hobby) values ('0000',37,'tianjing',2),('000000000',88,'shanghai',3); #进行查询表数据 mysql> select * from tt; +----+-----------+-------+----------+-------+ | id | name | score | address | hobby | +----+-----------+-------+----------+-------+ | 1 | wangwu | 70.00 | beijing | 2 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 60.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 70.00 | suzhou | 2 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 76.00 | hangzhou | 2 | | 13 | 0000 | 37.00 | tianjing | 2 | | 14 | 000000000 | 88.00 | shanghai | 3 | +----+-----------+-------+----------+-------+ 10 rows in set (0.00 sec) ##匹配包含00或者更多0的字段 mysql> select * from tt where name regexp '00*'; +----+-----------+-------+----------+-------+ | id | name | score | address | hobby | +----+-----------+-------+----------+-------+ | 13 | 0000 | 37.00 | tianjing | 2 | | 14 | 000000000 | 88.00 | shanghai | 3 | +----+-----------+-------+----------+-------+ 2 rows in set (0.00 sec) #*是否能匹配？？？ mysql> select * from tt where name regexp '*'; ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp 这边*不是显示所有 0*显示前面出现一个或者多个 *是次数的含义

2.15：”+“匹配前面字符至少一次

在tt表中查询包含四个或者更多个0的name字段并打印对应记录 mysql> select * from tt where name regexp '0000*'; +----+-----------+-------+----------+-------+ | id | name | score | address | hobby | +----+-----------+-------+----------+-------+ | 13 | 0000 | 37.00 | tianjing | 2 | | 14 | 000000000 | 88.00 | shanghai | 3 | +----+-----------+-------+----------+-------+ 2 rows in set (0.00 sec)

2.16：匹配指定字符集的任意一个

在tt表中查询包含以a、s开头的name字段并打印对应的记录 mysql> select * from tt where name regexp '^[a-s]'; +----+---------+-------+----------+-------+ | id | name | score | address | hobby | +----+---------+-------+----------+-------+ | 2 | lisi | 80.00 | nanjing | 1 | | 6 | shuaige | 70.00 | suzhou | 2 | | 8 | shuaige | 76.00 | hangzhou | 2 | +----+---------+-------+----------+-------+ 3 rows in set (0.00 sec)

三：运算符

3.1：算数运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符。下面分别对这四种运算符进行说明。

用于对记录中的字段值进行运算

3.1：运算符号分类

算数运算符

比较运算符

逻辑运算符

位运算符二进制进行运算

3.11：MYSQL支持使用的运算符号

运算符描述+加法-减法*乘法/除法%取余数

例如，以 SELECT 命令来实现最基础的加减乘除运算，具体操作如下所示。

mysql> select 2+3,2*4,8%2,9/2; +-----+-----+------+--------+ | 2+3 | 2*4 | 8%2 | 9/2 | +-----+-----+------+--------+ | 5 | 8 | 0 | 4.5000 | +-----+-----+------+--------+ 1 row in set (0.00 sec) 在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序。

3.2：比较运算

字符串的比较迷人不区分大小写，可使用binary来区分

2.31：常用比较运算符

运算符描述运算符描述=等于IS NOT NULL判断一个值是否不为 NULL>大于BETWEEN AND两者之间<小于IN在集合中>=大于等于LIKE通配符匹配<=小于等于GREATEST两个或多个参数时返回最大值!=或<>不等于LEAST两个或多个参数时返回最小值IS NULL判断一个值是否为 NULLREGEXP正则表达式

下面对几种常用的比较运算进行一些讲解和举例

3.21：等于运算符

等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同。例如，等于运算符在数字、字符串和表达式上的使用，具体操作如下所示。

示例

mysql> select 2=3,3='3','shuai'='shuai',(3+4)=(2+5),'a'=NULL; +-----+-------+-----------------+-------------+----------+ | 2=3 | 3='3' | 'shuai'='shuai' | (3+4)=(2+5) | 'a'=NULL | +-----+-------+-----------------+-------------+----------+ | 0 | 1 | 1 | 1 | NULL | +-----+-------+-----------------+-------------+----------+ 1 row in set (0.00 sec)

从以上查询可以看出来：

如果两者都是整数，则按照整数值进行比较。

如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。

如果两者都是字符串，则按照字符串进行比较。

如果两者中至少有一个值是 NULL，则比较的结果是 NULL

3.22：不等于运算符

不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL。 mysql> mysqlt 'shuai'<>'chou',13!=17,NULL=NULL; +-----------------+--------+-----------+ | 'shuai'<>'chou' | 13!=17 | NULL=NULL | +-----------------+--------+-----------+ | 1 | 1 | NULL | +-----------------+--------+-----------+ 1 row in set (0.00 sec) #不等于运算符不能判断NULL

数值与字符串是否能比较？

mysql> select 'hhh'!=45; +-----------+ | 'hhh'!=45 | +-----------+ | 1 | +-----------+ 1 row in set, 1 warning (0.00 sec) mysql> select 'hhh'=45; +----------+ | 'hhh'=45 | +----------+ | 0 | +----------+ 1 row in set, 1 warning (0.00 sec)

3.23：大于、大于等于、小于、小于等于运算符

大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL。

小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL。

大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL。

小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL。

数值比较会自动转换ASCII表的数值

具体内容点击 https://baike.baidu.com/item/ASCII/309296?fr=aladdin

我们需要大体记住几个常用Dec（十进制）的就行：

0是48

大写A是65 B是66 依次往后推算

小写a是97 b是98

mysql> mysql> select 5>3,'a'>'b',3>=4,(5+6)>=(3+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL; +-----+---------+------+--------------+-------+-----+----------+--------+-----------+ | 5>3 | 'a'>'b' | 3>=4 | (5+6)>=(3+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=NULL | +-----+---------+------+--------------+-------+-----+----------+--------+-----------+ | 1 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | NULL | +-----+---------+------+--------------+-------+-----+----------+--------+-----------+ 1 row in set (0.00 sec)

3.24： IS NULL、IS NOT NULL

SNULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0。IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0

具体操作如下

mysql> select 2 is NULL,'d' is not NULL,NULL IS NULL; +-----------+-----------------+--------------+ | 2 is NULL | 'd' is not NULL | NULL IS NULL | +-----------+-----------------+--------------+ | 0 | 1 | 1 | +-----------+-----------------+--------------+ 1 row in set (0.00 sec)

3.25：BETWEEN AND

BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间

示例

mysql> select 5 between 2 and 8,7 between 5 and 10,'d' between 'a' and 'z'; +-------------------+--------------------+-------------------------+ | 5 between 2 and 8 | 7 between 5 and 10 | 'd' between 'a' and 'z' | +-------------------+--------------------+-------------------------+ | 1 | 1 | 1 | +-------------------+--------------------+-------------------------+ 1 row in set (0.00 sec) 数字能否与字符比较？ mysql> select 5 between 2 and 8,7 between 5 and 10,8 between 'a' and 'z'; +-------------------+--------------------+-----------------------+ | 5 between 2 and 8 | 7 between 5 and 10 | 8 between 'a' and 'z' | +-------------------+--------------------+-----------------------+ | 1 | 1 | 0 | +-------------------+--------------------+-----------------------+ 1 row in set, 2 warnings (0.00 sec) #数字不能与字符比较的

3.26：LEAST、GREATEST

LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。

GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL，则返回结果就为 NULL。

具体操作如下

mysql> SELECT least(1,2,3),least('a','d','f'),greatest(1,2,3),greatest('a','d','f'); +--------------+--------------------+-----------------+-----------------------+ | least(1,2,3) | least('a','d','f') | greatest(1,2,3) | greatest('a','d','f') | +--------------+--------------------+-----------------+-----------------------+ | 1 | a | 3 | f | +--------------+--------------------+-----------------+-----------------------+ 1 row in set (0.00 sec) min是否能求最小值呢 mysql> select min(2,3,4),min('d','e','f'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3,4),min('d','e','f')' at line 1 #对了是不能的 min后面只能跟字段名称

3.27：IN、NOT IN

IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。

NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0。

mysql> select 2 in (1,2,3,4,5),'c' not in ('a','b','c'); +------------------+--------------------------+ | 2 in (1,2,3,4,5) | 'c' not in ('a','b','c') | +------------------+--------------------------+ | 1 | 0 | +------------------+--------------------------+ 1 row in set (0.00 sec)

从以上结果可以看出，IN 和 NOT IN 的返回值正好相反。

3.28：LIKE、NOT LIKE

LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0。LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0。 mysql> select 'shuai' like 'shu_','xiao' like '%k','lisi' not like '%a'; +---------------------+------------------+----------------------+ | 'shuai' like 'shu_' | 'xiao' like '%k' | 'lisi' not like '%a' | +---------------------+------------------+----------------------+ | 0 | 0 | 1 | +---------------------+------------------+----------------------+ 1 row in set (0.00 sec)

3.3：逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 TRUE 和 FALSE 表示。

又被称布尔值运算符

用来判断表达式的真假

3.31：常用的逻辑运算符号

运算符描述NOT 或！逻辑非AND 或 &&逻辑与OR 或 ||逻辑或XOR逻辑异或

3.32：逻辑非

逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。例如，对非 0 值和 0 值分别作逻辑非运算，具体操作如下所示。 mysql> select not 0,not 2,!4,!(2-2); +-------+-------+----+--------+ | not 0 | not 2 | !4 | !(2-2) | +-------+-------+----+--------+ | 1 | 0 | 0 | 1 | +-------+-------+----+--------+ 1 row in set (0.00 sec)

3.33：逻辑与

逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算，具体操作如下所示。 #两个都是非0值出来结果就是1 区分是按照非0值与0区分的 mysql> select 3 and 5,0 and 8,0 and NULL,1 and NULL; +---------+---------+------------+------------+ | 3 and 5 | 0 and 8 | 0 and NULL | 1 and NULL | +---------+---------+------------+------------+ | 1 | 0 | 0 | NULL | +---------+---------+------------+------------+ 1 row in set (0.00 sec)

3.34：逻辑或

逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算。

一旦有个非0值，出来就是非0值

mysql> select 3 OR 5,0 OR 8,0 OR NULL,1 OR NULL; +--------+--------+-----------+-----------+ | 3 OR 5 | 0 OR 8 | 0 OR NULL | 1 OR NULL | +--------+--------+-----------+-----------+ | 1 | 1 | NULL | 1 | +--------+--------+-----------+-----------+ 1 row in set (0.00 sec)

3.35:逻辑异或

两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0，另一个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL。例如，对非0 值、0 值和 NULL 值分别作逻辑异或运算，具体操作如下所示。 mysql> SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL; +---------+---------+---------+------------+---------------+ | 2 XOR 3 | 0 XOR 0 | 0 XOR 5 | 1 XOR NULL | NULL XOR NULL | +---------+---------+---------+------------+---------------+ | 0 | 0 | 1 | NULL | NULL | +---------+---------+---------+------------+---------------+ 1 row in set (0.00 sec)

3.4：位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。

3.41：MYSQL支持的运算符

运算符描述&按位与|按位或~按位取反^按位异或<<按位左移>>按位右移

示例：对数字进行按位与、或和取反运算

mysql> select 10&15,10|15,10^15,5 &~1; +-------+-------+-------+-------+ | 10&15 | 10|15 | 10^15 | 5 &~1 | +-------+-------+-------+-------+ | 10 | 15 | 5 | 4 | +-------+-------+-------+-------+ 1 row in set (0.00 sec)

下面是图解

按位与运算（&），是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10。

按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0，所以 10 | 15 的结果为 15。

按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15的结果为 5。

按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101进行求与操作，其结果是二进制的 0100，转换为十进制就是 4。

列如对数字进行左移动或右移的运算

mysql> select 1<<2; +------+ | 1<<2 | +------+ | 4 | +------+ 1 row in set (0.01 sec) mysql> select 10<<2; +-------+ | 10<<2 | +-------+ | 40 | +-------+ 1 row in set (0.00 sec) <<左移位运算向左移动两位，空缺出补0 1<<2 0001 0100 左移后 10<<2 1010 101000 左移后

3.42：运算符的优先级

以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算。如果不确定所使用的运算符的优先级，可以使用（）改变优先级。优先级运算符优优先级运算符1！8|2~9=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN3^10BETWEEN,CASE,WHEN,THEN,ELSE4* , /(DIV), %(MOD)11NOT5+,-12&&,AND6>>,<<13||,OR,XOR7&14:=

3.4：连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接。

为了便于理解，这里使用两个表tt和gg老进行演示

mysql> select * from tt; +----+-----------+-------+----------+-------+ | id | name | score | address | hobby | +----+-----------+-------+----------+-------+ | 1 | wangwu | 70.00 | beijing | 2 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 60.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 70.00 | suzhou | 2 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 76.00 | hangzhou | 2 | | 13 | 0000 | 37.00 | tianjing | 2 | | 14 | 000000000 | 88.00 | shanghai | 3 | | 15 | aaa | 66.00 | wuchang | 0 | | 16 | bbb | 45.00 | sichuan | 0 | +----+-----------+-------+----------+-------+ 12 rows in set (0.00 sec) mysql> select * from gg; +----+-----------+ | id | gg_name | +----+-----------+ | 1 | 小当家 | | 2 | 小神龙 | | 3 | 小福贵 | | 4 | 小可爱 | +----+-----------+ 4 rows in set (0.00 sec)

3.41：内连接

内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。

列如，在刚刚两个表的tt表和gg表内使用内连接查询

mysql> select T.name,G.gg_name from tt T inner join gg G on T.hobby=G.id; +-----------+-----------+ | name | gg_name | +-----------+-----------+ | wangwu | 小神龙 | | lisi | 小当家 | | zhangsan | 小当家 | | zhaoliu | 小神龙 | | tianqi | 小当家 | | shuaige | 小神龙 | | tom | 小当家 | | shuaige | 小神龙 | | 0000 | 小神龙 | | 000000000 | 小福贵 | +-----------+-----------+ 10 rows in set (0.00 sec)

上述操作是将 tt表的每一行与 gg表的每一行进行比较，以检查它们是否都满足条件。当满足条件时，INNER JOIN 将返回由 tt表和 gg表中的列组成的新行。如果没有匹配项，查询将返回一个空的结果集。

具体的INNER JOIN工作原理

如果表中至少有一个匹配项，INNER JOIN 关键字将返回一行。如果没有匹配项，则不会有输出结果。

3.42：左连接

MySQL 除了内连接，还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分，有左连接和右连接之分。

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

查询出tt表的左右内容，并查询出T.hobby和G.id相等判断的gg中的部分

mysql> select T.name,G.gg_name from tt T left join gg G on T.hobby=G.id; +-----------+-----------+ | name | gg_name | +-----------+-----------+ | lisi | 小当家 | | zhangsan | 小当家 | | tianqi | 小当家 | | tom | 小当家 | | wangwu | 小神龙 | | zhaoliu | 小神龙 | | shuaige | 小神龙 | | shuaige | 小神龙 | | 0000 | 小神龙 | | 000000000 | 小福贵 | | aaa | NULL | | bbb | NULL | +-----------+-----------+ 12 rows in set (0.01 sec) 主表全部显示

从以上左连接查询结果可以看出，除了匹配的行，还包括了左表中有但右表中没有的行，如果有表没有对应值，则使用 NULL 代替。也就是说在左连接查询中，使用 NULL 值表示右表中没有找到与左表中匹配的记录。

3.43：右连接

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录 mysql> mysql> select T.name,G.gg_name from tt T right join gg G on T.hobby=G.id; +-----------+-----------+ | name | gg_name | +-----------+-----------+ | wangwu | 小神龙 | | lisi | 小当家 | | zhangsan | 小当家 | | zhaoliu | 小神龙 | | tianqi | 小当家 | | shuaige | 小神龙 | | tom | 小当家 | | shuaige | 小神龙 | | 0000 | 小神龙 | | 000000000 | 小福贵 | | NULL | 小可爱 | +-----------+-----------+ 11 rows in set (0.00 sec) #右边所有信息都显示（tt）左表是辅助

在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹配的行，这些记录在左表中以 NULL 补足。

四：数据库函数

MYSQL提供了实现各种功能的函数

4.1：常用的函数分类

数学函数

聚合函数

字符串函数

日期时间函数

4.2：数学函数

abs（x）返回x的绝对值 mysql> select abs(-2),abs(-3.14); +---------+------------+ | abs(-2) | abs(-3.14) | +---------+------------+ | 2 | 3.14 | +---------+------------+ 1 row in set (0.00 sec) mysql> select abs(2),abs(-3.14); +--------+------------+ | abs(2) | abs(-3.14) | +--------+------------+ | 2 | 3.14 | +--------+------------+ 1 row in set (0.00 sec) rand（）返回0到1的随机数 mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.9606223953521088 | +--------------------+ 1 row in set (0.00 sec) 取值范围：0<=x<1 mod（x,y）返回x除以y以后的余数 mysql> select mod(10,2); +-----------+ | mod(10,2) | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql> select mod(10,3); +-----------+ | mod(10,3) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) power（x，y）返回x的y次方 mysql> select power(3,3); +------------+ | power(3,3) | +------------+ | 27 | +------------+ 1 row in set (0.00 sec) 3的3次方 mysql> select power(2,3); +------------+ | power(2,3) | +------------+ | 8 | +------------+ 1 row in set (0.00 sec) round（x）返回离x最近的整数 #返回的是整数四舍五入 mysql> select round(2.49); +-------------+ | round(2.49) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) mysql> select round(2.59); +-------------+ | round(2.59) | +-------------+ | 3 | +-------------+ 1 row in set (0.01 sec) mysql> select round(1.49,1); +---------------+ | round(1.49,1) | +---------------+ | 1.5 | +---------------+ 1 row in set (0.00 sec) mysql> select round(1.49,2); +---------------+ | round(1.49,2) | +---------------+ | 1.49 | +---------------+ 1 row in set (0.00 sec) sqrt（x）返回x的平方跟 mysql> select sqrt(4); +---------+ | sqrt(4) | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) mysql> select sqrt(9); +---------+ | sqrt(9) | +---------+ | 3 | +---------+ 1 row in set (0.00 sec) truncate(x,y)返回数字x截断为y位小数的值 mysql> select truncate(3.1415926,3); +-----------------------+ | truncate(3.1415926,3) | +-----------------------+ | 3.141 | +-----------------------+ 1 row in set (0.00 sec) ceil（x）返回大于或等于x的最小整数 mysql> select ceil(1.4); +-----------+ | ceil(1.4) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) mysql> select ceil(1.9); +-----------+ | ceil(1.9) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) floor（x）返回小于或等于x的最大整数 mysql> select floor(1.99); +-------------+ | floor(1.99) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select floor(1.09); +-------------+ | floor(1.09) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) greates（x1，x2…)返回集合中最大得值least（x1,x2…)返回集合中最小的值 mysql> select greatest(11,12,13),least(10,12,14); +--------------------+-----------------+ | greatest(11,12,13) | least(10,12,14) | +--------------------+-----------------+ | 13 | 10 | +--------------------+-----------------+ 1 row in set (0.0 sec)

4.4：字符串函数

length（x）返回字符串x的值

#空格也算长度 mysql> select length('aaaa'); +----------------+ | length('aaaa') | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) mysql> select length('aa aa'); +-----------------+ | length('aa aa') | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec)

trim（）返回去除指定格式的值

concat（x，y）将提供的参数x和y拼接成一个字符串

字符串有空格进行去除相连

mysql> select concat('asd',trim(' qwe')); +-----------------------------+ | concat('asd',trim(' qwe')) | +-----------------------------+ | asdqwe | +-----------------------------+ 1 row in set (0.00 sec)

upper（x）将字符串x的所有字母变为大写字母

lower（x）将字符串x的所有字母变为小写字母

mysql> select upper('abc'),lower('QWE'); +--------------+--------------+ | upper('abc') | lower('QWE') | +--------------+--------------+ | ABC | qwe | +--------------+--------------+ 1 row in set (0.00 sec)

left（x，y）返回字符串的前y个字符

right（x，y）返回字符的后y个字符

截图前3个字符 mysql> select left('abcert',3); +------------------+ | left('abcert',3) | +------------------+ | abc | +------------------+ 1 row in set (0.00 sec) 截取后3 mysql> select right('abcert',3); +-------------------+ | right('abcert',3) | +-------------------+ | ert | +-------------------+ 1 row in set (0.00 sec) 怎么去除中间的c字母？？ mysql> select concat(left('abcert',3),right('abcert',3)); +--------------------------------------------+ | concat(left('abcert',3),right('abcert',3)) | +--------------------------------------------+ | abcert | +--------------------------------------------+ 1 row in set (0.00 sec)

repeat（x，y）将字符串重复y次

mysql> select repeat('qwer',3); +------------------+ | repeat('qwer',3) | +------------------+ | qwerqwerqwer | +------------------+ 1 row in set (0.00 sec)

space（x）返回x个空格

#做一个内嵌

replace（x，y，z）将字符串z代替字符串x中的字符y

mysql> select replace('shuai','ua','xx'); +----------------------------+ | replace('shuai','ua','xx') | +----------------------------+ | shxxi | +----------------------------+ 1 row in set (0.00 sec)

strcmp（x，y）比较x和y，返回的值可以为-1，0，1

mysql> select strcmp(14,5); +--------------+ | strcmp(14,5) | +--------------+ | -1 | +--------------+ 1 row in set (0.00 sec) mysql> select strcmp(14,14); +---------------+ | strcmp(14,14) | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) mysql> select strcmp(27,7); +--------------+ | strcmp(27,7) | +--------------+ | -1 | +--------------+ 1 row in set (0.00 sec) #比较的是第一个数

substring（x，y，z）获取从字符串的x中的第y个位置开始长度为z的字符串

mysql> select substring('abcdefg',2,3); +--------------------------+ | substring('abcdefg',2,3) | +--------------------------+ | bcd | +--------------------------+ 1 row in set (0.00 sec) #substring（完整字符串，起始位置，长度）；

reverse（x）将字符串x反转

#倒过来输出 mysql> select reverse ('abc'); +-----------------+ | reverse ('abc') | +-----------------+ | cba | +-----------------+ 1 row in set (0.00 sec) #反过来大写 mysql> select upper(reverse ('abc')); +------------------------+ | upper(reverse ('abc')) | +------------------------+ | CBA | +------------------------+ 1 row in set (0.00 sec)

4.5：日期时间函数

4.51：常用的日期时间函数

curdate（)返回当前时间的年月日）

mysql> select curdate(); +------------+ | curdate() | +------------+ | 2020-08-25 | +------------+ 1 row in set (0.00 sec)

curtime（）返回当前时间的时分秒

mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 16:34:44 | +-----------+ 1 row in set (0.00 sec)

now（）返回当前时间的日期和时间

mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-08-25 16:35:53 | +---------------------+ 1 row in set (0.00 sec)

mount（x）返日期x中的月份值

mysql> select month('2020-08-25'); +---------------------+ | month('2020-08-25') | +---------------------+ | 8 | +---------------------+ 1 row in set (0.00 sec)

week（x）返回日期x是年度第几个星期

mysql> select week('2020-08-25'); +--------------------+ | week('2020-08-25') | +--------------------+ | 34 | +--------------------+ 1 row in set (0.00 sec)

hour（x）返回x中的小时值

mysql> select hour('16:35:53'); +------------------+ | hour('16:35:53') | +------------------+ | 16 | +------------------+ 1 row in set (0.00 sec)

显示当前的小时/分钟/秒

mysql> select hour(curtime()); +-----------------+ | hour(curtime()) | +-----------------+ | 16 | +-----------------+ 1 row in set (0.00 sec) #小时 mysql> select minute(curtime()); +-------------------+ | minute(curtime()) | +-------------------+ | 41 | +-------------------+ 1 row in set (0.00 sec #显示当前的秒

dayofweek（x）返回x是星期几，1星期日，2是星期一

mysql> select dayofweek(curdate()); +----------------------+ | dayofweek(curdate()) | +----------------------+ | 3 | +----------------------+ 1 row in set (0.00 sec)

dayofmonth（x）计算日期x是本月的第几天

mysql> select dayofmonth(curdate()); +-----------------------+ | dayofmonth(curdate()) | +-----------------------+ | 25 | +-----------------------+ 1 row in set (0.00 sec)

dayodyear（x）计算日期x是本年的第几天

mysql> select dayofyear(curdate()); +----------------------+ | dayofyear(curdate()) | +----------------------+ | 238 | +----------------------+ 1 row in set (0.00 sec)

五：存储过程简介

存储过程简介是一组为了完成特定功能的SQL语句集合比传统SQL速度更快、执行效率更高存储过程的优点执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率 SQL语句加上控制语句的集合，灵活性高在服务器端存储，客户端调用时，降低网络负载可多次重复被调用，可随时修改，不影响客户端调用可完成所有的数据库操作，也可控制数据库的信息访问权限

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cuhGa48b-1599059515276)(C:\Users\liu\AppData\Roaming\Typora\typora-user-images\image-20200825184206771.png)]

减轻网络负载

增加安全性

5.1：创建存储过程的语法结构

使用CREATE PROCEDURE语句创建存储过程

创建存储过程的语法结构

CREATE PROCEDURE<过程名>([过程参数[…..]])<过程体> [过程参数[.-..]]格式 IN| ouT|lNouTj参数名><类型> 参数分为输入参数:IN 输出参数:OUT 输入/输出参数:INOUT

5.11：创建存储过程

存储过程主题部分，被称为过程体

以BEGIN开始，以END结束，若只有一条SQL语句，则可以省略BEGIN-END

以DELIMITER开始和结束

mysql>DELIMITER $$ //$$是用户自定义的结束符 //省略存储过程其他步骤 mysql> DELIMITER; //分号前有空格

5.12存储过程的语法结构

#定义存储过程

delimiter $$ create procedure存储过程名(in 参数名参数类型) begin

#定义变量

declare变量名变量类型 #变量赋值 set变量名=值 sql语句1; sql语句2; … sql语句n; end$ delimiter;

#调用存储过程

call存储过程名(实际参数);

#查询存储过程

show procedure status where db='数据库';

#删除存储过程

drop procedure存储过程名;

实例：

5.13：存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名数据类形...]) IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

查看指定用户信息

加入控制语句

需求：输入一个成绩

把lisi进行分组输入75分以上被分配到第一组 75以下改为第二组

mysql> create procedure CC(in my_score decimal(5,2)) -> begin -> declare hob int(3); -> if my_score >=75 then -> set hob = 1; -> else -> set hob = 2; -> end if; -> update tt set score=my_score,hobby=hob where name='lisi'; -> end$$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ;

调用存储

mysql> select * from tt; +----+----------+-------+----------+-------+ | id | name | score | address | hobby | +----+----------+-------+----------+-------+ | 1 | wangwu | 75.00 | beijing | 2 | | 2 | lisi | 80.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 65.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 75.00 | suzhou | 2 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 81.00 | hangzhou | 2 | +----+----------+-------+----------+-------+ 8 rows in set (0.00 sec) #我们设置成绩76分看一下分组情况 mysql> call CC(76); Query OK, 1 row affected (0.00 sec) mysql> select * from tt; +----+----------+-------+----------+-------+ | id | name | score | address | hobby | +----+----------+-------+----------+-------+ | 1 | wangwu | 75.00 | beijing | 2 | | 2 | lisi | 76.00 | nanjing | 1 | | 3 | zhangsan | 80.00 | beijing | 1 | | 4 | zhaoliu | 65.00 | nanjing | 2 | | 5 | tianqi | 80.00 | beijing | 1 | | 6 | shuaige | 75.00 | suzhou | 2 | | 7 | tom | 87.00 | shanghai | 1 | | 8 | shuaige | 81.00 | hangzhou | 2 | +----+----------+-------+----------+-------+ 8 rows in set (0.00 sec) #以改好

内容较多，便于自己学习回顾

最新回复(0)