SQL:结构化查询语言 C R U D: 增删改查 table : name age score desc+表名 ---> 查询表结构 或者用 describe 命令 (desc是describe的简写) 查询语言:SELECT [DISTINCT] {*,column[alias],...} FROM table; SELECT identifies what columns FROM identifies which table ******* SELECT 查什么 FROM 从哪里查 ******* SELECT dept_id,last_name,manager_id FROM s_emp; 遇到空值时可以这样操作: SELECT last_name,title,salary*NVL(commission_pct,0)/100 COMM FROM s_emp; 排除查询时出现重名现象 ,用关键字 DISTINCT eg: SELECT DISTINCT name FROM s_dept; 排除两个字段连合起来还有重复的现象: eg: SELECT DISTINCT dept_id,title FROM s_emp; 对查询的结果进行排序用 ORDER BY; eg: SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary; (默认是升序排序) eg: SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary asc; (asc指定为升序排序) eg: SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary desc; (desc指定为降序排序) 总结:order by 排序 asc 升序 desc 降序 select last_name EMPLOYEE,start_date from s_emp order by EMPLOYEE desc; (在排序的时候可以用别名当做字段[排序的根据]进行排序)--->这里是(EMPLOYEE)。 select last_name,salary*12 from s_emp order by 2; (这里的2是用来指定字段的位置,这里指向 salary*12 ,即按员工的年薪排序) select last_name,dept_id,salary from s_emp order by dept_id,salary desc; (先按dept_id的升序进行排序,排完后再按salary的降序进行排序) 注:这里dept_id后没有直接指排序规则,按默认的升序排序。 WHERE关键字: SELECT 姓名,部门ID FROM s_emp WHERE dept_id = 42; (从s_emp表中查出部门ID=42的员工姓名和部门ID) select last_name,dept_id from s_emp where last_name = ‘Biri’; (查出员工姓名是Biri的员工的部门ID) 注:这里的Biri是用单[']号括起来的 select last_name,salary from s_emp where salary > 1000 order by salary; (查出s_emp表中员工工资大于1000的员工的姓名并且按员工工资进行排序) 注:这里是WHERE和BODER BY结合的例子,ORDER BY放在语句的最后。 查两个范围之间的语句有两种: 1.select last_name,salary from s_emp where 1500<salary and salary<2000 order by salary; 2.select last_name,salary from s_emp where salary between 1500 and 2000 order by salary; (两句都是用来查出工资在1500-2000之间的员工的姓名) 注:第二句用了between ..and..语句,在where之后一定要跟上标准; between..and..查出的数据是包括限定范围在内的 in的用法: select last_name,dept_id from s_emp where dept_id in (41,42,43); (查出部门ID是 41,42,43 的所有员工) select last_name,COMMISSION_PCT from s_emp where COMMISSION_PCT is null; (查出s_emp表中COMMISSION_PCT为空的所有员工的姓名) 注:这里用了关键字 ----> is null。 select last_name,COMMISSION_PCT from s_emp where COMMISSION_PCT is not null; (查出s_emp表中COMMISSION_PCT不为空的所有员工的姓名) select * from s_emp where last_name like ‘B%’; (查出s_emp表中姓名首字母为B的所有员工的信息) 补充:不等于的三种表示方式: != 、<>、^= NOT BETWEEN NOT IN NOT LIKE IS NOT NULL [%]是通配符 [_]单配符 select last_name from s_emp where last_name like '_e%'; (查出员工姓名第二字符是‘e’的员工) select table_name from user_Tables where table_name like 'S/_%' escape '/'; (查出数据库中以‘S_’开头的所有表,这里‘_’应用‘/’进行转义,如果不转义就会当做单配符使用) 注:escape关键字用来告诉系统以escape后面的字符做为转义字符。 注意下面两种语句: select last_name,salary,dept_id,title from s_emp where dept_id=41 and title='Stock Clerk'; 查出s_emp表中dept_id=41 且 title='Stock Clerk' 的所有员工 select last_name,salary,dept_id,title from s_emp where dept_id=41 or title='Stock Clerk'; 查出s_emp表中dept_id=41 或 title='Stock Clerk' 的所有员工 注意:and 的优先级要比or高 select last_name,salary,dept_id from s_emp where salary >=1000 and dept_id =44 or dept_id=44; (查出s_emp表中salary>=1000且dept_id=44的员工或dept_id=44的员工[优先级]) select last_name,salary,dept_id from s_emp where salary >=1000 and (dept_id =44 or dept_id=44); (时间出s_emp表中dept_id=44或dept_id=42的salary>=1000的员工) 查询语句的总结: SELECT[DISTINCT]{*,column[alias],...} FROM table [WHERE condition(s)] [ORDER BY{column,expralias}[ASC|DESC]]; 注:大写部分为关键字,'[]'部分为可选值 函数部分: function_name(column|expression,[arg1,arg2,...]) 转化函数: LOWER 转小写 UPPER 转大写 INITCAP 首字母大写 CONCAT 字符连接 SUBSTR 字符截取 LENGTH 取字符长度 NVL select last_name from s_emp where lower(last_name) = 'biri'; (先把表中的数据转化为小写再和指定字段比较) 注:SQL语句不区分大小写,但字段区分大小写,这样转化可以避免大小写问题。 数字函数(number functions): ROUND 四舍五入 TRUNC 截取 ROUND(45.923,2)--------->45.92 ROUND(45.923,0)--------->46 ROUND(45.923,-1)-------->50 TRUNC(45.923,2)--------->45.92 TRUNC(45.923)----------->45 TRUNC(45.923,-1)-------->40 日期相关函数: 日期格式:默认格式: DD-MON-RR SYSDATE 可以返回系统当前时间(oracle用) select sysdate from dual; 获取oracle系统当前时间 select sysdate+1 from dual; 获取明天日期 MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC `MONTHS_BETWEEN('01-SEP-95','11-JAN-94')---------------------->19.774194 `ADD_MONTHS('11-JAN-94',6)------------------------------------>'11-JUL-94' `NEXT_DAY('01-SEP-95','FRIDAY')------------------------------->'08-SEP-95' `LAST_DAY('01-SEP-95')---------------------------------------->'30-SEP-95' `ROUND('25-MAY-95','MONTH')----------------------------------->'01-JAN-95' `ROUND('25-MAY-95','YEAR')------------------------------------>'01-JAN-95' `TRUNC('25-MAY-95','MONTH')----------------------------------->'01-MAY-95' `TRUNC('25-MAY-95','YEAR')------------------------------------>'01-JAN-95' 类型间的互相转化: TO_CHAR TO_NUMBER TO_DATE HH24:MI:SS AM select start_date, last_name from s_emp where to_char(start_date,'mm')='07'; (查出s_emp表中入职月份是07的员工,只要是to_char的用法) 改进:select start_date,last_name from s_emp where to_number(to_char(start_date,'mm'))=7; ceil 向上取整 等值连接 非等值连接查询 外连接 左外:以左表为基准,即左表中的数据必须全部出现 (+)在右边 右外:以右表为基准,即右表中的数据必须全部出现 (+)在左边 【凡是出现"+"的一边,就有可能出现空值】 自连接 内连接 内连接举例: select e.last_name, d.name from s_emp e inner join s_dept d on e.dept_id = d.id where e.last_name = 'Biri'; 交叉连接 关联查询语法: SELECT table.column,table.column FROM table1,table2 WHERE table1.column1=table2.column2; eg: select last_name,name from s_emp,s_dept where dept_id=s_dept.id and last_name='Biri'; (查出Biri所在部门的名称,这里关联到两个表[s_emp表]和[s_dept表], 关联条件是 s_emp.dept_id=s_dept.id) 注:这里关联到两个表时,字段表示和字段的顺序一定要弄清楚。 自连接: select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where e.manager_id = l.id and e.last_name = 'Biri'; (查出s_emp表中员工名为Biri的员工的领导的名字) 注:领导也属于员工表的一员,所以本查询语句关联的是s_emp表本身,这里就要把它当作两个表 来使用,取两个别名来区分表,然后再对应表字段。 select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where e.manager_id = l.id; (查出s_emp表中员工对应的领导名,这里有一个缺陷就是可能会少一条数据) 改进:select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where e.manager_id = l.id(+) ; (对上面的语句进行了改进,即当总领导的领导为空时也打印出来,这里其实是左外连接) 标准的SQL语句应该为: select e.last_name 员工名,l.last_name 领导名 from s_emp e left outer join s_emp l on e.manager_id = l.id; (这里使用了语句 "left outer join" 明确表示了左外连接 ,注意后面的where 变成了 on 相应的 右外连接就"right outer join") GROUP BY语句的语法: SELECT column,group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; GROUP BY 语句用来分组,HAVING 用来过滤。 GROUP BY 的组函数 AVG(DISTINCT|ALL|n) COUNT(DISTINCT|ALL|expr|*) MAX(DISTINCT|ALL|expr) MIN(DISTINCT|ALL|expr) SUM(DISTINCT|ALL|n) select avg(salary) from s_emp; (查出s_emp表中所有员工的平均工资,这里用到了AVG组函数) select min(salary) , max(salary) , sum(salary) from s_emp group by dept_id; (以dept_id为标准来查询s_emp表中所有员工的最小、最大和工资之和,此句显示的结果是 按dept_id的不同进行分组显示) 注意:观察下面两条语句 1、select dept_id min(salary) , max(salary) , sum(salary) from s_emp group by dept_id; 2、select last_name ,min(salary) from s_emp; 这两条语句中 第一条 可以执行 而第二条执行不了,原因是,第一条是组函数而第二条是单函数, 这是由SQL的语法规则而定的 select e.dept_id,min(e.salary),max(e.salary),sum(e.salary),d.name from s_emp e,s_dept d where e.dept_id = d.id group by dept_id,d.name; (以dept_id和s_dept表中的name为标准来查询s_emp表中所有员工的最小、最大、工资之和及部门名称) select dept_id,count(*) "Number" from s_emp group by dept_id; (按dept_id[部门ID]来统计s_emp表中每个部门有多少个员工[即统计相同的dept_id出现的次数]) 注:dept_id可以和组函数count一起使用的原因是,这里是把dept_id做为了分组的条件 ------求除41部门以外各个部门的最小,最高,工资之和,部门名 方法一:select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name from s_emp e,s_dept d where e.dept_id=d.id and dept_id !=41 group by dept_id,d.name order by sum(salary) desc; 方法二:select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name from s_emp e,s_dept d where e.dept_id=d.id group by dept_id,d.name having dept_id!=41 order by sum(salary) desc; (方法二用到关键字having ,having 必须和group by一起使用,having 的主要功能是过滤 即保留having后条件成立的内容) 注:having子句,是对分组之后的数据进行过滤 -----求增均工资高于1500的各个部门的最小,最高,工资之和,部门名 select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name from s_emp e,s_dept d where e.dept_id=d.id group by dept_id,d.name having avg(salary)>1500 order by avg(salary) desc; 子查询: -------查出s_emp表中工资最低的员工 select last_name from s_emp where salary=(select min(salary) from s_emp); (Oracle认可的一个子查询语句,可以简单的理解为查询语句的嵌套) 注:这里"="后的语句只能返回一个结果,如果返回多个结果就会出错 -------查出平均工资低于32部门的部门ID select dept_id ,avg(salary) from s_emp group by dept_id having avg(salary)<(select avg(salary) from s_emp where dept_id = 32); 注:这里用到了组函数、group by、having 子句 还有子查询 用户名:sys as sysdba 密码:change_on_install 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/myron_mao/archive/2009/11/25/4870335.aspx