– MySQL Administrator dump 1.4
– Server version 5.6.12-log
/*!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 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ */;
–
– Create schema prosjektmgp
CREATE DATABASE IF NOT EXISTS prosjektmgp;
USE prosjektmgp;
–
– Temporary table structure for view mgpview
DROP TABLE IF EXISTS mgpview;
DROP VIEW IF EXISTS mgpview;
CREATE TABLE mgpview (
melodiid int(11),
melodinavn varchar(255),
artist varchar(255),
bilde varchar(255),
poengsum decimal(32,0)
);
–
– Definition of table melodi
DROP TABLE IF EXISTS melodi;
CREATE TABLE melodi (
melodiid int(11) NOT NULL AUTO_INCREMENT,
melodinavn varchar(255) NOT NULL,
artist varchar(255) NOT NULL,
bilde varchar(255) DEFAULT NULL,
PRIMARY KEY (melodiid)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
–
– Dumping data for table melodi
/*!40000 ALTER TABLE melodi DISABLE KEYS /;
INSERT INTO melodi (melodiid,melodinavn,artist,bilde) VALUES
(1,‘Heal’,‘Mo’,‘artist1.jpg’),
(2,‘Needs’,‘Dina Misund’,‘artist2.jpg’),
(3,‘High Hopes’,‘Linnea Dale’,‘artist3.jpg’),
(4,‘Taste of You’,‘Knut Kippersund Nesdal’,‘artist4.jpg’),
(5,‘Sing’,‘Oda & Wulff’,‘artist5.jpg’),
(6,‘Hit Me Up’,‘Charlie’,‘artist6.jpg’),
(7,‘Ain t no Love In This City no More’,‘El Cuero’,‘artist7.jpg’),
(8,‘Sole Survivor’,‘Elisabeth Carew’,‘artist8.jpg’),
(9,‘Silent Storm’,‘Carl Espen’,‘artist9.jpg’),
(10,‘fsddsfdsf’,‘sdfsdfs’,‘artist1.jpg’),
(11,‘sdf’,‘szdfx’,‘rofl.jpg’),
(12,‘sdffsd’,‘sdfdsf’,‘arista.zip’);
/!40000 ALTER TABLE melodi ENABLE KEYS */;
–
– Definition of table omraade
DROP TABLE IF EXISTS omraade;
CREATE TABLE omraade (
omraadeid int(11) NOT NULL AUTO_INCREMENT,
omraade varchar(255) NOT NULL,
sted varchar(255) NOT NULL,
PRIMARY KEY (omraadeid)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
–
– Dumping data for table omraade
/*!40000 ALTER TABLE omraade DISABLE KEYS /;
INSERT INTO omraade (omraadeid,omraade,sted) VALUES
(1,‘Midt-Norge’,‘Ørlandet’),
(2,‘Vest-Norge’,‘Florø’),
(3,‘Sør-Norge’,‘Larvik’),
(4,‘Øst-Norge’,‘Kongsvinger’),
(5,‘Nord-Norge’,‘Bodø’);
/!40000 ALTER TABLE omraade ENABLE KEYS */;
–
– Definition of table poeng
DROP TABLE IF EXISTS poeng;
CREATE TABLE poeng (
melodiid int(11) NOT NULL,
omraadeid int(11) NOT NULL,
poeng int(11) NOT NULL,
PRIMARY KEY (melodiid,omraadeid),
KEY fk_melodi_has_omraade_omraade1 (omraadeid),
KEY fk_melodi_has_omraade_melodi (melodiid),
CONSTRAINT fk_melodi_has_omraade_melodi FOREIGN KEY (melodiid) REFERENCES melodi (melodiid) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_melodi_has_omraade_omraade1 FOREIGN KEY (omraadeid) REFERENCES omraade (omraadeid) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
–
– Dumping data for table poeng
/*!40000 ALTER TABLE poeng DISABLE KEYS /;
INSERT INTO poeng (melodiid,omraadeid,poeng) VALUES
(1,1,20),
(1,4,5),
(1,5,20),
(2,1,20),
(2,3,10),
(2,5,20),
(3,2,30),
(4,1,10),
(4,4,35),
(5,2,10),
(6,2,10),
(6,5,10),
(7,3,25),
(8,3,15),
(9,4,10);
/!40000 ALTER TABLE poeng ENABLE KEYS */;
–
– Definition of view mgpview
DROP TABLE IF EXISTS mgpview;
DROP VIEW IF EXISTS mgpview;
CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW mgpview AS select melodi.melodiid AS melodiid,melodi.melodinavn AS melodinavn,melodi.artist AS artist,melodi.bilde AS bilde,sum(poeng.poeng) AS poengsum from (poeng join melodi) where (melodi.melodiid = poeng.melodiid) group by melodi.melodiid;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!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 /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Its the bottom part, where the view is. I have very low knowledge of sql in general, and havn’t focused much on it. But as i said, its a school project, so i dont need any fancy stuff here right now. I only need to be able to show songs or “melodier” even if the SUM(poeng.poeng) is null. Because the view doesn’t show them as it stands now.
