mysql基础知识

《mysql基础知识》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

20134ᨴ1510:17ᦪDataBaseSystem=ᦪᳮ(DBMS,DataBaseManagementSystem)+ᦪ(DataBase)+ᳮᕒDBS=DBMS+DBᜧᳮḄᦔᫀ"ᢥ᯿ᦪ%᪀ᩭ()ஹ+,-ᳮᦪḄᐵ/ᦪ01ᙠᐵ3/4Ḅᦪ஺ᐵ3/6789:;Ḅ<=(>9)"-<=Ḅ@ឋ(>9Ḅ>B"CD)E+ᑮᦪ;G"HI᝞KᜐᳮI<=%᪀஺1.ᦪ%᪀MNO"PQᦪ"%᪀R஺STUVWḄ⊤Y஺2,ᦪZ[ḄᐵMN\]஺<=Z[Ḅὶ஺NosqlNotonlysql_ᐵ/ᦪmongoDB,MemBase`/ᦪᐵ/ᦪḄᐺ/ᭆcᦪdatabseᦪḄd஺⊤tableᦪTE+ᙠ⊤ᑁ"E+ᙠU⊤ᑁḄᦪ"HIᐹᨵhPḄᦪYi஺&ᑡᵨmnoᦪ"ᑡᵨmOᦪYi஺noᑁḄᦪpqᦪḄrUᑡ஺SQLᦪᳮ"ᵨᩭᳮᦪḄst஺%᪀ᓄvwst(SQL,StructuredQueryLanguage)»MySQLᦪ(ᳮ+ᦪ)+yzᔣᦪ|}᝞KᳮḄ~஺MySQLTmC/S᪀Ḅ஺ᡝ/ᘤClient/SERVER20134ᨴ15-08:57ᨬḄhHḄmysql᱐஺+]஺⌱

1Ḅ(:ᘤo:

2ᦪo:installᓽM:

3ᓭlySQLServer5.5Setup□CompletedtheMySQLServer5.5SetupWizardClicktheFinishbuttontoexittheSetupWizard.
ᔣ0LaunchtheMySQLInstanceConfigurationWizard]:

4ᘤQ/lySQLServerInstanceConfigurationVizardMySQLServerInstanceConfigurationConfiguretheMySQLServer5.5serverinstance.Pleaseselectaservertype.Thiswillinfluencememory,diskandCPUusage.pevefoperMachind᛻ᵨThisisadevelopmentmachine,andmanyotherapplicationswillberunonitMySQLServershouldonlyuseaminimalamountofmemory.CServerMachineᘤᘤᓰᵨḄSeveralserverapplicationswillberunningonthismachine.ChooseJthisoptionforweb/applicationservers.MySQLwillhavemediummemoryusage.CDedicatedMySQLServerMachineḄᘤᨵᐸ"Ḅ,ᓰᵨᨬ%ᔆ'ThismachineisdedicatedtorunServer.No£otherservers,suchasawebormailserver,willberun.MySQLwillutilizeuptoallavailablememory.CancelTᔲ¡ᢝ£¤z:

5innodb⊤¥[:¦§ᦪ:

6p¨©\:]windowsᳮhᐵ:

7]ᐰ⌱⚗:

8ᨬ¬᡻]ᓽM:]¬"®¯°஺±²"᝞³}´]ᜫ¶"·ᜧḄMzឋ¸m+ᙠMysql஺¹zᡂ¤஺ᐺ/ḄMN7»ᨵḄᔂ"½¾ᓽM஺¿ᵨ20134ᨴ1511:44(9*+ড-ᨵ9./®_1_ᐹQJ123⌨▼ᓝ78ᦻ:ᜳ<=1>ᦻ:ᜳ?@Aᒹ|E:\amp\mysql\binJMᦻ:Dᦻ:ᜳEFNmyprint.-defaults.exe""1myisam_ftdump.exeGHIJKLᦻ:mysqlḄ᡻o"^mvivaGchlrOVA

9ᳮMySQLᘤÀÁ1,ÂÃwindows±ÄḄᳮÅᡂᡭÇOPQR1T■II■>■ySQLḄUឋWXᙢZ[\I)ᨬWX]\^ᡊ`aឮcdeᐵgJhMySQL■ySQLJMᤊjk᝱ᨴnopqar◞MicrosoftIMHxnXᙢgyJMz\303stuz^MicrosoftShar..xnXᙢ{vUHᜓᵨ^MobilePartner...xnXᙢgyঝIS9}1110tli1.Th♦…xnXᵯg᡻ᦻ:ḄQPJMySQLnnXᙢS|"E'9"ql'bixAoiyxqld"-dtf«ults-fil«=aE\M»p\myxql\»y|NetLogon…xnXᙢgynM&ឤnvᵯNet.TcpPortS…Pro...ἭᵨXᙢ᱐~ÈiᳮwindowsḄMysql:NetstartDNetstopD

10CXDocunentsandSettingsXAdninistrator>netstartMySQLMySQLᙠn.MySQLnᡂ஺CXDocunentsandSettings\fldninistrator>netstopMySQLMySQLᙠs1£.MySQLᡂst஺2,¡ᵨmysqldᩭ5020Adnanixtrator4288AdministratorC\DocunentsandSettings\AdRinistrator>nysqld——defaults-fileses/anp/mysql/ny.i«ditplus.«x«3996Adnirustratorw»iprvx«.tx«3812STSTDInimysqld£^¤£ᵨ¥ᡝ§ᩭ¨©ª«¬஺ᑭᵨmysql.exeᩭª«:ii\>c4•wp/wyvq1ᐜ°¡±²:f:\amp5ysqDbinSysql-hlocalhost-P3306-uroot-p.E\anp

11ysql>bin

12ysql-hlocalhost-P3306-uroot-pEnterpassword********_³I´:nysql>showdatabases
.--------------------------------------♦!DatabaseI.--------------------------------------♦iinfornation_schenaI'mysql••performance_schena
!testI♦--------------------------------------♦4rowsinset<0.02sec)|mysql>

13ÉÊ20134ᨴ1512:03ᡝ/ËÌÍÀ»¿ᵨMicrosoftOneNote2010Î0Uᵨm+ÏᡠᨵÑn-ḄÒ]ÓÔ20134ᨴ1510:16TIPsÕ⌕׿ᵨsÕ%بÎ0ᦪCreatedatabasedb_nameÙᦪ⌱⚗Ú;᪗ݨÞᦪDß~DOᑣᜧáᑏãm6äÓÔ஺ÞåæTçᑖḄßéDêë஺ì₞¿ᵨᑜïi஺

14᪗ݨḄp¨¿ᵨðëp¨"ᦪp"¨B"ñòT;ᦻ஺ôTõᱯ÷Ḅ(ᔠ"Ê᝞ùᦪp(ᔠ"ᱯ÷¨B"ᒹûmysqlTᑁüᐵýpHI¿ᵨ᪗ݨ▲¨ᩭᒹÿ஺▲஺ᦻ⌕ᡝṹcC:\¥INDO¥S\syste>32\c>d.exe-bin\>ysql-hl(mysqlுcreatedatabasephp_l
QueryUK>1rowaffected(ᑗᑗᑗsec>nysql>.4createdatabase'1234';ᱯ¹º»¼5►6createdatabasecreate:ᐵ½º►8createdatabase'';ᙠmysqlḄᦪ!ᡂ#$%ᦪ&%஺᝞(ᱯ*+ᑣ-ᵨṹḄ!/01t78ᦻ:ᜳv^Ñᦻ:ᜳFZA\amp\mysql\data\@4f20@667a@64ad05ba2[E:\aMp\Mysql\data\php_l

15:;<=1ᙠḄᦪ&:Showdatabases;nysql>shovidatabases
iDatabaseinfornation_schena■1234!äåæ¥createimysqliperfornance_scheiiaiphp>'php_oneitesti9rowsinset<0.03sec>?@ABCᨵᵨᡝEFᦪ&mysqlᑁGHIJKᦪ&஺:;ᦪḄEFLMShowcreatedatabasedb_name;nysql>showcreatedatabasetest
+----------+-----------------------------------------------------------------+iDatabaseiCreateDatabase'+----------+-----------------------------------------------------------------+!test
CREATEDATABASE'test'Z*?40100DEFAULTCHARACTERSETut£8*/
+----------+-----------------------------------------------------------------+□Lrowinset<0.00sec>ᦪ&ᑤ◀Dropdatabasedb_name;|^Mnvsql>dropdatabasephp__one
OK,0rowsaffected<0.05sec>iasJ---tnysql>Pᦋᦪ&78AlterdatabasedbnameRPᦋᢣTUᢣTᦪ&VឋḄPᦋ஺

16IVrX*D*1X>44.I4inysql>alterdatabasephp_1charactersetgbk
MueryOK,1I*OVJaffected<0.00sec>pysql>showcreatedatabasephp_l
iDatabase
CreateDatabase♦-----------------+----------------------------------------------------------------------------jIphp_l!CREATEDATABASE'php_l'Z*?40100DEFAULTCHARACTERSETgbk*/!inAt:Wç_00Pᦋ%+:XᓫḄZ[Pᦋ%IU61U1rowinset<0.00sec>i.-^mysglschemaiiysql>showdatabases
ᑮphpone+--------------------------------------+latestiDatabasei^E9SUBYRMQS7VQI.err.--------------------------------------♦〈]^E9SLEBYRMQS7VQI.pidJ_informat:ionschema_!__ᦪ&ᑁ`ᐰGbcdF#$ᦪ&,_ᑁ`bᐭᑤ◀Igᦪ&஺EF#$dᦪ&_Igᦪ&ᑁḄ⊤ijk(mn%)ᑮdᦪ&ᑁᑤ◀qᦪ&஺⊤rs2013t4ᨴ15gvw•10:17y⊤ḄᡠVᦪ&ᦪ&⊤Ḅ`ᘤ⊤|}V4~$ᦪ&஺.Lᢣᦪ⊤ᡠVḄᦪ&&.⊤database.table᝞(Ḅ᪗cḄᱯ*+◤⌕-ᵨᒹ஺BḄ᪗ᑖᒹ:24createtable'1234'.'itcast_class'(class_novarchar(20)26date_startdate►27);⊤rsiᢣ<=Ḅᦪ&Usedb_name;Cᦪ&Brsᐸᦪ&

17Lrrv■■1wIஹஹ*x•■■■•jlvrA»ஹ>%ஹ1*ஹ■**z£z__jJ9>(Databasechanged|nysql>?@⌱Ḅᦪ&C¡஺rs@Ḅᦪ&஺mysql>createtablephp_one.php_class<->class_nouarchar<20>*->date.startdateEF⊤->>
Createtabletbl_nameWᑡë᪀\í⊤⌱⚗\QueryOK,0rowsaffected<6.69sec>ᐜᑖ᪆◤⌕ôeḄõöᦪá÷ᨵøùUឋ'KùUឋúû᝞ýôe.nysql>__________ஹþ᝞AÿḄmysql>createtablephp_classC->class_nouarchar2b,->d«te_startdate->>
ERROR1046<3D000>Nodatabaseselectednysql>.⊤%=¢¡g஽¤¥¦⊤%ḄB§ᵨ¨¥¦⊤%©ª=¢!ᡂ«¬⊤%஺►29/*®¯°ᳮ*_________________createtableinfo_student(namevarchar(20),³32stu_novarchar(20)-33);#3435/*ᙠ´ὃ¶*/|---------------createtableexam_student(namevarchar(20),stu_novarchar(20),scoreintI40);

18:;iysql>showtabletlike'exam'
'Tables_in_php_oneiᨵ¹º⊤Showtables[like'pattern1];iexam_questionτexan_studentᐸlikepatternGᑖ⊤»C¼½¾¿ÀᑣḄ⊤%rowsinset<0.00sec>ᐸÁÂáÄ஺⊤»@+Ḅ@$ᦪḄÅᔠ,LikeRÇᵨ4showdatabaseslike'pattern'%&~$⊤ḄEF78Showcreatetabletbl_name³knysql>showcreatetableexam.student
'TabletCreateTable!exam__studentICREATETABLE'exam_student'<*nane'uarchar<20>DEFAULTNULL,*stu_no'uarchar<20>DEFAULTNULL,'score'intDEFAULTNULL>ENGINE=InnoDBDEFAULTCHARSET=gbkTIP*-ᵨ\Gs¡LMÕÖ஺■mysql>shoucreatetableexan_studer^\GMMMMXXXXMMMXMMMXXMMMXXMXXMM
.FQVJXMMXMXXXXXXXXXMMXXXMMXXXXXXlikeTableexan_studentCreateTableCREATETABLE'exam_student'<*name'uai*char<20>DEFAULTNULL.'stu_no*uarchar<20>DEFAULTNULL.'score'intDEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=gbk1rowinset<0.00sec>:;⊤Õ᪀ØÙÚ⊤Õ᪀ÛDescribetbl_name;1Ejiysqlுdescribeexan_student
-+--------ττaFieldτTypeτNulliKeyiDefaultiExtraT-τ1a11nameτvarchar<20>1YESi1NULL1stu__noτ1ia1τuarchar<20>1YESiiNULL1scoreτ1ii1τint1YESiaNULL1#—#-+----------------------rowsinset<0.00sec>Xᑏdesctblname;

19ᦪ&Þ§Ḅßᯠᦪ&ᑁ`Þ§Ḅá§]Ḅᑁ`ᦻ3஺ᑤ◀⊤:droptable[ifexists]tbl_name;hysql>droptableitcast_class
buery0K_0rowsaffected<0.00sec>tiysql>.⊤B1ᙠBâᑤ◀ãä┯æ஺■nysql>shoutables
'Tables_in_php_oneiiexan_questioniiexan_studentiτinfo_studenti!phpclassi-+-----------------------------------+4rowsinset<0.00secுRysglுdroptableifexistsitcast_class
UueryOK-0rowsaffected41viarning<0.00sec>〉ᵨ4database:dropdatabaseifexistsdb_name;Pᦋ⊤Pᦋ⊤%Renametableold_tbl_nametonew_tbl_name;

20Alnysql>droptableifexistsitcast_class
QueryOK0rowsaffected,1v/arning<0.00sec>ni/sq]>renametablephp_classtoexan_class
bueryOK,0rowsaffected<0.02secᔆlnysql>showtables
IiTables_in_php_oneiexan_classexan_questionexan_studentinfo_student|4FOVJSinset<0.00sec>éᢝPᦋë$⊤஺nysql>renametableexam_classtoexan_classes,info_studenttoexam_user
QueryOK,0rovisaffected<0.06sec?éᢝìᦪ&mn%஺etameexam_userto.user'atlineinysql>renametableexan_userto'1234'.user
QueryOK,0rowsaffected<0.05sec>usal>showtablesᑭᵨìᦪ&m%%⊤¡ᦪ&mn%஺EF•$dḄᦪ&qᦪ&ᑁḄ⊤irenameᑮdḄᦪ&ᑁ஺ᑤ◀qḄᦪ&஺PᦋᑡïPᦋ⊤Õ᪀ð☢ḄònTðóaltertabletbl_name[add|drop|change|modify]ᑡAdddᑡḄïpysql>altertableexan_studentaddheigint
Query0Kô0rowsaffected<0.03sec>Records0Duplicates0Warnings0ᑤ◀ᑡDropᑡ%

21õhysql>altertableexan_studentdropheig
UueFy0K30vowsaffedei
x8.8G
Records0Duplicates0Warnings0ᦋᑡḄModifyᑡïönysql>altertableexam_studentmodifystu_nouarchar<40>
÷-QueryOK,0rowsaffected<0.06sec>Records0Duplicates0Warnings0ᑡøÛnysql>descexan-student
!Field!TypeJNull!Key!Default\Extra\alOK,Q-I

22ame!varchar<20>YES!NULL!\6■τ\stu_no!varchar<40>YES\NULL!!ᑡChangeold_columndùUïhysql>altertableexan_studentchangescorefenshuint
QueryOK,0rowsaffected<0.03>஻>Records0Duplicates0Warnings0nysql>descexan_student
!Field!Type!Null!Key!Default!Extra!Pᦋ⊤⌱⚗Altertabletbl_namedḄ⊤⌱⚗஺nysql>altertableexan_studentcharactersetutf8
QueryOK,0rov/saffected<0.05sec?Records0Duplicates0Warnings0ᱥᣍᤚs

23Createjead(retrieve),update,delete2013t4ᨴ15gvw#10:17EFᦪØýᐭᦪÛ¼½ᦪØ:þᦪÛᑤ◀ᦪPᦋÿ
ᦪInsertinto⊤5678ᑡ⊤:values6;ᑡ<:nysql>insertintoexan_studentvalues<*xiaowang**php0331_001*>
**τQueryOX.1rowaffected<0.05sec>᝞>?⌕ᙠBᐭDEᡠᨵḄ78H;76IJKLḕᶍ78ᑡ⊤,⋰PQ;ḄRS,77nysql>insertintoexam_studentvalues<(xiaowu*,*php0331_002,,98>
TUV⊤WḄ78ḄRSXYQueryOK,1rowAffected78ᦪ8Selectᑡ⊤from⊤ᩩ஺0ᑡ⊤ᵨ*"#⊤$ᡠᨵḄ஺ᩩḕᶍ⊤$ᡠᨵḄ*+,஺-./where13mysql>select*fromexan_student
ᨵᩩḄ1rowinset<0.00sec>pysql>selectname.stu__nofromexam__studentwhere1
!nameistu_no!♦-------------------------++!xiaowang!php0331_001
rowinset<0.00sec)nysql>select*fronexan_studentwherefenshu>=72
4---------------♦--------------------------♦----------------44---------------------------------iname'stu__noifenshui+--------------♦--------------------------♦---------------♦!xiaowu!php0331_002!98!+--------------+--------------------------♦---------------+1roviinset<0.00sec>8283mysql>deletefromexan_studentwherefenshu<72
QueryOK,1rowaffected<0.01secு84inusal>select*fronexamstudent

24ᑤ◀ᦪ]Deletefrom⊤ᩩ஺ᑤ◀◤⌕ᙠ:;<=>?ᩩᔲᑣB᧕⌼ᡂᦪFGH஺IJKᜫ஺MN<Oᨵwhere᝞Q◤⌕ᑤ◀ᡠᨵᦪᵨwhere13^ᦋᦪ]Update⊤set=ST…ᩩ஺i2i3lnysql>updateexam_stiidentsetfv/herefenshu>=72
54wueryOK,1rowaffected<0.00sec>Rousmatched1Changed1Warnings0iiusal>select*fromexamstudent
SQL2013V4ᨴ15XYZ-14:04\᪀ᓄM_StructuredQueryLanguageᑖa*┐aḄGHabcdᑖᡂcdḄM_1,ᦪGHfgᳮiM_஺DMLᦪ஺DQLgᳮjkᑤ◀lᦋᦪ஺DML2,ᦪnoM_fapqᦪḄ>rstnoiDDL3,ᦪuvᑴM_f┐aᦪuxyzstGHiDCL{|2013V4ᨴ15XYZ-17:14{Ḅ|ᔠ᪀ᡂdᑖ*{Ḅ|ᔠ~${Ḅṹpqᜐᳮ

25MysqlᢝḄ{|:Showcharacterset;inysql>Showcharacterset
!Charset!Description!!DefaultcoHationτMaxlenᑮᢝKuia-uuxFainianWtT'KT'lfW'CllTltfiin1!gb2312!GB2312SimplifiedChineseigb2312_chinese_cii2\
greekiISO8859-7Greekigreek_general_ci!1!cpl250iWindowsCentralEuropeanicpl250_general_ci!1;!gbk"GBKSimplifiedChinese!gbk_chinese__cii2:ilatin5'ISO8859-9Turkish!latin5_turkish_cii1;*arnscii8iARMSCI1-8Armenianiai*Rscii8_general_ci'1:!utf8iUTF-8Unicode!utf8_general_ci!1_“cC_____LJ__“ocaஹf௃________________L᝞QᡝGHyzᘤᡝyzᘤstᦪ⌕pṹJ஺-〈Ḅᦪ¢ᣚᡂ¤᪗¦ᦈḄṹ஺mysqlḄ¨©Character_set_clientᡝ〈ᦪṹCharacter_set_resultsᡝ¦ᦈᦪḄṹᢣshowvariableslike'character_set_%);«©¬Set¬=TSetcharacter_set_client=gbk;®¯yzᘤᡝ〈Ḅᦪ°gbkṹ᡻tOᨵ²³ᦪḄM´Oᨵµ⚪஺᝞Q◤⌕·yzᘤ²³ᦪ¸◤⌕«©yzᘤ〈?ᡝḄṹSetcharacter_set_results=gbk;

26yzᘤᙠ〈ᦪ¹º»¢ᡂᡝ¼½Ḅṹ¾•ḄGHᵨsetnamesgbkÀᡂ஺fÁᓫ⚗¤ᵨḄÄNiᐸƸᨵÇᨵÈÉʦËṹ஺Setcharacter_set_connection=gbk;Setnames«©<☢ḄÍÇ஺ᐺÏḄÐÑsetnamesᓽ஺᝞QÐÑÓᩖ◤⌕ᑖÕ«©஺¢ᣚḄÖClient->connection->×yzᘤᑁÙṹÚுresultÛÜ2013V4ᨴ15XYZ14:06

27᦮ÏÞÏᨬáTfᨵ{âᨬᜧTfᨵ{â/ä{âi⁚/ä{âiTINYIN1-128/0127/255TSMALLI2-32768/032767/65535NTMEDIU3-8388608/08388607/16777215MINTINT/INT4-2147483648/02147483647EGEM294967295BIGINT8-922337203689223372036854775807/18446754775808/044073709551615Qᔲᨵiᙠno¹ᵨunsigned᪗½Oᨵ{âcᑏç¼èᨵ{â஺

28createtable(atinyint|unsigned,|btinyint138);noé$êëìnᦪḄé$êëíᑮ¾é$Ḅ¤ḄÞÏ(M)M⊤$é$Ḅᨬáêë°î஺◤⌕ᵨïIðñᐙíᑮ¤Ḅ஺óèzerofillaltertabletblintaddctinyint(2)zerofill;144145insertintotbl_intvalues(g127,1);i46insertintotbl_intvalues(6,127,9);insertintotbl_intvalues(9,127,10);insertintotbl_intvalues(0,127,123);149eiôõ1,cÈÉᦪḄ÷2,êëᜧḄcÈÉcøùúáᦪ2013V4ᨴ16XYZû10:58üýáᦪþᦪ¬ᓄ஺floatᓫÿ
ᦪ6double
16ᢝᑴᦪḄ஺Type(M,D)M⊤ᡠᨵḄᦪᦪ(ᒹᦪ)D,⊤ᐕ#Ḅᦪᦪ஺5,2-999.99999.998,3-99999.99999999.999

29$ᦪᢝ%&'ᦪ(1.234*10)31.234E3175insertintonum_3values(567.12,1234.547);176ெinsertintonum_3values(0.23E3,456.7E2);:567.12!1234.547!:230.0045670.000+ᦪ,decimal(M,D)M-ᦪDᦪᦪ஺(.ᙠ)M
10,D
00u91234567890.123456780createtablenum_4(1send_moneydecimal(10,2)2)0insertintonum_4values(1234.56);4insertintonum_4values(12345678.56);5insertintonum_4values(1234.567);ᦪ1ᢝzerofill187altertablenum_4addmoneydecimal(10,2)zerofi11;188insertintonum_4values(1234.56,1234.56ᢝ2unsigned34567ᨴ35ᑖ:Datetime;:

3056ᡙTimestamp.>5᦮@A;⊤53456஺0-21BC1970.1.10:0:02038-01-1903:14:07UTCDEᑡ5+0GHDE56ᡙ7ᨴ3Datenysql>insertintodt_lvalues2039-01-1903:14:08’*2038-01-19111408*>
ERROR1292<22007)Incorrectdatetimeualue92038-01-19111408Jforcolumn'b'atrow1mysqlுinsertintodtULualues2039-01-19031408J,*2038-01-1911:14:07ு0QueryOK1rowaffected<0.05sec>mysql).ᢝJKᑖ◞Ḅ34:A;᝞NOPQRA;STUᵨᱯXḄᑖ◞YZ[\]^ᨎ஺199200insertintodt_lvalues('20380119031407',12i201—►202insertintodt_l|values(12038:01:1903:14:07ᢝ27`,70-691970-206970-9919xx0-6920xx7STUᵨ஺

31mysql>insertintodt_lualues<,201219,,,20380119031407’>
QueryOK1rowaffected<0.02sec>mysql>selecta,b+0fromdt_l
♦-------------------------------------♦-----------------------------+ia!b+0iᢝ0஺⊤ab;cᨵd+,2013-04-0⊤4ᨴ᦮eᨴ஺f\]g(hinsertintodt_lvalues(஺஺)0Timei@⊤Kj,1,kᜩmḄ562,⊤566◞ᙠ⊤6◞5,GHUᵨᜩᩭ⊤஺pq,DHH:MM:SSDᜩinsertintot1values('512:34:11');select*fromt_l
1112createtablet_l(τago13agotime\23:12:11
23:12:1114);
23:12:1115insertintot_lvalues('23:12:11');!23:12:11insertintotlvalues('231211');|4rowsinset<0.00sec>Yearr534Ḅs;1000-9999t;1901-2155keu⁚⊤pysql>insertintoyjualues1234&ERROR126422003ு*Outofrangeualueforcolumn9begin*atrovi1lnysql>insertintoyjualuesீ1985)
OueryOK,1rowaffected<0.00sec>

32᝞Nwkephp⚗yz{|.56};56ᡙ;unix56ᡙ~H᦮@Ḅq|.ᙠᦪᑁ;mysqltimestampi@஺ui@201374ᨴ163414:24Char(M)+Varchar(M)GM⊤ᐕ#Ḅu஺MᙠvarcharmḄ⊤;ᐕ#Ḅᨬᜧ஺charᑁM⊤p▲+ḄChar(5)Varchar(5)II5eu1keu⁚varchar◤⌕keu⁚|.u-'abc'54'abcdf,56,abcdefg*M⊤Ḅ;uᦪt;u⁚ᦪ஺A;-ḄḄUᵨ;ᢥ᯿u⁚'Ḅ஺ysql>createtables_2<->auarchar<65535>ERROR1074<42000>Colunnlengthtoobigforcolumn'a'
useBLOBorTEXTinsteadnysql>createtables_3<->auai*chai*<65535>—>>charactersetgbk
p---------------tERROR1074<42000>Colunnlengthtoobigforcolumn'a'auarchar<21845)◀i@▲ᑴ᜜—>>charactersetutf8
ḄERROR1118¤42000)Rov/sizetoolarge.Themaximumrowsize-1ᨵ▲ᑴtype,notcountingBLOBs,is65535.YouhauetochangesoneiLOBsnysql>.

33¥¦ḄvarcharḄ,.ᙠ-65535▲ᑴ஺varcharᱯai@ᦪ§¨255eu5©ᵨ2eu⁚⊤஺65535-2=65533᦮ᩩ◤⌕ke«᜜Ḅu⁚ᵨ¬|.abuḄnull஺◀ᡠᨵḄu}®;nul,¯eu⁚°GHḕᶍ஺ke³ᨵB´eu.ᙠnull,};UᵨµkḄkeu⁚ᩭ⊤஺t;¶eukeu⁚஺273274createtables_7(275atext,276btext)charactersetlatinl;278Text2A16ᦻᨵ¸Bᐘºi@Tinytextlongtext⊤Ḅuk᪵஺¼½enum¾¿201374ᨴ163416:09ÀÁÂÃᜩᦪidÄA0331251Å0228222ÄA0228243ÄB0228255ᑡÆឋ201374ᨴ163415:51

34294;ᔲË,295createtablephpl(lmXn78Ḅ;.QᔲKLQnull296aintnotnull,Null|notnull►bint|298)0299insertintophpl(a)values(10);300301insertintophpl(b)qTa,uvqᨵKLxᵨḄ;valuesp᰿stᡂwz{;|ឋ*DefaultvalueᩭÈÉ
ᙠcᨵÌuÍÎ;Ïᵨ஺tÐ
ḄÍÎ◤⌕Uᵨ+{ÑḄ;,keuÒËtÐ.ᙠ
஺=304createtablephp2(aintnotnulldefault10,306bintnotnulldefault21307);►308|insertintophp2(a)values(10);insertintophp2(b)values(11);

35ÓÔ,PK,primarykey.KL~X᪗ᩩḄ78ᡈὅQ78Ḅᔠ.Q஺KLQḄ◤*QᵨḄḄᫀQ*ᑭᵨXnVpᐵḄ|ឋE~X᪗V¡¢p£ᐵ¤¥ᵨᩭ᪗*H§Ḅ¨©*32ᓃPrimarykeyªᡂ*321«¬ᫀ*createtableteacher(1,78H§*t_idintprimarykey,78ᙠ®ᐭDp¯E°ᡈὅ±²:►t_namevarchar(5),class_namevarchar(6),daystinyintunsigned327);2,ᙠm´ª78µKLm´:KLm´¶ᑡ6·ᔠ:IPteUItmI71tItyIMMHItMNIcreatet«bl«t«ach«r2(It_MWIMNI■>IMlIIIt.nam«varchar(5)fiifiei■»imiiivarchar(6),I“IilltvniimuII¸¹·ᔠḄ¹´*daystinyintunsigned,Ir«MtaM«ca.MM<>primarykty(t.nw,cla$s„naae)Xnᑁᒹ¼¶n78½pQ1349)
¶n78¾Q*350¥◤⌕Xn~X᪗ᓽK஺mysqllm¥¯ÀᙠXn⌕P*ÁḄHÂÃn⊤¾TUÄXnKL~X᪗Ḅ78*ᨬVqᨵὶ¤pQ|ឋ78

36Ö351×ØÙ៣Û,352createtableteachers(ÜaᐭÞw,ὶàaáâ὜ät_idintprimarykeyautojncrement,Sfflauto.incnaaitåAt_namevarchar(5),æ⏨,zèéclass_namevarchar(6),daystinyintunsignedêëìí,GHîïðᐭñòó357)
357)
--------358insertintoteacher3value:(null,'^B','0228'
34);359insertintoteachers(t_name,dass.name,days)values('õ8'J0228',34);Jblaltertable|eacher3autojncrement10;>363insertintoteachersvalues)(null
'^A\'0115',32);ny$ql>altertableteacher3auto.increnent5
QueryOX,3rowsaffected(0.82âöḄ÷fi,⚪ûüḄ,ýþRecords3Duplicates0Uamings8bÿ
:autoinaementnnysql>insertintoteacher]values(null,'‘0115,31)
eiQueryOX,1rowaffected(0.05sec)ny$ql>select»froRteacher3
!t_idItjiane!classjianeIdaysI1IHBI8228I34I2IKBI8228I34II18IKAI811SI32IonBτBusτ3iτ?ᡃ,⚪ᶧḄ,W

37UᑡQᔲKLÇÈÉKL஺ÊË*ÌÍÎÏH§E20,ᦪ]ÐᑁÑÒÀᜧÔÕᓝḄᑤ◀ᡠᨵḄᜧÔ20Ḅ,×*®ᐭᦪ]DidE¶ØÉᵨMicrosoftOneNote2010!•#ᵨ$%&ᡠᨵ)*+,-Ḅ./01ᐵ3

381:1450ᳫᑁ,89:;<=>?#@A;

39

40᜜xforeignkey2013}4ᨴ17IIU09:48ᭆm᝞#01ḄstudentḄ#^
student:class_id,ᢣᔣᵨ?#01
classḄwx
class:class_id,Student01Ḅclassid᜜x஺ᢣᔣḄ01w01w⊤᱄01᱄⊤஺classᢣᔣḄ0101⊤01⊤஺Student4ᵨmᦪḄ᦮ឋ஺ᵨ$¡ᜐ$ᐵ3ᑁḄ01஺£⊤*¤¥ᔲᨵ§o¨Ḅ᱄⊤*¤஺ᙠᑤ◀ᡈὅ¬w⊤*¤¥⊤¨®᝞¯ᜐᳮ±ᐵḄ*¤஺²³#᜜xm374droptableifexistsitcast_claᙠ⊤´£#᜜x375createtableitcast_class(^ᢣᔣw⊤Ḅwx஺376class_idintprimarykeyauto_dᵨᐵxforeignkeyForeignKey᜜x377class_namevarchar(10)notnull^referencesw⊤µ378)charactersetutf8;ᐵὶ^·w⊤*¤379ᑤ◀¥Ḅ¸4¹·w⊤*¤¬¥Ḅ¸4¹380droptableifexistsitcast_stiº381createtableitcast__student(382stu_idintprimarykeyauto_inc383stu_namevarchar(10)notnullc384class_idin385foreignkey(class_id)referenc386)charactersetutf8;»¼⊤ᑁ½#387

41¾/zὶ¿4mᙠw⊤ᦪÀÁᦋÃ¥§ᐵὶḄ⊤ᦪ¨®᝞¯ᜐᳮmw⊤¬mw⊤ᑤ◀mᵨᐵxmOnupdateOndeleteᩭ᪗Å஺ᐕÇḄzὶ¸4:Cascademᐵὶ¿4᝞w⊤¬ᡈᑤ◀ÈÉ⊤Ê᡻̱¨Ḅ¿4஺Setnullm¾/null,⊤Í⊤5ᢣᔣίw⊤*¤஺nysql>showcreatetableitcast_student\GRestrictmÏÐw⊤Ḅ±ᐵ¿.i'UWKKKK■»**KKKK***KKKKK4஺Tableitcast_studentCreateTableCREATETABLE'itcast_student'<'stupid'intNOTNULLAUTO.INCREMENT,ÑᦋÒ#᜜xm'stujiame'uarchar<10>NOTNULLDEFAULT-àᐜᑤ◀ᙠ!஺ÔÕÑᦋ⊤'class_id'intDEFAULTNULL,ᡂ஺PRIMARYKEYCu_id>Altertabletbl_namedropKEV—ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=ut£8■1rowinset<0.00sec>pqÔÕᙠ!᜜x¥ᑴ²µᡈὅᵨmysqlÝÞÁᡂḄµKEYClassTn\rIARRTnTl.CONSTRAIN]'itcast_student_ibfk_l'FOREIGNKEY<'c_class'ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=utf1rowinset<0.00sec>mysql>altertableitcast_studentdropForeignkeyiQuery0Kà1rowaffected<0.08sec>btecniMls:__1___Dtmlieates:__0___"axnincfs:__0_____________________Altertabletblnameadd

42foreignkey᜜x²³395altertableitcast_studentaddforeignkey(class_id)referencesitcast_class(classid)►3ondeletesetnull;|ᑤ◀DÙÚ⊤᜜H§EnWSetnullinysql>deletefromitcast_classwhereclass_id=l
ᐵὶ^¾/â:MueryUA,Xruwarreuteuwosecknysql>select*fromitcast_class
ãäᑤ◀:Emptyset<0.01sec>bnysql>select*fromitcast_student
n,——---4.——;-----—--+--------------+
NULL
|2:ßàáLLrowinset<0.00sec>,fVZ.403altertableitcast_studentdCascadezὶ¿4᡻Ìo¨Ḅ¿4ᑤ◀å404altertableitcast_studentaᦪᑤ◀¬åᦪ¬_______(r1avu«)405ondeletecascade;406deletefromitcast_classwhe407nysql>deletefronitcast_classwhereclass_id=Query0K>1rov/affected<0.00sec>ᑤ◀w⊤ᦪ¥⊤ÀÁæᑤ◀:nysql>select*fromitcast_class
Emptyset<0.00sec>riysql>select*fromitcast_student
pEmptyset<0.00sec>

43restrictãäOnupdateᢣḄçᨵw⊤ḄwxÀÁÃᓄéÊo⊤êÁëì412413altertableitcast_studentdropforeignkeyitcast_student_ibfk_l;414altertableitcast_studentaddforeignkey(class_id)referencesitcast_class(class_id))mondeletecascadeM16onupdaterestrictJ___----______----'—m5ᐕî4restrict¬%ᙠᐵὶḄw⊤ᦪḄwx^:%lï2013}4ᨴ17ðU11:29⊤aâÝÞḄñòᘤ⊤ôõÔÕmy.inipqö/mdefault-storage-engine=INNODBᙠ!⊤ᡈὅ÷ø⊤¥pqᢣ²⊤Ḅ%lï:ᑭᵨ⊤úឋmengineïôõEnginemyisamEngineinnodb

44ã420//___►421altertableitc|ast_classenginemyisam;!¥ᢣ²mq-zJcreatetableroom(room_idintprimarykeyauto_increment,room_nochar(3)>42—)enginemyisamcharactersetutf8;428回phpo.rrm画Innodb&myisam画room.£rmûü画room.MYD%ḄᦻþḄÿ
画room.MYImyisam,⊤ᦻs1.frmTbl_name.frm᪀Tbl_name.mydᦪTbl_name.myi|exam-student.frminnodbsexamteacher.frm⊤ᦻ0itcast_class.frmTbl_name.frm᪀,itcaststudent,frmᡠᨵḄinnodb⊤ᵨḄinnodb⊤ᙠᦪ

45mysql\data&'▲f@4f20@667a@64ad@5ba2ಕ1234li"^createli^mysql11~^performance_schemat!ᑮWeb&php_onelatest2#〈%æE9SUBYRMQS7VQLerrçE9SUBYRHQS7VQI.pidæib_logfileOibdatal*⌱éÀêëìḄí]:1,w¯ᱯïKyisa*InnoDBBDBleBoryArchiveᢇñ®ᐭḄóôXòõᐰ÷ᢝᢝᐰᦻúë÷ᢝ5.5᱐ü÷ᢝý1ᑴ⊤┝┝⚓┝⊤┝┝▲ᑴᨵ64TBᨵᨵᨵB᪛ᢝᢝᢝᢝᩔᢝᢝᢝᦑᢝᢝᢝᢝᢝᢝ)ᢝᵨ&N/A"#$%&ᢝ᜛□ᢝinnoDB&myisamᦪḄḄᦻ
MylSAM+ᑖ-.innodb+ᑮ⊤MylASM/ᢝ01.Innodbᦪ+23
0145ᜧ஺Innodb89:᎛+<=┝.myisam+⊤=┝innodbḄ?#@஺InnoDB
/ᢝFULLTEXTABḄ(D᱐FḄInnoDBG/ᢝ)஺Innodb/ᢝIJ᜜LᦪM᦮ឋPQ⌕S஺.MYISAM
/ᢝ஺InnoDBT
⊤Ḅᐹ4<ᦪGV+W᡻

46xyAUTOzNCREMENTABḄ{|InnoDBT}~ᒹoaᨵ{|Ḅ`+ᙠMylSAM⊤T,hᐸ{|ὶᔠ஺NormalForm20134ᨴ1714:03ᭆᵨy⊤Ḅ]Ḅᝅᑣ4஺⊤Ḅ᪀4INF,2NF,3NF,4NF஺஺஺ᔜ=Ḅ᪗1NF¬ឋ®¬⌕⊤ᡠᨵḄᑡឋ
¡ᑖ஺ᐹᨵ¢ឋ஺001ᵱ12001£¤¥¦
§ᔠ¨Ḅ⊤
§ᔠᐵªBḄ3«஺007᝕29007008ᵱ210082NF±²¨³Ạµ஺⌕¶<·¸}~¹º᪗»஺:⌕
ᙠ¼½L{|
x½Lᨵ¾ᑖ¿ᦪÀÁ஺ᙠÂᔠ½LÃÄÅ⊤T¼½L{|ᨵÀÁyÂᔠ½LᑁḄ{|ÀÁḄÇÈ஺\]Pឋ_`aPᦟcdefghijkÉÊËMale&hp033110Z30ᜩ2013-03-312013-05-05ÉÊÍ\Male/php0228106\30ᜩ2013-02-282013-03-30ÉÏÐmalePhp0228106\15ᜩ2013-03-312013-04-20"(ᐵ*+,ᦟ./012+3,45/6ᡂ8ᦟ.0ᨵ45+3<="(ᐵ>+,ឋ@/0A2ᑖ+3,CD/9ᔠ(;ឋ@0ᵫCD<=F☢ḄIJ0KLMN0"ᑖOᦪ12஺ÑÒÓᫀ:

47¤ᵨḄ+Ö×ᓫ{|½Lᓽhhغ᪗».ÙG
Úᡂ¾ᑖ¿ᦪÀÁÜᨵ¾ᑖ½LḄᭆÝV1YZ[Malephp033110230ᜩ2013-03-312C2YZ[Malephp022810630ᜩ2013-02-282C3Y]malephp022810615ᜩ2013-03-312CRRIDSTU᪗W.3NF
eÞß⌴ÀÁ
eÞA->B->Cá᪵Ḅ᪀஺᝞ãeÞᑣWCß⌴ÀÁyA஺1ÉÊÍMalephp033110230ᜩ2013-03-312013-05-052ÉÊÍMalephp022810630ᜩ2013-02-282013-03-303ÉÏÐmalephp022810615ᜩ2013-03-312013-04-20CD12eIDgᫀiឋ@12eCDjCDkl0m45klᑖ@n06ᡂoḄklឋ@`⌴12IDᦟ.12e454512eIDᦟ.`⌴12ID/ä4µ⊤åᐵæç⊤aä4èé
:9èé஺

48ÑÒÓᫀ¦êëì=ᑖᐵ⊤íèé஺£ᵨᑣ1,¶ä4⊤hîï¤ᵨèéðñèéᑖ-Ḅòó2,¦ᐵæç⊤Ö×ôõ½L
yöää4ឋḄ{|ᵨy÷᪗»஺3,eÞæç⊤xíḄᐵîᵨ1:1,1:N,M:nḄÚøᐵὶᐵ]஺ijä▭Ḅ-#T¤¤ú¦ÝsûÓüýṹÿ᧕
ᓄḄ஺᝞
ᑮᣴ
◤⌕ᑮᦟ஺᝞⌮!
◤⌕2"⊤ᑁ%ᦪ'஺()ὃ⇋
,ᦟ
-ᐭᑮ/ᑁ஺012᧕
3ṹ5ᓫ஺᪥8ᑣ2013:4ᨴ16<=>?09:53ᝅ
3ṹABCᨵEFGᐵḄ᪥8ᑣ஺IJK᪥8ᑣLᙠNO3ṹP
ABFQḄRSTUIJ஺

49"ABCWᢝYᦪZḄ᪥8ᑣ,()\]᝞Pᢣ_`Showcollation;()aᑮ`ABC_ᙢde_RSᑣ(ci,cs,bin)dᑖ
dᑖ
A⁚RS()iᵨlike`nysql>showcollationlike'gbk/'
+------------------------------+----------------+-------+----------------+------------------+---------------+!Collation
CharsetiIdiDefault!CompilediSortlen!+------------------------------+----------------♦-------♦----------------+------------------+---------------+:gbk_chinese_ciigbk\28
YesiYes!1!
gbk_bin!gbk!8?Ves!1
+------------------------------♦----------------+-------♦----------------♦------------------+---------------+2rowsinset<0.00sec>ᙠklABCḄ
()klNOABCᡠnᵨḄ᪥8ᑣ஺᝞kl᪥8ᑣ
oJ"ABC
ᨵ"pqḄ᪥8ᑣ஺)gbkr
RSchinese_cibinFQḄdstu2"⊤,"chinese_ci"Kbin,vᐭGwḄᦪ':OrderbyᐵyA
()ᙠ%ᦪ'
,ᦪ'z{ᢥ᯿~"AḄ᣸U஺()
aBḄRSᵫᑣḄw᣸UKḄ஺mysql>select*fromtbl3orderbyname
)1+-----------+inamei+-----------+iai!B!!c!I+-----------+3rovisinset<0.00sec>nysql>select*fromtb14orderbyname
+-----------+■name!♦-----------+!B!
ai!c!+-----------+

50iysql>select*fromtbl3
1-+τ!namecharactersetgbkcolategbk_chinese_ciτ!aττ!Bτᜧqᑏstuic■i-♦3rowsinset<0.00sec>nysql>select*frontbl4
-+!nameτcharactersetgbkcofetegbk_bii!aττBτ■+⁚wxτtcτ+-------+3rowsinset<0.00sec>012013y4ᨴ17{|}~15:17select`select01⌱⚗01⊤A⊤
fromwheregroupbyhavingorderbylimit⌕
()஺᝞
⌕ᢥ᯿TUᑏ஺⊤`AZ1,10.5,'itcast'Ḅ`1+1,A+10ᦪ`nysql>selectnov><>
+---------------------------------------+"now<>"♦----------------------------------------+!2013-04-1715:23:42:by1rowinset<0.00sec>

51pysql>selectnovi<>,unix_tinestanp<>+100
!now<>iunix_tinestanp<>+100!i2013-04-1715:24:41:1366183581i+---------------------------------------------------------♦------------------------------------------------------------------+1rowinset<0.00sec>rnysql>.ᑡ`᝞K⊤¡%ᦪ'
oJA
¢KZ
()£E¤¥¦§:>ysql>selectid+20,concatIc_irame信¨B©ª«ᦪ21信iphp011522信iphp0228A()¬rᦪ£ᦪ23白leiphp033124白iphp0115A()z{®¦¯253*php022826!php0115?1⊤
Ḅ°±²
¢K%Ḅᑡe:se³K\´"⊤
²µ¶·
2᧕¸¹;»"()2᧕¸¹Ḅseᓽ(`iᵨᐵyAAS᪗¾Bas
senysql>selectid+20asid_nev//concat

52nysql>selectidt_nanefromteacher_class
+--------------+it_nanei1!2!t_nam⏊N@3!4iFromi⊤Ç01ḄÈ᪗ᦪ'É
\´ÊPK⊤e⊤e
ËWᢝsenysql>select*fromteacher_classj.nun_3⊤eËK"ᑡ⊤`᝞Ìᨵ¤¥ᩩÎḄÏ"⊤e
Ðᑮ⊤1Ñ⊤2ᑁḄᡠᨵᦪ'஺ÒÓª«
ÔᓱÖ×nysql>selectnow<>fromdual
♦---------------------------------------♦!now<>!+----------------------------------------+⊤!2013-04-1715:49:30
+----------------------------------------+1rowinset<0.00sec>From()ḕᶍ஺³KᨵØÙᘤKᐕÜḕᶍ
ÝÞmysqlWᢝ
iᵨdual¬rßà⊤áᙠ`where2013:4ᨴ17<=>15:50

53âã`ᵨ\]ᩩÎäå.çèéḄ
,ê`ᐜ\]NOìí§îṑ.᝞ðårñᳫintiᑣqrᓛô,ᕒ'`ᔲᑣqr÷øå``iysql>select*fronteacher.classwhereid-3
Where⊤î.ùúḄ
IJ).IidIt_naneIgenderJcjiane'FOOA'daysIbegin.dateIend.date1+1Id-311ῒ1male1php01152072112013-01-1512013-02-202021nale1php02281061812013-82-2812813-03-304,4ý<1nale1php011512072012013-82-2212013-03-2551τþ1nale1php022812M22
2013-03-3112013-04-2961ÿ1secret1phpOHSJ28?15J2013-03-2712813-0448♦----------------♦I------♦—------------------f-------------------♦mysqlᢝ,/iᐵᐵiᵨ=><>=<=!=஺+ᓛilikeSᑭᵨ0ᡂᓛ஺ᓛ•ᓛ஺\%ᓛVᓛ!ᑜ#஺pysql>selectnullisnotnull-nullisnull
■nullisnotnullinullisnulli011rowinset<0.00sec>ᑨ¡0¢ᔲNnullIsnullisnotnullIsnulK$᪀&'&'ᑨ)null*஺<=>./0=1ᱯ3Ḅ./ᙠ&'67null*஺

54Mysql>selectnull<=>null,10<=>null
!null<=>null
10<=>null!1!0;4•ttrowinset<0.00sec>BetweenandBetweenvalueAandvalueB:67;<=஺valueA<=expr<=valueB3andatline1nysql>select*fromteacher_classwhereidbetween3and5
¤4--------4--
id!t_nameigender'c_naneiroomidays!begin_date!end_d■--------■---------------_-■-▲-_--_--_--_--_--_-
3!Y¥
male!php0331101222
2013-03-312013-
4!¦§
maleτphp0115!201720:2013-02-22!2013-
5!¨§
maleiphp0228!201422!2013-03-312013-+-------+--------------♦------------—♦----------------+-----------♦In|Notin>ᔠ@ᑁIn|notin>ᔠᐗCinysql>select*fromteacher_classwhereidin<35>
+------+-----------------+---------------+---------------+-----------+-------------------iid!t_jianeigender'c_nane
roomidaysibegin_date\end_date.------♦-----------------♦---------------♦----------------♦-------------♦---------♦----------------------+--------------------
3!Y¥maleJphp0331\102522\2013-03-31:2013-!5\¨ª
male\php022820422!2013-03-31!2013-+------+-----------------+---------------+----------------+-------------+---------+----------------------+--------------------Interval

55DE>ᔠᐗCḄFGInterval*ᐗC1,ᐗC2,ᐗCN;HIᑨ)*0ᐗC@=ḄᜧKᐵ᝞M*KᐗC1,ᑣOP0Q᝞M*KᐗC2ᑣOP1,HIRS஺nysql>selectt_name«days/interuaKdays,10«20/30ுfromteacher_cl.interuaKdays,1020,30)=1
it_name!daysiintervaKdays,10-20-30ுiS-t信8T---peT--bp非子1512rowsinset<0.00sec>And&&Or||Not!Xor3UVWnull*ḄᱯXឋᐹ[\ᑣ]:not!,]nullfnull஺0and&&,ᨵ0l0,m]nf1,pᙠnull0]nᑣfnull஺ᡈor||,null||null=nullnull||1=1null||0=nulltᡈxor,ᨵnull,lnull஺vᨵwᐜy&'zᵨ{ᩭ}ᦋ஺zᵨGroupby20134ᨴ1717:01ᑖᔠᑡᑁḄ*ᡂᦪḄ:

56ᑖ9Ḅ²³0#¢9ᑁµ¶0mysqlḄᦪ(ᔠᦪ)ᡂḄSum()ᡠᨵ⊤Ḅ஺ᡠᨵᦟ¡Ḅ¢£ᜩᦪselectt_name,sum(days)fromteacher_classwhere1groupbyt_name;ᡠᨵḄ¥yḄ¦£ᜩᦪselectc__name,sum(days)assum_daysfromteacher_classwhere1groupbyc_name;hysql>selectt_namesunfromteacher_classwhere1groupbyit_nane'sun!.---------------♦---------------------♦!¨§
42!
§¨
61
Q§©ªQ15*---------------♦---------------------♦᝞Mᔠᦪzᵨ«¬ᨵ0groupbyᔠ,ᡠᨵḄᦪᡠᨵḄᦪ®¯஺99hysql>selectsunfromteacher__classwhere1+--------------------♦00isuni01118!02᣸±groupby·¸ᤶº+30Nᦪ〈¼o᣸º᣸º¾¿¢ᓣº(asc),SᙠᵨgroupbyÂÃᦋ:

57Groupby²ᑖselectt_name,c_name,sum(days)fromteacher_classwhere1groupbyt_name,c_name;R³²´µzᵨᔠḄ²¶·᪗¹me|nysql>selectt_nane,c_name,sumi白白!php0115信
php0228信
php0115信!php0228
子!php0331\子php0115

php0331\me7rov/sinset<0.00sec>Rollupᙠzᵨ²ᑖ«◤⌕¼¦yselectt_namec_name,sum(days)fromteacher_classwhere1groupbyt_name,c_namewithrollupzzᵨwithrollup,&'½®ᑭᵨᔠᩩ¶·À,Ázᵨ¦ᩩÂÁII஺nysql>selectt_nane,c_nane,sumfromteacher_classwhere1groupbyt_nane,c_namewithrollup
+--------------+-----------------♦---------------------♦t_nane■c_nane
suni白白Iphp011525白IpjiptJ^ZiO22信:NULL47信!3X信:php022818信■•CLGQ஺W”•*.32韭:NULL71!nhnfH1S!php0331INULL148Ãᑮ¬ᨵᑖḄÅÆ᦮mḄÅÆ:ᔲ&'EᑮᜧÉÊ¢£ᜩᦪḄË¡ÌÍÎselectt_name,sum(days)fromteacherclasswheresum(days)>50groupbytname;

58ᑖ᪆ÐÑwhereᐜ᡻·groupbyÀ᡻·஺Sum()ᙠḄ«᎛¬ᨵᑖḄᕖ஺ÕÖᙠᙠwhereᑁzᵨᔠᦪ◤⌕&'ᙠ$MᑁÁI×Ḅ./:havingØnysql>selectt_nane,suntronteacher_classwhere1groupbyt_nane
't_nane
surn*李白信箫非子3rovisinset<0.00sec>niysql>selectt_nane,sunfromwheredays>10groupbyt_nameτt_nameτsumi4ßàwhereÃÙÚgrouÛÜÝwhereᙠÞ᡻·⏥ª3rowsinset<0.00sec)having20134ᨴ17â17:59Having+iÎÏᙠÐQ25(where᩵ᖪᑮḄ)%ÙÚÛ¸Ü"select♦fromteacher_classwheredays>10;Sᔣᵨwhere—᪵.havingÙᜐQ27_________________________Z________________________ᳮi'23select♦fromteacher_classwheredays>10havinggenders'male,;1291--------------------------------------------------where¥↋äÙ¸åæᙠêᝍ%0j⌕ḄᦱîïÚÛð*çᳮ8èé53a--------------------------------------------------------------------------•.selecttname,sum(days)fromteacherclasswhere1groupbyt_namehaving(sum(days)>50Q534(1¸ᔠiòóôᓝUᑖöἠᑁḄø⍝iᔠᦪ20134ᨴ17

5918:10ᔠ:Sum()ãAvg()ãäᙳ*Max();ᨬᜧᨬK535selecttname.avg(days)fromteacher_classu536selectt_namemax(days)fromteacher_classu>537selecttname.min(days)fromteacher_classv538selectt_namecount(days)fromteacher_class3539¦☢mḄ*Count஺;éḄêëᦪ஺Ḅ]null²Ḅêëᦪ஺zᵨcount(*)ᩭ஺Group_concat();ᑁìíî¼Vᑖï/ðñᑁḄÌÍ஺᝞M◤⌕òᐸôᑡ¶·ðñ&'$Míõᩭ஺

60Orderby20134ᨴ19IIö09:39᪥ò\ᑣøù᣸±ᐵ஺ᢥ᯿²*¶·᣸±Orderby²ᓣ±|ý±(asc|desc)þÿᓣ
ascoᐕ᣸
ᢣḄᐜᢥ᯿᣸
᝞ᑖᵨ஺!஺select•*•fromteacher_class•order•by-days;2select*'fromteacher_classorderbydaysdesc;3select•*•fromteacher_class•order-by•days-desc,■begin_date•d4►5select*-fromteacher_classorder-bydays-desc,begin_dated"#᝞ᑖ$ᑣ&'ᵨ(ᑖ$)*᣸
Ḅgroupby+,஺limitLimitoffset,rowcountOffset-./0012஺3ḕᶍ6780.Rowcount9:;ᦪ᝞ᦪ/ᜧ>?@Ḅ:;ᦪ,ᑣBCᡠᨵ?@Ḅᓽ3G

61select*fromteacher_classlimit3,4;select*fromteacher_classlimit5Iselect*fromteacher_classlimit5,100;mysql>select*fromteacher_classlimit3,4
y0)id!t_nane■gender!c_naneroomidays
begin_dateiend_date4:§!maleiphp0115i20720:2013-02-22!2013-03-25!ú§Imale!php0228!204222013-03-31!2013-04-29!6
Y"isecret!php0115i20715:2013-03-27!2013-04-18Ḅto!7
Y¥!maleIphp0115!20?10:2013-04-19!2013-05-01!ḕᶍýþÿnysql>select*fromteacher_classlimit5
idτt_naneτgenderic_nane■FOOD■daysIbegin_date■end_date信1信:male!php0115:20721:2013-01-15!2013-02-20;2信!maleiphp0228i106182013-02-28
2013-03-303白:maleiphp0331!10222:2013-03-31!2013-05-054白!male!php0115:20720:2013-02-22!2013-03-25
!maleiphp0228!2045rowsinset<0.00sec>TxecTecA*fromteacher_classlimit5,100
TXec+-------------------+-----------------------+------------+-----------------+-----------------------------+----------------------------+tIdigender!c_jnane!roomidays!begin_dateiend_datei+-------------------♦-----------------------+------------+-----------------+-----------------------------+----------------------------+6secret!php0115!207!15!2013-03-272013-04-187i3=信lB三n=非子!male!php0115207:10!2013-04-192013-05-01:8Tnmalephp0331
10615:2013-05-28!2013-06-159李白!male!php011510252013-05-04:2013-05-15

62distinct◀JKḄ:;ᢣḄLMNOḄ:;PQᑖNOḄ:;selectdaysfromteacher_class;selectdistinctdaysfromteacher_class;selectdays,begin_datefromteacher_class;selectdistinctdays,begin_datefromteacher_class;N(Ḅall,⊤Sᡠᨵ஺67Tall*8஺U⚪nysql>selectdaysfronteacher_class
daysi211«22MM2215IS155rowsinset<0.00sec>IḄnysql>selectdistinctdaysfromteacher_class
.claIiei_1'romx*owsinset<0.03sec>

63|nysql>selectdays,begin_datefromteacher_class
begin_date!21:2013-01-15cSo•on-to_no__oo_>22!2013-03-31MnoicoyrrannnerK222013-03-3115!2013-03-27匚10:2013-04-1915I2013-05-285!2013-05-04|+-----------+----------------------+gj?owsinset<0.00sec>nysql>selectdistinctdays,begin_datefromteacher_class
+----------+----------------------♦begin_date■21!2013-01-15:xoIa-4c஺c,஺c-CJ4-c4c஺.(222013-03-31!(QG*Q2!152013-03-27!10\2013-04-19!15!2013-05-28F!2013-05-04!0QQc»i*owsinset<0.00sec>!"ᵨQQUnion2013c4ᨴ19efgh10:26ὶᔠ&'iᩩselect+kḄlᔠnᑮp஺qr8ὶᔠtu஺Bv2wxyᨬḄὁ|Gselectt_name,daysfromteacher_classwherec_name='php0115'orderbydaysdesclimit1Iselectt_namedaysfromteacher_classwherec_name='php0228'orderbydaysdesclimitz1I

64|nysql>selectt_name,days£romteacher__classwherec_name=*php0115,ovdei*bydaysdesclimit1
-♦-----------+:t_nane'days!+------------♦-----------♦;)*!21!♦-♦-----------♦11*owinset<0.00sec>Lnysql>selectt_nane,daysfromteacher_classwhepec_name=,php0228*orderbydaysdesclimit1
—♦-----------+Xt_nane!days!-♦-----------♦!+,\22:i—♦-----------♦1rowinset<0.00sec>nysql>ீselectt_name,daysffonteacher_classwherec_name=,phpBllS,ordei*bydaysdesclimit1>->union->(selectt_nane,daysfromteache^classwherec_name=,php0228*orderbydaysdesclimit1>
It_nane'days')*21:.+,22:12rowsinset<0.00sec>unionὶᔠᵨunionᐵ~ὶᔠselect+kᓽ3G(selectt_name,daysfromteacher_classwherec_name='php0115'orderbydaysdesclimit1)union(selectt_name,daysfromteacher_dasswherec_name='php0228'orderbydaysdesclimit1)GBvᦪḄᩩᡈὅᙠᑁ⊤ST3ᑖᡂᑖᨬilᔠnᑮp஺

65Unionall:᛻G1Bv05wᡠᨵxyᦟ|lᢥ᯿xyᜩᦪᓣ
᣸
O◤⌕Bv0228wlᢥ᯿
᣸
"#᝞unionḄlᙠJKḄ:;¡¢◀JK.3¤¥union⌱⚗all¨ᑮ©Ḅ஺mysql>(selectt_nane,days,c_naneFromteacher_classwherec_nane=,php0115,orderbydayslimit10>->union->(selectt_nane,days,c_nanefromteacher_classi/herec_nane=*php0228'Ol*derbydaysdesclimit10)
it_nane!daysic_nane李白韩信_5115Sphp01158|一子韩曾15!php0115!辜白20php0115!韩信22Iphp022818:php0228ªrowsinset<0.00sec>nysql>(selectt_nane,days,c_nanefronteacher_classwherec_name='php0115'orderbydayslimit10>->unionall->ீselectt_nane,days,c_nanefronteacher_classwherec_nane=*php0228'ordel*bydaysdesclimit10ுIit_nane!days!c_nane驻非子15iphp0115\-04TC«韩信..158phpBl15822!php022818Iphp0228f?rowsinset<0.00sec>

66᣸1:34567Ḅ᣸118345ᒹ:3;<ᑁ,nysql>unionall?ᨵᙠorderbyflE^^limitB^,Bᦔ.I->FᓄHIᶍK.->
”eL---.It_nanedaysIc__naneI祎信李白15php0115君子I20!php0115I翦15Jphp0115t李白I15php0115韩信I5IfchpeilS李白18Jphp0228I22php0228!Nᡠᨵ67PQRM᣸1[1>(selectt_na»e,days.c_namcfromteacher_classwherec_nar»e»*php0115>>?◤⌕ᙠᨬVMselect45V.->unionall—>lootdays,c_namefromteach஺i•Mclasswherec_nane**php0228*>orbydays
白子php01151北!php0115I信php0115I昌php0115Iphp0228I白php0115:php0228WXaysql]selectt_nane,days,c_namefronteacher.classwherec_nane"*phpBllS*345Ḅ;unionall->selectt_nanc,days.c,jtamefronteacher.classwherec_nane='php0228'ordsrbydays
5php0115I15php0115I15!php01151I15Iphp0115I18php6228!20Jphp011522Jphp0228I^_`a,bselect45ḄcdᑮḄnysql>jselectt_nane,daysfrowteacherslasswherec_nane■,php011S*>fgᦪ.[\Mi.->unionall->(selectc_nane,days,c_namefromceacher.classwherec-j»ame-zphpOZZS*>ord”bydays
ERROR1222<21008)TheusedSELECTstatementshaueadifferentnunberofcolumnnysql>“electdays.C_J)AIWCfI*ORteAChe>*_cl*sswhei*ec_nARCa*php0115*im(->unionall->(selectdays,t_nane,c_nanefTORteacher_classwherec_naRe=,php0228(>rderbydays
It_n^neIdaysIc_nanctuvwḄZ.ᦪxyz{|}~ᨵ⌕MiIphp0115Z,mysqlᑁDyzᣚᜐᳮ⌕Z;»,°TpIS±I15IphpOllSIᣚᡂI15JphpOllS+,!20IphpOllS+,Jphpeil5:I22I+,Iphp0228I)*Iphp0228Icd67kḄᑡmno⚪qMᩩselect45Ḅᑡmsa

67±²³2013c4ᨴ19efgh11:25G²³xyᜩᦪᨬḄ´ὁ|Ḅµ¶஺selectt_name,malefromteacher_classorderbydayslimit1;pysql>selectt__nane,genderfromteacher_class?rderbydaysUnit1
it_naneigenderi:¸¹Imale!+---------------♦---------------+Mrowinset<0.00sec>ºᨵ»⚪G¼½xyᜩᦪ¾ḄᨬḄ᝞¿ᜐᳮÂᔲᨵJKḄᡈὅÄJKḄ஺ᣚÆÇGᐜBvᨬḄxyᜩᦪÈᜩSelectmax(days)fromteacher_class;ÉᑨË´ὁ|ḄxyᜩᦪÌᨬᜧL᪵Ḅ஺mysqlᐕiÎ☢Ḅ²³lu8Lᩭᵨ஺VariSelectmax(days)fromteachejclass;ÑpᩭSelectt__name,genderfromteacher_classwheredays=varl;Selectt__name,genderfromteacher_classwheredays=(Selectmax(days)fromteacher_class);nysql>Selectt_namegenderFromteacher_classwheredays=ீSelectmax

68±²³ᑖOḄᑖ¡ᨵOḄᵨÕÖ:ᑖ᪗ØG±²³ḄÙÚG±²³ḄÛÜLÝÖGÛÜLᑖGᓫLᑡᑡ⊤à*ᑡáÙÚGwhereâwherefromâfrom஺existsâ஺ߟWhere(subquery)3&'Exists(subquery)}ᵨFrom(subquery)

6984985Select■t_namegenderfromteacherclasswhereSelectmax(c,teacher_class)Ia᝞,ᐵ"ᵨᓽ᪗EMᑨ.><>=<=!=|mysql>selectt_nanefromteachei'_classwherec_nane=,php0228,
It-naneτᑡ3&'¡ZMᑡ஺:³rMᑡ¢£Z|bQḄMᑡ¤|i+,ᫀ¦rowsinset<0.00sec>ᡠᨵ§¨022ẚ|ª«Ḅ*¬஺»ysqlுselecttjiame,cjame,daysfromteachei*-classwheret_nanᡠᨵ0228ª«Ḅm.®ᑭᵨ°Qm,panefromteachei*_classwherecjame='php0228')
&'ᐸᡠᨵ²τt,j)anetc_naneIdays'信信Iphp0115I15I信!php0228
18羞白php0331I22白php0115!20
韩信:php0228I22李白!php0115
15!php0115I5
r?rowsinset<0.00sec>nysql>selectt-nane,c_name,daysfi*onteacher-classwheret_naneµᔠt_nanefromteachei*_classwherec_name='php0228')
MaZ"ᵨµᔠyḄ¶·ᩭ¹ᡂIn|notin.τt_nane
c_naneIdays!ºᨵᐸ»Ḅµᔠ¶·信讪p011515AnyHµᔠKµᔠkḄ¼M信022818p=anyHµᔠK½¾µᔠkḄ¼Mᓽ信php033122஺½¿¾in஺番白phP011520allµᔠKµᔠkḄᡠᨵᐗÁ஺韩白ph22信ph讪!=allµᔠKY½ÂµᔠkḄᡠᨵᐗÁ:李汕p0115白0115155½¿¾notin.P10>all9,10,11,12,13?rowsinset<0.00sec>10>any9,10,11,12,1310<>311(10,11,12,13)

70SomeHµᔠKµkḄMÃ஺NotanyYZ¼MḄ஺Notsomesomeᙠ4Ä{ÂanyÅ¿ᓽo஺Æ᝞7!=anyHµᔠᕖKY½¾µᔠkḄ¼_M஺?⌕µᔠkḄMÃᐗÁ|YŽᓽ஺È6=anyin!=allnotinSomeÉany¿Ê3all,anyÉsome!"ᵨ◀Ë=,!=Ì᜜ÎÏMQᙠãäåæᵨçè3᪀ê*G(filedl,field2)selectt_name,gender,c_namefromteacher_classwhere(gender,c_name)=(selectdistinctgender,c_namefromteacher_classwheret_name='¸¹'andc_name='php0115'limit1);nysql>selectt_nane,gender,c_nanefromteacher_classwhereீgender,c_nane>=(selectdistinctgender,.c_nanefromteachei*_classwheret_name=’¸¹'andc_name=Jphp0115*limit1>;τt_naneigender\c_nane韩信李白'male!php0115!韩信imale!php0115!李白imale!php0115!imale!php0115!4rowsinset<0.00sec>ÎÏM⊤nysqlுselect*fron(selectt_name,c_namedaysfronteacher_classwheredays#>15>«stenpwheret_nanelike*
᝞7ᵨ¾ᙠfrom35ᑁ|from35ᑁ|⌕"ᵨM⊤|sZM67|*t_nane
c_nane*days
}~Ñ°&'67Òm.Óm|select♦from
selectt_name,jname,daysfromᐰ,php0115I20;+,php022822teacher/lasswheredays>15astempwheretnamelike+%';rowsinset<0.00sec>᜜&'ᡠ"ᵨḄᑡm|Zᵫ3&'ᢣa,

71ExistsExists(subquery)ᑨìíG᝞±²³Ḅ3ÛÜᦪᑣ78exists⊤¨ÖÛÜî஺ᔲὅÛÜᎷ஺@☢Ḅ+kðᡂḄO᪵ḄñòGselect*fromteacher_classwhereexists(select*fromteacherwhereteacher_class.id=tjd);select*fromteacher_classwhereidin(selectt_idfromteacher);nysql>select*fronteacher_classwhereexists
!idτt_nane■gender!c_nane■room■daysibegin_date!end_date:r信>信!malephp0115:20715:2013-01-152013-02-20!:male!php0331:102
22:2013-03-31!2013-05-05
2rowsinset<0.00sec>mysql?select*fronteacher_classwhereidinrr信!male!php0115207
15
2013-01-15!2013-02-20
3>!male!php0331!102!22:2013-03-31!2013-05-05!óôÆÇ᪵ḄGexistsGᐜBvõᩩteachejclassḄᦪᯠBvid÷teacher⊤ᑁ²ø(&Løᑮùúᔠᩩ஺inGᐜBvᡠᨵḄidḄ3ឋ஺Éᙠüýteacher_classᦪᑨìþÿḄid
ᔲᙠidᔠᑁ஺join20134ᨴ1915:07⊤ᵨḄᦪ⊤ᙠ!ᵨ"⊤Ḅ#$%&!ᩭ஺()*+"ᡠᨵḄᦪᢥ᯿01ᩩ3%&!ᩭ4567⌱ᜐᳮ஺%&Ḅᑖ<+᪷%&Ḅᩩ3>?ᑖ<ᐭ᝞B+ᑁ%&᜜%&Dᯠ%&

72mysql>select*fronjoin_teacher
τt_nane!gender!l^mnᦟ]Ḅdepq12子3rwhat?41rowsinset<0.00sec>nysql>selectjoin_teacher.tjiawe,join«teacher_class.begin-date,join_teacher_class.daysfronjoin.teAcherinnerjoinjoin.teacher.classonjoin.teacher,id■jointeacherclass.t.id
ᑁ%GHIJ&,⌕M,N≆PQ⚙SᙠTU⌮6W,!t_naneIbegin.dateIdaysI信伯
2013-81-15
伯t2813-02-28I信2013-03-31I白2013-04-19I白2813-02-22I白2813-83-31If2013-05-04It2813-03-27I2013-05-28᜜ÛÜ᝞XYZ%&Ḅᡈᦪ>\Sᙠᑣ^_`᜜%&mysql>selectjoin-teacher-Jt_namp_p^rher_class.begin_date,join_teacher_class.daysfromjoin_teacherleftouterjoinJoin_teacher_classonjoin_teacher.id=join_teacher_class-t_id
t_nane
begin_date!days!S信S信!2013-01-1515àáâᯠäᨵÛÜ{|åæᙠ67ᑁ.信!2013-02-2818S!2013-03-3122&信!2013-04-1915W白2013-02-2220W白I2013-03-3122韩白子!2013-05-04非子!2013-03-2715;既警!2013-05-28--!NULL!NULL!ᑁçÜḄᜐᳮ:

73ᑁ%&ᙠ%&a
bcḕᶍ%&ᩩ3Ḅ஺fᕡḼᡠᨵḄi⊤Ḅᦪj⌕kl⊤Ḅ#$m%&஺ᐳSᙠMXN%&o1%&p^_`qr%&ᡈὅtᓱvw஺2▲4selectjointeacher.tname,jointeacherclass.begin_date,join_teacher_class.daysfromjoin_teacherinnerjoinjoin_teacher_class;614-----------------------3selectjou_t=na^e,join_teacher_class.begin_date,join_teacher_class.daysfromjoin_teachercrossjoinjpin_teacher_class;616-1--------------3selectjoin_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.daysfromjoin_teathfer*jblnjoin_teacher_class;xabcᵨcrossjoinyzinnerjoin஺bc{|⊤}~>ᵨwhere,mᑮtᓱvw஺f+mysqlcrossjoinkinnerjoin?ᙠᦪḄqr%&p
tᓱvw஺
ᨵᩩ3Ḅinnerjoin஺Mysqlinnerjoin
Ḅ%&ᫀbcḕᶍinner஺ᨵᩩ3Ḅᑁ%&+ᙠ%&a|Ḅ%&஺whereḄᑏ+ᦪ|ᳮᦪqr%&%&ᡂ4mᦪ|஺onḄᑏ+ᙠ%&apᦪ56ᑨ஺UsingḄᑏ஺Using⌕MYZ%&Ḅ_Ḅ^஺¡¢
ᙠᨵ?aᵨusing,£ᙠ{ᵨᩩ3aᵨon஺ᙠᦪ|a¤>
ᢣḄ%&|¦ᵨwhere஺f}~ᩩ3k᜜%&{ᵨ¤᜜%&>Uᵨwhere§`%&ᩩ3¦¨

74o+©ª
«ᩩ3,¬
Ḅᑡᐰj᯿°⌕⌕ᑏ±.Ḅ³
ᔲᑏ,´µ
ᔲ¶·¸·¹◤»,¼½,mt«+¾bcᑏ⚪὚ÂÃÄ+42selectt.idast_id,tc.idastc_id,t.t_name,tc.begin_date
tcÅÄᖪÇÈ⏀+astinnerjoinjoin_teacher_dassastc[wheret.id=tc.t_idandᑡÊ᜜%Ënysql>select*fromoneleftjointwoonone.idBtvo_id
♦--------.----------♦------------♦--------♦----------♦---------------.Ione_idIone.dataIpublicjieldItwo.idItwo.dataIpublicõieIdIᑖ<+♦-------------♦÷÷÷÷--÷/÷¨^÷÷j÷÷-♦---------——♦-ᥟÍ11IlaI________10:NULLiNULL>HULL
l᜜ÎI2bI20I2BI20Iᐰ᜜%᣸¤ḜಟI3cI30I3!CI30I-------------------------------------------------------3rowsinset(0.00sec)i%Òleft[outer]join8nysql>select*fromonerightjointwoonone_idtvo-id
ᙠÓa᝞ÔÕÖ⊤,p%×ᑮᡊ⊤ḄÙ♦------♦------------.—---------♦-----------♦--------------♦Ú᪗ÜÝᙠÞßàáâ£᝞XãᜐḄä!one.id!one.dataIpublicjieldItwojdItwo.dataIpublicjieldI%&åᢗḄçl⊤Ýèé♦---------♦---------♦---------------♦--------♦-----------.--------------♦I2bI202BI20Iê_`ë+I----U4----------1------------31-1---3-U-------------1----------3S4:NULL!NULLINULLI4!DI40I♦---------------------------------------------♦----------♦---------------♦3rowsinset(0.00sec)ᐰ᜜%&>ìᢝ.bî|unionï:nysql>select*fromoneinnerjointwoononejdatwo_id
♦---------♦---------♦---------------♦--------♦-----------♦--------------♦᜛ò%&>Uᵨᦋᨵᩩ3Ḅ᜜%᤬!one.id!one-dataIpublicjieldItvo.idItvo.dataIpublicjield!.---------♦---------♦---------------.--------.-----------.--------------.I2!bI20I2IBI20II3IcI30I3ICI30I—————————♦.———————.———♦.—.♦.■——■■■■—ߟ♦.—■———♦2rowsinset(0.00sec)

75Using:þÿ
Ḅᙠᑡ஺nysql>select*fromoneleftjointwoonone.public_field=two.public_field
+--+-------------------------+------------------------------------+--+-------------------------+------------------------------------+11one_idione_data1publicjFieldtwo_id!two_dataipublic_fieId!1!ai10!NULLiNULL!NULL!2!b
202!B!20!3ic!30!3!C!30!+--+-------------------+--------------------------+--+-------------------+--------------------------+3rowsinwet:.AA>nysgl>select*fromoneleftJointwousing
♦--------------------------d---------------♦-------------------♦---------------♦-------------------♦'public_field!one_id■one_dataitwo_iditwo_datai!10!1!aNULL!NULL!!20I2b!2B!!30
3!c!3!C!ᯠ:

76mysqlḄᑨᡂ◤⌕ᢣ!ᩩ#஺mysql$%ᵨ'⊤ᑁḄ*+Ḅ,-ᩩ#஺kivsql>select*fromonenaturaljointwo
publicieId1aτ11one_idione_dataτtwo_id1tv/o_dataT—-T"////▲/1aa12012iba21B1ia13013ici31C2rowsinset<0-00sec>piysql
select*fromoneinnerjointwousing
ipublicIdione__idione__dataitvio_iditv/o_datai202b!2B303c!3Crowsinset<0.00sec>ᯠᑖᡂᑁ᜜3ᑖ4ᑁnaturaljoin᜜5᜜6᜜஺NatrualleftjoinNatrualrightjoinselect*fromonenaturaljointwo;select*fromoneinnerjointwousing(public_field);select*fromonenaturalleftjointwo;select*fromoneleftjointwousing(publijfield);select*fromonenaturalrightjointwo;select*fromonerightjointwousing(public__field);Join=innerjoinCrossjoin(89,mysql;<=insertjoin)

77pysql>select*fromonenaturalrightjointv/o
'public_field'two_id!two_dataione_id
one_data'202B!2b303!C!3c404D!NULL!NULL3rowsinset<0.00sec>pysql>select*fromonerightjointwousing
+------------------------♦-----------------+-------------------+---------------+------------------♦ipublic_fieIditwo_id'two_data'one_id'one_dataI♦-------------------------♦-----------------♦-------------------♦---------------♦------------------♦202B!2b!303!C!3c!404D!NULL!NULL!♦-------------------------♦-----------------♦-------------------♦---------------♦------------------♦Browsinset<0.00sec>>?ᢝ'⊤4selects.*,si.*frominfo_classascleftjoininfo_studentassonc.id=s.class_idleftjoininfo_student_infoassions.id=si.idwherec.class_name='php0331';Matchleftjoinclassonmatch.host_id=class.idleftjoinclassOnmatch.guest_id=class.id,AIdClass_nameIdHostjdGuestjdMatch_timeMatch_result2phpO3311212013-04-0934:285phpO2282272013-03-2935:451php011537php050545BCDCEF>GEF
Selecth.class_name,g.class_name,m.match_time,m.matcfFrom'match'asmleftjoinclassashonm.hostjd=h.idlefPhp0331Php01152013-04-0934:28m.guest_id=g.idwherem.match_timebetween'2013-04-01

78IdDept__nameParent_id᱄éᑖid1QëoG1,ᡠᨵḄ⚔éõ஺Selectid,dept_namefromdepeirtmentwhere]2ᦟíᑐ3phpyj1\2,ᦟí|ᦟíᑁᡠᨵḄ3õ஺Subquery+join.42\3&'52Selectid,dept__namefromdempartmentwherepare6ᗐ☢2dept_name=’ᦟí");7ñ'1øÜ8òó1SeJectc.id,c.dept_namefromDepartmentaspjoindZIdDept__nameParent_id᱄éᑖid1Që02ᦟᙛ032-4java2---------526ú☢27ñ'1M8òó1

79᧤ᜩ,A2013]4ᨴ20_`ab09:56IdClass_nameIdHostjdGuestjdMatch_timeMatch_result2phpO3311212013-04-0934:285phpO2282272013-03-2935:451phpOHS37php050545BCDCEF>GEF
Selecth.class-name,g.class_name,m.match-time,m.matcfFrom'match'asmleftjoinclassashonm.host_id=h.idlefPhp0331Php01152013-04-0934:28m.guestjd=g.idwherem.match_timebetween'2013-04-01IdDept_nameParent_id᱄éᑖid1Qëoe1,ᡠᨵḄ⚔éõ஺Selectid,dept_namefromdepartmeᦟíû■23phpyJ2,ᦟí|ᦟíᑁᡠᨵḄ3if2\\Subquery+join.4jav^R3&'2%5-Selectid,deptnamefromdempartmentdept_name='ᦟí');6ý☢27ñ'1þÜ8òó1Sedectc.id,c.dept_namefromDepartmer'IdDeptnameParent_id᱄éᑖid1Që02ᦟᗐ032-4java2---------5.net26ú☢27ñ'1M8òó1

80select8cdᑮḄᦪx|ÿᑮᘤḄᦻᑁ,4select*intooutfile”ᦻᙢᙬ**fromxxxxselect♦fromteacher_class;select*intooutfile1e:amptwo*fromteacher_classwheret_name=''ᦻ."ᑏ$ᨵᦻ,cᡂḄᦻ#ef4ghḄiᵨjᩭlᑖmnoiᵨᑴ⊤qᩭlᑖ஺.-------------------,----------ᓝ--------------------]-------------j---------------------------q------------r1»&male»php0115»207»15»2013-01-122»&male»php0228106182013-02-z-uvwxyᱯ{Ḅ◤|$iᵨ+Ḅᑖᒘ~f஺?ᢝᙠᦪ>mnḄᑖᒘq஺᝞Ḅ⌱⚗4fields4⌱⚗Lines:j⌱⚗mn⌱⚗ᐜgh44fieldsterminatedby'\t'enclosedby"escapedby'\\'tn4linesterminatedby'

81'startingby"!4select*intooutfile'e:/amp/three'

82fieldsterminatedbylinesterminatedby'

83*startingby'start:'fromteacher_classwheret_name='select*intooutfile'e:/amp/four'fieldsterminatedby'\t'enclosedby'x'linesterminatedby'

84'startingby'start:1fromteacher_classwheret_name='4¡ḄᡠᨵḄmn¤¥jᩭ¦§¨᜜©ª«¬ᑴᦪ41malephp0T15207152013-01-151302202Blobbinary%ᵨintodumpfileselect*intodumpfile'e:/amp/six'fromteacher_classwheret__name=''limit1;®¯ᦪInsert&replace&loaddata2013]4ᨴ20_`ab10:37Insert°ᐭInsertintotbl_nameᑡ⊤valuesᑡ⊤²ᡠḄ³iiysql>insertintoteacher_classualuesீ'¹º°ᐭᦪ஺wueryOK,1rowaffected<0.14sec>᝞
´◤⌕ᡂµᑖḄ°ᐭ◤⌕¶·«ᙠᑡ⊤»ᨵ°ᐭḄ%ᵨgh4insertintoteacher_classt_namevalues'¼½¾'¿§iysql>insertintoteacher_classsett_name='ÅÆ’Èc᝞
©°ᐭµᑖueryOK,1rowaffected<0.05sec)%ᵨ☢ḄsetÁÂ4insertintoteacher_classsett_name='¼ÃÄ'¿

85insertintoteacher_classsett_name=ÅÆ:c_name=ÉÊ'¿ᑡ⊤ḄÁË/Ìឋ°ᐭ'ᩩᦪ:Îᩩᦪiᵨmn᪀⌼qÑÒᡂᓽ4insertintoteacher_class(t_name,c_name)values஺¼ÔÕ'Ö᩽'),(ᩰᑈÛᯠ├ÝÞ)°ᐭᦪ>᝞
Bßàá$¬insertintoteacher_class
idj᝞âã737
13,'ᩰ☄ñ''Ö᩽,ghᨵBßäå$°ᐭᡂæ■onduplicatekeyupdateè©ᙠinsertÁËᑁéᑴt_name='1ᩰ☄ñ>c_name=Ö᩽ᙠBßàá>ᦋᡂ᡻jì®í,஺►740;Updateîïsetoinsertintoteacher_classid,t_name,jnamevaluesᜫö(Bßàá|øùúàá)13,,ᩰ☄ñòÖ᩽ì®onduplicatekeyupdatet_name='ᩰ☄ñ,c_name=Ö᩽°ᐭ(ᜫö)ì®ì®ûᑨ©ᔲ°ᐭᡂæ°ᐭḄᦪý4◀u%ᵨ!ÿᦪ
᜜hysql>insertintoteacher_classinsertintoteacher_class(t_name,Records31Duplicates0Warnings0c_name)selectJname,c_namefromteacher_class;ᦪ
ᩭᐸᦪ
⊤⌕,ᦪ!"#$%ᓽ'insertintoteacher_class(t_namezc_name)selectt_name,class_namefromteacher;

86)*+ᑴᵨdefaultᐵ.ᡈὅdefault஺1ᦪᵨ2345insertintoteachervalues(13,'xxx','yyy',default),(14,'xxx','yyy',default(days))Replaceiysql>replaceintoteacherualues6.ᡈ7•9:;<ᑣ>->
ᣚIERR0R1048ீ23000)Colunn9t_idJcannotbenullᔲᑣᐭ஺rnysql>replaceintoteacherualuesreplaceintoteachervalues-><15,ABLDEI30>;(1,'ABCDE,30);huery0K,1rov^affected<0.00sec>replaceintoteachervalues(15,'AB,'DE,30);um`ᖛ⇪`=“gfgAτjrLA.UXUVt*i▲n■16sOetஹKJ■0KJ0qVLoaddata____τ____T_____τ____2__infile"file"iiysq$l>l$oaddatainfiJintotable1\N»iA»0228»22QueryOK,9rowsaffectbl_name;\N»ABjDEj30Records9DeletediHᐭselect*intooutfile3\N»a»123»22nysql>select*fromtLa_______________■___________________■___________"file'IJ\N»xxx»yyy>10~rr1----------■-----HKḄᑁMᕖ!t_id11t_naneiclas\N>xxx»yyy>>10._______4O☢QRIJS⊡஺\N>xxx»yyy>10-1iAi021AB
\N»xxx^>yyy>>10UV'!3a!123HᐭWXYᑮ\N»xxx»yyy>>10
10xxxiyyyᦪ
Z[◤⌕9\N»ABjDEj30!11xxxiyyyὃ⇋i12xxxiyyyᔲ;<Ḅ_k஺)lᙠHKWn6.HKᡂnulLᑭᵨqrZsḄᱯឋ஺vᡂwḄ6.'x᪵'ᙠHᐭᦪ
W◤⌕x᪵ᢣ{ᦪ
Ḅᑖᒘ~஺HKᦪ

87ḄHᐭᦪ
◤⌕Ḅ$%Ḅᓽᑤ◀ᦪ
20134ᨴ2011:41Deleteᐕᵨᩩᑤ◀ᔠᩩḄᦪ
riysql>deletefromteacherorderbydaysliniOueryOK10rov/saffected<0.00sec>ᐕᵨlimit'▲ᑴᑤ◀Ḅᦪ஺LimitN;lḄLimitᔠorderbyᩭᵨ'ᐜn᣸¡ᑤ◀¢{ᦪḄ:deletefromteacherorderbydayslimit10;¤ᨵorderby¦ᨵV§Ḅ஺/D/768ᐕ¨©ᑤ◀'769delete-frorione,tᐕ()ᵨ+,Ḅjoin-..one_id=2;/0ᑤ◀34⊤ᑁḄ67.◤⌕ᐜ;<⊤=>?;<@Aᩩ஺◤⌕ᙠyzᦪ|⊤ᑁDᑖᡂᑤ◀.DeleteoneDeletetwo஺

88GH⊤Truncate"ª'deletefromtable;de1Af-AF»*nm1-R^rLR>*JIMueryOK.14rowsaffected<0.00sec>Ipysql>.|hnysql>insertintoteacherualuesீnull.«ᓟDI10ு5MueryOK-1rov/affected<0.05sec>nysql>select*fronteacher
--------------♦--------------------♦---------------------------------,----------------,itidt-nameiclass-nane!days!-♦-----------------1------------------♦---------------------------------♦---------------♦146AB
D'10
Truncate:1,®¯°±ᑤ◀Ḅᦪ஺2,²³qrZsḄ6.஺Delete⌲µᑤ◀஺truncate'ᑤ◀⊤w³⊤஺¶wᦪ
20134ᨴ2012:05replace,insertonduplicatekeyupdateUpdateWhere

89OrderbyLimit:wnysql>updateoneJointvioonone.public-field=twitwo_data=,y9whereone_id=3
QueryOK.2rowsaffected<0.05sec>Rowsmatched2Changed2“awnings0·⊤¶wpv^ql>select*fronone
updateonejointwoonone.pub1ic_field=two.publinysql>select*fronone
c_fieldsetone_data='x',two_data=,y'whereone_id=!one_idione_dataipublicieId!3;!1!K!10!3x!i302rowsinset<0.00sec>vnysql>select*fromtwo
!tvio_id!two_dataipublicieId
!3
y3041D402rowsinset<0.00sec>nysql>select*fromonejointwousing

90〉ᵨmysiam⊤:LpnpMrrmyRDNphp3.frm9KBÕ©n1^php_oneroom20130420.zip1KBtbl_name.frmroom.frm9KBTbl_name.mydroom.MYD0KBTbl_name.myiroom.MYI1KBÞRᦻà-1஺KQvnᜓÒᓽ஺◤⌕ḄW᎛Õ©Ö×ᑮØrᑮÙÚḄᦪ
ÛÜnysql>select*fromjoin_class
ERROR1146<42S02>Table9test.join_classJdoesn'texistUV᝞x᪵ḄâDᜐᳮḄinnodb⊤᪀Ḅᦻ஺ᑣᵨshowtablesWæçᑮè®éᵨḄ஺Iᫀ2)ᵨḄâᫀ'ëì'n³⊤᪀ᐭᦪ
Ḅsqlíᡂîà,ïð᝞◤⌕ñ᪀!ᦪ
Õ©nᦪ
᡻µᓽ஺ᑭᵨmysqlóôḄõᐹ÷ᡂḄ'®sqløḄ$ùᑖ஺::\DocunentsandSettingsMIdninistrator>nysqldunpIsagemysqldumpCOPTIONS]database_LtaAlesJ-»Rmysqldump[OPTIONS]—databases[OPTIONS]DB1[DB2DB3…])Rni/sqldunp[OPTIONS]-all-databases[OPTIONS]?ormoreoptions,usemysqldump$$helpI\DocunentsandSettingsXfldninistrator>ei\>cdanpXnysqli\anp

91ysql>bin

92i;sqldunp.exeú'r1,I.flûE/þIsayu-ni2/^qlduiiipLOFTIGHSJdatabase[tables]»Rmysqlduap[OPTIONS]—databases[OPTIONS]DB1CDB2DB3...])Rnspsqldunp[OPTIONS]-all-databases[OPTIONS]7ornoreoptions,usemysqldump——help®◤⌕ᙠmysqlIJµÿᡝ
᡻,ᓽ஺

93ᜓphp_oneᦪ:mysqldump-uroot-pphp_one>e:/php_one.sql|E\amp\mysql>nysqldunp-uroot-pphp_one>e/php_one.sql■EnterpasswordXMXMMMXMᜓḄᦪᡂḄsql᡻ᓽ஺ysql>soiecee/php_one.sq]ᙠmysqlᡝ
᡻ᓽ᝞"᡻#$ᙠᦻ&ᑁḄsql(ᵨsourceᢣ+,ᢣ-◤⌕᡻sqlḄ01ṹᦻ&:3ᵨḄᜓ451,ᜓ᦮7ᦪᑁḄ⊤Mysqldump-uroot-pdbname>bak.sql2,ᜓᦪᑁḄ9:⊤mysqldump-uroot-pphp_oneteacher_class>e:/php_one_teacher_class.sqlmysqldump-uroot-pphp_oneteacher_classtbl_nameltbl_name2tbl_name3>e:/php_one_teacher_class.sql

94;<2013=4ᨴ20?@AB14:37CD;<:CreateviewviewnameASselect_statement;mysql>createuiewu_teacherasselectid,tunanefrominfo_teacher
QueryOK,0rowsaffected<0.06sec>;<E7$ᙠFᦪGḄHI⊤J:£1createviewv_teacherasselectid,t_namefrominfo_teacher;►80001select*from(selectid/t_namefrominfo_teacher)astemp;;<KLMᨵᦪOPQ᡻RSḄselectTᡂUVRSḄᦪ஺;

95hysql>dropviewifexistsu_teacher
(QueryOK,0rowsaffected<0.00sec>[ᦋ;<Alterviewview_nameitLKHUH±i4bk4zbozjlame-pnp_one.u_naneaoesn-rpiFsql>alteruiewulasselect*frominfo_teacher
hueryOK,0rowsaffected<0.05sec)[ᦋ;<ᑁᡠ(ᵨḄ^_Ḅ`a;<`ab(ᵨ(^_ᑡ⊤ᓽ)alterviewv_teacher(vl,v2)asselectid.fghijkPQ;<,lᩖḄhijknᓫḄTᡂᐜ(ᵨ;select*fromjoin_info
:tc♦ic_nane!days'一一I1信.信!php0115!15iI2—信!php022818:I3I白!php0331!22!I4—5白
phpOllS20!8I6^子
php0228!22!—7php0115!15I—子8!php011515I—9李Iphp0331!151■!php0115:5!♦一Brov/sinset<0.00sec>

96;<Ḅ᡻Q|;<Ḅ᡻}~:$ᙠ᡻}~1,merge2,temptableᢣḄE7;<ᙠ᎛᡻᡻mergeᔠḄ᡻᡻Ḅ᎛ᡃ;<ḄsqlF᜜;<ḄsqlᔠᙠE஺ᨬ᡻Temptable⊤Ḅ᎛;<ᡠ(ᵨselectᡂE7Ḅ⊤஺ᙠḄ⊤ᑁ஺ᵨᡝCD;<mysql(ᵨEundefineḄᜐᳮ}~¢£¤ᙠᔠ¥⊤ᑁ⌱§஺¨i2013=4ᨴ20?@AB16:05E©sql45ᓫᐗ஺©ᑁᡠᨵsqlTᡂE7hi஺᝞᦮©ᡂ«¬ᕡḼᐰsqlv°᝞ᐸG²"E7ᜫ´஺¬ᕡḼ᦮745vᜫ´஺ᜫ´¬ᕡḼ᦮7Q|vMᨵ¬µḄ஺S¶ᦪ·ᑮ45Ḅ¹º»᝱஺s☢Ḅᱯឋ¨i஺

972QRK46S᝞UᜐᳮX1,ᜫ´b,·ᑮÀºÁÂ2,Mvᡂ«ÃÄḄᵨᡝÅ|¢ÆÇÈÉÊᑮ45ᑁḄᦪ[ᦋḄ஺ËÌᙠE©45ÃÍÎÏE7ÐÑᜓÒ஺YZᑭᵨinnodb$ÔÕÖḄ¨i?׫É:᡻:

98SQLSQLḄ᡻\ᑖᡂ24▤^1.᡻\▤^2,_᡻\`a,;bḄᦪdeḄ▤^ᐸgᡃiḄjkl>mn᡻\▤^Ḅ`a᝞aᵨo⌱q;b,ᑣs_᡻\`a;bᑮᦪdt஺uvḄ᡻\Iwx>;b,᡻\yz>yᡂ;b{|.}~◤⌕ᐵ;b஺£¤ØÙ$ᙠE7ÚÛḄÜÝAutocommit,w£¤ØÙÞÂpSFsql>showvariableslike9autoconmitJ
iUariable_naneiUalueiiautoconnitiOFFFOMinset<0.02sec)ᐵàbá᡻RSḄâã:äᙠᐸåæGÊᦪMᨵäÜᓄèéMᨵØÙ஺

99;bᡈ஺ᙠêqẠs᡻TᡠᨵḄsql஺ᑨìᔲvᡂ«(î┯ðᒹò~┯ð¥jk┯ðóiᘤ┯ð)஺ᡂØÙᑭᵨcommit´·ᑮÀºÁÂ஺RollbackḄjḄᢣÀõ¨iStarttransaction;)ᵨbegin஺ᐵࣤØÙ஺᝞¨iöJᡂᡈ´v¢£¤ØÙøᑴ·ᑮstartḄ»᝱஺ᡂ«commit;ᜫ´rollbackú▲ᙠinnodbûᦔ(DBD)ú
class!CREATETABLE'class'<'id'intNOTNULLAUTO_INCRENENT,'cz_money'decinal<10,2>DEFAULTNULLCOMMENT'þÿPR!MARVWFVv'id')>ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf84^----------j-----------------------------------------------------------------------------------------------------------------------------------------jḄᱯ:1,
ឋ஺2,ឋ஺3,◞ឋ஺4,ᢝឋ஺ACIDoᘤ

100ᦪd\|:ᙠḄ⊤>QRK4\ᦪdḄK4ᘤ>ᐵjj0஺᡻\K^ᵫsqlyᡂḄK^ṹ஺ᘤḄᐗ¡j>᡻\ṹ஺ᘤCreatetrigger=¢j᡻\ឋṹ----M¤ᐭinsertᑤ◀delete¦ᦋupdateQueryjḄ0¨᡻\©ª᡻\©«>Afterbeforepysqlᵫ0¨ªjᙠK¬ᡂ®¯°j஺Beforeinsert,beforedelete,beforeupdateAfterinsert,afterdelete,afterupdatej±>ᙠ²4⊤Ḅ³´0¨Ḅ³´|஺᡻ṹ-----SQL-µ¶ᡂḄṹ஺ᱯj>ᓽupdateczbk_studentsetstu_money=stu_money+20whereid=l;

1011,ᘤ!஺2,"#mysql$%ᢝ&'()ᘤ஺nysql>createtriggerjiaobanfei_newafterupdateonczbk_student->foreachrow->updateclasssetcz_noney=cz_noney+20->
ERROR1235*42000ThisuersionofMySQLdoesn'tyetsupport'multipletriggersv/iththesaneactiontineandeuentforonetableJmysql>.___________________________________________________________________________+ᳮᘤ:ᑤ◀Droptriggertrigger_name;01Showcreatetriggertrigger_name;iiysql>droptriggerjiaobanfei
UueryOK,0rowsaffected<0.00sec>iiysql>createtriggerjiaobanfeiafterupdateonczbkstudent-->foreachrow->updateclasssetcz_nonesi=cz_iioney+20->
buery0K_0rowsaffected<0.05sec>ᙠᘤᑁ4567Ḅᦪ::OldnewᑭᵨᑁḄnew=oldᩭ?ᡂ;

102//1000950(old.stu_money-new.stu_money)►904|droptriggerjiaobanfei;createtriggerjiaobanfeiafterupdateonczt907foreachrow---------updateclasssetcz_money=cz_money4(old.stu_r909;910Old:BCᡠᙠ⊤FḄᦪ:ᙠGH#7Ḅᦪ:஺IḄᦪ:஺New:BC⊤FGHJKᜐᳮ?MḄᦪ:஺ᦪ:NO6ḄPQ஺OinsertᕖSTᵨ஺IdOdeleteᕖSTᵨnewnysql>createtriggerruxueafterinsertonczbk__studentforeachrow)updateclasssetstu_count=stu_count+oId.id/9ERROR1363ThereisnoOLDrowinonINSERTtriggernysqi>crearetriggerruixueatrerdeleteonczbk__srudeni;foreachrow->updateclasssetstu_count=stu_count+new.id->
I-------------------------------------------------ERROR1363ThereisnoNEWrowinonDELETEtriggermysql>.

103᝞a4¸¹>ᵫ3ᩩsql-µ¶ᡂº»9351.-µᵪᡂ½µᙽbe®nend`ᵨᩭ᪗Á-µᙽ.936droptriggerruxue;2,-µᙽḄµÃÄÅÆḄ⍗µ`ÈÉ>ᑖS.delimiter$$createtriggerruxueafterinsertonczbk_studentÊ\ᵫoᘤ¸¹ᑁ)ᵨᑖS|Ë̵`ÈÉ.939foreachrow²´ᶔ\ÎᡝÐÑᑮᑖS0.mºÌᳮÒᡂ940begin᨟¸¢ᑁÔ-µ`È,Õ᦮4×ØᘤḄ-µ`È.941updateclasssetstu_count=stu_count+l;º»¦ᦋÊ\Ḅ-µ`ÈÙÚᑮÛḄ.942updateclasssetcz_money=cz_money+20;Delimiter-µyᡂQR-µ`È943end944$$ÜÝ®ᦋyᙠ»Þ945|946delimiter;QA7ßàáâãᘤInsertintoonduplicatekeyupdate1.äᐭ.åæçBeforeinsert,afterinsert.2..å¤ᐭ.Beforeinsert*beforeupdate,afterupdateRepalce1,¤ᐭBeforeinsert,afterinsert2,èᣚBeforeinsert.beforedelete,afterdelete,afterinsert!AfterIbeforeinsert.insertintorecordvaluesbeforeinsert*);After|beforeupdate.AfterIbeforedelete;SQLx2013y4ᨴ26{|}~17:27SQL:᪀ᓄ0஺Ox஺Oᵫ+ᳮᦪ:Ḅx஺ᐗᦪ:ᦪ:&ᦪᑴ#-ᙽ/**/

104select*fromjoin__teacher#whereid>10;954select*fromjoin_teacher--whereid>10;►956;|`ÈÙ:pysql>selectfromjoin_teacher\^f1韩信2李白>g€3次子春ow4rowsinset.00sec>>g€ysql>selectfromjoin_iTᵨdelimiterᩭᦋ¡¢Delimiter$$ïð£¤!NO஺phpᨵ¦§¨©ª«¬᝞nysql>shovivariableslike9charzJᵨᡝ¯°±°±)setSet!=³஺´µ¶ᑖ¨©=£¤¸ᵨᡝ¯°±◤⌕ᙠᵨᡝ#»¼@᪗¿஺nysql>set=>>òQueryOK,0rowsaffected<0.00sec>

105mysql>setwho=ÀÁÂERROR1193Unknownsystemuariable9who,nysql>selectwho="ÀÁ'ÂERROR1054<42S22>UnknowncolumnCho'in"fieldlistJÄÅselect¡45Á#ḄḄ³:nysql>select(?whoRKó——♦!PwhoiSetOÆḄ´Ç³ḄÈÉÊË
0JB2►977set@total=(selectcount(*)fromjoin_teacher);°±)selectinfooSelect£¤ᑡ⊤⊤ÍÉ஺஺஺Intoᑡ⊤஺nysql>nysql>select10,15,20into(?aPb,(?c
xQueryOK,1rowaffected<0.00sec>hysql>selectc_naneFromjoin_classv/hereid=3into(?c_nane
Uuery0K,1rowaffected<0.00sec>selectinto@var⌕Î$ÏÐ஺᝞ÑÏЧÒÓ┯Õᡈὅ$ØᨬJḄᦪ:ᐭᑮᑁ஺nysql>selectc_nanefromjoin_classwhere1into(?c_nane
ERROR1172<42000>Resultconsistedofmorethanonerowᑭᵨselect¡ḄÜᑖ⊤ÍÉÍᑮ´Ç³Ḅ"Ḅ:Ýysql>selectPwho="ßà"Â-B1tWilttX+---------------------------♦(?who=ßà♦----------------------------♦!0!Tᵨ=ḄÈÉ;

106nysql>selectOwho=õ"ò!Mho=>÷õ”!+-----------------------------♦;÷õ
+-----------------------------+1rowinset<0.00sec>mysql>select(?who
+----------♦'(?whoi+----------+;÷õ:+----------+1rov/inset<0.00sec>=á6dzâOᙠselect¡ᑁNᡂµᐵ¨ä஺TᵨÆḄdz=஺᪵〉ᵨsetomysql>setPi=>ØQueryOK,0rowsaffected<0.00sec>nysql>select(?i
+---------+!(?i!!S’
♦----------♦ysql>select*fromteacherwhere÷ú’
hnptyset<0.03sec>TᵨOᙠ⊤ÍÉᡈὅTᵨselect0ᑮᓽ஺1,èᵨé஺ᵨᡝ°±ḄᦪOᐰëḄìᦪᑁᵨ`஺íᙠëÜèᵨéᦪᑁ°±Ḅ஺2,ᨵᦔ}஺Òï¢ìðñ¢`஺:ûᦪ2013y4ᨴ26{|}~18:15

107ᑁ(ᦪᦪ³Rand()5ᑮ1-0HòḄóôᦪinysql>selectvandO
'randO
0.4194232154818468+-----------------------------------᝞õ5ᑮ5ᑮ10?5+(0-5)5+rand()*5;ö᦮iysql>selectfloor*5♦5>floor*5+5>!9:--------------------------------------+rowinset<0.00sec>Éᓄ:formathysql>selectFormat<1234567.12342>
iformat<1234567.1234,2>!+-----------------------------------------------+
1,234,567.12:1rowinset<0.00sec>7ò{}:Now();Unix_timestamp();mysql>selectunix_tinestanp<>
♦-------------------------!unix_tinestanp<>i+----------------------------------+!1366971943!tlrowinset<0.00sec>From_unixtime();

108nysql>selectfron_unixtine<12345>
♦-----------------------------------------♦!from_unixtine<12345>!+-----------------------------------------+
1970-01-0111:25:45:+-----------------------------------------♦1rowinset<0.00secுmysql>selectfron_unixtine>
+--------------------------------------------------------------+!fron_unixtine>i+--------------------------------------------------------------+!2013-04-26182655:♦--------------------------------------------------------------♦1_WIVJinw૙1:__<0.00sec)____________________________£øConcatO£øðñSubstringì£øùúû(üöýþ`Âùúû(ÿ1
mysql>^2^2)
!substringKýþÿI2,2>i

+------------------------------------------------------+1rowinset<0.00sec>nysql>selectsubstring<,abed*2/2>
isubstring!ibeCharjength();Length();

109nysql>selectlength
+-------------------+ilengthi+-------------------+!4!♦--------------------------------------♦1rowinset<0.00sec>mysql>selectlengthீ'')♦-------------------------------------------------!length!♦-------------------------------------------------♦!8!♦------------------------------------------------♦1rowinset<0.00sec>muscrl>_________________________________tysql>selectchar_length<"'ுchar_length•>------------------------L----k.4irowinset<0.00secுLpad◤⌕⊡Ḅ⊡Ḅ⊡;"#⊡$ᐸ&:Md5()

110!md5mysql>selectpassviordC*1J>
*----1--------------------------------*'password!■----------------------------------------------------------------------------------+!*E6CC90B878B948C35E92B003C792C46C58C4AF40!■----------------------------------------------------------------------------------.1FOVJinset<0.00sec>nysql>selectshal
+--------------------------------------------------------------------------------+!shalீFு!■--------------------------------------------------------------------------------♦!356al92b?913b04c54574dl8c28d46e6395428ab!+--------------------------------------------------------------------------------+1rowinset<0.00sec>ᦪ)*ᦪAbs(X),+,)abs(-10.9)=10Format(X,D),-.ᓄᓟᑖ2ᦪ)format(1234567.456,2)=1,234,567.46Ceil(X),ᔣ45᦮ceil(10.1)=11Floor(X),ᔣ75᦮floor(10.1)=10Round(X),89:ᐭ<᦮Mod(M,N)M%NMMODN=>10%3=lPi(),?@ᙊᕜ᳛Pow(M,N)MANSqrt(X),DEFG᪷Rand(),IJᦪTRUNCATE(XD)K5D2LᦪMNOP*ᦪNow(),current_timestamp();QROPMNCurrent_date();QROPcurrent_time();QRMNDate(,yyyy-mm-ddHH;ii:ss');?5OPSᑖTime(/yyyy-mm-ddHH;ii:ss');?5MNSᑖDate_format(1yyyy-mm-ddHH;ii:ss','%D%y%a%d%m%b%j');Unix_timestamp();?@unixMNᡙ

111From_unixtime();஻VMNᡙ?@MN*ᦪLENGTH(sWing)஻stmng⁚CHAR_LENGTH(string)//stringḄYZᦪSUBSTRING(str,position[,length])஻VstrḄposition
,5lengthZREPLACE(str,search_str,replace_str)஻ᙠstrhᵨreplace_strmᣚsearch_strINSTR(string.substring)஻ol"lsubsiring✌tᙠsiringᧅvḄwᵨCONCAT(string[,...])஻xyCHARSET(str)஻oz{LCA5E(string)஻|ᣚᡂLᑏLEFT(string,length)஻Vstring2hḄ"#5lengthZ‘W$LOAD_FILE(file_name)஻Vᦻ5ᑁLOCATE(substring,string[,start_position])//INSTR,!ᢣ
2:LPAb(string,length,pad)஻:ᵨpadᙠstringᜮᑮhlengthLTRIM(string)஻<◀R-REPEAT(string,count)஻;counttRPAD(string,length,pad)஻ᙠstrᵨpad⊡ᐙ,flᑮlengthRTRIM(string)஻<◀-STRCMP(stringl,string2)஻⌲¡¢£ᜧL¥¦*ᦪ$CASEWHEN[condition]THENresult[WHEN[conditionyTHENresult...][ELSEresult]END§ᑖ¨]F(exprl,expr2,expr3)©ᑖ¨஺«ᔠ*ᦪCount()Sum();Max();

112Min();Avg();Group_concat()ᐸ&ᵨ*ᦪMd5();Default();®¯°Ḅ±²ᵨᡝ´*ᦪµ¶*ᦪ-·´*ᦪ2013¸4ᨴ27OºP»17:19⌕¼:*ᦪ½¾ᦪᑡ⊤*ᦪÁoz)ÂÃ$´$Createfunction*ᦪ½(¾ᦪᑡ⊤)oz)ÅÆ*ᦪÁ982983delimiter$$)84createfunctionsayHello()returnsvarchar(20)985begin986return'helloworld!';987end988$$989►99©delimiter;Çᵨ:nysql>selectsayHelloOisayHelloO!helloworld?iÈÉ$*ᦪÊËQRḄᦪÌÍÎḄÏÐᵨͽ.*ᦪ½ḄÒ.Çᵨ;

113&biysql>selectsayHelloO
ERROR1305<42l3fiiail--ElLNnTTnM.sa^HellodoesnotexistÓysql>selectphp_one.sayHelloOi_♦-------------------------------------+τphp_one.sayHelloO+-------------------------------------+ihelloworld?!+-------------------------------------+□Lrowinset<0.00sec)sqlhḄ¥¦Ôᑴ$ᑖ¨Ifᩩ1thenᩩ1×᡻ÙḄÂÚElseifᩩ2thenᩩ2×᡻ÙḄÂÚElse4☢ḄᩩᐰÝÞ×᡻ÙḄÂÚEndif;ElseifßelseÝÊÏḕᶍḄ஺99299?delimiter$$gcreatefunctionfuncl()returnsvarchar(20)995begin996--hourÏ?@QRMNḄLMSᑖifhour(now())>=18then998return,â999E®ise1000return'ã'A1001endif;1002end1003$$delimiter;äåWhileᩩdoäåÁEndwhile;

1141007delimiter$$1008createfunctionfunc2()returnsint1009beginlei஺--1-1஺ᙠ1011set@i=1;1012set(Ssum=0;10131014while=d஺set@sum=@sum+@i;1016set@i=@i+1;1017endwhile;►101811019return@sum;1020end1021$$1022delimiter;äåḄæRçèLeavebreakçèäåIteratecontinueçèQRäåÈÉÞÊ᪷ÌleaveßiterateᡠᙠḄ2ᩭëçèìZäåíÊᵫäåḄ᪗ðᩭëḄ஺äåḄ᪗ðñäå½஺᪗ð$whileEndwhile᪗ð54set(alsum=05w:|while@i<10do56set@i=@i+1;57if@i=5then--loovc?w;--------iteratew;60endif;set(a)sum=@sum+62endwhilef^wj63*ᦪᑁÐᵨḄòó@varḄÒ.ôQõᐰöòó*ᦪᑁß*ᦪ᜜øᵨ஺*ᦪḄ¾ᦪ$¾ᦪ᪵◤⌕úÅÆ஺¾ᦪ½ÅÆ

115ᓃ/ᓃdelimiter$$1072dropfunctionifexistssayHell஺
createfunctionsayHellokusernamevarchar(ieij)|returnsvarchar(20)1074beginreturnconcat('hello'.username):1076end------------------------------1077$$1078delimiter;1079Z*ᦪÏᨵ§Z¾ᦪÐᵨ⌹ýᑖᒘ஺*ᦪÿḄᵨdeclare஺◤⌕ᢣᢣdefault஺082083dropfunctionifexistsfunc2;084delimiter$$085createfunctionfunc2()returnsint086begin0”--1-10Ḅ018declareiintdefault0;declaretotalintdefault0;090whilei<=10dosettotal=total+i;092seti=i+1;093endwhile;|094095returntotal;096end097$$098delimiter;099mysql▤஺ᵨᡝ!஺