MySQL 建表

在test数据库创建players表

create table  if not exists test.players(
    playerno    integer not null PRIMARY KEY,
    `name`      char(15) not null,
    initials    char(3) not null,
    birth_date  date  null,
    sex         char(1) not null,
    joined      smallint not null,
    street      varchar(30) not null,
    houseno     char(4) null,
    postcode    char(6) null,
    town          varchar(30) not null,
    phoneno     char(13) null,
    leaguno     char(4) unique)

语法

<create table statement> ::=
    CREATE [ TEMPORARY ] TABLE [ IF NOT EXISTS ]
        <table specification> <table structure>[ <table option>... ]

<table specification> ::= [ <database name> . ] <table name>

<table structure> ::= <table schema>

<table schema> ::=
    ( <table element> [ , <table element> ]... )

<table element> ::=
    <column definition> |
    <table integrity constraint> |
    <index definition>

<column definition> ::=
    <column name> <data type> [ <null specification> ]
        [ <column integrity constraint> ]

<null specification> ::= [ NOT ] NULL

<column integrity constraint> ::=
    PRIMARY KEY |
    UNIQUE [ KEY ] |
    <check integrity constraint>

<table integrity constraint> ::=
    <primary key> |
    <alternate key> |
    <foreign key> |
    <check integrity constraint>

<table option> ::=
    ENGINE = <engine name> |
    TYPE = <engine name> |
    UNION = ( <table name> [ , <table name> ]... ) |
    INSERT_METHOD = { NO | FIRST | LAST } |
    AUTO_INCREMENT = <whole number> |
    COMMENT = <alphanumeric literal> |
    AVG_ROW_LENGTH = <whole number> |
    MAX_ROWS = <whole number> |
    MIN_ROWS = <whole number> |
    [ DEFAULT ] CHARACTER SET { <name> | DEFAULT } |
    [ DEFAULT ] COLLATE { <name> | DEFAULT } |
    DATA DIRECTORY = <directory> |
    INDEX DIRECTORY = <directory> |
    CHECK_SUM = { 0 | 1 } |
    DELAY_KEY_WRITE = { 0 | 1 } |
    PACK_KEYS = { 0 | 1 | DEFAULT } |
    PASSWORD = <alphanumeric literal> |
    RAID_TYPE = { 1 | STRIPED | RAID0 } |
    RAID_CHUNKS = <whole number> |
    RAID_CHUNKSIZE = <whole number> |
    ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }

1 删除表

语法

<drop table statement> ::=
    DROP [ TEMPORARY ] { TABLE | TABLES } [ IF EXISTS ]
    <table specification> [ , <table specification> ]...
    [ CASCADE | RESTRICT ]

删除 players

DROP TABLE PLAYERS

2 修改表名称

语法

<rename table statement> ::=
    RENAME { TABLE | TABLES } <table name change>
        [ , <table name change> ]...

<table name change> ::= <table name> TO <table name>

将 PLAYERS 改名为 TENNIS_PLAYERS

rename table players to tennis_players

将 PLAYERS 改名为 TENNIS_PLAYERS，COMMITTEE_MEMBERS 改名为 MEMBERS

RENAME TABLES PLAYERS TO TENNIS_PLAYERS,COMMITTEE_MEMBERS TO MEMBERS