sql练习

sql(structuredquerylanguage)½á¹¹»¯²éѯÓïÑÔmysql³¬¼¶¹ÜÀíÔ±µÄÓû§ÃûºÍÃÜÂ룺root¼ì²émysqlÊÇ·ñ°²×°³É¹¦£ºmysql-uroot-prootshowdatabases;´´½¨Ò»¸öÃû³ÆΪmydb1µÄÊý¾Ý¿â¡£createdatabaseifnotexistsmydb1;showdatabases;´´½¨Ò»¸öʹÓÃutf-8×Ö·û¼¯µÄmydb2Êý¾Ý¿â¡£createdatabasemydb2charactersetutf8;´´½¨Ò»¸öʹÓÃutf-8×Ö·û¼¯£¬²¢´øУ¶Ô¹æÔòµÄmydb3Êý¾Ý¿â¡£createdatabasemydb3charactersetutf8collateutf8_general_ci;²é¿´¿âµÄ´´½¨Ï¸½Úshowcreatedatabasemydb1;ɾ³ýÇ°Ãæ´´½¨µÄmydb1Êý¾Ý¿âdropdatabaseifexistsmydb1;²é¿´·þÎñÆ÷ÖеÄÊý¾Ý¿â£¬²¢°ÑÆäÖÐijһ¸ö¿âµÄ×Ö·û¼¯ÐÞ¸ÄΪgb2312;alterdatabasemydb2charactersetgb2312;Êý¾Ý¿âµÄ±¸·ÝºÍ»Ö¸´£º×¼±¸Êý¾Ý¿âºÍÊý¾Ýcreatedatabaset;uset;createtablea(idint);insertintoa(id)values(1);±¸·ÝÊý¾Ý¿âmysqldump-uroot-ptc:\t.sql(²»ÊÇsqlÃüÁËüÊÇÒ»¸öwindowÃüÁî)dropdatabaset;»Ö¸´Êý¾Ý¿â1(mysqlÃüÁʽ)createdatabaset;uset;sourcec:\t.sql²é¿´»Ö¸´Ð§¹û£ºselect*froma;»Ö¸´Êý¾Ý¿â2(windowsÃüÁʽ)mysql-uroot-proottc:\t.sql()createtableaa(b1float(10,2),b2decimal(10,2));insertintoaa(b1,b2)values(12345678.12,12345678.12);´´½¨Ò»¸öÔ±¹¤±ícreatetableemployee(idint,namevarchar(20),sexchar(4),birthdaydate,entry_datedate,jobvarchar(40),salarydecimal(10,2),resumetext)charactersetutf8collateutf8_general_ci;ÔÚÉÏÃæÔ±¹¤±íµÄ»ù±¾ÉÏÔö¼ÓÒ»¸öimageÁС£altertableemployeeaddimageblob;²é¿´±íµÄ½á¹¹descemployee;²é¿´±í¸üΪÏêϸµÄÐÅÏ¢showcreatetableemployee;ÐÞ¸ÄjobÁУ¬Ê¹Æ䳤¶ÈΪ60¡£altertableemployeemodifyjobvarchar(60);ɾ³ýsexÁС£altertableemployeedropsex;±íÃû¸ÄΪuser¡£renametableemployeetousers;²é¿´Êý¾Ý¿âÖÐËùÓбíshowtables;Ð޸ıíµÄ×Ö·û¼¯Îªgb2312altertableuserscharactersetgb2312;ÁÐÃûnameÐÞ¸ÄΪusernamealtertableuserschangecolumnnameusernamevarchar(40);ɾ³ý±ídroptablea;\s£ºÏÔʾµ±Ç°µÄϵͳ״̬ʹÓÃinsertÓï¾äÏò±íÖвåÈëÈý¸öÔ±¹¤µÄÐÅÏ¢¡£insertintousers(id,username,birthday,entry_date,job,salary,resume)values(1,'aa','1980-09-09','1980-09-09','aaa',1000.00,'aaaaa');select*fromusers;²åÈëµÄϸ½Ú1£ºinsertintousers(id,username,birthday,entry_date,job,salary,resume)values('2','bb','1980-09-09','1980-09-09','aaa','1000.00','aaaaa');²åÈëµÄϸ½Ú2£ºinsertintousersvalues('3','cc','1980-09-09','1980-09-09','aaa','1000.00','aaaaa');²åÈëµÄϸ½Ú3£º(ÖÐÎÄÊý¾Ý²åÈëµÄÎÊÌâ)insertintousers(id,username,birthday,entry_date,job,salary,resume)values(4,'Áî³å','1980-09-09','1980-09-09','aaa',1000.00,'aaaaa');ÂÒÂëµÄ½â¾ö£º1.½â¾ö²åÈëµÄÎÊÌâshowvariableslike'character%';setcharacter_set_client=gb2312;insertintousers(id,username,birthday,entry_date,job,salary,resume)values(4,'Áî³å','1980-09-09','1980-09-09','aaa',1000.00,'aaaaa');2.½â¾öÏÔʾµÄÂÒÂëÎÊÌâshowvariableslike'character%';setcharacter_set_results=gb2312;select*fromusers;½«ËùÓÐÔ±¹¤Ð½Ë®ÐÞ¸ÄΪ5000Ôª¡£updateuserssetsalary=5000;½«ÐÕÃûΪ¡¯zs¡¯µÄÔ±¹¤Ð½Ë®ÐÞ¸ÄΪ3000Ôª¡£updateuserssetsalary=3000whereusername='aa';½«ÐÕÃûΪ¡¯aaa¡¯µÄÔ±¹¤Ð½Ë®ÐÞ¸ÄΪ4000Ôª,job¸ÄΪccc¡£updateuserssetsalary=4000,job='ccc'whereusername='aa';½«aaµÄнˮÔÚÔ­Óлù´¡ÉÏÔö¼Ó1000Ôª¡£updateuserssetsalary=salary+1000whereusername='aa';ǧÍò×¢Ò⣺(×îºÃÏÈдwhereÌõ¼þ£¬ÔÙдǰÃæµÄ)updateuserssetusername='xxx'whereid='4';ɾ³ý±íÖÐÃû³ÆΪ¡¯zs¡¯µÄ¼Ç¼¡£deletefromuserswhereusername='aa';ɾ³ý±íÖÐËùÓмǼ¡£deletefromusers;ʹÓÃtruncateɾ³ý±íÖмǼ¡£truncatetableusers;²éѯ±íÖÐËùÓÐѧÉúµÄÐÅÏ¢¡£select*fromstudent;²éѯ±íÖÐËùÓÐѧÉúµÄÐÕÃûºÍ¶ÔÓ¦µÄÓ¢Óï³É¼¨¡£selectname,englishfromstudent;¹ýÂ˱íÖÐÖظ´Êý¾Ý¡£selectdistinctmathfromstudent;ÔÚËùÓÐѧÉú×Ü·ÖÉϼÓ10·ÖÌس¤·Ö¡£selectname,(chinese+english+math)+10fromstudent;ʹÓñðÃû±íʾѧÉú·ÖÊý¡£selectnameasÐÕÃû,(chinese+english+math)+10as×Ü·Öfromstudent;selectnameÐÕÃû,(chinese+english+math)+10×Ü·Öfromstudent;²éѯÐÕÃûΪwuµÄѧÉú³É¼¨select*fromstudentwherename='ÍõÎå';²éѯӢÓï³É¼¨´óÓÚ90·ÖµÄͬѧselect*fromstudentwhereenglish'90';²éѯ×Ü·Ö´óÓÚ200·ÖµÄËùÓÐͬѧselect*fromstudentwhere(math+english+chinese)200;²éѯӢÓï·ÖÊýÔÚ80£­90Ö®¼äµÄͬѧ¡£select*fromstudentwhereenglish80andenglish90;select*fromstudentwhereenglishbetween80and90;²éѯÊýѧ·ÖÊýΪ89,90,91µÄͬѧ¡£select*fromstudentwheremath=89ormath=90ormath=91;select*fromstudentwheremathin(89,90,91);²éѯËùÓÐÐÕÀîµÄѧÉú³É¼¨¡£select*fromstudentwherenamelike'Àî%';²éѯÊýѧ·Ö80£¬ÓïÎÄ·Ö80µÄͬѧ¡£select*fromstudentwheremath80andchinese80;¶ÔÊýѧ³É¼¨ÅÅÐòºóÊä³ö¡£selectname,mathfromstudentorderbymath;¶Ô×Ü·ÖÅÅÐòºóÊä³ö£¬È»ºóÔÙ°´´Ó¸ßµ½µÍµÄ˳ÐòÊä³öselectname,(chinese+math+english)fromstudentorderby(chinese+math+english)desc;¶ÔÐÕÀîµÄѧÉú×ܳɼ¨ÅÅÐòÊä³öselect*fromstudentwherenamelike'Àî%'orderby(chinese+math+english)desc;ͳ¼ÆÒ»¸ö°à¼¶¹²ÓжàÉÙѧÉú£¿selectcount(*)fromstudent;selectcount(name)fromstudent;ͳ¼ÆÊýѧ³É¼¨´óÓÚ90µÄѧÉúÓжàÉÙ¸ö£¿selectcount(*)fromstudentwheremath90;ͳ¼Æ×Ü·Ö´óÓÚ250µÄÈËÊýÓжàÉÙ£¿selectcount(*)fromstudentwhere(math+english+chinese)250;ͳ¼ÆÒ»¸ö°à¼¶Êýѧ×ܳɼ¨£¿selectsum(math)fromstudent;ͳ¼ÆÒ»¸ö°à¼¶ÓïÎÄ¡¢Ó¢Óï¡¢Êýѧ¸÷¿ÆµÄ×ܳɼ¨selectsum(chinese),sum(math),sum(english)fromstudent;ͳ¼ÆÒ»¸ö°à¼¶ÓïÎÄ¡¢Ó¢Óï¡¢ÊýѧµÄ³É¼¨×ܺÍselectsum(chinese+math+english)fromstudent;ͳ¼ÆÒ»¸ö°à¼¶ÓïÎijɼ¨Æ½¾ù·Öselectsum(chinese)/count(chinese)fromstudent;ͳ¼ÆÒ»¸ö°à¼¶ÓïÎijɼ¨Æ½¾ù·Öselectavg(chinese)fromstudent;ÇóÒ»¸ö°à¼¶×Ü·Öƽ¾ù·Öselectavg(chinese+math+english)fromstudent