Msql之分页查询

语法：

select 查询列表                    ⑦  

from table1                           ①

连接类型 join table2             ②   

on 连接条件                           ③

where 筛选条件                     ④

group by 分组字段                ⑤

having 分组筛选条件             ⑥

order by 排序字段                 ⑧

limit offSet,size                     ⑨

执行顺序：以上标号为执行顺序

offSet:起始位置从0开始  -> (page-1)*size

size:查询条数

应用场景：请求的数据一页放不下，需要分页提交sql请求

特点：①放在查询语句的最后面

           ②offSet=(page-1)*size

#查询前5条员工信息

SELECT e.* FROM employees e LIMIT 0,5;

 

#查询第11条-第25条员工的信息

SELECT e.* FROM employees e LIMIT 10,15;

 

#查询有奖金的员工的信息，并且工资较高的前10名显示出来

SELECT e.* FROM employees e WHERE commission_pct is not NULL ORDER BY salary desc LIMIT 0,10;

 

1.查询所有学员的邮箱的用户名 (注：邮箱中@前面的字符) 

SELECT SUBSTR(email,1,INSTR(email,'@')-1) from student WHERE email is not null;

2.查询男生和女生的个数 

SELECT COUNT(*),sex FROM student GROUP BY sex;

3.查询年龄>18的所有学生的姓名和年级名称 

SELECT name,gradeName FROM studentinfo s INNER JOIN grade g on s.gradeId = g.id WHERE age>18;

4.查询那个年级的学生的最小年龄>20

SELECT MIN(age),gradeId FROM studentinfo s INNER JOIN grade g on s.gradeId = g.id GROUP BY gradeId HAVING MIN(age)>20;

1.查询工资最低的员工信息：last_name,salary 

SELECT last_name,salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

2.查询平均工资最低的部门信息 

SELECT d.* FROM departments d WHERE department_id=(SELECT department_id FROM employees WHERE department_id is not NULL GROUP BY department_id ORDER BY avg(salary) limit 1);

3.查询平均工资最低的部门信息和该部门的平均工资 

SELECT d.*,ag_dep.ag_sal FROM departments d INNER JOIN (SELECT MIN(salary) ag_sal,department_id FROM employees GROUP BY department_id ORDER BY MIN(salary) LIMIT 1)ag_dep ON d.department_id=ag_dep.department_id;

4.查询平均工资最高的job信息

SELECT j.* FROM jobs j WHERE j.job_id=(SELECT job_id FROM employees GROUP BY job_id ORDER BY job_id DESC LIMIT 1);

5.查询平均工资高于公司平均工资的部门有哪些 

SELECT department_id FROM employees WHERE department_id is not NULL GROUP BY department_id HAVING avg(salary)>(SELECT avg(salary) FROM employees);

6.查询出公司中所有manager的详细信息 

SELECT e.* FROM employees e WHERE e.employee_id in (SELECT DISTINCT manager_id FROM employees WHERE manager_id is not NULL);

7.各个部门中最高工资中最低的那个部门的最低工资是多少 

SELECT MIN(salary) FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE department_id is not NULL GROUP BY department_id ORDER BY MAX(salary) LIMIT 1);

8.查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary 

 SELECT last_name,department_id,salary FROM employees e WHERE e.employee_id in (SELECT DISTINCT manager_id FROM employees e WHERE department_id=(SELECT department_id FROM employees GROUP BY department_id ORDER BY avg(salary) DESC limit 1));

 

1.查询每个专业的学生人数 

SELECT COUNT(*),majorid FROM student GROUP BY majorid;

2.查询参加考试的学生中，每个学生的平均分，最高分 

SELECT avg(score),MAX(score),s.studentno FROM student s INNER JOIN result r on s.studentno=r.studentno GROUP BY s.studentno;

3.查询姓张的每个学生的最低分大于60的学号、姓名 

SELECT MIN(score),s.studentno,s.studentname FROM student s INNER JOIN result r on s.studentno=r.studentno GROUP BY s.studentno,s.studentname HAVING MIN(score)>60;

4.查询学生生日在”1988-1-1”后的学生姓名、专业名称

SELECT  s.studentno,s.studentname FROM student s WHERE DATEDIFF(borndate,'1988-1-1');

 5.查询每个专业的男生人数和女生人数分别是多少

方式一：

SELECT COUNT(*),majorid,sex FROM student GROUP BY majorid,sex;

方式二：

SELECT (SELECT COUNT(*) FROM student WHERE sex='男') 男生,(SELECT COUNT(*) FROM student WHERE sex='女') 女生,majorid  FROM student GROUP BY majorid;

6.查询专业和张翠山一样的学生的最低分

SELECT MIN(score) FROM result r INNER JOIN student s on r.studentno=s.studentno WHERE majorid=(SELECT majorid FROM student WHERE studentname='张翠山');

 

7.查询大于60分的学生的姓名、密码、专业名

SELECT studentname,loginpwd,majorname FROM student s INNER JOIN major m ON s.majorid=m.majorid INNER JOIN result r on r.studentno=s.studentno WHERE score>60;

8.按邮箱位数分组，查询每组的学生个数 

SELECT COUNT(*),LENGTH(email) FROM student WHERE email is not NULL GROUP BY LENGTH(email);

 9.查询学生名、专业名、分数

SELECT studentname,majorname,score FROM student s INNER JOIN major m on s.majorid=m.majorid INNER JOIN result r on r.studentno=s.studentno;

10. 查询那个专业没有学生，分别用左连接和右连接实现

SELECT * FROM major m LEFT JOIN student s on m.majorid=s.majorid;

SELECT * FROM major m RIGHT JOIN student s on m.majorid=s.majorid;

11.查询没有成绩的学生人数

SELECT * FROM student s LEFT JOIN result r on s.studentno=r.studentno WHERE r.id is NULL; 

 联合查询：(union)  -> 查询的多个结果合并成一个结果

union：具有去重的效果

union all：不去重

语法：

查询语句1

union

查询语句2

....

特点：

        ①查询语句的查询列表的个数要一致

        ②查询语句中的查询列表的类型和顺序要一致

        ③union具有去重的效果，union all不去重

应用场景：当我们要查询的结果集来自多张表，并且这些表并没有直接的关系，我们就可以使用联合查询

案例：查询部门编号>90或邮箱包含a的员工信息

以前：

SELECT e.* FROM employees e WHERE department_id>90 or email LIKE '%a%';

 现在：

SELECT e.* FROM employees e WHERE department_id>90
UNION 
SELECT e.* FROM employees e WHERE email LIKE '%a%';

DML语言

插入

单表插入：

方式1：

insert into table(column1,column2,...) values(value1,value2,...);

方式2：

insert into table(column1,column2,...) select value1,value2,... from table where 筛选条件

多表插入：

insert into table(column1,column2,...) values(value1,value2,...),(value1,value2,...),...;

注意细节：

①插入的数据类型要与列的数据类型一致

②当列的值可以为null时，列可以不写或者值置为null

③列的顺序可以调换

④列的个数要与插入的数值个数一致

⑤列可以不写，默认与表的列顺序一致

更新

单表更新：

update table set column1=value1,column2=value2,... where 筛选条件;

多表更新

update table1 别名 连接类型 join table2 别名 on 连接条件 set column2=value2,... where 筛选条件;

删除

单表删除

方式一:

delete from table where 筛选条件;

方式二:

truncate from table;

多表删除：

delete 要删除的表的别名 from table1 别名 连接类型 join table2 别名 on 连接条件 where 筛序条件;

delete与truncate的区别:

1.delete可以加where，truncate不可以加where

2.truncate的执行效率高一些

3.如果删除的表的主键是自增长的话，delete删除表后插入新数据主键将从断点处增长，truncate删除表的话插入新的数据主键是从1开始自增长

4.delete删除表的话有返回值，truncate删除的表的话是没有返回值的

5.delete删除失败的话会回滚，truncate删除失败的是不回滚

 

2.显示表my_employees的结构

DESC my_employees;

 

 3.向my_empoyees插入数据

insert INTO my_employees(id,first_name,last_name,userId,salary) VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);

4. 向users表中插入数据

INSERT INTO users(id,userid,department_id) VALUES(1,'Rpatel',10),(2,'Bdancs',10),(3,'Bbiri',20),(4,'Cnewman',30),(5,'Aropebur',40);

5.将3号员工的last_name修改为'drelxer'

UPDATE my_employees set last_name='drelxer' WHERE id=3;

6.将所有工资少于900的员工的工资修改为1000

UPDATE my_employees SET salary=1000 WHERE salary < 900;

7.将userid为Bbiri的user表和my_employees表的记录全部删除\

DELETE mye,u FROM my_employees mye INNER JOIN users u on mye.userId=u.userid WHERE u.userid='Bbiri';

8.删除所有数据

DELETE FROM users;
DELETE FROM my_employees;

9.检查所作的修正

SELECT * FROM users;

SELECT * FROM my_employees;

10.清空表my_employees

TRUNCATE FROM my_employees;