MySQL表的建立與維護

標籤：查看   adl   --   add   存在   status   inno   order   double   

一、匯入測試資料

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

 

mysql> source /root/employees_db/employees.sql ;

 

 查看：

mysql> use employees;
Reading table information for completion of table and column names
You 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)

 

 

二、資料表

表是關係型資料庫的核心，表是記錄的集合（集合中的資料是無序的）

二維表模型易於人類理解，mysql預設儲存引擎都是基於行儲存

每行記錄都是基於列進行組織的

文法：

官網：https://dev.mysql.com/doc/refman/5.7/en/create-table.html

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[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‘]

 

知識點1：建立暫存資料表（temporary）

To create a temporary table, you must have 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)

（綜上，暫存資料表的表明可以和實際存在的表名同名，但是建議不這樣操作）

mysql> system ls /tmp -lh

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

資料存放到ibtmp1暫存資料表空間

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

 

案例2：

mysql> create table orders ( o_orderkey int(11) not null,o_custkey int(11) default null,o_orderstatus char(1) default null,o_totalprice double default null,o_orderDATE date default null, o_clerk char(15) default null, o_orderpriority char(15) default null, o_comment varchar(79) 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(11) NOT NULL,
`o_custkey` int(11) DEFAULT NULL,
`o_orderstatus` char(1) DEFAULT NULL,
`o_totalprice` double DEFAULT NULL,
`o_orderDATE` date DEFAULT NULL,
`o_clerk` char(15) DEFAULT NULL,
`o_orderpriority` char(15) DEFAULT NULL,
`o_comment` varchar(79) 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 查看錶的狀態
*************************** 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)

 

 

二、外鍵約束

[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

 案例1：

mysql> create table parent ( id int(11) not null,primary key(id))engine=innodb default charset=latin1;
Query OK, 0 rows affected (0.12 sec)

mysql> create table child( id int(11) default null, parent_id int(11) 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 (1,1);
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)

（層級更新，on delete cascade on update cascade 只要父表發生變化，所引用的子表也會發生更新）

 

 

 

 

 

 

 

 

 

 

 

 

MySQL表的建立與維護