mysql

1526字约5分钟

mysql

2020-05-06

安装

Win

解压Mysql.zip后，添加bin目录的环境变量
配置my.ini文件中的

basedir =D:\mysql-5.6.24-winx64
datadir = D:\mysql-5.6.24-winx64\data

cmd窗口进入运行bin目录运行mysqld -install添加到服务，mysqld -remove移出服务
cmd窗口启动服务　　net start mysql
cmd窗口登陆

mysql -u root -p（root用户登录）
更改用户名　　use mysql
update user set user="newUserName" where user="oldUserName"
flush privileges
exit
修改密码　　　
mysqladmin -u "username" -p password "newPassword"

环境变量配置

MYSQL_HOME
Path
Net start mysql57
Net stop mysql57

Mac

启动MySQL, 打开终端，给MySQL命令目录设置别名，不需要每次切换目录

alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin

登录MySQL mysql -u root -p

Linux

下载mysql wget https://dev.mysql.com//Downloads/MySQL-5.7/mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz
解压 tar zxvf mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz
重命名 mv mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz mysql
设置配置文件

cd /usr/local/mysql/support-files/
cp my-default.cnf /etc/my.cnf
vi /etc/my.cnf
character-set-client-handshake = FALSE 添加在[mysqld]下方即可
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[client]
default-character-set=utf8mb4

开机启动

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
vi /etc/init.d/mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

建立mysql的组 groupadd mysql
建立mysql用户 useradd -r -g mysql mysql
设置密码 passwd mysql
更改拥有者 `chown -R mysql:mysql /usr/local/mysql/
初始化数据库 ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
数据库加密 ./mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
启动 ./mysql -uroot -p
修改密码 set password=password('/* 新密码 */')
授权远程访问 grant all privileges on *.* to root@'%' identified by '/* 新密码 */'

语法

用户

SELECT USER, HOST FROM MYSQL.USER;
CREATE USER 'O2O'@'localhost' IDENTIFIED BY 'O2O';
DROP USER O2O;

实例

create database 2o;
show databases;
Drop database if exists o2o;

导入导出

软连接 
ln -fs /usr/local/mysql/bin/mysqldump /usr/bin
ln -fs /usr/local/mysql/bin/mysql /usr/bin
导出
导出表和数据 -- mysqldump -h localhost -u root -p cloudpivots > /data/cloudpivots.sql
导出表结构 -- mysqldump -h localhost -u root -p -d cloudpivots > /data/cloudpivots.sql
导入 -- mysql -u root -p cloudpivots <cloudpivots.sql

权限

grant all privileges on *.* to 'O2O'@'localhost';
flush privileges;

表空间

CREATE TABLESPACE TBSPACE_O2O
ADD DATAFILE 'TBSPACE_O2O.ibd'
USE LOGFILE GROUP LOGGROUP_O2O
EXTENT_SIZE =100M
INITIAL_SIZE = 3072M
AUTOEXTEND_SIZE = 100M;

表

use O2O;
CREATE TABLE `TB_AREA` (
  `AREA_ID` INT(2) NOT NULL AUTO_INCREMENT,
  `AREA_NAME` VARCHAR(200) NOT NULL,
  `PRIORITY` INT(2) NOT NULL,
  `CREATE_TIME` DATETIME DEFAULT NULL,
  `LAST_EDIT_TIME` DATETIME DEFAULT NULL,
  PRIMARY KEY(`AREA_ID`),
  UNIQUE KEY `UK_AREA`(`AREA_NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 
select * from TB_AREA;
InnoDB -- 多线程,基于行
MYISAM -- 单线程, 基于表
CREATE TABLE `tb_shop_category` (
  `shop_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `shop_category_name` varchar(1000) NOT NULL DEFAULT '',
  `shop_category_desc` varchar(1000) DEFAULT '',
  `shop_category_img` varchar(2000) DEFAULT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `last_edit_time` datetime DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`shop_category_id`),
  CONSTRAINT `fk_shop_category_self` FOREIGN KEY (`parent_id`) REFERENCES `tb_shop_category`(`shop_category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

索引

-- 分析命中
EXPLAIN SELECT * FROM table WHERE a=1 AND b=2;
-- 更新索引统计信息  
ANALYZE TABLE table_name;
-- 查询索引 
SHOW INDEX FROM table_name;
-- 删除索引
ALTER TABLE table_name DROP INDEX idx_ab(a,b);
-- 创建索引
ALTER TABLE table_name ADD INDEX idx_ab(a,b);
-- 忽略索引
SELECT * FROM table ignore index (idx_a) WHERE a=1 AND b=2;
-- 强制索引
SELECT * FROM table force index (idx_a) WHERE a=1 AND b=2;

将选择性高的列放在前面
查看 Cardinality（基数越大选择性越高），Cardinality的值小，可能不适合建立索引
where > join >

函数

CREATE DEFINER=`root`@`%` FUNCTION `GET_ALL_COMPANY_NODE`() RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE V_RESULT VARCHAR(1000); -- 返回结果
RETURN V_RESULT;
END;

存储过程

创建
CREATE DEFINER=`root`@`%` PROCEDURE `GEN_PERSION_MANAGE_STATISTICS`()
BEGIN
DECLARE V_PARAM1 VARCHAR(32);
DECLARE V_PARAM2 INT;
END;
执行
CALL GEN_PERSION_MANAGE_STATISTICS();
调试
SET  @num = 0;
CALL GEN_PERSION_MANAGE_STATISTICS_6();
SELECT @num;

内置函数

* 当前日期 -- CURDATE()
* 当前时间 -- SYSDATE()
* 当前年份 -- YEAR(CURDATE())
* 当前月份 -- MONTH(CURDATE())
* 字符串转数字 -- CONVERT(str,SIGNED)  convert(1342.45365, decimal(12,2))，ROUND(100,2)
* JSON转字符串 -- JSON_UNQUOTE(JSON_EXTRACT(username -> '$[0]', '$.id' ))
* 字符串转JSON --  CONCAT('[{"id":"', company, '","type":1}]')
* 替换函数 --  REPLACE('c8043fe5-0d16-11ea-bcc8-0050568a759b', '-', '');
* 字符串转时间 --  STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s');
* 时间转字符串 -- date_format(birthday, '%Y-%m-%d %H:%i:%s')
* 分组拼接 -- group_concat(id)

定时

创建定时
CREATE EVENT `cloudpivot`.`EVENT_GPMS`
ON SCHEDULE
EVERY '1' MONTH STARTS '2019-12-01 00:00:00'
DO call GEN_PERSION_MANAGE_STATISTICS();
查看定时 -- show variables like '%sche%';
开启定时 -- set global event_scheduler =1;
开启定时 -- alter event EVENT_DPAS on completion preserve enable;
关闭定时 -- alter event EVENT_GPMS on completion preserve disable;

游标

DECLARE S int DEFAULT 0; 
DECLARE V_COMPANY_ID VARCHAR(32); 
DECLARE V_COMPANY_IDS CURSOR FOR SELECT T1.ID FROM h_org_department T1 WHERE find_in_set(id, GET_ALL_COMPANY_NODE()); -- 查询所有公司ID
DECLARE CONTINUE HANDLER FOR NOT FOUND SET S=1;
OPEN V_COMPANY_IDS;
FETCH V_COMPANY_IDS INTO V_COMPANY_ID;
WHILE S<>1 DO
SET V_RESULT = CONCAT(V_RESULT, ",", V_COMPANY_ID);
FETCH V_COMPANY_IDS INTO V_COMPANY_ID;
END WHILE;
CLOSE V_COMPANY_IDS;

服务

* 登录 `mysql -u O2O -p Password`
* 启动 `service mysqld start`
* 重启 `service mysqld restart`
* 停止 `service mysqld stop`

线程

* 查找线程 `select * from information_schema.innodb_trx`
* 杀死线程 `kill 78879`

常用

查询所有字段名

纵表
select concat('`', column_name, '`', ',') 
from information_schema.columns 
where table_name='ic8x6_EmployeeRosters' and table_schema='cloudpivot';
横表
select group_concat(t1.`NAME`)
from (
select concat('`', column_name, '`') AS `NAME`, '1' AS `TAG` 
from information_schema.columns 
where table_name='ij3ak_employeerosters' and table_schema='cloudpivots'
) t1 group by t1.TAG;

查询系统当前时间

select current_date;

事件

创建
CREATE EVENT e_Employee_Rosters_Copy
ON SCHEDULE EVERY 1 SECOND 
DO INSERT cloudpivot.ic8x6_employeeRostersCopy (COPY_TIME, address, age, archivesSite, archivesType, birthday, createdDeptId, createdTime, creater, employeeId, employeeName, employeeStatus, employmentNature, gender, generalRecruitment, height, highestDegree, highestDegreeEdate, highestDegreeMajor, highestDegreeSdate, highestDegreeUniversity, id, iDaddress, iDcard, iDendDate, iDstartDate, industryPracticeDate, isBlacklist, isGeneralRecruitments, isOutsource,isOverseasEducation,isOverseasEducations,isSecond, jobDate, maritalStatus, mobile, modifiedTime, modifier, name, nation, nativePlace, owner, ownerDeptId, ownerDeptQueryCode, politicalOutlook,professionalLabel, registeredResidenceType, secondaryDegree, secondaryDegreeEdate, secondaryDegreeMajor, secondaryDegreeSdate, secondaryDegreeUniversity, sequenceNo, sequenceStatus, userid, workflowInstanceId)
select current_date, address, age, archivesSite, archivesType, birthday, createdDeptId, createdTime, creater, employeeId, employeeName, employeeStatus, employmentNature, gender, generalRecruitment, height, highestDegree, highestDegreeEdate, highestDegreeMajor, highestDegreeSdate, highestDegreeUniversity, id, iDaddress, iDcard, iDendDate, iDstartDate, industryPracticeDate, isBlacklist, isGeneralRecruitments, isOutsource, isOverseasEducation, isOverseasEducations, isSecond, jobDate, maritalStatus, mobile, modifiedTime, modifier, name, nation, nativePlace, owner, ownerDeptId, ownerDeptQueryCode, politicalOutlook, professionalLabel, registeredResidenceType, secondaryDegree, secondaryDegreeEdate, secondaryDegreeMajor, secondaryDegreeSdate, secondaryDegreeUniversity, sequenceNo, sequenceStatus, userid, workflowInstanceId from ic8x6_EmployeeRosters er;
查询事件开启状态
SHOW VARIABLES LIKE 'event_scheduler';
开启事件
SET GLOBAL event_scheduler = ON;

获取第一行数据

select scheme_code from scheme_fliter order by scheme_code desc limit 1;

查询所有表

select table_name
from information_schema.tables 
where table_schema='cloudpivot' and table_name like '%POST%'

查询上个月时间区间内的数据

BETWEEN DATE_ADD(DATE_SUB(curdate(), INTERVAL 1 MONTH), interval -day(curdate())+1 day) AND date_add(DATE_ADD(DATE_SUB(curdate(), INTERVAL 1 MONTH), interval -day(curdate())+1 day),interval 1 month)

清除数据表中的回车和空格

update ij3ak_salaryfiles
set type =  REPLACE(REPLACE(REPLACE(REPLACE(type,CHAR(13),''),CHAR(10),''),CHAR(9),''),' ','')

保留小数位数

decode(salary,0,'0.00',(to_char(round(salary,2),'fm99999999999999.00')))

时间间隔

--计算两个时间差相差多少秒
select ceil((sysdate-t.transdate)* 24 * 60 * 60),t.transdate,sysdate from esc_trans_log t
--计算两个时间差相差多少分
select ceil((sysdate-t.transdate)* 24 * 60),t.transdate,sysdate from esc_trans_log t
--计算两个时间差相差多少小时
select ceil((sysdate-t.transdate)* 24),t.transdate,sysdate from esc_trans_log t
--计算两个时间差相差多少天
select ceil(sysdate-t.transdate),t.transdate,sysdate from esc_trans_log t
--计算两个时间差相差多少月
select trunc(months_between(sysdate,t.transdate)) from esc_trans_log t
--计算两个时间差相差多少年
select trunc(months_between(sysdate,t.transdate)/12) from esc_trans_log t