MySQl 命令大全

擒賊先擒王發表於2017-07-21
原文網址 : https://blog.csdn.net/freeking101/article/details/75540982
MySql

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

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

MySQL 命令大全：http://c.biancheng.net/cpp/u/mysql_ml/

十步完全理解 SQL：http://blog.jobbole.com/55086/

易百教程：http://www.yiibai.com/sql/

MySQL之許可權管理：http://www.cnblogs.com/Richardzhu/p/3318595.html

mysql變數使用總結：http://www.cnblogs.com/wangtao_20/archive/2011/02/21/1959734.html


MySQL是一個關係型資料庫管理系統，由於其體積小、速度快、總體擁有成本低，尤其是開放原始碼這一特點，一般中小型網站的開發都選擇MySQL作為網站資料庫。由於其社群版的效能卓越，搭配PHP和Apache可組成良好的開發環境。現在主要介紹常用的MySQL命令，包括連線資料庫，修改密碼，管理使用者，運算元據庫，運算元據表，資料庫備份等


MySQL資料庫的一些簡單操作，請閱讀一下文章：



MySQL 資料庫一些操作

注意：你必須首先登入到MySQL中，以下操作都是在MySQL的提示符下進行的，而且每個命令以分號結束。

1、MySQL常用命令
    create database name;    建立資料庫
    use databasename;        選擇資料庫
    drop database name                    直接刪除資料庫，不提醒。
	mysqladmin drop database name     刪除資料庫前，有提示。
    show tables;             列出資料庫中的所有表	
    describe tablename;      表的詳細描述.顯示錶結構. 等價 show columns from tableName;
    select 中加上 distinct   去除重複欄位
    
    drop table MYTABLE;      刪除表
    delete from MYTABLE;     清空表
    

    MySQL中SELECT命令類似於其他程式語言裡的print或者write，你可以用它來顯示一個字串、數字、數學表示式的結果等等
    select version(),current_date;    顯示當前mysql版本和當前日期
    select now();                     查詢時間
    select user();                    查詢當前使用者
    select version();                 查詢資料庫版本
    select database();                查詢當前使用的資料庫
    顯示年月日
    select dayofmonth(current_date); 
    select month(current_date);
    select year(current_date);

    SELECT "welecome to my blog!";         顯示字串
    select ((4 * 4) / 10 ) + 25;           當計算器用

    拼接字串
    select CONCAT(f_name, " ", l_name)     
    AS Name
    from employee_data 
    where title = 'Marketing Executive'; 
    這裡用到CONCAT()函式，用來把字串串接起來。另外，AS 用來 給 結果列'CONCAT(f_name, " ", l_name)'起個 別名。
	
    create temporary table zengchao(name varchar(10));     建立臨時表：(建立臨時表zengchao)　
    create table if not exists students(……);             建立表時先判斷表是否存在　
    create table table2 select * from table1 where 1<>1;   從已經有的表中複製表的結構　　
    create table table2 select * from table1;              複製表
    alter table table1 rename as table2;                   對錶重新命名
　　
    修改列的型別
    alter table table1 modify id int unsigned;        //修改列id的型別為int unsigned
    alter table table1 change id sid int unsigned;    //修改列id的名字為sid，而且把屬性修改為int unsigned
　　
　　建立索引
    alter table table1 add index ind_id (id);
    create index ind_id on table1 (id);
    create unique index ind_id on table1 (id);    //建立唯一性索引
　　
　　刪除索引
    drop index idx_id on table1;
    alter table table1 drop index ind_id;
　　
    select concat(id,':',name,'=') from students;     拼接字元或者多個列(將列id與":"和列name和"="連線)　　　　
    select * from students order by id limit 9,10;    limit(選出10到20條)<第一個記錄集的編號是0>
　　
　　MySQL不支援的功能
　　事務，檢視，外來鍵和引用完整性，儲存過程和觸發器
	
    update MYTABLE set sex="f" where name='hyq';   更新表中資料	
    UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT rows]
    or
    UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    
    UPDATE 以新的值更新現存表中行的列。
    SET 子句指出要修改哪個列和他們應該給定的值。
    WHERE 子句如果被給出，指定哪個記錄行應該被更新。否則，所有的記錄行被更新。
    如果 ORDER BY 子句被指定，記錄行將被以指定的次序更新。
    如果你指定關鍵詞 LOW_PRIORITY，UPDATE 的執行將被延遲，直到沒有其它的客戶端正在讀取表。
    如果你指定關鍵詞 IGNORE，該更新語句將不會異常中止，即使在更新過程中出現重複鍵錯誤。導致衝突的記錄行將不會被更新。
    如果在一個表示式中從 tbl_name 中訪問一個列，UPDATE 使用列的當前值。
    舉例來說，下面的語句設定 age 列值為它的當前值加 1 ：
    mysql> UPDATE persondata SET age=age+1;
    
    UPDATE 賦值是從左到右計算的。
    舉例來說，下列語句將 age 列設定為它的兩倍，然後再加 1 ：
    mysql> UPDATE persondata SET age=age*2, age=age+1;
    如果你設定列為其當前的值，MySQL 注意到這點，並不更新它。
    
    UPDATE 返回實際被改變的記錄行數目。
    在 MySQL 3.22 或更新的版本中，C API 函式 mysql_info()返回被匹配並更新的記錄行數目，以及在 UPDATE 期間發生的警告的數目。
    在 MySQL 3.23 中，你可以使用 LIMIT # 來確保只有給定的記錄行數目被更改。
    如果一個 ORDER BY 子句被使用(從 MySQL 4.0.0 開始支援)，記錄行將以指定的次序被更新。這實際上只有連同 LIMIT一起才有用。
    從 MySQL 4.0.4 開始，你也可以執行一個包含多個表的 UPDATE 的操作：
    UPDATE items,month SET items.price=month.price
    WHERE items.id=month.id;
    注意：多表 UPDATE 不可以使用 ORDER BY 或 LIMIT。

2、修改 mysql中 root 的密碼：
    shell> mysql -u root -p
    mysql> update user set password="xueok654123" where user='root';
    mysql> flush privileges     // 重新整理資料庫
    mysql> use dbname;          // 開啟資料庫：
    mysql> show databases;      // 顯示所有資料庫
    mysql> show tables;         // 顯示資料庫mysql中所有的表：先use mysql；然後
    mysql> describe user;       // 顯示錶mysql資料庫中user表的列資訊）；	
	

增加MySQL使用者 
    格式：grant select on 資料庫.* to 使用者名稱@登入主機 identified by "密碼" 
    
    增加一個使用者user_1密碼為123，讓他可以在任何主機上登入，並對所有資料庫有查詢、插入、修改、刪除的許可權。
    首先用以root使用者連入MySQL，然後鍵入以下命令： 
    mysql> grant select,insert,update,delete on *.* to user_1@"%" Identified by "123"; 
    增加的使用者是十分危險的，如果知道了user_1的密碼，那麼他就可以在網上的任何一臺電腦上登入你的MySQL資料庫並對你的資料為所欲為了。
    
    針對上面的問題，可以這樣解決
    增加一個使用者user_2密碼為123,讓此使用者只可以在localhost上登入，並可以對資料庫aaa進行查詢、插入、修改、
    刪除的操作（localhost指本地主機，即MySQL資料庫所在的那臺主機），這樣使用者即使用知道user_2的密碼，
    他也無法從網上直接訪問資料庫，只能通過MYSQL主機來操作aaa庫。 
    mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123"; 
    
    用新增的使用者如果登入不了MySQL，在登入時用如下命令： 
    mysql -u user_1 -p　-h 192.168.113.50　（-h後跟的是要登入主機的ip地址） 

3、grant
    建立一個可以從任何地方連線伺服器的一個完全的超級使用者，但是必須使用一個口令 password 做這個
    mysql> grant all privileges on *.* to user@localhost identified by "password" with
    增加新使用者
    格式：grant select on 資料庫.* to 使用者名稱@登入主機 identified by “密碼”
    GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY "password" WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY "password" WITH GRANT OPTION;

    格式：grant 許可權 on 資料庫.* to 使用者名稱@登入主機 identified by "密碼"
    示例：增加一個使用者user1密碼為password1，讓其可以在本機上登入， 並對所有資料庫有查詢、插入、修改、刪除的許可權。
    首先用以root使用者連入mysql，然後鍵入以下命令：
    grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
    如果希望該使用者能夠在任何機器上登陸mysql，則將localhost改為"%"。
    如果你不想user1有密碼，可以再打一個命令將密碼去掉。
    grant select,insert,update,delete on mydb.* to user1@localhost identified by "";

    刪除授權：
    mysql> revoke all privileges on *.* from root@”%”;
    mysql> delete from user where user=”root” and host=”%”;
    mysql> flush privileges;
    建立一個使用者custom在特定客戶端it363.com登入，可訪問特定資料庫fangchandb
    mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’
    重新命名錶:
    mysql > alter table t1 rename t2;	
	
4、mysqldump
    備份資料庫
    shell> mysqldump -h host -u root -p dbname >dbname_backup.sql
    恢復資料庫
    shell> mysqladmin -h myhost -u root -p create dbname
    shell> mysqldump -h host -u root -p dbname < dbname_backup.sql
    如果只想卸出建表指令，則命令如下：
    shell> mysqladmin -u root -p -d databasename > a.sql
    如果只想卸出插入資料的sql命令，而不需要建表命令，則命令如下：
    shell> mysqladmin -u root -p -t databasename > a.sql
    那麼如果我只想要資料，而不想要什麼sql命令時，應該如何操作呢？
    mysqldump -T./ phptest driver
    其中，只有指定了-T引數才可以卸出純文字檔案，表示卸出資料的目錄，./表示當前目錄，即與mysqldump同一目錄。
	如果不指定driver 表，則將卸出整個資料庫的資料。每個表會生成兩個檔案，一個為.sql檔案，包含建表執行。
	另一個為.txt檔案，只包含資料，且沒有sql指令。
	
5、可將查詢儲存在一個檔案中並告訴mysql從檔案中讀取查詢而不是等待鍵盤輸入。可利用外殼程式鍵入重定向實用程式來完成這項工作。
    例如，如果在檔案my_file.sql 中存放有查詢，可如下執行這些查詢：
    例如，如果您想將建表語句提前寫在sql.txt中:
    mysql > mysql -h myhost -u root -p database < sql.txt
	
6、建立資料庫staffer
    create database staffer;
	
7、下面的語句在mysql環境在執行
    show databases;        顯示使用者擁有許可權的資料庫    
    use staffer;           切換到staffer資料庫         
    show tables;           顯示當前資料庫中有許可權的表  
    desc staffer;          顯示錶staffer的結構         
8、建立測試環境
    1)建立資料庫staffer
        mysql> create database staffer
    2)建立表staffer,department,position,depart_pos
        create table s_position
        (
        id int not null auto_increment,
        name varchar(20) not null default '經理', #設定預設值
        description varchar(100),
        primary key PK_positon (id) #設定主鍵
        );
        create table department
        (
        id int not null auto_increment,
        name varchar(20) not null default '系統部', #設定預設值
        description varchar(100),
        primary key PK_department (id) #設定主鍵
        );
        create table depart_pos
        (
        department_id int not null,
        position_id int not null,
        primary key PK_depart_pos (department_id,position_id) #設定復和主鍵
        );
        create table staffer
        (
        id int not null auto_increment primary key, #設定主鍵
        name varchar(20) not null default '無名氏', #設定預設值
        department_id int not null,
        position_id int not null,
        unique (department_id,position_id) #設定唯一值
        );

        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('','glchengang','深圳一中','1976-10-10'); 
        insert into teacher values('','jack','深圳一中','1975-12-23');

注：在建表中
（1）將ID設為長度為3的數字欄位:int(3)並讓它每個記錄自動加一:auto_increment並不能為空:not null而且讓他成為主欄位primary key
（2）將NAME設為長度為10的字元欄位
（3）將ADDRESS設為長度50的字元欄位，而且預設值為深圳。varchar和char有什麼區別呢，只有等以後的文章再說了。
（4）將YEAR設為日期欄位。 

如果你在mysql提示符鍵入上面的命令也可以，但不方便除錯。
你可以將以上命令原樣寫入一個文字檔案中假設為school.sql，然後複製到c:\下，並在DOS狀態進入目錄\mysql\bin，然後鍵入以下命令： 
mysql -uroot -p密碼 < c:\school.sql 
如果成功，空出一行無任何顯示；如有錯誤，會有提示。

    3)刪除
        mysql>
        drop table depart_pos;
        drop table department;
        drop table s_position;
        drop table staffer;
        drop database staffer;

9、修改結構

    增加一個欄位：alter table tabelName add column fieldName dateType;
　　增加多個欄位：alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;

    mysql>
    #表position增加列test
    alter table position add(test char(10));
    #表position修改列test
    alter table position modify test char(20) not null;
    #表position修改列test預設值
    alter table position alter test set default 'system';
    #表position去掉test預設值
    alter table position alter test drop default;
    #表position去掉列test
    alter table position drop column test;
    #表depart_pos刪除主鍵
    alter table depart_pos drop primary key;
    #表depart_pos增加主鍵
    alter table depart_pos add primary key PK_depart_pos (department_id,position_id);
10、運算元據
    #插入表department
    insert into department(name,description) values('系統部','系統部');
    insert into department(name,description) values('公關部','公關部');
    insert into department(name,description) values('客服部','客服部');
    insert into department(name,description) values('財務部','財務部');
    insert into department(name,description) values('測試部','測試部');
    #插入表s_position
    insert into s_position(name,description) values('總監','總監');
    insert into s_position(name,description) values('經理','經理');
    insert into s_position(name,description) values('普通員工','普通員工');
    #插入表depart_pos
    insert into depart_pos(department_id,position_id)
    select a.id department_id,b.id postion_id
    from department a,s_position b;
    #插入表staffer
    insert into staffer(name,department_id,position_id) values('陳達治',1,1);
    insert into staffer(name,department_id,position_id) values('李文賓',1,2);
    insert into staffer(name,department_id,position_id) values('馬佳',1,3);
    insert into staffer(name,department_id,position_id) values('亢志強',5,1);
    insert into staffer(name,department_id,position_id) values('楊玉茹',4,1);
	
11、查詢及刪除操作
    #顯示系統部的人員和職位
    select a.name,b.name department_name,c.name position_name
    from staffer a,department b,s_position c
    where a.department_id=b.id and a.position_id=c.id and b.name='系統部';
    #顯示系統部的人數
    select count(*) from staffer a,department b
    where a.department_id=b.id and b.name='系統部'
    #顯示各部門的人數
    select count(*) cou,b.name
    from staffer a,department b
    where a.department_id=b.id
    group by b.name;
    #刪除客服部
    delete from department where name='客服部';
    #將財務部修改為財務一部
    update department set name='財務一部' where name='財務部';
	
12、備份和恢復    
    c:\mysql\bin\mysqldump -uroot -proot staffer>e:\staffer.sql      備份資料庫staffer    
	    得到的staffer.sql是一個sql指令碼，不包括建庫的語句，所以需要手工建立一個空庫staffer，才可以匯入恢復資料庫staffer。
    c:\mysql\bin\mysql -uroot -proot staffer<staffer.sql
	    
    如果不希望後來手工建立staffer,可以
    c:\mysql\bin\mysqldump -uroot -proot --databases staffer>e:\staffer.sql
    mysql -uroot -proot >e:\staffer.sql
    但這樣的話系統種就不能存在staffer庫，且無法匯入其他名字的資料庫，當然你可以手工修改staffer.sql檔案
	
    
　　mysqldump -u root -p test>c:\test.txt                備份資料庫：(將資料庫test備份)　　
　　mysqldump -u root -p test mytable>c:\test.txt        備份表格：(備份test資料庫下的mytable表格)　　
　　mysql -u root -p test < c:\test.txt                  將備份資料匯入到資料庫：(導回test資料庫)

    將文字資料匯入資料庫。文字資料的欄位資料之間用tab鍵隔開。
    use test;
    load data local infile "檔名" into table 表名;    
    mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;   用文字方式將資料裝入資料庫表中（例如D:/mysql.txt）
    
    匯入.sql檔案命令（例如D:/mysql.sql）
    mysql> use database;
    mysql> source d:/mysql.sql;

    匯出資料：
    把資料庫dbname匯出到檔案mysql.dbname中。後者是一個文字檔案
    mysqldump -u root -p123456 --databases dbname > mysql.dbname    
    mysqldump --opt school>school.bbb     （命令在DOS的\mysql\bin目錄下執行） 
    註釋:將資料庫school備份到school.bbb檔案，school.bbb是一個文字檔案，檔名任取，開啟看看你會有新發現。

    匯入資料:
    mysqlimport -u root -p123456 < mysql.dbname。


連線 Mysql

格式： mysql -h主機地址 -u使用者名稱 －p使用者密碼

1、連線到本機上的MYSQL

首先開啟DOS視窗，然後進入目錄mysql\bin，再鍵入命令mysql -u root -p，回車後提示你輸密碼.注意使用者名稱前可以有空格也可以沒有空格，但是密碼前必須沒有空格，否則讓你重新輸入密碼。如果剛安裝好MYSQL，超級使用者root是沒有密碼的，故直接回車即可進入到MYSQL中了，MYSQL的提示符是： mysql>


2、連線到遠端主機上的MYSQL

假設遠端主機的IP為：110.110.110.110，使用者名稱為root,密碼為abcd123。則鍵入以下命令：

mysql -h110.110.110.110 -u root -p abcd123;（注:u與root之間可以不用加空格，其它也一樣）

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


mysqladmin：修改使用者密碼

格式：mysqladmin -u使用者名稱 -p舊密碼 password 新密碼

1、給root設定密碼。

首先在DOS下進入目錄mysql\bin，然後鍵入以下命令

mysqladmin -u root -password ab12           //設定 root 密碼為 ab12

注：因為開始時root沒有密碼，所以-p舊密碼一項就可以省略了。

2、修改root密碼。

mysqladmin -u root -p ab12 password djg345       // 修改 root 密碼為 djg345


grant on：建立新使用者並設定許可權

格式：grant 許可權 on 資料庫.表 to 使用者名稱@登入主機 identified by "密碼"；

注意：和上面不同，下面的因為是MYSQL環境中的命令，所以後面都帶一個 “分號” 作為命令結束符。

設定許可權時必須給出一下資訊
1，要授予的許可權
2，被授予訪問許可權的資料庫或表
3，使用者名稱
grant和revoke可以在幾個層次上控制訪問許可權
1，整個伺服器，使用 grant ALL  和revoke  ALL
2，整個資料庫，使用on  database.*
3，特定表，使用on  database.table
4，特定的列
5，特定的儲存過程
user表中host列的值的意義
%              匹配所有主機
localhost    localhost不會被解析成IP地址，直接通過UNIXsocket連線
127.0.0.1      會通過TCP/IP協議連線，並且只能在本機訪問；
::1                 ::1就是相容支援ipv6的，表示同ipv4的127.0.0.1


建立新使用者並設定許可權

例1：增加一個test1使用者，密碼為123456，可以在任何主機上登入，並對所有資料庫有查詢，增加，修改和刪除的功能。需要在mysql的root使用者下進行
mysql>grant select,insert,update,delete on *.* to test1@"%" identified by "123456"；
mysql>flush privileges;

例2：增加一個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;

如果不想 test3 有密碼，可以再打一個命令將密碼消掉。
mysql>grant all privileges on student.* to test3@localhost identified by '';
mysql>flush privileges;

修改使用者密碼、刪除使用者、刪除資料庫和刪除表、刪除賬戶及許可權

修改使用者密碼
mysql>update mysql.user set password=password("123456") where User=’test1′ and Host="localhost";
mysql>flush privileges;

刪除使用者
mysql>delete from user where user='test2' and host='localhost';
mysql>flush privileges;

刪除資料庫和刪除表
mysql>drop database 資料庫名;
mysql>drop table 表名;

刪除賬戶及許可權
drop user 使用者名稱@'%'
drop user 使用者名稱@localhost

MySQL新增新使用者、為使用者建立資料庫、為新使用者分配許可權

登入MySQL
mysql -u root -p  

新增新使用者,允許本地 IP 訪問 localhost, 127.0.0.1
create user 'test'@'localhost' identified by '123456';  

允許外網 IP 訪問
create user 'test'@'%' identified by '123456'; 

重新整理授權
flush privileges;  

為使用者建立資料庫
create database test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;  

為新使用者分配許可權
授予使用者通過外網IP對於該資料庫的全部許可權
grant all privileges on `testdb`.* to 'test'@'%' identified by '123456';  

授予使用者在本地伺服器對該資料庫的全部許可權
grant all privileges on `testdb`.* to 'test'@'localhost' identified by '123456';  

重新整理許可權
flush privileges;  


用新帳號 test 重新登入，由於使用的是 % 任意IP連線，所以需要指定外部訪問IP
mysql -u test -h 115.28.203.224 -p  

在Ubuntu伺服器下，MySQL預設是隻允許本地登入，因此需要修改配置檔案將地址繫結給註釋掉：
# Instead of skip-networking the default is now to listen only on  
# localhost which is more compatible and is not less secure.  
#bind-address       = 127.0.0.1     #註釋掉這一行就可以遠端登入了  

1,Mysql下建立新的使用者
語法：
1.create user 使用者名稱 identified by '密碼';
例：create user xiaogang identified by '123456';
新建立的使用者，預設情況下是沒有任何許可權的。

2. 如何給使用者分配許可權
語法：
1.grant 許可權 on 資料庫.資料表 to '使用者' @ '主機名';
例：給 xiaogang 分配所有的許可權
grant all on *.* to 'xiaogang'@'%';
這個時候 xiaogang 就擁有了 所有許可權了
3 如何更精準的控制使用者的許可權呢？
1.grant 許可權 on 資料庫.資料表 to '使用者' @ '主機名';

例：讓 xiaogang 有查詢 tmp 資料庫 tmp1 表的許可權；
grant select on temp.temp1 to 'xiaogang'@'%';   //這個時候 xiaogang 就具有查詢temp小的temp1的許可權了。

例如：
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;
給來自10.163.225.87的使用者joe分配可對資料庫vtdc的employee表進行select,insert,update,delete,create,drop等操作的許可權，並設定口令為123。
 
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by ‘123′;
給來自10.163.225.87的使用者joe分配可對資料庫vtdc所有表進行所有操作的許可權，並設定口令為123。
 
mysql>grant all privileges on *.* to joe@10.163.225.87 identified by ‘123′;
給來自10.163.225.87的使用者joe分配可對所有資料庫的所有表進行所有操作的許可權，並設定口令為123。
 
mysql>grant all privileges on *.* to joe@localhost identified by ‘123′;
給本機使用者joe分配可對所有資料庫的所有表進行所有操作的許可權，並設定口令為123。
 
4. 如何收回 許可權,一般指有root使用者才具有該許可權
語法：
1.revoke 許可權 on  資料庫.資料表 from '使用者'@'主機名';
例：收回 xiaogang的所有許可權
revoke all on *.* from 'xiaogang' @'%';

新增使用者、授權 步驟總結：

第一步：mysql服務的啟動和停止
    net stop mysql
    net start mysql
第二步：直接登陸mysql
    語法如下： mysql -u使用者名稱 -p使用者密碼
    鍵入命令mysql -uroot -p， 回車後提示你輸入密碼，輸入123456，然後回車即可進入到mysql中了，mysql的提示符是：
    mysql>
    注意，如果是連線到另外的機器上，則需要加入一個引數-h機器IP
第三步：增加新使用者
    格式：grant 許可權 on 資料庫.* to 使用者名稱@登入主機 identified by "密碼"
    譬如，增加一個使用者user1密碼為password1，讓其可以在本機上登入， 並對所有資料庫有查詢、插入、修改、刪除的許可權。
	首先用以root使用者連入mysql，然後鍵入以下命令：
    grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
    如果希望該使用者能夠在任何機器上登陸mysql，則將localhost改為"%"。
    如果你不想user1有密碼，可以再打一個命令將密碼去掉。
    grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
第四步： 運算元據庫
    登入到mysql中，然後在mysql的提示符下執行下列命令，每個命令以分號結束

在windows中MySql以服務形式存在，在使用前應確保此服務已經啟動，未啟動可用net start mysql命令啟動。而Linux中啟動時可用“/etc/rc.d/init.d/mysqld start"命令，注意啟動者應具有管理員許可權。剛安裝好的MySql包含一個含空密碼的root帳戶和一個匿名帳戶，這是很大的安全隱患。對於一些重要的應用我們應將安全性儘可能提高，在這裡應把匿名帳戶刪除、 root帳戶設定密碼，可用如下命令進行：

use mysql;
delete from User where User="";
update User set Password=PASSWORD(newpassword) where User=root;
如果要對使用者所用的登入終端進行限制，可以更新User表中相應使用者的Host欄位。改完重啟 mysql 才能生效. ，此時登入時可用如下類似命令：
mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;
上面命令引數是常用引數的一部分，詳細情況可參考文件。此處的mydb是要登入的資料庫的名稱。在進行開發和實際應用中，使用者不應該只用root使用者進行連線資料庫，雖然使用root使用者進行測試時很方便，但會給系統帶來重大安全隱患，也不利於管理技術的提高。我們給一個應用中使用的使用者賦予最恰當的資料庫許可權。如一個只進行資料插入的使用者不應賦予其刪除資料的許可權。MySql的使用者管理是通過User表來實現的，新增新使用者常用的方法有兩個： 一是在User表插入相應的資料行，同時設定相應的許可權；二： 是通過GRANT命令建立具有某種許可權的使用者。其中GRANT的常用用法如下：
grant all on mydb.* to NewUserName@HostName identified by "password" ;
grant usage on *.* to NewUserName@HostName identified by "password";
grant select,insert,update on mydb.* to NewUserName@HostName identified by "password";
grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password";
grant all privileges on *.* to root@localhost 
grant select,insert,delete,update,alter,create,drop on lybbs.* to NewUserName@"%" identified by "lybbs";
若要給此使用者賦予他在相應物件上的許可權的管理能力，可在GRANT後面新增WITH GRANT OPTION選項。而對於用插入User表新增的使用者，Password欄位應用PASSWORD 函式進行更新加密，以防不軌之人竊看密碼。對於那些已經不用的使用者應給予清除，許可權過界的使用者應及時回收許可權，回收許可權可以通過更新User表相應欄位，也可以使用REVOKE操作。

常用許可權的解釋：

全域性管理許可權：
FILE: 在MySQL伺服器上讀寫檔案。
PROCESS: 顯示或殺死屬於其它使用者的服務執行緒。
RELOAD: 過載訪問控制表，重新整理日誌等。
SHUTDOWN: 關閉MySQL服務。
資料庫/資料表/資料列許可權：
ALTER: 修改已存在的資料表(例如增加/刪除列)和索引。
CREATE: 建立新的資料庫或資料表。
DELETE: 刪除表的記錄。
DROP: 刪除資料表或資料庫。
INDEX: 建立或刪除索引。
INSERT: 增加表的記錄。
SELECT: 顯示/搜尋表的記錄。
UPDATE: 修改表中已存在的記錄。

特別的許可權：
ALL: 允許做任何事(和root一樣)。
USAGE: 只允許登入--其它什麼也不允許做。

MySQL 有多個個許可權？ 很明顯總共28個許可權


mysql有哪些許可權？



資料庫層面(db表)的許可權分析


mysql 中存在4個控制許可權的表，分別為 user表，db表，tables_priv表，columns_priv表。

mysql授權表共有5個表：user、db、host、tables_priv和columns_priv。

許可權表的存取過程是：

  1. 先從user表中的Host,User,Password這3個欄位中判斷連線的ip、使用者名稱、密碼是否存在，存在則通過驗證。
  2. 通過身份認證後，進行許可權分配，按照user，db，tables_priv，columns_priv的順序進行驗證。即先檢查全域性許可權表user，如果user中對應的許可權為Y，則此使用者對所有資料庫的許可權都為Y，將不再檢查db, tables_priv,columns_priv；如果為N，則到db表中檢查此使用者對應的具體資料庫，並得到db中為Y的許可權；如果db中為N，則檢查tables_priv中此資料庫對應的具體表，取得表中的許可權Y，以此類推。

以上許可權分三組:
第一組:適用於資料庫、表和列如:alter create delete drop index insert select update
第二組:數管理許可權 它們允許使用者影響伺服器的操作 需嚴格地授權 如:file process reload shut*
第三組:許可權特殊 all意味著“所有許可權” uasge意味著無許可權，即建立使用者，但不授予許可權
columns
許可權運用的列(可選)並且你只能設定列特定的許可權。如果命令有多於一個列，應該用逗號分開它們。
what
許可權運用的級別。許可權可以是全域性,定資料庫或特定表.
user
許可權授予的使用者，由一個使用者名稱和主機名組成,許兩個同名使用者從不同地方連線.預設:mysql使用者password
賦予使用者的口令(可選),如果你對使用者沒有指定identified by子句,該使用者口令不變.
用identified by時，口令字串用改用口令的字面含義,grant將為你編碼口令.
注:set password使用password()函式
with grant option
使用者可以授予許可權通過grant語句授權給其它使用者(可選)

例項講解:
grant all on db_book.* to huaying@koowo.com identified by “yeelion” 只能在本地連線
grant all on db_book.* to huaying@vpn.koowo.com identified by “yeeliong” 允許從此域連線
grant all on db_book.* to huaying@% identified by “yeelion” 允許從任何主機連線 注:”%”字元起萬用字元作用，與like模式匹配的含義相同。
grant all on db_book.* to huaying@%.koowo.com identified by “yeelion”; 允許huaying從koowo.com域的任何主機連線
grant all on db_book.* to huaying@192.168.1.189 identified by “yeelion”
grant all on db_book.* to huaying@192.168.1.% identified by “yeelion”
grant all on db_book.* to huaying@192.168.1.0/17 identified by “yeelion”
允許從單IP 段IP或一子網IP登陸
注:有時 使用者@IP 需用引號 如”huaying@192.168.1.0/17″
grant all on *.* to huaying@localhost identified by “yeelion” with grant option
新增超級使用者huaying　可在本地登陸做任何操作.
grant reload on *.* to huaying@localhost identified by “yeelion” 只賦予reload許可權
grant all on db_book to huaying@koowo.com indetified by “yeelion” 所有許可權
grant select on db_book to huaying@% indetified by “yeelion” 只讀許可權
grant select,insert,delete,update on db_book to huaying@koowo.com indetified by “yeelion”
只有select,insert,delete,update的許可權
grant select on db_book.storybook to huaying@localhost indetified by “yeelion”　只對表
grant update (name) on db_book.storybook to huaying@localhost 只對表的name列 密碼不變
grant update (id,name,author) on db_book.storybook to huaying@localhost 只對表的多列
grant all on book.* to “”@koowo.com 允許koowo.com域中的所有使用者使用庫book

grant all on book.* to huaying@%.koowo.com indetified by “yeelion” with grant option
允許huaying對庫book所有表的管理員授權.

2.撤權並刪除使用者

revoke的語法類似於grant語句
to用from取代,沒有indetifed by和with grant option子句. 如下：
revoke privileges (columns) on what from user
user:必須匹配原來grant語句的你想撤權的使用者的user部分。
privileges:不需匹配,可以用grant語句授權,然後用revoke語句只撤銷部分許可權。
revoke語句只刪許可權不刪使用者,撤銷了所有許可權後user表中使用者記錄保留,使用者仍然可以連線伺服器.
要完全刪除一個使用者必須用一條delete語句明確從user表中刪除使用者記錄：
delete from user where user=”huaying”
flush privileges;　過載授權表
注：使用grant和revoke語句時，表自動過載，而你直接修改授權表時不是.

例項：

1.建立資料庫
CREATE DATABASE `fypay` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2.為建立的資料庫增加使用者fypay
grant create,select,insert,update,delete,drop,alter on fypay.* to fypay@”%” identified by “testfpay”;

3.刪除fypay使用者
delete from user where user=”fypay”
drop user fypay@localhost

4.重新整理資料庫
flush privileges;


MySQL 賦予使用者許可權命令的簡單格式可概括為：

grant 許可權 on 資料庫物件 to 使用者

一、grant 普通資料使用者，查詢、插入、更新、刪除 資料庫中所有表資料的權利。

grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%'

或者，用一條 MySQL 命令來替代：

grant select, insert, update, delete on testdb.* to common_user@'%'

二、grant 資料庫開發人員，建立表、索引、檢視、儲存過程、函式。。。等許可權。

grant 建立、修改、刪除 MySQL 資料表結構許可權。

grant create on testdb.* to developer@'192.168.0.%';
grant alter on testdb.* to developer@'192.168.0.%';
grant drop on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 外來鍵許可權。

grant references on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 臨時表許可權。

grant create temporary tables on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 索引許可權。

grant index on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 檢視、檢視檢視原始碼 許可權。

grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 儲存過程、函式 許可權。

grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
grant execute on testdb.* to developer@'192.168.0.%';

三、grant 普通 DBA 管理某個 MySQL 資料庫的許可權。

grant all privileges on testdb to dba@'localhost'

其中，關鍵字 “privileges” 可以省略。

四、grant 高階 DBA 管理 MySQL 中所有資料庫的許可權。

grant all on *.* to dba@'localhost'

五、MySQL grant 許可權，分別可以作用在多個層次上。

1. grant 作用在整個 MySQL 伺服器上：

grant select on *.* to dba@localhost; -- dba 可以查詢 MySQL 中所有資料庫中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有資料庫

2. grant 作用在單個資料庫上：

grant select on testdb.* to dba@localhost; -- dba 可以查詢 testdb 中的表。

3. grant 作用在單個資料表上：

grant select, insert, update, delete on testdb.orders to dba@localhost;

這裡在給一個使用者授權多張表時，可以多次執行以上語句。例如：

grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';
grant select on smp.mo_sms to mo_user@'%' identified by '123345';

4. grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log to dba@localhost;

5. grant 作用在儲存過程、函式上：

grant execute on procedure testdb.pr_add to 'dba'@'localhost'
grant execute on function testdb.fn_add to 'dba'@'localhost'

六、檢視 MySQL 使用者許可權

檢視當前使用者（自己）許可權：

show grants;


檢視其他 MySQL 使用者許可權：

show grants for dba@localhost;



七、撤銷已經賦予給 MySQL 使用者許可權的許可權。

revoke 跟 grant 的語法差不多，只需要把關鍵字 “to” 換成 “from” 即可：

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

八、MySQL grant、revoke 使用者許可權注意事項

1. grant, revoke 使用者許可權後，該使用者只有重新連線 MySQL 資料庫，許可權才能生效。

2. 如果想讓授權的使用者，也可以將這些許可權 grant 給其他使用者，需要選項 “grant option“

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

這個特性一般用不到。實際中，資料庫許可權最好由 DBA 來統一管理。


安全配置需要的常用命令

新建一個使用者並給予相應資料庫的許可權
grant select,insert,update,delete,create,drop privileges on database.* to user@localhost identified by 'passwd';
grant all privileges on database.* to user@localhost identified by 'passwd';

重新整理許可權
flush privileges;

顯示授權
show grants;

移除授權
revoke delete on *.* from 'jack'@'localhost';

刪除使用者
drop user 'jack'@'localhost';

給使用者改名
rename user 'jack'@'%' to 'jim'@'%';

給使用者改密碼
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

刪除資料庫
drop database test;

從資料庫匯出檔案
select * from a into outfile "~/abc.sql"


create：建立資料庫

命令：create database <資料庫名>

注意：建立資料庫之前要先連線Mysql伺服器

例1：建立一個名為xhkdb的資料庫

mysql> create database xhkdb;


例2：建立資料庫並分配使用者
1. create database 資料庫名;

2. grant select,insert,update,delete,create,drop,alter on 資料庫名.* to 資料庫名@localhost identified by '密碼';

3. set password for '資料庫名'@'localhost' = old_password('密碼');

依次執行3個命令完成資料庫建立。注意：命令中的 “密碼”和“資料庫”欄位是需要自己設定的。


show databases：顯示資料庫

命令：show databases （注意：最後有個s）

mysql> show databases;


注意：為了不再顯示的時候亂碼，要修改資料庫預設編碼。以下以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() 設定的字符集。


drop database：刪除資料庫

命令：drop database <資料庫名>

例如：刪除名為 xhkdb的資料庫
mysql> drop database xhkdb;

例子1：刪除一個已經確定存在的資料庫
mysql> drop database drop_database;
Query OK, 0 rows affected (0.00 sec)

例子2：刪除一個不確定存在的資料庫
mysql> drop database drop_database;
ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
//發生錯誤，不能刪除'drop_database'資料庫，該資料庫不存在。
mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 warning (0.00 sec)//產生一個警告說明此資料庫不存在
mysql> create database drop_database;
Query OK, 1 row affected (0.00 sec)
mysql> drop database if exists drop_database;//if exists 判斷資料庫是否存在，不存在也不產生錯誤
Query OK, 0 rows affected (0.00 sec)



use：使用資料庫

命令： use <資料庫名>

例如：如果xhkdb資料庫存在，嘗試存取它：
mysql> use xhkdb;
螢幕提示：Database changed

use 語句可以通告MySQL把db_name資料庫作為預設（當前）資料庫使用，用於後續語句。該資料庫保持為預設資料庫，直到語段的結尾，或者直到釋出一個不同的USE語句：
mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable

使用USE語句為一個特定的當前的資料庫做標記，不會阻礙您訪問其它資料庫中的表。下面的例子可以從db1資料庫訪問作者表，並從db2資料庫訪問編輯表：
mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
-> WHERE author.editor_id = db2.editor.editor_id;

USE語句被設立出來，用於與Sybase相相容。

show databases 可以查詢所有資料庫，如果想跳到其他資料庫，用 "use 其他資料庫名字"命令 就可以跳到其他資料庫。


select：當前連線的資料庫

命令：mysql> select database();

MySQL中SELECT命令類似於其他程式語言裡的print或者write，你可以用它來顯示一個字串、數字、數學表示式的結果等等。

如何使用MySQL中SELECT命令的特殊功能？

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

注意：這裡用到CONCAT()函式，用來把字串串接起來。另外，我們還用到以前學到的AS給結果列'CONCAT(f_name, " ", l_name)'起了個假名。


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



desc：獲取表結構

desc 命令用於獲取資料表結構。

desc命令格式：desc 表名;
同樣  show columns from 表名;   也能獲取資料表結構。

舉例如下：

mysql> desc MyClass;
mysql> show columns from MyClass;


使用MySQL資料庫 desc 表名時，我們看到Key那一欄，可能會有4種值，即 ' '，'PRI'，'UNI'，'MUL'。

  1. 如果 Key 是 空的, 那麼該列值的可以重複, 表示該列沒有索引, 或者是一個非唯一的複合索引的非前導列；
  2. 如果 Key 是 PRI, 那麼該列是主鍵的組成部分；
  3. 如果 Key 是 UNI, 那麼該列是一個唯一值索引的第一列(前導列),並別不能含有空值(NULL)；
  4. 如果 Key 是 MUL, 那麼該列的值可以重複, 該列是一個非唯一索引的前導列(第一列)或者是一個唯一性索引的組成部分但是可以含有空值NULL。

如果對於一個列的定義，同時滿足上述4種情況的多種，比如一個列既是PRI,又是UNI，那麼"desc 表名"的時候，顯示的Key值按照優先順序來顯，PRI->UNI->MUL。那麼此時，顯示PRI。

一個唯一性索引列可以顯示為PRI,並且該列不能含有空值，同時該表沒有主鍵。

一個唯一性索引列可以顯示為MUL, 如果多列構成了一個唯一性複合索引，因為雖然索引的多列組合是唯一的，比如ID+NAME是唯一的，但是沒一個單獨的列依然可以有重複的值，只要ID+NAME是唯一的即可。


drop table：刪除表

命令：drop table <表名>

例如：刪除表名為 MyClass 的表
mysql> drop table MyClass;
drop table 用於取消一個或多個表。您必須有每個表的 drop 許可權。所有的表資料和表定義會被取消，所以使用本語句要小心！

注意：對於一個帶分割槽的表，drop table 會永久性地取消表定義，取消各分割槽，並取消儲存在這些分割槽中的所有資料。drop table 還會取消與被取消的表有關聯的分割槽定義（.par）檔案。

對與不存在的表，使用IF EXISTS用於防止錯誤發生。當使用IF EXISTS時，對於每個不存在的表，會生成一個NOTE。

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


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 每次只能向表中插入一條記錄。

也可以全部寫在一行


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後面的列的順序 必須和目標表中的列的定義順序完全一致 才能完成正確的資料插入，這是一個很容易被忽略的地方，值得注意。


select from：查詢表中的資料

1)、查詢所有行

命令： select <欄位1，欄位2，...> from < 表名 > where < 表示式 >

例如：檢視錶 MyClass 中所有資料
mysql> select * from MyClass;

2）、查詢前幾行資料

例如：檢視錶 MyClass 中前2行資料
mysql> select * from MyClass order by id limit 0,2;

select一般配合where使用，以查詢更精確更復雜的資料。


delete from：刪除表中資料

命令：delete from 表名 where 表示式

例如：刪除表 MyClass中編號為1 的記錄
mysql> delete from MyClass where id=1;

下面是一個刪除資料前後表的對比。

FirstName LastName Age
Peter Griffin 35
Glenn Quagmire 33

下面以PHP程式碼為例刪除 "Persons" 表中所有 LastName='Griffin' 的記錄：
<?php 
   $con = mysql_connect("localhost","peter","abc123"); 
   if (!$con) 
   {
      die('Could not connect: ' . mysql_error()); 
   } 
   mysql_select_db("my_db", $con); 
   mysql_query("DELETE FROM Persons WHERE LastName='Griffin'"); mysql_close($con); 
?>
在這次刪除之後，表是這樣的：
FirstName LastName Age
Glenn Quagmire 33


update set：修改表中資料

語法：update 表名 set 欄位=新值,… where 條件

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


例子1：單表的MySQL UPDATE語句：
update [low_prioriity] [ignore] tbl_name set col_name1=expr1 [, col_name2=expr2 ...] [where where_definition] [order by ...] [limit row_count]

例子2：多表的UPDATE語句：
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

UPDATE語法可以用新值更新原有錶行中的各列。

SET子句指示要修改哪些列和要給予哪些值。

WHERE子句指定應更新哪些行。如果沒有WHERE子句，則更新所有的行。

如果指定了ORDER BY子句，則按照被指定的順序對行進行更新。

LIMIT子句用於給定一個限值，限制可以被更新的行的數目。


alter table：用於在已有的表中新增、修改或刪除列。

要刪除表中的列

命令：ALTER TABLE table_name DROP COLUMN column_name

加索引

mysql> alter table 表名 add index 索引名 (欄位名1[，欄位名2 …]);
例子： mysql> alter table employee add index emp_name (name);

加主關鍵字的索引

mysql> alter table 表名 add primary key (欄位名);
例子： mysql> alter table employee add primary key(id);

加唯一限制條件的索引

mysql> alter table 表名 add unique 索引名 (欄位名);
例子： mysql> alter table employee add unique emp_name2(cardnumber);

刪除某個索引

mysql> alter table 表名 drop index 索引名;
例子： mysql>alter table employee drop index emp_name;

增加欄位：

命令：alter table table_name add field_name field_type 其他;

例如：在表MyClass中新增了一個欄位passtest，型別為int(4)，預設值為0
mysql> alter table MyClass add passtest int(4) default '0'

修改原欄位名稱及型別：

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

刪除欄位：

命令：ALTER TABLE table_name DROP field_name;


rename：修改表名

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

例如：在表MyClass名字更改為YouClass
mysql> rename table MyClass to YouClass;

當你執行 RENAME 時，你不能有任何鎖定的表或活動的事務。你同樣也必須有對原初表的 ALTER 和 DROP 許可權，以及對新表的 CREATE 和 INSERT 許可權。

如果在多表更名中，MySQL 遭遇到任何錯誤，它將對所有被更名的表進行倒退更名，將每件事物退回到最初狀態。

RENAME TABLE 在 MySQL 3.23.23 中被加入。

或者 使用下面這個命令重新命名

mysql > alter table t1 rename t2;


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
例如，將aaa庫備份到檔案back_aaa中：
　　[root@test1 root]# cd　/home/data/mysql
　　[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa


用mysqldump命令來備份資料庫
c:/mysql/bin/>mysqldump –u root –p 3306 mysql>d:/backup.sql
執行此語句將把mydb 備份到D盤的backup.sql檔案中

備份多個資料庫表
c:/mysql/bin/>mysqldump –u root –p 3306 school user01 user >d:/backup.sql
此句的意思是把school庫中的user01表和user表的內容和表的定義備份到D盤backup.sql檔案中。

備份所有的資料庫
c:/myql/bin>mysqldump –u root –p 3306 –all –database>d:backup.sql
 
還原Mysql資料庫
c:/mysql/bin/mysql –u root –p 3306 school
還原其中的一個表
mysql> source d:/books.sql;

備份資料庫
shell> mysqldump -h host -u root -p dbname >dbname_backup.sql

恢復資料庫
shell> mysqladmin -h myhost -u root -p create dbname
shell> mysqldump -h host -u root -p dbname < dbname_backup.sql

如果只想卸出建表指令，則命令如下：
shell> mysqladmin -u root -p -d databasename > a.sql

如果只想卸出插入資料的sql命令，而不需要建表命令，則命令如下：
shell> mysqladmin -u root -p -t databasename > a.sql

那麼如果我只想要資料，而不想要什麼sql命令時，應該如何操作呢？
mysqldump -T./ phptest driver

其中，只有指定了-T引數才可以卸出純文字檔案，表示卸出資料的目錄，
./表示當前目錄，即與mysqldump同一目錄。
如果不指定driver 表，則將卸出整個資料庫的資料。
每個表會生成兩個檔案，一個為.sql檔案，包含建表執行。
另一個為.txt檔案，只包含資料，且沒有sql指令。

可將查詢儲存在一個檔案中並告訴mysql從檔案中讀取查詢而不是等待鍵盤輸入。
可利用外殼程式鍵入重定向實用程式來完成這項工作。
例如，如果在檔案my_file.sql 中存放有查詢，可如下執行這些查詢：
例如，如果您想將建表語句提前寫在sql.txt中:
mysql > mysql -h myhost -u root -p database < sql.txt


一個建庫和建表的例項1

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:\\下，然後鍵入以下命令：mysql -uroot -p密碼 < c:\\school.sql
如果成功，空出一行無任何顯示；如有錯誤，會有提示。（以上命令已經除錯，你只要將//的註釋去掉即可使用）。

2、或者進入命令列後使用 mysql> source c:\\school.sql; 也可以將school.sql檔案匯入資料庫中。


一個建庫和建表的例項2

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('''',''glchengang'',''深圳一中'',''1976-10-10'');
insert into teacher values('''',''jack'',''深圳一中'',''1975-12-23'');

注：在建表中

    1、將ID設為長度為3的數字欄位:int(3)；並讓它每個記錄自動加一:auto_increment；並不能為空:not null；而且讓他成為主欄位primary key。
    2、將NAME設為長度為10的字元欄位
    3、將ADDRESS設為長度50的字元欄位，而且預設值為深圳。
    4、將YEAR設為日期欄位。


MySQL的幾個重要目錄

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的目錄）

其它：
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 





相關文章