CREATE DATABASE mydb2;
use mydb2;
create table student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
english int,
math int
);
insert into student (id, name, gender, chinese, english, math) VALUES (1,'张明','男',89,78,90);
insert into student (id, name, gender, chinese, english, math) VALUES (2,'李进','男',67,53,95);
insert into student (id, name, gender, chinese, english, math) VALUES (3,'王五','女',87,78,77);
insert into student (id, name, gender, chinese, english, math) VALUES (4,'李一','女',88,98,92);
insert into student (id, name, gender, chinese, english, math) VALUES (5,'李财','男',82,84,67);
insert into student (id, name, gender, chinese, english, math) VALUES (6,'张宝','男',55,85,45);
insert into student (id, name, gender, chinese, english, math) VALUES (7,'黄蓉','女',75,65,30);
insert into student (id, name, gender, chinese, english, math) VALUES (7,'黄蓉','女',75,65,30);
#查询表中所有学生的信息
select * from student;
#查询表中所有学生的姓名和对应的英语成绩
select name,english from student;
#过滤表中重复数据
select distinct * from student;
#统计每个学生的总分
select distinct name,chinese+english+math as sum from student;
#在所有学生的总分数上加10分特长分
select distinct name,chinese+english+math+10 as sum from student;
#使用别名表示学生分数
select name,chinese '语文成绩',english '英语成绩',math '数学成绩' from student;
#查询英语成绩大于90分的同学
select * from student where english > 90;
#查询总分大于200的同学
select *,chinese+english+math as total from student where chinese+english+math >200;
#查询英语分数在80-90之间的同学
select * from student where english between 80 and 90;
select * from student where english >=80 and english <=90;
#查询英语分数不在80-90之间的同学
select * from student where english not between 80 and 90;
#查询数学分数为89，90，91分的同学
select * from student where math in(89,90,91);
#查询数学分数不为89，90，91分的同学
select * from student where math not in(89,90,91);
#查询所有姓李的学生的英语成绩
select * from student where name like '李%';
#对数学成绩降序排序后输出
select * from student order by math desc;
#对姓李的同学总分成绩排序输出
select * from student where name like '李%' order by chinese+english+math desc;
#查询男生和女生各有多少人，并将人数将序排序输出
select gender,count(*) as total from student group by gender order by total desc;1.like 的基本用法
1）%
包含0个或多个字符的任意字符串
2）_
任意单个字符
3）[]
指定范围（[a-c]）或集合([abcdef])中的任何单个字符
4）[^]
不属于指定范围或集合中的任何单个字符
5）？
代表单个字符
6）#
代表单个字符，不过只能代表数字
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),
(7499,'ALEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
#1.按员工编号升序排列不在10号部门工作的员工信息
select * from emp where deptno !=10 order by empno asc;
#2.查询姓名第二个字母不是A且薪水大于1000元的员工信息，按年薪降序排列
select * from emp where ename not like '_A%' and sal >1000 order by (12*sal+ifnull(comm,0)) desc;
#3.求每个部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
#4.求各个部门的最高薪水
select deptno,max(sal) from emp group by deptno;
#5.求每个部门每个岗位的最高薪水
select deptno,job,max(sal) from emp group by deptno,job order by deptno;
#6.求平均薪水大于2000的部门编号
select deptno,avg(sal) as average from emp group by deptno having average >2000;
#7.选择公司中有奖金的员工姓名，工资
select * from emp where comm is not null;
#8.查询员工最高工资和最低工资的差距
select max(sal)-min(sal) '薪资差异' from emp;
