MySQL 常用命令

#连接数据源
#mysql -hIP -PPORT -uUSER -p
mysql -h127.0.0.1 -P13306 -uroot -pmy_pw
mysql -h127.0.0.1 -P13306 -uroot -pmy_pw
#插入数据 
insert into test (name) values ("test4");
#查询所有库
show databases;    
use db;
#查询所有数据表
show tables;
select * from dts_test_mysql_hive where id=34289;

#删除表内容
delete from test_cdc.dts_sink

#更新表字段类型
ALTER TABLE test_cdc.las2bytehouse MODIFY col6 blob;
#查看Python进程命令
ps -aux | grep python

#查询连接数
SHOW VARIABLES LIKE 'max_connections';
#设置连接数
在数据源的配置目录/data00/datasource/volumns/mysql/conf/my.cnf 下，
添加max_connections = 1000

#清除连接数,命令执行不了可重启
mysqladmin -u root -p flush-hosts

#修改数据库密码
SET PASSWORD FOR 'root'@'%' = PASSWORD('my_pw');

#查询所有用户
select user from mysql.user;
#查看建表信息
show create table dts_source_all;   --查询Mysql建表信息

CREATE TABLE `dts_source_all_sink` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  `address` varchar(400) DEFAULT NULL,
  `create_time` bigint(20) DEFAULT NULL,
  `event_time` bigint(20) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `tinyint_info` tinyint(4) DEFAULT NULL,
  `num_small` smallint(6) DEFAULT NULL,
  `num_medium` mediumint(9) DEFAULT NULL,
  `num_big` bigint(20) DEFAULT NULL,
  `num_float` float(13,3) DEFAULT NULL COMMENT '浮点数',
  `num_decimal` decimal(15,5) DEFAULT NULL COMMENT '定点小数类型',
  `year_info` year(4) DEFAULT NULL,
  `time_info` time DEFAULT NULL,
  `date_info` date DEFAULT NULL,
  `datetime_info` datetime DEFAULT NULL,
  `timestamp_info` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tinytext_info` tinytext COMMENT '短文本字符串',
  `text_info` text COMMENT '长文本数据',
  `medium_txt_info` mediumtext COMMENT '中等长度文本数据',
  `longtext_info` longtext COMMENT '极大文本数据',
  `tinyblob_info` tinyblob COMMENT '不超过 255 个字符的二进制字符串',
  `blob_info` blob COMMENT '二进制形式的长文本数据',
  `medilumblob` mediumblob COMMENT '二进制形式的中等长度文本数据',
  `longblob_info` longblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46366035 DEFAULT CHARSET=utf8