14.基本操作练习

现有数据结构

sql

CREATE TABLE `dept` (
  `deptno` int(2) NOT NULL,
  `dname` varchar(14) DEFAULT NULL,
  `loc` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `dept`(`deptno`,`dname`,`loc`) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');


CREATE TABLE `emp` (
  `empno` int(4) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` int(4) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `emp`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`) values (1,'dog','code',7369,'2018-06-05','5000.00','5000.00',10),(7369,'SMITH','CLERK',7902,'1980-12-17','800.00',NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20','1600.00','300.00',30),(7521,'WARD','SALESMAN',7698,'1981-02-22','1250.00','500.00',30),(7566,'JONES','MANAGER',7839,'1981-04-02','2975.00',NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28','1250.00','1400.00',30),(7698,'BLAKE','MANAGER',7839,'1981-05-01','2850.00',NULL,30),(7788,'SCOTT','ANALYST',7566,'1987-04-19','3000.00',NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08','1500.00','0.00',30),(7876,'ADAMS','CLERK',7788,'1987-05-23','1100.00',NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03','950.00',NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03','3000.00',NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23','1300.00',NULL,10);

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

sql

CREATE TABLE dept10 
 SELECT 

  * FROM
    emp 
    WHERE deptno = 10 ;

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

sql

CREATE TABLE dtest  SELECT 

  * FROM
    dept 
    WHERE deptno <40 ;

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

sql

CREATE TABLE empl  SELECT 

  * FROM
    emp 
    WHERE deptno <30 ;

-- 向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。

sql

INSERT INTO dept  VALUES(50,'HR','SY')

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

sql

INSERT INTO `java1`.`emp` (
  `empno`,
  `ename`,
  `job`,
  `mgr`,
  `hiredate`,
  `sal`
) 
VALUES
  (
    '8888',
    'BOB',
    'CLERK',
    '7788',
    '1985-03-03',
    '3000'

  ) ;

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

sql

CREATE TABLE emp_back  SELECT 

  * FROM
    emp 
    WHERE hiredate < STR_TO_DATE('1982-01-01' , '%Y-%m-%d' )

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

sql

SELECT * FROM emp 
WHERE deptno IN (
  SELECT deptno FROM dept WHERE loc ='NEW YORK' OR loc ='CHICAGO'
)


UPDATE  emp
SET sal = sal +500
WHERE deptno IN (
  SELECT deptno FROM dept WHERE loc ='NEW YORK' OR loc ='CHICAGO';
)

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

sql

 DELETE FROM  emp WHERE mgr = 7566;

14.基本操作练习 ​

14.基本操作练习