1. SELECT语句
//WHERE ... IN ( ... )
SQL> SELECT first_name ||' '|| last_name "Employees", dept_id
2 FROM s_emp
3 WHERE dept_id IN (10, 50)
4 ORDER BY last_name;
//WHERE ... LIKE '...%...%'
SQL> SELECT first_name, last_name
2 FROM s_emp
3 WHERE last_name LIKE '%s%';
//WHERE ... BETWEEN ... AND ...
SQL> SELECT userid, start_date
2 FROM s_emp
3 WHERE start_date BETWEEN '05-may-90' AND '26-may-91'
4 ORDER BY start_date;
//WHERE ... NOT BETWEEN ... AND ...
SQL> SELECT last_name, salary
2 FROM s_emp
3 WHERE salary NOT BETWEEN 1000 AND 2500;
2. 创建表及添加表约束
//创建表,无主键约束
SQL> CREATE TABLE s_product(
2 id NUMBER(7) NOT NULL,
3 name VARCHAR2(50) NOT NULL,
4 short_desc VARCHAR2(255),
5 longtext_id NUMBER(7),
6 image_id NUMBER(7),
7 suggested_whlsl_price NUMBER(11,2),
8 whlsl_units VARCHAR2(25));
Table created.
//添加主键约束
SQL> ALTER TABLE s_product ADD CONSTRAINT pk_s_product PRIMARY KEY(ID);
Table altered.
3. 使用函数
//使用Oracle内置函数ROUND()
SQL> SELECT id, last_name, ROUND(salary * 1.15)
2 FROM s_emp;
//使用Oracle内置函数INITCAP(), 字段串接
SQL> SELECT last_name || '(' || INITCAP(title) || ')'
2 FROM s_emp;
//使用Oracle内置函数TO_CHAR(), NEXT_DAY(..., ...), ADD_MONTH(..., ...)
SQL> SELECT last_name, start_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(start_date, 6), 'MONDAY'), 'fmDdspth "of" Month YYYY') REVIEW
2 FROM s_emp;