3.1、对DQL简化补充

目录

01、创建表：学生表(stu)、雇员表(emp)

02、创建学生表(stu)、雇员表(emp) 完成数据查询操作

---

01、创建表：学生表(stu)、雇员表(emp)

#创建数据库myStudent
CREATE DATABASE IF NOT EXISTS `myStudent`;
#创建学生表：stu
CREATE TABLE stu (
	sid       CHAR(6),
	sname     VARCHAR(50),
	age       INT,
	gender    VARCHAR(50)
);
#添加数据：向学生表stu中添加数据
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
INSERT INTO stu VALUES('S_1012', NULL, 10, 'female');


#创建雇员表：emp
CREATE TABLE emp(
	empno      INT,
	ename      VARCHAR(50),
	job        VARCHAR(50),
	mgr        INT,
	hiredate   DATE,
	sal        DECIMAL(7,2),
	comm       DECIMAL(7,2),
	deptno     INT
) ;
#添加数据：向雇员表emp中添加数据
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,200,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

02、创建学生表(stu)、雇员表(emp) 完成数据查询操作

#1.基础查询
#1.1 查询stu表中的所有字段(-->即查询所有列)
SELECT `sid`,`sname`,`age`,`gender` FROM stu;
SELECT * FROM stu;   #当字段太多，查询所有字段，其中*表示所有
#1.2 查询学生表stu中的指定列:如查询sid、sname列
SELECT `sid`,`sname` FROM stu;

#2.条件查询
/*2.1 条件查询介绍
      条件查询就是在查询时给出where子句，在where子句中使用如下运算符及关键字：
     -->:  = != <> < <= > >=
     -->: between...and
     -->: IN(set)
     -->: IS NULL
     -->: AND
     -->: OR
     -->: NOT
*/
##查询某某记录=查询某一条数据的所有记录
#2.2 查询性别为女，并且年龄大于50的记录
SELECT * FROM `stu` WHERE `gender`='female' AND `age`>50;
#2.3 查询性别为女，并且年龄大于50的员工编号和姓名
SELECT `sid`,`sname` FROM `stu` WHERE `gender`='female' AND `age`>50;
#2.4 查询学号为S_1001,或者姓名为lisi的记录
SELECT * FROM stu WHERE `sid`='S_1001' OR `sname`='lisi';
#2.5 查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE `sid`='S_1001' OR `sid`='S_1002' OR `sid`='S_1003';
SELECT * FROM stu WHERE `sid` IN('S_1001','S_1002','S_1003');
#2.6 查询学号不是S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE `sid`!='S_1001' AND `sid`!='S_1002' AND `sid`!='S_1003';
SELECT * FROM stu WHERE sid NOT IN('S_1001','S_1002','S_1003');
#2.7 查询年龄为null的记录
SELECT * FROM stu WHERE `age` IS NULL;
#2.8 查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE `age`>=20 AND `age`<=40;
SELECT * FROM stu WHERE `age` BETWEEN 20 AND 40;
#2.9 查询性别非男的学生记录
SELECT * FROM stu WHERE `gender`='female';
SELECT * FROM stu WHERE `gender`!='male';
SELECT * FROM stu WHERE `gender`<>'male';
SELECT * FROM stu WHERE NOT `gender`='male';
#2.10 查询姓名不为null的学生记录
SELECT * FROM stu WHERE `sname` IS NOT NULL;
SELECT * FROM stu WHERE NOT `sname` IS NULL;


#3.模糊查询---LIKE
/*
   当想查询姓名中包含a字符的学生时就需要使用模糊查询了。
   模糊查询需要使用关键字：LIKE
     通配符：
        _ 表示任意一个字符
        % 表示任意0~n个字符
*/
#3.1 查询姓名由5个字母构成的学生记录
SELECT * FROM stu WHERE `sname` LIKE '_____';
#3.2 查询姓名由5个字母构成，并且第5个字母为"i"的学生记录
SELECT * FROM stu WHERE `sname` LIKE '____i';
#3.3 查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE `sname` LIKE 'z%';
#3.4 查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE `sname` LIKE '_i%';
#3.5 查询姓名中包含“a”字母的学生记录
SELECT * FROM stu WHERE `sname` LIKE '%a%';

#4.字段控制查询
#4.1 去除重复记录:DISTINCT 
SELECT DISTINCT sal FROM emp;
#4.2 查看雇员的月薪与佣金之和   注意: 一个数与NULL进行运算，结果为NULL
/*(1)
     因为sal和comm两列的类型都是数值类型，所以可以做加运算。
     如果sal或comm中有一个字段不是数值类型，那么会出错。
     例如：将会数据出错
	SELECT *,sal+comm FROM emp;
     注意： 一个数与NULL进行运算，结果为NULL
  (2)
     comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，
     所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：
*/
SELECT *,sal+IFNULL(comm,0) FROM emp;
#4.3 给列名添加别名，使用As关键字   As关键字可以省略不写
SELECT *,sal+IFNULL(comm,0) AS '月薪与佣金之和' FROM emp;
SELECT *,sal+IFNULL(comm,0) '月薪与佣金之和' FROM emp;

#5.排序 ORDER BY   ASC升序(默认排序)  DESC降序
#5.1 查询所有学生记录，按年龄升序排序
SELECT * FROM stu ORDER BY `age` ASC;
#5.2 查询所有学生记录，按年龄降序排序
SELECT * FROM stu ORDER BY `age` DESC;
#5.3 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT * FROM emp ORDER BY `sal` DESC,`empno` ASC;
#5.4 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号降序排序
SELECT * FROM emp ORDER BY sal DESC,empno DESC;
#5.5 查询工资高于1600的员工记录，并按照薪资进行降序排序
SELECT * FROM emp WHERE sal>1600 ORDER BY sal DESC;

#6.聚合函数
/*
  聚合函数用来做纵向运算的函数
  （1）COUNT()：统计指定列不为NULL的记录行数
  （2）MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
  （3）MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
  （4）SUM():计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0;
  （5）AVG():计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0;
*/
#（1）COUNT()：统计指定列不为NULL的记录行数
SELECT COUNT(comm) AS '有佣金的人数' FROM emp;
SELECT COUNT(mgr) AS '有领导的员工数目' FROM emp;
SELECT COUNT(*) AS '数据数目' FROM emp;
#（2）MAX():计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
SELECT MAX(sal) AS '最高工资' FROM emp;
#（3）MIN():计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算;
SELECT MIN(sal) AS '最低工资' FROM emp;
#（4）SUM():计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0;
SELECT SUM(sal) AS '员工工资之和' FROM emp;
#（5）AVG():计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0;
SELECT AVG(sal) AS '员工平均工资' FROM emp;

#查询emp表中月薪大于2500的人数
SELECT COUNT(*) AS '月薪大于2500的人数' FROM emp WHERE sal>2500; 
#统计月薪与佣金之和大于2500元的人数
SELECT COUNT(sal+IFNULL(comm,0)) AS '月薪与佣金之和大于2500元的人数' FROM emp WHERE sal+IFNULL(comm,0)>2500;
#查询所有雇员月薪+佣金和
SELECT SUM(sal+IFNULL(comm,0)) AS '所有雇员月薪+佣金和' FROM emp;