MySQL案例（增删改查）
MySQL查询案例
i:emp表
spring mvc主要做什么
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),
mysql面试题 增删改查deptno int
)
使用git拉取代码
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,
losal int,
hisal int
);
2)插⼊数据
insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
element的input无法编辑insert into salgrade values (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
pno = 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 排序
under造句
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%’;
说明：
删除和修改⽤的相对较少，这⾥没有给出具体事例，只给出删除和修改的⽅法，数据库重点是查询，需重点掌握
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论