SELECT 语法
单表查询
查询所有的字段(或列) SELECT * FROM employees;
查询个别列
SELECT employee_id,last_name,salary FROM employees; (不带别名)
SELECT employee_id emp_id,last_name AS lname, department_id “部门Id” FROM employees;
SELECT employee_id emp_id,last_name AS lname, department_id “部门id” salary*12 “annual sal” FROM employees;
列的别名使用“”引起来,不要使用’ ’
SELECT DISTINCT
department_id, salary FROM employees; DISTINCT 表示唯一,去重
,这里对department_id去重
空值参与运算
空值为Null,null 不等同于0,’’,‘null’
SELECT employee_id, salary “月工资”, salary * (1+commission_pct)*12 “年工资”, commission_pct FROM employees;
如果commission_pct为NULL,则SQL会报错
SELECT employee_id, salary “月工资”, salary * (1 + IFNULL(commission_pct,0))*12 “年工资”, commission_pct FROM employees;
着重号 ` xx `, xx 可以为关键字,当字段名/表名与保留字重名,必加着重号
查询常数 SELECT ‘xxx’, 123, employee_id, last_name FROM employees;
显示表结构 DESCRIBE employees; DESC employees;
WHERE
过滤数据 WHERE
查询90号部门的员工信息
SELECT * FROM employees WHERE department_id = 90;
查询last_name为King的员工信息
WHERE last_name=‘King’
运算符
运算符
-
算术运算符 + - * / div % mod
-
SELECT 100+‘1’ FROM dual;
=> 101 (SQL)
=> 1001 (JAVA)
在SQL中,+没有连接的作