基本的语句

语法

select 标识选择哪些列
from   标识从哪些表中选择
1
2

选择全部列(不推荐)

select *
from departments;
1
2

选择特定的列

select department_id,location_id
from departments;
1
2

列的别名

select last_name as name,commission_pct comm
from employees;
1
2

select	last_name "name",salary*12 "annual Salary"
from employees;
1
2

去除重复行

select distinct department_id
from employees;
1
2

空值参与运算

所有运算符或列值遇到null值，运算的结果都为null
在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长
度是空。而且，在 MySQL 里面，空值是占用空间的

SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;
1
2
3
4
5
6
7

着重号

 SELECT * FROM `ORDER`;
 
 我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在
SQL语句中使用一对``（着重号）引起来
1
2
3
4

查询结果中增加一列固定常数列

select 'MySQL' corporation ,last_name
from employees
1
2

查询表结构

DESC employees;
或
describe employees;
1
2
3

过滤where

select 字段1,字段2
from 表名
where 过滤条件
1
2
3

select employee_id, last_name, job_id, department_id
from employees
where department_id = 90 ;
1
2
3

练习

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

select employee_id,last_name,salary*12 "ANNUAL SALARY"
from employees;

select employee_id,last_name,salary*12 *(1+ifnull(commission_pct,0)) "ANNUAL SALARY"
from employees;
1
2
3
4
5

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

select distinct job_id
from employees;
1
2

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

SELECT last_name,salary
FROM employees
WHERE salary > 12000;
1
2
3

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

SELECT last_name,department_id
FROM employees
WHERE employee_id = 176;
1
2
3

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

DESC departments;

SELECT * FROM departments;
1
2
3