Кутарева Елена, MySQL

Кутарева Елена

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

set foreign_key_checks=0;

DROP TABLE IF EXISTS VR_Base_Brand;
DROP TABLE IF EXISTS VR_Base_Stime;
DROP TABLE IF EXISTS VR_Base_Koeff;
DROP TABLE IF EXISTS VR_Base_Vost;
DROP TABLE IF EXISTS VR_Base_Srok;
DROP TABLE IF EXISTS VR_Base_Ampere;
DROP TABLE IF EXISTS VR_Base_Volt;
DROP TABLE IF EXISTS VR_Base_Capacity;
DROP TABLE IF EXISTS VR_Base_Technology;
DROP TABLE IF EXISTS VR_Base_Dashcam;

CREATE TABLE VR_Base_Brand (
    VR_Base_Brand_id INT(10),
    VR_Base_Brand_name VARCHAR(20),
    PRIMARY KEY (VR_Base_Brand_id)
    );

CREATE TABLE VR_Base_Stime (
    VR_Base_Stime_id INT(5),
    VR_Base_Stime_name VARCHAR(10),
    PRIMARY KEY (VR_Base_Stime_id)
    );

CREATE TABLE VR_Base_Koeff (
    VR_Base_Koeff_id INT(4),
    VR_Base_Koeff_name VARCHAR(30),
    PRIMARY KEY (VR_Base_Koeff_id)
    );

CREATE TABLE VR_Base_Vost (
    VR_Base_Vost_id INT(6),
    VR_Base_Vost_name VARCHAR (30),
    PRIMARY KEY (VR_Base_Vost_id)
    );
    
CREATE TABLE VR_Base_Srok (
    VR_Base_Srok_id INT(4),
    VR_Base_Srok_name VARCHAR (30),
    PRIMARY KEY (VR_Base_Srok_id)
    );
    
CREATE TABLE VR_Base_Ampere (
    VR_Base_Ampere_id INT(5),
    VR_Base_Ampere_name VARCHAR (30),
    PRIMARY KEY (VR_Base_Ampere_id)
    );
    
CREATE TABLE VR_Base_Volt (
    VR_Base_Volt_id INT(3),
    VR_Base_Volt_name VARCHAR (30),
    PRIMARY KEY (VR_Base_Volt_id)
    );

CREATE TABLE VR_Base_Capacity (
    VR_Base_Capacity_id INT(4),
    VR_Base_Capacity_name VARCHAR (30),
    PRIMARY KEY (VR_Base_Capacity_id)
    );

CREATE TABLE VR_Base_Technology (
    VR_Base_Technology_id INT(1),
    VR_Base_Technology_name VARCHAR(30),
    PRIMARY KEY (VR_Base_Technology_id)
    );

CREATE TABLE VR_Base_Dashcam (
    VR_Base_D_name             VARCHAR(100), 
    VR_Base_D_brand            INT(10),
    VR_Base_D_stime            INT(1),
    VR_Base_D_koeff            INT(1),
    VR_Base_D_vost             INT(1),
    VR_Base_D_srok             INT(1),
    VR_Base_D_ampere           INT(1),
    VR_Base_D_volt             INT(1),
    VR_Base_D_capacity         INT(1),
    VR_Base_D_technology       INT(1),
    INDEX(VR_Base_D_name),
    VR_Base_D_price DECIMAL(10,2),
         PRIMARY KEY (VR_Base_D_name),
         FOREIGN KEY (VR_Base_D_brand)       REFERENCES VR_Base_Brand(VR_Base_Brand_id),
         FOREIGN KEY (VR_Base_D_stime)       REFERENCES VR_Base_Stime(VR_Base_Stime_id),
         FOREIGN KEY (VR_Base_D_koeff)       REFERENCES VR_Base_Koeff(VR_Base_Koeff_id),
         FOREIGN KEY (VR_Base_D_vost)        REFERENCES VR_Base_Vost(VR_Base_Vost_id),
         FOREIGN KEY (VR_Base_D_srok)        REFERENCES VR_Base_Srok(VR_Base_Srok_id),
         FOREIGN KEY (VR_Base_D_ampere)      REFERENCES VR_Base_Ampere(VR_Base_Ampere_id),
         FOREIGN KEY (VR_Base_D_volt)        REFERENCES VR_Base_Volt(VR_Base_Volt_id),
         FOREIGN KEY (VR_Base_D_capacity)    REFERENCES VR_Base_Capacity(VR_Base_Capacity_id),
         FOREIGN KEY (VR_Base_D_technology)  REFERENCES VR_Base_YesNo(VR_Base_YesNo_id)
       );
set foreign_key_checks=1;

INSERT INTO VR_Base_Brand (VR_Base_Brand_id, VR_Base_Brand_name)
VALUES  (0, 'Xiaomi  '),
        (1, 'Remax'), 
        (2, 'Qumo '), 
        (3, 'Defender '),
        (4, 'Sony'),
        (5, 'Totu Joe '),
        (6, 'TEC '),
        (7, 'Pisen  '),
        (8, 'Interstep '),
        (9, 'Vorson');

INSERT INTO VR_Base_Stime (VR_Base_Stime_id, VR_Base_Stime_name)
VALUES  (0, '8760'),
        (1, '10512'), 
        (2, '10950'), 
        (3, '13140'),
        (4, '17520');
        
INSERT INTO VR_Base_Koeff (VR_Base_Koeff_id, VR_Base_Koeff_name)
VALUES  (0, '0,9995'),
        (1, '0,9994'), 
        (2, '0,9993'), 
        (3, '0,9992');
        
INSERT INTO VR_Base_Vost (VR_Base_Vost_id, VR_Base_Vost_name)
VALUES  (0, '4,5'),
        (1, '5'), 
        (2, '5,5'),
        (3, '6'),
        (4, '7'),
        (5, '7,5');
        
INSERT INTO VR_Base_Srok (VR_Base_Srok_id, VR_Base_Srok_name)
VALUES  (0, ' 1'),
        (1, ' 1,5'),
        (2, ' 2'),
        (3, ' 3'); 
        
INSERT INTO VR_Base_Ampere (VR_Base_Ampere_id, VR_Base_Ampere_name)
VALUES  (0, '2А '),
        (1, '1А, 2.1А'), 
        (2, '3А'), 
        (3, '2,1А'),
        (4, '2,4А');      
        
INSERT INTO VR_Base_Volt (VR_Base_Volt_id, VR_Base_Volt_name)
VALUES  (0, '5В'),
        (1, '9В'),
        (2, '5А, 9А, 12В');    
        
INSERT INTO VR_Base_Capacity (VR_Base_Capacity_id, VR_Base_Capacity_name)
VALUES  (0, '10000 мАч'),
        (1, '15000 мАч'), 
        (2, '20000 мАч'), 
        (3, '20800 мАч');          
            
INSERT INTO VR_Base_Technology (VR_Base_Technology_id, VR_Base_Technology_name)
VALUES  (0, 'Отсутствует'),
        (1, 'Есть');

INSERT INTO VR_Base_Dashcam (VR_Base_D_name, VR_Base_D_brand, VR_Base_D_stime, VR_Base_D_koeff, VR_Base_D_vost, 
                             VR_Base_D_srok, VR_Base_D_ampere, VR_Base_D_volt, VR_Base_D_capacity,
                             VR_Base_D_technology, VR_Base_D_price)
VALUE   ('Xiaomi Mi Power Bank 2S ',             0,  0,  0,  0,  0,  0,  2,  0,  1,  1590), 
        ('Remax Proda Jane',                     1,  3,  0,  4,  2,  1,  0,  2,  0,  2480),
        ('Qumo PowerAid  (V2)',                  2,  4,  1,  5,  3,  2,  2,  3,  1,  1990),
        ('Defender Power Bank ExtraLife',        3,  2,  2,  3,  1,  3,  0,  1,  0,  1990),
        ('Sony CP-V10',                          4,  0,  2,  2,  0,  2,  0,  0,  1,  2643),
        ('Totu Joe',                             5,  0,  3,  3,  0,  4,  0,  0,  1,  1990),
        ('TEC alumSlim',                         6,  0,  3,  2,  0,  3,  0,  0,  0,  1688),
        ('Pisen Power Station',                  7,  0,  3,  2,  0,  3,  0,  2,  0,  3490),
        ('Interstep PB10DQi Pro',                8,  0,  3,  3,  0,  2,  1,  0,  0,  6990),
        ('Vorson Pebble',                        9,  1,  1,  4,  0,  3,  0,  0,  1,  1190);

-- output        	
-- SELECT * FROM VR_Base_Dashcam;
SELECT * FROM VR_Base_Brand;
SELECT * FROM VR_Base_Stime;
SELECT * FROM VR_Base_Koeff;
SELECT * FROM VR_Base_Vost;
SELECT * FROM VR_Base_Srok;
SELECT * FROM VR_Base_Ampere;
SELECT * FROM VR_Base_Volt;
SELECT * FROM VR_Base_Capacity;
SELECT * FROM VR_Base_Technology;

SELECT VR_Base_D_name                               AS 'Наименование',
       brand.VR_Base_Brand_name                     AS 'Бренд',
       stime.VR_Base_Stime_name                     AS 'Среднее время наработки на отказ (часов)',
       koeff.VR_Base_Koeff_name                     AS 'Коэф-фициент готов-ности (техни-ческого использо-вания)',
       vost.VR_Base_Vost_name                       AS 'Среднее время восстанов-ления (часов)',
       srok.VR_Base_Srok_name                       AS 'Средний срок службы (лет)',
       ampere.VR_Base_Ampere_name                   AS 'Выходной ток ',
       volt.VR_Base_Volt_name                       AS 'Выходное напряжение',
       capacity.VR_Base_Capacity_name               AS 'Емкость',
       technology.VR_Base_Technology_name           AS 'Технология быстрой зарядки',
       VR_Base_D_price                              AS 'Цена'

FROM   VR_Base_Dashcam AS dashcam
 
INNER JOIN  VR_Base_Brand            AS brand              ON brand.VR_Base_Brand_id                         = dashcam.VR_Base_D_brand
INNER JOIN  VR_Base_Stime            AS stime              ON stime.VR_Base_Stime_id                         = dashcam.VR_Base_D_stime
INNER JOIN  VR_Base_Koeff            AS koeff              ON koeff.VR_Base_Koeff_id                         = dashcam.VR_Base_D_koeff
INNER JOIN  VR_Base_Vost             AS vost               ON vost.VR_Base_Vost_id                           = dashcam.VR_Base_D_vost
INNER JOIN  VR_Base_Srok             AS srok               ON srok.VR_Base_srok_id                           = dashcam.VR_Base_D_srok
INNER JOIN  VR_Base_Ampere           AS ampere             ON ampere.VR_Base_Ampere_id                       = dashcam.VR_Base_D_ampere
INNER JOIN  VR_Base_Volt             AS volt               ON volt.VR_Base_Volt_id                           = dashcam.VR_Base_D_volt
INNER JOIN  VR_Base_Capacity         AS capacity           ON capacity.VR_Base_Capacity_id                   = dashcam.VR_Base_D_capacity
INNER JOIN  VR_Base_Technology       AS technology         ON technology.VR_Base_Technology_id               = dashcam.VR_Base_D_technology;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS VR_Stime_to_Vost;
CREATE TABLE VR_Stime_to_Vost (
  id int(11) UNIQUE AUTO_INCREMENT,
  id_stime int(11),
  id_vost int(11),
  PRIMARY KEY (id),
  FOREIGN KEY (id_stime)    REFERENCES VR_Base_Stime(VR_Base_Stime_id),
  FOREIGN KEY (id_vost)     REFERENCES VR_Base_Vost(VR_Base_Vost_id)
); 
ALTER TABLE VR_Stime_to_Vost AUTO_INCREMENT=1;
ALTER TABLE VR_Stime_to_Vost CONVERT TO CHARACTER SET utf8;
SET FOREIGN_KEY_CHECKS = 1;

DESCRIBE VR_Stime_to_Vost;
INSERT INTO VR_Stime_to_Vost (id_stime, id_vost) VALUES
(0, 0),
(3, 4),
(4, 5),
(2, 3),
(0, 2),
(0, 3),
(0, 2),
(0, 2),
(0, 3),
(1, 4);

SELECT id AS 'Индекс',
       stime.VR_Base_Stime_name         AS 'Среднее время наработки на отказ',
       vost.VR_Base_Vost_name           AS 'Среднее время восстанов-ления'

FROM   VR_Stime_to_Vost AS r2s

INNER JOIN  VR_Base_Stime     AS stime      ON stime.VR_Base_Stime_id         = r2s.id_stime
INNER JOIN  VR_Base_Vost      AS vost       ON vost.VR_Base_Vost_id           = r2s.id_vost;


run \| edit \| history \| help	0