SQL语言基础及应用实例讲解

数据库

一. SQL语言概述

SQL（结构化查询语言）是关系型数据库语言，用于建立、存储、修改、检索和管理关系数据库中的数据。

PL/SQL与SQL的关系：

l PL/SQL是Oracle对关系数据库语言SQL的扩充，它将SQL的数据操纵功能与过程化语言数据处理功能相结合。

l PL/SQL是Oracle数据库应用的重要开发工具。

二．SQL语言的组成

数据定义语言DDL ：CREATE、ALTER、DROP

数据操纵语言DML：INSERT、DELETE、UPDATE、SELECT

事物控制语句TCL：COMMIT、ROLLBACK、SAVEPOINT（部分回滚）

数据控制语言DCL：GRANT、REVOKE

三．数据类型

常用数据类型：

1.字符串

CHAR 固变 默认1字节，最大2000字节

VARCHAR2 可变长度 默认0字节，最大4000字节

NVARCHAR2 可变长度 默认0节字，最大4000字节

VARCHAR2和NVARCHAR2的区别：

1.NVARCHAR2的性能更强;

2.varchar2(字节数)，nvarchar2(字符数)

LONG 可变长度 最大为2GB

2.数值类型

NUMBER(p, s) 长度1-22字节

示例：

NUMBER 默认为整型

NUMBER(5) 长度为5的整数，如：12345

NUMBER(5,1) 长度为5，且有1位小数位，如：1234.5

3.日期类型

DATE 保存日期和时间

TIMESTAMP 除保存日期和时间外，还可保存小数秒，小数位可指定为0-9，默认6位

CREATE TABLE tb_date(

D1 DATE,

D2 TIMESTAMP(9)

);

插入日期类型数据

方式1：INSERT INTO tb_date(D1, D2)

VALUES( DATE‘2018-2-16’, TIMESTAMP '2018-2-16 13:24:52.123456789');

方式2：INSERT INTO tb_date(D1, D2)

VALUES( TO_DATE(‘2018-2-16 10:20:30’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP( '2018-2-16 13:24:52.123456', ‘YYYY-MM-DD HH24:MI:SS.FF6’));

4. LOB大对象类型

BLOB 存二进制对象（图片、视频等）

CLOB 存字符格式大对象

提示：在应用开发中，我们将图片、视频、音乐等地址（字符串，如：E:\abc\110.jpg）存在数据库表中，根据地址找到对应的图片、视频、音乐。

5. 数据完整性（约束）

数据完整性要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。

为实现数据完整性，数据库需要做两方面的工作：

第一：确保每行的数据符合要求

第二：确保每列的数据符合要求

五大约束:

主键约束：primary key 外键约束：foreign key 唯一约束：unique

空值约束：null 检查约束：check 默认约束：default

四.创建表结构

CREATE TABLE 表名(

列名 数据类型 完整性约束，

……

);

案例：列级定义

CREATE TABLE t_class(

class_id NUMBER CONSTRAINT pk_class_id PRIMARY KEY, --为class_id添加主键约束

cname VARCHAR2(20) CONSTRAINT uq_cname UNIQUE --为cname添加唯一约束

)

案例：表级定义

CREATE TABLE tb_student(

stu_id NUMBER(5),

stu_name VARCHAR2(10) NOT NULL, --空值约束

u_sex VARCHAR2(6),

bitrhday DATE DEFAULT SYSDATE, --默认约束：系统时间（system）

class_id NUMBER(3),

CONSTRAINT pk_stu_id PRIMARY KEY(stu_id), --主键约束

CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES t_class(class_id), --外键约束

CONSTRAINT sex_chk CHECK(sex IN ('男','女')) --检查约束

);

注意：空值约束和默认无论是列级约束还是表级约束都打在列后面，主键约束，外键约束，唯一约束，检查约束可以用列级定义也可以用表级定义。

一、修改表结构

1. 修改约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明

案例：

ALTER TABLE t_student

ADD CONSTRAINT uq_username UNIQUE(stuname) --创建唯一约束

ADD CONSTRAINT pk_uid PRIMARY KEY(stuID) --创建主键约束

ADD CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES t_class(cid) --创建外键约束

ADD CONSTRAINT ck_age CHECK(age>0 AND age<150) --创建检查约束

----默认值约束

ALTER TABLE tb_student

MODIFY birthday DEFAULT SYSDATE

2. 删除约束

删除T_STUDENT表中的pk_uid约束。

ALTER TABLE t_student

DROP CONSTRAINT pk_uid

3. 查看约束

查看T_STUDENT表中所有约束。注意：表名必须全部大写

SELECT * FROM user_constraints

WHERE table_name = ‘T_STUDENT’

4..使用ALTER TABLE命令增加新字段

ALTER TABLE <表名>

ADD 列名 类型 约束

5.使用ALTER TABLE 命令删除字段

ALTER TABLE <表名>

DROP COLUMN 列名

 

6.用ALTER TABLE命令修改字段

ALTER TABLE <表名>

MODIFY 列名 类型(修改后的) 约束(修改后的)

oracle修改字段名，

alter table [表名] rename column 旧的字段名 to 新的字段名;

 

7.重命名基本表

RENAME <原表名> TO <目标表名>

或者：

ALTER TABLE <原表名>

RENAME TO <目标表名>

 

8. 追加约束

追加一个表的主键约束：

ALTER TABLE <表名>

ADD CONSTRAINT <主键名> PRIMARY KEY(列名)

 

追加一个表的唯一约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> UNIQUE(列名)

 

追加一个表的检查约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> CHECK(表达式)

 

追加一个表的外键约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名>

FOREIGN KEY(列名) REFERENCES 主表名(列名)

二、删除表

DROP TABLE 表名

TRUNCATE TABLE 表名truncate

注意：该命令执行后，目标表即从数据库中被移除到回收站中，

基于该表的索引，触发器等数据对象也同时被删除。

三、复制表

1. 复制表结构及数据

--将emp表中的数据插入到tb_new表中（tb_new表要先创建，而且表结构与SELECT的结果集相同）

INSERT INTO tb_new

SELECT * FROM emp;

--复制emp表

CREATE TABLE tb_new

AS SELECT * FROM emp;

--复制emp表的结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

2. 复制表结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

四、设置多用户访问同一张表

建一张表，用管理员给多个用户授予访问权限

数据库

一. SQL语言概述

SQL（结构化查询语言）是关系型数据库语言，用于建立、存储、修改、检索和管理关系数据库中的数据。

PL/SQL与SQL的关系：

l PL/SQL是Oracle对关系数据库语言SQL的扩充，它将SQL的数据操纵功能与过程化语言数据处理功能相结合。

l PL/SQL是Oracle数据库应用的重要开发工具。

二．SQL语言的组成

数据定义语言DDL ：CREATE、ALTER、DROP

数据操纵语言DML：INSERT、DELETE、UPDATE、SELECT

事物控制语句TCL：COMMIT、ROLLBACK、SAVEPOINT（部分回滚）

数据控制语言DCL：GRANT、REVOKE

三．数据类型

常用数据类型：

1.字符串

CHAR 固变 默认1字节，最大2000字节

VARCHAR2 可变长度 默认0字节，最大4000字节

NVARCHAR2 可变长度 默认0节字，最大4000字节

VARCHAR2和NVARCHAR2的区别：

1.NVARCHAR2的性能更强;

2.varchar2(字节数)，nvarchar2(字符数)

LONG 可变长度 最大为2GB

2.数值类型

NUMBER(p, s) 长度1-22字节

示例：

NUMBER 默认为整型

NUMBER(5) 长度为5的整数，如：12345

NUMBER(5,1) 长度为5，且有1位小数位，如：1234.5

3.日期类型

DATE 保存日期和时间

TIMESTAMP 除保存日期和时间外，还可保存小数秒，小数位可指定为0-9，默认6位

CREATE TABLE tb_date(

D1 DATE,

D2 TIMESTAMP(9)

);

插入日期类型数据

方式1：INSERT INTO tb_date(D1, D2)

VALUES( DATE‘2018-2-16’, TIMESTAMP '2018-2-16 13:24:52.123456789');

方式2：INSERT INTO tb_date(D1, D2)

VALUES( TO_DATE(‘2018-2-16 10:20:30’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP( '2018-2-16 13:24:52.123456', ‘YYYY-MM-DD HH24:MI:SS.FF6’));

4. LOB大对象类型

BLOB 存二进制对象（图片、视频等）

CLOB 存字符格式大对象

提示：在应用开发中，我们将图片、视频、音乐等地址（字符串，如：E:\abc\110.jpg）存在数据库表中，根据地址找到对应的图片、视频、音乐。

5. 数据完整性（约束）

数据完整性要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。

为实现数据完整性，数据库需要做两方面的工作：

第一：确保每行的数据符合要求

第二：确保每列的数据符合要求

五大约束:

主键约束：primary key 外键约束：foreign key 唯一约束：unique

空值约束：null 检查约束：check 默认约束：default

四.创建表结构

CREATE TABLE 表名(

列名 数据类型 完整性约束，

……

);

案例：列级定义

CREATE TABLE t_class(

class_id NUMBER CONSTRAINT pk_class_id PRIMARY KEY, --为class_id添加主键约束

cname VARCHAR2(20) CONSTRAINT uq_cname UNIQUE --为cname添加唯一约束

)

案例：表级定义

CREATE TABLE tb_student(

stu_id NUMBER(5),

stu_name VARCHAR2(10) NOT NULL, --空值约束

u_sex VARCHAR2(6),

bitrhday DATE DEFAULT SYSDATE, --默认约束：系统时间（system）

class_id NUMBER(3),

CONSTRAINT pk_stu_id PRIMARY KEY(stu_id), --主键约束

CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES t_class(class_id), --外键约束

CONSTRAINT sex_chk CHECK(sex IN ('男','女')) --检查约束

);

注意：空值约束和默认无论是列级约束还是表级约束都打在列后面，主键约束，外键约束，唯一约束，检查约束可以用列级定义也可以用表级定义。

一、修改表结构

1. 修改约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明

案例：

ALTER TABLE t_student

ADD CONSTRAINT uq_username UNIQUE(stuname) --创建唯一约束

ADD CONSTRAINT pk_uid PRIMARY KEY(stuID) --创建主键约束

ADD CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES t_class(cid) --创建外键约束

ADD CONSTRAINT ck_age CHECK(age>0 AND age<150) --创建检查约束

----默认值约束

ALTER TABLE tb_student

MODIFY birthday DEFAULT SYSDATE

2. 删除约束

删除T_STUDENT表中的pk_uid约束。

ALTER TABLE t_student

DROP CONSTRAINT pk_uid

3. 查看约束

查看T_STUDENT表中所有约束。注意：表名必须全部大写

SELECT * FROM user_constraints

WHERE table_name = ‘T_STUDENT’

4..使用ALTER TABLE命令增加新字段

ALTER TABLE <表名>

ADD 列名 类型 约束

5.使用ALTER TABLE 命令删除字段

ALTER TABLE <表名>

DROP COLUMN 列名

 

6.用ALTER TABLE命令修改字段

ALTER TABLE <表名>

MODIFY 列名 类型(修改后的) 约束(修改后的)

oracle修改字段名，

alter table [表名] rename column 旧的字段名 to 新的字段名;

 

7.重命名基本表

RENAME <原表名> TO <目标表名>

或者：

ALTER TABLE <原表名>

RENAME TO <目标表名>

 

8. 追加约束

追加一个表的主键约束：

ALTER TABLE <表名>

ADD CONSTRAINT <主键名> PRIMARY KEY(列名)

 

追加一个表的唯一约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> UNIQUE(列名)

 

追加一个表的检查约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> CHECK(表达式)

 

追加一个表的外键约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名>

FOREIGN KEY(列名) REFERENCES 主表名(列名)

二、删除表

DROP TABLE 表名

TRUNCATE TABLE 表名truncate

注意：该命令执行后，目标表即从数据库中被移除到回收站中，

基于该表的索引，触发器等数据对象也同时被删除。

三、复制表

1. 复制表结构及数据

--将emp表中的数据插入到tb_new表中（tb_new表要先创建，而且表结构与SELECT的结果集相同）

INSERT INTO tb_new

SELECT * FROM emp;

--复制emp表

CREATE TABLE tb_new

AS SELECT * FROM emp;

--复制emp表的结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

2. 复制表结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

四、设置多用户访问同一张表

建一张表，用管理员给多个用户授予访问权限

数据库

一. SQL语言概述

SQL（结构化查询语言）是关系型数据库语言，用于建立、存储、修改、检索和管理关系数据库中的数据。

PL/SQL与SQL的关系：

l PL/SQL是Oracle对关系数据库语言SQL的扩充，它将SQL的数据操纵功能与过程化语言数据处理功能相结合。

l PL/SQL是Oracle数据库应用的重要开发工具。

二．SQL语言的组成

数据定义语言DDL ：CREATE、ALTER、DROP

数据操纵语言DML：INSERT、DELETE、UPDATE、SELECT

事物控制语句TCL：COMMIT、ROLLBACK、SAVEPOINT（部分回滚）

数据控制语言DCL：GRANT、REVOKE

三．数据类型

常用数据类型：

1.字符串

CHAR 固变 默认1字节，最大2000字节

VARCHAR2 可变长度 默认0字节，最大4000字节

NVARCHAR2 可变长度 默认0节字，最大4000字节

VARCHAR2和NVARCHAR2的区别：

1.NVARCHAR2的性能更强;

2.varchar2(字节数)，nvarchar2(字符数)

LONG 可变长度 最大为2GB

2.数值类型

NUMBER(p, s) 长度1-22字节

示例：

NUMBER 默认为整型

NUMBER(5) 长度为5的整数，如：12345

NUMBER(5,1) 长度为5，且有1位小数位，如：1234.5

3.日期类型

DATE 保存日期和时间

TIMESTAMP 除保存日期和时间外，还可保存小数秒，小数位可指定为0-9，默认6位

CREATE TABLE tb_date(

D1 DATE,

D2 TIMESTAMP(9)

);

插入日期类型数据

方式1：INSERT INTO tb_date(D1, D2)

VALUES( DATE‘2018-2-16’, TIMESTAMP '2018-2-16 13:24:52.123456789');

方式2：INSERT INTO tb_date(D1, D2)

VALUES( TO_DATE(‘2018-2-16 10:20:30’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP( '2018-2-16 13:24:52.123456', ‘YYYY-MM-DD HH24:MI:SS.FF6’));

4. LOB大对象类型

BLOB 存二进制对象（图片、视频等）

CLOB 存字符格式大对象

提示：在应用开发中，我们将图片、视频、音乐等地址（字符串，如：E:\abc\110.jpg）存在数据库表中，根据地址找到对应的图片、视频、音乐。

5. 数据完整性（约束）

数据完整性要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。

为实现数据完整性，数据库需要做两方面的工作：

第一：确保每行的数据符合要求

第二：确保每列的数据符合要求

五大约束:

主键约束：primary key 外键约束：foreign key 唯一约束：unique

空值约束：null 检查约束：check 默认约束：default

四.创建表结构

CREATE TABLE 表名(

列名 数据类型 完整性约束，

……

);

案例：列级定义

CREATE TABLE t_class(

class_id NUMBER CONSTRAINT pk_class_id PRIMARY KEY, --为class_id添加主键约束

cname VARCHAR2(20) CONSTRAINT uq_cname UNIQUE --为cname添加唯一约束

)

案例：表级定义

CREATE TABLE tb_student(

stu_id NUMBER(5),

stu_name VARCHAR2(10) NOT NULL, --空值约束

u_sex VARCHAR2(6),

bitrhday DATE DEFAULT SYSDATE, --默认约束：系统时间（system）

class_id NUMBER(3),

CONSTRAINT pk_stu_id PRIMARY KEY(stu_id), --主键约束

CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES t_class(class_id), --外键约束

CONSTRAINT sex_chk CHECK(sex IN ('男','女')) --检查约束

);

注意：空值约束和默认无论是列级约束还是表级约束都打在列后面，主键约束，外键约束，唯一约束，检查约束可以用列级定义也可以用表级定义。

一、修改表结构

1. 修改约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明

案例：

ALTER TABLE t_student

ADD CONSTRAINT uq_username UNIQUE(stuname) --创建唯一约束

ADD CONSTRAINT pk_uid PRIMARY KEY(stuID) --创建主键约束

ADD CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES t_class(cid) --创建外键约束

ADD CONSTRAINT ck_age CHECK(age>0 AND age<150) --创建检查约束

----默认值约束

ALTER TABLE tb_student

MODIFY birthday DEFAULT SYSDATE

2. 删除约束

删除T_STUDENT表中的pk_uid约束。

ALTER TABLE t_student

DROP CONSTRAINT pk_uid

3. 查看约束

查看T_STUDENT表中所有约束。注意：表名必须全部大写

SELECT * FROM user_constraints

WHERE table_name = ‘T_STUDENT’

4..使用ALTER TABLE命令增加新字段

ALTER TABLE <表名>

ADD 列名 类型 约束

5.使用ALTER TABLE 命令删除字段

ALTER TABLE <表名>

DROP COLUMN 列名

 

6.用ALTER TABLE命令修改字段

ALTER TABLE <表名>

MODIFY 列名 类型(修改后的) 约束(修改后的)

oracle修改字段名，

alter table [表名] rename column 旧的字段名 to 新的字段名;

 

7.重命名基本表

RENAME <原表名> TO <目标表名>

或者：

ALTER TABLE <原表名>

RENAME TO <目标表名>

 

8. 追加约束

追加一个表的主键约束：

ALTER TABLE <表名>

ADD CONSTRAINT <主键名> PRIMARY KEY(列名)

 

追加一个表的唯一约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> UNIQUE(列名)

 

追加一个表的检查约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名> CHECK(表达式)

 

追加一个表的外键约束：

ALTER TABLE <表名>

ADD CONSTRAINT <约束名>

FOREIGN KEY(列名) REFERENCES 主表名(列名)

二、删除表

DROP TABLE 表名

TRUNCATE TABLE 表名truncate

注意：该命令执行后，目标表即从数据库中被移除到回收站中，

基于该表的索引，触发器等数据对象也同时被删除。

三、复制表

1. 复制表结构及数据

--将emp表中的数据插入到tb_new表中（tb_new表要先创建，而且表结构与SELECT的结果集相同）

INSERT INTO tb_new

SELECT * FROM emp;

--复制emp表

CREATE TABLE tb_new

AS SELECT * FROM emp;

--复制emp表的结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

2. 复制表结构

CREATE TABLE tb_new

AS SELECT * FROM emp WHERE 1=2;

四、设置多用户访问同一张表

建一张表，用管理员给多个用户授予访问权限