天天看点
返回

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区别
下一篇: 隐私声明

继续阅读