常用命令
程序员文章站
2024-03-13 16:43:21
...
增加表字段
ALTER TABLE table_name ADD COLUMN `create_user_id` BIGINT DEFAULT 0 NOT NULL COMMENT '创建人';
修改字段
ALTER TABLE table_name MODIFY COLUMN address VARCHAR(200) DEFAULT '' NOT NULL COMMENT '详细地址';
修改字段编码
ALTER TABLE table_name MODIFY COLUMN nick_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '昵称';
修改表名
ALTER TABLE table_name RENAME table_name_new;
导出数据库
mysqldump -u orange -p -h 1.1.11.111 --set-gtid-purged=off orange >/data/orange.sql
创建索引
ALTER TABLE table_name ADD INDEX index_name (column_name)
CREATE INDEX index_name ON table_name (column_name);
用命令行进入
mysql:mysql -u root -p
修改root密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
建立远程用户
grant all privileges on *.* to 'orange'@'%' identified by 'orange';
修改表的存储引擎
ALTER TABLE table_name ENGINE=MYISAM
创建(分区)表
CREATE TABLE table_name (
id VARCHAR(255) DEFAULT NULL COMMENT '记录id',
dt VARCHAR(10) DEFAULT NULL COMMENT '分区字段'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='测试表'
PARTITION BY LIST COLUMNS(dt)
(
PARTITION p_20180501 VALUES IN ('20180501'),
PARTITION p_20180502 VALUES IN ('20180502') ,
PARTITION p_20180503 VALUES IN ('20180503')
);
给分区表增加分区
ALTER TABLE table_name ADD PARTITION (PARTITION p_20180504 VALUES IN ('20180504'));
ALTER TABLE table_name ADD PARTITION (PARTITION p_20180505 VALUES IN ('20180505'));
查看sql_mode
SELECT @@global.sql_mode
重新设置sql_mode
SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; (去掉only_full_group_by)
给查询的结果增加序号
SELECT (@i := @i + 1) i, name,address FROM t_user,SELECT @i := 0) AS i WHERE sex = '男'
查询表数据占用空间、索引占用空间、总条数和引擎(要进入information_schema库)
USE information_schema ;
SELECT
table_schema ,
table_name,
CONCAT(ROUND(SUM(DATA_LENGTH / 1024 / 1024 / 1024), 2),'G') AS data_size,
CONCAT(ROUND(SUM(INDEX_LENGTH / 1024 / 1024 / 1024), 2),'G') AS index_size,
table_rows ,
`engine`
FROM
TABLES
WHERE
TABLE_SCHEMA = 'db_name' AND
TABLE_NAME = 'table_name'
maven打包编译
mvn clean compile package -Dmaven.test.skip=true
maven 增加 外部jar到本地仓库
mvn install:install-file -DgroupId=taobao-sdk-java-auto -DartifactId=taobao-sdk-java -Dversion=1479188381469-20180904 -Dpackaging=jar -Dfile=F:/lib/taobao-sdk-java-auto_1479188381469-20180904.jar
pom.xml配置:
<dependency>
<groupId>taobao-sdk-java-auto</groupId>
<artifactId>taobao-sdk-java</artifactId>
<version>1479188381469-20180904</version>
</dependency>