所有实现基于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 表头名
WHEN 值1 THEN 输出结果
WHEN 值2 THEN 输出结果
WHEN 值3 THEN 输出结果
ELSE 输出结果
END
或
CASE
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
文章来源:https://www.toymoban.com/news/detail-698555.html
到了这里,关于Linux学习--MySQL学习之查询语句的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!