数据库练习

最新推荐文章于 2023-05-24 17:28:31 发布

knowledge base

最新推荐文章于 2023-05-24 17:28:31 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签：数据库

本文链接：https://blog.youkuaiyun.com/Lc_970407/article/details/86547090

数据库专栏收录该内容

3 篇文章

订阅专栏

（二~四）数据库和数据库表管理

#创建表

create table employee(
EID int PRIMARY KEY,
ENAME varchar(25),
DATE DATE,
DEPARTMENT_ID int,
SALARY FLOAT,
CONSTRAINT employee_fk FOREIGN KEY (DEPARTMENT_ID) REFERENCES department(did)
)

CREATE table department(
DID int PRIMARY KEY,
DNAME varchar(25),
TELL int
)
#添加记录

insert into department(DID,DNAME,TELL) VALUES(101,‘人事部’,30221)

insert into department(DID,DNAME,TELL) VALUES(102,‘宣传部’,30222)
insert into department(DID,DNAME,TELL) VALUES(103,‘销售部’,30223)

insert into employee(EID,ENAME,DATE,DEPARTMENT_ID,SALARY) values(001,‘张飞’,now(),101,1000.0);
insert into employee(EID,ENAME,DATE,DEPARTMENT_ID,SALARY) values(002,‘张辉’,now(),102,2000.1);
insert into employee(EID,ENAME,DATE,DEPARTMENT_ID,SALARY) values(003,‘张肥’,SYSDATE(),101,3000);
insert into employee(EID,ENAME,DATE,DEPARTMENT_ID,SALARY) values(004,‘张发’,SYSDATE(),103,NULL);
#修改

`UPDATE department set DNAME=‘开发部’ where DNAME=‘人事部’;

UPDATE employee set DATE=DATE_ADD(DATE,INTERVAL 1 MONTH) where DEPARTMENT_ID=101;/*时间推迟一个月*/
UPDATE employee set SALARY=SALARY+1000 where DEPARTMENT_ID=101;

#删除

DELETE FROM employee where SALARY is NULL
#复制表

复制一个表结构的实现方法有两种。
方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
create table 新表名 like 源表
方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
create table 新表名 select * from 源表
#修改表

修改person表结构如下：

alter table person drop interest;
alter table person add person_no int auto_increment  primary key first;
alter table person add person_name char(10) not null after person_no;

alter table person change person_name name char(20);
alter table person modify name char(30);

#约束

PRIMARY KEY，指定字段为主键。
AUTO_INCREMENT，指定字段为自动增加字段。auto_increment
INDEX，为字段创建索引。
NOT NULL，字段值不允许为空。
NULL 字段值可以为空。
COMMENT，设置字段的注释信息。
DEFAULT，设置字段的默认值。
– 练习
1.学校想做一个选课系统，其中涉及到课程表，学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。

create table course(
CID INT PRIMARY KEY,
CNAME VARCHAR(20),
TNAME VARCHAR(20),
SCORE INT
)
create table STU(
SID INT PRIMARY KEY,
SNAME VARCHAR(20),
S_CID int,
CONSTRAINT CID_fk FOREIGN KEY (S_CID) REFERENCES course(CID)
);
DROP TABLE COURSE;

2.学校有一个选课系统，其中包括如下关系模式：
系(系编号：主键，
系名称: 唯一键，
系主任：非空约束，
系所在校去：取值范围只能在南湖校区和浑南校区)
班级(班级编号：主键，
班级名称：唯一键，
所属系：外键)
CREATE table DEPT(
did int primary key,
dname varchar(20) unique,
mgrno int NOT NULL,
loc varchar(20) check(loc in(‘南湖校区’,‘浑南校区’))
);

CREATE TABLE CLASS(
	cno int primary key,
	cname varchar(10) unique,
	class_did int,
	constraint did_fk foreign key (class_did) references DEPT(did)
)

3.创建学生表，包含如下属性:
学号定长字符型 10位主键
姓名变长字符型 20位非空
性别定长字符型 2位取值范围只能为男或女
出生日期日期型
所在班级

create table student(
         studentno char(10)  primary key,
         stuname varchar(20) not null,
         sex char(2) check(sex in('男','女')),
         birthdate date,
         classno varchar(10)   
);

1.通过子查询的方式创建一个表dept10,该表保存10号部门的员工数据。

create table dept10 select * from emp where deptno=10;

1.在员工表中添加一个性别列，列名为gender，类型为char(2)，默认值为“男”

alter table emp add gender char(2) default '男';

2.修改员工表中性别列的数据类型为char(4)

alter table emp change gender sex char(2);#修改列名
alter table emp change sex gender char(2);
alter table emp modify gender char(4);

3.修改员工表中性别列的默认值为“女”

alter table emp modify gender char(4) default '女';

4.删除员工表中的性别列

alter table emp drop gender;

1.创建表date_test,包含列d，类型为date型。试向date_test表中插入两条记录，一条当前系统日期记录，一条记录为“1998-08-18”。

create table date_test(
	d date
)
insert into date_test(d) values (now()); 
insert into date_test values ('1998-08-18');

2.创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表。

create table dtest like dept;#复制表结构，空表
create table dtest select * from dept where 1<0;#复制表结构，空表

insert into dtest select * from dept where deptno<40

3.创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。

create table empl select * from emp where deptno<30;

4.试为学生表student增加一列学生性别gender 默认值 “女”。

alter table student add gender char(4) default '女';

5.试修改学生姓名列数据类型为定长字符型10位。

alter table student modify gender char(10);
#char 定长字符 varchar 不定长字符

1.简述5种约束的含义。

not null 非空约束：主要作用为约束列值不能为空
primary key 主键约束：约束列值唯一且不能为空
foreign key 外键约束：约束列值与父项相关
unique 唯一约束：约束列值必须唯一
check 检查约束：约束列值在某一范围

2.创建学生关系sc，包括属性名：
选课流水号数值型主键；
学生编号非空外键
课程编号非空外键；
成绩 0-100之间；

drop table student

create table student(
	id char(10) primary key,
	sname varchar(10)
)

drop table course

create table course
(
	id int primary key,
	cname varchar(10)
)

create table sc (
       scid int primary key,
       stuno char(10) not null,
			 constraint stuno foreign key (stuno) references student(id),
       cid int,
			 constraint cid_fk foreign key (cid) references course(id),
       grade int check(grade between 0 and 100)
);

3.创建copy_emp，要求格式同emp表完全一样，不包含数据。

create table copy_emp like emp;

4.创建copy_dept，要求格式同dept表完全一样，不包含数据。

create table copy_dept like dept;

#select * from copy_dept;

5.设置copy_emp 表中外键deptno，参照copy_dept中deptno,语句能否成功,为什么？

alter table copy_emp 
add constraint copy_emp_deptno_fk foreign key(deptno) references copy_dept(deptno);

如果copy_dept表中的deptno不是主键或唯一值则不成功，反之成功

6.追加copy_dept表中主键deptno

alter table copy_dept add constraint copy_dept_deptno_pk primary key(deptno);

– 练习

1.向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。

insert into emp values(8888,'BOB','CLERK','7788','1985-03-03',3000,NULL,NULL);

1.使用CREATE TABLE emp_back as
SELECT * FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。
CREATE TABLE emp_back SELECT * FROM EMP WHERE 1=0

2.把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。

drop table emp_back;
CREATE TABLE emp_back as select * from emp where hiredate>'1982-1-1';

1.修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天

update emp set HIREDATE=DATE_ADD(HIREDATE,INTERVAL 10 DAY) where deptno=20

2.修改奖金为null的员工，奖金设置为0

UPDATE EMP SET COMM=0 WHERE COMM IS NULL;

3.修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

UPDATE EMP SET SAL=SAL+10 WHERE DEPTNO =(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK')
OR DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='CHICAGO');

1.删除经理编号为7566的员工记录

DELETE FROM EMP WHERE MGR=7566

2.删除工作在NEW YORK的员工记录

DELETE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

/*3.删除工资大于所在部门平均工资的员工记录

DELETE FROM EMP WHERE EMPNO IN (SELECT EMPNO FROM EMP,(SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) A 
WHERE EMP.SAL>A.AVGSAL AND EMP.DEPTNO=A.DEPTNO)*/

确认也就是使DML操作生效，使用提交(COMMIT)命令实现；
取消也就是使DML操作失效，使用回滚(ROLLBACK)命令实现

List item

MySQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现
begin开始一个事务
rollback事务回滚
commit 事务提交
2.直接用set来改变MySQL的自动提交模式
MySQL默认是自动提交的，也就是你提交一个sql，就直接执行！
可以通过 set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交来实现事务的处理。
但要注意当用set autocommit = 0 的时候，以后所有的sql都将作为事务处理，直到用commit确认或 rollback结束
注意当结束这个事务的同时也开启了新的事务！按第一种方法只将当前的做为一个事务!

设置保存点：如果在一个事务内，想要回滚到指定位置，不是回滚到事务的起始点，可以通过保存点(SAVEPOINT)来实现。
SAVEPOINT savepointname；–定义一个保存点语句；
ROLLBACK TO savepointname;–回滚到指定保存点

1.test表为空表，分析如下语句操作后，最后test表的状态。

BEGIN;                                      -- 开始事务操作
INSERT INTO test(id,name) values(1, 'a');  
INSERT INTO test(id,name) values(2, 'b');
SAVEPOINT s1;                               -- 设置保存点
INSERT INTO test(id,name) values(3, 'c');
INSERT INTO test(id,name) values(4, 'd');
DELETE FROM test WHERE id in (1,3);
ROLLBACK TO s1;                             -- 撤销操作至保存点 s1
DELETE FROM test WHERE id in (2,4);
COMMIT;                                     -- 提交事务，将所有修改写入数据库
ROLLBACK;                                   -- 事务已提交，回滚无效

create table test(
	id int,
	name varchar(20)
)

SELECT * FROM TEST;

#最终结果是 test 表中只有一条记录 1,a

1.使用如下语句，创建学生表student和班级表class

create table student (        -- 学生表
			xh char(4),-- 学号
			xm varchar(10),-- 姓名
			sex char(2),-- 性别
			birthday date,-- 出生日期
			sal double, -- 奖学金
			studentcid int(2) -- 学生班级号
)
Create table class(   -- 班级表
			classid int(2), -- 班级编号
			cname varchar(20),-- 班级名称
  		ccount  int(3) -- 班级人数
)

2.基于上述学生表和班级表，完成如下问题
（1）添加三个班级信息为：1，JAVA1班，null
2，JAVA2班，null
3，JAVA3班，null
insert into class values(1,‘JAVA1班’,null);
insert into class values(2,‘JAVA2班’,null);
insert into class values(3,‘JAVA3班’,null);

（2）添加学生信息如下：‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1
（3）添加学生信息如下：‘A002’,‘MIKE’,‘男’,‘1905-05-06’,10
（4）插入部分学生信息： ‘A003’,‘JOHN’,‘女’

insert into student values('A001','张三','男','01-5月-05',100,1);
insert into student values('A002','MIKE','男','1905-05-06',10,null);

insert into student values('A003','JOHN','女',null,null,null);
INSERT INTO student (xh,xm,sex)VALUES('A003','JOHN','女');

（5）将A001学生性别修改为’女’

update student set sex='女' where xh='A001';

（6）将A001学生信息修改如下：性别为男，生日设置为1980-04-01

update student set sex='男' ,birthday='1980-04-01' where xh='A001';

（7）将生日为空的学生班级修改为Java3班
#实际是修改的学生表，学生表中没有班级姓名字段，因此修改班级编号

UPDATE student
SET studentcid=(SELECT classid FROM CLASS WHERE cname='java3班')
WHERE birthday IS NULL

（8）请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数字段

UPDATE CLASS c
SET ccount=(SELECT COUNT(xh) FROM student WHERE studentcid=c.classid)

3.使用如下语句，建立以下表

CREATE TABLE copy_emp(
  empno int(4),
  ename varchar(20),
  hiredate date,
  deptno int(2),
  sal double(8,2))
drop table copy_emp

– 练习

4.在第三题表的基础上，完成下列问题
(1)在表copy_emp中插入数据，要求sal字段插入空值，部门号50，参加工作时间为2000年1月1日，其他字段随意

insert into copy_emp(sal,deptno,hiredate) values(null,50,'2000-01-01');

(2)在表copy_emp中插入数据，要求把emp表中部门号为10号部门的员工信息插入

INSERT INTO copy_emp 
SELECT empno,ename,hiredate,deptno,sal FROM emp WHERE deptno=10
#copy_emp与emp表结构不一样 不可以用select *

(3)修改copy_emp表中数据，要求10号部门所有员工涨20%的工资

update copy_emp set sal=sal*1.2 where deptno=10;

(4)修改copy_emp表中sal为空的记录，工资修改为平均工资

update copy_emp set sal=(select avg(sal) from emp) where sal is null;

(5)把工资为平均工资的员工，工资修改为空

update copy_emp set sal=null where sal=(select avg(sal) from emp);

(6)另外打开窗口2查看以上修改

(7)执行commit，窗口2中再次查看以上信息

commit;

(8)删除工资为空的员工信息

delete from copy_emp where sal is null;

(9)执行rollback

rollback;

（二）简单查询
#查询
/员工加薪)/

select 	ENAME,SALARY+SALARY*0.2 FROM employee

/实习工资年薪计算,修改列名/

select ENAME,SALARY*6+SALARY*1.2*6 as year_salary from employee

/计算时对NULL的处理（计算时有NULL结果也会变成null）把null改成0/

#DISTINCT

select distinct DEPARTMENT_ID,DATE FROM employee

#查询不在10部门的员工信息不等于10 <>10

SELECT * FROM employee WHERE DEPARTMENT_ID<>101

#and or in(…) not in（…）
select * from employee where DEPARTMENT_ID in(101,102)#值为其中之一

#BETWEEN 101 AND 120

**/模糊查询中的通配符like
%匹配字符
_任意字符
/

select * from employee where ENAME LIKE '张%'
#以张开头
select * from employee where ENAME LIKE '%飞'
#以飞结束

insert into department(DID,DNAME,TELL) VALUES(104, 'SALE_FOOD',302290)
select * from department where DNAME LIKE '_A%'#第二个字符是A
select * from department where DNAME LIKE '__L%'#第三个字符是L
select * from department where DNAME LIKE '%O_'#倒数第二个字符为O

#查询中出现关键字要转义 escape设置转义符
select * from department where DNAME LIKE ‘___@%’ ESCAPE ‘@’
#第四个字母是下划线，下划线是关键字用@做转义字符

insert into department(DID,DNAME,TELL) VALUES(105, 'SALE%FOOD',302299)
select * from department where DNAME LIKE '____ $%%' ESCAPE '$'

#第四个字母是%，%是关键字用$做转义字符

#ORDER BY 排序 asc升序 desc降序默认升序

select * from department ORDER BY DID DESC,TELL

select * from employee ORDER BY DEPARTMENT_ID DESC,SALARY DESC  #二次排序DEPARTMENT_ID相同的按SALARY降序，

#limit限制查询行数查1-3行

INSERT INTO employee (EID, ENAME, DATE, DEPARTMENT_ID, SALARY) VALUES (5, '李肥', '2017-02-10', 105, 3500);
select * from employee ORDER BY SALARY DESC limit 1,3

查询入职日期最早的前2名员工姓名，入职日期。

select ENAME, DATE from employee order by DATE limit 2  #只显示两行数据

/*#分页查询
按照每页显示5条记录，分别查询第1页，第2 页，第3页信息，要求显示员工姓名、入职日期、部门名称。
*/

– 练习
1.使用两种方式查询所有员工(EMP)信息

select * from emp;
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

2.查询(EMP)员工编号、员工姓名、员工职位、员工月薪、工作部门编号。

select empno 员工编号,ename 员工姓名,job 员工职位,sal*30 员工月薪,deptno 工作部门编号 from emp;

1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。

select ename 员工姓名,sal*30*1.2 调整后员工月薪 from emp;

2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)

select empno 员工编号,sal*30*1.2*6+sal*6 员工年薪 from emp;

1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示列标题为员工姓名，工资收入，奖金收入，总收入。

select ename 员工姓名,sumsal 工资收入,(if(comm is null,0,comm))奖金收入,sumsal+ (if(comm is null,0,comm) )总收入
from emp ,(select empno ,sal*30*1.2*6+sal*6 sumsal from emp) a 
where emp.empno=a.empno;

1.查询员工表中一共有哪几种岗位类型。

select distinct job from emp;

分别查看员工表、部门表、薪资等级表的表结构。
#查看表结构 desc 表名

desc emp;
desc dept;
desc salgrade;

1.查询职位为SALESMAN的员工编号、职位、入职日期。

select empno,job,hiredate from emp where job='SALESMAN';

2.查询1985年12月31日之前入职的员工姓名及入职日期。

select ename,hiredate from emp where hiredate<'1985-12-31';

3.查询部门编号不在10部门的员工姓名、部门编号。

select ename,deptno from emp where deptno<>10;

1.查询入职日期在82年至85年的员工姓名，入职日期。

select ename,hiredate from emp where year(hiredate) between '1982' and '1985';

2.查询月薪在3000到5000的员工姓名，月薪。

select ename,sal from emp where sal between 3000 and 5000;
#between and 包括边界值

3.查询部门编号为10或者20的员工姓名，部门编号。

select ename,deptno from emp where deptno in(10,20);

4.查询经理编号为7902, 7566, 7788的员工姓名，经理编号。

select ename,mgr from emp where mgr in(7902,7566,7788);

1.查询员工姓名以W开头的员工姓名。

select ename from emp where ename like 'W%';

2.查询员工姓名倒数第2个字符为T的员工姓名。

select ename from emp where ename like '%T_';

3.查询奖金为空的员工姓名，奖金。

SELECT ENAME,COMM FROM EMP WHERE COMM IS NULL;

1.查询工资超过2000并且职位是MANAGER,或者职位是

SALESMAN的员工姓名、职位、工资
SELECT ENAME,JOB,SAL FROM EMP WHERE (SAL>2000 AND JOB='MANAGER') OR JOB='SALESMAN';

2.查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。

SELECT ENAME,JOB,SAL FROM EMP WHERE JOB IN('MANAGER','SALESMAN');

3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。

SELECT ENAME,DEPTNO,SAL FROM EMP WHERE SAL BETWEEN 3000 AND 5000 AND DEPTNO IN (10,20);

4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。

SELECT ENAME,HIREDATE,JOB FROM EMP WHERE YEAR(HIREDATE)='1981' AND JOB NOT LIKE 'sales%';

5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。

SELECT ENAME,JOB,deptno FROM EMP 
WHERE JOB IN('MANAGER','SALESMAN') and deptno in(10,20) and ename like '%A%';

1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。

select ename,deptno from emp where deptno in(30,20)
order by sal;

2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。

select ename,deptno,sal from emp where sal between 2000 and 3000 and deptno !=10
order by deptno asc,sal desc;

3.查询入职日期在82年至83年之间，职位以SALES或者MAN
开头的员工姓名，入职日期，职位，并按照入职日期降序排序。

select ename,hiredate,job from emp where year(hiredate) between 1982 and 1983 and (job like 'SALES%'or'MAN%');
#或者'1982'

限制记录行数

使用select语句时，经常要返回前几条或者中间某几行记录，可以使用关键字limit。语法格式如下：
select 字段列表
from 数据源
limit [start,]length;
说明：
1．limit接受一个或两个整数参数。start表示从第几行记录开始输出，length表示输出的记录行数。
2．表中第一行记录的start值为0(不是 1)。
*/

1.查询入职日期最早的前5名员工姓名，入职日期。

select ename, hiredate from emp
order by hiredate asc
limit 5;

2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期。

select ename, hiredate from emp
where deptno=(select deptno from dept where loc='CHICAGO')
order by hiredate asc
limit 2;

3.按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称。
#mysql 没有rownum 用下面自定义函数显示行号

SELECT @rownum:=@rownum+1 as rownum,r.* from emp r ,(select @rownum:=0) t;
select * from emp;

– 只是显示而已，表中不会存储rownum信息

select e.rn       第1页,
          e.ename    员工姓名,
          e.hiredate 入职日期,
          e.dname    部门名称,
          e.sal      工资
     from (select rownum rn, ed.*
             from (select e.ename, e.hiredate, d.dname, e.sal
                     from emp e, dept d
                    where e.deptno = d.deptno
                    order by e.sal desc) ed
             where rownum <= 5) e;

   -- 方法一，效率低
   select e.rn       第2页,
          e.ename    员工姓名,
          e.hiredate 入职日期,
          e.dname    部门名称,
          e.sal      工资
     from (select rownum rn, ed.*
             from (select e.ename, e.hiredate, d.dname, e.sal
                     from emp e, dept d
                    where e.deptno = d.deptno
                    order by e.sal desc) ed) e
    where e.rn > 5 and e.rn <= 10;

   --  方法二 ，效率高
   select e.rn       第2页,
          e.ename    员工姓名,
          e.hiredate 入职日期,
          e.dname    部门名称,
          e.sal      工资
     from (select rownum rn, ed.*
             from (select e.ename,
                          e.hiredate,
                          d.dname,
                          e.sal
                     from emp e, dept d
                    where e.deptno = d.deptno
                    order by e.sal desc) ed
            where rownum <= 10) e
    where e.rn > 5;

   select e.rn       第3页,
          e.ename    员工姓名,
          e.hiredate 入职日期,
          e.dname    部门名称,
          e.sal      工资
     from (select rownum rn, ed.*
             from (select e.ename,
                          e.hiredate,
                          d.dname,
                          e.sal
                     from emp e, dept d
                    where e.deptno = d.deptno
                    order by e.sal desc) ed
            where rownum <= 15) e
    where e.rn > 10;

1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。

select  ename,hiredate,job from emp 
where hiredate>'1982-7-9' and job != 'SALESMAN';

2.查询员工姓名的第三个字母是a的员工姓名。

select ename from emp where ename like '__a%';

3.查询除了10、20号部门以外的员工姓名、部门编号。

select ename,deptno from emp where deptno not in(10,20);

4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。

select * from emp where deptno=30
order by sal desc,ename asc;

5.查询没有上级的员工(经理号为空)的员工姓名。

select ename from emp where mgr is null;

6.查询工资大于等于4500并且部门为10或者20的员工的姓名、工资、部门编号。

select ename,sal,deptno from emp where sal>=4500 and deptno in(10,20);

（三）常用函数

dual称为伪表，仅仅为了操作方便

一.数学函数

1.四舍五入函数 round(x,y):四舍五入至|y|位

–y=0,四舍五入成整数（y默认为0）
–y>0,四舍五入至小数点后y位
–y<0,四舍五入至小数点前|y|位

2.截断函数 TRUNCATE(X,D):从第d位开始截断,d不可为空
–d=0
–d<0
–d>0
3.取余函数 MOD(N,M):n%m,M为0返回null,m不可以为空

二.字符串函数

1.求长度 length(str);
2.转大写，转小写upper(),lower();
3.补足函数左补：lpad(str,n,str1);右补：rpad(str,n,str1);

–如果str不足n就在左面或右面用str1重复直至补全,如果str大于n就从左或右舍去多余部分
4.去除空格、字符去空格(首尾) trim(str)
去除字符串首尾的某字符串trim(str from str1);
若str1的首尾含str则去除，若不含则不变；
5.LEFT(s,n)：返回最左边指定长度的字符；RIGHT(s,n)：返回最右边指定长度的字符;
- 长度少于n取原字符，n<=0是空串
6.替换函数replace(s,s1,s2) 用S2替换s中的S1
7.截取子串K：str从start位置向后取 length长度的子串
- start从1开始为0是空串
- 子串包括start位

三.日期时间函数

1.获取当前时间now()
2.获取时间间隔 DATEDIFF(expr1,expr2)
3.DATE_ADD(date,INTERVAL expr unit) = date + interval 1 day
4.DATE_SUB(date,INTERVAL expr unit)
5.获取当前日期某个元素值EXTRACT (unit FROM date) = unit(date)
6.DATE_FORMAT(date,format) i 分’%Y-%M-%d’
7.DAYOFWEEK(date) 、DAYOFMONTH(date) 、DAYOFYEAR(date):返回日期在一周、一月、一年中是第几天
DAYNAME、MONTHNAME：返回日期的星期和月份名称；

select EXTRACT(YEAR  MONTH from HIREDATE) from emp

四.流程控制函数

1.case:

等值判定

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result …] [ELSE result] END
条件判定

CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
2.IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。
IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
3.IFNULL(expr1,expr2) | NULLIF(expr1,expr2)
假如expr1 不为NULL ，则IFNULL() 的返回值为expr1 ; 否则其返回值为expr2 //oraclce不允许前后类型不同

select round(455.55),round(455.55,0),round(455.55,1),round(455.55,-1),round(455.55,-3),round(555.55,-3) from dual;
/460,460,455.6,460,0,1000/
select TRUNCATE(455.55,0), TRUNCATE(455.55,-1),TRUNCATE(455.55,-3)from dual
/455,450.0/
SELECT MOD(10,0),MOD(10,3),MOD(10,-3),mod(-10,-3)from dual
/null,1,1,-1/

select length(‘abcd’),length(’’),length(’ ') from dual
/4,0,1/

select upper(‘abcd’),upper(‘ABcd’),lower(’ ABCD’) from dual

select lpad(‘aas’,10,‘A’) FROM DUAL
select rpad(‘aas111111’,10,‘abc’) FROM DUAL
select rpad(‘aas111111’,5,‘abc’) FROM DUAL

select trim(’ hoah ') from dual
select trim(‘te’ from ‘tttttesttte’) from dual;/不变，因为匹配首尾字符/
select trim(‘te’ from ‘tetttesttte’) from dual;/改变/

select LEFT(‘sssaa’,4) from dual
select RIGHT(‘sssaa’,4) from dual
select RIGHT(‘sssaa’,6) from dual
select RIGHT(‘sssaa’,-1) from dual

select replace(‘aaaaaaa’,‘a’,‘A’) from dual
select replace(‘aASHFIH’,‘AH’,‘AB’) from dual/不变/
select replace(‘aASHFIH’,‘AS’,‘AB’) from dual/aABHFIH/

select substring(‘aASHFIH’,2,3) from dual
select substring(‘aASHFIH’,0,3) from dual/空/
select substring(‘aASHFIH’,3,0) from dual/空/

– 练习
1.写一个查询，分别计算100.456 四舍五入到小数点后第2位，第1位，整数位的值。

select round(100.456,2),round(100.456,1),round(100.456) from dual

2.写一个查询，分别计算100.456 从小数点后第2位，第1位，整数位截断的值。

select TRUNCATE(100.456,2),TRUNCATE(100.456,1),TRUNCATE(100.456,0) from dual

/这里之后使用mysqldb/
1.显示所有员工姓名的前三个字符

select substring(ENAME,1,3) from emp
SELECT LEFT(ENAME,3) from emp

2.显示正好为5个字符的员工的姓名，工资，部门号

SELECT ENAME,SAL,DEPTNO FROM emp WHERE LENGTH(ENAME)=5

1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用length、substr）

SELECT CONCAT(UPPER(LEFT(ENAME,1)) ,SUBSTRING(LOWER(ENAME),2))AS ename,LENGTH(ENAME) as nlength FROM emp
where left(ENAME,1) in('J','A','M') ORDER BY ENAME

/* select column_1 as 列1,column_2 as 列2 from table */

2.查询员工姓名中包含大写或小写字母A的员工姓名。

select ENAME FROM emp WHERE LOWER(ENAME) LIKE '%a%'

3.显示所有员工的姓名,用a替换所有"A"
K

4.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度

select ENAME,LENGTH(ENAME) FROM emp WHERE DEPTNO  in (10,20) and DATEDIFF(HIREDATE,'1981-05-01') >0 AND ENAME LIKE '%A%'

5.查询每个职工的编号,姓名，工资
要求将查询到的数据按照一定的格式合并成一个字符串.
前10位：编号,不足部分用填充,左对齐
中间10位：姓名，不足部分用填充,左对齐
后10位：工资，不足部分用*填充,右对齐

select CONCAT(RPAD(DEPTNO,10,'*'),RPAD(ENAME,10,'*'),LPAD(SAL,10,'*')) as 员工信息串 FROM emp

1.查询前半年入职的信息
select * from employee where EXTRACT(MONTH from DATE)<=6

2.查询服务器当前时间

select now() from dual

3.查询部门10,20的员工截止到2000年1月1日，工作了多少个月，入职的月份。

#CEIL(X) 返回大于等于X的最小整数值
SELECT ENAME 员工姓名, CEIL(DATEDIFF('2000-01-01',HIREDATE)/30) AS 工作月, EXTRACT(MONTH FROM HIREDATE) 入职月份 FROM emp 
SELECT ENAME 员工姓名, CEIL(DATEDIFF('2000-01-01',HIREDATE)/30) AS 工作月, MONTH(HIREDATE) 入职月份 FROM emp

4.如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期，转正日期，入职日期是第多少月，第多少周

/*select ENAME 员工姓名,HIREDATE 入职日期, HIREDATE + INTERVAL 6 MONTH 转正日期, DAYOFMONTH(HIREDATE) 入职月,DAYOFWEEK(HIREDATE) 入职周 FROM emp where JOB IS NOT 'MANAGER'*/
SELECT ENAME 员工姓名,HIREDATE 入职日期,DATE_ADD(HIREDATE,INTERVAL 6 MONTH) 转正日期, DAYOFMONTH(HIREDATE)入职月,DAYOFWEEK(HIREDATE) 入职周 FROM emp WHERE JOB <> 'MANAGER'

1.计算2000年1月1日到现在有多少月，多少周(四舍五入).

SELECT distinct ROUND(DATEDIFF(NOW(),'2000-01-01')/30) 年数, 
                ROUND(DATEDIFF(NOW(),'2000-01-01')/7) 周数
FROM emp

2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。

select * from emp where ENAME like '__A%'

3.使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘ hello ’分别处理得到下列字符串ello、Hello、ll、hello。
select trim(‘h’from ‘hello’) from dual
select trim(’ Hello ') from dual
select trim(‘b’from ‘bllb’) from dual
select trim(’ hello ') from dual

4.将员工工资按如下格式显示：123,234.00 RMB。

SELECT CONCAT(FORMAT(sal,2),' RMB') from emp

/*
1.FORMAT函数在mysql中是数据内容格式化的，格式化后得到结果：＃＃＃，＃＃＃，＃＃＃＃＃。
SELECT FORMAT(100000,2);
输出结果：
100,000.00
2.可以格式化数据为整数或者浮点数。
select format(100.31111,2);
输出结果：
100.31
select format(100.31111,0);
输出结果：
100
3.具有四舍五入的功能
SELECT FORMAT(423423234.65534453,2);
输出结果：
423,423,234.66
*/
– 练习
5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。

select ENAME,IF(MGR IS NULL,'No Manager',MGR) FROM EMP 
select ENAME,IFNULL(MGR,'No Manager') FROM EMP

6.将员工的参加工作日期按如下格式显示：月份/年份。

SELECT ENAME, DATE_FORMAT(HIREDATE,'%m/%y') 工作日期 FROM EMP
SELECT ename,CONCAT_WS('/',MONTH(hiredate),year(hiredate)) from emp

7.在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。

select ENAME,SAL,case when SAL<1000 then SAL
                      when SAL<2000 then SAL*1.1
                      when SAL<3000 then SAL*1.15
                      else SAL*1.2
                  end 税款
from emp;

8.创建一个查询显示所有雇员的 ename和 sal.格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。

SELECT ename,lpad(sal,15,'$') SALARY FROM EMP;
 #表名，字段名可以不区分大小写

（五）多表连接查询
(1).笛卡尔积
select 列名，列名。。。from 表名别名，表名。。。where 条件
(2).内连接
1.等值连接，查询两张表内有相同的含义,列名相同用 table.修饰

select *from emp,dept where emp.deptno=dept.deptno;

2.不等值连接，根据表结构用代码呈现关系

select * from emp,salgrade where emp.sal between salgrade.LOSAL and salgrade.HISAL;

3.自身连接：自身列与列存在关系(看成两张表，取别名)

select * from emp a,emp b where a.mgr = b.empno;

–如果定义了别名之后，修饰之后只有别名可以做修饰，加修饰后会提高检索效率
–注意条件中a=b,c=b的情况，至少n-1个连接条件

(3)外连接outer join
1.交叉连接(笛卡尔积)：table1 cross join table2
select * from emp cross join dept;
2.自然连接(自然连接自动找同名字段=等值连接)：table1 natural join table2
select * from emp natural join dept;
–只有一个相同列才可以使用
3.USING子句(自然连接的补充，选择公共列)
join…using(公共列列名)：table1 join table2 using(公共列名)
select *from emp join dept using(deptno);
– 如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句来替换，以指定产生等值连接的列。
– 如果有多于一个列都匹配的情况，使用USING子句只能指定其中的一列。
– USING子句中的用到的列不能使用表名和别名作为前缀。
– NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用
4.JOIN…on 连接条件
– on 后面是连接条件其他条件放在where后
select *from emp join salgrade on emp.sal between salgrade.losal and salgrade.hisal;
– 如果要指定任意连接条件,或指定要连接的列，则可以使用ON子句；
– 也可以连接多表
select *from emp join salgrade on emp.sal between salgrade.losal and salgrade.hisal
join dept on emp.deptno=dept.deptno;
5.左外连接left outer join（左面数据都显示，右边仅显示符合约束的部分，无匹配null）
select * from dept left join emp on dept.deptno=emp.deptno;
6.右外连接right outer join：
select * from emp right join dept on emp.deptno=dept.deptno;

7.全外连接full outer join：(左右符合条件的都显示，不符合的为null)
select * from emp e full outer join emp m on e.mgr = m.empno;

—查询员工的姓名，领导姓名

select a.ename 员工姓名, b.ename 领导姓名
from emp a,emp b
where a.mgr = b.empno;

—查询员工的姓名，领导姓名,员工所在的部门名称

select a.ename 员工姓名, b.ename 领导姓名,d.dname 员工的所在部门名称
from emp a,emp b,dept d
where a.mgr = b.empno and a.deptno = d.deptno;

—查询员工的姓名，领导姓名,员工所在的部门名称，领导的所在部门名称

select a.ename 员工姓名, b.ename 领导姓名,d.dname 员工的所在部门名称,md.dname 领导的部门名称
from emp a,emp b,dept d,dept md
where a.mgr = b.empno and a.deptno = d.deptno and b.deptno=md.deptno;

—查询员工的姓名，领导姓名,员工所在的部门名称，领导的所在部门名称,员工的工资等级

select a.ename 员工姓名, b.ename 领导姓名,d.dname 员工的所在部门名称,md.dname 领导的部门名称,s.grade 员工的工资等级
from emp a,emp b,dept d,dept md,salgrade s
where a.mgr = b.empno and a.deptno = d.deptno and b.deptno=md.deptno and a.sal between s.losal and s.hisal;

– a 视为员工表 b 视为经理表
—查询员工的姓名，领导姓名,员工所在的部门名称，领导的所在部门名称,员工的工资等级，领导的工资等级

select a.ename 员工姓名, b.ename 领导姓名,d.dname 员工的所在部门名称,md.dname 领导的部门名称,s.grade 员工的工资等级,ms.grade 领导的工资等级
from emp a,emp b,dept d,dept md,salgrade s,salgrade ms
where a.mgr = b.empno and a.deptno = d.deptno and b.deptno=md.deptno and a.sal between s.losal and s.hisal and b.sal between ms.losal and ms.hisal;

– 练习
1.写一个查询，显示所有员工姓名，部门编号，部门名称。

select ENAME,emp.DEPTNO,DNAME from emp,dept where emp.DEPTNO=dept.DEPTNO

2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

select ENAME,LOC,COMM FROM emp,dept where emp.DEPTNO=dept.DEPTNO and COMM IS NOT NULL

3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

select ENAME 员工姓名 ,LOC 工作地点 FROM emp,dept where emp.DEPTNO=dept.DEPTNO and ENAME LIKE'%A%'

1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

select EMPNO 员工编号,ENAME 姓名,SAL 工资,GRADE 工资等级,LOC 所在工作城市 FROM emp,salgrade,dept
where emp.DEPTNO=dept.DEPTNO and emp.SAL BETWEEN salgrade.LOSAL and salgrade.HISAL

1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

select a.ename,a.empno,b.mgr,b.ename 
from emp a,emp b,dept 
where a.mgr=b.empno and a.deptno=dept.deptno and loc in ('NEW YORK','CHICAGO')

使用SQL-99写法，完成如下练习
1.创建一个员工表和部门表的交叉连接。

select * from emp cross join dept

2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期

select ename,dname,hiredate from emp natural join dept where hiredate>'1980-05-01'
select ename,dname,hiredate from emp natural join dept where datediff(hiredate,'1980-05-01')>0

3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点

select ename, dname, loc from emp join dept using (deptno) where loc='CHICAGO'
#using ()

4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级

select ename,dname,loc,grade 
from emp join dept on emp.deptno=dept.deptno
				 join salgrade on sal between losal and hisal
where loc='CHICAGO'

/*5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select a.ename, b.ename 
from emp a left join emp b on a.mgr=b.empno
*/

/*6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select a.ename, b.ename 
from emp a right join emp b on a.mgr=b.empno

1.显示员工SMITH的姓名，部门名称，直接上级名称

SELECT e.ename,d.dname,m.ename
FROM emp e join  dept d on e.deptno=d.deptno
           join emp m on e.mgr=m.empno;

2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

select ename,dname,sal,grade 
from emp join dept on emp.deptno=dept.deptno
         join salgrade on sal between losal and hisal
where grade > 4;

3.显示员工KING和FORD管理的员工姓名及其经理姓名。

select e.empno,e.ename,m.ename 
from emp e join emp m on e.mgr=m.empno
where m.ename in ('KING','FORD');

4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

select e.ename,e.hiredate,m.ename,m.hiredate
from emp e join emp m on e.mgr=m.empno and e.hiredate<m.hiredate

（六）分组查询
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
分组作用
1.去重=distinct group by 列名，列名…
select distinct job from emp;
select job from emp group by job;
2.统计：聚合函数/分组函数、统计函数 sum(),avg(),min(),max(),count()
–count(*、列名、distinct)
*：统计记录的个数
列名：统计不为null的个数
distinct 列名：统计不重复记录的个数
–分组函数会把null值排除在外不参与计算

select sum(comm),avg(comm),count(comm) from emp;
select sum(comm),avg(ifnull(comm,0)),count(comm) from emp;

—统计领导人数

select count(distinct mgr) from emp;

3.对组过滤的条件：group by …having 条件
–分组函数在where后不能够直接参与比较
-统计各个部门的工资总和，平均工资，要求显示部门平均工资大于2000元的部门编号和其工资总和，平均工资。

select deptno,sum(sal),avg(sal) from emp group by deptno having avg(sal) > 2000;

–where与having的区别
where是对分组前数据的过滤
having是对分组后数据的过滤
4.查询语句的执行顺序
from-where-group by-having-select-order by
5.分组函数的嵌套、与多表连接的联合使用
—统计各个部门的工资总和，平均工资，要求显示部门平均工资大于2000元的部门名称和其工资总和，平均工资。

select dept.deptno,dname,loc,sum(sal),avg(sal)
from emp,dept
where emp.deptno=dept.deptno
group by emp.deptno;

– 练习
1.查询部门20的员工，每个月的工资总和及平均工资。

SELECT  DEPTNO,SUM(SAL) SUM FROM EMP WHERE DEPTNO=20
SELECT DEPTNO,COUNT(*) COUNT FROM EMP WHERE DEPTNO=20

SELECT A.DEPTNO,A.SUM/B.COUNT AVGSAL FROM (SELECT  DEPTNO,SUM(SAL) SUM FROM EMP WHERE DEPTNO=20) A,(SELECT DEPTNO,COUNT(*) COUNT FROM EMP WHERE DEPTNO=20) B WHERE A.DEPTNO=B.DEPTNO

select sum(sal) "工资总和",avg(sal) "平均工资"
from emp
where deptno=20;

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

SELECT DEPTNO FROM DEPT WHERE LOC='CHICAGO'
SELECT COUNT(*) 员工人数,MAX(SAL) 最高工资,MIN(SAL) 最低工资 FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC='CHICAGO')

3.查询员工表中一共有几种岗位类型。

select count(distinct job) from emp;

1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select dept.deptno 部门编号,dname 部门名称,count(*) 部门人数,max(sal)最高工资,min(sal) 最低工资,count(sal)工资总和,avg(sal) 平均工资
from dept join emp on emp.deptno=dept.deptno
group by dept.deptno

2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select dept.deptno 部门编号,dname 部门名称,job 岗位,count(*) 部门人数,max(sal)最高工资,min(sal) 最低工资,count(sal)工资总和,avg(sal) 平均工资
from dept join emp on emp.deptno=dept.deptno
group by dept.deptno,dname,emp.job
#group by可以添加多个分组

/*3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

select count(d.ename) 管理的人数, e.empno 经理编号,e.ename 经理姓名
from emp e ,emp d
where e.empno =d.mgr
group by e.empno,e.ename;*/

1.查询部门人数大于2的部门编号，部门名称，部门人数。

select d.deptno,d.dname,count(e.ename)
from dept d join emp e on d.deptno=e.deptno
group by d.deptno
having(count(e.ename)>2);

#having 用起来

2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

select d.deptno 部门编号, d.dname 部门名称, count(e.ename) 部门人数,avg(sal) 平均工资
from dept d join emp e on d.deptno=e.deptno
group by d.deptno
having(count(e.ename)>2 and avg(sal)>2000)
order by 部门人数;

（七）高级查询
一.子查询
–子查询可以嵌于以下SQL子句中：
WHERE子句
HAVING子句
FROM子句
查询出比JONES为雇员工资高的其他雇员
SQL> SELECT ename
2 FROM emp
3 WHERE sal >
4 (SELECT sal
5 FROM emp
6 WHERE ename=‘JONES’);
– 子查询使用指导
子查询要用括号括起来
将子查询放在比较运算符的右边
对于单行子查询要使用单行运算符
对于多行子查询要使用多行运算符
单行(列)子查询：返回单行单列数据，出现的where,having后面

select deptno
from emp
where lower(ename)='smith';


select *
from emp
where deptno = (select deptno
                from emp
                where lower(ename)='smith');

—查询部门名称为sales的部门工资总和

select deptno,sum(sal)
from emp
group by deptno;

select dname,sumsal
from dept,(select deptno,sum(sal) sumsal
           from emp
           group by deptno) t
where dept.deptno=t.deptno and lower(dname)='sales';

多行子查询：返回结果是单列多行的数据
in 运算符集合运算
any – =any(): 与in相同
– >any(): 大于子查询最小值
– <any(): 小于子查询最大值
– 要排除同类
—查询比20部门的最高工资要高的其它部门的员工信息

select sal from emp where deptno=20;

select *
from emp
where sal<any(select sal from emp where deptno=20) and deptno!=20;
in:

----查询部门表的所有部门的员工信息

select deptno from dept;

select *
from emp
where deptno in(select deptno from dept);

any:
=any: 与in运算符是相同的。
select *
from emp
where deptno=any(select deptno from dept);
—查询比10部门的最低工资要高的其它部门的员工信息

select sal from emp where deptno=10;

select *
from emp
where sal > any(select sal from emp where deptno=10) and deptno!=10;

all – >all():比子查询结果中的最大值要大
– <all():比子查询结果中的最小值要小
– 不用排除同类
–查询比30部门的最高工资要高的员工信息

select *
from emp
where sal>all(select sal from emp where deptno=30);

—查询比10部门的最低工资要低的员工信息

select *
from emp
where sal<all(select sal from emp where deptno=10);

多列子查询：返回的结果是多列多行的数据
嵌套子查询：
—查询部门名称为sales的部门工资总和

select deptno,sum(sal)
from emp
group by deptno;

select dname,sumsal
from dept,(select deptno,sum(sal) sumsal
           from emp
           group by deptno) t
where dept.deptno=t.deptno and lower(dname)='sales';

—查询部门平均工资高的部门平均工资

select deptno,avg(sal)
from emp
group by deptno;

select max(avgsal)
from (select deptno,avg(sal) avgsal
      from emp
      group by deptno) t;

以上的子查询都称为嵌套子查询。子查询是可以独立执行的。
相关子查询:子查询的数据依赖于父查询的侯选行的数据

select ename,dname
from emp,dept
where emp.deptno=dept.deptno;

相关子查询：子查询的数据依赖于候选行的数据，子查询需要父查询提供的数据

select ename,dname
from emp,dept
where emp.deptno=dept.deptno;

使用相关子查询实现：

select ename,(select dname from dept where deptno=emp.deptno)
from emp;

—查询是领导的员工信息
select *

from emp
where empno in(select mgr from emp);

– not in
SQL> SELECT ename
2 FROM emp
3 WHERE empno NOT IN
4 (SELECT mgr
5 FROM emp);
no rows selected.因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符

规避方法：用相关子查询 not exists

select *
from emp e
where EXISTS(select ename from emp m where mgr=e.empno);

—查询不是领导的员工信息

select *
from emp
where empno not in(select mgr from emp);

select *
from emp e
where not EXISTS(select ename from emp m where mgr=e.empno);

– 练习
1.查询入职日期最早的员工姓名，入职日期

select min(hiredate) from emp 

select ename,hiredate from emp where hiredate=(select min(hiredate) from emp)

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

select sal from emp where ename='SMITH'

select ename,sal,dname from emp,dept
where emp.deptno=dept.deptno and sal>(select sal from emp where ename='SMITH') and loc='CHICAGO'

‘这里要注意，多表连接一定要有关联的条件’

3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

select min(hiredate) from emp where deptno=20

select ename,hiredate from emp where hiredate < (select min(hiredate) from emp where deptno=20)

4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数

select count(*) sum from emp d group by deptno
select avg(sum) from (select count(*) sum from emp group by deptno) d

select dept.deptno,dname,sum from dept,(select deptno,count(*) sum from emp group by deptno) d
where sum > (select round(avg(sum)) from (select count(*) sum from emp group by deptno) d) and dept.deptno=d.deptno

‘这里要注意，多表连接一定要有关联的条件’

1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工

select ename,hiredate from emp where hiredate > ANY(select hiredate from emp where deptno=10) and deptno !=10

2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

select ename,hiredate from emp where hiredate > All(select hiredate from emp where deptno=10) and deptno !=10

3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

select job from emp where deptno=10
select ename, job from emp where job=any(select job from emp where deptno=10) and deptno!=10

1.查询部门平均工资在2500元以上的部门名称及平均工资。

select deptno,avg(sal) avgsal from emp group by deptno
select dname,avgsal from dept,(select deptno,avg(sal) avgsal from emp group by deptno) d 
where dept.deptno=d.deptno and avgsal>2500

2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

select dname,avgsal from dept,(select deptno,avg(sal) avgsal from emp group by deptno) d 
where dept.deptno=d.deptno and avgsal>2500 and dname not like 'SA%'

3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

select deptno,count(*) sum from emp group by deptno

select dname,dept.deptno,min(sal) minsal,max(sal) maxsal from emp,dept where emp.deptno=dept.deptno
group by deptno

select dname 部门名称,round(minsal) 最低工资,round(maxsal) 最高工资 from (select dname,dept.deptno,min(sal) minsal,max(sal) maxsal from emp,dept where emp.deptno=dept.deptno group by deptno) a,(select deptno,count(*) sum from emp group by deptno) b
where a.deptno=b.deptno and b.sum > 2

4.查询岗位不为SALES，工资和大于等于2500的岗位及每种岗位的工资和。

select dname ,sum(sal) sum from dept join emp on dept.deptno=emp.deptno group by dept.deptno
select dname,sum from (select dname ,sum(sal) sum from dept join emp on dept.deptno=emp.deptno group by dept.deptno) d where dname!='SALES'

/*5.显示经理编号和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于2000的，按最低工资由高到低排序。

select b.mgr,min(a.sal) minsal, b.ename from emp a join emp b on a.mgr=b.mgr group by b.mgr

select mgr, ename, minsal from (select b.mgr,min(a.sal) minsal, b.ename from emp a join emp b on a.mgr=b.mgr group by b.mgr ) d where minsal>=2000 or d.mgr=null
order by minsal desc*/

6.查询工资小于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

select sal from emp where empno=7782
select job from emp where empno=7369

select empno,ename,sal from emp where sal<(select sal from emp where empno=7782) and job=(select job from emp where empno=7369)

7.查询工资最高的员工姓名和工资。

select ename,sal from emp where sal=(select max(sal) from emp)

8.查询部门最低工资小于10号部门最低工资的部门的编号、名称及部门最低工资。

select deptno,min(sal) minsal from emp group by deptno
select min(sal) minsal from emp group by deptno

select d.deptno,dname,minsal from (select deptno,min(sal) minsal from emp group by deptno) d join dept
on d.deptno=dept.deptno where minsal<(select min(sal) from emp where deptno=10)

9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

select deptno,min(sal) minsal from emp group by deptno
select empno,ename,sal from emp join (select deptno,min(sal) minsal from emp group by deptno) d on emp.deptno=d.deptno and sal=d.minsal

10.显示经理是KING的员工姓名，工资。

SELECT EMPNO FROM EMP WHERE ENAME='KING'
SELECT ENAME,SAL FROM EMP WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='KING')

11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

SELECT HIREDATE FROM EMP WHERE UPPER(ENAME)='SMITH'
SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE>(SELECT HIREDATE FROM EMP WHERE UPPER(ENAME)='SMITH');

12.使用子查询的方式查询哪些职员在NEW YORK工作。

SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK'
SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。

SELECT DEPTNO FROM EMP WHERE UPPER(ENAME)='SMITH'
SELECT ENAME,HIREDATE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE UPPER(ENAME)='SMITH') AND UPPER(ENAME)!='SMITH'

14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

SELECT AVG(SAL) FROM EMP
SELECT EMPNO,ENAME FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP);

15.显示部门名称和人数

SELECT DEPTNO,COUNT(*) SUM FROM EMP GROUP BY DEPTNO
SELECT DNAME, SUM FROM DEPT,(SELECT DEPTNO,COUNT(*) SUM FROM EMP GROUP BY DEPTNO) D
WHERE DEPT.DEPTNO=D.DEPTNO

16.显示每个部门的最高工资的员工

SELECT DEPTNO,MAX(SAL) MAXSAL FROM EMP GROUP BY DEPTNO
SELECT D.DEPTNO,ENAME,MAXSAL FROM EMP, (SELECT DEPTNO,MAX(SAL) MAXSAL FROM EMP GROUP BY DEPTNO) D
WHERE EMP.DEPTNO=D.DEPTNO AND SAL=MAXSAL

17.显示出和员工号7369部门相同的员工姓名，工资

SELECT DEPTNO FROM EMP WHERE EMPNO=7369
SELECT ENAME,SAL FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE EMPNO=7369) AND EMPNO!=7369

18.显示出和姓名中包含“W”的员工相同部门的员工姓名

SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%W%'
SELECT ENAME FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%W%')

（八）视图与索引
一、视图
– 视图表不占空间，向视图表插入数据实际是往原表加数据，视图不变
– 修改/删除视图，原表改变，视图改变
1.创建简单视图

create view v_emp10
as select empno,ename,job
   from emp 
   where deptno=10;

– with check option 满足视图条件的才可以操作

create or replace view v_emp10
as select empno,ename,job,deptno
   from emp 
   where deptno=10
with check option;

select * from v_emp10;
insert into v_emp10(empno,ename,job,deptno) values(8889,'zhangsan','javadev',10);
insert into v_emp10(empno,ename,job,deptno) values(8887,'wangwu','javadev',20);   //报错，违规check option选项
select * from emp;

update v_emp10 set deptno=20 where empno=8889;
update v_emp10 set ename='modify' where empno=8889;

delete from v_emp10 where empno=8889;
delete from v_emp10 where deptno=20;

– 练习
1.创建一个视图，通过该视图可以查询到工资在2000-5000内并且姓名中包含有A的员工编号，姓名，工资。

create view v_emp10
as select empno,ename,sal
   from emp 
   where ename like '%A%' AND sal BETWEEN 2000 AND 5000;

2.通过上述创建的视图查询数据

SELECT * FROM v_emp10

1.创建一个视图，通过该视图可以查询到工作在NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。

CREATE VIEW V_EMP
AS select EMPNO,ENAME,EMP.DEPTNO,HIREDATE 
   from emp,dept
   where emp.deptno=dept.deptno and LOC IN('CHICAGO','NEW YORK')

SELECT * FROM V_EMP

2.创建一个视图，通过该视图可以查询到每个部门的部门名称及最低工资。

CREATE VIEW V_EMP0
AS select dname,dept.deptno,min(sal) minsal 
from emp,dept 
where emp.deptno=dept.deptno
group by deptno

SELECT DNAME,MINSAL FROM V_EMP0

3.通过如上视图，查询每个部门工资最低的员工姓名及部门名称

SELECT ENAME,DNAME
FROM V_EMP0 JOIN EMP ON V_EMP0.DEPTNO=EMP.DEPTNO
WHERE EMP.SAL=MINSAL

2.创建复杂视图
– 复杂视图可以简化查询但限制DML操作
– 视图表的子查询包含distinct关键字，只允许查询视图表
– 视图表中含有group by 子句，同上
– 视图表中包含表达式，不允许添加
– 视图表不包含非空约束列，不允许添加

create view v_emp
as select a.ename 员工姓名, b.ename 领导姓名,d.dname 员工的所在部门名称,md.dname 领导的部门名称,s.grade 员工的工资等级,ms.grade 领导的工资等级
from emp a,emp b,dept d,dept md,salgrade s,salgrade ms
where a.mgr = b.empno and a.deptno = d.deptno and b.deptno=md.deptno and a.sal between s.losal and s.hisal and b.sal between ms.losal and ms.hisal;

select * from v_emp;

insert into v_emp values('lisi','wangwu','sales','sales',2,4);

update v_emp set 领导姓名='lisi' where 员工姓名='SMITH';

delete from v_emp where 员工姓名='SMITH';

– OR REPLACE：如果所创建的视图已经存在，该选项表示修改原视图的定义

create or replace view v_emp
as select ename,job,sal from emp;

select * from v_emp;
insert into v_emp values('lisi','java',5200);

3.删除视图表
drop view 视图名;
drop view v_emp;

rollback;

– 练习
1.创建视图v_emp_20，包含20号部门的员工编号，姓名，年薪列(年薪=12*(工资+奖金）；

create view v_emp_20
as select empno,ename, (sal+ifnull(comm,0))*12 年薪 from emp where deptno=20

#与奖金相关的计算要特别注意奖金为null的情况

2.从视图v_emp_20中查询年薪大于1万元员工的信息；

select * from v_emp_20 where 年薪>10000

3.请为工资大于2000的员工创建视图，要求显示员工的部门信息，职位信息，工作地点；

create view v_emp1
as select dept.deptno,job,loc from emp join dept on dept.deptno=emp.deptno

4.针对以上视图执行insert,update,delete,语句能否成功，为什么？
满足创建视图时的where限制条件的insert,update,delete才可以成功

二.索引
（1）.索引作用
（2）索引分类
-普通索引
-惟一性索引
-全文索引
-单列索引
-多列索引
– 创建多列索引

Create table index5(
Id int,
name varchar(20),
Sex char(4),
Index index5_ns(name,sex)
);
-- 使用多列索

引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。

-空间索引
（3）创建索引（索引占用空间）
1.创建表的时候创建索引

create table index1(
id int,
name varchar(20),
sex boolean,
unique Index 索引名 (id desc,name...)
);

2.在已经存在的表上创建索引
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );

create index id_index on emp(empno)

3.使用ALTER TABLE语句来创建索引（ALTER TABLE 用于修改表的属性）
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名（属性名 [ (长度) ] [ ASC | DESC]）;

ALTER TABLE emp ADD INDEX index1 (empno)
4.删除索引
DROP INDEX 索引名 ON 表名 ;

drop index id_index on emp;
drop index index1 on emp;

(九)用户和权限管理
一、用户管理
1.创建用户
-CREATE USER user[IDENTIFIED BY [PASSWORD]‘password’]
[,user[IDENTIFIED BY [PASSWORD]‘password’]][,…];
所有库所有表所有主机都可以登录访问
-GRANT priv_type ON database.table
TO user [IDENTIFIED BY [PASSWORD] ‘password’]
[, user [IDENTIFIED BY [PASSWORD] ‘password’]] [,…]
[WITH GRANT OPTION];

create user test@localhost identified by '123456';

grant select on mysqldb.emp to test1@localhost identified by '123456';

grant select on *.* to test2@% identified by '123456';

2.修改用户
-修改用户名

rename user test@localhost to testnew@localhost;

-修改密码

alter user test1 identified by '123';

3.删除用户

drop user test1@localhost;

二、权限管理
-赋予权限

grant insert,update,select on *.* to testnew@localhost;
grant create,alter,drop on *.* to testnew@localhost;

grant all PRIVILEGES on *.* to testnew@localhost;

-回收权限

revoke select,update on *.* from testnew@localhost;

show grants for testnew@localhost;

– 练习
1.自己尝试创建一个用户user1,并授予所有数据表的SELECT和UPDATE权限

create user user1 identified by '123456';
grant SELECT,UPDATE on *.* to user1@localhost identified by '123456';
revoke all privileges on *.* from user1@localhost

drop user user1

#privileges
1.建立新用户neu

create user neu@localhost identified by '123456';

2.给用户neu授权

grant all PRIVILEGES on *.* to neu@localhost;

显示用户权限

show grants for neu@localhost;
3.回收用户neu的权限

revoke all PRIVILEGES on *.* from neu@localhost;

drop user neu@localhost;