MySQL学习-简单查询、条件查询、排序查询、分组查询

标签: MySQL学习  数据库  mysql  sql

用到的表结构

employees表

在这里插入图片描述

departments表

在这里插入图片描述

jobs表

在这里插入图片描述

locations表

在这里插入图片描述

job_grades表

在这里插入图片描述

简单查询

    语法:SELECT 查询列表 FROM 表名
    特点:1.查询列表可以是:表中的字段常量值表达式函数
                2.查询的结果是一个虚拟的表


#1.查询表中单个字段

SELECT last_name FROM employees;

#2.查询表中多个字段

SELECT last_name,salary,email FROM employees;

#3.查询表中所有字段

SELECT * FROM jobs;  #顺序与表中的字段一样

SELECT job_id,job_title,max_salary,min_salary FROM jobs;

#当字段名和关键字重名,使用 `` (1左边那个)将字段名隔开

SELECT `last_name` FROM employees;

#4.查询常量值
#SQL不区分字符和字符串,所有字符用 ‘’(单引号)

SELECT 'abc';

#5.查询表达式

SELECT 100*2/50%3;   #1.0000

#6.查询函数

SELECT DATABASE();

#7.起别名 关键字 AS(可以省略)
#1.提高可读性。
#2.如果查询的字段有重名的情况,使用别名可以区分开来

SELECT 100*2/50%3 AS 结果;
SELECT first_name AS,last_name ASFROM employees;

#如果别名有关键字,使用""

SELECT last_name AS "SELECT" FROM employees;

#8.去重 关键字 DISTINCT
#查询与员工表中涉及的所有部门编号

SELECT DISTINCT department_id FROM employees;

#9.+ 号的作用在MySQL只是运算符
#两个操作数都为数值型,则作加法运算

SELECT 100+50;   #150

#只要其中一个为字符型,则试图将字符型数值转换为数值型
        如果转换成功则作加法运算
        如果转换失败则将字符型数值转换为0

SELECT 100+'50';   #150
SELECT 100+'abc';   #100

#只要有一个是NULL,结果为NULL

SELECT NULL+50;   #null

#IFNULL(可能为NULL的值,要返回的值)

SELECT CONCAT("奖金率",IFNULL(commission_pct,0)) AS "RESULT" FROM employees;

#案例:查询与员工的名和姓连接成一个字段,并显示姓名

SELECT CONCAT(first_name,' ',last_name) AS 姓名 FROM employees;

条件查询

语法:SELECT 查询列表-----3
            FROM 表名---------------1
            WHERE 筛选条件------2
分类:1.按条件表达式筛选
                    条件表达式:>   <   =   !=   <>   <=   >=
            2.按逻辑表达式筛选
                    逻辑运算符(连接条件表达式):AND OR NOT && || !
                    模糊查询:LIKE:一般与通配符搭配使用,除了支持字符型还支持数值型
                                                通配符:%:任意多个字符,包含0个字符
                                                                 _:单个字符
                                        BETWEEN AND:在…之间,包含临界值,临界值不能交换顺序
                                        IN:判断某字段的值是否属于IN列表中的某一项,列表中的值必须一致或兼容。且列表的值不可以使用通配符
                                        IS [NOT] NULL:判断是否为NULL
                                        <=>(少用):可以判断NULL值,=不能判断NULL值


#一、按条件表达式筛选

#查询工资>12000的员工信息

SELECT * FROM employees WHERE salary > 12000;

#查询部门编号不等于90号的员工名和部门编号

SELECT CONCAT(first_name,' ',last_name) AS "NAME",department_id FROM employees WHERE department_id <> 90;

#二、按逻辑表达式筛选

#查询工资在10000~20000的员工信息

SELECT * FROM employees WHERE salary>=10000 AND salary<=20000;

#查询部门编号不是在90~~110之间且工资大于15000的员工信息

SELECT * FROM employees WHERE 
	NOT (department_id>=90 AND department_id<=110) AND salary>15000;

#三、模糊查询

#LIKE
#查询员工名中包含a的员工信息

SELECT * FROM employees WHERE last_name LIKE '%a%';

#查询员工名中第三个字符为e,第五个字符为l的员工信息

SELECT * FROM employees WHERE last_name LIKE '__n_l%';

#查询员工名中第二个字符为_的员工信息

SELECT * FROM employees WHERE last_name LIKE '_\_%';

#转义字符可以是任意的,需要加上ESCAPE

SELECT * FROM employees WHERE last_name LIKE '_?_%' ESCAPE '?';

#查询员工号是200及以上的员工

SELECT * FROM employees WHERE employee_id LIKE '2__';

#BETWEEN AND
#查询工资在10000~20000的员工信息

SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;

#IN
#查询员工的工种编号是IT_PROG、AD_VP、AD_PRES

SELECT * FROM employees WHERE job_id='IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES';

SELECT * FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');

#IS NULL
#查询没有奖金率的员工信息

SELECT * FROM employees WHERE commission_pct IS NULL;

SELECT * FROM employees WHERE commission_pct <=> NULL;

#查询员工号为176的员工的姓名和部门名和年薪

SELECT last_name,employee_id,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS salary
		FROM employees WHERE employee_id=176;

排序查询

    语法:SELECT 查询列表 FROM 表 [WHERE 筛选条件] ORDER BY 排序
    升序:ASC(默认) 降序:DESC
    ORDER BY支持单个或多个字段、表达式、别名、函数
    ORDER BY一般放在查询语句的最后面,limit字句除外


#查询员工信息,要求工资从高到低排序

SELECT * FROM employees ORDER BY salary DESC;

#查询部门编号>=90的员工信息,按入职时间先后进行排序

SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;

#按年薪的高低显示员工信息(按表达式排序)

SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
		FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#按年薪的高低显示员工信息(按别名排序)

SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
		FROM employees ORDER BY 年薪 DESC;

#按姓名的长度显示员工的姓名和工资(按函数排序)

SELECT last_name,LENGTH(last_name) FROM employees ORDER BY LENGTH(last_name) DESC;

#查询员工信息,要求先按工资升序,再按员工编号降序(按多个字段排序)

SELECT last_name,salary,employee_id FROM employees ORDER BY salary ASC,employee_id DESC;

#查询员工的姓名和部门编号和年薪,按年薪降序,按姓名升序

SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
		FROM employees ORDER BY 年薪 DESC,last_name ASC;

#选择工资不在8000~17000的员工的姓名和工资,按工资降序

SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;

#查询邮箱中包含e的员工信息,并按邮箱的字节数降序,再按部门编号升序

SELECT *,LENGTH(email) AS LENGTH FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC;

分组查询

语法:SELECT 分组函数,列(要求出现在group by的后面)-----5
            FROM 表---------------------------------------------------------------------1
            [WHERE 筛选条件]-------------------------------------------------------2
            GROUP BY 分组的列表------------------------------------------------3
            HAVING 分组后筛选-----------------------------------------------------4
            [ORDER BY 子句]--------------------------------------------------------6
    1. SELECT过滤列,WHERE过滤行,HAVING过滤分组
    2. WHERE过滤分组前,HAVING过滤分组后
    3. 分组函数做条件肯定是放在HAVING子句中,分组函数不可以出现在WHERE
    4. 能用分组前筛选就优先使用分组前筛选
    5. GROUP BY子句可以支持单个或多个字段分组(多个字段之间没有顺序要求
    6. GROUP BY子句多字段在第一个字段分组后的基础上按第二个字段分组


#查询每个工种的最高工资

SELECT job_id,MAX(salary) FROM employees GROUP BY job_id;

#查询每个部门的员工个数

SELECT department_id,COUNT(*) FROM employees GROUP BY department_id;

#查询邮箱中含有’a’字符的,每个部门的平均工资

SELECT department_id,AVG(salary) FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

#添加分组后的筛选条件

#查询那些部门员工个数大于2

SELECT department_id,COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*)>2;

#查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资

SELECT job_id,MAX(salary) FROM employees WHERE commission_pct IS NOT NULL
		GROUP BY job_id HAVING MAX(salary)>12000;

#查询领导编号大于102的每个领导手下员工的最低工资大于5000的领导编号,以及其最低工资

SELECT manager_id,MIN(salary) FROM employees WHERE manager_id>120
		GROUP BY manager_id HAVING MIN(salary)>5000;

#按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数大于5的有哪些

SELECT LENGTH(last_name) AS 长度,COUNT(*) FROM employees GROUP BY 长度 HAVING COUNT(*)>5;

#按多个字段分组

#查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;

#添加排序

#查询每个部门每个工种的员工的平均工资大于10000的有哪些,并且按平均工资的高低显示

SELECT AVG(salary),department_id,job_id FROM employees
		GROUP BY department_id,job_id HAVING AVG(salary)>10000 ORDER BY AVG(salary) DESC;

#查询所有部门的编号,员工数量和工资平均值,并按平均工资降序

SELECT department_id,COUNT(*),AVG(salary) FROM employees
		GROUP BY department_id ORDER BY AVG(salary) DESC;

#查询各个job_id的员工个数

SELECT job_id,COUNT(*) FROM employees GROUP BY job_id;

    

版权声明:本文为H_X_P_原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/H_X_P_/article/details/105329647