Ucim rad sa bazama podataka u MySQL-u iz knjige:
"PHP and MySQL Web Development, 2Ed"
Za rad koristim WAMP5 1.7.0.
U knjizi se prati primer baze podataka koja se moze kreirati sledecim upitom:
-- phpMyAdmin SQL Dump
-- version 2.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 03, 2007 at 07:49 AM
-- Server version: 5.0.27
-- PHP Version: 4.4.6
--
-- Database: `books`
--
DROP TABLE IF EXISTS `book_reviews`;
CREATE TABLE `book_reviews` (
`isbn` char(13) NOT NULL,
`review` text,
PRIMARY KEY (`isbn`)
) TYPE=InnoDB;
INSERT INTO `book_reviews` (`isbn`, `review`) VALUES
('0-672-31697-8', 'Morgan''s book is clearly written and goes well beyond\r\nmost of the basic Java books out there.');
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`isbn` char(13) NOT NULL,
`author` char(30) default NULL,
`title` char(60) default NULL,
`price` float(4,2) default NULL,
PRIMARY KEY (`isbn`)
) TYPE=InnoDB;
INSERT INTO `books` (`isbn`, `author`, `title`, `price`) VALUES
('0-672-31509-2', 'Pruitt, et al.', 'Teach Yourself GIMP in 24 Hours', 24.99),
('0-672-31697-8', 'Michael Morgan', 'Java 2 for Professional Developers', 34.99),
('0-672-31745-1', 'Thomas Down', 'Installing Debian GNU/Linux', 24.99),
('0-672-31769-9', 'Thomas Schenk', 'Caldera OpenLinux System Administration Unleashed', 49.99);
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
`customerid` int(10) unsigned NOT NULL auto_increment,
`name` char(30) NOT NULL,
`address` char(40) NOT NULL,
`city` char(20) NOT NULL,
PRIMARY KEY (`customerid`)
) TYPE=InnoDB AUTO_INCREMENT=4 ;
INSERT INTO `customers` (`customerid`, `name`, `address`, `city`) VALUES
(1, 'Julie Smith', '25 Oak Street', 'Airport West'),
(2, 'Alan Wong', '1/47 Haines Avenue', 'Box Hill'),
(3, 'Michelle Arthur', '357 North Road', 'Yarraville');
DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
`orderid` int(10) unsigned NOT NULL,
`isbn` char(13) NOT NULL,
`quantity` tinyint(3) unsigned default NULL,
PRIMARY KEY (`orderid`,`isbn`)
) TYPE=InnoDB;
INSERT INTO `order_items` (`orderid`, `isbn`, `quantity`) VALUES
(1, '0-672-31697-8', 2),
(2, '0-672-31769-9', 1),
(3, '0-672-31509-2', 1),
(3, '0-672-31769-9', 1),
(4, '0-672-31745-1', 3);
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`orderid` int(10) unsigned NOT NULL auto_increment,
`customerid` int(10) unsigned NOT NULL,
`amount` float(6,2) default NULL,
`date` date NOT NULL,
PRIMARY KEY (`orderid`)
) TYPE=InnoDB AUTO_INCREMENT=5 ;
INSERT INTO `orders` (`orderid`, `customerid`, `amount`, `date`) VALUES
(1, 3, 69.98, '2000-04-02'),
(2, 1, 49.99, '2000-04-15'),
(3, 2, 74.98, '2000-04-19'),
(4, 3, 24.99, '2000-05-01');
U knjizi stoji kao primer upit:
select customers.customerid, customers.name
from customers left join orders
using (customerid)
where orders.orderid is null;
Rezultat bi po knjizi trebao biti:
Code:
+------------+----------------+
| customerid | name |
+------------+----------------+
| 4 | Melissa Jones |
| 5 | Michael Archer |
+------------+----------------+
+------------+----------------+
| customerid | name |
+------------+----------------+
| 4 | Melissa Jones |
| 5 | Michael Archer |
+------------+----------------+
ali MySQL daje rezultat:
"MySQL returned an empty result set (i.e. zero rows)."
Da li je greska u knjizi ili je nesto lose kod moje verzije MySQL-a?
Sta je ispravno?