.jpg)
這篇文章主要介紹了ubuntu下mysql 常用指令及中文mysql的解決方法,需要的朋友可以參考下
下面給大家介紹mysql 常用指令及中文亂碼解決問題
Mysql 系統(tǒng)管理指令
登陸本地 Mysql
mysql?-u?username?-p?#?回車輸入密碼
或者
mysql?-u?username?-p?passswd;
登陸遠(yuǎn)程 Mysql
mysql?-h?address?-u?username?-p?#?回車輸入密碼
或者
?mysql?-h?address?-u?username?-p?passswd;
修改新密碼
mysql>?use?mysql; mysql>?update?user?set?password=PASSWORD(‘newpasswd')?where?user='username'; mysql>?flush?privileges;?#更新權(quán)限 mysql>?quit;?#退出
增加新用戶
mysql>?grant?select?on?database_name.*?to?user@address?identified?by?‘passwd'
ex: 增加一個(gè)用戶 test1 密碼為 abc,讓他可以在任何主機(jī)上登錄,并對(duì)所有數(shù)據(jù)庫(kù)有 查詢、插入、修改、刪除的權(quán)限。
mysql>?grant?select,insert,update,delete?on?.?to?root@localhost?identified?by?'mysql'; or?mysql>?grant?all?privileges?on?.?to?root@localhost?identified?by?'mysql'; mysql>?flush?privileges;
ex: 不希望 root 有密碼操作數(shù)據(jù)庫(kù)“mydb”里的數(shù)據(jù)表,可以再打一個(gè)命令將密碼消掉
mysql>?grant?select,insert,update,delete?on?mydb.*?to?root@localhost?identified?by?'';
刪除用戶
mysql>?delete?from?user?where?user='user_name'?and?host='address'; mysql>?flush?privileges; mysql>?drop?database?dbname;?#刪除用戶的數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出(mysqldump)
導(dǎo)出所有數(shù)據(jù)庫(kù)
mysqldump?-u?user?-p?-A?>?backup_file_path
導(dǎo)出數(shù)據(jù)和數(shù)據(jù)結(jié)構(gòu)
mysqldump?-u?user?-p?database_name_need_to_backup?>?backup_file_path ex00:?export?database?mydb mysqldump?-h?localhost?-u?root?-p?mydb?>?./mydb.sql ex01:?export?database?mydb?mytable mysqldump?-h?localhost?-u?root?-p?mydb?mytable?>?./mytable.sql ex02:?export?database?mydb?framework mysqldump?-h?localhost?-u?root?-p?mydb?–add-drop-table?>?./mydb_stru.sql
只導(dǎo)出數(shù)據(jù)不導(dǎo)出數(shù)據(jù)結(jié)構(gòu)
mysqldump?-u?user?-p?-t?database_name_need_to_backup?>?backup_file_path
導(dǎo)出數(shù)據(jù)庫(kù)中的Events
mysqldump?-u?user?-p?-E?database_name_need_to_backup?>?backup_file_path
導(dǎo)出數(shù)據(jù)庫(kù)中的mysql和mysql
mysqldump?-u?user?-p?-R?database_name_need_to_backup?>?backup_file_path
從外部文件mysql中
使用“source”命令
mysql?>?source?path_of_backup_file
使用“
mysql?-u?root?–p?<p><span style="color: #ff0000"><strong>Mysql 常用指令</strong></span></p><p>查看所有數(shù)據(jù)庫(kù)</p><pre class="brush:sql;">mysql>?show?databases;
選擇要操作數(shù)據(jù)庫(kù)
mysql>?use?database_name;
查看當(dāng)前數(shù)據(jù)庫(kù)下所有表
mysql>?show?tables;
獲取表結(jié)構(gòu)
mysql>?desc?table_name;
或者
?mysql>?show?columns?from?table_name;
創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)
mysql>?create?database?database_name;
刪除一個(gè)數(shù)據(jù)庫(kù)
mysql>?drop?database?database_name;
創(chuàng)建一個(gè)表
mysql>?create?table?table_name(?uid?bigint(20)?not?null,?uname?varchar(20)?not?null);
刪除一個(gè)表
mysql>?drop?table?table_name;
SQL插入語句
mysql>?insert?into?table_name(col1,?col2)?values(value1,?value2);
SQL更新語句
mysql>?update?tablename?set?col1='value1',?col2='value2'?where?wheredefinition;
SQL查詢語句
mysql>?select?*?from?table_name?where.......?#(最復(fù)雜的語句)
SQL刪除語句
mysql>?delete?from?table_name?where...
增加表結(jié)構(gòu)的字段
mysql>?alert?table?table_name?add?column?field1?date,?add?column?field2?time?...
刪除表結(jié)構(gòu)的字段
mysql>?alert?table?table_name?drop?field1;
查看表的結(jié)構(gòu)
mysql>?show?columns?from?table_name;
limit 的使用
mysql>?select?*?from?table_name?limit?3;?#每頁(yè)只顯示3行 mysql>?select?*?from?table_name?limit?3,4;?#從查詢結(jié)果的第三個(gè)開始，顯示四項(xiàng)結(jié)果。?此處可很好的用來作分頁(yè)處理。
對(duì)查詢結(jié)果進(jìn)行排序
mysql>?select?*?from?table_name?order?by?field1,?orderby?field2;?#多重排序
查看字符編碼
mysql>?show?variables?like?'character%'?;
Ubuntu mysql中文亂碼解決
打開mysql
sudo?vim?/etc/mysql/my.cnf
找到[mysqld]添加
character-set-server?=?utf8
重啟mysql
/etc/init.d/mysql?restart?或者?service?mysql?restart
.png)
