总结SQLite不支持的SQL语法有哪些

来源：互联网发布：java class 查看工具编辑：程序博客网时间：2024/05/22 05:11

<spanclass="s3">目前<spanclass="s4">SQLITE<spanclass="s3">版本中<spanclass="s4">ALTER TABLE<spanclass="s3">不支持<spanclass="s4">DROP COLUMN<spanclass="s3">，只有<spanclass="s4">RENAME<spanclass="s3">和<spanclass="s4">ADD <spanclass="s4">alter table drop column  <spanclass="s4">rename add <spanclass="s4">不能支持完整的触发器，支持一部分 <spanclass="s5">1 TOP<spanclass="s6"> <spanclass="s4">这是一个大家经常问到的问题，例如在<spanclass="s7">SQLSERVER<spanclass="s4">中可以使用如下语句来取得记录集中的前十条记录： <spanclass="s4">SELECT<spanclass="s8">TOP<spanclass="s8">10<spanclass="s8">*<spanclass="s8">FROM<spanclass="s8">[index]<spanclass="s8">ORDER<spanclass="s8">BY<spanclass="s8"> indexidDESC<spanclass="s8">; <spanclass="s4">但是这条<spanclass="s7">SQL<spanclass="s4">语句在<spanclass="s7">SQLite<spanclass="s4">中是无法执行的，应该改为： <spanclass="s12">SELECT<spanclass="s4">*<spanclass="s4">FROM<spanclass="s4">[index]<spanclass="s4">ORDER<spanclass="s4">BY<spanclass="s4"> indexidDESC<spanclass="s4"> limit0<spanclass="s4">,<spanclass="s9">10<spanclass="s4">; <spanclass="s4">其中<spanclass="s5">limit<spanclass="s7">0,10<spanclass="s4">表示从第<spanclass="s7">0<spanclass="s4">条记录开始，往后一共读取<spanclass="s7">10<spanclass="s4">条 <spanclass="s4"> TOP <spanclass="s5">2<spanclass="s11">创建视图（<spanclass="s5">Create View<spanclass="s11">）<spanclass="s6"> <spanclass="s7">SQLite<spanclass="s4">在创建多表视图的时候有一个<spanclass="s7">bug<spanclass="s4">，问题如下： <spanclass="s12">CREATE<spanclass="s4">VIEW<spanclass="s4"> watch_singleAS<spanclass="s4">SELECT<spanclass="s4"> DISTINCT watch_item.<spanclass="s11">[watchid]<spanclass="s4">,watch_item.[itemid]<spanclass="s4"> FROM<spanclass="s4"> watch_item; create view /// as select distinct<spanclass="s13">（不同的，分别的） <spanclass="s4">上面这条<spanclass="s7">SQL<spanclass="s4">语句执行后会显示成功，但是实际上除了 <spanclass="s12">SELECT<spanclass="s4">COUNT<spanclass="s4">(<spanclass="s10">*<spanclass="s4">)FROM<spanclass="s4">[watch_single ]<spanclass="s4">WHERE<spanclass="s4"> watch_single<spanclass="s4">.watchid=<spanclass="s4">1<spanclass="s4">; <spanclass="s4">能执行之外是无法执行其他任何语句的。其原因在于建立视图的时候指定了字段所在的表名，而<spanclass="s7">SQLite<spanclass="s4">并不能正确地识别它。所以上面的创建语句要改为： <spanclass="s12">CREATE<spanclass="s4">VIEW<spanclass="s4"> watch_singleAS<spanclass="s4">SELECT<spanclass="s4">DISTINCT<spanclass="s4">[watchid]<spanclass="s4">,<spanclass="s11">[itemid]<spanclass="s4">FROM<spanclass="s4"> watch_item; <spanclass="s4">但是随之而来的问题是如果是多表的视图，且表间有重名字段的时候该怎么办？ <spanclass="s4">3 COUNT<spanclass="s3">（<spanclass="s4">DISTINCT column<spanclass="s3">）<spanclass="s15"> <spanclass="s16">SQLite<spanclass="s17">在执行如下语句的时候会报错： <spanclass="s12">SELECT<spanclass="s4">COUNT<spanclass="s4">(<spanclass="s12">DISTINCT<spanclass="s4"> watchid)FROM<spanclass="s4">[watch_item]<spanclass="s4">WHERE<spanclass="s4"> watch_item.watchid=<spanclass="s4">1<spanclass="s4">; <spanclass="s4">其原因是<spanclass="s7">SQLite<spanclass="s4">的所有内置函数都不支持<spanclass="s7">DISTINCT<spanclass="s4">限定，所以如果要统计不重复的记录数的时候会出现一些麻烦。比较可行的做法是先建立一个不重复的记录表的视图，然后再对该视图进行计数。 <spanclass="s5">4<spanclass="s11">外连接outer join<spanclass="s6"> <spanclass="s4">虽然<spanclass="s7">SQLite<spanclass="s4">官方已经声称<spanclass="s7">LEFT OUTER JOIN已经实现，但还没有<spanclass="s7"> RIGHT OUTER JOIN和<spanclass="s7"> FULL OUTER JOIN<spanclass="s4">。但是实际测试表明似乎并不能够正常的工作。以下三条语句在执行的时候均会报错： <spanclass="s12">SELECT<spanclass="s8"> tags.<spanclass="s4">[tagid]<spanclass="s8">FROM<spanclass="s8">[tags]<spanclass="s8">,<spanclass="s4">[tag_rss]<spanclass="s8">WHERE<spanclass="s8"> tags.<spanclass="s4">[tagid]<spanclass="s8">=<spanclass="s8"> tag_rss.<spanclass="s4">[tagid]<spanclass="s8">(<spanclass="s10">*<spanclass="s8">); <spanclass="s12">SELECT<spanclass="s8"> tags.<spanclass="s4">[tagid]<spanclass="s8">FROM<spanclass="s8">[tags]<spanclass="s8">,<spanclass="s4">[tag_rss]<spanclass="s8">WHERE<spanclass="s8">LEFT<spanclass="s8">OUTER<spanclass="s8">JOIN<spanclass="s8"> tag_rss.<spanclass="s4">[tagid]<spanclass="s8">=<spanclass="s8"> tags.<spanclass="s4">[tagid]<spanclass="s8">; <spanclass="s12">SELECT<spanclass="s8"> tags.<spanclass="s4">[tagid]<spanclass="s8">FROM<spanclass="s8">[tags]<spanclass="s8">,<spanclass="s4">[tag_rss]<spanclass="s8">WHERE<spanclass="s8">LEFT<spanclass="s8">JOIN<spanclass="s8"> tag_rss.<spanclass="s4">[tagid]<spanclass="s8">=<spanclass="s8"> tags.<spanclass="s4">[tagid]<spanclass="s8">; <spanclass="s4">此外经过测试用<spanclass="s7">+<spanclass="s4">号代替<spanclass="s7">*<spanclass="s4">号也是不可行的。 <spanclass="s3">收集<spanclass="s4">SQLite<spanclass="s3">与<spanclass="s4">Sql Server<spanclass="s3">的语法差异 <spanclass="s11">1.<spanclass="s18">返回最后插入的标识值<spanclass="s11"> <spanclass="s6"> <spanclass="s3">返回最后插入的标识值<spanclass="s4">sql server<spanclass="s3">用<spanclass="s11">@@IDENTITY<spanclass="s4"> identity<spanclass="s6"> <spanclass="s4">sqlite<spanclass="s3">用标量函数<spanclass="s11">LAST_INSERT_ROWID() last inserid<spanclass="s6"> <spanclass="s3">返回通过当前的<spanclass="s4"> SQLConnection插入到数据库的最后一行的行标识符（生成的主键）。此值与<spanclass="s4">SQLConnection.lastInsertRowID<spanclass="s3">属性返回的值相同。 <spanclass="s11">2.top n<spanclass="s6"> <spanclass="s3">在<spanclass="s4">sql server<spanclass="s3">中返回前<spanclass="s4">2<spanclass="s3">行可以这样：<spanclass="s4">  <spanclass="s4">select<spanclass="s8">top<spanclass="s8">2<spanclass="s8">*<spanclass="s8">from<spanclass="s8"> aaorder<spanclass="s8">by<spanclass="s8"> idsdesc <spanclass="s4">sqlite<spanclass="s3">中用<spanclass="s4">LIMIT<spanclass="s3">，语句如下：<spanclass="s4">  <spanclass="s4">select<spanclass="s8">*<spanclass="s8">from<spanclass="s8"> aaorder<spanclass="s8">by<spanclass="s8"> idsdesc<spanclass="s8"> LIMIT2 <spanclass="s11">3.GETDATE ( ) getDate<spanclass="s6"> <spanclass="s3">在<spanclass="s4">sql server<spanclass="s3">中<spanclass="s11">GETDATE ( )<spanclass="s3">返回当前系统日期和时间<spanclass="s4"> <spanclass="s6"> <spanclass="s4">sqlite<spanclass="s3">中没有 <spanclass="s11">4.EXISTS<spanclass="s18">语句<spanclass="s11"> <spanclass="s6"> <spanclass="s4">sql server<spanclass="s3">中判断插入（不存在<spanclass="s4">ids=5<spanclass="s3">的就插入）<spanclass="s4">  <spanclass="s4">IF<spanclass="s8">NOT<spanclass="s8">EXISTS<spanclass="s8"> (<spanclass="s4">select<spanclass="s8">*<spanclass="s8">from<spanclass="s8"> aawhere<spanclass="s8"> ids<spanclass="s10">=<spanclass="s9">5<spanclass="s8">)  <spanclass="s4">BEGIN<spanclass="s8">  <spanclass="s12">insert<spanclass="s4">into<spanclass="s4"> aa(nickname)  <spanclass="s4">select<spanclass="s8">'t'<spanclass="s8">  <spanclass="s4">END<spanclass="s8">  <spanclass="s3">在<spanclass="s4">sqlite<spanclass="s3">中可以这样<spanclass="s4">  <spanclass="s12">insert<spanclass="s4">into<spanclass="s4"> aa(nickname)  <spanclass="s4">select<spanclass="s8">'t'<spanclass="s8">  <spanclass="s4">where<spanclass="s8">not<spanclass="s8">exists<spanclass="s8">(<spanclass="s4">select<spanclass="s8">*<spanclass="s8">from<spanclass="s8"> aawhere<spanclass="s8"> ids<spanclass="s10">=<spanclass="s9">5<spanclass="s8">) <spanclass="s5">5.<spanclass="s11">嵌套事务<spanclass="s5"> <spanclass="s6"> <spanclass="s7">sqlite<spanclass="s4">仅允许单个活动的事务 <spanclass="s4">6.RIGHT<spanclass="s3">和<spanclass="s4"> FULL OUTER JOIN <spanclass="s15"> <spanclass="s16">sqlite<spanclass="s17">不支持<spanclass="s16">RIGHT OUTER JOIN<spanclass="s16">或<spanclass="s16">FULL OUTER JOIN <spanclass="s11">7.<spanclass="s18">可更新的视图<spanclass="s11"> <spanclass="s6"> <spanclass="s4">sqlite<spanclass="s3">视图是只读的。不能对视图执行<spanclass="s4"> DELETE<spanclass="s3">、<spanclass="s4">INSERT或<spanclass="s4"> UPDATE语句，<spanclass="s4">sql server<spanclass="s3">是可以对视图<spanclass="s4"> DELETE<spanclass="s3">、<spanclass="s4">INSERT或<spanclass="s4"> UPDATE <spanclass="s4">sqlite -- limit<spanclass="s3">，没有，<spanclass="s4">exists where not,<spanclass="s3">仅允许单个活动的事务，不能对视图执行<spanclass="s4">delete<spanclass="s3">，<spanclass="s4">insert<spanclass="s3">，<spanclass="s4">update<spanclass="s3">操作，不支持<spanclass="s4">right<spanclass="s3">、<spanclass="s4">full outer join<spanclass="s3">，<spanclass="s4">SQLConnection.LastInsertRowID <spanclass="s4">sql sever --  top<spanclass="s3">，<spanclass="s4">getdate,if not exists,<spanclass="s3">可多个活动事务，可以对视图进行<spanclass="s4">delete<spanclass="s3">，<spanclass="s4">insert<spanclass="s3">，<spanclass="s4">update<spanclass="s3">操作，返回最后插入的标识符用<spanclass="s4"> @identity

0 0