文章目录
SELECT …
SELECT 1; -- 返回1
SELECT 1+1;-- 返回2
SELECT 1*3;-- 返回3
SELECT … FROM
DUAL
是伪表。SELECT 字段1,字段2,...字段n FROM 表名
。SELECT * FROM 表名
。其中*
代表 表中的所有字段(或者所有列)。
SELECT 1
FROM DUAL;
-- 其中,DUAL是伪表。
DESCRIBE DUAL;
-- 报错:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DUAL' at line 1
SELECT *
FROM employees;
-- *,代表 表中的所有字段(或者所有列)
SELECT DISTINCT … (去除重复行)
SELECT DISTINCT
,去除重复行。SELECT DISTINCT department_id
,去除department_id
重复的行。SELECT DISTINCT department_id,salary
,去除department_id
、salary
同时重复的行。
SELECT department_id
FROM employees;
-- 返回107条记录
# 去除重复行
SELECT DISTINCT department_id
FROM employees;
-- 返回12条记录
# 反面案例
SELECT DISTINCT department_id
FROM employees;
-- 返回12行记录
SELECT salary,department_id
FROM employees;
-- 返回106行记录
SELECT salary,DISTINCT department_id
FROM employees;
-- 报错
/*
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT department_id
FROM employees' at line 1
*/
# 反面案例
SELECT department_id,salary
FROM employees;
-- 返回107行记录
SELECT DISTINCT department_id,salary
FROM employees;
-- 返回74行记录
SELECT department_id,salary
FROM employees
WHERE department_id = 90
AND salary = 17000;
/*
返回2行记录,如下:
department_id salary
90 17000.00
90 17000.00
*/
SELECT DISTINCT department_id,salary
FROM employees
WHERE department_id = 90
AND salary = 17000;
/*
返回1行记录,如下,
department_id salary
90 17000.00
即 SELECT DISTINCT department_id,salary 剔除了 department_id、salary同时重复的记录。
*/
列的别名
给列取别名,有以下3种方式,
- 列名 别名,比如
SELECT employee_id emp_id FROM employees;
- 列名 AS 别名,比如
SELECT employee_id AS emp_id FROM employees;
- 列名 “别名”,比如
SELECT employee_id "emp_id" FROM employees;
SELECT employee_id emp_id
FROM employees;
SELECT department_name AS dep_name
FROM departments;
SELECT salary*12 "annual salary"
FROM employees;
使用WHERE过滤数据
SELECT … FROM … WHERE
- 查询department_id等于90的员工信息。
SELECT * FROM employees WHERE department_id = 90;
- 查询last_name为’King’员工信息
SELECT * FROM employees WHERE last_name = 'King';
SELECT * FROM employees WHERE last_name = 'king';
SELECT * FROM employees WHERE LAST_NAME = 'KING';
MySQL对大小写不敏感。
# 查询department_id等于90的员工信息。
SELECT *
FROM employees
WHERE department_id = 90;
# 查询last_name为'King'员工信息
SELECT *
FROM employees
WHERE last_name = 'King';
SELECT *
FROM employees
WHERE last_name = 'king';
SELECT *
FROM employees
WHERE LAST_NAME = 'KING';
WHERE中不能使用列的别名
SELECT last_name,salary,12*salary*(1+IFNULL(commission_pct,0)) annual_salary
FROM employees
WHERE annual_salary > 100000;
-- 报错:Unknown column 'annual_salary' in 'where clause'
以上示例报错,是因为MySQL的执行顺序是,
第1步:FROM
。
第2步:WHERE
。
第3步:SELECT
。
空值参与运算
空值,即NULL
。空值参与运算,结果仍然为空值。
SELECT employee_id,salary "月工资",commission_pct,12*salary*(1+commission_pct) "年收入"
FROM employees;
-- 当commission_pct为NULL时,12*salary*(1+commission_pct)的运算结果仍然为NULL。
流控制函数
IF(condition,value1,value2)
,如果condition
为true
,则返回value1
,否则返回value2
。IFNULL(value1,value2)
,如果value1
不为NULL
,则返回value1
,否则返回value2
。
SELECT employee_id,salary "月工资",commission_pct,12*salary*(1+IF(commission_pct IS NOT NULL,commission_pct,0)) "年收入"
FROM employees;
SELECT employee_id,salary "月工资",commission_pct,12*salary*(1+IFNULL(commission_pct,0)) "年收入"
FROM employees;
LIMIT语法
LIMIT语法,有以下两种,
LIMIT 偏移量,条目数
,比如,SELECT ... FROM ... LIMIT 44,2
,偏移44,2条记录。LIMIT 条目数 OFFSET 偏移量
。比如,SELECT ... FROM ... LIMIT 2 OFFSET 44
,偏移44,2条记录。
SELECT employee_id,salary "月工资",commission_pct,12*salary*(1+IF(commission_pct IS NOT NULL,commission_pct,0)) "年收入"
FROM employees
LIMIT 2 OFFSET 44;
SELECT employee_id,salary "月工资",commission_pct,salary*(1+IFNULL(commission_pct,0)) "年收入"
FROM employees
LIMIT 2 OFFSET 44;
SELECT employee_id,salary "月工资",commission_pct,salary*(1+IF(commission_pct IS NOT NULL,commission_pct,0)) "年收入"
FROM employees
LIMIT 44,2;
/*
返回2行记录
employee_id 月工资 commission_pct 年收入
144 2500.00 (Null) 30000.00
145 14000.00 0.40 235200.00
*/
着重号(``)
给 数据库、表、字段 命名时,必须保证数据库名、表名、字段名,不和保留字、数据库系统或常用方法冲突。万一发生了冲突,在SQL语句中使用着重号(``)引起来。
# 错误示范
SELECT *
FROM order;
/*
报错如下,因为表名order 与 MySQL关键字(ORDER BY)冲突了
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order'
*/
# 正确示范
# 使用着重号``
SELECT *
FROM `order`;
查询常数
SELECT '快乐联萌',employee_id,department_id,last_name
FROM employees
LIMIT 3;
/*
返回3行记录,如下:
快乐联萌 employee_id department_id last_name
快乐联萌 100 90 King
快乐联萌 101 90 Kochhar
快乐联萌 102 90 De Haan
*/
显示表结构
显示表结构,语法如下:
DESC 表名
。比如,DESC employees;
DESCRIBE 表名
。比如,DESCRIBE employees;