SELECT * | { [ DISTINCT ] column | expression [ alias ] , ... }
FROM table;
column后面加上空格,同时跟上别名(alias),或者 as 别名,到下一行的内容时,要用逗号隔开,
默认的别名是小写的,如果想要让它是大写的,使用 "别名"
如果别名有多个单词的时候,用双引号别名的方式 比如 “annual salary”
select employee_id id, first_name name from employees;
结果:
.....
193 Britney
194 Samuel
id NAME
------- --------------------
195 Vance
196 Alana
197 Kevin
107 rows selected
连接符:
类似于Java中的System.out.println(123+ “hello” + 123) ;//123hello123
默认情况下,查询会返回全部行,包括重复行。
SELECT last_name||' is a '||job_id
AS "Employee Details"
FROM employees;
列的别名:
SELECT last_name AS name,commission_pctcomm
FROM employees;
SELECT last_name "Name", salary*12 "AnnualSalary"
FROM employees;
The first example displays the names and the commission percentages of all the employees. Notice that theoptionalASkeyword has been used before the columnalias name. The result of the query is the same whether the AS keyword is used or not. Also notice that the SQL statement has the column aliases, name and comm, in lowercase, whereas the result of the querydisplays the column headings inuppercase. As mentioned in a previous slide, column headingsappear inuppercase by default.
默认的这种没有引号的别名是大写的
Thesecond example displays the last names and annual salaries of all the employees. Because Annual Salarycontain a space, it has been enclosed in double quotation marks. Notice that the column heading in the output is exactly the same as the column alias.用双引号的这种方式,可以将特殊的字符保留在引用的别名中,同时大小写和列的别名一致
在SELECT子句中使用关键字‘DISTINCT’删除重复行。
select distinct department_id from employees;
DEPARTMENT_ID
-------------
100
30
20
70
90
110
50
40
80
10
60
12 rows selected
定义空值
包含空值的数学表达式的值都为空值
SQL 语句与 SQL*Plus命令
Structural query language
SQL
SQL*Plus
2. 包含空值的数学表达式的值都为空值
3. 别名使用双引号!
4. oracle 中连接字符串使用 "||", 而不是 java 中的 "+"
5. 日期和字符只能在单引号中出现. 输出 last_name`s email is email
select last_name || ' `s email is ' || email EMAIL
from employees
6. distinct 关键字, 以下语法错误
select last_name, distinct department_id
from employees
习题:
SQL*PLUS命令可以控制数据库吗?否!SQL*PLUS只是一个运行环境,控制数据库的是SQL语言。