MySQL常用指令

转自：http://www.2cto.com/os/201408/329505.html

MySQL常用指令（备查）

一、常用命令：

1、显示数据库列表。

show databases;

2、显示库中的数据表：

use mysql;

show tables;

3、显示数据表的结构：

describe 表名;

4、建库：

create database 库名;

5、建表：

use 库名；

create table 表名 (字段设定列表)；

6、删库和删表:

drop database 库名;

drop table 表名；

7、将表中记录清空：

delete from 表名;

8、显示表中的记录：

select * from 表名

9、连接MySQL：

格式: mysql -h 主机地址 -u用户名 -p用户密码

例 1.连接到本机上的 MySQL。

        mysql -uroot -pmysql;

    连接到远程主机上的 MYSQL。

        mysql -h 127.0.0.1 -uroot -pmysql; 

  2.连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：

        mysql -h110.110.110.110 -u root -p 123;（注:u与root之间可以不用加空格，其它也一样）

10、退出MYSQL命令： exit （回车）

11、修改新密码:

在终端输入：mysql -u用户名 -p密码，回车进入Mysql。

use mysql;

update user set password=PASSWORD(‘新密码’) where user=’用户名’;

flush privileges; #更新权限

quit; #退出

13、修改密码:

格式：mysqladmin -u用户名 -p旧密码 password 新密码
    1、给root加个密码ab12。首先在DOS下进入目录mysql\bin，然后键入以下命令

        mysqladmin -u root -password ab12
        (注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。)

    2、再将root的密码改为djg345。

        mysqladmin -u root -p ab12 password djg345
        (注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符)
    3、命令行修改root密码：

        mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;

        mysql> FLUSH PRIVILEGES;

14、显示当前的user：

mysql> SELECT USER();

二、其他操作：

1、增加新用户：

格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”
(1)增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL，然后键入以下命令：

    grant select,insert,update,delete on *.* to test1”%" Identified by “abc”;

(但增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见2)

(2)增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），

    这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。

    grant select,insert,update,delete on mydb.* to test2@localhost identifiedby “abc”;

如果你不想test2有密码，可以再打一个命令将密码消掉:

    grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;

2、删除用户：

mysql -u用户名 -p密码

mysql>delete from user where user='用户名' and host='localhost';

mysql>flush privileges;

//删除用户的数据库

mysql>drop database dbname;

3、数据库操作：

(1)显示所有的数据库

    mysql> show databases;(注意:最后有个 s)

(2)创建数据库

    mysql> create database test;

(3)连接数据库

    mysql> use test;

(4)查看当前使用的数据库

    mysql> select database();

(5)查看当前数据库包含的表信息

    mysql> show tables; (注意:最后有个 s)

(6)删除数据库

    mysql> drop database test;

4、表操作：

备注:操作之前使用“use <数据库名>”应连接某个数据库。

(1)建表

    命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);

    例子：

    mysql> create table MyClass(

    > id int(4) not null primary key auto_increment,

    > name char(20) not null,

    > sex int(4) not null default '0',

    > degree double(16,2));

(2)获取表结构

    命令: desc 表名，或者show columns from 表名

    例子：

    mysql> describe MyClass

    mysql> desc MyClass;

    mysql> show columns from MyClass;

(3)删除表

    命令:drop table <表名>

    例如:删除表名为 MyClass 的表

    mysql> drop table MyClass;

(4)插入数据

    命令:insert into <表名> [( <字段名 1>[,..<字段名 n > ])] values ( 值 1 )[, ( 值 n )]

    例子:

    mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

(5)查询表中的数据

    查询所有行

        mysql> select * from MyClass;

    查询前几行数据

    例如:查看表 MyClass 中前 2 行数据

        mysql> select * from MyClass order by id limit 0,2;

    或者

        mysql> select * from MyClass limit 0,2;

(6)删除表中数据

    命令:delete from 表名 where 表达式

    例如:删除表 MyClass 中编号为 1 的记录

    mysql> delete from MyClass where id=1;

(7)修改表中数据

    命令:update 表名 set 字段=新值,... where 条件

    mysql> update MyClass set name='Mary' where id=1;

(8)在表中增加字段

    命令:alter table 表名 add 字段 类型 其他;

    例如:在表 MyClass 中添加了一个字段 passtest,类型为 int(4),默认值为 0

    mysql> alter table MyClass add passtest int(4) default '0'

(9)更改表名

    命令:rename table 原表名 to 新表名;

    例如:在表 MyClass 名字更改为 YouClass

        mysql> rename table MyClass to YouClass;

(10)更新字段内容

    命令:update 表名 set 字段名 = 新内容

        update 表名 set 字段名 = replace(字段名, '旧内容', '新内容');

    例如:文章前面加入 4 个空格

        update article set content=concat(' ', content);

5、数据库导入导出：

(1)从数据库导出数据库文件

    使用“mysqldump”命令

    首先进入 DOS 界面,然后进行下面操作。

    1)导出所有数据库

        格式:mysqldump -u [数据库用户名] -p -A>[备份文件的保存路径]

    2)导出数据和数据结构

        格式:mysqldump -u [数据库用户名] -p [要备份的数据库名称]>[备份文件的保存路径]

    举例:

    例 1:将数据库 mydb 导出到 e:\MySQL\mydb.sql 文件中。

        打开开始->运行->输入“cmd”,进入命令行模式。

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

        然后输入密码,等待一会导出就成功了,可以到目标文件中检查是否成功。

    例 2:将数据库 mydb 中的 mytable 导出到 e:\MySQL\mytable.sql 文件中。

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

    例 3:将数据库 mydb 的结构导出到 e:\MySQL\mydb_stru.sql 文件中。

        c:\> mysqldump -h localhost -u root -p mydb --add-drop-table >e:\MySQL\mydb_stru.sql

        备注:-h localhost 可以省略,其一般在虚拟主机上用。

    3)只导出数据不导出数据结构

    格式:

        mysqldump -u [数据库用户名] -p -t [要备份的数据库名称]>[备份文件的保存路径]

    4)导出数据库中的Events

    格式:

        mysqldump -u [数据库用户名] -p -E [数据库用户名]>[备份文件的保存路径]

    5)导出数据库中的存储过程和函数

    格式:
        mysqldump -u [数据库用户名] -p -R [数据库用户名]>[备份文件的保存路径]

(2)从外部文件导入数据库中

    1)使用“source”命令

        首先进入“mysql”命令控制台,然后创建数据库,然后使用该数据库。最后执行下面操作。

            mysql>source [备份文件的保存路径]

    2)使用“<”符号

        首先进入“mysql”命令控制台,然后创建数据库,然后退出 MySQL,进入 DOS 界面。最后执行下面操作。

            mysql -u root –p < [备份文件的保存路径]

6、备份数据库：
注意，mysqldump命令在DOS的 mysql\bin 目录下执行,不能在mysql环境下执行，因此，不能以分号“;”结尾。若已登陆mysql，请运行退出命令mysql> exit

(1)导出整个数据库

    导出文件默认是存在mysql\bin目录下

        mysqldump -u用户名 -p数据库名 > 导出的文件名

        mysqldump -uroot -p123456 database_name > outfile_name.sql

(2)导出一个表

    mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

    mysqldump -u user_name -p database_name table_name > outfile_name.sql

(3)导出一个数据库结构

    mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql

    -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

(4)带语言参数导出

    mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql

7.将文本数据转到数据库中

(1)文本数据应符合的格式：字段数据之间用tab键隔开，null值用\n来代替.例：

    3 rose 大连二中 1976-10-10

    4 mike 大连一中 1975-12-23

假设你把这两组数据存为school.txt文件，放在c盘根目录下。

(2)数据传入命令

    mysql> load data local infile "c:\school.txt" into table 表名;

注意：你最好将文件复制到mysql\bin目录下，并且要先用use命令打表所在的库。

三、对表的操作

1、显示数据表的结构：

mysql> DESCRIBE 表名; （DESC 表名）

2、建立数据表：

mysql> USE 库名; //进入数据库

mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));

3、删除数据表：

mysql> DROP TABLE 表名；

4、重命名数据表

alter table t1 rename t2;

5、显示表中的记录：

mysql> SELECT * FROM 表名;

6、往表中插入记录：

mysql> INSERT INTO 表名 VALUES (”hyq”,”M”);

7、更新表中数据：

mysql-> UPDATE 表名 SET 字段名1=’a',字段名2=’b’ WHERE 字段名3=’c';

8、将表中记录清空：

mysql> DELETE FROM 表名;

9、用文本方式将数据装入数据表中：

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE 表名;

10、 显示表的定义，还可以看到表的约束，例如外键

mysql> SHOW CREATE TABLE yourtablename ;

还可以通过 mysqldump 将表的完整定义转储到文件中，当然包括外键定义。

还可以通过下面的指令列出表 T 的外键约束：

mysql> SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'

外键约束将会在表注释中列出。

四、存储过程

1、创建存储过程：

CREATE PROCEDURE procedureName (in paramentName type, in paramentName type,……)

BEGIN

SQL sentences;

END

2、调用存储过程：

mysql> CALL procedureName(paramentList);
例：mysql> CALL addMoney(12, 500);

3、查看特定数据库的存储过程：

方法一：mysql> SELECT `name` FROM mysql.proc WHERE db = 'your_db_name' AND `type` = 'PROCEDURE';

方法二：mysql> show procedure status;

4、删除存储过程：

mysql> DROP PROCEDURE procedure_name;

mysql> DROP PROCEDURE IF EXISTS procedure_name;

5、查看指定的存储过程定义：

mysql> SHOW CREATE PROCEDURE proc_name;

mysql> SHOW CREATE FUNCTION func_name;

---------- 示例一-----------

mysql> DELIMITER $$

mysql> USE `db_name`$$ //选择数据库

mysql> DROP PROCEDURE IF EXISTS `addMoney`$$ //如果存在同名存储过程，则删除之

mysql> CREATE DEFINER= `root`@`localhost` PROCEDURE `addMoney`(IN xid INT(5),IN xmoney INT(6))

mysql> BEGIN

mysql> UPDATE USER u SET u.money = u.money + xmoney WHERE u.id = xid; //分号";"不会导致语句执行，因为当前的分割符被定义为$$

mysql> END$$ //终止

mysql> DELIMITER ; //把分割符改回分号";"
mysql> call addMoney(5,1000); //执行存储过程
---------- 示例二-----------

mysql> delimiter //

mysql> create procedure proc_name (in parameter integer)

mysql> begin

mysql> if parameter=0 then

mysql> select * from user order by id asc;

mysql> else

mysql> select * from user order by id desc;

mysql> end if;

mysql> end;

mysql> // //此处“//”为终止符

mysql> delimiter ;

mysql> show warnings;

mysql> call proc_name(1);

mysql> call proc_name(0);

五、修改表的列属性的操作

1、为了改变列a，从INTEGER改为TINYINT NOT NULL(名字一样)，

并且改变列b，从CHAR(10)改为CHAR(20)，同时重命名它，从b改为c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

2、增加一个新TIMESTAMP列，名为d：

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

3、在列d上增加一个索引，并且使列a为主键：

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

4、删除列c：

mysql> ALTER TABLE t2 DROP COLUMN c;

5、增加一个新的AUTO_INCREMENT整数列，命名为c：

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);

注意，我们索引了c，因为AUTO_INCREMENT柱必须被索引，并且另外我们声明c为NOT NULL，

因为索引了的列不能是NULL

六、一个建库和建表以及插入数据的实例

drop database if exists school; //如果存在SCHOOL则删除

create database school; //建立库SCHOOL

use school; //打开库SCHOOL

create table teacher //建立表TEACHER

(

id int(3) auto_increment not null primary key,

name char(10) not null,

address varchar(50) default ‘深圳’,

year date

); //建表结束

//以下为插入字段

insert into teacher values('','allen','大连一中','1976-10-10');

insert into teacher values('','jack','大连二中','1975-12-23');

如果你在mysql提示符键入上面的命令也可以，但不方便调试。

（1）你可以将以上命令原样写入一个文本文件中，假设为school.sql，然后复制到c:\下，并在DOS状态进入目录\mysql\bin，然后键入以下命令：

mysql -uroot -p密码 < c:\school.sql

如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。

（2）或者进入命令行后使用 mysql> source c:\school.sql; 也可以将school.sql文件导入数据库中。

七、语法

　　１.MySQL 为关系型数据库(Relational Database Management System), 这种所谓的”关系型”可以理解为”表格”的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:

\
表头(header): 每一列的名称;
列(row): 具有相同数据类型的数据的集合;
行(col): 每一行用来描述某个人/物的具体信息;
值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
键(key): 表中用来识别某个特定的人\物的方法, 键的值在当前列中具有唯一性。

　　2.MySQL脚本的基本组成：

与常规的脚本语言类似, MySQL 也具有一套对字符、单词以及特殊符号的使用规定, MySQL 通过执行 SQL 脚本来完成对数据库的操作, 该脚本由一条或多条MySQL语句(SQL语句 + 扩展语句)组成, 保存时脚本文件后缀名一般为 .sql。在控制台下, MySQL 客户端也可以对语句进行单句的执行而不用保存为.sql文件。

　　3.标识符：

标识符用来命名一些对象, 如数据库、表、列、变量等, 以便在脚本中的其他地方引用。MySQL标识符命名规则稍微有点繁琐, 这里我们使用万能命名规则: 标识符由字母、数字或下划线(_)组成, 且第一个字符必须是字母或下划线。
对于标识符是否区分大小写取决于当前的操作系统, Windows下是不敏感的, 但对于大多数 linux\unix 系统来说, 这些标识符大小写是敏感的。

　　４.MySQL中的数据类型:

MySQL有三大类数据类型, 分别为数字、日期\时间、字符串, 这三大类中又更细致的划分了许多子类型:

数字类型
    整数: tinyint、smallint、mediumint、int、bigint
    浮点数: float、double、real、decimal
    日期和时间: date、time、datetime、timestamp、year

字符串类型
    字符串: char、varchar
    文本: tinytext、text、mediumtext、longtext

    二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

详细介绍参见: 《MySQL数据类型》 : http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html