MySQL5.5.21學習教程之二，mysql5.5.21教程

MySQL5.5.21學習教程之二，mysql5.5.21教程

     現在來學習一下關於表的基本操作！主要是建立表和基本的約束，之後會繼續講解介紹索引的問題！

#列--也稱為屬性列，在具體建立表的時候，必須指定列的名字和資料類型#索引--是指根據指定的資料庫列表列建立起來的順序，提供了快速存取資料的途徑#------可監督表的資料，使其索引所指向的列中的資料不重複#觸發器--是指使用者定義的命令的集合，當對一個表中的資料進行插入，更新或者刪除時這組命令就會自動#--------自動執行，可以用來確保資料的完整性和安全性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_deptCreate Table: CREATE TABLE `t_dept` (  `deptno` int(11) DEFAULT NULL,  `dname` varchar(20) DEFAULT NULL,  `loc` varchar(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8drop 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    |       |+----------+--------------+------+-----+---------+-------+