MySQL学习笔记6

• 一、函数
• 1、函数
• 2、字符函数
• 3、数学函数
• 4、日期函数
• 5、流程控制函数

• 1、插入一行数据
• 2、插入多行记录

一、函数

1、函数

1. 函数分类：
   分组函数：将一组值进行统计，得出一个值
   单行函数：将一个值进行处理，得出一个值

2. 调用语法：
   select 函数名(实参列表);

3. 单行函数：
   字符函数
   数学函数
   日期函数
   流程控制函数

2、字符函数

#length 获取字节长度
/*
unicode：固定字节，一个字母和一个汉字都占2个字节
gbk:一个字母占一个字节，一个汉字占2个字节
utf8：一个字母占一个字节，一个汉字占3个字节

*/
SELECT LENGTH(‘abc张三’);
SHOW VARIABLES LIKE ‘%char%’;

#concat 拼接字符
SELECT CONCAT(‘hello’,’,’,last_name) out_put
FROM employees

#upper 转换成大写，lower 转换成小写
SELECT UPPER(‘ABC’);
SELECT LOWER(‘ABC’);

#案例：将姓转大写，名转小写，然后拼接
SELECT CONCAT(UPPER(last_name),’ \’,LOWER(first_name)) 姓名
FROM employees;

#instr 获取子串第一次出现的索引，索引从1开始，如果找不到返回0
SELECT INSTR(‘张三丰爱上了三丰的郭襄’,‘杨过’);#结果 0

#substr字符串截取
SELECT SUBSTR(‘郭襄爱上了杨过’,6);
SELECT SUBSTR(‘郭襄爱上了杨过’,1,2);#索引从1开始，截取两个 #结果 郭襄

#lpad左填充、rpad右填充
SELECT LPAD(‘尹志平爱上了’,5,’’);
SELECT rPAD(‘尹志平爱上了’,18,’’); #结果 尹志平爱上了************

#trim 去前后空格或特殊字符，中间不去
SELECT LENGTH(TRIM(’ 小 龙 女 ‘)) 美女; #结果 11
SELECT LENGTH(TRIM(’ 小龙女 \’)) 美女; #结果 9
SELECT TRIM(‘a’ FROM ‘aaaaaaaqqqqqq小qq龙q女qqqqqqqqqqqqqq’) 美女;
#结果 qqqqqq小qq龙q女qqqqqqqqqqqqqq

3、数学函数

#向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.0);

#向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.0);

#四舍五入,先按绝对值四舍五入，然后加正负号
SELECT ROUND(-1.56); #-2
SELECT ROUND(1.537,1); #1.5

#随机数,返回0-1之间的小数，不包含1
SELECT RAND();

#返回50-100 a——b floor(rand()*(b-a+1)+a)
SELECT CEIL(RAND()*50+50); #50到100之间
SELECT FLOOR(RAND()*51+50);
#50到200之间的随机数
SELECT CEIL(RAND()*50+150);

#截断
SELECT TRUNCATE(1.99,1);#保留小数点后面的几位数 #1.9

4、日期函数

#获取当前日期
SELECT NOW(); #2020-07-23 10:10:22

#获取日期，没有时间
SELECT CURDATE(); 2020-07-23

#获取时间，没有日期
SELECT CURTIME(); #10:10:22

#获取日期的指定部分
SELECT YEAR(NOW()); #2020
SELECT MONTH(NOW()); #7
SELECT MONTHNAME(NOW()); #July
SELECT DAY(NOW()); #23

SELECT CONCAT(YEAR(NOW()),‘年’,MONTH(NOW()),‘月’,DAY(NOW()),‘日’) 日期;
#2020年7月23日

#判断两个日期的差别天数，前者比后者大，返回正数，小返回负数，相等返回0.
SELECT DATEDIFF(NOW(),‘2022-8-8’); #-746
SELECT DATEDIFF(NOW(),‘2017-9-25’); #1032

5、流程控制函数

#1.if函数
SELECT IF(100>9,‘不错呦’,‘不够大’) 备注;

#2.case语句
#语法：
#CASE
#WHEN 条件1 THEN 显示的值1
#WHEN 条件2 THEN 显示的值2
#…
#ELSE 显示的值n
#END

#类似于：
#switch(变量或表达式){

#CASE 值1 ：语句1;break;
#…
#DEFAULT:语句n;break;

#}
#案例：
SELECT salary,
CASE
WHEN salary>20000 THEN ‘购买汽车’ #注意数值从大到小
WHEN salary>10000 THEN ‘购买手机’
WHEN salary>5000 THEN ‘购买pad’
ELSE ‘购买棒棒糖’
END 购买资格 #别名
FROM employees;

#案例：查询部门和对应的级别

SELECT department_id,
CASE department_id
WHEN 10 THEN ‘A’
WHEN 20 THEN ‘B’
WHEN 30 THEN ‘C’
WHEN 50 THEN ‘D’
END 级别
FROM employees
where department_id is not null;

#把department_id为null的设置为F
SELECT department_id,
CASE department_id
WHEN 50 THEN ‘D’ #注意数值从大到小
WHEN 30 THEN ‘C’
WHEN 20 THEN ‘B’
WHEN 10 THEN ‘A’
ELSE ‘F’
END 级别
FROM employees

二、插入数据

1、插入一行数据

#DML语言

DML:数据操纵语言
insert、update、delete

#语法：
#INSERT INTO beauty(字段1，字段2,…)
#VALUES(值1，值2,…);
#特点：
#1、字段和值列表必须一一对应
#2、字符型和日期型必须用单引号引起来
#3、字段的顺序可以和表中字段的顺序不一致
#4、不可以为NULL的字段，必须插入值，比如说主键id；
#可以为NULL的字段，可以不用插入值，使用NULL来代替，或字段名和值都不写
#5、字段和值的个数必须一致
#6、字段列表可以省略，默认所有列 ，而且顺序和表中的列的顺序一致

SELECT * FROM beauty;

#方式一：insert into …values…

#案例1：传统的插入
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(100,‘赵丽颖’,‘女’,‘1986-1-1’,‘15811111111’,NULL,10);

#案例2：字段的插入顺序和表中的顺序不一致
INSERT INTO beauty(sex,borndate,phone,id,NAME,photo,boyfriend_id)
VALUES(‘女’,‘1986-1-1’,‘15811111111’,101,‘小燕子’,NULL,10);

#案例3：不可以为null的字段如何处理
INSERT INTO beauty(sex,borndate,phone,id,NAME,photo,boyfriend_id)
VALUES(‘女’,‘1986-1-1’,‘15811111111’,NULL,‘小鸽子’,NULL,10);

#案例4：可以为null的字段的处理
INSERT INTO beauty(id,NAME,phone)
VALUES(103,‘紫薇’,‘119’);

#案例5：字段可以省略【可读性较差】
INSERT INTO beauty
VALUES(104,‘赵丽蓉’,‘女’,‘1986-1-1’,‘15811111111’,10);

#方式二：insert into…set
INSERT INTO beauty SET id=105,NAME=‘关雎尔’,phone=‘999’;

2、插入多行记录

SELECT * FROM beauty;
#方式一：insert into…values
INSERT INTO beauty (id,NAME,phone,boyfriend_id)
VALUES(106,‘古力娜扎’,‘119’,1),
(107,‘范冰冰’,‘112’,1),
(108,‘吉克隽逸’,‘888’,1);

#方式二：insert into…select
INSERT INTO beauty(NAME,phone)
SELECT ‘唐艺昕2’,‘999’ UNION
SELECT ‘李沁2’,‘888’ UNION
SELECT ‘董洁2’,‘777’

三、数据更新

#更新语句 update
#语法：

UPDATE 表名 SET 字段名=新值,字段名=新值 [WHERE 条件];

#案例1：将赵丽蓉更新为 关晓彤
UPDATE beauty SET NAME=‘关晓彤’
WHERE NAME=‘赵丽蓉’;

#案例2：将11号的女神，更改为名字是杨紫，生日1998-1-1
UPDATE beauty SET NAME = ‘杨紫’,borndate=‘1998-1-1’
WHERE id=11;

#更新多表的数据
#语法
UPDATE 表1 别名1,表2 别名2
SET 字段1=新值，字段2=新值
WHERE 连接条件
AND 筛选条件;

#案例：将张无忌的女朋友的电话都更改为88888

UPDATE beauty b,boys bo
SET phone=‘88888’
WHERE b.

boyfriend_id

=bo.

id

AND bo.

boyName

=‘张无忌’

SELECT * FROM beauty;
SELECT * FROM boys;

四、数据删除

#数据删除

#删除的方式一：
#语法：
#DELETE FROM 表 [WHERE 条件]

#案例1：删除电话号码的长度小于11位的女神信息
DELETE FROM beauty
WHERE LENGTH(phone)<11;

#案例2：删除小燕子
DELETE FROM beauty
WHERE NAME=‘小燕子’;

#多表级联删除
DELETE 别名1,别名2 FROM 表1 别名1,表2 别名2
WHERE 连接条件
AND 筛选条件

#案例3：将张无忌的女朋友都删除
DELETE b FROM beauty b,boys bo
WHERE b.boyfriend_id=bo.id
AND bo.boyname=‘张无忌’;

#案例4：将黄晓明的女朋友信息和黄晓明都删除
DELETE b,bo FROM beauty b,boys bo
WHERE b.

boyfriend_id

=bo.

id

AND bo.

boyName

=‘黄晓明’;

#案例5：将没有男朋友的女神删除
DELETE b FROM beauty b
LEFT JOIN boys bo ON b.

boyfriend_id

=bo.

id

WHERE bo.

id

IS NULL

#删除的方式二：
#语法：
#TRUNCATE TABLE 表名

#案例1：删除beauty表的记录
TRUNCATE TABLE beauty

#二种删除方式的区别【面试题】
#1、TRUNCATE不能加WHERE条件，而DELETE可以加WHERE
#2、TRUNCATE效率较高
#3、TRUNCATE不能删除多表，而DELETE可以
#4、删除带标识列字段的表时，TRUNCATE 删除后再插入，标识列的值从1开始
#DELETE删除后再插入，标识列的值从断点处开始
#5、TRUNCATE删除后，不能回滚；DELETE删除可以回滚

SELECT * FROM beauty;
DELETE FROM beauty;

INSERT INTO beauty(NAME,phone)
SELECT ‘唐艺昕2’,‘999’ UNION
SELECT ‘李沁2’,‘888’ UNION
SELECT ‘董洁2’,‘777’