Basic MySQL Data Table operation 3: comprehensive example

Basic MySQL Data Table operation 3: comprehensive example

1. Create a database

mysql> create database company;mysql> use company; 

Ii. Create a table

1. Create an offices table

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

2. Create a table employees

mysql> create table employees    -> (    -> employeeNumber int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,    -> lastName 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,    -> note VARCHAR(255),    -> sex VARCHAR(5),    -> CONSTRAINT office_fk FOREIGN KEY (officeCode) REFERENCES offices(officeCode)    -> );

3. view the tables created in the database 

mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees         || offices           |+-------------------+

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    |       |+------------+-------------+------+-----+---------+-------+

mysql> desc employees;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment || lastName       | 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    |                || note           | varchar(255) | YES  |     | NULL    |                || sex            | varchar(5)   | YES  |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+ 

III. Basic table operations

1. Modify the mobile field of the table employees to the end of the officeCode field.

mysql> alter table employees MODIFY mobile varchar(25) after officeCode;mysql> desc employees;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment || lastName       | 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    |                || note           | varchar(255) | YES  |     | NULL    |                || sex            | varchar(5)   | YES  |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+

2. Rename the birth field of employees to employee_birth.

mysql> alter table employees CHANGE birth employee_birth DATETIME;mysql> desc employees;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment || lastName       | 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    |                || note           | varchar(255) | YES  |     | NULL    |                || sex            | varchar(5)   | YES  |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+

3. Modify the sex field. The data type is CHAR (1). It is not empty.

mysql> alter table employees MODIFY sex CHAR(1) NOT NULL;mysql> desc employees;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment || lastName       | 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    |                || note           | varchar(255) | YES  |     | NULL    |                || sex            | char(1)      | NO   |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+

4. Delete Field note

mysql> alter table employees DROP note;mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NULL    | auto_increment || lastName       | 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    |                |+----------------+-------------+------+-----+---------+----------------+

5. Add the field name favoriate_activity. The data type is VARCHAR (100)

mysql> alter table employees ADD favoriate_activity varchar(100);mysql> desc employees;+--------------------+--------------+------+-----+---------+----------------+| Field              | Type         | Null | Key | Default | Extra          |+--------------------+--------------+------+-----+---------+----------------+| employeeNumber     | int(11)      | NO   | PRI | NULL    | auto_increment || lastName           | 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    |                |+--------------------+--------------+------+-----+---------+----------------+

6. Delete the table offices

1) The foreign key of the table is set during table creation, so it cannot be deleted directly.

mysql> drop table offices;ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

2) Delete the foreign key constraint of the employees table

mysql> alter table employees drop foreign key office_fk;

3) Delete the offices table

mysql> drop table offices;Query OK, 0 rows affected (0.03 sec)

mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees         |+-------------------+

7. Modify the storage engine of the employees table to MyISAM.

mysql> alter table employees ENGINE=MyISAM;Query OK, 0 rows affected (0.12 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,  `lastName` 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=latin11 row in set (0.01 sec)

8. Change the employees table name to employees_info.

mysql> alter table employees rename employees_info;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees_info    |+-------------------+1 row in set (0.00 sec)

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!