33_02-MySQL系列之六MySQL管理表和索引

2015-07-06

数据库

1 2	CREATE SCHEMA IF NOT EXISTS students CHARACTER SET 'gbk' COLLATE 'gbk_chinese-ci'; DROP [DATABASE\|SCHEMA] db_name;

表

直接定义一张空表；
从其他表中查询出数据，并以之创建新表；
以其他表为模板创建一个空表；

CREATE TABLE [IF NOT EXISTS] tb_name(col_name col_defination,constraint)

CREATE TABLE tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(20)NOT NULL,Age TINYINT NOT NULL);
CREATE TABLE tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT,Name CHAR(20)NOT NULL,PRIMARY KEY(id));

键都是索引，是特殊索引
键也称作约束，可用作索引，属于特殊索引(有特殊限定):B+Tree

直接定义一张空表

指定表的存储引擎:

1 2	CREATE TABLE courses(CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Course VARCHAR(50)NOT NULL)ENGINE=MyISAM; SHOW TABLE STATUS LIKE 'courses';

查看表的索引:

1	SHOW INDEXES FROM tb_name;

从其他表中查询出数据，并以之创建新表；

CREATE TABLE testcourses SELECT * FROM courses WHERE CID<=2;

以其他表为模板创建一个空表；
1
CREATE TABLE test LIKE courses;
修改表定义:

添加、删除、修改字段
添加、删除、修改索引
改表名
修改表属性

ALTER TABLE test ADD UNIQUE KEY(courses);
ALTER TABLE test ADD starttime date default '2013-04-12';

ALTER TABLE test CHANGE Couse Course VARCHAR(50)NOT NULL;

DROP TABLE testcourses;
ALTER TABLE test RENAME TO testcourses;

删除表

1	DROP TABLE

组合查询

CREATE TABLE student(SID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(30),CID TINYINT NOT NULL);

INSERT INTO student(Name,CID)VALUES('Zhangsan',2),('Xiao lizi',1);

CREATE TABLE courses(CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Course VARCHAR(30)NOT NULL);

INSERT INTO courses(CID,Course)VALUES(1,'Chinese'),(2,'Math'),(3,'English');

SELECT student.Name,courses.Course  FROM student,courses WHERE student.CID=courses.CID;

如果选择的课程不在课表中，怎么定义，这个时候应该使用外键;
但是MyASIM不支持外键，需要把已经创建的表的引擎修改为InnoDB;
ALTER TABLE courses ENGINE=InnoDB;
使用外键:
ALTER TABLE student ADD FOREIGN KEY(CID) REFERENCES courses(CID);

SHOW INDEXES FROM students;

INSERT INTO student(name,cid)VALUES(‘chen jialuo’,5);
就会报错，没法创建课表以外的课程；

一般情况下不要使用外键，会大量消耗系统资源；

约束

约束保证数据的完整性和唯一性
约束分为表级约束和列级约束
约束类型包括:

NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)

外键约束的参照操作:

CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL，如果使用此选项，必须保证子表列没有指定NOT NULL；
RESTRICT:拒绝对父表的删除或更新操作
NO ACTION:标准的SQL的关键字，在MySQL中与RESTRICT相同

外键约束的要求:

父表和子表必须使用相同的存储引擎，而且禁止使用临时表；
数据表的存储引擎只能是InnoDB；
外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同；

外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL将自动创建索引
索引:

    CREATE INDEX index_name ON tb_name(col1,...);
    DROP INDEX index_name On tb_name;

CREATE INDEX name_on_student ON student (Name) USING BTREE;(默认使用BTree，可不写)；

说明:
由于第一次接触数据库，所以很多写的无章法，以后将会重头再来一遍。
需要说明的是，本文章或者说数据库知识我参考了幕课网的三个讲解MySQL数据库。