MYSQL 语言入门(2)

一、DQL查询表中数据

​ 数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

​ DQL是实际开发过程中使用最多的。

1. 数据准备

# 创建员工表 
表名 emp 
表中字段： 
eid 员工id，int 
ename 姓名，varchar 
sex 性别，char 
salary 薪资，
double hire_date 入职时间，date
dept_name 部门名称，varchar

# 创建WCK数据库
create database WCK;

# 选择WCK
use WCK;

# 创建员工表
create table emp(
	eid int,
	ename varchar(20),
	sex char(1),
	salary double,
	hire_date date,
	dept_name varchar(20)
);

# 添加测试数据 
insert into emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部'); 
insert into emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
insert into emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部'); 
insert into emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部'); 
insert into emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部'); 
insert into emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部'); 
insert into emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
insert into emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部'); 
insert into emp VALUES(9,'吴承恩','男',20000,'2000-03-14',null); 
insert into emp VALUES(10,'孙悟饭','男', 10,'2020-03-14','财务部');
insert into emp VALUES(11,'兔八哥','女', 300,'2010-03-14','财务部');
insert into emp VALUES(12,'Tom','男', null,'2010-03-14','财务部');

2. 简单查询

​ 查询不会对数据库中的数据进行修改, 只是一种显示数据的方式。

​ 语法格式：

select 字段名 from 表名;

​ 案例1: 查询emp中所有的数据

-- 将要查询的字段全部列出
select eid ,ename, sex, salary, hire_date, dept_name from emp;

-- 查询所有的字段可以使用 * , * 代表所有的字段
select * from emp;

​ 案例2: 查询emp表中所有的记录, 只显示eid和ename字段

select eid, ename from emp;

​ 需求3: 查询所有的员工信息, 将字段名显示为中文

select 
	eid as '编号',      -- as可以省略
	ename as '姓名',
	sex as '性别',
	salary as '薪资',
	hire_date as '入职日期',
	dept_name as '部门名称'
from emp;

​ 需求4: 查询有多少个部门

-- 查询所有的部门(出现重复部门)
select dept_name from emp;
-- 使用去重查询 关键字 distinct
select distinct dept_name from emp;

​ 需求5: 将所有的员工薪资加1000显示

-- 支持算术运算符 + - * / % 的运算
select eid, ename, salary, salary+1000 from emp;

3. 条件查询

• 如果查询语句中没有设置条件, 就会查询所有的行信息

• 在实际应用中, 一定要指定查询的条件, 对记录进行过滤

  语法格式：

select 列名 from 表名 where 条件;

3.1 比较运算符

运算符	说明
> < <= >= = <> !=	大于 小于 小于等于 大于等于 等于 不等于
between…and…	显示在某一区间的值
in(集合)	集合表示多个值，使用逗号分隔，例如name in (悟空,八戒); in中的每个数据都会作为一次条件，只要满足条件就会显示
like ‘%张%’	模糊查询
is null	查询某一列为null的值，注意，不能写 = null

3.2 逻辑运算符

运算符	说明
and &&	多个条件同时成立
or ||	多个条件任一成立
not	取反

案例1: 精确查询

#1 查询员工姓名为黄蓉的员工信息 
select * from emp where ename = '黄蓉';

#2 查询薪水价格为5000的员工信息
select * from emp where salary = 5000;

#3 查询薪水价格不是5000的所有员工信息 
select * from emp where salary != 5000; 
select * from emp where salary <> 5000; 

#4 查询薪水价格大于6000元的所有员工信息 
select * from emp where salary > 6000; 
#5 查询薪水价格在5000到10000之间所有员工信息 
select * from emp where salary >= 5000 and salary <= 10000; 
select * from emp where salary between 5000 and 10000; 

#6 查询薪水价格是3600或7200或者20000的所有员工信息
select * from emp where salary = 3600 or salary = 7200 or salary = 20000;
select * from emp where salary in (3600, 7200, 20000);

案例2: 模糊查询

模糊查询，通配符

通配符	说明
%	表示匹配任意多个字符
_	表示匹配任意一个字符

#1 查询含有'八'字的所有员工信息
select * from emp where ename like '%八%';
 
#2 查询以'孙'字开头的所有员工信息 
select * from emp where ename like '孙%';

#3 查询第二个字为'兔'的所有员工信息 
select * from emp where ename like '_兔%';

#4 查询没有部门的员工信息 
select * from emp where dept_name is null;

#5 查询有部门的员工信息
select * from emp where dept_name is not null;

4. 排序

​ 通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影响真实的数据）。

语法格式：

select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc  升序(默认)
-- desc 降序

4.1 单列排序

​ 只按照某一个字段进行排序。

​ 案例: 查询所有的员工信息, 使用salary进行排序。

-- 升序排序(默认 asc)
select * from emp order by salary;

-- 降序排序(desc)
select * from emp order by salary desc;

4.2 组合排序

​ 同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推。

​ 案例: 查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序。

-- 组合排序
select * from emp order by salary, hire_date desc;

5. 函数

​ MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。

​ 函数只是对查询结果中的数据进行处理，不会改变数据库中数据表的值。

​ MySQL中的函数主要分为单行函数和多行函数两大类:

​ 单行函数:

​ 单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

​ 常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。

​ 使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。

​ 多行函数:

​ 我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。

​ 多行函数会忽略null空值。
​ 多行函数也称为分组函数, 聚合函数。

5.1 单行函数

5.1.1 字符串函数

函数	描述
CONCAT(str1, str2, ···, strn)	将str1、str2···strn拼接成一个新的字符串
INSERT(str, index, n, newstr)	将字符串str从第index位置(从1开始计算)开始n个字符替换成字符串newstr
LENGTH(str)	获取字符串str的长度(字节)
LOWER(str)	将字符串str中的每个字符转换为小写
UPPER(str)	将字符串str中的每个字符转换为大写
LEFT(str, n)	获取字符串str最左边的n个字符
RIGHT(str, n)	获取字符串str最右边的n个字符
LPAD(str, n, pad)	使用字符串pad在str的最左边进行填充，直到长度为n个字符为止
RPAD(str, n, pad)	使用字符串pad在str的最右边进行填充，直到长度为n个字符为止
LTRIM(str)	去除字符串str左侧的空格
RTRIM(str)	去除字符串str右侧的空格
TRIM(str)	去除字符串str左右两侧的空格
REPLACE(str,oldstr,newstr)	用字符串newstr替换字符串str中所有的子字符串oldstr
REVERSE(str)	将字符串str中的字符逆序
STRCMP(str1, str2)	比较字符串str1和str2的大小
SUBSTRING(str,index,n)	获取从字符串str的index(从1开始)位置开始的n个字符

#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat('编号:', eid), concat('姓名:', ename), concat('性别:', sex) from emp;

#2 查询emp表所有数据, 将ename第二个字符都换为 某
select eid, insert(ename, 2, 1, 某'), sex from emp;

#3 查询emp表所有数据, 显示ename的长度
select eid, ename, length(ename), sex from emp;

#4 查询emp表所有数据, 将 ename有英文的改为都是大写
select eid, ename, upper(ename), sex from emp;

#5 查询emp表所有数据, 将 ename有英文的改为都是小写
select eid, ename, lower(ename), sex from emp;

#6 查询emp表所有数据, ename只显示姓
select eid, ename, substring(ename, 1, 1), sex from emp;

5.1.2 数值函数

函数	描述
ABS(num)	返回num的绝对值
CEIL(num)	返回大于num的最小整数（向上取整）
FLOOR(num)	返回小于num的最大整数（向下取整）
MOD(num1, num2)	返回num1/num2的余数（取模）
PI()	返回圆周率的值
POW(num,n)/POWER(num, n)	返回num的n次方
RAND(num)	返回0~1之间的随机数
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值

​ MySQL提供的一张虚拟表中进行演示，该表名为“dual”，是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。 在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表。

select abs