MYSQL：基本SELECT语句
第03章_基本的SELECT语句
1.SQL概述
1.1SQL背景知识
1.2SQL语言排行榜
自从SQL加入了TIOBE编程语言排行榜，就一直保持在Top 10.
1.3 SQL分类
SQL语言在功能上主要分为如下3大类：
学习技巧：大出着眼，小处着手。
2.SQL语言的规则与规范
2.1基本规则
2.2 SQL大小写规范（建议遵守）
2.3注释
2.4命名规则（暂时了解）
2.5导入数据
方式一：source 文件的全路径名
举例：source D:\atguigudb.sql;
mysql> source D:\atguigudb.sql;
ERROR:
Unknown command '\a'.
Query OK, 0 rows affected, 1 warning (0.04 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected, 1 warning (0.30 sec)
Database changed
Query OK, 0 rows affected (0.10 sec)
Query OK, 0 rows affected, 2 warnings (5.29 sec)
Query OK, 25 rows affected (0.50 sec)
Records: 25 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.18 sec)
Query OK, 0 rows affected, 4 warnings (1.84 sec)
Query OK, 27 rows affected (0.20 sec)
Records: 27 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.10 sec)
Query OK, 0 rows affected, 6 warnings (4.07 sec)
Query OK, 107 rows affected (0.62 sec)
Records: 107 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.12 sec)
Query OK, 0 rows affected, 3 warnings (2.30 sec)
Query OK, 6 rows affected (0.36 sec)
Records: 6 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.21 sec)
Query OK, 0 rows affected, 3 warnings (1.51 sec)
Query OK, 10 rows affected (0.33 sec)
Records: 10 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.22 sec)
Query OK, 0 rows affected, 3 warnings (2.03 sec)
Query OK, 19 rows affected (0.29 sec)
Records: 19 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.15 sec)
Query OK, 0 rows affected, 2 warnings (2.00 sec)
Query OK, 23 rows affected (0.48 sec)
Records: 23 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.29 sec)
Query OK, 0 rows affected, 2 warnings (1.63 sec)
Query OK, 3 rows affected (0.36 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.19 sec)
Query OK, 0 rows affected, 2 warnings (3.94 sec)
Query OK, 4 rows affected (0.57 sec)
Records: 4 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.29 sec)
Query OK, 0 rows affected (0.23 sec)
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected, 6 warnings (2.69 sec)
Query OK, 0 rows affected (2.01 sec)
Query OK, 0 rows affected (0.18 sec)
Query OK, 0 rows affected (0.93 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| atguigudb |
| dbtest1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.11 sec)
方式二：基于具体的图形化界面的工具可以导入数据库
3.基本的SELECT语句
3.0SELECT...
SELECT 1; #没有任何子句
SELECT 9/2
REOM DUAL; #dual：伪表
3.1SELECT ... FROM
- 语法：SELECT 字段1，字段2，... FROM 表名
# *：表中的所有字段（或列）
SELECT * FROM employees;
SELECT employees_id,last_name,salary
FROM employees;
3.2列的别名
3.3去除重复行
3.4空值参与运算
3.5着重号
3.6查询常数
4.显示表结构
使用DESCRIBE或DESC，命令，表示表结构。
DESCRIBE employees;
或
DESC employees;
5.过滤数据
- 背景
- 语法
#查询90号部门的员工信息
SELECT *
FROM employees
#过滤条件声明在from后面
WHERE department_id = 90;
课后习题
【题目】1.查询员工12个月的工资总和，并起别名为ANNUALSALARY
2.查询employees表中去除重复的job_id以后的数据
3.查询工资大于12000的员工姓名和工资
4.查询员工号为176的员工的姓名和部门号
5.显示表departments的结构，并查询其中的全部数据
#第03章课后练习题
1.查询员工12个月的工资总和，并起别名为ANNUALSALARY
#基本工资，无奖金
SELECT employee_id,last_name,salary * 12 "ANNUL SALARY"
FROM employees;
所有工资，包括奖金
SELECT employee_id,last_name,salary * 12*(1+IFNULL(commission_pct,0)) "ANNUL SALARY"
FROM employees;
2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;
3.查询工资大于12000的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary >12000;
4.查询员工号为176的员工的姓名和部门号
SELECT last_name,department_id
FROM employees
WHERE employee_id = 176;
#5.显示表departments的结构，并查询其中的全部数据
DESCRIBE departments;
