An Image/Link below is provided (as is) to download presentation
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.Content is provided to you AS IS for your information and personal use only.Download presentation by click this link.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.During download, if you can't get a presentation, the file might be deleted by the publisher.
Savepoint A Savepoint B 在表中插入一行 在表中删除第一行 Rollback to savepoint A Savepoint C
5.select语句 select 列1,列2……列N from 表1,……表N 【where 条件】 【order by 列1[asc|desc][列2[asc|desc]…]】; Where子句的比较运算符:=,<>, <, <=,> , >= 查询表中所有列信息:select * from 表名;
例1:使用calling-card表查询移动公司发行的电话卡的卡号和初值。查询显示按卡号从小到大排列。例1:使用calling-card表查询移动公司发行的电话卡的卡号和初值。查询显示按卡号从小到大排列。 select card-number, starting-value from calling-card where company-name=‘移动’ order by card-number [asc];
1. Select name, id,title From emp Order by title ,name desc; 2. Savepoint before_update; update emp set salary=salary+200 where dept_id=’41’; 通过查询可对改变是否正确加以验证: select name salary from emp where dept_id=’41’; 如果更新不正确,用户可通过以下命令取消这些改变。 rollback to savepoint before_update;
SQL中关系运算符的执行 • 使用distinct取消重复的行 select [all|distinct] 列1,列2……列N from 表1,……表N 【where 条件】 【order by 列1[asc|desc][列2[asc|desc]…]】; 例:在emp表中,有多少不同的职务? select distinct title from emp;
例1:查询所有雇员的姓名及所在部门名称。 Select emp.name,dept.name From emp,dept Where emp.dept_id=dept.id; 使用表别名: select A.name , B.name from emp A,dept B where A.dept_id=B.id;
自连接 • 自连接是一个表与其自身的连接 例1:显示每一个雇员的名字及他或她的管理者的名字。 select E.name as “employee”, M.name as “manager” from emp E,emp M where M.id=E.manager_Id
外连接 • 假设有两个表的连接,有时我们想知道不满足其中一个表指定条件的另一个表的行。例如,我们想知道还没有管理者的雇员。 select 表1.列1,表1.列2…,表2.列1,…表2.列N from 表1,表2 where 表1.列名(+)=表2.列名; 用于计算在表1中所有与表2不匹配的行的外连接 select 表1.列1,表1.列2…,表2.列1,…表2.列N from 表1,表2 where 表1.列名=表2.列名(+); 用于计算在表2中所有与表1不匹配的行的外连接
例:列出无管理者的所有雇员。 select E.name as “employee”, M.name as “manager” from emp E,emp M where M.id(+)=E.manager_Id
Select D.name as “department” ,R.name as “region” from dept D, region R where D.region_id = R.id ; 2. Select E.name as “sales reps”, C.name as “customers” from emp E, customer C where C.sales_rep_id=E.id ; 3. Select C.name as “customers”, E.name as “sales reps” from emp E, customer C where C.sales_rep_id=E.id(+) ;