SQL语言基础

 

上周对oracle进行了培训，在此对上周的培训内容及知识点进行回顾和整理。

第一部分：数据库及SQL语言基础

1、数据库软件的构成：DBMS（数据库管理系统）+数据字典（存储数据库有关对象的定义信息，如数据库表的定义、用户定义、权限定义等）+数据库接口+数据库应用开发工具。

2、window中的oracle服务

OracleHOME_NAMETNSListener服务。该服务为监听器服务，用于监听客户端的连接请求。

OracleServiceSID服务。数据库实例服务，若想用数据库，必须启动本服务。

OracleDBCConsoleSID服务。用于启动OEM数据库控制台，仅用于oracle10g。

OracleOraDb10ghome1iSQL*Plus服务，该服务启动之后，可以通过网页登陆oracle，无需启动。

OracleJobSchedulerSID服务，用于管理预定义的任务，无需启动。

3、SQL语句的分类

数据操作（DML[data manipulation language]）—select, insert, update, delete

数据定义（DDL）---create，alter，drop…

数据控制（DCL）---grant，revoke

事务控制（TCL）---commit，rollback，savepoint

4、建表语句

      方法一：直接定义

create table student(
       sno char(6),
       sname       varchar2(10),
       sex char(1),
       birthday date,
       dno char(3)
);

方法二：通过选择语句

create table tab_name as select …….

5、SQL中的数据类型

char(size)          定长的字符型

varchar2（maxsize）          可变长的字符型

number(p)     浮点数为P的定点数

number 浮点数

number(p,s)    总长为p的小数点后s位的浮点数

date  隐含的格式为dd-mon-yy，即“28-sep-2005”，所以日期一般会用to_date()方法进行转换。

空格是比任何字符都要小。

6、表结构的修改与删除（DDL）

alter table student add (列名 列定义) add(addr varchar2(20)) modify (sname varchar2(15)) drop (列名);

删除表:

drop table student

修改表的名称

alter table temp
rename to trmp-1

修改表的某列名称

alter table student
rename column 列A to 列B

7、完整性约束。

完整性约束是指对表结构中的某些字段进行约束，限制其取值范围或其他，完整性约束有以下几种：

1、实体完整性约束（primary key，即主键约束）

create table zhenglin_test(
stu_no number constraint s1 primary key,
stu_name varchar2(10)
);

主键约束的特点为：主码的值不能重复，也不能为空（即，主键不能为空，不能重复！其中主键不重复也保证了实体的唯一性及可追踪性）。

在建立主键之后，数据库会为该主键创建索引

2、引用完整性约束（foreign key,即外键）

引用完整性约束的特点为:外码的值必须与另一关系中的主码的值匹配.  说白了,就是外键的值必须是另一表的主键的值.

举例说明:假设现在存在两个表格emp与dept表,其中两个表的定义如下:

emp(员工表)的表结构如下所示:

create table emp(
  empno char(4) primary key,
  ename varchar2(10),
  deptno char(4)
);

dept(部门表)的表结构如下所示:

create table dept(
  deptno char(4) primary key,
  dname varchar2(20),
  phone number(10)
);

因为员工表中有deptno字段，所以可以设立外连接，修改emp表，具体如下：

create table emp(
  empno char(4) primary key,
  ename varchar2(10),
  deptno char(4) constraint s1 references dept(deptno) [on delete cascade]/[on delete set null]
);

对以上进行解释:  为emp表建立一个约束,该约束的名称为s1，其关联到的表为dept（部门表），关联到的字段为deptno。

另外，在emp，dept两表中，emp为子表，dept为主表。因此，必须先建立主表，后建子表。

为了保证主表与子表中数据的一致性，后面添加了两句话：on delete cascade/on delete set null。这两句话的意思是：当主表中的数据删除时，子表中数据英如何处理。on delete cascade意为：主表中删除数据时，子表的数据也删除；on delete set null意为：当主表中的数据删除时，子表的数据的该字段置为null。若没有这句话（二者之一），则删除主表记录时，若子表存在数据，则不能删除。

3、唯一完整性约束

唯一完整性约束，是指在表中，某一字段/多字段是唯一的，不可重复的（但不是主键）

单字段的唯一完整性约束:

dname varchr2(10) constraint d1 unique

多字段的唯一完整性约束:

create table unique_test(
  id number,
  fname varchar2(20),
  lname varchar2(20),
  address varchar2(100),
  email varchar2(40), constraint name_unique unique(fname,lname)
);

4、非空完整性约束

很简单，就是某一列不能为空，举例：

create table test(
    sname varchar2(10) constraint s1 not null );

5、check完整性约束

很easy，对某一列的值限定其范围，举例：

create table test(
  credit number(1) constraint c1 check(credit between 1 and 5)
);

对于约束,可以分为两种约束：1、列约束。什么是列约束，其为，涉及单一的列，并且是列的定义语句之后;2、表约束。表约束是指约束涉及至少一列，并且是建表语句全部完成之后，定义的约束。如下代码所示：

create table test(
     empno char(4) constraint s1 primary key
);

这样就是列约束,

create table test(
     empno char(4), constraint s1 primary key(empno)
);

这样就是表约束。

个人感觉这就是一个语法糖，没啥内容。

6、关于约束的其他事情

① 数据的迁移、备份等操作中，需要对约束进行关闭或打开，所以对约束的操作语法为：

1、关闭约束

alter table 表名
disable constraint 约束名[cascade]

方括号中的cascade的作用是指：关闭存在有完整性关系的约束。【此处】

2、打开约束

alter table 表名
enable constraint 约束名

3、查看约束

select constraint_name,constraint_type from user_constraints where table_name='你需要查询的表的名称';

8、DML语句

DML语句是指插入、修改、删除的语句（insert、update、delete、truncate等）

举例：

insert into dept values('50','new_dept','beijing'); /*不写列名时，需要完整对应*/
insert into dept(deptno,dname) values ('111','22222');  /*指定列名*/
insert into dept select * from dept; /*批量的插入数据*/ update dept set dname='3636' where deptno='50';  /*更新数据*/ delete from dept where deptno='50';   /*删除指定项*/ truncate table dept;  /*删除表中所有的数据*/

1、select语句

呵呵，select语句的模板：

select *** from 表/视图/快照 where 针对行的逻辑表达式 group by 分组1、2... having 针对组的逻辑表达式 order by 排序列1、2...

2、表达式中的运算符

字符串连接运算符：||

SQL比较运算符：

[not] between ... and ...
[not] like _  %     /*模糊查询*/ is [not] null /*空值判断*/
[not] in /*集合运算符*/

3、where子句

使用where子句是对表中数据的每一行进行检查，符合条件的才能返回到结果集中

① escape子句，若在程序中遇到需要进行匹配，例如，需要查询XXXXA_BXXXX内容时，就会用到匹配

select ename from emp where ename like '%A\_B%' escape '\';

就可以查询到员工姓名中包含:A_B的员工姓名了

4、order by排序语句

①按照指定顺序显示结果

select ename from emp where sal is not null order by sal desc

② order by还可以使用数字指定按哪列排序

③ order by还可以指定按照多列进行排序，其排序原则为，优先按照第一列，若第一列值相同，则按照第二列的值进行比较。

5、外连接

oracle中有左连接和右连接。正常情况下，若某行不满足条件，则该行不会出现在结果集中。但是oracle中的左连接和右连接，可以查询到不满足条件的结果。

举例：有以下两个表格（student和dep)，学生表和院系表，其数据为：

学生表中的数据:

student 

  sno            sname         dno     
980001            王山           d01
980002            关林           d01
980003            李雪           d02
980004            冯晓春         d03
980005            陆展风         d04
980006            蒋丽           null

院系表中的数据:

dep

dno             dname
d01            物理系
d02            化学系
d03            中文系
d04            传播系

若为正常的查询内容，其SQL语句应为：

select sno,sname,dname from student,dep where student.dno=dep.dno;

若想查询所有学生的信息,尽管有些学生的院系没有内容,此时会用到左连接.

select sno,sname,dname from student,dep where student.dno=dep.dno(+);

此时查询出的数据为:

即,优先按照学生信息,而不管另一信息.

另外左连接还有另外一种写法

select sno,sname,dname from student left join dep on student.dno=dep.dno;

其效果是一样的.

同样,右连接. 以上为例,SQL语句为:

select sno,sname,dname from student,dep where student.dno(+)=dep.dno;

则,此时数据为:

即,将部门信息为准,列出所有信息.

同样,右连接也有其他的写法:

select sno,sname,dname from student right join dep on student.dno=dep.dno;

【总结】：①在左连接中，其数据是以左侧表的数据为准；右连接是以右侧表中数据为准。

②左连接的另一形式为：student.dno=dep.dno(+)， 即，“+”的位置与连接是相反的！

6、自连接

自连接比较简单，就是连接同一表的不同的行。其实现的方法：为一个表建立两个别名，模拟两张表，通过条件，完成查询。

例如:

select e2.empno,e2.ename from emp e1,  emp e2 where e1.empno='e01' and e1.mgr=e2.empno;

9、集合运算（并、交、minus）

1、与（union）

其语法为：

(select empno,ename,sal from emp1 where sal>50)      union (select empno,ename,sal from emp2 where age>50);

2、交集（intersect），即两个域的相同的部分。

(select sno from sc where cno='c01') intersect (select sno from sc where cno='c02');

3、minus。minus是指将满足条件1的结果集中删掉满足条件2的。即，满足条件1的结果集减去满足条件2的结果集。

(select sno from sc where cno='c01')
minus
(select sno from sc where cno='c02')

总结:集合运算主要是查询相同的内容,但是查询条件不一致,所以需要用到集合运算符.

集合运算有以下的特点:

① 具有相同的列,即,查询的列相同.

② order by 子句需要跟在最最后面。

4、查询结果中去重

查询结果中去重可以再查询字段/查询子句后面添加distinct或unique。例如：

select distinct job from emp

5、列数据的统计函数

① sum(列名)：求和

② avg(列名)：求平均值

③ MIN(列名)、max（列名）：求最小、最大值

④ count(列名）：求列中非空值的个数

⑤ count(*)：求行数

去掉重复值之后的统计：

select count(distinct cname) from emp

10、分组统计

1、group by  列名。

group by 列名：是指按照该列的值，进行分组，列值相同的分为一组。然后根据条件对分组的数据进行查询。

2、having 表达式：

having是group by的where条件，即，having 表达式是对分组中的数据进行的条件查询

where子句是对表、对行的查询，而having子句是对指定组的查询

举例：在scott表中，有emp表，dept表，若希望知道：每一个部门的部门代号、名称、及该部门的员工数量，则该如何写SQL语句。

select     a.deptno,dname,a.emp_count  from (select emp.deptno,count(*) emp_count from emp group by deptno) a,dept where a.deptno=dept.deptno;

【注意】：在select后的列，即需要查询相关数据的列，不是被统计的列，就是分组列！

3、组中分组

在group by子句中可以指定多个分组列（一般为2个），例如：

select dno,sex,count(*) from student group by dno,sex;

group by后的第一列，是指，表中的数据按照第一列进行分组，分组之后是按照第二列进行组内分组。

11、子查询

子查询是指查询语句嵌入到查询语句的查询条件中。很简单

select ename,sal
from emp where sal<(select avg(sal) from emp);

子查询中用到的比较运算符: in ,not in, exists(r), >,>=,<,<=等

12、层次查询（树查询）

层次性查询或树查询与自连接相同，同样也是在一张表格中查询，但是层次查询用到了递归，所以层次查询又称为：递归查询。

层次查询的语法为：

select ... from ... where 条件1 start with 条件2 connect by 条件3

其中，条件2是根节点的起始位置

connect by 后面需要添加prior关键字。prior关键字是指的上一条记录。例如，在scott用户下的emp表中，层次查询的SQL语句为：

select leselect level,empno,ename from emp start with ename='KING' connect by prior empno=mgr;

connect by 中prior是指父节点的数据，且，prior在左侧，所以是从上之下的遍历，另外，从上到下的遍历是采用的中序遍历方式。

13、SQL中的函数

LOWER -- 变小写字母 UPPER -- 变大写字母 CONCAT(string1, string2) --  合并字符串 SUBSTR('character',4,3) -- 取子串 LENGTH('Tsinghua') -- 取长度，8 LENGTH('清华大学') -- 取长度，4；
所以一般常用的是lengthb()方法。 nvl(parm1,parm2) -- 为空，则为parm2,不为空，则为本值parm1.通常会用在从数据库中取数据。

2、数值型函数

ROUND(num1,num2) -- 取到指定位数（四舍五入）,num1是操作数，num2是截取的位数； eg. round(68.247,2) → 68.25
      round(68.247,0) → 68
      round(68.247,-1) → 70
TRUNC(number1,number2) -- 截取函数 eg.
trunc(68.247,2) →68.24
trunc(68.247) →68
trunc(68.247,-1) →60
FLOOR -- 取<=参数值的最大整数 MOD --取模
eg.  mod(234, 100)  -->  34

3、类型转换函数

to_char(sysdate, 'yyyy-mm-dd, day') -- 将时间转换为字符串 to_char(sysdate,'hh:mi:ss') -- 将时间转换为具体的时间 to_char(sysdate,format-string)  
其中：format string有以下的格式：
yy/mm/dd
yyyy-mon-dd
hh:mi:ss day:    Monday...
Dy       mon....(简写）

4、其他格式的转换方法：

to_number(string)   -- 将字符串转换为数值型
to_date(sting, fromat)  --将字符串转为日期型的

5、有关空值的函数

NVL(表达式1，表达式2) -- 表达式非空，则为表达式1；否则为 表达式2的值 NVL2（表达式1，表达式2，表达式3） --  表达式1非空，则返回表达式2， 为空，则返回表达式3

6、去空函数

trim()方法是将字符串的前后的空格去掉
若想去掉所有的空格，则可以使用replace方法
replace('   adfa   test   ',' '); 即可

第一天的培训整理，到此结束。

转载于:https://my.oschina.net/u/144357/blog/141593