1 各种JOIN
2 临时表、文件
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)
CREATE TEMPORARY TABLE a (select * FROM sys_user_role);
Select * into outfile 'c://outfile.txt' from test;3 变量（局部变量、用户变量 、会话变量 、全局变量）
深入MySQL用户自定义变量：使用详解及其使用场景案例 - GenialX - 博客园
MySQL中变量的定义和变量的赋值使用（转） - EasonJim - 博客园
3.1 使用set或select直接赋值，变量名以@开头，可以在一个会话的任何地方声明，作用域是整个会话，称为用户变量。
set @var=1;
3.2 以declare关键字声明的变量，只能在存储过程中使用（begin..end），或者是给存储传参数中，称为存储过程/局部变量，例如：
declare var1 int default 0;
4 查询结果增加序号列
在mysql中给查询的结果添加序号列 - 爱笑的我_smile - 博客园
4.1.法一：
select (@i:=@i+1) i,a.url from base_api_resources a ,(select @i:=0) t2 order by a.id desc limit 10;4.2.法二：有序号就不能 select *，一定要指定显示类名
set @rownum=0;
select @rownum:=@rownum+1 as rownum, a.url from base_api_resources a order by a.id desc limit 10分序号：
一个select中按照分组排序
--sqlserver
--同种商品按求货数量排序
select ROW_NUMBER() OVER (PARTITION BY spid ORDER BY qhshl) AS sn,
spid as 商品,qhr as 求货人,qhshl as 求货数 from tsqh_qhmx
where plid='TQHF00000000008'
5 时间处理
格式化
select FROM_UNIXTIME(1344887103); //参数：一般为10位的时间戳（秒级别）
2012-08-14 03:45:03
select unix_timestamp("2012-08-14 03:45:03") //秒级时间戳
1344887103
select unix_timestamp(NOW())* 1000 //粗算毫秒时间戳
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') //参数：datetime
2012-08-14 03:45:03
SELECT DATE_FORMAT(NOW(),'%H')
03时间差
//datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒
select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');
//TIMESTAMPDIFF函数 定位到天数、小时、分钟和秒
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');时间计算
DATE_ADD(date,INTERVAL expr type） 给日期添加指定的时间间隔
DATE_SUB(date,INTERVAL expr type） 从日期减去指定的时间间隔
type：SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
select date_sub(now(),interval 1 day); //减一天 返回格式 2018-12-19 23:35:396 NULL处理
1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0
2.ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替
3.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1。
7 MySQL类型转换
CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。两者具体的语法如下：
1、CAST(value as type) 就是CAST(xxx AS 类型)
2、CONVERT(value, type) 就是CONVERT(xxx,类型)
但是要特别注意,可以转换的数据类型是有限制的。这个类型可以是以下值其中的一个：
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED （小数丢失，不是四舍五入）
无符号整数 : UNSIGNED
8 动态SQL
推荐都使用？占位符预编译sql，不要直接拼接，防止注入
set @stmt = concat('select * from ',table_name,' limit ?,?');--table_name 表名字符串
prepare s1 from @stmt;
set @s1 = page_begin;
set @s2 = page_end;
execute s1 using @s1,@s2;
deallocate prepare s1;9 返回影响
SqlServer、mysql 要用变量在执行后立即记录影响行数，因为影响行数只返回上一条语句的，且不立即记录有可能会出现意想不到的失准情况。
MYSQL
found_rows() : select
row_count() : insert update delete
insert into `MyTestTable`(`ID`,`Name`)
select '1','role1' union all
select '2','role2' union all
select '3','role3';
select row_count(); -- 输出3(返回新添加的记录数),[注:如果使用insert into...values只返回1]
select * from `MyTestTable`;
select found_rows(); -- 输出3(返回选择的行数)
update `MyTestTable` set `Name`='people';
select row_count(); -- 输出3(返回修改的行数)
delete from `MyTestTable`;
select row_count(); -- 输出3(返回删除的行数)SqlServer
declare @rcount int
update task_hz set is_ysh='是',state='分配' where djbh='' and is_ysh='中'
//记录受影响的行数
set @rcount=@@rowcount
if @rcount= 0 //判断上句UPDATE是否有受影响的行
begin
//没有，表示没有djbh='' and is_ysh='中' 的数据
update task_hz set is_ysh='中' where djbh='' and is_ysh='否'
//记录受影响的行数
set @rcount=@@rowcount
end
select @rcount as rcount10 WHILE 循环
while do ..... end while
DECLARE COUNT INT DEFAULT 0;
DECLARE SUM INT DEFAULT 0;
WHILE COUNT < in_count DO
SET SUM = SUM + COUNT;
SET COUNT = COUNT + 1;
END WHILE;
SELECT SUM;11 TRIM
SELECT TRIM(' bar '); --去除空格-> 'bar'
SELECT LTRIM(' barbar');
SELECT RTRIM(' barbar ');
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); --删除指定的首字符 x -> 'barxxx'
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); --删除指定的首尾字符 x -> 'bar'
SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); --删除指定的尾字符 xyz -> 'barx'12 OFFSET 显示偏移
SELECT DISTINCT
Salary AS SecondHighestSalary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1 #显示1条数据，从[1]开始。默认是从[0]存储过程（函数）
//推荐用navicat创建
CREATE DEFINER=`root`@`%` PROCEDURE `selectuser`(IN `flag` int,INOUT getresult int )
BEGIN
if flag=1 THEN
select getresult ;
set getresult=10;//INOUT
end IF;
END执行
set @getresult=1;
call selectuser(1,@getresult) ; //INOUT参数 必须由@变量传入
select @getresult //接收INOUT参数结果 10UPDATE原子性的获得原值和新值
MYSQL:
update pack_dhhm set je=@old:=je, je=@new:=je+10 where dhhm=12345678901;
select @old,@new,ROW_COUNT();
SQLSERVER:
declare @old,@new int
update pack_dhhm set @old=je, @new=je=je+10 where dhhm=12345678901
select @old,@new,@@rowcountUpdate From
mysql
update kb_tbrecordinfo_monthreport1124_new t2
inner join kb_tbrecordinfo_monthreport1124 as t1
on t1.id = t2.id
set t2.corpname = t1.corpcode
where .....
sqlserver
update kb_tbrecordinfo_monthreport1124_new
set corpname=b.corpcode
from kb_tbrecordinfo_monthreport1124_new a
join kb_tbrecordinfo_monthreport1124 b
on a.id=b.id;
where .....insert转update
执行insert时已存在，则执行update
INSERT INTO user_admin_t (_id,password)
VALUES ('1','第一次插入的密码')
ON DUPLICATE KEY UPDATE --下边是执行update的set
_id = 'UpId',
password = 'upPassword';replace into
相当于insert into，若主键冲突则替换旧数据。
group_concat
原文：mysql之group_concat函数详解_u012620150的专栏-CSDN博客_group_concat函数
select id, group_concat(price) from goods group by id;
查看执行日志
sqlserver
SELECT TOP 30 QS.creation_time, SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1,
((CASE QS.statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset) / 2) + 1)
AS statement_text, ST.text, QS.total_worker_time, QS.last_worker_time, QS.max_worker_time, QS.min_worker_time
FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE
--QS.creation_time BETWEEN '2017-09-09 10:00:00' AND '2017-09-11 18:00:00'
ST.text LIKE '%%'
ORDER BY QS.creation_time DESC数字辅助表
行列互换
MySQL 实现行转列SQL_个人博客：https://micai-code.github.io 欢迎来访哟！-CSDN博客
扫一扫
1997
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
