小编在这里总结了两个MySQL小例子希望对大家的学习有所帮助 创建表向表中插入数据
#创建EMP表 CREATE TABLE EMP( EMPNO INT(4) PRIMARY KEY, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT(4), HIREDATE TIMESTAMP, SAL DOUBLE(7,2), COMM DOUBLE(7,2), DEPTNO INT(2) ); #修改表信息（添加主键） ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPT FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO); #在表EMP中插入数据 INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-7-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-9-8',1500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-6-12',1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);SQL小练习一
--1.查询emp表的所有数据 select * from emp; --2.查询emp表中ename,job,sal几列,其中sal列在结果集中以别名salary查询 select ename,job,sal as "salary" from emp; --3.查询emp表中deptno,ename,年工资,并将年工资命名别名total salary select deptno,ename,sal*12 as "total salary" from emp; --4.查看emp表的结构 desc emp; --5.查询emp表中雇员号,雇员的名字,工作岗位,入职日期 select empno,ename,job,hiredate from emp; --7.查询员工表中部门编号,重复的部门编号只查询一次distinct select distinct mgr from emp; --8.在结果集中雇员名和工作岗位相连用"空格逗号空格"分隔,并将列头查询为Employee and Title select concat(ename,' ',job) 'Employee and Title' from emp; --01. 查询员工表所有数据 select * from emp; --02. 查询职位(JOB)为'PRESIDENT'的员工的工资 select sal from emp where job='president'; --03. 查询佣金(COMM)为0 或为NULL 的员工信息 select * from emp where comm is null or comm='0'; --04. 查询入职日期在1981-5-1 到1981-12-31 之间的所有员工信息 select * from emp where hiredate between '1981-5-1' and '1981-12-31'; --05. 查询所有名字长度为4 的员工的员工编号,姓名 select empno from emp where length(ename)=4; --06. 显示10 号部门的所有经理('MANAGER')和20 号部门的所有职员('CLERK')的详细信息 select * from emp where(deptno=10 and job='manager') or (deptno=20 and job='clerk'); --07. 显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息 select * from emp where ename not like '%l%' or like '%sm%'; --08. 显示各个部门经理('MANAGER')的工资 select sal from emp where job='manager'; --09. 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息 select * from emp where comm > sal; --10. 按照入职日期由新到旧排列员工信息 --时间倒序排列 select * from emp order by hiredate asc; --时间正序排列 select * from emp order by hiredate desc;SQL小练习二
select * from emp; --1.求emp表中工资总和 select sum(sal) from emp; --2.求emp表中工资平均值 select format(avg(sal),2) as avgsal from emp; --3.求emp表中最小工资 select min(sal) as minsal from emp; --4.求emp表中最大工资 select max(sal) as maxsal from emp; --5.求emp表的总行数,再求出部门编号去重后的总行数 select count(*) as result1,count(distinct mgr) as result2 from emp; --6.求有奖金的员工的行数 select count(*) from emp where comm is not null; --7.求所有员工的平均奖金 select avg(comm) from emp; --8.求每个部门的工资总和 select deptno,sum(sal) from emp group by deptno order by 1; --9.求每个部门的平均工资 select deptno,format(avg(sal),2) from emp group by deptno order by 1; --10.求每个部门的最小工资 select deptno,min(sal) from emp group by deptno; --11.求每个部门的最大工资 select deptno,max(sal) from emp group by deptno; --12.求每个部门中从事不同工作的员工的工资总和 select deptno,job,sum(sal) from emp group by deptno,job order by 1; --13.求每个部门中从事不同工作的员工的平均工资 select deptno,job,avg(sal) from emp group by deptno,job order by 1; --14.求每个部门中从事不同工作的员工的最小工资 select deptno,job,min(sal) from emp group by deptno,job order by 1; --15.求每个部门中从事不同工作的员工的最大工资 select deptno,job,max(sal) from emp group by deptno,job order by 1; --16.查出平均工资大于2000的部门 select deptno,format(avg(sal),2) from emp group by deptno having avg(sal)>2000; --01. 查询各个部门的平均工资 select deptno,format(avg(sal),2) from emp group by deptno order by 1; --02. 显示各种职位的最低工资 select job,min(sal) from emp group by job; --03. 查询每个部门的最高工资 select deptno,max(sal) from emp group by deptno; --04. 查询每个部门,每种职位的最高工资 select deptno,job,max(sal) from emp group by deptno,job order by 1;