Oracle数据库SQL语言学习笔记

1、表空间的创建

create tablespace LQ datafile 'D:\ku\LQ_01.dbf' size 200M 
autoextend 
on next 20M
maxsize 1000M;

解释：
创建表空间应该使用 create tablespace 命令；
LQ:表空间名称；
datafile ‘D:\ku\LQ_01.dbf’ :指定表空间的物理文件；
size 200M :指定物理文件的初始大小
autoextend:指定当数据量超过物理文件的存储空间时，自动增加文件大小；
on next 20M:指定每次增长的尺寸为20M；
maxsize 1000M:指定表空间的最大尺寸为1000M；

注意：
datafile:这里的‘D:\ku\’路径必须已经存在，否则无法创建表空间会报错；
执行SQL语句时，结尾的";"务必写上！！！
 

2、表空间的操作

1）本行代码可直接使用，查看所用的用户的表空间。

select user_id,username,default_tablespace from dba_users order by username;

2）本行代码可以查看所有表空间。

select * from sys.dba_tablespaces;

3） 查看表所属的表空间。

select table_name,tablespace_name from user_tables where table_name ='XXX';

4）查看表空间中有那些表。

select table_name from all_tables where tablespace_name='LQ';

5） 更改默认表空间。

alter database default tablespace LQ;

6） 修改表空间名称。

alter tablespace LQ rename to LQ1;

7） 删除表空间。

drop tablespace LQ including contents and datafiles;

8）查看所有的表空间。

select * from v$tablespace;

3、创建、修改，赋权用户

--创建用户guo 密码 guo
create user guo identified by guo;

--切换用户(要给用户添加connect权限才能切换）
conn 用户名/密码

Conn  用户名/密码@全局数据库名

--赋值权限
/*connect:是授予最终用户的典型权利，最基本的权利，能够连接到Oracle数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。
resource:是授予开发人员的，能在自己的方案中创建表、序列、视图等。
dba:是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限。*/
grant connect,resource,dba to guo;

--把某张表的权限赋予某个用户
grant 权限名,权限名 on 表名 to 用户名;

--赋予用户表的部分列的权限
grant 权限名(列名,列名) on 表名 to 用户名;

--赋予权限时，若使用'with grant option'，则获取此权限的用户可以把权限再赋予其他用户
grant 权限名 on 表名 to 用户名 with grant option;

--撤销授权
revoke 权限名 from guo;

--查看用户的权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '用户名'; 

--修改密码
alter user guo identified by guo;
 
--用户解锁
alter user scott account unlock;
 
--用户加锁
alter user scott account lock;

--查看所有用户
select * from all_users;

1）把用户添加到表空间中。

alter user user_naem default tablespace tablespace_name;

2） 查看表空间的路径和id。

SELECT * FROM dba_data_files WHERE tablespace_name='tablespace_name';

 3）查看用户是否添加进去。 

select username,default_tablespace from user_users;

4、表的操作

1）创建表。

create table student(
    sno number(10) constraint pk_si_sno primary key,
    sname varchar2(10),
    sex varchar2(3),
    create_date date
    );

a、 复制另外一个表，创建。

--创建一个表，表的字段是复制另外一个表
create table 表名
as
select * from 表名2;

b、 复制另外一个表的指定字段，创建。

create table 表名（字段1，字段2）
as
select 字段1，字段2 from 表名2;

c、通过指定永远无法满足的 where 条件，还可以只将一个表的结构复制到一个新表中 。

create table 表名
as
select * from list where 1=2;

2） 插单条入数据。

insert into student (sno,sname,sex,create_date) values (1,'李四','男',sysdate);

3）插入多条数据。

insert all
	into table_name (id,name) values(10001,'wang_er')
	into table_name (id,name) values(10002,'zhang_san')
	insert into table_name (id,name) values(10003,'zhao_si')
SELECT * FROM dual;

 4）添加注释。

comment on table student is '学生信息表';
comment on column student.sno is '学号';
comment on column student.sname is '姓名';
comment on column student.sex is '性别';
comment on column student.create_date is '创建日期';

 5）修改某一行。

update student si
   set si.sex = '男'
 where si.sno = 1;

6）数据修改 

1. '增加' 一列或者多列
   alter table student add address varchar2(50);
   alter table student add (id_type varchar2(2), id_no varchar2(10));
2. '修改' 一列或者多列
   (1) 数据类型
   alter table student modify address varchar2(100);
   alter table student modify (id_type varchar(20), id_no varchar2(20));
   (2) 列名
   alter table student rename column address to new_address;
   (3) 表名
   alter table student rename to new_student_info ;
   alter table new_student rename to student_info;   
3. '删除' 一列或者多列，删除多列时，不需要关键字 column
   alter table student drop column sex;
   alter table student drop (id_type, id_no);

 7）查看用户下的所有表。

select * from user_tables;

8）将列标记为未用 

ALTER TABLE 表明 SET UNUSED(列名);

 9）删除表达所有记录，恢复到初始状态

TRUNCATE TABLE 表名;

10）列出表的列及其数据类型（仅在SQL*plus下有用）

DESC 表名;

11）删除用户

--删除当前连接用户的操作
1）首先查找当前用户的会话id
SELECT sid, serial#
FROM v$session
WHERE username = 'YOUR_USERNAME';

2）断开当前用户的连接
ALTER SYSTEM KILL SESSION 'sid,serial#';

3）删除用户
drop user user_name cascade;

 5、order by的使用

order by可以对查询到的数据按照某一列进行排序操作，排序时加上后缀关键字asc或desc。

asc是按升序排列，可以省略。

desc是按降序排列，不能省略。

order by可以指定多个列名，表示同时按多个列值对输出结果排序，列名之间用逗号隔开。排序的方法是先按第一个列值排序，如果有多个记录在第一列的取值相同，那么它们之间的排序就有第二列的值决定，以此类推。

6、group by的使用

使用group by子句可以将具有相同特征的行组织成一组，使得每一组的各行得到唯一的结果。单独使用group by，相当于在select子句中加上distinct或unique限制符，可以剔除查询结果中在指定列伤出现的重复值。

SELECT 列名 FROM 表名 GROUP BY 列名;

 (两个列名要相同)

7、having语句的使用 

having用于子句中，其相当于一个过滤器，剔除不需要的组，在having子句中可以使用聚组1函数。

--例子
select 列名，count(*) number，... from 表明 group by 列名 having count(*) > 2;

8、聚组函数

avg（平均值）、sum（求和）、max（最大值）、min（最小值）（他们使用的对象必须是数值类型的）、count（求行）、

SELECT COUNT(*),SUM(AGE),AVG(AGE),MAX(AGE),MIN(AGE) FROM USERS;

9、通配符

10、子查询

子查询语句必须用括弧括起来，且子查询语句不得带有order by子句。

一般我们将带有子查询的select语句称为嵌套查询，引用子查询的select语句成为外层查询，子查询本身称为内层查询。

子语句返回的结果必须是原子值，即不能是由多个属性值组成。其可以分为三种情况：无值，单值或多值。

11、存储过程的错误

在应用程序中创建存储过程，一旦发现语法错误，数据库服务器便会设置通信去SQLCA和全局区SQL_STATE的值。通信区SQLCA的SQLCODE域的值被设置未一个负数，出错位置记录在SQLERROR域中，同时SQLSTATE的值被设置未42000.在应用成勋中应当编写一些代码，用于检测这些错误，并采取适当的行为。

12、语句使用

1）打开时间显示功能，执行SQL语句时会显示执行时间。

set time on;

2）打开计时功能，执行SQL语句时会显示执行时间。

set timing on;

3）打开回显功能，执行SQL语句时会显示执行的SQL语句。

set echo on;

上面的三个语句通常用于调试和性能优化。通过打开时间显示功能和计时功能，可以了解每个SQL语句的执行时间，从而判断性能瓶颈所在。通过打开回显功能，可以在执行SQL语句时查看实际执行的SQL语句，有助于排查语法错误和逻辑错误。

4）查看表的名称，字段，属性，大小，代码要在编辑器里编写，然后点击运行。

SELECT TABLE_NAME,   /*表名*/
       COLUMN_NAME,  /*字段名*/
       DATA_TYPE,    /*字段类型*/
       DATA_LENGTH,   /*字段长度*/
       DATA_PRECISION,  /*number类型有效位数*/
       DATA_SCALE     /*number类型小数点后位数*/
  FROM ALL_TAB_COLS
 WHERE TABLE_NAME = '表名';

5）复制表的内容到另外一个新建的表

insert 表名1(select * from 表名2);

6）模糊查询 

select 字段1，字段2 from 表名
where 字段 like '%字母'

 -- 一个下划线（_）只匹配一个字符
select 字段1，字段2 from 表名
where 字段 like 'S___l'

 13、操作符

1）Union操作符的使用

 

2）intersect操作符的使用

3）minus 操作符

返回仅由第一个查询选定并且没有被第二个查询选定的所有不同行
差集运算：找两个查询结果的差集:包括1不包括2

eg：

select orderno from order_detail
minus
select orderno from order_master;

 4）连接符

外连接符(+)，表示如果连接条件不满足，则返回NULL值。

14、关键字

1）as关键字

在SQL语句中，AS关键字用于为查询结果中的列或表达式指定别名。它可以用于以下几个方面：

a. 列别名：AS关键字可以在SELECT语句中为列指定别名，使查询结果中的列具有更具描述性的名称。例如：

 SELECT column_name AS alias_name
   FROM table_name;

   这样查询结果中的列将以alias_name作为列名显示。

b. 表别名：AS关键字可以在FROM子句中为表指定别名，使查询语句更简洁。例如：

  SELECT column_name
   FROM table_name AS alias_name;

 这样在查询语句中可以使用alias_name代替table_name来引用表。

 别名A是表ORDERS的别名,不是临时表TEMP的别名。

CREATE TABLE TEMP AS
SELECT A.ID
FROM ORDERS A WHERE A.USER_ID='1'
AND A.PRODUCT IS NOT NULL;

c. 表达式别名：AS关键字可以在SELECT语句中为表达式指定别名，使查询结果中的表达式具有更具描述性的名称。例如：

 SELECT column_name + 1 AS alias_name
   FROM table_name;

   这样查询结果中的表达式将以alias_name作为列名显示。

整个子语句的结果被赋予别名b。

(Select * from ydzw.chenlh_jttf_sjzc Where end_date>to_char(sysdate-1,'yyyymmdd')) b

总之，AS关键字在SQL语句中用于为列、表或表达式指定别名，以提高查询结果的可读性和可理解性。