mysql中where条件后边能不能用函数

转载

jiecho 2024-11-05 22:52:04

文章标签 mysql 数据库 database sql查询 ci 文章分类 MySQL 数据库

什么是Mysql？

mysql 是一个开源的关系型数据库管理系统，现在是 oracle 公司旗下的一款产品，由 C 和 C++ 语言编写，可移植性高。支持在多种操作系统上安装，最常见有AIX ， linux ， window 。 mysql 因为开源免费，所以受到了目前互联网行业的欢迎。

MySql的各个版本

MySQL Community Server 社区版本，开源免费，但不提供官方技术支持。

MySQL Enterprise Edition 企业版本，需付费，购买了之后可以电话支持

MySQL Cluster 集群版，开源免费。可将几个 MySQL Server 封装成一个 Server 。

Mysql操作语句分类

DDL 数据定义语言 (Data Defifinition Language) 例如：建库，建表

DML 数据操纵语言 (Data Manipulation Language) 例如：对表中的数据进行增删改操作

DQL 数据查询语言 (Data Query Language) 例如：对数据进行查询

DCL 数据控制语言 (Data Control Language) 例如：对用户的权限进行设置

下面以这三个表为例

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `deptnu` int NOT NULL COMMENT '部门编号',
  `dname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '部门名称',
  `addr` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`deptnu`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept部门表
-- ----------------------------
INSERT INTO `dept` VALUES (10, '研发部', '北京');
INSERT INTO `dept` VALUES (20, '工程部', '上海');
INSERT INTO `dept` VALUES (30, '销售部', '广州');
INSERT INTO `dept` VALUES (40, '财务部', '深圳');

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `empno` int NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '雇员职位',
  `mgr` int NULL DEFAULT NULL COMMENT '雇员上级编号',
  `hiredate` date NULL DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7, 2) NULL DEFAULT NULL COMMENT '薪资',
  `deptnu` int NULL DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee人员表
-- ----------------------------
INSERT INTO `employee` VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000.00, 10);
INSERT INTO `employee` VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750.00, 20);
INSERT INTO `employee` VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500.00, 30);
INSERT INTO `employee` VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500.00, 10);
INSERT INTO `employee` VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000.00, 20);
INSERT INTO `employee` VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000.00, 20);
INSERT INTO `employee` VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000.00, 30);
INSERT INTO `employee` VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500.00, 30);
INSERT INTO `employee` VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500.00, 30);
INSERT INTO `employee` VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000.00, 30);
INSERT INTO `employee` VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500.00, 30);
INSERT INTO `employee` VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500.00, 10);
INSERT INTO `employee` VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000.00, 20);
INSERT INTO `employee` VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000.00, 20);

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade`  (
  `grade` int NOT NULL COMMENT '等级',
  `lowsal` int NULL DEFAULT NULL COMMENT '最低薪资',
  `higsal` int NULL DEFAULT NULL COMMENT '最高薪资',
  PRIMARY KEY (`grade`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salgrade工资表
-- ----------------------------
INSERT INTO `salgrade` VALUES (1, 7000, 12000);
INSERT INTO `salgrade` VALUES (2, 12010, 14000);
INSERT INTO `salgrade` VALUES (3, 14010, 20000);
INSERT INTO `salgrade` VALUES (4, 20010, 30000);
INSERT INTO `salgrade` VALUES (5, 30010, 99990);

SET FOREIGN_KEY_CHECKS = 1;

1.1 Mysql查询子句之一where查询

简单查询

/*查询人员表信息*/
select * from employee; 
/*查询人员表empno,ename,job信息，并给job起别名*/
select empno,ename,job as ename_job from employee;

精确条件查询

/*查询人员为后裔的人*/
select * from employee where ename='后裔';
/*查询工资不为50000的人*/
select * from employee where sal != 50000;
/*查询工资等于50000的人！！！mysql8应该不支持这么写，<>可替换成=*/
select * from employee where sal <> 50000;
/*查询工资大于10000的人*/
select * from employee where sal > 10000;

模糊条件查询

/*ename字段林开头的都会被查询出来*/
select * from employee where ename like '林%';
/*ename字段包含林的都会被查询出来*/
select * from employee where ename like '%林%';

范围查询

/*范围查询函数BETWEEN，查询工资在10000-20000之间的信息*/
SELECT * from employee WHERE sal BETWEEN 10000 and 20000;

select * from employee where hiredate BETWEEN '2011-01-01' and '2017-12-1';

离散查询

/*in查询，查询指定内容的信息*/
SELECT * from employee WHERE ename in ('韩信','妲己')

清除重复值

/*清除字段中重复的信息，只显示一个*/
SELECT DISTINCT(job) from employee;

统计查询（聚合函数）

/*统计数据条数*/
select COUNT(*) from employee;

/*求和函数*/
select SUM(sal) from employee;

/*计算最大值*/
select * from employee where sal= (select max(sal) from employee);

/*求平均值*/
select avg(sal) from employee; 

/*计算最小值*/
select * from employee where sal = (select min(sal) from employee); 

/*concat  起到连接作用*/
select concat(ename,' 是 ', sal) as aaa from employee;

1.2Mysql查询子句之二grop by分组查询（分组）

作用：把行按字段分组

语法： group by 列 1 ，列 2.... 列 N

适用场合：常用于统计场合，一般和聚合函数连用

/*统计每个部门内有几个人*/
select deptnu,count(*) from employee group by deptnu; 

/*统计各个部门的职位及人数*/
select deptnu,job,count(*) from employee group by deptnu,job; 

/*统计各个职位的人数*/
select job,count(*) from employee group by job;

1.3Mysql查询子句之三having条件查询（筛选）

作用：对查询的结果进行筛选操作

语法： having 条件或者 having 聚合函数条件

适用场合：一般跟在 group by 之后

/*使用grop by统计完后，使用having筛选统计完后的结果*/
select job,count(*) from employee group by job having job ='文员'; 

/*查询总数大于2的数据*/
select deptnu,job,count(*) from employee group by deptnu,job having count(*)>=2; 

/*与第二个意思相同，这个增加了别名*/
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2;

1.4Mysql查询子句之三order by排序查询（排序）

作用：对查询的结果进行排序操作

语法： order by 字段 1, 字段 2 .....

适用场合：一般用在查询结果的排序

/*按照工资排序*/
select * from employee order by sal; 

/*按照日期排序*/
select * from employee order by hiredate; 

/*查询职位人数大于2的部门，并进行降序排序*/
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu desc; 

/*查询员职位人数大于2的部门，并进行生序排序*/
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu asc; 

/**/
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu; 

顺序：---- where ---- group by ----- having ------ order by

1.5Mysql查询子句之五limit限制查询（限制）

作用：对查询结果起到限制条数的作用

语法： limit n ， m n: 代表起始条数值，不写默认为 0 ； m 代表：取出的条数

适用场合：数据量过多时，可以起到限制作用

/*从第四条开始查询5条数据*/
select * from employee limit 4,5;

1.6Mysql查询之exists型子查询

exists 型子查询后面是一个受限的 select 查询语句

exists 子查询，如果 exists 后的内层查询能查出数据，则返回 TRUE 表示存在；为空则返回 FLASE 则不存在。

/*分为俩种：exists跟 not exists*/
select 1 from employee where 1=1; 
select * from 表名 a where exists (select 1 from 表名2 where 条件); 


/*查询出公司有员工的部门的详细信息*/
select * from dept a where exists (select 1 from employee b where a.deptnu=b.deptnu);
select * from dept a where not exists (select 1 from employee b where a.deptnu=b.deptnu)

1.7Mysql查询之左连接查询与右连接查询

左连接称之为左外连接右连接称之为右外连接这俩个连接都是属于外连接

左连接关键字： left join 表名 on 条件 / left outer 表名 join on 条件右连接关键字： right join 表名 on 条件 /right outer 表名 join on 条件

左连接说明： left join 是 left outer join 的简写，左 ( 外 ) 连接，左表 (a_table) 的记录将会全部表示出来，而右表 (b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为 NULL 。

右连接说明： right join 是 right outer join 的简写，与左 ( 外 ) 连接相反，右 ( 外 ) 连接，左表 (a_table) 只会显示符合搜索条件的记录，而右表(b_table) 的记录将会全部表示出来。左表记录不足的地方均为 NULL 。

/*eg:列出部门名称和这些部门的员工信息，同时列出那些没有的员工的部门 dept，employee*/
select a.dname,b.* from dept a left join employee b on a.deptnu=b.deptnu; 
select b.dname,a.* from employee a right join dept b on b.deptnu=a.deptnu;

1.8Mysql查询之内连接查询与联合查询

内连接：获取两个表中字段匹配关系的记录

主要语法： INNER JOIN 表名 ON 条件 ;

/*查出员工张飞的所在部门的地址*/
select a.addr from dept a inner join employee b on a.deptnu=b.deptnu and b.ename='张 飞'; 
select a.addr from dept a,employee b where a.deptnu=b.deptnu and b.ename='张飞';

联合查询：就是把多个查询语句的查询结果结合在一起

主要语法 1 ： ... UNION ... （去除重复）主要语法 2 ： ... UNION ALL ... （不去重复）

union 查询的注意事项：

(1) 两个 select 语句的查询结果的 “ 字段数 ” 必须一致；

(2) 通常，也应该让两个查询语句的字段类型具有一致性；

(3) 也可以联合更多的查询结果；

(4) 用到 order by 排序时，需要加上 limit （加上最大条数就行），需要对子句用括号括起来

/*对销售员的工资从低到高排序，而文员的工资从高到低排序*/
(select * from employee a where a.job = '销售员' order by a.sal limit 999999 ) union (select * from employee b where b.job = '文员' order by b.sal desc limit 999999);

练习题：

查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

涉及表： employee dept 
语句：select deptnu,count(*) from employee group by deptnu 
语句：select a.deptnu,a.dname,a.addr, b.zongshu from dept a,(select deptnu,count(*) as zongshu from employee group by deptnu) b where a.deptnu=b.deptnu;

列出薪金比安琪拉高的所有员工

涉及表：employee 
语句：select * from employee where sal > (select sal from employee where ename='安琪拉');

列出所有员工的姓名及其直接上级的姓名

涉及表：employee 
语句：select a.ename,ifnull(b.ename,'BOSS') as leader from employee a left join employee b on a.mgr=b.empno;

列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称

涉及表：employee dept
语句：select a.empno,a.ename,c.dname from employee a left join employee b on a.mgr=b.empno left join dept c on a.deptnu=c.deptnu where a.hiredate < b.hiredate;

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

涉及表：dept employee 
语句：select a.dname,b.* from dept a left join employee b on a.deptnu=b.deptnu;

列出所有文员的姓名及其部门名称，所在部门的总人数

涉及表：employee dept 
条件：job='文员' 
语句：select deptnu,count(*) as zongshu from employee group by deptnu; 语句：select b.ename,a.dname,b.job,c.zongshu from dept a ,employee b ,(select deptnu,count(*) as zongshu from employee group by deptnu) c where a.deptnu=b.deptnu and b.job='文员' and b.deptnu=c.deptnu;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。