CREATE TABLE employee( id INT, NAME VARCHAR(20), sex VARCHAR(10), salary FLOAT );INSERT INTO employee VALUES(1,"zhangsan","male",2000);INSERT INTO employee VALUES(2,"lisi","male",1000);INSERT INTO employee VALUES(3,"xiaohong","female",4000);-- 将所有员工薪水修改为5000元。UPDATE employee SET salary = 5000;-- 将姓名为’zhangsan’的员工薪水修改为3000元。UPDATE employee SET salary = 3000 WHERE NAME='zhangsan';-- 将姓名为’lisi’的员工薪水修改为4000元,sex改为female。UPDATE employee SET salary = 4000,sex = 'female' WHERE NAME='lisi';-- 将xiaohong的薪水在原有基础上增加1000元。UPDATE employee SET salary = salary + 1000 WHERE NAME='xiaohong';-- select练习CREATE TABLE student( id INT, NAME VARCHAR(20), chinese INT, english INT, math INT);INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);-- 查询表中所有学生的信息。SELECT * FROM student;-- 查询表中所有学生的姓名和对应的英语成绩。SELECT NAME '名字', english '英语成绩' FROM student;-- 过滤表中重复数据。SELECT DISTINCT * FROM student;-- 统计每个学生的总分。SELECT NAME '名字', (chinese + english + math) '总成绩' FROM student;-- 在所有学生总分数上加10分特长分。SELECT NAME '名字', (chinese + english + math + 10) '总成绩' FROM student;-- 使用别名表示学生分数。-- 查询英语成绩大于90分的同学SELECT * FROM student WHERE english > 90;-- 查询总分大于200分的所有同学SELECT * FROM student WHERE (chinese + english + math) > 200;-- 查询英语分数在 80－90之间的同学。SELECT * FROM student WHERE english BETWEEN 80 AND 90;-- 查询英语分数不在 80－90之间的同学。SELECT * FROM student WHERE NOT(english BETWEEN 80 AND 90);-- 查询数学分数为89,90,91的同学。SELECT * FROM student WHERE math IN(90,89,91)-- 查询所有姓李的学生英语成绩。SELECT NAME,english FROM student WHERE NAME LIKE '李%';-- 查询数学分80并且语文分80的同学。SELECT * FROM student WHERE chinese = 80 AND math = 80;-- 查询英语80或者总分200的同学SELECT * FROM student WHERE english = 98 or (chinese + english + math)=200;
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
chinese INT,
english INT,
math INT
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
#对数学成绩排序后输出。
#对总分排序后输出，然后再按从高到低的顺序输出
#对姓李的学生成绩排序输出
select math from student ORDER BY math ASC;
SELECT (math + english + chinese) as 总分 from student ORDER BY 总分 DESC;
SELECT name 名字,(math + english + chinese) as 总分 from student where name like '李%' ORDER BY 总分 DESC ;
-- ----------------------------------------------------------
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);
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、按员工编号升序排列不在10号部门工作的员工信息
select * from emp where deptno !=10 order by empno ASC;
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列
SELECT * from emp where ename NOT like '_A%' AND sal >800 ORDER BY (sal * 12 + comm) 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;
-- 6、求平均薪水大于2000的部门编号
SELECT deptno,avg(sal) from emp GROUP BY deptno HAVING avg(sal) > 2000;
-- 7、将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序
SELECT deptno,avg(sal) from emp GROUP BY deptno HAVING avg(sal) > 1500 ORDER BY avg(sal) DESC;
-- 部门表
CREATE TABLE DEPT(
DEPTNO INT PRIMARY KEY, -- 部门编号
DNAME VARCHAR(14) , -- 部门名称
LOC VARCHAR(13) -- 部门地址
) ;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
-- 员工表
CREATE TABLE EMP
(
EMPNO INT PRIMARY KEY, -- 员工编号
ENAME VARCHAR(10), -- 员工名称
JOB VARCHAR(9), -- 工作
MGR DOUBLE, -- 直属领导编号
HIREDATE DATE, -- 入职时间
SAL DOUBLE, -- 工资
COMM DOUBLE, -- 奖金
DEPTNO INT, -- 部门号 -- 外键列
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);
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-07-13',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-07-13',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);
-- 工资等级表
CREATE TABLE SALGRADE
(
GRADE INT, -- 工资等级
LOSAL DOUBLE, -- 最低工资
HISAL DOUBLE ); -- 最高工资
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
-- SELECT * FROM salgrade;
-- 练习(7-12题:自己练习)：
-- 1、查询部门和所属的员工
SELECT * FROM dept d,emp e WHERE d.deptno = e.deptno;
-- 2、工资水平多于smith的员工信息。
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename = 'SMITH');
-- 3、返回员工和所属上级的姓名。
SELECT e.ename,m.ename FROM emp e, emp m WHERE e.mgr = m.empno;
-- 4、返回雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。
SELECT e.ename,m.ename FROM emp e, emp m WHERE e.mgr = m.empno AND e.HIREDATE < m.HIREDATE;
-- 5、返回从事clerk工作的员工姓名和所在部门名称。
SELECT ename,dname FROM dept d,emp e WHERE d.deptno = e.deptno AND job='clerk';
-- 6、返回部门号,部门名称及其本部门的最低工资
SELECT e.deptno,d.dname,MIN(sal) FROM emp e, dept d WHERE d.deptno = e.deptno GROUP BY e.deptno;
-- 7、返回销售部(sales)所有员工的姓名。
SELECT e.ename from emp e, dept d where d.DNAME = "sales" and d.deptno = e.deptno;
-- 8、返回与SCOTT从事相同工作的员工。
SELECT * from emp where job = (SELECT job from emp where ename = 'SCOTT');
-- 9、返回与30部门员工工资水平相同(在最低和最高之间)的员工姓名与工资。
SELECT ename, sal FROM emp HAVING sal BETWEEN (SELECT MIN(sal) FROM emp WHERE DEPTNO = 30)AND (SELECT MAX(sal) FROM emp WHERE DEPTNO = 30);
-- 10、返回工资高于30部门所有员工工资水平的员工信息。
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp where deptno = 30);
-- 11、返回部门号、部门名、部门所在位置及其每个部门的员工总数。
SELECT e.DEPTNO,d.DNAME,d.LOC, COUNT(e.DEPTNO)FROM emp e ,DEPT d WHERE e.deptno = d.deptno GROUP BY e.DEPTNO;
-- 12、返回员工工作及其从事此工作的最低工资。
SELECT job,MIN(sal) FROM emp GROUP BY job;
