mysql create table tutorial with examples
Dans ce tutoriel, nous explorerons l'utilisation de la commande MySQL CREATE TABLE avec des exemples de syntaxe et de programmation:
L'instruction CREATE TABLE fait partie du DDL (Data Definition Language) de SQL.
Nous discuterons des façons dont vous pouvez CRÉER une table dans une base de données donnée, mentionner les noms de colonnes et le moteur de base de données lors de la création de la table, ainsi que les règles relatives aux conventions de dénomination des tables SQL.
Ce que vous apprendrez:
- Conditions préalables
- Commande MySQL CREATE TABLE
- Conclusion
Conditions préalables
La condition préalable pour exécuter l'une des commandes SQL serait de télécharger le serveur MySQL. L'édition communautaire gratuite peut être téléchargée Ici.
Nous utiliserons également le client MySQL Workbench SQL pour tous les exemples et discussions de ce didacticiel. L'édition communautaire gratuite de MySQL Workbench peut être téléchargée ici (vous pouvez choisir la version en fonction du système d'exploitation sur lequel vous travaillez).
Si vous ne souhaitez pas utiliser MySQL Workbench, vous pouvez également utiliser le client de ligne de commande MySQL fourni avec l'installation par défaut de MySQL Server.
Commande MySQL CREATE TABLE
La commande CREATE TABLE fait partie de l'ensemble de commandes DDL (Data Definition Language) de MySQL et permet à un utilisateur de créer une nouvelle table pour la base de données donnée.
Noter: La commande CREATE TABLE existe pour presque toutes les bases de données relationnelles - comme MySQL, Postgres, SQL Server, etc.
Syntaxe de MySQL CREATE TABLE
Dans la forme la plus simple, vous pouvez utiliser la commande CREATE TABLE avec uniquement les options de base, c'est-à-dire le nom de la table et les définitions de colonne.
CREATE TABLE (IF NOT EXISTS) tableName ( column1 datatype, column2 datatype, .... );
Comprenons les arguments de syntaxe en détail:
- nom de la table: Cela doit être le nom de la table que vous essayez de créer. Il doit s'agir d'un nom complet (au cas où vous ne disposez pas d'un ensemble de base de données par défaut). Par exemple, databaseName.tableName
Le nom de la table peut être spécifié sans guillemets ou avec un symbole de backtick comme `tableName` et` databaseName` .`tableName` - Définition de la colonne: Une table en SQL doit comprendre au moins une colonne. Toutes les définitions de colonne doivent comprendre nom_colonne ainsi que le type de données de colonne. Vous pouvez également éventuellement inclure les autres propriétés de la colonne telles que la clé primaire, null, non null, etc.
Voyons un exemple d'utilisation de la syntaxe ci-dessus pour créer une table.
Nous allons créer une table nommée EMPLOYEE_DETAILS (dans la base de données - SAMPLE_DB) avec des colonnes
- Nom: varchar (100)
- âge: int
- adresse: varchar (100)
CREATE TABLE SAMPLE_DB.employee_details ( name varchar(100), age int, address varchar(100) );
Ci-dessous sera le résultat de la création de la table:
Noter:
# 1) Notez l'utilisation de (IF NOT EXISTS) la commande facultative dans la syntaxe ci-dessus.
Il est généralement recommandé d’utiliser cette commande car elle éviterait de générer une erreur si la table que nous essayons de créer est déjà présente dans la base de données.
Voici un exemple d'utilisation avec et sans IF NOT EXISTS.
- Sans IF NOT EXISTS générerait une erreur si la table existe déjà.
- Avec IF NOT EXISTS ne générera pas d'erreur. Cependant, il affichera un avertissement indiquant que la table existe déjà.
# 2) Le 'tableName' lors de l'utilisation de la commande CREATE TABLE doit être pleinement qualifié avec le nom de la base de données, c'est-à-dire que SAMPLE_DB.employee_details
Les autres moyens de spécifier le nom de la table consistent à configurer la base de données actuelle à l’aide de la commande ‘USE’. Par exemple. USE SAMPLE_DB; puis exécuter / utiliser uniquement le nom de la table au lieu du nom complet de la table.
CRÉER UNE TABLE avec des options de table
Les options de table sont utilisées afin d'optimiser le comportement des tables MySQL. Ceux-ci peuvent être appliqués lors de la création d'une table à l'aide de la commande MySQL CREATE TABLE (ou plus tard via la commande ALTER TABLE).
La syntaxe reste la même avec des options de table supplémentaires qui peuvent être spécifiées.
CREATE TABLE (IF NOT EXISTS) tableName ( column1 datatype, column2 datatype, .... ) (table “” not found /)
;
Nous discuterons des options les plus utilisées ci-dessous (une liste complète des options de table peut être trouvée ici ).
# 1) MOTEUR
Il est utilisé pour spécifier le moteur de stockage de la table, c'est-à-dire que la valeur par défaut est InnoDB. Il n'est pas nécessaire de le modifier, sauf si un moteur de stockage spécifique est nécessaire. Les autres valeurs valides pour les moteurs de stockage sont MEMORY, CSV, HEAP, etc.
La syntaxe pour spécifier ENGINE dans le cadre de MySQL CREATE TABLE est donnée ci-dessous.
CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details ( name varchar(100), age int, address varchar(100) )ENGINE='MEMORY';
# 2) AUTO_INCREMENT
Cette option est utilisée pour définir la valeur initiale AUTO_INCREMENT de la table, c'est-à-dire que la valeur par défaut est 1, mais vous pouvez remplacer toute autre valeur entière positive.
Noter: AUTO_INCREMENT peut être spécifié pour une seule colonne de la table et doit être la clé primaire. Voyons un exemple de spécification de l'incrémentation automatique sur 10 et d'insertion d'un enregistrement pour valider si l'incrémentation automatique est correctement définie.
Nous utilisons la même table employee_details (assurez-vous de supprimer la table existante avant d'exécuter cette commande) avec un champ id supplémentaire marqué comme clé primaire.
CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details( id int not null AUTO_INCREMENT primary key, name varchar(100), age int, address varchar(100) )AUTO_INCREMENT=10;
Insérons une ligne sans aucune valeur pour ID et assurez-vous que les valeurs s'insèrent à partir de la valeur commençant par 10.
INSERT INTO SAMPLE_DB.employee_details(name,age,address) values ('aman kumar',20,'mumbai'); select * from SAMPLE_DB.employee_details;
# 3) SOMME DE CONTRÔLE
Cela doit être défini sur 1 si vous souhaitez stocker une somme de contrôle pour toute la table. Il est généralement utilisé pour s'assurer qu'il n'y a pas de tables corrompues.
CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details ( name varchar(100), age int, address varchar(100) )CHECKSUM=1;
CHECKSUM conserve une somme de contrôle en direct de la table entière lors des insertions ou des mises à jour
CRÉER UNE TABLE avec les détails de partitionnement
Nous pouvons également mentionner le partitionnement défini par l'utilisateur si nécessaire en utilisant les options de partitionnement.
Le partitionnement en tant que concept est largement utilisé pour distribuer le contenu des tables à travers le système de fichiers afin de garantir des temps d'accès plus rapides et des requêtes optimisées. Le partitionnement divise une grande table en tables plus petites en fonction des stratégies ou des clés de partitionnement spécifiées.
Voyons comment nous pouvons spécifier les détails du partitionnement avec la commande MySQL CREATE TABLE.
Nous utiliserons l'exemple de table employee_details et ajouterons une nouvelle colonne entière nommée department_id qui serait utilisée comme clé de hachage de partition pour avoir une distribution uniforme des données.
Spécifier également le nombre de partitions indiquerait combien de partitions réelles seraient créées (dans ce cas 4). S'il n'est pas spécifié, il n'y aurait par défaut qu'une seule partition.
CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details ( name varchar(100), age int, address varchar(100), department_id int )PARTITION BY HASH (department_id) PARTITIONS 4;
Noter: En général, la clé qui serait utilisée pour créer des partitions dépendrait des modèles d'accès anticipés qui seraient utilisés pour la table. Dans ce cas, supposons que nous interrogions la table en fonction de l'ID de service la plupart du temps, alors il est logique d'avoir department_id comme partie de la clé de hachage.
Clonage et copie de table MySQL
Parfois, vous souhaiterez peut-être créer un clone d'une table existante ou copier le contenu d'une table vers une autre table. MySQL prend en charge 2 façons d'y parvenir, comme indiqué ci-dessous.
- Utilisation de la commande LIKE
- Utilisation de la commande SELECT
Clonage de table à l'aide de LIKE COMMAND
Avec la commande LIKE, vous pouvez créer une nouvelle table avec exactement les mêmes noms de colonne et propriétés que les tables existantes.
Voici la syntaxe utilisant la commande LIKE.
meilleur logiciel d'espionnage pour iphone 5
CREATE TABLE tableName1 LIKE tableName2
Avec la commande ci-dessus, une nouvelle table, c'est-à-dire tableName1 serait créée avec la même structure et les mêmes propriétés que tableName2.
Veuillez noter qu'avec cette approche, seuls les noms de colonnes et les propriétés sont clonés et non les données réelles de la table.
Essayons de créer une table nommée employee_details et utilisons cette table pour créer une nouvelle table nommée student_details à l'aide de l'option LIKE.
CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details ( name varchar(100), age int, address varchar(100), department_id int ); CREATE TABLE SAMPLE_DB.student_details LIKE SAMPLE_DB.employee_details;
Ci-dessous est la sortie de la commande ci-dessus.
Clonage de table à l'aide de SELECT COMMAND
Cette approche utilise la commande SELECT pour créer une copie de la table existante dans une nouvelle table.
Avec cette approche, les données de la table sont également copiées dans la nouvelle table.
meilleur logiciel pour compresser des fichiers vidéo
CREATE TABLE tableName1 AS SELECT * FROM tableName2;
Essayons de créer une table nommée employee_details et utilisons cette table pour créer une nouvelle table nommée student_details à l'aide de l'option SELECT.
CREATE TABLE SAMPLE_DB.student_details AS SELECT * FROM SAMPLE_DB.employee_details;
Conventions de dénomination des tables MySQL
Dans les sections précédentes, nous avons appris à créer des tables MySQL. Voyons maintenant quelques règles à garder à l'esprit lors de l'attribution de noms aux tables, ainsi que les restrictions qui s'appliquent à MySQL.
Ces conventions / règles s'appliquent à la fois aux tables SQL et aux bases de données.
# 1) Caractères légaux dans les noms
à) Les noms sans guillemets peuvent être constitués de n'importe quel caractère du jeu de caractères par défaut de SQL Server, à l'exception du fait que tous les caractères ne peuvent pas être des chiffres. Par exemple, «23test» est un nom de table valide mais pas «2345».
Vous trouverez ci-dessous la liste des caractères qui pourraient être utilisés pour les noms sans guillemets:
ASCII: (0-9, a-z, A-Z $ _) (lettres latines de base, chiffres 0-9, dollar, trait de soulignement)
Étendu: U + 0080 .. U + FFFF
En savoir plus sur les codes ASCII ici
b) Les noms de table et de base de données peuvent être entre guillemets avec un caractère backtick (`) et peuvent contenir n'importe quelle lettre / caractère sauf le caractère backtick lui-même. Avec les noms cités, vous pouvez même avoir des noms de table / base de données comportant tous les chiffres.
Veuillez noter que pour de telles tables, vous devrez utiliser les backticks autour de la table et / ou le nom de la base de données pour accéder aux données à l'intérieur de ces tables.
c) Les noms de table et de base de données ne peuvent pas contenir de point «.» Car il est utilisé comme base de données et séparateur de table.
ré) Les noms de base de données et de table peuvent contenir des caractères spéciaux «$» et «_».
# 2) Longueur du nom
La longueur maximale autorisée pour une base de données ou un nom de table dans MySQL est 64 personnages.
# 3) Qualifications de nom
Comme indiqué dans les sections précédentes, dans MySQL, une table est toujours en contexte avec la base de données dont elle fait partie. Par conséquent, pour accéder à une table, vous pouvez utiliser un nom de table complet - qui n'est rien d'autre que la combinaison du nom de la base de données séparé par un point et ensuite le nom de la colonne.
Par exemple, pour sélectionner tous les éléments de la table 'EMPLOYEE_DETAILS' de la base de données SAMPLE_DB, vous pouvez utiliser un nom complet comme ci-dessous.
SELECT * from SAMPLE_DB.EMPLOYEE_DETAILS;
Les noms de base de données et de table peuvent également être spécifiés séparément, comme indiqué ci-dessous.
SELECT * from `SAMPLE_DB`.`EMPLOYEE_DETAILS`;
Les conventions et meilleures pratiques recommandées par l'industrie concernant les tables de dénomination et les bases de données sont indiquées ci-dessous.
- Nommer les tables et les bases de données en minuscules - Cela accélère généralement la saisie et l'interrogation de la base de données, en particulier pour les personnes impliquées dans l'interrogation quotidienne de la base de données.
- Utilisez des traits de soulignement («_») au lieu d’espaces dans les noms de table et de base de données - Cela rend les noms plus lisibles.
- Utilisez des noms explicites pour les tables et les bases de données - Par exemple, une table contenant les détails de l'employé peut être nommée de différentes manières, comme information_employé, données_employé, employé, détails_employé, nom_employé_et_adresse. Il serait logique de choisir un nom qui soit le plus explicite. Par exemple, nous pouvons choisir le nom de la table pour être Employee_details ou employee_info. Bien qu'il s'agisse d'une discussion subjective, elle doit être approuvée par plusieurs membres de l'équipe qui vont utiliser et créer ces entités.
- Évitez d'utiliser des nombres dans les noms de bases de données et de tables - Comme sample_db_2, test_table_1 etc.
Foire aux questions et réponses
Q # 1) Comment créer une table dans MySQL en utilisant Index?
Répondre: Vous pouvez ajouter un INDEX sur n'importe quelle colonne (ou combinaison de colonnes) lors de la création de la table elle-même.
Voyons un exemple d'ajout d'un index par rapport à la colonne department_id pour la table employee_details.
CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details ( name varchar(100), id int, age int, address varchar(100), department_id int, index(department_id) );
Q # 2) Comment créer une table avec date dans MySQL?
Répondre: La date est un type de données qui doit être associé à n'importe quelle colonne lors de la création d'une table.
Reportez-vous à l'exemple de commande CREATE TABLE ci-dessous avec la colonne join_date ayant DATETIME comme type de données pour un exemple de table employee_details.
CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details ( name varchar(100), id int primary key, age int, address varchar(100), joining_date datetime, department_id int );
Q # 3) Comment puis-je voir la structure de la table dans MySQL?
Répondre: Après avoir créé une table, si vous souhaitez faire référence à la structure de la table comme les colonnes, les index, etc., vous pouvez utiliser la commande DESCRIBE pour récupérer les détails.
Syntaxe:
DESCRIBE tableName;
Créons une table et voyons le résultat de la commande DESCRIBE.
CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details ( name varchar(100), id int primary key, age int, address varchar(100), department_id int ); DESCRIBE sample_db.employee_details;
Vous trouverez ci-dessous la sortie de la commande DESCRIBE.
Q # 4) Comment ajouter une FOREIGN KEY à une table dans MySQL?
Répondre: La clé étrangère est utilisée pour lier 2 tables ensemble dans MySQL. Pour utiliser la contrainte de clé étrangère, vous devez déjà avoir créé la table à laquelle vous faites référence.
>> En savoir plus sur Contrainte de clé étrangère MySQL
Essayons de comprendre cela avec un exemple. Supposons que nous ayons 2 tableaux, à savoir Département (qui contient des détails sur les différents départements du collège) et Détails sur les étudiants (tous les détails concernant les étudiants).
Le département a des colonnes - id (clé primaire) et nom.
Détails de l'étudiant - id (clé primaire), age, adresse et department_id (clé étrangère référencée à partir de la table Department).
Vous trouverez ci-dessous la syntaxe de la commande CREATE TABLE pour ces deux tables.
CREATE TABLE department ( name varchar(100), id INT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE student_details ( name varchar(100), id int not null, age int, address varchar(100), department_id int, PRIMARY KEY (id), FOREIGN KEY (department_id) references department(id) ON DELETE CASCADE );
Reportez-vous à la syntaxe de la référence FOREIGN KEY dans la table student_details où nous avons mentionné que la relation entre les colonnes et department_id doit être référencée par l'ID de colonne de la table department.
Conclusion
La commande CREATE TABLE dans MySQL qui appartient aux commandes de la catégorie Data Definition Language a été expliquée en détail ici.
Nous avons découvert les différentes options de table telles que ENGINE, CHECKSUM, etc. qui pourraient être mentionnées avec la commande CREATE TABLE pour avoir des propriétés supplémentaires définies pour la table.
Nous avons parcouru les moyens de créer un clone de la table existante dans MySQL. Enfin, nous avons parlé des conventions de dénomination des noms de table ainsi que des meilleures pratiques recommandées par l'industrie.
Bonne lecture!!
lecture recommandée
- Tutoriel MySQL Create View avec des exemples de code
- Types de données MySQL | Quels sont les différents types de données dans MySQL
- MySQL Insert Into Table - Syntaxe et exemples d'instructions d'insertion
- Tutoriel MySQL JOIN: intérieur, extérieur, croix, gauche, droite et soi
- Différence entre SQL et MySQL et SQL Server (avec exemples)
- Comment utiliser l'instruction PL SQL Insert, Update, Delete And Select
- Tutoriel MongoDB Create Database
- MongoDB Créer un utilisateur et attribuer des rôles avec des exemples