SQL作业的题与代码4：函数
--一、创建数据库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)
--二、创建歌手表singer:
--歌手ID(SingerID)：主键ID，自动增长,种子和增量为1
--歌手名称(SingerName):非空，nvarchar(50)
--歌手类型(SingerType):非空 nvarchar(10),只能为男歌手或女歌手
--歌手生日(Birthday):date类型
--歌手地区(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)
--三、创建歌曲表song
--歌曲id(SongID):主键ID,自动增长,种子和增量为1
--歌曲名称(Title):非空 nvarchar(50)
--歌手编号(SingerID):int 外键,参照完整性歌手表
--点播次数(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
----------------------------------------------
(以上内容可以不写，但是要会)
--练习(T4):
--1.使用sql语句给歌手表和歌曲表各添加5条数据
--2.删除歌手表歌手ID为5的数据
delete singer where sing_ID=5
select * from singer
select * from song
--3.修改歌手表歌手ID为3的地区为湖南
update singer set sing_address='湖南' where sing_ID=3
--4.查询歌手表所有的数据
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并且是男歌手的数据
select * from singer where sing_ID>=3 and sing_typeid ='男歌手'
--8.查询歌手表前三条数据,按照倒序排序
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.查询歌手地区为空的歌手信息
update singer set sing_address = null where sing_ID=2
select * from singer where sing_address is null
--10.查询歌手表歌手ID为2到4之间的数据
select * from singer where sing_ID between 2 and 4
--11.查询歌手表歌手ID为2和4的数据
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 '原内容', lower(sing_imgpath) as '转换小写后',
upper(sing_imgpath) as '转换为大写后'from singer
--去除空格函数
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
--数学函数
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]'
