zapytanie sql zakupy a kody

wszystkie zakupy z kodem promocyjnym: 
SELECT k.`id_order`, k.`reference`, k.`id_cart`, k.`total_paid_tax_excl`, h.`name`, h.`value_tax_excl` FROM `ps_orders` k join `ps_order_cart_rule` h on k.`id_order` = h.`id_order` WHERE k.`invoice_date` BETWEEN '2015-12-06' AND '2016-01-03';

wszystkie zakupy + kod promocyjny (jeżeli był): 
SELECT k.`id_order`, k.`reference`, k.`id_cart`, k.`total_paid_tax_excl`, h.`name`, h.`value_tax_excl` FROM `ps_orders` k left join `ps_order_cart_rule` h on k.`id_order` = h.`id_order` WHERE k.`invoice_date` BETWEEN '2015-12-06' AND '2016-01-03' ;

zapytanie sql wyciąganie danych z 2 tabel

"SELECT mk.key, sum(mc.cost) FROM `monitoring_history`
 mh, monitoring_keys mk, monitoring_cost mc WHERE mh.key_id = mc.key_id 
and mh.key_id = mk.key_id and mh.site_id = mk.site_id and mh.`site_id` =
 889 AND mh.`data` BETWEEN '2015-01-01' AND '2015-01-31' and mh.position
 <= mc.to and mh.position >= mc.from group by mk.key"
 
 
 
 SELECT * FROM `monitoring_keys` where `site_id` = 889 

 SELECT `position` FROM `monitoring_history` WHERE `data`= '2015-05-01' AND `site_id` = 889
 
 
SELECT mk.key, sum(mc.cost) FROM `monitoring_history`
 mh, monitoring_keys mk, monitoring_cost mc WHERE mh.key_id = mc.key_id 
and mh.key_id = mk.key_id and mh.site_id = mk.site_id and mh.`site_id` =
 889 AND mh.`data` BETWEEN '2015-01-01' AND '2015-01-31' and mh.position
 <= mc.to and mh.position >= mc.from group by mk.key
 
 `monitoring_keys`
  interesują nas tutaj:
 
 site_id - id strony
 key_id - id frazy
 key - fraza
 
 `monitoring_history`
  interesują nas tutaj:
  
 `position` - pozycja strony
 `data` - danego dnia
 
 
 
 
  SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data` FROM `monitoring_keys` k  NATURAL JOIN `monitoring_history` h WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND CURDATE();
  
 
 OSTATNIE ZAPYTANIE, DOBRE
 
   SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-26'
   
   
   SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data`, c.`cost` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` join `monitoring_cost` c on (c.`key_id` = h.`key_id` AND h.`position` BETWEEN c.`from` AND c.`to`) WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-26';
   
      SELECT k.`key`, h.`position`, h.`data`, c.`cost` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` join `monitoring_cost` c on (c.`key_id` = h.`key_id` AND h.`position` BETWEEN c.`from` AND c.`to`) WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-30';

baza danych teatr (zapytanie SQL)

CREATE TABLE IF NOT EXISTS `actors` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `gender` varchar(1) NOT NULL,
 `birth_date` date NOT NULL,
 `description` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ;

INSERT INTO `actors` (`id`, `first_name`, `last_name`, `gender`, `birth_date`, `description`) VALUES
(1, 'Antonina', 'Choroszy', 'K', '1970-03-05', 'Od 3 lat działa w teatrze'),
(2, 'Stefan', 'Wleklak', 'M', '1984-04-05', 'Dostał Oskara za rolę myszki'),
(3, 'Andrzej', 'Krajna', 'M', '1985-02-05', 'Szczególnie angażowany w role książąt'),
(5, 'Stefan', 'Wleklak', 'M', '1984-04-05', 'Dostał Oskara za rolę myszki'),
(6, 'Wojciech', 'Łuczak', 'M', '1980-02-05', 'Dostał złotą malinę oraz oskara za króla denarów');

CREATE TABLE IF NOT EXISTS `performances` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `description` text DEFAULT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `performances` (`title`) VALUES
('Gra o tron'),
('Dexter'),
('Wikingowie'),
('Macbeth'),
('Hamlet'),
('Otello');


CREATE TABLE IF NOT EXISTS `performances_performed` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `performance_id` int(10) NOT NULL,
 `date` date NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO performances_performed (`id`, `performance_id`, `date`) VALUES
(1,'1', '2014-05-06-19-00-00'),
(2,'2', '2015-01-06-19-00-00'),
(3,'3', '2012-03-07-19-00-00');


CREATE TABLE IF NOT EXISTS `roles` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `actor_id` int(10) NOT NULL,
 `performance_id` int(10) NOT NULL,
 PRIMARY KEY (`id`)
);


CREATE TABLE IF NOT EXISTS `authors` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `nick` varchar(255) DEFAULT NULL,
 `description` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
);


INSERT INTO `authors` (`id`, `first_name`, `last_name`, `nick`, `description`) VALUES
(1, 'William', 'Shakespeare', NULL , 'dżentelmen z Londynu'),
(2, 'Dorota', 'Masłowska', 'Masłoska', 'Rocznik 1984'),
(3, 'T.S.', 'Eliot', NULL , 'Egzystencjalista trendsetter'),
(4, 'Samuel', 'Beckett', 'Sam', 'Geniusz');


CREATE TABLE IF NOT EXISTS `directors` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `nick` varchar(255) DEFAULT NULL,
 `description` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ;

INSERT INTO `directors` (`id`, `first_name`, `last_name`, `nick`, `description`) VALUES
(1, 'Anna', 'Kozłowska', NULL , 'Gwiazda'),
(2, 'Krzysztof', 'Warlikowski', NULL, 'Okrzyknięty najlepszym polskim reżyserem');

CREATE TABLE IF NOT EXISTS `reviewers` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `reviewers` (`id`, `name`, `last_name`) VALUES
(1, 'Łukasz', 'Drewniak'),
(2, 'Anna', 'Koza');

CREATE TABLE IF NOT EXISTS `reviews` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) DEFAULT NULL,
 `content` varchar(255) NOT NULL,
 `newspaper_title` varchar(255) DEFAULT NULL,
 `reviewer_id` int(10) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `reviews` (`id`, `title`, `content`, `newspaper_title`) VALUES
(1, 'Skandal w teatrze', 'Aktorka zdjęła bluzkę', 'Fakt'),
(2, 'Anioł to reżyserował', 'Tylko tyle', 'Super Express');




CREATE TABLE IF NOT EXISTS `photos` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `picture_path` varchar(255) NOT NULL,
 `photographer` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ;

CREATE TABLE IF NOT EXISTS `stages` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `stages` (`id`, `name`) VALUES
(1, 'Malarnia'),
(2, 'Scena Wielka');


CREATE TABLE IF NOT EXISTS `zones` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `stage_id` varchar(8) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `zones` (`id`, `name`, `stage_id`) VALUES
(1, 'Strefa VIP', 1),
(2, 'Balkon 1', 2),
(3, 'Strefa normal', 1),
(4, 'Parter', 2),
(5, 'Balkon 2', 2);

CREATE TABLE IF NOT EXISTS `seats` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `number` varchar(8) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `seats` (`id`, `number`) VALUES
(1, '1'),
(2, '2'),
(3, '3'),
(4, '4'),
(5, '5');

CREATE TABLE IF NOT EXISTS `tickets` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `price` varchar(8) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `tickets` (`id`, `price`) VALUES
(1, '100'),
(2, '200'),
(3, '35'),
(4, '40'),
(5, '50');


CREATE TABLE IF NOT EXISTS `tickets_printed` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `ticket_id` int(10) NOT NULL,
 `seat_id` int(10) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `tickets_printed` (`id`, `ticket_id`, `seat_id`) VALUES
(1, '1', '1');

CREATE TABLE IF NOT EXISTS `ticket_pricegroups` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `ticket_pricegroups` (`id`, `name`) VALUES
(1, 'Emeryci'),
(2, 'Studenci'),
(3, 'Normalne'),
(4, 'Wejściówki');

RELACJE:

performances_performed_stages
#id
id_performance_performed
id_stage
-----------------------------
CREATE TABLE IF NOT EXISTS `performances_performed_stages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_performance_performed` int(10) NOT NULL,
  `id_stage` varchar(255) NOT NULL,
  );
--------------------------------------------------------------------
--------------------------------------------------------------------
zones_stages
#id
id_zone
id_stage
-------------------------------
CREATE TABLE IF NOT EXISTS `zones_stages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_zone` int(10) NOT NULL,
  `id_stage` varchar(255) NOT NULL,
  );
--------------------------------------------------------------------
--------------------------------------------------------------------
seats_zones
#id
id_seat
id_zone
-------------------------------
CREATE TABLE IF NOT EXISTS `seats_zones` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_seat` int(10) NOT NULL,
  `id_zone` varchar(255) NOT NULL,
  );
--------------------------------------------------------------------
-------------------------------------------------------------------- 
roles_actor_replacements
#id
id_role
id_actor
start_date
finish_date
-------------------------------
CREATE TABLE IF NOT EXISTS `roles_actor_replacements` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_role` int(10) NOT NULL,
  `id_actor` varchar(255) NOT NULL,
  `start_date` date NOT NULL,
  `finish_date` date,
  );
--------------------------------------------------------------------
-------------------------------------------------------------------- 
tickets_performances_performed
#id
id_performance performed
id_ticket
-------------------------------
CREATE TABLE IF NOT EXISTS `tickets_performances_performed` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_performance performed` int(10) NOT NULL,
  `id_ticket` int(10) NOT NULL,
  );
--------------------------------------------------------------------
--------------------------------------------------------------------  
tickets_printed_tickets_price_groups
#id
id_ticket_printed
id_ticket_price_group
-------------------------------
CREATE TABLE IF NOT EXISTS `tickets_printed_tickets_price_groups` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_ticket_printed` int(10) NOT NULL,
  `id_ticket_price_group` int(10) NOT NULL,
  );

encja, przykładowe formaty danych

AKTOR           ——————>     Aktorzy

#ID                                    – ID .INT PRIMARY. KEY

* imię                                – VARCHAR(255) NOT NULL

* nazwisko                       – VARCHAR(255) NOT NULL

* płeć                                – VARCHAR(1) („K” lub „M”)

o zdjęcie                          – VARCHAR(255) NULL  /  BLOB

* data urodzenia            – DATE

Moja baza – zadanie domowe



-- --------------------------------------------------------
-- Struktura tabeli dla tabeli `actors`

CREATE TABLE IF NOT EXISTS `actors` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `picture_path` varchar(255) DEFAULT NULL,
  `birth_date` date NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `actors`
--

INSERT INTO `actors` (`id`, `first_name`, `last_name`, `gender`, `picture_path`, `birth_date`, `description`) VALUES
(1, 'Antonina', 'Choroszy', 'K', NULL, '1970-03-05', 'Od 3 lat działa w teatrze'),
(2, 'Stefan', 'Wleklak', 'M', NULL, '1984-04-05', 'Dostał Oskara za rolę myszki');

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `performances`
--

CREATE TABLE IF NOT EXISTS `performances` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Zrzut danych tabeli `performances`
--

INSERT INTO `performances` (`id`, `title`) VALUES
(1, 'Gra o tron'),
(2, 'Dexter'),
(3, 'Wikingowie');

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `actor_id` int(10) NOT NULL,
  `performance_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


----------------------------------------BASIA DOPISANE LINIJKI----------------------------------

CREATE TABLE IF NOT EXISTS `authors` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `nick` varchar(255) DEFAULT NULL,
  `picture_path` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `authors`
--

INSERT INTO `authors` (`id`, `first_name`, `last_name`, `nick`, `picture_path`, `description`) VALUES
(1, 'William', 'Shakespeare',  NULL , NULL, 'dżentelmen z Londynu'),
(2, 'Dorota', 'Masłowska', 'Masłoska', NULL, 'Rocznik 1984');







CREATE TABLE IF NOT EXISTS `directors` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `nick` varchar(255) DEFAULT NULL,
  `picture_path` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `directors`
--

INSERT INTO `directors` (`id`, `first_name`, `last_name`, `nick`, `picture_path`, `description`) VALUES
(1, 'Anna', 'Kozłowska',  NULL , NULL, 'Gwiazda'),
(2, 'Krzysztof', 'Warlikowski', NULL, NULL, 'Okrzyknięty najlepszym polskim reżyserem');





CREATE TABLE IF NOT EXISTS `reviews` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `content` varchar(255) NOT NULL,
  `newspaper_title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `reviews`
--

INSERT INTO `reviews` (`id`, `title`, `content`, `newspaper_title`) VALUES
(1, 'Skandal w teatrze', 'Aktorka zdjęła bluzkę', 'Fakt'),
(2, 'Anioł to reżyserował', 'Tylko tyle', 'Super Express');








CREATE TABLE IF NOT EXISTS `reviewers` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `picture_path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `reviewers`
--

INSERT INTO `reviewers` (`id`, `name`, `last_name`, `picture_path`) VALUES
(1, 'Łukasz', 'Drewniak', NULL),
(2, 'Anna', 'Koza', NULL);








CREATE TABLE IF NOT EXISTS `galleries` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `galleries`
--

INSERT INTO `galleries` (`id`, `name`) VALUES
(1, 'Premiera przedstawienia'),
(2, 'Zdjęcia z próby generalnej');



CREATE TABLE IF NOT EXISTS `photos` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `picture_path` varchar(255) NOT NULL,
  `photographer` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;



CREATE TABLE IF NOT EXISTS `stages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `stages`
--

INSERT INTO `stages` (`id`, `name`) VALUES
(1, 'Malarnia'),
(2, 'Scena Wielka');




CREATE TABLE IF NOT EXISTS `zones` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `zones`
--

INSERT INTO `zones` (`id`, `name`) VALUES
(1, 'A'),
(2, 'B');



CREATE TABLE IF NOT EXISTS `sponsors` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `sponsors`
--

INSERT INTO `sponsors` (`id`, `name`) VALUES
(1, 'Enea'),
(2, 'Pepsi');


CREATE TABLE IF NOT EXISTS `seats` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `seat_number` varchar(8) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `seats`
--

INSERT INTO `seats` (`id`, `seat_number`) VALUES
(1, '1'),
(2, '2');





Budowa struktury bazy danych

budowa_encji

CREATE TABLE `roles` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
    );

    
dodanie kolejnych kolumn/atrybutów za pomocą ALTER TABLE
    
ALTER TABLE `roles` ADD COLUMN `actor_id` INT(10) NOT NULL REFERENCES `actors` (`id`),
ADD COLUMN `performance_id` INT(10) NOT NULL REFERENCES `performances` (`id`);

domyślnie nie można usunąć rekordu, do którego odwołują się poprzez klucze obce rekordy innej tabeli
"ON DELETE SET NULL" - zamienia wartość kolumny z kluczem obcym na wartość pustą (NULL) 
"ON DELETE CASCADE" - usuwa rekord odwołujący się do usuniętego rekordu 'performances'

ALTER TABLE `roles` ADD COLUMN `actor_id` INT(10) NOT NULL REFERENCES `actors` (`id`),
ADD COLUMN `performance_id` INT(10) NOT NULL REFERENCES `performances` (`id`) ON DELETE CASCADE;

pełen zrzut z 15 lipca:



-- phpMyAdmin SQL Dump
-- version 4.0.9
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Czas wygenerowania: 15 Lip 2015, 17:52
-- Wersja serwera: 5.5.34
-- Wersja PHP: 5.4.22

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Baza danych: `teatr`
--

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `actors`
--

CREATE TABLE IF NOT EXISTS `actors` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `picture_path` varchar(255) DEFAULT NULL,
  `birth_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `actors`
--

INSERT INTO `actors` (`id`, `first_name`, `last_name`, `gender`, `picture_path`, `birth_date`) VALUES
(1, 'Antonina', 'Choroszy', 'K', NULL, '1970-03-05'),
(2, 'Stefan', 'Wleklak', 'M', NULL, '1984-04-05');

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `performances`
--

CREATE TABLE IF NOT EXISTS `performances` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Zrzut danych tabeli `performances`
--

INSERT INTO `performances` (`id`, `title`) VALUES
(1, 'Gra o tron'),
(2, 'Dexter'),
(3, 'Wikingowie');

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `actor_id` int(10) NOT NULL,
  `performance_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

SQL, nauka komend

Odczyt wszystkich wierszy z tabeli users
	SELECT * FROM `users` WHERE 1
	lub
	SELECT * FROM `users`
	
Odczyt wszystkich nazwisk  z tabeli users, gdzie imię to Bogumił
	SELECT `last_name` 
	FROM `users` 
	WHERE first_name =  'Bogumił'
	
Odczyt wszystkich niepowtarzalnych nazwisk  z tabeli users, gdzie imię to Bogumił
	SELECT DISTINCT  `last_name` 
	FROM  `users` 
	WHERE first_name =  'Bogumił'

Odczyt wszystkich użytkowników o nazwisku Jack, którzy zarejestrowali się 2 kwietnia 2014 roku
	SELECT * 
	FROM  `users` 
	WHERE  `registration_date` =  '2014-04-02'
	AND  `last_name` =  'Jack'
	
Odczyt wszystkich użytkowników o nazwisku Jack, którzy zarejestrowali się 2 kwietnia 2014 roku i mają adres e-mail zakończony ciągiem prokonto.pl
	SELECT * 
	FROM  `users` 
	WHERE  `registration_date` =  '2014-04-02'
	AND  `last_name` =  'Jack'
	AND  `email` LIKE  '%prokonto.pl'
	
Odczyt wszystkich użytkowników o nazwisku Jack, którzy zarejestrowali się 2 kwietnia 2014 roku i mają adres e-mail zakończony ciągiem prokonto.pl lub o2.pl, ale nie go2.pl	
	SELECT * 
	FROM  `users` 
	WHERE  `registration_date` =  '2014-04-02'
	AND  `last_name` =  'Jack'
	AND ( `email` LIKE  '%prokonto.pl'
		OR `email` LIKE  '%o2.pl'
			AND `email` NOT LIKE  '%go2.pl');

drop table if exists actors;

CREATE TABLE actors (
    id int(10) primary key,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    gender varchar(1) not null,
    picture_path varchar(255) null,
    birth_date date not null
);

ALTER TABLE actors
modify COLUMN id INT(10) AUTO_INCREMENT;
 
INSERT INTO `actors`(`first_name`, `last_name`, `gender`, `picture_path`, `birth_date`) VALUES ('Antonina', 'Choroszy', 'K', NULL, '1970-03-05');
 
INSERT INTO `actors`(`first_name`, `last_name`, `gender`, `picture_path`, `birth_date`) VALUES ('Stefan', 'Wleklak', 'M', NULL, '19840-03-05');
 
UPDATE `actors` SET `birth_date` = '1984-04-05' WHERE `first_name` = 'Stefan';


A OTO KOPIA TEGO SAMEGO W FORMIE ZRZUTU SQL:

-- phpMyAdmin SQL Dump
-- version 4.0.9
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Czas wygenerowania: 25 Cze 2015, 17:32
-- Wersja serwera: 5.5.34
-- Wersja PHP: 5.4.22

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Baza danych: `teatr`
--

-- --------------------------------------------------------

--
-- Struktura tabeli dla tabeli `actors`
--

DROP TABLE IF EXISTS `actors`;
CREATE TABLE IF NOT EXISTS `actors` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `picture_path` varchar(255) DEFAULT NULL,
  `birth_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `actors`
--

INSERT INTO `actors` (`id`, `first_name`, `last_name`, `gender`, `picture_path`, `birth_date`) VALUES
(1, 'Antonina', 'Choroszy', 'K', NULL, '1970-03-05'),
(2, 'Stefan', 'Wleklak', 'M', NULL, '1984-04-05');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


Zapytania SQL, monitoring

"SELECT mk.key, sum(mc.cost) FROM `monitoring_history`
 mh, monitoring_keys mk, monitoring_cost mc WHERE mh.key_id = mc.key_id 
and mh.key_id = mk.key_id and mh.site_id = mk.site_id and mh.`site_id` =
 889 AND mh.`data` BETWEEN '2015-01-01' AND '2015-01-31' and mh.position
 <= mc.to and mh.position >= mc.from group by mk.key"
 
 
 
SELECT * FROM `monitoring_keys` where `site_id` = 889 

SELECT `position` FROM `monitoring_history` WHERE `data`= '2015-05-01' AND `site_id` = 889
 
 
SELECT mk.key, sum(mc.cost) FROM `monitoring_history`
 mh, monitoring_keys mk, monitoring_cost mc WHERE mh.key_id = mc.key_id 
and mh.key_id = mk.key_id and mh.site_id = mk.site_id and mh.`site_id` =
 889 AND mh.`data` BETWEEN '2015-01-01' AND '2015-01-31' and mh.position
 <= mc.to and mh.position >= mc.from group by mk.key
 
`monitoring_keys`
  interesują nas tutaj:
 
site_id - id strony
key_id - id frazy
key - fraza
 
`monitoring_history`
interesują nas tutaj:
  
`position` - pozycja strony
`data` - danego dnia
 
 
 
 
SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data` FROM `monitoring_keys` k  NATURAL JOIN `monitoring_history` h WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND CURDATE();
  
 
OSTATNIE ZAPYTANIE, DOBRE
 
SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-26'
   
   
SELECT k.`site_id`, k.`key_id`, k.`key`, h.`position`, h.`data`, c.`cost` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` join `monitoring_cost` c on (c.`key_id` = h.`key_id` AND h.`position` BETWEEN c.`from` AND c.`to`) WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-26';
   
SELECT k.`key`, h.`position`, h.`data`, c.`cost` FROM `monitoring_keys` k join `monitoring_history` h on k.`key_id` = h.`key_id` join `monitoring_cost` c on (c.`key_id` = h.`key_id` AND h.`position` BETWEEN c.`from` AND c.`to`) WHERE k.`site_id`= 889 AND h.`data` BETWEEN '2015-05-01' AND '2015-06-30';