当前位置:首页 > SQL数据库知识点 > 正文内容

SQL 脚本(自用)

poppy-jun10个月前 (05-11)SQL数据库知识点296

    考前复习

        SQL自用脚本,基本知识点都覆盖。

            脚本1:

                    1.单行添加: 

insert into [VIP Table](rank,Money) values('黄金',10000)/*将(values=值)给表(VIP Table),来添加表中数据*/

insert into [VIP Table](rank,Money) values('青铜',100),('钻石',100000),('黄金',100000)/*多行添加数据*//*主表里面一定要先插入数据,才能往外键表中插入数据*/

                    2.多行添加:

insert into [VIP Table](rank,Money)  /*将多行(值)添加到(VIP Table)表中*/

select '铂金','1000'Union

select '钻石','10000'Union

select '高级VIP','10000'

insert [Customer Table](Name,Sex,RankNum,PhoneNum)/*将多行(值)添加到(CUSTomer Table)表中*/

select '张宏','男',3,'123456' Union

select '李芹惟','女',2,'12345634546' Union

select '刘飒','男',5,'123451234' 


/*添加数据时,如果字段类型为字符类型或者是日期时间类型,则必须使用单引号,而非双引号*/

/*添加记录时,自增的列无形显式地添加*/

insert [Customer ID](ID,Rank)/*因为引用了外键,所以要先添加主表数据*/

select '6','部长' Union

select '5','副部门经理'


insert [Customer ID](ID,Rank)/*因为引用了外键,所以要先添加主表数据*/

select '7','副部长' Union

select '8','总部门经理'


update [Customer Table] set RankNum=1,Sex='男' where Number=1/*将表中数据更改*/


delete from [Customer ID] where ID='6'or ID='7'/*(要删除有外键约束的主表信息是,必须保证从表没有该记录)

删除表中数据,当有外键时有三种处理法*/

/*1,将主表中的数据删掉,(要在主表中改)2,在设置外键时将规范中将(删除规则改为级联)

3,在设置外键时将规范中将(删除规则改为NULL) */


select * from [Customer Table]/*(*)查找所有的数据,所有的查询语句不会影响到原来表中的数据*/

select Name from [Customer Table]/*查找name这一列*/

select Name , Sex from [Customer Table]/*查找多列*/

select Name as '姓名' , Sex as '性别' from [Customer Table]/*将Nmae和Sex在查询时显示为中文*/

select unitPrice*2 as '出售价格' from goods/*将出售价格乘2*/

select distinct RankNum from [Customer Table] where RankNum=2 or RankNum=3/*(distinct)将重复的去掉*/


insert into goods(goodsCode,goodsName,category,unitPrice) values('00006','薯条','食品','10.6')/*添加数据到goods表中*/

insert into goods(goodsCode,goodsName,category,unitPrice) values('00003','汉堡','食品','12.6')

insert into goods(goodsCode,goodsName,category,unitPrice) values('00004','牛肉干','食品','29.9')

insert into goods(goodsCode,goodsName,category,unitPrice) values('00005','镜夫人','食品','16.6')


select * from goods where unitPrice>=5/*查询goods表中销售价格大于等于5的*/


select * from [Customer Table] where Number>3 and Sex='男'/*找出员工表中序号大于3并且为男生的数据*/


select * from [Customer Table] where Number>10 or Sex='女'


select * from [Customer Table] where Number>6 or Sex='女' order by Number desc/*从大到小排序*/


select * from [Customer Table] where RankNum in(1,2) /*找出RankNum中是1和2的列,可跟子查询*/


select * from [Customer Table] where RankNum between 3 and 7/*找出RankNum中是3到7的列*/


/*所有的常量都可以用字段代替*/

select replace('asdfsdfgklpoiuy','s','c') as 替换字符/*将asdfsdfgklpoiuy中的s改为c*/


select  reverse(12345678) as 反序/*反序输出*/


select str(12345.789,8,2)/*第一个是数据,第二个输出多少位,第三个是保留小数位*/


select substring('一包火鸡面',3,3)/*将第一个数据从第三位开始取(3)位,*/


select charindex('鳗鱼丝','在中国的海边可以吃到便宜的鳗鱼丝')/*第一个参数是查找的数据,第二个是被查找的数据(返回的是下标)*/


select getdate()/*获取当前的时间*/


select datediff(day,'2001-12-18',getdate())/*2001-12-18与现在的时间隔了多久*/


select dateadd(month,4,getdate())/*相加,(month)加月份,加多少(4),加在哪里的时间(getdate)*/


update lemployee set birthday= '1992-2-18' where lemployeeid=1


select datediff(year,birthday,getdate()) from lemployee where name='刘芳'/*在只知道姓名的情况求年龄*/


/*数学函数*/


select abs(-1) as 绝对值/*绝对值*/


select round(1234.345,2) as 数字四舍五入/*将数字四舍五入,确保精度*/


select rand() as 随机数/*随机数,返回0~1的随机float数*/

select round (rand(),1)*10 as 返回0到10的随机数 /*返回0到10的随机数*/


select Pi() as 圆周率/*圆周率*/


select cos(35) as 余弦函数/*返回指定弧度的三角函数(余弦函数)*/


select cot(45) as 余切函数/*返回指定弧度的三角函数(余切函数)*/


select power(3,9) as 幂函数/*将3进行9次幂*/


select sign(-10) as 返回符号/*将指定表达式返回符号,返回零和正号,负号*/


select sin(45) as 正弦函数/*返回指定弧度的三角函数(正弦函数)*/


select square(6) as 平方/*返回指定数的平方*/


select sqrt(9) as 平方根/*返回指定值的平方根*/


select tan(90) as 正切函数/*返回指定弧度的三角函数(正切函数)*/


select cast('渴望玩耍' as nvarchar(2)) as 出现nvarchar中的字符数/*将前面的渴望玩耍,取两个字符出来,(强制转换)*/


select PhoneNum+1 from [Customer Table] /*将电话号码加3*/


select convert(nvarchar(10),getdate(),120) as 年月份/*将得到的现在时间改输出格式*/

/*聚合函数*/

select sum(unitPrice) as 价格的和 from goods /*求价格的和*/


select max(unitPrice) as 最大的价格 from goods/*求价格的最大值*/


select min(unitPrice) as 最小的价格 from goods /*求价格的最小值*/


select count(unitPrice) as 记录了多少行 from goods/*求所选择列的行数*/


select avg(unitPrice) as 平均的价格 from goods/*求平均值*/


select count(distinct name) as 记录了多少行 from lemployee /*求所选择列的行数*/


        图例:

脚本1.png

            脚本2:

/*多行添加*/

insert into lemployee (name,gender,birthday,rankid,salary,address)

select '李军波','男','1987-05-09',204,'9000','北京' Union

select '郭景尹','男','1981-11-19',204,'12000','北京' Union

select '罗瑞政','男','1983-05-20',103,'14500','北京' Union

select '宋凯南','男','1982-09-07',106,'18500','衡阳' Union

select '姚广安','男','1983-03-29',202,'14500','北京' Union

select '张立军','男','1986-07-12',205,'12500','北京' Union

select '徐宏涛','男','1969-04-12',204,'12500','北京'Union

select '刘飒','女','1982-05-12',205,'125300','长沙' Union

select '张军','女','1987-07-18',204,'12000','北京' Union

select '刘芳','女','1982-11-12',204,'12500','长沙' Union

select '刘泽','女','1985-01-2',202,'12300','衡阳' Union

select '张若霖','女','1985-06-18',202,'12300','四川' Union 

select '张起芸','女','1986-08-18',104,'14400','上海'


insert into lemployee (name,gender,birthday,rankid,salary,address)

select '张若霖','女','1989-06-18',205,'1500','长沙'


insert into [lemployee rank] (rankid,rankname) values('204','总经理'),('205','副总经理'),('103','人事部门部长')

,('202','总经理助理'),('104','财务部门部长'),('106','秘书部部长')


select top 5 * from lemployee where gender='男' and rankid=204 order by birthday asc/*decs是降序,asc是升序*/


select top 50 percent * from lemployee where gender='女' and rankid=202 order by birthday asc/*decs是降序,asc是升序*/


select * from lemployee where  name like '%军%'/*有军字的姓名*/

select * from lemployee where name like '张%'/*张在前面的姓名*/

select * from lemployee where name like '刘%'/*刘在前面的*/

select * from lemployee where name like '张%' or  name like'罗%'/*姓张的和姓罗的(罗和张字在前面的)*/


select * from lemployee where name like '张_' or  name like'罗__'/*_是占一个位置的,有多少个_就是多少字*/


select * from lemployee where name like '%[军楠霖]%'/*[]里面是一个字符集合*/


select top 1 * from lemployee where salary>10000 order by salary desc


select * from lemployee where salary>13000 and address='深圳'


        图例:

脚本2.png

            脚本3:


/*周杰伦唱了几首流行歌*/

select count(*) from [Song-Table] where SingerRank=(select SongNum from Singer where [Song-Name]='周杰伦')

and SongRank=(select Num from [Song-Type] where Name='热门流行')


/*将菊花台的点击量改为49*/

update [Song-Table] set Number='49' where SongNum='菊花台'

/*把beyond的经典老歌 海阔天空 */

insert into Singer([Song-Name],[Song-Type],Birthday,Addrss,[Singer-Photos])

select 'beyond','男歌手','1989-07-18','中国大陆','beyond.jpg'


insert into [Song-Table](SongNum,SongChina,SongRank,SingerRank,URL)

select '海阔天空','hktk','7','11','海阔天空.mp3'


/*将李孝利删除*/

delete from Singer where [Song-Name]='李孝利'

delete from [Song-Table] where SingerRank=(select SongNum from Singer where [Song-Name]='李孝利')/*先执行这个*/

/*将Song-Table 里面歌手外*/


/*有几个歌手唱了热门流行*/

select count (distinct SingerRank) 有这么多个 from [Song-Table] where SongRank=(select Num from [Song-Type] where Name='热门流行')


/*将数据表中按照某一个字段进行分组:将这个字段里相同的值作为一个小部分或者一个组*/

select name , gender , count(*) from  lemployee group by name , gender


/*统计非"武汉”地区所属员工的数量和员工月薪的平均值、最大值和最小值,并按照员工数升序显示查询结果*/

/*with rollup 是进行一个总统计*/

select address as 地点, avg(salary)as 平均值, max(salary)as 最大值,min(salary) as 最小值,count(address) as is_no_WuHan 

from lemployee where address!='武汉' group by address with rollup


/*经典老歌和热门流行的歌曲各有几首*/

select SongRank ,count (SongRank)as 歌曲数量 from [Song-Table]  where SongRank=(select Num from [Song-Type] where Name='经典老歌') 

or SongRank=(select Num from [Song-Type] where Name='热门流行') group by SongRank


/*各个地区的歌手年龄大于30*/ 

select Addrss '地区', count([Song-Num]) from Singer  where datediff(year,Birthday,getdate())>30 group by Addrss


--所有姓张的的歌手分别唱了几种类型的歌

select SingerRank , SongRank, count(*) '几种类型' from [Song-Table]  where 

SingerRank in(select [Song-Num] from Singer where [Song-Name] like '张%') group by SingerRank, SongRank


/*找张学友有几首歌*/

select count(*) from [Song-Table] where SingerRank in(select [Song-Num] from Singer where [Song-Name]='张学友')

select * from [Song-Table]


/*每个地区有多少歌手*/

select Addrss, count([Song-Name])'歌手数量' from Singer  group by Addrss


/*找出各个地区年龄最大的歌手*/

select Addrss,max(datediff(year,Birthday,getdate())) from Singer  group by Addrss

select * from Singer


--将所有的笛卡尔积查询出来(其中有不用的数据,使用where限制条件)

select lemployee.name,lemployee.gender,

lemployee.rankid, lemployee.address,[lemployee rank].rankid,

[lemployee rank].rankname from lemployee  cross join [lemployee rank] where

lemployee.rankid=[lemployee rank].rankid


--分组计数加联合查询

select a.rankid,count(a.rankid) '计数',b.rankname 

from  lemployee a join [lemployee rank] b on a.rankid=b.rankid 

group by a.rankid,b.rankname,a.rankid


--分组计数加联合查询

select b.rankname,count(a.rankid) '该职位有这么多人'

from lemployee a join [lemployee rank] b on a.rankid=b.rankid

group by b.rankname


--左右查询

--左连接查询: 将左边表格的数据都显示出来,如果右边表格有满足连接的记录就显示,如果没有满足连接的记录就显示空值

select * from lemployee a left join [lemployee rank] b  on a.rankid=b.rankid

--右连接查询: 将右边表格的数据都显示出来,如果左边表格有满足连接的记录就显示,如果没有满足连接的记录就显示空值

select * from lemployee a right join [lemployee rank] b  on a.rankid=b.rankid


        图例:

脚本3.png


版权声明:本文为原创文章,版权归 Jun所有!

转载请注明出处:https://cranek.cn/?id=8

分享给朋友:

相关文章

SQL 课程复习(四)

SQL 课程复习(四)

前几天生病,没写文章。今天病好点了,我又来写文章了。第二章       管理数据表    ...

SQL 课程复习(七)

SQL 课程复习(七)

第二章        管理数据表       &n...

SQL课程复习(十一)

SQL课程复习(十一)

第五章        函数的使用       &n...

SQL课程复习(九)

SQL课程复习(九)

第三章    数据表基本操作        删除语句:delete from&n...

SQL 课程复习(二)

SQL 课程复习(二)

第一章    数据库基础        SQL数据库服务链接方法: &...

SQL课程复习(十)

SQL课程复习(十)

第四章        SELECT定制查询(select定制查询,准确的在数据库中查找数据)  &n...