SQL简介

第一章：基本SQL语句和函数

掌握Oracle基本SQL语句

掌握Oracle单值、分组函数

掌握Oracle多表查询、集合运算


SQL是Structured Query Language（结构化查询语言）的首字母缩写词

SQL是数据库语言，Oracle使用该语言存储和检索信息

表是主要的数据库对象，用于存储数据，还有一种用来存放数据叫做：物化视图 Materialized view



数据定义语言（DDL）：包括了CREATE、ALTER、DROP

数据操纵语言（DML）：包括了INSERT、SELECT、DELETE、UPDATE

事务控制语言（TCL）：包括了COMMIT、SAVEPOINT、ROLLBACK

数据控制语言（DCL）：包括了GRANT、REVOKE


ORACLE数据库类型

创建表时，必须为各个列指定数据类型

以下是Oracle数据类型的类别:



以下列举每一种类型并进行说明：

字符数据类型


  • 当需要固定长度的字符串时，使用CHAR数据类型。
  • CHAR数据类型存储字母数字值。
  • CHAR数据类型的列长度可以是1到2000个字节。

Oracle中也支持nchar类型，和SqlServer中一样。


  1. VARCHAR2数据类型支持可变长度字符串
  2. VARCHAR2数据类型存储字母数字值
  3. VARCHAR2数据类型的大小在1至4000个字节范围
  4. 注意：Oracle最好用VARCHAR2, 不要用VARCHAR，原因是后续版本会祛除


  • LONG数据类型存储可变长度字符数据
  • LONG数据类型最多存储2GB

数值数据类型

数值数据类型：

   可以存储整数，浮点数和实数

   最高精度38位，范围：负的10的38次方到10的38次方

数值数据类型的声明语法：

   NUMBER[(p[,s])]

   P表示精度， S表示小数点的位数


SQL> create table test(a number(3,2));


Table created.


SQL> insert into test values(1.23);


1 row created.


SQL> select * from test;


         A

----------

      1.23


SQL> insert into test values(2.89234555);


1 row created.


SQL> select * from test;


         A

----------

      1.23

      2.89


SQL> insert into test values(3.88888889);


1 row created.


SQL> select * from test;


         A

----------

      1.23

      2.89

      3.89


SQL> insert into test values(22.345);

insert into test values(22.345)

                         *

ERROR at line 1:

ORA-01438: value larger than specified precision allowed for this column



SQL>


SQL> create table t4(a number(3));


Table created.


SQL> insert into t4 values(223.999);


1 row created.


SQL> select * from t4;


         A

----------

       224


SQL>


日期类型

日期时间数据类型存储日期和时间值，包括年、月、日。小时、分钟、秒

主要的日期时间类型有：

DATE – 存储日期和时间部分，精准到整个的秒

TIMESTAMP – 存储日期、时间和时区信息，秒值精确到小数点后6位

SQL> select sysdate from dual;


SYSDATE

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

11-JAN-22


SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;


TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')

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

2022-01-11 16:19:26


SQL> select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') from dual;


TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DDHH24:MI:SSXFF6')

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

2022-01-11 16:22:43.629012

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

# 针对SQLServer

select getdate();

RAW/LONG RAW类型

  • RAW数据类型用于存储二进制数据
  • RAW数据类型最多能存储2000字节
  • LONG RAW数据类型用于存储可变长度的二进制数据
  • LONG RAW数据类型最多能存储2GB

LOB类型

LOB称为“大对象”数据类型，可以存储多达128TB的非结构化信息，列如声音剪辑和视频文件等（LOB类型的容量从原来的4G增长到了最大128T）

LOB数据类型允许对数据进行高效、随机、分段的访问



CLOB即Character LOB（字符LOB）,它能够存储大量字符数据

BLOB即Binary LOB（二进制 LOB）,可以存储较大的二进制对象，如图形、视频剪辑和声音文件

BFILE即Binary File（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中

ROWID/ROWNUM类型

  • Oracle中伪列就像一个表列，但是它并没有存储在表中
  • 伪列可以从表中查询，但不能插入、更新和删除它们的值
  • 常用的伪列有ROWID和ROWNUM
  • ROWID是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID伪列快速地定位表中的一行
  • ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

数据库定义语言​​[s1]​​ 

  • 数据库定义语言用于改变数据库结构，包括创建、更改和删除数据库对象
  • 用于操纵表结构的数据定义语言命令有：
  • CREATE TABLE
  • ALTER TABLE
  • TRUNCATE TABLE
  • DROP TABLE

SQL> create table stu(sno number(6), sname varchar2(10), birthday date);


Table created.


SQL> desc stu;

 Name                                      Null?    Type

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

 SNO                                                NUMBER(6)

 SNAME                                              VARCHAR2(10)

 BIRTHDAY                                           DATE


SQL> alter table stu add tele varchar2(11);


Table altered.


SQL> desc stu;

 Name                                      Null?    Type

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

 SNO                                                NUMBER(6)

 SNAME                                              VARCHAR2(10)

 BIRTHDAY                                           DATE

 TELE                                               VARCHAR2(11)


SQL> alter table stu modify tele varchar2(20); //这里需要注意，从大调整到小，如果数据存在比调整的大，是调整不了的。

Table altered.


SQL> alter table stu drop column telt;

Table altered.

SQL> desc stu;

 Name                                      Null?    Type

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

 SNO                                                NUMBER(6)

 SNAME                                              VARCHAR2(11)

 BIRTHDAY                                           DATE

SQL语句

时间操作：

SQL> select sysdate from dual;


SYSDATE

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

12-JAN-22


SQL> desc stu;

 Name                                      Null?    Type

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

 SNO                                                NUMBER(6)

 SNAME                                              VARCHAR2(11)

 BIRTHDAY                                           DATE


SQL> insert into stu values(1, 'A', '09-MAY-95');


1 row created.


SQL> select * from stu;


       SNO SNAME       BIRTHDAY

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

         1 A           09-MAY-95


SQL> alter session set nls_date_format='yyyy-mm-dd'; #这里修改只对当前窗口有效，如果新开窗口依然为默认

Session altered.


SQL> select sysdate from dual;


SYSDATE

----------

2022-01-12


SQL> insert into stu values(2, 'BB', '2022-1-12');


1 row created.


SQL> select * from stu;


       SNO SNAME       BIRTHDAY

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

         1 A           1995-05-09

         2 BB          2022-01-12


空值操作：

SQL> select * from stu;


       SNO SNAME       BIRTHDAY

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

         1 A           09-MAY-95

         2 BB          12-JAN-22


SQL> insert into stu values(3, null, to_date('20220102', 'yyyymmdd'));


1 row created.


SQL> insert into stu(sno, birthday) values(4, to_date('2021-12-09', 'yyyy-mm-dd'));


1 row created.


SQL> select * from stu;


       SNO SNAME       BIRTHDAY

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

         3             02-JAN-22

         4             09-DEC-21

         1 A           09-MAY-95

         2 BB          12-JAN-22


SQL> select * from stu where sname is null;#查询空值


       SNO SNAME       BIRTHDAY

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

         3             02-JAN-22

         4             09-DEC-21

拷贝表结构以及数据：

SQL> desc stu;

 Name                                      Null?    Type

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

 SNO                                                NUMBER(6)

 SNAME                                              VARCHAR2(11)

 BIRTHDAY                                           DATE


SQL> select * from stu;


       SNO SNAME       BIRTHDAY

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

         3             02-JAN-22

         4             09-DEC-21

         1 A           09-MAY-95

         2 BB          12-JAN-22


SQL> create table stu1 as select * from stu; #拷贝一张表结构及数据到另外一张表，sqlServer实现方式：select * into stu1 from stu;


Table created.


SQL> desc stu1;

 Name                                      Null?    Type

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

 SNO                                                NUMBER(6)

 SNAME                                              VARCHAR2(11)

 BIRTHDAY                                           DATE


SQL> select * from stu1;


       SNO SNAME       BIRTHDAY

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

         3             02-JAN-22

         4             09-DEC-21

         1 A           09-MAY-95

         2 BB          12-JAN-22


SQL> create table stu2 as select * from stu where 1 > 2;#只拷贝表stu的结构到stu2，不拷贝数据


Table created.


SQL> select * from stu2;


no rows selected


SQL> desc stu2;

 Name                                      Null?    Type

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

 SNO                                                NUMBER(6)

 SNAME                                              VARCHAR2(11)

 BIRTHDAY                                           DATE

删除数据：

SQL> truncate table stu1;


Table truncated.


SQL> select * from stu1;


no rows selected

SQL> delete from stu;


4 rows deleted.

这两种方式的区别：
delete删除之后需要commit才能真正删除并且进入日志，可以根据参数从回滚段中将数据进行回滚，truncate不需要commit，并且不进入日志。删除之后无法回滚，使用truncate命令需要特别注意


SQL> select * from stu;


       SNO SNAME       BIRTHDAY

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

         1 A           09-FEB-21

         2 BB          10-JAN-22

         3             22-MAY-21

         4             18-OCT-21


SQL> insert into stu2 select * from stu;#将stu表中的数据插入到stu2中，oracle和SQLserver为同样的语句


4 rows created.


SQL> select * from stu2;


       SNO SNAME       BIRTHDAY

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

         1 A           09-FEB-21

         2 BB          10-JAN-22

         3             22-MAY-21

         4             18-OCT-21


SQL> update stu2 set sname='SS' where sno=3;


1 row updated.


SQL> delete from stu2 where sno=1;


1 row deleted.


SQL> select * from stu2;


       SNO SNAME       BIRTHDAY

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

         2 BB          10-JAN-22

         3 SS          22-MAY-21

         4             18-OCT-21


查询操作：

SQL> clear screen;

SQL> select count(*) from stu2;


  COUNT(*)

----------

         3


SQL> select * from stu2;


       SNO SNAME       BIRTHDAY

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

         2 BB          10-JAN-22

         3 SS          22-MAY-21

         4             18-OCT-21


SQL> insert into stu2 values(1, 'AAA', '19-APR-95');


1 row created.


SQL> select * from stu2;


       SNO SNAME       BIRTHDAY

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

         2 BB          10-JAN-22

         3 SS          22-MAY-21

         4             18-OCT-21

         1 AAA         19-APR-95


SQL> select count(*) from stu2;


  COUNT(*)

----------

         4


SQL> select count(*) from stu2 where sname is not null;


  COUNT(*)

----------

         3


SQL> select * from stu2 where upper(sname) = 'A';


no rows selected


SQL> select * from stu2 where sname like 'A%';


       SNO SNAME       BIRTHDAY

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

         1 AAA         19-APR-95


SQL> select * from stu2 where sname like 'A_';


no rows selected


SQL> select * from stu2 where sname like 'A__';


       SNO SNAME       BIRTHDAY

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

         1 AAA         19-APR-95


SQL> update stu2 set sname='A''B' where sno=4;


1 row updated.


SQL> select * from stu2;


       SNO SNAME       BIRTHDAY

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

         2 BB          10-JAN-22

         3 SS          22-MAY-21

         4 A'B         18-OCT-21

         1 AAA         19-APR-95


SQL> select * from stu2 where length(sname)=2;


       SNO SNAME       BIRTHDAY

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

         2 BB          10-JAN-22

         3 SS          22-MAY-21


SQL> select * from stu2 order by sno desc;


       SNO SNAME       BIRTHDAY

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

         4 A'B         18-OCT-21

         3 SS          22-MAY-21

         2 BB          10-JAN-22

         1 AAA         19-APR-95


SQL> select * from stu2 order by sno desc, sname;


       SNO SNAME       BIRTHDAY

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

         4 A'B         18-OCT-21

         3 SS          22-MAY-21

         2 BB          10-JAN-22

         1 AAA         19-APR-95


SQL> select * from stu2 order by 1 desc, 2;


       SNO SNAME       BIRTHDAY

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

         4 A'B         18-OCT-21

         3 SS          22-MAY-21

         2 BB          10-JAN-22

         1 AAA         19-APR-95


SQL> select sno 学号, sname "姓名" from stu2;


      学号 姓名

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

         2 BB

         3 SS

         4 A'B

         1 AAA


SQL> select sno "学  号", sname 姓名 from stu2;


     学 号 姓名

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

         2 BB

         3 SS

         4 A'B

         1 AAA

Deno表及数据：

create table score(sno number(11), km varchar2(11), fs number(6,3));

insert into score values(1, '语文', 60);

insert into score values(1, '数学', 60);

insert into score values(1, '英语', 60);

insert into score values(2, '语文', 70);

insert into score values(2, '数学', 70);

insert into score values(3, '语文', 80);



create table stu(sno number(6), sname varchar2(10), birthday date, male char(2), tele char(11));

insert into stu values(1, '张三', to_date('2021-01-08', 'yyyy-mm-dd'), '男', '13312342306');

insert into stu values(2, '李四', to_date('1982-01-25', 'yyyy-mm-dd'), '男', '12212343214');

insert into stu values(3, '王五', to_date('1994-06-01', 'yyyy-mm-dd'), '女', '18786133204');


create table addr(sno number(6), zz varchar2(20));

insert into addr values(1, '郑州');

insert into addr values(2, '广州');

insert into addr values(3, '东莞');

insert into addr values(4, '贵阳');

对demo表及数据的操作：

SQL> select * from score;


       SNO KM                  FS

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

         1 语文                60

         1 数学                60

         1 英语                60

         2 语文                70

         2 数学                70

         3 语文                80


6 rows selected.


SQL> select sno, sum(fs) from score group by sno;


       SNO    SUM(FS)

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

         1        180

         2        140

         3         80


SQL> select km, avg(fs) from score group by km;


KM             AVG(FS)

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

数学                65

英语                60

语文                70


SQL> select km, avg(fs) from score group by km order by 2 desc;


KM             AVG(FS)

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

语文                70

数学                65

英语                60


 ​​[s1]​​登录oracle数据库管理员：sqlplus / as sysdba;

创建用户：create user 用户名 identified by 密码

create user alpaca identified by alpaca;

授权：grant dba to 用户名

grant dba to alpaca;

退出sys用户，登录创建的用户

Sqlplus alpaca/alpaca;