第一部分 创建表
这第一部分创建表的练习主要是从 https://www.w3resource.com/mysql-exercises/create-table-exercises/ 中得到的，英文好的大家可以直接去看看，不过这里面的练习题和答案多多少少有些不合题意，最后的最后当然是欢迎大家看我的博客文章了。
Practice 1
编写一个SQL语句创建一个countries表，包含country_id、country_name和region_id列
[my solution]
|
1 2 3 4 5 |
CREATE TABLE countries( country_id INT, country_name VARCHAR(30), region_id INT ); |
上面是当时想的解法，毕竟country_id和region_id之类的用INT应该都能存储下来了，然后下面是标准答案，其中DECIMAL类型是用来存储精度比较高的数值，比如金额，也叫定点数。声明语法DECIMAL(M,D)，其中M是数值最大位数，范围1-65；D是小数点右侧数字个数，范围0-30，不超过M。
|
1 2 3 4 5 |
CREATE TABLE countries( country_id VARCHAR(2), country_name VARCHAR(40), region_id DECIMAL(10,0) ); |
Practice 2
编写一条SQL语句创建一个countries表，该表已存在，表中包含country_id、country_name和region_id列
[my solution]
|
1 2 3 4 5 6 |
DROP TABLE IF EXISTS countries; CREATE TABLE countries ( country_id VARCHAR(2), country_name VARCHAR(40), region_id DECIMAL(10,0) ); |
首先判断是否存在表，存在就删除，然后再创建表，大概是符合题目意思，但是给的标准答案就是另外的意思了，并没有要删除的意思，反而只是判断表是否存在，如果存在就不创建了。
|
1 2 3 4 5 |
CREATE TABLE IF NOT EXITS countries ( country_id VARCHAR(2), country_name VARCHAR(40), region_id DECIMAL(10,0) ); |
Practice 3
编写一条SQL语句创建一个类似countries结构的表格dup_countries
[my solution]
|
1 |
CREATE TABLE IF NOT EXISTS dup_countries LIKE countries; |
Practice 4
编写一条SQL语句创建一个包含countries表中结构和数据的拷贝的表dup_countries
|
1 2 3 |
DROP TABLE IF EXISTS dup_countries; CREATE TABLE IF NOT EXISTS dup_countries AS SELECT * FROM countries; |
先删除旧表dup_contries，然后通过SELECT语句将数据和结构从countries中拷贝到dup_countries中。
Practice 5
编写一条SQL语句，创建一个表countries，该表包含country_id、country_name和region_id，同时将这些列设置为非NULL约束。
|
1 2 3 4 5 6 |
DROP TABLE IF EXISTS countries; CREATE TABLE IF NOT EXISTS countries ( country_id VARCHAR(2) NOT NULL, country_name VARCHAR(40) NOT NULL, region_id DECIMAL(10,0) NOT NULL ); |
老规矩，先直接将旧表删除，然后创建列不能为空的约束的表
Practice 6
编写一条SQL语句，创建一个名为jobs的表，包含job_id、job_title、min_salary、max_salary并检查max_salary的值是否超过上限25000
[my solution]
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS jobs ( job_id INT, job_title VARCHAR(40), min_salary INT, max_salary INT CHECK(max_salary <= 25000) ); |
下面是网站上标准的答案
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS jobs ( JOB_ID varchar(10) NOT NULL, JOB_TITLE varchar(35) NOT NULL, MIN_SALARY decimal(6,0), MAX_SALARY decimal(6,0) CHECK(MAX_SALARY <= 2500) ); |
Practice 7
编写一条SQL语句，创建一个名为countries的表，表中包括country_id、country_name和region_id列，并确保除了Italy、India和China之外的国家不会被列入表中
|
1 2 3 4 5 |
CREATE TABLE IF NOT EXISTS countries ( country_id VARCHAR(2), country_name VARCHAR(40) CHECK(country_name IN('Italy', 'India', 'China')), region_id DECIMAL(10,0) ); |
Practice 8
编写一条SQL语句，创建一个名为job_histry的表，表中包括employee_id、start_date、end_date、job_id和department_id列，并确保插入数据时，输入的end_date列的数据格式为‘–/–/—-’
|
1 2 3 4 5 6 7 |
CREATE TABLE IF NOT EXISTS job_histry ( employee_id DECIMAL(6,0) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL CHECK(end_date LIKE '--/--/----'), job_id VARCHAR(10) NOT NULL, department_id DECIMAL(4, 0) NOT NULL ); |
Practice 9
编写一条SQL语句，创建一个名为countries的表，表中包括country_id、country_name和region_id列，并确保在插入数据时，country_id列的数据时独一无二的。
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS contries ( country_id VARCHAR(2), country_name VARCHAR(40), region_id DECIMAL(10, 0), UNIQUE(country_id); ); |
Practice 10
编写一条SQL语句，创建一个名为jobs的表，表中包含job_id、job_title、min_salary、max_salary列，并确保在插入数据时，如果job_title、min_salary和max_salary没有赋值的话，就讲job_title设置为空、min_salary设置为8000和max_salary设置为NULL
[my solution]
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS jobs ( JOB_ID varchar(10) NOT NULL, JOB_TITLE varchar(35) DEFAULT ' ', MIN_SALARY decimal(6,0) DEFAULT '8000', MAX_SALARY decimal(6,0) DEFAULT NULL ); |
下面是网站的标准的答案，基本类似，就是job_id列神奇的多了个UNIQUE，好像题目也没说，只能说那个网站上的答案多多少少都有点不合题目意思就是了，自己知道将就看吧。
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS jobs ( JOB_ID varchar(10) NOT NULL UNIQUE, JOB_TITLE varchar(35) DEFAULT ' ', MIN_SALARY decimal(6,0) DEFAULT '8000', MAX_SALARY decimal(6,0) DEFAULT NULL ); |
Practice 11
编写一条SQL语句，创建一个名为countries的表，表中包括country_id、country_name和region_id列，并且确保在插入数据时，country_id列为主键，该列不允许出现重复的数据
按照上面的提示，那就简单了，只是添加一个主键定义而已
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS contries ( country_id VARCHAR(2) PRIMARY KEY, country_name VARCHAR(40), region_id DECIMAL(10, 0), UNIQUE(country_id) ); |
不过标准答案，就只是将UNIQUE直接跟到country_id列后面了，这样定义简洁一些，是个不错的选择
|
1 2 3 4 5 |
CREATE TABLE IF NOT EXISTS contries ( country_id VARCHAR(2) NOT NULL UNIQUE PRIMARY KEY, country_name VARCHAR(40), region_id DECIMAL(10, 0) ); |
Practice 12
编写一条SQL语句，创建一个名为countries的表，表中包括country_id、country_name和region_id列，并且确保在插入数据时，country_id列为主键，country_id列中的数据是卫衣的，并储存一个自动递增的值。
按照题目简单啦，正常定义主键我们都会添加一个自动递增值的定义的，不过这里有一个小问题就是VARCHAR是无法自动递增的，要想自动递增，只能讲country_id列设置为INT类型才行
|
1 2 3 4 5 |
CREATE TABLE IF NOT EXISTS contries ( country_id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, country_name VARCHAR(40), region_id DECIMAL(10, 0) ); |
Practice 13
编写一条SQL语句，创建一个名为countries的表，表中包括country_id、country_name和region_id列，并且确保在插入数据时，country_id和region_id列的组合是唯一的
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS countries ( country_id INT NOT NULL, country_name VARCHAR(40), region_id DECIMAL(10, 0), PRIMARY KEY(country_id, region_id) ); |
Practice 14
编写一条SQL语句，创建一个名为job_histry的表，表中包括employee_id、start_date、end_date、job_id和department_id列，并确保插入数据时，employee_id不包含任何重复的值和外键列job_id只包含存在于jobs表中的值,jobs表的数据结构如下图示：
|
1 2 3 4 5 6 7 8 |
CREATE TABLE IF NOT EXISTS job_histry ( employee_id DECIMAL(6,0) NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, job_id VARCHAR(10) NOT NULL , department_id DECIMAL(4, 0) NOT NULL, FOREIGN KEY(job_id) REFERENCES jobs(JOB_ID) ); |
在做这一道联系的时候需要注意的是，在Practice 10中的jobs表不能够直接使用，需要做一下修改，如下，简单说就是需要将job_id设置为jobs的主键后，job_id列才能作为job_histry的job_id列的外键
|
1 2 3 4 5 6 7 |
DROP TABLE jobs; CREATE TABLE IF NOT EXISTS jobs ( JOB_ID varchar(10) NOT NULL PRIMARY KEY, JOB_TITLE varchar(35) DEFAULT ' ', MIN_SALARY decimal(6,0) DEFAULT '8000', MAX_SALARY decimal(6,0) DEFAULT NULL ); |
Practice 15
编写一条SQL语句，创建一个名为employees的表，表中包括employee_id、first_name、last_name、email、phone_number、hire_date、job_id、salary、commission、manager_id和department_id列，并确保在插入数据时，employee_id不包含重复的值；由department_id和manager_id组成的外键值仅包含存在在departments表中的唯一值。departments表的数据结构如下图示：
首先需要通过下面的SQL语句，来创建departments表
|
1 2 3 4 5 6 7 |
CREATE TABLE IF NOT EXISTS departments ( department_id DECIMAL(4, 0) NOT NULL DEFAULT 0, department_name VARCHAR(30) NOT NULL, manager_id DECIMAL(6, 0) NOt NULL DEFAULT 0, location_id DECIMAL(4, 0) , PRIMARY KEY(department_id, manager_id) ); |
departments表创建好后，就可以按照题目的意思创建employees表了，创建使用的SQL语句如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE IF NOT EXISTS employees ( employee_id DECIMAL(6, 0) NOT NULL UNIQUE PRIMARY KEY, first_name VARCHAR(20) DEFAULT NULL, last_name VARCHAR(25) NOT NULL, email VARCHAR(25) NOT NULL, phone_number VARCHAR(25) NOT NULL, hire_date date NOT NULL, job_id VARCHAR(10) NOT NULL, salary DECIMAL(10, 2) DEFAULT NULL, commision DECIMAL(2,2) DEFAULT NULL, manager_id DECIMAL(6, 0) DEFAULT NULL, department_id DECIMAL(4, 0) DEFAULT NULL, FOREIGN KEY(department_id, manager_id) REFERENCES departments(department_id, manager_id) ); |
创建的语句总体来说还是比较简单易懂的。
Practice 16
编写一条SQL语句，创建一个名为employees的表，表中包括employee_id、first_name、last_name、email、phone_number、hire_date、job_id、salary、commission、manager_id和department_id列，并在插入数据的时候确保，emplyee_id列的数据时唯一的，外键department_id是引用departments表中的department_id列，同时它的值只能使用包含在departments表的department_id列中值，另一个外键job_id引用jobs表中的job_id，同时它的值同样只能使用包含在jobs表中job_id列中的值。
这里用的jobs表可以直接使用Practice 14中的jobs表，departments表可以直接使用Practice 15中创建的departments表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DROP TABLE IF EXISTS employees; CREATE TABLE IF NOT EXISTS employees ( employee_id DECIMAL(6, 0) NOT NULL UNIQUE PRIMARY KEY, first_name VARCHAR(20) DEFAULT NULL, last_name VARCHAR(25) NOT NULL, email VARCHAR(25) NOT NULL, phone_number VARCHAR(25) NOT NULL, hire_date date NOT NULL, job_id VARCHAR(10) NOT NULL, salary DECIMAL(10, 2) DEFAULT NULL, commision DECIMAL(2,2) DEFAULT NULL, manager_id DECIMAL(6, 0) DEFAULT NULL, department_id DECIMAL(4, 0) DEFAULT NULL, FOREIGN KEY (department_id) REFERENCES departments(department_id), FOREIGN KEY (job_id) REFERENCES jobs(job_id) ); |
