Linux学习--MySQL学习之查询语句

这篇具有很好参考价值的文章主要介绍了Linux学习--MySQL学习之查询语句。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

所有实现基于mysql8.0.26实现,事例代码

1:常用函数

  • 字符函数
  • 数学函数
  • 日期函数
  • 聚集函数
  • 数学计算
  • if函数
  • case函数
函数:MySQL服务内置命令
语法:函数名(表头名)
select格式
SELECT  函数(表头名)  FROM  库名.表名;
SELECT  函数(表头名)  FROM  库名.表名 WHERE 条件;
1.字符函数(处理字符或字符类型的表头)
mysql> use tarena;
Database changed
mysql> select name from user where name='root';
+------+
| name |
+------+
| root |
+------+
1 row in set (0.00 sec)
#获取字符字节长度length
mysql> select name,length(name) as 字节个数 from user where name='root';
+------+--------------+
| name | 字节个数     |
+------+--------------+
| root |            4 |
+------+--------------+
1 row in set (0.00 sec)
mysql> select name,length(name) from employees where employee_id=3;
+-----------+--------------+
| name      | length(name) |
+-----------+--------------+
| 李玉英    |            9 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql> select name from employees where employee_id=3;
+-----------+
| name      |
+-----------+
| 李玉英    |
+-----------+
1 row in set (0.00 sec)
#获取字符长度char_length
mysql> select name,char_length(name) from employees where employee_id=3;
+-----------+-------------------+
| name      | char_length(name) |
+-----------+-------------------+
| 李玉英    |                 3 |
+-----------+-------------------+
1 row in set (0.00 sec)

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

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

mysql> select lower("ABCD");
+---------------+
| lower("ABCD") |
+---------------+
| abcd          |
+---------------+
1 row in set (0.00 sec)
#lcase将信息转换为小写
mysql> select lcase("ABCD");
+---------------+
| lcase("ABCD") |
+---------------+
| abcd          |
+---------------+
1 row in set (0.00 sec)

mysql> select name from employees where employee_id <=3;
+-----------+
| name      |
+-----------+
| 梁伟      |
| 郭岩      |
| 李玉英    |
+-----------+
3 rows in set (0.00 sec)
//substr(s,start,end)从s的start位置开始取出end长度的子串
mysql> select substr(name,2,3) from employees where employee_id<=3;
+------------------+
| substr(name,2,3) |
+------------------+
||
||
| 玉英             |
+------------------+
3 rows in set (0.00 sec)
mysql> select instr(name,'a') from user where uid <=3;
+-----------------+
| instr(name,'a') |
+-----------------+
|               0 |
|               0 |
|               2 |
|               1 |
+-----------------+
4 rows in set (0.00 sec)
#instr(str,str1)返回str1参数,在str参数内的位置
mysql> select name,instr(name,"英") from employees;
+-----------+-------------------+
| name      | instr(name,"英")  |
+-----------+-------------------+
| 梁伟      |                 0 |
| 郭岩      |                 0 |
| 李玉英    |                 3 |
| 张健      |                 0 |
| 郑静      |                 0 |
| 牛建军    |                 0 |
| 刘斌      |                 0 |
| 汪云      |                 0 |
...
#去除字符串两边空格
mysql> select trim("    ABC  ");
+-------------------+
| trim("    ABC  ") |
+-------------------+
| ABC               |
+-------------------+
1 row in set (0.00 sec)
#2.数学函数(处理数字或数值类型的表头)
//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)
#3.日期时间函数
![日期时间函数](https://img-blog.csdnimg.cn/cdda5eb7e0f34d9aaf432774b9b5ebbb.png)
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)
#4.聚合函数
#求和函数sum
mysql> select sum(basic) from salary where employee_id=3 and year(date)=2018;
+------------+
| sum(basic) |
+------------+
|     111595 |
+------------+
1 row in set (0.00 sec)
#avg(表头名) 计算平均值
mysql> select avg(basic) from salary where employee_id=3 and year(date)=2018;
+------------+
| avg(basic) |
+------------+
|  9299.5833 |
+------------+
1 row in set (0.00 sec)
#min(表头名) 获取最小值
mysql> select min(basic) from 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 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 count(bonus) from salary where employee_id=3 and year(date)=2018 and bonus<3000;
+--------------+
| count(bonus) |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)
#5.数学计算
![数学计算](https://img-blog.csdnimg.cn/3f38703a72b14b3fa23de4c7dbdec92c.png)
#输出8号员工2019年1月10工资总和
mysql> select employee_id ,date , basic +  bonus  as 总工资 from salary 
where employee_id = 8 and date=20190110;
+-------------+------------+----------------+
| employee_id | date       |     总工资       |
+-------------+------------+----------------+
|           8 | 2019-01-10 |          24093 |
+-------------+------------+----------------+
#输出8号员工的名字和年龄
mysql> select name,year(now())-year(birth_date) as 年龄 from employees where employee_id=8;
+--------+--------+
| name   | 年龄   |
+--------+--------+
| 汪云   |     30 |
+--------+--------+
1 row in set (0.00 sec)
#查看8号员工2019年1月10 基本工资翻3倍的值
mysql> select employee_id , basic , basic * 3  as 工资翻三倍  from salary 
where  employee_id=8  and date=20190110;
+-------------+-------+-----------------+
| employee_id | basic | 工资翻三倍      |
+-------------+-------+-----------------+
|           8 | 23093 |           69279 |
+-------------+-------+-----------------+
1 row in set (0.00 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)
#6.if函数
if(条件,v1,v2) 如果条件是TRUE则返回v1,否则返回v2
ifnull(v1,v2) 如果v1不为NULL,则返回v1,否则返回v2
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> 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> 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 | 系统用户     |
...
+-----------------+-------+--------------+
27 rows in set (0.00 sec)
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 | 非交户用户      |
...
+-----------------+----------------+-----------------+
27 rows in set (0.00 sec)

mysql> insert   into   user (name, homedir) values ("jerrya",null);
mysql>  select name  姓名, ifnull(homedir,"NO  home")as 家目录  from  tarena.user;
+-----------------+--------------------+
| 姓名            | 家目录             |
+-----------------+--------------------+
| root            | /root              |
| bin             | /bin               |
| daemon          | /sbin              |
...
| apache          | /usr/share/httpd   |
| mysql           | /var/lib/mysql     |
| bob             | NO  home           |
| jerrya          | NO  home           |
+-----------------+--------------------+
28 rows in set (0.00 sec)
#7.case函数
命令格式
CASE 表头名              
WHEN1 THEN 输出结果 
WHEN2 THEN 输出结果  
WHEN3 THEN 输出结果 
ELSE 输出结果  
ENDCASE              
WHEN  判断条件1 THEN 输出结果 
WHEN  判断条件2 THEN 输出结果  
WHEN  判断条件3 THEN 输出结果 
ELSE  输出结果  
END
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:查询结果处理

- 分组
- 排序
- 过滤
- 分页
语法格式
SELECT 表头名 FROM 库名.表名 [WHERE条件] 分组 | 排序 | 过滤 | 分页;
#1.分组练习
#输出符合条件的shell和name
mysql> select shell as 解释器,count(name) as 总人数 from user where shell in ("/bin/bash","/sbin/nologin") group by shell;
+---------------+-----------+
| 解释器        | 总人数    |
+---------------+-----------+
| /bin/bash     |         2 |
| /sbin/nologin |        20 |
+---------------+-----------+
2 rows in set (0.00 sec)
#统计每个部门的总人数
mysql> select dept_name, emp.dept_id,count(name) from employees emp,departments dep where emp.dept_id=dep.dept_id group by emp.dept_id;
+-----------+---------+-------------+
| dept_name | 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)
#2.排序
#按uid升序排序
mysql> select name,uid from user where uid is not null and uid between 100 and 1000 order by uid;
+-----------------+------+
| 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 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 *,basic+bonus as 工资总额 from salary where employee_id<10 and date='20150110';
+----+------------+-------------+-------+-------+--------------+
| id | date       | employee_id | basic | bonus | 工资总额     |
+----+------------+-------------+-------+-------+--------------+
|  2 | 2015-01-10 |           2 | 17000 | 10000 |        27000 |
|  3 | 2015-01-10 |           3 |  8000 |  2000 |        10000 |
|  4 | 2015-01-10 |           4 | 14000 |  9000 |        23000 |
|  6 | 2015-01-10 |           6 | 14000 | 10000 |        24000 |
|  7 | 2015-01-10 |           7 | 19000 | 10000 |        29000 |
+----+------------+-------------+-------+-------+--------------+
5 rows in set (0.00 sec)
#以工资总额升序排,总额相同按员工编号升序排
mysql> select *,basic+bonus as total from salary where date=20150110 and employee_id<10  order by total,employee_id;
+----+------------+-------------+-------+-------+-------+
| id | date       | employee_id | basic | bonus | total |
+----+------------+-------------+-------+-------+-------+
|  3 | 2015-01-10 |           3 |  8000 |  2000 | 10000 |
|  4 | 2015-01-10 |           4 | 14000 |  9000 | 23000 |
|  6 | 2015-01-10 |           6 | 14000 | 10000 | 24000 |
|  2 | 2015-01-10 |           2 | 17000 | 10000 | 27000 |
|  7 | 2015-01-10 |           7 | 19000 | 10000 | 29000 |
+----+------------+-------------+-------+-------+-------+
5 rows in set (0.00 sec)
#3.过滤
#查找到的数据里过滤符合条件的数据
select 表头名  from.where 筛选条件  having     筛选条件;
#查找部门总人数少于10人的部门名称及人数
mysql> select dept_id,count(name) as total  from employees emp group by dept_id having total<10;
+---------+-------+
| dept_id | total |
+---------+-------+
|       1 |     8 |
|       2 |     5 |
|       3 |     6 |
|       6 |     9 |
|       8 |     3 |
+---------+-------+
5 rows in set (0.00 sec)
mysql> select dep.dept_name,count(name) as total  from employees emp,departments dep where dep.dept_id = emp.dept_id  group by dep.dept_id having total<10;
+-----------+-------+
| dept_name | total |
+-----------+-------+
| 人事部    |     8 |
| 财务部    |     5 |
| 运维部    |     6 |
| 市场部    |     9 |
| 法务部    |     3 |
+-----------+-------+
5 rows in set (0.00 sec)
#4.分页
#限制查询结果显示行数(默认显示全部查询结果)
#使用SELECT查询时,如果结果集数据量很大,比如1万行数据,放在一个页面显示的话数据量太大,可以分100次显示 每次只显示100行。
#语法
SELECT语句  LIMIT  数字;            //显示查询结果前多少条记录
SELECT语句  LIMIT  数字1,数字2;    //显示指定范围内的查询记录
数字1  表示起始行 (0表示第1行) 数字2表示总行数

#只显示查询结果的第1行
mysql> select * from user where shell is not null limit 1;
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid  | gid  | comment | homedir | shell     |
+----+------+----------+------+------+---------+---------+-----------+
|  1 | root | x        |    0 |    0 | root    | /root   | /bin/bash |
+----+------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)
#只显示查询结果的前3行
mysql> select * from user where shell is not null limit 3;
+----+--------+----------+------+------+---------+---------+---------------+
| id | name   | password | uid  | gid  | comment | homedir | shell         |
+----+--------+----------+------+------+---------+---------+---------------+
|  1 | root   | x        |    0 |    0 | root    | /root   | /bin/bash     |
|  2 | bin    | x        |    1 |    1 | bin     | /bin    | /sbin/nologin |
|  3 | daemon | x        |    2 |    2 | daemon  | /sbin   | /sbin/nologin |
+----+--------+----------+------+------+---------+---------+---------------+
3 rows in set (0.00 sec)
#从查询结果的第3行
mysql> mysql> select * from user where shell is not null limit 3,4;
+----+----------+----------+------+------+----------+----------------+----------------+
| id | name     | password | uid  | gid  | comment  | homedir        | shell          |
+----+----------+----------+------+------+----------+----------------+----------------+
|  4 | adm      | x        |    3 |    4 | adm      | /var/adm       | /sbin/nologin  |
|  5 | lp       | x        |    4 |    7 | lp       | /var/spool/lpd | /sbin/nologin  |
|  6 | sync     | x        |    5 |    0 | sync     | /sbin          | /bin/sync      |
|  7 | shutdown | x        |    6 |    0 | shutdown | /sbin          | /sbin/shutdown |
+----+----------+----------+------+------+----------+----------------+----------------+
4 rows in set (0.00 sec)
#查看uid 号最大的用户名和UID
mysql> select name,uid from user order by uid desc limit 1;
+-----------+-------+
| name      | uid   |
+-----------+-------+
| nfsnobody | 65534 |
+-----------+-------+
1 row in set (0.00 sec)

3:管理表记录

- 插入表记录
- 修改表记录
- 删除表记录

#插入1条记录
mysql> insert into 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)
#插入多行记录给所有列赋值
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)

#2.修改表记录
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    |
...
| 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           |
...
| 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 |
...
| mysql.sys        | /student |
| root             | /student |
+------------------+----------+
36 rows in set (0.00 sec)
#3.删除表记录
//仅删除与条件匹配的行
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)

文章来源地址https://www.toymoban.com/news/detail-698555.html

到了这里,关于Linux学习--MySQL学习之查询语句的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Linux5.7 MySQL 高级(进阶) SQL 语句

    第四章 LINUX MySQL 高级(进阶) SQL 语句 一、高级SQL 语句 1.SELECT 显示表格中一个或数个字段的所有数据记录 2.DISTINCT 不显示重复的数据记录 3.WHERE 有条件查询 4.AND OR 且 或 5.IN 显示已知的值的数据记录 6.BETWEEN 显示两个值范围内的数据记录 7.通配符 通常通配符都是跟 LIKE 一起使用

    2024年02月09日
    浏览(48)
  • 07mysql查询语句之子查询

    #1.查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name,salary FROM employees WHERE department_id IN (             SELECT department_id             FROM employees             WHERE last_name = \\\'Zlotkey\\\' ); #2.查询工资比公司平均工资高的员工的员工号,姓名和工资。 SELECT employee_id,last_name

    2024年02月15日
    浏览(38)
  • 基于Linux操作系统中的MySQL数据库SQL语句(三十一)

    MySQL数据库SQL语句 目录 一、SQL语句类型 1、DDL 2、DML 3、DCL 4、DQL 二、数据库操作 1、查看 2、创建 2.1、默认字符集 2.2、指定字符集 3、进入  4、删除 5、更改 6、练习 三、数据表操作 (一)数据类型 1、数值类型 1.1、TINYINT 1.2、SMALLINT 1.3、INT 1.4、BIGINT 1.5、FLOAT(M,D) 2、时间

    2024年02月15日
    浏览(63)
  • MySQL数据库查询语句之组函数,子查询语句

    以组为操作单位,一组数据得到一个结果。 在没有手动分组的前提下,整张表默认为一组数据 max(列名):获取最大值 min(列名):获取最小值 sum(列名):获取总和 avg(列名):获取平均值 count(列名):统计值的个数 所有组函数都会自动忽略null值 在某些情况下,我们需要根据需要

    2024年01月23日
    浏览(56)
  • Mysql查询语句大全

    2024年02月11日
    浏览(44)
  • MySQL 查询语句大全

    目录 基础查询 直接查询 AS起别名 去重(复)查询 条件查询 算术运算符查询 逻辑运算符查询 正则表达式查询⭐ 模糊查询 范围查询 是否非空判断查询 排序查询  限制查询(分页查询) 随机查询 分组查询 HAVING 高级查询 子查询(嵌套查询) 排号  排名  聚合函数 连接查询

    2024年02月13日
    浏览(33)
  • MySQL中基础查询语句

    用户表user数据如下: id device_id gender age university province 1 2138 male 21 北京大学 Beijing 2 3214 male 复旦大学 Shanghai 3 6543 famale 20 北京大学 Deijing 4 2315 female  23   浙江大学 ZheJiang 5 5432 male 25 山东大学 Shandong 1,写出ddl语句创建如上表,插入数据,查询所有数据   执行结果如下: ​

    2024年02月13日
    浏览(32)
  • 看一眼Mysql查询语句

    目录 🛻 查询数据 🛻基本查询语句 🛻单表查询 🚕查询所有字段 🚕查询指定字段 🚕查询指定记录 🚕带in的查询 🚕带between and的范围查询 🚕带like的字符匹配查询 🚕查询空值 🚕带and的多条件查询 🚕带or的多条件查询 🚕查询结果不重复 🚕对查询结果排序 🛻单

    2024年02月12日
    浏览(70)
  • 【SQL】MySQL的查询语句

    MySQL是一种广泛使用的关系型数据库管理系统,它被广泛地应用于各种应用程序和网站。学会使用MySQL的查询语句可以帮助我们更好地管理和分析数据,从而更好地利用数据库中的信息。在本篇博客中,我们将深入学习MySQL的查询语句,包括 SELECT 语句、 WHERE 子句、 JOIN 语句等

    2024年02月06日
    浏览(44)
  • 【MySQL】——Select查询语句(其二)

    🎃个人专栏: 🐬 算法设计与分析:算法设计与分析_IT闫的博客-CSDN博客 🐳Java基础:Java基础_IT闫的博客-CSDN博客 🐋c语言:c语言_IT闫的博客-CSDN博客 🐟MySQL:数据结构_IT闫的博客-CSDN博客 🐠数据结构:​​​​​​数据结构_IT闫的博客-CSDN博客 💎C++:C++_IT闫的博客-CSDN博

    2024年02月08日
    浏览(47)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包