第4章 SQL基础

时间：2021-6-19 作者：qvyue

1. SQL

SQL (结构化査询语言)的主要功能就是在各种数据库间建立联系，进行沟通。

1.1 什么是SQL

美国国家标准学会(American National Standards Institute)成立于1918年，SQL就是美国国家标准学会确定的。

SQL主要用于存取数据以及査询、更新和管理关系数据库系统。SQL语 言自IBM公司在1981年推出以后，由于其语法结构简洁又简单易学，在数据库中得到广泛的 应用。

现行的所有数据库几乎都支持SQL语言，如 SQL Server, Oracle以及Visual FoxPro等数据库。但是这些数据库各自又对SQL语言进行了改进，如SQL Server数据库使用 Transact-SQL语言。我们学习的Oracle数据库使用的是PL/SQL语言。

1.2 了解SQL的种类

SQL语言本身可以分成4类，即：

  • 定义要在数据库存储哪些信息的数据定义语言(DDL)

  • 对数据库中的表进行操作的数据操纵语言（DML）

  • 对数据库中的表进行检索的数据査询语言（DQL）

  • 对数据库中对象进行权限管理的数据控制语言（DCL）

2. Oracle 11g常用数据类型

数据类型是在向数据表中存储数据前必须设定好的，就像如果要使用记事本査看文件内容， 那么文件就要是文本的，不能有图片，否则图片是査看不了的，因为记事本中只能査看文本文件。

把数据类型分为字符型、数字型、日期类型和其他数据类型4类进行讲解。

2.1 字符型

字符型在Oracle 11g中有varchar2, char, nchar, nvarchar2和long五种，它们在数据库中 是以ASCII码的格式存储的。

数据类型 取值范围（字节） 说明
varchar2 0-4000 可变长的字符串
nvarchar2 0-1000 存储Unicode字符集的变长字符型数据
char 0-2000 描述定长的字符型数据
nchar 0-1000 存储Unicode字符集的定长字符型数据
long 0-2GB 存储变长的字符串

在Oracle中long类型很少使用，最常使用的字符数据类型就是varchar2

2.2 数字型

数字型在Oracle 11g中常用的有number和float类型两种，可以用它们来表示整数和小数。

数据类型 取值范围 说明
number(p,s) 1-38（十进制） p代表精度，s代表的是保留的小数位数；可以存储定长的整数和小数。
float 1-126（二进制） 存储的精度时按二进制计算的，精度范围为二进制的1-126，在转化为十进制时需要乘以0.30103

2.3 日期类型

日期类型在Oracle 11g中常用的有date和timestamp两种类型。

数据类型 说明
date 存储日期和时间，精确到秒
timestamp 存储日期和时间，能精确到小数秒，还能显示上午还是下午

2.4 其他类型

除了上面讲过的字符型、数字型、日期类型之外，在Oracle 11g中还有存放大数据的数据类型以及存放二进制文件的数据类型。

数据类型 取值范围（字节） 说明
blob 最多存放4GB 存储二进制数据
clob 最多存放4GB 存储字符串数据
bfile 大小与操作系统有关 用来把非结构化的二进制数据存储到数据库以外的操作系统文件中

3. 数据定义语言(DDL)

数据定义语言(Data Definition Language ，DDL)，主要包括数据库对象的创建（create）、删除（drop）和修改（alter）的操作。

3.1 使用Create语句创建表

要使用数据库要用到的就是创建表，创建表使用create table语句完成。 具体语法如下：

create table 表名(
    列名 数据类型 [null | not null],
  列名 数据类型 [null | not null],
  ...
  [constraint]
)

说明：

  • 表名：一个数据库中，表名是不能重复的。
  • 列名：一个数据表中，列名是不能重复的。
  • [null | not null]：允许该列为空或者不为空，在创建表时主键默认为null，其他字段默认为not null。
  • [constraint]：为表中的列设置约束，约束包括主键约束、外键约束、检查约束等。

🌰：以 scott 身份登录，创建下表：

字段名 注释 数据类型
ProductId 商品编号 varchar2(10)
ProductName 商品名称 varchar2(20)
ProductPrice 商品价格 number(8,2)
Quantity 商品数量 number(10)
Category 商品类型 varchar2(10)
Desperation 商品描述 varchar2(1000)
Origin 产地 varchar2(10)

SQL语句：

create table productInfo(
    ProductId varchar2(10),
    ProductName varchar2(20),
    ProductPrice number(8,2),
  Quantity number(10),
  Category varchar2(10),
  Desperation varchar2(1000),
  Origin varchar2(10)
);

3.2 使用Alter语句修改表

如果要对已经创建好的表进行修改，那么就需要使用alter table语句来修改。

alter table 表名
add 列名 | modify 列名 | drop column 列名;

说明：

  • add：向表中添加列。
  • modify：修改表中已经存在的列。
  • drop column：删除表中的列。在删除表中的列时经常要加上cascade constraints，是要把与该列有关的约束也一并删除。

🌰：修改ProductInfo，向表中增加一列remark（备注信息），字段类型是varchar2

alter table productInfo
add remark varchar2(200);

如果登录的是sys用户，需要在表名前面加上scott，如果是scott用户，就不用加scott了。

🌰：修改productinfo商品信息表，修改刚添加的remark列的字段类型为number类型。

alter table productInfo
modify remark number(2,2);

🌰：删除productinfo商品信息表中的remark字段。

alter table productInfo
drop column remark;

🌰：修改 productinfo 表中的 ProductName 字 段，把字段的长度修改成25，并添加一个字段remark.

alter table productInfo
modify ProductName varchar2(25)
add remark varchar(200);

3.3 使用Drop语句删除表

语法：

drop table 表名;

🌰：删除 productinfo 表

drop table productinfo;

4. 约束的使用

约束是保证数据库表中数据的完整性和一致性的手段，包括主键约束、外键约束.唯一约束、检査约束、非空约束。

4.1 主键约束

主键约束在每一个数据表中只有一个，但是一个主键约束诃以由数据表中多个列组成。

(1) 创建表时加主键

在创建表时就创建主键约束，只需要使用**primary key** (字段名)即可完成。

🌰：创建categoryInfo表，并设置主键。

字段名 注释 数据类型
CategoryId 商品类型编号 varchar2(10)
CategoryName 商品类型名称 varchar2(30)

SQL语句：

create table categoryInfo(  CategoryId varchar(10),  CategoryName varchar(30),  primary key(CategoryId));

(2) 查询表的主键约束

select CONSTRAINT_NAME from user_constraints where table_name = 'CATEGORYINFO' and constraint_type ='P';

注意：表名必须大写，否则查不出来。

(3) 移除主键

语法：

alter table 表名drop constraints 约束名;

(4) 使用Alter table语句添加主键

语法：

alter table 表名add constraints 约束名 primary key(列名);

🌰：为categoryInfo表添加主键

alter table categoryInfoadd constraints pk_categoty primary key(CategoryId);

4.2 外键约束

外键约束可以保证使用外键约束的数据库列与所引用的主键约束的数据列一致，外键约束在一个数据表中可以有多个。

(1) 创建表时加外键

语法：

constraints 约束名 foreign key(列名)reference 表名(列名)on delete cascade;

说明：

on delete cascade：设置级联删除，当主键的字段被删除时，外键对应的字段也同时被删除。

🌰：创建商品信息表（productInfo），有一个字段是category，为其设置外键约束，让其关联商品类型表(categoryInfo)

create table productInfo(   productId varchar2(10),  productName varchar2(20),  productPrice number(8,2),  category varchar2(10),  primary key(productId),  constraints fk_pro_cate foreign key(category)  references categoryInfo(CategoryId)  on delete cascade);

这样，在商品信息表productinfo的category字段中的数据就必须是商品类型信息表中已经存在的商品类型，也就保证了商品类型信息的一致性。

(2) 查询主外键信息

select zi.table_name 子表名,zi.column_name 子表列,fu.table_name 父表名,fu.column_name 父表列,zi.constraint_name 外键名from  user_cons_columns zijoin user_constraints b on zi.constraint_name=b.constraint_name and zi.table_name=b.table_namejoin user_cons_columns fu on b.r_constraint_name = fu.constraint_namewhere b.constraint_type = 'R' and fu.table_name= '表名';

注意：表名需要全部大写。

(3) 移除外键约束

移除外键约束需要在子表上进行操作，语法为：

alter table productInfodrop constraints 约束名;

(4) 使用Alter table语句添加外键

语法：

add constraints 约束名 foreign key(列名)references 表名(列名)on delete cascade;

🌰：为商品信息表（productInfo）的字段category设置外键约束，关联商品类型表(categoryInfo)

alter table productInfoadd constraints fk_pro_cate foreign key(category)references categoryInfo(CategoryId)on delete cascade;

4.3 检查约束

检査约束也叫check约束，能够规定每一个列能够输入的值，以保证数据的正确性。

(1) 建表时添加check约束

语法：

constraint 约束名 check(条件);

🌰：创建顾客信息表（custominfo），要求年龄只能输入18-50岁。

字段名 注释 数据类型
CustomId 顾客编号 varchar2(10)
Name 顾客姓名 varchar2(10)
Age 顾客年龄 number(2)
Gender 顾客性别 varchar2(2)
Tel 顾客电话 varchar2(11)
Address 顾客地址 varchar2(100)

sql语句：

create table custominfo(    CustomId varchar2(10),  Name varchar2(10),  Age number(2),  Gender varchar2(2),  Tel varchar2(11),  Address varchar2(100),  constraint chk_age check(Age >= 18  and Age

(2) 使用Alter table添加检查约束

语法：

add constraint 约束名 check(条件);

🌰：修改顾客信息表（custominfo），要求性别只能输入男或者女

alter table custominfoadd constraint chk_gender check(Gender='男' or Gender='女');

(3) 查询检查约束

select * from user_constraints where table_name='表名';

注意：

  • 表名需要全部大写。

  • 此语句能查看表的所有约束。

(4) 移除检查约束

alter table custominfo drop constraint chk_gender;

4.4 唯一约束

唯一约束也称unique约束，可以设置在表中输入的字段值都是唯一的，这个约束和之前 学习的主键约束非常相似。不同的就是唯一约束在一个表中可以有多个，而主键约束在一个表 中只能有一个。

(1) 建表时加唯一约束

语法：

constraint 约束名 unique(列名);

🌰：创建订单信息表（orderinfo），设置订单编号唯一。

字段名 注释 数据类型
OrderId 订单编号 varchar2(10)
CustomId 顾客编号 varchar2(10)
ProductId 商品编号 varchar2(10)
OrderDate 订单日期 varchar2(10)
OrderQuantity 订单数量 number(10)
SendDate 发货日期 varchar2(10)

sql语句：

create table orderinfo( OrderId varchar2(10),  CustomId varchar2(10),  ProductId varchar2(10),  OrderDate varchar2(10),  OrderQuantity number(10),  SendDate varchar2(10),  constraint uhq_orderId unique(OrderId));

(2) 查询唯一约束

select * from user_constraints where table_name='表名';

注意：表名需要全部大写。

(3) 使用Alter table添加唯一约束

为订单信息表（orderinfo）的顾客编号（CustomId）字段添加唯一约束：

alter table orderinfoadd constraint uhk_CustomId unique(CustomId);

(4) 移除唯一约束

移除订单信息表（orderinfo）的顾客编号（CustomId）字段的唯一约束：

alter table orderinfodrop constraint uhk_CustomId;

4.5 非空约束

非空约束就是not null，经常会在创建表时添加非空约束以确保字段必须要输入值。

(1) 建表时创建非空约束

创建管理员信息表（Managerinfo），将LoginName和Password设置为非空。

字段名 注释 数据类型
ManagerId 管理员编号 varchar2(10)
LoginName 管理员注册名 varchar2(10)
Password 管理员密码 varchar2(10)
Name 管理员真实姓名 varchar2(10)
Tel 管理员联系方式 varchar2(11)

SQL语句：

create table Managerinfo(   ManagerId varchar2(10),  LoginName varchar2(10) not null,  Password varchar2(10) not null,  Name varchar2(10),  Tel varchar2(11));

(2) 使用Alter table添加非空约束

语法：

alter table 表名modify 列名 not null;

🌰：修改管理员信息表（Managerinfo），将Name字段设置为非空

alter table Managerinfomodify Name not null;

(3) 查看非空约束

desc 表名;

(4) 移除非空约束

只需要modify的时候将not null改为null即可。

alter table 表名modify 列名 null;

5. 数据操纵语言(DML)

DML也就是用来操纵数据库中数据所使用的语言，对数据库中的数据操纵无非就是对数 据进行增加、删除、修改、査询的操作。

5.1 添加数据Insert

(1) 直接插入数据

语法：

insert into 表名(列名,列名..) values(值,值...);

🌰：向管理员信息表(Managerinfo)中添加一条数据

insert into Managerinfo(ManagerId,LoginName,Password,Name,Tel) values('1','admin','123456','Rose','13112345678');

(2) 通过其他数据表向表中插入数据：

insert int table_name1(column_name1,column_name2...)select column_name1,column_name2... from table_name1;

注意：

在使用其他表向目标表中插入数据时，一定要保证两个表的列的个数和列的数据类型都一直，否则会出现错误。

🌰：从管理员信息表中把数据添加到账号信息表中

新建账号信息表（LoginInfo）：

字段名 注释 数据类型
LoginName 管理员注册名 varchar2(10)
LoginPassword 管理员密码 varchar2(10)

SQL语句：

create table LoginInfo( LoginName varchar2(10),  LoginPassword varchar2(10));

从Managerinfo表中查出用户名、密码两个字段的值，并插入到LoginInfo表中：

insert into LoginInfo(LoginName,LoginPassword)select loginName,Password from Managerinfo;

5.2 修改数据Update

语法：

update 表名set colunn_nane1=data1,column_nane2=data2 where 条件;

注意：

where条件可以省略，如果省略了，那么就意味着要修改表中该字段的所有值，如果加上WHERE语句，那么就可以有选择地修改数据表中的某个字段。

(1) 修改表中指定字段的全部值

🌰：将Managerinfo表中的LoginName字段全部改成”test“

update Managerinfoset LoginName='test';

(2) 根据条件修改表中指定字段的值

根据条件修改表中的数据可使用WHERE子句来完成。

🌰：将Managerinfo表中，Rose用户的密码改成“654321”

update Managerinfoset Password='654321'where Name='Rose';

5.3 删除数据Delete

语法如下：

DELETE FROM table_name ［WHERE condition］

其中，［WHERE condition］子句是可以省略的，如果省略了［WHERE condition］子句，就意 味着删除数据表中全部的数据；如果加上了［WHERE condition］子句就可以根据条件删除表中的数据。这里，删除数据都是指別除数据表中一条记录并不是删除表中某个字段。

(1) 根据条件删除表中的数据

🌰：删除Managerinfo表中Name为Rose的记录

delete from Managerinfowhere Name = 'Rose';

(2) 删除表中全部记录

🌰：删除Managerinfo中全部记录

delete from Managerinfo;

5.4 其他数据操纵语句

在Oracle中除了上面所讲述的INSERT、UPDATE, DELETE, SELECT语句之外，还有MERGE、TRUNCATE、LOCK TABLE等语句。

(1) TRUNCATE语句

TRUNCATE语句和DELETE语句一样都是用来完成删除数据表中数据的，但是二者是有区别的。

  • TRUNCATE语句删除表中的全部记录，DELETE可以删除全部，也可以删除指定数据。
  • TRUNCATE语句删除表中数据的速度要比使用DELETE语句删除表中的数据更快一点。

语法：

truncate table 表名;

(2) Merge语句

MERGE语句与UPDATE语句的功能类似，都是修改数据表中数据的，但是MERGE语句可以对数据表同时进行增加 和修改的操作。

merge [into] table_name1    using table_name2   on (condition)  when matched then merge_update_clause   when not matched then merge_insert_clause;

说明：

  • table_name1：要修改的表。
  • table_name2：参照更新的表。
  • condition：table_name1和table_name2之间的关系，或其他一些条件。
  • merge_update_clause：如果和参照表table_name2的条件匹配，执行更新操作
  • merge_insert_clause：如果条件不匹配，就执行增加操作的SQL语句。

merge_update_clause和merge_insert_clause都是可以省略的，但是在操作时只能省略一个，如果两个都省略，那么Merge语句就失去意义了。

① 省略增加的语句

创建Login表，并添加数据：

create table Login( Login_Id varchar2(10),  LoginName varchar2(10),   Password varchar2(10));insert into Login(Login_Id,LoginName,Password) values('1','aaa','123');insert into Login(Login_Id,LoginName,Password) values('2','bbb','456');insert into Login(Login_Id,LoginName,Password) values('3','ccc','789');

向Managerinfo表中插入一条数据：

insert into Managerinfo(ManagerId,LoginName,Password,Name,Tel) values('1','admin','123456','Rose','13112345678');

首先分别査询Login与Managerinfo表中的全部数据，然后使用MERGE语句进行更新操作。更新的条件是两个表的编号列相同，当满足条件时把Login表中满足条件记录的LoginName列改写成Managerinfo表中的LoginName列。

SQL> select * from Login;LOGIN_ID   LOGINNAME  PASSWORD---------- ---------- ----------1          aaa        1232          bbb        4563          ccc        789SQL> select * from Managerinfo;MANAGERID  LOGINNAME  PASSWORD   NAME       TEL---------- ---------- ---------- ---------- -----------1          admin      123456     Rose       13112345678SQL> merge into Login  2     using Managerinfo  3     on (Login.LOGIN_ID=Managerinfo.MANAGERID)  4     when matched then update  5     set Login.LOGINNAME=Managerinfo.LOGINNAME;1 行已合并。SQL> select * from  Login;LOGIN_ID   LOGINNAME  PASSWORD---------- ---------- ----------1          admin      1232          bbb        4563          ccc        789

发现原来的aaa已经变成了admin。

② 省略修改的语句

首先分别査询Login与Managerinfo表中的全部数据，然后使用MERGE语句进行增加操作。増加的条件是两个表的编号列不相同，当满足条件时向Login表中增加一条在 Managerinfo中存在的数据。

SQL> select * from  Login;LOGIN_ID   LOGINNAME  PASSWORD---------- ---------- ----------1          admin      1232          bbb        4563          ccc        789SQL> select * from  Managerinfo;MANAGERID  LOGINNAME  PASSWORD   NAME       TEL---------- ---------- ---------- ---------- -----------4          韩梅梅     123456     hanmeimei  131123456785          李雷       12345      lilei      131123456781          admin      123456     Rose       13112345678SQL> merge into Login  2  using Managerinfo  3  on(Login.LOGINNAME=Managerinfo.LOGINNAME)  4  when not matched then insert  5  values(Managerinfo.MANAGERID,Managerinfo.LOGINNAME,Managerinfo.PASSWORD);2 行已合并。SQL> select * from Login;LOGIN_ID   LOGINNAME  PASSWORD---------- ---------- ----------1          admin      1232          bbb        4563          ccc        7894          韩梅梅     1234565          李雷       12345

(3) 增加和修改同时进行

増加和修改同时进行是指当on后面的条件满足时执行修改的操作，不满足时执行增加的操作。首先也是分别査询Login与Managerinfo表中的全部数据，然后使用MERGE语句进行增加和修改的操作。

merge into Login     on(Login.LOGINNAME=Managerinfo.LOGINNAME)   when matched then update    set Login.LOGINNAME=Managerinfo.LOGINNAME   when not matched then insert   values(Managerinfo.MANAGERID,Managerinfo.LOGINNAME,Managerinfo.PASSWORD);

习题

  1. 语言中一共有几种语言？

  2. Oracle 11g中有哪些比较常用的数据类型？ varchar2是什么类型？为什么要加上2呢？

  3. 在Oracle中一共存在几种约束？能够确保字段输入的值是“18-30”的约束是下列（）约束？

    A. Primary Key B. CHECK C. UNIQUE

  4. 修改数据表时如果要添加一个约束，使用的语句是什么？

  5. 向数据表中增加数据的方法有几种？分别都是什么？

  6. DELETE与TRUNCATE语句的区别是什么？

声明：本文内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：qvyue@qq.com 进行举报，并提供相关证据，工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

今日推荐

Python修改Excel

import openpyxl import json wb = openpyxl.load_workbook […]