MySQL使用小結(jié)(Linux & Win)

zestsh

這是從開(kāi)始使用MySQL，到現(xiàn)在所積累的一些雜亂無(wú)章的筆記，因?yàn)椴皇且豢跉鈱?xiě)下來(lái)，而是以追加的形式構(gòu)成，大多地方還是從各種地方粘過(guò)來(lái)(很抱歉不能列出出處，地方太多，當(dāng)時(shí)沒(méi)記)，難免有重復(fù)和累贅，所以結(jié)構(gòu)邏輯不太分明，但又不想花時(shí)間改了，直接從電腦搬了出來(lái)，大家可以拿起磚頭。。。不過(guò)有的地方還是挺關(guān)鍵的，所以分享出來(lái)供大家學(xué)習(xí)參考，更詳細(xì)的資料可去：(MySQL doc:http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html)
史前準(zhǔn)備：
Linux(Debian & Ubuntu):
Install:
    sudo apt-get install mysql-server
    [port#3306]
    sudo apt-get install mysql-client
As default, mysql will automatically run, check:
    sudo netstat -tap | grep mysql
   
    sudo mysqladmin -u root password newpassword
    mysql -uroot -p
Install gui tools:
    sudo apt-get install mysql-admin
    sudo apt-get install mysql-query-browser
Windows(曾經(jīng)學(xué)Java連DB時(shí)使用作的總結(jié)):
雖然官方提供了非常好的安裝文件，但是有的時(shí)候不想每次再重裝系統(tǒng)之后都要安裝一遍MySQL，需要使用zip包版本的MySQL。在配置的時(shí)候有以下幾個(gè)問(wèn)題需要解決：
1、下載mysql
       http://dev.mysql.com/downloads/
2、將mysql解壓到待安裝目錄，使用%MYSQL_HOME%表示。
3、編輯my.ini文件(該文件放到C:\windows目錄下)，內(nèi)容如下：
        [mysqld]
        #default-character-set=gbk
        basedir=%MYSQL_HOME%
        datadir=%MYSQL_HOME%/data

        [WinMySQLadmin]
        Server=%MYSQL_HOME%/bin/mysqld-nt.exe
        user=root
        password=
4、打開(kāi)windows的cmd命令窗口，進(jìn)入到%MYSQL_HOME%\bin。
5、執(zhí)行命令： mysqld-nt.exe --install  （安裝到windows的服務(wù)）
6、執(zhí)行命令： net start mysql
7、初始帳號(hào):用戶(hù)名root，密碼為空，所以說(shuō)默認(rèn)情況下的MySQL5是不安全的。進(jìn)入之后立即修改root帳號(hào)密碼，命令如下：
        set password for root@localhost =password('yourPassword');  
        flush privileges;  
一般情況下，這樣操作后，mysql安裝完成。

    注：
    1、如果以前系統(tǒng)存在mysql。則進(jìn)入以前系統(tǒng)存在的mysql目錄,執(zhí)行 mysqld-nt.exe --remove(先從系統(tǒng)中移
          除mysql服務(wù))在開(kāi)始執(zhí)行上面第4步。
    2、如果出現(xiàn)1067錯(cuò)誤，一般是my.ini文件配置問(wèn)題。更改my.ini文件使之符合本地情況.
     
     
    盡管可以用現(xiàn)在的安裝文件，但有的時(shí)候不想每次再重裝系統(tǒng)之后都要安裝一遍MySQL，需要使用zip包版本的MySQL。在配置的時(shí)候有以下幾個(gè)問(wèn)題需要了解的，總結(jié)一下：
     
    1. 設(shè)置MySQL為Windows服務(wù)
     
    在Windows下用mysqld-nt.exe可以注冊(cè)為服務(wù)，即用mysqld-nt –install mysql
    這會(huì)設(shè)置為mysqld-nt.exe為mysql服務(wù)，相關(guān)信息在命令行模式下查詢(xún)mysqld-nt –verbose –help
     
    2. 啟動(dòng)服務(wù)時(shí)出現(xiàn)“錯(cuò)誤1067”的解決方法
     
    參考手冊(cè)中明確說(shuō)明了這個(gè)問(wèn)題：
     
    These messages often occur when the MySQL base or data directories are installed in different locations than the default locations (C:\Program Files\MySQL\MySQL Server 5.0 and C:\Program Files\MySQL\MySQL Server 5.0\data, respectively).
     
    上面的意思是在沒(méi)有將MySQL安裝到默認(rèn)的目錄下面的情況下啟動(dòng)Windows服務(wù)的時(shí)候就會(huì)出現(xiàn)上述提示，解決的辦法就是指定MySQL跟目錄以及數(shù)據(jù)庫(kù)目錄。在my.ini文件最后增加以下內(nèi)容，然后將my.ini拷貝到windows目錄下。
     
    [mysqld]  
    # set basedir to your installation path  
    basedir=E:/mysql  
    # set datadir to the location of your data directory  
    datadir=D:/MySQLdata

一) 連接MYSQL：  
   格式： mysql -h主機(jī)地址 -u用戶(hù)名 －p用戶(hù)密碼  
   查看狀態(tài)信息：status;

1、例1：連接到本機(jī)上的MYSQL  
   首先在打開(kāi)DOS窗口，然后進(jìn)入mysql安裝目錄下的bin目錄下，例如： D:\mysql\bin，再鍵入命令mysql -uroot -p，回車(chē)后提示你輸密碼，如果剛安裝好MYSQL，超級(jí)用戶(hù)root是沒(méi)有密碼的，故直接回車(chē)即可進(jìn)入到MYSQL中了，MYSQL的提示符是：mysql>  

2、例2：連接到遠(yuǎn)程主機(jī)上的MYSQL  
   假設(shè)遠(yuǎn)程主機(jī)的IP為：10.0.0.1，用戶(hù)名為root,密碼為123。則鍵入以下命令：
   mysql -h10.0.0.1 -uroot -p123  
  （注：u與root可以不用加空格，其它也一樣）  

3、退出MYSQL命令  
   exit/quit （回車(chē)）  



(二) 用戶(hù)管理：  
   格式：mysqladmin -u用戶(hù)名 -p舊密碼 password 新密碼  

1、例1：給root加個(gè)密碼123。首先在DOS下進(jìn)入目錄C:\mysql\bin，然后鍵入以下命令：  
   mysqladmin -uroot -password 123  
   注：因?yàn)殚_(kāi)始時(shí)root沒(méi)有密碼，所以-p舊密碼一項(xiàng)就可以省略了。  

2、例2：再將root的密碼改為456  
   mysqladmin -uroot -pab12 password 456
   
3、權(quán)限管理
   格式：grant select on 數(shù)據(jù)庫(kù).* to 用戶(hù)名@登錄主機(jī) identified by "密碼"  
   例1、增加一個(gè)用戶(hù)test1密碼為abc，讓他可以在任何主機(jī)上登錄，并對(duì)所有數(shù)據(jù)庫(kù)有查詢(xún)、插入、修改、刪除的權(quán)限。首先用以root用戶(hù)連入MYSQL，然后鍵入以下命令：  
   grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";

   但例1增加的用戶(hù)是十分危險(xiǎn)的，你想如某個(gè)人知道test1的密碼，那么他就可以在internet上的任何一臺(tái)電腦上登錄你的mysql數(shù)據(jù)庫(kù)并對(duì)你的數(shù)據(jù)可以為所欲為了，解決辦法見(jiàn)例2。  
   例2、增加一個(gè)用戶(hù)test2密碼為abc,讓他只可以在localhost上登錄，并可以對(duì)數(shù)據(jù)庫(kù)mydb進(jìn)行查詢(xún)、插入、修改、刪除的操作（localhost指本地主機(jī)，即MYSQL數(shù)據(jù)庫(kù)所在的那臺(tái)主機(jī)），這樣用戶(hù)即使用知道test2的密碼，他也無(wú)法從internet上直接訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)，只能通過(guò)MYSQL主機(jī)上的web頁(yè)來(lái)訪(fǎng)問(wèn)了。  
   grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";  
   如果你不想test2有密碼，可以再打一個(gè)命令將密碼消掉。  
   grant select,insert,update,delete on mydb.* to test2@localhost identified by "";  
2.1 //創(chuàng)建用戶(hù)
mysql> insert into mysql.user(Host,User,Password) values('localhost','phplamp',password('1234'));
//刷新系統(tǒng)權(quán)限表
mysql>flush privileges;
這樣就創(chuàng)建了一個(gè)名為：phplamp  密碼為：1234  的用戶(hù)。
2.2 //為用戶(hù)授權(quán)
//首先為用戶(hù)創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)(phplampDB)
mysql>create database phplampDB;
//授權(quán)phplamp用戶(hù)擁有phplamp數(shù)據(jù)庫(kù)的所有權(quán)限
@>grant all privileges on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系統(tǒng)權(quán)限表
mysql>flush privileges;
//如果想指定部分權(quán)限給一用戶(hù)，可以這樣來(lái)寫(xiě):
mysql>grant select,update on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系統(tǒng)權(quán)限表。
mysql>flush privileges;
mysql> grant 權(quán)限1,權(quán)限2,…權(quán)限n on 數(shù)據(jù)庫(kù)名稱(chēng).表名稱(chēng) to 用戶(hù)名@用戶(hù)地址 identified by ‘連接口令’;
權(quán)限1,權(quán)限2,…權(quán)限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14個(gè)權(quán)限。
2.3 //刪除用戶(hù)
mysql>DELETE FROM user WHERE User="phplamp" and Host="localhost";
mysql>flush privileges;
//刪除用戶(hù)的數(shù)據(jù)庫(kù)
mysql>drop database phplampDB;
2.4 //修改指定用戶(hù)密碼：
mysql>update mysql.user set password=password('新密碼') where User="phplamp" and Host="localhost";
mysql>flush privileges;
2.5 //查看所有用戶(hù)：
mysql> select user,host,password from mysql.user;

(三) 如何使用：
3.1、關(guān)于遠(yuǎn)程訪(fǎng)問(wèn)：
在安裝MySql后只有一個(gè)超級(jí)管理權(quán)限的用戶(hù)ROOT，而且ROOT限制只能在數(shù)據(jù)庫(kù)本機(jī)上使用，如果我們要遠(yuǎn)程管理MySql咋辦呢？那么事實(shí)上我們需要添加一個(gè)具有超級(jí)管理權(quán)限并且可能遠(yuǎn)程訪(fǎng)問(wèn)的超級(jí)用戶(hù)，而在MySql中有兩種方法可以實(shí)現(xiàn)這個(gè)目的:
    1.修改MySQL配置文件（/etc/mysql/my.cnf），注釋以下行：
    #bind-address = 127.0.0.1
    2.重啟mysql數(shù)據(jù)庫(kù)，對(duì)于Ubuntu系統(tǒng)，執(zhí)行如下命令
    sudo /etc/init.d/mysql restart
    3.以root用戶(hù)登錄mysql后，執(zhí)行如下命令：
    grant all privileges on *.* to root@’允許登錄的遠(yuǎn)程機(jī)器ip’
    identified by ‘密碼’
    其中，*.*表示該主機(jī)中所有數(shù)據(jù)庫(kù)的所有表。（數(shù)據(jù)庫(kù).表）
    “給某IP地址上登錄的root用戶(hù)授予訪(fǎng)問(wèn)所有數(shù)據(jù)庫(kù)中的所有表的所有權(quán)限”　

     你也可以通過(guò)發(fā)出GRANT語(yǔ)句增加新用戶(hù)：首先在數(shù)據(jù)庫(kù)本機(jī)上用ROOT用戶(hù)登錄上MySql（不用我告訴你如何登錄吧？），然后：

        mysql>GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY 'hsm' WITH GRANT OPTION;
        mysql>GRANT ALL PRIVILEGES ON *.* TO admin@"%" IDENTIFIED BY 'hsm' WITH GRANT OPTION;


第一句增加了一個(gè)admin用戶(hù)授權(quán)通過(guò)本地機(jī)（localhost)訪(fǎng)問(wèn)，密碼“hsm”。第二句則是授與admin用戶(hù)從任何其它主機(jī)發(fā)起的訪(fǎng)問(wèn)（通配符％）,或者特定IP地址，注意這是欲連接到此Mysql數(shù)據(jù)庫(kù)的客戶(hù)端的IP地址，而不是Mysql數(shù)據(jù)庫(kù)所在數(shù)據(jù)庫(kù)服務(wù)器的IP地址，切記。

你也可以直接通過(guò)發(fā)出INSERT語(yǔ)句增加同樣的用戶(hù)存取信息：

mysql>INSERT INTO user VALUES('localhost','admin',PASSWORD('hsm'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql>INSERT INTO user VALUES('%','admin',PASSWORD('hsm'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')

該命令取決于你的MySQL版本，對(duì)上述，你可能必須使用一個(gè)不同數(shù)目'Y'值(在3.22.11以前的版本有更少的權(quán)限列)。

驗(yàn)證方法，在命令行中輸入：mysql -h主機(jī)名或IP地址 -u用戶(hù)名 -p密碼
3.2、關(guān)于安全：
create user dba identified by 'sqlstudy
此時(shí)，創(chuàng)建的 MySQL 用戶(hù)的全名是：dba@'%'
見(jiàn)到這樣的MySQL 用戶(hù)名是不是覺(jué)得有點(diǎn)困惑？原來(lái) MySQL 的用戶(hù)名包含兩個(gè)部分： 1. 帳戶(hù)名（account name）；2. 主機(jī)名（hostname）。 hostname 用來(lái)限制該MySQL 帳戶(hù)（account）從何處來(lái)訪(fǎng)問(wèn) MySQL 服務(wù)器。
dba@'%'             ：可以在網(wǎng)絡(luò)中的任意地方，使用帳戶(hù)名 dba 來(lái)訪(fǎng)問(wèn) MySQL 服務(wù)。
dba@'localhost'     ：只能在本機(jī)（MySQL 服務(wù)所在的機(jī)器上），使用帳戶(hù)名 dba 來(lái)連接 MySQL。
dba@'192.168.0.200' ：只能從網(wǎng)絡(luò)中的 192.168.0.200 機(jī)器上，使用帳戶(hù)名 dba 來(lái)連接 MySQL。
dba@'192.168.0.%'   ：可以從網(wǎng)絡(luò)中的 192.168.0. 任意一臺(tái)機(jī)器上，使用帳戶(hù)名 dba 來(lái)連接 MySQL。
為了使 MySQL 更安全，我們?cè)趧?chuàng)建用戶(hù)的時(shí)候，應(yīng)該根據(jù)實(shí)際訪(fǎng)問(wèn)限制，來(lái)選擇合適的用戶(hù)名。 同時(shí)要謹(jǐn)慎使用 dba@'%'，另外在創(chuàng)建用戶(hù)的時(shí)候，最好顯式指定 hostname。
create user dba@'localhost' identified by 'sqlstudylocal'
create user dba@'%'         identified by 'sqlstudyany'
drop user dba
等價(jià)于：
drop user dba@'%'
3.3、數(shù)據(jù)操縱：
舊數(shù)據(jù)升級(jí)辦法
    (1) 導(dǎo)出數(shù)據(jù)庫(kù)：
    mysqldump -uroot -p123456 --default-character-set=latin1 --set-charset=utf8 --opt olddatabase > newdatabase.sql
    (2) 修改newdatabase.sql，在文件開(kāi)頭增加一條sql語(yǔ)句： “SET NAMES utf8;“，保存。
    (3) 導(dǎo)入數(shù)據(jù)庫(kù)：
        mysql -hlocalhost -uroot my_db  music.sql
        mysql -uroot -p
            >create database music;
            >use music;
            >create user z;
            >source ./music.sql;
            >show tables;
            >charset utf8;// == set names 'utf8'
            >select * from composer;
   

(四) 顯示命令  
（注意：和上面不同，下面的因?yàn)槭荕YSQL環(huán)境中的命令，所以后面都帶一個(gè)分號(hào)作為命令結(jié)束符）  
1、顯示數(shù)據(jù)庫(kù)列表：  
   SHOW DATABASES;  
   剛開(kāi)始時(shí)才兩個(gè)數(shù)據(jù)庫(kù)：mysql和test。mysql庫(kù)很重要它里面有MYSQL的系統(tǒng)信息，我們改密碼和新增用戶(hù)，實(shí)際上就是用這個(gè)庫(kù)進(jìn)行操作。  

2、顯示庫(kù)中的數(shù)據(jù)表：  
   USE mysql； //打開(kāi)庫(kù)
   SHOW TABLES;  

3、顯示數(shù)據(jù)表的結(jié)構(gòu)：  
   DESCRIBE 表名;  

4、建庫(kù)：  
   CREATE DATABASE 庫(kù)名;  

5、建表：  
   USE 庫(kù)名；  
   CREATE TABLE 表名 (字段設(shè)定列表)；  

6、刪庫(kù)和刪表:  
   DROP DATABASE 庫(kù)名;  
   DROP TABLE 表名；  

7、將表中記錄清空：  
   DELETE FROM 表名;  

8、顯示表中的記錄：  
   SELECT * FROM 表名;
9、向表中添加數(shù)據(jù)：
   INSERT INTO table_name VALUES('','');
10、添加表：
   CREATE TABLE  `music`.`user` (`user` varchar(255) default NULL,`pwd` varchar(255) default NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8
11、更新表：
   UPDATE table_name SET name='HSM' WHERE id=1;
12、更改表：
   ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
13、把表t1重新命名為t2：
   ALTER TABLE t1 RENAME t2;
14、刪除列c：
   ALTER TABLE t2 DROP COLUMN c;
15、添加一個(gè)新的AUTO_INCREMENT整數(shù)列，名稱(chēng)為c：
   ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c);
16、創(chuàng)建索引：
   CREATE INDEX part_of_name ON customer (name(10));
    示例：
   CREATE TABLE lookup (id INT) ENGINE = MEMORY;
   CREATE INDEX id_index USING BTREE ON lookup (id);
17、賦予權(quán)限：
   GRANT ALL ON amarok.* TO amarok IDENTIFIED BY 'amarok';
   
   SHOW GRANTS FOR amarok;
18、撤銷(xiāo)權(quán)限：
   REVOKE ALL ON amarok.* FROM amarok;
               
               
               
               

本文來(lái)自ChinaUnix博客，如果查看原文請(qǐng)點(diǎn)：http://blog.chinaunix.net/u3/107272/showart_2110533.html