MySQL 数据库学习（二）常用函数与查询结果的处理

久一

已于 2023-11-01 12:28:01 修改

阅读量471

点赞数

分类专栏： MySQL 数据库学习文章标签：数据库 mysql 学习 linux 服务器运维

于 2023-08-22 23:42:53 首次发布

本文链接：https://blog.csdn.net/2301_79227925/article/details/132439305

版权

MySQL 数据库学习专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一常用函数

1 函数介绍

介绍

MySQL服务内置命令

语法：函数名(表头名)

格式

SELECT 函数(表头名) FROM 库名.表名;

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

2 环境准备

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

查看tarena库employees表的表头

图-1

查看tarena库employees表表记录

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

图-2

tarena库下的salary表保存2015以后的工资信息如图-3，图-4

查看tarena库salary表的表头

图-3

查看tarena库salary表表记录

行号发工资日期员工编号基本工资奖金

图-4

3 字符函数

作用：处理字符或字符类型的表头

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删除了两边空格之后的字符串

//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>

4 数学函数

作用：处理数字或数值类型的表头

ABS(x)	返回x的绝对值
PI（）	返回圆周率π，默认显示6位小数
MOD(x,y)	返回x被y除后的余数
CEIL(x)、CEILING(x)	返回不小于x的最小整数 (x 是小数)
FLOOR(x)	返回不大于x的最大整数 (x 是小数)

//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)

5 日期函数

作用：获取系统或指定的日期与时间

mysql> select curtime(); //获取系统时间
+-----------+
| curtime() |
+-----------+
| 17:42:20  |
+-----------+
1 row in set (0.00 sec)
mysql> select curdate();//获取系统日期
+------------+
| curdate()  |
+------------+
| 2023-05-24 |
+------------+
1 row in set (0.00 sec)
mysql> select now() ;//获取系统日期+时间
+---------------------+
| now()               |
+---------------------+
| 2023-05-24 17:42:29 |
+---------------------+
1 row in set (0.00 sec)
mysql> select year(now()) ; //获取系统当前年
+-------------+
| year(now()) |
+-------------+
|        2023 |
+-------------+
1 row in set (0.00 sec)
mysql> select month(now()) ; //获取系统当前月
+--------------+
| month(now()) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)
mysql> select day(now()) ; //获取系统当前日
+------------+
| day(now()) |
+------------+
|         24 |
+------------+
1 row in set (0.00 sec)
mysql> select hour(now()) ; //获取系统当前小时
+-------------+
| hour(now()) |
+-------------+
|          17 |
+-------------+
1 row in set (0.00 sec)
mysql> select minute(now()) ; //获取系统当分钟
+---------------+
| minute(now()) |
+---------------+
|            46 |
+---------------+
1 row in set (0.00 sec)
mysql> select second(now()) ; //获取系统当前秒
+---------------+
| second(now()) |
+---------------+
|            34 |
+---------------+
1 row in set (0.00 sec)
mysql> select time(now()) ;//获取当前系统时间
+-------------+
| time(now()) |
+-------------+
| 17:47:36    |
+-------------+
1 row in set (0.00 sec)
mysql> select date(now()) ; //获取当前系统日期
+-------------+
| date(now()) |
+-------------+
| 2023-05-24  |
+-------------+
1 row in set (0.00 sec)
mysql> select curdate();//获取当前系统日志
+------------+
| curdate()  |
+------------+
| 2023-05-24 |
+------------+
1 row in set (0.00 sec)
mysql> select dayofmonth(curdate());//获取一个月的第几天
+-----------------------+
| dayofmonth(curdate()) |
+-----------------------+
|                    24 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select dayofyear(curdate());//获取一年中的第几天
+----------------------+
| dayofyear(curdate()) |
+----------------------+
|                  144 |
+----------------------+
1 row in set (0.00 sec)
mysql>
mysql> select monthname(curdate());//获取月份名
+----------------------+
| monthname(curdate()) |
+----------------------+
| May                  |
+----------------------+
1 row in set (0.00 sec)
mysql> select dayname(curdate());//获取星期名
+--------------------+
| dayname(curdate()) |
+--------------------+
| Wednesday          |
+--------------------+
1 row in set (0.00 sec)
mysql> select quarter(curdate());//获取一年中的第几季度
+--------------------+
| quarter(curdate()) |
+--------------------+
|                  2 |
+--------------------+
1 row in set (0.00 sec)
mysql> select week(now());//一年中的第几周
+-------------+
| week(now()) |
+-------------+
|          21 |
+-------------+
1 row in set (0.00 sec)
mysql> select weekday(now())；//一周中的周几 
+----------------+
| weekday(now()) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

6 聚集函数

作用：数据统计命令

avg(字段名)	计算平均值
sum(字段名)	求和
min(字段名)	获取最小值
max(字段名)	获取最大值
count(字段名)	统计字段值个数

avg(表头名) 计算平均值

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)

sum(表头名) 求和

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)

min(表头名) 获取最小值

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(表头名) 获取最大值

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的奖金
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)
//统计3号员工2018年奖金小于3000的次数
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)

对数值类型表头下的数据做统计：输出3号员工2018每个月的基本工资

7 数学计算

作用：对行中的列做计算

输出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 , 2022 - 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)

8 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 , uid  , 
#cold_boldif(uid < 1000 , "系统用户","创建用户") as 用户类型  from tarena.user;
+-----------------+-------+--------------+
| 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 | 系统用户     |
| 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>

9 case函数

语法：

如果字段名等于某个值，则返回对应位置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)

二查询结果处理

1 介绍

说明：对select语句查找到的数据再做处理

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

2 环境准备

使用tarena库下的表完成练习，对select语句查找到的数据再做处理

3 分组

语法:

SELECT 表头名，...…… FROM 表名 WHERE 条件 GROUP BY 表头名;

说明:

1、查询列表必须是分组函数和出现在GROUP BY后面的字段

2、字段中值相同的为一组

命令操作如下所示：

输出符合条件的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)

4 排序

语法：

命令操作如下所示：

（1）查看满足条件记录的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)

（2）按照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)

（3）按照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)

（4）查看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)

（5）以工资总额升序排，总额相同按照员工编号升序排

mysql> select employee_id , basic+bonus as total  from  tarena.salary where date=20150110 and employee_id <= 10 order by total asc ,employee_id asc;
+-------------+-------+
| employee_id | total |
+-------------+-------+
|           3 | 10000 |
|           4 | 23000 |
|           6 | 24000 |
|           2 | 27000 |
|           7 | 29000 |
+-------------+-------+
5 rows in set (0.00 sec)

5 过滤

作用：

在查找到的数据里筛选符合条件的数据

语法：

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

命令操作如下所示：

查找部门总人数少于10人的部门名称及人数

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

6 分页

作用：

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

使用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)

三管理表记录

1 插入表记录

命令操作如下所示：

查看表头

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行给指定列赋值，必须写列名，没赋值的列 没有数据 后通过设置的默认值赋值

mysql> insert into tarena.user(name,uid,shell)values("benben",1002,"/sbin/nologin");
插入多行给指定列赋值，必须写列名，没赋值的列 没有数据 后通过设置的默认值赋值

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)

2 修改表记录

语法格式

命令操作如下所示：

//修改前查看
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)

3 删除表记录

语法格式

命令操作如下所示

//删除前查看
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)