推薦：Mysql大小寫(xiě)敏感的問(wèn)題
一、1 CREATE TABLE NAME(name VARCHAR(10)); 對(duì)這個(gè)表，缺省情況下，下面兩個(gè)查詢的結(jié)果是一樣的： 代碼如下: SELECT * FROM TABLE NAME WHERE name='clip'; SELECT * FROM TABLE NAME WHERE name='Clip'; MySql默認(rèn)查詢是不區(qū)分大小寫(xiě)的,如果需要區(qū)分他,必須在建表的

 alter是非常強(qiáng)大的一個(gè)功能我們可以利用alter來(lái)修改數(shù)據(jù)表表名字體名及一些其它的操作了，下面一起來(lái)看看mysql中alter數(shù)據(jù)表中增加、刪除字段與表名修改的一個(gè)例子．

修改刪除mysql數(shù)據(jù)庫(kù)中的數(shù)據(jù)內(nèi)容：

[root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' #進(jìn)入mysql
mysql> create database gbk default character set gbk collate gbk_chinese_ci; #建立一個(gè)名字叫做gbk的數(shù)據(jù)庫(kù)
mysql> use gbk
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gbk |
+--------------------+
mysql> show tables;
Empty set (0.00 sec)
mysql> create table test( #建立一個(gè)叫做test的數(shù)據(jù)表
-> id int(4) not null primary key auto_increment,
-> name char(20) not null
-> );
Query OK, 0 rows affected (0.13 sec)

mysql> show tables;
+---------------+
| Tables_in_gbk |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
mysql> insert into test(id,name) values(1,'zy'); #插入部分內(nèi)容
mysql> insert into test(id,name) values(2,'binghe');
mysql> insert into test(id,name) values(3,'zilong');
mysql> insert into test(id,name) values(4,'feng');
mysql> select * from test; #檢索整個(gè)test表
+----+--------+
| id | name |
+----+--------+
| 1 | zy |
| 2 | binghe |
| 3 | zilong |
| 4 | feng |
+----+--------+
4 rows in set (0.00 sec)
[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B gbk >/tmp/gbk.sql #備份gbk數(shù)據(jù)庫(kù)
mysql> update test set name = 'zy' ; #未定義
mysql> select * from test; #
+----+------+
| id | name |
+----+------+
| 1 | zy |
| 2 | zy |
| 3 | zy |
| 4 | zy |
+----+------+
[root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' mysql> use gbk
mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | zy |
| 2 | binghe |
| 3 | zilong |
| 4 | feng |
+----+--------+
mysql> update test set name = 'yadianna' where id =1;
mysql> select * from test;
+----+----------+
| id | name |
+----+----------+
| 1 | yadianna |
| 2 | binghe |
| 3 | zilong |
| 4 | feng |
+----+----------+
mysql> update test set id = 999 where name ='yadianna';
mysql> select * from test;
+-----+----------+
| id | name |
+-----+----------+
| 2 | binghe |
| 3 | zilong |
| 4 | feng |
| 999 | yadianna |
+-----+----------+
mysql> delete from test where id =999;
mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 2 | binghe |
| 3 | zilong |
| 4 | feng |
+----+--------+
mysql> delete from test where id <4; #以條件刪除
mysql> truncate table test; #刪除all
mysql> select * from test;
Empty set (0.00 sec)

接上上面，修改數(shù)據(jù)庫(kù)中表名，表中增加、刪除字段。

mysql> use gbk    #進(jìn)入gbk數(shù)據(jù)庫(kù)
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
mysql> alter table test add gender char(4);    #增加gender
mysql> desc test;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(4)   | NO   | PRI | NULL    | auto_increment |
| name   | char(20) | NO   |     | NULL    |                |
| gender | char(4)  | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
mysql> alter table test add age int(4) after name;
mysql> desc test;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(4)   | NO   | PRI | NULL    | auto_increment |
| name   | char(20) | NO   |     | NULL    |                |
| age    | int(4)   | YES  |     | NULL    |                |
| gender | char(4)  | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
mysql> show tables;
+---------------+
| Tables_in_gbk |
+---------------+
| test          |
+---------------+
mysql> rename table test to hello;
mysql> show tables;
+---------------+
| Tables_in_gbk |
+---------------+
| hello         |
+---------------+
mysql> alter table hello rename to world;
mysql> show tables;
+---------------+
| Tables_in_gbk |
+---------------+
| world         |
+---------------+
mysql> alter table world drop age;
mysql> desc world;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(4)   | NO   | PRI | NULL    | auto_increment |
| name   | char(20) | NO   |     | NULL    |                |
| gender | char(4)  | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

分享：給Mysql添加遠(yuǎn)程訪問(wèn)權(quán)限的方法
user:myuser pwd:mypassword 從任何主機(jī)連接到mysql服務(wù)器 GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; user:myuser pwd:mypassword 從192.168.1.3連接到mysql服務(wù)器 GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.1