SQL-练习

发表于 分类于

1. 创建 dept 表

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `dept`(
    `deptno` INT(2) NOT NULL,
    `dname` VARCHAR(14),
    `loc` VARCHAR(13),
    CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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');

2. 创建 emp 表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE `emp` (
    `empno` int(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),
    `job` VARCHAR(9),
    `mgr` int(4),
    `hiredate` DATE,
    `sal` float(7,2),
    `comm` float(7,2),
    `deptno` int(2),
    CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

其他练习

mysql 查询当天的数据_→_→BéLieve 的博客-CSDN 博客_mysql 查询当天日期数据
https://blog.csdn.net/weixin_46099269/article/details/115520341

参考

在 MySQL 中创建 Oracle 中的 dept、emp、bonus、salgrade 表 - 请叫我 Can - 博客园
https://www.cnblogs.com/CanBlog/p/10711741.html

SQL 经典实例-图书-图灵社区
https://www.ituring.com.cn/book/1691