--一、創(chuàng)建數(shù)據(jù)庫superKTV

create database superKTV;

go

use superKTV;

create table singer(

sing_ID int primary key identity(1,1),

sing_name nvarchar(50) not null,

sing_typeid nvarchar(20) check(sing_typeid='男歌手' or sing_typeid='女歌手') ,

sing_birthday date default(getdate()),

sing_address nvarchar(100),

sing_imgpath nvarchar(200),

sing_cunkuan decimal(18,2)

)

create table song(

song_ID int primary key identity(1,1),

song_name nvarchar(50) not null,

sing_ID int,

sing_Hit int default(0)

)

alter table song add foreign key(sing_id) references singer(sing_id)

--二、創(chuàng)建歌手表singer:

--歌手ID(SingerID)：主鍵ID，自動增長,種子和增量為1

--歌手名稱(SingerName):非空，nvarchar(50)

--歌手類型(SingerType):非空 nvarchar(10),只能為男歌手或女歌手

--歌手生日(Birthday):date類型

--歌手地區(qū)(Area):nvarchar(50)

--歌手圖片路徑(PhotoPath):nvarchar(200)

--存款(Cunkuan):decimal(18,2)

insert into singer values('周杰倫1','男歌手','1999-11-11','北京','C://img1.jpg',988832134.11)

insert into singer values('周杰倫2','男歌手','1999-11-11','北京','C://img1.jpg',988832134.11)

insert into singer values('周杰倫3','男歌手','1999-11-11','北京','C://img1.jpg',988832134.11)

insert into singer values('周筆暢','女歌手','1999-11-11','廣州','C://img1.jpg',988832134.11)

insert into singer values('鄧紫琪','女歌手','1999-11-11',null,'C://img1.jpg',988832134.11)

--三、創(chuàng)建歌曲表song

--歌曲id(SongID):主鍵ID,自動增長,種子和增量為1

--歌曲名稱(Title):非空 nvarchar(50)

--歌手編號(SingerID):int 外鍵,參照完整性歌手表

--點(diǎn)播次數(shù)(Hit):int 默認(rèn)值為0

insert into song(song_name,sing_ID,sing_Hit) values('稻香',1,2)

insert into song(song_name,sing_ID,sing_Hit) values('東風(fēng)破',1,2)

insert into song(song_name,sing_ID,sing_Hit) values('青花瓷',1,2)

insert into song(song_name,sing_ID,sing_Hit) values('泡沫',5,2)

insert into song(song_name,sing_ID,sing_Hit) values('最美的期待',4,2)

--drop table song

--drop table singer

----------------------------------------------

(以上內(nèi)容可以不寫，但是要會)

--練習(xí)(T4):

--1.使用sql語句給歌手表和歌曲表各添加5條數(shù)據(jù)

--2.刪除歌手表歌手ID為5的數(shù)據(jù)

delete singer where sing_ID=5

select * from singer

select * from song

--3.修改歌手表歌手ID為3的地區(qū)為湖南

update singer set sing_address='湖南' where sing_ID=3

--4.查詢歌手表所有的數(shù)據(jù)

select * from singer

--5.查詢歌手表所有男歌手的名稱和生日,名稱和生日需使用別名

select sing_name as '姓名',sing_birthday as '生日' from singer

where sing_typeid in('男歌手')

--6.查詢歌手表的所有信息,給其增加一個常量列國家為中國

select *,'中國'as '國家' from singer

--7.查詢歌手表的歌手ID大于3并且是男歌手的數(shù)據(jù)

select * from singer where sing_ID>=3 and sing_typeid ='男歌手'

--8.查詢歌手表前三條數(shù)據(jù),按照倒序排序

select top 3 * from singer order by sing_ID desc

子查詢

select top 3 * from singer where sing_id in(select top 3 sing_id from singer? )order by sing_ID desc

--9.查詢歌手地區(qū)為空的歌手信息

update singer set sing_address = null where sing_ID=2

select * from singer where sing_address is null

--10.查詢歌手表歌手ID為2到4之間的數(shù)據(jù)

select * from singer where sing_ID between 2 and 4

--11.查詢歌手表歌手ID為2和4的數(shù)據(jù)

select * from singer where sing_ID in(2,4)

--12.查詢歌手表ID為1和3的歌手名稱和歌手圖片路徑并使用別名給其起名稱,給其加一個常量列國家是中國

select sing_name as '歌手名稱',sing_imgpath as '圖片路徑',

'中國' as '國家' from singer where sing_ID in(1,3)

select sing_imgpath as '原內(nèi)容', lower(sing_imgpath) as '轉(zhuǎn)換小寫后',

upper(sing_imgpath) as '轉(zhuǎn)換為大寫后'from singer

--去除空格函數(shù)

select sing_name,LTRIM(sing_name) as '去除左邊空格' from singer

select sing_name,len(sing_name) as '長度' from singer

--查詢用戶名長度為4的用戶

select sing_name,len(sing_name) as '長度',REVERSE(sing_name) as '翻轉(zhuǎn)后' from singer

where len(sing_name)=4

--返回指定字符的開始位置

select sing_name,CHARINDEX('周',sing_name) from singer

--截取字符串

select sing_name,replace(sing_name,1,'') from singer

--字符串截取

select sing_name,SUBSTRING(sing_name,2,2) from singer

--刪除并插入

select sing_name,stuff(sing_name,1,5,'你好') from singer

--數(shù)學(xué)函數(shù)

select sing_cunkuan,CEILING(sing_cunkuan) as '向上取整' from singer

select sing_cunkuan,floor(sing_cunkuan) as '向下取整' from singer

select sing_cunkuan,round(sing_cunkuan,1) as '四舍五入' from singer

--冪值

select sing_ID,POWER(sing_ID,3) from singer

--絕對值

select sing_cunkuan,ABS(sing_cunkuan) from singer

--平方根

select sing_ID,sqrt(sing_ID) from singer

select * from singer

--取年，取月，取日

select sing_birthday,YEAR(sing_birthday) '取年',

MONTH(sing_birthday) '取月',day(sing_birthday),

dateadd(YY,1,sing_birthday) as '增加一個月'

?from singer?

?select *,'中國' as '國家' from singer where singname like '[a-i]'