mysql查询工具显示结果不全 mysql查询总结

转载

编程梦想翱翔者 2023-12-02 21:35:35

文章标签 mysql查询工具显示结果不全数据库 java sql 正则表达式 文章分类 MySQL 数据库

实训总结-mysql-day02

数据库查询总结
数据库脚本文件 sql.zip基础查询

# 查询常量
select 10;
select "boy";
# 查询表达式
select 2+3;
# 调用函数
select version();
select length("boy");
# 查询表中字段(*查询降低性能)
select * from employees;
# 查询指定字段
select email as "邮箱" from employees;
# 查询表中多个字段
select email,job_id from employees;
# 字段起别名
select job_id as "工号",email as "邮箱" from employees;
select job_id "工号",email "邮箱" from employees;
/**
distinct 去除重复元素，查询结果
concat 连接指定列
*/
# 去除重复数据(员工表) distinct 去除重复数据
select DISTINCT department_id from employees;
select distincat job_id from employees
# 查询数据通过concat 连接.注意连接的,号，连接字段通过指定符号
select CONCAT(email,",",job_id) out_put from employees;
select concat(email,",",employess) out_put from employees;

总结：distinct 作为查询前缀，用于去除重复元素(distinct)
concat 作为查询前缀，用于查询列的拼接(concat)
as 同于对列和表起别名 (as 可省略)

条件查询

/**
条件查询(根据条件筛选数据)
*/
# where 条件
# 查询月薪大于5000
select * from employees where salary > 5000; 
# 查询月薪等于12000
select * from employees where salary = 12000;
# 查询月薪不等于 12000
select * from employees where salary <> 12000;
# 查询指定范围的员工 (between and 范围从小到大) 
select * from employees where salary > 5000 and salary < 12000;
select * from employees where salary between 5000 and 12000;
# 查询不在指定范围的员工的姓名 not 取反
select last_name from employees where not(salary > 5000 and salary < 12000);
# 查询部门编号=90，或月薪>10000并且小于<15000
select * from employees where department_id = 90 or salary >10000 and salary < 15000;
# 随机数
SELECT CEIL(RAND()*30);
#下面的语句是否可以执行成功 
SELECT last_name , job_id , salary AS sal
FROM employees;
# 显示表departments的结构，并查询其中的全部数据 desc 表名
desc departments;
select * from  departments;
# 显示出表employees中的全部job_id(不能重复)去除重复 distinct
select distinct job_id from employees;
# 显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT 连接 concat 
SELECT concat (email,",",job_id,",",salary) from employees;
# 显示出表employees部门编号在80-100之间 的姓名、职位 (between and 注意从小到大)
SELECT first_name,job_id from employees between 80 and 100;
SELECT first_name,job_id from employees where salary >=80 and salary<= 100;
# 显示出表employees的manager_id 是 100,101,110 的员工姓名、职位 in 子句(指定范围)
select first_name,job_id from employees where manager_id in(100,101,110);
select first_name,job_id from employees where manager_id = 100 or manager_id = 101 or manager_id = 110;

总结：where 后面子句用于指定查询条件
ceil(rand()*30)用于生成随机数
and or 连接语句

模糊查询与排序查询

/**
BETWEEN AND 从小到大
IN 
OR
is null / is not null
LIKE 正则 像 _匹配任意一个字符 %匹配任意多个字符
*/
# 查询姓名中包含e的员工,%匹配任意多个字符，_任意单个字符
select * from employees where first_name like "%e%";
select * from employees where first_name like "__e%";
SELECT * from employees where first_name like "_e_a%";
# 查询第三个字符是下换线的 \下划线转义
SELECT * from employees where first_name LIKE "__\_%";
# 自定义转义 ESCAPE "$"定义转义字符
select * from employees where first_name like "__$_%" ESCAPE "$";
# between and  从小到大
SELECT * from employees where salary BETWEEN 8000 and 100000;
# in  或者替换为or语句
select * from employees where manager_id in (100,101,110);
select * from employees where job_id in("AD_VP");
# 查询没有奖金的员工
SELECT * from employees where commission_pct is NULL;
# 查询有奖金的员工
SELECT * from employees WHERE commission_pct is NOT NULL or commission_pct = " ";
# 查询工资大于12000的员工姓名与工资
SELECT first_name,salary where salary>12000;
# 查询员工号176的员工姓名和部门号，和年薪 IFNULL 判断是否为空 
/**
 IFNULL(exp1,exp2) 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值
*/
select first_name as "姓名",manager_id as "部门", salary * 12 * (1+IFNULL(commission_pct,0)) as "年薪" from employees where employee_id=176;
# 查询工资不在5000和12000的
SELECT * from  employees where  not (salary BETWEEN 5000 AND 12000); 
# 查询20号或50号部门的员工姓名与部门号
SELECT first_name,department_id from employees where department_id = 20 or department_id = 50
# 选择公司中没有管理者的员工姓名和job_id
SELECT first_name,job_id from employees where manager_id is NULL or manager_id =" ";
# 选择公司中有奖金的员工姓名，工资和级别
SELECT first_name,salary,commission_pct from employees where commission_pct is not NULL;
# 选择员工姓名第三个字母为a的 _任意匹配一个字符 %匹配任意多个字符
SELECT first_name from employees where first_name like "__a%";
# 选择员工姓名包含字母a和e的员工姓名
SELECT first_name from employees where first_name like "%e%a%" or first_name LIKE "%a%e%";
# 以e结尾的员工姓名
select first_name from employees where first_name LIKE "%e";
/**
排序查询 ORDER BY
DESC  降序 默认 desc 降序
ASC 升序 asc 升序
*/
# 按单个字段进行排序
select * from employees ORDER BY salary desc;
select * from employees ORDER BY salary asc;
# 按多个字段进行排序
SELECT * from employees ORDER BY salary desc,employee_id asc;
# 按表达式排序
SELECT salary * 12 * (1+IFNULL(commission_pct,0)) as "年薪" from employees ORDER BY salary * 12 * (1+IFNULL(commission_pct,0)) desc;
# 按别名排序
SELECT salary * 12 * (1+IFNULL(commission_pct,0)) as "年薪" from employees ORDER BY "年薪" desc;
# 按函数排序
SELECT last_name,length(last_name) as "长度" FROM employees ORDER BY LENGTH(last_name) DESC;
# 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
select first_name,department_id,salary * 12 * (1+IFNULL(commission_pct,0)) as "年薪" from employees ORDER BY "年薪"desc,first_name asc;
# 选择工资不在8000到17000的员工的姓名和工资，按工资降序
select first_name,salary from employees where not (salary between 8000 and 17000) ORDER BY salary desc;
# 查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
select * from employees where email like "%e%" ORDER BY length(email) desc,department_id asc;
# 显示当前连接的所有数据库名称
show databases;
SELECT DATABASE();
# 查询employees表中的所有数据，并按年薪升序
select * from employees ORDER BY salary * 12 * (1+IFNULL(commission_pct,0)) asc;
# 查询employee表中 ，部门编号在80到100之间的的last_name,employee_id 信息
select last_name,employee_id from employees where department_id BETWEEN 80 and 100;
select last_name,employee_id from employees where department_id >= 80 and  department_id<=100;
# 查询没有奖金，且工资小于18000的salary，last_name
select salary,last_name from employees where salary < 18000 and commission_pct is NULL or commission_pct =" ";
# 查询employees表中，job_id不为 ‘IT’或者 工资为12000的员工信息
select * from employees where job_id !="IT" or salary=12000;

总结：order by 用于排序查询 asc代表升序，desc代表降序。order by 后面可以指定多个查询条件，通过
，分割。默认排序是asc 升序。总出现在查询的最后位置
is null ，is not null 的使用
between and 子句注意，范围从小到大
in 子句查询在指定集合中
not 用于在前面否定
like( col like "%a%") 子句用于模糊筛选 %匹配任意多个字符，_匹配任意单个字符，字符转义 \
like "__$_%" ESCAPE "$" escape 用于自定义转义字符，默认为\
IFNULL(commission_pct,0) IFNULL函数用于判断元素是否为空，为空的话用后面元素替代

分组查询

/**
分组函数与查询
max min支持任何类型,忽略Null
sum avg 平均值 支持数值类型,忽略Null
支持数值类型，忽略null
count(*)
count(字段) 忽略null 值
where 子句后面不可以跟分组函数
*/
# 分组函数
select MAX(salary) as 最大工资,MIN(salary) as 最小工资,AVG(salary) as 平均工资 ,SUM(salary) as 工资总和 from employees;
SELECT COUNT(*) from employees;
/**
分组查询一般配合分组查询使用
 where 原表筛选，having 分组后筛选
*/
# 查询各部门最高工资
select MAX(salary) "最高工资",department_id from employees GROUP BY department_id;
# 查询各部门最高工资
select MAX(salary) ,department_id from employees GROUP BY department_id;
# 按照多个字段分组 (group by 后面通过并列分组字段,分割)
SELECT AVG(salary) "平均工资",job_id,department_id from employees GROUP BY job_id,department_id;
# 查询有奖金的,每个部门的最高奖金率
SELECT MAX(commission_pct),department_id from employees where commission_pct is not NULL GROUP BY department_id;
# 查询员工姓名中包含a,每个部门最低工资高于3000
SELECT first_name,MIN(salary),department_id from employees where first_name LIKE "%a%" GROUP BY department_id HAVING MIN(salary) > 3000;
# 查询电话已515开头的，工种号包含字符"T",门哥工种平均工资>5000的工种号和平均工资
select job_id,AVG(salary),phone_number from employees where phone_number like "515%" and job_id like "%T%" GROUP BY department_id HAVING AVG(salary) > 5000;
# 查询每个地区的部门个数，求个数大于2的部门个数和地区号 having 支持别名
select COUNT(*) as 个数,location_id from departments GROUP BY location_id HAVING 个数>2;
# 查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号,按照最低工资降序排序
SELECT first_name,MIN(salary) as minsalary from employees where first_name like "%a%" GROUP BY department_id ORDER BY minsalary desc;
# 查询公司员工工资的最大值，最小值，平均值，总和
select max(salary),min(salary),avg(salary),sum(salary) from employees;
# 查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序 asc 升序
select job_id ,max(salary),min(salary),avg(salary),sum(salary) from employees GROUP BY job_id ORDER BY job_id asc;
# 查询员工最高工资和最低工资的差距使用别名DIFFERENCE)
select (max(salary)-min(salary)) as DIFFERENCE from employees;
# 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
select manager_id,min(salary) as 最低工资 from employees where manager_id is NOT NULL GROUP BY manager_id HAVING 最低工资>6000;
# 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序desc(降序desc)(升序asc)
SELECT department_id,count(employee_id),avg(salary) from employees GROUP BY department_id ORDER BY avg(salary) desc;
# 选择各个job_id的员工人数
select count(*) ,job_id from employees GROUP BY job_id ORDER BY count(*) asc;

常见分组函数：(只能在分组查询中使用不能作为where 子句的条件)
min max ：支持任何类型数据，忽略列中的NULL
avg(均值) sum ：支持数值类型，忽略列中的NULL
count(*): 查询数据条数，推荐使用因为底层做了sql优化
count(字段)：忽略null 值
having: 子句出现在group by 子句后面，用于分组后条件筛选，分组前可以通过where 子句进行条件筛选。ordey by 子句出现在分组查询之后

等值连接查询

/**
连接查询
笛卡尔积(行 * 列)
传统模式 等值连接 表顺序无要求
join 左右连接，左右外联
可以为表起别名，结合基础查询使用
*/
# 查询员工名和部门名 等值连接(表起别名)
SELECT e.last_name,d.department_name from employees as e,departments as d where e.department_id=d.department_id;
# 查询 工资>5000的工种名和员工名、工资
select e.last_name,e.salary,j.job_title from employees as e,jobs as j where e.job_id=j.job_id and e.salary>5000;
# 查询每个部门的员工个数和部门名
SELECT d.department_name,COUNT(*) as  人数 from employees as e,departments as d where e.department_id=d.department_id GROUP BY d.department_id ORDER BY 人数 desc;
# 查询每个部门的员工个数和部门名 员工个数大于5，按照人数降序排列 desc
SELECT d.department_name,COUNT(*) as  人数 from employees as e,departments as d where e.department_id=d.department_id GROUP BY d.department_id HAVING 人数>5 ORDER BY 人数 desc;
# 查询员工名、部门名和所在城市
SELECT e.last_name,d.department_name,l.city from employees as e,departments as d,locations as l where e.department_id=d.department_id and d.location_id=l.location_id;
# 显示所有员工的姓名，部门号和部门名称。
select e.last_name,e.department_id,d.department_name from employees as e,departments as d where e.department_id=d.department_id;
# 查询90号部门员工的job_id和90号部门的location_id
select e.job_id,e.last_name,d.location_id from employees as e,departments as d where e.department_id=90 and e.department_id=d.department_id;
# 选择所有有奖金的员工的last_name , department_name , location_id , city 
select employees.last_name,departments.department_name,locations.location_id,locations.city  from employees ,departments ,locations  where employees.commission_pct is not NULL and employees.department_id=departments.department_id and departments.location_id=locations.location_id;
# 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 
select employees.last_name,employees.job_id,departments.department_id,departments.department_name from employees,departments,locations where employees.department_id=departments.department_id and departments.location_id=locations.location_id and locations.city="Toronto";
# 查询每个工种、每个部门的部门名、工种名和最低工资
SELECT   d.department_name,    j.job_title,        MIN(e.salary)   最低工资
FROM   departments d ,    employees e,    jobs j
WHERE e.`department_id`=d.`department_id` AND e.`job_id`=j.`job_id`
GROUP BY e.`department_id`,e.`job_id`;
# 查询每个国家下的部门个数大于2的国家编号
select l.country_id,count(*) from departments as d,locations as l where d.location_id = l.location_id GROUP BY l.country_id HAVING count(department_id) > 2;