03 基本的Select语句

3.1 select .....

select 1; # 没有字句
/*
查询结果:
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
*/

3.2 select ... from ...

# 语法
select 标识选择哪些列(*表示全选) from 表示从那个表中选择;

# 例子
select * from departments;
/*
+---------------+----------------------+------------+-------------+
| department_id | department_name      | manager_id | location_id |
+---------------+----------------------+------------+-------------+
|            10 | Administration       |        200 |        1700 |
|            20 | Marketing            |        201 |        1800 |
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|            60 | IT                   |        103 |        1400 |
|            70 | Public Relations     |        204 |        2700 |
|            80 | Sales                |        145 |        2500 |
|            90 | Executive            |        100 |        1700 |
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             | NULL       |        1700 |
|           130 | Corporate Tax        | NULL       |        1700 |
|           140 | Control And Credit   | NULL       |        1700 |
|           150 | Shareholder Services | NULL       |        1700 |
|           160 | Benefits             | NULL       |        1700 |
|           170 | Manufacturing        | NULL       |        1700 |
|           180 | Construction         | NULL       |        1700 |
|           190 | Contracting          | NULL       |        1700 |
|           200 | Operations           | NULL       |        1700 |
|           210 | IT Support           | NULL       |        1700 |
|           220 | NOC                  | NULL       |        1700 |
|           230 | IT Helpdesk          | NULL       |        1700 |
|           240 | Government Sales     | NULL       |        1700 |
|           250 | Retail Sales         | NULL       |        1700 |
|           260 | Recruiting           | NULL       |        1700 |
|           270 | Payroll              | NULL       |        1700 |
+---------------+----------------------+------------+-------------+
27 rows in set (0.05 sec)
*/

# 选择特定的列
select manager_id, location_id from departments;
/*
+------------+-------------+
| manager_id | location_id |
+------------+-------------+
|        200 |        1700 |
|        201 |        1800 |
|        114 |        1700 |
|        203 |        2400 |
|        121 |        1500 |
|        103 |        1400 |
|        204 |        2700 |
|        145 |        2500 |
|        100 |        1700 |
|        108 |        1700 |
|        205 |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
| NULL       |        1700 |
+------------+-------------+
*/

3.3 列的别名

• 给列起别名之后查询出来的表列名用别名替代
• as 可以省略
• 别名中如果包含空格需要用双引号

select last_name as lname, commission_pct as comm from employees;
# 省略as也可以
select last_name lname, commission_pct comm from employees;
/*
+-------------+------+
| lname       | comm |
+-------------+------+
| King        | NULL |
| Kochhar     | NULL |
| De Haan     | NULL |
| Hunold      | NULL |
| Ernst       | NULL |
| Austin      | NULL |
| Pataballa   | NULL |
| Lorentz     | NULL |
| Greenberg   | NULL |
*/

3.4 去除重复行

通过DISTINCT关键词来去除重复行

• DISTINCT必须要放在所有查询列名之前
• DISTINCT针对后面所有的列名组合去重，简单的说就是去掉后面列名都一致的重复行

SELECT DISTINCT department_id FROM employees;
/*
+---------------+
| department_id |
+---------------+
| NULL          |
|            10 |
|            20 |
|            30 |
|            40 |
|            50 |
|            60 |
|            70 |
|            80 |
|            90 |
|           100 |
|           110 |
+---------------+
12 rows in set (0.05 sec)
*/

# 这条语句会查询出所有列，因为employee_id是表的主键，唯一的，因此每一列都不是重复列
SELECT DISTINCT department_id, employee_id FROM employees;

3.5 空值参与运算

• 所有的运算符或者是列值遇到null值，运算的结果都为null

# 查询员工一年的工资
SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

注：null不等于空字符串。一个空字符串的长度是0，空值的长度是空。

3.6 着重号

当表名或者是列名和关键词或者已有函数重名需要用着重号

select * from ORDER; 
/*1064 - 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' at line 1
*/

select * from `ORDER`;
/*
+----------+------------+
| order_id | order_name |
+----------+------------+
|        1 | shkstart   |
|        2 | tomcat     |
|        3 | dubbo      |
+----------+------------+
3 rows in set (0.04 sec)
*/

3.7 查询常数

查询的时候添加长度可以增加一列字段，用于整合不同的数据源

select 'jiang' as corporation, last_name as lname from employees;
/*
+-------------+-------------+
| corporation | lname       |
+-------------+-------------+
| jiang       | King        |
| jiang       | Kochhar     |
| jiang       | De Haan     |
| jiang       | Hunold      |
| jiang       | Ernst       |
| jiang       | Austin      |
| jiang       | Pataballa   |
| jiang       | Lorentz     |
| jiang       | Greenberg   |
| jiang       | Faviet      |
| jiang       | Chen        |
*/

3.8 显示表结构

使用DESCRIBE或者DESC命令，表示表结构。

DESCRIBE employees;
DESC employees;
/*
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id   | int         | NO   | PRI | 0       |       |
| department_name | varchar(30) | NO   |     | NULL    |       |
| manager_id      | int         | YES  | MUL | NULL    |       |
| location_id     | int         | YES  | MUL | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.05 sec)
*/

• Filed表示字段名
• Type表示类型
• Null表示是否可以为null
• key表示是否存在索引，PRI是主键，UNI表示唯一索引，MUL表示不唯一
• Defalut表示是否存在默认值，默认值为多少
• Extra表示可以获取与给定列有关的附加信息，例如AUTO_INCREMENT等

3.10 过滤表数据

用where条件来过滤数据

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件;
# WHERE需要紧跟这FROM后面

# 查询部门id为90的员工号、姓名、工作id、部门id
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;

3.10 练习

• 查询员工12个月的工资总和，并起别名为ANNUAL SALARY

  select employee_id, 12 * salary as "ANNUAL SALARY" from employees;
  
  # 如果要算上奖金系数的话，需要用到IFNULL函数，如果commission_pct为null取0，不为null取其值
  select employee_id, 12 * salary * (1 + IFNULL(commission_pct, 0)) as "ANNUAL SALARY" from employees;
  

• 查询employees表中去除重复的job_id以后的数据

  select DISTINCT job_id from employees;
  

• 查询工资大于12000的员工姓名和工资

  select first_name fname, last_name lname, salary 
  from employees WHERE salary > 12000;
  

• 查询员工号为176的员工的姓名和部门号

  select first_name fname, last_name lname, department_id 
  from employees WHERE employee_id = 176;
  

• 显示表 departments 的结构，并查询其中的全部数据

  DESC departments;
  select 8 from departments;