目录

一、MySQL进价查询1.1按关键字排序1.1.1 单字段排序1.1.2 多字段排序 1.2 对结果进行分组1.2.1 GROUP BY分组1.2.2 GROUP BY结合ORDER BY 1.3限制结果条目LIMIT1.4 设置别名1.5 通配符1.6 子查询1.7 视图1.8 NULL值1.9 正则表达式1.10 运算符1.10.1 算数运算符1.10.2 比较运算1.10.2.1 等于运算符1.10.2.2 不等于运算符1.10.2.3 大于、大于等于、小于、小于等于运算符1.10.2.4 IS NULL、IS NOT NULL1.10.2.5 BETWEEN AND1.10.2.6 LEAST、GREATEST1.10.2.7 IN、NOT IN1.10.2.8 IN、NOT IN 1.10.3 逻辑运算符1.10.3.1 逻辑非1.10.3.2 逻辑与1.10.3.3 逻辑或1.10.3.4 逻辑异或 1.10.4 位运算符1.10.5 运算符的优先级 1.11 连接查询1.11.1 内连接1.11.2 左连接1.11.3 右连接 二、MySQL数据库函数2.1 数据库函数2.2 数学函数2.3 聚合函数2.4 字符串函数2.5 日期时间函数 三、 存储过程3.1 创建存储过程语法3.2 存储过程的参数3.3 MySQL存储过程的控制语句

一、MySQL进价查询

1.1按关键字排序

■ 使用ORDERBY语句来实现排序

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

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

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

■ ORDER BY的语法结构

■ ORDER BY后面跟字段名

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

例：先准备一张表

mysql> create table info (id int(4) auto_increment primary key,name varchar(10) not null,score int(4)); Query OK, 0 rows affected (0.02 sec) 插入数据后 mysql> select * from info; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 1 | zhangsan | 80 | | 2 | lisi | 88 | | 3 | wangwu | 90 | | 4 | zhaoliu | 67 | | 5 | zhaosi | 77 | | 6 | zhouliu | 83 | +----+----------+-------+ 6 rows in set (0.00 sec)

1.1.1 单字段排序

不加排列顺序，默认是升序排列

mysql> select * from info order by score; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 4 | zhaoliu | 67 | | 5 | zhaosi | 77 | | 1 | zhangsan | 80 | | 6 | zhouliu | 83 | | 2 | lisi | 88 | | 3 | wangwu | 90 | +----+----------+-------+ 6 rows in set (0.00 sec)

加desc 降序排列

mysql> select * from info order by score desc; +----+----------+-------+ | id | name | score | +----+----------+-------+ | 3 | wangwu | 90 | | 2 | lisi | 88 | | 6 | zhouliu | 83 | | 1 | zhangsan | 80 | | 5 | zhaosi | 77 | | 4 | zhaoliu | 67 | +----+----------+-------+ 6 rows in set (0.01 sec)

1.1.2 多字段排序

默认状态

mysql> select * from info; +----+----------+-------+-------+ | id | name | score | hobby | +----+----------+-------+-------+ | 1 | zhangsan | 80 | 2 | | 2 | lisi | 88 | 3 | | 3 | wangwu | 90 | 1 | | 4 | zhaoliu | 67 | 1 | | 5 | zhaosi | 77 | 2 | | 6 | zhouliu | 83 | 1 | +----+----------+-------+-------+ 6 rows in set (0.00 sec) mysql> select * from info where 2=2 order by hobby desc,score desc; +----+----------+-------+-------+ | id | name | score | hobby | +----+----------+-------+-------+ | 2 | lisi | 88 | 3 | | 1 | zhangsan | 80 | 2 | | 5 | zhaosi | 77 | 2 | | 3 | wangwu | 90 | 1 | | 6 | zhouliu | 83 | 1 | | 4 | zhaoliu | 67 | 1 | +----+----------+-------+-------+ 6 rows in set (0.00 sec) 只有第一个字段相同的情况下，第二字段排序才有意义

1.2 对结果进行分组

■ 使用GROUP BY语句来实现分组

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

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

■ GROUP BY的语法结构

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

1.2.1 GROUP BY分组

按hobby进行分组，统计每组的name个数 mysql> select count(name),hobby from info group by hobby; +-------------+-------+ | count(name) | hobby | +-------------+-------+ | 3 | 1 | | 2 | 2 | | 1 | 3 | +-------------+-------+ 3 rows in set (0.00 sec)

1.2.2 GROUP BY结合ORDER BY

mysql> select count(name),hobby from info group by hobby order by count(name) desc; +-------------+-------+ | count(name) | hobby | +-------------+-------+ | 3 | 1 | | 2 | 2 | | 1 | 3 | +-------------+-------+ 3 rows in set (0.00 sec)

1.3限制结果条目LIMIT

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

■ 使用LIMIT语句限制条目

■ LIMIT语法结构

SELECT column1,column2,...FROM table_name LIMIT[offset,] number; number：返回记录行的最大数目 [offset,]：位置偏移量，从0开始 查看前三行 mysql> select * from info limit 3; +----+----------+-------+-------+ | id | name | score | hobby | +----+----------+-------+-------+ | 1 | zhangsan | 80 | 2 | | 2 | lisi | 88 | 3 | | 3 | wangwu | 90 | 1 | +----+----------+-------+-------+ 3 rows in set (0.00 sec) 查看4-6行 mysql> select * from info limit 3,3; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 4 | zhaoliu | 67 | 1 | | 5 | zhaosi | 77 | 2 | | 6 | zhouliu | 83 | 1 | +----+---------+-------+-------+ 3 rows in set (0.00 sec)

1.4 设置别名

■ 使用AS语句设置别名，关键字AS可省略 ■ 设置别名时，保证不能与库中其他表或字段名称冲突 ■ 别名的语法结构

SELECT column_name AS alias_name FROM table_name; SELECT column_name(s)FROM table_name ASalias_name;

■ AS的用法 对于字段设置

给name和score设置别名 mysql> select name as 姓名,score as 成绩 from info; +----------+--------+ | 姓名 | 成绩 | +----------+--------+ | zhangsan | 80 | | lisi | 88 | | wangwu | 90 | | zhaoliu | 67 | | zhaosi | 77 | | zhouliu | 83 | +----------+--------+ 6 rows in set (0.00 sec)

不加as语法也可以设置别名

mysql> select name 姓名,score 成绩 from info; +----------+--------+ | 姓名 | 成绩 | +----------+--------+ | zhangsan | 80 | | lisi | 88 | | wangwu | 90 | | zhaoliu | 67 | | zhaosi | 77 | | zhouliu | 83 | +----------+--------+ 6 rows in set (0.00 sec)

对于表设置，一般在多表查询时使用

给info表设置别名i，然后在name和score前面也要加i.使用，不加as也可以使用 mysql> select i.name as 姓名,i.score as 成绩 from info as i; +----------+--------+ | 姓名 | 成绩 | +----------+--------+ | zhangsan | 80 | | lisi | 88 | | wangwu | 90 | | zhaoliu | 67 | | zhaosi | 77 | | zhouliu | 83 | +----------+--------+ 6 rows in set (0.00 sec)

作为连接语句

创建good新表，将info表的 score字段>=85的数据放在新表good上 mysql> create table good as select * from info where score >=85; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 可以看到主键、自增约束都没有了， mysql> desc good; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | NO | | 0 | | | name | varchar(10) | NO | | NULL | | | score | int(4) | YES | | NULL | | | hobby | int(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 数据导过去了 mysql> select * from good; +----+--------+-------+-------+ | id | name | score | hobby | +----+--------+-------+-------+ | 2 | lisi | 88 | 3 | | 3 | wangwu | 90 | 1 | +----+--------+-------+-------+ 2 rows in set (0.00 sec)

1.5 通配符

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

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

■ 常用的通配符

● %表示零个、一个或多个

● _表示单个字符

查询l开头的，%表示零个、一个或多个 mysql> select * from info where name like 'l%'; +----+------+-------+-------+ | id | name | score | hobby | +----+------+-------+-------+ | 2 | lisi | 88 | 3 | +----+------+-------+-------+ 1 row in set (0.00 sec) _下划线代表单个字符 mysql> select * from info where name like '_i_i'; +----+------+-------+-------+ | id | name | score | hobby | +----+------+-------+-------+ | 2 | lisi | 88 | 3 | +----+------+-------+-------+ 1 row in set (0.00 sec)

1.6 子查询

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

先建一个num表，里面只有id，在id字段在添加一些数据 mysql> select * from num; +------+ | id | +------+ | 1 | | 3 | | 5 | | 7 | +------+ 4 rows in set (0.00 sec) 然后根据刚刚的info表和num表进行多表相连，按照num表的1、3、5、7显示出info表的1、3、5、7行数据 从最内的括号开始，括号内的结果是括号外的条件 注意！！！括号内作为结果输出的数据类型要与括号外的一样，不然无法查询。 mysql> select * from info where id in(select id from num); +----+----------+-------+-------+ | id | name | score | hobby | +----+----------+-------+-------+ | 1 | zhangsan | 80 | 2 | | 3 | wangwu | 90 | 1 | | 5 | zhaosi | 77 | 2 | +----+----------+-------+-------+ 多层嵌套，从内部括号到外面匹配 mysql> select * from info where id in(select id from num where name in(select name from num)); +----+----------+-------+-------+ | id | name | score | hobby | +----+----------+-------+-------+ | 1 | zhangsan | 80 | 2 | | 3 | wangwu | 90 | 1 | | 5 | zhaosi | 77 | 2 | +----+----------+-------+-------+ 3 rows in set (0.00 sec)

1.7 视图

■ 数据库中的虚拟表，这张虚拟表中不包含任何数据，只是做了数据映射；

创建视图v_score表 mysql> create view v_score as select * from info where score >=80; 查看一下刚刚创建的视图 mysql> select * from v_score; +----+----------+-------+-------+ | id | name | score | hobby | +----+----------+-------+-------+ | 1 | zhangsan | 80 | 2 | | 2 | lisi | 88 | 3 | | 3 | wangwu | 90 | 1 | | 6 | zhouliu | 83 | 1 | +----+----------+-------+-------+ 4 rows in set (0.00 sec) mysql> show table status; ###查看视图表的信息

1.8 NULL值

■ 表示缺失的值 ■ 与数字0或者空白(spaces）是不同的 ■ 使用IS NULL或IS NOT NULL进行判断

NULL与空值的区别： 空值长度为0，不占空间；NULL值的长度为NULL，占用空间 IS NULL无法判断空值 空值使用“=”或者“<>”来处理 COUNT（）计算时，NULL会忽略，不加入计算，空值会加入计算

1.9 正则表达式

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

匹配模式描述实例^匹配文本的开始字符‘^bd’ 匹配以 bd 开头的字符串$匹配文本的结束字符‘qn$’ 匹配以 qn 结尾的字符串.匹配任何单个字符‘s.t’ 匹配任何s 和t 之间有一个字符的字符串*匹配零个或多个在它前面的字符‘fo*t’ 匹配 t 前面有任意个 o+匹配前面的字符 1 次或多次‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串字符串匹配包含指定的字符串‘clo’ 匹配含有 clo 的字符串p1p2匹配 p1 或 p2[…]匹配字符集合中的任意一个字符‘[abc]’ 匹配 a 或者 b 或者 c[^…]匹配不在括号中的任何字符‘[^ab]’ 匹配不包含 a 或者 b 的字符串{n}匹配前面的字符串 n 次‘g{2}’ 匹配含有 2 个 g 的字符串{n,m}匹配前面的字符串至少 n 次，至多m 次‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次 查询以z开头的 mysql> select * from info where name regexp '^z'; +----+----------+-------+-------+ | id | name | score | hobby | +----+----------+-------+-------+ | 1 | zhangsan | 80 | 2 | | 4 | zhaoliu | 67 | 1 | | 5 | zhaosi | 77 | 2 | | 6 | zhouliu | 83 | 1 | +----+----------+-------+-------+ 4 rows in set (0.00 sec) 查询以wu为结尾的 mysql> select * from info where name regexp 'iu$'; +----+---------+-------+-------+ | id | name | score | hobby | +----+---------+-------+-------+ | 4 | zhaoliu | 67 | 1 | | 6 | zhouliu | 83 | 1 | +----+---------+-------+-------+ 2 rows in set (0.00 sec) .代表任意字符，查询zh.ngsan的记录 mysql> select * from info where name regexp 'zh.ngsan'; +----+----------+-------+-------+ | id | name | score | hobby | +----+----------+-------+-------+ | 1 | zhangsan | 80 | 2 | +----+----------+-------+-------+ 1 row in set (0.00 sec)

在原表上做一些结构与数据的调整

mysql> select * from info; +----+----------+-------+-------+----------+ | id | name | score | hobby | addr | +----+----------+-------+-------+----------+ | 1 | zhangsan | 80 | 2 | NULL | | 2 | lisi | 88 | 3 | NULL | | 3 | wangwu | 90 | 1 | NULL | | 4 | zhaoliu | 67 | 1 | NULL | | 5 | zhaosi | 77 | 2 | NULL | | 6 | zhouliu | 83 | 1 | NULL | | 7 | woo | 79 | 2 | shanghai | | 8 | wwwwo | 65 | 2 | hangzhou | | 9 | woww | 84 | 3 | hangzhou | +----+----------+-------+-------+----------+ 9 rows in set (0.00 sec) 查询w w任意个前面的字符，零次或多次 mysql> select * from info where name regexp 'ww*'; +----+--------+-------+-------+----------+ | id | name | score | hobby | addr | +----+--------+-------+-------+----------+ | 3 | wangwu | 90 | 1 | NULL | | 7 | woo | 79 | 2 | shanghai | | 8 | wwwwo | 65 | 2 | hangzhou | | 9 | woww | 84 | 3 | hangzhou | +----+--------+-------+-------+----------+ 4 rows in set (0.00 sec) 查询ow前面字符至少一次 mysql> select * from info where name regexp 'ow+'; +----+------+-------+-------+----------+ | id | name | score | hobby | addr | +----+------+-------+-------+----------+ | 9 | woww | 84 | 3 | hangzhou | +----+------+-------+-------+----------+ 1 row in set (0.00 sec) 查询z开头的 mysql> select * from info where name regexp '^[z]'; +----+----------+-------+-------+------+ | id | name | score | hobby | addr | +----+----------+-------+-------+------+ | 1 | zhangsan | 80 | 2 | NULL | | 4 | zhaoliu | 67 | 1 | NULL | | 5 | zhaosi | 77 | 2 | NULL | | 6 | zhouliu | 83 | 1 | NULL | +----+----------+-------+-------+------+ 4 rows in set (0.00 sec) 查询不是z开头的 mysql> select * from info where name regexp '^[^z]'; +----+--------+-------+-------+----------+ | id | name | score | hobby | addr | +----+--------+-------+-------+----------+ | 2 | lisi | 88 | 3 | NULL | | 3 | wangwu | 90 | 1 | NULL | | 7 | woo | 79 | 2 | shanghai | | 8 | wwwwo | 65 | 2 | hangzhou | | 9 | woww | 84 | 3 | hangzhou | +----+--------+-------+-------+----------+ 5 rows in set (0.00 sec) 匹配w及后面一个w两次 mysql> select * from info where name regexp 'ww{2}'; +----+-------+-------+-------+----------+ | id | name | score | hobby | addr | +----+-------+-------+-------+----------+ | 8 | wwwwo | 65 | 2 | hangzhou | +----+-------+-------+-------+----------+

1.10 运算符

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

1.10.1 算数运算符

MYSQL支持使用的运算符号 运算符描述+加法-减法*乘法/除法%取余数 mysql> select 2+2,5-2,4*3,9/4,9%4; +-----+-----+-----+--------+------+ | 2+2 | 5-2 | 4*3 | 9/4 | 9%4 | +-----+-----+-----+--------+------+ | 4 | 3 | 12 | 2.2500 | 1 | +-----+-----+-----+--------+------+ 1 row in set (0.00 sec)

1.10.2 比较运算

常用比较运算符

1.10.2.1 等于运算符

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

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

1.10.2.2 不等于运算符

不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL。

mysql> select 'wen'<>'tin',22!=33,NULL!=NULL; +--------------+--------+------------+ | 'wen'<>'tin' | 22!=33 | NULL!=NULL | +--------------+--------+------------+ | 1 | 1 | NULL | +--------------+--------+------------+ 1 row in set (0.01 sec) 从下面两组字符串比较可知，字符串比的是字符的个数和顺序 mysql> select 'abc'='abc'; +-------------+ | 'abc'='abc' | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select 'abc'='bca'; +-------------+ | 'abc'='bca' | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)

1.10.2.3 大于、大于等于、小于、小于等于运算符

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

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

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

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

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

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

0是48,A是65，a是97，其余的都是按顺序下去的

mysql> select 3>2,'b'>'c',5>=5,(3+4)>=(1+2),'A'<='a',5<=5.5,'n'>=NULL; +-----+---------+------+--------------+----------+--------+-----------+ | 3>2 | 'b'>'c' | 5>=5 | (3+4)>=(1+2) | 'A'<='a' | 5<=5.5 | 'n'>=NULL | +-----+---------+------+--------------+----------+--------+-----------+ | 1 | 0 | 1 | 1 | 1 | 1 | NULL | +-----+---------+------+--------------+----------+--------+-----------+ 1 row in set (0.00 sec)

比较字符串是或的关系，一旦开头的b比a大，后面就不在比较

mysql> select 'abc'<'baa'; +-------------+ | 'abc'<'baa' | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec)

1.10.2.4 IS NULL、IS NOT NULL

SNULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0。IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0 mysql> select 3 is NULL,'y' is not NULL,NULL is NULL,NULL is not NULL; +-----------+-----------------+--------------+------------------+ | 3 is NULL | 'y' is not NULL | NULL is NULL | NULL is not NULL | +-----------+-----------------+--------------+------------------+ | 0 | 1 | 1 | 0 | +-----------+-----------------+--------------+------------------+ 1 row in set (0.00 sec)

1.10.2.5 BETWEEN AND

BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间。between and 包含两端 mysql> select 5 between 2 and 8,10 between 5 and 10,'d' between 'a' and 'z'; +-------------------+---------------------+-------------------------+ | 5 between 2 and 8 | 10 between 5 and 10 | 'd' between 'a' and 'z' | +-------------------+---------------------+-------------------------+ | 1 | 1 | 1 | +-------------------+---------------------+-------------------------+ 1 row in set (0.00 sec) 数字能否与字符比较？ mysql> select 99 between 'a' and 'z'; +------------------------+ | 99 between 'a' and 'z' | +------------------------+ | 0 | +------------------------+ 1 row in set, 2 warnings (0.00 sec) 如果对a和z进行ASCLL码转换，那么99同时也会进行ASLL码转换，所以不能这么比 此处数字不能与字符比较的

1.10.2.6 LEAST、GREATEST

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

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

具体操作如下

mysql> select least(10,20,30),greatest(10,20,30); +-----------------+--------------------+ | least(10,20,30) | greatest(10,20,30) | +-----------------+--------------------+ | 10 | 30 | +-----------------+--------------------+ 1 row in set (0.00 sec)

1.10.2.7 IN、NOT IN

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

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

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

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

1.10.2.8 IN、NOT IN

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

1.10.3 逻辑运算符

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

又被称布尔值运算符

用来判断表达式的真假

常用的逻辑运算符号

运算符描述NOT 或 ！逻辑非AND 或 &&逻辑与OR逻辑或XOR逻辑异或 类型1和01和10和00和1与运算and0100或运算or1101异或运算xor1001

1.10.3.1 逻辑非

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

1.10.3.2 逻辑与

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

1.10.3.3 逻辑或

逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算。 一旦有个非0值，出来就是非0值逻辑或其实还可以用 ||,但是还是建议用or，因为使用||会出现问题，输出不到你想要的结果 mysql> select 2 or 3,2 or 0; +--------+--------+ | 2 or 3 | 2 or 0 | +--------+--------+ | 1 | 1 | +--------+--------+ 1 row in set (0.00 sec)

1.10.3.4 逻辑异或

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

可以看出 有null结果就会为null

1.10.4 位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。 运算符描述&按位与I按位或~按位取反^按位异或<<按位左移>>按位右移

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

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)

10 转换为二进制数是 1010, 15 转换为二进制数是 1111。

按位与运算（&），是对应的二进制位都是 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

对数字进行左移位运算，向左移动位数，空缺处补0

mysql> select 1<<2; +------+ | 1<<2 | +------+ | 4 | +------+ 1 row in set (0.00 sec)

转换成二进制进行运算 1是 0001 左移2位 0100 =4

对数字进行右移位运算，向右移动位数，多余的位数直接删除 mysql> select 15>>2; +-------+ | 15>>2 | +-------+ | 3 | +-------+ 1 row in set (0.00 sec)

转换成二进制进行运算 15是 1111 右移2位 0011 =3

1.10.5 运算符的优先级

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

1.11 连接查询

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

1.11.1 内连接

■ 两张或多张表中同时符合某种条件的数据记录组合 ■ FROM子句中使用INNER JOIN关键字连接多张表，并使用ON设置连接条件 ■ 是系统默认的表连接方式，可以省略INNER关键字 ■ 多表支持连续使用INNER JOIN，建议不超过三个表 ■ 语法结构 ■ 实现原理，下图所示：

mysql> select info.id,info.name from info inner join num on info.id=num.id; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 3 | wangwu | | 5 | zhaosi | | 7 | woo | +----+----------+ 4 rows in set (0.00 sec

1.11.2 左连接

■ 也被称为左外连接 ■ 在FROM子句中使用LEFT JOIN关键字来表示 ■ 匹配左表中所有及右表中符合条件的行 ■ 实现原理，下图所示： 先创个表

mysql> select * from hobby; +------+--------+ | id | hobby | +------+--------+ | 1 | 绘画 | | 2 | 奥数 | | 3 | 体育 | +------+--------+ 3 rows in set (0.00 sec)

左连接查询

mysql> select i.id,i.name,h.hobby from info i left join hobby h on i.hobby=h.id; +----+----------+--------+ | id | name | hobby | +----+----------+--------+ | 3 | wangwu | 绘画 | | 4 | zhaoliu | 绘画 | | 6 | zhouliu | 绘画 | | 1 | zhangsan | 奥数 | | 5 | zhaosi | 奥数 | | 7 | woo | 奥数 | | 8 | wwwwo | 奥数 | | 2 | lisi | 体育 | | 9 | woww | 体育 | +----+----------+--------+ 9 rows in set (0.00 sec)

1.11.3 右连接

■ 也被称为右外连接 ■ 在FROM子句中使用RIGHT JOIN 关键字来表示 ■ 匹配右表中所有行及左表中符合条件的行 ■ 实现原理，下图所示

mysql> select i.id,i.name,h.hobby from info i right join hobby h on i.hobby=h.id; +------+----------+--------+ | id | name | hobby | +------+----------+--------+ | 1 | zhangsan | 奥数 | | 2 | lisi | 体育 | | 3 | wangwu | 绘画 | | 4 | zhaoliu | 绘画 | | 5 | zhaosi | 奥数 | | 6 | zhouliu | 绘画 | | 7 | woo | 奥数 | | 8 | wwwwo | 奥数 | | 9 | woww | 体育 | +------+----------+--------+ 9 rows in set (0.00 sec)

二、MySQL数据库函数

2.1 数据库函数

■ MySQL提供了实现各种功能的函数 ■ 常用的函数分类

数学函数聚合函数字符串函数日期时间函数

2.2 数学函数

■ 常用的数学函数

abs(x)返回x的绝对值rand() 返回0到1的随机数mod(x， y) 返回x除以y以后的余数power(x， y)返回x的y次方round(x) 返回离x最近的整数round(x， y)保留x的y位小数四舍五入后的值sqrt(x) 返回x的平方根truncate(x， y) 返回数字x截断为y位小数的值ceil(x) 返回大于或等于x的最小整数floor(x) 返回小于或等于x的最大整数greatest(x 1， x 2…) 返回集合中最大的值least(x 1， x 2…) 返回集合中最小的值

实例操作：

abs(x) 返回x的绝对值 mysql> select abs(-10); +----------+ | abs(-10) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) rand() 返回0到1的随机数 mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.8111456118627877 | +--------------------+ 1 row in set (0.00 sec) mod(x,y) 返回x除以y以后的余数 mysql> select mod (7,2); +-----------+ | mod (7,2) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) power（x,y）返回x的y次方 mysql> select power(2,3); +------------+ | power(2,3) | +------------+ | 8 | +------------+ 1 row in set (0.00 sec) round(x) 返回离x最近的整数 mysql> select round(6); +----------+ | round(6) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) round(x,y） 保留x的y位小数四舍五入后的值 mysql> select round(1.49,1); +---------------+ | round(1.49,1) | +---------------+ | 1.5 | +---------------+ 1 row in set (0.00 sec) sqrt(x) 返回x的平方根 mysql> select sqrt(9); +---------+ | sqrt(9) | +---------+ | 3 | +---------+ 1 row in set (0.00 sec) truncate(x,y) 返回数字x截断为y位小数的值 mysql> select truncate(rand(),2); +--------------------+ | truncate(rand(),2) | +--------------------+ | 0.30 | +--------------------+ 1 row in set (0.00 sec) ceil（x）返回大于或等于x的最小整数floor(x) 返回小于或等于x的最大整数 mysql> select ceil(1.1),floor(1.1); +-----------+------------+ | ceil(1.1) | floor(1.1) | +-----------+------------+ | 2 | 1 | +-----------+------------+ 1 row in set (0.00 sec) greatest(x1,x2…) 返回集合中最大的值least(x1,x2…) 返回集合中最小的值 mysql> select greatest(1,2,3),least(1,2,3); +-----------------+--------------+ | greatest(1,2,3) | least(1,2,3) | +-----------------+--------------+ | 3 | 1 | +-----------------+--------------+ 1 row in set (0.00 sec)

2.3 聚合函数

■ 对表中数据记录进行集中概述而设计的一类函数 ■ 常用的聚合函数

avg() 返回指定列的平均值count() 返回指定列中非NULL值的个数min() 返回指定列的最小值max() 返回指定列的最大值sum() 返回指定列的所有值之和 操作实例：avg() 返回指定列的平均值 mysql> select avg(score) from info; +------------+ | avg(score) | +------------+ | 79.2222 | +------------+ 1 row in set (0.00 sec) 1 row in set (0.01 sec) count() 返回指定列中非NULL值的个数 mysql> select count(3); +----------+ | count(3) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) min() 返回指定列的最小值 mysql> select min(score) from info; +------------+ | min(score) | +------------+ | 65 | +------------+ 1 row in set (0.00 sec) max() 返回指定列的最大值 mysql> select max(score) from info; +------------+ | max(score) | +------------+ | 90 | +------------+ 1 row in set (0.00 sec) sum() 返回指定列的所有值之和 mysql> select sum(score) from info; +------------+ | sum(score) | +------------+ | 713 | +------------+ 1 row in set (0.00 sec)

2.4 字符串函数

■ 常用的字符串函数

length(x) 返回字符串x的长度trim() 返回去除指定格式的值concat(x， y) 将提供的参数x和y拼接成一个字符串upper(x) 将字符串x的所有字母变成大写字母lower(x) 将字符串x的所有字母变成小写字母left(x， y) 返回字符串x的前y个字符right(x， y) 返回字符串x的后y个字符repeat(x， y) 将字符串x重复y次space(x) 返回x个空格replace(x， y， z) 将字符串z替代字符串x中的字符串ystrcmp(x， y) 比较x和y， 返回的值可以为-1， 0， 1substring(x， y， z) 获取从字符串x中的第y个位置开始长度为z的字符串reverse(x)将字符串x反转

操作实例：

length(x) 返回字符串x的长度 mysql> select length('abc'); +---------------+ | length('abc') | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec) trim() 返回去除指定格式的值 mysql> select length(trim(' a bc ')); +------------------------+ | length(trim(' a bc ')) | +------------------------+ | 4 | +------------------------+ 1 row in set (0.00 sec)

由上实验可知，只能去掉头尾空格，字符中间的空格不会去掉

concat(x， y) 将提供的参数x和y拼接成一个字符串 mysql> select length(concat('abc','de f')); +------------------------------+ | length(concat('abc','de f')) | +------------------------------+ | 7 | +------------------------------+ 1 row in set (0.00 sec)

由上实验可知，空格也计算进去了

upper(x) 将字符串x的所有字母变成大写字母lower(x) 将字符串x的所有字母变成小写字母 mysql> select upper('abc'),lower('ABC'); +--------------+--------------+ | upper('abc') | lower('ABC') | +--------------+--------------+ | ABC | abc | +--------------+--------------+ 1 row in set (0.01 sec) left(x， y) 返回字符串x的前y个字符，也就是从左数三个right(x， y) 返回字符串x的后y个字符，也就是从右数两个 mysql> select left('abcdefg',3),right('abcdefg',2); +-------------------+--------------------+ | left('abcdefg',3) | right('abcdefg',2) | +-------------------+--------------------+ | abc | fg | +-------------------+--------------------+ 1 row in set (0.00 sec) repeat(x， y) 将字符串x重复y次 mysql> select repeat('abc',3); +-----------------+ | repeat('abc',3) | +-----------------+ | abcabcabc | +-----------------+ 1 row in set (0.00 sec) space(x) 返回x个空格 mysql> select length(concat('abc',space(3),'abc')); +--------------------------------------+ | length(concat('abc',space(3),'abc')) | +--------------------------------------+ | 9 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> select concat('abc',space(3),'abc'); +------------------------------+ | concat('abc',space(3),'abc') | +------------------------------+ | abc abc | +------------------------------+ 1 row in set (0.00 sec) replace(x， y， z) 将字符串z替代字符串x中的字符串y ###把bc替换成12### mysql> select replace('abc','bc','12'); +--------------------------+ | replace('abc','bc','12') | +--------------------------+ | a12 | +--------------------------+ 1 row in set (0.00 sec) strcmp(x， y) 比较x和y， 返回的值可以为-1， 0， 1 mysql> select strcmp(5,5); +-------------+ | strcmp(5,5) | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> select strcmp(6,5); +-------------+ | strcmp(6,5) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) substring(x， y， z) 获取从字符串x中的第y个位置开始长度为z的字符串 截取从左到右第三个字符开始的三个字符 mysql> select substring('abcdefg',3,3); +--------------------------+ | substring('abcdefg',3,3) | +--------------------------+ | cde | +--------------------------+ 1 row in set (0.00 sec) reverse(x)将字符串x反转 数值倒过来 mysql> select reverse('abc'); +----------------+ | reverse('abc') | +----------------+ | cba | +----------------+ 1 row in set (0.00 sec)

2.5 日期时间函数

MySQL 也支持日期时间处理，提供了很多处理日期和时间的函数。一些常用的日期时间函数

字符串函数描述curdate()返回当前时间的年月日curtime()返回当前时间的时分秒now()返回当前时间的日期和时间month(x)返回日期 x 中的月份值week(x)返回日期 x 是年度第几个星期hour(x)返回 x 中的小时值minute(x)返回 x 中的分钟值second(x)返回 x 中的秒钟值dayofweek(x)返回 x 是星期几，1 星期日，2 星期一dayofmonth(x)计算日期 x 是本月的第几天dayofyear(x)计算日期 x 是本年的第几天

MySQL 日期时间函数的使用方法，具体操作如下所示。

curdate()、curtime()、now()

mysql> select curdate(),curtime(),now(); +------------+-----------+---------------------+ | curdate() | curtime() | now() | +------------+-----------+---------------------+ | 2020-10-20 | 19:42:11 | 2020-10-20 19:42:11 | +------------+-----------+---------------------+ 1 row in set (0.00 sec)

month、week、hour

mysql> select month('2020-10-20'),week('2020-10-20'),hour('19:42'); +---------------------+--------------------+---------------+ | month('2020-10-20') | week('2020-10-20') | hour('19:42') | +---------------------+--------------------+---------------+ | 10 | 42 | 19 | +---------------------+--------------------+---------------+ 1 row in set (0.00 sec)

minute、second

mysql> select minute(now()),second(now()); +---------------+---------------+ | minute(now()) | second(now()) | +---------------+---------------+ | 43 | 36 | +---------------+---------------+ 1 row in set (0.00 sec)

dayofweek(）、dayofmonth()、dayofyear()

mysql> select dayofweek(now()),dayofmonth(now()),dayofyear(now()); +------------------+-------------------+------------------+ | dayofweek(now()) | dayofmonth(now()) | dayofyear(now()) | +------------------+-------------------+------------------+ | 3 | 20 | 294 | +------------------+-------------------+------------------+ 1 row in set (0.00 sec)

三、 存储过程

前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中， 有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求。

3.1 创建存储过程语法

使用 CREATE PROCEDURE 语句创建存储过程，其语法格式如下所示。

声明语句结束符，可以自定义:

DELIMITER $$ 或 DELIMITER //

声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

存储过程开始和结束符号:

BEGIN .... END

变量赋值:

SET @p_in=1

变量定义:

DECLARE l_int int unsigned default 4000000;

创建mysql存储过程、存储函数:

create procedure 存储过程名(参数)

存储过程体:

create function 存储函数名(参数) 完整的实例： mysql> delimiter $$ mysql> create procedure tt() -> begin -> select * from info limit 3; -> end $$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> call tt(); +----+----------+-------+-------+------+ | id | name | score | hobby | addr | +----+----------+-------+-------+------+ | 1 | zhangsan | 80 | 2 | NULL | | 2 | lisi | 88 | 3 | NULL | | 3 | wangwu | 90 | 1 | NULL | +----+----------+-------+-------+------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)

注意：最后的delimiter ; 分号之间一定要有空格

3.2 存储过程的参数

部分内容参考菜鸟教程

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

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

1、in 输入参数

mysql> delimiter $$ mysql> create procedure in_param(in p_in int) -> begin -> 　　select p_in; -> 　　set p_in=2; -> select P_in; -> end$$ mysql> delimiter ; mysql> set @p_in=1; mysql> call in_param(@p_in); +------+ | p_in | +------+ | 1 | +------+ +------+ | P_in | +------+ | 2 | +------+ mysql> select @p_in; +-------+ | @p_in | +-------+ | 1 | +-------+

以上可以看出，p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为前者为局部变量、后者为全局变量。

2、out输出参数

mysql> delimiter // mysql> create procedure out_param(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end -> // mysql> delimiter ; mysql> set @p_out=1; mysql> call out_param(@p_out); +-------+ | p_out | +-------+ | NULL | +-------+ 　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null +-------+ | p_out | +-------+ | 2 | +-------+ mysql> select @p_out; +--------+ | @p_out | +--------+ | 2 | +--------+ 　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

3、inout输入参数

mysql> delimiter $$ mysql> create procedure inout_param(inout p_inout int) -> begin -> select p_inout; -> set p_inout=2; -> select p_inout; -> end -> $$ mysql> delimiter ; mysql> set @p_inout=1; mysql> call inout_param(@p_inout); +---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+ mysql> select @p_inout; +----------+ | @p_inout | +----------+ | 2 | +----------+ #调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

3.3 MySQL存储过程的控制语句

(1). 变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3() -> begin -> declare x1 varchar(5) default 'outer'; -> begin -> declare x1 varchar(5) default 'inner'; -> select x1; -> end; -> select x1; -> end; -> // mysql > DELIMITER ;

(2). 条件语句

if-then-else 语句 mysql > DELIMITER // mysql > CREATE PROCEDURE proc2(IN parameter int) -> begin -> declare var int; -> set var=parameter+1; -> if var=0 then -> insert into t values(17); -> end if; -> if parameter=0 then -> update t set s1=s1+1; -> else -> update t set s1=s1+2; -> end if; -> end; -> // mysql > DELIMITER ; case语句 mysql > DELIMITER // mysql > CREATE PROCEDURE proc3 (in parameter int) -> begin -> declare var int; -> set var=parameter+1; -> case var -> when 0 then -> insert into t values(17); -> when 1 then -> insert into t values(18); -> else -> insert into t values(19); -> end case; -> end; -> // mysql > DELIMITER ; case when var=0 then insert into t values(30); when var>0 then when var<0 then else end case

(3). 循环语句

while ···· end while mysql > DELIMITER // mysql > CREATE PROCEDURE proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> // mysql > DELIMITER ; while 条件 do --循环体 endwhile