Mysql常用命令

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

Item	Description
安装Mysql service(.zip下载Mysql)	mysqld -install
查看记录	select * from insterm;
查看数据库状态	status
查找非冗余数据	select distinct * from movies;
初始化Mysql	mysqld --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';
登录Mysql	mysql -u root -p
关闭Mysql service	net 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 service	net 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;
退出Mysql	exit;
退出当前命令行	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);