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;