SQL 语言入门

目录

• SQL 语言入门
  • 数据定义语言（DDL）和 数据操作语言和（DML）
    • DDL
      • 1.创建数据库
      • 2.创建表
        • 列的数据类型
        • 修改基本表
        • 设定列的约束
        • 实例
      • 3. 创建架构
        • 架构与表
      • 4.创建登录名、用户名、为用户名指定特定的架构、角色分配
      • 5.创建索引
        • 实例
        • 修改索引
    • DML
      • 1.数据查询 SELECT
        • 多表查询
        • 重名处理 AS
      • 2.数据插入 INSERT INTO
      • 3.数据更新 UPDATE
      • 4.数据删除 DELETE
  • 文件分类
    • 文件名

SQL 语言入门

1.独立的语言：能够独立地用于联机交互的使用方式

2.嵌入式语言：嵌入了c，c++，java语言

数据定义语言（DDL）和 数据操作语言和（DML）

DDL

• CREATE DATABASE 创建新数据库
• ALTER DATABASE 修改数据库

---

• CREATE TABLE 创建新表

• DROP TABLE 删除表

• ALTER TABLE 修改表

---

• CREATE SCHEMA 创建模式

• DROP SCHEMA 删除模式

• SQL标准不提供修改模式定义的操作，如果需要修改，只能先删除再重新创建

---

• CREATE VIEW 创建视图

• 删除视图

• SQL标准不提供修改视图定义的操作，如果需要修改，只能先删除再重新创建

---

• CREATE INDEX 创建索引（搜索键）

• DROP INDEX 删除索引

• ALTER INDEX 修改索引

---

1.创建数据库

数据库是若干个具有相互关联关系 TABLE/RELATION 的集合

数据库可以看作是一个集中存放若干TABLE的大型文件

CREATE DATABASE DB_NAME;
/* 创建学生-课程数据库 */
CREATE DATABASE student
/* 主数据文件 */
ON PRIMARY
(
NAME = 'student_data',
FILENAME = 'c:201900800149datastu_data.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 10%
)

/* 日志文件 */
LOG ON
(
NAME = 'stu_log',
FILENAME = 'c:201900800149datastu_log.ldf',
SIZE = 3MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB
);

可以使用USE来改变当前操作的数据库。

SQL中USE的用法

2.创建表

在创建一个数据库后，就可以向其中加入表了。创建表的格式如下

CREATE TABLE TABLE_NAME(COL_NAME DATA_TYPE[PRIMARY KEY|UNIQUE][NOT NULL], ...)

列的数据类型

char(n) -- 固定长度为n的字符串
varchar(n) --初始长度为n的可变长字符串
int -- 整数
numeric(p,q) -- 固定精度数字,小数点左边p位,小数点右边p-q位
real -- 浮点精度数字
date -- 日期(如2003-09-12)
time -- 时间(23:15:003)
smallint -- 短整数
bigint -- 大整数
boolean -- 逻辑布尔量

修改基本表

ALTER TABLE TABLE_Name
-- 1. ADD 用于增加新列、新的列级完整性约束条件、新的表级完整性约束条件
ADD 
NewColName integer [NOT NULL]; -- 增加新列

ADD 
CONSTRAINT
CONSTRAINT_Name CONSTRAINT_Type...
 
-- 2. DROP
DROP ColName CASCADE/RESTRICT;
DROP CPMSTRAOMT

-- 3.ALTER
ALTER ColName -- 修改列定义

设定列的约束

设定列的约束一般在向表中添加完元组之后进行

列的约束包括

约束类型	约束意义
主键约束(Primary Key Constraint):	要求主键列的数据唯一，并且不允许为空
唯一约束(Unique Constraint)	要求该列唯一，允许为空，但只能出现一个空值
检查约束(Check Constraint)	某列取值范围限制、格式限制等，如有关年龄的约束
默认约束(Default Constraint)	某列的默认值，如我们的男性同学较多，性别默认为男
外键约束(Foreign Key)	用于在两表之间建立关系需要制定引用主表的哪一列
非空约束(Not Null Constraint)	要求某列不可取空值，一般在创建表时定义

实例

创建数据库，并向其中创建若干个表，在导入数据后设定约束

/* 创建学生表,指定学号非空 */
CREATE TABLE student
(Sno varchar(9)NOT NULL,Sname char(10),Ssex char(5),Sage integer,Sdept char(10));

/* 创建课程表,指定课号非空 */
CREATE TABLE course
(Cno varchar(5)NOT NULL,Cname char(20) UNIQUE,Cpno varchar(5),Ccredit integer);

/* 创建成绩表,指定学号、课号非空 */
CREATE TABLE sc
(Sno varchar(9)NOT NULL,Cno varchar(5)NOT NULL,Grade integer);

-- 向表中添加初始数据（略）

/* 添加约束 */
ALTER TABLE course --修改course表
ADD --添加
CONSTRAINT --约束
PK_Cno PRIMARY KEY(Cno), --course表的主键为Cno

CONSTRAINT
FK_Cpno FOREIGN KEY(Cpno) REFERENCES course(Cno);--约束course表的外键Cpno为course表的候选键Cno


ALTER TABLE student --修改student表
ADD --添加 
CONSTRAINT --约束
PK_Sno PRIMARY KEY(Sno); --student表的主键为Sno


ALTER TABLE sc --修改sc表
ADD --添加
CONSTRAINT -- 约束
PK_SnoCno PRIMARY KEY (Sno,Cno), --sc表主键为(Sno,Cno)的组
CONSTRAINT 
FK_Sno FOREIGN KEY(Sno) REFERENCES student(Sno), --外码1
CONSTRAINT
FK_Cno FOREIGN KEY(Cno) REFERENCES course(Cno); -- 外码2

3. 创建架构

Schema is a way of categorising the objects in a database. It can be useful if you have several applications share a single database and while there is some common set of data that all application accesses.

在SQL中，架构的定义语句如下

CREATE SCHEMA <SCHEMA_Name> AUTHORIZATION <USER_Name>

如果没有指定<SCHEMA_Name>，那么<SCHEMA_Name>隐含为<USER_Name>

要创建一个架构，调用该命令的用户必须具有数据库管理员权限，或者获得了数据库管理员授予的CREATE SCHEMA权限。

CREATE SCHEMA "S-T" AUTHORIZATION WANG; -- 为用户WANG定义一个学生-课程架构S-T 
CREATE SCHEMA AUTHORIZATION WANG; -- 为用户WANG定义一个架构,因为没有指定架构名,因此架构名默认为WANG

定义架构实际上定义了一个命名空间，在这个空间中可以进一步定义该模式所包含的数据库对象，例如基本表、视图、索引等。

-- 为用户ZHANG创建一个模式TEST,并且在其中定义一个表TAB1
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1
(
COL1 smallint,
COL2 int,
COL3 char(20),
COL4 numeric(10,3),
COL5 decimal(5,2)
);

删除模架构

DROP SCHEMA ZHANG CASCADE;  -- 级联删除(如果在模式中定义了数据库对象,如表、视图等)
DROP SCHEMA ZHANG RESTRICT; -- 限制删除(删除模式及其下定义的数据库对象)

架构与表

每一个基本表都属于某一个架构，一个架构包含多个基本表。当定义一个基本表的时候，一般可以有三种方法来定义它所属的架构。

-- 方法1,在表名中明显地给出架构名
CREATE TABLE "S-T".Student(...)

-- 方法2,在创建架构语句的同时创建表
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(...);

-- 方法3,设置所属的架构,这样在创建表时表名中不必给出架构名
/* 当用户创建基本表时如果没有指定所属的架构,系统根据搜索路径来确定该对象所属的架构
   搜索路径包含一组架构列表,关系数据库管理系统会使用架构列表中第一个存在的架构作为数据库对象的架构名
   若搜索路径中没有架构名,则系统将给出错误
*/
SHOW search_path; -- 显示当前搜索路径
SET search_path TO "S-T",PUBLIC; -- 管理员设置搜索路径
CREATE TABLE Student(...) 

4.创建登录名、用户名、为用户名指定特定的架构、角色分配

理解服务器、数据库、架构，登录名、用户名、角色之间的关系

在创建用户名之前，必须先创建登录名，将登录名分配给用户名。

• 创建登录名( 用于访问 SQL Sever 服务器 )

-- 使用sql语句创建
CREATE login usersf with password='123'
-- 使用存储过程完成
EXEC sp_addlogin 'usersf','123'

• 创建用户名( 用于访问特定的数据库 )

一个登录名可以与多个用户名建立映射，但是前提是这些用户名所属于不同的数据库

USE student -- 当前建立用户名的数据库
-- 使用sql语句创建
CREATE user u1 for login usersf (with dafault_schema = dbo)
-- 使用存储过程完成
EXEC sp_adduser 'usersf','u1'

• 角色分配

角色是权限的集合

创建登录名、用户名仅仅提供了连接到服务器、数据库的权限，但对于数据库进行操作的权限还需要通过指定用户名的角色这一步操作来分配。

创建角色

CREATE ROLE <ROLE_Name>

为角色授权

GRANT <权限1,权限2,...>
ON <对象类型> 对象名
TO <角色1,角色2,...>

将角色授予其它角色或用户

GRANT <角色1,角色2,...>
TO <角色3> , <用户1,用户2,...>

• 指定用户架构

CREATE SCHEMA AUTHORIZATION user1; 

5.创建索引

当表的数据量比较大的时候,查询操作会比较耗时。建立索引时加快查询速度的有效手段。数据库的索引类似于图书后面的索引,能够快速定位到需要查询的内容。用户可以根据应用环境的需要在基本表上建立一个或多个索引,以提供多种存取路径,加快查找速度。

建立索引使用CREATE INDEX语句

CREATE [UNIQUE] [CLUSTER] INDEX <INDEX_Name>
ON <TABLE_Name>
(
Col1 ORDER_Type1
Col2 ORDER_Type2
Col3 ORDER_Type3
);

实例

CREAETE UNIQUE INDEX Stusno
ON Student
(
Sno
);

CREATE UNIQUE INDEX Coucno
ON Course
(
Cno
);

CREATE UNIQUE INDEX SCno
ON SC
(
Sno ASC,
Cno DESC
);

修改索引

格式

ALTER INDEX <旧索引名> RENAME TO <新索引名>

实例

ALTER INDEX SCno RENAME TO SCno;

删除

DML

1.数据查询 SELECT

SQL语言的select X from Y where Z相当于关系代数中的

[Pi_{X}sigma_{Z}(Y) ]

select 列名称 from 表名称
--从表中选择特定的列
select * from  表名称
--从表中选择所有列

高级应用

where----有条件的从表中选取数据

and 和 or --- 基于一个以上的条件对记录进行过滤

order by ---对结果进行排序（默认是升序），若在语句后面添加desc，则是降序

top ---规定要返回记录的数目（可以是返回的具体数目 ， 也可以是百分比）

like ---在where子句中搜索列的指定模式

between---在where子句中使用 ，选取介于两者之间的数据

select distinct---句用于返回唯一不同的值

通配符（%）---可替代一个或多个字符，必须与like一起使用

select * from teacher;   --查找所有表元素
 
--where 
select *  from  teacher where ID> 3  --查找ID>3的列
 
--and 、or
select * from teacher where ID>3 and salary <>6000   --查找ID>3  并且 salary != 6000的列
select * from teacher where ID> 3 or salary < 6000   --查找ID>3 或者 salary<6000的列
 
--order by 
select * from teacher order by salary;  --按salary进行排序，升序
select * from teacher order by salary desc ;  --按salary进行排序，降序
 
--top
select top 2 * from teacher;  --查找前2行
select top 20 percent * from teacher;  --查找前20%
 
--like 通配符一块使用
select * from teacher  where  name like 'a%';  --查找name是以a开头的所有列
select * from teacher where name like '%b';  --查找name是以b结尾的所有列
select * from teacher where  name  like 'a_';  --查找name是以a开头后面只有一个字符 的所有列
select * from teacher where name like '[ac]%';  --  查找name是以a/c开头的所有列
select * from teacher  where name like '[^ac]%'; --查找name 不是以a/c开头的所有列
 
--between   and
select * from teacher where salary between 5000 and  8000;  --查找salary介于[5000,8000]的所有列
--select distinct
select distinct salary  from teacher ;  -- 找出teacher表中salary不一样的列

多表查询

多表联合查询可以通过连接运算来完成，而连接运算又可以通过广义笛卡尔积+选择运算来实现

select 列名,... from 表名,... where 检索条件

检索条件要包含连接条件，通过不同的连接条件可以实现等值连接，不等值连接，各种( heta)连接

相当于关系代数中的

[Pi_{列_1,列_2,列_3,...}(sigma_{检索条件}(表_1 imes 表_2 imes ... imes )) iff Pi_{列_1,列_2,列_3,...} 表_1 underset{连接条件_1}Join 表_2 underset{连接条件_2}Join 表_3 ... ]

例1：按'001'号课程的成绩由高到低顺序显示所有的学生的姓名

SELECT Sname
FROM Student,SC
WHERE Student.S# = SC.S# and SC.C# = '001'
ORDER BY Score DESC;

例2：按'数据库'课成绩由高到低顺序显示所有的学生的姓名

SELECT Sname
FROM Student,SC,Course
WHERE Student.S# = SC.S# and SC.C# = Course.C# and Cname = '数据库'
ORDER BY Score DESC;

重名处理 AS

连接运算会涉及到重名问题，如两个表中的属性重名，表的自身连接.因此需要使用别名区分.

SELECT 列名1 as 列别名1,列名2 as 列别名2, ...
FROM 表名1 as 表别名1,表名2 as 表别名2,...
WHERE 检索条件
上述定义中的 as 可以省略
当定义了别名之后,在检索条件中可以使用别名来限定属性

例1：求薪水不相等的任意一对教师的教师名(薪水高的在前)

SELECT T1.Tname as Teacher1,T2.Tname as Teacher2
FROM Teacher as T1,Teacher as T2
WHERE T1.salary > T2.salary

例2：求年龄不相等的任意一对同学的姓名(年龄大的在前)

SELECT S1.Sname as Student1,S2.Sname as Student2
FROM Student as S1,Student as S2
WHERE S1.age > S2.age;

例3： 求既学过'001'号课,又学过'002'号课的所有学生的学号

SELECT SC1.S#
FROM SC as SC1,SC as SC2
WHERE SC1.C# = '001' and SC2.C# = '002',SC1.S# = SC2.S#

例4：列出没学过李明老师讲授课程的所有同学的姓名

SELECT S#
FROM SC,Course,Teacher
WHERE Teacher.Tname='李明',Course.T# = Teacher.T#,Course.C# = SC.C#

2.数据插入 INSERT INTO

新增一个或一些元组(批处理)到数据库的TABLE中

单一元素的新增命令

INSERT INTO 表名称 VALUES (值1, 值2,....)
--我们也可以指定所要插入数据的列：
INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)

例1：

INSERT INTO Teacher (T#,Tname,D#,Salary)
VALUES ('005','阮小七','03',1250);

--或
INSERT INTO Teacher 
VALUES ('005','阮小七','03',1250);

批处理新增命令

INSERT INTO 表名[(列名,列名,...)]
子查询...

例2：

CREATE TABLE St(S# char(8),Sname char(10))
INSERT INTO St(S#,Sname)
	SELECT S#,Sname
	FROM Student
	WHERE Sname LIKE "%伟"
	ORDER BY Sname

3.数据更新 UPDATE

对某些元组中的某些属性值进行重新设定

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

例1：

--更新某一行中的一个列
select *from teacher;
update teacher set  name = 'bb3' where ID = 3;  --teachaer中ID 等于3的name 改为bb3
select * from teacher;

例2：将所有教师的工资上调5%

UPDATE Teacher
SET Salary = Salary * 1.05;

例3：将所有计算机系的教师工资上调5%

UPDATE Teacher
SET Salary = Salary * 1.05
WHERE D# in
(
 SELECT D#
 FROM Dept
 WHERE Dname = '计算机'
);

4.数据删除 DELETE

DELETE FROM 表名称 WHERE 列名称 = 值

例1：

delete from teacher where ID =1;  --删除表中ID 为1 的行
delete from teacher;  --删除表中所有行

例2：删除自动控制系的所有同学

DELETE FROM Student
WHERE D# in
(
 SELECT D# 
 FROM Dept
 WHERE Dname = '自动控制'
)

文件分类

• 主数据文件 .mdf
• 次要数据文件 .ndf
• 日志文件（对数据库的所有更新操作）.ldf

文件名

• 逻辑文件名：自定义名称
• 物理文件名：实际路径