mysql一些命令

分组函数
select sum/avg/max/min/count(字段名)

select count(),type from sysm_rd_mo group by type having count() < 10

插入语句其他方式

truncate table 表名 效率高

创建库Books
CREATE DATABASE IF NOT EXISTS books ;
库的修改
RENAME DATABASE books TO 新库名;
更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
库的删除
DROP DATABASE IF EXISTS books;

SELECT now() – 2019-02-20 10:31:02
SELECTsysdate() – 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。
SELECT curdate() – 2019-02-20
SELECT curtime() – 10:32:39

语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
④删除列
ALTER TABLE book_author DROP COLUMN annual;
⑤修改表名
ALTER TABLE author RENAME TO book_author;

表的删除
DROP TABLE IF EXISTS book_author;

#通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();

显示数据库 show databases; 查看版本 select version() cmd下查看版本
DESC tb_content_category 查看表结构 select ifnull(字段名,null时的值)
指定转义字符 ASC升序 desc降序
select * from RESM_RD_ATTRIBUTE WHERE ID LIKE ‘_KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '’;
BETWEEN AND(包含临界值)）
select * from RESM_RD_ATTRIBUTE WHERE ID between 10 and 100;
<>不等于 select * from RESM_RD_ATTRIBUTE WHERE ID <>10
length(字段名) 查看名字长度 trim(字段名) 去除空格
SHOW VARIABLES LIKE ‘%char%’ 查看字符集 uft8一个汉字占3个字节 gbk 2个字节
select upper/lower(字段名) 大写小写
select sybstr(字段名,6)截取从指定索引处的后面的所有字符
select sybstr(字段名,3,6)截取从指定索引处指定长度的字符

select trim(‘1’ from name) from common_rd_user 去除字段的字符串前后中特定的字符

round(a,2)小数点后保留2位

SELECT TRUNCATE(1.32123,2) 截取小数点后保留

被除数a位正结果就为正，反之。

子对象
SELECT TYPE,ID,CAPTION,FULL_CAPTION FROM SYSM_RD_MO WHERE id IN(
SELECT id FROM SYSM_RD_MO WHERE FULL_CAPTION LIKE ‘%c4aim02%’ UNION
SELECT id FROM SYSM_RD_MO WHERE FULL_CAPTION LIKE ‘%c4aim01%’)
GROUP BY CAPTION
SELECT id FROM SYSM_RD_MO WHERE FULL_CAPTION LIKE ‘%c6cbeo10%’ AND TYPE IN(‘host_mem’,‘host_cpu’)
zhuxianglei 1:311280
INSERT INTO SYSM_RD_MO_RESPONSIBLE VALUES
(‘059e1a0fc5a540a69b77b57181a6a618’,’;0:zhuxianglei;’,’;0:zhuxianglei;’,’;1:311280;’,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NOW(),‘1530500530719’),

资源详情
SELECT id,MAX(CASE WHEN ra.attrid=‘name’ THEN ra.VALUE END) AS NAME,
MAX(CASE WHEN ra.attrid=‘os_jk’ THEN ra.value END) AS 操作系统版本,
MAX(CASE WHEN sa.name=‘ip_sys’ THEN sa.VALUE END) AS jk_ip_sys,
CONCAT(MAX(CASE WHEN ra.attrid=‘ip_sys’ THEN ra.value END),’ ',MAX(CASE WHEN sa.name=‘ip_sys’ THEN sa.VALUE END)) AS 对比,
MAX(CASE WHEN ra.attrid=‘ip_address’ THEN ra.VALUE END) AS ip_address
FROM RESM_RD_ATTRIBUTE ra,SYSM_RD_ATTRIBUTE sa
WHERE id IN(
select id from SYSM_RD_MO WHERE CAPTION IN (‘c6bdso28’,‘c6bdso27’,‘c6bdso26’,‘c6bdso25’,‘c6bdso24’,‘c6bdso22’,‘c6bdso23’,‘c6bdso21’,‘c6bdso18’,‘c6bdso17’,‘c6bdso15’,‘c6bdso16’,‘c6bdso14’,‘c6bdso12’,‘c6bdso13’,‘c6bdso11’,‘c6bdsn28’,‘c6bdsn27’,‘c6bdsn26’,‘c6bdsn25’,‘c6bdsn24’,‘c6bdsn23’,‘c6bdsn21’,‘c6bdsn22’,‘c6bdsn18’,‘c6bdsn17’,‘c6bdsn16’,‘c6bdsn15’,‘c6bdsn14’,‘c6bdsn13’,‘c6bdsn12’,‘c6bdsn11’,‘c6bdsm28’,‘c6bdsm27’,‘c6bdsm25’,‘c6bdsm26’,‘c6bdsm24’,‘c6bdsm22’,‘c6bdsm23’,‘c6bdsm21’,‘c6bdsm18’,‘c6bdsm17’,‘c6bdsm16’,‘c6bdsm15’,‘c6bdsm14’,‘c6bdsm12’,‘c6bdsm13’,‘c6bdsm11’)
) AND mo_id = id
GROUP BY id

业务类所有数据
业务类所有数据
– SYSM_RD_ATTRIBUTE
SELECT id,sm.caption,t.* FROM(
SELECT mo_id,
MAX(CASE WHEN NAME = ‘yw_bsp_sql’ THEN VALUE END) AS sql语句,
MAX(CASE WHEN NAME = ‘db_username’ THEN VALUE END) AS username,
MAX(CASE WHEN NAME = ‘db_password’ THEN VALUE END) AS PASSWORD,
MAX(CASE WHEN NAME = ‘db_url’ THEN VALUE END) AS url,
MAX(CASE WHEN NAME = ‘yw_bsp_type’ THEN VALUE END) AS TYPE
FROM SYSM_RD_ATTRIBUTE GROUP BY mo_id)t,SYSM_RD_MO sm WHERE t.sql语句 IS NOT NULL AND t.TYPE IS NOT NULL
AND t.username IS NOT NULL AND t.PASSWORD IS NOT NULL AND t.username IS NOT NULL AND t.url IS NOT NULL
AND t.sql语句 NOT IN(’’) AND t.TYPE NOT IN(’’)
AND t.username NOT IN(’’) AND t.PASSWORD NOT IN(’’) AND t.username NOT IN(’’) AND t.url NOT IN(’’) AND id = mo_id AND state = 1
SELECT t.* FROM(
SELECT id,
MAX(CASE WHEN ATTRID = ‘yw_bsp_sql’ THEN VALUE END) AS sql语句,
MAX(CASE WHEN ATTRID = ‘db_username’ THEN VALUE END) AS username,
MAX(CASE WHEN ATTRID = ‘db_password’ THEN VALUE END) AS PASSWORD,
SUBSTRING_INDEX(SUBSTRING_INDEX(MAX(CASE WHEN NAME = ‘db_url’ THEN VALUE END),’(Host=’,’-1’),’)(Port’,1) AS HOST,
MAX(CASE WHEN ATTRID = ‘yw_bsp_type’ THEN VALUE END) AS TYPE,
MAX(CASE WHEN ATTRID = ‘status’ THEN VALUE END) AS STATUS
FROM RESM_RD_ATTRIBUTE GROUP BY id)t WHERE t.status = ‘使用中’ AND t.sql语句 IS NOT NULL AND t.TYPE IS NOT NULL
AND t.username IS NOT NULL AND t.PASSWORD IS NOT NULL AND t.username IS NOT NULL AND t.url IS NOT NULL
AND t.sql语句 NOT IN(’’) AND t.TYPE NOT IN(’’)
AND t.username NOT IN(’’) AND t.PASSWORD NOT IN(’’) AND t.username NOT IN(’’) AND t.url IS NOT NULL
SELECT t.id, t.TYPE,t.FULL_CAPTION FROM SYSM_RD_MO t WHERE t.ID IN (SELECT id FROM RESM_RD_ATTRIBUTE WHERE attrid = ‘yw_bsp_sql’
AND NOT VALUE = “” AND NOT VALUE IS NULL) AND t.STATE = 1

业务类查询
– 详情
SELECT sm.id AS id,sm.caption,t.username,t.password,
t.sql语句,t.url,t.type FROM(
SELECT mo_id,
MAX(CASE WHEN NAME = ‘yw_bsp_sql’ THEN VALUE END) AS sql语句,
MAX(CASE WHEN NAME = ‘db_username’ THEN VALUE END) AS username,
MAX(CASE WHEN NAME = ‘db_password’ THEN VALUE END) AS PASSWORD,
MAX(CASE WHEN NAME = ‘db_url’ THEN VALUE END) AS url,
MAX(CASE WHEN NAME = ‘yw_bsp_type’ THEN VALUE END) AS TYPE
FROM SYSM_RD_ATTRIBUTE sa WHERE mo_id IN(
SELECT id FROM RESM_RD_ATTRIBUTE WHERE ATTRID = ‘yw_bsp_sql’ AND VALUE LIKE ‘%srp.sp_workf_comm%’)
GROUP BY mo_id)t
LEFT JOIN SYSM_RD_MO sm ON sm.id = t.mo_id ORDER BY sm.caption
– url截取部分字符串
SELECT sm.id AS id,sm.caption,t.url,t.host,t.username,t.password,
t.sql语句,t.type FROM(
SELECT mo_id,
MAX(CASE WHEN NAME = ‘yw_bsp_sql’ THEN VALUE END) AS sql语句,
MAX(CASE WHEN NAME = ‘db_username’ THEN VALUE END) AS username,
MAX(CASE WHEN NAME = ‘db_password’ THEN VALUE END) AS PASSWORD,
SUBSTRING_INDEX(SUBSTRING_INDEX(MAX(CASE WHEN NAME = ‘db_url’ THEN VALUE END),’(Host=’,’-1’),’)(Port’,1) AS HOST,
RIGHT(MAX(CASE WHEN NAME = ‘db_url’ THEN VALUE END),35) AS url,
MAX(CASE WHEN NAME = ‘yw_bsp_type’ THEN VALUE END) AS TYPE
FROM SYSM_RD_ATTRIBUTE sa WHERE mo_id IN(
SELECT id FROM RESM_RD_ATTRIBUTE WHERE ATTRID = ‘NAME’ AND VALUE LIKE ‘%用户状态同步%’)
GROUP BY mo_id)t
LEFT JOIN SYSM_RD_MO sm ON sm.id = t.mo_id ORDER BY t.url
SELECT * FROM
(SELECT t.id, t.TYPE,t.FULL_CAPTION FROM SYSM_RD_MO t WHERE t.ID IN (SELECT id FROM RESM_RD_ATTRIBUTE WHERE attrid = ‘yw_bsp_sql’ AND NOT VALUE = “” AND NOT VALUE IS NULL) AND t.STATE = 1) t
LEFT JOIN
(SELECT srp.mo_id,MAX(srp.time) AS cjtime FROM SYSM_RD_PERFORMANCE PARTITION (PAR_SYSM_RD_PERFORMANCE_1802) srp WHERE srp.MO_ID IN
(SELECT t.ID FROM SYSM_RD_MO t WHERE t.ID IN (SELECT id FROM RESM_RD_ATTRIBUTE WHERE attrid = ‘yw_bsp_sql’ AND NOT VALUE = “” AND NOT VALUE IS NULL) AND t.STATE = 1) GROUP BY srp.MO_ID) srp
ON t.id = srp.mo_id
WHERE (srp.cjtime <= SUBDATE(NOW(), INTERVAL 40 MINUTE) OR srp.cjtime IS NULL)

判断字段是否存在
SELECT t1.modelid,t.id,MAX(CASE WHEN t.attrid = ‘name’ THEN t.value END) “NAME”,
MAX(CASE WHEN t.attrid = ‘resource_admin’ THEN t.value END) “CI_ADMIN”
FROM RESM_RD_ATTRIBUTE t,(
SELECT * FROM RESM_RD_RESOURCE rr WHERE NOT EXISTS (SELECT 1 FROM RESM_RD_ATTRIBUTE ra WHERE ra.id = rr.id AND ra.ATTRID = ‘resource_admin’)) t1
WHERE t.id = t1.id AND t.id IN()
GROUP BY t.id
更新与插入数据
UPDATE RESM_RD_ATTRIBUTE SET VALUE = ‘445636’ WHERE id IN(26893160) AND attrid LIKE ‘belong_cabinet’
INSERT INTO RESM_RD_ATTRIBUTE VALUES (10212,‘belong_cabinet’,NULL,‘resm’),(10215,‘belong_cabinet’,NULL,‘resm’),(10628,‘belong_cabinet’,NULL,‘resm’)

截取
SUBSTRING_INDEX(SUBSTRING_INDEX(xml,‘grade="’,-1),’"’,1)
SELECT LEFT(xml,100) FROM SYSM_CD_CHECKER
SELECT RIGHT(xml,100) FROM SYSM_CD_CHECKER
SELECT SUBSTRING(xml,2,4) FROM SYSM_CD_CHECKER – 截取字段 从第几位(-倒着找) 长度
SELECT SUBSTRING_INDEX(xml,‘grade="’,-1) FROM SYSM_CD_CHECKER – 关键字 关键字出现的次数
SELECT STR_TO_DATE(‘122621’,’%H%i%s’)
SELECT * FROM ITSM_TASK WHERE task_show_oid IN(‘20171127144704315235’)
SELECT * FROM ITSM_TASK_STAT_44

从备份表恢复数据
UPDATE RESM_RD_ATTRIBUTE a,(
SELECT * FROM RESM_RD_ATTRIBUTE_20171116 WHERE id IN
(SELECT DISTINCT
id
FROM
RESM_RD_ATTRIBUTE_20171116
WHERE ATTRID LIKE ‘alarm_contacts’
AND VALUE LIKE ‘%zhangdong%’) )b SET a.VALUE=b.value
WHERE a.ATTRID=b.ATTRID AND a.id = b.id