Lombard - 1-amaliy mashg’ulot Mavzu: Lombard Ma’lumotlar bazasini loyihash. Maqsad

Lombard
mysql> use lombard;
Database changed
mysql> create table mijozlar(
-> id int auto_increment primary key,
-> familyasi varchar(100),
-> ismi varchar(100),
-> otasining_ismi varchar(100),
-> pasport_raqami varchar(20),
-> pasport_seriyasi varchar(10),
-> pasport_berilgan_sana date);
Query OK, 0 rows affected (0.01 sec)
mysql> create table mahsulot_toifalari(
-> id int auto_increment primary key,
-> ism varchar(100),
-> eslatma text);
Query OK, 0 rows affected (0.02 sec)
mysql> create table yetkazib_berish(
-> id int auto_increment primary key,
-> mahsulot_tavsifi text,
-> yetkazib_berish_sanasi date,
-> qaytarish_sanasi date,
-> summa int,
-> komissiya varchar(100));
Query OK, 0 rows affected (0.01 sec)
mysql> alter table yetkazib_berish
-> add column mahsulot_toifalari_id int
-> ,
-> add foreign key (mahsulot_toifalari_id) references mahsulot_toifalari(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+--------------------+
| Tables_in_lombard |
+--------------------+
| mahsulot_toifalari |
| mijozlar |
| yetkazib_berish |
+--------------------+
3 rows in set (0.00 sec)
mysql> desc mijozlar;
+-----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| familyasi | varchar(100) | YES | | NULL | |
| ismi | varchar(100) | YES | | NULL | |
| otasining_ismi | varchar(100) | YES | | NULL | |
| pasport_raqami | varchar(20) | YES | | NULL | |
| pasport_seriyasi | varchar(10) | YES | | NULL | |
| pasport_berilgan_sana | date | YES | | NULL | |
+-----------------------+--------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
mysql> desc yetkazib_berish;
+------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| mahsulot_tavsifi | text | YES | | NULL | |
| yetkazib_berish_sanasi | date | YES | | NULL | |
| qaytarish_sanasi | date | YES | | NULL | |
| summa | int(11) | YES | | NULL | |
| komissiya | varchar(100) | YES | | NULL | |
| mahsulot_toifalari_id | int(11) | YES | MUL | NULL | |
+------------------------+--------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
mysql> desc mahsulot_toifalari;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ism | varchar(100) | YES | | NULL | |
| eslatma | text | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> INSERT INTO mijozlar (familyasi, ismi, otasining_ismi, pasport_raqami,
pasport_seriyasi, pasport_berilgan_sana)
-> VALUES ('Smith', 'John', 'Michael', '123456789', 'AB1234', '2023-11-09');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mahsulot_toifalari (ism, eslatma)
-> VALUES ('Product A', 'Description of Product A');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO yetkazib_berish (mahsulot_tavsifi, yetkazib_berish_sanasi,
qaytarish_sanasi, summa, komissiya, mahsulot_toifalari_id)
-> VALUES ('Product description', '2023-11-09', '2023-11-15', 500, '5%', 1);
Query OK, 1 row affected (0.00 sec)
mysql> select*from mijozlar;
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
| id | familyasi | ismi | otasining_ismi | pasport_raqami | pasport_seriyasi |
pasport_berilgan_sana |
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
| 1 | Smith | John | Michael | 123456789 | AB1234 | 2023-11-09
|
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
1 row in set (0.00 sec)
mysql> select*from yetkazib_berish;
+----+---------------------+------------------------+------------------+-------+-----------+-------
----------------+
| id | mahsulot_tavsifi | yetkazib_berish_sanasi | qaytarish_sanasi | summa |
komissiya | mahsulot_toifalari_id |
+----+---------------------+------------------------+------------------+-------+-----------+-------
----------------+
| 2 | Product description | 2023-11-09 | 2023-11-15 | 500 | 5% |
1 |
+----+---------------------+------------------------+------------------+-------+-----------+-------
----------------+
1 row in set (0.00 sec)
mysql> select*from mahsulot_toifalari;
+----+-----------+--------------------------+
| id | ism | eslatma |
+----+-----------+--------------------------+
| 1 | Product A | Description of Product A |
+----+-----------+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT *
-> FROM mijozlar
-> WHERE NOT familyasi = 'Smith';
Empty set (0.00 sec)
mysql> SELECT *
-> FROM mijozlar
-> WHERE familyasi = 'Smith' AND otasining_ismi = 'Michael';
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
| id | familyasi | ismi | otasining_ismi | pasport_raqami | pasport_seriyasi |
pasport_berilgan_sana |
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
| 1 | Smith | John | Michael | 123456789 | AB1234 | 2023-11-09
|
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
1 row in set (0.00 sec)
mysql> SELECT *
-> FROM mijozlar
-> WHERE ismi = 'Jessica' OR pasport_seriyasi = 'AB1234';
+----+-----------+---------+----------------+----------------+------------------+------------------
-----+
| id | familyasi | ismi | otasining_ismi | pasport_raqami | pasport_seriyasi |
pasport_berilgan_sana |
+----+-----------+---------+----------------+----------------+------------------+------------------
-----+
| 1 | Smith | John | Michael | 123456789 | AB1234 | 2023-11-09
|
| 2 | Smith | Jessica | Emily | 987654321 | XY9876 | 2023-11-15
|
+----+-----------+---------+----------------+----------------+------------------+------------------
-----+
2 rows in set (0.00 sec)
mysql> SELECT *
-> FROM mijozlar
-> WHERE pasport_raqami LIKE '12%';
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
| id | familyasi | ismi | otasining_ismi | pasport_raqami | pasport_seriyasi |
pasport_berilgan_sana |
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
| 1 | Smith | John | Michael | 123456789 | AB1234 | 2023-11-09
|
+----+-----------+------+----------------+----------------+------------------+---------------------
--+
1 row in set (0.00 sec)
|