图形化界面

这里使用的是SQLlog：Webyog_SQLyog_setup.zip

图1. SQLlog操作界面
另外，Navicat也是不错的操作工具。

接下来，就写一个命令，看一下怎么在这里运行命令。

在询问里写代码，下面就可以显示出来结果：

图2. 写代码并运行

五、DQL语言的学习

首先，先导入数据，执行后面文件夹中的myemployees.sql文件，就会将数据导入到其中，如图所示数据库文件.zip

图3. 导入数据

5.1 基础查询

基础查询的语句是：select 查询的东西 from 表名;在里面可以使用F12来进行自动调整代码，下面我们使用字段来区分关键字和字段名（第3条语句）。
语法如下：

# 5.1 基础查询
# 语法：select 查询的东西 from 表名;
/* 
特点
1. 查询列表可以是：表中的字段、常量、常量值、表达式、函数
2. 查询的结果是一个虚拟的表格
*/

5.1.1 查询字段

USE myemployees;
SELECT last_name FROM employees;            # 查询表中的单个字段
SELECT last_name,salary,email FROM employees;        # 查询多个字段，字段的,号之后不要空格
SELECT `first_name`, `last_name`, `phone_number`, `job_id` FROM employees;    # 字段的,号之后要有空格

图4. 查询结果

5.1.2 查询常量

SELECT 100;
SELECT 'john';

图5. 查询结果

5.1.3 查询表达式

SELECT 100*98;

图6. 查询结果

5.1.4 查询函数

SELECT VERSION();

图7. 查询结果

5.1.5 起别名

给我们的字段起一个别名，方便理解：

SELECT 100*98 AS result;

图8. 查询结果
将表中的字段在最后结果显示的时候起一个别名：

SELECT last_name AS 姓,first_name AS 名 FROM employees;

图9. 查询结果

5.1.6 去重

不去重会得到107行的结果：

图10. 不去重

接下来将重复的内容不显示，就变成了12行：

# 加上DISTINCT关键字
SELECT DISTINCT department_id FROM employees;

图11. 去重

5.1.7 +号的作用

mysql中的+号，只有一个作用：运算符select 100+90。如果+号双方存在字符型的变量，就会将字符型转换成数值型，如果转换成功，则继续做加法运算；如果转换失败，则字符型数值转换成0.

如果想要实现字符串的拼接，就要使用CONCAT()函数。

/* 使用CONCAT()函数进行拼接 */
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

图12. 拼接显示

5.2 条件查询

条件查询的语法格式如下：

# 5.2 条件查询
/*
语法：
select 查询列表 from 表名 where 筛选条件;
*/

5.2.1 条件表达式筛选

简单条件运算符：>、<、=、!=、<>、>=、 <=

案例1：查找工资>12000的员工信息：

/* 查找工资>12000的员工信息 */
SELECT * FROM employees WHERE salary>12000;

图13. 工资>12000的员工信息

案例2：查询部门编号不等于90号的员工名和部门编号

/* 查询部门编号不等于90号的员工名和部门编号 */
SELECT last_name,department_id FROM employees WHERE department_id<>90;
/* 注意：在sql中我们一般使用<>而不是!= */
#SELECT last_name,department_id FROM employees WHERE department_id!=90;

图14. 部门编号<>90的员工信息

5.2.2 逻辑表达式筛选

逻辑运算符：&&(and)、||(or)、 !(not)

案例1：查询工资在￥10000到￥20000之间的员工名、工资及奖金

/* 查询工资在￥10000到￥20000之间的员工名、工资及奖金 */
SELECT last_name,salary, commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;

图15. 工资10000~20000之间的查询结果

案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息

/* 查询部门编号不是在90到110之间，或者工资高于15000的员工信息 */
SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

图16. 查询结果

5.2.3 模糊查询

模糊查询关键字：like、between and、in、is null

like关键字

案例1：查询员工名中包含字符a的员工名

/* 查询员工名中包含字符a的员工名 */
SELECT last_name FROM employees WHERE last_name LIKE '%a%';       /* %a%中的%表示通配符，即任意字符 */

图17. like查询结果

通配符： %：能够匹配任意多个字符，包含0个字符 _：任意单个字符

案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资

/* 查询员工名中第三个字符为n，第五个字符为l的员工名和工资 */
SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';

图18. 单个字符查询结果

案例3：查询员工名中第二个字符为_的员工名

# 查询员工名中第二个字符为_的员工名 
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';

图19. 转义字符\_查询结果

between and关键字

案例：查询员工编号在100到120之间的员工信息

# 查询员工编号在100到120之间的员工信息 
/*SELECT * FROM employees WHERE employee_id >=100 AND employee_id <= 120;*/
/* 相比于上面的条件表达式，使用between and 会简单一点 */
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

图20. 查询结果

in关键字

案例：查询员工的公众编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

# 查询员工的公众编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
/* 使用逻辑表达式实现 */
/* SELECT last_name,job_id FROM employees WHERE job_id='IT_PROT' OR job_id='AD_VP' OR job_id='AD_PRES'; */
/* 使用in关键字实现 */
SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');

图21. in关键字查询结果

is null关键字

案例：查询没有奖金的员工名和奖金率

# 查询没有奖金的员工名和奖金率
/* 使用逻辑表达式筛选，但是=不能判断NULL值 */
/* SELECT last_name,commission_pct FROM employees WHERE commission_pct=NULL; */
/* 所以需要用到is null关键字 */
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

图22. is null关键字查询结果

5.2.4 测试

查询工资大于12000 的员工姓名和工资
查询员工号为176 的员工的姓名和部门号和年薪
选择工资不在5000 到12000 的员工的姓名和工资
选择在20 或50 号部门工作的员工姓名和部门号
选择公司中没有管理者的员工姓名及 job_id
选择公司中有奖金的员工姓名，工资和奖金级别
选择员工姓名的第三个字母是 a 的员工姓名
选择姓名中有字母 a 和 e 的员工姓名
显示出表employees 表中 first_name 以 ‘e’结尾的员工信息
显示出表employees 部门编号在80-100 之间的姓名、职位
显示出表employees 的manager_id 是 100,101,110 的员工姓名、职位
5.3 排序查询
我们需要对筛选出来得结果进行升序或者降序排列，这个时候就需要用到排序查询：
```
# 语法
select 查询列表 from 表 where 查询条件 order by 排序列表 [asc|desc]
```

案例1：查询员工信息，要求工资从高到低排序

# 查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;

图23. 工资从高到低

案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序

# 查询部门编号>=90的员工信息，按入职时间的先后进行排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY salary ASC;

图24. 入职时间从先到后

案例3：按年薪的高低显示员工的信息和年薪

/* 注意：没有年薪这一列，所以需要我们新建这一列，并且按照这列的数值进行排序 */
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 
FROM employees 
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
# 我们可以使用别名
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 
FROM employees 
ORDER BY 年薪 DESC;

图25. 年薪由高到低

案例4：按姓名的长度显示员工的姓名和工资

# 使用length函数来获取姓名的长度
SELECT LENGTH(last_name) 姓名长度,last_name,salary 
FROM employees 
ORDER BY LENGTH(last_name) DESC;

图26. 长度由长到短

案例5：查询员工信息，要求先按工资排序，再按员工编号排序

图27. 先由salary排序，再由编号排序
测试：

查询员工的姓名和部门号和年薪，按年薪降序按姓名升序
选择工资不在8000 到17000 的员工的姓名和工资，按工资降序
查询邮箱中包含e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
5.4 常见函数
5.4.1 单行函数
字符函数

length()函数：返回字节长度
concat()函数：拼接字符串
upper()、lower()函数：大小写互换
substr()、substring()函数：截取从索引开始的字符串
instr()函数：返回子字符串在字符串中的索引
trim()函数：去掉字符串左右的空格 / 特定字符串
lpad()函数：对字符串长度不够的字符串进行左填充
rpad()函数：对字符串长度不够的字符串进行右填充
replace()函数：替换字符串

数学函数
round：四舍五入
ceil：向上取整
floor：向下取整
truncate：截断
mod：取余

日期函数
now：用于返回当前系统日期+时间
curdate：返回当前系统日期，不包括时间
select year(now()) 年;：可以获取指定的部分，年、月、日、小时、分钟、秒
str_to_date：将日期格式的字符串转换成指定格式的日期（例子：str_to_date('9-13-1999','%m-%d-%Y')）
date_format：将日期转换为字符（例子：date_format('2018/6/6','%Y年%m月%d日')）

其他函数
select version();：当前版本
select database();：当前数据库
select user();：当前用户

流程控制函数
if 函数：

select IF(10<5, '大','小'); #如果第一个表达式10<5为true，则返回第一个表达式"大"，不然返回第二个
例子：

select last_name,commission_pct, if(commission_pct is null, '没奖金','有奖金')  as 奖金情况 
from employees;

图28. if函数

case函数：

语法：

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
else 要显得值n或语句n;
end

例子1，使用的=情况：

/*
查询员工的工资，要求：
部门号=30，显示的工资为1.1倍
部门号=40，现实的工资为1.2倍
部门号=50，现实的工资为1.3倍
其他部门，显示的工资为原工资
*/
select salary as 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

图29. case函数判断条件为=
例子2，使用的是> <等情况：

/*
查询员工的工资级别情况：
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/
select salary as 工资,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'c'
else 'D'
end as 工资级别
from employees;

图30. case函数判断条件为>

测试

显示系统时间(注：日期+时间)
查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
将员工的姓名按首字母排序，并写出姓名的长度（length）
做一个查询，产生下面的结果

earns monthly but wants

图31. 查询结果

使用 case-when，按照下面的条件：

图32. 题目5

5.4.2 分组函数

常用的分组函数有：sum求和函数、max最大值函数、min最小值函数和count计算个数的函数

# 分组函数的简单使用
SELECT SUM(salary) AS 和,AVG(salary) AS 平均,MAX(salary) AS 最大,MIN(salary) AS 最小,COUNT(salary) AS 工资不为零个数
FROM employees;

图33. 分组函数的简单使用

注意：分组函数都忽略了null值

测试：

查询公司员工工资的最大值，最小值，平均值，总和
查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）
查询部门编号为90 的员工个数

数据库的合辑

MySQL学习（二）