SQL数据库不熟的知识代码1
--一、创建数据库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]'
-----------------------
--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之间的数据
select * from singer where sing_name like '__[a-i]%'
--5.查询歌手表所有歌手信息并添加一列将其里的字母转换成小写
SELECT sing_name as '元字符',lower(sing_name) as '转换后' from singer
--6.查询歌手表所有的歌手姓名并添加一列打印地区的长度
select *,len(sing_address) as '地区长度' from singer
--7.查询歌手表所有的歌手姓名并添加一列打印翻转歌手姓名
select sing_name as ' 翻转前',REVERSE(sing_name) as '翻转后' from singer
--8.查询歌手表所有的歌手姓名包含王的数据并添加一列打印王的位置
select sing_name as '原歌手', CHARINDEX('王',sing_name) as '位置'
from singer where sing_name like '%王%'
--9.查询歌手表所有的歌手姓名包含王的数据并添加一列将王全部改为李
select sing_name as '原歌手',replace(sing_name,'王','李')
from singer where sing_name like '%王%'
--10.查询歌手表所有的歌手的姓名包含王的数据并添加一列打印截取地区的前一个字符
select sing_address,SUBSTRING(sing_address,1,1) from singer where sing_name like '%王%'
--11.查询歌手表所有的歌手的姓名包含王的数据并添加一列打印将其地区全部改为湖南省
select sing_name,'湖南省' as '地区' 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为小数点
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.查询歌曲表点播次数为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
--聚合函数
sum():求和
avg():平均值
max():最大值
min():最小值
count():求条数
--求所有人的总存款
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数据一共有多少条
select count(sing_id) from singer where sing_ID>2
--查询歌手表中的男歌手人数和女歌手人数
select asss.sing_typeid,count(asss.sing_typeid) from singer asss
group by asss.sing_typeid
--查询每个地区有多少个人
select sing_address,COUNT(sing_address) from singer group by sing_address
--查询每个地区的总存款
select sing_address,avg(sing_cunkuan) from singer group by sing_address
查询每个地区总金额大于100的地区名称和总金额
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
--按照金额进行分类，查询每个金额的条数
select sing_cunkuan,count(sing_cunkuan) from singer
group by sing_cunkuan
--按照歌手id进行分类，统计每个类别的点播次数
select sing_id,sum(sing_hit) from song group by sing_id
select * from singer
--查询每个地区总金额大于100的地区名称和总金额
select sing_address as '地区',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
--按照金额进行分类，查询每个金额的条数
select sing_cunkuan,count(sing_cunkuan) from singer group by sing_cunkuan
--按照歌手id进行分类，统计每个类别的点播次数
--按照男歌手进行分组
--统计每个地区每个人金额大于35的总金额
select sing_address,sum(sing_cunkuan) from singer
where sing_cunkuan>35 group by sing_address having sum(sing_cunkuan)>100
having(先统计后判断) where (先判断后统计)
select sing_typeid,count(sing_typeid) from singer group by sing_typeid
--按照地区和姓名进行分组，统计每个地区的总人数
select sing_address,sing_name,count(sing_address) from singer group by sing_address,sing_name
