mysql高阶语句：运算符、join连接、函数、存储过程

最新推荐文章于 2023-05-14 18:17:49 发布

Ryougi_Shiki_uio

最新推荐文章于 2023-05-14 18:17:49 发布

阅读量179

点赞数

分类专栏：集群

原文链接：https://blog.csdn.net/longtiandao/article/details/109100800

版权

集群专栏收录该内容

25 篇文章 0 订阅

订阅专栏

运算符

mysql的运算符用于对记录中的字段值进行运算。MysQL 的运算符共有四种，分别是:算术运算符、比较运算符、逻辑运算符和位运算符。

算术运算符

+	加法
-	减法
*	乘法
/	除
%	取余

mysql> select 1-2,3+4,2*5,5/2,8%3;
+-----+-----+-----+--------+------+
| 1-2 | 3+4 | 2*5 | 5/2    | 8%3  |
+-----+-----+-----+--------+------+
|  -1 |   7 |  10 | 2.5000 |    2 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出表中有哪些记录是符合条件的，如果比较的结果(以布尔值的方式进行返回判断)为真则返回1，如果为假则返回o，比较的结果如果不确定则返回NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过binary关键宁来实现。

=	等于
>	大于
<	小于
<=	小于等于
>=	大于等于
!=或<>	不等于
is null	是否为空
is not null	是否为非空
in	是否在集合中
like	支持两种通配符，%用于匹配任意数目的字符，而 _ 只能匹配一个字符。
between and	比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间.
least	当有两个或者多个参数时，返回其中的最小值。
greatest	多个参数返回最大值。

#演示
mysql> select 'a'='b',3>5,'abc'like'ab_';
+---------+-----+----------------+
| 'a'='b' | 3>5 | 'abc'like'ab_' |
+---------+-----+----------------+
|       0 |   0 |              1 |
+---------+-----+----------------+
1 row in set (0.00 sec)

#判断最大值，最小值，是否为null
mysql> select least (1,2,3),greatest(1,3,4),null is null;
+---------------+-----------------+--------------+
| least (1,2,3) | greatest(1,3,4) | null is null |
+---------------+-----------------+--------------+
|             1 |               4 |            1 |
+---------------+-----------------+--------------+
1 row in set (0.00 sec)

#搭配子查询使用in
mysql> select id,name from game where id in (select id from ti where 2 in (2));
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | lisi     |
|  4 | wangwu   |
|  5 | laoliu   |
+----+----------+
5 rows in set (0.00 sec)

逻辑运算符

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

not或!	逻辑非，取反，1变为0，0变为1
and或&&	逻辑与，有0返回0，无0有null返回null，都没有返回1
or		逻辑或，有非0值返回1，有null且另一个为0时返回null，都为0时返回0
xor		逻辑异或，有null返回null，两个数的值同为0或同为非0判断结果为0，一个为0一个非0判断结果为1

#and判断
mysql> select 0 and 2,null and 3,1 and 4;
+---------+------------+---------+
| 0 and 2 | null and 3 | 1 and 4 |
+---------+------------+---------+
|       0 |       NULL |       1 |
+---------+------------+---------+
1 row in set (0.00 sec)

#or判断
mysql> select 1 or 3,null or 0,0 or 0;
+--------+-----------+--------+
| 1 or 3 | null or 0 | 0 or 0 |
+--------+-----------+--------+
|      1 |      NULL |      0 |
+--------+-----------+--------+
1 row in set (0.00 sec)

#xor判断
mysql> select 2 xor null,3 xor 5,0 xor 0,9 xor 0;
+------------+---------+---------+---------+
| 2 xor null | 3 xor 5 | 0 xor 0 | 9 xor 0 |
+------------+---------+---------+---------+
|       NULL |       0 |       0 |       1 |
+------------+---------+---------+---------+
1 row in set (0.00 sec)

位运算符

位运算符实际上是对二进制数进行计算的运算符。MysQL内位运算会先将操作数变成二进制格式(1010 1111），然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。MysQI支持6种位运算符，具体如表所示。

&	按位与
|	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

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)

join连接

内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合
通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN
同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表

#将两张表粘在一起，左表每一行都匹配一次右表，然后输出结果
mysql> select * from game inner join name;
+----+----------+-------+----------+-------+------+-------+--------+
| id | name     | sorce | addr     | level | id   | name  | passwd |
+----+----------+-------+----------+-------+------+-------+--------+
|  1 | zhangsan | 49.00 | 233      |    10 |    1 | user1 | abc123 |
|  1 | zhangsan | 49.00 | 233      |    10 |    2 | user2 | abcabc |
|  2 | lisi     | 51.00 | beijing  |    13 |    1 | user1 | abc123 |
|  2 | lisi     | 51.00 | beijing  |    13 |    2 | user2 | abcabc |
|  3 | lisi     | 37.00 | nanjing  |     9 |    1 | user1 | abc123 |
|  3 | lisi     | 37.00 | nanjing  |     9 |    2 | user2 | abcabc |
|  4 | wangwu   | 43.00 | zhejiang |    12 |    1 | user1 | abc123 |
|  4 | wangwu   | 43.00 | zhejiang |    12 |    2 | user2 | abcabc |
|  5 | laoliu   | 38.00 | shanghai |    16 |    1 | user1 | abc123 |
|  5 | laoliu   | 38.00 | shanghai |    16 |    2 | user2 | abcabc |
+----+----------+-------+----------+-------+------+-------+--------+
10 rows in set (0.00 sec)

#上面看起来比较乱，所以通常跟on进行一个判断匹配
mysql> select * from game inner join name on game.id=name.id;
+----+----------+-------+---------+-------+------+-------+--------+
| id | name     | sorce | addr    | level | id   | name  | passwd |
+----+----------+-------+---------+-------+------+-------+--------+
|  1 | zhangsan | 49.00 | 233     |    10 |    1 | user1 | abc123 |
|  2 | lisi     | 51.00 | beijing |    13 |    2 | user2 | abcabc |
+----+----------+-------+---------+-------+------+-------+--------+
2 rows in set (0.00 sec)

左连接

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

不匹配的记录对多出来的字段的值标为null
mysql> select * from game left join name on game.id=name.id;
+----+----------+-------+----------+-------+------+-------+--------+
| id | name     | sorce | addr     | level | id   | name  | passwd |
+----+----------+-------+----------+-------+------+-------+--------+
|  1 | zhangsan | 49.00 | 233      |    10 |    1 | user1 | abc123 |
|  2 | lisi     | 51.00 | beijing  |    13 |    2 | user2 | abcabc |
|  3 | lisi     | 37.00 | nanjing  |     9 | NULL | NULL  | NULL   |
|  4 | wangwu   | 43.00 | zhejiang |    12 | NULL | NULL  | NULL   |
|  5 | laoliu   | 38.00 | shanghai |    16 | NULL | NULL  | NULL   |
+----+----------+-------+----------+-------+------+-------+--------+
5 rows in set (0.00 sec)

右连接

和左连接反着来，使用RIGHT JOIN进行连接。

mysql> select * from game right join name on game.id=name.id;
+------+----------+-------+---------+-------+------+-------+--------+
| id   | name     | sorce | addr    | level | id   | name  | passwd |
+------+----------+-------+---------+-------+------+-------+--------+
|    1 | zhangsan | 49.00 | 233     |    10 |    1 | user1 | abc123 |
|    2 | lisi     | 51.00 | beijing |    13 |    2 | user2 | abcabc |
| NULL | NULL     |  NULL | NULL    |  NULL |    7 | user3 | abcabc |
+------+----------+-------+---------+-------+------+-------+--------+
3 rows in set (0.00 sec)

函数

数学函数

数据库内存储的记录，经常要进行一系列的算术操作，所以MysQL支持很多数学函数。常用的数学函数如表所示。

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的平方根
truncate (x, y)		返回数字x截断为y位小数的值
ceil(x)		返回大于或等于x的最小整数
floor (x)	返回小于或等于x的最大整数

聚合函数

数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。

avg()	返回指定列的平均值
count()	返回指定列中非NULL值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
surm()	返回指定列的所有值之和

字符串函数

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中的字符串y
stremp(x, y)	比较x和 y，返回的值可以为-1,0,1
substring(x,y,z)	获取从字符串x中的第y个位置开始长度为z的字符串
reverse(x)	将字符串x反转

日期函数

curdateT()	返回当前时间的年月日
curtime()	返回当前时间的时分秒
now()	返回当前时间的日期和时间
month(x)	返回日期x中的月份值
week(×)	返回日期x是年度第几个星期
hour(x)	返回x中的小时值
minute(x)	返回x中的分钟值
second(x)	返回x中的秒钟值
dayofweek(x)	返回x是星期几，1 星期日，2星期一
replace(x,y,z)	将字符串z替代字符串x中的字符串y
dayofmonth(x)	计算日期x是本月的第几天
dayofyear(x)	计算日期x是本年的第几天

存储过程

1、存储过程是一组为了完成特定功能的sQL语句集合。

2、存储过程在使用过程中是将常用或者复杂的工作预先使用sQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统sQL速度更快、执行效率更高。

3、存储过程的优点:
(1)执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
(2) sQL语句加上控制语句的集合，灵活性高
(3)在服务器端存储，客户端调用时，降低网络负载
(4)可多次重复被调用，可随时修改，不影响客户端调用
(5)可完成所有的数据库操作，也可控制数据库的信息访问权限

#创建存储过程
mysql> delimiter $$		#更改mysql的结束字符
mysql> create procedure aaa()
    -> begin
    -> select * from game;
    -> select * from name;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;		#最后改回来，不然mysql识别不了存储过程里的;

#调用存储过程
mysql> call aaa();
+----+----------+-------+----------+-------+
| id | name     | sorce | addr     | level |
+----+----------+-------+----------+-------+
|  1 | zhangsan | 49.00 | 233      |    10 |
|  2 | lisi     | 51.00 | beijing  |    13 |
|  3 | lisi     | 37.00 | nanjing  |     9 |
|  4 | wangwu   | 43.00 | zhejiang |    12 |
|  5 | laoliu   | 38.00 | shanghai |    16 |
+----+----------+-------+----------+-------+
5 rows in set (0.00 sec)

+------+-------+--------+
| id   | name  | passwd |
+------+-------+--------+
|    1 | user1 | abc123 |
|    2 | user2 | abcabc |
|    7 | user3 | abcabc |
+------+-------+--------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

#查看存储过程
#可以看到执行的语句，谁创建的，字符集等信息。
mysql> show create procedure aaa\G;
*************************** 1. row ***************************
           Procedure: aaa
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER="root"@"localhost" PROCEDURE "aaa"()
begin
select * from game;
select * from name;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

#在存储过程里使用参数
mysql> delimiter $$
mysql> create procedure bbb(in ccc int)		#在创建的时候定义一个参数
    -> begin
    -> select * from game where id=ccc;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

#调用存储过程
mysql> call bbb(1);		#参数起到了一个筛选作用
+----+----------+-------+------+-------+
| id | name     | sorce | addr | level |
+----+----------+-------+------+-------+
|  1 | zhangsan | 49.00 | 233  |    10 |
+----+----------+-------+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)