mysqlデータ登録テンプレ - ノブログ製作委員会

sql文以外は一行ずついれたほうがいいかも
エラーとかがあったらサブリーダーまで


cd c:\mysql\bin
mysql -u root
select host,user,password from mysql.user;   ←やってもやらんでも、どっちでも可
set password for root@localhost=PASSWORD('root');
set password for root@build=PASSWORD('root');

exit;

mysql -u root -p 次回からrootで入るにはここと次の行の２つを入力する必要がある
root



create database noblog;
use noblog;

CREATE TABLE user
(
userID VARCHAR(20) primary key,
passwd VARCHAR(16) not null,
mail VARCHAR(100)not null,
himitu int(1),
answer varchar(30),
ninsyouKey VARCHAR(8),
kariDay     date
);


create table profile
(
        userID varchar(20),
        name varchar(40),
        year varchar(4),
        month varchar(2),
        day varchar(2),
        job varchar(2),
        sex int(1),
        hobby varchar(200),
        hitokoto varchar(200),
        pic varchar(2),
        nameSecret int(1),
        barthSecret int(1),
        primary key(userID)
);


create table job
(
jobNo integer(2) auto_increment,
jobName  varchar(20),
        primary key (jobNo)
);

create table categori
(
categoriNo   int(5) primary key,
categoriName varchar(40)
);

create table theme
(
        userID varchar(20),
        themeNo int(2),  
        themeName varchar(40)
);

create table kiji
(
        userID varchar(20),
        kijiNo int(5),
        themeNo int(2),
        kijiTitle varchar(100),
        text text,
        commentFlg int(1),
        trackflg int(1),
        writeDay date,
        writeTime time
);

create table comment
(
userid varchar(20),
kijiNo int(5),
commentNo int(3),
commWriter varchar(100),
        commDate datetime
);



create table blog
(
        userID varchar(20) primary key,
        bTitle varchar(100) not null,
        bSetumei varchar(200),
        cssNo int(3),
        categoriNo int(5),
        categoriRunk int(6),
        allRank int(7),
        commentFlg int(1),
        trackFlg int(1)
);

create table trackback
(
        kijiNo int(5),
        trackNo int(3),
        trackTitle varchar(100),
        bTitle varchar(100),
        bUul varchar(100),
        trackText varchar(200),
        trackDate datetime
);

create table ranking
(
        userID varchar(20) primary key,
        one int(5),
        two int(5),
        three int(5),
        four int(5),
        five int(5),
        six int(5),
        seven int(5),
        weekTotal int(7),
        allTotal int(8)
);

create table design
(
        desNo int(2) primary key auto_increment,
        css text
);

create table picture
(
        userID varchar(20) primary key,
        picNo int(2),
        pic longblob,
        thumbnail varchar(50)
);

INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 1001 , 'ダイエット');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 1002, '健康法');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 1003, '美容・コスメ');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 1004, '出産・育児');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 1005, '仕事');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 1006, '恋愛');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 1007, '就職活動');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 1008, 'その他');

INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 2001, '社会');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 2002, '政治');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 2003, '芸能');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 2004, 'その他');

INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 3001, '野球');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 3002, 'サッカー');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 3003, 'その他');

INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 4001, 'パチンコ・スロット');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 4002, '競馬');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 4003, 'その他');

INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5001, '本・雑誌');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5002, '音楽');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5003, '料理');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5004, 'グルメ');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5005, '旅行');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5006, '小説・詩');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5007, '車・バイク') ;
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5008, 'ショッピング');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5009, 'ゲーム');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5010, 'ペット');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5011, 'コレクション');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5012, 'ファッション');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5013, 'おもちゃ');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 5014, 'その他');

INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 6001, '俳優・タレント・アイドル');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 6002, '漫画・アニメ');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 6003, 'お笑い');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 6004, 'バラエティ');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 6005, '映画');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 6006, 'ドラマ');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 6007, 'テレビ');
INSERT INTO categori ( categoriNo , categoriName )
VALUES ( 6008, 'その他');