一、MySQL介绍

MySQL是一个关系型数据库管理系统，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。现在主要介绍常用的MySQL命令，包括连接数据库，修改密码，管理用户，操作数据库，操作数据表，数据库备份等。

W3School ---SQL 教程：http://www.w3school.com.cn/sql/index.asp

菜鸟教程 ---SQL 教程：http://www.runoob.com/sql/sql-tutorial.html

1.1 几个重要目录

MySQL安装完成后不象SQL Server默认安装在一个目录，它的数据库文件、配置文件和命令文件分别在不同的目录，了解这些目录非常重要，尤其对于Linux的初学者，因为Linux本身的目录结构就比较复杂，如果搞不清楚MySQL的安装目录那就无从谈起深入学习。

1、数据库目录：/var/lib/mysql/

2、配置文件： /usr/share/mysql(mysql.server命令及配置文件)

3、相关命令： /usr/bin(mysqladmin mysqldump等命令)

4、启动脚本: /etc/rc.d/init.d/(启动脚本文件mysql的目录)

二软件管理命令

2.1 连接到Mysql

 mysql -h主机地址 -u用户名 －p用户密码

2.1.1、连接到本机上的MYSQL

首先打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是： mysql>

2.1.2、连接到远程主机上的MYSQL

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

mysql -h110.110.110.110 -u root -p abcd123
##:u与root之间可以不用加空格，其它也一样

2.1.3、退出MYSQL

命令： exit (回车)

mysql> exit;

2.2 修改用户密码

mysqladmin -u用户名 -p旧密码 password 新密码

2.2.1 给root设置密码

首先在DOS下进入目录mysql\bin，然后键入以下命令:

mysqladmin -u root -password ab12           //设置 root 密码为 ab12

因为开始时root没有密码，所以-p旧密码一项就可以省略了。

2.2.2、修改root密码

mysqladmin -u root -p ab12 password djg345       // 修改 root 密码为 djg345

2.3 数据库权限

2.3.1 权限的种类：

MySQL中总共有28个权限：

以上权限大致分为3类:

第1类：结构管理类。如：alter、create、delete、drop、index、insert、select、update；

第2类：数据管理类，它们允许用户影响服务器的操作需严格地授权。如：file、process、reload、shut*；

第3类：特殊权限，all 意味着 “所有权限” ，uasge 意味着无权限，即创建用户，但不授予权限；

权限的级别和说明：

权限赋予的建议：

2.3.2 查看权限

查看当前用户(自己)权限：show grants;

查看其他 MySQL 用户权限：show grants for dba@localhost;

2.3.3 撤销已经赋予给 MySQL 用户权限的权限

grant all on *.* to dba@localhost;
或
revoke all on *.* from dba@localhost;

2.3.4 用户权限注意事项

1. grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

2. 如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“

grant select on testdb.* to dba@localhost with grant option;

这个特性一般用不到。实际中，数据库权限最好由 DBA 来统一管理。

三、日常管理命令

3.1 grant on：创建新用户并设置权限

语法格式：

grant 权限 on 数据库.表 to 用户名@登录主机 identified by "密码"；

3.1.1 注意事项：

1、设置权限时必须给出一下信息：

● 要授予的权限

● 被授予访问权限的数据库或表

● 用户名

2、grant 和 revoke 可以在几个层次上控制访问权限：

● 整个服务器，使用 grant ALL 和revoke ALL

● 整个数据库，使用on database.*

● 特定表，使用on database.table

● 特定的列

● 特定的存储过程

3、user 表中 host 列的值的意义：

● % 匹配所有主机

● localhost localhost不会被解析成IP地址，直接通过UNIXsocket连接

● 127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问；

● ::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

3.1.2 实例

实例1：新增用户

增加一个test1用户，密码为123456，可以在任何主机上登录，并对所有数据库有查询，增加，修改和删除的功能。需要在mysql的root用户下进行。

mysql>grant select,insert,update,delete on *.* to test1@"%" identified by "123456"；
mysql>flush privileges;

实例2：限制登录IP

增加一个test2用户，密码为123456，只能在192.168.2.12上登录，并对数据库student有查询，增加，修改和删除的功能。需要在mysql的root用户下进行。

mysql>grant select,insert,update,delete on student.* to test2@192.168.2.12 identified by "123456";
mysql>flush privileges;

实例3：用户授权

授权用户test3拥有数据库student的所有权限；

mysql>grant all privileges on student.* to test3@localhost identified by '123456';
mysql>flush privileges;

实例4：修改用户密码

修改test3账户的密码为空；

mysql>grant all privileges on student.* to test3@localhost identified by '';
mysql>flush privileges;

实例5：删除用户

mysql>delete from user where user='test2' and host='localhost';
mysql>flush privileges;

实例6：删除用户及权限

drop user 用户名@'%'
drop user 用户名@localhost

3.2 create：创建数据库

语法格式：

create database <数据库名>         //创建数据库之前要先连接Mysql服务器

实例1：建立一个名为xhkdb的数据库

mysql> create database xhkdb;

实例2：创建数据库并分配用户

create database 数据库名;

grant select,insert,update,delete,create,drop,alter on 数据库名.* to 数据库名@localhost identified by '密码';

set password for '数据库名'@'localhost' = old_password('密码');

3.3 show databases：显示数据库

语法格式

mysql> show databases;                    // 注意：最后有个s

为了不再显示的时候乱码，要修改数据库默认编码。以下以GBK编码页面为例进行说明：

1、修改MYSQL的配置文件：my.ini里面修改default-character-set=gbk

2、代码运行时修改：

①Java代码：jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk

②PHP代码：header("Content-Type:text/html;charset=gb2312");

③C语言代码：int mysql_set_character_set( MYSQL mysql, char csname)；

该函数用于为当前连接设置默认的字符集。字符串csname指定了1个有效的字符集名称。连接校对成为字符集的默认校对。该函数的工作方式与SET NAMES语句类似，但它还能设置mysql- > charset的值，从而影响了由mysql_real_escape_string() 设置的字符集。

3.4 drop database：删除数据库

语法格式：

drop database <数据库名>

实例1：删除名为xhkdb的数据库

mysql> drop database xhkdb;

实例2：删除一个不确定存在的数据库

mysql> drop database if exists drop_database;

3.5 use：使用数据库

语法格式

use <数据库名>

USE语句被设立出来，用于与Sybase相兼容。use 语句可以通告MySQL把db_name数据库作为默认(当前)数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的USE语句；使用USE语句为一个特定的当前的数据库做标记，不会阻碍您访问其它数据库中的表。

3.6 select：当前连接的数据库

语法格式：

select 函数|字符串|逻辑算法|字符拼接;

SELECT命令类似于其他编程语言里的print或者write，你可以用它来显示一个字符串、数字、数学表达式的结果等等。

实例：

1.显示MYSQL的版本
mysql> select version(); 

2. 显示当前时间
mysql> select now(); 

3. 显示年月日
SELECT DAYOFMONTH(CURRENT_DATE); 
SELECT MONTH(CURRENT_DATE); 
SELECT YEAR(CURRENT_DATE); 

4. 显示字符串
mysql> SELECT "welecome to my blog!"; 

5. 当计算器用
select ((4 * 4) / 10 ) + 25; 

6. 拼接字符串
select CONCAT(f_name, " ", l_name) 
AS Name 
from employee_data 
where title = 'Marketing Executive';

3.7 create table：创建表

语法格式：

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

实例：建立一个名为MyClass的表，表内的字段和限制如下：

字段名	数字类型	数据宽度	是否为空	是否主键	自动增加	默认值
id	int	4	否	primary key	auto_increment
name	char	20	否
sex	int	4	否			0
degree	double	16	是

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));

3.8 desc：获取表结构

语法格式：

desc 表名;
作用同  
show columns from 表名;

使用 MySQL 数据库 desc 表名时，我们看到 Key 那一栏，可能会有4种值，即 ' '，'PRI'，'UNI'，'MUL'。

如果 Key 是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列；

如果 Key 是 PRI, 那么该列是主键的组成部分；

如果 Key 是 UNI, 那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)；

如果 Key 是 MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。

如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI,又是UNI，那么"desc 表名"的时候，显示的Key值按照优先级来显，PRI->UNI->MUL。那么此时，显示PRI。

一个唯一性索引列可以显示为PRI,并且该列不能含有空值，同时该表没有主键。

一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引，因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是没一个单独的列依然可以有重复的值，只要ID+NAME是唯一的即可。

3.9 drop table：删除表

语法格式：

drop table <表名>

drop table 用于取消一个或多个表。您必须有每个表的 drop 权限。所有的表数据和表定义会被取消，所以使用本语句要小心！

注意：对于一个带分区的表，drop table 会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据。drop table 还会取消与被取消的表有关联的分区定义(.par)文件。

对与不存在的表，使用IF EXISTS用于防止错误发生。当使用IF EXISTS时，对于每个不存在的表，会生成一个NOTE。

RESTRICT和CASCADE可以使分区更容易。目前，RESTRICT和CASCADE不起作用。

3.10 insert into：表插入数据

语法格式

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

实例：往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99，编号为3 的名为Wang 的成绩为96.5。

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

insert into 语句的三种写法

方式 1、 INSERT INTO t1(field1,field2) VALUE(v001,v002); // 明确只插入一条Value

方式 2、 INSERT INTO t1(field1,field2) VALUES(v101,v102),(v201,v202),(v301,v302),(v401,v402); // 在插入批量数据时方式2优于方式1.

方式 3.1、 INSERT INTO t2(field1,field2) SELECT col1,col2 FROM t1 WHERE

这里简单说一下，由于可以指定插入到talbe2中的列，以及可以通过相对较复杂的查询语句进行数据源获取，可能使用起来会更加的灵活一些，但我们也必须注意，我们在指定目标表的列时，一定要将所有非空列都填上，否则将无法进行数据插入，还有一点比较容易出错的地方就是，当我们写成如下简写格式：

方式 3.2、 INSERT INTO t2 SELECT id, name, address FROM t1

此时，我们如果略掉了目标表的列的话，则默认会对目标表的全部列进行数据插入，且SELECT后面的列的顺序必须和目标表中的列的定义顺序完全一致才能完成正确的数据插入，这是一个很容易被忽略的地方，值得注意。

3.11 select from：查询表中的数据

语法格式：

select <字段1，字段2，...> from < 表名 > where < 表达式 >

实例：

mysql> select * from MyClass;            //查看表 MyClass 中所有数据
mysql> select * from MyClass order by id limit 0,2;  //查看前2行数据

select一般配合where使用，以查询更精确更复杂的数据。

3.12 delete from：删除表中数据

格式语法：

delete from 表名 where 表达式

实例：

mysql> delete from MyClass where id=1;    //删除表 MyClass中编号为1 的记录

3.13 update set：修改表中数据

语法格式：

update 表名 set 字段=新值,… where 条件

实例：

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

3.14 alter table：在已有的表中添加、修改或删除列

语法格式

ALTER TABLE table_name DROP COLUMN column_name

实例1：加索引

语法格式： alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

mysql> alter table employee add index emp_name (name);

实例2：加主关键字的索引

语法格式：alter table 表名 add primary key (字段名);

mysql> alter table employee add primary key(id)

实例3：加唯一限制条件的索引

语法格式：alter table 表名 add unique 索引名 (字段名);

mysql> alter table employee add unique emp_name2(cardnumber);

实例4：删除某个索引

语法格式： alter table 表名 drop index 索引名;

mysql>alter table employee drop index emp_name;

实例5：增加字段：

语法格式：alter table table_name add field_name field_type 其他;

mysql> alter table MyClass add passtest int(4) default '0'
##添加了一个字段passtest，类型为int(4)，默认值为0

实例6：修改原字段名称及类型：

语法格式： ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

实例7：删除字段：

语法格式：ALTER TABLE table_name DROP field_name;

3.15 rename：修改表名

语法格式：

rename table 原表名 to 新表名;

实例：

mysql> rename table MyClass to YouClass;
##在表MyClass名字更改为YouClass

当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。

如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。

RENAME TABLE 在 MySQL 3.23.23 中被加入。

3.16 mysqldump：备份数据库

1 导出整个数据库

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

mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u user_name -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

也可以在SHELL中导出：

[root@test1 root]# cd /home/data/mysql
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

四、其他命令

show processlist;                 //列出每一笔联机的信息。
show varlables;                   //列出mysql的系统设定。
show tables from db_name;      //列出db_name中所有数据表;
show [full] columns from table_name;    //列出table_name中完整信息，如栏名、类型,包括字符集编码。
show index from table_name;                //列出table_name中所有的索引。
show table status;；                       // 列出当前数据库中数据表的信息。
show table status from db_name;        //列出当前db_name中数据表的信息。
alter table table_name engine innodb|myisam|memory ;    //更改表类型
explain table_name / describe table_name ;     // 列出table_name完整信息，如栏名、类型。
show create table table_name             //显示当前表的建表语句
alter table table_name add primary key (picid) ;     //向表中增加一个主键
alter table table_name add column userid int after picid     //修改表结构增加一个新的字段
alter table table_name character set gb2312    //改变表的编码
select user();     //显示当前用户。
select password(’root’);      // 显示当前用户密码
select now();     //显示当前日期
flush privileges //在不重启的情况下刷新用户权限
mysqld –default-character-set=gb2312 ;    //设置默认字符集为gb2312

分享

MySQL命令大全

一、MySQL介绍

1.1 几个重要目录

二 软件管理命令

2.1 连接到Mysql

2.1.1、连接到本机上的MYSQL

2.1.2、连接到远程主机上的MYSQL

2.1.3、退出MYSQL

2.2 修改用户密码

2.2.1 给root设置密码

2.2.2、修改root密码

2.3 数据库权限

2.3.1 权限的种类：

2.3.2 查看权限

2.3.3 撤销已经赋予给 MySQL 用户权限的权限

2.3.4 用户权限注意事项

三、日常管理命令

3.1 grant on：创建新用户并设置权限

3.2 create：创建数据库

3.3 show databases：显示数据库

3.4 drop database：删除数据库

3.5 use：使用数据库

3.6 select：当前连接的数据库

3.7 create table：创建表

3.8 desc：获取表结构

3.9 drop table：删除表

3.10 insert into：表插入数据

3.11 select from：查询表中的数据

3.12 delete from：删除表中数据

3.13 update set：修改表中数据

3.14 alter table：在已有的表中添加、修改或删除列

3.15 rename：修改表名

3.16 mysqldump：备份数据库

1 导出整个数据库

2.导出一个表

3.导出一个数据库结构

4.带语言参数导出

四、其他命令

评论

二软件管理命令