MySQL學(xué)習(xí)筆記-案例

num_from_employee第4章MySQL數(shù)據(jù)類型mysql>USETEST;Databasechangedmysql>CREATETABLEintdata(->aTINYINT(4),->bSMALLINT(6),->cMEDIUMINT(9),->dINT(11),->eBIGINT(20));QueryOK,0rowsaffected(0.41sec)mysql>DESCintdata;+-------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+--------------+------+-----+---------+-------+|a|tinyint(4)|YES||NULL|||b|smallint(6)|YES||NULL|||c|mediumint(9)|YES||NULL|||d|int(11)|YES||NULL|||e|bigint(20)|YES||NULL||+-------+--------------+------+-----+---------+-------+5rowsinset(0.01sec)mysql>INSERTINTOintdataVALUES(1,1,1,1,1);QueryOK,1rowaffected(0.37sec)mysql>SELECT*FROMintdata;+------+------+------+------+------+|a|b|c|d|e|+------+------+------+------+------+|1|1|1|1|1|+------+------+------+------+------+1rowinset(0.03sec)mysql>CREATETABLEintdata2(->aTINYINT(3)UNSIGNEDZEROFILL,->bSMALLINT(5)UNSIGNEDZEROFILL,->cMEDIUMINT(8)UNSIGNEDZEROFILL,->dINT(10)UNSIGNEDZEROFILL,->eBIGINT(20)UNSIGNEDZEROFILL);QueryOK,0rowsaffected(0.38sec)mysql>DROPTABleintdata1;QueryOK,0rowsaffected(0.03sec)mysql>ALTERTABLEintdata2RENAMEintdata1;QueryOK,0rowsaffected(0.34sec)mysql>DESCintdata1;+-------+--------------------------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+--------------------------------+------+-----+---------+-------+|a|tinyint(3)unsignedzerofill|YES||NULL|||b|smallint(5)unsignedzerofill|YES||NULL|||c|mediumint(8)unsignedzerofill|YES||NULL|||d|int(10)unsignedzerofill|YES||NULL|||e|bigint(20)unsignedzerofill|YES||NULL||+-------+--------------------------------+------+-----+---------+-------+5rowsinset(0.01sec)mysql>INSERTINTOintdata1VALUES(1,1,1,1,1);QueryOK,1rowaffected(0.03sec)mysql>SELECT*FROMintdata1;+------+-------+----------+------------+----------------------+|a|b|c|d|e|+------+-------+----------+------------+----------------------+|001|00001|00000001|0000000001|00000000000000000001|+------+-------+----------+------------+----------------------+1rowinset(0.00sec)mysql>CREATETABLEintdata2(->aINT(4),->bINT(11));QueryOK,0rowsaffected(0.05sec)mysql>DESCintdata2;+-------+---------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+---------+------+-----+---------+-------+|a|int(4)|YES||NULL|||b|int(11)|YES||NULL||+-------+---------+------+-----+---------+-------+2rowsinset(0.00sec)mysql>INSERTINTOintdata2VALUES(111111,22222222);QueryOK,1rowaffected(0.03sec)mysql>SELECT*FROMintdata2;+--------+----------+|a|b|+--------+----------+|111111|22222222|+--------+----------+1rowinset(0.00sec)mysql>CREATETABLEfloat1(->aFLOAT(6,2),->bDOUBLE(6,2),->cDECIMAL(6,2));QueryOK,0rowsaffected(0.05sec)mysql>DESCfloat1;+-------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+--------------+------+-----+---------+-------+|a|float(6,2)|YES||NULL|||b|double(6,2)|YES||NULL|||c|decimal(6,2)|YES||NULL||+-------+--------------+------+-----+---------+-------+3rowsinset(0.01sec)mysql>INSERTINTOfloat1VALUES(3.143,3.145,3.1434);QueryOK,1rowaffected,1warning(0.34sec)mysql>SHOWWARNINGS;+-------+------+----------------------------------------+|Level|Code|Message|+-------+------+----------------------------------------+|Note|1265|Datatruncatedforcolumn'c'atrow1|+-------+------+----------------------------------------+1rowinset(0.00sec)mysql>SELECT*FROMfloat1;+------+------+------+|a|b|c|+------+------+------+|3.14|3.15|3.14|+------+------+------+1rowinset(0.00sec)mysql>CREATETABLEfloat2(->aFLOAT,->bDOUBLE,->cDECIMAL(10,0));QueryOK,0rowsaffected(0.07sec)mysql>INSERTINTOfloat2VALUES(3.143,3.145,3.1434);QueryOK,1rowaffected,1warning(0.03sec)mysql>SHOWWARNINGS;+-------+------+----------------------------------------+|Level|Code|Message|+-------+------+----------------------------------------+|Note|1265|Datatruncatedforcolumn'c'atrow1|+-------+------+----------------------------------------+1rowinset(0.00sec)mysql>SELECT*FROMfloat2;+-------+-------+------+|a|b|c|+-------+-------+------+|3.143|3.145|3|+-------+-------+------+1rowinset(0.00sec)mysql>CREATETABLEyear(aYEAR(4));QueryOK,0rowsaffected(0.07sec)mysql>INSERTINTOyearVALUES(1997),('1998'),('1900');ERROR1264(22003):Outofrangevalueforcolumn'a'atrow3mysql>INSERTINTOyearVALUES(1997),('1998'),('1900');ERROR1264(22003):Outofrangevalueforcolumn'a'atrow3mysql>SELECT*FROMYEAR;Emptyset(0.00sec)mysql>INSERTINTOyearVALUES(1997),('1998'),('2154');QueryOK,3rowsaffected(0.03sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMYEAR;+------+|a|+------+|1997||1998||2154|+------+3rowsinset(0.00sec)mysql>INSERTINTOyearVALUES('24'),('86'),('8'),('00');QueryOK,4rowsaffected(0.03sec)Records:4Duplicates:0Warnings:0mysql>SELECT*FROMYEAR;+------+|a|+------+|1997||1998||2154||2024||1986||2008||2000|+------+7rowsinset(0.00sec)mysql>INSERTINTOyearVALUES(24),(86),(8);QueryOK,3rowsaffected(0.03sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMYEAR;+------+|a|+------+|1997||1998||2154||2024||1986||2008||2000||2024||1986||2008|+------+10rowsinset(0.00sec)mysql>CREATETABLEtime(aTIME);QueryOK,0rowsaffected(0.05sec)mysql>INSERTINTOtimeVALUES('223:50:50'),('22:22:22'),('11:11'),('220:20'),('220'),('30');QueryOK,6rowsaffected(0.03sec)Records:6Duplicates:0Warnings:0mysql>SELECT*FROMtime;+----------+|a|+----------+|71:50:50||22:22:22||11:11:00||68:20:00||68:00:00||00:00:30|+----------+6rowsinset(0.00sec)mysql>INSERTINTOtimeVALUES(121212),(131313),('0'),(0);QueryOK,4rowsaffected(0.03sec)Records:4Duplicates:0Warnings:0mysql>SELECT*FROMtime;+----------+|a|+----------+|71:50:50||22:22:22||11:11:00||68:20:00||68:00:00||00:00:30||12:12:12||13:13:13||00:00:00||00:00:00|+----------+10rowsinset(0.00sec)mysql>INSERTINTOtimeVALUES(CURRENT_TIME),(NOW());QueryOK,2rowsaffected(0.05sec)Records:2Duplicates:0Warnings:0mysql>SELECT*FROMtime;+----------+|a|+----------+|71:50:50||22:22:22||11:11:00||68:20:00||68:00:00||00:00:30||12:12:12||13:13:13||00:00:00||00:00:00||22:25:16||22:25:16|+----------+12rowsinset(0.00sec)mysql>CREATETABLEdate(aDATE);QueryOK,0rowsaffected(0.07sec)mysql>INSERTINTOdateVALUES('1949-10-01'),('1950#2#3'),('1951@3@4'),('19520101');QueryOK,4rowsaffected(0.04sec)Records:4Duplicates:0Warnings:0mysql>SELECT*FROMdate;+------------+|a|+------------+|1949-10-01||1950-02-03||1951-03-04||1952-01-01|+------------+4rowsinset(0.00sec)mysql>INSERTINTOdateVALUES('53-01-01'),('78@3@4'),('540101'),('790101');QueryOK,4rowsaffected(0.03sec)Records:4Duplicates:0Warnings:0mysql>SELECT*FROMdate;+------------+|a|+------------+|1949-10-01||1950-02-03||1951-03-04||1952-01-01||2053-01-01||1978-03-04||2054-01-01||1979-01-01|+------------+8rowsinset(0.00sec)mysql>INSERTINTOdateVALUES(20080808),(800101),(800101),(090101),(0);QueryOK,5rowsaffected(0.04sec)Records:5Duplicates:0Warnings:0mysql>SELECT*FROMdate;+------------+|a|+------------+|1949-10-01||1950-02-03||1951-03-04||1952-01-01||2053-01-01||1978-03-04||2054-01-01||1979-01-01||2008-08-08||1980-01-01||1980-01-01||2009-01-01||0000-00-00|+------------+13rowsinset(0.00sec)mysql>INSERTINTOdateVALUES(CURRENT_DATE),(NOW());QueryOK,2rowsaffected,1warning(0.03sec)Records:2Duplicates:0Warnings:1mysql>SELECT*FROMdate;+------------+|a|+------------+|1949-10-01||1950-02-03||1951-03-04||1952-01-01||2053-01-01||1978-03-04||2054-01-01||1979-01-01||2008-08-08||1980-01-01||1980-01-01||2009-01-01||0000-00-00||2014-03-11||2014-03-11|+------------+15rowsinset(0.00sec)mysql>CREATETABLEdatetime(aDATETIME);QueryOK,0rowsaffected(0.08sec)mysql>INSERTINTOdatetimeVALUES('1949-10-0111:11:11'),('1950#2#311+11+11'),('19510101121212');QueryOK,3rowsaffected(0.04sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMdatetime;+---------------------+|a|+---------------------+|1949-10-0111:11:11||1950-02-0311:11:11||1951-01-0112:12:12|+---------------------+3rowsinset(0.00sec)mysql>INSERTINTOdatetimeVALUES('52-01-0111:11:11'),('53#1#111+11+11'),('790101121212');QueryOK,3rowsaffected(0.03sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMdatetime;+---------------------+|a|+---------------------+|1949-10-0111:11:11||1950-02-0311:11:11||1951-01-0112:12:12||2052-01-0111:11:11||2053-01-0111:11:11||1979-01-0112:12:12|+---------------------+6rowsinset(0.00sec)mysql>INSERTINTOdatetimeVALUES(200808080808),(090101080808),(790101080808);QueryOK,3rowsaffected(0.03sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMdatetime;+---------------------+|a|+---------------------+|1949-10-0111:11:11||1950-02-0311:11:11||1951-01-0112:12:12||2052-01-0111:11:11||2053-01-0111:11:11||1979-01-0112:12:12||2020-08-0808:08:08||2009-01-0108:08:08||1979-01-0108:08:08|+---------------------+9rowsinset(0.00sec)mysql>INSERTINTOdatetimeVALUES(NOW());QueryOK,1rowaffected(0.03sec)mysql>SELECT*FROMdatetime;+---------------------+|a|+---------------------+|1949-10-0111:11:11||1950-02-0311:11:11||1951-01-0112:12:12||2052-01-0111:11:11||2053-01-0111:11:11||1979-01-0112:12:12||2020-08-0808:08:08||2009-01-0108:08:08||1979-01-0108:08:08||2014-03-1122:47:33|+---------------------+10rowsinset(0.00sec)mysql>CREATETABLEtimestamp(->aTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP);QueryOK,0rowsaffected(0.06sec)mysql>DESCTIMESTAMP;+-------+-----------+------+-----+-------------------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-----------+------+-----+-------------------+-------+|a|timestamp|NO||CURRENT_TIMESTAMP||+-------+-----------+------+-----+-------------------+-------+1rowinset(0.00sec)mysql>DESCTIMESTAMP\G***************************1.row***************************Field:aType:timestampNull:NOKey:Default:CURRENT_TIMESTAMPExtra:1rowinset(0.00sec)mysql>INSERTINTOtimestampVALUES(0),(NOW());QueryOK,2rowsaffected(0.03sec)Records:2Duplicates:0Warnings:0mysql>SELECT*FROMtimestamp;+---------------------+|a|+---------------------+|0000-00-0000:00:00||2014-03-1123:09:30|+---------------------+2rowsinset(0.00sec)mysql>INSERTINTOtimestampVALUES(CURRENT_TIMESTAMP);QueryOK,1rowaffected(0.03sec)mysql>INSERTINTOtimestampVALUES(NOW());QueryOK,1rowaffected(0.03sec)mysql>INSERTINTOtimestampVALUES(NULL);QueryOK,1rowaffected(0.03sec)mysql>INSERTINTOtimestampVALUES();QueryOK,1rowaffected(0.03sec)mysql>DESCtimestamp;+-------+-----------+------+-----+-------------------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-----------+------+-----+-------------------+-------+|a|timestamp|NO||CURRENT_TIMESTAMP||+-------+-----------+------+-----+-------------------+-------+1rowinset(0.00sec)mysql>SELECT*FROMtimestamp;+---------------------+|a|+---------------------+|0000-00-0000:00:00||2014-03-1123:09:30||2014-03-1123:14:00||2014-03-1123:14:21||2014-03-1123:14:42||2014-03-1123:14:48|+---------------------+6rowsinset(0.00sec)mysql>CREATETABLEstring(aChar(10),->bVARCHAR(10),->ctext);mysql>descstring;+-------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+-------+|a|char(10)|YES||NULL|||b|varchar(20)|YES||NULL|||c|text|YES||NULL||+-------+-------------+------+-----+---------+-------+3rowsinset(0.09sec)CREATETABLEenum(aenum('woman','man'));=CREATETABLEenum(aenum('woman','man')defaultnull);QueryOK,0rowsaffected(0.41sec)mysql>DESCENUM;+-------+---------------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+---------------------+------+-----+---------+-------+|a|enum('woman','man')|YES||NULL||+-------+---------------------+------+-----+---------+-------+1rowinset(0.03sec)mysql>SELECT*FROMenum;Emptyset(0.00sec)mysql>INSERTINTOenumVALUES();QueryOK,1rowaffected(0.09sec)mysql>SELECT*FROMenum;+------+|a|+------+|NULL|+------+1rowinset(0.00sec)mysql>INSERTINTOenumVALUES(NULL);QueryOK,1rowaffected(0.07sec)mysql>SELECT*FROMenum;+------+|a|+------+|NULL||NULL|+------+2rowsinset(0.00sec)mysql>INSERTINTOenumVALUES('woman');QueryOK,1rowaffected(0.05sec)mysql>INSERTINTOenumVALUES('WOMAN');QueryOK,1rowaffected(0.04sec)mysql>INSERTINTOenumVALUES('MAN');QueryOK,1rowaffected(0.06sec)mysql>SELECT*FROMenum;+-------+|a|+-------+|NULL||NULL||woman||woman||man|+-------+5rowsinset(0.00sec)mysql>SHOWWARNINGS\G***************************1.row***************************Level:ErrorCode:1265Message:Datatruncatedforcolumn'a'atrow11rowinset(0.00sec)mysql>SHOWWARNINGS;+-------+------+----------------------------------------+|Level|Code|Message|+-------+------+----------------------------------------+|Error|1265|Datatruncatedforcolumn'a'atrow1|+-------+------+----------------------------------------+1rowinset(0.00sec)mysql>SHOWWARNINGS;+-------+------+-----------------------------------------------------------------------------------------------------------------+|Level|Code|Message|+-------+------+-----------------------------------------------------------------------------------------------------------------+|Error|1064|YouhaveanerrorinyourSQLsyntax;checkthemanualthatcrespondstoyourMySQLserverversionfortherightsyntaxtousenear'WARNINatline1|+-------+------+------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>CREATETABLEenum1(aenum('woman','man')NOTNULL);QueryOK,0rowsaffected(0.06sec)mysql>DESCenum1;+-------+---------------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+---------------------+------+-----+---------+-------+|a|enum('woman','man')|NO||NULL||+-------+---------------------+------+-----+---------+-------+1rowinset(0.02sec)mysql>INSERTINTOenum1VALUES();QueryOK,1rowaffected(0.05sec)mysql>SELECT*FROMenum1;+-------+|a|+-------+|woman|+-------+1rowinset(0.00sec)mysql>INSERTINTOenum1VALUES(null);ERROR1048(23000):Column'a'cannotbenullmysql>CREATETABLEsets(aSET('A','B','C','','')NOTNULL);ERROR1291(HY000):Column'a'hasduplicatedvalue''inSETmysql>CREATETABLEsets1(aSET('A','B','C','D','E'));QueryOK,0rowsaffected(0.09sec)mysql>descsets1;+-------+--------------------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+--------------------------+------+-----+---------+-------+|a|set('A','B','C','D','E')|YES||NULL||+-------+--------------------------+------+-----+---------+-------+1rowinset(0.02sec)mysql>INSERTINTOsetsVALUES('B');QueryOK,1rowaffected(0.06sec)mysql>INSERTINTOsetsVALUES('C','B','D');ERROR1136(21S01):Columncountdoesn'tmatchvaluecountatrow1mysql>INSERTINTOsetsVALUES('C,B,D');QueryOK,1rowaffected(0.08sec)mysql>SELECT*FROMSETS;+-------+|a|+-------+|B,C,D||B||B,C,D|+-------+3rowsinset(0.00sec)mysql>CREATETABLEvb(bBINARY(4),VBVARBINARY(4));QueryOK,0rowsaffected(0.07sec)mysql>INSERTINTOvbVALUES('d','d');QueryOK,1rowaffected(0.05sec)mysql>SELECT*FROMvb;+------+------+|b|VB|+------+------+|d|d|+------+------+1rowinset(0.00sec)mysql>SELECTlength(b),length(vb)FROMvb;+-----------+------------+|length(b)|length(vb)|+-----------+------------+|4|1|+-----------+------------+1rowinset(0.06sec)mysql>SELECTb,b='d',b='d\0\0\0',vb,vb='d',vb='d\0\0\0'FROMvb;+------+-------+-------------+------+--------+--------------+|b|b='d'|b='d\0\0\0'|vb|vb='d'|vb='d\0\0\0'|+------+-------+-------------+------+--------+--------------+|d|0|1|d|1|0|+------+-------+-------------+------+--------+--------------+1rowinset(0.01sec)mysql>CREATETABLEbt(bBIT(4));QueryOK,0rowsaffected(0.09sec)mysql>descbt;+-------+--------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+--------+------+-----+---------+-------+|b|bit(4)|YES||NULL||+-------+--------+------+-----+---------+-------+1rowinset(0.00sec)mysql>CREATETABLEbt(bBIT(4));QueryOK,0rowsaffected(0.09sec)mysql>descbt;+-------+--------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+--------+------+-----+---------+-------+|b|bit(4)|YES||NULL||+-------+--------+------+-----+---------+-------+1rowinset(0.00sec)mysql>INSERTINTObtVALUES(0),(8),(14);QueryOK,3rowsaffected(0.09sec)Records:3Duplicates:0Warnings:0mysql>SELECT*FROMbt;+------+|b|+------+|||||+------+3rowsinset(0.00sec)mysql>SELECTBIN(b+0)FROMbt;+----------+|BIN(b+0)|+----------+|0||1000||1110|+----------+3rowsinset(0.02sec)mysql>SELECTBIN(b)FROMbt;+--------+|BIN(b)|+--------+|0||1000||1110|+--------+3rowsinset(0.00sec)第5章操作數(shù)據(jù)庫mysql>SHOWDATABASES;+--------------------+|Database|+--------------------+|information_schema||drivingschool||example||job||mysql||mysqltest||school||test||triggers||view|+--------------------+11rowsinset(0.12sec)mysql>CREATEDATABASEexample0;QueryOK,1rowaffected(0.33sec)mysql>useexample0Databasechangedmysql>showtables;Emptyset(0.00sec)mysql>DROPDATABASEexample0;QueryOK,0rowsaffected(0.15sec)mysql>showengines\G***************************1.row***************************Engine:MyISAMSupport:YESComment:DefaultengineasofMySQL3.23withgreatperformanceTransactions:NOXA:NOSavepoints:NO***************************2.row***************************Engine:CSVSupport:YESComment:CSVstorageengineTransactions:NOXA:NOSavepoints:NO***************************3.row***************************Engine:MRG_MYISAMSupport:YESComment:CollectionofidenticalMyISAMtablesTransactions:NOXA:NOSavepoints:NO***************************4.row***************************Engine:BLACKHOLESupport:YESComment:/dev/nullstorageengine(anythingyouwritetoitdisappears)Transactions:NOXA:NOSavepoints:NO***************************5.row***************************Engine:FEDERATEDSupport:NOComment:FederatedMySQLstorageengineTransactions:NULLXA:NULLSavepoints:NULL***************************6.row***************************Engine:InnoDBSupport:DEFAULTComment:Supportstransactions,row-levellocking,andforeignkeysTransactions:YESXA:YESSavepoints:YES***************************7.row***************************Engine:ARCHIVESupport:YESComment:ArchivestorageengineTransactions:NOXA:NOSavepoints:NO***************************8.row***************************Engine:MEMORYSupport:YESComment:Hashbased,storedinmemory,usefulfortemporarytablesTransactions:NOXA:NOSavepoints:NO8rowsinset(0.03sec)mysql>showvariableslike'%engine%';+---------------------------+--------+|Variable_name|Value|+---------------------------+--------+|engine_condition_pushdown|ON||storage_engine|InnoDB|+---------------------------+--------+2rowsinset(0.04sec)mysql>showvariableslike'have%';+-------------------------+----------+|Variable_name|Value|+-------------------------+----------+|have_community_features|YES||have_compress|YES||have_crypt|NO||have_csv|YES||have_dynamic_loading|YES||have_geometry|YES||have_innodb|YES||have_ndbcluster|NO||have_openssl|DISABLED||have_partitioning|YES||have_query_cache|YES||have_rtree_keys|YES||have_ssl|DISABLED||have_symlink|YES|+-------------------------+----------+14rowsinset(0.00sec)mysql>showvariableslike'storage_engine';+----------------+--------+|Variable_name|Value|+----------------+--------+|storage_engine|InnoDB|+----------------+--------+1rowinset(0.00sec)第6章創(chuàng)建、修改和刪除表創(chuàng)建表格：mysql>useexampleDatabasechangedmysql>SHOWTABLES;CREATETABLEexample0(idINT,nameVARCHAR(20),sexBOOLEAN);QueryOK,0rowsaffected(0.16sec)mysql>descexample0;+-------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+-------+|id|int(11)|YES||NULL|||name|varchar(20)|YES||NULL|||sex|tinyint(1)|YES||NULL||+-------+-------------+------+-----+---------+-------+3rowsinset(0.02sec)CREATETABLEexample1(stu_idINTPRIMARYKEY,stu_nameVARCHAR(20),stu_sexBOOLEAN);QueryOK,0rowsaffected(0.35sec)mysql>DESCexample1;+----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+-------+|stu_id|int(11)|NO|PRI|NULL|||stu_name|varchar(20)|YES||NULL|||stu_sex|tinyint(1)|YES||NULL||+----------+-------------+------+-----+---------+-------+3rowsinset(0.00sec)mysql>CREATETABLEexample2(stu_idINT,->course_idINT,->gradeFLOAT,->PRIMARYKEY(stu_id,course_id));QueryOK,0rowsaffected(0.08sec)mysql>DESCexample2;+-----------+---------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+---------+------+-----+---------+-------+|stu_id|int(11)|NO|PRI|0|||course_id|int(11)|NO|PRI|0|||grade|float|YES||NULL||+-----------+---------+------+-----+---------+-------+3rowsinset(0.00sec)CREATETABLEexample3(idINTPRIMARYKEY,stu_idINT,course_idINT,CONSTRAINTc_fkFOREIGNKEY(stu_id,course_id)REFERENCESexample2(stu_id,course_id));QueryOK,0rowsaffected(0.11sec)mysql>DESCRIBEexample3;+-----------+---------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+---------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||stu_id|int(11)|YES|MUL|NULL|||course_id|int(11)|YES||NULL||+-----------+---------+------+-----+---------+-------+3rowsinset(0.02sec)CREATETABLEexample4(idINTNOTNULLPRIMARYKEY,nameVARCHAR(20)NOTNULL,stu_idINT,CONSTRAINTd_fkFOREIGNKEY(stu_id)REFERENCESexample1(stu_id));mysql>DESCexample4;+--------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+--------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||name|varchar(20)|NO||NULL|||stu_id|int(11)|YES|MUL|NULL||+--------+-------------+------+-----+---------+-------+3rowsinset(0.00sec)CREATETABLEexample55(idINTPRIMARYKEY,stu_idINTUNIQUE,nameVARCHAR(20)NOTNULL);mysql>descexample5;+--------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+--------+-------------+------+-----+---------+-------+|id|int(11)|NO|PRI|NULL|||stu_id|int(11)|YES|UNI|NULL|||name|varchar(20)|NO||NULL||+--------+-------------+------+-----+---------+-------+3rowsinset(0.00sec)CREATETABLEexample6(idINTPRIMARYKEYAUTO_INCREMENT,stu_idINTUNIQUE,nameVARCHAR(20)NOTNULL);mysql>DESCexample6;+--------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+--------+-------------+------+-----+---------+----------------+|id|int(11)|NO|PRI|NULL|auto_increment||stu_id|int(11)|YES|UNI|NULL|||name|varchar(20)|NO||NULL||+--------+-------------+------+-----+---------+--------