15 MySQL 系统函数及聚合函数

系统信息函数

函数名/说明	应用实例
version() 返回当前MySQL服务器版本	select version(); - - 返回： 8.0.18
connection_id() MySQL服务器当前连接次数, 各连接都有唯一 id	select connection_id(); - - 返回：18
database() 和 schema() 返回当前数据库名	select database(); - - 返回： booksys
user() 返回当前登录的用户名	select user(); - - 返回：root@localhost
md5(str) 返回 32位字符串 以16进制数二进制加密; 若str为null 则返回null	select md5(‘test1’); - -结果 5a105e8b9d40e1329780d62ea2265d8a select md5(‘test2’); - -结果 ad0234829205b9033196ba818f7a872b
password(str) 返回加密后的密码字符串; 若str为null 则返回null	- - 老师课程涉及此函数，但测试发现有错误

示例：md5 加密/ 验证

create table tabUser(
	userName varchar(16),
	pwd varchar(32)
);	-- 创建tabUser数据表;

insert into tabUser values('test', md5('testpwd')), ('test2', md5('testpwd2')), ('test3', md5('testpwd3'));
--插入3条 用户信息:test~test3的用户名及密码;

执行结果:

mysql> create table tabUser(
    ->  userName varchar(16),
    ->  pwd varchar(32)
    -> );
Query OK, 0 rows affected (0.32 sec)

mysql> insert into tabUser values('test', md5('testpwd')),('test2', md5('testpwd2')),('test3', md5('testpwd3'));
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

以查询 select 查询语句查询/验证：

select * from tabUser; 
-- 可查询全部用户名及密码
select * from tabUser where userName='test' and password=md5('pwd'); 
-- 错误密码验证
select * from tabUser where userName='test2' and password=md5('testpwd2');
-- 正确密码验证

执行结果:

mysql> select * from tabUser;
+----------+----------------------------------+
| userName | pwd                              |
+----------+----------------------------------+
| test     | 342df5b036b2f28184536820af6d1caf |
| test2    | 54ea9cd5119e518beaf0525885ac2875 |
| test3    | 9478af7f2f6205a633e0849ea5094bca |
+----------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select * from tabUser where userName='test' and password=md5('pwd');
Empty set (0.05 sec)

mysql> select * from tabUser where userName='test2' and password=md5('testpwd2');
+----------+----------------------------------+
| userName | pwd                              |
+----------+----------------------------------+
| test2    | 54ea9cd5119e518beaf0525885ac2875 |
+----------+----------------------------------+
1 row in set (0.00 sec)

常用聚合函数

函数名	说明
avg(col)	返回col列的平均值
count(col)	返回col列的行数
max(col)	返回col列的最大值
min(col)	返回col列的最小值
sum(col)	返回col列的求和

应用示例:

alter table tabUser add balance int; --为tabUser表 先插入balance列
-- 再更新test/ test2/ test3用户的余额balance列
update tabUser set balance=100 where userName='test';
update tabUser set balance=200 where userName='test2';
update tabUser set balance=300 where userName='test3';
--再测试以下SQL语句:
select avg(balance) from tabUser; 	-- 200
select count(balance) from tabUser;	-- 3
select max(balance) from tabUser; 	-- 300
select min(balance) from tabUser; 	-- 100
select sum(balance) from tabUser; 	-- 600

mysql> select * from tabUser;
+----------+----------------------------------+---------+
| userName | password                         | balance |
+----------+----------------------------------+---------+
| test     | 342df5b036b2f28184536820af6d1caf |     100 |
| test2    | 54ea9cd5119e518beaf0525885ac2875 |     200 |
| test3    | 9478af7f2f6205a633e0849ea5094bca |     300 |
+----------+----------------------------------+---------+
3 rows in set (0.00 sec)

1 测试SQL语句：select avg(balance) from tabUser;

mysql> select avg(balance) from tabUser;
+--------------+
| avg(balance) |
+--------------+
|     200.0000 |
+--------------+
1 row in set (0.09 sec)

2 测试SQL语句：select count(balance) from tabUser;

mysql> select count(balance) from tabUser;
+----------------+
| count(balance) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

3 测试SQL语句：select max(balance) from tabUser;

mysql> select max(balance) from tabUser;
+--------------+
| max(balance) |
+--------------+
|          300 |
+--------------+
1 row in set (0.06 sec)

4 测试SQL语句：select min(balance) from tabUser;

mysql> select min(balance) from tabUser;
+--------------+
| min(balance) |
+--------------+
|          100 |
+--------------+
1 row in set (0.00 sec)

5 测试SQL语句：select sum(balance) from tabUser;

mysql> select sum(balance) from tabUser;
+--------------+
| sum(balance) |
+--------------+
|          600 |
+--------------+
1 row in set (0.00 sec)