SQL笔记

备注：[]：可省略

插入数据

INSERT INTO 表名[(列名, ...)] VALUES(值1, ...);

修改数据

Update 表名 set 列名=’新值’ where 过滤条件;

删除数据

DELETE FROM 表名 WHERE 过滤条件;
TRUNCATE TABLE 表名;                ------------清空表内所有数据，并无法回滚
----只能删除记录 不能删属性
----逻辑删除 update 物理删除 delete

查询数据

Select |DISTINCT不重复出现数据|【列名】/【*】/【函数】 
from 表名 
[where] 过滤条件 ----列 like ‘值1%’//记录包含有值1开头的列
_下划线 仅且只代表一个任意字符并必须占一个位  
%百分号 代表任意长度的任意字符，百分号可以为空，尽量进行局部扫描尽量不要用百分号前有百分号
|in（值1，值2）//包含值1和值2的记录
|not in//不包含
|rownum //控制返回记录的行数 <小于号会自动排序 PL-sql  top（）//T-sql的
|between 值1 and 值2//值1到值2的数据
[starting with]  ----类似 like ‘%’
[group by]       ----分组   
[having]         ----分组后的过滤条件（过滤条件为函数）
[order by];      ----排序--会降低查询速度，建议使用索引 ASC升序  DESC降序

函数

AVG(列名) 平均数   count()统计求个数   sum()相加    max()最大   min()最小 
Variance()方差    STDDEV()标准差  ABS()绝对值 ceil()最大整数 floor()最小整数 mod(A，B)A与B取余
Concat()连接两个指定的字符 
Length(x)返回以字节为X的长度，包括填充字符如果值未知返回null
Ltrim 裁剪字符左边部分  rtrim 裁剪字符右边部分  trim 裁剪字符两边字符  substr 返回字符的一部分
upper()转为大写字母 lower()转为小写字母
Add_months(日期,要增加的月数)          Last_day ()指定日期的最后一天  
Months_between(日期1，日期2) 日期1到日期2还有多少个月
Next_day(日期，星期1)指定日期的下一个星期1在那天
Sysdate 系统当前日期和时间

多表联合查询

Union 不重复联合
Union all 完全联合
Intersect 相交联合
Minus 相减联合
----用法：select语句 union select语句
----类型 属性个数 要相同

笛卡尔积

Select * from 表1 ，表2 where 表1.列=表2.列;

内联接

Select *from 表1 innerjoin 表2 on 表1的列=表2的列;

外联接

–左联接（以左表为基准，右表如有包含满足条件记录，则显示，否则显示为空，记录数为左表数

Select *from 表1 left outer join 表2 on 表1的列=表2的列 ;
Select * from 表1，表2 where 1列=2列（+）;

–右联接（以右表为基准，右表如有包含满足条件记录，则显示，否则显示为空，记录数为右表数

Select *from 表1 outer right join 表2 on 表1的列=表2的列
Select * from 表1，表2 where 1列（+）=2列

–全联接（将左右表中满足条件的合并 不满足的各自显示）

Select *from 表1 full join 表2 on 表1的列=表2的列

嵌套查询

如果某张表所有属性均不在查询结果中出现，那么这张表可用嵌套
可节约系统资源。
–T-sql 在嵌套里不能有 order by
例子：查询姓wang的考试科目名

select c.cname from c where c.cid in
      (select sc.cid from sc where sc.sid =
           (select s.sid from s where s.sname='wang'))

创建表

CREATE TABLE 表名 
（
     列名 数据类型[约束],
	 …
	 [约束]
）;

约束

Pk      Primary key（主键列）validate//主键  唯一 高效 不能为空 簇索引
fk      Foreign key（列） references 外键表（列）validate    //外键
unique   唯一
check    检查
default   默认值

修改表

ALTER TABLE 表名 MODIFY (列名 新数据类型);//修改列的数据类型
ALTER TABLE 表 rename column 旧列 to 新列名;//修改列名
ALTER TABLE 表名 add（要添加的列 数据类型）;//添加一个列

删除表

DROP TABLE 表名;

PLSQL数据类型

数据类型	说明
NUMBER	（数字个数，小数点保留的位数） 整数或浮点数
CHAR（长度）	长度一定为定义的长度 不足的用空格代替最长2000字节
VARCHAR2(长度)	长度为定义长度范围内
DATE	日期、小时、分、秒
BOOLEAN	逻辑值（真、假、空）
ROWID	存储表中每一行的物理地址（十六进制）
UROWID	存储数据库表中没一行的物理的、逻辑的或外部的地址
CLOB	存储巨型、单字节字符对象
BLOB	存存储巨型二进制对象
LONG RAW	可以存储图像、声音、视频数据
BFILE	数据库外文件系统管理LOB的文件指针

语句块

Declare
--变量定义, –I int;
Begin
--执行部分 – i:=10;                                               --变量赋值
           select 列1,列2 into 变量1,变量2 from s where；          --变量赋值
dbms_output.put_line(i);                                --输出
           execute immediate sql语句 into 变量名；      --将sql语句保存在变量内执行
Exception
--错误捕获 when no_data_found then  dbms_output.put_line();
               others
End；

–作用域 在上级被定义了就是局部变量 未被定义就是全局变量

if

If 判断语句 then
Dbms_output.put_line();
Elsif 判断语句 then
Dbms_output.put_line();
Else
  Dbms_output.put_line();
End if;
End;

record

declare
type myrecord is record
(
myname s.sname%type,
age int,
sex varchar2(50)
);
Sr myrecord;
begin
Select ssname,age,sex into sr from s where rownum=1;
Dbms_output.put_line(sr.myname||sr.age||sr.sex);
end;Dbms-output.put_line(sr.myname||sr.age||sr.sex);

loop循环

Loop
Exit when 条件；
End loop;

For 循环

For 变量 in 范围 loop
	--语句
End loop;

–Error function

Declare
  Errorcde varchar
  Errormsg varchar
Begin 
Select  into errorcode from s；-- 故意写一个错误代码
  Exception
When others then
  Errorcode：= sqlcode；                ---错误编码
  Errormsg：=substr（sqlerrm，1,100）；  ----错误的详细情况

Procedure 存储过程

–优点：封装业务 改善SQL语句的性能 保证数据的安全性和完整性 降低网络的通信量

Create or replace procedure 存储过程名
As
--定义变量
Begin
--执行部分
Dbms_output.put_line(‘’);//输入出函数
End;

--调用存储过程
Begin
--存储过程名
End;

–带输入的存储过程
–拥有两个参数的

Create or replace procedure mypro2(input1 int,input2 int)
As
outinfo varchar2(100);
Begin

select s.ssname into outinfo from s where s.sid=(
  select sid from
    (select sid from
        (select sc.sid,sum(sc.grade) as mysum from sc group by sc.sid having sum(sc.grade) between input1 and input2)
     order by mysum desc)
   where rownum=1) and rownum=1;

dbms_output.put_line(outinfo);
End;

Begin
mypro2(20,100);
End;

自定义函数

create or replace function 函数名(参数1 varchar2,参数2 out varchar2)
return varchar2
as
begin

  select cname into 参数2 from c where cid=
  (select * from
    (select cid from sc where sid=
      (select s.sid from s where ssname=参数1)
    order by sc.grade desc)
  where rownum=1);
return 参数2;

end;

----调用

declare
info varchar2(200);
begin
  dbms_output.put_line(函数名('参数1',info));
  dbms_output.put_line(info);
end;

游标

输出15岁到20岁的人的名字

declare
cursor mycursor is select *from s where s.age between 15 and 20;
mys s%rowtype;
begin
  open mycursor;
  
  loop
      fetch mycursor into mys; 
      exit when mycursor%notfound;
      dbms_output.put_line(mys.ssname||' '||mys.age);
  end  loop ;

  close mycursor;
end;

存储过程 + 游标

Create or replace procedure mypS(input1 int,input2 int)
As
cursor mycursor is select *from s where s.age between input1 and input2 or s.age between input2 and input1 
mys s%rowtype;

Begin
  open mycursor;
  
  loop
      fetch mycursor into mys; 
      exit when mycursor%notfound;
      if mys.age<15 then
        dbms_output.put_line(mys.ssname||' '||'小'||' '||mys.sex);
      elsif mys.age>20 then 
        dbms_output.put_line(mys.ssname||' '||'大'||' '||mys.sex);
      else
        dbms_output.put_line(mys.ssname||' '||'中'||' '||mys.sex);
      end if;
  end  loop ;

  close mycursor;
End;

触发器

create or replace trigger 触发器名
after 事件 on 目标表名
for each row
begin
    update spinfo set spcount=spcount+:new.cgcount where spid=:new.spid;
end;

事件 包含了 insert update delete
:new.cgcount //用于insert事件，表示新增数据的cgcount列的值
:old.cgcount //用于delete事件，表示删除数据的cgcount列的值

事务

确认或回滚

select * from s;
savepoint from s where age>20;
select * from s;
commit;

视图

CREATE VIEW 视图名 AS
                     SELECT * FROM 表名；