430 聚合 分页 排序 分组查询 筛选

聚合查询：函数sum()求和 、avg() 平均数、max()最大值、min()最小值、count()记录数

格式：select  聚合函数 (字段) from 表名； 注：聚合不能与其他字段一起用

SELECT COUNT(*) FROM score;  COUNT(*) 取每一列的取最大值

NULL 不包括在内 所有显示5条

注:用count 统计 不包含 null数据，要使用不包含null的字段 不然不准

查询列起别名

select count(*) AS 起的名 from 表名;

分页查询

公式：select * from 表名 limit (当前页-1)*每页显示的条数

查询排序（order by）

语法：select * from 表名 order by 字段 asc/desc;

asc：正序  数值：递增 0~9 字母：自然a~z (默认)

desc：倒序 9~0 z~a  数值 字母 反之

例：按jsp>60 倒序

select * from 表名 字段  order by 字段 desc;

例：按jsp正序，HTML倒序

select * from 表名 order by jsp asc,html desc ;

注意：

1排序以第一个为主 2 只有第一个字段排序有相同时， html倒序才会有作用 

 分组查询 (group by) 分组查询一般搭配聚合函数使用

公式:select 字段 from 表名 group by 字段

例：查询男女的人数  男3  女2

男女生jsp总成绩

select ssex,sum(jsp) from score group by ssex;  当条件里含有  每个时 先分组

分组查询后筛选

例：查询总人数大于2的性别

1.查询男女人数 2.筛选人数大于2的记录（having）注：分组前有where 分组后用having

条件多 一步一步来查询

 

课堂随笔
-- 查询所有列
SELECT * FROM student;
/*创建表*/
CREATE TABLE student(
    sid INT,
    sname VARCHAR(10),
    ssex CHAR(2)
);
/*插入数据*/
INSERT INTO student VALUES(1,'袁一鸣','男');
/*给定字段插入数据*/
INSERT INTO student(sid,sname) VALUES(2,'小明');
/*查询表中所有数据*/
SELECT * FROM student;
/*修改数据（一定要带条件）*/
UPDATE student SET sname='小红'WHERE sid=2;
/*删除全表数据*/
DELETE FROM student;
TRUNCATE student;
/*删除某条数据*/
DELETE FROM student WHERE sid=2;
/*创建名为emp的员工表*/
CREATE TABLE emp(
    sid INT,
    sname VARCHAR(20),
    gender VARCHAR(2),
    birthday DATETIME,
    email VARCHAR(10),
    remark VARBINARY(50)
);
/*修改练习*/
/*add是添加*/
ALTER TABLE emp ADD age VARCHAR(30) 
/*MODIFY COLUMN是修改字段类型*/
ALTER TABLE emp  MODIFY COLUMN  email VARCHAR(50)
/*DROP COLUMN是删除列的*/
ALTER TABLE emp DROP COLUMN remark;
/*修改字段名称*/
 ALTER TABLE emp CHANGE COLUMN sname usemame VARCHAR(20);
-- 新建score表
CREATE TABLE score(
    sid INT,
    sname VARCHAR(10),
    jsp INT,
    html INT,
    js INT
);
-- 给score表添加数据
INSERT INTO score VALUES(1,'钢铁侠',12,66,100);
INSERT INTO score VALUES(2,'浩克',66,88,11);
INSERT INTO score VALUES(3,'美国队长',0,70,80);
INSERT INTO score VALUES(4,'小辣椒',100,50,60);
INSERT INTO score VALUES(5,'灭吧',66,70,93);
-- 聚合函数sum()求和 avg()平均数 max()最大值 min()最小值 count()记录数
SELECT * FROM score
/*求全班jsp的总成绩 SUM()求和*/
SELECT SUM(jsp) FROM score;/*聚合函数不能跟其他字段一起用*/
/*求全班的html这么课的平均成绩*/
SELECT AVG(html) FROM score;
/*求全班同学js这门课的最高分*/
SELECT MAX(js) FROM score;
/*求html这么课的最低分*/
SELECT MIN(html) FROM score;
/*求本班一共有多少位同学*/
SELECT COUNT(*) FROM score; -- 用count时 不能包含null的字段 不然不准
-- 查询字段起名 字段名 AS 起的名 FROM 表名
SELECT COUNT(*) AS 统计数据 FROM score;
-- 添加数据
INSERT INTO student VALUES(2,'雄大','男');
INSERT INTO student VALUES(3,'雄二','男');
INSERT INTO student VALUES(4,'光头强','男');
INSERT INTO student VALUES(5,'黑寡妇','女');
INSERT INTO student VALUES(6,'黑灭霸','男');
INSERT INTO student VALUES(7,'',NULL);

/*分页查询 limit起始索引，每页显示多少条*/
SELECT * FROM student LIMIT 0,2;/*第一页*/
SELECT * FROM student LIMIT 2,2;/*第二页*/
SELECT * FROM student LIMIT 4,2;/*第三页*/
SELECT * FROM student LIMIT 6,2;/*第四页*/
-- 分页公式
SELECT * FROM student LIMIT
(当前页-1)*每页的显示的条数;

SELECT * FROM score;
/*查询排序  按照jsp>60 的倒序*/
SELECT * FROM score WHERE jsp>60 ORDER BY jsp DESC;
/*按照jsp的正序，html倒序*/
SELECT * FROM score ORDER BY jsp ASC,html DESC;
/*分组查询(一般与聚合函数搭配)*/
SELECT ssex,COUNT(*) FROM student GROUP  BY ssex;
/*男女生的jsp总成绩*/
SELECT ssex,SUM(jsp) FROM score GROUP BY ssex;
/*查询总人数大于2的性别*/
SELECT ssex FROM student GROUP BY ssex HAVING COUNT(*)>2;

 

MySQL小测验
 
二、练习
1. 查询出部门编号为30的所有员工
select * from 表名 where deptno=30;
2. 所有销售员的姓名、编号和部门编号。
select ename,empno,deptno from 表名 where job=’销售员;’
3. 找出奖金高于工资的员工。
select ename from 表名 where comm>sal;
4. 找出奖金高于工资的60%的员工。
select ename from 表名 where comm>sal*0.6;
5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
select * from 表名 where (deptno=10 and job=’经理’)
6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
select * from 表名 where (deptno=10 and job=’经理’)
or (deptno=20 and job=’销售员’)or(job<>’经理’ and job<>’销售员 and sal>=20000’;)
7. 无奖金或奖金低于1000的员工。
select ename from 表名 where comm is null or comm=’’ orcomm<1000;
8. 查询名字由三个字组成的员工。
select ename from 表名 where    ename like ‘___’;
9.查询2000年入职的员工。
select ename from 表名 where year(hiredate)=2000;
select ename from 表名 where hiredate like ‘2000%’;
10. 查询所有员工详细信息，用编号升序排序
select * from 表名 order by empno asc;
11. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
select * from 表名 order by sal desc ,hiredate asc;
12.    查询姓周的两个名字的员工。
select ename from 表名 where ename like ‘周_’;
13.    查询所有姓张的员工。
select ename from 表名 where ename like ‘张%’;
14.    查询该公司有多少个岗位，每个岗位有多少人。
select job,count(*) from 表名 group by job;
15.查询哪个岗位中人数大于3。
select  job,count(*) from 表名 group by job having count(*)>3;