1 案例1：常用函数

1.1 问题

  1. 练习字符函数
  2. 练习数学函数
  3. 练习日期函数
  4. 练习聚集函数
  5. 练习数学计算
  6. 练习if函数
  7. 练习case函数

1.2 方案

函数：MySQL服务内置命令

01.  语法：函数名（表头名）

select格式：

SELECT  函数(表头名)  FROM  库名.表名；
SELECT  函数(表头名)  FROM  库名.表名 WHERE 条件；

环境说明

tarena库下的employees表 保存133个员工的信息如图-1，图-2

查看表头

desc  tarena.employees;

Mysql 常用函数_mysql

查看表记录

select * from tarena.employees where employee_id = 1;

员工编号 姓名 入职日期 出生日期 电子邮箱 电话号码 部门编号

Mysql 常用函数_常用函数_02

desc tarena.salary;

Mysql 常用函数_常用函数_03

查看表记录

select  *  from  tarena.salary  where  id  =  2;

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：练习字符函数(处理字符或字符类型的表头)

LENGTH(str) 返字符串长度，以字节为单位

mysql> select name from tarena.user where name = "root" ;
+------+
| name |
+------+
| root |
+------+
1 row in set (0.00 sec)
mysql> select name , length(name) as 字节个数from tarena.user where name = "root" ;
+------+--------------+
| name | 字节个数      |
+------+--------------+
| root |            4  |
+------+--------------+
1 row in set (0.00 sec)
//一个汉字3个字节
mysql> select name , length(name) from tarena.employees where employee_id = 3 ;
+-----------+--------------+
| name      | length(name) |
+-----------+--------------+
| 李玉英    |            9 |
+-----------+--------------+
//CHAR_LENGTH(str)        返回字符串长度，以字符为单位
mysql> select name from tarena.employees where employee_id = 3 ;
+-----------+
| name      |
+-----------+
| 李玉英    |
+-----------+
1 row in set (0.00 sec)
mysql> select name , char_length(name) from tarena.employees where employee_id = 3 ;
+-----------+-------------------+
| name      | char_length(name) |
+-----------+-------------------+
| 李玉英     |                 3 |
+-----------+-------------------+
1 row in set (0.00 sec)

UPPER(str)和UCASE(str) 将字符串中的字母全部转换成大写

mysql> select name from  tarena.user where uid <= 3 ;
+--------+
| name   |
+--------+
| root   |
| bin    |
| daemon |
| adm    |
+--------+
4 rows in set (0.00 sec)
mysql> select upper(name) from  tarena.user where uid <= 3 ;
+-------------+
| upper(name) |
+-------------+
| ROOT        |
| BIN         |
| DAEMON      |
| ADM         |
+-------------+
4 rows in set (0.00 sec)
mysql> select ucase(name) from  tarena.user where uid <= 3 ;
+-------------+
| ucase(name) |
+-------------+
| ROOT        |
| BIN         |
| DAEMON      |
| ADM         |
+-------------+
4 rows in set (0.00 sec)

LOWER(str)和LCASE(str) 将str中的字母全部转换成小写

mysql> select lower("ABCD") ;
+---------------+
| lower("ABCD") |
+---------------+
| abcd          |
+---------------+
1 row in set (0.00 sec)
mysql> select lcase("ABCD") ;
+---------------+
| lcase("ABCD") |
+---------------+
| abcd          |
+---------------+
1 row in set (0.00 sec)
mysql>

SUBSTR(s, start,end) 从s的start位置开始取出到end长度的子串

mysql> select name from  tarena.employees where employee_id <= 3 ;
+-----------+
| name      |
+-----------+
| 梁伟      |
| 郭岩      |
| 李玉英    |
+-----------+
3 rows in set (0.00 sec)
//不是输出员工的姓 只输出名字
mysql> select substr(name,2,3) from  tarena.employees where employee_id <= 3 ;
+------------------+
| substr(name,2,3) |
+------------------+
| 伟               |
| 岩               |
| 玉英             |
+------------------+
3 rows in set (0.00 sec)

INSTR(str,str1) 返回str1参数，在str参数内的位置

mysql> select name from  tarena.user where uid <= 3 ;
+--------+
| name   |
+--------+
| root   |
| bin    |
| daemon |
| adm    |
+--------+
4 rows in set (0.00 sec)
mysql> select instr(name,"a") from  tarena.user where uid <= 3 ;
+-----------------+
| instr(name,"a") |
+-----------------+
|               0 |
|               0 |
|               2 |
|               1 |
+-----------------+
4 rows in set (0.00 sec)
//查找名字里有英字及出现的位置
mysql> select name , instr(name,"英") from  tarena.employees;
+-----------+-------------------+
| name      | instr(name,"英")  |
+-----------+-------------------+
| 梁伟      |                 0 |
| 郭岩      |                 0 |
| 李玉英    |                 3 |
| 张健      |                 0 |
| 郑静      |                 0 |
| 牛建军    |                 0 |
| 刘斌      |                 0 |
| 汪云      |                 0 |
| 张建平    |                 0 |
| 郭娟      |                 0 |
| 郭兰英    |                 3 |
| 王英      |                 2 |

TRIM(s) 返回字符串s删除了两边空格之后的字符串

mysql> select trim("  ABC  ");
+-----------------+
| trim("  ABC  ") |
+-----------------+
| ABC             |
+-----------------+
1 row in set (0.00 sec)
mysql>

步骤二：练习数学函数(处理数字或数值类型的表头)

ABS(x) 返回x的绝对值

mysql> select abs(11);
+---------+
| abs(11) |
+---------+
|      11 |
+---------+
1 row in set (0.00 sec)
mysql> select abs(-11);
+----------+
| abs(-11) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)
mysql>

PI() 返回圆周率π，默认显示6位小数

mysql> select pi() ;
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

MOD(x,y) 返回x被y除后的余数

mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
//输出1-10之间的偶数uid号
mysql> select name , uid from tarena.user where uid between 1 and 10 and  mod(uid,2) = 0 ;
+----------+------+
| name     | uid  |
+----------+------+
| daemon   |    2 |
| lp       |    4 |
| shutdown |    6 |
| mail     |    8 |
+----------+------+
4 rows in set (0.00 sec)

CEIL(x)、CEILING(x) 返回不小于x的最小整数 (x 是小数)

mysql> select ceil(9.23);
+------------+
| ceil(9.23) |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)
mysql> select ceiling(9.23);
+---------------+
| ceiling(9.23) |
+---------------+
|            10 |
+---------------+
1 row in set (0.00 sec)
mysql>

FLOOR(x） 返回不大于x的最大整数 (x 是有小数的数字)

mysql> select floor(9.23);
+-------------+
| floor(9.23) |
+-------------+
|           9 |
+-------------+
1 row in set (0.00 sec)

步骤三：练习日期函数 (获取系统或指定的日期与时间)

Mysql 常用函数_mysql_04

curtime() //获取系统时间

mysql> select curtime(); //获取系统时间
+-----------+
| curtime() |
+-----------+
| 17:42:20  |
+-----------+
1 row in set (0.00 sec)

curdate() //获取系统日期

mysql> select curdate();//获取系统日期
+------------+
| curdate()  |
+------------+
| 2023-05-24 |
+------------+
1 row in set (0.00 sec)

now() //获取系统日期+时间

mysql> select now() ;//获取系统日期+时间
+---------------------+
| now()               |
+---------------------+
| 2023-05-24 17:42:29 |
+---------------------+
1 row in set (0.00 sec)

year() ; //获取年

mysql> select year(now()) ; //获取系统当前年
+-------------+
| year(now()) |
+-------------+
|        2023 |
+-------------+
1 row in set (0.00 sec)

month() //获取月

mysql> select month(now()) ; //获取系统当前月
+--------------+
| month(now()) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

day() //获取日期

mysql> select day(now()) ; //获取系统当前日
+------------+
| day(now()) |
+------------+
|         24 |
+------------+
1 row in set (0.00 sec)

hour() //获取小时

mysql> select hour(now()) ; //获取系统当前小时
+-------------+
| hour(now()) |
+-------------+
|          17 |
+-------------+
1 row in set (0.00 sec)

minute() //获取分钟

mysql> select minute(now()) ; //获取系统当分钟
+---------------+
| minute(now()) |
+---------------+
|            46 |
+---------------+
1 row in set (0.00 sec)

second() //获取秒

mysql> select second(now()) ; //获取系统当前秒
+---------------+
| second(now()) |
+---------------+
|            34 |
+---------------+
1 row in set (0.00 sec)

time() //获取小时分钟秒

mysql> select time(now()) ;//获取当前系统时间
+-------------+
| time(now()) |
+-------------+
| 17:47:36    |
+-------------+
1 row in set (0.00 sec)

date() //获取年月日

mysql> select date(now()) ; //获取当前系统日期
+-------------+
| date(now()) |
+-------------+
| 2023-05-24  |
+-------------+
1 row in set (0.00 sec)

curdate() //获取当前系统日期，单独使用不能传参

mysql> select curdate();//获取当前系统日期
+------------+
| curdate()  |
+------------+
| 2023-05-24 |
+------------+
1 row in set (0.00 sec)

dayofmonth()//获取一个月的第几天

mysql> select dayofmonth(curdate());//获取一个月的第几天
+-----------------------+
| dayofmonth(curdate()) |
+-----------------------+
|                    24 |
+-----------------------+
1 row in set (0.00 sec)

dayofyear() //获取一年中的第几天

mysql> select dayofyear(curdate());//获取一年中的第几天
+----------------------+
| dayofyear(curdate()) |
+----------------------+
|                  144 |
+----------------------+
1 row in set (0.00 sec)
mysql>

monthname()//获取月份名

mysql> select monthname(curdate());//获取月份名
+----------------------+
| monthname(curdate()) |
+----------------------+
| May                  |
+----------------------+
1 row in set (0.00 sec)

dayname() //获取星期名

mysql> select dayname(curdate());//获取星期名
+--------------------+
| dayname(curdate()) |
+--------------------+
| Wednesday          |
+--------------------+
1 row in set (0.00 sec)

quarter()//获取一年中的第几季度

mysql> select quarter(curdate());//获取一年中的第几季度
+--------------------+
| quarter(curdate()) |
+--------------------+
|                  2 |
+--------------------+
1 row in set (0.00 sec)

week() //一年中的第几周

mysql> select week(now());//一年中的第几周
+-------------+
| week(now()) |
+-------------+
|          21 |
+-------------+
1 row in set (0.00 sec)

weekday()//一周中的周几

mysql> select weekday(now())；//一周中的周几 
+----------------+
| weekday(now()) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

步骤四：练习聚集函数(对数值类型表头下的数据做统计)

输出3号员工2018每个月的基本工资

mysql> select basic from tarena.salary where employee_id=3 and year(date)=2018;
+-------+
| basic |
+-------+
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9724 |
+-------+
12 rows in set (0.00 sec)

sum(表头名) 求和

// 3号员工2018年工资总和
mysql> select sum(basic) from tarena.salary where employee_id=3 and year(date)=2018;
+------------+
| sum(basic) |
+------------+
|     111595 |
+------------+
1 row in set (0.00 sec)

avg(表头名) 计算平均值

// 3号员工2018年平均工资
mysql> select avg(basic) from tarena.salary where employee_id=3 and year(date)=2018;
+------------+
| avg(basic) |
+------------+
|  9299.5833 |
+------------+
1 row in set (0.00 sec)

min(表头名) 获取最小值

// 3号员工2018年工资最小值
mysql> select min(basic) from tarena.salary where employee_id=3 and year(date)=2018;
+------------+
| min(basic) |
+------------+
|       9261 |
+------------+
1 row in set (0.00 sec)

max(表头名) 获取最大值

// 3号员工2018年工资最大值
mysql> select max(basic) from tarena.salary where employee_id=3 and year(date)=2018;
+------------+
| max(basic) |
+------------+
|       9724 |
+------------+
1 row in set (0.00 sec)

count(表头名) 统计表头值个数

统计3号员工2018年奖金小于3000的次数

//先查看基本工资 条件 员工编号=3 且 发工资年份是2018年 且奖金小于3000
mysql> select bonus from tarena.salary where employee_id=3 and year(date)=2018 and bonus<3000; 
+-------+
| bonus |
+-------+
|  1000 |
|  1000 |
|  1000 |
+-------+
3 rows in set (0.00 sec)
//统计次数
mysql> select count(bonus) from tarena.salary where employee_id=3 and year(date)=2018 and bonus<3000;  
+--------------+
| count(bonus) |
+--------------+
|            3 |
+--------------+
1 row in set (0.01 sec)

Mysql 常用函数_常用函数_05

输出8号员工2019年1月10 工资总和

mysql> select employee_id ,date , basic +  bonus  as 总工资 from tarena.salary 
where employee_id = 8 and date=20190110;
+-------------+------------+----------------+
| employee_id | date       |     总工资       |
+-------------+------------+----------------+
|           8 | 2019-01-10 |          24093 |
+-------------+------------+----------------+

输出8号员工的名字和年龄

mysql> select name , 2024 - year(birth_date) as 年龄 from tarena.employees
where employee_id = 8 ;
+--------+--------+
| name   | 年龄   |
+--------+--------+
| 汪云   |     29 |
+--------+--------+

查看8号员工2019年1月10 基本工资翻3倍的 值

mysql> select employee_id , basic , basic * 3  as 工资翻三倍  from tarena.salary 
where  employee_id=8  and date=20190110;
+-------------+-------+-----------------+
| employee_id | basic | 工资翻三倍      |
+-------------+-------+-----------------+
|           8 | 23093 |           69279 |
+-------------+-------+-----------------+
1 row in set (0.00 sec)

查看8号员工2019年1月10的平均工资

mysql> select employee_id ,  (basic+bonus)/2 as 平均工资  from tarena.salary where  employee_id=8  and date=20190110 ;
+-------------+--------------+
| employee_id | 平均工资     |
+-------------+--------------+
|           8 |   12046.5000 |
+-------------+--------------+
1 row in set (0.01 sec)

输出员工编号1-10之间偶数员工编号及对应的员工名

mysql> select employee_id , name  from  tarena.employees  
where  employee_id  between 1 and 10  and  employee_id % 2  =  0   ;
+-------------+-----------+
| employee_id | name      |
+-------------+-----------+
|           2 | 郭岩      |
|           4 | 张健      |
|           6 | 牛建军    |
|           8 | 汪云      |
|          10 | 郭娟      |
+-------------+-----------+
5 rows in set (0.00 sec)

步骤六：练习if函数

if(条件,v1,v2) 如果条件是TRUE则返回v1，否则返回v2

ifnull(v1,v2) 如果v1不为NULL，则返回v1，否则返回v2

演示if（） 语句的执行过程

mysql> select  if(1 = 2 , "a","b");
+---------------------+
| if(1 = 2 , "a","b") |
+---------------------+
| b                   |
+---------------------+
1 row in set (0.00 sec)
mysql> select  if( 1 = 1 , "a","b");
+---------------------+
| if(1 = 1 , "a","b") |
+---------------------+
| a                   |
+---------------------+
1 row in set (0.00 sec)
mysql>

演示ifnull（） 语句的执行过程

mysql> select  ifnull("abc","xxx");
 +---------------------+
 | ifnull("abc","xxx") |
 +---------------------+
 | abc                 |
 +---------------------+
 1 row in set (0.00 sec)
 mysql> select  ifnull(null,"xxx");
 +--------------------+
 | ifnull(null,"xxx") |
 +--------------------+
 | xxx                |
 +--------------------+
 1 row in set (0.00 sec)
 mysql>

查询例子

根据uid 号 输出用户类型

mysql> select name as 姓名 , uid  as  UID号 , 
 if(uid < 1000 , "系统用户","创建用户") as 用户类型  from tarena.user;
 +-----------------+-------+--------------+
 | 姓名             | 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 | 系统用户     |
 | nobody          |    99 | 系统用户     |
 | systemd-network |   192 | 系统用户     |
 | dbus            |    81 | 系统用户     |
 | polkitd         |   999 | 系统用户     |
 | sshd            |    74 | 系统用户     |
 | postfix         |    89 | 系统用户     |
 | chrony          |   998 | 系统用户     |
 | rpc             |    32 | 系统用户     |
 | rpcuser         |    29 | 系统用户     |
 | nfsnobody       | 65534 | 创建用户     |
 | haproxy         |   188 | 系统用户     |
 | plj             |  1000 | 创建用户     |
 | apache          |    48 | 系统用户     |
 | mysql           |    27 | 系统用户     |
 | bob             |  NULL | 创建用户     |
 +-----------------+-------+--------------+
 27 rows in set (0.00 sec)

根据shell 输出用户类型

mysql>  select name , shell  , 
 #cold_boldif(shell = "/bin/bash" , "交互用户","非交户用户") as 用户类型 from tarena.user;
 +-----------------+----------------+-----------------+
 | name            | shell          | 用户类型        |
 +-----------------+----------------+-----------------+
 | root            | /bin/bash      | 交互用户        |
 | bin             | /sbin/nologin  | 非交户用户      |
 | daemon          | /sbin/nologin  | 非交户用户      |
 | adm             | /sbin/nologin  | 非交户用户      |
 | lp              | /sbin/nologin  | 非交户用户      |
 | sync            | /bin/sync      | 非交户用户      |
 | shutdown        | /sbin/shutdown | 非交户用户      |
 | halt            | /sbin/halt     | 非交户用户      |
 | mail            | /sbin/nologin  | 非交户用户      |
 | operator        | /sbin/nologin  | 非交户用户      |
 | games           | /sbin/nologin  | 非交户用户      |
 | ftp             | /sbin/nologin  | 非交户用户      |
 | nobody          | /sbin/nologin  | 非交户用户      |
 | systemd-network | /sbin/nologin  | 非交户用户      |
 | dbus            | /sbin/nologin  | 非交户用户      |
 | polkitd         | /sbin/nologin  | 非交户用户      |
 | sshd            | /sbin/nologin  | 非交户用户      |
 | postfix         | /sbin/nologin  | 非交户用户      |
 | chrony          | /sbin/nologin  | 非交户用户      |
 | rpc             | /sbin/nologin  | 非交户用户      |
 | rpcuser         | /sbin/nologin  | 非交户用户      |
 | nfsnobody       | /sbin/nologin  | 非交户用户      |
 | haproxy         | /sbin/nologin  | 非交户用户      |
 | plj             | /bin/bash      | 交互用户        |
 | apache          | /sbin/nologin  | 非交户用户      |
 | mysql           | /bin/false     | 非交户用户      |
 | bob             | NULL           | 非交户用户      |
 +-----------------+----------------+-----------------+
 27 rows in set (0.00 sec)

插入没有家目录的用户

mysql> insert   into   tarena.user (name, homedir) values ("jerrya",null);

查看时加判断

mysql>  select name  姓名, ifnull(homedir,"NO  home")as 家目录  from  tarena.user;
 +-----------------+--------------------+
 | 姓名            | 家目录             |
 +-----------------+--------------------+
 | root            | /root              |
 | bin             | /bin               |
 | daemon          | /sbin              |
 | adm             | /var/adm           |
 | lp              | /var/spool/lpd     |
 | sync            | /sbin              |
 | shutdown        | /sbin              |
 | halt            | /sbin              |
 | mail            | /var/spool/mail    |
 | operator        | /root              |
 | games           | /usr/games         |
 | ftp             | /var/ftp           |
 | nobody          | /                  |
 | systemd-network | /                  |
 | dbus            | /                  |
 | polkitd         | /                  |
 | sshd            | /var/empty/sshd    |
 | postfix         | /var/spool/postfix |
 | chrony          | /var/lib/chrony    |
 | rpc             | /var/lib/rpcbind   |
 | rpcuser         | /var/lib/nfs       |
 | nfsnobody       | /var/lib/nfs       |
 | haproxy         | /var/lib/haproxy   |
 | plj             | /home/plj          |
 | apache          | /usr/share/httpd   |
 | mysql           | /var/lib/mysql     |
 | bob             | NO  home           |
 | jerrya          | NO  home           |
 +-----------------+--------------------+
 28 rows in set (0.00 sec)
 Mysql>

步骤七：练习case函数

命令格式

CASE 表头名              
 WHEN 值1 THEN 输出结果 
 WHEN 值2 THEN 输出结果  
 WHEN 值3 THEN 输出结果 
 ELSE 输出结果  
 END
 或
 CASE              
 WHEN  判断条件1 THEN 输出结果 
 WHEN  判断条件2 THEN 输出结果  
 WHEN  判断条件3 THEN 输出结果 
 ELSE  输出结果  
 END

如果表头名等于某个值，则返回对应位置then后面的值并结束判断，

如果与所有值都不相等，则返回else后面的结果并结束判断

查看部门表(departments)所有行

mysql> select  * from tarena.departments;
 +---------+-----------+
 | dept_id | dept_name |
 +---------+-----------+
 |       1 | 人事部    |
 |       2 | 财务部    |
 |       3 | 运维部    |
 |       4 | 开发部    |
 |       5 | 测试部    |
 |       6 | 市场部    |
 |       7 | 销售部    |
 |       8 | 法务部    |
 +---------+-----------+
 8 rows in set (0.03 sec)

输出部门类型

select dept_id, dept_name,
 case dept_name
 when '运维部' then '技术部门'
 when '开发部' then '技术部门'
 when '测试部' then '技术部门'
 else '非技术部门'
 end as  部门类型   from  tarena.departments;
 +---------+-----------+-----------------+
 | dept_id | dept_name | 部门类型        |
 +---------+-----------+-----------------+
 |       1 | 人事部    | 非技术部门      |
 |       2 | 财务部    | 非技术部门      |
 |       3 | 运维部    | 技术部门        |
 |       4 | 开发部    | 技术部门        |
 |       5 | 测试部    | 技术部门        |
 |       6 | 市场部    | 非技术部门      |
 |       7 | 销售部    | 非技术部门      |
 |       8 | 法务部    | 非技术部门      |
 +---------+-----------+-----------------+
 8 rows in set (0.00 sec)

或

mysql> select dept_id,dept_name,
     -> case
     -> when dept_name="运维部"  then "技术部"
     -> when dept_name="开发部"  then "技术部"
     -> when dept_name="测试部"  then "技术部"
     -> else "非技术部"
     -> end as 部门类型  from  tarena.departments;
 +---------+-----------+--------------+
 | dept_id | dept_name | 部门类型     |
 +---------+-----------+--------------+
 |       1 | 人事部    | 非技术部     |
 |       2 | 财务部    | 非技术部     |
 |       3 | 运维部    | 技术部       |
 |       4 | 开发部    | 技术部       |
 |       5 | 测试部    | 技术部       |
 |       6 | 市场部    | 非技术部     |
 |       7 | 销售部    | 非技术部     |
 |       8 | 法务部    | 非技术部     |
 +---------+-----------+--------------+
 8 rows in set (0.00 sec)

或

mysql> select dept_id,dept_name,
     -> case
     -> when dept_name in ("运维部","开发部","测试部") then "技术部"
     -> else "非技术部"
     -> end as 部门类型  from  tarena.departments;
 +---------+-----------+--------------+
 | dept_id | dept_name | 部门类型     |
 +---------+-----------+--------------+
 |       1 | 人事部    | 非技术部     |
 |       2 | 财务部    | 非技术部     |
 |       3 | 运维部    | 技术部       |
 |       4 | 开发部    | 技术部       |
 |       5 | 测试部    | 技术部       |
 |       6 | 市场部    | 非技术部     |
 |       7 | 销售部    | 非技术部     |
 |       8 | 法务部    | 非技术部     |
 +---------+-----------+--------------+
 8 rows in set (0.00 sec)


2 案例2：查询结果处理

2.1 问题

  1. 分组练习
  2. 排序练习
  3. 过滤练习
  4. 分页练习

2.2 方案

使用tarena库下的表完成练习

对select语句查找到的数据再做处理，语法格式

SELECT 表头名 FROM 库名.表名 [WHERE条件] 分组 | 排序 | 过滤 | 分页;

2.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：分组练习

输出符合条件的shell 和 name

mysql> select shell  , name  from  tarena.user where shell in ("/bin/bash","/sbin/nologin");
 +---------------+-----------------+
 | shell         | name            |
 +---------------+-----------------+
 | /bin/bash     | root            |
 | /sbin/nologin | bin             |
 | /sbin/nologin | daemon          |
 | /sbin/nologin | adm             |
 | /sbin/nologin | lp              |
 | /sbin/nologin | mail            |
 | /sbin/nologin | operator        |
 | /sbin/nologin | games           |
 | /sbin/nologin | ftp             |
 | /sbin/nologin | nobody          |
 | /sbin/nologin | systemd-network |
 | /sbin/nologin | dbus            |
 | /sbin/nologin | polkitd         |
 | /sbin/nologin | sshd            |
 | /sbin/nologin | postfix         |
 | /sbin/nologin | chrony          |
 | /sbin/nologin | rpc             |
 | /sbin/nologin | rpcuser         |
 | /sbin/nologin | nfsnobody       |
 | /sbin/nologin | haproxy         |
 | /bin/bash     | plj             |
 | /sbin/nologin | apache          |
 +---------------+-----------------+
 22 rows in set (0.00 sec)

统计每种解释器用户的个数 （按照shell表头值分组统计name表头值个数）

mysql> select shell as 解释器 , count(name) as 总人数  from  tarena.user where shell in ("/bin/bash","/sbin/nologin") group by shell;
 +---------------+-----------+
 | 解释器        | 总人数    |
 +---------------+-----------+
 | /bin/bash     |         2 |
 | /sbin/nologin |        20 |
 +---------------+-----------+
 2 rows in set (0.00 sec)

统计每个部门的总人数 (按照部门表头分组统计name表头值的个数)

mysql> select dept_id , count(name)  from tarena.employees group by  dept_id ;
 +---------+-------------+
 | dept_id | count(name) |
 +---------+-------------+
 |       1 |           8 |
 |       2 |           5 |
 |       3 |           6 |
 |       4 |          55 |
 |       5 |          12 |
 |       6 |           9 |
 |       7 |          35 |
 |       8 |           3 |
 +---------+-------------+
 8 rows in set (0.00 sec)

步骤二： 排序练习

查看满足条件的name和uid 字段的值

mysql> select name , uid from  tarena.user where uid is not null and uid between 100  and 1000 ;
 +-----------------+------+
 | name            | uid  |
 +-----------------+------+
 | systemd-network |  192 |
 | polkitd         |  999 |
 | chrony          |  998 |
 | haproxy         |  188 |
 | plj             | 1000 |
 +-----------------+------+
 5 rows in set (0.00 sec)

按照uid升序排序

mysql> select name , uid from  tarena.user where uid is not null and uid between 100  and 1000  order by uid asc;
 +-----------------+------+
 | name            | uid  |
 +-----------------+------+
 | haproxy         |  188 |
 | systemd-network |  192 |
 | chrony          |  998 |
 | polkitd         |  999 |
 | plj             | 1000 |
 +-----------------+------+
 5 rows in set (0.00 sec)

按照uid降序排序

mysql> select name , uid from  tarena.user where uid is not null and uid between 100  and 1000  order by uid desc;
 +-----------------+------+
 | name            | uid  |
 +-----------------+------+
 | plj             | 1000 |
 | polkitd         |  999 |
 | chrony          |  998 |
 | systemd-network |  192 |
 | haproxy         |  188 |
 +-----------------+------+
 5 rows in set (0.00 sec)

查看2015年1月10号员工编号小于10的工资总额

// 先找出满足条件的 基本工资 和 奖金 
 mysql> select employee_id , date , basic , bonus ,  basic+bonus as total  from  tarena.salary where date=20150110 and employee_id <= 10;
 +-------------+------------+-------+-------+-------+
 | employee_id | date       | basic | bonus | total |
 +-------------+------------+-------+-------+-------+
 |           2 | 2015-01-10 | 17000 | 10000 | 27000 |
 |           3 | 2015-01-10 |  8000 |  2000 | 10000 |
 |           4 | 2015-01-10 | 14000 |  9000 | 23000 |
 |           6 | 2015-01-10 | 14000 | 10000 | 24000 |
 |           7 | 2015-01-10 | 19000 | 10000 | 29000 |
 +-------------+------------+-------+-------+-------+
 5 rows in set (0.00 sec)
 //再输出每个月的工资总和
 mysql> select employee_id , basic+bonus as total  from  tarena.salary where date=20150110 and employee_id <= 10;
 +-------------+-------+
 | employee_id | total |
 +-------------+-------+
 |           2 | 27000 |
 |           3 | 10000 |
 |           4 | 23000 |
 |           6 | 24000 |
 |           7 | 29000 |
 +-------------+-------+
 5 rows in set (0.11 sec)
 //以工资总额升序排 
 mysql> select employee_id , basic+bonus as total  from  tarena.salary where date=20150110 and employee_id <= 10 order by total asc;
 +-------------+-------+
 | employee_id | total |
 +-------------+-------+
 |           3 | 10000 |
 |           4 | 23000 |
 |           6 | 24000 |
 |           2 | 27000 |
 |           7 | 29000 |
 +-------------+-------+
 5 rows in set (0.00 sec)

步骤三：过滤练习

在查找到的数据里 过滤符合条件的数据。

select 表头名 from 库.表 where 筛选条件 having 筛选条件;

查找部门总人数少于10人的部门编号及人数

//第一步，查看所有员工的部门名称
 select  dept_id ,  name    from   tarena.employees;
 
 //第二步，按部门编号分组 统计人名个数
 mysql> select  dept_id as 部门编号,  count(name) as 总人数   from   tarena.employees group by  dept_id;
 +--------------+-----------+
 | 部门编号     | 总人数    |
 +--------------+-----------+
 |            1 |         8 |
 |            2 |         5 |
 |            3 |         6 |
 |            4 |        55 |
 |            5 |        12 |
 |            6 |         9 |
 |            7 |        35 |
 |            8 |         3 |
 +--------------+-----------+
 8 rows in set (0.15 sec)
 
 //第三步 输出部门总人数 小于  10 人 的部门编号及人数
 mysql> select  dept_id as 部门编号,  count(name) as 总人数   from   tarena.employees group by  dept_id having 总人数 < 10 ;
 +--------------+-----------+
 | 部门编号     | 总人数    |
 +--------------+-----------+
 |            1 |         8 |
 |            2 |         5 |
 |            3 |         6 |
 |            6 |         9 |
 |            8 |         3 |
 +--------------+-----------+
 5 rows in set (0.00 sec)

步骤四：分页练习

分析：

作用：限制查询结果显示行数（默认显示全部查询结果）

使用SELECT查询时，如果结果集数据量很大，比如1万行数据，放在一个页面显示的话数据量太大，可以分100次显示 每次只显示100行。

语法:

SELECT语句  LIMIT  数字;            //显示查询结果前多少条记录
 SELECT语句  LIMIT  数字1,数字2;    //显示指定范围内的查询记录
 数字1  表示起始行 （0表示第1行） 数字2表示总行数

例如：

limit   1   ;  显示查询结果的第1行
 limit   3   ;  显示查询结果的前3行
 limit   10  ;  显示查询结果的前10行
 limit   0,1 ;      从查询结果的第1行开始显示，共显示1行
 limit   3,5 ;      从查询结果的第4行开始显示，共显示5行
 limit  10,10;      从查询结果的第11行开始显示，共显示10行

命令操作如下所示：

查看有解释器的用户信息

mysql> select  * from tarena.user where shell is not null ;

只显示查询结果的第1行

mysql> select *  from tarena.user where shell is not null  limit 1;

只显示查询结果的前3行

mysql> select *  from tarena.user  where shell is not null  limit  3;

仅仅显示查询结果的第1行 到 第3 （0 表示查询结果的第1行）

mysql> select  *  from user   where shell is not null  limit 0,3;

从查询结果的第4行开始显示，共显示3行

mysql> select name,uid , gid  , shell  from user  where shell is not null  limit 3,3;

查看uid 号最大的用户名和UID

mysql> select   name , uid from   tarena.user order  by   uid  desc  limit    1  ;
 +-----------+-------+
 | name      | uid   |
 +-----------+-------+
 | nfsnobody | 65534 |
 +-----------+-------+
 1 row in set (0.00 sec)


3 案例3：管理表记录

3.1 问题

  1. 练习插入表记录
  2. 练习修改表记录
  3. 练习删除表记录

3.2 方案

使用tarena库下的user表完成表记录管理的练习。

3.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：练习插入表记录

命令操作如下所示：

查看表头

mysql> desc tarena.user;
 +----------+-------------+------+-----+---------+----------------+
 | Field    | Type        | Null | Key | Default | Extra          |
 +----------+-------------+------+-----+---------+----------------+
 | id       | int         | NO   | PRI | NULL    | auto_increment |
 | name     | char(20)    | YES  |     | NULL    |                |
 | password | char(1)     | YES  |     | NULL    |                |
 | uid      | int         | YES  |     | NULL    |                |
 | gid      | int         | YES  |     | NULL    |                |
 | comment  | varchar(50) | YES  |     | NULL    |                |
 | homedir  | varchar(80) | YES  |     | NULL    |                |
 | shell    | char(30)    | YES  |     | NULL    |                |
 +----------+-------------+------+-----+---------+----------------+
 8 rows in set (0.00 sec)

插入1条记录给所有表头赋值

（给所有表头赋值表头可以省略不写）id表头的值不能重复，主键的知识在后边课程里讲

mysql> insert into tarena.user values(40,"jingyaya","x",1001,1001,"teacher","/home/jingyaya","/bin/bash");
 Query OK, 1 row affected (0.05 sec)

查看表记录

mysql> select  * from  tarena.user where name="jingyaya";
 +----+----------+----------+------+------+---------+----------------+-----------+
 | id | name     | password | uid  | gid  | comment | homedir        | shell     |
 +----+----------+----------+------+------+---------+----------------+-----------+
 | 40 | jingyaya | x        | 1001 | 1001 | teacher | /home/jingyaya | /bin/bash |
 +----+----------+----------+------+------+---------+----------------+-----------+
 1 row in set (0.00 sec)
 mysql>

插入多行记录给所有列赋值

insert into tarena.user values
 (41,"jingyaya2","x",1002,1002,"teacher","/home/jingyaya2","/bin/bash"),
 (42,"jingyaya3","x",1003,1003,"teacher","/home/jingyaya3","/bin/bash");

插入1行给指定列赋值，必须写列名，没赋值的列 没有数据 值是NULL

mysql> insert into tarena.user(name,uid,shell)values("benben",1002,"/sbin/nologin");

插入多行给指定列赋值，必须写列名，没赋值的列 没有数据 值是NULL

mysql> insert into tarena.user(name,uid,shell)values("benben2",1002,"/sbin/nologin"),("benben3",1003,"/sbin/nologin");

查看记录

mysql> select  * from tarena.user where name like  "benben%";
 +----+---------+----------+------+------+---------+---------+---------------+
 | id | name    | password | uid  | gid  | comment | homedir | shell         |
 +----+---------+----------+------+------+---------+---------+---------------+
 | 41 | benben  | NULL     | 1002 | NULL | NULL    | NULL    | /sbin/nologin |
 | 42 | benben2 | NULL     | 1002 | NULL | NULL    | NULL    | /sbin/nologin |
 | 43 | benben3 | NULL     | 1003 | NULL | NULL    | NULL    | /sbin/nologin |
 +----+---------+----------+------+------+---------+---------+---------------+
 3 rows in set (0.00 sec)

使用select查询结果赋值(查询表头个数和 插入记录表头个数要一致)

mysql> select user from  mysql.user;
 +------------------+
 | user             |
 +------------------+
 | mysql.infoschema |
 | mysql.session    |
 | mysql.sys        |
 | root             |
 +------------------+
 4 rows in set (0.00 sec)
 
 mysql> insert into tarena.user(name) (select user from  mysql.user);
 Query OK, 4 rows affected (0.09 sec)
 Records: 4  Duplicates: 0  Warnings: 0

查看插入后的数据

mysql> select  * from  tarena.user where name like "mysql%" or name="root";
 +----+------------------+----------+------+------+--------------+----------------+------------+
 | id | name             | password | uid  | gid  | comment      | homedir        | shell      |
 +----+------------------+----------+------+------+--------------+----------------+------------+
 |  1 | root             | x        |    0 |    0 | root         | /root          | /bin/bash  |
 | 26 | mysql            | x        |   27 |   27 | MySQL Server | /var/lib/mysql | /bin/false |
 | 44 | mysql.infoschema | NULL     | NULL | NULL | NULL         | NULL           | NULL       |
 | 45 | mysql.session    | NULL     | NULL | NULL | NULL         | NULL           | NULL       |
 | 46 | mysql.sys        | NULL     | NULL | NULL | NULL         | NULL           | NULL       |
 | 47 | root             | NULL     | NULL | NULL | NULL         | NULL           | NULL       |
 +----+------------------+----------+------+------+--------------+----------------+------------+
 6 rows in set (0.00 sec)

使用set命令赋值

mysql> insert into tarena.user set name="yaya" , uid=99 , gid=99 ;
 Query OK, 1 row affected (0.06 sec)
 
 mysql> select * from  tarena.user where name="yaya";
 +----+------+----------+------+------+---------+---------+-------+
 | id | name | password | uid  | gid  | comment | homedir | shell |
 +----+------+----------+------+------+---------+---------+-------+
 | 28 | yaya | NULL     |   99 |   99 | NULL    | NULL    | NULL  |
 +----+------+----------+------+------+---------+---------+-------+
 1 row in set (0.00 sec)

步骤二：练习修改表记录

命令操作如下所示：

修改前查看

mysql> select  name , comment from tarena.user where id <= 10 ;
 +----------+----------+
 | name     | comment  |
 +----------+----------+
 | root     | root     |
 | bin      | bin      |
 | daemon   | daemon   |
 | adm      | adm      |
 | lp       | lp       |
 | sync     | sync     |
 | shutdown | shutdown |
 | halt     | halt     |
 | mail     | mail     |
 | operator | operator |
 +----------+----------+
 10 rows in set (0.00 sec)

仅仅修改符合条件的

mysql> update tarena.user set comment=NULL where id <= 10 ;
 Query OK, 10 rows affected (0.09 sec)
 Rows matched: 10  Changed: 10  Warnings: 0

修改后查看

mysql> select  name , comment from tarena.user where id <= 10 ;
 +----------+---------+
 | name     | comment |
 +----------+---------+
 | root     | NULL    |
 | bin      | NULL    |
 | daemon   | NULL    |
 | adm      | NULL    |
 | lp       | NULL    |
 | sync     | NULL    |
 | shutdown | NULL    |
 | halt     | NULL    |
 | mail     | NULL    |
 | operator | NULL    |
 +----------+---------+
 10 rows in set (0.00 sec) [root@localhost ~]#

修改前查看

mysql> select name , homedir  from tarena.user;
 +------------------+--------------------+
 | name             | homedir            |
 +------------------+--------------------+
 | root             | /root              |
 | bin              | /bin               |
 | daemon           | /sbin              |
 | adm              | /var/adm           |
 | lp               | /var/spool/lpd     |
 | sync             | /sbin              |
 | shutdown         | /sbin              |
 | halt             | /sbin              |
 | mail             | /var/spool/mail    |
 | operator         | /root              |
 | games            | /usr/games         |
 | ftp              | /var/ftp           |
 | nobody           | /                  |
 | systemd-network  | /                  |
 | dbus             | /                  |
 | polkitd          | /                  |
 | sshd             | /var/empty/sshd    |
 | postfix          | /var/spool/postfix |
 | chrony           | /var/lib/chrony    |
 | rpc              | /var/lib/rpcbind   |
 | rpcuser          | /var/lib/nfs       |
 | nfsnobody        | /var/lib/nfs       |
 | haproxy          | /var/lib/haproxy   |
 | plj              | /home/plj          |
 | apache           | /usr/share/httpd   |
 | mysql            | /var/lib/mysql     |
 | bob              | NULL               |
 | jerrya           | NULL               |
 | jingyaya         | /home/jingyaya     |
 | benben           | NULL               |
 | benben2          | NULL               |
 | benben3          | NULL               |
 | mysql.infoschema | NULL               |
 | mysql.session    | NULL               |
 | mysql.sys        | NULL               |
 | root             | NULL               |
 +------------------+--------------------+
 36 rows in set (0.00 sec)

不加条件批量修改

mysql> update  tarena.user set homedir="/student" ;
 Query OK, 36 rows affected (0.09 sec)
 Rows matched: 36  Changed: 36  Warnings: 0

修改后查看

mysql> select name , homedir  from tarena.user;
 +------------------+----------+
 | name             | homedir  |
 +------------------+----------+
 | root             | /student |
 | bin              | /student |
 | daemon           | /student |
 | adm              | /student |
 | lp               | /student |
 | sync             | /student |
 | shutdown         | /student |
 | halt             | /student |
 | mail             | /student |
 | operator         | /student |
 | games            | /student |
 | ftp              | /student |
 | nobody           | /student |
 | systemd-network  | /student |
 | dbus             | /student |
 | polkitd          | /student |
 | sshd             | /student |
 | postfix          | /student |
 | chrony           | /student |
 | rpc              | /student |
 | rpcuser          | /student |
 | nfsnobody        | /student |
 | haproxy          | /student |
 | plj              | /student |
 | apache           | /student |
 | mysql            | /student |
 | bob              | /student |
 | jerrya           | /student |
 | jingyaya         | /student |
 | benben           | /student |
 | benben2          | /student |
 | benben3          | /student |
 | mysql.infoschema | /student |
 | mysql.session    | /student |
 | mysql.sys        | /student |
 | root             | /student |
 +------------------+----------+
 36 rows in set (0.00 sec)

步骤三：练习删除表记录

命令操作如下所示：

删除前查看

mysql> select  * from tarena.user where id <= 10 ;
 +----+----------+----------+------+------+---------+----------+----------------+
 | id | name     | password | uid  | gid  | comment | homedir  | shell          |
 +----+----------+----------+------+------+---------+----------+----------------+
 |  1 | root     | x        |    0 |    0 | NULL    | /student | /bin/bash      |
 |  2 | bin      | x        |    1 |    1 | NULL    | /student | /sbin/nologin  |
 |  3 | daemon   | x        |    2 |    2 | NULL    | /student | /sbin/nologin  |
 |  4 | adm      | x        |    3 |    4 | NULL    | /student | /sbin/nologin  |
 |  5 | lp       | x        |    4 |    7 | NULL    | /student | /sbin/nologin  |
 |  6 | sync     | x        |    5 |    0 | NULL    | /student | /bin/sync      |
 |  7 | shutdown | x        |    6 |    0 | NULL    | /student | /sbin/shutdown |
 |  8 | halt     | x        |    7 |    0 | NULL    | /student | /sbin/halt     |
 |  9 | mail     | x        |    8 |   12 | NULL    | /student | /sbin/nologin  |
 | 10 | operator | x        |   11 |    0 | NULL    | /student | /sbin/nologin  |
 +----+----------+----------+------+------+---------+----------+----------------+
 10 rows in set (0.00 sec)

仅删除与条件匹配的行

mysql> delete from tarena.user where id <= 10 ;
 Query OK, 10 rows affected (0.06 sec)

查不到符合条件的行了

mysql> select  * from tarena.user where id <= 10 ;
 Empty set (0.00 sec)