Mysql常用命令

和Mysql打交道也有些时日了，其间遇到过大大小小各种问题，收集到以下一些常用命令，希望有一定参考价值：

ItemDescription安装Mysql service(.zip下载Mysql)mysqld -install查看记录select * from insterm;查看数据库状态status查找非冗余数据select distinct * from movies;初始化Mysqlmysqld --initialize --console
(运行成功后，将自动生成data文件夹；如果mysql安装路径下已存在该文件夹，需先删除才能再次运行此命令)
--console选项将显示随机生成的初始密码，首次进入mysql时需提供创建表Mysql: create table insterm (EN text default null,JP text default null,CH text default null,JPD text default null,CHD text default null,END text default null)CHARACTER SET = utf8;
Derby_Insurance: create table insterm (EN varchar(1000),JP varchar(1000),CH varchar(1000) ,JPD varchar(5000),CHD varchar(5000) ,ENDisc varchar(5000));
Derby_Pharm: create table pharm_term (Type varchar(10),Sub_Type varchar(50),EN varchar(200),JP varchar(1000),CH varchar(1000) ,ENDisc varchar(5000),JPD varchar(5000),CHD varchar(5000));
Netbeans-derby-Insurance: create table instermfull (TERMID int GENERATED ALWAYS AS IDENTITY,EN varchar(1000),JP varchar(1000),CH varchar(1000) ,JPD varchar(5000),CHD varchar(5000) ,ENDISC varchar(5000));创建数据库SQL:create database terms;导入数据load data infile "D:\\movie.csv" into table movies character set utf8 fields terminated by ',' lines terminated by '\r\n';导入数据（中文路径）load data infile "D:/2.学无止境/iWorkspace/SQL/movie.csv" into table movies character set utf8 fields terminated by ',' lines terminated by '\r\n';登录Mysqlmysql -u root -p关闭Mysql servicenet stop mysql建表create table movies (id varchar(3), name varchar(100), genre varchar(100), year varchar(10),imdb_rating varchar(50) ) CHARACTER SET = utf8;将.txt文件导入数据表Mysql:Load Data InFile 'D:\\TermNew.txt' Into Table insterm character set utf8 fields terminated by ',' lines terminated by '\r\n';
Derby: CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'INSTERM', null , null,'TermNew.csv',null, null,null,0);
Netbeans-derby-Insurance: CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'INSTERMFULL', 'EN,JP,CH,JPD,CHD,ENDISC' ,'1,2,3,4,5,6','TermNew.csv',null, null,null,0);批量操作数据库source D:/Program Files/mysql-5.6.19-winx64/mysql-5.6.19-winx64/data/book_source.sql;启动Mysql servicenet start mysql切换数据库use terms;清空数据表delete from insterm;去重后复制数据到新表insert into movies_backup (select distinct * from movies);删除数据表drop table authors;设置字符集set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_filesystem=utf8;
set character_set_results=utf8;
set character_set_server=utf8;数据表更名alter table movies_backup rename movies;退出Mysqlexit;退出当前命令行mysql> \c显示Mysql排序字符集SHOW collation; 显示Mysql字符集SHOW CHARACTER SET; 显示表结构show columns from insterm;显示建表语句show create table movies;显示数据表show tables;显示数据库字符集SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';显示已有数据库show databases;向数据表插入记录insert into insterm values (6,"MA-2013","System","","","SYMAP","","SYstems for Mass Application、通販系の新契約申込の手続きについて、処理/管理するアプリケーション。仕組み的にはワークフローに類似。（申込書?告知書等のイメージ取得、査定、キャッシュレス、自動成立等を処理する機能を持つ。）","","");卸载Mysql service(.zip下载Mysql)mysqld -remove修改Mysql密码mysql> set password=password('newPassword');修改数据表字符集ALTER TABLE insterm DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;修改数据库字符集ALTER DATABASE terms DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;在表中删除列mysql> alter table address_book remove column block;在表中增加列mysql> alter table address_book add column block varchar (50);