天天看點
傳回

MySQL資料庫程式設計（二）前言

MySQL?版??搴?绋?搴?璁捐?★?浜?锛?

  • ??瑷?
    • MySQL缂?绋?璇?瑷?
      • 缁??????ヨ?㈣??瑷?SQL
      • MySQL璇?瑷?缁???
        • ?版??搴?瀹?涔?璇?瑷?(DDL)
        • ?版????绾佃??瑷?(DML)
        • ?版???у?惰??瑷?(DCL)
        • MySQL?╁?澧?????璇?瑷?瑕?绱?
      • MySQL?芥??/li>
        • ?????芥??/li>
        • ?闆???芥??/li>
        • 瀛?绗?覆?芥??/li>
        • ?ユ?????堕?村?芥??/li>
        • ?朵??芥??/li>
    • ?版??瀹?涔?
      • 瀹?涔??版??搴?
        • ??寤烘?版??搴?
        • ???╀??ョ???版??搴?
        • 淇??規?版??搴?
        • ???ゆ?版??搴?
      • 瀹?涔?琛?/li>
        • MySQL甯哥?ㄦ?版??绫誨??
        • ??寤鴻〃
        • ?ョ??琛?/li>
        • 淇??矽〃
        • ???藉??琛?/li>
        • ???よ〃
      • ?版????瀹??存?х害??
        • 瀹?浣?瀹??存??/li>
        • ???у???存??/li>
        • ?ㄦ?峰??涔???瀹??存??/li>
        • ?存?闆???存?х害??
      • 绔?????褰?

??瑷?

SQL锛?Structured Query Language锛?锛?琚?绉頒負缁??????ヨ?㈣??瑷?锛???????涓繪????崇郴???版??搴?涓??ц??版????浣????版??妫?绱?互???版??搴?缁存?ゆ????瑕???????璇?瑷???瀹?灏??版???ヨ?????版????绾點??浜??℃?у?躲???版??瀹?涔????版???у?堕??浜?涓?韬???涓???澶у???锛?涔???涓?锛??抽??瀛??ㄥぇ??锛?琛ㄥ?????界?ㄥ?????

MySQL缂?绋?璇?瑷?

MySQL?版??搴????″?ㄧ??浜や?瀹?璐???芥????杩?缁??????ヨ?㈣??瑷??ュ???扮??锛???浠?QL????绫繪?版??搴?浜や??瑰????虹???

缁??????ヨ?㈣??瑷?SQL

SQL???烘?????ヨ?㈣??瑷?(Structured Query Language)???辨??棣?瀛?姣?锛?涓??ㄧ?ㄦ?ヤ??版??搴???淇＄??璇?瑷???SQL?卞?澶??抽??瀛?缁?????SQL?鋒??浠ヤ?浼??癸?

  • ??瑕??崇郴?版??搴??芥????SQL锛?????涓???璇?瑷?
  • SQL绠?????瀛?????璇???杩版?у?寮猴???璇??伴??涓?澶?
  • SQL?界?跺?绠???锛?浣?纭???涓?绉?寮烘??????璇?瑷?锛??墊椿浣跨?ㄥ??浠ヨ?琛?澶?????楂?绾х???版??搴???浣?

MySQL璇?瑷?缁???

MySQL?版??搴?????????SQL璇?瑷?????锛??版??搴?瀹?涔?璇?瑷?(DDL)???版??搴???绾佃??瑷?(DML)???版??搴??у?惰??瑷?(DCL)??

?版??搴?瀹?涔?璇?瑷?(DDL)

涓昏??ㄤ?瀵規?版??搴???瀵矽薄杩?琛???寤恒?????ゃ??淇??圭????浣????版??搴?瀵矽薄????锛?琛ㄣ??榛?璁ょ害????瑙?????璇??俱??瑙????ㄣ??瀛??ㄨ?绋?绛???涓昏?SQL璇??ヤ負锛?

  • CREATE锛??ㄤ???寤烘?版??搴????版??搴?瀵矽薄??
  • ALTER锛??ㄤ?瀵規?版??搴????版??搴?瀵矽薄??淇??廣??
  • DROP锛??ㄤ?瀵規?版??搴????版??搴?瀵矽薄?????ゃ??

?版????绾佃??瑷?(DML)

涓昏?瀵規?版??搴?瀵矽薄???版??杩?琛??ヨ????澧????????ゃ??淇??圭????浣???涓昏?SQL璇??ヤ負锛?

  • SELECT锛??ㄤ?琛ㄦ??瑙??劇???版???ヨ??/li>
  • INSERT锛?灏??版?????ヨ〃??瑙??句腑??
  • UPDATE锛?淇??矽〃??瑙??劇???版??锛???浠ヤ慨?逛??★?澶??°???ㄩ?ㄦ?版????
  • DELETE锛????よ〃??瑙??劇???版??锛?涓?瀹?瑕????′歡????浼????ゅ?ㄩ?ㄦ?版????

?版???у?惰??瑷?(DCL)

?ㄤ?瀹??ㄧ?＄??锛?渚?濡??劇ず?版??搴??誨??ㄦ?鋒??????涓昏?SQL璇??ヤ負锛?

  • GRANT锛??ㄤ???浜?????锛???浠ユ????绾佃??瑷???瀹?????????缁??ㄦ?楓??
  • REVOKE锛??ㄤ??跺????浜???????锛?涓?浼?瀵規?版??搴???褰卞????

MySQL?╁?澧?????璇?瑷?瑕?绱?

涓昏?????涓?浜?甯哥?ㄧ??甯擱??????????杩?绠?绗???琛ㄨ揪寮????芥?般??娴?绋??у?跺??娉ㄨВ绛???

锛?1锛?甯擱??

?ㄧ?搴?杩?琛?杩?绋?涓??間?????????

  • 瀛?绗?覆甯擱??锛???寮??鋒????寮??鋒??璧鋒?ョ??瀛?绗????锛???涓?SCII瀛?绗?覆甯擱????Unicode瀛?绗?覆甯擱??
  • ?闆?煎父??锛?????涓烘?存?闆??娴??規?闆父??锛??朵腑?存?頒?甯???扮?癸?娴??ㄥ甫灏??扮?廣??
  • ????杩??跺父??锛?涓?涓?????杩??跺?奸??甯告??瀹?涓轟?涓?瀛?绗?覆甯擱??锛?姣?瀵瑰????杩??舵?闆??琚?杞???負涓?涓?瀛?绗???
  • ?ユ???堕?村父??锛??ㄥ??寮??峰?琛ㄧず?ユ???堕?寸??瀛?绗?覆??
  • 浣?瀛?娈靛?鹼?浣跨????value??锛?value??涓?涓?0??1??浜?杩??跺?箋??
  • 甯?灏??鹼?TRUE??FALSE锛?TRUE???闆???兼??1锛?FALSE???闆???兼??0??

  • NULL?鹼?琛ㄧず娌℃???箋??

    锛?2锛?????

    ?????ㄤ?涓存?跺?????版??锛?????????瀛????版??绫誨??涓や釜灞??э???瀛??ㄤ???璇?????锛??版??????绋?搴?杩?琛???????

    ??ySQL涓???涓虹?ㄦ?峰??????绯葷????????ㄤ嬌?ㄧ?ㄦ?峰?????舵坊??涓?涓???@??绗??鳳??ㄤ?灏??朵???????寮???

    锛?3锛?杩?绠?绗? 甯哥?ㄧ??杩?绠?绗??绠???杩?绠?绗???浣?杩?绠?绗???姣?杈?杩?绠?绗????昏?杩?绠?绗?/li>

  • 绠???杩?绠?绗??+(??)??-(??)??*(涔?)??/(????%(??浣?)??
  • 浣?杩?绠?绗??&(涓?)??|(??)??^(寮???)??~(????)??>>(?崇Щ)??<<(宸?Щ)??
  • 姣?杈?杩?绠?绗??=(绛?浜?)??>(澶т?)??<(灏?浜?)??>=(澶т?绛?浜?)??<=(灏?浜?绛?浜?)??<>(涓?绛?浜?)??!=(涓?绛?浜?)??<=>(?哥????绛?浜?绌???

  • ?昏?杩?绠?绗??NOT??!(??)??AND??&&(涓?)??OR??||(??)??XOR(寮???)??

    锛?4锛?琛ㄨ揪寮?

    琛ㄨ揪寮???甯擱????????????????澶???璁＄????杩?绠?绗????芥?扮??缁?????涓?涓?琛ㄨ揪寮???浠ュ??頒?涓??鹼?琛ㄨ揪寮?涔????版??绫誨????

    锛?5锛???缃??芥?? ??甯稿??浠ヨ??ㄧ郴缁???渚?????缃??芥?版?ュ?規?版??搴?琛ㄨ?琛??稿?蟲??绾點??涓???婦渚???涓?甯哥?ㄧ??锛?

  • ?闆???芥?幫?ABS()?芥?般??SORT()?芥?般??
  • ?????芥?幫?COUNT()?芥?般??
  • 瀛?绗?覆?芥?幫?ASCII()?芥?般??CHAR()?芥?般??
  • ?ユ?????堕?村?芥?幫?NOW()?芥?般??YEAR()?芥?般??
  • ?у?舵?绋??芥?幫?IF()?芥?般??IFNULL()?芥??/li>

MySQL?芥??/h3>

MySQL??渚?????缃??芥?幫???浠ュ府?╃?ㄦ?鋒?村???逛究??澶???琛ㄧ???版????

?????芥??/h4>

?規??涓?缁??版??姹??轟?涓??箋???????芥?扮??缁?????瀵歸??NULL???艱?琛?璁＄??锛?NULL??琚?蹇界?ワ?浠ヤ??㈡?版??涓轟?锛?浠?缁???涓?甯哥?ㄧ???????芥?般??

mysql> select * from tb_student;
+------------+--------------+------+----------+
| student_no | student_name | age  | class_no |
+------------+--------------+------+----------+
|          1 | 寮?涓?         |   17 | 001      |
|          2 | ????         |   18 | 001      |
|          3 | ??浜?         |   17 | 001      |
|          4 | 灏???         |   19 | 002      |
+------------+--------------+------+----------+
4 rows in set (0.02 sec)
锛?1锛?COUNT()?芥?幫??ヨ?㈣〃涓?????琛??般?? 
mysql> select count(*) from tb_student;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
锛?2锛?SUM()?芥?幫?璁＄????涓?瀛?娈電???誨???? 
mysql> select sum(age) from tb_student;
+----------+
| sum(age) |
+----------+
|       71 |
+----------+
1 row in set (0.00 sec)
锛?3锛?AVG()?芥?幫?璁＄????涓?瀛?娈靛鈎???箋?? 
mysql> select avg(age) from tb_student;
+----------+
| avg(age) |
+----------+
|  17.7500 |
+----------+
1 row in set (0.00 sec)
锛?4锛?MAX()?芥?幫??懼?版??涓?瀛?娈電????澶у?箋?? 
mysql> select max(age) from tb_student;
+----------+
| max(age) |
+----------+
|       19 |
+----------+
1 row in set (0.00 sec)
锛?5锛?MIN()?芥?幫??懼?版??涓?瀛?娈電????灏??箋?? 
mysql> select min(age) from tb_student;
+----------+
| min(age) |
+----------+
|       17 |
+----------+
1 row in set (0.00 sec)

?闆???芥??/h4>

涓昏??ㄤ?澶????闆??绫誨????

锛?1锛?ABS()?芥?幫?璁＄????涓?瀛?娈電??缁?瀵瑰?箋??

mysql> select abs(1),abs(-1);
+--------+---------+
| abs(1) | abs(-1) |
+--------+---------+
|      1 |       1 |
+--------+---------+
1 row in set (0.00 sec)
锛?2锛?FLOOR()?芥?幫?杩???灏?浜???绛?浜????扮????澶ф?存?般?? 
mysql> select floor(1.5),floor(-2);
+------------+-----------+
| floor(1.5) | floor(-2) |
+------------+-----------+
|          1 |        -2 |
+------------+-----------+
1 row in set (0.00 sec)
锛?3锛?RAND()?芥?幫?杩???0-1涔??寸?????烘?? 
mysql> select rand(),rand();
+--------------------+--------------------+
| rand()             | rand()             |
+--------------------+--------------------+
| 0.8018397919850795 | 0.6479518717601447 |
+--------------------+--------------------+
1 row in set (0.00 sec)
锛?4锛?TRUNCATE()?芥?幫?淇???灏??扮?瑰????浣??箋?? 
mysql> select truncate(1.23242,3);
+---------------------+
| truncate(1.23242,3) |
+---------------------+
|               1.232 |
+---------------------+
1 row in set (0.00 sec)

瀛?绗?覆?芥??/h4>

涓昏?澶???瀛?绗?覆绫誨????

锛?1锛?UPPER()??UCASE()?芥?幫?灏?瀛?姣?杞???涓哄ぇ????灏?????

mysql> select upper('abc'),ucase('ABC');
+--------------+--------------+
| upper('abc') | ucase('ABC') |
+--------------+--------------+
| ABC          | ABC          |
+--------------+--------------+
1 row in set (0.00 sec)
锛?2锛?LEFT()?芥?幫?杩???瀛?绗?覆??n涓?瀛?绗??? 
mysql> select left('ABC',2);
+---------------+
| left('ABC',2) |
+---------------+
| AB            |
+---------------+
1 row in set (0.00 sec)
(3)SUBSTRING()?芥?幫?????n???垮害??瀛?绗??? 
mysql> select substring('ABC',1,2);
+----------------------+
| substring('ABC',1,2) |
+----------------------+
| AB                   |
+----------------------+
1 row in set (0.00 sec)

?ユ?????堕?村?芥??/h4>

锛?1锛?CURDATE()?芥?幫?杩???褰????ユ????

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-09-10 |
+------------+
1 row in set (0.00 sec)
锛?2锛?CURTIME()?芥?幫?杩???褰????堕?淬?? 
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 23:40:52  |
+-----------+
1 row in set (0.00 sec)
锛?3锛?NOW()?芥?幫?杩???褰????堕?村???ユ???? 
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-09-10 23:40:59 |
+---------------------+
1 row in set (0.00 sec)

?朵??芥??/h4>

?や?涓?杩頒?缁???锛?杩???寰?澶??芥?幫?渚?濡??′歡?ゆ???芥?扮????

锛?1锛?IF()?芥?幫??ゆ??琛ㄨ揪寮???????绔?锛?杩???瀵瑰?缁?????

mysql> select if(1>2,'澶т?','灏?浜?');
+-----------------------+
| if(1>2,'澶т?','灏?浜?') |
+-----------------------+
| 灏?浜?                  |
+-----------------------+
1 row in set (0.00 sec)
锛?2锛?IFNULL()?芥?幫??ゆ???版??????負绌?NULL)?? 
mysql> select ifnull(null,'绌?);
+-------------------+
| ifnull(null,'绌?) |
+-------------------+
| 绌?               |
+-------------------+
1 row in set (0.00 sec)

?版??瀹?涔?

?版??搴???浠ョ??浣???涓?涓?涓??ㄥ???ㄦ?版??瀵矽薄??瀹瑰???涓???富瑕?璁茶В浣跨??QL璇??ュ??寤哄????浣??版??搴???琛ㄣ??

瀹?涔??版??搴?

瀹?瑁?濂?ySQL(??琛??懼害瀹?瑁?MySQL)??锛?灏卞??浠ュ?濮???寤哄??浣跨?ㄦ?版??搴?浜?锛?娑????版??搴?????寤恒?????┿???ョ????淇??瑰?????ゆ??绾點??

??寤烘?版??搴?

??寤烘?版??搴????ㄧ郴缁?纾???涓?????涓????哄???ㄤ??版??瀛??ㄥ??绠＄??锛?MySQL??寤烘?版??搴????烘??璇?娉??煎???锛? 
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] ?版??搴???
[[DEFAULT] CHARACTER SET [=] 瀛?绗?????]
[[DEFAULT] COLLATE [=] ?″?矽???];
璇?娉?璇存??锛?
  • 璇??ヤ腑??[ ]??涓哄????椤廣??
  • 璇??ヤ腑??|??涓洪???╅」锛?浠婚???朵??ョ???SQL璇??ュ?戒護锛??????????崇郴??
  • IF NOT EXISTS锛?琛ㄧず?ㄥ??寤烘?版??搴?涔??????ゆ??锛??ㄤ?瀛??ㄦ?版??搴??????典??ㄨ?琛???寤烘??绾碉??垮????澶???寤哄?艱?撮??璇???
  • CHARACTER SET锛???瀹??版??搴?瀛?绗???锛??垮???虹?頒貢?????碉?姣?濡?gb2312??utf-8??
  • COLLATE 锛???瀹?瀛?绗??″?矽???锛?姣?濡?锛?gb2312_chinese_ci??
渚?瀛?锛???ySQL涓???寤轟?涓?db_class???版??搴?锛? 
CREATE DATABASE IF NOT EXISTS db_class
DEFAULT CHARACTER SET GB2312
DEFAULT COLLATE GB2312_chinese_ci;
?ц?缁???濡?涓???绀猴? 
mysql> CREATE DATABASE IF NOT EXISTS school
    -> DEFAULT CHARACTER SET GB2312
    -> DEFAULT COLLATE GB2312_chinese_ci;
Query OK, 1 row affected (0.01 sec)

??????绠???????寤烘?瑰?

?ㄦ?峰??寤烘?版??搴???瑕??峰??稿?瑰???????锛?涓?杩闆?戒護?ц???????浼???ySQL??data??褰?涓???寤轟?涓?涓??版??搴???绉扮?稿??????浠跺す??

???╀??ョ???版??搴?

?ㄥ????寮???杩?绋??ㄤ???澶?涓??版??搴??????碉?涓????缁?濡?浣?蹇??????ョ???????╂?版??搴???

  1. ?ョ???版??搴?

    褰??版??寰?澶????跺??寰?寰?浼?蹇?璁版??澶?灏?涓??版??搴?锛????寵?頒?澶?娓?妤?姝ｇ‘???版??搴?????浠?涔???

璇?娉??煎?锛?

?ц?缁???濡?涓???绀猴?

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
7 rows in set (0.00 sec)
??浠ョ???幫??版??搴?涓?浠?浠?????浠???寤虹???版??搴?锛?杩?????绯葷????ㄥ??寤虹???版??搴?锛???涓??版??搴???浣??ㄥ?涓?琛ㄦ??绀恒??
?版??搴???绉?/th> ?版??搴?浣???/th>
mysql ??缃??ㄦ?瘋?塊??????
information_schema 淇?瀛?MySQL???″?ㄦ??缁存?ょ???朵??版??搴?淇℃??锛?濡??版??搴??????版??搴???琛ㄣ??琛ㄧ???版??绫誨??涓?璁塊??????绛?
performance_schema ?堕???版??搴????″?ㄦ?ц?藉????/td>
school ??瀹?涔??版??搴?锛??ㄤ?瀛??＄郴缁?绠＄??
  1. ???╂?版??搴?

??杩??戒護?ョ??????浜??版??搴?浜?锛???浠ラ??杩??戒護璺寵漿?版??瀹??版??搴?锛?????浠ュ?規?版??搴???琛ㄣ???版??杩?琛???绉???浣???璇?娉??煎?锛?

?ц?缁???濡?涓???绀猴?

mysql> use school;
Database changed

淇??規?版??搴?

MySQL?版??搴???榛?璁ゅ??绗???涓?latin1锛?榛?璁ゆ?″?矽???涓?latin1_swedish_ci锛???浠ヤ嬌?ㄥ?瑰???璇?娉?瀵瑰?朵慨?廣??

ALTER {DATABASE | SCHEMA} ?版??搴???
[DEFAULT] CHARACTER SET [=] 瀛?绗???
[DEFAULT] COLLATE [=] ?″?矽???;

姣?濡??闆?ㄤ慨??chool?版??搴???瀛?绗???锛??ц?缁???濡?涓???绀猴?

mysql> alter database school
    -> default character set =gb2312
    -> default collate gb2312_chinese_ci;
Query OK, 1 row affected (0.00 sec)

???ゆ?版??搴?

???ゆ?版??搴?灏辨??灏???寤哄ソ???版??搴???浠朵?纾???涓?娓??ゅ???舵?版??涔?浼????ゃ??璇?娉??煎?锛?

姣?濡??闆?ㄥ?school?版??搴????わ??ц?缁???濡?涓???绀猴?

mysql> drop database school;
Query OK, 1 row affected (0.03 sec)

浣跨??how databases;?ョ???版??搴?锛?宸茬?娌℃??school???版??搴?浜?锛?濡?涓???绀猴?

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

??蹇?璇???绯葷????ㄧ???版??搴?锛?????灏?涓??芥?ｅ父杩?琛???

瀹?涔?琛?/h3>

?版??搴???寤烘??????锛?灏卞??浠ュ?ㄦ?版??搴?涓???寤鴻〃浜???琛ㄦ???版??搴?涓?????瑕??????烘?????版??瀵矽薄锛??ユ病??琛???版??搴??朵????版??瀵矽薄灏辨病????涔???琛ㄦ?????ц????????煎??ュ???ㄧ??锛?姣?涓?琛?浠ｈ〃涓?涓?璁闆?锛?姣?涓???浠ｈ〃璁闆???涓?涓?瀛?娈靛???箋????寤鴻〃??杩?绋?灏辨??瀹?涔?姣?涓?瀛?娈電??杩?绋?锛?涔???瀹??芥?版??瀹??存?х害????杩?绋???

MySQL甯哥?ㄦ?版??绫誨??

?版??绫誨??????绯葷?涓?????璁告?版??瀛??ㄧ??绫誨????渚?濡?瀛??鵑??棰???瀛?娈靛?璇ョ?ㄦ?闆?煎??锛?瀛??炬??????浜哄??绛?淇℃??搴?璇ョ?ㄥ??绗???绛?????寤鴻〃?跺?椤諱負琛ㄧ??姣?涓?瀛?娈墊??瀹?姝ｇ‘???版??绫誨???????界???版???垮害???版??绫誨??涓?浠???浠ュ?規?版??杩?琛???搴?锛?瀵逛????歸???????瑕?浣??ㄣ??

MySQL甯哥?ㄧ???版??绫誨?????闆?肩被?????ユ?????堕?淬??瀛?绗?覆绫誨??锛?涓????缁?甯哥?ㄧ????绉?绫誨????

  1. ?闆?肩被??
?闆?肩被?? ??????绗??? ??????绗??? 澶?娉?/th>
BIT 1~64 1~64 浣?瀛?娈電被??锛?榛?璁ら?垮害涓?
TINYINT -128~127 0~255 寰?灏????存??/td>
BOOL??BOOLEAN - - 绛???浜?TINYINT(1)??true涓虹??锛?false涓哄??
INT??INTEGER -2147483648~2147483647 0~4294967295 ????澶у????存??/td>
DOUBLE -1.7976931348623157E+308-2.2250738585072014E-308)锛?0锛?(2.2250738585072014E-3081.7976931348623157E+308 0~(2.2250738585072014E-308锛?1.7976931348623157E+308) ??绮懼害娴??規?闆??/td>
DECIMAL - - 灏??闆?鹼?DECIMAL(M,D) 锛?濡???M>D锛?涓?+2????涓?+2
  1. ?ユ?????堕?寸被??
?闆?肩被?? ????/th> 澶?娉?/th>
DATE 1000-01-01~9999-12-31 ?ユ????锛??煎?涓衡??YYYY-MM-DD??
TIME -838:59:59~838:59:59 ?堕?村??锛??煎?涓衡??HH:mm:ss??
DATETIME 1000-01-01 00:00:00~9999-12-31 23:59:59 ?ユ???堕?村??锛??煎?涓衡??YYYY-MM-DD HH:mm:ss??
YEAR 涓や??煎? 锛?70~69琛ㄧず1970 ~2069锛???浣??煎?锛?1901 ~ 2155 骞達?2浣???3浣?锛??煎?涓衡??YYYY-MM-DD??
TIMESTAMP 1970-01-01 00:00:00~2037 ?堕?存?籌??堕?寸??绉???/td>
  1. 瀛?绗?覆绫誨??
?闆?肩被?? ????/th> 澶?娉?/th>
CHAR 0~255 ?哄???垮害??瀛?绗?被??锛?涓?婊¤凍?垮害瀛?绗??ㄧ┖?間唬??/td>
VARCHAR 0~65535 ?????垮害瀛?绗??瀛??懼?灏?瀛?绗?氨??澶?灏?瀛?绗?/td>
TINYTEXT 255(2^8-1) ???????版??
TEXT 65535(2^16-1) ?挎?????版??

??寤鴻〃

??寤鴻〃浣跨??

CREATE TABLE

璇??ワ??跺?烘??璇?娉??煎?涓猴?

CREATE TABLE 琛ㄥ??(
瀛?娈靛?? ?版??绫誨?? [??绾у???存?х害???′歡] [榛?璁ゅ??,
... ...
)ENGINE = 寮???绫誨??

?ㄥ凡??school?版??搴?涓???寤?b_student锛?骞朵嬌??nnoDB寮???瀛??ㄦ?版????濡?涓???绀猴?

mysql> create table tb_student(
    -> student_no int not null primary key auto_increment,
    -> student_name varchar(20) not null,
    -> age int not null,
    -> class_no varchar(10) default null
    -> )engine=Innodb;
Query OK, 0 rows affected (0.02 sec)

涓??㈠?逛?杩拌???ヨ?琛?璇存??锛?

  1. ??瀹?琛ㄥ????瀛?娈靛??

    ??寤鴻〃?訛?????瀹?褰????版??搴?锛?蹇?椤繪??瀹?琛ㄥ??锛?濡???tb_student??????寤鴻??蹇?椤繪?ユ??

    CREATE

     ????锛?澶?涓?瀛?娈甸??杩?????,)????锛?瀛?娈靛???ㄨ〃????涓???

  2. 瀹??存?х害???′歡

    ??寤鴻〃绀轟???浠ュ??涔?璇ヨ〃???崇??瀹??存?х害???′歡??????瀹?浣?瀹??存?х害??(

    PRIMARY KEY

     ??

    UNIQUE

     )?????闆???存?х害??(

    FOREIGN KEY

     )???ㄦ?瘋??瀹?涔?绾???(

    NOT NULL

     ??

    DEFAULT

     ??

    CHECK

     绾???绛?)??

  3. NULL??NOT NULL

    ?抽??瀛?

    NULL

     ??

    NOT NULL

     ??浠ョ?瀛?娈佃??瀹?涔?绾???锛?

    NULL

     琛ㄧず??蹇?濉?椤瑰??浠ヤ負

    NULL

     ??

    NOT NULL

     琛ㄧず蹇?濉?椤癸?涓???璁鎬負

    NULL

     ????娉ㄦ??

    NULL

     ??绌烘?間??寸???哄??锛?绌烘???? ??)??涓?涓?????瀛?绗?覆锛?

    NULL

     ??涓?涓??抽??瀛???绌哄??????)??
  4. AUTO_INCREMENT

    AUTO_INCREMENT

     ??浠ュ?瀛?娈佃?劇疆涓鴻??澧???涓??鹼??????村?????借?劇疆锛???濮??間負1锛?绱?璁￠??澧?+1??
  5. DEFAULT

    DEFAULT

     琛ㄧず瀛?娈電??榛?璁ゅ?鹼?褰?娌℃???兼?堕?璁や嬌?ㄨ?懼??濂界?????般??

  6. 瀛??ㄥ???绫誨??

    瀛??ㄥ?????MySQL???稿?锛??崇郴?闆?浣?瀛??ㄦ?版????寤虹??绱㈠????存?般???ヨ?㈢????????瀹??版?規?锛??ㄦ?峰??浠ユ?規??涓?????姹????╀???瀛??ㄥ?????

    ??浠ヤ嬌??how engines?ョ???版??搴???????寮???锛?

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

?ョ??琛?/h4>

??寤鴻〃??锛???浠ラ??杩??戒護?ョ?????版??搴???寤虹??????浜?琛ㄥ??琛ㄧ??缁???锛?纭?瀹?琛ㄥ??涔??????ｇ‘??

  1. ?ョ??琛ㄧ????绉?/li>

璇?娉??煎?濡?涓?锛?

?ц?濡?涓???绀猴?

mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| tb_student       |
+------------------+
1 row in set (0.00 sec)

  1. ?ョ??琛ㄧ???

    ??ySQL涓?锛???浠ヤ嬌??code>DESCRIBE/DESC璇??ユ??

    SHOW CLUMNS

     ?ョ??琛ㄧ??缁???锛?????瀛?娈靛?????版??绫誨????榛?璁ゅ?肩????
璇?娉??煎?濡?涓?锛? 
SHOW COLUMNS FROM 琛ㄥ??;
DESC 琛ㄥ??;
?ц?濡?涓???绀猴? 
mysql> show columns from tb_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| age          | int         | NO   |     | NULL    |                |
| class_no     | varchar(10) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc tb_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| age          | int         | NO   |     | NULL    |                |
| class_no     | varchar(10) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

褰??訛?杩???浠ユ?ョ??琛ㄧ??璇??缁???锛??煎?濡?涓?锛?

?ц?濡?涓???绀猴?

mysql> show create table tb_student;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                    |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_student | CREATE TABLE `tb_student` (
  `student_no` int NOT NULL AUTO_INCREMENT,
  `student_name` varchar(20) NOT NULL,
  `age` int NOT NULL,
  `class_no` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`student_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
杩??蜂?浠???浠ユ?ョ????寤鴻〃??璇??璇??ワ?杩???浠ユ?ョ??瀛??ㄥ?????瀛?绗??????

淇??矽〃

???跺?????芥??琛ㄥ??寤哄ソ浜?锛?搴???姹????????瑕?瀵矽〃??涓?浜?璋??翠?淇??廣??
  1. 娣誨??瀛?娈? ?煎?濡?涓?锛?
[

FIRST

 |

AFTER

]锛?琛ㄧず?闆?瀛?娈電??浣?缃?锛???绗?涓?涓?瀛?娈佃????ㄥ凡??瀛?娈靛?????榛?璁ゆ????涓?????

姣?濡?锛???b_student琛ㄤ腑?闆?id_no瀛?娈碉??ц?濡?涓???绀猴?

mysql> alter table tb_student add column id_no varchar(32) not null unique ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tb_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| age          | int         | NO   |     | NULL    |                |
| class_no     | varchar(10) | YES  |     | NULL    |                |
| id_no        | varchar(32) | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
  1. 淇??瑰??娈?

    ALTER TABLE

    ??渚?浜?涓?涓?淇??瑰??娈電??瀛??ワ?????濡?涓?锛?

    锛?1锛?

    CHANGE

     瀛??ャ????浠ヤ慨?瑰???????版??绫誨??锛????舵坊??澶?涓?

    CHANGE

     瀛??ュ???????峰??寮???
姣?濡?锛?淇???b_student琛ㄧ??age瀛?娈墊?逛負student_age绫誨??涓?igint锛?榛?璁ゅ?間負0锛??ц?濡?涓???绀猴? 
mysql> alter table tb_student change column age student_age bigint(20) default 0;
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc tb_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| student_age  | bigint      | YES  |     | 0       |                |
| class_no     | varchar(10) | YES  |     | NULL    |                |
| id_no        | varchar(32) | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
锛?2锛?

ALTER

瀛??ャ??淇??瑰?????ゅ????榛?璁ゅ?箋??

姣?濡?锛?灏?student_no瀛?娈墊?逛負18锛??????ら?璁ゅ?鹼??ц?濡?涓???绀猴?

mysql> alter table tb_student alter column student_age set default '18';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| student_age  | bigint      | YES  |     | 18      |                |
| class_no     | varchar(10) | YES  |     | NULL    |                |
| id_no        | varchar(32) | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table tb_student alter column student_age drop default;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| student_age  | bigint      | YES  |     | NULL    |                |
| class_no     | varchar(10) | YES  |     | NULL    |                |
| id_no        | varchar(32) | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
锛?3锛?

MODIFY

 瀛??ャ??涓?

CHANGE

瀛??ヤ???锛????戒慨?規?版??绫誨????????椤哄???

姣?濡?锛?淇???d_no???版??绫誨???垮害涓?nt绫誨??锛?????lass_no??????

mysql> alter table tb_student modify column id_no int(18) after student_age;
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc tb_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| student_age  | bigint      | YES  |     | NULL    |                |
| id_no        | int         | YES  | UNI | NULL    |                |
| class_no     | varchar(10) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
  1. ???ゅ??娈? 琛ㄧ??瀛?娈佃?澶?锛?DBMS??宸ヤ?璐??蜂?瓒?澶э?????绌洪?翠?浼?澧???????浼????版??搴????跺??浼?璁捐?″?頒?浜?瀛?娈電?????わ?涓??????ゅ??锛?姝ゅ??????瀹逛?浼????わ?璇?娉??煎?涓猴?
姣?濡?锛??闆?ㄧ????绾у?鋒???蟲?????烘?ワ??????闆緩涓?涓?琛??姝ゆ?堕??瑕????ゆ?ゅ??娈碉??ц?濡?涓???绀猴? 
mysql> alter table tb_student drop column class_no;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| student_age  | bigint      | YES  |     | NULL    |                |
| id_no        | int         | YES  | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

???藉??琛?/h4>

???跺?????藉???闆緩琛ㄧ???跺????琛ㄥ???藉????浜?锛???瑕????闆?藉??锛?璇?娉??煎?涓猴?

????

?闆?ㄦ??浠?灏?tb_student?逛負student锛??逛究杈??ワ??ц?濡?涓???绀猴?

mysql> alter table tb_student rename to student;
Query OK, 0 rows affected (0.01 sec)

mysql> rename table tb_student to student;
Query OK, 0 rows affected (0.03 sec)

mysql> desc tb_student;
ERROR 1146 (42S02): Table 'school.tb_student' doesn't exist

mysql> desc student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_no   | int         | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | NULL    |                |
| student_age  | bigint      | YES  |     | NULL    |                |
| id_no        | int         | YES  | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
??浣跨?ㄥ???ョ??琛ㄥ??灏變??ヨ〃涓?瀛??????????浣??芥???ㄦ?扮??琛ㄥ??

???よ〃

褰??版??搴?????浠ュ??锛?浼???寰?澶?搴?寮???琛???叉?㈠???ㄧ┖?達?娴?璐矽?婧?锛???瑕?灏?瀹????わ?璇?娉??煎?涓猴?

??涓?

IF EXISTS

 璇??ラ?叉?㈠???よ〃?舵?ラ??锛????ゅ?涓?琛ㄧ?ㄩ????,)??寮?锛?姣?濡?锛??闆?ㄦ??瑕?????lass_student琛???ц?濡?涓???绀猴? 
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class_student    |
| student          |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table class_student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)
??????琛ㄧ?????ゆ??浣??戒?褰卞??瀵瑰????版??锛???浠ュ???ら??璋ㄦ????

?版????瀹??存?х害??

?崇郴妯″????瀹??存?ц?????瀵瑰?崇郴????绉?绾?????淇?璇??版????姝ｇ‘?у??涓??存?э??崇郴妯″??涓???涓?绫誨???存?х害??锛???????瀹?浣?瀹??存?с?????у???存?с???ㄦ?峰??涔???瀹??存?с??瀹??存?х害???芥?????伴?叉?㈠??ySQL????澶??村??????娉?瀛???锛???楂?瀹??存?ф?娴???????锛???杞誨???浜哄??璐?????

瀹?浣?瀹??存??/h4>

瀹?浣?瀹??存?ф?????崇郴??涓誨??т??戒負绌哄?箋??

  1. 涓婚??绾???

    涓婚????琛ㄤ腑??涓???????浜???????????缁???????MySQL涓?锛?涓婚??蹇?椤婚?靛驚濡?涓?瑙???锛?

    锛?1锛? 姣?涓?琛ㄥ???藉??涔?涓?涓?涓婚????

    锛?2锛? 涓婚?????煎??涓?涓?涓??戒負

    NULL

    ??

    锛?3锛? 澶???涓婚??涓??藉????涓?蹇?瑕???澶?浣?????涔?灏辨??璇村???涓婚?????や?????锛??╀?????浠?婊¤凍??涓??у????锛??ｄ?杩?涓?澶???涓婚????涓?姝ｇ‘??锛?杩?????灏?????????

    锛?4锛? ??????涓???

涓婚??绾???浣跨??code>PRIMARY KEY?ュ???幫???浠ラ??杩???寤鴻〃??淇??矽〃绀烘坊??锛??舵?瑰???涓ょ?锛?涓???互?闆緩涓?涓?class琛ㄦ?ヨ?存??涓ょ????哄??锛?
  • ??绾у???存?х害??锛???瀛?娈靛???㈡坊???抽??瀛??? 
mysql> create table class(
    -> class_no int primary key,
    -> class_count int null,
    -> class_tech_no int not null
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)
  • 琛ㄧ駭瀹??存?х害??锛???琛ㄧ???????㈡坊???抽??瀛??? 
mysql> drop table class;
Query OK, 0 rows affected (0.01 sec)

mysql> create table class(
    -> class_no int,
    -> class_count int null,
    -> class_tech_no int not null,
    -> primary key(class_no)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

  1. 瀹??存?х害?????藉??

    涓??版??搴???琛ㄥ??瑙??句??鳳???浠ュ?瑰???存?х害??杩?琛?娣誨???????ゅ??淇??圭????浣???璇?娉??煎?涓猴?

CONSTRAINT<绾?????>
{
PRIMARY KEY(涓婚??瀛?娈?
|UNIQUE(??????瀛?娈?
|FOREIGN KEY(澶???瀛?娈? REFERENCES ???ц〃(涓婚??瀛?娈?
|CHECK(绾????′歡琛ㄨ揪寮?)
};
姣?濡?锛?缁???绾ц〃??class_teacher_no??寤轟?涓?绾?????涓?K_tech??绾??? 
mysql> drop table class;
Query OK, 0 rows affected (0.01 sec)

mysql> create table class(
    -> class_no int primary key,
    -> class_count int null,
    -> class_tech_no int not null,
    -> constraint PK_tech foreign key(class_tech_no) references tb_tech(tech_no)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)

  1. ??????绾???

    ??????涓?涓婚??涓??鳳??煎?椤誨??涓?锛???璁鎬?涓?

    NULL

     ?鹼???浠ユ??澶?涓???????锛????鋒????绾у??琛ㄧ駭涓ょ?瀹??存?х害??锛?涓??㈠??class琛ㄤ腑缁?class_tech_no娣誨????????绾???锛??ц?濡?涓???绀猴? 
mysql> drop table class;
Query OK, 0 rows affected (0.01 sec)

mysql> create table class(
    -> class_no int primary key,
    -> class_count int null,
    -> class_tech_no int not null unique
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table class;
Query OK, 0 rows affected (0.01 sec)

mysql> create table class(
    -> class_no int,
    -> class_count int null,
    -> class_tech_no int not null,
    -> constraint uq_class UNIQUE(class_tech_no)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

???у???存??/h4>

???у???存?ц???瀹?涔?????澶???涓?涓婚??涔??寸??寮??ㄨ???锛??沖????????兼????涓虹┖锛?????绛?浜?琚????у?崇郴涓???涓?涓婚?????鹼????鋒????绾у??琛ㄧ駭涓ょ?瀹??存?х害????瀹?涔?澶????訛???瑕??靛驚浠ヤ?瑙???锛?

锛?1锛?琚????ц〃蹇?椤誨凡瀛??ㄣ??

锛?2锛?蹇?椤繪??琚????ц〃瀹?涔???涓婚????????????

锛?3锛?澶???????琚????ц〃??涓婚???????????版??绫誨???稿????

姣?濡?锛?缁?class琛ㄧ??class_tech_no瀹?涔?澶???锛??ц?濡?涓???绀猴?

mysql> drop table class;
Query OK, 0 rows affected (0.01 sec)

mysql> create table class(
    -> class_no int primary key,
    -> class_count int null,
    -> class_tech_no int not null references tb_tech(tech_no)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table class;
Query OK, 0 rows affected (0.01 sec)

mysql> create table class(
    -> class_no int primary key,
    -> class_count int null,
    -> class_tech_no int not null,
    -> constraint PK_tech foreign key(class_tech_no) references tb_tech(tech_no)
    -> )engine=InnoDB;
    Query OK, 0 rows affected (0.04 sec)

??class琛ㄤ?瀹?涔?澶???绾?????锛?????褰?tech琛ㄦ病???版?????舵????????lass?版??锛?MySQL??浠ラ??杩?瀹?涔?杩?绾????绛??ワ??ヤ慨?矽?涓?瑙?????

杩?绾????绛??ュ????涓ら?ㄥ??锛?涓?????瀹????у?ㄤ???璇???

UPDATE

 ??

DELETE

 );浜?????瀹??????ㄤ?(

RESTRICT

 ??

CASCADE

 ??

SET NULL

 ??

NO ACTION

 ??

SET DEFAULT

 )锛??朵腑

RESTRICT

 涓洪?璁ゅ?箋???蜂?绛??ュ?涓?锛?
  • RESTRICT锛????剁???ャ??琚????ц〃???版???訛???缁????ゆ??淇??規??浣???
  • CASCADE锛?绾ц??绛??ャ??琚????ц〃???ゆ??淇??矽?闆??訛????ㄥ???ゆ??淇??瑰???ц〃???版????
  • SET NULL锛?缃?绌虹???ャ??琚????ц〃???ゆ??淇??矽?闆??訛????ц〃???版???逛負NULL??
  • NO ACTION锛?涓?????瀹??界???ャ??涓????剁???ョ?稿????

  • SET DEFAULT锛?榛?璁ゅ?肩???ャ??姝ょ???ヨ?姹?瀹?涔?榛?璁ゅ?鹼?琚????ц〃???ゆ??淇??矽?闆??訛?澶???瀛?娈電???間負榛?璁ゅ?箋??

    姣?濡?锛?????b_tech琛ㄦ?訛?瀵瑰???澶???瀛?娈甸???ㄧ疆绌虹???ャ??淇???b_tech琛ㄦ?訛?瀵瑰???澶???瀛?娈甸???ㄧ駭??绛??ャ??

mysql> create table class(
    -> class_no int primary key,
    -> class_count int null,
    -> class_tech_no int
    -> constraint PK_tech foreign key(class_tech_no) references tb_tech(tech_no)
    -> ON DELETE SET NULL
    -> ON UPDATE CASCADE
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from class;
+----------+-------------+---------------+
| class_no | class_count | class_tech_no |
+----------+-------------+---------------+
|        1 |          22 |             1 |
+----------+-------------+---------------+
1 row in set (0.00 sec)

mysql> select * from tb_tech;
+---------+
| tech_no |
+---------+
|       1 |
|       2 |
+---------+
2 rows in set (0.00 sec)

mysql> update tb_tech set tech_no=11 where tech_no=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class;
+----------+-------------+---------------+
| class_no | class_count | class_tech_no |
+----------+-------------+---------------+
|        1 |          22 |            11 |
+----------+-------------+---------------+
1 row in set (0.00 sec)

mysql> delete from tb_tech where tech_no =11;
Query OK, 1 row affected (0.01 sec)

mysql> select * from class;
+----------+-------------+---------------+
| class_no | class_count | class_tech_no |
+----------+-------------+---------------+
|        1 |          22 |          NULL |
+----------+-------------+---------------+
1 row in set (0.00 sec)

?ㄦ?峰??涔???瀹??存??/h4>

?ㄦ?峰??涔?瀹??存?э?婊¤凍浜?搴??ㄧ??璇?涔???姹?锛?MySQL????????绉??ㄦ?峰??涔?瀹??存?э?????????绌虹害????

CHECK

 绾?????瑙????ㄣ??

  1. 璁劇疆??绌虹害??

    ????寤鴻〃????淇??矽〃???跺??缁?瀛?娈靛??涓?

    NOT NULL

     ?抽??瀛?锛??ョ害???????間??戒負绌恒???ц?濡?涓???绀猴? 
mysql> create table tb_student(
    -> student_no int not null primary key auto_increment,
    -> student_name varchar(20) not null,
    -> age int not null,
    -> class_no varchar(10) default null
    -> )engine=Innodb;
Query OK, 0 rows affected (0.02 sec)
    
mysql> alter table tb_student add column id_no varchar(32) not null unique ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

  1. CHECK绾???

    涔???????寤鴻〃????淇??矽〃???跺??缁?瀛?娈靛??涓?

    CHECK

     ?抽??瀛????ц?濡?涓???绀猴? 
mysql> create table tb_student(
    -> student_no int not null primary key auto_increment,
    -> student_name varchar(20) not null,
    -> age int not null CHECK(age >0 and age <25),
    -> class_no varchar(10) default null
    -> )engine=Innodb;
Query OK, 0 rows affected (0.02 sec)

?存?闆???存?х害??

褰?绾???寤虹??涓?绗???瑕?姹??訛???浠ヤ嬌??

ALTER TABLE

 璇??ユ?ユ?存?版???崇害????
  1. ???ょ害??

锛?1锛????や富??绾???

璇?娉??煎?涓猴?

姣?濡?锛?????lass琛ㄧ??class_no??涓婚??绾???锛??ц?濡?涓???绀猴?

mysql> show create table class;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  PRIMARY KEY (`class_no`),
  KEY `PK_tech` (`class_tech_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table class drop primary key;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table class;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  KEY `PK_tech` (`class_tech_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

锛?2锛????ゅ???绾???

璇?娉??煎?涓猴?

姣?濡?锛?????lass琛ㄤ腑class_tech_no??澶???绾???锛??ц?濡?涓???绀猴?

mysql> show create table class;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  PRIMARY KEY (`class_no`),
  KEY `PK_tech` (`class_tech_no`),
  CONSTRAINT `PK_tech` FOREIGN KEY (`class_tech_no`) REFERENCES `tb_tech` (`tech_no`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table class drop foreign key PK_tech;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table class;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  PRIMARY KEY (`class_no`),
  KEY `PK_tech` (`class_tech_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

锛?2锛????ゅ??????绾???

璇?娉??煎?涓猴?

姣?濡?锛?????lass琛?lass_count瀛?娈電????????锛??ц?濡?涓???绀猴?

mysql> show create table class;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  UNIQUE KEY `PK_class_count` (`class_count`),
  KEY `PK_tech` (`class_tech_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table class drop class_count;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table class;
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_tech_no` int DEFAULT NULL,
  KEY `PK_tech` (`class_tech_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  1. 娣誨??绾???

锛?1锛?娣誨??涓婚??绾???

璇?娉??煎?涓猴?

姣?濡?锛?缁?class琛ㄧ??class_no瀛?娈靛??涓?涓婚??绾???锛??ц?濡?涓???绀猴?

mysql> show create table class;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table class add constraint PK_class_no primary key(class_no);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table class;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  PRIMARY KEY (`class_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

锛?2锛?娣誨??澶???绾???

璇?娉??煎?涓猴?

姣?濡?锛?缁?class琛ㄧ??class_tech_no瀛?娈靛??涓?澶???绾???锛??ц?濡?涓???绀猴?

mysql> show create table class;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  PRIMARY KEY (`class_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table class add constraint FK_class_tech_no foreign key(class_tech_no) references tb_tech(tech_no);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table class;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  PRIMARY KEY (`class_no`),
  KEY `FK_class_tech_no` (`class_tech_no`),
  CONSTRAINT `FK_class_tech_no` FOREIGN KEY (`class_tech_no`) REFERENCES `tb_tech` (`tech_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

锛?3锛?娣誨????????绾???

璇?娉??煎?涓猴?

姣?濡?锛?缁?class琛ㄧ??class_count瀛?娈靛??涓?涓婚??绾???锛??ц?濡?涓???绀猴?

mysql> show create table class;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  PRIMARY KEY (`class_no`),
  KEY `FK_class_tech_no` (`class_tech_no`),
  CONSTRAINT `FK_class_tech_no` FOREIGN KEY (`class_tech_no`) REFERENCES `tb_tech` (`tech_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table class add constraint UN_class_count unique key(class_no);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table class;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `class_no` int NOT NULL,
  `class_count` int DEFAULT NULL,
  `class_tech_no` int DEFAULT NULL,
  PRIMARY KEY (`class_no`),
  UNIQUE KEY `UN_class_count` (`class_no`),
  KEY `FK_class_tech_no` (`class_tech_no`),
  CONSTRAINT `FK_class_tech_no` FOREIGN KEY (`class_tech_no`) REFERENCES `tb_tech` (`tech_no`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

绔?????褰?

涓?涓?绡?锛?MySQL?版??搴?绋?搴?璁捐?★?涓?锛?
全國計算機等級二級 MySQL資料庫設計 mysql 資料庫
上一篇: php mysql_connect()，mysqli差別
下一篇: 隐私聲明

繼續閱讀