MySQL命令及复制表简介

目录

复制表结构

完整复制表

部分复制表

数据库导入导出

外部文件导入数据库中

mysql命令行

---

MySQL可方便的通过客户端工具或命令行进行操作：

Drop Database If Exists 'n1'
Create Database 'n1'

Drop Table If Exists n1.t1
Create Table n1.t1( ... )

 

复制表结构

mysql中可通过create table as 和create table like来复制表结构：

• create table t2 as select * from t1 [where 1=2 或 limit 0];

as创建出来的新表（t2）缺少源表（t1）的索引信息，只有表结构相似（如bigint可能会变成int），没有索引。

注意：这个语句其实只是把select语句的结果建一个表。因此，可以复制表（全部内容或选定的内容）

• create table t2 like t1;

like 创建出来的新表包含源表的完整表结构和索引信息。

 

完整复制表

真正的完整复制一个表：

CREATE TABLE newT LIKE oriT; 
INSERT INTO newT SELECT * FROM oriT;

-- 也可以跨数据库操作：
CREATE TABLE newDB.newT LIKE oriDB.oriT;

 

部分复制表

部分复制（修改）一个表：

-- 拷贝一个表中其中的一些字段。
CREATE TABLE newT AS 
( 
    SELECT username, password FROM oriT 
)；

-- 修改字段名
CREATE TABLE newT AS 
( 
SELECT id, username AS uname, password AS pass FROM oriT 
)；

-- 拷贝一部分数据。
CREATE TABLE newT AS 
( 
SELECT * FROM oriT WHERE username like 's%' 
)

 

数据库导入导出

使用mysqldump命令（在cmd命令行中执行），可方便导出数据库文件

1)导出所有数据库

 mysqldump -u{user} -p -A > c:\out.sql

2)导出数据和表结构

 mysqldump -u{user} -p {db_name} > c:\out.sql

 

将数据库 mydb 导出到 c:\out.sql 文件中。

c:\> mysqldump -h localhost -u root -p mydb >c:\out.sql

将数据库 mydb 中的 mytable 导出到 c:\out.sql 文件中。

c:\> mysqldump -h localhost -u root -p mydb mytable>c:\out.sql

将数据库 mydb 的表结构导出到 c:\out.sql 文件中。

c:\> mysqldump -h localhost -u root -p mydb -d --add-drop-table >c:\out.sql

• -d 只到表结构（没有数据）

• --add-drop-table 在每个create语句之前增加一个drop table

 

外部文件导入数据库中

1)使用“source”命令（在mysql中）

mysql>source  c:\out.sql

2)使用“<”符号（在cmd命令行中）

c:\> mysql -u root –p <  c:\out.sql

 

mysql命令行

通过mysql命令行，可方便地登录数据库，做各种数据库操作。

登录：mysql -h <ip> -u<user> -p<pass> -P 3306

如：mysql -h 192.168.1.100 -u root -p -P 3306

执行-e后面sql语句，并把结果保存到文件

mysql -uroot -p -e "use mysql; show tables;" > c:\out.txt

进入命令行后（mysql>）后，就可使用sql语句进行各种操作（创建、删除表）与查询了，除常规Sql操作语句（创建、修改、删除、查询等）外，还有：

• show databases; 显示所有数据库名

• show tables; 显示所有表名

• show create table table_name; 显示完整的表创建命令

• show full columns from table_name; 显示列详细信息（包括权限）

• show table status; 显示表的状态

• show full processlist; 当前所有连接的详细情况(没有full，只显示前100个连接)

• use <db_name>; 切换当前数据库

• describe <table_name>; 显示列的详细信息（列名、类型、是否空、键、默认值等）

• select version();

• select database(); 显示当前数据库名

• SELECT CURRENT_DATE; 显示‘年-月-日’

• select now();或select current_timestamp(); 显示‘年-月-日 时分秒’

• quit或\q：退出

• show status like '%<var>%'; 查看状态，var如下：

  • Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 

  • Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。 

  • Connections 试图连接MySQL服务器的次数。 

  • Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。 

  • Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。 

  • Delayed_writes 用INSERT DELAYED写入的行数。 

  • Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 

  • Flush_commands 执行FLUSH命令的次数。 

  • Handler_delete 请求从一张表中删除行的次数。 

  • Handler_read_first 请求读入表中第一行的次数。 

  • Handler_read_key 请求数字基于键读行。 

  • Handler_read_next 请求读入基于一个键的一行的次数。 

  • Handler_read_rnd 请求读入基于一个固定位置的一行的次数。 

  • Handler_update 请求更新表中一行的次数。 

  • Handler_write 请求向表中插入一行的次数。 

  • Key_blocks_used 用于关键字缓存的块的数量。 

  • Key_read_requests 请求从缓存读入一个键值的次数。 

  • Key_reads 从磁盘物理读入一个键值的次数。 

  • Key_write_requests 请求将一个关键字块写入缓存次数。 

  • Key_writes 将一个键值块物理写入磁盘的次数。 

  • Max_used_connections 同时使用的连接的最大数目。 

  • Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。 

  • Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。 

  • Open_tables 打开表的数量。 

  • Open_files 打开文件的数量。 

  • Open_streams 打开流的数量(主要用于日志记载） 

  • Opened_tables 已经打开的表的数量。 

  • Questions 发往服务器的查询的数量。 

  • Slow_queries 要花超过long_query_time时间的查询数量。 

  • Threads_connected 当前打开的连接的数量。 

  • Threads_running 不在睡眠的线程数量。 

  • Uptime 服务器工作了多少秒。

 

SELECT...INTO OUTFILE可以非常快速地把一个表转储到服务器上，语句将表的内容导出为一个‘目标文件’（创建到服务器主机上，因此必须拥有文件写入权限；同时，“目标文件”不能是一个已经存在的文件）

SELECT [列名] FROM table [WHERE 语句] INTO OUTFILE '目标文件' [OPTION];

“OPTION”参数为可选参数选项，其可能的取值有：

• FIELDS TERMINATED BY '分隔符'：设置字段之间的分隔符，可以为单个或多个字符。默认值是“\t”。

• FIELDS ENCLOSED BY '字符'：设置括住字段的符号，只能为单个字符。默认不使用任何符号。

• FIELDS OPTIONALLY ENCLOSED BY '字符'：设置括住CHAR、VARCHAR和TEXT等字符型字段的值。默认情况下不使用任何符号。

• FIELDS ESCAPED BY '字符'：设置转义字符，只能为单个字符。默认值为“\”。

• LINES STARTING BY '字符串'：设置每行数据开头的字符，可以为单个或多个字符。默认情况下不使用任何字符。

• LINES TERMINATED BY '字符串'：设置每行数据结尾的字符，可以为单个或多个字符。默认值是“\n”。

• FIELDS和LINES两个子句都是自选的，但是如果两个子句都被指定了，FIELDS必须位于LINES的前面。

select * into outfile 'out.txt' fields terminated by ',' lines terminated by '\n' starting by '\t'  from my_table;