常用函数
什么是函数
mysql服务内置命令
提供了各种功能的函数
比如统计函数 处理日期的函数 处理字符的函数 数学函数
例如：version() user() database()
函数的用法
select格式：
select 函数(表头名) from 库名.表名;
select 函数(表头名) from 库名.表名 where 条件;单独使用
函数套函数
mysql> select year( now() );使用函数处理表头下的数据
mysql> select count(name) from user;常用函数的使用
字符函数
处理字符或字符类型的表头
length(str) 返回字符串长度，以字节为单位（一个汉字等于三个字节）
char_length(str) 返回字符串长度，以字符为单位
upper(str)和ucase(str) 将字符串的字母全部转换成大写
lower(str)和lcase(str) 将str中的字母全部转换成小写
substr(s,start,end) 从s的start位置开始取出到end长度的子串
instr(str,str1) 返回str1参数，在str参数内的位置
trim(s) 返回字符串s删除了两边空格之后的字符串
数学函数
处理数字或数值类型的表头
ABS(x) 返回x的绝对值
PI() 返回圆周率π，默认显示6位小数
MOD(x,y) 返回x除以y的余数
CEIL(x)、CEILING(x) 返回不小于x的最小整数（x 是小数）
日期函数
获取系统或指定的日期与时间
聚集函数
数据统计命令
avg(字段名) # 计算平均值
sum(字段名) # 求和
min(字段名) # 获取最小值
max(字段名) # 获取最大值
count(字段名) # 统计字段值个数
数学计算
对行中的列做计算
判断函数
对表的数据做查看时 可以根据判断结果输出数据
if函数
if(筛选条件,v1,v2) 如果条件是true则返回v1,否则返回v2
ifnull(v1,v2) 如果v1不为NULL，则返回v1,否则返回v2
case函数
可以判断多个条件
case 表头名 case
when 值1 then 输出结果 when 判断条件 then 输出结果
when 值2 then 输出结果 when 判断条件 then 输出结果
...... ......
else 输出结果 else 输出结果
end end
处理查询结果
对select语句查找到的数据再处理
select查询命令 [where 条件] 排序 | 分组 | 过滤 | 分页;
排序
对查找到的表头下的数据排队
语法：
select 表头名 from 库.表 where 筛选条件 order by 表头名 asc(升序排) | desc(降序排);
mysql> select name,uid from user where uid is not null order by uid; # 默认升序
+-----------------+-------+
| name | uid |
+-----------------+-------+
| root | 0 |
| bin | 1 |
| daemon | 2 |
| adm | 3 |
| lp | 4 |
| sync | 5 |
| shutdown | 6 |
| halt | 7 |
| mail | 8 |
| operator | 11 |
| games | 12 |
| ftp | 14 |
| mysql | 27 |
| rpcuser | 29 |
| rpc | 32 |
| apache | 48 |
| sshd | 74 |
| dbus | 81 |
| postfix | 89 |
| nobody | 99 |
| haproxy | 188 |
| systemd-network | 192 |
| chrony | 998 |
| polkitd | 999 |
| plj | 1000 |
| nfsnobody | 65534 |
+-----------------+-------+
26 rows in set (0.01 sec)
mysql> select name,uid from user where uid is not null order by uid desc;
+-----------------+-------+
| name | uid |
+-----------------+-------+
| nfsnobody | 65534 |
| plj | 1000 |
| polkitd | 999 |
| chrony | 998 |
| systemd-network | 192 |
| haproxy | 188 |
| nobody | 99 |
| postfix | 89 |
| dbus | 81 |
| sshd | 74 |
| apache | 48 |
| rpc | 32 |
| rpcuser | 29 |
| mysql | 27 |
| ftp | 14 |
| games | 12 |
| operator | 11 |
| mail | 8 |
| halt | 7 |
| shutdown | 6 |
| sync | 5 |
| lp | 4 |
| adm | 3 |
| daemon | 2 |
| bin | 1 |
| root | 0 |
+-----------------+-------+
26 rows in set (0.00 sec)
分组
相同的数据为一组
语法：
select 表头名 from 库.表 where 筛选条件 group by 表头名;
说明：
查询列表必须是分组函数和出现在group by后面的字段
字段中值相同的为一组
# 查看 employees 表里员工编号100以内的员工所在的部门
mysql> select dept_id,name from employees where employee_id<100;
+---------+-----------+
| dept_id | name |
+---------+-----------+
| 1 | 梁伟 |
| 1 | 郭岩 |
| 1 | 李玉英 |
| 1 | 张健 |
| 1 | 郑静 |
| 1 | 牛建军 |
| 1 | 刘斌 |
| 1 | 汪云 |
| 2 | 张建平 |
| 2 | 郭娟 |
| 2 | 郭兰英 |
| 2 | 王英 |
| 2 | 王楠 |
| 3 | 廖娜 |
| 3 | 窦红梅 |
| 3 | 聂想 |
| 3 | 陈阳 |
| 3 | 戴璐 |
| 3 | 陈斌 |
| 4 | 蒋红 |
| 4 | 曹宁 |
| 4 | 吕刚 |
| 4 | 王莉 |
| 4 | 邓秀芳 |
| 4 | 邵佳 |
| 4 | 党丽 |
| 4 | 梁勇 |
| 4 | 郑秀珍 |
| 4 | 胡秀云 |
| 4 | 邢淑兰 |
| 4 | 刘海燕 |
| 4 | 冯建国 |
| 4 | 曹杰 |
| 4 | 苗桂花 |
| 4 | 袁建平 |
| 4 | 黄淑兰 |
| 4 | 朱淑兰 |
| 4 | 曹凯 |
| 4 | 张倩 |
| 4 | 王淑珍 |
| 4 | 陈玉 |
| 4 | 陈玉英 |
| 4 | 王波 |
| 4 | 黄文 |
| 4 | 陈刚 |
| 4 | 罗建华 |
| 4 | 黄建平 |
| 4 | 范秀英 |
| 4 | 李平 |
| 4 | 臧龙 |
| 4 | 吴静 |
| 4 | 张冬梅 |
| 4 | 邢成 |
| 4 | 孙丹 |
| 4 | 梁静 |
| 4 | 陈洁 |
| 4 | 许辉 |
| 4 | 张伟 |
| 4 | 钟倩 |
| 4 | 贺磊 |
| 4 | 沈秀梅 |
| 4 | 林刚 |
| 4 | 王玉华 |
| 4 | 徐金凤 |
| 4 | 张淑英 |
| 4 | 罗岩 |
| 4 | 潘玲 |
| 4 | 柴冬梅 |
| 4 | 谢莹 |
| 4 | 傅雪 |
| 4 | 龙旭 |
| 4 | 赵杰 |
| 4 | 王璐 |
| 4 | 赵成 |
| 5 | 温兰英 |
| 5 | 刘桂兰 |
| 5 | 吴丽娟 |
| 5 | 张娜 |
| 5 | 叶欣 |
| 5 | 符燕 |
| 5 | 徐成 |
| 5 | 韩丹 |
| 5 | 蒋秀芳 |
| 5 | 朱文 |
| 5 | 刘玲 |
| 5 | 张宇 |
| 6 | 田萍 |
| 6 | 田英 |
| 6 | 唐芳 |
| 6 | 莫凤兰 |
| 6 | 李建华 |
| 6 | 陶红 |
| 6 | 余春梅 |
| 6 | 熊东 |
| 6 | 毛丹 |
| 7 | 胡瑜 |
| 7 | 崔志强 |
| 7 | 李莹 |
| 7 | 王畅 |
+---------+-----------+
99 rows in set (0.00 sec)
mysql> select dept_id from employees where employee_id<100 group by dept_id;
+---------+
| dept_id |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---------+
7 rows in set (0.00 sec)
# 并输出部门的总人数
mysql> select dept_id as 部门编号,count(name) as 总人数 from employees where employee_id<100 group by dept_id;
+--------------+-----------+
| 部门编号 | 总人数 |
+--------------+-----------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 4 |
+--------------+-----------+
7 rows in set (0.00 sec)过滤
在查找到的数据里再筛选符合条件的数据
语法：
select 表头名 from 库.表 where 筛选条件 having 筛选条件;
# 仅出部门人数小于10人的部门编号和总人数
mysql> select dept_id as 部门编号,count(name) as 总人数 from employees where employee_id<100 group by dept_id having 总人数<10;
+--------------+-----------+
| 部门编号 | 总人数 |
+--------------+-----------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 6 | 9 |
| 7 | 4 |
+--------------+-----------+
5 rows in set (0.00 sec)分页
限制查询结果数据的行数，默认输出查询结果所有行
select 表头名 from 库.表 where 筛选条件 limit 数字; 仅显示前几行
select 表头名 from 库.表 where 筛选条件 limit 数字1,数字2;(起始行,显示的总行数 0表示第一行) 显示范围内的行
# 把UID号最大的用户信息输出
mysql> select * from user order by uid desc; # 根据uid大小进行降序排序
mysql> select * from user order by uid desc limit 1; # 输出排序后的前一行
mysql> select * from employees limit 0,5;
+-------------+-----------+------------+------------+----------------------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+-----------+------------+------------+----------------------+--------------+---------+
| 1 | 梁伟 | 2018-06-21 | 1971-08-19 | liangwei@tedu.cn | 13591491431 | 1 |
| 2 | 郭岩 | 2010-03-21 | 1974-05-13 | guoyan@tedu.cn | 13845285867 | 1 |
| 3 | 李玉英 | 2012-01-19 | 1974-01-25 | liyuying@tedu.cn | 15628557234 | 1 |
| 4 | 张健 | 2008-09-17 | 1972-06-07 | zhangjian@tarena.com | 13835990213 | 1 |
| 5 | 郑静 | 2018-02-03 | 1997-02-14 | zhengjing@tedu.cn | 14508936730 | 1 |
+-------------+-----------+------------+------------+----------------------+--------------+---------+
5 rows in set (0.00 sec)
mysql> select * from employees limit 5,5;
+-------------+-----------+------------+------------+--------------------------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+-----------+------------+------------+--------------------------+--------------+---------+
| 6 | 牛建军 | 2005-11-18 | 1985-03-19 | niujianjun@tarena.com | 14750937118 | 1 |
| 7 | 刘斌 | 2014-09-04 | 1989-12-02 | liubin@tarena.com | 13422179271 | 1 |
| 8 | 汪云 | 2018-11-21 | 1993-03-25 | wangyun@tedu.cn | 15500969849 | 1 |
| 9 | 张建平 | 2016-06-04 | 1971-11-02 | zhangjianping@tarena.com | 13229532500 | 2 |
| 10 | 郭娟 | 2018-09-03 | 1993-06-07 | guojuan@tarena.com | 13730949382 | 2 |
+-------------+-----------+------------+------------+--------------------------+--------------+---------+
5 rows in set (0.00 sec)
mysql> select * from employees limit 10,5;
+-------------+-----------+------------+------------+--------------------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+-----------+------------+------------+--------------------+--------------+---------+
| 11 | 郭兰英 | 2007-03-20 | 1994-12-20 | guolanying@tedu.cn | 18505599695 | 2 |
| 12 | 王英 | 2016-12-27 | 1997-10-11 | wangying@tedu.cn | 15227394915 | 2 |
| 13 | 王楠 | 2006-05-29 | 1989-02-05 | wangnan@tarena.com | 15098647876 | 2 |
| 14 | 廖娜 | 2012-05-20 | 1982-06-22 | liaona@tarena.com | 15827928192 | 3 |
| 15 | 窦红梅 | 2018-03-16 | 1971-09-09 | douhongmei@tedu.cn | 15004739483 | 3 |
+-------------+-----------+------------+------------+--------------------+--------------+---------+
5 rows in set (0.00 sec)管理表记录
对表里已经存储的数据做管理
查看数据
select
插入表记录
insert into
添加1行 仅给指定的表头赋值
insert into 库.表(表头名) values(表头的值);
mysql> insert into user(name,uid) values("jerry",1088);
mysql> select * from user where name="jerry";
+----+-------+----------+------+------+---------+---------+-------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-------+----------+------+------+---------+---------+-------+
| 75 | jerry | NULL | 1088 | NULL | NULL | NULL | NULL |
+----+-------+----------+------+------+---------+---------+-------+
1 row in set (0.00 sec)
添加多行 仅给指定的表头赋值
mysql> insert into user(name,uid) values("john",1089),("alice",1090),("jim",1087);
mysql> select * from user where name in ("john","alice","jim");
+----+-------+----------+------+------+---------+---------+-------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-------+----------+------+------+---------+---------+-------+
| 76 | john | NULL | 1089 | NULL | NULL | NULL | NULL |
| 77 | alice | NULL | 1090 | NULL | NULL | NULL | NULL |
| 78 | jim | NULL | 1087 | NULL | NULL | NULL | NULL |
+----+-------+----------+------+------+---------+---------+-------+
3 rows in set (0.01 sec)添加一行，给所有表头赋值
mysql> insert into user values(51,"abc","x",99,99,"test user","/home/abc","/bin/bash");
mysql> select * from user where id=51
-> ;
+----+------+----------+------+------+-----------+-----------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+-----------+-----------+-----------+
| 51 | abc | x | 99 | 99 | test user | /home/abc | /bin/bash |
+----+------+----------+------+------+-----------+-----------+-----------+
1 row in set (0.00 sec)添加多行，给所有表头赋值
mysql> insert into user values(53,"A","X",108,108,"AAA","/home/a",null),(54,"A","X",108,108,"AAA","/home/a",null),(55,"A","X",108,108,"AAA","/home/a",null);
mysql> select * from user;
| 53 | A | X | 108 | 108 | AAA | /home/a | NULL |
| 54 | A | X | 108 | 108 | AAA | /home/a | NULL |
| 55 | A | X | 108 | 108 | AAA | /home/a | NULL |
修改表记录
update
仅修改符合条件
update 库.表 set 表头名=修改之后的值,表头名=修改之后的值 where 筛选条件;
mysql> select name,homedir from user user where homedir is null;
+-------+---------+
| name | homedir |
+-------+---------+
| bob | NULL |
| yaya | NULL |
| yaya1 | NULL |
| 6yaya | NULL |
| | NULL |
| null | NULL |
| NULL | NULL |
| NULL | NULL |
| jerry | NULL |
| john | NULL |
| alice | NULL |
| jim | NULL |
+-------+---------+
12 rows in set (0.00 sec)
mysql> update user set homedir="/" where homedir is null;
Query OK, 12 rows affected (0.05 sec)
Rows matched: 12 Changed: 12 Warnings: 0
批量修改
update 库.表 set 表头名=修改之后的值,表头名=修改之后的值;
mysql> select name,password from user;
+-----------------+----------+
| name | password |
+-----------------+----------+
| root | x |
| bin | x |
| daemon | x |
| adm | x |
| lp | x |
| sync | x |
| shutdown | x |
| halt | x |
| mail | x |
| operator | x |
| games | x |
| ftp | x |
| nobody | x |
| systemd-network | x |
| dbus | x |
| polkitd | x |
| sshd | x |
| postfix | x |
| chrony | x |
| rpc | x |
| rpcuser | x |
| nfsnobody | x |
| haproxy | x |
| plj | x |
| apache | x |
| mysql | x |
| bob | NULL |
| yaya | NULL |
| yaya1 | NULL |
| 6yaya | NULL |
| abc | x |
| A | X |
| A | X |
| A | X |
| | NULL |
| null | NULL |
| NULL | NULL |
| NULL | NULL |
| jerry | NULL |
| john | NULL |
| alice | NULL |
| jim | NULL |
+-----------------+----------+
42 rows in set (0.00 sec)
mysql> update user set password="z";
Query OK, 42 rows affected (0.08 sec)
Rows matched: 42 Changed: 42 Warnings: 0
mysql> select name,password from user;
+-----------------+----------+
| name | password |
+-----------------+----------+
| root | z |
| bin | z |
| daemon | z |
| adm | z |
| lp | z |
| sync | z |
| shutdown | z |
| halt | z |
| mail | z |
| operator | z |
| games | z |
| ftp | z |
| nobody | z |
| systemd-network | z |
| dbus | z |
| polkitd | z |
| sshd | z |
| postfix | z |
| chrony | z |
| rpc | z |
| rpcuser | z |
| nfsnobody | z |
| haproxy | z |
| plj | z |
| apache | z |
| mysql | z |
| bob | z |
| yaya | z |
| yaya1 | z |
| 6yaya | z |
| abc | z |
| A | z |
| A | z |
| A | z |
| | z |
| null | z |
| NULL | z |
| NULL | z |
| jerry | z |
| john | z |
| alice | z |
| jim | z |
+-----------------+----------+
42 rows in set (0.00 sec)
删除表记录（不删除表）
仅删除与条件匹配的行
delete from 库.表 where 筛选条件;
mysql> delete from user where homedir="/";
Query OK, 16 rows affected (0.05 sec)
mysql> select * from user where id=10;
+----+----------+----------+------+------+----------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+----------+----------+------+------+----------+---------+---------------+
| 10 | operator | z | 11 | 0 | operator | /root | /sbin/nologin |
+----+----------+----------+------+------+----------+---------+---------------+
1 row in set (0.00 sec)
mysql> delete from user where id=10;
Query OK, 1 row affected (0.05 sec)
mysql> select * from user where id=10;
Empty set (0.00 sec)
清空表数据（！！！慎用）
mysql> delete from user;
Query OK, 25 rows affected (0.09 sec)
mysql> select * from user;
Empty set (0.00 sec)
扫一扫
1336
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
