数据库及表的基本操作

说明：
– INSERT语句中新插入元组的各列名必须与常量一一对应，例 如列名1与常量1对应，列名2与常量2对应，„„ – 各常量的数据类型必须与其对应列的数据类型定义相容， INTO子句中没有出现的列，新插入元组在这些列上取空值。 – 若INTO子句中没有指定任何列，那么需要在VALUES子句中给 出所有列的值
SELECT、FROM和 WHERE这三个子句构成 了SQL最基本的查询语 句
参数 ： –ALL：指定在结果集中可以显示重复（所有）行。 ALL是默认设置 –DISTINCT：指定在结果集中只能显示唯一行，空值 (NULL)被认为相等 –指定只从查询结果集中输出前n行 INTO子句 ：创建新表并将结果行从查询插入新表中 GROUP BY子句：指定按照什么条件对结果集进行分组 ，对于分组条件中的每个列，将其值相等的元组分为 一组。
以上为SQL Server 2000中SELECT语句的语法（其中[ ]中的内容是可选 的），对其基本含义作如下介绍： – SELECT list (选择列表)：定义SELECT语句执行后结果集中的列 （ 可用别名）； – FROM子句：指定查询所涉及到的表 ; – WHERE子句 ：指定查询需要满足的逻辑条件： =、>、<、>=、<=、!=、<>、!>、!< BETWEEN„AND„、NOT BETWEEN„AND„ IN、NOT IN 、LIKE、NOT LIKE IS NULL、NOT NULL AND、OR
数据库及表的基本操作
基本SQL数据操纵
- SQL概述 - 数据的查询 - 数据的更新
数据库的创建
- 角色和用户 - 数据库的创建
表的创建及其完整性约束的实现
- 基本表的创建 - 实体完整性和参照完整性约束的有效性验证及实现
6.1.1 SQL概述
–1974年由Boyce和Chamberlin提出 –不断修改、扩充和完善 –发展成为建立和查询关系数据库的标准语言 –SQL 86—SQL 92—SQL 99—SQL 2003 –大多数数据库共同的数据存取语言和标准接口 –为不同数据库系统之间的互操作奠定了基础
SELECT job_id 工作编号, COUNT(emp_id) 员工数 FROM employee WHERE job_id>4 and job_id<7 GROUP BY job_id HAVING COUNT(emp_id)>2
带有GROUP BY-HAVING子句的SELECT语句编写时容易出错,必须 清晰理解GROUP BY-HAVING的原理,编写这类SELECT语句时应考虑 到的一般原则： –GROUP BY子句将查询结果按某一列或多列的值进行分组，值 相等的为一 组，目的在于细化聚合函数的作用域，使得聚 合函数作用于每一个组，即每一组有一个函数值 –由于输出的是分组信息，SELECT选择列表中的列要么出现在 GROUP BY子句中、要么出现在聚合函数中，否则是无效的列 –GROUP BY子句中不能使用聚合函数 –HAVING子句与WHERE子句的作用不同：WHERE子句作用于表， 从中选择满足条件元组；而HAVING子句作用于各组，从中选 择满足条件的组
思考：
假设数据库中还有另外一个表job2，其中各列属性如下： job_id—int (4), not null, primary key; job_name—nvarchar (100) job_desc—nvarchar(100)
问题：
– 需要将jobs表中所有相关的数据导入（插入）job2表。 采用一次插入一个元组的方式并不现实。 – 那么如何设计和编写这一类需要从另一个表中批量取数 据存储到当前表中的INSERT语句呢？
例1：查询jobs表中所有元组的全部属性 –图形界面方式 –SELECT 语句方式
在“企业管理器”中，选择“工具”→“SQL查询分析 器”命令，弹出“SQL查询分析器”窗口，在数据库下拉列 表中将pubs设置为当前数据库 输入：SELECT job_id,job_desc,min_lvl,max_lvl FROM jobs 执行：选择“查询”→“执行”命令，或者点击工具栏中 带“”的图标；或者点击F5键 SELECT * FROM jobs
例17：在jobs表中插入一个元组，给出部分列的值，明确指 定列 INSERT INTO jobs (job_id, job_desc, max_lvl) VALUES (19, 'InsertPartField', 130)
注意：
– 该INSERT语句未指定min_lvl列及其相应的插入值 – 该INSERT语句成功执行后，可以看到表中新插入的元组，min_lvl的 值为NULL
SQL的聚合函数：
使用SQL提供的聚合函数进行查询，可以直接返回一些 查询结果的统计信息，这些函数一般以列标识符的形式出现 在SELECT的选择列表或者HAVING子句的条件中。基本聚合函 数如下：
– COUNT (*|<列名>)统计元组个数，或一列中值的个数 – SUM (<表达式>)计算与列相关的表达式值的和 – AVG (<表达式>)计算与列相关的表达式值的平均值 – MAX (<列名>)求一列值中的最大者 – MIN (<列名>)求一列值中的最小者
例13：用IN查询employee表中job_id为5或8或10的员工信息
SELECT emp_id, fname, lname, job_id FROM employee WHERE job_id IN (5, 8, 10)
例14：用IS NOT NULL查询job_desc值不为空的元组数
STU_PHONE
Insert Into STUDENT (STU_NO, STU_NAME…) Values(‘19900001’, ‘zhang’, …..)
Delete from STUDENT Where STU_NO = ‘19900001’
6.1.2数据的查询
数据的查询方式
– 图形界面方式 操作简单 ，用于查看单个表中的数据，不 能实现较复杂的查询。 – SQL语句方式 ：SELECT语句，实现复杂查询处理的前提。
例12：用BETWEEN-AND查询jobs表中min_lvl值在75和120之间 的元组
可以用“min_lvl>=75 and min_lvl<=120”描述以上给定的条件，也可用 “min_lvl BETWEEN 75 and 120”描述： SELECT job_id,job_desc,min_lvl,max_lvl FROM jobs WHERE min_lvl BETWEEN 75 and 120
【提示】 - LIKE后的一对单引号，用LIKE实现的是字符串的模糊匹配，LIKE后面 紧跟用作匹配条件的字符串，因此需要使用引号将其括起来。
例11：用TOP n查询jobs表中满足条件的元组中的前10个
SELECT TOP 10 job_id,job_desc FROM jobs WHERE min_lvl >= 75
例9：用MIN查询jobs表中max_lvl的最小值
SELECT MIN (max_lvl) FROM jobs 可类似地编写含有MAX聚合函数的查询语句
例10：基于字符串的模糊匹配，用LIKE在jobs表中查询 job_desc含有“office”的工作信息
SELECT job_id,job_desc,min_lvl, max_lvl FROM jobs WHERE job_desc LIKE '% office %'
例16：向jobs表中插入一个元组，给出所有列的值，不明确 指定列 INSERT INTO jobs VALUES (18, 'TestInsert', 30, 100)
执行后提示“（所影响的行数为 1 行）”，可以在执行以上INSERT语 句前后都执行查询jobs表中数据的SELECT语句，以检测插入是否成功。
SELECT COUNT (job_id) FROM jobs WHERE job_desc is NOT NULL
例15: 在employee表中有员工承担job_id大于4且小 于7的工作，用GROUP BY-HAVING按job_id进行分组 统计，对于有两个以上员工承担的工作，输出其编 号及承担该工作的员工数量
插入查询结果的INSERT语句格式如下：
INSERT INTO <表名> [<列名1>[,<列名2>]„] SELECT查询语句
注意：
必须保证SELECT查询结果的数据类型与将插入的表 中对应列的数据类型一致
例18：将jobs表中所有元组的job_id和job_desc插 入job2表（job2表中没有任何数据）中
例6：用DISTINCT查询jobs表中出现的不同的max_lvl值 SELECT DISTINCT max_lvl from jobs 例7：用COUNT查询employee表中的元组数 SELECT COUNT(emp_id) 员工数 FROM employee 例8：用AVG查询jobs表中min_lvl的平均值 SELECT AVG(min_lvl) FROM jobs 也可以用SUM计算出所有元组min_lvl值的总和，再除以 jobs表中元组的数量得到min_lvl的平均值 SELECT SUM(min_lvl)/COUNT(job_id) FROM jobs
SQL的主要特点 ：
– 集数据定义、数据操纵和数据控制功能于一体 – 高度非过程化 – 采用集合操作方式 – 既是自含式语言，又是嵌入式语言 – 既支持内模式（基本表），也支持外模式（视图） – 语言简捷，_E-MAIL