MySQL table creation and maintenance

First, import test data

[Email protected] ~]# wget https://launchpadlibrarian.net/24493586/employees_db-full-1.0.6.tar.bz2

Mysql> Source/root/employees_db/employees.sql;

View:

mysql> use employees;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> Show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| Departments |
| Dept_emp |
| Dept_manager |
| Employees |
| Salaries |
| Titles |
+---------------------+
6 rows in Set (0.00 sec)

Second, data table

A table is the core of a relational database, and a table is a collection of records (the data in the collection is unordered)

Two-dimensional table model easy for human understanding, the MySQL default storage engine is row-based storage

Each row of records is organized based on columns

Grammar:

Official website: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

CREATE [temporary] TABLE [IF not EXISTS] tbl_name

(create_definition ,.) /span>

[table_options

[partition_options]

create_definition:

Col_name column_definition 

Column_definition:
data_type [Not NULL | NULL] [DEFAULT default_value]
[Auto_increment] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT ' string ']
[Column_format {fixed| dynamic| DEFAULT}]
[STORAGE {disk| memory| DEFAULT}]
[Reference_definition]
| Data_type [GENERATED always] as (expression)
[VIRTUAL | STORED] [Not NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT ' string ']

Knowledge point 1: Create a temporary table (temporary)

To create a temporary table must has the CREATE TEMPORARY TABLES privilege

Mysql> CREATE TABLE A (id int);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT into a select 5;
Query OK, 1 row affected (0.02 sec)
Records:1 duplicates:0 warnings:0

Mysql> Create temporary table A (id int);
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| A |
+------------------+
1 row in Set (0.00 sec)

Mysql> select * from A;
Empty Set (0.00 sec)

mysql> INSERT into a select 6;
Query OK, 1 row Affected (0.00 sec)
Records:1 duplicates:0 warnings:0

Mysql> select * from A;
+------+
| ID |
+------+
| 6 |
+------+
1 row in Set (0.00 sec)

(in summary, the temporary table indicates that it can have the same name as the actual table name, but it is not recommended)

mysql> system LS/TMP-LH

-rw-r-----. 1 mysql mysql 8.4K 16:38 #sql5ad7_f_0. frm
-rw-r-----. 1 mysql mysql 8.4K 16:58 #sql5ad7_f_1. frm

Data is stored in the IBTMP1 temp table space

[email protected] mysql_data1]# ll IBTMP1
-rw-r-----. 1 mysql mysql 12582912 Nov 17:05 ibtmp1

Case 2:

Mysql> CREATE TABLE orders (O_orderkey int (one) not Null,o_custkey int (one) default Null,o_orderstatus char (1) defaul T Null,o_totalprice double default null,o_orderdate date default NULL, O_clerk char (+) default NULL, O_orderpriority Char () default NULL, o_comment varchar default null,primary key (O_orderkey), key ' I_o_custkey ' (' O_custkey '));
Query OK, 0 rows affected (0.23 sec)

Mysql> Show CREATE TABLE Orders\g
1. Row ***************************
Table:orders
Create table:create Table ' orders ' (
' O_orderkey ' int (one) is not NULL,
' O_custkey ' int (one) DEFAULT NULL,
' O_orderstatus ' char (1) DEFAULT NULL,
' O_totalprice ' double DEFAULT NULL,
' O_orderdate ' Date DEFAULT NULL,
' O_clerk ' char (+) DEFAULT NULL,
' O_orderpriority ' char (+) DEFAULT NULL,
' o_comment ' varchar DEFAULT NULL,
PRIMARY KEY (' O_orderkey '),
KEY ' I_o_custkey ' (' O_custkey ')
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.00 sec)

Mysql> Show Table status like ' orders ' \g view the state of the table
1. Row ***************************
Name:orders
Engine:innodb
Version:10
Row_format:dynamic
rows:0
avg_row_length:0
data_length:16384
max_data_length:0
index_length:16384
data_free:0
Auto_increment:null
Create_time:2017-11-28 17:24:22
Update_time:null
Check_time:null
Collation:utf8_general_ci
Checksum:null
Create_options:
Comment:
1 row in Set (0.00 sec)

Second, FOREIGN KEY constraints

[constraint [symbol ]] foreign key [ Index_name index _col_name ,.reference_definition | check (expr

Reference_definition:
REFERENCES tbl_name (Index_col_name,...)
[MATCH Full | MATCH PARTIAL | MATCH Simple]
[on DELETE Reference_option]
[on UPDATE Reference_option]

reference_option:

　　RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Case 1:

Mysql> CREATE TABLE parent (ID int (one) not null,primary key (ID)) Engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.12 sec)

Mysql> CREATE TABLE child (ID int (one) default NULL, parent_id int (one) default Null,key ' par_id ' (' parent_id '), constraint ' Child_ibfk_1 ' foreign key (' parent_id ') references parent (ID) on DELETE cascade on UPDATE cascade);
Query OK, 0 rows affected (0.13 sec)

Mysql> INSERT into parent values (1);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT into child values (+);
Query OK, 1 row affected (0.05 sec)

mysql> Update parent set id=2 where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from child;
+------+-----------+
| ID | parent_id |
+------+-----------+
| 1 | 2 |
+------+-----------+
1 row in Set (0.00 sec)

(Level update, ON DELETE cascade on UPDATE cascade the referenced child table will also be updated as soon as the parent table changes)

MySQL table creation and maintenance