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;
四、设置多用户访问同一张表
建一张表，用管理员给多个用户授予访问权限