--一、創(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 外鍵,參照完整性歌手表

--點播次數(shù)(Hit):int 默認值為0

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

insert into song(song_name,sing_ID,sing_Hit) values('東風破',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)容可以不寫，但是要會)

--練習(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 '轉換小寫后',

upper(sing_imgpath) as '轉換為大寫后'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 '翻轉后' 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ù)學函數(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]'

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

--1.查詢歌手表所有歌手所有姓周的歌手名稱和生日

select sing_name, sing_birthday from singer

where sing_name like '周%'

--2.查詢歌手表所有歌手名字中包含王的信息

select * from singer where sing_name like'%王%'

--3.查詢歌手表所有歌手名字的第二個字符為王的信息

select * from singer where sing_name like '_王%'

--4.查詢歌手表所有歌手名字中第三個字符為i之間的數(shù)據(jù)

select * from singer where sing_name like '__[a-i]%'

--5.查詢歌手表所有歌手信息并添加一列將其里的字母轉換成小寫

SELECT sing_name as '元字符',lower(sing_name) as '轉換后' from singer?

--6.查詢歌手表所有的歌手姓名并添加一列打印地區(qū)的長度

select *,len(sing_address) as '地區(qū)長度' from singer

--7.查詢歌手表所有的歌手姓名并添加一列打印翻轉歌手姓名

select sing_name as ' 翻轉前',REVERSE(sing_name) as '翻轉后' from singer

--8.查詢歌手表所有的歌手姓名包含王的數(shù)據(jù)并添加一列打印王的位置

select sing_name as '原歌手', CHARINDEX('王',sing_name) as '位置'

from singer where sing_name like '%王%'

--9.查詢歌手表所有的歌手姓名包含王的數(shù)據(jù)并添加一列將王全部改為李

select sing_name as '原歌手',replace(sing_name,'王','李')?

from singer where sing_name like '%王%'

--10.查詢歌手表所有的歌手的姓名包含王的數(shù)據(jù)并添加一列打印截取地區(qū)的前一個字符

select sing_address,SUBSTRING(sing_address,1,1) from singer where sing_name like '%王%'

--11.查詢歌手表所有的歌手的姓名包含王的數(shù)據(jù)并添加一列打印將其地區(qū)全部改為湖南省

select sing_name,'湖南省' as '地區(qū)' from singer

--12.查詢歌手表所有的歌手的存款并添加一列打印其絕對值

select sing_cunkuan,abs(sing_cunkuan) as '絕對值后' from singer

--13.查詢歌手表所有的歌手的存款并添加一列打印其平方值

select sing_cunkuan ,power(sing_cunkuan,2) from singer

--14.查詢歌手表中所有男歌手中存款最多的一位

select top 1 * from singer order by sing_cunkuan desc

--15.查詢歌手表中所有男歌手存款最少的一位

select top 1 * from singer order by sing_cunkuan

--16.查詢歌手表中歌手姓名和存款并精確到1為小數(shù)點

select *,ROUND(sing_cunkuan,1) from singer

--17.查詢歌手ID為3的歌手并設置其下次演出時間為1個月后

select *,DATEADD(MM,1,GETDATE()) as '下次演出時間' from singer where sing_ID=3

--18.查詢所有歌手年齡大于30的姓名

select sing_name as '姓名',DATEDIFF(YEAR,sing_birthday,GETDATE()) as '年齡'

from singer where DATEDIFF(YEAR,sing_birthday,GETDATE())>30

select * from singer

--19.查詢本機的名稱

select HOST_NAME()

--20.查詢歌曲表點播次數(shù)為5次到10次之間的歌曲信息和歌手

select * from singer

select * from song

select song_name,sing_Hit from song,singer where singer.sing_ID=song.sing_ID and

? sing_Hit between 5 and?

? select * from singer

? select sum(sing_id) from singer

求所有人的id和

select sum(sing_id) as '總金額' from singer?

where sing_ID<4

select top 3 sum(sing_id) from singer

select sum(sing_id) from singer where sing_id in (select top 3 sing_id from singer)

--最高金額

select max(sing_cunkuan) from singer

select min(sing_cunkuan) from singer

--求所有人的id的平均值? ?21

select avg(sing_cunkuan) from singer

? select count(*) from singer

? --聚合函數(shù)

? sum():求和

? avg():平均值

? max():最大值

? min():最小值

? count():求條數(shù)

? --求所有人的總存款

? select sum(sing_cunkuan) from singer

? --求id在2到5之間的人的平均存款

? select avg(sing_cunkuan) from singer where sing_ID between 2 and 5

? --求所有人中存款最高的人和最低的人

? select max(sing_cunkuan),min(sing_cunkuan) from singer

? --求singer表id大于2數(shù)據(jù)一共有多少條

? select count(sing_id) from singer where sing_ID>2

? --查詢歌手表中的男歌手人數(shù)和女歌手人數(shù)

? select asss.sing_typeid,count(asss.sing_typeid) from singer asss??

? group by asss.sing_typeid

? --查詢每個地區(qū)有多少個人

? select sing_address,COUNT(sing_address) from singer group by sing_address

? --查詢每個地區(qū)的總存款

? select sing_address,avg(sing_cunkuan) from singer group by sing_address

? 查詢每個地區(qū)總金額大于100的地區(qū)名稱和總金額

? select sing_address,sum(sing_cunkuan) from singer?

? group by sing_address having sum(sing_cunkuan)>=100

? --按照歌手類別進行分類，查詢每個歌手類別的總金額

? select sing_typeid,sum(sing_cunkuan) from singer

? group by sing_typeid

? --按照金額進行分類，查詢每個金額的條數(shù)

? select sing_cunkuan,count(sing_cunkuan) from singer

? group by sing_cunkuan

? --按照歌手id進行分類，統(tǒng)計每個類別的點播次數(shù)

? select sing_id,sum(sing_hit) from song group by sing_id

? select * from singer

? --查詢每個地區(qū)總金額大于100的地區(qū)名稱和總金額

? select sing_address as '地區(qū)',sum(sing_cunkuan) from singer

? group by sing_address having sum(sing_cunkuan) >100

? --按照歌手類別進行分類，查詢每個歌手類別的總金額

? select sing_typeid,sum(sing_cunkuan) from singer

? group by sing_typeid

? --按照金額進行分類，查詢每個金額的條數(shù)

? select sing_cunkuan,count(sing_cunkuan) from singer group by sing_cunkuan

? --按照歌手id進行分類，統(tǒng)計每個類別的點播次數(shù)

? --按照男歌手進行分組

? --統(tǒng)計每個地區(qū)每個人金額大于35的總金額

? select sing_address,sum(sing_cunkuan) from singer

? where sing_cunkuan>35 group by sing_address having sum(sing_cunkuan)>100

? having(先統(tǒng)計后判斷)? where (先判斷后統(tǒng)計)

? select sing_typeid,count(sing_typeid) from singer group by sing_typeid

? --按照地區(qū)和姓名進行分組，統(tǒng)計每個地區(qū)的總人數(shù)

? select sing_address,sing_name,count(sing_address) from singer group by sing_address,sing_name