当前位置 : 首页 » 文章分类 :  开发  »  SQL基础

SQL基础

SQL笔记


SQL序列

序列是一种数据库对象,用来自动产生一组唯一的序号;序列是一种共享式的对象,多个用户可以共同使用序列中的序号。

创建序列

创建序列语法(需要CREATE SEQUENCE系统权限):

CREATE SEQUENCE sequencename
[START WITH n]
[INCREMENT BY n]
[{MINVALUE n|NOMINVALUE}]
[{MAXVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
  • START WITH:定义序列的初始值(即产生的第一个值),默认为1。
  • INCREMENT:用于定义序列的步长。如果省略,则默认为1,如果出现负值,则代表序列的值是按照此步长递减的。
  • MINVALUE:定义序列生成器能产生的最小值。NOMINVALUE是默认选项(表示没有最小值定义),这时对于递减序列,系统能够产生的最小值是,10的26次方;对于递增序列,最小值是1。
  • MAXVALUE:定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项,代表没有最大值定义,这时对于递增序列,系统能够产生的最大值是10的27次方;对于递减序列,最大值是-1。
  • CYCLE|NOCYCLE:表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环,默认NOCYCLE。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。
  • CACHE n|NOCACHE:CACHE(缓冲)定义存放序列的内存块的大小,默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能。

例如:

CREATE SEQUENCE "nrise2"."ras_base_auth_watch_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1644
CACHE 1
OWNED BY "nrise2"."ras_base_auth_watch"."id";

使用序列

select sequencename.NEXTVAL from dual;
select sequencename.CURRVAL from dual;
insert into member(mid,name) values (myseq.nextval,'Scott');

NEXTVAL,返回序列中下一个有效的值,任何用户都可以引用
CURRVAL,返回序列的当前值
第一次使用时CURRVAL不能用
使用时需要指定序列的对象名
将序列值装入内存可提高访问效率

序列在下列情况下出现裂缝:

  • 回滚
  • 系统异常
  • 多个表同时使用同一序列

修改序列

修改序列语法:

ALTER SEQUENCE sequencename
[INCREMENT BY n]
[{MINVALUE n|NOMINVALUE}]
[{MAXVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];

注:不能修改序列的初始值

删除序列

删除序列语法:

DROP SEQUENCE sequencename;

参考


dual表

Oracle中的dual表

  • Dual表是Oracle中的一个单行单列的虚拟表,任何用户均可读取,常用在没有目标表的Select中。
  • Dual表是oracle与数据字典一起自动创建的一个表,这个表只有1列:DUMMY,数据类型为VERCHAR2(1),dual表中只有一个数据’X’, Oracle有内部逻辑保证dual表中永远只有一条数据。
  • Dual表主要用来选择系统变量或求一个表达式的值。

例如 SELECT sysdate FROM dual
由于没有表名就没有办法查询,而时间日期并不存放在任何表中,于是这个dual虚拟表的概念就被引入了。

dual表应用实例

  • 1、查看当前连接用户
    select user from dual

  • 2、调用系统函数

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual ----得到当前系统时间
    SELECT REPLACE('abcdefg','f','$') rp FROM DUAL;
    select SYS_CONTEXT('USERENV','language') from dual;--获得当前locale
    select dbms_random.random from dual;--获得一个随机数
    
  • 3、得到序列的下一个值或当前值

    select your_sequence.nextval from dual;--获得序列your_sequence的下一个值
    select your_sequence.currval from dual;--获得序列your_sequence的当前值
    
  • 4、数值计算
    select 7*9 from dual;

其他数据库与dual表

在mysql里也存在和oracle里类似的dual虚拟表,不同的是,官方声明纯粹是为了满足select … from…这一习惯问题,mysql会忽略对该表的引用。

PostgreSQL中不需要dual虚拟表。dual是Oracle中为了保证每个select语句都有from而设置的一个虚拟表,而没有其他任何意义。
在PostgreSQL中(SQL Server也是一样),select语句可以没有from,例如:
select 'Hello Jack' as txt;
而在Oracle中则必须:
select 'Hello Jack' as txt from dual;

参考


SQL优化

排序

SELECT
     f.Trend_Flight_ID, f.SaveTimeStamp, f.OriDest, f.FltDate, f.FltTime, f.FltNumOC, f.OC, f.DIFlag,
    f.SegNum, f.FltNumMC,f.airports,s.Trend_Flight_ID, s.SegSeq, s.SaveTimeStamp, s.DepAirport, s.ArrAirport, s.DepDate, s.DepTime,
    s.ArrDate, s.ArrTime, s.Distance, s.Booked, s.Grp, s.TktedNum, s.VipNum, s.ClsBkdDetail, s.AVDetail,
    s.LowestOpenCls, s.LowestKCls,l.Trend_Flight_ID, l.LegSeq, l.SaveTimeStamp, l.DepAirport as "lDepAirport", l.ArrAirport as "lArrAirport", l.DepDate as "lDepDate", l.DepTime as "lDepTime",
    l.ArrDate as "lArrDate", l.ArrTime as "lArrTime", l.Distance as "lDistance", l.EquipType, l.Cap, l.Booked as "lBooked", l.Max
FROM
ras_trend_flight f
left outer JOIN ras_trend_seg s ON f.trend_flight_id = s.trend_flight_id
left outer JOIN ras_trend_leg l ON f.trend_flight_id = l.trend_flight_id
WHERE
    fltdate = 20151124
AND fltnumoc = 'HO1252'
AND f.savetimestamp BETWEEN 20151010 AND 20151124
order by f.savetimestamp

不加排序10几秒运行完,加最后的order by f.savetimestamp排序,几分钟也运行不完。f.savetimestamp还是有索引的字段。

between,大于小于,or,in

mysql中,between and 和>= and <=是等价的

SELECT * FROM tin where c1 >= 100 and c1 <= 104;
SELECT * FROM tin where c1 in (100, 101, 102, 103, 104);
SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 = 104;

对于语句1的WHERE条件十分简单,匹配上下界限即可,即对于每返回的一行数据需要两次验证,时间复杂度为常量O(2)
对于语句2和语句3,则需要对IN或OR中的每个条件进行验证,知道找到某一匹配项为止,时间复杂度为O(n)。 但是MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的,故匹配的时候是二分查找, 时间复杂度为O(lgn)
在忽略I/O的情况下,仅仅从CPU的耗时来看,语句1应该是最少的,其次是IN,最差的就是OR

count(1),count(*),count(column)

Count(1)和Count(*)实际上的意思是,评估Count()中的表达式是否为NULL,如果为NULL则不计数,而非NULL则会计数。所以对于查询结果,只要在Count中指定非NULL表达式,结果没有任何区别。
对于Count(列)来说,同样适用于上面规则,评估列中每一行的值是否为NULL,如果为NULL则不计数,不为NULL则计数。因此Count(列)会计算列或这列的组合不为空的计数。

性能比较:
如果你的数据表没有主键,那么count(1)比count(*)快
如果有主键的话,那主键(联合主键)作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(?),用count(*),sql会帮你完成优化的

总结三条经验
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择;
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询;
3.杜绝SELECT COUNT(COL) FROM tablename的出现。

where 1=1

select from table1 where 1=1与select from table1完全没有区别,甚至还有其他许多写法,1<>2,’a’=’a’,’a’<>’b’,其目的就只有一个,where 的条件为永真。这样在where子句有变量的SQL中可避免出错,把加条件的SQL和不加条件的SQL合二为一。
例如一个查询可能有oc,flightnum条件,也可能没有,加上where 1=1,就不用考虑两个条件都没有时还要去掉where子句。

select * from re_ais_opencloseclassbasis where 1=1
<if test="oc != null" >
  and oc = #{oc,jdbcType=VARCHAR}
</if>
<if test="flightnum != null" >
  and flightnum = #{flightnum,jdbcType=VARCHAR}
</if>


SQL通配符

SQL通配符
http://www.w3school.com.cn/sql/sql_wildcards.asp

  • _:与任意单字符匹配
  • %:与包含一个或多个字符的字符串匹配
  • []:与特定范围(例如,[a-f])或特定集(例如,[abcdef])中的任意单字符匹配。
  • [^][!]:与特定范围(例如,[^a-f])或特定集(例如,[^abcdef])之外的任意单字符匹配。

例子:
WHERE FirstName LIKE ‘_im’ 可以找到所有三个字母的、以 im 结尾的名字(例如,Jim、Tim)。
WHERE LastName LIKE ‘%stein’ 可以找到姓以 stein 结尾的所有员工。
WHERE LastName LIKE ‘%stein%’ 可以找到姓中任意位置包括 stein 的所有员工。
WHERE FirstName LIKE ‘[JT]im’ 可以找到三个字母的、以 im 结尾并以 J 或 T 开始的名字(即仅有 Jim 和 Tim)
WHERE LastName LIKE ‘m[^c]%’ 可以找到以 m 开始的、后面的(第二个)字母不为 c 的所有姓。


查询

select [ all | distinct ]  <目标列表达式 | 聚集函数>[别名]...
from <表名或视图名>[别名]...
[ where <条件表达式> ]
[ group by <列名> [having <条件表达式>]  ]
[ order by <列名> [ASC | DESC] ]

left和right函数

left(character_expression, integer_expression),返回character_expression 左起 integer_expression 个字符。
right(character_expression, integer_expression),返回character_expression 最右边 integer_expression 个字符。
character_expression 可以是常量、变量或列。
例如:
select left('abcdef',3),查询结果为abc
SELECT LEFT(Name, 5),查询Name左5个字符
where left(op_time,8)=20160601,匹配op_time左8位是20160601的


group by与聚集函数

group by:将查询结果按某一列或多列的值分组,值相等的为一组

注意:

  • where子句中不能使用聚集函数作为条件表达式
  • select中指定的字段要么就要包含在Group By语句的后面,作为分组的依据;要么就要被包含在聚合函数中。

    含有group by子句的select中指定的字段必须是“分组依据字段”,其他字段若想出现在select中则必须包含在聚合函数中。
  • group by或having子句中不能使用列的别名
    例:

    SELECT a as c, sum(b)
    FROM test
    GROUP BY c
    

    会提示错误,group by c改成group by a就行。
    我在开发中遇到的错误:

    [ERROR] 2016-07-05 11:01:04,694 method:com.travelsky.adap.re.ras.agentbookmonitor.ws.AgentBookWs.integratedAnalyse(AgentBookWs.java:83)
    integerated analyse query failed!
    org.springframework.jdbc.BadSqlGrammarException:
    ### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function
    Position: 8
    ### The error may exist in mybatis/PsgBookMapper.xml
    ### The error may involve com.travelsky.adap.re.ras.agentbookmonitor.dao.PsgBookMapper.selectIntegratedAnalyseByOffice-Inline
    ### The error occurred while setting parameters
    ### SQL: select bookingoffice as operatingOffice, oc as oc,count(1) as addNum    from ras_agent_psgbook    where    oc=? and depdate between ? and ? and bookingdate between ? and ?                  and (depdate > ? or (depdate=? and deptime > ?))                  group by operatingoffice,oc      order by addNum desc
    ### Cause: org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function
    Position: 8
    ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function
    

    select中给bookingoffice设定别名为operatingOffice,group by中使用别名operatingoffice导致SQL报错。

  • group by后的order by子句中的字段必须是“分组依据字段”,其他字段若想出现在select中则必须包含在聚合函数中。
    例如

    SELECT errortype, isschchg, count(*)
    FROM "ais"."re_ais_dcm_changeresult"
    WHERE "airlinecode" = 'HU'
    group by errortype, isschchg
    ORDER BY savedate
    

    执行时报错:ERROR: column “re_ais_dcm_changeresult.savedate” must appear in the GROUP BY clause or be used in an aggregate function
    因为savedate字段不是group by分组字段,也不在聚集函数中,若将order by子句改为:ORDER BY count(savedate),即将savedate放入聚集函数则没问题。


连接、嵌套、集合查询

(1) 连表查询:常用等值连接、自然连接
(2) 嵌套查询:常使用IN谓词
不相关子查询、相关子查询
子查询中不能有order by子句
(3) 集合查询:UNION, INTERSECT, EXCEPT

相关子查询经典例题:
Student(Sno,Sname,Ssex,Sage,Sdept)
Course(Cno,Cname,Cpno,Ccredit)
SC(Sno,Cno,Grade)
找出每个学生超过他选修课程平均成绩的课程号(查询工资大于其所在部门平均工资的员工)

select Sno, Cno
from SC x
where x.Grade >(
    select AVG(Grade)
    from SC y
    where x.Sno = y.Sno
    );

limit

指定返回数据个数

用MySql中的limit语句返回指定个数的数据
http://www.php100.com/html/webkaifa/database/Mysql/2010/0120/3838.html
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。注意初始记录行的偏移量是 0(而不是 1)


SQL语句

数据定义:create, alter, drop
数据操纵:insert, update, delete

插入数据

insert into 表名(列名)
values(列值)

修改数据

update 表名
set 列名=表达式
where 条件

将符合条件的行的某字段置空

//更新为””串
update taxi_driver_info set wechat = ‘’ where driverno = ‘0440923198101075636’;

//更新为null
update taxi_driver_info set wechat = null where driverno = ‘0440923198101075636’;

删除数据

delete
from 表名
where 条件

创建视图

create view 视图名(列名)
as 子查询(select from where)
[with check option] //对视图的插入、修改、删除要满足定义视图的谓词条件

上一篇 数据库基础

下一篇 OpenNI2获取华硕XtionProLive深度图和彩色图并用OpenCV显示

阅读
3,325
阅读预计14分钟
创建日期 2014-08-22
修改日期 2018-06-27
类别
标签
百度推荐