select语句
1.distinct去重
eg:select distinct job_type from employees;
2.空值参与运算
eg:select salary*(1+IFNULL(commission,0))*12 'ANNUAL_SALARY' from employees;
3.着重号''
保证字段与保留字和数据库关键字不重复,如果使用重复了,使用着重号''区分
eg:select * from 'order';
4.查询常数
select '能力有限公司',employee_id,last_name,salary from employees;
5.显示表结构
显示了表中字段的详细信息
DESCRIBE employees;
DESC employees;
6.过滤数据
where条件
eg:select * from employees where job_id='1212';
运算符
算术运算符
1.+,-,*,div,% mod
eg:select 100,100+0,100-0,100+50 from DUAL;
eg:select 100+'1' from DUAL;在SQL中,+没有连接的作用,会将字符串转换为数值,被称为隐式转换
比较运算符
1.=,<=>, <> !=, <,<=,>,>=
eg:select 1=2,1!=2,1='1' ,1='a' from DUAL;
=>0,1,1,0
字符串存在隐式转换,如果转换数值不成功,则看做0
eg:select 'a'='a','ab'='ab','a'='b' from DUAL;
=>1,1,0
两边都是字符串的话,则按照ANSI的比较规则进行比较
eg:select 1=NULL,NULL=NULL from DUAL;
=>NULL,NULL
只要有NULL参与,结果就为NULL
2.<=>安全等于,主要作用于NULL比较
eg:select 1<=>NULL,NULL<=>NULL from DUAL;
=>0,1