現在來學習一下關於表的基本操作！主要是建立表和基本的約束，之後會繼續講解介紹索引的問題！
#列--也稱為屬性列，在具體創建表的時候，必須指定列的名字和數據類型
#索引--是指根據指定的數據庫列表列建立起來的順序，提供了快速訪問數據的途徑
#------可監督表的數據，使其索引所指向的列中的數據不重復
#觸發器--是指用戶定義的命令的集合，當對一個表中的數據進行插入，更新或者刪除時這組命令就會自動
#--------自動執行，可以用來確保數據的完整性和安全性
create database company;
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| mysql |
| performance_schema |
| test |
+--------------------+
use company;
create table t_dept(deptno INTEGER,dname VARCHAR(20),loc VARCHAR(40));
describe t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
show create table t_dept \G
*************************** 1. row ***************************
Table: t_dept
Create Table: CREATE TABLE `t_dept` (
`deptno` int(11) DEFAULT NULL,
`dname` varchar(20) DEFAULT NULL,
`loc` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
drop table t_dept;
show tables;
Empty set (0.00 sec)
alter table t_dept rename tab_dept;
show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| tab_dept |
+-------------------+
alter table tab_dept rename t_dept;
desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
alter table t_dept add descri VARCHAR(20);
#在表的首部添加一個字段
#alter table t_dept add descri VARCHAR(20) first;
#在表的某個字段後面添加一個字段
#alter table t_dept add descri VARCHAR(20) after deptno;
desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
| descri | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
alter table t_dept drop descri;
desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
alter table t_dept modify deptno VARCHAR(20);
desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | varchar(20) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
alter table t_dept modify deptno INTEGER;
desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
#alter table table_name change 舊屬性名 新屬性名 舊數據類型
#alter table table_name change 舊屬性名 新屬性名 新數據類型
alter table t_dept change loc location VARCHAR(40);
desc t_dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| location | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
alter table t_dept modify location VARCHAR(40) first;
desc t_dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES | | NULL | |
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
MySQL軟件支持的完整性約束
NOT NULL--設置約束字段不能為空
DEFAULT--設置字段的默認值
UNIQUE KEY--約束字段的值唯一
PRIMARY KEY--約束字段為表的主鍵，可以作為該表記錄的唯一約束
AUTO_INCREMENT--約束字段的值為自動增加
FOREIGN KEY--約束字段為表的外鍵
alter table t_dept modify deptno INTEGER NOT NULL;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES | | NULL | |
| deptno | int(11) | NO | | NULL | |
| dname | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
alter table t_dept modify location VARCHAR(40) default 'NWPU';
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES | | NWPU | |
| deptno | int(11) | NO | | NULL | |
| dname | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
alter table t_dept modify dname VARCHAR(20) unique;
desc t_dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES | | NWPU | |
| deptno | int(11) | NO | | NULL | |
| dname | varchar(20) | YES | UNI | NULL | |
+----------+-------------+------+-----+---------+-------+
drop table t_dept;
show tables;
如果想給字段dname上的UK約束設置一個名字，可以執行SQL語句constraint
下面是創建表t_dept的語句:
create table t_dept(
deptno INTEGER,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname)
);
在具體的設置主鍵約束時，必須滿足主鍵字段的值是唯一的、非空的。
由於主鍵可以是單一字段，也可以是多個字段，因此分為單字段主鍵和多字段主鍵
create table t_dept(
deptno INTEGER primary key,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname)
);
設置多字段主鍵
create table t_dept(
deptno INTEGER,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname),
constraint pk_dname_depno primary key(deptno,dname)
);
show tables;
desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | NO | PRI | 0 | |
| dname | varchar(20) | NO | PRI | | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
drop table t_dept;
設置字段值自動增加
create table t_dept(
deptno INTEGER auto_increment,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname),
constraint pk_dname_depno primary key(deptno,dname)
);
desc t_dept;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| deptno | int(11) | NO | PRI | NULL | auto_increment |
| dname | varchar(20) | NO | PRI | | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
設置外鍵約束通常能表示多個表之間的參照性的完整性約束，即構建於兩個
表的兩個字段之間的參照關系
設置外鍵約束的兩個表之間會具有父子關系，即子表中某個字段的取值范圍由
父表決定，表示一種部門和雇員關系，即每個部分有多少雇員。
首先應該有兩個表：部門表和雇員表，雇員表中有一個字段表示部門編號的字段deptno
其依賴於部門表的主鍵，這樣字段deptno就是雇員表的外鍵，通過該字段部門編號的字段deptno
其依賴於部門表的主鍵，這樣字段deptno就是雇員表的外鍵。
create table table_name (
屬性名 數據類型,
屬性名 數據類型,
......
constraint 外鍵約束名 foreign key (屬性名1)
references 表明(屬性名2)
);
create table t_employee(
empno INTEGER primary key,
ename VARCHAR(20),
job VARCHAR(20),
MGR INTEGER,
Hiredate date,
sal double(10,2),
comm double(10,2),
deptno INTEGER,
constraint fk_deptno foreign key(deptno) references t_dept(deptno)
);
show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| t_dept |
| t_employee |
+-------------------+
desc t_employee;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| MGR | int(11) | YES | | NULL | |
| Hiredate | date | YES | | NULL | |
| sal | double(10,2) | YES | | NULL | |
| comm | double(10,2) | YES | | NULL | |
| deptno | int(11) | YES | MUL | NULL | |
+----------+--------------+------+-----+---------+-------+
