搜索
韩顺平的java教程中数据库的源代码（mysql版）一：建库和建表：1.创建数据库：createdatabaselsydb1defaultcharactersetutf8;uselsydb1;2.创建表（dept）：createtabledept(deptnointprimarykey,dnamenvarchar(30),locnvarchar(30))defaultcharactersetutf8;3.创建表（emp）：createtableemp(empnointprimarykey,enamenvarchar(30),jobnvarchar(30),mgrint,hiredatedatetime,saldecimal(6.2),commdecimal(6.2),deptnoint,foreignkey(deptno)referencesdept(deptno))defaultcharactersetutf8;4.向dept表插入数据（先插dept再插emp）：insertintodept(deptno,dname,loc)values(10,'ACCOUNTING','NEWYORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');dept表如下：+--------+------------+----------+|deptno|dname|loc|+--------+------------+----------+|10|ACCOUNTING|NEWYORK||20|RESEARCH|DALLAS||30|SALES|CHICAGO||40|OPERATIONS|BOSTON|+--------+------------+----------+5.向emp表插入数据insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,20);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,NULL,20);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100,NULL,20);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);emp表：+-------+--------+-----------+------+---------------------+------+------+--------+|empno|ename|job|mgr|hiredate|sal|comm|deptno|+-------+--------+-----------+------+---------------------+------+------+--------+|7369|SMITH|CLERK|7902|1980-12-1700:00:00|800|NULL|20||7499|ALLEN|SALESMAN|7698|1981-02-2000:00:00|1600|300|30||7521|WARD|SALESMAN|7698|1981-02-2200:00:00|1250|500|30||7566|JONES|MANAGER|7839|1981-04-0200:00:00|2975|NULL|20||7654|MARTIN|SALESMAN|7698|1981-09-2800:00:00|1250|1400|30||7698|BLAKE|MANAGER|7839|1981-05-0100:00:00|2850|NULL|30||7782|CLARK|MANAGER|7839|1981-06-0900:00:00|2450|NULL|10||7788|SCOTT|ANALYST|7566|1987-04-1900:00:00|3000|NULL|20||7839|KING|PRESIDENT|NULL|1981-11-1700:00:00|5000|NULL|10||7844|TURNER|SALESMAN|7698|1981-09-0800:00:00|1500|0|30||7876|ADAMS|CLERK|7788|1987-05-2300:00:00|1100|NULL|20||7900|JAMES|CLERK|7698|1981-12-0300:00:00|950|NULL|30||7902|FORD|ANALYST|7566|1981-12-0300:00:00|3000|NULL|20||7934|MILLER|CLERK|7782|1982-01-2300:00:00|1300|NULL|10|+-------+--------+-----------+------+---------------------+------+------+--------+二.基本查询操作：1.如何查询工资在2000到2500的员工情况select*fromempwheresal2000andsal2500;select*fromempwheresalbetween2000and2500;between是取两边的包括2000和2500.2.模糊查询：显示首字母为S的员工的姓名和工资selectename,salfromempwhereenamelike“s%”;3.显示empno为123,345,800..的雇员情况。使用in关键字效率更高。Select*fromempwhereemonoin(123,345,800);三.复杂查询操作：1.显示员工最低和最高工资。selectename,salfromempwheresal=(selectmax(sal)fromemp)orsal=(selectmin(sal)fromemp);2.显示员工平均工资和总工资selectsum(sal),avg(sal)fromemp;3.显示高于平均工作雇员的姓名和工资,并显示平均工资。(1).selectename,sal,(selectavg(sal)fromemp)fromempwheresal(selectavg(sal)fromemp);(2).4.显示员工人数：selectcount(ename)fromemp;groupby:用于查询的结果分组统计。havingby:用于限制分组显示结果。5.显示每个部门的平均工资和最高工资mysqlselectdeptno,avg(sal)as每个部门的平均工资,max(sal)as每个部门的最高工资fromempgroupbydeptno;结果下图：+--------+-----------------------------+-----------------------------+|deptno|每个部门的平均工资|每个部门的最高工资|+--------+-----------------------------+-----------------------------+|10|2916.6667|5000||20|2175.0000|3000||30|1566.6667|2850|+--------+-----------------------------+-----------------------------+6.显示每个部门的没中岗位的平均工资和最低工资：selectavg(sal),min(sal),deptno,jobfromempgroupbydeptno,job;结果图：+-----------+----------+--------+-----------+|avg(sal)|min(sal)|deptno|job|+-----------+----------+--------+-----------+|1300.0000|1300|10|CLERK||2450.0000|2450|10|MANAGER||5000.0000|5000|10|PRESIDENT||3000.0000|3000|20|ANALYST||950.0000|800|20|CLERK||2975.0000|2975|20|MANAGER||950.0000|950|30|CLERK||2850.0000|2850|30|MANAGER||140