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

SQL基础

SQL笔记

SQL类型

DQL

DQL (Data Query Language)数据查询语言：
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：

SELECT 字段名表
FROM 表或视图名
WHERE 查询条件

DDL

DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.

DDL是SQL语言的四大功能之一。
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

DML

DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
DML分成交互型DML和嵌入型DML两类。
依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
需要commit.
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

DCL

DCL（Data Control Language）数据库控制语言 授权，角色控制等
GRANT 授权
REVOKE 取消授权

TCL

TCL（Transaction Control Language）事务控制语言
SAVEPOINT 设置保存点
ROLLBACK 回滚
SET TRANSACTION

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 的所有姓。

查询

连接、嵌套、集合查询

(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显示

阅读
评论
3k字
阅读预计12分钟
创建日期 2014-08-22
修改日期 2020-05-06
类别
标签
目录

页面信息

location:
protocol:
host:
hostname:
origin:
pathname:
href:
document:
referrer:
navigator:
platform:
userAgent:

评论

▲