MySQL
Tabel maken

Syntaxis

• CREATE TABLE tabelnaam (kolomnaam1 gegevenstype (grootte), kolomnaam2 gegevenstype (grootte), kolomnaam3 gegevenstype (grootte), ....); // Eenvoudige tabelcreatie

• CREATE TABLE tabelnaam [INDIEN NIET BESTAAT] (kolomnaam1 gegevenstype (grootte), kolomnaam2 gegevenstype (grootte), kolomnaam3 gegevenstype (grootte), ....); // Controle op het maken van tabellen bestaat

• CREATE [TEMPORARY] TABLE tabelnaam [ALS NIET BESTAAT] (kolomnaam1 gegevenstype (grootte), kolomnaam2 gegevenstype (grootte), kolomnaam3 gegevenstype (grootte), ....); // Tijdelijke tabel maken

• CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; // Tabellen maken van SELECT

Opmerkingen

CREATE TABLE table_name ( column_definitions ) ENGINE=engine;

Eenvoudige tabel creatie

De instructie CREATE TABLE wordt gebruikt om een tabel in een MySQL-database te maken.

CREATE TABLE Person (
    `PersonID`      INTEGER NOT NULL PRIMARY KEY,
    `LastName`      VARCHAR(80),
    `FirstName`     VARCHAR(80),
    `Address`       TEXT,
    `City`          VARCHAR(100)
) Engine=InnoDB;

Elke velddefinitie moet hebben:

1. Veldnaam: een geldige veldnaam. Zorg ervoor dat u de namen in `-tekens insluit. Dit zorgt ervoor dat u bijvoorbeeld spatie-tekens in de veldnaam kunt gebruiken.
2. Gegevenstype [Lengte]: als het veld CHAR of VARCHAR , is het verplicht om een veldlengte op te geven.
3. Attributen NULL | NOT NULL : Als NOT NULL is opgegeven, mislukt elke poging om een NULL waarde in dat veld op te slaan.
4. Zie meer op data types en hun attributen hier .

Engine=... is een optionele parameter die wordt gebruikt om de opslag-engine van de tabel op te geven. Als er geen opslag-engine is opgegeven, wordt de tabel gemaakt met behulp van de standaard tabelopslag-engine van de server (meestal InnoDB of MyISAM).

Standaardwaarden instellen

Waar het zinvol is, kunt u bovendien een standaardwaarde voor elk veld instellen met DEFAULT :

CREATE TABLE Address (
    `AddressID`   INTEGER NOT NULL PRIMARY KEY,
    `Street`      VARCHAR(80),
    `City`        VARCHAR(80),
    `Country`     VARCHAR(80) DEFAULT "United States",
    `Active`      BOOLEAN DEFAULT 1,
) Engine=InnoDB;

Als tijdens invoegingen geen Street is opgegeven, is dat veld NULL wanneer het wordt opgehaald. Wanneer bij invoeging geen Country wordt opgegeven, wordt dit standaard ingesteld op "Verenigde Staten".

U kunt standaardwaarden instellen voor alle kolomtypen, behalve de velden BLOB , TEXT , GEOMETRY en JSON .

Tabellen maken met primaire sleutel

CREATE TABLE Person (
    PersonID     INT UNSIGNED NOT NULL,
    LastName     VARCHAR(66) NOT NULL,
    FirstName    VARCHAR(66),
    Address      VARCHAR(255),
    City         VARCHAR(66),
    PRIMARY KEY (PersonID)
);

Een primaire sleutel is een NOT NULL -identificatiecode of een identificatie met meerdere kolommen die een rij van een tabel op unieke wijze identificeert. Er wordt een index gemaakt en als ze niet expliciet als NOT NULL worden verklaard, zal MySQL ze zo stil en impliciet verklaren.

Een tabel kan slechts één PRIMARY KEY hebben en elke tabel wordt aanbevolen om er een te hebben. InnoDB maakt er automatisch een in afwezigheid, (zoals te zien in MySQL-documentatie ), hoewel dit minder wenselijk is.

Vaak wordt een AUTO_INCREMENT INT ook bekend als "surrogate key", gebruikt voor optimalisatie van de dunne index en relaties met andere tabellen. Deze waarde zal (normaal) met 1 toenemen telkens wanneer een nieuw record wordt toegevoegd, beginnend met een standaardwaarde van 1.

Ondanks zijn naam is het echter niet de bedoeling om te garanderen dat waarden incrementeel zijn, maar alleen dat ze opeenvolgend en uniek zijn.

Een INT waarde voor automatisch verhogen wordt niet gereset naar de standaard startwaarde als alle rijen in de tabel worden verwijderd, tenzij de tabel wordt afgekapt met de instructie TRUNCATE TABLE .

Eén kolom definiëren als primaire sleutel (inline definitie)

Als de primaire sleutel uit een enkele kolom bestaat, kan de PRIMARY KEY clausule in lijn worden geplaatst met de kolomdefinitie:

CREATE TABLE Person (
    PersonID     INT UNSIGNED NOT NULL PRIMARY KEY,
    LastName     VARCHAR(66) NOT NULL,
    FirstName    VARCHAR(66),
    Address      VARCHAR(255),
    City         VARCHAR(66)
);

Deze vorm van de opdracht is korter en gemakkelijker te lezen.

Een primaire sleutel met meerdere kolommen definiëren

Het is ook mogelijk om een primaire sleutel te definiëren die meer dan één kolom omvat. Dit kan bijvoorbeeld worden gedaan op de onderliggende tabel van een relatie met een buitenlandse sleutel. Een primaire sleutel met meerdere kolommen wordt gedefinieerd door de deelnemende kolommen weer te geven in een afzonderlijke PRIMARY KEY clausule. Inline-syntaxis is hier niet toegestaan, omdat slechts één kolom PRIMARY KEY inline kan worden verklaard. Bijvoorbeeld:

CREATE TABLE invoice_line_items (
    LineNum      SMALLINT UNSIGNED NOT NULL,
    InvoiceNum   INT UNSIGNED NOT NULL,
    -- Other columns go here
    PRIMARY KEY (InvoiceNum, LineNum),
    FOREIGN KEY (InvoiceNum) REFERENCES -- references to an attribute of a table
);

Merk op dat de kolommen van de primaire sleutel moeten worden opgegeven in logische sorteervolgorde, die kan verschillen van de volgorde waarin de kolommen werden gedefinieerd, zoals in het bovenstaande voorbeeld.

Grotere indexen vereisen meer schijfruimte, geheugen en I / O. Daarom moeten sleutels zo klein mogelijk zijn (vooral met betrekking tot samengestelde sleutels). In InnoDB bevat elke 'secundaire index' een kopie van de kolommen van de PRIMARY KEY .

Tabellen maken met buitenlandse sleutel

CREATE TABLE Account (
    AccountID     INT UNSIGNED NOT NULL,
    AccountNo     INT UNSIGNED NOT NULL,
    PersonID    INT UNSIGNED,
    PRIMARY KEY (AccountID),
    FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
) ENGINE=InnoDB;

Buitenlandse sleutel: een buitenlandse sleutel ( FK ) is een enkele kolom of een kolom met meerdere kolommen in een referentietabel . Deze FK is bevestigd in de tabel waarnaar wordt verwezen . Het wordt ten zeerste aanbevolen dat de tabelsleutel waarnaar wordt verwezen als bevestiging van de FK een primaire sleutel is, maar dat wordt niet afgedwongen. Het wordt gebruikt als een snelle opzoeking naar de referentie waar het niet uniek hoeft te zijn, en in feite een meest linkse index kan zijn.

Buitenlandse sleutelrelaties hebben betrekking op een bovenliggende tabel met de centrale gegevenswaarden en een onderliggende tabel met identieke waarden die terugverwijzen naar de bovenliggende waarde. De clausule FOREIGN KEY is opgegeven in de onderliggende tabel. De bovenliggende en onderliggende tabellen moeten dezelfde opslagmotor gebruiken. Het mogen geen TIJDELIJKE tabellen zijn.

Overeenkomstige kolommen in de externe sleutel en de sleutel waarnaar wordt verwezen, moeten vergelijkbare gegevenstypen hebben. De grootte en het teken van gehele typen moeten hetzelfde zijn. De lengte van snaartypes hoeft niet hetzelfde te zijn. Voor niet-bindende (teken) tekenreekskolommen moeten de tekenset en sortering hetzelfde zijn.

Opmerking: beperkingen voor externe sleutels worden ondersteund door de InnoDB-opslag-engine (niet MyISAM of MEMORY). DB-configuraties die andere engines gebruiken, accepteren deze CREATE TABLE instructie, maar houden geen rekening met beperkingen voor externe sleutels. (Hoewel nieuwere MySQL-versies standaard op InnoDB , maar het is een goede gewoonte om expliciet te zijn.)

Een bestaande tabel klonen

Een tabel kan als volgt worden gerepliceerd:

CREATE TABLE ClonedPersons LIKE Persons;

De nieuwe tabel heeft exact dezelfde structuur als de oorspronkelijke tabel, inclusief indexen en kolomkenmerken.

Naast het handmatig maken van een tabel, is het ook mogelijk om een tabel te maken door gegevens uit een andere tabel te selecteren:

CREATE TABLE ClonedPersons SELECT * FROM Persons;

U kunt alle normale functies van een SELECT instructie gebruiken om de gegevens onderweg te wijzigen:

CREATE TABLE ModifiedPersons
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;

Primaire sleutels en indexen blijven niet behouden bij het maken van tabellen vanuit SELECT . U moet ze opnieuw opgeven:

CREATE TABLE ModifiedPersons (PRIMARY KEY (PersonID))
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;

CREËER TAFEL VAN SELECT

U kunt een tabel van een andere maken door een SELECT instructie toe te voegen aan het einde van de CREATE TABLE instructie:

CREATE TABLE stack (
    id_user INT,
    username VARCHAR(30),
    password VARCHAR(30)
);

Maak een tabel in dezelfde database:

-- create a table from another table in the same database with all attributes
CREATE TABLE stack2 AS SELECT * FROM stack;

-- create a table from another table in the same database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM stack;

Maak tabellen uit verschillende databases:

-- create a table from another table from another database with all attributes
CREATE TABLE stack2 AS SELECT * FROM second_db.stack;

-- create a table from another table from another database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM second_db.stack;

NB

Als u een tabel wilt maken die hetzelfde is als die van een andere tabel in een andere database, moet u de naam van de database als volgt opgeven:

FROM NAME_DATABASE.name_table

Tabelstructuur weergeven

Als u de schemagegevens van uw tabel wilt zien, kunt u een van de volgende gebruiken:

SHOW CREATE TABLE child; -- Option 1

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(100) NOT NULL,
  `myParent` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mommy_daddy` (`myParent`),
  CONSTRAINT `mommy_daddy` FOREIGN KEY (`myParent`) REFERENCES `parent` (`id`) 
      ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Als het wordt gebruikt vanuit het opdrachtregelprogramma mysql, is dit minder uitgebreid:

SHOW CREATE TABLE child \G

Een minder beschrijvende manier om de tabelstructuur weer te geven:

mysql> CREATE TABLE Tab1(id int, name varchar(30));
Query OK, 0 rows affected (0.03 sec)

mysql> DESCRIBE Tab1; -- Option 2 

+-------+-------------+------+-----+---------+-------+ 
| Field | Type        | Null | Key | Default | Extra |  
+-------+-------------+------+-----+---------+-------+ 
| id    | int(11)     | YES  |     | NULL    |       | 
| name  | varchar(30) | YES  |     | NULL    |       |  
+-------+-------------+------+-----+---------+-------+ 

Zowel DESCRIBE als DESC geeft hetzelfde resultaat.

Zie dit voorbeeld om DESCRIBE in één keer op alle tabellen in een database te zien uitvoeren.

Tabel maken met TimeStamp-kolom om laatste update weer te geven

De kolom TIMESTAMP geeft aan wanneer de rij voor het laatst is bijgewerkt.

CREATE TABLE `TestLastUpdate` (
    `ID` INT NULL,
    `Name` VARCHAR(50) NULL,
    `Address` VARCHAR(50) NULL,
    `LastUpdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COMMENT='Last Update'
;