MYSQL中常用命令

最新推荐文章于 2022-10-31 19:21:26 发布

单向街的夏天

最新推荐文章于 2022-10-31 19:21:26 发布

阅读量1.1k

点赞数

分类专栏：数据存储

本文链接：https://blog.csdn.net/wenbingoon/article/details/8803712

版权

数据存储专栏收录该内容

15 篇文章 0 订阅

订阅专栏

PS:

1【只有在MySQL中可以使用“utf-8”的别名“utf8”，但是在其他地方一律使用“UTF-8”。】

　　　在命令“set names utf8”外，一律用“UTF-8”。

2 插入数据sql使用“insert into set”形式的理解

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]。
往往是“ insert into table_name set a=$a,b=$b”的格式。
在mysql手册中能看到这种语法：之前不明白。为什么要使用这种方式。 后来听同学说，这样使用是为了提高插入速度。

（一）连接到远程主机上的MYSQL的命令

命令行各参数的含义：

mysql-h (host--IP地址) -P (port端口，默认3306)-u (用户名root)-p(password) -D(database, 指定数据库)

mysql连接本地远程数据库命令范例：

一、MySQL 连接本地数据库，用户名为“root”，密码“123”（注意：“-p”和“123” 之间不能有空格）

C:\>mysql -h localhost -u root -p123

二、MySQL 连接远程数据库（192.168.0.201），端口“3306”，用户名为“root”，密码“123”

C:\>mysql -h 192.168.0.201-P 3306-u root -p123

三、MySQL 连接本地数据库，用户名为“root”，隐藏密码

C:\>mysql -h localhost -u root -p
四、MySQL 连接本地数据库，用户名为“root”，指定所连接的数据库为“test”

C:\>mysql -h localhost -u root -p123-D test

（二）mysql修改表名，列名，列类型，添加表列，删除表列--alter 语句

alter table test rename test1; --修改表名

alter table test add column name varchar(10); --添加表列

alter table test drop column name; --删除表列

alter table test modify address char(10) --修改表列类型

alter table test change address address char(40)-----修改表列类型

alter table test change column address address1 varchar(30)--修改表列名

（三）mysql导入导出命令

3.1命令行导入数据库 [ 执行sql脚本(导入数据) ]

方法一:

在命令行下(未连接数据库),输入 mysql -h localhost -u root -p123456 < F:\hello world\niuzi.sql(注意路径不用加引号与分号（“” /;）) ,回车即可.

方法二:

在命令行下(已连接数据库,此时的提示符为 mysql> ),输入 source F:\hello world\niuzi.sql (注意路径不用加引号的) 或者 \. F:\hello world\niuzi.sql (注意路径不用加引号的) 回车即可。

3.2 MySQL命令行导出数据库：

(1)，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录
如我输入的命令行：cd C:\Program Files\MySQL\MySQL Server 4.1\bin
(或者直接将windows的环境变量path中添加该目录)

(2)，导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名
如我输入的命令行:mysqldump -u root -p news > news.sql (-p之用输入密码，news为数据库。执行命令后会让你输入进入MySQL的密码)
（如果导出单张表的话在数据库名后面输入表名即可）
(3)、会看到文件news.sql自动生成到bin文件下

(4)、命令行下具体用法如下：

mysqldump -u 用戶名 -p 数据库名表名 > 脚本名; //导出整个数据库结构和数据

mysqldump -h localhost -u root -p database table > dump.sql //导出单个数据表结构和数据

mysqldump -h localhost -u root -p -d database > dump.sql
//导出整个数据库结构（不包含数据,数据库名前加-d）
mysqldump -h localhost -u root -p -d database table > dump.sql

//导出单个数据表结构（不包含数据）

3.3 MySql数据库导出csv文件命令

# CHARACTER SET gbk
（Excel展示完好）首推：select id ,ecityid,cityname,pid, name,address,tel,glat,glng,blat,blng from jd into outfile 'e:\\output1.csv' CHARACTER SET

gbk fields terminated by ','optionally enclosed by ''lines terminated by '\n';

3.4 Mysql导入CSV文件命令(或利用navicat for mysql 来实现 3.4与3.5)

LOAD DATA INFILE "d:/diaoru.csv" REPLACE INTO TABLE shanglv_hotel FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n"(city,name,address,tel,lng,lat);

// 注意没有id字段，且记录中不要有逗号，逗号已作为各自字段的分隔符

FIELDS TERMINATED BY ---- 字段终止字符 (excel转化为csv文件纵使你使用分列，并且把分隔符限定为“分号”，一旦你保存，csv文件还是默认为逗号分隔，导入的文件中，每行记录除了字段分隔时出现的逗号，其它地方不允许出现，否则发生错误)
OPTIONALLY ENCLOSED BY ---- 封套符

LINES TERMINATED BY ---- 行终止符

3.3与3.4参照的表结构 ----------shanglv_hotel 的表结构如下：

create table shanglv_hotel(
id int unsigned primary key auto_increment, // id自增长，
city varchar(20),
name varchar(250) ,
address varchar(200) ,
tel varchar(50),
lng varchar(15),
lat varchar(15)
);

（四） mysql修改字段长度(sql命令)

试了好长时间终于找到

alter table 表名 modify column 字段名类型;

例如

数据库中user表 name字段是varchar(30)

可以用

alter table user modify column name varchar(50) ;

这样就修改了

（五）如何恢复 MySQL 的 .frm、.myd、myi 数据库文件

.frm 表结构文件
.myd 表数据文件
.myi 表索引文件

一个 MySQL 数据库就是由很多这样的文件构成的。我们可以直接把它们复制出来，然后拿到另一台机子上装起，实现了数据库移植。

具体做法

将这些文件连同容纳他们的文件夹复制到另一台机子的 MySQL 数据库文件夹中，就可以了。有人说需要重新启动 MySQL，我这里没重新启动也认到了。

那么 MySQL 数据库文件夹在哪里？

这是个问题，MySQL 数据库文件夹是在 my.ini 配置文件中说明了的，比如是：

#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/"

注意，上面的是 ProgramData 不是 Program Files 噢。

那么 my.ini 又在哪里呢？

它在 MySQL 的安装目录下，比如我的：C:\Program Files\MySQL\MySQL Server 5.1。

（六）允许远程访问--更改最大连接数---VARCHAR占用的空间

1、允许远程访问

在linux下安装mysql之后，默认是不允许远程访问的。

安装mysql之后，首先要启用之，参考：http://blog.csdn.net/bhq2010/article/details/7364502

然后登录mysql：

[html]view plaincopy 
     
 $ mysql -u root -p  

输入密码。然后执行：

[html]view plaincopy 
     
 mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;  

其中，myuser为要授权远程方法的用户，%表示对所有的机器，这里也可以用ip或者主机名指定主机，mypassword是myuser的密码

然后：

[html]view plaincopy 
     
 mysql> FLUSH PRIVILEGES;  

使之生效。重启mysql。

2、更改最大连接数

mysql最大连接数默认是100个（windows上的mysql貌似是20个），有时就需要更改以适应应用的需求，方法如下：

登录mysql后，执行：

[html]view plaincopy 
     
 mysql> set GLOBAL max_connections=200  

即可，其中200是要设置的最大连接数。

3、VARCHAR占用的空间

VARCHAR在mysql中占用的空间会比所存的字符串的长度大一些，据说是大约大一倍，主要是为了满足以后更新数据的时候字符串的增长。如果所插入的字符串长度超过VARCHAR的长度，默认的话，mysql会自动截取字符串而不报异常，这一点是需要注意的。

（七）truncate,delete,drop的异同点

注意:这里说的delete是指不带where子句的delete语句

相同点

truncate和不带where子句的delete, 以及drop都会删除表内的数据

不同点:

1. truncate和 delete只删除数据不删除表的结构(定义)

drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.

2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.

truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动

显然drop语句将表所占用的空间全部释放
truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始).

4.速度,一般来说: drop>; truncate >; delete

5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及

使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.

想删除表,当然用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可.
如果和事务有关,或者想触发trigger,还是用delete.

如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据

参考：http://www.cftea.com/c/2010/09/IRYY0J1QVOOR0PFC.asp