Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example of basic operation of data table in mysql, which is very detailed and has certain reference value. Friends who are interested must finish it!

Example: 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 operation of the data table according to the operation procedure.

The operation process is as follows:

(1): log in to MySQL.

Mysql-h localhost-u root-p

Open the windows command line and enter the login user name and password:

C:\ Users\ Hudie > mysql-h localhost-u root-pEnter password: * Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 19Server version: 8.0.16 MySQL Community Server-GPLCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > _

If the login is successful, you can enter the SQL statement to operate.

(2): create the database company.

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

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

Mysql > use company;Database changed

(3): create table offices.

Create table officesmysql > 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 enployees.

Create table employeesmysql > 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)

Successfully created, view 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 table employees to the end of 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 and set the data type to char (1), which is not an empty constraint.

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 the field noth.

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.01sec) |

(9): add the field name favoriate_activity and the data type is varchar

Alter table employees add favoriate_activity varchar; mysql > alter table employees add favoriate_activity varchar; 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 | YES | | NULL | | +-| -+ 9 rows in set (0.00 sec)

(10): delete the main table offices

① deletes a table's foreign key constraint: alter table employees drop foreign key office_fk

② delete 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 table employees storage engine 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\ Graph * 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_ Code` 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

(12) change the table employees name to employees_info.

Alter table employees rename employees_info;mysql > alter table employees rename employees_info;Query OK, 0 rows affected (0.07 sec) mysql > show tables +-+ | Tables_in_company | +-+ | employees_info | +-+ 1 row in set (0.00 sec) above are all the contents of this article entitled "examples of basic operations of data tables in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.