oracle圖形界面操作和數(shù)據(jù)分頁-5 .mysql

5.MySQLCONTENTS簡介SQL語句語法導(dǎo)入導(dǎo)出

數(shù)據(jù)庫列類型1:MySQL簡介SXTMySQL是一種關(guān)系數(shù)據(jù)庫管理系統(tǒng)，是一種開源軟件由瑞典MySQLAB公司開發(fā)，2008年1月16號(hào)被Sun公司收購。2009年，SUN又被Oracle收購MySQL軟件采用雙授權(quán)政策，分為社區(qū)版和商業(yè)版。由于體積小、速度快、總體擁有成本低，尤其是開放源碼特點(diǎn)，一般中小型網(wǎng)站的開發(fā)都選擇MySQL作為網(wǎng)站數(shù)據(jù)庫。由于其社區(qū)版的性能卓越，搭配PHP和Apache可組成良好的開發(fā)環(huán)境。MySQL能夠工作在眾多不同的平臺(tái)上1:MySQL簡介SXT端口：3306默認(rèn)用戶：root字符集：默認(rèn)字符集latin1，應(yīng)設(shè)置為gbk或utf-8安裝時(shí)可以啟動(dòng)遠(yuǎn)程登錄、創(chuàng)建匿名賬戶可安裝圖形化操作界面，方便操作安裝mysql錯(cuò)誤解決方案手動(dòng)刪除mysql安裝目錄重新運(yùn)行配置向?qū)ySQLInstanceConfig.exe刪除C:\ProgramData\MySQL目錄1:MySQL簡介SXT啟動(dòng)MySQL服務(wù)netstartmysql連接MySQLmysql-h127.0.0.1-uroot–p退出MySQLexitQuit關(guān)閉MySQL服務(wù)netstopmysql2.MySQL列類型SXT數(shù)值類型：SMALLINT:2個(gè)字節(jié)INT:4個(gè)字節(jié)//ageint(10)INTEGER:INT的同義詞BIGINT:8個(gè)字節(jié)FLOAT:4個(gè)字節(jié)DOUBLE:8個(gè)字節(jié)//scorefloat(10,2)MySQL支持選擇在該類型關(guān)鍵字后面的括號(hào)內(nèi)指定整數(shù)值的顯示寬度(例如，INT(4))。顯示寬度并不限制可以在列內(nèi)保存的值的范圍，也不限制超過列的指定寬度的值的顯示2.MySQL列類型SXT字符串(字符)類型CHAR：固定長度字符串sexchar(2)VARCHAR:可變長度字符串namevarchar(20)VARCHAR使用起來較為靈活，CHAR處理速度更快TEXT:非二進(jìn)制大對(duì)象（字符）BLOB:二進(jìn)制大對(duì)象（非字符）2.MySQL列類型SXT日期/時(shí)間類型：DATE:YYYY-MM-DDDATETIME:YYYY-MM-DDHH:MM:SSTIMESTAMP:YYYY-MM-DDHH:MM:SSTIME:HH:MM:SSYEAR:YYYY主鍵自增不使用序列，通過auto_increment3.SQL語句語法SXTSQL語言包含4個(gè)部分：

數(shù)據(jù)定義語言（如create,drop,alter等語句）數(shù)據(jù)查詢語言（select語句）數(shù)據(jù)操縱語言（insert,delete,update語句）數(shù)據(jù)控制語言（如mit,rollback等語句）數(shù)據(jù)操縱語言針對(duì)表中的數(shù)據(jù)，而數(shù)據(jù)定義語言針對(duì)數(shù)據(jù)庫或表3.SQL語句語法SXT數(shù)據(jù)定義語言createdatabaseschool;/*創(chuàng)建數(shù)據(jù)庫*/showdatabases;/*顯示所有數(shù)據(jù)庫selectdatabase()*/useschool;/*指定默認(rèn)數(shù)據(jù)庫*/createtablestudent(/*創(chuàng)建表*/idint(10)primarykeyauto_increment,/*主鍵，自增*/namevarchar(8),sexchar(1),scorefloat(6,2));3.SQL語句語法SXT數(shù)據(jù)定義語言showtables;/*顯示當(dāng)前庫中表清單*//*顯示指定表結(jié)構(gòu)showcolumnsfromstudent;*/describestudent;showcreatetablestudent;/*顯示建表sql語句*/droptablestudent;/*刪除表*/dropdatabaseschool;/*刪除數(shù)據(jù)庫*/3.SQL語句語法SXT數(shù)據(jù)操縱語言insertintostudentvalues("張三","t",87.5);insertintostudentvalues(null,"張三","t",87.5);//insertintostudent(name,sex,score)values("張三","t",87.5);select*fromstudent;insertintostudentvalues(null,"李四","男",89);altertablestudentmodifysexchar(2);insertintostudentvalues(null,"李四","男",89);select*fromstudent;3.SQL語句語法SXT數(shù)據(jù)操縱語言u(píng)pdatestudentsetsex="女";select*fromstudent;updatestudentsetsex="男"whereid=1;select*fromstudent;updatestudentsetname="王五",score=100whereid=2;select*fromstudent;deletefromstudentwherename="王五";select*fromstudent;deletefromstudent;select*fromstudent;3.SQL語句語法SXT數(shù)據(jù)查詢語言（select語句）select*fromstudent;selectid,name,scorefromstudent;select*fromstudentwhereid<5andsex=“男”;selectcount(*)fromstudent;selectmax(score)fromstudent;selectid,name,scorefromstudentorderbyscoredesc;select*fromstuwherenamelike"張%";3.SQL語句語法SXT更改表結(jié)構(gòu)語句altertablestudentaddbirthdate;insertintostudentvalues(null,"趙六","男",100,now());insertintostudentvalues(null,"趙六","男",100,“1980-12-23");altertablestudentchangebirthbirthdaydate;altertablestudentmodifysexchar(4)notnull;altertablestudentmodifysexchar(2)afterbirthday;altertablestudentrenameasstu;altertablestudropbirthday;3.SQL語句語法SXT多表查詢和外鍵關(guān)聯(lián)createtableperson(idintprimarykey,namevarchar(6),sexchar(2),ageint(10));insertintopersonvalues(1,"張三","男",45);insertintopersonvalues(2,"李四","男",32);insertintopersonvalues(3,"王五","女",32);

createtablepet(idintauto_increment,namevarchar(6),masteridint,primarykey(id),constraintfkforeignkey(masterid)referencesperson(id));3.SQL語句語法SXT多表查詢和外鍵關(guān)聯(lián)insertintopet(name,masterid)values("happy",1);insertintopet(name,masterid)values(“l(fā)ucky",1);…………select*frompersonp1,petp2wherep1.id=p2.masterid;select*frompersonp1,petp2wherep1.id=p2.masteridandp1.id=1;altertablepetdropforeignkeyfk;altertablepetaddconstraintfkforeignkey(masterid)referencesperson(id)ondeletecascadeonupdatecascade;updatepersonsetid=6whereid=2;deletefrompersonwhereid=6;3.SQL語句語法SXT分頁語句select*fromtablelimit(start-1)*limit,limit;其中start是頁碼limit是每頁顯示的條數(shù)。4.導(dǎo)入導(dǎo)出SXT命令