MySQL学习第三天 分组函数 连接查询

SQL 文件  员工.SQL见第一天

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `girls`;

/*Table structure for table `admin` */

DROP TABLE IF EXISTS `admin`;

CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table `admin` */

insert  into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');

/*Table structure for table `beauty` */

DROP TABLE IF EXISTS `beauty`;

CREATE TABLE `beauty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `sex` char(1) DEFAULT '女',
  `borndate` datetime DEFAULT '1987-01-01 00:00:00',
  `phone` varchar(11) NOT NULL,
  `photo` blob,
  `boyfriend_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `beauty` */

insert  into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);

/*Table structure for table `boys` */

DROP TABLE IF EXISTS `boys`;

CREATE TABLE `boys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boyName` varchar(20) DEFAULT NULL,
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `boys` */

insert  into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

分组函数，连接查询不同表

#分组函数
/*
功能：用作统计使用

分类 sum求和 avg平均值  max最大值  min最小值， count 计算个数

*/

#1 简单使用
SELECT SUM(`salary`) ,AVG(`salary`),MAX(`salary`),MIN(`salary`),COUNT(`salary`)FROM `employees`

#2 参数支持的类型
SELECT SUM(`last_name`) ,AVG(`last_name`),MAX(`last_name`),MIN(`last_name`),COUNT(`last_name`)FROM `employees`
#3 sum 和avg 都会计算都会忽略null值 

#4 和 distinct 实现去重运算
SELECT SUM(DISTINCT `salary`),SUM(`salary`) FROM `employees`
SELECT COUNT(DISTINCT `salary`),COUNT(`salary`)FROM `employees`

#count 函数详细介绍
SELECT COUNT(*) FROM `employees`
SELECT COUNT(1) FROM `employees`

#分组查询
/*
select 分组函数，列 from 表 where 表达式 GROUP BY 字句
特点：
  1分组查询的筛选条件分为两类
    分度前筛选
    分组后筛选
  2 group by 子句支持单个字段分组，多个字段分组  
  3 支持排序
*/

#查询每个工种的最高工资
SELECT MAX(`salary`),`job_id` FROM `employees` GROUP BY `job_id`
#查询每个位置的部门个数
SELECT COUNT(*),`location_id` FROM `departments` GROUP BY `location_id`
#查询邮箱中包含a字符的每个部门的平均工资
SELECT AVG(`salary`),`department_id` FROM `employees` WHERE `email` LIKE "%a%" GROUP BY `department_id`
#查询有奖金的每个领导手下员工的最高工资
SELECT MAX(`salary`),`manager_id` FROM `employees` WHERE !ISNULL(`commission_pct`) GROUP BY `manager_id`

#添加复杂的筛选机制
#查询哪个部门的员工个数>2     先查每个部门的员工数再查大于2的
SELECT COUNT(*),`department_id`
FROM `employees`
GROUP BY `department_id` 
HAVING COUNT(*)>2

#按表达式或函数分组
#按员工姓名的长度分组，查询每组员工的个数，筛选员工个数大于8大有哪些
SELECT COUNT(*),LENGTH(`last_name`) FROM `employees` GROUP BY LENGTH(`last_name`) HAVING COUNT(*)>8


#按多个字段分组

#查询每个部门每个工种的员工的平均工资 并且按平均工资高低显示
SELECT AVG(`salary`),`department_id`,`job_id` 
FROM `employees` 
GROUP BY `department_id`,`job_id` 
ORDER BY  AVG(`salary`) DESC
 
 
 ########################################################
 #连接查询
 /*
 又叫多表查询当查询的数据来自多个表时，就会用到连接查询。
 笛卡尔乘积现象 表1 有m行 表2有n行 结果m*n'行
   原因：没有有效的连接条件
   应该添加有效的连接条件
   
 分类：
    按功能分类
      内连接
         等值连接
         非等值连接
         自连接
      外连接
         左外连接
         右外连接
         全外连接
      交叉连接
 
 
 */
 SELECT * FROM `beauty`

 SELECT * FROM `boys`
 

 
 #一，sql92标准
 #1，等值连接

 # 查询女名对应的男名
  SELECT `name`,`boyName` FROM `beauty`,`boys`
 WHERE `beauty`.`boyfriend_id`=`boys`.`id`
 
 #查询部门名对应的员工名
 SELECT `department_name`,`last_name`
 FROM `departments`,`employees`
 WHERE `departments`.`department_id`=`employees`.`department_id`

 
 /*
 为表起别名
 ①提高语句的简洁的
 ②区分多个重名的字段
 
 注意：如果为表齐了别名，查询也一定要用别名限定
 */
 
 #查询城市名中第二个字符为o的部门名和城市名
SELECT `city`,`department_name`
FROM `departments`AS d,`locations`AS l
WHERE `city` LIKE "_o%" AND d.`location_id`=l.`location_id`
 
 #加分组查询
 #查询每个城市的部门个数
 SELECT `city`, COUNT(*) 
 FROM `locations` AS l,`departments` AS d 
 WHERE d.`location_id`=l.`location_id`
 GROUP BY l.city
 #三表连接 查询部门名 员工名 城市名
 SELECT `department_name`,`last_name`,`city`
 FROM `departments` d,`employees` e ,`locations` l
 WHERE  d.`location_id`=l.`location_id`AND d.`department_id`=e.`department_id`
 
 #2 非等值连接
 #查询员工的工资和工资级别
 SELECT `salary`,`grade_level`
 FROM `employees`,`job_grades`
 WHERE `employees`.`salary` BETWEEN `job_grades`.`lowest_sal` AND `job_grades`.`highest_sal`
 ORDER BY `salary` ASC
 
 #3 自连接 用别名
 # 查询员工名和其上级的名称
 SELECT e.`last_name`,e.`employee_id`,m.`last_name`,m.`employee_id`
 FROM `employees` AS e,`employees` AS m
 WHERE e.`manager_id`=m.`employee_id`

测试习题

 

SELECT MAX(`salary`),AVG(`salary`)
FROM `employees`


SELECT `employee_id`,`job_id`,`last_name`
FROM `employees`
ORDER BY `department_id` DESC ,`salary` ASC

SELECT `job_id`
FROM `employees`
WHERE `job_id` LIKE "%a%e%"

SELECT s.name,g.name,r.scorre
FROM student s,grade g,result r
WHERE s.id=r.studentNO AND  g.id - s.gradeif

SELECT NOW()
SELECT TRIM();
SELECT SUBSTR(str,pos);
SELECT SUBSTR(str,pos,len);