mysql常用語(yǔ)句總結(jié)

《mysql常用語(yǔ)句總結(jié)》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在應(yīng)用文檔-天天文庫(kù)。

1、一、數(shù)據(jù)庫(kù)操作1．創(chuàng)建數(shù)據(jù)庫(kù)：Mysql>CREATEDATABASEdatabaseName;Mysql數(shù)據(jù)庫(kù)的數(shù)據(jù)文件默認(rèn)存放在/usr/local/mysql/var/目錄下，每個(gè)數(shù)據(jù)庫(kù)對(duì)應(yīng)一個(gè)子目錄，用于存儲(chǔ)數(shù)據(jù)表文件；新建的空數(shù)據(jù)庫(kù)在/usr/local/mysql/var/目錄下會(huì)自動(dòng)生成一個(gè)與新建的庫(kù)名相同的空文件夾。例：mysql>createdatabasetestdb;???????mysql>createdatabaseifnotexiststestdb;???????mysql>createschemaifnotexistsstudentcharacterset'

2、gbk'collate'gbk_chinese_ci';?2．刪除數(shù)據(jù)庫(kù)Mysql>DROPDATABASEdatabseName;例：mysql>dropdatabasetestdb;?3．更改表名命令：altertable原表名renameto新表名;?????????mysql>altertabletb1renametotb11;數(shù)據(jù)庫(kù)進(jìn)行改名。?4．改變數(shù)據(jù)表引擎?????????mysql>altertabletb1engine="MyISAM";?5．壞庫(kù)掃描修復(fù)cd/var/lib/mysql/xxx&&myisamchkplaylist_block二、數(shù)據(jù)表操作1．創(chuàng)建數(shù)

3、據(jù)表Mysql>CREATETABLE[ifnotexists]tableName(字段1名稱類型，字段2名稱類型，…，PRIMARYkEY(主鍵名))mysql>createtabletable_name(column_namedatatype{identity

4、null

5、notnull},f_timeTIMESTAMP(8),…)ENGINE=MyISAMAUTO_INCREMENT=3811DEFAULTCHARSET=utf8;例：???????mysql>createtabletb(idintunsignednotnullauto_incrementprimarykey,Nam

6、echar(20)notnull,Agetinyintnotnull);???????mysql>createtabletb(idintunsignednotnullauto_increment,Namechar(20)notnull,Agetinyintnotnull,primarykey(id));???????mysql>createdatabasemydb;???????mysql>usemydb;???????mysql>createtablestudents(namechar(20)notnull,agetinyintunsigned,genderchar(1)notnull

7、);???????mysql>createtablecourses(IDtinyintunsignednotnullauto_incrementprimarykey,Cousevarchar(50)notnull);????????mysql>createtablecourses(namechar(20)notnull,agetinyintunsigned,genderchar(1)notnull);?復(fù)制數(shù)據(jù)表：從一張表中查出需要的數(shù)據(jù)并創(chuàng)建為一個(gè)新表：???????create[temporary]table新表名select*from舊表名;?例：??????mysql>creat

8、etabletestcoursesselect*fromcourseswhereCID<=2;?如果很多字段的屬性沒(méi)有存在，則需要自己重新定義：select組合成需要的語(yǔ)句。例：createtableteststu(idintnotnullauto_increment,primarykey(id))selectname,agefromstudents;?復(fù)制數(shù)據(jù)表：以其它表為模板，創(chuàng)建一個(gè)新表,字段的屬性還會(huì)存在，復(fù)制數(shù)據(jù)庫(kù)結(jié)構(gòu)，創(chuàng)建一個(gè)結(jié)構(gòu)完全相同的表：????????create[temporary]table[ifnotexists]表名like已存在的表名;例：???????my

9、sql>createtabletestlikecourses;?mysql>SHOWCREATETABLEtableName???#顯示創(chuàng)建tableName表的語(yǔ)句2．刪除表Mysql>DROPTABLEdatabaseName.tableName;等同于：Mysql>userdatabaseName;Mysql>DROPTABLEtableName;???????mysql>droptabletestcourses;?3．修改表