mysql数据库练习

CREATE DATABASE ktv
USE ktv
CREATE TABLE type(
TypeID int PRIMARY KEY,
TypeName nvarchar(20)
)

INSERT INTO type VALUES(1,‘热门流行’)
INSERT INTO type VALUES(2,‘经典老歌’)
INSERT INTO type VALUES(3,‘影视金曲’)
INSERT INTO type VALUES(4,‘动漫’)
INSERT INTO type VALUES(5,‘摇滚’)
INSERT INTO type VALUES(6,‘喊麦’)
INSERT INTO type VALUES(7,‘翻唱’)
INSERT INTO type VALUES(8,‘跨界’)

CREATE TABLE singer(
SingerID int PRIMARY KEY,
SingerName nvarchar(20),
SingerType nvarchar(10),
Birthday DATE,
Area nvarchar(50),
PhotoPath nvarchar(200)
)

INSERT singer VALUES(1,‘薛之谦’,‘男歌手’,‘1983-07-17’,‘大陆’,‘薛之谦.jpg’);
INSERT singer VALUES(2,‘何韵诗’,‘女歌手’,‘1977-05-10’,‘香港’,‘何韵诗.jpg’);
INSERT singer VALUES(3,‘SHE’,‘组合’,NULL,‘台湾’,‘SHE.jpg’);
INSERT singer VALUES(4,‘周杰伦’,‘男歌手’,‘1979-01-18’,‘台湾’,‘周杰伦.jpg’);
INSERT singer VALUES(5,‘Michael JackSon’,‘男歌手’,‘1958-08-29’,‘欧美’,‘Michael JackSon.jpg’);
INSERT singer VALUES(6,‘李孝利’,‘女歌手’,‘1979-05-10’,‘日韩’,‘李孝利.jpg’);
INSERT singer VALUES(7,‘张学友’,‘男歌手’,‘1961-07-10’,‘香港’,‘张学友.jpg’);
INSERT singer VALUES(8,‘蔡琴’,‘女歌手’,‘1957-12-12’,‘台湾’,‘蔡琴.jpg’);
INSERT singer VALUES(9,‘那英’,‘女歌手’,‘1967-11-27’,‘大陆’,‘那英.jpg’);
INSERT singer VALUES(10,‘张信哲’,‘男歌手’,‘1967-03-26’,‘台湾’,‘张信哲.jpg’);
INSERT singer VALUES(11,‘陈光栋’,‘男歌手’,‘1997-08-10’,‘大陆’,‘陈光栋.jpg’);
INSERT singer VALUES(12,‘王亚婷’,‘女歌手’,‘1997-05-23’,‘大陆’,‘王亚婷.jpg’);

CREATE table song(
SongID int PRIMARY KEY,
Title nvarchar(50),
PingTitle VARCHAR(20),
TypeID int,
SingerID int,
Hit INT,
Url nvarchar(200),
FOREIGN KEY(TypeID) REFERENCES type(TypeID),
FOREIGN KEY(SingerID) REFERENCES singer(SingerID)
)

INSERT song VALUES(1,‘菊花台’,‘jht’,2,4,0,‘菊花台.mp3’);
INSERT song VALUES(2,‘丑八怪’,‘cbg’,1,1,0,‘丑八怪.mp3’);
INSERT song VALUES(5,‘传说’,‘cs’,1,1,0,‘传说.mp3’);
INSERT song VALUES(6,‘伤城秘密’,‘scmm’,1,2,0,‘伤城秘密.mp3’);
INSERT song VALUES(7,‘super star’,‘super star’,1,3,0,‘super star.mp3’);
INSERT song VALUES(8,‘I’m So Blue’,‘I’m So Blue’,5,5,0,‘I’m So Blue.mp3’);
INSERT song VALUES(9,‘Bad Girls’,‘Bad Girls’,1,6,0,‘Bad Girls.mp3’);
INSERT song VALUES(10,‘人间道’,‘rjd’,3,7,0,‘人间道.mp3’);
INSERT song VALUES(11,‘明月几时有’,‘myjsy’,2,8,0,‘明月几时有.mp3’);
INSERT song VALUES(12,‘征服’,‘zf’,2,9,0,‘征服.mp3’);
INSERT song VALUES(13,‘爱就一个字’,‘ajygz’,4,10,0,‘爱就一个字.mp3’);

– A.显示中国大陆男歌手的基本信息，信息包括歌手编号、歌手姓名、歌手类型和歌手所在地区。要求采用别名显示。
SELECT SingerID ‘歌手编号’,SingerName ‘歌手名字’,SingerType ‘歌手类型’,Area ‘歌手所在地区’
FROM singer WHERE Area=‘大陆’ AND SingerType=‘男歌手’;

– B.显示出生年月晚于1980-1-1的台湾女歌手的全部信息。
SELECT *FROM singer WHERE Area=‘台湾’ AND SingerType=‘女歌手’ AND Birthday>‘1980-1-1’;

– C.显示欧美歌手和香港女歌手的信息。
SELECT *FROM singer WHERE Area=‘欧美’ OR (Area=‘香港’ AND SingerType=‘女歌手’);

– D.显示类型为“影视金曲”的歌曲全部信息。
SELECT s.SongID,s.Title,s.PingTitle,s.SingerID,s.Hit,s.Url,t.TypeName FROM type t,song s WHERE t.TypeID=s.TypeID and t.TypeName=‘影视金曲’;

– E.显示大陆女歌手演唱的歌曲全部信息。
SELECT s.SongID,s.Title,s.PingTitle,s.TypeID,i.SingerID,s.Hit,s.Url FROM singer i,song s
WHERE i.Area=‘大陆’ AND i.SingerType=‘女歌手’ AND i.SingerID=s.SingerID;

– F.显示大陆和香港男歌手演唱的类型为“热门流行”的歌曲全部信息。
SELECT s.SongID,s.Title,s.PingTitle,t.TypeName,i.SingerName,s.Hit,s.Url
FROM song s,singer i,type t
WHERE s.SingerID=i.SingerID AND t.TypeID=s.TypeID
AND((i.Area=‘大陆’ AND i.SingerType=‘男歌手’) OR (i.Area=‘香港’ AND i.SingerType=‘男歌手’))
AND t.TypeName=‘热门流行’;

– G.显示男歌手的全部信息，按歌手出生年月升序排列。
SELECT *FROM singer WHERE SingerType=‘男歌手’
ORDER BY Birthday;

– H.显示点歌数排行前五名的歌曲信息，信息包括：歌曲编号、曲目、歌曲类型编号、歌手编号和点歌数。要求使用别名显示列名。
SELECT SongID ‘歌曲编号’,Title ‘曲目’,TypeID ‘歌曲类型编号’,SingerID ‘歌手编号’,Hit ‘点歌数’
FROM song
ORDER BY Hit
LIMIT 5;

– I.显示歌曲的全部信息，按歌曲名长度升序排列，如果字数相等，则按点歌次数降序排列。
SELECT *FROM song
ORDER BY Title,Hit DESC;