MYSQL 语法基础随记

最新推荐文章于 2023-04-19 17:50:10 发布

shanchahua123456

最新推荐文章于 2023-04-19 17:50:10 发布

阅读量262

点赞数

分类专栏：数据库 mysql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/shanchahua123456/article/details/84845692

版权

数据库同时被 2 个专栏收录

16 篇文章 2 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

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:39

6 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 rcount

10 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参数结果 10

UPDATE原子性的获得原值和新值

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,@@rowcount

Update 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

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL 语法基础随记

1 各种JOIN2 临时表、文件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...
复制链接

扫一扫