【SQL开源代码栏目提醒】:网学会员在SQL开源代码频道为大家收集整理了create_database.sql提供大家参考,希望对大家有所帮助!
DROP DATABASE IF EXISTS content;
CREATE DATABASE content;
USE content;
DROP table IF EXISTS writers;
CREATE table writers (
username varchar(16) PRIMARY KEY,
password char(40) NOT NULL,
full_name text
);
DROP table IF EXISTS stories;
CREATE table stories (
id int PRIMARY KEY auto_increment,
writer varchar(16) NOT NULL, # FOREIGN KEY writers.username
page varchar(16) NOT NULL, # FOREIGN KEY pages.code
headline text,
story_text text,
picture text,
created int,
modified int,
published int
);
DROP table IF EXISTS pages;
CREATE table pages (
code varchar(16) PRIMARY KEY,
description text
);
DROP table IF EXISTS writer_permissions;
CREATE table writer_permissions (
writer varchar(16) NOT NULL, # FOREIGN KEY writers.username
page varchar(16) NOT NULL # FOREIGN KEY pages.code
);
DROP table IF EXISTS keywords;
CREATE table keywords (
story int NOT NULL, # FOREIGN KEY stories.id
keyword varchar(32) NOT NULL,
weight int NOT NULL
);
GRANT select, insert, update, delete
ON content.*
TO content@localhost identified by 'password';
insert into writers (username, password, full_name)
values ('bob', SHA1('pass
word'), 'Robert Bobbins');
insert into writers (username, password, full_name)
values ('bill', SHA1('password'), 'William Billings');
insert into pages (code, description)
values ('news', 'The Top News Stories From Around the World');
insert into pages (code, description)
values ('sport', 'Sports Latest - All The Winners and Losers');
insert into pages (code, description)
values ('weather', 'Up To The Minute Weather Reports and Forecasts');
insert into writer_permissions (writer, page) values ('bob', 'news');
insert into writer_per
missions (writer, page) values ('bob', 'sport');
insert into writer_permissions (writer, page) values ('bill', 'news');
insert into writer_permissions (writer, page) values ('bill', 'weather');
insert into stories
(id, writer, page, headline, created, modified, published,
story_text, picture)
values (1, 'bill', 'news', 'Man gives birth', 976573221, 976580154, 976570230,
'A man today gave birth in a hospital on Staten Island, NY. The baby boy weighed in at just over eight pounds and is doing well. The parents were naturally overjoyed at the birth of their first son, and have have said they hope to have a large family. <br /><br />Father Ted, 34, conceived using a new method known as paternatility whereby the fertilised embryo is transferred to the father\'s body at an early stage. It is believed that this method reduces many of the risks of childbirth.', 'images/1.jpg');
insert into stories
(id, writer, page, headline, created, modified, published,
story_text, picture)
values (2, 'bill', 'news', 'Fire!', 976562355, 976572203, 976570230,
'Breaking news: Reports are coming in of a fire in a barn somewhere in Arizona. Our sources say the barn is very likely to burn to the ground and will not be economically viable to repair.<br /><br />A bystander is reputed to have said "There was quite a lot of smoke"', 'images/2.jpg');
insert into stories
(id, writer, page, headline, created, modified, published,
story_text, picture)
values (3, 'bill', 'news', 'SFON Launch Party report', 976542355, 976542503, 976555650,
'Yesterday has already gone down in history as the day the best news site on the
web first hit the Internet. Just to prove the point, there was a star-studded party last night at a secret location in Seattle.<br /><br />Joining our team for a boogie were several A-list celebs who wish to remain anonymous.', 'images/3.jpg');
insert into stories
(id, writer, page, headline, created, modified, published,
story_text, picture)
values (4, 'bob', 'sport', 'World Crossword Championship Preview', 976531355, 976532503, 976533320,
'It\'s now just three days to the start of the prestigious annual World Crossword Championship to be held this year for the first time live on the Internet. The new media format will allow many more competitors than ever before to take part from the comfort of their own home, or from one of 126 regional centers.<br /><br />Last year\' champion is not keen on the new format. She said "Crosswords should be done on paper, not online".', 'images/4.jpg');
insert into stories
(id, writer, page, headline, created, modified, published,
story_text, picture)
values (5, 'bob', 'sport', 'Basketball is bad for you', 976542355, 976542503, 976555650,
'Scientists believe that basketball can be bad for you. Research has suggested that both watching