Scribd
Upload
22 views5 pages

MYSQL

The document describes creating databases and tables in MySQL. Key points: - The user creates a database called "agencevoyage1" and sets it as the active database. - Several tables are created to store commune (city) data, neighborhood data, property types, amenities, and properties with relationships between the tables. - Data is inserted into the tables and some errors are encountered while inserting related data due to foreign key constraints. - At the end, the tables created in the database are listed and the structure of two tables is described.

Uploaded by

RAZIQ YOUSSEF
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
22 views5 pages

MYSQL

The document describes creating databases and tables in MySQL. Key points: - The user creates a database called "agencevoyage1" and sets it as the active database. - Several tables are created to store commune (city) data, neighborhood data, property types, amenities, and properties with relationships between the tables. - Data is inserted into the tables and some errors are encountered while inserting related data due to foreign key constraints. - At the end, the tables created in the database are listed and the structure of two tables is described.

Uploaded by

RAZIQ YOUSSEF
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

ype 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE AGENCEVOYAGE;

ERROR 1007 (HY000): Can't create database 'agencevoyage'; database exists

mysql> CREATE DATABASE agencevoyage1;

Query OK, 1 row affected (0.01 sec)

mysql> use agencevoyage1;

Database changed

mysql> show table;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1

mysql> show database;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'database' at line 1

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| agencevoyage |

| agencevoyage1 |

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

6 rows in set (0.00 sec)

mysql> use agencevoyage1;

Database changed
mysql> CREATE TABLE commune (

-> id INT PRIMARY KEY,

-> nom VARCHAR(50)

-> CREATE TABLE commune (idcommune INT PRIMARY KEY,nom VARCHAR(50));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CREATE
TABLE commune (idcommune INT PRIMARY KEY,nom VARCHAR(50))' at line 4

mysql> CREATE TABLE commune (idcommune INT PRIMARY KEY,nom VARCHAR(50) not null);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO commune (idcommune, nom) VALUES (1, 'Paris'),(2, 'Lyon');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE quartier (idquartier INT PRIMARY KEY,nom VARCHAR(50) not
null,commune_idcommune INT, FOREIGN KEY (commune_idcommune) REFERENCES
commune(idcommune));

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO quartier (idquartier, nom, commune_idcommune) VALUES(1,


'Quartier 1', 1), (2, 'Quartier 2', 1), (3, 'Quartier 3', 2),(4, 'Quartier 4', 2);

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE type_logement (idType_logement INT PRIMARY KEY,nom VARCHAR(50)


not null);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO type_logement (idtype_logement, nom) VALUES (1, 'Appartement'),


(2, 'Maison'),(3, 'villa');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0


mysql> CREATE TABLE option_logement (idoption_logement INT PRIMARY KEY,nom
VARCHAR(50) not null);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO option_logement (idoption_logement, nom) VALUES(1, 'Wifi'),(2,


'douche'),(3, 'piscine'),(4, 'ménage');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE logement (idlogement INT PRIMARY KEY,adresse VARCHAR(100)not


null,quartier_idquartier INT,type_logement_idtype_logement INT,FOREIGN KEY
(quartier_idquartier) REFERENCES quartier(idquartier),FOREIGN KEY
(type_logement_idtype_logement) REFERENCES type_logement(idtype_logement));

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO logement (idlogement, adresse, quartier_idquartier,


type_logement_idtype_logement) VALUES(1, 'quartier 1', 1, 1),(2, 'quartier 2', 2,
2),(3, 'quartier 3', 3, 3),(4, 'quartier 4', 4, 4),(5, 'quartier 5', 5, 5),(6,
'quartier 6', 6, 6);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`agencevoyage1`.`logement`, CONSTRAINT `logement_ibfk_2` FOREIGN KEY
(`type_logement_idtype_logement`) REFERENCES `type_logement` (`idType_logement`))

mysql> INSERT INTO logement (idlogement, adresse, quartier_idquartier,


type_logement_idtype_logement) VALUES(1, 'quartier1', 1, 1),(2, 'quartier2', 2, 3),
(3, 'quartier3', 3, 2),(4, 'quartier 4', 4, 1),(5, 'quartier 5', 1, 2),(6,
'quartier7', 3, 3);

Query OK, 6 rows affected (0.01 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE logement_option (logement_idlogement INT,option_idoption


INT,PRIMARY KEY (logement_idlogement, option_idoption), FOREIGN KEY
(logement_idlogement) REFERENCES logement(idlogement),FOREIGN KEY (option_idoption)
REFERENCES option_logement(idoption_logement));

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO logement_option (logement_idlogement, option_idoption) VALUES(1,


1),(1, 2),(1, 3),(2, 1),(2, 4),(3, 1),(3, 3),(3, 4),(4,

-> INSERT INTO logement_option (logement_idlogement, option_idoption) VALUES(1,


1),(1, 2),(1, 3),(2, 1),(2, 4),(3, 1),(3, 3),(3, 4);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'INTO
logement_option (logement_idlogement, option_idoption) VALUES(1, 1),(1, 2),' at
line 2

mysql> INSERT INTO logement_option (logement_idlogement, option_idoption) VALUES(1,


1),(1, 2),(1, 3),(2, 1),(2, 4),(3, 1),(3, 3),(3, 4);(4,4);

Query OK, 8 rows affected (0.01 sec)

Records: 8 Duplicates: 0 Warnings: 0

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '4,4)' at
line 1

mysql> INSERT INTO logement_option (logement_idlogement, option_idoption) VALUES(1,


1),(1, 2),(1, 3),(2, 1),(2, 4),(3, 1),(3, 3),(3, 4);(4,2);

ERROR 1062 (23000): Duplicate entry '1-1' for key 'logement_option.PRIMARY'

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '4,2)' at
line 1

mysql> INSERT INTO logement_option (logement_idlogement, option_idoption) VALUES(1,


1),(1, 2),(1, 3),(2, 1),(2, 4),(3, 1),(3, 3),(3, 4),(4, 2);

ERROR 1062 (23000): Duplicate entry '1-1' for key 'logement_option.PRIMARY'

mysql> ^C

mysql> show tables;

+-------------------------+

| Tables_in_agencevoyage1 |

+-------------------------+

| commune |

| logement |

| logement_option |

| option_logement |

| quartier |

| type_logement |

+-------------------------+

6 rows in set (0.01 sec)


mysql> desc commune;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| idcommune | int | NO | PRI | NULL | |

| nom | varchar(50) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> desc logement;

+-------------------------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------------------------+--------------+------+-----+---------+-------+

| idlogement | int | NO | PRI | NULL | |

| adresse | varchar(100) | NO | | NULL | |

| quartier_idquartier | int | YES | MUL | NULL | |

| type_logement_idtype_logement | int | YES | MUL | NULL | |

+-------------------------------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> ^C

mysql>

You might also like