MySQL5.5.21學(xué)習(xí)教程之二
來源：程序員人生 發(fā)布時間：2015-01-19 08:37:24 閱讀次數(shù)：2996次
現(xiàn)在來學(xué)習(xí)1下關(guān)于表的基本操作！主要是建立表和基本的束縛，以后會繼續(xù)講授介紹索引的問題！
#列--也稱為屬性列，在具體創(chuàng)建表的時候，必須指定列的名字和數(shù)據(jù)類型
#索引--是指根據(jù)指定的
數(shù)據(jù)庫列表列建立起來的順序，提供了快速訪問數(shù)據(jù)的途徑
#------可監(jiān)督表的數(shù)據(jù)，使其索引所指向的列中的數(shù)據(jù)不重復(fù)
#觸發(fā)器--是指用戶定義的命令的集合，當對1個表中的數(shù)據(jù)進行插入，更新或刪除時這組命令就會自動
#--------自動履行，可以用來確保數(shù)據(jù)的完全性和安全性
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);
#在表的首部添加1個字段
#alter table t_dept add descri VARCHAR(20) first;
#在表的某個字段后面添加1個字段
#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 舊屬性名 新屬性名 舊數(shù)據(jù)類型
#alter table table_name change 舊屬性名 新屬性名 新數(shù)據(jù)類型
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--設(shè)置束縛字段不能為空
DEFAULT--設(shè)置字段的默許值
UNIQUE KEY--束縛字段的值唯1
PRIMARY KEY--束縛字段為表的主鍵，可以作為該表記錄的唯1束縛
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束縛設(shè)置1個名字，可以履行SQL語句constraint
下面是創(chuàng)建表t_dept的語句:
create table t_dept(
deptno INTEGER,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname)
);
在具體的設(shè)置主鍵束縛時，必須滿足主鍵字段的值是唯1的、非空的。
由于主鍵可以是單1字段，也能夠是多個字段，因此分為單字段主鍵和多字段主鍵
create table t_dept(
deptno INTEGER primary key,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname)
);
設(shè)置多字段主鍵
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;
設(shè)置字段值自動增加
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 | |
+--------+-------------+------+-----+---------+----------------+
設(shè)置外鍵束縛通常能表示多個表之間的參照性的完全性束縛，即構(gòu)建于兩個
表的兩個字段之間的參照關(guān)系
設(shè)置外鍵束縛的兩個表之間會具有父子關(guān)系，即子表中某個字段的取值范圍由
父表決定，表示1種部門和雇員關(guān)系，即每一個部份有多少雇員。
首先應(yīng)當有兩個表：部門表和雇員表，雇員表中有1個字段表示部門編號的字段deptno
其依賴于部門表的主鍵，這樣字段deptno就是雇員表的外鍵，通過該字段部門編號的字段deptno
其依賴于部門表的主鍵，這樣字段deptno就是雇員表的外鍵。
create table table_name (
屬性名 數(shù)據(jù)類型,
屬性名 數(shù)據(jù)類型,
......
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 | |
+----------+--------------+------+-----+---------+-------+
生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機掃描二維碼進行捐贈
