mysql求总分 mysql求总成绩

-- 聚合函数
 -- sum()统计和
 -- couint() 统计个数
 -- avg() 平均值
 -- max() 最大值
 -- min() 最小值-- 查询表
 select * from exam;-- 统计学生英语成绩总和
 select sum(english) '英语总和' from exam;-- 统计学生英语成绩总合 和 数学成绩总和
 select sum(english) '英语成绩',sum(math) '数学成绩' from exam;-- 查询姓王的学生的英语总成绩
 select sum(english) '姓王的同学的英语总成绩' from exam where uname like '王%';-- 查询所有学生的各科总成绩
 select sum(chinese) as '语文总成绩',sum(math) as '数学总成绩',sum(english) as '英语总成绩' from exam;
 select sum(chinese)+sum(math)+sum(english) as '各科总成绩' from exam;
 select sum(chinese+math+english) as '各科总成绩' from exam; -- 查询王涛的成绩总和
 select sum(chinese+math+english) as '王涛总成绩' from exam where uname='王涛';select uname,chinese,math,english,sum(chinese+math+english) as '王涛总成绩' from exam where uname='王涛';
-- NULL
 select uname as '姓名',sum(english+math+chinese) as '三科总成绩' from exam where uname='杜申';-- IFNULL(NULL,0)
 select uname ,english+math+IFNULL(chinese,0) from exam where uname='王涛';-- 统计学生的总数
 select COUNT(*) as '学生个数' from exam;select COUNT(id) from exam;
-- 统计英语成绩的学生个数
 select COUNT(english) from exam;-- MAX(expr) MIN(expr) AVG(expr)
-- 求数学成绩的最高值
 -- 求语文成绩的最低值
 -- 求英语成绩的平均值
 -- 求语文成绩的平均值select MAX(math) from exam;
SELECT MIN(chinese) from exam;
select SUM(english)/COUNT(english) from exam;
select AVG(chinese) from exam;
-- 分组查询
 -- 前期准备drop table emp;
 create table emp(
         empno int,
         ename varchar(5),
         job varchar(100),
         mgr int,
         hiredte date,
         sal double(7,2),
         comm double(7,2),
         deptno int 
 )insert into emp VALUES(7365,'jack','doc',7902,'1998-06-05',9898,3000,20);
 insert into emp VALUES(7366,'rose','doc',7902,'1995-10-05',6858,3000,20);
 insert into emp VALUES(7367,'tom','doc',9800,'1996-12-06',7586,7895,20);
 insert into emp VALUES(7368,'karry','doc',8007,'1999-06-01',9456,5853,20);
 insert into emp VALUES(7369,'bob','doc',8324,'1995-06-05',8524,3686,20);
 insert into emp VALUES(7255,'viya','doc',8907,'1980-10-31',5748,3000,20);
 insert into emp VALUES(7263,'hou','doc',7546,'1998-02-25',8765,3000,20);
 insert into emp VALUES(7156,'jay','doc',7879,'1997-08-15',5786,3000,20);
 insert into emp VALUES(7258,'jj','doc',7998,'1996-03-05',8678,3000,20);
 insert into emp VALUES(7239,'kk','doc',9782,'1997-02-05',9564,3000,20);
 insert into emp VALUES(7168,'mm','doc',9856,'1988-06-05',5788,3000,20);
 insert into emp VALUES(7400,'hh','doc',6532,'1994-06-22',10155,3000,20);
 insert into emp VALUES(7287,'aa','doc',8989,'1992-10-22',5868,3000,20);
 insert into emp VALUES(7324,'ss','doc',7865,'1998-06-15',8775,3000,20);
 insert into emp VALUES(7321,'xcc','doc',7945,'1998-3-31',8956,3000,20);-- 分组查询 group by
 create table orderitem(
         id int PRIMARY KEY auto_increment,
         product VARCHAR(50),
         price double
 )insert into orderitem values(null,'洗衣机',2638);
 insert into orderitem values(null,'电视机',3632);
 insert into orderitem values(null,'洗碗机',2438);
 insert into orderitem values(null,'电冰箱',1965);
 insert into orderitem values(null,'微波炉',999);
 insert into orderitem values(null,'蒸柜机',2350);
 insert into orderitem values(null,'空调',2523);
 insert into orderitem values(null,'空调',4258);
 insert into orderitem values(null,'电视机',1958);-- 按照商品的名称统计每类商品所购买的个数
 select product,COUNT(*) from orderitem GROUP BY product;-- 按照 商品名称进行统计，每类商品所花费的总金额
 select product, SUM(price) from orderitem GROUP BY product;-- 需求 按照商品名称统计每类商品金额大于2000的商品
 select product,price from orderitem where price > 2000 GROUP BY product;-- 按照商品名称进行统计，统计出商品的花费总金额大于5000的商品
 select product,sum(price) from orderitem where price > 5000 GROUP BY product;-- 按照商品名称进行统计，统计出商品花费总金额大于5000的商品
 select product,SUM(price) from orderitem group by product HAVING sum(price) > 5000 ; -- 分组过滤使用having关键字 where
 -- having是在分组后对数据进行过滤
 -- where先对条件进行判断，然后在进行分组过滤
 -- having后面可以使用聚合函数，可以跟非聚合函数吗？
 -- where后面能跟聚合函数，只能跟条件判断-- 分页 LIMIT
 -- 查询emp员工表
 select * from emp;-- 查询5条记录，起始从0开始，代表的是从第一行开始查
 -- limit 第一个参数代表的是从多少条件开始查，第二个参数代表的是每次查询多少条记录
 select * from emp limit 0,5;-- 查询10行记录， 起始从第三行开始查
 select * from emp limit 3,10;-- 一页记录是5条，希望展示2页
 select * from emp limit 0,5;select * from emp limit 6,5;
select * from emp ;
 set @currentPage=2;-- 当前页一共需要多少页
 set @pageSize=5;-- 每一页显示的条数set @currentPage=(currentPage-1)*pageSize;
select * from emp limit @@currentPage,@pageSize;
 select * from emp orderr by limit (curPage-1)*pageSize,pageSize;  
 -- 主键数据唯一切不能为空
 -- 第一种添加主键的方式
 create table student(
         id int PRIMARY KEY,
         uname VARCHAR(50)
 )-- 第二种添加主键的方式
 create table students(
         id int,
         uname varchar(40)
 );
 -- 添加主键
 alter table students add PRIMARY KEY(id);CREATE TABLE studentss(
         id int PRIMARY KEY,
         uname VARCHAR(50) UNIQUE-- 唯一键约束 unique
 )CREATE TABLE studentsss(
         id int PRIMARY KEY auto_increment,-- 自动增长
         uname VARCHAR(29) UNIQUE-- 唯一键约束 unique
 )-- 非空约束
 create table tea(
     
         id int PRIMARY KEY auto_increment,
         uname varchar(39) not NULL,
         sex varchar(2)
 )insert into tea values(null,"",'男');
 -- insert into tea values(null,NULL,'男');create table tea1(
     
         id int PRIMARY KEY auto_increment,
         uname varchar(39) not NULL,
         sex varchar(2) default '男'
 )-- 默认约束 default
 insert into tea1 values(null,"",'男');
 insert into tea1 values(null,"aaa",DEFAULT);-- 第一种创建外键的方式
 -- 创建一个部门表
 create table dept(
         did int PRIMARY KEY auto_increment,
         dname varchar(50)
 )insert into dept values(null,'市场部');
 insert into dept values(null,'教学部');
 insert into dept values(null,'教务部');
 insert into dept values(null,'渠道部');
 insert into dept values(null,'咨询部'); -- 创建一个员工表
 create table employee(
         eid int PRIMARY KEY auto_increment,
         ename varchar(20),
         salary double,
         birthday date,
         sex varchar(2),
         dno int
 )insert into employee values(null,'张三',8000,'1999-06-02','男',2);
 insert into employee values(null,'李四',5000,'1988-12-23','男',1);
 insert into employee values(null,'王五',6000,'1996-04-04','女',5);-- 在员工表上添加外键
 alter table employee add foreign key(dno) REFERENCES dept(did); -- 第二种创建外键的方式
 create table dept1(
         did int PRIMARY KEY auto_increment,
         dname varchar(50)
 )insert into dept1 values(null,'市场部');
 insert into dept1 values(null,'教学部');
 insert into dept1 values(null,'教务部');
 insert into dept1 values(null,'渠道部');
 insert into dept1 values(null,'咨询部');-- 创建一个员工表
 create table employee1(
         eid int PRIMARY KEY auto_increment,
         ename varchar(20),
         salary double,
         birthday date,
         sex varchar(2),
         dno int,
         CONSTRAINT fk_dno_did FOREIGN KEY(dno) REFERENCES dept1(did)-- 第二种创建外键的方式
 )insert into employee1 values(null,'张三',8000,'1999-06-02','男',2);
 insert into employee1 values(null,'李四',5000,'1988-12-23','男',1);
 insert into employee1 values(null,'王五',6000,'1996-04-04','女',5);