MYSQL 教程 §4, 创建数据库,表,素引

§4, 创建数据库,表,素引

本章使用的数据库如下:

employee(employeeID, name, job, departmentID )

 

department(departmentID, name)

 

employeeSkills(employeeID, skill)

 

client(clientID, name, address, contactPerson, contactNumber)

 

assignment(clientID, employeeID , workdate, hours)

§4.1  创建表

Mysql不区分大小写,所有数据库系统中对SQL的关键字不区分大小写。但是你使用的标识符可能是区分大小写。因为mysql的每个数据库在系统中有个目录，每个表有一个文件。Column names, indexes, and aliases是不区分大小写的。

       标识符一般用于标识an alias, a database, a table, a column, or an index。不能使用quote characters, ACSII(0) and ASCII(255).

实例:

drop database if exists employee;

create database employee;

use employee;

create table department

(

  departmentID int not null auto_increment primary key,

  name varchar(30)

) type=InnoDB;

create table employee

(

  employeeID int not null auto_increment primary key,

  name varchar(80),

  job varchar(30),

  departmentID int not null references department(departmentID)

) type=InnoDB;

create table employeeSkills

(

  employeeID int not null references employee(employeeID),

  skill varchar(15) not null,

  primary key (employeeID, skill)

) type=InnoDB;

create table client

(

  clientID int not null auto_increment primary key,

  name varchar(40),

  address varchar(100),

  contactPerson varchar(80),

  contactNumber char(12)

) type=InnoDB;

create table assignment

(

  clientID int not null references client(clientID),

  employeeID int not null references employee(employeeID),

  workdate date not null,

  hours float,

  primary key (clientID, employeeID, workdate)

) type=InnoDB; 

 

Varchar使用的是动态存储,

默认使用的是MyISAM表类型,单个列作为主键可以如下表示:

employeeID int not null auto_increment primary key,

MyISAM 或许要5.1版本才支持外键.

 

Create 的语法参见教材

 

§4.2  数据类型

具体请参考教材

有3种基本类型:数字类型,字符串类型,时间日期类型.

数字类型

TINYINT    8位   

SMALLINT   16

MEDIUMINT  32

BIGINT     64

 

FLOAT

DOUBLE

 

字符串类型

CHAR VARCHAR

BLOB用二进制形式存储,区分大小写.

TINYTEXT or TINYBLOB   8位

TEXT or BLOB              16

MEDIUMTEXT or MEDUIMBLOB  24

LONGTEXT or LONGBLOB      32

ENUM

SET

时间日期类型

DATE

TIME

DATETIME

TIMESTAMP

YEAR

 

§4.3  创建索引,删除,修改等

＊创建索引

PRIMARY KEY, KEY, UNIQUE, or INDEX 自动带有索引

创建索引:create index name on employee(name);选项有UNIQUE FULLTEXT 等.针对VARCHAR可以只提取前面的几个字母.比如：create index part_name on employee(name(5)); 文本索引往往没有数字的速度快.这样可以提高性能。

 

＊删除操作.

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...]

drop index part_name on employee;

drop database employee;

drop database if exists employee;

drop table assignment;

TEMPORARY 用于删除临时表

＊修改表结构

alter table employee

add index name (name);

详细说明参考教材

 

 

 

§4.4  小结

Summary

In this chapter, we learned how to create and delete databases, tables, and indexes and how to change the structure of an existing table.

 

Case Sensitivity and Identifiers

Database names have the same case sensitivity as directories in your operating system. Table names follow the same rules as filenames. Everything else is case insensitive.

 

All identifiers except aliases can be up to 64 characters long. Aliases can be up to 255 characters long.

 

Identifiers can contain most characters, but database names may not contain /, \, or . and table names cannot contain . or /.

 

You can use reserved words for identifiers as long as you put them in quotes.

 

Creating a Database

create database dbname; creates a database.

 

use database dbname; selects a database for use.

 

Creating Tables

Use the create table statement, which has this general form:

 

 

 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]

[table_options] [select_statement]

 

or

 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;

 

create_definition:

  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]

            [PRIMARY KEY] [reference_definition]

  or    PRIMARY KEY (index_col_name,...)

  or    KEY [index_name] (index_col_name,...)

  or    INDEX [index_name] (index_col_name,...)

  or    UNIQUE [INDEX] [index_name] (index_col_name,...)

  or    FULLTEXT [INDEX] [index_name] (index_col_name,...)

  or    [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)

            [reference_definition]

  or    CHECK (expr)

 

Column Types

Exact numeric types are TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, NUMERIC, and DECIMAL.

 

Approximate numeric types are FLOAT and DOUBLE.

 

String types are CHAR, VARCHAR, TEXT, and BLOB.

 

Date and time types are DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

 

There are also various aliases to these type names.

 

Dropping Databases, Tables, and Indexes

Drop a database with

 

 

 

drop database dbname;

 

Drop a table with

 

 

 

drop table tablename;

 

Drop an index with

 

 

 

drop index indexname on tablename;

 

Altering Existing Table Structures

Change table structure with ALTER TABLE. This is the general structure of the ALTER TABLE command:

 

 

 

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

 

alter_spec:

        ADD [COLUMN] create_definition [FIRST | AFTER col_name ]

  or    ADD [COLUMN] (create_definition, create_definition,...)

  or    ADD INDEX [index_name] (index_col_name,...)

  or    ADD PRIMARY KEY (index_col_name,...)

  or    ADD UNIQUE [index_name] (index_col_name,...)

  or    ADD FULLTEXT [index_name] (index_col_name,...)

  or    ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)

            [referenc_e_definition]

  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

  or    CHANGE [COLUMN] old_col_name create_definition

               [FIRST | AFTER col_name]

  or    MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]

  or    DROP [COLUMN] col_name

  or    DROP PRIMARY KEY

  or    DROP INDEX index_name

  or    DISABLE KEYS

  or    ENABLE KEYS

  or    RENAME [TO] new_tbl_name

  or    ORDER BY col_name

  or    table_options