史上最全SQL语句诠释.docx

1、史上最全SQL语句诠释创建用户：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 -

2、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 ;CREATEDATABASEdatabaseNameDEFAULTCHARA

3、CTERSETutf8COLLATEutf8_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=InnoDBDE

4、FAULTCHARSET=utf8 AUTO_INCREMENT=1;还包含自增由1开始方式二：（标准）createtabletwo(namechar(20)notnull,idint(11)notnullauto_increment,primarykey(id);方式三：CREATETABLEnewwe1e.tableName (idINTNOTNULLAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20)NOTNULLDEFAULT默认,timeTIMESTAMPONUPDATECURRENT_TIMESTAMPNOTNULLDEFAULTCURRENT_TIME

5、STAMP) ENGINE=INNODB通过上面上种创建，可以发现列的not null, primary key, on update current_timestamp等的次序是可以随意改变的另外primary可以马上说明，也可列出所有列之后在指明。列名可以不用添加单引号不可以在主键为多个列的情况下，让某列自增。插入数据：方式一：insertintotwo (name,id)vaules(name,null);#可以去掉id和null，方式二：insertintotwovalues(name,null); #必须一个个对应，必须列出所有值，不予赋值置nullinsertintosevenva

6、lues(null, now(); #使用时间函数删除：删除表中所有元组：deletefromr; #表还在删除表：droptabler;/*表不存在*/删除列，增加列：ALTERTABLEtwoADDnum1VARCHAR(11)NOTNULL,ADDnum2INTNOTNULL;#增加列altertabletwodropnum1,dropnum2;#删除列(由下面可知，可以删除一个列就要一个drop，增加一个列就要一个add，同时也说明可以嵌套，同时删除，同时增加)altertabletwodropcity,addnum1int(11);添加属性：ALTERTABLEtablenameAD

7、DUNIQUE( time)/*增加唯一属性*/ALTERTABLEtablenameADDINDEX( time )/*增加索引属性*/ALTERTABLEtablename CHANGE time timeVARCHAR(20)NOTNULL/*改变某一列的属性*/上面这些修改，都是可以修改嵌套的，增加后面紧跟改变等外键:ALTERTABLEtablenamerADDFOREIGNKEY( 本地id )REFERENCESone.tablename (外部id);更新:UPDATEimu.userSETqq=QQ,uTime=NOW( ) ,resume=简历,mood=签名WHEREus

8、er.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字符串匹配查询： _代表任意的一个字符， %代表任

9、意子串，长度无限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

10、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 eigh

11、t);集合的比较：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 * f

12、rom 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

13、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

14、 from account where balance (select avg(balance) from account); / = 1000;case结构:更新顺序很重要，eight表名，shu2，加5，不然加10update eight set shu =casewhen shu 2 then shu +5else shu + 10end;创建数据类型：create type newType as int(11) final;断言：create assertion check ;1-order by-1- 单列-2- 多列 order by column1, column2, colum

15、n3-3- 位置select语句中的最后一条子句-4- order by的列，不一定要选择哪些被检索，被显示的列，哪些没有出现的列也是可以的。-5- 支持用位置序号代替（必须是select清单中，所以不支持未检索的）列名 order by 1, 3; #列名不清楚，如果数据库表修改也会出乱问题-6- 指定排序方向desc（降序），asc(升序，默认) 多列是要分别说明order by column1 desc, column2, column3 desc; # column1 降序 z-a， column3 降序， 默认升序,desc必须放在后面。-7-a与A是否排序先后，要根据不同数据库系统

16、，可以手动修改数据库系统设置select name from eight order by id; / 最后，产生查询结果后，在排序。任意列。2-where操作符= 等于 不等于!= 不等于 （有的不支持，access, access只支持）=!=! 不大于between 值1 and 值2 #在指定的两个值之间is null 为空值where column1 10;where column1 10; #字符串的情况要加单引号where column1 between 1 and 10;where column1 is null;组合where子语句and(优先级高于or先执行)orwhere

17、 column1=1 or column2=2 and column3 = 10; 小于10的也会出现，次序问题where (column1=1 or column2=2) and column3 = 10; 这样才没有小于10的in指定条件范围(效率快，而且可以在括号中嵌套select语句)where column1 in (1, 2); = where column1 = 1 or column1 = 2;not in的相反（mysql不支持，NOTEXISTS代替）where (id, num) (2,3) 等价于 where id 2 or num 3;3-通配符%代表任何字符出现任意

18、次数（access用*）_代表任何字符出现一次，匹配单个字符select * from one where num like%3%;select * from one where num like _3;select * from one where num like12;虽然num是int类型，同样可以用like，可以用字符串去匹配，但是要用上通配符，就一定是否字符串，一定要加上单引号，像%12是会报错的。放过来char类型也可以直接like 12; 数据库会自动将12转换成字符串之后再进行比较的。集合 (微软的才支持)like js; / 意思是字符串里包含有j或s的，只能匹配一个like

19、 js%; / 意思是字符串里以j或s开头的字符串。js是取相反的意思，!js有些用!;4-拼接字符串select num+:( + x + , + y + ) as point from one; / 多少数据库用+表示连接，如将点的序号加上坐标形成新的列， 2:(232,332)/ 有些数据库系统使用|,而不是+;MySQL对上面的都不支持。MySql 使用concat函数select concat (vend_name, (, vend_country, ) from vendors; / 参数任意长。5-别名列别名表别名：from语句 表名 as 别名， 别名可以用于select，wh

20、ere， group by等， or注意：oracle 不支持 as关键字，也可以说省略了as。别名可以缩短sql语句，允许单条中多次使用6-计算 * / + -select id, quantity * price as expanded_price from items; / 这种不是交叉相乘，而是每一行中两个元素的相乘，结果行数不会发生变化select id, quantity * 2.3 as quantity from items; / as可以是表中某列名，也可以是原来的，不过命名，为没有名的列,或者列名为quantity * 2.37-数据库函数：不同数据库系统支持的函数可能不同

21、不区分大小写字符串：length(name)upperlowerrtrim(name) /去掉右边空格ltrim(name)left(name, length) 返回name左边的length个长度，length为阿拉伯数字left(name, 列名）， 长度可变，可以是对应元组的某一属性值。列必须为数字列，长度过大会将其全部输出right同上日期和时间处理函数：DATEPART(yy,列名)/ 取出年，mysql不支持year(列名), mysql支持的，day,month,hour等to_char(),to_number() / mysql不支持数值计算函数：abs(列名）cos(列名）e

22、xp(列名）pi() 圆周sin()sqrt()tan等汇总函数:avg(列名) / 求平均， 去除重复avg(distinct 列名）count(列) / 忽略列名中的null行，count(*)整个表有多少记录，不忽略null， 也可用distinct去除重复maxminsum等 去除重复sum(distinct 列名）过滤分组：select count(*) as num_count, num from eight group by num;/ 不忽略null，num相同的被分到同一组，让后对各个组进行count操作。，count(num), null不像上面的，而是会被算出，null作

23、为一个分组。select count(*) as num_count, num from eight group by num, shu; / num和shu都相同才会被分到一起group by 也可按选择的列数编号， group by 1, 2对分组进行过滤：havinghaving支持所有where后面的操作符where在分组前进行过滤，having在分组后进行过滤，where过滤行，where排除的行不会出现在分组中order by排序。group by 很多是排序，可是不一定的。order by与group byorder by 排序产出，所有过滤操作后，才计算，可以是任意列，即使列没

24、有出现group by 把相同的归类到一组，不能是任意列，列一定要出现select name, shu, count(*) as count from eight group by shu;11-子查询情况一：in的使用select a from eight where id = 2;a12select b from eight where a in (1,2);b34组合上面的查找select b from eight where a in (select a from eight where id = 2); / 重内向外进行计算注意：作为子查询的select语句只能查询单列，多列将会报错

25、情况二：填充计算列select a, (select count(*) from seven where eigth.id = seven.id) as count from eight;一个a在seven中有多个记录，计算它的数目12-联结表关系表的设计就是要保证把信息分解成多个表，比如由多个供应商生产的多种产品，如果一个表表示，供应商会出现很多次，而如果把供应商和产品分开，用两个表，用id关联，会避免重复字符串的重复。联结查询，不同于其他就是，from语句中为多个表，而且列名出现二义性时，要完全限定列名，通过（表名.列名）表示联结查询，不能少了正确的where语句，没有where语句，返回

26、的将是表1的每一行与表2的每一行配对，结果也就是笛卡尔积。表的联结个数是有限制的，对于不同的DBMS系统有不同的个数限制select a, b from t1, t2 where t1.id = t2.id; 等价于 select a, b from t1 inner join t2 on t1.id = t2.id; / on后面和where一样自联结：select name from customers where name = (select name from customers where contact = Jim) / 用=类似于in， 子查询慢select c1.name fr

27、om customers as c1, customers as c2 where c1.name = c2.name and c2.contact = Jim / 同一个表多个命名， 自联结快自然联结：select C1.*, C2.name, C3.contact from customers as C1, citys as C2, cat as C3 from where C1.id = C2.id and C2.id = C3.id; / 避免多列出现C1.*外部联结：在联结中包含哪些相关表中没有关联行的行如：列出所有产品及订购数量，及哪些没有人订购的产品c表id,name d表id,

28、count上面的可以用select name, count from c,d where c.id *= d.id; / 左外联， =*右外联， oracle使用（+） 代替*select name, count from c full outer join d on c.id=d.id; / 全外连接，部分dbms支持使用聚集函数的联结：13-组合查询将多个select语句的结果并在一起在单个查询中从不同的表类似返回数据结构对单个表执行多个查询，按单个查询返回数据就是并运算，参加元素的元素要相容（类型可以不同）才行，多条select语句，n条select语句，n-1个unionunion / 自动取出重复的union all以下mysql不支持交 intersect （all）差 except14-插入数据插入完整的一行：可以不用列出列名，直接insert into 表名 values(值); / 值一定要全部一一对应，如果有自增的不妥插入行的部分数据：插入到哪里，就要指定对应的列名，一一对应insert into eight (name, id) values(wew, 12