〇、导入一个数据库
首先导入数据库 myemployees
,执行 myemployees.sql
文件(图形客户端执行)。
执行SQL
命令行下:
## 先下载文件到本地 wget https://statics.gemo.one/others/mysql/myemployees.sql ## 一步执行命令 mysql -uroot -ppass_word <myemployees.sql ## 或 mysql -uroot -ppass_word mysql> source xxxx # 文件名
初步认识
导入后我们可以看到数据库中的表的关系如下:
一、基础查询
1. 语法
select 查询列表 from 表名;
2. 特点
查询结果是一个虚拟表
select
查询列列表,类似打印select` 后面可以有多个部分组成,中间用逗号隔开:
select 字段1,字段2,表达式 from 表;
执行顺序
from
子句select
子句
查询列表可以是:字段、表达式、常量、函数等
3. 执行
1. 查询常量
SELECT 100;
2. 查询表达式
SELECT 100%3; ## 结果是 1
3. 查询单个字段
SELECT last_name FROM employees; ## 对于sql关键字作为查询字段的时候,需要加着重号` ## SELECT `last_name` FROM `employees`;
结果:
last_name |
---|
K_ing |
Kochhar |
De Haan |
…… |
4. 查询多个字段
SELECT last_name, email, employee_id FROM employees;
结果:
last_name | employee_id | |
---|---|---|
K_ing | SKING | 100 |
Kochhar | NKOCHHAR | 101 |
De Haan | LDEHAAN | 102 |
…… | …… | …… |
5. 查询所有字段
SELECT * FROM employees; # 字段顺序根据表中定义的顺序输出 # 多行多个字段 SELECt last_name, first_name, commission_pct, hiredate, salary FROM employees;
结果:
employee_id | first_name | last_name | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate | |
---|---|---|---|---|---|---|---|---|---|---|
100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
…… | …… |
6. 查询函数(调用函数,获取返回值)
## 查看当前所在的数据库 SELECT DATABASE(); ## 查看当前数据库的版本号 SELECT VERSION(); ## 获取当前用户信息 返回 user@ip SELECT USER();
7. 起别名
查询的显示名称可以使用方法修改成其他名字:
# 方式1 使用 as 关键字 SELECT USER() AS 用户名; SELECT USER() AS "用户名"; SELECT USER() AS '用户名'; SELECT last_name AS "姓 名" FROM employees; # 方式2 使用空格 直接删除 AS 即可 SELECT USER() 用户名; SELECT USER() "用户名"; SELECT USER() '用户名'; SELECT last_name "姓 名" FROM employees; ## 语义性上 AS 方法更强
结果:
姓 名 |
---|
K_ing |
…… |
8. 拼接
+
号的作用
- 两个操作数为数值型,则执行正常的加法运算;
- 其中一个操作数为字符型则将字符型转换成数值,如果无法转换,则直接当作
0
处理; - 其中一个操作数为
null
,则结果是null
;
拼接函数 CONCAT
SELECT CONCAT(first_name, ' ', last_name) AS "姓 名" FROM employees;
结果:
姓 名 |
---|
Steven K_ing |
…… |
9. DISTINCT
函数
可以用来去重查询,查询后结果会自动去除重复数据。
SELECT DISTINCT department_id FROM employees;
10. 查看表结构
DESC employees; # aka SHOW COLUMNS FROM employees; ## 显示表的字段信息
4. 案例
USE `myemployees`; # 下面的语句是否可以执行成功 SELECT last_name , job_id , salary AS Sal FROM employees; # 下面的语句是否可以执行成功 SELECT * FROM employees; # 找出下面语句中的错误 SELECT employee_id , last_name, salary * 12 "ANNUAL SALARY" FROM employees; # 显示表 departments 的结构,并查询其中的全部数据 DESC departments; SELECT * FROM departments; # 显示出表 employees 中的全部 job_id(不能重复) SELECT DISTINCT job_id FROM employees; # 显示出表 employees 的全部列,各个列之间用逗号连接,列头显示成 OUT_PUT SELECT CONCAT(employee_id, ',', first_name, ',', last_name, ',', email, ',', phone_number, ',', job_id, ',', salary, ',', IFNULL(commission_pct, ''), ',', IFNULL(manager_id, ''), ',', IFNULL(department_id, ''), ',', hiredate ) AS "OUT_PUT" FROM employees; ## IFNULL(1, 2) -- 如果 1 是 NULL ,则显示2;否则1
二、条件查询
1. 语法
select 查询列表 from 表名 where 筛选条件
执行顺序:
from
where
select
SELECT last_name, first_name FROM employees where salary>20000;
2. 特点
按关系表达式筛选
关系运算符 >大于 <小于 >=大于等于 <=小于等于 =等于 <>不等于 可以使用 != 但不建议
按逻辑表达式筛选
逻辑运算符: and or not 可以 && || ! ,但不建议
模糊查询
like in between and is null
3. 执行
1. 按关系表达式筛选
# 案例1: 查询部门编号不是100的员工信息 SELECT * FROM employees WHERE department_id <> 100; # 案例2: 查询工资<15000 的姓名,工资 SELECT CONCAT(first_name, ' ', last_name) AS '姓名', salary AS '工资' FROM employees WHERE salary <15000;
2. 按逻辑表达式筛选
# 案例1: 查询部门编号不是50-100之间员工的姓名、部门编号、邮箱 # 方式1 SELECT CONCAT(first_name, ' ', last_name) AS '姓名', department_id AS '部门编号', email AS '邮箱' FROM employees WHERE department_id <50 OR department_id >100; # 方式2 SELECT CONCAT(first_name, ' ', last_name) AS '姓名', department_id AS '部门编号', email AS '邮箱' FROM employees WHERE NOT(department_id >= 50 AND department_id <=100); # 语义清晰还是使用模糊查询 但本小结是逻辑表达式 SELECT CONCAT(first_name, ' ', last_name) AS '姓名', department_id AS '部门编号', email AS '邮箱' FROM employees WHERE department_id NOT BETWEEN 50 AND 100; # 案例2: 查询奖金率>0.03 或者 员工编号在60-110 之间的员工信息 SELECT * FROM employees WHERE commission_pct>0.03 or (employee_id >= 60 AND employee_id <= 100)
3. 模糊查询
1. like
语句
一般用于字符型字段。
一般和通配符搭配使用,对字符型数据进行部分匹配查询。
常见的通配符:
_
任意单个字符%
任意多个字符(0-多个)
# 案例1: 查询姓名中包含字符a的员工信息 SELECT * FROM employees WHERE last_name LIKE '%a%'; # 案例2: 查询姓名中最后一个字符为e的员工信息 SELECT * FROM employees WHERE last_name LIKE '%e'; # 案例3: 查询姓名中第一个字符为e的员工信息 SELECT * FROM employees WHERE last_name LIKE 'e%'; # 案例4: 查询姓名中第三个字符为x的员工信息 SELECT * FROM employees WHERE last_name LIKE '__x%'; # 案例5: 查询姓名中第二个字符为_的员工信息 # 使用转义字符 SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; ## 可以使用其他语言中 的 '\' 作为转义字符,但不建议 ## ESCAPE 可以定义前面作为转义字符的 字符 ## 该语句中定义率 '$' 作为转义字符
2. in
语句
用于查询某字段的值是否属于指定的列表之内
a in (常量值1, 2, 3, ……) # not a not in (1, 2, 3, ……)
# 案例1: 查询部门编号是30/50/90的员工名、部门编号 SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', department_id AS '部门ID' FROM employees WHERE department_id in (30, 50, 90); # 逻辑表达式 SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', department_id AS '部门ID' FROM employees WHERE department_id = 30 OR department_id = 50 OR department_id = 90 ## 两种方式的效率是一致的,仅仅是语义上的区别 # 案例2 查询工种编号不是 SH_CLERK 或 IT_PROG 的员工信息 SELECT * FROM employees WHERE job_id NOT IN ('SH_CLERK', 'IT_PROG'); ## 数值型不需要引号,其他需要
3. between and
语句
判断某个字段的值是否介于xx之间
between and not between and
# 案例1 查询部门编号在30-90之间的部门编号、员工姓名 SELECT department_id AS '部门ID', CONCAT(first_name, ' ', last_name) AS '姓名' FROM employees WHERE department_id BETWEEN 30 AND 90; # 案例2 查询年薪不是100000-200000之间的员工姓名、工资、年薪 SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', salary AS '月工资', salary * 12 * (1 + IFNULL(commission_pct, 0)) AS '年薪' FROM employees WHERE salary * 12 * (1 + IFNULL(commission_pct, 0)) NOT BETWEEN 100000 AND 200000;
4. is null
/ is not null
# 案例1 查询没有奖金的员工信息 SELECT * FROM employees WHERE commission_pct IS NULL; ## = 只能判断普通类型 ## IS 只能判断NULL值 ## <=> 安全等于,既能判断普通内容,又能判断NULL值 --> 语义性不好
4. 案例
# 查询工资大于 12000 的员工姓名和工资 SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', salary AS '工资' FROM employees WHERE salary > 12000; # 查询员工号为 176 的员工的姓名和部门号和年薪 SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', department_id AS '部门号', salary * 12 * (1 + IFNULL(commission_pct, 0)) AS '年薪' FROM employees WHERE employee_id = 176; # 选择工资不在 5000 到 12000 的员工的姓名和工资 SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', salary AS '工资' FROM employees WHERE salary NOT BETWEEN 5000 AND 12000; # 选择在 20 或 50 号部门工作的员工姓名和部门号 SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', department_id AS '部门号' FROM employees WHERE department_id IN (20, 50); # 选择公司中没有管理者的员工姓名及 job_id SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', job_id FROM employees WHERE manager_id IS NULL; # 选择公司中有奖金的员工姓名,工资和奖金级别 SELECT CONCAT(first_name, ' ', last_name) AS '员工姓名', salary AS '工资', commission_pct AS '奖金级别' FROM employees WHERE commission_pct IS NOT NULL; # 选择员工姓名的第三个字母是 a 的员工姓名 SELECT last_name AS '名字' FROM employees WHERE last_name LIKE '__a%'; # 选择姓名中有字母 a 和 e 的员工姓名 SELECT last_name AS '名字' FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%'; # 显示出表 employees 表中 first_name 以 'e'结尾的员工信息 SELECT * FROM employees WHERE first_name LIKE '%e'; # 显示出表 employees 部门编号在 80-100 之间 的姓名、职位 SELECT CONCAT(first_name, ' ', last_name) AS '姓名', job_id AS '职位' FROM employees WHERE department_id BETWEEN 80 AND 100; # 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位 SELECT CONCAT(first_name, ' ', last_name) AS '姓名', job_id AS '职位' FROM employees WHERE manager_id IN (100, 101, 110);
三、排序查询
1. 语法
select 查询列表 from 表名 [where 筛选条件] order by 排序列表
执行循序:
from
where
select
order by
select last_name, salary form employees where salary > 10000 order by salary;
2. 特点
排序列表可以是单个字段、多个字段、表达式、函数、以及以上的组合
升序:通过
asc
,默认降序:通过
desc
3. 执行
1. 按单个字段排序
# 案例1 将员工编号>120的员工信息进行工资的升序 SELECT * FROM employees WHERE employee_id > 120 ORDER BY salary;
2. 按表达式排序
# 案例1 对有奖金的员工,按年薪降序 SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS '年薪' FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC; ## 先执行 WHERE 语句, 于是 在计算的过程中 不需要使用 IFNULL ## 由于 WHERE 先执行 不能用 SELECT 起的别名,但 ORDER BY 是 SELECT 后执行的,可以使用别名 SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪 FROM employees WHERE commission_pct IS NOT NULL ORDER BY 年薪 DESC; ## 注意 ORDER BY 后面不能接 '年薪',而应该接 年薪。 ## 加 '' 引号的内容 ORDER BY 会自动忽略
3. 按函数的结果排序
# 案例1 按姓名的字数长度进行升序 SELECT LENGTH(last_name), last_name FROM employees ORDER BY LENGTH(last_name)
4. 按多个字段进行排序
# 案例1 查询员工的姓名、工资、部门编号,按工资升序、再按部门编号降序 SELECT CONCAT(first_name, ' ', last_name), salary, department_id FROM employees ORDER BY salary, department_id DESC ;
5. 按列数排序
SELECT * FROM employees ORDER BY 2; ## 直接列数 将按照表中的第二个属性字段进行排序 ## 阅读性差
总结
本文主要完成 SQL
中的查询操作,即 DQL(Data Query Language)
数据查询语言,其中包括基础查询以及扩展的条件查询和数据排序。
其中,所有查询都是基于基础查询进行操作的:
SELECT KEYWORD1,KEYWORD2 FROM TABLE_S_NAME
具体实现上是先执行 FROM
语句再执行 SELECT
,即先获取表,再在表中获取对应的字段数据。同时在 SELETE
后我们还可以添加 DISTINCT
关键字实现去重查询,即查询结果中我们不会看到重复的数据。
在基础查询的基础上,我们可以给予其一些限定条件,使用 WHERE
实现操作。
SELECT KEYWORD FROM TABLE_S_NAME WHERE XXX
条件语句是我们编程中熟悉的语句,其中不等号在 SQL
中是 <>
而非 !=
,但 !=
是可用的。且或非使用对应的英文替代,当然符号也支持。
特有的是模糊查询,有些类似于正则表达式,但有所不同。
LIKE
关键字后可以使用 _
%
,_
代表任意单个字符,%
代表任意多个字符。
还有 IN
和 BETWEEN AND
IS NULL
等,都是对应的语义。
而排序查询控制升序的 ASC
,降序的是 DESC
。查询若有排序字段,默认是升序,不需要添加对应的关键字。
在写 SQL
语句时应当使用其原生的语句,而且设计时应当使得语句有强的可读性。