MySQL基本的SELECT语句

1.基本查询语句

SELECT 字段1,字段2,...FROM 表名
*:代表表中所有的字段(或者列)
例子：
SELECT name FROM test.user;  # 查询test数据库中user表里的所有name

2.列的别名

2.1 加空格起别名

2.2 AS起别名

SELECT employees.department_id de_id,employees.phone_number AS phone FROM employees;

2.3.使用""起别名(支持utf8)

SELECT employees.department_id de_id,employees.phone_number AS phone,employee_id "员工编号" 
FROM employees;

3.去除重复行 

# DISTINCT:去重
SELECT DISTINCT employees.department_id de_id FROM employees;
# 易错警示
SELECT phone_number DISTINCT employees.department_id FROM employees;

 4.空值参与运算

1.空值：NULL

2.NULL不等同于0,' ','NULL'

3.空值参与运算:结果一定也为空

SELECT employees.employee_id,employees.salary "月工资",employees.salary*(1+employees.commission_pct)*12 "年工资",employees.commission_pct FROM employees;

 

 5.着重号 

SELECT name FROM test.`user`;

6.查询常数

SELECT '中国',test.`user`.id,test.`user`.`name` "姓名" FROM test.`user`;

7.显示表结构 

DESCRIBE 表名; # 显示表中字段的详细信息

或

DESC

8.过滤数据 

条件：声明必须放到FROM结构的后面

语法：SELECT 字段1,字段2,... FROM 表名 WHERE 过滤条件

SELECT * FROM employees WHERE department_id = 90;
SELECT * FROM employees WHERE last_name = 'King';