SQL: Getting started

Sql:结构化查询语句，几乎支持所有数据库。

Sql语句分类：

查询：select from where

单表查询、多表查询、子查询

单行函数、聚集函数

排序、分组

连接...

运算...

DDL：数据定义语言：

定义：create、

删除：drop、

修改：alter、

DML：数据操作语言：

插入：insert、

删除：delete、

更改：update、

高级功能：

事物：commit、rollback、savepoint、

授权：grant、revoke、

序列、索引：、视图

约束：...

sql支持的数据类型：

char（n）：固定长度字符串n，character，尽量不用。

varchar（n）：最大长度为n的可变长度字符串，charactervarying，建议使用。

int：整数类型，integer

smallint：小整数类型

numeric（p，d）：定点数，p位数字，d位小数

real：浮点数

doubleprecision：双精度浮点数

float：精度为n位的浮点数

NULL：空值，尽量不用。

date：日期

time：时间

##########################################################

查询：

单表查询：

Select:

select选中的字段可以进行+- × / 算术运算。

distinct关键字：排除重复的字段

all关键字：显示重复的字段，默认行为可省略。

From:

as关键字：重命名

Where:

可以使用逻辑运算符：

and

or

not

可以使用比较运算符

>

=

<

>=

<=

<>

sql使用单引号‘ string ’来表示字符串，如果字符串中含有单引号就用两个单引号，‘’string1 ‘’ string2‘’。

sql中字符串比较是大小写敏感的。

多表查询：

Selectname1_from_table1,name2_from_table2from table1,table2;

如果table1和table2都有字段name1，那么需要做区分：

Selecttable1.name1,only_name2_from_table2from table1,table2where only_name3_from_table1= table2.name1;

给表取别名：等效于用as关键字

Selectalias1.name,alisa2.namefrom table1 alias1,table2 alias2where alias1.name1= alias2.name2;

等值连接：使用等号作为连接条件的运算符的连接。

Selectname from table1where name=value;

Selecttable1.name1,only_name2_from_table2from table1,table2where only_name3_from_table1= table2.name1;

非等值连接：不使用等号作为连接符号的连接。也就是where中使用的不是=。

X （not）between [a,b]:

where x (not) between a and b;

等于

where x <=a and x>= b;

X （not）in (n1, n2,..) :

where x(not) in (n1, n2,…);

selectname1, name2 from table1, table2 where table1.name3 = table2.name3and table1.name4 = 'string';

等于：

selectname1, name2 from table1, table2 where (table1.name3, table1.name4) =(table2.name3, 'string');

空值：空值的结果是unknow

X is （not）NULL :where x is (not) NULL;

用like进行模式匹配：

X （not）like a% : where x (not) like ‘a%’ 通配符：%任意多个任意字符

X （not）like a_ : where x (not) like ‘a_’ 通配符：_一个任意字符

用escape和\进行转义：

where X (not) like 'a\%b%' escape '\' #表示包含a%b的任意字符串。

Where X (not) like 'a\_b%' escape '\' #表示包含a_b的任意字符串。

Where X (not) like 'a\\b%' escape '\' #表示包含a\b的任意字符串。

排序：

order by 用来排序，默认是升序，

order by name asc; 升序（字典顺序）

order by name desc; 降序

orderby name1 desc, name2 asc;

分组：

Groupby 分组标准 ，放在where之后

having：在groupby之后，groupby的having相当于select的where。

Selectname1_from_table1,sum(name2_from_table1)from table1

groupby name1_from_table1

havingsum(name2_from_table1)>number1;

根据name1分组，列出name2的和大于number1的字段

Sql的执行顺序：

From

Where

Groupby

Having

Select

Orderby

最里层的括号最先执行。

-----------------------------------------------------------

单行函数：

对sql语句影响的每一行都返回一个结果。

Selectupper(‘string’) form table;

Selectlower(‘string’) from table;

Selectinitcap（‘string‘）from table;

Selectconcat(‘string1’,’string2’) from table; 拼接两个字符串

Selectlength(‘string’) from table; 输出string的长度

Selectnvl（name，‘string‘）from table; 如果name为NULL就输出string

Selectsubstr（‘string‘，start，size）from table; 从start位置开始处理size个字符串

to_number(n1)：字符串转换函数，把n1转换成数字

round(n1,n2):对n1从小数后面n2位开始四舍五入，n2缺省为0.

trunk(n1,n2):从n1截取小数点后n2位之前的部分，n2缺省为0.

to_char(n1,n2):n1是要处理的字段，n2是格式，缺省为把数字转换成字符串；

n2用‘’引起来，以fm开头，用下列符号组成一个完整的格式：

$:美元符号

L：本地货币符号，中国就是RMB：￥

0:强制显示开头的无效的0

9：任意一位数字

，：分隔符

.：小数点

聚集函数：

无论sql语句影响多少行，只返回一个结果。

对一组函数处理之后得到一个结果，对null值的处理是忽略的。

Count(字段)统计个数

Max（字段）统计最大值

Min（字段）统计最小值

Avg（数字类型）统计平均值

Sum（数字类型）统计和

some

every

$operator( distinct name) : 可以用来排重

count（*)：统计元组数，不能用distinct.

selectavg(name1) as avg_salary from table1 where P1.

-------------------------------------------------------------

集合运算：

union:并运算，满足P1条件的和满足P2条件的并集，自动排重

union all: 不排重。

(selectname1 from table1 where P1) union (select name2 fromtable2 where P2);

(selectname1 from table1 where P1) union all (select name2 fromtable2 where P2);

intersect：交运算，同时满足P1和P2条件的交集，自动排重

intersectall：不排重。

(selectname1 from table1 where P1) intersect (select name2 fromtable2 where P2);

(selectname1 from table1 where P1) intersect all (select name2from table2 where P2);

except:差运算，满足P1条件，但不满足P2条件的差运算，自动排重

exceptall：不排重。

(selectname1 from table1 where P1) except (select name2 fromtable2 where P2);

(selectname1 from table1 where P1) except all (select name2 fromtable2 where P2);

子查询

把一条sql的执行结果作为另一条查询语句的基础，有三种情况：

1. where （子句）

2. having （子句）

3. from （子句）

selectname1_from_table1 fromtable1 wherename1_from_table1 in (selectdistinct name2_from_table1from talbe1);

selectname1_from_table1,avg(name2_from_table1)from table1 group byname1_from_table1 havingavg(name2_from_table1)>=(selectavg(name2_from_table1) fromtable1 wherename1_from_table1=number);

按照name1分组，求每个部门的平均工资，列出平均工资大于等于部门号为number的部门平均工资的部门

Select* from (select name1_from_table1,name2_from_table1from table1) wherename2_from_table1>number;

name > some （子句） ： 至少比某一个要大

<some <=some >some >=some =some <>some

=some 等于 in

<>some不等于 not in

nane >all （子句） ：比所有的都大

<all <=all >all >=all =all <>all

<>all 等于 not in

=all 不等于 in

exists（子句）： 测试一个子查询的结果是否存在元组（行）。

not exists （子句）：测试子查询是否不存在元组（行）。

unique（子句）：测试一个子查询是否不存在重复的元组（行）。

not unique（子句） ：测试是否存在重复的元组（行）

-------------------------------------------------------------

自然连接：

表1的属性和表2的属性共同属性name相同，两张表根据这一特点返回一张表。

selectname from table1natural join table2where P;

selectname from table1natural join table2,table3 where P;

selectname from table1natural join table2 naturaljoin table3 whereP;

selectname1 from (table1natural join table2) jointable3 using (name2)where P;

内连接：符合连接条件数据就被选中，不符合连接条件数据就被过滤。

Ajoin b on condition;

Selecttable1.name,name1_from_table1,name1_from_table2from table1 join table2on name2_from_table1=table2.name;

Ainner join b on condition;

Selecttable1.name,name1_from_table1,name1_from_table2from table1 inner jointable2 onname2_from_table1=table2_name2;

外连接：外连接的结果集等于内连接的结果集加上匹配不上的记录，一个字段都不能少。

Aleft outer join b oncondition;

Selectname1_from_table1,name2_from_table1,name1_from_table2from table1 letf outer jointable2 on name2_from_table1between name2_from_table2and name3_from_table2;

Aright outer join b oncondition;

Selectname1_from_table1,name2_from_table1,name1_from_table2from table2 right outer jointable1 on name2_from_table1between name2_from_table2and name3_from_table2 wherename1_from_table2 is null;

############################################################

DDL：数据定义语句：create、drop、alter、

建表：

Createtable table_name(

Nametype,

Nametype,

…

);

Type:

Number(m,n) 数字类型

Char(n) 定长字符串，无论存入过少都是n个字符，不够补空格。

Varchar2（n）变长字符串，不够n个字符就不需要补位。

Date 日期类型。

删表表：

droptable table_name;

日期的操作：

日期的默认格式是：dd-month-yy,时分秒都是0.

插入当天日期可以用sysdate直接代替日期字符串。

改变默认格式用to_char(name或date,‘yyyy-mm-ddhh24:mi:ss’)函数。

把一个日期字符串编程一个日期值用to_date(‘2014-05-1416:50:00‘，‘yyyy-mm-ddhh24:mi:ss’)函数。

Yyyy：四位年

Mm：两位月

Dd：两位天

Hh：小时，hh24表示24小时制。

Mi：分钟

Ss：秒

Mon：英文月，也可以写成month。

Day：星期

两个日期相减得到两个日期相差的天数：

Selectsysdate-to_date(‘2014-05-14 18:52:00’,’yyyy-mm-dd hh24:mi:ss’)from dual;

日期可以加减一个整数：

Selectto_char(sysdate,’yyyy-mm-ddhh24:mi:ss’),to_char(sysdate+1,’yyyy-mm-dd hh24:mi:ss’) fromdual;

月份加减函数：add_months(日期，加减多少月)

Selectto_char(add_months(sysdate,2),’yyyy-mm-dd hh24:mi:ss’) from dual;

计算两个日期相差多少月：months_between(date1,date2)

Selectmonths_between(sysdate,to_date(‘2008-08-08 20:08:08’,’yyyy-mm-ddhh24:mi:ss’)) from dual;

计算当前对应月的最后一天的当前时间：last_day(日期)

Selectto_char(last_day(sysdate),’yyyy-mm-dd hh24:mi:ss’) from dual;

计算下一个星期几对应的时间：next_day(日期，‘星期几‘)

Selectto_char(next_day(sysdate,’Monday’),’yyyy-mm-dd hh24:mi:ss’)from dual;

Round来处理日期，默认以天为单位进行向前取整：

Selectto_char(round(sysdate),’yyyy-mm-dd hh24:mi:ss’) from dual;取下一天的零点

Selectto_char(round(sysdate,’hh’),’yyyy-mm-dd hh24:mi:ss’) fromdual;取下一个小时的零分零秒

Trunk来处理日期，默认以天为单位进行向后取整（截取）：

Selectto_char(trunk(sysdate),’yyyy-mm-dd hh24:mi:ss’) from dual;取这一天的零点

Selectto_char(trunk(sysdate,’mm’),’yyyy-mm-dd hh24:mi:ss’) fromdual;取这个月的第一天的零点

变更表：

Row表示列、排

Column表示行、纵

Altertable table_name add(row_name type);

Altertable table_name modify(row_name type);

Altertable table_name renamecolumn current_row_name tonew_row_name;

Altertable table_name drop column(row_name);

Altertable current_table_namerename to new_table_name;

############################################################

DML：数据操作语句：insert、delete、update、

插入语句：

insertinto table_namevalues(value1_for_name1,value2_for_name2,…);

字段值的顺序要和表头中的顺序相同,这种方式必须给出所有字段值。

insertinto table_name(name1,name2,…)values(value1_for_name1,value2_for_name2,…);

字段值要和前面的字段顺序相同。

删除语句：

Deletefrom table_name wherecomdition;

按条件删除。

Deletefrom table_name;

删除所有语句。

更新语句：

Updatetabl_name setname1=value1,name2=value2where comdition;

############################################################

事务：commit、rollback、savepoint、

Select没有事务性，不能commit也不能rollback。

ddl语句是自动提交的，不需要commit，也不能rollback。

dml语句在别的数据库中是自动提交的，在oracle中具有事务性。

确认这次操作：

Commit；提交

撤销这次操作：

Rollback；回滚

事务的原子性。

如果一个事务中的dml操作没有提交，则在另一个事务中（另外登录到sqlplus）不能发现数据的变化，这叫事务的隔离性。

事务的一致性。

事务的持久性。

事务的四个基本要素决定了一个事务的操作要么成功，要么失败，

保存点：保存点可以做到部分成功，部分失败。

Savepointlabel;

Eg：

Update…

Savepointa;

Update…

Rollbackto a;

Commit;

############################################################

授权：grant、revoke、

授权语句：

Grantpermissions to user;

收回权限：

Revokepermissions from user;

permissions结构：

all：授予所有权限

dba：授予dba权限

unlimitedtablespace：授予不限制的表空间

$authority$object：以下结构

authority取值：

select：

connect：

resource：

create：

insert：

update：

delete：

rule：

object取值：

table

dictionary

view

sequence

synonym

database

index

user取值：

public：代表是所有用户的简写

group$group：将要赋予权限的组

$username：将要赋予权限的用户

############################################################

约束：

对数据库中的表的字段加一些限制。这是保护数据的最后一道屏障。

列级别约束：在定义表的一列时，直接在这一列的后面加约束限制。

表级别约束：在定义完表的所有列之后，再选择某些列加约束限制。

6种具体的约束：

1. 主键：primarykey ,设置成主键的字段是非空的，并且是唯一的，一个表的主键只能有一个。

2. 唯一：unique，这个字段的值是不能重复的。

3. 非空：notnull ， 这个字段的值不能是null值。

4. 检查：check，这个字段的值必须符合检查条件。

5. 外键：

6. 排除：

references 设置外键

ondelete cascade :级联删除

ondelete set null:级联置空

主键的列级约束：

Createtable table_name(

Name1type1 primary key

);

Name1被设置成列级主键，只能是唯一，不能为空，没有指定列级主键约束系统会自动取一个约束名字。

给主键列级约束取名字

Createtable table_name(

Name1type1 constraintalias_name1_pkprimary key

);

唯一的列级约束：

Createtable table_name(

Name1type unique

);

Name1被设置成唯一，这个字段的值不能重复。

给唯一的列级约束取名字

Createtable table_name(

Name1 type constraintalias_name1_ukunique

);

非空的列级约束：

非空的列级约束取名字

检查的列级约束：

Createtable table_name(

Name1type1 check(conditions)

);

Name1被设置成检查，只有满足conditions条件才能插入。

检查的列级约束取名

Createtable table_name(

Name1type1 constraintalias_name1_ckcheck(conditions)

);

表级约束：

表级约束可以做联合约束，主键、唯一、检查都有表级约束，非空没有表级约束。

Createtable table_name(

Name1type1,

Name2type2,

Name3type3,

Name4type4

Constraintalias1_name1_pkprimary key(name1),

Constraintalias2_name2_ukunique(name2),

Constraintalias3_name3_ckcheck(conditions)

);

外键约束：

外键约束涉及到两张表，父表（主表） 和子表（从表）。

定义了外键的表就是子表。

如果两张表建立了主外键关系，则外键的字段的值要么引用自父表，要么是空值。

外键的列级约束：

References

Ondelete cascade

Ondelete set null

1. 建表

一般先建立父表，后建立子表

Createtable parent_table_name(

Pname1type1 primary key,

Pname2type2

);

Createtable child_table_name(

Cname1type1 primary key,

Cname2type2 referencesparent_table_name(Pname1)

);

给外键约束取名字

Createtable child_table_name(

Cname1type1 primary key,

Cname2type2 constraintalias_Cname2_fkreferences parent_table_name(Pname1)

);

2. 放入数据

一般先插入父表数据，后插入子表数据，除非子表的外键为NULL。

Insertinto table parent_table_namevalues(value1_Pname1,value2_Pname2);

Insertinto table child_table_namevalues(value1_Cname1,value2_Cname2);

注意子表中的外键需要和父表中的主键一致，也就是value2_Cname2=value1_Pname1.

3. 删除表

只有先删除子表，才能删除父表，除非子表中无数据。

Droptable child_table_name;

Droptable parent_table_name;

或者解除主外键关系，再删除父表

Droptable parent_table_namecascade constraints;

4. 删除数据

要想删除父表中的数据，必须先要删除和父表数据相关联的子表数据。

Deletefrom child_table_name whereCname2=number;

Deletefrom parent_table_name wherePname1=number;

或者使用级联(级联删除和级联置空都可以)：

Ondelete cascade 级联删除

Createtable parent_table_name(

Pname1type1 primary key,

Pname2type2

);

Createtable child_table_name(

Cname1type1 primary key,

Cname2type2 constraintalias_Cname2_fkreferences parent_table_nameon delete cascade

);

Deletefrom parent_table_name wherecondition;此时就不需要先删除子表中的关联数据。

Ondelete set null 级联置空

Createtable parent_table_name(

Pname1type1 primary key,

Pname2type2

);

Createtable child_table_name(

Cname1type1 primary key,

Cname2type2 consrtaintalias_Cname2_fkreferences parent_table_name(Pname1)on delete set null

);

Deletefrom parent_table_name wherecondition;此时不需要先删除子表中关联的数据。

外键的表级约束：

Foreignkey

Ondelete cascase

Ondelete set null

Ondelete cascade 级联删除

Createtable parent_table_name(

Pname1type1 primary key,

Pname2type2

);

Createtable child_table_name(

Cname1type1 primary key,

Cname2type2,

Constraintalias_Cname2_fkforeign key(Cname2)referneces parent_table_name(Pname1)on delete cascade

);

Ondelete set null 级联置空

Createtable parent_table_name(

Pname1type1 primary key,

Pname2type2

);

Createtable child_table_name(

Cname1type1 primary key,

Cname2type2,

Constraintalias_Cname2_fkforeign key(Cname2)references parent_table_name(Pname1)on delete cascade;

);

############################################################

序列sequence：

建立序列：

Createsequence name;

测试序列：

Selectname.nextval from dual;

删除序列:

Dropsequence name;

Name.nextval

Name.currval

索引index:

索引的建立需要大量的时间和空间,删除索引不会释放索引所占用的空间。

主键和唯一系统会自动建立索引。

建立索引：

Createindex name ontable_name(name_of_table)；

删除索引：

Dropindex name;

视图view:

对应一条sql语句，可以对同一份物理数据做不同的表现,试图不占空间。

创建试图：

Createor replace view name asselect name_of_table from table;

Select* from (select name_of_tablefrom table) where condition;

Select* from name where condition;

删除试图：

Dropview name;

未完待续......