MySQL常見SQL語句使用方法總結

CREATE?USER?'root'@'%'?IDENTIFIED?BY?'password';

grant?create,select,update,insert,delete,alter?on?bbs.*?to?lvtao@localhost?identified?by?'password';

Grant?all?privileges?on?*.*?to?'root'@'%'?identified?by?'password'?with?grant?option;

GRANT?SELECT,RELOAD,SHOW?DATABASES,LOCK?TABLES,EVENT,REPLICATION?CLIENT??ON?*.*?TO?'bak'@'localhost'?IDENTIFIED?BY?'password';

mysqldump?-u?root?-p?--all-databases?--ignore-database=performance_schema?--ignore-database=information_schema?--skip-lock-tables?>?/home/db.sql

mysqldump?-u?root?-p?-d?–add-drop-table?database?>/home/db.sql

A:常用source?命令  進入mysql數據庫控制臺，  如mysql?-u?root?-p  mysql&gt;use?數據庫  然后使用source命令，后面參數為腳本文件(如這里用到的.sql)  mysql&gt;source?wcnc_db.sql    B:使用mysqldump命令  mysqldump?-u?username?-p?dbname?<p>創建數據庫</p><pre class="brush:sql;toolbar:false;">create?database?;

show?databases;

drop?database?;

use?;

select?database();

show?tables;

create?table??(???[,..?]);    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?表名，或者show?columns?from?表名    mysql>DESCRIBE?MyClass;  mysql>desc?MyClass;?  mysql>show?columns?from?MyClass;

drop?table?    mysql>?drop?table?MyClass;

insert?into??[(?[,..?])]?values?(?值1?)[,?(?值n?)]    mysql>?insert?into?MyClass?values(1,'Tom',96.45),(2,'Joan',82.99),?(2,'Wang',?96.59);

1)、查詢所有行  命令：?select??from??where?  例如：查看表?MyClass?中所有數據  mysql>?select?*?from?MyClass;2）、查詢前幾行數據  例如：查看表?MyClass?中前2行數據  mysql>?select?*?from?MyClass?order?by?id?limit?0,2;  或者：  mysql>?select?*?from?MyClass?limit?0,2;

delete?from?表名?where?表達式    mysql>?delete?from?MyClass?where?id=1;

update?表名?set?字段=新值,…?where?條件    mysql>?update?MyClass?set?name='Mary'?where?id=1;

alter?table?表名?add字段?類型?其他;?    mysql>?alter?table?MyClass?add?passtest?int(4)?default?'0'

rename?table?原表名?to?新表名;?    mysql>?rename?table?MyClass?to?YouClass;

update?表名?set?字段名?=?新內容update?表名?set?字段名?=?replace(字段名,'舊內容','新內容');  文章前面加入4個空格update?article?set?content=concat('　　',content);

update?contents?set?`text`=REPLACE(text,'http://www.lvtao.net','https://www.lvtao.net')