java mysql

#查看table所有字段

desc  userinfo;
 

# convert 类型转换

convert(expr, type) 

# building_name是varchar类型

select DISTINCT(building_name) from quarantine_room_extend
where del_flag='0'
order by  CONVERT(building_name, unsigned) asc;






# 没有时间的日期：

SELECT CONVERT("2017-08-29", DATE) ，如2017-08-29 00:22:33 ；输出2017-08-29


# 日期时间：

SELECT CONVERT("2019-08-01", DATETIME)，如2019-08-01；输出2019-08-01 00:00:00


# 去掉日期的时间：

SELECT CONVERT("2017-08-01 01:01:01", TIME) ，如2017-08-01 01:01:01；输出01:01:01


# 小数，DECIMAL函数里第一个参数是总位数（包含小数位数），第二个参数是小数位数：

SELECT CONVERT(9999.9999, DECIMAL(3,2)) AS num，如9999.9999；输出是9.9


有符号 Integer：

SELECT CONVERT(-9999.5099, SIGNED) AS num，如 -9999.5099；输出-10000


无符号 Integer：

SELECT CONVERT(9999.5099, UNSIGNED) AS num，如9999.5099； 输出10000

# 移动字段

移动到另一个字段后面

ALTER TABLE <表名> MODIFY <需要移动的字段名> <字段类型> AFTER <参考的字段名>; 并回车

//移动到行首
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(22) FIRST;

#field

##返回下标

FIELD(value, val1, val2, val3, ...) 

参数：
此方法接受以下两个参数：

• value:要搜索的指定值。
• val1，val2，val3，...：指定要搜索的值列表。

返回值：
它返回给定值列表中指定值的索引位置。

##排序，按照某个字段的值排序

order by field(字段1, 值1,值2, 值3) desc/asc, 字段2 desc/asc;

SELECT FIELD("a", "b", "A", "a", "c");   // 2



//排序
select * from userinfo order by field(name, 's','z') desc, 
case when name='s' then createTime else updataTime end desc;

#字段位置移动

alter table 表名 modify 字段名 字段类型 after 字段
 

alter table user_info modify user_name varchar(10) after user_id;

alter table user_info modify user_id char(8) first;//将user_id移到最前面！！

# find_in_set()和in() 和like

FIND_IN_SET(str,strlist) 

str 要查询的字符串

strlist 字段名 参数以”,”分隔 如 (1,2,6,8,10,22)

查询字段(strlist)中包含(str)的结果，如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL

in(list)

list是常量，则可以直接用IN， 否则要用find_in_set()函数。

SELECT userid,username,userrole,type  FROM `user` WHERE find_in_set('admin',userrole)

SELECT userid,username,userrole,type  FROM `user` WHERE type in ('admin','user')

# 两个字段拼接

concat函数和concat_ws函数

concat函数
CONCAT(str1,str2,…)

在使用concat函数的时候， 只要其中一个字段属性有NULL值，concat结果就是NULL。

concat_ws函数
CONCAT_WS(separator,str1,str2,…), 表示concat with separator,即有分隔符的字符串拼接函数
在拼接的时候不用担心有一个字段为null

#、某个字段的一部分group by

1、SUBSTRING_INDEX（字段名，‘分割符’， 第几个分割符（以1开始））

2.固定开头 left(字段名，长度) 函数

   是一个字符串函数，它返回具有指定长度的字符串的左边部分

3.固定结尾 right(字段名，长度) 函数

   返回具有指定长度的字符串的右边部分（长度从右往左计算）

SELECT LEFT(commoditybar,4) as code FROM `b_stock` where commoditybar is not null GROUP BY LEFT(commoditybar,4)

#、int型报null的错误

<select id="qPartUqty" resultType="java.lang.Integer" >
    select IFNULL((select sum(uqty)
    from b_stock where commodityid = #{commodityid} and locatorstate=#{locatorstate}),0)
</select>

2、插入数据，返回新数据的id

<selectKey keyProperty="id" order="AFTER" resultType="String">
   select last_insert_id() as id//返回第一条添加的
</selectKey>
<selectKey keyProperty="id" order="AFTER" resultType="String">
   select max(id) from change_copy//返回最后一条添加的
</selectKey>

3、中文排序

ORDER BY CONVERT(属性名 USING GBK)

4、查找引用这个表做外键的其他表

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE  where REFERENCED_TABLE_NAME='表名'

5、mysql查询以4开头

select * from checkscore a where left(a.auth, 1)<>'4';//不查

select * from checkscore a where a.auth like '4%';

select * from checkscore a where left(a.auth, 1)='4';

//以2022-11-11开头
select * from alarm where beam = '19' and alarm_time REGEXP ('2022-11-11');

mysql查询以逗号隔开的字符串 FIND_IN_SET(str,strlist)  如FIND_IN_SET(48,'12,48,79')，可放在order by 后面用来排序

select
<include refid="Base_Column_List"/>
from b_commodity where 1 = 1
<if test="paramCondition.category != null and paramCondition.category != '' and paramCondition.category != '0'.toString()">
    and find_in_set(#{paramCondition.category},category)
</if>

ps：上面判断！=‘0’.toString（），这样写的原因是字符的 0 对应的ASCII码就是 48，当值为48的时候将不走这句，所以要加上.toString（）,就不会过滤掉48

mysql查询以逗号隔开的常量list用in（str,list）如 select * from table where id in( 12,48,79 );

6、比较两个时间

SELECT * from table where TIMESTAMPDIFF(type,pretime,latertime)>100

FRAC_SECOND (microseconds), SECOND,MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR

7、自动加一天

UPDATE table SET  time2 = DATE_ADD(time1,INTERVAL 1 DAY) WHERE status=1

例子：update user set leverstart=now(),leverover=date_add(NOW(), interval 1 MONTH) where id=1;

date_add() 增加
date_sub()减少

month 月份
minute 分钟
second 秒

8、too many connection

9、修改表里面所有属性字符编码

alter table `表名` convert to character set utf8;

10、将null强制放在最前

if(isnull(字段名),0,1) asc  --asc可以省略
select * from test order by if(isnull(num),0,1),num;

11、查当天

select * from hb_article_view where TO_DAYS(hb_AddTime) = TO_DAYS(NOW())
select * from my_table where biz_date >= CURDATE()

select * from table where date(regdate) = curdate();

12、mysql查询今天、昨天、7天、近30天、本月、上一月 数据

#今天
select * from 表名 where to_days(时间字段名) = to_days(now());
#昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
#7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
#近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
#本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
#上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
 
#查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
#查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
#查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

13、分组查询并将查询的结果都列出来

SELECT GROUP_CONCAT(id)  FROM INSPECT GROUP BY  BRIDGEID

SELECT IDATE ,GROUP_CONCAT(BIDS) AS BIDS,GROUP_CONCAT(DISTINCT ITEM ORDER BY C.CREATEDATE DESC) AS ITEM
FROM USER2ITEM A  
LEFT JOIN USERINFO B ON B.ID = A.USERID
LEFT JOIN ITEM C ON A.ITEMID = C.ID
WHERE A.USERID=#{userID} AND A.STATU=1 
GROUP BY IDATE
ORDER BY IDATE DESC,C.CREATEDATE DESC

14、添加外键

alter table 子表的数据表名 add foreign key(子表的外键名称) references 父表的数据表名称（父表的主键名称）;

alter table 表名 drop foreign key 外键（区分大小写）

15、添加一个新的列

alert table 表格名 add column 列名 列类型 DEFAULT 默认值;


alert table 表格名 add column CRAETEDATE DATETIME DEFAULT NOW();

#添加一个名为age的整数列

alter table 表格名 add column age int;

#默认值为0

alter table 表格名 add column age int default 0;

#某个位置添加新列

alter table 表格名 add column age int after 列名;

ALTER TABLE student ADD class VARCHAR(120) NOT NULL DEFAULT ‘None’;

#查看新列

desc 表格名;

# 修改列名

# 表“students”中的“gender”列名为“gender_type”，并将其类型变更为VARCHAR(10)。

ALTER TABLE students CHANGE `gender` `gender_type` VARCHAR(10)

# 修改列属性

alter table 表名 modify column 列名 属性(其他配置) ;


ALTER TABLE table_name    
MODIFY COLUMN column_name1 data_type,  
MODIFY COLUMN column_name2 data_type ... ;   

alter table iteminfo modify column TIMES decimal(18,2) DEFAULT 0.00  not null COMMENT '质量保证金/结算款';

# 删除列

ALTER TABLE table_name DROP COLUMN column_name;

16、自增加从3开始

alter table userinfo auto_increment=3;

17、改表名

ALTER TABLE productions RENAME TO KR;

18、忘记设置not null

1.
UPDATE KR SET LEV = '' WHERE LEV IS NULL;（varchar）
或
 UPDATE KR SET LEV = 0 WHERE LEV IS NULL;（int）
 
2. alter table kr change  column LEV LEV int not null DEFAULT 0;

19、查询第11到第15条数据，默认从0开始

select * from table_name limit 10,5

limit关键字的用法：

LIMIT [offset,] rows

20、Mysql中如果表和表之间建立的外键约束，则无法删除表及修改表结构

21、清空表数据并将自增修改为1

truncate table pro_workman;
alter table pro_workman AUTO_INCREMENT=1;

22、将两个查到的结果集，合并在一起返回出来，用到 union all 和union 区别是，

union all是把结果集直接合并在一起，而union 是将union all后的结果镜像一次distinct，去除重复的记录后的结果。