MySQL exercise one: basic operations of data tables

Have learned various operations of MySQL, such as creating tables, adding various constraints, generating table structures, and modifying and deleting tables. A practical exercise is given to comprehensively review the basic operation foundation of the data table.

---

Case: Create the database company, create two data tables offices and employees in the company database according to the table structure given in the following two tables, and complete the basic operations of the data table according to the operation process .

(Free learning recommendation: mysql video tutorial)

操作过程如下：

(1): Log in to MySQL.

mysql -h localhost -u root -p

Open the windows command line and enter the login username and password:

C:\Users\Hudie>mysql -h localhost -u root -p
Enter password: ********Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 19Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>_

The login is successful and you can enter SQL statements for operation.

---

(2): Create database company.

create database company;

mysql> create database company;Query OK, 1 row affected (0.06 sec)

After successful creation, create a data table in the company database, and you must select the database first. The SQL statement is as follows:

mysql> use company;Database changed

---

(3): Create table offices.

create table offices

mysql> create table offices    -> (
    -> officeCode int(10) not null unique,
    -> city varchar(50) not null,
    -> address varchar(50) not null,
    -> country varchar(50) not null,
    -> postalCode varchar(15) not null,
    -> primary key (officeCode)
    -> );Query OK, 0 rows affected (0.14 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| offices           |+-------------------+1 row in set (0.00 sec)

(4): Create table employees.

create table employees

mysql> create table employees    -> (
    -> employeeNumber int(11) not null primary key auto_increment,
    -> lastNamee varchar(50) not null,
    -> firstName varchar(50) not null,
    -> mobile varchar(25) not null,
    -> officeCode int (10) not null,
    -> jobTitle varchar(50) not null,
    -> birth datetime,
    -> noth varchar(25),
    -> sex varchar(5),
    -> constraint office_fk foreign key(officeCode) references offices(officeCode)
    -> );Query OK, 0 rows affected (0.14 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees         || offices           |+-------------------+2 rows in set (0.01 sec)

Creation is successful, check the structure of the two tables:

mysql> desc offices;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| officeCode | int(10)     | NO   | PRI | NULL    |       || city       | varchar(50) | NO   |     | NULL    |       || address    | varchar(50) | NO   |     | NULL    |       || country    | varchar(50) | NO   |     | NULL    |       || postalCode | varchar(15) | NO   |     | NULL    |       |+------------+-------------+------+-----+---------+-------+5 rows in set (0.06 sec)mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NULL    | auto_increment || lastNamee      | varchar(50) | NO   |     | NULL    |                || firstName      | varchar(50) | NO   |     | NULL    |                || mobile         | varchar(25) | NO   |     | NULL    |                || officeCode     | int(10)     | NO   | MUL | NULL    |                || jobTitle       | varchar(50) | NO   |     | NULL    |                || birth          | datetime    | YES  |     | NULL    |                || noth           | varchar(25) | YES  |     | NULL    |                || sex            | varchar(5)  | YES  |     | NULL    |                |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)

---

(5): Modify the mobile field of the employees table to behind the officeCode field.

alter table employees modify mobile varchar(25) after officeCode;

mysql> alter table employees modify mobile varchar(25) after officeCode;Query OK, 0 rows affected (0.18 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NULL    | auto_increment || lastNamee      | varchar(50) | NO   |     | NULL    |                || firstName      | varchar(50) | NO   |     | NULL    |                || officeCode     | int(10)     | NO   | MUL | NULL    |                || mobile         | varchar(25) | YES  |     | NULL    |                || jobTitle       | varchar(50) | NO   |     | NULL    |                || birth          | datetime    | YES  |     | NULL    |                || noth           | varchar(25) | YES  |     | NULL    |                || sex            | varchar(5)  | YES  |     | NULL    |                |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)

---

(6): Rename the birth field of table employees to employee_birth.

alter table employees change birth employee_birth datetime;

mysql> alter table employees change birth employee_birth datetime;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NULL    | auto_increment || lastNamee      | varchar(50) | NO   |     | NULL    |                || firstName      | varchar(50) | NO   |     | NULL    |                || officeCode     | int(10)     | NO   | MUL | NULL    |                || mobile         | varchar(25) | YES  |     | NULL    |                || jobTitle       | varchar(50) | NO   |     | NULL    |                || employee_birth | datetime    | YES  |     | NULL    |                || noth           | varchar(25) | YES  |     | NULL    |                || sex            | varchar(5)  | YES  |     | NULL    |                |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.00 sec)

---

(7): Modify the sex field, set the data type to char(1), and non-null constraints.

alter table employees modify sex char(1) not null;

mysql> alter table employees modify sex char(1) not null;Query OK, 0 rows affected (0.20 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NULL    | auto_increment || lastNamee      | varchar(50) | NO   |     | NULL    |                || firstName      | varchar(50) | NO   |     | NULL    |                || officeCode     | int(10)     | NO   | MUL | NULL    |                || mobile         | varchar(25) | YES  |     | NULL    |                || jobTitle       | varchar(50) | NO   |     | NULL    |                || employee_birth | datetime    | YES  |     | NULL    |                || noth           | varchar(25) | YES  |     | NULL    |                || sex            | char(1)     | NO   |     | NULL    |                |+----------------+-------------+------+-----+---------+----------------+9 rows in set (0.01 sec)

---

(8): Delete field not.

alter table employees drop noth;

mysql> alter table employees drop noth;Query OK, 0 rows affected (0.15 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NULL    | auto_increment || lastNamee      | varchar(50) | NO   |     | NULL    |                || firstName      | varchar(50) | NO   |     | NULL    |                || officeCode     | int(10)     | NO   | MUL | NULL    |                || mobile         | varchar(25) | YES  |     | NULL    |                || jobTitle       | varchar(50) | NO   |     | NULL    |                || employee_birth | datetime    | YES  |     | NULL    |                || sex            | char(1)     | NO   |     | NULL    |                |+----------------+-------------+------+-----+---------+----------------+8 rows in set (0.01 sec)

---

(9): Add the field name favorite_activity, the data type is varchar(100)

alter table employees add favoriate_activity varchar(100);

mysql> alter table employees add favoriate_activity varchar(100);Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc employees;+--------------------+--------------+------+-----+---------+----------------+| Field              | Type         | Null | Key | Default | Extra          |+--------------------+--------------+------+-----+---------+----------------+| employeeNumber     | int(11)      | NO   | PRI | NULL    | auto_increment || lastNamee          | varchar(50)  | NO   |     | NULL    |                || firstName          | varchar(50)  | NO   |     | NULL    |                || officeCode         | int(10)      | NO   | MUL | NULL    |                || mobile             | varchar(25)  | YES  |     | NULL    |                || jobTitle           | varchar(50)  | NO   |     | NULL    |                || employee_birth     | datetime     | YES  |     | NULL    |                || sex                | char(1)      | NO   |     | NULL    |                || favoriate_activity | varchar(100) | YES  |     | NULL    |                |+--------------------+--------------+------+-----+---------+----------------+9 rows in set (0.00 sec)

---

(10 ): Delete the main table offices

①Delete the foreign key constraints of the table:alter table employees drop foreign key office_fk;
②Delete the table offices:drop table offices;

mysql> alter table employees drop foreign key office_fk;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> drop table offices;Query OK, 0 rows affected (0.03 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees         |+-------------------+1 row in set (0.06 sec)

---

(11): Modify the storage engine of table employees to MyISAM.

alter table employees ENGINE=MyISAM;

mysql> alter table employees ENGINE=MyISAM;Query OK, 0 rows affected (0.17 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table employees \G*************************** 1. row ***************************
       Table: employeesCreate Table: CREATE TABLE `employees` (
  `employeeNumber` int(11) NOT NULL AUTO_INCREMENT,
  `lastNamee` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `officeCode` int(10) NOT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL,
  `employee_birth` datetime DEFAULT NULL,
  `sex` char(1) NOT NULL,
  `favoriate_activity` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  KEY `office_fk` (`officeCode`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

---

(12) Change the name of table employees to employees_info.

alter table employees rename employees_info;

rrree

Related free learning recommendations: mysql database(Video)

The above is the detailed content of MySQL exercise one: basic operations of data tables. For more information, please follow other related articles on the PHP Chinese website!