对数据库基本查询操作的练习

--------对数据库基本查询操作的练习1--------

-- 创建学生表

 CREATE TABLE student(
        id INT,
        NAME VARCHAR(20),
        gender VARCHAR(20),
        chinese INT,
        english INT,
        math INT
 );

-- 插入学生信息

 INSERT INTO student VALUES(1,'张三','女',89,79,90);
 INSERT INTO student VALUES(2,'李四','男',78,81,93);
 INSERT INTO student VALUES(3,'王五','女',60,79,77);
 INSERT INTO student VALUES(4,'赵六','男',56,84,89);
 INSERT INTO student VALUES(5,'张博','男',53,68,80);
 INSERT INTO student VALUES(6,'安悦','女',86,97,76);
 INSERT INTO student VALUES(7,'王阳','女',88,58,66);
 INSERT INTO student VALUES(8,'闫东','男',93,99,72);
 INSERT INTO student VALUES(8,'闫东','男',93,99,72);

-- 例题一

1.查询表中所有学生信息

 SELECT * FROM student;

2.删除表中所有信息

 DELETE FROM student;

3.查询表中所有学生的姓名和对应的英语成绩

SELECT NAME,english FROM student;

4.过滤表中重复信息

SELECT DISTINCT * FROM student;

5.统计每个学生的总分

SELECT NAME,(chinese+english+math) AS total_score FROM student;

6.在所有学生总分数上加10分特长分

SELECT NAME,(chinese+english+math+10) AS total_score FROM student;

7.使用别名表示学生分数

SELECT NAME,chinese '语文成绩',english '英语成绩',math '数学成绩' FROM student;

8.查询英语成绩大于90分的同学

SELECT * FROM student WHERE english >90;

9.查询总分大于200分的所有同学

SELECT * ,(chinese+english+math) AS total_score FROM student WHERE (chinese+english+math) > 200;

10.查询英语分数在80-90之间的同学

SELECT NAME,english '英语成绩' FROM student WHERE english BETWEEN 80 AND 90;

11.查询英语分数不在80-90之间的同学

SELECT NAME,english '英语成绩' FROM student WHERE english NOT BETWEEN 80 AND 90;

12.查询数学分数为89,90,91的同学

SELECT * FROM student WHERE math IN(89,90,91);

13.查询所有姓张的同学的英语成绩

 SELECT NAME,english FROM student WHERE NAME LIKE '张%';

14.查询语文分86且数学分76的同学

 SELECT NAME,chinese,math FROM student WHERE chinese = 86 && math = 76; 

15.查询英语80或者总分大于230的同学

 SELECT NAME,english,(chinese+english+math) 'total_score' 
 FROM student 
 WHERE english = 80 || (chinese+english+math) >230;

16.对数学成绩降序排序后输出

 SELECT * FROM student ORDER BY math DESC;

17.对总分排序后输出，然后再从高到低的排序输出

 SELECT *,(chinese+english+math) '总成绩' FROM student ORDER BY (chinese+english+math) DESC;

18.对姓张的学生成绩排序输出

 SELECT * FROM student WHERE NAME LIKE '张%' ORDER BY (chinese+english+math) DESC;

19.查询男生和女生分别有多少人，并将总成绩降序排序输出

 SELECT gender,COUNT(*) total_count FROM student GROUP BY gender ORDER BY total_count DESC;

20.查询男生和女生分别有多少人，并将总成绩降序排序输出,在查询出总人数大于4的性别人数

 SELECT gender,COUNT(*) total_count 
               FROM student 
               GROUP BY gender 
               HAVING total_count > 4 
               ORDER BY total_count DESC;

--------对数据库基本查询操作的练习2--------

-- 创建员工表

 CREATE TABLE emp(
        empno INT,            -- 员工编号
        ename VARCHAR(50),    -- 员工名字
        job VARCHAR(50),      -- 工作名字
        mgr INT,              -- 上级领导编号
        hiredate DATE,        -- 入职日期
        sal INT,              -- 薪资
        comm INT,             -- 奖金
        deptno INT            -- 部门编号
 );

-- 插入员工信息

 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,0,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);
 ​
 SELECT * FROM emp;

--例题二

1.按员工编号升序排列不在10号部门工作的员工信息

 SELECT * FROM emp WHERE deptno != 10 ORDER BY empno ASC;

2.查询姓名第二个字母不是‘A’且薪水大于1000元的员工信息，按年薪降序排列

 -- 年薪：12*月薪+奖金
 -- ifnull(comm,0) : 如果comm的值为null，则当做0计算，如果不为null，则当做原来的值计算
 SELECT * FROM emp WHERE ename NOT LIKE '_A%' AND sal >1000 ORDER BY (12*sal+IFNULL(comm,0)) DESC;

3.求每个部门的平均薪水

 mtSELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
 SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno ORDER BY avg_sal ASC;

4.求各个部门的最高薪水

 SELECT deptno,MAX(sal) max_sal FROM emp GROUP BY deptno;

5.求每个部门，每个岗位的最高薪水

 SELECT deptno,job,MAX(sal) max_sal FROM emp GROUP BY deptno,job;

6.求平均薪水大于2000的部门编号

 SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 2000;

7.将部门平均薪资大于1500的部门编号列出来，按部门平均薪资降序排序

 SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno HAVING avg_sal > 1500 ORDER BY avg_sal DESC;

8.选择公司有奖金的员工姓名，工资

 SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL;

9.查询员工最高工资和最低工资的差距

 SELECT MAX(sal) - MIN(sal) '薪资差距' FROM emp;