MySQL Data sheet Basic Operations Three: a comprehensive example

First, create a database

Mysql> CREATE database company;mysql> use company;

Second, create a table

1. Create a table offices

Mysql> CREATE TABLE Offices    , Officecode Int (ten) not NULL UNIQUE, city    varchar (+) not NU  LL,    address varchar (+) NOT NULL,    country varchar (+) NOT NULL,    postalCode varchar (a) not NULL,    PRIMARY KEY (officecode)    );

2. Create a Table Employees

Mysql> CREATE TABLE Employees    , EmployeeNumber Int (one) not NULL PRIMARY KEY auto_increment,    -& Gt  LastName varchar (+) NOT NULL,    firstName varchar (+) NOT NULL,    mobile VARCHAR (+) not NULL,    Officecode Int (ten) not NULL,    jobtitle varchar (25) is not NULL, and    birth DATETIME,    note VARCHAR 5),    sex VARCHAR (5),    CONSTRAINT office_fk FOREIGN KEY (officecode) REFERENCES Offices (Officecode) c11/>->);

3. View the tables that the database has created

Mysql> Show tables;+-------------------+| Tables_in_company |+-------------------+| Employees         | | Offices           |+-------------------+

Mysql> desc offices;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| Officecode | Int (Ten)     | 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 operation of the table

1. Modify the mobile field of the table employees after the Officecode field

Mysql> ALTER TABLE Employees MODIFY Mobile varchar (+) 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 the table 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 with a data type of char (1), non-null constraint

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 a 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 field name favoriate_activity, the data type is varchar (+)

Mysql> ALTER TABLE Employees ADD favoriate_activity varchar (+);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 Table Offices

1) The table's foreign key is set when the table is created, 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 foreign KEY constraint for Employees table

Mysql> ALTER TABLE employees drop foreign key OFFICE_FK;

3) Delete Offices table

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

Mysql> Show tables;+-------------------+| Tables_in_company |+-------------------+| Employees         |+-------------------+

7. Modify the Employees table's storage engine 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 ( One) not NULL auto_increment,  ' lastName ' varchar (NO) NULL,  ' firstName ' varchar (NOT NULL,  ' Officecode ' int ' is not NULL,  ' mobile ' varchar (+) DEFAULT null,  ' jobtitle ' varchar (NOT NULL,  ' Employee_birth ') DateTime default NULL,  ' sex ' char (1) NOT NULL,  ' favoriate_activity ' varchar (+) default NULL,  PRIMARY Key (' EmployeeNumber '),  key ' OFFICE_FK ' (' Officecode ')) Engine=myisam DEFAULT charset=latin11 Row in Set (0.01 sec)

8. Change the Table 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 have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source!

MySQL Data sheet Basic Operations Three: a comprehensive example