mysql实用使用方法

最新推荐文章于 2024-05-11 10:15:00 发布

嘿你别睡

最新推荐文章于 2024-05-11 10:15:00 发布

阅读量311

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/fairynini/article/details/102875501

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Mysql 使用大全

一、字段处理切割，拼接，替换字符串
二、特殊字符日期格式
三、查询，插入，更新，删除
四、逻辑运算
五、逻辑判断

一、字段处理切割，拼接，替换字符串

常用的字符串函数：

函数	用法
CONCAT(s1,s2，…)	返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL。
CONCAT_WS(x,s1,s2,…)	返回多个字符串拼接之后的字符串，每个字符串之间有一个x。
SUBSTRING(s,n,len)、MID(s,n,len)	两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串。
LEFT(s,n)、RIGHT(s,n)	前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符。
INSERT(s1,x,len,s2)	返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符。
REPLACE(s,s1,s2)	返回一个字符串，用字符串s2替代字符串s中所有的字符串s1。
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)	三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）。
FIELD(s,s1,s2,…)	返回第一个与字符串s匹配的字符串的位置。

1、字符串的拼接

1.1 CONCAT(s1,s2，…)函数

返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL。

SELECT CONCAT('现在的时间：',NOW());  -- 输出结果：现在的时间：2019-01-17 11:27:58

1.2 CONCAT_WS(x,s1,s2,…)函数

返回多个字符串拼接之后的字符串，每个字符串之间有一个x。

SELECT CONCAT_WS(';','pan_junbiao的博客','KevinPan','pan_junbiao'); -- 输出结果：pan_junbiao的博客;KevinPan;pan_junbiao

2、字符串的截取

2.1 SUBSTRING(s,n,len)、MID(s,n,len)函数

两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串。

SELECT SUBSTRING('您好，欢迎访问pan_junbiao的博客',8,14);  -- 输出结果：pan_junbiao的博客
SELECT MID('您好，欢迎访问pan_junbiao的博客',8,14);        -- 输出结果：pan_junbiao的博客

2.2 LEFT(s,n)、RIGHT(s,n)函数

前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符。

SELECT LEFT('您好，欢迎访问pan_junbiao的博客',7);   -- 输出结果：您好，欢迎访问
SELECT RIGHT('您好，欢迎访问pan_junbiao的博客',14); -- 输出结果：pan_junbiao的博客

3、字符串的替换

3.1 INSERT(s1,x,len,s2)函数

返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符。

SELECT INSERT('您好，欢迎访问阿标的博客',8,2,'pan_junbiao');  -- 输出结果：您好，欢迎访问pan_junbiao的博客

3.2 REPLACE(s,s1,s2)函数

返回一个字符串，用字符串s2替代字符串s中所有的字符串s1。

SELECT REPLACE('您好，欢迎访问阿标的博客','阿标','pan_junbiao'); -- 输出结果：您好，欢迎访问pan_junbiao的博客

4、字符串的查询位置

4.1 LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)函数

三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）。

SELECT LOCATE('pan_junbiao','您好，欢迎访问pan_junbiao的博客');       -- 输出结果：8
SELECT POSITION('pan_junbiao' IN '您好，欢迎访问pan_junbiao的博客');  -- 输出结果：8
SELECT INSTR('您好，欢迎访问pan_junbiao的博客','pan_junbiao');        -- 输出结果：8

4.2 FIELD(s,s1,s2,…)函数

返回第一个与字符串s匹配的字符串的位置。

SELECT FIELD('pan_junbiao','pan_junbiao的博客','KevinPan','阿标','pan_junbiao');  -- 输出结果：4

二、特殊字符日期格式

在将utf-8编码的txt文件导入数据库的时候，可能会遇到将不可见字符（回车，换行）导入数据的情况，这样查询就会失效，这时可以利用update和replace来替换这些字符。

空值 null 或 ‘’

 select * from t where 字段=''
 update t set 字段=null where '查询条件'
 update t set 字段=0 where 字段 is null

不改数据情况下查询：

SELECT REPLACE(REPLACE(REPLACE([字段],CHAR(9),''),CHAR(10),''),CHAR(13),'') FROM 表名 
WHERE  '查询条件'

一次性去掉所有不可见字符：

UPDATE 表名 SET  列名 = REPLACE(REPLACE(REPLACE(REPLACE(列名, CHAR(10), ''), CHAR(13), ''), CHAR(9), ''),' ','');

项目	Value
char(9)	水平制表符
char(10)	换行
char(13)	回车

日期格式可见 https://blog.csdn.net/pan_junbiao/article/details/86529602

三、查询，插入，更新，删除

select、insert、update、delete

显示列名

SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name ="lead_sf"

插入

把一个数据表查询的结果插入另一个表：
(当我们使用mysql 往不同的表插入查询另一个表的多条记录时不使用 VALUES)

INSERT INTO 表名(字段名) SELECT 字段名 FROM 表名

insert into t_a (user_id, project_id,is_delete, create_time,update_time, is_current_project)
SELECT
distinct(t_b.assigned_charge_id),1,0,NOW(),NOW(),0
from  t_b where 
t_b.is_delete = 0 and assign_type = 0 and t_b.project_id=1;
 
 
insert into t_a (user_id, project_id,is_delete, create_time,update_time, is_current_project)
SELECT
distinct(task.assigned_agent_id),1,0,NOW(),NOW(),0
from  t_b task  where 
t_b.is_delete = 0 and assign_type = 1 and t_b.project_id=1
and t_b.assigned_agent_id not in (select user_id from t_a)

插入a表中不存在b表的内容，非全部插入则加入字段

insert into lead_prd 
select * 
from sf
where not EXISTS (
select * 
from lead_prd
where sf.SaleforceId = lead_prd.SaleforceId
)

insert into lead_prd （列名，列....） 
select 列名，列.... 
from sf
where not EXISTS (
select * 
from lead_prd
where sf.SaleforceId = lead_prd.SaleforceId
)

更新

查询的结果更新到表中：update select 语句(注意:必须使用inner join)

update a inner join (select yy from b) c on a.id =c.id set a.xx = c.yy
或者
update a,b set a.xx=b.yy where a.id = c.id

UPDATE account_manager t1,accmbu t2 
SET 
t1.Sales_Name = t2.Sales_Name,
t1.Account_Type =t2.Account_Type,
t1.Parent =t2.Parent,
t1.Area_Code =t2.Area_Code,
t1.Area_Name =t2.Area_Name,
t1.Employee_Status =t2.Employee_Status,
t1.Sales_Email =t2.Sales_Email,
t1.Account_Manager_Name =t2.Account_Manager_Name,
t1.InsertSynStatus =t2.InsertSynStatus,
t1.UpdateSynStatus =t2.UpdateSynStatus
WHERE t1.Sales_Number = t2.Sales_Number

列中有，（逗号）时导出数据会分格，乱掉，因此加上“搜索后再导出。

SELECT ID,InsertSynStatus,UpdateSynStatus,SaleforceId,CONCAT('"',first_name,'"'),CONCAT('"',last_name,'"'),email,Demo_Account_Leverage__c,ATFX_Country,agri_term_condition,Entity,utm_source,utm_medium,utm_campaign,CONCAT('"',utm_term,'"'),utm_content,Landing_Page_Language,Landing_Page_Id,CXD_token,atoken,AFF_ID_c,Affiliate_Entity,Referrer_Code,Demo_Account_Balance,Referrer_Type,Referrer_Group,AA_Number,oid,orgid,lead_source,full_name,Trading_Account_Number,Existing_Account_No__c,phone,average,company,street,city,state,zip,country,Campaign_ID,signature,investment,test_check,Financial_sector,Demo_Account_Type__c,Demo_Account_Currency__c,ATFX_Hidden_Country__c,description,Create_Time,url,collaborating_with_booker__c,potential_clients__c,Email_language_lead__c,Registered_Country,Registered_City,Demo_Spread_Type__c,gaconnector_IP_Address__c,Registrant_Date__c
FROM `sf`

导入后再去掉引号。

UPDATE sf 
SET 
a.`first_name`=REPLACE(a.first_name,'"',''),
a.`last_name`=REPLACE(a.last_name,'"',''),
a.`utm_term`=REPLACE(a.utm_term,'"','')

删除

去重复项

DELETE FROM `sf` WHERE SaleforceId IN (SELECT * FROM (SELECT SaleforceId FROM `sf` GROUP BY SaleforceId HAVING COUNT(SaleforceId)>1 ) a)

四、逻辑运算

1、算术运算符

mysql采用的算术运算符和python的算术运算符基本相同，包含加（+）、减（-）、乘（*）、除（/）、取余（%）

符号	作用
+	加法运算
-	减法运算
*	乘法运算
/或 DIV	除法运算
%或 MOD	取余运算

2、比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

符号	描述	SELECT语句示例	解释
=	等于	SELECT * FROM user WHERE id =2;	查找id=2的该行信息
<>, !=	不等于	SELECT * FROM user WHERE id !=5;	查找除了id=5外其他行的信息
>	大于	SELECT * FROM user WHERE id >5;	查找所有id>5的行的信息
<	小于	SELECT * FROM user WHERE id <5;	查找所有id<5的行的信息
>=	大于等于	SELECT * FROM user WHERE id >=5;	查找所有id>=5的行的信息
<=	小于等于	SELECT * FROM user WHERE id <=5;	查找所有id<=5的行的信息
BETWEEN AND	在两值之间	SELECT * FROM user WHERE id BETWEEN 1 AND 10;	查找id介于5到10之间行的信息
IN	在集合中	SELECT * FROM user WHERE id IN (1,3,5);	查找id为集合中元素1、3、5的行的信息
NOT IN	不在集合中	SELECT * FROM user WHERE id NOT IN (1，3);	查找id不为1、3的所有行的信息
IS NULL	判断为空	SELECT *FROM user WHERE name IS NULL;	查找name为null的所有行的信息
IS NOT NULL	非空	SELECT *FROM user WHERE name IS NOT NULL;	查找name不为null的所有行的信息
LIKE	模糊查询	SELECT *FROM user WHERE name LIKE “%张%”;	查找name中有张的所有行的信息
REGEXP	正则式匹配	SELECT ‘beijing’ REGEXP ‘jing’;

3、逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回1。如果表达式是假，结果返回0。逻辑运算符又称为布尔运算符。MySQL中支持4种逻辑运算符，分别是与、或、非和异或。

符号	描述	作用	SELECT语句示例
& 或AND	与	如果所有数据不为0且不为空值（NULL），则结果返回1；如果存在任何一个数据为0，则结果返回0；	select 2 and 1;
丨或OR	或	所有数据中存在任何一个数据为非0的数字时，结果返回1；如果数据中不包含非0的数字，但包含NULL时，结果返回NULL；如果操作数中只有0时，结果返回0	select 2 or 0;
！或 NOT	非	“！”或者NOT表示“非”运算。通过“非”运算，将返回与操作数据相反的结果。如果操作数据是非0的数字，结果返回0；如果操作数据是0，结果返回1	select not 1;
XOR	异或	当其中一个表达式是真而另外一个表达式是假时，该表达式返回的结果才是真；当两个表达式的计算结果都是真或者都是假时，则返回的结果为假。	select 1 xor 1;

4、位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

符号	作用
&	按位与
丨	按位或
^	按位异或
!	取反
<<	左移
>>	右移

五、逻辑判断

1、条件控制

IF函数（判断ture，false来走下面）

IF expression THEN commands  
   [ELSEIF expression THEN commands]  
   [ELSE commands]  
   END IF;

三目运算：

IF(expr1,expr2,expr3)

如果expr1不等于0，或者expr1不等于NULL，就expr2否则expr3

IFNULL(expr1,expr2)

如果expr1不等于0，或者expr1不等于NULL，就expr1否则expr2

当然可以用case when代替。

Case When函数（相当于switch）

列名对应什么值就取什么 end后面可以加as 新字段名

CASE fieldname
	WHEN value1 THEN result1
	WHEN value2 THEN result2
	WHEN value3 THEN result3
	……
	ELSE defaultresult
END

如：
SELECT
	`id` '用户ID',
	`name` '用户名称',
	(
		CASE `state`
			WHEN 1 THEN '正常'
			WHEN 2 THEN '不正常'
			ELSE NULL
		END
	) '用户状态'
FROM
	`user`

或者

CASE
	WHEN condition1 THEN result1
	WHEN condition2 THEN result2
	WHEN condition3 THEN result3
	……
	ELSE defaultresult
END

如：
SELECT
	`id` '用户ID',
	`name` '用户名称',
	(
		CASE 
			WHEN `state`=1 THEN '正常'
			WHEN `state`=2 THEN '不正常'
			ELSE NULL
		END
	) '用户状态'
FROM
	`user`

ELT函数

ELT(condition,result1,result2,result3,...)

如：
SELECT
	`id` '用户ID',
	`name` '用户名称',
	`type`,
	ELT(type,'普通员工','中层员工','高层员工') '用户类型'
FROM
	`user`;

结果如下：
在这里插入图片描述

2、循环控制

在MySql的存储过程中可使用的循环有三种：WHILE、REPEAT、LOOP

while 控制

WHILE expression DO  
   Statements  
END WHILE

如：
DELIMITER $$  
  
DROP PROCEDURE IF EXISTS `test`.`WhileLoopProc` $$  
CREATE PROCEDURE `test`.`WhileLoopProc` ()  
BEGIN  
 DECLARE x  INT;  
 DECLARE str  VARCHAR(255);  
 SET x = 1;  
 SET str =  '';  
 WHILE x  <= 5 DO  
     SET  str = CONCAT(str,x,',');  
     SET  x = x + 1;  
 END WHILE;  
 SELECT str;  
END $$  
  
DELIMITER ;

最后输出“1,2,3,4,5,”这样的字符。

REPEAT

REPEAT好比我们编程里面的do…while一样。

REPEAT  
Statements;  
UNTIL expression  
END REPEAT

LOOP 及 LEAVE、ITERATE

这里LOOP用来标记循环；而LEAVE表示离开循环，好比编程里面的break一样；ITERATE则继续循环，类型与编程里面的continue。

DELIMITER $$  
  
DROP PROCEDURE IF EXISTS `test`.`LoopProc` $$  
CREATE PROCEDURE `test`.`LoopProc` ()  
BEGIN  
DECLARE x  INT;  
DECLARE str  VARCHAR(255);  
SET x = 1;  
SET str =  '';  
loop_label:  LOOP  
  IF  x > 10 THEN  
   LEAVE  loop_label;  
  END  IF;  
  SET  x = x + 1;  
  IF  (x mod 2) THEN  
   ITERATE  loop_label;  
  ELSE  
   SET  str = CONCAT(str,x,',');  
  END  IF;  
  
END LOOP;  
SELECT str;  
END $$  
  
DELIMITER ;

上面代码输出10以内的偶数，用逗号分隔。这里注意到x>10的时候就LEAVE掉，如果遇到奇数则ITERATE，奇数对2取模为1，表示true

嘿你别睡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql实用使用方法

mysql 的文本处理字段处理切割，拼接，替换字符串1、字符串的拼接1.1 CONCAT(s1,s2，...)函数1.2 CONCAT_WS(x,s1,s2,...)函数2、字符串的截取2.1 SUBSTRING(s,n,len)、MID(s,n,len)函数2.2 LEFT(s,n)、RIGHT(s,n)函数3、字符串的替换3.1 INSERT(s1,x,len,s2)函数3.2 REPLACE...
复制链接

扫一扫