Jimmy Chen

A Programmer

创建SQL表练习

第一部分 创建表

  这第一部分创建表的练习主要是从 https://www.w3resource.com/mysql-exercises/create-table-exercises/ 中得到的,英文好的大家可以直接去看看,不过这里面的练习题和答案多多少少有些不合题意,最后的最后当然是欢迎大家看我的博客文章了。

Practice 1

  编写一个SQL语句创建一个countries表,包含country_id、country_name和region_id列

[my solution]

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。

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]

DROP TABLE IF EXISTS countries;
CREATE TABLE countries (
    country_id VARCHAR(2),
    country_name VARCHAR(40),
    region_id DECIMAL(10,0)
);

首先判断是否存在表,存在就删除,然后再创建表,大概是符合题目意思,但是给的标准答案就是另外的意思了,并没有要删除的意思,反而只是判断表是否存在,如果存在就不创建了。

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]

CREATE TABLE IF NOT EXISTS dup_countries LIKE countries;

Practice 4

  编写一条SQL语句创建一个包含countries表中结构和数据的拷贝的表dup_countries

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约束。

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]

CREATE TABLE IF NOT EXISTS jobs (
    job_id INT,
    job_title VARCHAR(40),
    min_salary INT,
    max_salary INT CHECK(max_salary <= 25000)
);

下面是网站上标准的答案

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之外的国家不会被列入表中

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列的数据格式为‘--/--/----’

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列的数据时独一无二的。

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]

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,好像题目也没说,只能说那个网站上的答案多多少少都有点不合题目意思就是了,自己知道将就看吧。

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列为主键,该列不允许出现重复的数据

按照上面的提示,那就简单了,只是添加一个主键定义而已

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列后面了,这样定义简洁一些,是个不错的选择

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类型才行

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列的组合是唯一的

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表的数据结构如下图示:

《创建SQL表练习》

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列的外键

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表练习》

首先需要通过下面的SQL语句,来创建departments表

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语句如下:

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表

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d 博主赞过: