Day9 Java—SQL

Day9

1. SQL

1.1 知识点

• DML（数据操纵语言）——insert，delete，update
• DQL（数据查询语言）——select
• DDL（数据定义语言）——create，alter，drop
• TCL（事务控制语言）——commit，rollback
• DCL（数据控制语言）——grant，revoke

1.2 创建数据库步骤

• 打开SQL服务，并进入mysql

  • net start mysql
  • mysql -u root -p123

• 创建新的数据库zj1

  • create database zj1;

• 显示当前mysql账号下所有数据库

  • show databases;

• 选择数据库进行操作

  • use zj1;

• 建表（emp,dept,salgrade）

  • DROP TABLE IF EXISTS EMP;
    DROP TABLE IF EXISTS DEPT;
    DROP TABLE IF EXISTS SALGRADE;
    
    CREATE TABLE DEPT
           (DEPTNO int(2) not null ,
    	DNAME VARCHAR(14) ,
    	LOC VARCHAR(13),
    	primary key (DEPTNO)
    	);
    CREATE TABLE EMP
           (EMPNO int(4)  not null ,
    	ENAME VARCHAR(10),
    	JOB VARCHAR(9),
    	MGR INT(4),
    	HIREDATE DATE  DEFAULT NULL,
    	SAL DOUBLE(7,2),
    	COMM DOUBLE(7,2),
    	primary key (EMPNO),
    	DEPTNO INT(2) 
    	)
    	;
    
    CREATE TABLE SALGRADE
          ( GRADE INT,
    	LOSAL INT,
    	HISAL INT );
    
    
    INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    10, 'ACCOUNTING', 'NEW YORK'); 
    INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    20, 'RESEARCH', 'DALLAS'); 
    INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    30, 'SALES', 'CHICAGO'); 
    INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
    40, 'OPERATIONS', 'BOSTON'); 
    commit;
     
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
    , 800, NULL, 20); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
    , 1600, 300, 30); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
    , 1250, 500, 30); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
    , 2975, NULL, 20); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
    , 1250, 1400, 30); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
    , 2850, NULL, 30); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
    , 2450, NULL, 10); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
    , 3000, NULL, 20); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
    , 5000, NULL, 10); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
    , 1500, 0, 30); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
    , 1100, NULL, 20); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
    , 950, NULL, 30); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
    , 3000, NULL, 20); 
    INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
    DEPTNO ) VALUES ( 
    7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
    , 1300, NULL, 10); 
    commit;
     
    INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    1, 700, 1200); 
    INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    2, 1201, 1400); 
    INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    3, 1401, 2000); 
    INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    4, 2001, 3000); 
    INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
    5, 3001, 9999); 
    commit;
    

    • emp表

      • 

    • dept表

      • 

    • salgrade表

      • 

• 增删改查等操作

  • 增：insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(1000,'QUEEN','STUDENT',7000,'1999-02-02',2000,2,23);

  • 删：delete from dept where dname='sales';

  • 改： update dept set deptno='1',dname='ZJ' where deptno=10;

    • 改前：

    • 改后：

  • 查：

    运算符	说明
    =	等于
    <> 或 !=	不等于
    <	小于
    <=	小于等于
    >	大于
    >=	大于等于
    between…and…	之间（包含边界）
    is null
    and	并且
    or	或者
    in	包含
    not	取非
    like	模糊查询

    函数	作用
    count	计数
    sum	求和，忽略null
    avg	平均
    min	最大
    max	最小

    • 一个完整的select语句（注意先后顺序）

      • select 字段
        from 表名
        where …….
        group by ……..
        having …….(就是为了过滤分组后的数据而存在的，不可以单独的出现)
        order by ……..
        

        以上语句的执行顺序

        1. 首先执行where语句，过滤原始数据

        2. 执行group by，进行分组

        3. 执行having，对分组数据进行操作

        4. 执行select，选出数据

        5. 执行order by，排序

    /*
    1、简单查询
    */
    select ename from emp;	//查询员工姓名（一个字段）
    
    select empno, ename from emp;	//查询编号和姓名（两个字段）
    
    select * from emp;	//查询全部字段
    
    select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from emp;	//计算员工年薪并命名每个字段
    
    
    
    /*
    2、条件查询
    */
    select empno, ename, sal from emp where sal=5000;	//查询薪水为5000的员工
    
    select empno, ename from emp where job=‘manager’;		//查询job为manager的员工
    
    select empno, ename, sal from emp where sal between 1600 and 3000;	//查询薪水为1600到3000之间的员工
    
    select * from emp where comm is null;	//查询津贴为空的员工
    
    select * from emp where job='MANAGER' and sal > 2500;	//查询职位为manager并且薪水大于2500的员工
    
    select * from emp where job='MANAGER' or job='SALESMAN';	//	查询job为manager或salesman的员工
    
    select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);	//查询薪水大于1800，并且部门代码为20或30的
    
    select * from emp where job in ('manager','salesman');	//	查询出job为manager或者job为salesman的员工
    
    select * from emp where sal in(1600, 3000);	//查询薪水是1600或3000的
    
    select * from emp where ename like 'M%';	//查询姓名以M开头所有的员工
    
    select * from emp where ename like '%N';	//	查询姓名以N结尾的所有的员工
    
    select * from emp where ename like '%O%';	//查询姓名中包含O的所有的员工
    
    select * from emp where ename like '_A%';	//查询姓名中第二个字符为A的所有员工
    /*
    注意：
    Like中'%'和'_'的差别：
    1、'%'匹配任意字符出现的个数
    2、'_'只匹配一个字符
    */
    
    
    
    /*
    3、数据排序
    */
    select * from emp order by sal;	  //按照emp表中sal字段升序排序
    
    select * from emp order by sal desc;	//按照emp表中sal字段降序排序
    
    select * from emp where job='manager' order by sal;	//找出job为manager的员工并按sal升序排序
     
    select * from emp order by job desc,sal desc;	  //按照job(首字母z-a)和sal倒序
    
    
    
    /*
    4、分组函数/聚合函数/多行处理函数
    */
    select count(*) from emp;		//计emp表共有多少记录，包含null
     
    select count(*) '所有经理' from emp where job='manager';		//统计emp表中所有经理人数
     
    select count(comm) from emp;		//计comm字段不为null的记录数
     
    select count(distinct job ) from emp;		//求职位个数，distinct不计算重复的职位
     
    select sum(sal+IFNULL(comm, 0)) '总工资' from emp;		//由于comm字段有null值，所以无法计算，sum会忽略掉，所以要将comm字段为null值的转换成0，再相加就可得到总工资（sal+comm)
     
    select avg(sal) '平均薪水' from emp;		//平均sal
     
    select max(sal) '最高薪水' from emp;		//最高sal
     
    select min(sal) '最低薪水' from emp;		//最低sal 
    
    select min(hiredate) '最早入职' from emp;		//最早入职时间
    
    select count(*) '员工数' ,sum(sal) '总薪水',avg(sal) '平均薪水',max(sal) '最高薪水',min(sal)  '最低薪水' from emp;		//组合函数
    
    select job, sum(sal) from emp group by job;		//取得每个job的sal合计，要求显示岗位名称和工资合计
    注意：写命令时，参与分组的字段最好在select后面一段区域，如job
    
    
    
    /*
    5、分组查询
    */
    select job, sum(sal) from emp group by job order by sum(sal);	  //上面基础上按sum(sal)升序排序
    注意：group by与order by共同出现时，order by必须在后
    
    select empno,deptno,avg(sal) from emp group by deptno;
    /*注意：
    以上SQL语句在Oracle数据库中无法执行，执行报错。
    以上SQL语句在Mysql数据库中可以执行，但是执行结果矛盾。
    在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。*/
    
    select job, avg(sal) from emp group by job having avg(sal) >2000;		
    //对分组数据再进行过滤需要使用having子句，取得每个岗位的平均工资大于2000
    /*注意：having一定与group by一起出现*/
    
    
    
    /*
    6、连接查询
    */
    select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;		
    //（内连接）显示每个员工信息，并显示所属的部门名称
    
    select e.ename '员工姓名', m.ename '所属领导' from emp e, emp m where e.mgr=m.empno;		
    //（自连接）取得员工和所属的领导的姓名
    
    SQL92语法：
    select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno and  e.sal > 2000;	//（内连接）显示薪水大于2000的员工信息，并显示所属的部门名称
    SQL99语法：
    select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
    /*注意：and会影响效率，99语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比sql92更清晰*/
    
    左外连接：
    select e.ename '员工姓名', m.ename '所属领导' from emp e left join emp m on e.mgr=m.empno;		
    //显示员工信息，并显示所属的领导，如果某一个员工上面没有领导，那么该员工也必须显示出来（左表为主表）
    /*注意：左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示*/
    
    右外连接：
    select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno=d.deptno;		
    //（外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来
    
    sql92&内连接，匹配不到的null结果不显示：
    select e.ename,s.grade,d.dname from emp e ,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal order by s.grade desc;	
    //三张表或以上的连接查询：查询所有员工的薪水、薪水等级、部门名称，并根据薪水等级降序排列
    
    sql99&外连接，匹配不到的null结果也会显示：
    select e.ename,s.grade,d.dname from emp e  left join dept d on e.deptno=d.deptno left join salgrade s on e.sal between s.losal and hisal order by s.grade desc;		
    //查询所有员工的薪水、薪水等级、部门名称，并根据薪水等级降序排列
    
    
    
    /*
    7、子查询：
    */
    select empno,ename,sal from emp where sal>(select avg(sal) from emp) order by sal;		
    //查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水，并按薪水升序排序
    
    select e.empno,e.ename from emp e join(select distinct mgr from emp where mgr is not null) m on e.mgr=m.mgr;		//查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
    
    select a.deptno,a.avgsal,g.grade 
    from (select deptno,avg(sal) avgsal from emp group by deptno ) a join salgrade g 
    on a.avgsal between g.losal and hisal;	  
    //查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号
    
    
    
    /*
    8、分页：
    */
    select * from emp limit 1,5;     //从下标1开始，查5条数据
    
    select * from emp order by sal limit 0,5;		//薪水最低的5名员工
    

    • 外连接
      • 

2. SQL案例1

• student表

  • 

• score表

  • 

1、查询student表的所有记录
select * from student;

2、查询student表的第2条到4条记录
select * from student limit 1,3;

3、从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
select id ,name, department from student;

4、从student表中查询计算机系和英语系的学生的信息
select * from student where department in('计算机系','英语系');

5、从student表中查询年龄28~32岁的学生信息
select * from student where 2022-birth between 28 and 32;

6、从student表中查询每个院系有多少人
select department,count(*) from student GROUP BY department;

7、从score表中查询每个科目的最高分
select cname,max(grade) from score GROUP BY cname;

8、查询李四的考试科目（c_name）和考试成绩（grade）
SELECT s.name,c.cname,c.grade from student s join score c on s.name='李四' and s.id=c.stu_id;

9、用连接的方式查询所有学生的信息和考试信息
select * from student s join score c where s.id=c.stu_id;

10、计算每个学生的总成绩
select s.name,SUM(c.grade) from student s,score c where s.id=c.stu_id GROUP BY name;

11、计算每个考试科目的平均成绩
SELECT distinct cname,avg(grade) from score group by cname;

12、查询计算机成绩低于95的学生信息
select * from student s,(select * from score c where c.cname='计算机') o where s.id=o.stu_id and o.grade<95;

13、查询同时参加计算机和英语考试的学生的信息
select * 
from  student
where id in 
(select c1.stu_id 
from score c1,score c2 
where c1.cname='计算机'and c2.cname='英语' and c1.stu_id=c2.stu_id);