SQL语句学习笔记

1.什么是SQL
SQL：Structured query language
功能：查询，操纵，定义，控制

2.SQL支持关系数据库三级模式
基本表：独立存在的表，每个基本表对应一个存储文件，一个表可带若干索引
存储文件+索引=内模式
视图：是一个虚表，数据库中仅存视图定义，不存在对应的数据

3.SQL命令的种类
1)DDL(Data Definition Language),数据定义语言
用户创建或重新构建数据库的语言
create table,create index
alter table,alter index
drop table,drop index

2)DML(Data Manipulation Language),数据操纵语言
用于在关系数据库对象中操纵数据
insert, update,delete

3)DQL(Data Query Language),数据查询语言
对数据库中的信息寻找和定位
select

4)DCL(Data Control Language),数据控制语言
用于创建与用户访问相关的对象，也控制着用户的权限分配
alter password 修改命令
grant 为用户授予特权
revoke 从用户处收回特权

5)数据管理命令
审计和分析对数据库的操作，并可分析系统的执行过程
start audit(审计)
stop audit

6)事务性控制命令
用户管理数据库的事务命令
commit 用于保护数据库的事务
rollback 用户撤销数据库的事务
savepoint 创建一组事务中的撤销点
set transaction 给事务命名

4.数据库中的表结构
1)数据库中的表
表：字段，记录，列，主键
2)数据和基本数据类型
数据是存储在数据库中的信息集合，数据类型用于给特定的数据提供规则
基本数据类型包括：字符串，数字，日期和时间值

字符串：
定长字符串：character(n)
变长字符串：character verying(n)
数字：
bit(n),bit verying(n),decimal(n,n),integer,smallint
float(p),real(s),double precision(p)
日期和时间值：
date(year,month,day)
time(hour,minute,second)
interval
timestamp
null值：为空

5.数据查询语句
语法：
select[*|all|column1,column2,……]
from table1[,table2]
[where condition]
[group by column1]按照列取值相等的原则进行分组
[order by column2(asc,desc)]按照列值进行排序

6.SQL中操作符的使用
定义：操作符是保留的字或者字符，主要用于比较和算术运算
分类：比较操作符，逻辑操作符和算术运算符
1)比较操作符
包括：=，<>(不等于)，>，<，<=，>=
注意：比较双方的数据类型
2)算术运算符
包括：+，-，*，/等
注意：与数学运算规则一致
3)逻辑操作符
is null(是否为空)
where salary is null 工资为空值
where salary = null 工资中含有null字符

between(在某两个值之间)

in(在一系列值中)

like(相似值的比较)
使用匹配符将一个值同其相似的值比较
匹配符包括：-代表一个字符
%代表多个字符
where salary like '-00' 查找'00'作为后两位数的值
where salary like '200%' 查找'200'作为前三位数的值

exist(是否存在符合条件的数据)
查询在指定表中是否存在一行符合某种条件的数据
where exist(select employee_id from employee_t where employee_id='111')

unique(是否唯一)
在特定的表里搜索每一行是否唯一
where unique(select salary from employee_t where employee_id='111')
测试满足条件的工资是否有重复数据

all/any(一组数据的所有/其中的任何一个)
与某个数据集中所有数据/任何一个比较
where salary > all(selelct salary from employee_t where city='INDIAN')

and/or(逻辑与/或)
and 连接的条件必须都满足
or 连接的条件至少满足一个

7.库函数
函数通常是列名或者表达式相联系的命令
1)统计函数
统计函数：用于提供统计信息，如：累加，总和，平均值
主要的统计函数：
count:统计行数 或者 不为null的列数
语法：count(*) 或者
count([distinct|all]column name)
注意：distinct 统计唯一的行
distinct不能用于count(*),只能用于count(column_name)

sum:求和
仅用于数字类型
语法：sum([distinct]column name)

max:求最大值
语法：max([distinct]column name)

min:求最小值
语法：min([distinct]column name)

avg：求平均值
语法：avg([distinct]column name)

2)字符函数
主要的字符函数：
upper(character string):将指定字符串中的小写字母换成大写字母
lower(character string):将指定字符串中的大写字母换成小写字母
substring(column name,start position,length):从指定字符串中取字符

3)算术函数
abs(绝对值),sin,cos,tan,exp(指数)，sort(开方)，power(幂)
语法：函数名(表达式)

8.数据库更新语句
insert 给表填充数据
语法：
insert into table_name values(value1,value2,……)

update 更新已存在的数据
语法：
update table_name set column_name='***' [where condition]

delete 删除表中的数据
语法：
delete from table_name [where conditon]

9.数据定义语句
数据定义动能包括：基表、视图和索引的定义
1)创建删除修改基表
创建基表语法：
create table table_name(
col_name col_properties constraint,
col_name col_properties constraint,
…………
)

创建临时表语法：
create table #table_name(
col_name col_properties constraint,
col_name col_properties constraint,
…………
)

根据已有的表创建新表：
A.create table table_new like table_old
B.create table table_new as
(select col1,col2,col3…… from table_old definition only)


删除表结构语法：
drop table table_name

修改表结构语法：
alter table table_name
[modify][col_name col_properties]
[add][col_name properties]
[drop][col_name properties]
例：alter table employee_t modify(emp_id varchar(10));
更改表名：alter table t rename to s;
更改列名：alter table t rename column n to s;

2)创建删除视图

什么是视图？
视图是存储在数据库中的预先定义好的查询，具有表的外观，可以像表一样对其进行存取，但不占据物理存储空间

视图的特点：
视图的存在依赖于生成视图的表
视图能用作数据库安全的一种形式
利用视图维护综合数据

创建视图：
基本语法：
create view view_name as
select statement [with check option]
1.从单独的表中创建视图
2.从多表中创建视图
3.从视图中创建视图

从单独的表中创建视图
语法：create view view_name as
select *|col1[,col2,……]
from table_name
where expression

从多表中创建视图
语法：create view view_name as
select *|col1[,col2,……]
from table1,table2
where expression

从视图中创建视图
语法：create view_name as
select * from view1

视图的查询：
对视图的查询与对基本表的查询一样
系统执行视图查询时是把它转换成等价的对基本表的查询

视图的更新：
对视图的更新最终要转换成对基本表的更新

视图更新条件：
视图必须未涉及连接
视图必须不包含group by 子句
视图不能包含任何分组函数
视图不能使用distinct子句
where 子句不能包含表的嵌套引用

例：update employee_summary(视图名)
set pay_rate=18
where emp_id = '123456'

转换成：update employee(表名)
set pay_rate=18
where emp_id = '123456'

删除视图:
语法：drop view view_name

删除了视图，由此视图导出的其他视图也将自动被删除

若导出此视图的基本表被删除了，则此视图也将自动删除

视图的优点：
视图对于数据库的重构提供了一定程度的逻辑独立性
简化了用户观点
视图使不同的用户能以不同的方式看待同一个数据
视图对机密数据提供了自动的安全保护功能

3)创建删除索引
语法：
create [unique|cluster] index index_name
on table_name[col_name asc/desc]
unique 每一个索引只对应唯一的数据记录
cluster 聚簇索引，是指索引项的顺序与表中记录的物理顺序一致的索引组织

删除索引：
drop index index_name

10.数据控制语句：

数据控制包括：
数据的安全性控制
完整性控制
并发控制和恢复


授权和收回权限

授权：将对指定操作对象的指定操作权限授予指定的用户

grant <权限>[,<权限>,……]
[on <对象类型><对象名>]
to <用户>[,<用户>,……]
[with grant option]

权限：select,insert ,update,delete,alter,index,create
对象类型：table,database
对象：属性列，视图，基本表，数据库

例1：把查询student 表权限授予用户U1/所以用户
grant select on table student to U1/public

例2：把对表SC的insert权限授予用户U2,并允许他转授
grant insert on table sc to U2 with grant option;

收回权限：
revote <权限>[,<权限>,……]
[on <对象类型><对象名>]
from <用户>[,<用户>,……]

把所有用户查询SC表权限收回
revoke select on table SC from public
把用户U1修改student表的sno字段的权限收回
revoke update(sno) on table student from U1;

11.零散的知识点
1)主键约束的创建，添加和删除：
主键约束的操作在mysql数据库和oracle数据库里有所不同，下面将分别介绍：
A.mysql数据库
a)创建
在建表时创建主键约束
create table tablename(
col1 number primary key,
……
);
b)添加
表建立好之后向表中添加主键约束
两种主键，primary key 和 not null auto_increment
primary key:
alter table tabname add primary key(col)
not null auto_increment，以1为开始的自增长的主键：
alter table tabname change col col int(10) not null auto_increment=1;
c)删除:
删除主键：
alter table tabname delete primary key;
删除自增长主键：
alter table tabname change col col int(10);
B.oracle 数据库
a)创建
create table tablename(
col1 number primary key,
……
);
或者：
create table tablename(
col1 number,
……,
constraint yy primary key(col1)
);
注：yy是该主键约束的名称
b)添加
alter table tablename add constraint pk_tabname primary key(col);
注：pk_tabname 是该主键约束的名称
c)删除
删除有命名的主键约束：
alter table tablename drop constraint yy;
删除无命名的主键约束:
先搜寻主键名称：
select * from user_cons_columns where table_name='**' or column_name='**';
注：还有一个保存约束的表格：user_constraints
select * from user_constraints where ……
查找到主键名称后再删除该主键：
alter table tablename drop constraint pkname;
d)是约束失效
alter table tablename disable constraint fkname;

2)外键约束的创建，添加和删除：
mysql和oracle相同：
a)创建
create table tablename(
col1 number,
……，
constraint fkname foreign key(col1) references table2name(col2) on
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

);

b)添加
alter table tablename add constraint fkname foreign key(col1) references table2name(col2) on
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
c)删除
alter table tablename drop constraint fkname;

注：
CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。
SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词，这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。

NO ACTION: NO ACTION意味这不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主要键值的企图不被允许进行

RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样，删除ON DELETE或ON UPDATE子句。（一些数据库系统有延期检查，并且NO ACTION是一个延期检查。在MySQL中，外键约束是被立即检查的，所以NO ACTION和RESTRICT是同样的）。

3)序列的创建
A.mysql没有现成的序列，如果要达到跟oracle的序列一样的效果，可以建立一个表，只有一个字段，一直存储下最新的数字
如下：
create table my_seq(
seq int(10) not null default 1
);
每次用完序列里的值以后，存入新的：
update my_seq set seq=seq+1;

B.oracle数据库的序列
a)创建
create sequence myseq
increment by 1 --自增长度
start with 1 --起始值
nomaxvalue --不设置最大值
order --指定一定往下增加
nocycle --不循环，CYCLE和NOCYCLE 表示当序列生成器的值 达到限制值后是否循环
cache 10 --CACHE(缓冲)定义存放序列的内存块的大小，默认 为20。NOCACHE表示不对序列进行内存缓冲。对序 列进行内存缓冲，可以改善序列的性能

b)修改
序列的某些部分也可以在使用中进行修改，但不能修改start with选项。对序列的修改只影响随后产生的序号，已经产生的序号不变。修改序列的语法如下：
alter sequence myseq
increment by 10
maxvaule 10000 -- 到10000后从头开始
cycle ;

c)删除
drop sequence myseq;

注： 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。

4)触发器的使用
A.mysql触发器
a)创建
create trigger <触发器名称> --触发器必须有名字，最多64个字符,可能后面会附有分隔符，它和MySQL中其他对象的命名方式基本相似。
{before|after} --触发器有执行的时间设置：可以设置为事件发生前或后
{insert|update|delete} --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发
on <表名称> --触发器是属于某一个表的：当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活。我们不能给同一张表的同一个事件安排两个触发器。
for each row --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。
<触发器SQL语句> --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。这里支持复合语句(BEGIN / END)，流控制(Flow-of-control)语句(IF, CASE, WHEN, LOOP, WHILE, REPEAT, LEAVE,ITERATE)，变量声明(DECLARE)以及指派(SET),同时允许条件声明。

实例：
创建两个表，当给表一插入一条记录时给表二自动插入相同的记录
创建表一：
drop table if exists tab1;
create table tab1(
t1_id varchar(11)
);

创建表二：
drop table if exists tab2;
create table tab2(
t2_id varchar(11)
);

创建触发器：
drop trigger if exists t_afterinsert_on_tab1;
create trigger t_afterinsert_on_tab1
after insert
on tab1
for each row
begin
if new.t1_id is not null
then insert into tab2(t2_id) values(new.t1_id);
end if;
end;

注：你必须拥有相当大的权限才能创建触发器（CREATE TRIGGER），如果你已经是Root用户，那么就足够了。这跟SQL的标准有所不同。
在触发器的SQL语句中，你可以关联表中的任意列。但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。因此你必须用这样的语法来标识：
"NEW . column_name"或者"OLD . column_name".这样在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量("transition variables")。
对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。

b)删除
drop trigger triname;