MySQL案例（增删改查）

MySQL查询案例

i:emp表

1)建表：

CREATE TABLE emp (
empno int PRIMARY KEY,
ename varchar(20) NOT NULL,
job varchar(20) NOT NULL,
mgr int DEFAULT NULL,
hiredate date,
sal decimal(20, 2),
comm decimal(20, 2),
deptno int
)
alter table emp add CONSTRAINT FK_CID foreign key(deptno) REFERENCES dept(deptno);
2）插入数据
INSERT INTO emp VALUES (7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-17 00:00:00’, 800.00,NULL, 20);

INSERT INTO emp VALUES (7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20 00:00:00’,1600.00, 300.00, 30);

INSERT INTO emp VALUES (7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22 00:00:00’,1250.00, 500.00, 30);

INSERT INTO emp VALUES (7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-04-02 00:00:00’,2975.00, NULL, 20);

INSERT INTO emp VALUES (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28 00:00:00’,1250.00, 1400.00, 30);

INSERT INTO emp VALUES (7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-05-01 00:00:00’,2850.00, NULL, 30);

INSERT INTO emp VALUES (7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09 00:00:00’,2450.00, NULL, 10);

INSERT INTO emp VALUES (7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-04-19 00:00:00’,3000.00, NULL, 20);

INSERT INTO emp VALUES (7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981-11-17 00:00:00’,5000.00, NULL, 10);

INSERT INTO emp VALUES (7844, ‘TURNER’, ‘SALESMAN’, 7698, ‘1981-09-08 00:00:00’,1500.00, 0.00, 30);

INSERT INTO emp VALUES (7876, ‘ADAMS’, ‘CLERK’, 7788, ‘1987-05-23 00:00:00’,1100.00, NULL, 20);

INSERT INTO emp VALUES (7900, ‘JAMES’, ‘CLERK’, 7698, ‘1981-12-03 00:00:00’, 950.00,NULL, 30);

INSERT INTO emp VALUES (7934, ‘MILLER’,‘CLERK’, 7782, ‘1982-01-23 00:00:00’, 1300.00, NULL, 10);
3)emp表展示

ii：dept表

1）建表
CREATE TABLE dept (
deptno int primary key,
dname varchar(20) not null
loc varchar(20) not null
)
2）插入数据
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’);
3）dept表展示

iii:salgrade表(工资等级表)

1）建表
create table salgrade(
grade int primary key,
losalint,
hisal int
);
2)插入数据
insert into salgrade values (1,700,1200);
insert into salgradevalues (2,1201,1400);
insert into salgradevalues (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);
3）salgrade表展示

*对上面表格完成以下查询

i.
查询部门名称是‘ACCOUNTING’的员工姓名及薪水等级
select ename,grade from emp,dept,salgrade
where emp.deptno = dept.deptno and dept.dname = ‘ACCOUNTING’
and sal between losal and hisal;

ii.
查询平均薪水最低的部门名称，只统计领导薪资
select dname from dept where deptno = (
select deptno from emp
where empno in (select mgr from emp) group by deptno order by avg(sal) limit 0,1);

iii.
查询‘SALES’部门平均薪水的等级
select grade from salgrade where
(select avg(sal) avg from emp,dept where emp.deptno =
dept.deptno and dname = ‘sales’) between losal and hisal;

iv.
查询不是领导的员工信息
select * from emp e1 where not exists (select 1 from emp e2
where e1.empno = e2.mgr);

v.
求薪水最高的员工姓名 （并列时都查询出来）
select * from emp where sal = (select max(sal) from emp)

vi.
查询年收入超过10000的员工的姓名，编号，薪水，年收入，按照年收入降序排列
select empno,ename,sal,sal12 from emp where sal12 >
10000 ORDER BY sal*12 desc;

vii.
查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周
select (TO_DAYS(now()) - TO_DAYS(hiredate))/7 from emp where ename = ‘smith’;

viii.
查询第2到第7名的员工，按薪水降序排列
select * from emp order by sal desc limit 1,6;

ix.
查询最后入职的5名员工
select * from emp order by TO_DAYS(hiredate) desc limit 5;

x.
查询薪水大于所在部门平均工资的员工信息。
select * from emp e1 where sal > (
select avg(sal) from emp e2 where e1.deptno = e2.deptno)

补充：

1. 添加数据(insert)

Insert into tname[(字段名称…)] values(值…);
#添加数据classroom
#给所有字段添加值,和表中字段顺序一致
insert into classroom values(3,‘bd1809’);

#给cname字段添加值,和指定字段的顺序必须一致
insert into classroom(des,cname) values(‘des’,‘bd1810’);

#添加三条记录(批量插入)
insert into classroom(cname,des)
values(‘bd1811’,‘des’)，(‘bd1812’,‘des’)， (‘bd1901’,‘des’);

#将classroom的值整体复制到classroom1
insert into classroom1 select * from classroom;

2. 删除数据 delete

delete from tname [where 条件]
truncate:清空 文件级别清空
自增重置
delete:删除 只修改数据 逐行删除 自增不会重置

3. 修改数据 update

Update tname set 字段=新值,… [where 条件]
update classroom set des = ‘des’ where cid = 2;

4. 查询数据 select
   Select 字段|表达式
   from 表|视图|结果集

Where 条件
Group by 分组
Having 分组之后进行检索
Order by 排序
Limit 限制结果

[as] 别名
#字段 表达式 结果集 表 都可以起别名
select ename,sal*1.05 nsal from emp；

#查询所有职位信息 去重
select distinct job from emp;

order by 排序字段 默认升序排序 asc | desc
#根据员工工资降序排序,工资一致的按照员工编号的降序排序。
select * from emp order by sal desc,empno desc;

#限制结果查询 limit 下标,长度 (仅适用于mysql,分页查询）
select * from emp limit 0,5;

#查询所有有奖金的员工信息（不为空）
select * from emp where comm is not null;
#模糊查询 like
%:0到多个任意字符 _:1个字符
#查询名字以s打头的员工信息
select * from emp where ename like ‘s%’;
#包含
select * from emp where ename like ‘%s%’;
# 第二个字符为L的所有员工信息
select * from emp where ename like ‘_l%’;

说明：
删除和修改用的相对较少，这里没有给出具体事例，只给出删除和修改的方法，数据库重点是查询，需重点掌握！！！