资源描述：
《MYSQL数据库操作大全(名师整理版)》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、MYSQL操作大全（名师整理版）数据库相关操作：createdatabaseifnotexiststest3defaultcharactersetutf8;alterdatabasetest3charactersetgb2312;dropdatabaseifexitstest5;数据表相关操作：createtableusers(idint(11)notnullauto_increment,usernamevarchar(55)defaultnull,sexsmallint(2)defaultnull,passwordvarchar(55)defaultnull,prima
2、rykey(id))defaultcharset=utf8;修改表结构：altertableusers_copydropcolumnsex,addcolumnaddressvarchar(55)defaultnull;altertableusers_copychangepasswordpassint(30)notnull;altertableusers_copymodifypassvarchar(55)null;altertableusers_copyrenameusers_backup;altertableuser_copyaddindex(username);desc
3、user_copy;droptableifexistsusers_backup;renametableuserstousers_copy;descusers;表数据的处理:insertintousers_copy(username,sex,password)values('jack','1','123789');updateusers_copysetusername='rose',sex='2'whereid=2;deletefromusers_copywhereid=1;deletefromusers_copy;表数据的查询:selectid,username,sex,
4、passwordfromusers_copywhereusernamelike'%a%'andid=6;selectdistinct(username)name,id,sex,passwordfromusers_copy;selectid,username,sex,passwordfromusers_copyorderbyid,sexasc;--descselectsexas'性别',count(distinctid)'合计',usernamefromusers_copygroupbysexhavingid=5;selectsexas'性别',count(distinct
5、id)asid,usernamefromusers_copygroupbysexhavingid=5;select*fromusers_copylimit2,5;set@skip=1;set@numrows=3;preparestmtfrom"select*fromusers_copylimt?,?";excutestmtusing@skip,@numrows;selectid,sexintooutfile'c:\xx.txt'fromusers;函数：/****************************repeat************************
6、**********************/createprocedureadd_users3(inusernamevarchar(55),inpasswordvarchar(55),inaddressvarchar(150),chkint)beginrepeatsetchk=chk+1;insertintousers(username,password,address)values(username,password,address);untilchk<10endrepeat;end;--calladd_users3('111redyin111','1116666',
7、'11999999',7);createprocedureadd_users4(inusernamevarchar(55),inpasswordvarchar(55),inaddressvarchar(150))begininsertintousers(username,password,address)values(username,password,address);end;--alterprocedureadd_users4containsSQL;--comment'add_users4';--drop{proceder
