MySQL基础语法.doc

创建用户：create user zxh identified by czs;create userzxhlocalhostidentified by czs;授权:grant all privileges on *.* to zxh;*.* 是代表任何数据库的任何表数据库名.* 某某数据的任何操作grant insert, select on mysql.* to username;flush privileges; / 刷新权限表用户登录与切换：一定要在dos的run中打开找到mysql.exe的路径C:wampmysqlbinmysql -h localhost -u czs -p #-h前面为打开mysql， localhost可以换为ip删除用户：drop user username1, username2;删除用户权限：mysql revoke all privileges on *.* from czs;修改用户密码：GRANT USAGE ON *.* TO username% IDENTIFIED BY password;mysql注释符有三种：.代表任意内容 1，3比较常用1、#.2、- .3、/*.*/创建数据库：CREATEDATABASEsql_study ;CREATEDATABASEdatabaseNameDEFAULTCHARACTERSETutf8COLLATEutf8_general_ci;进入数据库:use 数据库名;显示所有表:show tables;打印表列名:desc 表名;创建表：方式一：CREATETABLEcollect (cId bigint(20)NOTNULLauto_increment,jId bigint(20)NOTNULL,uIdint(11)NOTNULL,cTimetimestampNOTNULLdefaultCURRENT_TIMESTAMPonupdateCURRENT_TIMESTAMP,PRIMARYKEY(cId),KEYjId (jId) ENGINE=InnoDBDEFAULTCHARSET=utf8 AUTO_INCREMENT=1;还包含自增由1开始方式二：（标准）createtabletwo(namechar(20)notnull,idint(11)notnullauto_increment,primarykey(id);方式三：CREATETABLEnewwe1e.tableName (idINTNOTNULLAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20)NOTNULLDEFAULT默认,timeTIMESTAMPONUPDATECURRENT_TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP) ENGINE=INNODB通过上面上种创建，可以发现列的not null, primary key, on update current_timestamp等的次序是可以随意改变的另外primary可以马上说明，也可列出所有列之后在指明。列名可以不用添加单引号不可以在主键为多个列的情况下，让某列自增。插入数据：方式一：insertintotwo (name,id)vaules(name,null);#可以去掉id和null，方式二：insertintotwovalues(name,null); #必须一个个对应，必须列出所有值，不予赋值置nullinsertintosevenvalues(null, now(); #使用时间函数删除：删除表中所有元组：deletefromr; #表还在删除表：droptabler;/*表不存在*/删除列，增加列：ALTERTABLEtwoADDnum1VARCHAR(11)NOTNULL,ADDnum2INTNOTNULL;#增加列altertabletwodropnum1,dropnum2;#删除列(由下面可知，可以删除一个列就要一个drop，增加一个列就要一个add，同时也说明可以嵌套，同时删除，同时增加)altertabletwodropcity,addnum1int(11);添加属性：ALTERTABLEtablenameADDUNIQUE( time)/*增加唯一属性*/ALTERTABLEtablenameADDINDEX( time )/*增加索引属性*/ALTERTABLEtablename CHANGE time timeVARCHAR(20)NOTNULL/*改变某一列的属性*/上面这些修改，都是可以修改嵌套的，增加后面紧跟改变等外键:ALTERTABLEtablenamerADDFOREIGNKEY( 本地id )REFERENCESone.tablename (外部id);更新:UPDATEimu.userSETqq=QQ,uTime=NOW( ) ,resume=简历,mood=签名WHEREuser.uId=16LIMIT1;查询:不重复:结果元组集中不出现相同元组distinctselect distinct num, shu from eight; / distinct只能一个，作用于所有列, distinct还用于count, sum等函数范围查询：between 11 and 200select * from eight where shu between 11 and 200;等价于 select * from eight where shu = 11;between.and 包含了等号， 还有not between.and字符串匹配查询： _代表任意的一个字符， %代表任意子串，长度无限like ab%cd%; / 转义字符，让字符串可以匹配%， 匹配排序查询：放在select语句最后，且最后作用，order by 列名 desc,列名 asc， 默认asc为升序，desc为降序结果集运算：selectid from eight union (select id from seven); / 并运算intersect / 交运算except / 差运算参加运算的关系，对应的列数据类型要相容，n个select语句就n-1个运算关键字函数查询：avg,min,max,sum,countselect name, avg(distinct balance) from account group by name having avg(distinct balance) 1200;/ 先分组，然后查询，之后用having过滤空值查询：如果算术运算的输入有一个是空，则该算术表达式（+-*/)的结果是空，如果空值参与比较运算，运算结果看成unknown（既不是is null,也不是is not null)where amount is null/is not null/ is unknown/ is not unknown嵌套子查询：in, not inselect * from eight where name in (select name from eight);集合的比较：select * from eight where shu = some (select shu from eight); / shu只要大于或等于子查询结果中的一个就为真select * from eight where shu =all (select shu from eight); / shu要大于或等于子查询结果中的全部才为真select * from eight where shu = any (select shu from eight); / any同somesome, =some, =some, some等，all也类推测试关系是否为空：select * from eight where shu exists (select shu from eight); / 这种只有两种结果，相当于wheretrue 或者 where false; 子查询为空则为falseselect * from eight where shu not exists (select shu from eight);测试关系是否存在重复元组：select * from eight where shuunique (select shu from eight);select * from eight where shu not unique (select shu from eight);别名as的另一种用法：（select name, avg(balance) from account group by name) as branch_avg(branch_name,avg_balance); / 对整个查询结果进行命名select max(tot_balance) from (select name, sum(balance) from account group by branch_name) as branch_total (branch_name,tot_balance);/ 对整个查询结果进行命名，让后马上投入max计算。临时视图：withdelete from account where balance (select avg