Mysql查询语句

动力节点学习笔记

一个完整的SQL询句如下：
select
      xxxx
from
    xxxx
where
     xxxx
group by
      xxxx
having
      xxxx
order by
      xxxx
以上关键字的顺序不能变，严格遵守
以上询句的执行顺序：
1） from 将硬盘上的表文件加载到内存
2） where:将符合条件的数据行摘取出来。生成一张新的临时表
3） group by :根据列中的数据种类，将当前临时表划分成若干个新的临时表
4） having : 可以过滤掉group by生成的不符合条件的临时表
5） select : 对当前临时表迚行整列读取
6） order by : 对select生成的临时表，进行重新排序，生成新的临时表
7) limit : 对最终生成的临时表的数据行，进行截取。

select * from emp;
create database zhoufang;
use zhoufang;// use 数据库名称
show databases;
show tables;//查看当前数据库中的表
use bjpowernode;
show tables;
desc dept;
desc emp;
SELECT database();//查看当前使用的数据库
select version();
show tables from yanwei;//查看其它数据库中的表
SELECT database();
show CREATE table dept;
SELECT ENAME FROM EMP;
SELECT empno as '编号',ename '姓名',sal*12 as yearsal from emp;
select empno,ename,sal from emp where sal =5000;
DEsC emp;
select empno,ename,job from emp where job= MANAGER;//错误
select empno,ename,job from emp where JOB = 'MANAGER';
select empno,ename,sal from emp where sal <> 5000;//查询薪水不等于5000的员工
select empno,ename,sal from emp where sal != 5000;//第二种方式
select empno,ename,sal from emp WHERE SAL >=1600 and sal <=3000;
select empno,ename,sal from emp WHERE SAL BETWEEN 1600 and 3000;//包含最大值和最小值
select empno,ename,sal from emp where ename BETWEEN 'A' and 'F';//用在字符上区间为：前闭后开；
//Null 为空，它不是一个数值,不是一个空串，为null可以设置这个字段不填值，如果查询为null的字段，采用is null。
select empno,ename,comm from emp where comm is null;
select empno,ename,comm from emp where comm = null;//无法查诟出符合条件的数据，因为null类型比较特殊，必须使用 is 来比较
select empno,ename,comm from emp where comm is not null;
select empno,ename,job,sal FROM emp WHERE job='MANAGER' and SAL>2500;
select empno,ename,job from emp where job='manager' or job='SALESMAN';//and的优先级高于or
//查询薪水大于1800并且部门编号为20或30的员工
select * from emp where sal>1800 AND deptno =20 or deptno=30;//过滤了 sal > 1800 and deptno = 20，然后再将deptno = 30的员工合并过来，所以是不正确的。
select * from emp where sal>1800 AND deptno =20 or (sal>1800 and deptno=30);
//查询出Job为 SALESMAN 和 Job为 MANAGER 的员工
select ename,job from emp where job='MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in ('MANAGER', 'SALESMAN');
//查询出薪水为1600和3000的员工
select ename,sal from emp WHERE sal in(1600,3000);
//查询出薪水不是1600和3000的员工
select ename,sal from emp where sal not in (1600,3000);
select ename,sal from emp where sal <>1600 and sal<>3000;
select ename,sal from emp where not(sal=1600 or sal=3000);
//查询以姓名以 M 开头的所有员工'M%'
select ename from emp where ename like 'M%';
//查询以姓名以N结尾的所有员工'%N'
select ename from emp where ename like '%N';
//查询姓名中包含O的所有员工‘ %O%’
select ename from emp where ename like '%O%';
//查询姓名中第二个字符为A的所有员工‘ _A%’
select ename from emp where ename like '_A%';
//查询姓名中第三个字符为R的所有员工姓名‘ __R%’
select ename from emp where ename like '__R%';

/********************数据排序 asc、 desc*********************
排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采
用逗号间隔，order by默认采用升序（asc），如果存在 where 子句，那么 order by 必须放到
where 询句后面。*/
/*按照薪水由小到大排序（系统默认由小到大）*/
select ename,sal from emp where order by sal;/*错误*/
select ename,sal from emp order by sal;
select ename,job,sal from emp where job = 'MANAGER' order by sal;
/*手动指定按照薪水由小到大排序（升序关键字 asc）*/
select ename,sal from emp order by sal asc;
/*手动指定按照薪水由大到小排序（降序关键字desc）*/
select ename,sal from emp order by sal desc;
/*按照 job 和薪水倒序排序*/
select ename,job,sal from emp order by job desc,sal desc;
/*使用字段位置排序*/
select * FROM emp order by 6;

/***************************处理函数***************************/
/*
Lower 转换小写
upper 转换大写
substr 取子串（substr(被截取的字符串,起始下标,截取的长度)）
length 叏长度
trim 凼数去除首尾空格，不会去除中间空格  ；trim(字符串)
str_to_date 将字符串转换成日期
date_format 格式化日期
format 设置千分位
round 四舍五入
rand() 生成随机数
Ifnull 可以将null转换成一个具体值
*/
/*查询员工姓名，将员工姓名全部转换成小写*/
select lower(ename) AS ename from emp;
/*查询并显示所有员工姓名的第二个字母*/
select SUBSTRING(ename,2,1) from emp;
/*查询员工姓名中第二个字母为A的所有员工*/
select ename from emp where substr(ename, 2,1)='A';
/*取得员工姓名长度*/
select ename,length(ename) as namelength from emp;
/* ifnull(字段名,替换值)函数：空值处理凼数*/
select ename,ifnull(comm,0) from emp;/*查诟员工姓名及补助，如果补助为Null设置为0；*/
/*在数据库中，有Null参与数学运算的结果一定为Null；为了防止计算结果出现Null，
建议先使用ifnull函数预先处理。*/
/*查询员工薪水与补助的和*/
select sal+comm from emp;/*错误写法*/
select sal + ifnull(comm,0) from emp;
/*没有补助的员工，将每月补助100，求员工的年薪*/
select ename,(sal+ifnull(comm, 100))*12 as '年薪' from emp;
/*case…when…then…else…end*/
/*
匹配工作岗位，当为 MANAGER时，薪水上调 10%，当为
SALESMAN时，薪水上调50%，其它岗位薪水不变
case job
     when 'MANAGER' then sal*1.1
     when 'SALESMAN' then sal*1.5
     else sal
end
*/
select ename,sal,job,
       (case job
         when 'MANAGER' then sal*1.1
         when 'SALESMAN' then sal*1.5
         else sal
         end) as newsal from emp;
/*取得工作岗位为manager的所有员工*/
select ename,job from emp where job=trim('   MANAGER   ');
/*保留整数位或不保留小数位：select round(125.18)；或者
select round(125.18,0);*/
/*保留1位小数：*/
select round(125.18,1);
/*个数位四舍五入：select round(125.18,-1);*/
/*生成一个 0≤ v ≤ 1.0的随机数;*/
select RAND();
/*生成一个0-100的随机数*/
select round(rand()*100);
/*将'日期字符串’转换为'日期类型'数据 ;用法：str_to_date('日期字符串','日期格式')*/
/*查询出1981-12-03入职的员工*/
select ename,hiredate from emp where hiredate = '1981-12-03';/*MySQL默认日期格式：%Y-%m-%d*/
select ename,hiredate from emp where hiredate = str_to_date('02-20-1981','%m-%d-
%Y');
/*str_to_date函数通常使用在插入操作中；字段DATA类型，不接收varchar类型，需要先通过
该函数将varchar变成data再插入数据*/
/*
作用：将'日期类型'转换为特定格式的'日期字符串'类型
用法： date_format(日期类型数据,'日期格式')
*/
/*查询员工的入职日期，以’10-12-1980’的格式显示到窗口中；*/
select ename,date_format(hiredate, '%m-%d-%Y') hiredate from emp;
/*查询员工的入职日期，以‘ 10/12/1980’的格式显示到窗口中；*/
select ename,date_format(hiredate, '%m/%d/%Y') hiredate from emp;
/*下面两句效果一样，默认采用%Y-%m-%d格式*/
select ename,hiredate from emp;
select ename,date_format(hiredate, '%Y-%m-%d') hiredate from emp;

/************分组函数聚合函数/多行处理函数*****************/
/*
单行函数都是一行输入对应一行输出
多行处理函数都是多行输入对应一行输出
 分组函数自动忽略空值，不需要手动增加where条件排除空值；
 分组函数不能直接使用在where关键字后面；*/

/*作用：求某一列的和，null会自动被忽略；
用法： sum(字段名称)*/
select sum(sal) from emp;/*取得薪水的合计*/
/*取得总共薪水（工资+补助）合计*/ 
select sum(sal+comm) from emp; /*错误*/
/*comm字段有 null 值，在多列进行运算时，只要有 null参与的数学运算结果都为 null*/
select sum(sal+ifnull(comm,0)) as sal from emp;

/*作用：求某一列的平均值，null会被自动忽略
用法：avg(字段名称)*/
select avg(sal) from emp;/*取得平均薪水*/

/*作用：取得某一列的最大值
用法：max(字段名称)*/
select MAX(sal) from emp;/*取得最高薪水max(sal)*/

/*作用：取得某一列最小值
用法：min(字段名称)*/
select min(sal) from emp;/*取得最低薪水max(sal)*/

/*作用：取得某字段值不为null的记录总数
用法：count(字段名称) 或count(*)
1、 count(*)表示取得当前查询表所有记录
2、 count(字段名称)，不会统计为 null 的记录*/
select COUNT(empno) from emp;
select COUNT(*) from emp;/*取得所有员工数count(*)*/
select COUNT(comm) from emp;/*取得补助不为空的所有员工数count(comm)*/
select count(*) from emp where comm is null;
select count(*), sum(sal),max(sal),min(sal) from emp;/*聚合函数都放到 select 中一起使用*/

/*
distinct 去除重复记录
作用：将查询结果中某一字段的的重复记录去除掉
用法：distinct 字段名或distinct 字段名1, 字段名2 … …
distinct 字段名A：去除与字段名A相同的记录
distinct 字段名A,字段名B：去除与字段名A和字段名B同时相同的记录
注意：distinct叧能出现在所有字段最前面，后面如果有多个字段及为多字段联合去重*/

SELECT distinct job from emp;/*查询该公司有哪些工作岗位*/
SELECT count(distinct job) from emp;/*查询该公司工作岗位数量*/
select DISTINCT deptno,job from emp;/*去除部门编号deptno和工作岗位job重复的记录*/

/*
分组查询：group by
作用：通过哪个或哪些字段进行分组
用法：group by 字段名称
*/
select job ,max(sal) from emp GROUP by job;/*找出每个工作岗位的最高薪水*/
/*计算每个工作岗位的最高薪水，并且按照由低到高迚行排序*/
select job ,max(sal) as maxsal from emp GROUP by job ORDER by maxsal asc;
/*order by 必须放到 group by后面；*/
/*计算每个部门的平均薪水*/
SELECT deptno,avg(sal) from emp group by deptno;/*首先，按照部门编号分组，然后对每一组的薪水求平均值*/
/*计算出不同部门不同岗位的最高薪水*/
SELECT deptno,job,max(sal) from emp group by deptno;/**错误*/
SELECT deptno,job,max(sal) from emp group by deptno,job;/*把deptno和job看成一个字段进行联合字段分组*/
select job,max(sal) from emp where job <>'MANAGER' group by job;/*找出每个工作岗位的最高薪水，除MANAGER以外*/

/*如果想对分组的数据再进行过滤，需要使用having子句；*/
/*找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的；*/
select job,avg(sal)as avg1 from emp where avg(sal) >2000 group by job;/*错误*/
/*where关键字后面不能直接使用分组含税，这与SQL询句的执行顺序有关系，它会先执行
from emp，然后再进行where条件过滤，where条件过滤结束之后再执行 group by 分组,之后才会
显示出查诟结果。*/
select job,avg(sal) from emp group by job having avg(sal) > 2000;

/*
 where 和 having 都是为了完成数据的过滤，它们后面都是添加条件；
2） where 是在 group by之前完成过滤；
3） having 是在 group by之后完成过滤；
*/