MySQL数据库(管理CD盘)
一、数据库设计
1、数据库E/R图(实体-关系图)应该是多对多,为了简化,一对多(把artist的key——Artist_ID放到CD中作为外键)
2、关系模式(Relation Schema)
Artist(artist_ID, names) 简称为D1表
CD(CD_ID, Title, cataloge, Notes, artist_id(外键),track_id(磁道), song_name)
其中:非主属性track_id, song_name,不能完全函数依赖于键CD_ID, 不符合第二范式,将其投影分解为:
CD(CD_ID,Title, cataloge, Notes, artist_id) 简称为D2表
Track(CD_ID,track_id, song_name) 简称为D3表
//甲骨文数据库可以分属性为多个属性,可以直接存图片或者MP3
二、创建数据库
1、进入MySQL数据库
%mysql –u root –p
Password:
2、生成数据库
mysql> create database ex1;
Query OK, 1 row affected (0.01 sec)
2、授权:
用户的授权信息保存于mysql数据库中的user表中
例:mysql>use mysql;
mysql>select user, host, passwordfrom user;
授权语法:
grant all on 数据库名.表名 to ‘用户名’@’客户登录计算机名(地址)’ identified by ‘密码’;
或
grant allprivileges on数据库名.表名 to 用户名@客户登录计算机名(地址) identified by ‘密码’;
或
grant allon 数据库名.表名 to 用户名@客户登录计算机名(地址) identified by ‘密码’;
例:mysql>grant all on *.* to test@localhost identified by ‘password’;
mysql>use mysql; 打开MySQL数据库
mysql>update user 更新user表
>set password=OLD_PASSWORD('password')
>where user='test';
mysql>flush privileges;
或
mysql>grant all on ex110301.* tow12@localhost identified by ‘password’;
mysql>use mysql; 打开MySQL数据库
mysql>flush privileges;
或
mysql>grant all on ex110301.* tow12@'%' identified by 'password';
mysql>use mysql; 打开MySQL数据库
mysql>exit
表示用户w12对cd110301具有所有权限,可从任何计算(‘%’)上连接该数据库
说明:远程连接需要使用TCP端口:3306
3、改用你授权的用户名/密码登录
%mysql –u 授权的用户名 -p
Password:输入授权密码
4、创建表
4.1 生成表cd
create table cd (
id INTEGER AUTO_INCREMENT NOT NULLPRIMARY KEY,
title VARCHAR(70) NOT NULL,
artist_id INTEGER NOT NULL,
catalogue VARCHAR(30) NOT NULL
);
4.2 生成表artist
create tableartist (
id INTEGER AUTO_INCREMENT NOT NULLPRIMARY KEY,
name VARCHAR(100) NOT NULL
);
4.3 生成表track
create table track(
cd_id INTEGER NOT NULL,
track_id INTEGER NOT NULL,
title VARCHAR(70),
PRIMARY KEY(cd_id, track_id)
);
三、插入数据
3.1 向表artist中插入数据
insert intoartist(id, name) values(1, 'Pink Floyd');
insert intoartist(id, name) values(2, 'Genesis');
insert intoartist(id, name) values(3, 'Einaudi');
insert intoartist(id, name) values(4, 'Melanie C');
3.2 向表cd中插入数据
insert into cd(id,title, artist_id, catalogue) values(1, 'Dark Side of the Moon', 1, 'B000024D4P');
insert into cd(id,title, artist_id, catalogue) values(2, 'Wish You Were Here', 1, 'B000024D4S');
insert into cd(id,title, artist_id, catalogue) values(3, 'A Trick of the Tail', 2, 'B000024EXM');
insert into cd(id,title, artist_id, catalogue) values(4, 'Selling England By the Pound', 2,'B000024E9M');
insert into cd(id,title, artist_id, catalogue) values(5, 'I Giorni', 3, 'B000071WEV');
insert into cd(id,title, artist_id, catalogue) values(6, 'Northern Star', 4, 'B00004YMST');
3.3 向表track中插入数据
insert intotrack(cd_id, track_id, title) values(1, 1, 'Speak to me');
insert intotrack(cd_id, track_id, title) values(1, 2, 'Breathe');
insert intotrack(cd_id, track_id, title) values(1, 3, 'On the run');
insert intotrack(cd_id, track_id, title) values(1, 4, 'Time');
insert intotrack(cd_id, track_id, title) values(1, 5, 'Great gig in the sky');
insert intotrack(cd_id, track_id, title) values(1, 6, 'Money');
insert intotrack(cd_id, track_id, title) values(1, 7, 'Us and them');
insert