表是数据库存储数据的基本单位。一个表包含若干个字段或记录。表的操作包括创建新表、修改表和删除表。
创建表的语法形式
CREATE TABLE 表名(属性名1 数据类型[完整约束条件],
属性名2 数据类型[完整约束条件],
….
属性名n 数据类型
);
create database example;
show databases;
use example;
create table example0(
id INT,
name VARCHAR(20),
sex BOOLEAN
);
SHOW TABLES;
DESC example;
create table example1(
stu_id INT primary key,
stu_name VARCHAR(20),
stu_sex BOOLEAN
);
show tables;
desc example1;
设置多字段主键
create table example3(
id INT primary key,
stu_id INT,
course_id INT,
constraint c_fk froeign key(stu_id,course_id) references example2(stu_id,course_id)
);
desc example4;
SHOW TABLES;
CREATE TABLE example4(
id INT primary key NOT NULL,
name VARCHAR(2) NOT NULL,
stu_id INT,
CONSTRAINT d_fk FROEIGN KEY(stu_id) REFERENCES example1(stu_id)
);
SHOW TABLES;
DESC example4;
CREATE TABLE example5(
id INT NOT NULL PRIMARY KEY,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL
);
SHOW TABLES;
DESC example5;
CREATE TABLE example6(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL
);
SHOW TABLES;
DESC example6;
CREATE TABLE example7(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL,
English VARCHAR(20) DEFAULT ‘ZERO’,
Math FLOAT DEFAULT 0 ,
Computer FLOAT DEFAULT 0
);
SHOW TABLES;
DESC example7;
查看表结构
是指查看数据库中已存在的表的定义。查看表结构的语句包括DESCRIBE 表名, SHOW CREATE TABLE 表名 语句。可查看表的字段名、字段的数据类型、完整性约束条件。
DESC EXAMPLE0;
ALTER TABLE EXAMPLE0 RENAME USER;
DESC USER;DESC EXAMPLE0;
ALTER TABLE USER MODIFY name VARCHAR(30);
DESC USER;
①ALTER TABEL EXAMPLE1 CHANGE stu_name name;
②ALTER TABEL EXAMPLE1 CHANGE stu_sex sex INT(2);
DESC EXAMPLE1;
DESC USER;
ALTER TABLE USER ADD phone VARCHAR(20); DESC USER;
ALTER TABLE USER ADD age INT(4) NOT NULL; DESC USER;
ALTER TABLE USER ADD num INT(8) PRIMARY KEY FIRST; DESC USER;
ALTER TABLE USER ADD address VARCHAR(30) NOT NULL AFTER phone; DESC USER;
ALTER TABLE USER DROP id; DESC USER;
ALTER TABLE USER MODIFY name VARCHAR(30) FIRST;
DESC USER;
ALTER TABLE USER MODIFY sex TINYINT(1) AFTER age;
DESC USER;
SHOW CREATE TABLE USER \G
ALTER TABLE USER ENGINE=MyISAM;
SHOW CREATE TABLE EXAMPLE3 \G
ALTER TABLE EXAMPLE3 DROP FROEIGN KEY c_fk;
SHOW CREATE TABLE EXAMPLE3 \G
DESC EXAMPLE5;
DROP TABLE EXAMPLE5;
DESC EXAMPLE5;
DESC EXAMPLE1;
DROP TABLE EXAMPLE1;
SHOW CREATE TABLE EXAMPLE4 \G
CREATE TABLE student(
num INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
);
DESC student;
CREATE TABLE grade(
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
course VARCHAR(10) NOT NULL,
s_num INT(10) NOT NULL,
grade VARCHAR(4),
CONSTRAINT grade_fk FOREIGN KEY(grade) REFERENCES student(num)
);
DESC grade; //注意desc查不到外键信息，要用SHOW CREATE TABLE 表名;
ALTER TABEL GRADE MODIFY course VARCHAR(20);
ALTER TABLE GRADE MODIFY s_num INT(10) AFTER course;
ALTER TABLE GRADE CHANGE grade score VARCHAR(4);
ALTER TABLE GRADE DROP FROEIGN KEY grade_fk;
ALTER TABLE GRADE ENGINE=MyISAM;
ALTER TABLE GRADE DROP address;
ALTER TABLE GRADE ADD phone INT(10);
ALTER TABLE GRADE RENAME GRADEINFO;
DESC GRADE; DESC GRADE GRADEINFO;
DROP TABLE STUDENT; DESC STUDENT;
USE SCHOOL;
CREATE TABLE TEACHER(
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10) NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
);
ALTER TABLE TEACHER MODIFY BIRTHDAY DATETIME AFTER NAME;
ALTER TABLE TEACHER CHANGE NUM T_ID INT(10) NOT NULL;
ALTER TABLE TEACHER DROP ADDERSS;
ALTER TABLE TEACHER ADD WAGES FLOAT;
ALTER TABLE TEACHER RENAME TEACHERINFO;
DESC TEACHERINFO;
ALTER TABLE TEACHER ENGINE=MYISAM;
SHOW CREATE TABLE TEACHERINFO \G;
USE EXAMPLE;
CREATE TABLE DEPARMENT(
d_id INT(4) NOT NULL UNIQUE PRIMARY KEY,
d_name VARCHAR(20) NOT NULL UNIQUE,
function VARCAHR(50),
address VARCHAR(50)
);
DESC DEPARMENT;
CREATE TABLE WORKER(
id INT(4) Not null primary key auto_increment,
num INT(10) not null unique,
d_id INT(4),
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATE,
address VARCHAR(50),
CONSTRAINT WORKER_FK FOREIGN KEY(d_id) REFERENCE DEPARTMENT(d_id),
);
SHOW CREATE TABLE WORKER \G;
DROP TABLE DEPARTMENT;
ALTER TABLE WORKER DROP FOREIGN KEY worker_fk;
常见问题及解答：
字段改名后，会有部分约束条件丢失
1. 创建、修改和删除表表是数据库存储数据的基本单位。一个表包含若干个字段或记录。表的操作包括创建新表、修改表和删除表。
创建表的语法形式
CREATE TABLE 表名(属性名1 数据类型[完整约束条件],
属性名2 数据类型[完整约束条件],
….
属性名n 数据类型
);
create database example;
show databases;
use example;
create table example0(
id INT,
name VARCHAR(20),
sex BOOLEAN
);
SHOW TABLES;
DESC example;
create table example1(
stu_id INT primary key,
stu_name VARCHAR(20),
stu_sex BOOLEAN
);
show tables;
desc example1;
设置多字段主键
create table example3(
id INT primary key,
stu_id INT,
course_id INT,
constraint c_fk froeign key(stu_id,course_id) references example2(stu_id,course_id)
);
desc example4;
SHOW TABLES;
CREATE TABLE example4(
id INT primary key NOT NULL,
name VARCHAR(2) NOT NULL,
stu_id INT,
CONSTRAINT d_fk FROEIGN KEY(stu_id) REFERENCES example1(stu_id)
);
SHOW TABLES;
DESC example4;
CREATE TABLE example5(
id INT NOT NULL PRIMARY KEY,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL
);
SHOW TABLES;
DESC example5;
CREATE TABLE example6(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL
);
SHOW TABLES;
DESC example6;
CREATE TABLE example7(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL,
English VARCHAR(20) DEFAULT ‘ZERO’,
Math FLOAT DEFAULT 0 ,
Computer FLOAT DEFAULT 0
);
SHOW TABLES;
DESC example7;
查看表结构
是指查看数据库中已存在的表的定义。查看表结构的语句包括DESCRIBE 表名, SHOW CREATE TABLE 表名 语句。可查看表的字段名、字段的数据类型、完整性约束条件。
DESC EXAMPLE0;
ALTER TABLE EXAMPLE0 RENAME USER;
DESC USER;DESC EXAMPLE0;
ALTER TABLE USER MODIFY name VARCHAR(30);
DESC USER;
①ALTER TABEL EXAMPLE1 CHANGE stu_name name;
②ALTER TABEL EXAMPLE1 CHANGE stu_sex sex INT(2);
DESC EXAMPLE1;
DESC USER;
ALTER TABLE USER ADD phone VARCHAR(20); DESC USER;
ALTER TABLE USER ADD age INT(4) NOT NULL; DESC USER;
ALTER TABLE USER ADD num INT(8) PRIMARY KEY FIRST; DESC USER;
ALTER TABLE USER ADD address VARCHAR(30) NOT NULL AFTER phone; DESC USER;
ALTER TABLE USER DROP id; DESC USER;
ALTER TABLE USER MODIFY name VARCHAR(30) FIRST;
DESC USER;
ALTER TABLE USER MODIFY sex TINYINT(1) AFTER age;
DESC USER;
SHOW CREATE TABLE USER \G
ALTER TABLE USER ENGINE=MyISAM;
SHOW CREATE TABLE EXAMPLE3 \G
ALTER TABLE EXAMPLE3 DROP FROEIGN KEY c_fk;
SHOW CREATE TABLE EXAMPLE3 \G
DESC EXAMPLE5;
DROP TABLE EXAMPLE5;
DESC EXAMPLE5;
DESC EXAMPLE1;
DROP TABLE EXAMPLE1;
SHOW CREATE TABLE EXAMPLE4 \G
CREATE TABLE student(
num INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
);
DESC student;
CREATE TABLE grade(
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
course VARCHAR(10) NOT NULL,
s_num INT(10) NOT NULL,
grade VARCHAR(4),
CONSTRAINT grade_fk FOREIGN KEY(grade) REFERENCES student(num)
);
DESC grade; //注意desc查不到外键信息，要用SHOW CREATE TABLE 表名;
ALTER TABEL GRADE MODIFY course VARCHAR(20);
ALTER TABLE GRADE MODIFY s_num INT(10) AFTER course;
ALTER TABLE GRADE CHANGE grade score VARCHAR(4);
ALTER TABLE GRADE DROP FROEIGN KEY grade_fk;
ALTER TABLE GRADE ENGINE=MyISAM;
ALTER TABLE GRADE DROP address;
ALTER TABLE GRADE ADD phone INT(10);
ALTER TABLE GRADE RENAME GRADEINFO;
DESC GRADE; DESC GRADE GRADEINFO;
DROP TABLE STUDENT; DESC STUDENT;
USE SCHOOL;
CREATE TABLE TEACHER(
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10) NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
);
ALTER TABLE TEACHER MODIFY BIRTHDAY DATETIME AFTER NAME;
ALTER TABLE TEACHER CHANGE NUM T_ID INT(10) NOT NULL;
ALTER TABLE TEACHER DROP ADDERSS;
ALTER TABLE TEACHER ADD WAGES FLOAT;
ALTER TABLE TEACHER RENAME TEACHERINFO;
DESC TEACHERINFO;
ALTER TABLE TEACHER ENGINE=MYISAM;
SHOW CREATE TABLE TEACHERINFO \G;
USE EXAMPLE;
CREATE TABLE DEPARMENT(
d_id INT(4) NOT NULL UNIQUE PRIMARY KEY,
d_name VARCHAR(20) NOT NULL UNIQUE,
function VARCAHR(50),
address VARCHAR(50)
);
DESC DEPARMENT;
CREATE TABLE WORKER(
id INT(4) Not null primary key auto_increment,
num INT(10) not null unique,
d_id INT(4),
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATE,
address VARCHAR(50),
CONSTRAINT WORKER_FK FOREIGN KEY(d_id) REFERENCE DEPARTMENT(d_id),
);
SHOW CREATE TABLE WORKER \G;
DROP TABLE DEPARTMENT;
ALTER TABLE WORKER DROP FOREIGN KEY worker_fk;
1. 创建、修改和删除表表是数据库存储数据的基本单位。一个表包含若干个字段或记录。表的操作包括创建新表、修改表和删除表。
创建表的语法形式
CREATE TABLE 表名(属性名1 数据类型[完整约束条件],
属性名2 数据类型[完整约束条件],
….
属性名n 数据类型
);
create database example;
show databases;
use example;
create table example0(
id INT,
name VARCHAR(20),
sex BOOLEAN
);
SHOW TABLES;
DESC example;
create table example1(
stu_id INT primary key,
stu_name VARCHAR(20),
stu_sex BOOLEAN
);
show tables;
desc example1;
设置多字段主键
create table example3(
id INT primary key,
stu_id INT,
course_id INT,
constraint c_fk froeign key(stu_id,course_id) references example2(stu_id,course_id)
);
desc example4;
SHOW TABLES;
CREATE TABLE example4(
id INT primary key NOT NULL,
name VARCHAR(2) NOT NULL,
stu_id INT,
CONSTRAINT d_fk FROEIGN KEY(stu_id) REFERENCES example1(stu_id)
);
SHOW TABLES;
DESC example4;
CREATE TABLE example5(
id INT NOT NULL PRIMARY KEY,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL
);
SHOW TABLES;
DESC example5;
CREATE TABLE example6(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL
);
SHOW TABLES;
DESC example6;
CREATE TABLE example7(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT UNIQUE,
name VARCHAR(20) NOT NULL,
English VARCHAR(20) DEFAULT ‘ZERO’,
Math FLOAT DEFAULT 0 ,
Computer FLOAT DEFAULT 0
);
SHOW TABLES;
DESC example7;
查看表结构
是指查看数据库中已存在的表的定义。查看表结构的语句包括DESCRIBE 表名, SHOW CREATE TABLE 表名 语句。可查看表的字段名、字段的数据类型、完整性约束条件。
DESC EXAMPLE0;
ALTER TABLE EXAMPLE0 RENAME USER;
DESC USER;DESC EXAMPLE0;
ALTER TABLE USER MODIFY name VARCHAR(30);
DESC USER;
①ALTER TABEL EXAMPLE1 CHANGE stu_name name;
②ALTER TABEL EXAMPLE1 CHANGE stu_sex sex INT(2);
DESC EXAMPLE1;
DESC USER;
ALTER TABLE USER ADD phone VARCHAR(20); DESC USER;
ALTER TABLE USER ADD age INT(4) NOT NULL; DESC USER;
ALTER TABLE USER ADD num INT(8) PRIMARY KEY FIRST; DESC USER;
ALTER TABLE USER ADD address VARCHAR(30) NOT NULL AFTER phone; DESC USER;
ALTER TABLE USER DROP id; DESC USER;
ALTER TABLE USER MODIFY name VARCHAR(30) FIRST;
DESC USER;
ALTER TABLE USER MODIFY sex TINYINT(1) AFTER age;
DESC USER;
SHOW CREATE TABLE USER \G
ALTER TABLE USER ENGINE=MyISAM;
SHOW CREATE TABLE EXAMPLE3 \G
ALTER TABLE EXAMPLE3 DROP FROEIGN KEY c_fk;
SHOW CREATE TABLE EXAMPLE3 \G
DESC EXAMPLE5;
DROP TABLE EXAMPLE5;
DESC EXAMPLE5;
DESC EXAMPLE1;
DROP TABLE EXAMPLE1;
SHOW CREATE TABLE EXAMPLE4 \G
CREATE TABLE student(
num INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
);
DESC student;
CREATE TABLE grade(
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
course VARCHAR(10) NOT NULL,
s_num INT(10) NOT NULL,
grade VARCHAR(4),
CONSTRAINT grade_fk FOREIGN KEY(grade) REFERENCES student(num)
);
DESC grade; //注意desc查不到外键信息，要用SHOW CREATE TABLE 表名;
ALTER TABEL GRADE MODIFY course VARCHAR(20);
ALTER TABLE GRADE MODIFY s_num INT(10) AFTER course;
ALTER TABLE GRADE CHANGE grade score VARCHAR(4);
ALTER TABLE GRADE DROP FROEIGN KEY grade_fk;
ALTER TABLE GRADE ENGINE=MyISAM;
ALTER TABLE GRADE DROP address;
ALTER TABLE GRADE ADD phone INT(10);
ALTER TABLE GRADE RENAME GRADEINFO;
DESC GRADE; DESC GRADE GRADEINFO;
DROP TABLE STUDENT; DESC STUDENT;
USE SCHOOL;
CREATE TABLE TEACHER(
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10) NOT NULL UNIQUE,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
);
ALTER TABLE TEACHER MODIFY BIRTHDAY DATETIME AFTER NAME;
ALTER TABLE TEACHER CHANGE NUM T_ID INT(10) NOT NULL;
ALTER TABLE TEACHER DROP ADDERSS;
ALTER TABLE TEACHER ADD WAGES FLOAT;
ALTER TABLE TEACHER RENAME TEACHERINFO;
DESC TEACHERINFO;
ALTER TABLE TEACHER ENGINE=MYISAM;
SHOW CREATE TABLE TEACHERINFO \G;
USE EXAMPLE;
CREATE TABLE DEPARMENT(
d_id INT(4) NOT NULL UNIQUE PRIMARY KEY,
d_name VARCHAR(20) NOT NULL UNIQUE,
function VARCAHR(50),
address VARCHAR(50)
);
DESC DEPARMENT;
CREATE TABLE WORKER(
id INT(4) Not null primary key auto_increment,
num INT(10) not null unique,
d_id INT(4),
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATE,
address VARCHAR(50),
CONSTRAINT WORKER_FK FOREIGN KEY(d_id) REFERENCE DEPARTMENT(d_id),
);
SHOW CREATE TABLE WORKER \G;
DROP TABLE DEPARTMENT;
ALTER TABLE WORKER DROP FOREIGN KEY worker_fk;
常见问题及解答：
字段改名后，会有部分约束条件丢失。因此在给字段改名时，应先用show create table 查看当前字段的约束条件，将字段改名时设置原有的约束条件。
如何设置外键？子表的外键必须依赖于父表的某个字段，因此父表必须先于子表建立，并且关联的字段是父表的主键或者主键的一部分。
自增字段不能设置默认值。
如何删除父表？因为子表的外键约束限制了父表的删除，有2个方法解决这个问题，一是先删除子表，再删除父表，这样可以达到删除父表的目的，但是子表也牺牲了；第二种方式是先删除子表的外键约束，再删除父表。
创建表、修改表是本章最重要的内容，创建表和修改表的内容比较多，难度也非常大，需要不断的实践练习。创建表和修改表之后一定要查看表的结构，这样可以确认操作是否正确。
常见问题及解答：
字段改名后，会有部分约束条件丢失。因此在给字段改名时，应先用show create table 查看当前字段的约束条件，将字段改名时设置原有的约束条件。
如何设置外键？子表的外键必须依赖于父表的某个字段，因此父表必须先于子表建立，并且关联的字段是父表的主键或者主键的一部分。
自增字段不能设置默认值。
如何删除父表？因为子表的外键约束限制了父表的删除，有2个方法解决这个问题，一是先删除子表，再删除父表，这样可以达到删除父表的目的，但是子表也牺牲了；第二种方式是先删除子表的外键约束，再删除父表。
创建表、修改表是本章最重要的内容，创建表和修改表的内容比较多，难度也非常大，需要不断的实践练习。创建表和修改表之后一定要查看表的结构，这样可以确认操作是否正确。
因此在给字段改名时，应先用show create table 查看当前字段的约束条件，将字段改名时设置原有的约束条件。
如何设置外键？子表的外键必须依赖于父表的某个字段，因此父表必须先于子表建立，并且关联的字段是父表的主键或者主键的一部分。
自增字段不能设置默认值。
如何删除父表？因为子表的外键约束限制了父表的删除，有2个方法解决这个问题，一是先删除子表，再删除父表，这样可以达到删除父表的目的，但是子表也牺牲了；第二种方式是先删除子表的外键约束，再删除父表。
创建表、修改表是本章最重要的内容，创建表和修改表的内容比较多，难度也非常大，需要不断的实践练习。创建表和修改表之后一定要查看表的结构，这样可以确认操作是否正确。
