|
进行了为期两三个月的数据库和软件测试培训，打开pl-sql进行数据查询分析估计是日常工作最经常做的事情，很多时候我们应用人员对SQL语句的应用却是不甚明了，本文尝试结合日常工作经验和网上的资料对工作中常用的简单语句进行总结，希望本文能抛砖引玉，不正之处望多指教。 目录 一、DDL－数据定义语言 作为一般黑盒测试人员，在一般测试工作中，数据库环境已经由数据库管理员建立好，并不需要测试人员建库，删表。所以大家能看懂格式与意义就ok了，这部分只介绍简单的。 1、创建数据库 （几乎用不着） CREATE DATABASE [database-name] 2、删除数据库 （慎重使用） DROP DATABASE dbname1,dbname2… 4、创建表 create table tabname(<列名><数据类型> [not null] [primary key], <列名2><数据类型> [not null],..) 例如: CREATE TABLE S (SNO CHAR(10) NOT NULL , SN VARCHAR(20), AGE INT, SEX CHAR(2) DEFAULT '男' , DEPT VARCHAR(20)); 根据已有的表创建新表（常用）： A：create table NEW like OLD (使用旧表创建新表) B：create table NEW as select col1,col2… from OLD where…… 5、删除表（慎重使用） drop table TABNAME 6、增加字段 Alter table TABNAME ADD <列名><数据类型>[NULL|NOT NULL] 7、修改字段 ALTER TABNAME ALTER COLUMN <列名><数据类型>[NULL|NOT NULL] 8、删除字段 ALTER TABNAME DROP COLUMN <列名><数据类型>[NULL|NOT NULL] 9、创建索引 create [unique] index idxname on TABNAME (col….) 10、删除索引 drop index IDXNAME 注：索引是不可更改的，想更改必须删除重新建。 11、创建视图 create view VIEWNAME as SELECT…… 12、删除视图 drop view VIEWNAME 二、DML－数据操纵语言 1、数据查询 数据查询是数据库中最常见的操作。在本文档里将作重点介绍。SQL语言提供SELECT语句，通过查询操作可得到所需的信息。 SELECT语句：估计没有应用人员不会使用，但是真正了解select语句的整体结构还是需要颇费功夫，这里只作介绍，详细可通过下面例子学习。顺便还介绍一个学习的好办法，sql的帮助里面有很多例子而且都有对应的练习数据库，可以具体分析。 SELECT语句的一般格式为： SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] SELECT语句的执行过程是: 根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。 如果有GROUP子句，则将查询结果按照<列名1>相同的值进行分组。 如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。 如果有ORDER子句，查询结果还要按照<列名2>的值进行排序。 1.1、查询指定列 SELECT <列名> FROM <表名或视图名>
1.2、查询全部列 SELECT * FROM <表名或视图名> 或SELECT <全部列名> FROM <表名或视图名>
1.3、取消相同取值的行 在查询结果中有可能出现取值完全相同的行了。 SELECT DISTINCT <列名> FROM <表名或视图名>
1.4、比较大小 比较运算符有 =，>，>=，<=，<，<>，!>，!< NOT 上述比较运算符 SELECT <列名> FROM <表名或视图名> WHERE <列名> [比较运算符] <比较的值>
1.5、多重条件查询 当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。 其优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级。 SELECT <列名> FROM <表名或视图名> WHERE <条件1> AND <条件1> OR <条件1>…
1.6、确认范围查询 用于确定范围运算符有：BETWEEN…AND…和NOT BETWEEN…AND… SELECT <列名> FROM <表名或视图名> WHERE <列名> [NOT] BETWEEN 值1 AND 值2 这与下等价 SELECT <列名> FROM <表名或视图名> WHERE <列名>>=值1 AND <列名><=值2 SELECT <列名> FROM <表名或视图名> WHERE <列名><值1 OR <列名>>值2
1.7、确认集合 确定集合符号：IN，NOT IN SELECT <列名> FROM <表名或视图名> WHERE <列名>[NOT] IN (常量1，常量2，…，常量n)
1.8字符匹配查询 字符匹配查询符号：LIKE，NOT LIKE ORACLE支持如下四种通配符： _(下划线)：匹配任意一个字符； %(百分号)： 匹配O个或多个字符； SELECT <列名> FROM <表名或视图名> WHERE <列名> [NOT] LIKE <匹配字符串> 注意：oracle字符匹配比sqlserve严格得多，如char类型后带的自动填补的空格就必须作为单个字符考虑。
1.9空值查询 空值不同于零和空格，它不占任何存储空间。 判断某个值是否为NULL值，不能使用普通的比较运算符(一、!一等)，而只能使用专门的判断NULL值的子句来完成。 SELECT <列名> FROM <表名或视图名> WHERE <列名> IS [NOT] NULL
1.10常用库函数及统计汇总查询 常用的库函数 AVG： 按列计算平均值 SUM：按列计算值的总和 MAX：求一列中的最大值 MIN：求一列中的最小值 COUNT：按列值计算个数 总数：select count(field1) as totalcount from table1 求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1 注1：SQL规定，当使用计算函数时，列名不能与计算函数一起使用(除非他们出现在其他集合中)。 例如查询年龄最大的学生的姓名和年龄，如下写法是错误的： SELECT 姓名，MAX(年龄)FROM Student 注2：计算函数不能出现在WHERE子句中。 ． 例如查询年龄最大的学生的姓名如下写法是错误的： SELECT 姓名 FROM Student WHERE 年龄=MAX(年龄) 正确的命令应为: SELECT 姓名，年龄 FROM Student Where 年龄=(select max(年龄) from student)
1.11分组查询 SELECT <列名> FROM <表名或视图名> GROUP BY<分组依据列>[，…n] [HAVING<组提取条件>] 注1：分组依据列不能是text、ntext、image和bit类型的列。 注2：有分组时，查询列表中的列只能取自分组依据列(计算函数中的列除外)
1.12对查询结果进行排序 SELECT <列名> FROM <表名或视图名> ORDER BY<列名>[ASC l DESC][，…n]
1.13数据表连接查询 A、 INNER JOIN： 这是最普通的联接类型。只要在这两个表的公共字段之中有相符值，内部联接将组合两个表中的记录。 SELECT fields FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field1 AND ON table1.field2 compopr table2.field2) OR ON table1.field3 compopr table2.field3)]; B、left outer join： 左外连接（左连接）：结果集包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c C：right outer join: 右外连接(右连接)：结果集包括连接表的匹配连接行，也包括右连接表的所有行。 D：full outer join： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
1.14使用TOP限制结果集 使用TOP谓词时注意最好与ORDER BY子句一起使用，因为这样的前几名才有意义。但当使用WITH TIES时，要求必须使用ORDER BY子句。 TOP谓词写在SELECT单词的后边，查询列表的前边。 使用TOP谓词的格式为： TOP n[percent]with ties] 其中：n为非负整数。 TOP n：表示取查询结果的前n行； TOP n percent：表示取查询结果的前n％ 行； With ties：表示包括并列的结果。
1.15将查询结果存入表中 INTO子句的语法格式为： INTO 新表名 INTO子句跟在SELECT子句之后、FROM子句之前。SELECT <列名> INTO 新表名 FROM。 新表名是要存放查询结果的表名，SELECT INTO语句包含两个操作：首先按查询列表创建新表，然后执行查询语句，并将结果保存到新表中。 用INTO子句创建的新表可以是永久表，也可以是临时表。临时表又分为两种：局部临时表和全局临时表。局部临时表要在表名前加#，它只能用在当前的连接中；全局临时表要在表名前加##，它的生存期为创建全局临时表的连接的生存期
1.16合并查询 使用UNION的格式为： SELECT 语句1 SELECT 语句2 SELECT 语句n 使用UNION的两个基本规则是： A、所有查询语句中的列个数和列的顺序必须相同。 B、所有查语句中的对应列的数据类型必须兼容。
1.17子查询 A、使用子查询进行比较测试 使用子查询进行比较测试时，通过比较运算符(=、!=、<、>、<=、>=)，将一个表达式的值与子查询返回的单值进行比较。如果比较运算的结果为True，则比较测试也返回True。 使用子查询进行的比较测试要求子查询语句必须是返回单值的查询语句。 例1:查询修了"c02"课程的且成绩高于此课程的平均成绩的学生的学号和成绩。 SELECT 学号,成绩 FROM SC WHERE 课程号=‘c and 成绩>( SELECT AVG(成绩) from SC WHERE 课程号=‘c
B、使用子查询基于集合的测试 使用子查询进行基于集合的测试时，通过运算符IN和NOT IN，将一个表达式的值与子查询返回的结果集进行比较。这同前边在WHERE子句中使用的IN作用完全相同。使用IN运算符时，如果该表达式的值与集合中的某个值相等，则此测试为True；如果该表达式与集合中的所有值均不相等，则返回False。 注意：使用子查询进行基于集合的测试时，由该子查询返回的结果集是仅包含单个列的一个列表，该列必须与测试表达式的数据类型相同。当子查询返回结果之后，外层查询将使用这些结果。
C、 使用子查询进行存在性测试 使用子查询进行存在性测试时，往往使用EXISTS谓词。带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。 例6:查询选修了‘‘c SELECT 姓名 FROM Student WHERE EXISTS (SELECT * FROM SC WHERE 学号=Student．学号 AND 课程号=‘c 注1:带EXISTS谓词的查询是先执行外层查询，然后再执行内层查询。由外层查询 的值决定内层查询的结果；内层查询的执行次数由外层查询的结果数决定。 上述查询语句的处理过程为： (1)找外层表Student表的第一行，根据其学号的值处理内层查询； (2)用外层的值与内层的结果比较，由此决定外层条件的真、假值；如果为真，则此记录为符合条件的结果； (3)顺序处理外层表Student表中的第2、3、…行。 注2：由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用“*”。
2．数据更新 SQL语言的数据更新语句DML主要包括插入数据、修改数据和删除数据三种语句。 2.1插入一行新记录 INSERT INTO <表名>[(<列名1>[,<列名2>…])] VALUES(<值>)
2.2插入一行的部分数据值 只写上部分列名，没有写上的列名值自动为空，如果列是NOT NULL则必需赋值。
2.3插入多行记录 INSERT INTO <表名> [(<列名1>[,<列名2>…])] 子查询
2.4修改数据 UPDATE <表名> SET <列名>=<表达式> [,<列名>=<表达式>]… [WHERE <条件>]
2.5删除记录 DELETE FROM<表名> [WHERE <条件>] |
|
|
