mysql开发常用SQL技巧方法总结

最新推荐文章于 2024-09-25 11:06:23 发布
weixin_33713503
最新推荐文章于 2024-09-25 11:06:23 发布
阅读量96
点赞数
文章标签： java 数据库
原文链接：https://my.oschina.net/xiejunbo/blog/517268
版权
为什么80%的码农都做不了架构师？>>>
1.使用自定义函数封装数据操作：
如：封装性别，查询性别时只需传入性别参数：select get_sex('1');
创建自定义函数SQL如下：

DELIMITER $$

USE `cn`$$

DROP FUNCTION IF EXISTS `get_sex`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_sex`(sex VARCHAR(2)) RETURNS VARCHAR(2) CHARSET utf8
BEGIN
    DECLARE return_str VARCHAR(2) DEFAULT '';
    
	IF (sex='1') THEN
	   SET return_str = '男';
	ELSEIF (sex='2') THEN
	   SET return_str = '女';
	ELSE 
	   SET return_str = '';
	END IF;
	RETURN return_str;
    END$$

DELIMITER ;

=================================================================================================
2.调用mysql自带的函数格式化输出参数：
如：格式化输出日期时间：  
select DATE_FORMAT(create_date,'%y-%m-%d %H:%i') AS create_date from t_user

根据字段值做判断并格式化输出：
SELECT 
 IFNULL(money,'0.00') AS money,
 CASE WHEN STATUS = '1'
 THEN '待处理'
 WHEN STATUS = '2'
 THEN '审核拒绝'
 WHEN STATUS = '3'
 THEN '处理中'
 ELSE '转账成功' 
 END AS remark,
 DATE_FORMAT(create_date,'%Y-%m-%d %T') AS date,
 transfer_type
 FROM t_user
 ==============================================================================================
 3.合并查询结果并插入的SQL:
 INSERT INTO t_doctor_money_apply (
		  user_id,
		  money,
		  `name`,
		) 
		SELECT 
		  user_id,
		  balance as money,
		  `name`,
		 FROM t_user 
		WHERE user_id = 1 limit 1	
			
===========================================================================================
 4.字段值算术SQL：
 UPDATE t_user SET read_num = read_num + 1
 
 ===========================================================================================
 5.mybatis中传参数：
 表结构字段用$，如：${tableName},${field}
 字符类型参数用#,如：#{userName}
 整型参数用$,如: ${offset}
 
 ===========================================================================================
 6.数据类型转换：
 select  cast(user_id as char) as user_id,
	 user_name,
	 user_type,
	 cast(friend_id as char) as friend_id 
 from t_user
 
 ===========================================================================================
 7.使用自定义存储过程：
如：mybatis调用存储过程并传递参数： 
 {call t_user_procedure(?,?,?,?,?,?)}
 
 ===========================================================================================
 8.left join on: 左边是主表，右边是附表，on是连接点，where接条件
 SELECT (
    (SELECT COUNT(1) 
     FROM t_patient_follow_consult AS t1 
     LEFT JOIN t_disease AS t2
     ON t2.`disease_id`=t1.`disease_id`
     WHERE t1.`patient_id`=#{patient_id} AND t1.`doctor_id`=#{doctor_id})
 ) AS count
 
 ==================================================================================
 9.数据库操作：
 获取数据库的所有表名：
　SELECT table_name FROM information_schema.tables WHERE table_schema=#{db}
　获取表的所有字段：
　SHOW COLUMNS FROM ${table}
　
　=================================================================================
　10.当天新记录：
  create_date > DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')
　当月新记录：
　create_date > DATE_SUB(TIMESTAMP(DATE(SYSDATE())),INTERVAL DAYOFMONTH(TIMESTAMP(DATE(SYSDATE())))-1 DAY)
    =================================================================================
　11.按中文名的首字母排序
　ORDER BY CONVERT(username USING gbk) COLLATE gbk_chinese_ci ASC
　
　=====================================================================================
　12.mysql全文检索
　当检索英文时，直接检索。
　
　当检索中文时：将中文转成unicode存储，检索时，把关键字转为unicode搜索。
　如：key='俊',转成unicode为：u4FCA ,搜索的字段名为n=unicode_name,则:
　
　SELECT * FROM `user` WHERE MATCH(unicode_name)AGAINST('u4FCA' IN NATURAL LANGUAGE MODE);
　
  ====================================================================================
 13.计算地球表面两点距离(开发附近的XX，距离，单位km)
 两点坐标：A(latA,lonA) B(latB,lonB)
 距离为：
 SELECT 12733.129728*ASIN(SQRT(POWER(SIN((latA-latB)*PI()/360),2)+COS(latA*PI()/180)*COS(latB*PI()/180)*POWER(SIN((lonA-lonB)*PI()/360),2))) AS dist
 如：A(40,113.55) B(42,113.21)
SELECT 12733.129728*ASIN(SQRT(POWER(SIN((40-42)*PI()/360),2)+COS(40*PI()/180)*COS(42*PI()/180)*POWER(SIN((113.55-113.21)*PI()/360),2))) AS dist
======================================================================================= 
14. mybatis调用存储过程,例如:
<update id="doctorapplymoney"  parameterMap="applyMoneyMap"
		statementType="CALLABLE" >
  {call doctor_applymoney_procedure(?,?,?,?,?,?,?,?,?)}   
</update>
=======================================================================================
15.比like性能更好方案
SELECT * FROM t_medicine WHERE medicine_name like '%神经%';
SELECT * FROM t_medicine WHERE LOCATE('神经',medicine_name) > 0 ;

16.两张表
CREATE TABLE t_sub_account_doctor(
sub_id INT(11) PRIMARY KEY AUTO_INCREMENT,
friendlyName VARCHAR(30) NOT NULL COMMENT '子账户名称。可由英文字母和阿拉伯数字组成子账户唯一名称，推荐使用电子邮箱地址',
subAccountSid VARCHAR(35) NOT NULL COMMENT '子账户Id。由32个英文字母和阿拉伯数字组成的子账户唯一标识符',
subToken VARCHAR(32) NOT NULL COMMENT '子账户的授权令牌',
voipAccount VARCHAR(14) NOT NULL COMMENT 'VoIP号码,用于客户端SDK登录，作为语音通话及IM功能的唯一标识帐号',
voipPwd VARCHAR(8) NOT NULL COMMENT 'VoIP密码',
dateCreated TIMESTAMP NOT NULL COMMENT '子账户的创建时间',
doctor_id INT(11) NOT NULL COMMENT '医生ID',
CONSTRAINT FOREIGN KEY (doctor_id) REFERENCES t_studio_doctor(doctor_id)
) COMMENT '融联云医生子账号表';

CREATE TABLE t_sub_account_patient(
sub_id INT(11) PRIMARY KEY AUTO_INCREMENT,
friendlyName VARCHAR(30) NOT NULL COMMENT '子账户名称。可由英文字母和阿拉伯数字组成子账户唯一名称',
subAccountSid VARCHAR(35) NOT NULL COMMENT '子账户Id。由32个英文字母和阿拉伯数字组成的子账户唯一标识符',
subToken VARCHAR(32) NOT NULL COMMENT '子账户的授权令牌',
voipAccount VARCHAR(14) NOT NULL COMMENT 'VoIP号码,用于客户端SDK登录，作为语音通话及IM功能的唯一标识帐号',
voipPwd VARCHAR(8) NOT NULL COMMENT 'VoIP密码',
dateCreated TIMESTAMP NOT NULL COMMENT '子账户的创建时间',
patient_id INT(11) NOT NULL COMMENT '患者ID',
CONSTRAINT FOREIGN KEY (patient_id) REFERENCES t_studio_patient(patient_id)
) COMMENT '融联云患者子账号表';

============================================================================================
//从tb_user表获取密码更新到tb_user_identity表：
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_str` varchar(30) DEFAULT NULL,
  `password` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf-8;

CREATE TABLE `tb_user_identity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(16) DEFAULT NULL,
  `password` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf-8


UPDATE tb_user_identity a SET a.password = ( SELECT b.password FROM tb_user b WHERE b.id = a.user_id )
WHERE a.user_id IN ( SELECT id FROM tb_user b ) AND (a.`password` IS NULL OR a.`password`='')
=============================================================================================
SELECT * FROM tb_user;
SELECT * FROM tb_user_identity;
SELECT * FROM tb_bindrecord;

##查出所有VISA,VISA-CEMEA USERS
SELECT t1.* FROM tb_user_identity t 
LEFT JOIN tb_user t1
ON t.`user_id`=t1.`id`
WHERE t.`identity_type` IN ('Visa','Cemea');

##UPDATE tb_user表的VISA&CEMEA的用户EMAILS到tb_user_identity的email
UPDATE tb_user_identity a SET a.`email` = ( SELECT b.`email_str` FROM tb_user b WHERE b.id = a.user_id )
WHERE a.user_id IN ( SELECT id FROM tb_user b ) AND a.`identity_type` IN ('Visa','Cemea');

##查user=(visa||cemea) && enapp
SELECT * FROM tb_user tt WHERE tt.id IN(
    SELECT t0.`user_id` FROM tb_bindrecord t0 WHERE t0.`user_id` IN(
    SELECT t.`user_id` FROM tb_user_identity t WHERE t.`identity_type` IN ('Visa','Cemea')
    )
);
##手工处理多重身份用户
INSERT INTO tb_user

##remove old visa&cemea emails from tb_user 
UPDATE tb_user a SET a.email_str=''
WHERE a.id IN (
  SELECT * FROM (
    SELECT t1.id FROM tb_user_identity t0
    LEFT JOIN tb_user t1 ON t0.user_id=t1.id
    WHERE t0.identity_type IN ('Visa','Cemea')
  ) t
)
;
转载于:https://my.oschina.net/xiejunbo/blog/517268