第三章 带where条件的select语句

1.2.SQL select * from student where sdept=计算机;3.SQL select sysdate from dual;其中 sysdate 为系统时间，dual 为虚表1994-8-16 的员工。SQL select * from employees where hire_date=16-8 月-94;第三节 带where条件的select语句where条件from 表名 where条件;语法：select字段1，字段2,字段3比较运算,=,=,=,例：SQL select first_name,salary from employees where salary select * from employees where first_name=Steven;注意:1. 在Oracle中，sql语句不区分大小写，但是，数据库中的记录内容是区分大小写 的。2. 日期类型的数据要用单引号表示，日期类型的格式必须与当前数据库的日期显示 格式一致，也可以利用相应的函数转化（后面学）。4. between. and.查询记录中某一区域的数据，内容SQL select first_name,salary from employees where salary between 5000 and 10000;2。Not between. and.表示不再这个范围内的数据SQL select first_name,salary from employees where salary not between 5000 and 10000;5.范围内原则关键字：inSQL select first_name,salary from employees where salary in (17000,4200,12000);否定时，则为not in题：SQL select first_name from employees where employee_id|first_name in (100Steven,202Pat);6.模糊查询like关键字，通配符：_通配符：_代表任意一个字符。通配符：%代表任意多个字符。题：查询学生表中姓张的学生。-1口卜1SQL select * from student where sname like 张 %;w E:oracleproduct10. 2. Odb_lbinsqlplu-SQL select * from student where sname likE 张X ；SNOSNAMESAGE SDEPT2000113弓魅民200B279 张二民6.题：查中 first name 以下” 开头的员工信，息。SQL select * from employees where first_name like E%;题：查询员工表中，job_id以AD_开头的员工信息。SQL select first_name,job_id from employees where job_id like AD_ _%;错误写法正确写法：SQL select first_name,job_id from employees where job_id like AD_% escape ;判断是否为空利用 is null 与 is not nullSQL select first_name,commission_pct from employees where commission_pct is null;SQL select first_name,commission_pct from employees where commission_pct is not null;7.逻辑运算符(and , not , or)题：查询员工表中job_id为SA_MAN，工资大于8000的员工。SQL select job_id,salary from employees where job_id=SA_REP and salary8000;题：查询学生表中，计算机系的男生。SQL select * from student where sdept=计算机and ssex=男;题：在计算机系中，年龄在12到22岁之间，姓张或姓李的男生。SQL select * from student where sdept=计算机and sage=12 and sage and or正确写法：SQL select * from student where sdept=计算机and sage=12 and sage select first_name,salary from employees order by salary asc; SQL select first_name,salary from employees order by salary desc;desc表示降序排列JSQL select first_name,salary from employees order by salary;题：查询学生表，先按性别升序排列，再按年龄降序排列默认不写asc和desc，则与asc 一致为升序排列，如下：SQL select * from student order by ssex,sage desc;e. E:oracleproduct10. 2. 0db_1binsqlplus. exeselect * from student order by ssex,sage desc；SNAME SSSAGE SDEPTSB己选择7行。注意：1.如果字段中含有null值，则此字段降序排在表的最上边；升序排在最下边。2. order by排序比较轻松，可以利用列名，列的位置，列的别名等排序。3. 如果有过个字段进行排序，升序降序的字段要分别写上desc和asc如：SQL select * from student order by ssex desc,sage desc;与SQL select * from student order by ssex desc,sage;是两条语句