MySQL Common operation commands

Basic command

  • create database

    CREATE DATABASE menagerie

    注意字符集, 可能默认是 ladin 不支持中文, 所以创建 database 时可以指定字符集:
    CREATE DATABASE menagerie DEFAULT CHARACTER SET utf8

    如果已经创建了 database 想修改呢?
    ALTER DATABASE surge CHARACTER SET utf8;

    另外, 如果数据表已经创建了, 数据表的字符集还是原来的, 我们还需要修改表:
    alter table collection_case CONVERT TO CHARACTER SET utf8;

  • 进入数据库

    USE ksxing;

  • 显示所有数据库

    SHOW DATABASES

  • 显示数据库创建命令(数据库属性, 例如编码)

    show create database surge

  • 显示所有表格

    SHOW TABLES

  • 显示表的结构

    DESC table_name

  • 查看当前数据的函数

    SHOW FUNCTION STATUS

  • 查看表创建过程

    SHOW CREATE TABLE table_name

  • 查看创建语句

    -t忽略创建表格过程

    $ mysqldump -h localhost -u root -P 3306 -t <database name> <table name> --where="id<=100" -p

  • truncate all tables

    https://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command

    $ mysql -uUSER -pPASSWORD -Nse 'show tables' DATABASE_NAME | while read table; do mysql -uUSER -pPASSWORD -e "truncate table $table" DATABASE_NAME; done

  • 撤销当前命令
    在输入定界符;之前输入\c

创建

  • 创建数据表

    CREATE TABLE examinees ( *** )

    标准的创建数据表示例，包括创建索引：

    create table user_login_new
(id mediumint unsigned not null auto_increment,
primary key (id),
user_id mediumint unsigned,
company_id smallint unsigned,
login_is_succ tinyint,
login_device tinyint,
login_time datetime,
login_ip varchar(20),
index login_time_index (id,company_id,login_time))

update

  • insert row

    > INSERT INTO examinees (`exam_id`,`user_ids`) VALUES (1,'1,2'),(2,'2,3');

    you should use ` to quote the column name

  • 修改行

    update *** set ***=*** where ***

  • 删除表的某个字段

    #删除exam_dep表的is_all字段
>ALTER TABLE exam_dep DROP COLUMN is_all;

  • 删除表

    DROP TABLE test

  • 复制一个字段的值到另一个字段 `

    Update {your_table} set {source_field} = {object_field} WHERE case;

  • 删除某个字段的所有值

    # 还是用上面的命令
> UPDATE {your_table} SET {column_field}=NULL;

  • 约束条件删除部分数据

    > delete from exam_dep where department_id='';

  • 修改列的数据类型

    > ALTER TABLE mytb1 MODIFY i FLOAT(5,1) DEFAULT 100;

  • add column

    > ALTER TABLE ext_paper_test ADD COLUMN option_peer_score float(5,1) AFTER has_option_peer;

    add created_at and updated_at columns to a table

    > ALTER TABLE badass_innershop  ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 
> UPDATE badass_innershop SET created_at = CURRENT_TIMESTAMP,  updated_at = CURRENT_TIMESTAMP WHERE created_at IS NULL OR updated_at IS NULL;

    and you can add indexes

    > CREATE INDEX idx_badass_innershop_created_at ON badass_innershop(created_at);
> CREATE INDEX idx_badass_innershop_updated_at ON badass_innershop(updated_at);

  • 在首列增加 id 列

    > alter table companys_new add column id mediumint unsigned not null auto_increment primary key first;

  • 修改列

    > alter table ext_paper_test change has_option_peer has_option_peer_score enum('1','0') default '0';

  • 删除列

    alter table sub_admin drop column allow_modify;

  • 更改某列数据

    update fix_paper_test set option_peer_score=0 where option_peer_score is null;

  • 清空表（清空后添加数据 id 从 1 开始）

    truncate table ***;

    如果因为外键的原因 truncate 失败, 则需要这样操作:

    SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

  • 将别的表的内容复制到本表

    > insert into [table] (valueA,valueB) select valueA,valueB from [table2]

  • 删除某条数据

    > delete from *** where id = *

  • 修改表名

    > alter table *** rename to ***

  • 更新时间

    > update exam_results set end_time =date_add(end_time,interval 1 hour) where exam_info_id=20536;

    date_add 是增加时间 date_sub 是减少时间

  • truncate all tables

    $ mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done

  • 替换某个字段的某些字符

    MySQL search and replace some text in a field

    > UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE INSTR(field, 'foo') > 0;

query

  • 查询格式化显示
    加上\G
    select * from version where ver_map_tile_id = 2479\G;

  • 跨表查询

    > select a.company_id, b.company_name from user_pay as a companys_new as b where a.company_id = b.id;

  • 模糊查询

    > select * from companys_new where domain like "%gaoxiaobang.com%";

  • left join query column not in another table

    > select * from users left join department_user on users.id=department_user.user_id where users.company_id=949 and department_user.user_id is null;

  • 检索前五行数据

    > SELECT * FROM examinees ORDER BY id LIMIT 5;

  • 联合查询

    > select users.user_name,exam_results.results from users,exam_results where users.id=exam_results.user_id and exam_results.exam_info_id=19256 into outfile '/tmp/hunan15.csv';

  • 联合查询 1

    > select companys_new.id,companys_new.company_name, max(user_login_new.login_time) from companys_new left join user_login_new on (companys_new.id=user_login_new.company_id) order by companys_new.id limit 230,10;

  • 联合查询 2

    > select companys_new.id,companys_new.company_name,max(user_login_new.login_time) from companys_new, user_login_new where companys_new.id=user_login_new.company_id and user_login_new.login_time>="2016-07-01 00:00:00" and user_login_new.login_time<="2016-08-11 00:00:00" group by companys_new.id;

  • query where a field contains any Chinese character

    > SELECT * FROM your_table WHERE your_column REGEXP '[一-龥]';

  • query where a field not contains multiple words

    > SELECT * FROM your_table WHERE your_column NOT REGEXP 'word1|word2|word3';

  • query where a field matches a regex and one of the matches is larger than a certain value

    > SELECT * from your_table WHERE version REGEXP '^3\\.[0-9]+\\.[0-9]+(\\.[0-9]+)?$' AND CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) AS UNSIGNED) >= 40;

索引

# 单列索引
> alter table table_name add index index_name (column_list) ;
# unique索引
> alter table table_name add unique (column_list);
# primary索引
> alter table table_name add primary key (column_list);
# 全文索引
> alter table table_name add fulltext index index_name (column_list);
# 多列索引
> alter table table_name add index index_name (user, name);
# 删除索引
> DROP INDEX index_name ON tbl_name;

状态

＃查看mysql的进程，可以观察哪些查询耗时比较高
show processlist

administration

  • 显示用户

    select host, user from mysql.user

  • query user privileges

    SHOW GRANTS FOR 'yangle'@'%'

  • create user

    CREATE USER 'yangle'@'%' IDENTIFIED BY 'password';
    % means you can login from any host.
    if you want to restrict user yangle only login from localhost, you can use localhost instead of %. if you can’t login in other machine, please check your mysql’s configuration file which generally located in /etc/mysql/my.cnf, and comment the line bind-address = 127.0.0.1, then restart mysql service by command sudo systemctl restart mysql.service.

  • 给用户赋权限

    # GRANT ALL PRIVILEGES ON mydatabase.* TO 'yangle'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'yangle'@'%' WITH GRANT OPTION;
# only grant SELECT permission on a specific database
GRANT SELECT ON mydatabase.* TO 'yangle'@'%';

  • 删除用户

    DROP USER 'jeffrey'@'localhost'

  • 查询数据路径

    select @@datadir

  • 设置密码

    > SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

  • 查看端口

    show global variables like 'port'

  • set character set

    if the data you query contains Chinese characters but display ?, then you need to set the character set to utf8mb4, which supports all Unicode characters including emojis.

    > SET NAMES 'utf8mb4';