一、MySQL的查询

本文主要是关于常用语句SELECT的应用，首先需要导入数据。这些数据是关于员工的信息。

1、创建数据

#创建数据
CREATE TABLE employes(
id INT(6),
name_first VARCHAR(20),
name_last VARCHAR(25),
email VARCHAR(25),
phone VARCHAR(20),
job_id VARCHAR(10),
salary DOUBLE(10,2),
commission_pet DOUBLE(4,2),
manager_id INT(6),
department_id INT(4),
hiredate DATETIME)

DROP TABLE employes;#删除表格

INSERT INTO 
employes(id,name_first,name_last,email,phone,job_id,salary,commission_pet,manager_id,department_id,hiredate) 
VALUES
(1,'小花','张','1578895@qq.com','15732115468','102',8500,0.2,582,745,20180502);

INSERT INTO 
employes(id,name_first,name_last,email,phone,job_id,salary,commission_pet,manager_id,department_id,hiredate) 
VALUES
(2,'大军','刘','1552414@qq.com','15754235841','103',7900,0.1,582,745,20180812);

INSERT INTO 
employes(id,name_first,name_last,email,phone,job_id,salary,commission_pet,manager_id,department_id,hiredate) 
VALUES
(3,'亮','陈','15896335474@qq.com','15231052478','104',8900,0.2,583,746,20160712);

INSERT INTO 
employes(id,name_first,name_last,email,phone,job_id,salary,commission_pet,manager_id,department_id,hiredate) 
VALUES
(4,'大林','刘','1554265421@qq.com','15699524123','105',6500,0.1,583,746,20170514);

INSERT INTO 
employes(id,name_first,name_last,email,phone,job_id,salary,commission_pet,manager_id,department_id,hiredate) 
VALUES
(5,'福全','刘','154876774@qq.com','18956423578','103',7800,NULL,584,746,20170514);

INSERT INTO 
employes(id,name_first,name_last,email,phone,job_id,salary,commission_pet,manager_id,department_id,hiredate) 
VALUES
(6,'弥月','赵','1585465451@qq.com','19638542718','105',8500,0.2,582,746,20190514);

INSERT INTO 
employes(id,name_first,name_last,email,phone,job_id,salary,commission_pet,manager_id,department_id,hiredate) 
VALUES
(7,'大云','张','18524139524@qq.com','13452165742','104',7600,0.1,584,747,20160427);

创建的数据通过 SELECT * FROM employes; 语句显示，如下所示：

2、普通查询:select

USE test;#首先启用具体的库

SELECT name_last FROM employes;#查询表中的单个字段
SELECT name_last,name_first FROM employes;#查询表中的多个字段
SELECT * FROM employes;#查询表中的所有字段(直接双击字段即可选中)
#如果自己定义的字段与关键字重复，可以使用着重号 ` 将软件中的关键字变为自己使用字段

#查询常量值
SELECT 100;
SELECT 'john';

SELECT 100%98;#查询表达式——除法取余
SELECT VERSION();#查询函数

#起别名
SELECT 100%98 AS 结果;
SELECT name_last AS 姓,name_first AS 名 FROM employes;
SELECT name_last 姓,name_first 名 FROM employes;#不再使用as
SELECT salary AS 'out put' FROM employes#如果别名之间存在空格，需要用单引号括起来

SELECT DISTINCT department_id FROM employes;#去重

#+号的作用：只有运算数值功能，没有拼接字符串功能
SELECT 30+4;
SELECT '90'+7;#会将字符串转化为int
SELECT 'john'+89;#如果转化失败，会将转化失败的转化为0
SELECT NULL+4;

SELECT CONCAT('a','b','c') AS 结果;#将字符串拼接起来
SELECT CONCAT(name_last,name_first) AS 姓名 FROM employes; #将表格中的字符串拼接起来

DESC employes;#显示表结构
SELECT IFNULL(name_last,0) AS 结果 FROM employes;#ifnull()函数可以将null值转化为指定数值

2、条件查询：where

/*  
select 查询列表 from 表名 where 筛选条件;

分类：1,按照条件表达式筛选
	条件运算符：> < = !=或<> >= >=
      2,按照逻辑表达式筛选
        逻辑运算符:and or not,&& || !
      3,模糊查询
	like:一般和通配符搭配使用
	通配符：% 任意多个字符，包含0个字符;
		_ 任意单个字符
		\ escape 转义字符，可以将具有特殊功能的_变为字符串中无功能的_
	between and,in,is null,is not null
*/

#1、按条件表达式筛选
SELECT name_first,department_id FROM employes WHERE department_id !=746;#查询部门id不为746的员工名字和部门id

#2、按照逻辑表达式筛选
SELECT name_first,salary,commission_pet FROM employes WHERE salary>= 8000 AND salary<=9000;#查询工资在8000和9000之间的员工名字、工资和奖金率

#3、模糊查询
#like
SELECT * FROM employes WHERE name_first LIKE '%林';#查询名字中含有林字的数据，%表示通配符中的任意字符
SELECT * FROM employes WHERE name_first LIKE '大_';#查询总长为2，首字为大的员工名的数据
#between and
SELECT * FROM employes WHERE salary BETWEEN 7000 AND 8000; #查询工资范围在7000和8000之间(包含临界值)的员工数据,两个临界值不要调换顺序
#and
SELECT * FROM employes WHERE name_last IN('李','张');#查询指定姓氏集合的员工数据,in列表的值类型必须统一
#is null
SELECT name_last AS 姓氏 FROM employes WHERE manager_id IS NOT NULL;#查询领导id非null的员工姓氏
#安全等于 <=>，is null 仅可以判断null值，<=>既可以判断null值也可以判断普通数值，=只能判断普通数值
SELECT name_last AS 姓氏 FROM employes WHERE manager_id <=> 582;
#concat()函数的应用
SELECT CONCAT(name_last,name_first) AS 姓名,salary*12*(1+commission_pet) AS 年薪 FROM employes WHERE id=4;#查询id为4的员工姓名和年薪

3、排序查询:order by

/*
select * 查询列表 from 表 where 筛选条件 order by asc|desc

asc代表升序,desc代表降序,默认升序
*/
SELECT * FROM employes WHERE salary>6000 ORDER BY salary ASC;#将高于6000的工资员工信息按照工资水平由低到高排序
SELECT *,salary AS 年薪 FROM employes WHERE salary>6000 ORDER BY 年薪 ASC;#按照别名排序
SELECT * FROM employes WHERE salary>6000 ORDER BY LENGTH(name_first) ASC;#按照函数结果排序，length()用于测量字符串长度，本处按照名字长度排序
SELECT * FROM employes ORDER BY salary ASC,id DESC;#先按照工资升序,再按照id降序排序