MySQL5.5.21学习教程之二
来源：程序员人生 发布时间：2015-01-19 08:37:24 阅读次数：2731次
现在来学习1下关于表的基本操作！主要是建立表和基本的束缚，以后会继续讲授介绍索引的问题！
#列--也称为属性列，在具体创建表的时候，必须指定列的名字和数据类型
#索引--是指根据指定的
数据库列表列建立起来的顺序，提供了快速访问数据的途径
#------可监督表的数据，使其索引所指向的列中的数据不重复
#触发器--是指用户定义的命令的集合，当对1个表中的数据进行插入，更新或删除时这组命令就会自动
#--------自动履行，可以用来确保数据的完全性和安全性
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 旧属性名 新属性名 旧数据类型
#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--束缚字段的值唯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束缚设置1个名字，可以履行SQL语句constraint
下面是创建表t_dept的语句:
create table t_dept(
deptno INTEGER,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname)
);
在具体的设置主键束缚时，必须满足主键字段的值是唯1的、非空的。
由于主键可以是单1字段，也能够是多个字段，因此分为单字段主键和多字段主键
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 | |
+--------+-------------+------+-----+---------+----------------+
设置外键束缚通常能表示多个表之间的参照性的完全性束缚，即构建于两个
表的两个字段之间的参照关系
设置外键束缚的两个表之间会具有父子关系，即子表中某个字段的取值范围由
父表决定，表示1种部门和雇员关系，即每一个部份有多少雇员。
首先应当有两个表：部门表和雇员表，雇员表中有1个字段表示部门编号的字段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 | |
+----------+--------------+------+-----+---------+-------+
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
