老男孩mysql dba6期課件第七課數(shù)據(jù)庫設(shè)計

mysql>createtabletemp1(idint(1),id2QueryOK,0rowsaffected(0.01mysql>insertintotemp1 QueryOK,1rowaffected(0.00 mysql> | | 1rowinset(0.00

mysql>altertabletemp1modifyidint(1)QueryOK,0rowsaffected(0.00Records:0Duplicates:0Warnings:mysql>altertabletemp1modifyid2int(20)QueryOK,1rowaffected(0.03Records:1Duplicates:0Warnings:mysql>select*from? | | ? 10|00000000000000000010?+------

Decimal和numeric數(shù)據(jù)類型用來高精度數(shù)據(jù)，一般只在對小數(shù)進行精確計算時才使用， MySQL對decimal字段采用每4個字節(jié)9個數(shù)字的方式，例如decimal(18,9)小數(shù)點兩邊各存 E+38to- E-38,0, E-38to 2.2250738585072014E-3080and2.2250738585072014E-308to ，double使用8個字節(jié)。mysql>createtabletemp2(idfloat(10,2),id2double(10,2),id3mysql>insertintotemp2 QueryOK,2rowsaffected(0.00Records:2Duplicates:0Warnings:mysql>select*from? | | | ? .25 .21 .21 .00 .12 .12? 2rowsinset(0.00 2038-01-1903:14:07，即1000-01-01 -12-31 -01-19 間數(shù)據(jù)，當(dāng)你默認指定currenttimestamp為默認值，或者指定此數(shù)據(jù)列為自CREATETABLEt1tsTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATEdtDATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECREATETABLEt1tsTIMESTAMPDEFAULTCURRENT_TIMESTAMP,dtDATETIMEDEFAULTCURRENT_TIMESTAMPmysql>createtabletemp3(idint,tstampdatetime,tstamp2datetime,tstamp3timestamp,tstamp4QueryOK,0rowsaffected(0.01mysql>insertintotemp3(id)QueryOK,1rowaffected(0.77mysql>insertintotemp3(id)QueryOK,1rowaffected(0.00mysql>select*from? | |tstamp|tstamp2|

| ? |1|||2017-05-1223:48:47|0000-00-0000:00:00|2|||2017-05-1223:48:51|0000-00-0000:00:00

mysql>createtabletemp4(idint,tstampdatetimedefaultcurrent_timestamp,tstamp2datetimedefaultcurrent_timestamp,tstamp3timestamp,tstamp4timestampdefaultcurrent_timestamp);QueryOK,0rowsaffected(0.01mysql>insertintotemp4(id)QueryOK,1rowaffected(0.00mysql>insertintotemp4(id)QueryOK,1rowaffected(0.00mysql>select*from? | | |?

|+

| 1|2017-05-1223:51:42|2017-05-1223:51:42|2017-05-1223:51:42|2017-05-1223:51:42 2|2017-05-1223:51:44|2017-05-1223:51:44|2017-05-1223:51:44|2017-05-1223:51:44? ??mysql>createtabletemp4(idint,tstampdatetimedefaultcurrent_timestamponupdatecurrent_timestamp,tstamp2datetimecurrent_timestamp,tstamp3timestamp,tstamp4timestampdefaultcurrent_timestamponupdatemysql>insertintotemp4(id)mysql>select*from |id| | | | 1|2017-05-1223:55:11|2017-05-1223:55:11|2017-05-1223:55:11|2017-05-1223:55:11 mysql>updatetemp4setmysql>select*from |id| |

| | 2|2017-05-1223:55:27|2017-05-1223:55:11|2017-05-1223:55:27|2017-05-1223:55:27 mysql>updatetemp4setmysql>select*from |id| |

| | 3|2017-05-1223:55:46|2017-05-1223:55:11|2017-05-1223:55:46|2017-05-1223:55:461rowinset(0.00 CREATETABLEt1(tTIME(3),dtmysql>CREATETABLEfractest(c1TIME(2),c2DATETIME(2),c3TIMESTAMP(2)QueryOK,0rowsaffected(0.33mysql>INSERTINTOfractest>('17:51:04.777','2014-09-0817:51:04.777','2014-09-08QueryOK,1rowaffected(0.03mysql>SELECT*FROM? | | | ? |17:51:04.78|2014-09-0817:51:04.78|2014-09-0817:51:04.78? 1rowinset(0.00

mysql>CREATETABLEvc(vVARCHAR(4),cQueryOK,0rowsaffected(0.01mysql>INSERTINTOvcVALUES('ab','abQueryOK,1rowaffected(0.00mysql>SELECTCONCAT('(',v,')'),CONCAT('(',c,')')FROM? |CONCAT('(',v,')')|CONCAT('(',c,')')? |(ab | ? 1rowinset(0.06 mysql>CREATETABLEt(cQueryOK,0rowsaffected(0.01mysql>INSERTINTOtSETc=QueryOK,1rowaffected(0.01mysql>SELECTHEX(c),c='a',c='a\0\0'from? |HEX(c)|c='a'|c='a\0\0'? |610000 0 1? 1rowinset(0.09

CREATETABLEshirtsnamesizeENUM('x-small','small','medium','large','x-INSERTINTOshirts(name,size)VALUES('dressshirt','large'),('t-('poloSELECTname,sizeFROMshirtsWHEREsize=? | | ? |t-shirt|medium? UPDATEshirtsSETsize='small'WHEREsize= selectsize+0fromselectsizefromshirtsorderbymysql>select*fromshirtsorderby? | | ? |poloshirt|small|t- |medium|dressshirt|largeselectsizefromshirtsorderby?mysql>altertableshirtsmodifysizeENUM('x-small','small','medium','large','x-large','xx-?numbersmysql>INSERTINTOt(numbers)mysql>SELECT*FROM? |numbers? | | | ?

mysql>insertintoshirtsERROR1265(01000):Datatruncatedforcolumn'size'atrow mysql>CREATETABLEmyset(colSET('a','b','c',mysql>INSERTINTOmyset(col)->('a,d'),('d,a'),('a,d,a'),('a,d,d'),QueryOK,5rowsaffected(0.01Records:5Duplicates:0Warnings:mysql>SELECTcolFROM? |col? |a,d|a,d|a,d|a,d|a,d ? 5rowsinset(0.04

比如如果知道某個數(shù)字列 盡量避免通常情況下最好指定列為NTNUL。因為如果查詢中包含可為NUL的列，對MySQ更難優(yōu)化，因為可為NUL的列使得索引、索引統(tǒng)計和值比較都更為復(fù)雜。當(dāng)可為L用 的 CREATETABLEanimalsidMEDIUMINTNOTNULLnameCHAR(30)NOTPRIMARYKEYINSERTINTOanimals(name)SELECT*FROM

? |id| ? |1| |2| |3|penguin|4| |5| |6|ostrich?

CREATETABLEanimalsgrp l','bird')NOTidMEDIUMINTNOTNULLnameCHAR(30)NOTPRIMARYKEY)INSERTINTOanimals(grp,name) SELECT*FROManimalsORDERBY

? | |id| ? | |1| | l|1| | l|2| | l|3| ||

|1|penguin|2|ostrich? Engine:Transactions:NOXA:Engine:InnoDBTransactions:YESXA:Engine:MyISAMTransactions:NOXA:

SET CREATETABLEt1(iINT)ENGINE=--SimpletabledefinitionscanbeswitchedfromonetoCREATETABLEt2(iINT)ENGINE=CREATETABLEt3(iINT)ENGINE= ALTERTABLEtENGINE= (crash mysql>CREATETABLEtest FROMlog_tableGROUPBYmysql>SELECTCOUNT(ip),AVG(down)FROMmysql>DROPTABLE mysql>CREATETABLEtest(iINTNOTNULL,cCHAR(10)NOT->ENGINE=QueryOK,0rowsaffected(0.12mysql>INSERTINTOtestVALUES(1,'recordone'),(2,'recordQueryOK,2rowsaffected(0.00Records:2Duplicates:0Warnings:mysql>SELECT*FROM | | 1|recordone 2|recordtwo 2rowsinset(0.00

Archive表支持insert, Blackhole引擎用來接收表插入請求，但不數(shù)據(jù)，所以查詢表數(shù)據(jù)總是返回空，通常用在主從的情況下當(dāng)主庫不想保留數(shù)據(jù)而從庫通過語句執(zhí)行而保留數(shù)據(jù)的情況Blackhole表在磁盤會創(chuàng)建一個文件，.frm文件用來mysql>CREATETABLEtest(iINT,cCHAR(10))ENGINE=QueryOK,0rowsaffected(0.03mysql>INSERTINTOtestVALUES(1,'recordone'),(2,'recordQueryOK,2rowsaffected(0.00Records:2Duplicates:0Warnings:mysql>SELECT*FROMEmptyset(0.00

mysql>CREATETABLEt1 aINTNOTNULLAUTO_INCREMENTPRIMARY messageCHAR(20))mysql>CREATETABLEt2 aINTNOTNULLAUTO_INCREMENTPRIMARY messageCHAR(20))mysql>INSERTINTOt1(message)VALUESmysql>INSERTINTOt2(message)VALUESmysql>CREATETABLEtotal aINTNOTNULL messageCHAR(20), mysql>SELECT*FROM? |a|message? |1|Testing|2| |3| |1|Testing|2| |3| ? CREATETABLEfederated_table INT(20)NOTNULL VARCHAR(32)NOTNULLDEFAULTotherINT(20)NOTNULLDEFAULTPRIMARYKEYINDEXnameINDEXother_key) CREATESERVERFOREIGNDATAWRAPPEROPTIONS(USER'fed_user',HOST'remote_host',PORT9306,DATABASECREATETABLEtest_table INT(20)NOTNULL VARCHAR(32)NOTNULLDEFAULTotherINT(20)NOTNULLDEFAULTPRIMARYKEYINDEXnameINDEXother_key)DEFAULT

mysql>CREATETABLEtest(iINT)ENGINE=QueryOK,0rowsaffected(0.78mysql>INSERTINTOtestERROR1031(HY000):Tablestorageenginefor'test'doesn'thavethismysql>SELECT*FROMEmptyset(0.31

實體和聯(lián)系的類型（1:1，1:n或m:n）建了，但要等到8月份才招生），那么是無法將系名與系