Windows7下MySQL5.5.20免安装版的配置

Windows7下MySQL5.5.20免安装版的配置

• 博客分类：
• MySQL

MySQL Windows安装包说明：
1、mysql-5.5.20-win32.msi:Windows 安装包，图形化的下一步下一步的安装。
2、mysql-5.5.20.zip，这个是windows源文件，需要编译，对应的Linux源文件是mysql-5.5.20.tar.gz
3、mysql-5.5.20-win32.zip，这个文件解包后即可使用，是编译好的windows32位Mysql。

1、下载mysql-5.5.20-win32.zip，解压到D:\dev，D盘的dev文件夹下就会出现mysql-5.5.20-win32目录，将其重命名为mysql。

2、配置MYSQL的环境变量
新增系统变量MYSQL_HOME： D:\dev\mysql
在PATH变量的最后面添加： ;%MYSQL_HOME%\bin
保存即可。

3、打开文件my-default.ini另存为my.ini，删除my.ini中的所有配置，在my.ini文件中加入如下简单配置：(my.ini是保存在与my-default.ini同一个目录下的）(#表示注释）

Mysql代码

1. # The following options will be passed to all MySQL clients 
2. [client] 
3. #password   = your_password 
4. port        = 3306 
5.  
6. [mysql] 
7. #设置mysql客户端的字符集 
8. default-character-set = utf8 
9.  
10. # The MySQL server 
11. [mysqld] 
12. port        = 3306 
13. #设置mysql的安装目录 
14. basedir = D:\dev\mysql 
15. #设置mysql数据库的数据存放目录,必须是data或者\xxx-data 
16. datadir = D:\dev\mysql\data 
17. #设置服务器段的字符集 
18. character_set_server = utf8 

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306

[mysql]
#设置mysql客户端的字符集
default-character-set = utf8

# The MySQL server
[mysqld]
port		= 3306
#设置mysql的安装目录
basedir = D:\dev\mysql
#设置mysql数据库的数据存放目录,必须是data或者\xxx-data
datadir = D:\dev\mysql\data
#设置服务器段的字符集
character_set_server = utf8

4、注册服务
开始菜单，搜索cmd，单击右键 “以管理员身份运行”，输入命令：

Mysql代码

1. mysqld --install mysql5 --defaults-file=d:\dev\mysql\my.ini 

mysqld --install mysql5 --defaults-file=d:\dev\mysql\my.ini

（如果此时“出现Install/Remove of the Service Denied!”的错误，说明cmd不是以管理员身份运行）
或着，
开始菜单，搜索cmd，单击右键“以管理员身份运行”，输入命令：

Mysql代码

1. mysqld --install mysql5 

mysqld --install mysql5

删除服务(开始菜单，搜索cmd，单击右键“以管理员身份运行”)：

Mysql代码

1. sc delete mysql5 

sc delete mysql5

在“服务”中就会出现mysql这一项。

5、启动服务(开始菜单，搜索cmd，单击右键 “以管理员身份运行”)：

Mysql代码

1. net start mysql5 

net start mysql5

停止服务：

Mysql代码

1. net stop mysql5 

net stop mysql5

6、服务启动后：
登录MySQL服务器：
命令格式：

Mysql代码

1. mysql -h hostname -u username -p 

mysql -h hostname -u username -p

或

Mysql代码

1. mysql -hhostname -uusername -p 

mysql -hhostname -uusername -p

命令说明：mysql命令将调用MySQL监视程序，这是一个可以将我们连接到MySQL服务器端的客户端命令行工具。
选项说明：
-h选项：用于指定所希望连接的主机，即运行MySQL服务器的机器。如果在运行MySQL服务器的机器上运行该命令，则可以忽略该选项和hostname参数；如果不是，必须用运行MySQL服务器的主机名称来代替主机名称参数。
-u命令：用于指定连接数据库时使用的用户名称。
-p命令：用于指定用户输入的密码

此时我本机安装了MYSQL，可忽略该选项和hostname参数：

Mysql代码

1. mysql -uroot -p 

mysql -uroot -p

注：
    MySQL的管理员用户名为root，密码默认为空

修改root密码
MySQL配置好后，启动成功，默认密码是空，但是为了安全，设置密码（MySQL有一个默认用户名为root，密码自己设定：假如设为root）。
1）登录MySQL root用户：
   打开命令行，执行：

Mysql代码

1. mysql -uroot -p 

mysql -uroot -p

2）修改root密码：
  

Mysql代码

1. mysql> update mysql.user set password="root" where User="root"; 
2. mysql> flush privileges; 

   mysql> update mysql.user set password="root" where User="root";
   mysql> flush privileges;
   

修改该修改密码的语句：update mysql.user set password="root" where User="root";
为： update mysql.user set password=password("root") where User="root";

详细说明：见最底下的补充说明。

以后再进入MySQL，则为：

Mysql代码

1. mysql -uroot -proot 

mysql -uroot -proot

7、常用命令：

Mysql代码

1. create database new_dbname;--新建数据库 
2. show databases;--显示数据库 
3. use databasename;--使用数据库 
4. select database();--查看已选择的数据库 
5.  
6. show tables;--显示当前库的所有表 
7. create table tablename(fieldname1 fieldtype1,fieldname2 fieldtype2,..)[ENGINE=engine_name];--创建表 
8. create table tablename select statement;--通过子查询创建表 
9. desc tablename;--查看表结构 
10. show create table tablename;--查看建表语句 
11.  
12. alter table tablename add new_fielname new_fieldtype;--新增列 
13. alter table tablename add new_fielname new_fieldtype after 列名1;--在列名1后新增列 
14. alter table tablename modify fieldname new_fieldtype;--修改列 
15. alter table tablename drop fieldname;--删除列 
16. alter table tablename_old rename tablename_new;--表重命名 
17.  
18. insert into tablename(fieldname1,fieldname2,fieldnamen) valuse(value1,value2,valuen);--增 
19. delete from tablename [where fieldname=value];--删 
20. update tablename set fieldname1=new_value where filename2=value;--改 
21. select * from tablename [where filename=value];--查 
22.  
23. truncate table tablename;--清空表中所有数据，DDL语句 
24.  
25. show engines;--查看mysql现在已提供的存储引擎: 
26. show variables like '%storage_engine%';--查看mysql当前默认的存储引擎 
27. show create table tablename;--查看某张表用的存储引擎（结果的"ENGINE="部分） 
28. alter table tablename ENGINE=InnoDB--修改引擎 
29. create table tablename(fieldname1 fieldtype1,fieldname2 fieldtype2,..) ENGINE=engine_name;--创建表时设置存储引擎 

create database new_dbname;--新建数据库
show databases;--显示数据库
use databasename;--使用数据库
select database();--查看已选择的数据库

show tables;--显示当前库的所有表
create table tablename(fieldname1 fieldtype1,fieldname2 fieldtype2,..)[ENGINE=engine_name];--创建表
create table tablename select statement;--通过子查询创建表
desc tablename;--查看表结构
show create table tablename;--查看建表语句

alter table tablename add new_fielname new_fieldtype;--新增列
alter table tablename add new_fielname new_fieldtype after 列名1;--在列名1后新增列
alter table tablename modify fieldname new_fieldtype;--修改列
alter table tablename drop fieldname;--删除列
alter table tablename_old rename tablename_new;--表重命名

insert into tablename(fieldname1,fieldname2,fieldnamen) valuse(value1,value2,valuen);--增
delete from tablename [where fieldname=value];--删
update tablename set fieldname1=new_value where filename2=value;--改
select * from tablename [where filename=value];--查

truncate table tablename;--清空表中所有数据，DDL语句

show engines;--查看mysql现在已提供的存储引擎:
show variables like '%storage_engine%';--查看mysql当前默认的存储引擎
show create table tablename;--查看某张表用的存储引擎（结果的"ENGINE="部分）
alter table tablename ENGINE=InnoDB--修改引擎
create table tablename(fieldname1 fieldtype1,fieldname2 fieldtype2,..) ENGINE=engine_name;--创建表时设置存储引擎

8、例如：
（1）登录MySQL服务器后，查看当前时间，登录的用户以及数据库的版本

Mysql代码

1. mysql> select now(),user(),version(); 
2. +---------------------+----------------+-----------+ 
3. | now()               | user()         | version() | 
4. +---------------------+----------------+-----------+ 
5. | 2012-02-26 20:29:51 | root@localhost | 5.5.20    | 
6. +---------------------+----------------+-----------+ 
7. 1 row in set (0.00 sec) 

mysql> select now(),user(),version();
+---------------------+----------------+-----------+
| now()               | user()         | version() |
+---------------------+----------------+-----------+
| 2012-02-26 20:29:51 | root@localhost | 5.5.20    |
+---------------------+----------------+-----------+
1 row in set (0.00 sec)

（2）显示数据库列表

Mysql代码

1. mysql> show databases; 
2. +--------------------+ 
3. | Database           | 
4. +--------------------+ 
5. | information_schema | 
6. | mysql              | 
7. | performance_schema | 
8. | test               | 
9. +--------------------+ 
10. 4 rows in set (0.03 sec) 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.03 sec)

（3）新增数据库并查看

Mysql代码

1. mysql> create database test_db; 
2. Query OK, 1 row affected (0.00 sec) 
3.  
4. mysql> show databases; 
5. +--------------------+ 
6. | Database           | 
7. +--------------------+ 
8. | information_schema | 
9. | mysql              | 
10. | performance_schema | 
11. | test               | 
12. | test_db            | 
13. +--------------------+ 
14. 5 rows in set (0.00 sec) 

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

（4）选择数据库

Mysql代码

1. mysql> use test_db; 
2. Database changed 

mysql> use test_db;
Database changed

查看已选择的数据库：

Mysql代码

1. mysql> select database(); 
2. +------------+ 
3. | database() | 
4. +------------+ 
5. | test_db    | 
6. +------------+ 
7. 1 row in set (0.00 sec) 

mysql> select database();
+------------+
| database() |
+------------+
| test_db    |
+------------+
1 row in set (0.00 sec)

（5）显示当前数据库的所有数据表

Mysql代码

1. mysql> show tables; 
2. Empty set (0.00 sec) 

mysql> show tables;
Empty set (0.00 sec)

（6）新建数据表并查看

Mysql代码

1. mysql> create table person( 
2.     -> id int, 
3.     -> name varchar(20), 
4.     -> sex char(1), 
5.     -> birth date 
6.     -> ); 
7. Query OK, 0 rows affected (0.09 sec) 

mysql> create table person(
    -> id int,
    -> name varchar(20),
    -> sex char(1),
    -> birth date
    -> );
Query OK, 0 rows affected (0.09 sec)

Mysql代码

1. mysql> show tables; 
2. +-------------------+ 
3. | Tables_in_test_db | 
4. +-------------------+ 
5. | person            | 
6. +-------------------+ 
7. 1 row in set (0.00 sec) 

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| person            |
+-------------------+
1 row in set (0.00 sec)

（7）获取表结构

Mysql代码

1. mysql> desc person; 
2. +-------+-------------+------+-----+---------+-------+ 
3. | Field | Type        | Null | Key | Default | Extra | 
4. +-------+-------------+------+-----+---------+-------+ 
5. | id    | int(11)     | YES  |     | NULL    |       | 
6. | name  | varchar(20) | YES  |     | NULL    |       | 
7. | sex   | char(1)     | YES  |     | NULL    |       | 
8. | birth | date        | YES  |     | NULL    |       | 
9. +-------+-------------+------+-----+---------+-------+ 
10. 4 rows in set (0.01 sec) 

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

或者

Mysql代码

1. mysql> describe person; 
2. +-------+-------------+------+-----+---------+-------+ 
3. | Field | Type        | Null | Key | Default | Extra | 
4. +-------+-------------+------+-----+---------+-------+ 
5. | id    | int(11)     | YES  |     | NULL    |       | 
6. | name  | varchar(20) | YES  |     | NULL    |       | 
7. | sex   | char(1)     | YES  |     | NULL    |       | 
8. | birth | date        | YES  |     | NULL    |       | 
9. +-------+-------------+------+-----+---------+-------+ 
10. 4 rows in set (0.01 sec) 

mysql> describe person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

（8）查询表中的数据

Mysql代码

1. mysql> select * from person; 
2. Empty set (0.00 sec) 

mysql> select * from person;
Empty set (0.00 sec)

（9）插入数据

Mysql代码

1. mysql> insert into person(id,name,sex,birth) 
2.     -> values(1,'zhangsan','1','1990-01-08'); 
3. Query OK, 1 row affected (0.04 sec) 

mysql> insert into person(id,name,sex,birth)
    -> values(1,'zhangsan','1','1990-01-08');
Query OK, 1 row affected (0.04 sec)

查询表中的数据：

Mysql代码

1. mysql> select * from person; 
2. +------+----------+------+------------+ 
3. | id   | name     | sex  | birth      | 
4. +------+----------+------+------------+ 
5. |    1 | zhangsan | 1    | 1990-01-08 | 
6. +------+----------+------+------------+ 
7. 1 row in set (0.00 sec) 

mysql> select * from person;
+------+----------+------+------------+
| id   | name     | sex  | birth      |
+------+----------+------+------------+
|    1 | zhangsan | 1    | 1990-01-08 |
+------+----------+------+------------+
1 row in set (0.00 sec)

（10）修改字段的类型

Mysql代码

1. mysql> alter table person modify sex char(8); 
2. Query OK, 1 row affected (0.17 sec) 
3. Records: 1  Duplicates: 0  Warnings: 0 

mysql> alter table person modify sex char(8);
Query OK, 1 row affected (0.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

查看字段描述：

Mysql代码

1. mysql> desc person; 
2. +-------+-------------+------+-----+---------+-------+ 
3. | Field | Type        | Null | Key | Default | Extra | 
4. +-------+-------------+------+-----+---------+-------+ 
5. | id    | int(11)     | YES  |     | NULL    |       | 
6. | name  | varchar(20) | YES  |     | NULL    |       | 
7. | sex   | char(8)     | YES  |     | NULL    |       | 
8. | birth | date        | YES  |     | NULL    |       | 
9. +-------+-------------+------+-----+---------+-------+ 
10. 4 rows in set (0.01 sec) 

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(8)     | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

（11）新增一个字段

Mysql代码

1. mysql> alter table person add(address varchar(50)); 
2. Query OK, 1 row affected (0.27 sec) 
3. Records: 1  Duplicates: 0  Warnings: 0 

mysql> alter table person add(address varchar(50));
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0

查看字段描述：

Mysql代码

1. mysql> desc person; 
2. +---------+-------------+------+-----+---------+-------+ 
3. | Field   | Type        | Null | Key | Default | Extra | 
4. +---------+-------------+------+-----+---------+-------+ 
5. | id      | int(11)     | YES  |     | NULL    |       | 
6. | name    | varchar(20) | YES  |     | NULL    |       | 
7. | sex     | char(8)     | YES  |     | NULL    |       | 
8. | birth   | date        | YES  |     | NULL    |       | 
9. | address | varchar(50) | YES  |     | NULL    |       | 
10. +---------+-------------+------+-----+---------+-------+ 
11. 5 rows in set (0.01 sec) 

mysql> desc person;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| sex     | char(8)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

（12）更新字段内容
查看修改前表的内容：

Mysql代码

1. mysql> select * from person; 
2. +------+----------+------+------------+---------+ 
3. | id   | name     | sex  | birth      | address | 
4. +------+----------+------+------------+---------+ 
5. |    1 | zhangsan | 1    | 1990-01-08 | NULL    | 
6. +------+----------+------+------------+---------+ 
7. 1 row in set (0.00 sec) 

mysql> select * from person;
+------+----------+------+------------+---------+
| id   | name     | sex  | birth      | address |
+------+----------+------+------------+---------+
|    1 | zhangsan | 1    | 1990-01-08 | NULL    |
+------+----------+------+------------+---------+
1 row in set (0.00 sec)

修改：

Mysql代码

1. mysql> update person set name='lisi' where id=1; 
2. Query OK, 1 row affected (0.04 sec) 
3. Rows matched: 1  Changed: 1  Warnings: 0 
4.  
5. mysql> select * from person; 
6. +------+------+------+------------+---------+ 
7. | id   | name | sex  | birth      | address | 
8. +------+------+------+------------+---------+ 
9. |    1 | lisi | 1    | 1990-01-08 | NULL    | 
10. +------+------+------+------------+---------+ 
11. 1 row in set (0.00 sec) 
12.  
13. mysql> update person set sex='man',address='China' where id=1; 
14. Query OK, 1 row affected (0.04 sec) 
15. Rows matched: 1  Changed: 1  Warnings: 0 
16.  
17. mysql> select * from person; 
18. +------+------+------+------------+---------+ 
19. | id   | name | sex  | birth      | address | 
20. +------+------+------+------------+---------+ 
21. |    1 | lisi | man  | 1990-01-08 | China   | 
22. +------+------+------+------------+---------+ 
23. 1 row in set (0.00 sec) 

mysql> update person set name='lisi' where id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person;
+------+------+------+------------+---------+
| id   | name | sex  | birth      | address |
+------+------+------+------------+---------+
|    1 | lisi | 1    | 1990-01-08 | NULL    |
+------+------+------+------------+---------+
1 row in set (0.00 sec)

mysql> update person set sex='man',address='China' where id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from person;
+------+------+------+------------+---------+
| id   | name | sex  | birth      | address |
+------+------+------+------------+---------+
|    1 | lisi | man  | 1990-01-08 | China   |
+------+------+------+------------+---------+
1 row in set (0.00 sec)

为了方便下面测试删除数据，在向person表中插入2条数据：

Mysql代码

1. mysql> insert into person(id,name,sex,birth,address) 
2.     -> values(2,'wangwu','man','1990-01-10','China'); 
3. Query OK, 1 row affected (0.02 sec) 
4.  
5. mysql> insert into person(id,name,sex,birth,address) 
6.     -> values(3,'zhangsan','man','1990-01-10','China'); 
7. Query OK, 1 row affected (0.04 sec) 
8.  
9. mysql> select * from person; 
10. +------+----------+------+------------+---------+ 
11. | id   | name     | sex  | birth      | address | 
12. +------+----------+------+------------+---------+ 
13. |    1 | lisi     | man  | 1990-01-08 | China   | 
14. |    2 | wangwu   | man  | 1990-01-10 | China   | 
15. |    3 | zhangsan | man  | 1990-01-10 | China   | 
16. +------+----------+------+------------+---------+ 
17. 3 rows in set (0.00 sec) 

mysql> insert into person(id,name,sex,birth,address)
    -> values(2,'wangwu','man','1990-01-10','China');
Query OK, 1 row affected (0.02 sec)

mysql> insert into person(id,name,sex,birth,address)
    -> values(3,'zhangsan','man','1990-01-10','China');
Query OK, 1 row affected (0.04 sec)

mysql> select * from person;
+------+----------+------+------------+---------+
| id   | name     | sex  | birth      | address |
+------+----------+------+------------+---------+
|    1 | lisi     | man  | 1990-01-08 | China   |
|    2 | wangwu   | man  | 1990-01-10 | China   |
|    3 | zhangsan | man  | 1990-01-10 | China   |
+------+----------+------+------------+---------+
3 rows in set (0.00 sec)

（13）删除表中的数据
删除表中指定的数据：

Mysql代码

1. mysql> delete from person where id=2; 
2. Query OK, 1 row affected (0.02 sec) 
3.  
4. mysql> select * from person; 
5. +------+----------+------+------------+---------+ 
6. | id   | name     | sex  | birth      | address | 
7. +------+----------+------+------------+---------+ 
8. |    1 | lisi     | man  | 1990-01-08 | China   | 
9. |    3 | zhangsan | man  | 1990-01-10 | China   | 
10. +------+----------+------+------------+---------+ 
11. 2 rows in set (0.00 sec) 

mysql> delete from person where id=2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from person;
+------+----------+------+------------+---------+
| id   | name     | sex  | birth      | address |
+------+----------+------+------------+---------+
|    1 | lisi     | man  | 1990-01-08 | China   |
|    3 | zhangsan | man  | 1990-01-10 | China   |
+------+----------+------+------------+---------+
2 rows in set (0.00 sec)

删除表中全部的数据：

Mysql代码

1. mysql> delete from person; 
2. Query OK, 2 rows affected (0.04 sec) 
3.  
4. mysql> select * from person; 
5. Empty set (0.00 sec) 

mysql> delete from person;
Query OK, 2 rows affected (0.04 sec)

mysql> select * from person;
Empty set (0.00 sec)

（14）重命名表
查看重命名前的表名：

Mysql代码

1. mysql> show tables; 
2. +-------------------+ 
3. | Tables_in_test_db | 
4. +-------------------+ 
5. | person            | 
6. +-------------------+ 
7. 1 row in set (0.00 sec) 

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| person            |
+-------------------+
1 row in set (0.00 sec)

重命名：

Mysql代码

1. mysql> alter table person rename person_test; 
2. Query OK, 0 rows affected (0.04 sec) 
3.  
4. mysql> show tables; 
5. +-------------------+ 
6. | Tables_in_test_db | 
7. +-------------------+ 
8. | person_test       | 
9. +-------------------+ 
10. 1 row in set (0.00 sec) 

mysql> alter table person rename person_test;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| person_test       |
+-------------------+
1 row in set (0.00 sec)

（15）新增主键

Mysql代码

1. mysql> alter table person_test add primary key(id); 
2. Query OK, 0 rows affected (0.11 sec) 
3. Records: 0  Duplicates: 0  Warnings: 0 
4.  
5. mysql> desc person_test; 
6. +---------+-------------+------+-----+---------+-------+ 
7. | Field   | Type        | Null | Key | Default | Extra | 
8. +---------+-------------+------+-----+---------+-------+ 
9. | id      | int(11)     | NO   | PRI | 0       |       | 
10. | name    | varchar(20) | YES  |     | NULL    |       | 
11. | sex     | char(8)     | YES  |     | NULL    |       | 
12. | birth   | date        | YES  |     | NULL    |       | 
13. | address | varchar(50) | YES  |     | NULL    |       | 
14. +---------+-------------+------+-----+---------+-------+ 
15. 5 rows in set (0.00 sec) 

mysql> alter table person_test add primary key(id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person_test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | 0       |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| sex     | char(8)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除主键：

Mysql代码

1. mysql> alter table person_test drop primary key; 
2. Query OK, 0 rows affected (0.18 sec) 
3. Records: 0  Duplicates: 0  Warnings: 0 
4.  
5. mysql> desc person_test; 
6. +---------+-------------+------+-----+---------+-------+ 
7. | Field   | Type        | Null | Key | Default | Extra | 
8. +---------+-------------+------+-----+---------+-------+ 
9. | id      | int(11)     | NO   |     | 0       |       | 
10. | name    | varchar(20) | YES  |     | NULL    |       | 
11. | sex     | char(8)     | YES  |     | NULL    |       | 
12. | birth   | date        | YES  |     | NULL    |       | 
13. | address | varchar(50) | YES  |     | NULL    |       | 
14. +---------+-------------+------+-----+---------+-------+ 
15. 5 rows in set (0.01 sec) 

mysql> alter table person_test drop primary key;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person_test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | 0       |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| sex     | char(8)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

（16）删除表

Mysql代码

1. mysql> drop table person_test; 
2. Query OK, 0 rows affected (0.04 sec) 
3.  
4. mysql> show tables; 
5. Empty set (0.00 sec) 

mysql> drop table person_test;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
Empty set (0.00 sec)

（17）删除数据库

Mysql代码

1. mysql> show databases; 
2. +--------------------+ 
3. | Database           | 
4. +--------------------+ 
5. | information_schema | 
6. | mysql              | 
7. | performance_schema | 
8. | test               | 
9. | test_db            | 
10. +--------------------+ 
11. 5 rows in set (0.00 sec) 
12.  
13. mysql> drop database test_db; 
14. Query OK, 0 rows affected (0.11 sec) 
15.  
16. mysql> show databases; 
17. +--------------------+ 
18. | Database           | 
19. +--------------------+ 
20. | information_schema | 
21. | mysql              | 
22. | performance_schema | 
23. | test               | 
24. +--------------------+ 
25. 4 rows in set (0.00 sec) 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database test_db;
Query OK, 0 rows affected (0.11 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

（18）查看建表语句

Mysql代码

1. mysql> show create table table_name; 

mysql> show create table table_name;

补充说明：
update mysql.user set password="root" where User="root";修改的不是密码，如果按照这个方式修改了，重新登录时将会报错：

Mysql代码

1. mysql> update mysql.user set password="root" where User="root"; 
2. Query OK, 3 rows affected (0.00 sec) 
3. Rows matched: 3  Changed: 3  Warnings: 0 
4.  
5. mysql> exit 
6. Bye 
7.  
8. C:\Users\liqiong>mysql -uroot -p 
9. Enter password: **** 
10. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y 
11. ES) 

mysql> update mysql.user set password="root" where User="root";
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> exit
Bye

C:\Users\liqiong>mysql -uroot -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y
ES)

请按照以下方式重新修改密码，即可登录成功：

Mysql代码

1. C:\Users\liqiong>mysql -uroot 
2. Welcome to the MySQL monitor.  Commands end with ; or \g. 
3. Your MySQL connection id is 4 
4. Server version: 5.5.20 MySQL Community Server (GPL) 
5.  
6. Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 
7.  
8. Oracle is a registered trademark of Oracle Corporation and/or its 
9. affiliates. Other names may be trademarks of their respective 
10. owners. 
11.  
12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
13.  
14. mysql> update mysql.user set password=password("root") where User="root"; 
15. Query OK, 3 rows affected (0.00 sec) 
16. Rows matched: 3  Changed: 3  Warnings: 0 
17.  
18. mysql> flush privileges; 
19. Query OK, 0 rows affected (0.00 sec) 
20.  
21. mysql> exit 
22. Bye 
23.  
24. C:\Users\liqiong>mysql -uroot -p 
25. Enter password: **** 
26. Welcome to the MySQL monitor.  Commands end with ; or \g. 
27. Your MySQL connection id is 5 
28. Server version: 5.5.20 MySQL Community Server (GPL) 
29.  
30. Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 
31.  
32. Oracle is a registered trademark of Oracle Corporation and/or its 
33. affiliates. Other names may be trademarks of their respective 
34. owners. 
35.  
36. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
37.  
38. mysql> 

C:\Users\liqiong>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update mysql.user set password=password("root") where User="root";
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Users\liqiong>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

height="60" src="/iframe_ggbd/794" frameborder="0" width="468" scrolling="no">

分享到：     

从组织结构图看Google、Facebook、微软等大 ...      |      The naming scheme in MySQL 5.5

• 2012-02-21 13:35
• 浏览 11302
• 评论(6)
• 收藏
• 分类:数据库
• 相关推荐

评论

6 楼    握着橄榄枝的人2014-03-22      引用

我之前按照你的update mysql.user set password="root" where User="root";  操作导致Access denied for user ''@'localhost'”的错误！！！，我现在用你最后说明的方式修改不过来啊！

总是说
ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user
'

    5 楼    skykingf    2013-07-10      引用

不注册成服务可以用吗？

4 楼    lalor    2013-05-07      引用

凭作者前面三句话也应该点个赞

3 楼    lsy    2012-10-20      引用

很好的文章！赞！

2 楼    supportopensource    2012-05-04      引用

akunamotata 写道

update mysql.user set password="root" where User="root"; 

这句密码设置有问题，密码根本不为root，会导致Access denied for user ''@'localhost'”的错误！！！

谢谢提醒，我已更正！

1 楼    akunamotata    2012-05-03      引用

update mysql.user set password="root" where User="root"; 

这句密码设置有问题，密码根本不为root，会导致Access denied for user ''@'localhost'”的错误！！！