mysql新闻管理增删改查_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)

补充：添加数据(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;删除数据 delete

delete from tname [where 条件]

truncate:清空 文件级别清空

自增重置

delete:删除 只修改数据 逐行删除 自增不会重置修改数据 update

Update tname set 字段=新值,… [where 条件]

update classroom set des = ‘des’ where cid = 2;查询数据 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%’;

说明：

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