MySQL经常使用指令（备查）

最经常使用的显示命令：

连接MySQL

格式: mysql -h 主机地址 -uusername -p用户password

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

         mysql -uroot -pmysql;

     连接到远程主机上的 MYSQL。

         mysql -h 127.0.0.1 -uroot -pmysql;

2、连接到远程主机上的MYSQL。如果远程主机的IP为：110.110.110.110，username为root,password为abcd123。则键入下面命令：

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

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

改动新password

在终端输入：mysql -uusername -ppassword，回车进入Mysql。

> use mysql;

> update user set password=PASSWORD('新密码') where user='username';

> flush privileges; #更新权限

> quit; #退出

二、改动password。

格式：mysqladmin -uusername -p旧password password 新password

1、给root加个passwordab12。首先在DOS下进入文件夹mysql\bin，然后键入下面命令

mysqladmin -u root -password ab12

注：由于開始时root没有password，所以-p旧password一项就能够省略了。

2、再将root的password改为djg345。

mysqladmin -u root -p ab12 password djg345

（注意：和上面不同，以下的由于是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符）

3、命令行改动rootpassword：

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

mysql> FLUSH PRIVILEGES;

4、显示当前的user：

mysql> SELECT USER();

格式：grant select on 数据库.* to username@登录主机 identified by “password”

1、添加一个用户test1password为abc，让他能够在不论什么主机上登录，并对全部数据库有查询、插入、改动、删除的权限。首先用root用户连入

MYSQL，然后键入下面命令：

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

但添加的用户是十分危急的，你想如某个人知道test1的password，那么他就能够在internet上的不论什么一台电脑上登录你的mysql数据库并对你的数据能够为所欲为了，解决的方法见2。

2、添加一个用户test2password为abc,让他仅仅能够在localhost上登录，并能够对数据库mydb进行查询、插入、改动、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），

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

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

假设你不想test2有password，能够再打一个命令将password消掉。

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

删除用户

mysql -uusername -ppassword

mysql>delete from user where user='username' and host='localhost';

mysql>flush privileges;

//删除用户的数据库

mysql>drop database dbname;



数据库操作

显示全部的数据库

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



创建数据库

mysql> create database test;



连接数据库

mysql> use test;



查看当前使用的数据库

mysql> select database();



当前数据库包括的表信息

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



删除数据库

mysql> drop database test;



表操作

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

建表

命令: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));



获取表结构

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

样例：

mysql> describe MyClass

mysql> desc MyClass;

mysql> show columns from MyClass;



删除表

命令:drop table <表名>

比如:删除表名为 MyClass 的表

mysql> drop table MyClass;



插入数据

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

样例：

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



查询表中的数据

查询全部行

mysql> select * from MyClass;



查询前几行数据

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

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

或者

mysql> select * from MyClass limit 0,2;



删除表中数据

命令:delete from 表名 where 表达式

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

mysql> delete from MyClass where id=1;



改动表中数据

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

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



在表中添加字段

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

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

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



更改表名

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

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

mysql> rename table MyClass to YouClass;



更新字段内容

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

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

比如:文章前面增加 4 个空格

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



数据库导入导出

从数据库导出数据库文件

使用“mysqldump”命令

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

1)导出全部数据库

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



2)导出数据和数据结构

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

举例:

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

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

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

然后输入password,等待一会导出就成功了,能够到目标文件里检查是否成功。



例 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 [数据库username] -p -t [要备份的数据库名称]>[备份文件的保存路径]



4)导出数据库中的Events

格式:mysqldump -u [数据库username] -p -E [数据库username]>[备份文件的保存路径]



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

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



从外部文件导入数据库中

1)使用“source”命令

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

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



2)使用“<”符号

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

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

六、备份数据库：

注意，mysqldump命令在DOS的 mysql\bin 文件夹下运行,不能在mysql环境下运行，因此，不能以分号“;”结尾。若已登陆mysql，请运行退出命令mysql>
exit

1.导出整个数据库

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

mysqldump -uusername -p数据库名 > 导出的文件名称

mysqldump -uroot -p123456 database_name > outfile_name.sql

2.导出一个表

mysqldump -u username -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

七、将文本数据转到数据库中

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'   

外键约束将会在表凝视中列出。

存储过程

11、创建存储过程

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

BEGIN

SQL sentences;

END

12、调用存储过程

mysql> CALL procedureName(paramentList);

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

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

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

方法二：mysql> show procedure status;

14、删除存储过程

mysql> DROP PROCEDURE procedure_name;

mysql> DROP PROCEDURE IF EXISTS procedure_name;

15、查看指定的存储过程定义

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 -ppassword < c:\school.sql

假设成功，空出一行无不论什么显示；如有错误，会有提示。（以上命令已经调试，你仅仅要将//的凝视去掉就可以使用）。

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

MySQL 为关系型数据库(Relational Database Management System), 这样的所谓的"关系型"能够理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所看到的的一个表格:

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

MySQL脚本的基本组成

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

标识符

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

使用MySQL数据库

登录到MySQL

创建一个数据库

选择所要操作的数据库

创建数据库表

	create table students

	（

		id int unsigned not null auto_increment primary key,

		name char(8) not null,

		sex char(4) not null,

		age tinyint unsigned not null,

		tel char(13) null default "-"

	);

操作MySQL数据库

向表中插入数据

查询表中的数据

	mysql> select name, age from students;

	+--------+-----+

	| name   | age |

	+--------+-----+

	| 王刚   |  20 |

	| 孙丽华 |  21 |

	| 王永恒 |  23 |

	| 郑俊杰 |  19 |

	| 陈芳   |  22 |

	| 张伟朋 |  21 |

	+--------+-----+

	6 rows in set (0.00 sec)

	mysql>

更新表中的数据

删除表中的数据

创建后表的改动

alter table 语句用于创建后对表的改动, 基础使用方法例如以下:

加入列

改动列

删除列

重命名表

删除整张表

删除整个数据库

附录

改动 root 用户password

可视化管理工具 MySQL Workbench