??绔???褰?

??瑷?
涓???杩??ュ??MySQL ?版??搴?
- 1.1??杩??ュ?版???轟??? MySQL
- 1.2??杩??ュ?拌?绋?涓繪?轟??? MySQL
浜???????MySQL ?戒護
涓???淇???MySQL 瀵???
- 3.1????缁? root ?ㄦ?峰??涓?瀵??? ab12
- 3.2????灏? root ?ㄦ?風??瀵????逛負 djg345
????澧????扮?ㄦ??ul>
4.1??澧???涓?涓???浠ュ?ㄤ換浣?涓繪?轟??誨??ㄦ??test1锛?瀵???涓?abc锛?骞跺?規?????版??搴????ヨ???????ャ??淇??廣?????ょ??????
4.2??澧???涓?涓?????浠ュ??localhost 涓??誨??ㄦ??test2锛?瀵???涓?abc锛?骞跺??浠ュ?規?版??搴? mydb 杩?琛??ヨ???????ャ??淇??廣?????ょ????浣?

浜?????寤烘?扮???版??搴?

?????劇ず瀛??ㄧ???版??搴?

6.1???版??搴?缂???璁劇疆

涓??????ゆ?版??搴?

????杩??ユ?版??搴?

涔????ョ??褰??????╃???版??搴?

9.1???ョ???劇ず MySQL ??????
9.2???ョ???劇ず褰????堕??/li>
9.3???ョ???劇ず褰???骞存????/li>
9.4???ョ???劇ず瀛?绗?覆
9.5??褰?璁＄???ㄧ??/li>
9.6??涓叉?ュ??绗?覆

??????寤烘?版??琛?/li>

??涓????ョ??琛ㄧ??瀛?娈典俊??

??浜??????ゆ?版??琛?/li>

??涓???琛ㄤ腑???ユ?版??

13.1?????ョ????缁?涓?锛???浜?灞??х???間負绌?/li>
13.2??涓烘??瀹??????ユ?版??

???????ヨ?㈣〃涓????版??

14.1???ヨ?㈡????琛?
14.2???ヨ?㈠????琛??版??

??浜??????よ〃涓??版??

??????淇??矽〃涓??版??

16.1????琛ㄧ?? MySQL UPDATE 璇???/li>
16.2??澶?琛ㄧ?? MySQL UPDATE 璇???/li>

??涓???澧???瀛?娈?ul>

17.1??澧???绱㈠?

17.1.1????涓誨?抽??瀛???绱㈠?
17.1.2??????涓????舵?′歡??绱㈠?

17.2?????ゆ??涓?绱㈠?

17.3??瀵瑰??娈電????浣?

17.3.1??澧???瀛?娈?/li>
17.3.2??淇??瑰??瀛?娈靛??绉闆??绫誨??
17.3.3?????ゅ??娈?/li>

??????淇??矽〃??

??涔???淇??矽〃??瀹?涔?

浜?????澶?浠芥?版??搴?

20.1??瀵煎?烘?翠釜?版??搴?
20.2???ㄥ?戒護琛?瀵煎??MySQL ?版????excel 琛?/li>
20.3??瀵煎?轟?涓?琛?/li>
20.4??瀵煎?轟?涓??版??搴?缁???
20.5??甯???瑷????闆?煎??/li>

浜???涓???寤哄???寤鴻〃??瀹?渚?

21.1??瀹?渚?涓?
21.2??瀹?渚?浜?

?葷?

??瑷?

MySQL ??涓?绉?寮??炬?浠ｇ?????崇郴???版??搴?绠＄??绯葷?锛?RDBMS锛?锛?浣跨?ㄦ??甯哥?ㄧ???版??搴?绠＄??璇?瑷?--缁??????ヨ?㈣??瑷?锛?SQL锛?杩?琛??版??搴?绠＄????MySQL ??寮??炬?浠ｇ????锛???姝や換浣?浜洪?藉??浠ュ??General Public License ??璁稿??涓?涓?杞藉苟?規??涓??у??????瑕?瀵瑰?惰?琛?淇??廣??MySQL ??涓哄?堕??搴????????у????搴??ц??澶????蟲敞??澶у??頒漢?借?や負?ㄤ???瑕?浜??″??澶????????典?锛?MySQL ??绠＄????瀹規??濂界?????┿??

?充?瀛?? MySQL ???虹??ヨ??璇風Щ姝ユ???????绡?甯?瀛?锛?MySQL 瀛??涓?锛??跺?虹?澧????規?ュ??界?????ラ?ㄤ?????绋?锛??? MySQL 涓?杞藉?闆??锛?

涓???杩??ュ??MySQL ?版??搴?

?烘???煎?濡?涓?锛?

mysql -h涓繪?哄?闆?? -u?ㄦ?峰?? 锛?p?ㄦ?峰????

澶???

1.1??杩??ュ?版???轟??? MySQL

棣?????寮? DOS 绐??ｏ??跺??杩??ョ??褰? mysql\bin锛??????ュ?戒護

mysql -u root -p

锛???杞?????绀轟?杈?瀵?????娉ㄦ???ㄦ?峰??????浠ユ??绌烘?間???浠ユ病??绌烘?鹼?浣???瀵?????蹇?椤繪病??绌烘?鹼?????璁╀????拌??ュ??????

濡?????瀹?瑁?濂?MySQL锛?瓒?绾х?ㄦ??root ??娌℃??瀵?????锛????存?ュ??杞??沖??杩??ュ??MySQL 涓?浜?锛?MySQL ????绀虹????锛?

mysql>

澶???

1.2??杩??ュ?拌?绋?涓繪?轟??? MySQL

??璁捐?绋?涓繪?虹?? IP 涓猴?110.110.110.110锛??ㄦ?峰??涓?root锛?瀵???涓?abcd123???????ヤ互涓??戒護锛?

mysql -h110.110.110.110 -u root -pabcd123

澶???

娉ㄦ??锛?u 涓? root 涔??村??浠ヤ??ㄥ??绌烘?鹼??跺??涔?涓??楓??

浜???????MySQL ?戒護

exit

澶???

涓???淇???MySQL 瀵???

?煎?濡?涓?锛?

mysqladmin -u?ㄦ?峰?? -p?у???? password ?闆????

澶???

3.1????缁? root ?ㄦ?峰??涓?瀵??? ab12

棣?????DOS 涓?杩??ョ??褰? mysql\bin锛??跺?????ヤ互涓??戒護锛?

mysqladmin -u root password ab12

澶???

娉ㄦ??锛???涓哄?濮???root 娌℃??瀵???锛???浠?-p ?у???? 涓?椤瑰氨??浠ョ???ヤ???

3.2????灏? root ?ㄦ?風??瀵????逛負 djg345

mysqladmin -u root -pab12 password djg345

澶???

????澧????扮?ㄦ??/h1>

娉ㄦ??锛???涓??????锛?涓??㈢????涓烘?? MySQL ??澧?涓????戒護锛???浠ュ???㈤?藉甫涓?涓????蜂?涓哄?戒護缁???绗???

?煎?濡?涓?锛?

grant select on ?版??搴?.* to ?ㄦ?峰??@?誨?涓繪??identified by ??瀵?????;

澶???

4.1??澧???涓?涓???浠ュ?ㄤ換浣?涓繪?轟??誨??ㄦ??test1锛?瀵???涓?abc锛?骞跺?規?????版??搴????ヨ???????ャ??淇??廣?????ょ??????

棣?????oot?ㄦ?瘋???ySQL锛??跺?????ヤ互涓??戒護锛?

grant select,insert,update,delete on *.* to [email=test1@??%]test1@??%[/email]?? Identified by ??abc??;

澶???

娉ㄦ??锛?澧??????ㄦ?鋒???????遍?╃??锛?浣??沖???涓?浜虹?ラ?? test1 ??瀵???锛??ｄ?浠?灏卞??浠ュ??internet 涓???浠諱?涓??扮?佃??涓??誨?浣??? mysql ?版??搴?骞跺?逛????版????浠ヤ負??娆蹭負浜?锛?瑙ｅ?沖??娉?瑙? 4.2??

4.2??澧???涓?涓?????浠ュ??localhost 涓??誨??ㄦ??test2锛?瀵???涓?abc锛?骞跺??浠ュ?規?版??搴? mydb 杩?琛??ヨ???????ャ??淇??廣?????ょ????浣?

锛?localhost ?????頒富?猴???MySQL ?版??搴????ㄧ???ｅ?頒富?猴?锛?杩??風?ㄦ?峰?充嬌?ㄧ?ラ?? test2 ??瀵???锛?浠?涔???娉?浠? internet 涓??存?ヨ?塊???版??搴?锛????介??杩? MySQL 涓繪?轟??? web 椤墊?ヨ?塊??浜???

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by ??abc??;

澶???

濡???浣?涓???test2 ??瀵???锛???浠ュ????涓?涓??戒護灏?瀵???娑???锛??戒護濡?涓?锛?

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by ????;

澶???

浜?????寤烘?扮???版??搴?

娉ㄦ??锛???寤烘?版??搴?涔???瑕???杩???MySQL ???″?ㄣ??

?戒護濡?涓?锛?

create database <?版??搴???>;

澶???

渚?1锛?寤虹??涓?涓???涓?xhkdb ???版??搴?锛??戒護濡?涓?锛?

mysql> create database xhkdb;

澶???

渚?2锛???寤烘?版??搴?骞跺?????ㄦ?鳳??戒護濡?涓?锛?

CREATE DATABASE ?版??搴???;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON ?版??搴???.* TO ?版??搴???@localhost IDENTIFIED BY '瀵???';
SET PASSWORD FOR '?版??搴???'@'localhost' = OLD_PASSWORD('瀵???');

澶???

渚?娆℃?ц? 3 涓??戒護瀹????版??搴???寤恒??娉ㄦ??锛?涓??? ??瀵??????????版??搴??????瘋??宸遍??瑕?璁劇疆????

?????劇ず瀛??ㄧ???版??搴?

?戒護濡?涓?锛?锛?娉ㄦ??锛???????涓?s锛?

show databases;

澶???

6.1???版??搴?缂???璁劇疆

涓轟?涓????劇ず???跺??涔辯??锛?瑕?淇??規?版??搴?榛?璁ょ?????浠ヤ?浠?GBK 缂???椤甸??負渚?杩?琛?璇存??锛?

淇???MySQL ????缃???浠訛?my.ini ????慨??code>default-character-set=gbk
浠ｇ??杩?琛??朵慨?癸?

??Java 浠ｇ??锛?

jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk;

澶???

??HP 浠ｇ??锛?

header("Content-Type:text/html;charset=gb2312");

澶???

?? 璇?瑷?浠ｇ??锛?

int mysql_set_character_set( MYSQL * mysql, char * csname);

澶???

璇ュ?芥?扮?ㄤ?涓哄???杩??ヨ?劇疆榛?璁ょ??瀛?绗?????瀛?绗?覆 csname ??瀹?浜?1涓???????瀛?绗?????绉般??杩??ユ?″?規??涓哄??绗?????榛?璁ゆ?″?廣??璇ュ?芥?扮??宸ヤ??瑰?涓? SET NAMES 璇??ョ被浼鹼?浣?瀹?杩??借?劇疆

mysql- > charset

???鹼?浠???褰卞??浜???code>mysql_real_escape_string() 璁劇疆??瀛?绗?????

涓??????ゆ?版??搴?

?戒護濡?涓?锛?

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 <?版??搴???>;

澶???

渚?濡?锛?濡??? 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 ?稿?煎?廣??

??浜?缃??????幫?杩??ヤ互????涔????恒???跺??锛?涓??ㄩ???烘?ワ?use ?版??搴???锛?浣跨??show databases 灏辮?芥?ヨ?㈡?????版??搴?锛?濡????寵煩?闆?朵??版??搴?锛???code>use ?朵??版??搴???瀛?; 灏卞??浠ヤ???

涔????ョ??褰??????╃???版??搴?

?戒護濡?涓?锛?

mysql> select database();

澶???

MySQL 涓? SELECT ?戒護绫諱技浜??朵?缂?绋?璇?瑷????? print ???? write锛?浣???浠ョ?ㄥ???ユ?劇ず涓?涓?瀛?绗?覆???闆?????闆??〃杈懼???缁???绛?绛????ｄ???浠?濡?浣?浣跨??MySQL 涓? SELECT?戒護???規?????斤?

9.1???ョ???劇ず MySQL ??????

mysql> select version(); 
+-----------------------+ 
| version()             | 
+-----------------------+ 
| 6.0.4-alpha-community | 
+-----------------------+ 
1 row in set (0.02 sec)

澶???

9.2???ョ???劇ず褰????堕??/h2>

mysql> select now(); 
+---------------------+ 
| now()               | 
+---------------------+ 
| 2009-09-15 22:35:32 | 
+---------------------+ 
1 row in set (0.04 sec)

澶???

9.3???ョ???劇ず褰???骞存????/h2>

SELECT DAYOFMONTH(CURRENT_DATE); 
+--------------------------+ 
| DAYOFMONTH(CURRENT_DATE) | 
+--------------------------+ 
|                       15 | 
+--------------------------+ 
1 row in set (0.01 sec)

澶???

SELECT MONTH(CURRENT_DATE); 
+---------------------+ 
| MONTH(CURRENT_DATE) | 
+---------------------+ 
|                   1 | 
+---------------------+ 
1 row in set (0.00 sec)

澶???

SELECT YEAR(CURRENT_DATE); 
+--------------------+ 
| YEAR(CURRENT_DATE) | 
+--------------------+ 
|               2020 | 
+--------------------+ 
1 row in set (0.00 sec)

澶???

9.4???ョ???劇ず瀛?绗?覆

mysql> SELECT "welecome to my blog!"; 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
1 row in set (0.00 sec)

澶???

9.5??褰?璁＄???ㄧ??/h2>

select ((4 * 4) / 10 ) + 25; 
+----------------------+ 
| ((4 * 4) / 10 ) + 25 | 
+----------------------+ 
|                26.60 | 
+----------------------+ 
1 row in set (0.00 sec)

澶???

9.6??涓叉?ュ??绗?覆

select CONCAT(f_name, " ", l_name) AS Name 
from employee_data 
where title = 'Marketing Executive'; 
+---------------+ 
| Name          | 
+---------------+ 
| Monica Sehgal | 
| Hal Simlai    | 
| Joseph Irvine | 
+---------------+ 
3 rows in set (0.00 sec)

澶???

娉ㄦ??锛?杩????ㄥ??CONCAT() ?芥?幫??ㄦ?ユ??瀛?绗?覆涓叉?ヨ搗?ャ?????锛???浠?杩??ㄥ?頒互??瀛??扮?? AS 缁?缁?????

'CONCAT(f_name, " ", l_name)'

璧蜂?涓?????????

??????寤烘?版??琛?/h1>

?戒護濡?涓?锛?

create table <琛ㄥ??> ( <瀛?娈靛??1> <绫誨??1> [,..<瀛?娈靛??n> <绫誨??n>]);

澶???

渚?濡?锛?寤虹??涓?涓???涓?yClass??琛??

瀛?娈靛??	?闆??绫誨??	?版??瀹藉害	??????浠ヤ負绌?????富??	???ㄥ???	榛?璁ゅ??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));

澶???

??涓????ョ??琛ㄧ??瀛?娈典俊??

?戒護濡?涓?锛?

mysql> describe table_name;

澶???

??浜??????ゆ?版??琛?/h1>

?戒護濡?涓?锛?

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] 琛ㄥ?? [(????1, ????2, ????3, ...)] values (??, ??, ??, ...);

澶???

璇存??锛??朵腑 [] ??????瀹規????????锛?褰???琛ㄤ腑?????????ユ?版???訛?骞朵???濡????版???煎??琛ㄤ腑????瀹?涔?椤哄?瀹??ㄧ?稿???訛???浠ョ己??[]??

渚?濡?锛?

寰?琛?MyClass 涓??????????ヤ??＄??璁闆?, 杩?浜??¤?闆?琛ㄧず锛?缂??蜂負 1 ????涓?Tom ????缁╀負 96.45, 缂??蜂負 2 ????涓?oan????缁╀負 82.99锛? 缂??蜂負 3 ????涓?Wang ????缁╀負 96.5??

mysql> insert into MyClass values(1,'Tom',1,96.45),(2,'Joan',1,82.99), (3,'Wang', 0,96.59);

澶???

????杞???纭?璁ゅ???ユ??绀?

Query Ok, 1 row affected (0.05 sec)

琛ㄧず?版?????ユ?????? ?ユ???ュけ璐ヨ?鋒??ユ????凡???╅??瑕???浣????版??搴???

13.1?????ョ????缁?涓?锛???浜?灞??х???間負绌?/h2>

??浠ラ???ㄦ?煎??戒護锛?

insert into 琛ㄥ?? values(??锛???锛?NULL锛???锛???;

澶???

琛ㄦ?????ョ??璁闆???绗?涓????????間負NULL??

13.2??涓烘??瀹??????ユ?版??

???舵??浠?瑕?涓烘??瀹??????ユ?版??, ????涓????у????椤哄?杩?琛????? ??琛ㄥ????蹇?椤誨?跺??瑕????ョ??????锛??蜂??煎?濡?涓?:

insert into student (Sno,Sname, Ssex) values(??95007??,??瀛?涓藉????, ??濂斥??);

澶???

杩???student 涓?娌℃?????ュ?蜂??肩??灞??х?????間負绌猴?榛?璁?NULL??

???????ヨ?㈣〃涓????版??

14.1???ヨ?㈡????琛?

?戒護濡?涓?锛?

select <瀛?娈?锛?瀛?娈?锛?...> from < 琛ㄥ?? > where < 琛ㄨ揪寮? >;

澶???

渚?濡?锛?

?ョ??琛?MyClass 涓??????版??锛??戒護濡?涓?锛?

mysql> select * from MyClass;

澶???

14.2???ヨ?㈠????琛??版??

渚?濡?锛??ョ??琛?MyClass 涓??? 2 琛??版??

mysql> select * from MyClass order by id limit 0,2;

澶???

琛ㄧ????缁?浠? 0 寮?濮?缂??鳳?limit ???煎?涓?limit a,b锛?琛ㄦ??浠?绗? a 涓???缁?寮?濮??劇ず锛??誨?辨?劇ず b 涓???缁???

??浜??????よ〃涓??版??

?戒護濡?涓?锛?

delete from 琛ㄥ?? where 琛ㄨ揪寮?;

澶???

select 涓??????? 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 瀛?娈??闆????where ?′歡;

澶???

渚?濡?锛?

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

澶???

16.1????琛ㄧ?? MySQL UPDATE 璇???/h2>

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

澶???

16.2??澶?琛ㄧ?? MySQL UPDATE 璇???/h2>

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

澶???

UPDATE 璇?娉???浠ョ?ㄦ?闆?兼?存?闆????琛ㄨ?涓?????????
SET 瀛??ユ??绀鴻?淇??瑰??浜?????瑕?缁?浜???浜??箋??
WHERE 瀛??ユ??瀹?搴??存?闆??浜?琛???濡???娌℃?? WHERE 瀛??ワ????存?版??????琛???
濡?????瀹?浜? ORDER BY 瀛??ワ??????ц???瀹???椤哄?瀵矽?杩?琛??存?般??
LIMIT 瀛??ョ?ㄤ?缁?瀹?涓?涓????鹼????跺??浠ヨ??存?扮??琛????扮????

??涓???澧???瀛?娈?/h1>

?戒護濡?涓?锛?

alter table 琛ㄥ?? add瀛?娈?绫誨?? ?朵?;

澶???

渚?濡?锛?

?ㄨ〃MyClass涓?娣誨??浜?涓?涓?瀛?娈?asstest锛?绫誨??涓?nt(4)锛?榛?璁ゅ?間負0锛??戒護濡?涓?锛?

mysql> alter table MyClass add passtest int(4) default '0';

澶???

17.1??澧???绱㈠?

mysql> alter table 琛ㄥ?? add index 绱㈠??? (瀛?娈靛??1[锛?瀛?娈靛??2 ??);

澶???

渚?濡?锛?

mysql> alter table employee add index emp_name (name);

澶???

17.1.1????涓誨?抽??瀛???绱㈠?

mysql> alter table 琛ㄥ?? add primary key (瀛?娈靛??);

澶???

渚?濡?锛?

mysql> alter table employee add primary key(id);

澶???

17.1.2??????涓????舵?′歡??绱㈠?

mysql> alter table 琛ㄥ?? add unique 绱㈠??? (瀛?娈靛??);

澶???

渚?濡?锛?

mysql> alter table employee add unique emp_name2(cardnumber);

澶???

17.2?????ゆ??涓?绱㈠?

mysql> alter table 琛ㄥ?? drop index 绱㈠???;

澶???

渚?濡?锛?

mysql>alter table employee drop index emp_name;

澶???

17.3??瀵瑰??娈電????浣?

17.3.1??澧???瀛?娈?/h3>

mysql> ALTER TABLE table_name ADD field_name field_type;

澶???

17.3.2??淇??瑰??瀛?娈靛??绉闆??绫誨??

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

澶???

17.3.3?????ゅ??娈?/h3>

mysql> ALTER TABLE table_name DROP field_name;

澶???

??????淇??矽〃??

?戒護濡?涓?锛?

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 ?抽??瀛?淇??矽〃??锛?????锛???绫誨??锛?娣誨??琛ㄥ??锛????よ〃????

1)alter table ?ц〃?? rename ?拌〃??; --淇??矽〃??  
2)alter table?ц〃??add  column ?闆???? ????绫誨??; --娣誨??琛ㄥ??  
3)alter table?ц〃??drop  column ????; --???よ〃??  
4)alter table?ц〃??modify ???? 瑕?淇??圭??绫誨?? --淇??矽〃??绫誨??  
??alter table test change???? ???? 瑕?淇??圭??绫誨?? 
5)alter table test change column ?у???? ?闆???? ????绫誨??--淇??矽〃????

澶???

浜?????澶?浠芥?版??搴?

20.1??瀵煎?烘?翠釜?版??搴?

?戒護??DOS??[url=file://\mysql\bin] ??褰?涓??ц?锛?瀵煎?烘??浠堕?璁ゆ??瀛???mysql\bin ??褰?涓????戒護濡?涓?锛?

mysqldump -u ?ㄦ?峰?? ??p?ㄦ?峰???? ?版??搴??? > 瀵煎?虹????浠跺??
mysqldump -u user_name -p123456 database_name > outfile_name.sql 锛??ユ??娌℃?????鳳?

澶???

20.2???ㄥ?戒護琛?瀵煎??MySQL ?版????excel 琛?/h2>

???跺????瑕?灏? MySQL ???版??瀵煎?烘?? excel锛?杩?寰?绠???锛?????绗?涓??瑰伐?鳳??存??MySQL ?戒護琛?灏辮??甯??杩??風?????姐??

?戒護???ㄨ???MySQL锛???MySQL ??绀虹??mysql> 涓???浣?锛?骞堕??瀹?琛ㄦ??灞????版??搴????蜂??煎?濡?涓?锛?

SELECT * FROM table_name into outfile 'D:\\nowamagic.xls';

澶???

璇存??锛?????????甯?漿涔?璇?????褰???

褰??朵???浠ュ???ョ?????′歡锛?灏??瑰??????绛????烘?ワ?瀵煎?烘?? excel锛?姣?濡?锛?

SELECT Sname, Sno FROM Student WHERE Ssex = '?? into outfile 'D:\\Student.xls';

澶???

娉ㄦ??锛?濡???浣????版???? utf-8 缂???锛????界?????? excel ??浠剁?存?ユ??寮?浼??虹?頒貢????杩?涓?瑕?绱э??ㄨ?頒?????寮?锛?浠?ANSI 缂????煎?????涓?涓?灏?OK 浜???

20.3??瀵煎?轟?涓?琛?/h2>

mysqldump -u ?ㄦ?峰?? -p ?版??搴??? 琛ㄥ??> 瀵煎?虹????浠跺??
mysqldump -u user_name -p database_name table_name > outfile_name.sql

澶???

20.4??瀵煎?轟?涓??版??搴?缁???

mysqldump -u user_name -p -d ??add-drop-table database_name > outfile_name.sql

澶???

-d 娌℃???版?? ??add-drop-table ?ㄦ??涓?create璇??ヤ???澧???涓?涓?drop table??

20.5??甯???瑷????闆?煎??/h2>

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

澶???

浜???涓???寤哄???寤鴻〃??瀹?渚?

21.1??瀹?渚?涓?

drop database if exists school; //濡???瀛???CHOOL??????create database school; //寤虹??搴?SCHOOL
use school; //??寮?搴?SCHOOL
create table teacher //寤虹??琛?EACHER
(
    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 ??绀虹?????ヤ??㈢???戒護涔???浠ワ?浣?涓??逛究璋?璇???

浣???浠ュ?浠ヤ??戒護???峰???ヤ?涓???????浠朵腑锛???璁句負 school.sql锛??跺??澶??跺??c:\ 涓?锛?骞跺??DOS ?舵??杩??ョ??褰?[url=file://\mysql\bin]\mysql\bin[/url]锛??跺?????ヤ互涓??戒護锛?

mysql -uroot -p瀵??? < c:\\school.sql

澶???

濡???????锛?绌哄?轟?琛???浠諱??劇ず锛?濡?????璇?锛?浼?????绀恒??锛?浠ヤ??戒護宸茬?璋?璇?锛?浣???瑕?灏? // ??娉ㄩ???繪???沖??浣跨?????

????杩??ュ?戒護琛???浣跨?? mysql> source c:\\school.sql; 涔???浠ュ? school.sql ??浠跺?煎?ユ?版??搴?涓???

21.2??瀹?渚?浜?

drop database if exists school; //濡???瀛???CHOOL??????create database school; //寤虹??搴?SCHOOL
use school; //??寮?搴?SCHOOL
create table teacher //寤虹??琛?EACHER
(
    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'');

澶???

璇存??锛?

灏? ID 璁句負?垮害涓?3 ???闆??瀛?娈碉?int(3)锛?骞惰?╁??姣?涓?璁闆????ㄥ??涓?锛?auto_increment锛?骞朵??戒負绌猴?not null锛???涓?璁╁????涓轟富瀛?娈?primary key??
灏? NAME 璁句負?垮害涓?10 ??瀛?绗???娈點??
灏? ADDRESS 璁句負?垮害 50 ??瀛?绗???娈碉???涓?缂虹???間負娣卞?熾??
灏? YEAR 璁句負?ユ??瀛?娈點??

?葷?

????绯葷????葷?浜? MySQL ?版??搴???澧????規?ラ??绾у?戒護骞朵?瀵逛?寮???涓???甯歌?????璇???浜??葷??????堕??浜?涓や釜妗?渚?渚?澶у?跺????锛??朵????充? MySQL ?版??搴?????绔?澶у?跺??浠ョЩ姝ユ????涓????ョ??

??绔???褰?

??瑷?

涓???杩??ュ??MySQL ?版??搴?

1.1??杩??ュ?版???轟??? MySQL

1.2??杩??ュ?拌?绋?涓繪?轟??? MySQL

浜???????MySQL ?戒護

涓???淇???MySQL 瀵???

3.1????缁? root ?ㄦ?峰??涓?瀵??? ab12

3.2????灏? root ?ㄦ?風??瀵????逛負 djg345

????澧????扮?ㄦ??/h1> 娉ㄦ??锛???涓??????锛?涓??㈢????涓烘?? MySQL ??澧?涓????戒護锛???浠ュ???㈤?藉甫涓?涓????蜂?涓哄?戒護缁???绗??? ?煎?濡?涓?锛? grant select on ?版??搴?.* to ?ㄦ?峰??@?誨?涓繪??identified by ??瀵?????; 澶???

4.1??澧???涓?涓???浠ュ?ㄤ換浣?涓繪?轟??誨??ㄦ??test1锛?瀵???涓?abc锛?骞跺?規?????版??搴????ヨ???????ャ??淇??廣?????ょ??????

4.2??澧???涓?涓?????浠ュ??localhost 涓??誨??ㄦ??test2锛?瀵???涓?abc锛?骞跺??浠ュ?規?版??搴? mydb 杩?琛??ヨ???????ャ??淇??廣?????ょ????浣?

浜?????寤烘?扮???版??搴?

?????劇ず瀛??ㄧ???版??搴?

6.1???版??搴?缂???璁劇疆

涓??????ゆ?版??搴?

????杩??ユ?版??搴?

涔????ョ??褰??????╃???版??搴?

9.1???ョ???劇ず MySQL ??????

9.2???ョ???劇ず褰????堕??/h2> mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-09-15 22:35:32 | +---------------------+ 1 row in set (0.04 sec) 澶???

9.4???ョ???劇ず瀛?绗?覆

9.5??褰?璁＄???ㄧ??/h2> select ((4 * 4) / 10 ) + 25; +----------------------+ | ((4 * 4) / 10 ) + 25 | +----------------------+ | 26.60 | +----------------------+ 1 row in set (0.00 sec) 澶???

9.6??涓叉?ュ??绗?覆

??涓????ョ??琛ㄧ??瀛?娈典俊??

??涓???琛ㄤ腑???ユ?版??

13.1?????ョ????缁?涓?锛???浜?灞??х???間負绌?/h2> ??浠ラ???ㄦ?煎??戒護锛? insert into 琛ㄥ?? values(??锛???锛?NULL锛???锛???; 澶??? 琛ㄦ?????ョ??璁闆???绗?涓????????間負NULL??

13.2??涓烘??瀹??????ユ?版??

???????ヨ?㈣〃涓????版??

14.1???ヨ?㈡????琛?

14.2???ヨ?㈠????琛??版??

??浜??????よ〃涓??版??

??????淇??矽〃涓??版??

16.1????琛ㄧ?? MySQL UPDATE 璇???/h2> UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] 澶???

17.1??澧???绱㈠?

17.1.1????涓誨?抽??瀛???绱㈠?

17.1.2??????涓????舵?′歡??绱㈠?

17.2?????ゆ??涓?绱㈠?

17.3??瀵瑰??娈電????浣?

17.3.1??澧???瀛?娈?/h3> mysql> ALTER TABLE table_name ADD field_name field_type; 澶???

17.3.2??淇??瑰??瀛?娈靛??绉闆??绫誨??

17.3.3?????ゅ??娈?/h3> mysql> ALTER TABLE table_name DROP field_name; 澶???

??????淇??矽〃??

??涔???淇??矽〃??瀹?涔?

浜?????澶?浠芥?版??搴?

20.1??瀵煎?烘?翠釜?版??搴?

20.3??瀵煎?轟?涓?琛?/h2> mysqldump -u ?ㄦ?峰?? -p ?版??搴??? 琛ㄥ??> 瀵煎?虹????浠跺?? mysqldump -u user_name -p database_name table_name > outfile_name.sql 澶???

20.4??瀵煎?轟?涓??版??搴?缁???

浜???涓???寤哄???寤鴻〃??瀹?渚?

21.1??瀹?渚?涓?

21.2??瀹?渚?浜?

?葷?

????澧????扮?ㄦ??/h1>

娉ㄦ??锛???涓??????锛?涓??㈢????涓烘?? MySQL ??澧?涓????戒護锛???浠ュ???㈤?藉甫涓?涓????蜂?涓哄?戒護缁???绗???

?煎?濡?涓?锛?

`grant select on ?版??搴?.* to ?ㄦ?峰??@?誨?涓繪??identified by ??瀵?????;`
澶???

9.2???ョ???劇ず褰????堕??/h2>
`mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-09-15 22:35:32 | +---------------------+ 1 row in set (0.04 sec)`
澶???

9.5??褰?璁＄???ㄧ??/h2>
`select ((4 * 4) / 10 ) + 25; +----------------------+ | ((4 * 4) / 10 ) + 25 | +----------------------+ | 26.60 | +----------------------+ 1 row in set (0.00 sec)`
澶???

13.1?????ョ????缁?涓?锛???浜?灞??х???間負绌?/h2>

??浠ラ???ㄦ?煎??戒護锛?

`insert into 琛ㄥ?? values(??锛???锛?NULL锛???锛???;`

澶???

琛ㄦ?????ョ??璁闆???绗?涓????????間負NULL??

16.1????琛ㄧ?? MySQL UPDATE 璇???/h2>
`UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]`
澶???

17.3.1??澧???瀛?娈?/h3>
`mysql> ALTER TABLE table_name ADD field_name field_type;`
澶???

17.3.3?????ゅ??娈?/h3>
`mysql> ALTER TABLE table_name DROP field_name;`
澶???

20.3??瀵煎?轟?涓?琛?/h2>
`mysqldump -u ?ㄦ?峰?? -p ?版??搴??? 琛ㄥ??> 瀵煎?虹????浠跺?? mysqldump -u user_name -p database_name table_name > outfile_name.sql`
澶???