mysql常规命令

本文主要讲MySQL常规命令，完成日常的常规操作即可，哈哈，也是有这种操作的^_^!

下面开始正题：(这种操作都作用在windows平台, mysql  Ver 14.14 Distrib 5.7.13, for Win64 (x86_64)  (mysql -V))

--mysql的基本安装：
在windows上，mysql的'安装'有两种方式：第一种：通过可执行的安装程序进行安装；第二种：解压缩文件到磁盘上，执行相关的命令即可。
第一种：常规的软件安装，一般按照说明进行安装即可。
附些链接一便参阅：
mysql 5.5 安装配置方法图文教程(作者：Brittany)：http://www.jb51.net/article/96898.htm
Windows下MySQL 5.6安装及配置详细图解(作者：萤火虫)：http://blog.sina.com.cn/s/blog_7cecec9501017cmk.html
MySQL 5.7版本的安装使用详细教程+更改数据库data的存储路径(作者：Mosen_Huang)：http://blog.csdn.net/huangmx1995/article/details/52909580
MySQL 5.7版本安装教程-踩坑总结(作者：Memory_lily)：http://www.cnblogs.com/MemoryLily/p/5980413.html
注：不清楚具体是什么问题，有的官方安装版本中没有给带默认的配置文件，自己就找个配置一下。


第二种：解压mysql的压缩文件后，(注意)这个就是你的mysql的运行程序(放在兜兜里，别丢了)。
假如解压后文件夹的名字是：MySQL5.6  且MySQL5.6放在D盘根目录下，且文件夹MySQL5.6的深度为1 便是bin，data，docs，include...等文件。（假如不清楚怎么操作，就按假如的走）
（1）放置好MySQL5.6文件夹后，就要编写一下配置文件了；一般的，都会有my-default.ini文件，这个文件本身不要动它，copy一份并命名为my.ini 然后开始编辑my.ini配置文件，基本的文件配置有：
（2）初始化并安装：(执行安装命令时也可设置相应的参数，此处简过)
mysqld --initialize
mysqld --install
注：安装成功会提示：Service successfully installed.
（3）启动mysql服务：net start mysql
（4）登陆mysql数据库：mysql -uroot -p<enter>
Enter password:<enter>
注：第一次登陆时root的密码为空，所以要求键入密码是直接按<enter>
（5）登陆成功：mysql>


文件配置：(基本的配置，更多...)
# The MySQL client
[client]
port  = 3306
socket=D:\MySQL5.6\3306\mysql.sock
#默认字符集
default-character-set=utf8

# The MySQL server
[mysqld]
port  = 3306
socket=D:\MySQL5.6\3306\mysqld.sock
pid-file=D:\MySQL5.6\3306\mysqld.pid
#mysql服务器安装目录
basedir=D:\MySQL5.6
#mysql数据存储目录
datadir=D:\MySQL5.6\3306\data
#临时文件目录
tempdir=D:\MySQL5.6\3306\temp
#mysql数据库数据编码
character-set-server = utf8
#配置大小写敏感类型
lower_case_table_names = 2
open_files_limit=10240
explicit_defaults_for_timestamp
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#Buffer
max_allowed_packet=256M
max_heap_table_size=256M
net_buffer_length=8k
sort_buffer_size=2M
join_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=16M

#Log
log-bin=D:\MySQL5.6\3306\binlog\mysql-bin
binlog_cache_size=32M
max_binlog_cache_size=512M
max_binlog_size=512M
binlog_format=mixed
log_output=FILE
log-error=D:\MySQL5.6\3306\mysql-error.log
slow_query_log=1
slow_query_log_file=D:\MySQL5.6\3306\slow_query.log
general_log=0
general_log_file=D:\MySQL5.6\3306\general_query.log
expire-logs-days=14

#InnoDB
innodb_data_file_path=ibdata1:2048M:autoextend
innodb_log_file_size=256M
innodb_log_files_in_group=3
innodb_buffer_pool_size=1024M


[mysql]
no-auto-rehash
prompt=(\u@\n) [\d]>\_
default-character-set=utf8


--mysql的用户管理：
--先登录数据库
mysql -h localhost -u root -p<enter>
然后提示输入密码，而后<enter>
注：其中，“-h”参数指连接的主机名，所以后面是localhost；“-u”参数表示用户名，此处的用户名为root；“-p”参数表示用户的密码，按下Enter键后就显示“Enter password：”，输入密码即可登录进去。

--选择数据库
use mysql;

--创建不存在的账号
grant all privileges on *.* to 'xganga'@'localhost' identified by '123456' with grant option;


--修改已经已经存在的账号，如root
方法1：update mysql.user set password=PASSWORD('123456') where User='root';
方法2；set password for root@localhost = password('654321');
方法3；mysqladmin -uroot -p654321 password 123456
方法4；在忘记root密码的时候，可以这样 
1. 关闭正在运行的MySQL服务。 
2. 打开DOS窗口，转到mysql\bin目录。 
3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。 
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。 
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。 
6. 连接权限数据库： use mysql; 。 
6. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。 
7. 刷新权限（必须步骤）：flush privileges;　。 
8. 退出 exit。 
9. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。 

--删除账号
delete from mysql.user where User='xganga';

--查看用户的权限
mysql> select * from mysql.user where user='username';
mysql> show grants for username@localhost;

--修改账号密码 
格式：mysqladmin -u用户名 -p旧密码 password 新密码


--mysql的数据库操作：
--数据库的查看
show databases;

--数据库的创建
create database testdb;    --创建的数据库名为：testdb
create database testdb character set utf8; 

----修改数据库，表，列的字符集
alter database testdb character set gbk; 
alter table t_name character set gbk; 
alter table t_name modify col_name varchar(50) character set gbk; 

--数据库的删除
drop database testdb;

--数据库的修改
--数据库修改名字的三种方法：
第一种：
RENAME database olddbname TO newdbname
这个是5.1.7到5.1.23版本可以用的，但是官方不推荐，会有丢失数据的危险

第二种：
1.创建需要改成新名的数据库。 
2.mysqldum 导出要改名的数据库 
3.删除原来的旧库（必须的必？） 
这种方法虽然安全，但是如果数据量大，会比较耗时。。。

第三种：
将testdb改为testdb_new   
mysql -uroot -p123456 -e 'create database if not exists testdb_new' list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='testdb'")  for table in $list_table do     mysql -uroot -p123456 -e "rename table testdb.$table to testdb_new.$table" done
注：如果新表名后面加数据库名，就会将老数据库的表移动到新的数据库，这种方法即安全，又快速。
或
假设旧库名是’testdb’, 新库名是’testdb_new’
首先创建目标库
create database testdb;
获取所有旧数据库的表名
use information_schema;
select table_name from TABLES where TABLE_SCHEMA=’testdb’;
重命名表的数据库名
rename table testdb.[tablename] to testdb_new.[tablename];

--显示上一次的错误警告消息
show warnings;


--mysql的表结构的操作：
在查看数据库表的信息时，先选择数据库：use db_name;
假设在testdb数据库中存在表：user, user_login, user_settings, aaa
--查看数据库中的表名
show tables;
show tables from db_name;

--查看表的结构
desc user;
show columns from user;
show create table user;    --更加详细
show full columns from user;    --更加详细
查看数据库编码：
show create database db_name;

查看表编码：
show create table t_name;

查看字段编码：
show full columns from t_name;

use infomation_schema;
select * from columns where table_name='tablename';    --更加详细

--创建表
介绍如何创建表的命令：help create table;
介绍如何修改表的命令：help alter table;
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
or
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
or
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
例：
create table user(uuid char(10), name char(20));    --基本创建
create table user_settings(uuid char(10) primary key, settings char(20));    --附加主键
create table platform(uuid char(10) primary key, io_name char(20), ip char(16) not null, port int(4) default 0);    --附加不为空值,默认值
create table t_name select * from testdb_new.user_tname;  --复制testdb_new.user_tname表，来创建本数据库中的t_name表，而且附在t_name的右侧
create temporary table temp_tname select column_1 column_2 from t_name;    --创建临时表 （tname, t_name为自己的表名）

create table platform_settings(uuid char(10) primary key, msg varchar(40) not null default '')engine=innodb default charset=gbk;    --设置引擎和字符集

（附：查看数据库整体使用的字符集命令：show variables like '%char%';）（使用方法：例：set character_set_connection=utf8;）
CREATE TABLE t_name ( 
id int(10) unsigned NOT NULL auto_increment, 
id_flag enum('Y','N') character set utf8 NOT NULL default 'N', 
id_flag_type int(5) NOT NULL default '0', 
id_flag_type_name varchar(50) character set utf8 NOT NULL default '', 
PRIMARY KEY (`id`) 
)  DEFAULT CHARSET=utf8; 


为表创建外键：
create table parent(id int not null, primary key(id))TYPE=INNODB;    -- type=innodb 相当于 engine=innodb
create table child(id int, parent_id int, index par_ind (parent_id), foreign key(parent_id) references parent(id) on delete cascade) TYPE=INNODB;    --向parent插入数据后,向child插入数据时,child中的parent_id的值只能是parent中有的数据,否则插入不成功;删除parent记录时,child中的相应记录也会被删除;-->因为: on delete cascade；更新parent记录时,不给更新;-->因为没定义,默认采用restrict。

create table child(id int not null primary key auto_increment, parent_id int, index par_ind (parent_id), constraint fk_1 foreign key (parent_id) references parent(id) on update cascade on delete restrict)type=innodb;    --此时，则可以更新parent记录时,child中的相应记录也会被更新;-->因为: on update cascade；但不能是子表操作,影响父表.只能是父表影响子表。

删除外键:
alter table child drop foreign key fk_1;

添加外键:
alter table child add constraint fk_1 foreign key (parent_id) references parent(id) on update restrict on delete set null;


为表创建索引
1.主键索引(primary key)
 主要作用是确定数据库表里一条特定数据记录的位置
 最好为每张数据表定义一个主键,一个表只能有一个主键
 主键的值不能为空
 primary key(id);

2.唯一索引(unique)
 都可以防止创建重复的值
 每个表都可以有多个唯一索引

3.常规索引
 提升数据库性能,最重要的技术
 提高查找的速度,减慢数据列上插入,删除,修改的速度
 可以单独使用,也可以在创建表时创建
 create index ind1 on tb_name(name,age);
 drop index ind1 on tb_name;
 index和key 是同义词
 ->index(name,key),  //or key(name,key),

4.全文索引
 fulltext类型的索引,只能在MyISAM表类型上使用,只用在varchar,char,text上使用
 也可以多个数据列使用
  create table books(
  id int,
  bookname varchar(30),
  price double,
  detail text not null,
  fulltext(detail,bookname),
  index ind(price),
  primary key(id));
select * from books where bookname like '%php%';
select bookname,price from books where MATCH(detail) AGAINST('php');

--删除表
drop table aaa;
or
drop table if exists aaa;

--修改表
----增加表的列
alter table 表名 add 列名 列类型 列参数;    --在表列的最后面
例：alter table tb_name add col_name char(20) not null default '';
    alter table tb_name add birth date not null default '0000-00-00';

alter table 表名 add 列名 列类型 列参数 after 某列;    --将新列加某列后一列
例：alter table tb_name add gender char(1) not null default '' after username;

alter table 表名 add 列名 列类型 列参数 first;    --把新列加在最前面
例：alter table tb_name add uuid int not null default 0 first;

----删除表的列
alter table 表名 drop 列名;
例：alter table tb_name drop col_name;

----修改表的名字
alter table tb_name rename to new_tb_name;
or
alter table tb_name rename new_tb_name;

----修改表的字段名及类型
alter table 表名 modify 列名 新类型 新参数;    --修改列类型
例：alter table tb_name modify gender char(4) not null default '';

alter table 表名 change 旧列名 新列名 新类型 新参数;    --修改列名和列类型
例：alter table tb_name change uuid uid int unsigned not null default 0;

----修改表的字符集
alter table tb_name convert to character set character_name;     --设置表的字符集

----修改表的主键
------例子表对象
create table tb_name(id int primary key, name varchar(20));
------添加主键
alter table tb_name change id id int(20) auto_increment;
------不能直接删除主键，先删自动增长，再删主键
alter table tb_name change id id int(10);
alter table tb_name drop primary key;

----修改表的外键
基本知识：数据库 mysql 建立外键的前提：本表的列必须与外键类型相同(外键必须是外表主键)。
外键作用： 使两张表形成关联，外键只能引用外表中的列的值！
指定主键关键字： foreign key(列名)
引用外键关键字： references <外键表名>(外键列名)
事件触发限制: on delete 和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action
------创建示例
create table tb_name(id int, name char(20), foreign key(id) references out_tb_name(id) on delete cascade on update cascade);
------修改实例
alter table tb_name add foreign key(col_name) references out_tb_name(col_name);
alter table tbl_products drop foreign key fk_symbol;

------外键修改的语法
ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

InnoDB也支持使用ALTER TABLE来删除外键：
alter table tb_name drop foreign key fk_symbol;
------外键修改实例
alter table pc add constraint fk_cpu_model foreign key (cpumodel) references parts(model) on update cascade;

注：除了 CASCADE 外，还有 RESTRICT(禁止主表变更)、SET NULL(子表相应字段设置为空)等操作。
取消外键约束：SET FOREIGN_KEY_CHECKS=0;

----修改表的索引
官方网址：URL: http://dev.mysql.com/doc/refman/5.7/en/create-index.html
创建索引的帮助命令：help create index;

------索引实例
我们以User表的username字段(类型为VARCHAR(50))为例，使用username字段的6个字符前缀来创建索引。
CREATE INDEX idx_user_username ON user (username(6));
alter table tb_name add [UNIQUE|FULLTEXT|SPATIAL] index index_name (index_col_name) [USING index_type];

------删除指定表中指定名称的索引
alter table tb_name drop index index_name;

------先删除，再以修改后的内容创建同名索引
alter table tb_name drop index index_tb_name;
create index index_tb_name on tb_name (tb_name(8));

------查看索引
--如果查看索引前，没有使用use db_name等命令指定具体的数据库，则必须加上FROM db_name 即结合的语法。
show index from tb_name [from db_name];
show index from [db_name.]tb_name;

----其他，通过其他表创建表
1. 此语句会拷贝表结构到新表newadmin中，但不会拷贝表中的数据。
例：create table new_tb_name like tb_name;
 
2. 此语句会拷贝数据到新表中，其实只是把select语句的结果建一个表，所以newadmin这个表不会有主键，索引。
例：create table new_tb_name as (select * from tb_name);
 
3. 如果要真正的复制一个表，可以用下面的语句。
create table new_tb_name like admin;
insert into new_tb_name select * from tb_name;
 
4. 不同的数据库间的操作。
create table new_tb_name like db_name.tb_name;
create table new_db_name.tb_name like db_name.tb_name;
 
5. 拷贝表中其中的一些字段。
create table new_tb_name as (select col_name1, col_name2 from tb_name);
 
6. 新建的表的字段改名。
create table new_tb_name as (select col_name col_name2 as col_new_name2 , col_name3 as col_new_name3 from tb_name);
 
7. 拷贝一部分数据。
create table tb_name as (select * from tb_name where left(col_name, 1) = expr);
 
8. 创建表的同时定义表中的字段信息。
create table tb_name (id integer not null auto_increment primary key) as (select * from tb_name);

9、多个表的UPDATE操作（更加 B表 修改A表数据）
update tb_name A, tb_name B set A.col_name = B.col_name where A.col_name2 = B.col_name;


--mysql的数据的操作：
帮助命令：help select;  help delete;  help alter;
命令网址：http://dev.mysql.com/doc/refman/5.7/en/select.html
and
http://dev.mysql.com/doc/refman/5.7/en/expressions.html
http://dev.mysql.com/doc/refman/5.7/en/functions.html

基本命令格式：select <字段1, 字段2, ...> from < 表名 > where < 表达式 >;
select col_name1, col_name2, col_name3, .... from tb_name where condition;

----查看表数据
select * from tb_name;

----删除表数据
delete from tb_name where col_name=value;

----修改表数据
帮助命令：help update;
基本命令格式：update 表名 set 字段=新值,… where 条件;
官方网址：URL: http://dev.mysql.com/doc/refman/5.7/en/update.html

------单表的MySQL UPDATE语句：
update [low_priority] [ignore] tb_name set col_name1=expr1 [, col_name2=expr2, ...] [where condition] [order by ...] [limit row_count];

------多表的UPDATE语句：
update [low_priority] [igmpre] tb_references set col_name1=expr1 [, col_name2=expr2, ...] [where condition];

例：copy from ...
 
UPDATE product p, productPrice pp SET pp.price = pp.price * 0.8 WHERE p.productId = pp.productId AND p.dateCreated < '2004-01-01';

UPDATE product p INNER JOIN productPrice pp ON p.productId = pp.productId SET pp.price = pp.price * 0.8 WHERE p.dateCreated < '2004-01-01';

UPDATE product p LEFT JOIN productPrice pp ON p.productId = pp.productId SET p.deleted = 1 WHERE pp.productId IS null;

UPDATE product p INNER JOIN productPrice pp ON p.productId = pp.productId SET pp.price = pp.price * 0.8, p.dateUpdate = CURDATE() WHERE p.dateCreated < '2004-01-01';

大批量更新数据mysql批量更新的四种方法（作者：_小小黑）
http://blog.csdn.net/u014520745/article/details/52416002



--mysql的字符集设置：
查看数据库编码：
show create database db_name;
----查看字符集
查看表编码：
show create table tb_name;

查看字段编码：
show full columns from tb_name;

----设置字符集
SET NAMES 'utf8'; 
它相当于下面的三句指令： 
SET character_set_client = utf8; 
SET character_set_results = utf8; 
SET character_set_connection = utf8; 

----修改字符集
alter database db_name default character set character_name;    --设置数据库默认字符集
alter table t_name convert to character set character_name;     --设置表的字符集
alter table t_name change col_name col_name character set character_name;     --设置字段的字符集



--mysql的命令脚本：--mysql的备份与恢复：
----加载一个sql命令文件
source命令在mysql命令提示中执行：
如果数据库过大，建议可以使用source命令
例：source E:\xganga.sql
or
导出数据库中所有表的sql脚本（包含数据）：
命令：mysqldump -uroot -p123456 db_name > e:\db_name.sql
or 
导出数据库中某一张表并且不包含表的数据：
命令：mysqldump -uroot -p -d db_name tb_name > x:\db_name_tb_name.sql
or
如果希望备份所有的数据库：
mysqldump -u root -p --all-databases > all_backup.sql

or  还原数据库
mysql -hlocalhost -uroot -p123456 < E:\xganga.sql
注：如果路径中有空格，不用管它。

例：文件内容如下，文件名：xganga.sql 路径：E:\xganga.sql
create database xganga;
use xganga;
create table software (id int, name varchar(20));

--更多命令查看：mysqldump --help

--mysql的视图创建：

MySQL笔记之视图的使用详解：http://www.jb51.net/article/36363.htm

其他问题：
1、如果安装失败，而且同时移除也失败，那么的话就是注册表的一些数据没有被清除所导致的
   注册表的路径：
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
   imagepath的键值（此是按照上面的配置路径）：D:\Mysql\bin\mysqld --defaults-file=D:\Mysql\my.ini mysql
   
（另外一个注册的mysql注册表项的路径：HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\RADAR\HeapLeakDetection\DiagnoseApplications\）


----数据表类型及存储位置：

 mysql和大多数数据库不同,有一个存储引擎概念
 可以针对不同的存储引擎需求选择最优的存储引擎
 show engines; 查看支持的引擎
 show variables like 'table_type';
 现在只学MyISAM和InnoDB
 如果没有设置表类型,就会根据配置在这两种之间选择一种
 create table () type =InnoDB;
 create table () engine =InnoDB;
 MyISAM表类型是默认的，强调快速读取操作，比较成熟的表类型
 在一个MySQL库中可以（创建表时）指定不同表类型
 经常使用 OPTIMIZE TABLE 表名 来对MyISAM类型的表进行优化
 
 功能          MyISAM      InnoDB
 事务处理   不支持        支持
 数据行锁定 不              支持
 外键约束    不               支持
 表空间占用 小               相对大 最大2倍
 全文索引    支持           不支持

 MyISAM 中表.frm表结构,.MYD表数据,.MYI表索引
 InnoDB .frm
 在MYSQL ini中在skip-InnoDB;前加#可以创建InnoDB类型的表


推荐文章：

MYSQL删除表的记录后如何使ID从1开始    --俗世 凡尘
http://www.cnblogs.com/no7dw/archive/2010/04/16/1713240.html

mysql中删除表记录delete from和truncate table的用法区别      --morelearning
http://blog.sina.com.cn/s/blog_775102c60100q611.html

show processlist;
SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。

MySQL锁定状态查看命令    (作者：cdai)
http://blog.csdn.net/dc_726/article/details/8576151


后半部分待续......