2020-10-09（mysql练习）

1.设有成绩表如下所示
    1.查询两门及两门以上不及格的学生姓名
    2.查询学生平均分
    3.查询姓名是张三的学生 成绩和
    4.将学生信息按照 分数倒序
    5.获取学生信息中 分数最低的学生姓名和分数最高的学生姓名
    6.查询两门及两门以上不及格同学的平均分。
    编号	姓名	科目	分数
    1	 张三	 数学  90
    2	 张三	 语文	 50
    3	 张三	 地理	 40
    4	 李四	 语文	 55
    5	 李四	 政治	 45
    6	 王五	 政治	 30
    7	 李四	 数学	 80
    8	 王五	 语文	 70

CREATE TABLE student (
	编号 INT PRIMARY KEY auto_increment,
	姓名 VARCHAR(10),
	科目 VARCHAR(10),
	分数 INT
);
INSERT INTO student(
	姓名,科目,分数
)VALUES
('张三','数学',90),
('张三','语文',50),
('张三','地理',40),
('李四','语文',55),
('李四','政治',45),
('王五','政治',30),
('李四','数学',80),
('王五','语文',70);

# 1.查询两门及两门以上不及格的学生姓名
SELECT s.`姓名` FROM student s GROUP BY s.`姓名` HAVING sum(s.`分数`<60)>=2;
# 2.查询学生平均分
SELECT s.`姓名`,AVG(s.`分数`) as '平均分' FROM student s GROUP BY s.`姓名`;
# 3.查询姓名是张三的学生 成绩和
SELECT sum(s.`分数`) as '成绩和' FROM student s WHERE s.`姓名` = '张三';
# 4.将学生信息按照 分数倒序
SELECT * FROM student s ORDER BY s.`分数` ASC;
# 5.获取学生信息中 分数最低的学生姓名和分数最高的学生姓名
SELECT 姓名,分数 FROM student WHERE  分数=(SELECT MAX(分数) FROM student) or 分数=(SELECT MIN(分数) FROM student);
# 6.查询两门及两门以上不及格同学的平均分。
SELECT s.`姓名`,AVG(s.`分数`) FROM student s GROUP BY s.`姓名` HAVING sum(s.`分数`<60)>=2;

2.写出 SQL语句的格式 : 插入 ，更新 ，删除 
  表名 user
  name	tel	content	date
  张三	13333663366	 大专毕业	2006-10-11 
  张三	13612312331	 本科毕业	2006-10-15 
  张四	021-55665566 中专毕业	2006-10-15
  (a).有一新记录(小王 13254748547 高中毕业 2007-05-06)请用SQL语句新增至表中
  (b).请用sql语句把张三的时间更新成为当前系统时间 
  (c).请写出删除名为张四的全部记录

CREATE TABLE user(
	name VARCHAR(10),
	tel VARCHAR(20),
	content VARCHAR(10),
	date DATE
);
INSERT INTO user(
	name,tel,content,date
)VALUES
('张三','13333663366','大专毕业','2006-10-11'),
('张三','13612312331','本科毕业','2006-10-15'),
('张四','021-55665566','中专毕业','2006-10-15');

#(a).有一新记录(小王 13254748547 高中毕业 2007-05-06)请用SQL语句新增至表中
INSERT INTO user(
	name,tel,content,date
)VALUES
('小王','13254748547','高中毕业','2007-05-06');
#(b).请用sql语句把张三的时间更新成为当前系统时间
UPDATE user SET date=CURRENT_TIMESTAMP() WHERE name='张三';
#(c).请写出删除名为张四的全部记录
DELETE FROM user WHERE name='张四';

3.写出 SQL语句的格式 :对emp表进行查询操作
  1.找出奖金高于工资的雇员 
  2.找出奖金高于工资60%的雇员
  3.找出部门10中所有经理和部门20中所有店员的信息
  4.薪资大于或等于2000的所有员工的信息。
  5.查询没有奖金或者奖金低于100的员工信息
  6.查询姓名不带”R”的员工姓名
  7.显示员工的姓名和入职时间，根据入职时间，将最老的员工排放在最前面。
  8.显示所有员工的姓名、工作和工资，按照工作的降序排序，若工作相同则按工资升序排序。

#创建雇员表
CREATE TABLE emp(
	empno		INT,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		decimal(7,2),
	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);

#1.找出奖金高于工资的雇员
SELECT * FROM emp WHERE comm>sal;
#2.找出奖金高于工资60%的雇员
SELECT *,sal*0.6 as sal1 FROM emp WHERE comm>sal*0.6;
#3.找出部门10中所有经理和部门20中所有店员的信息
SELECT * FROM emp WHERE deptno=10 OR deptno=20;
#4.薪资大于或等于2000的所有员工的信息。
SELECT * FROM emp WHERE sal>=2000;
#5.查询没有奖金或者奖金低于100的员工信息
SELECT * FROM emp WHERE comm is NULL or comm<100;
#6.查询姓名不带”R”的员工姓名
SELECT * FROM emp WHERE ename NOT like '%R%';
#7.显示员工的姓名和入职时间，根据入职时间，将最老的员工排放在最前面。
SELECT ename,hiredate FROM emp ORDER BY hiredate ASC;
#8.显示所有员工的姓名、工作和工资，按照工作的降序排序，若工作相同则按工资升序排序。
SELECT ename,job,sal FROM emp ORDER BY job DESC,sal ASC;