shufen decimal(18create database lianxi0831

create database lian0831
go
use lian0831
go
create table stu
(
code int not null,
cid varchar(20) not null,
name varchar(10),

create database lianxi0831
go
use lianxi0831
go
create table saaa(
code int,
name varchar(10),
sex varchar(10),
banji varchar(10),
cid varchar(20),
age int,
yufen decimal(18,2),
shufen decimal(18,2),
yinfen decimal(18,2)
)
go
insert into saaa values
(1001,’张三’,’男’,’一班’,’370301198502051546′,32,88,89,90)
insert into saaa values
(1002,’李四’,’女’,’一班’,’370301199005162586′,22,66,68,66)
insert into saaa values
(1003,’张强’,’男’,’一班’,’370301199205265987′,32,88,89,90)
insert into saaa values
(1004,’王八’,’男’,’一班’,’370301199602196654′,18,55,56,57)
insert into saaa values
(1005,’李赫宰’,’男’,’二班’,’370301198703156978′,27,44,45,46)
insert into saaa values
(1006,’牛爽’,’女’,’二班’,’370301199502246635′,22,81,69,50)
insert into saaa values
(1007,’赵四’,’男’,’二班’,’370301199204197895′,23,44,55,66)
insert into saaa values
(1008,’田妞’,’女’,’二班’,’370301199204153254′,24,84,55,60)
insert into saaa values
(1009,’边爽’,’女’,’三班’,’370301199508249875′,25,66,79,40)
insert into saaa values
(1010,’林恩’,’男’,’三班’,’370301199504156354′,25,55,56,45)
insert into saaa values
(1011,’东东’,’男’,’三班’,’370301199604192596′,26,66,77,80)
insert into saaa values
(1012,’刘了’,’女’,’三班’,’370301995081936978′,27,66,87,80)
go
–查看有消息
select *from saaa
–查看姓名也李四的语文成绩
select yufen from saaa where name=’李四’
–查看语文成绩80-100间的全名,性别,年龄
select name,sex,age from saaa where yufen between 80 and 100
–查看姓名谁李四年龄于24-26内的具有消息
select *from saaa where name=’李四’or age in (24,25,26)
–查看姓名中发生四的享有消息
select *from saaa where name like’%四%’
–查看性李的富有信息
select *from saaa where name like’李%’
–按年龄降序排列并查阅前三员之音讯
select top 3 * from saaa order by age desc
–按照数学分数降序排列查看前三号之人名数学成绩
select top 3 name as 姓名 ,shufen as 数学成绩 from saaa order by shufen
desc
–同次的语文平均分
select AVG(yufen) from saaa where banji=’一班’
–二班的数学平均分
select AVG(shufen) from saaa where banji=’二班’
–三班的英语平均分
select AVG(yinfen) from saaa where banji=’三班’
–查看个数
select COUNT(*)from saaa
–查看性王的个数
select COUNT(*)from saaa where name like’王%’
–去重之后查看个数
select COUNT(distinct banji) from saaa
–同趟的语文最高分
select MAX(yufen) from saaa where banji=’一班’
–按照班级分组查看语文最低分
select banji,min(yufen)from saaa group by banji
— 以年分组查看年龄大于24底岁
select age from saaa where age>24 group by age
–按照班级分组 查看语文分数大于70划分而人数超过1底班级
select banji from saaa where yufen>70 group by banji having
COUNT(*)>1
–按照班级分组查看语文分数大于60分的班级人数并按降序排列
select banji,COUNT(*)from saaa where yufen>60 group by banji order
by COUNT(*)desc
–按班级分组求每个班的语文最高分语文最低分数学高分数学最低分英语最高分英语最低分
select banji,MAX(yufen),MIN(yufen),MAX(shufen),MIN(shufen),MAX(yinfen
),MIN(yinfen) from saaa group by banji
–取上限
select CEILING(yufen)from saaa
–取下限
select FLOOR(shufen) from saaa
–求某数的几乎不良方
select POWER(3,4)
–四放弃五入,逗号表示有点数点保留几各
select ROUND(3.1415926,4)
–开平方根
select SQRT(4)
–返回字符串首字母的ascii码
select ASCII(name)from saaa
–将ascii码转成为对应的字符
select CHAR(97)
select CHAR(age)from saaa
–在表达式中搜素另一个表达式,返回首字母的目录,索引从1初始,返回值为0象征没找到
select CHARINDEX(‘aas’,’sssddfaasddf’)
–字符串拼接
select ‘a’+’ll’+’se’
–字符冲从漏洞百出为右侧截取多少个
select LEFT (name,1)from saaa
–字符冲从右边为左截取多少个
select RIGHT(‘qwerts’,3)
–字符串长度,前面空格算,后面空格不算是
select LEN(‘ aasd ‘)
–大写转小写 小写转大写
select LOWER(‘ASDddd’)
select UPPER(‘aaaSDS’)
–替换显示并无是改
select REPLACE(sex,’女’,’姑娘’)from saaa
–复制可以确定次数
select REPLICATE(‘asd’,2)
–反转字符串
select REVERSE(name)from saaa
–从指定索引截取指定长度
select SUBSTRING(name,1,1)from saaa
–获得当前时间
select GETDATE()
–精确的落时间
select SYSDATETIME()
select @@DATEFIRST
–增加时长的起,增加多少,在大参数上长
select DATEADD(YEAR,2,’2016-9-24′)
select DATEDIFF(YEAR,’2014-1-25′,’2018-5-25′)
select CAST(123 as varchar(10))
select CONVERT(varchar(10),123)
select
name,(SUBSTRING(cid,7,4)+’年’+SUBSTRING(cid,11,2)+’月’+SUBSTRING(cid,13,2)+’日’)
as 生日 from saaa

banji varchar(10),
sex varchar(10),
age int not null,
yufen decimal(18,2),
shufen decimal(18,2),
yingfen decimal(18,2),
)
insert into stu values(1001,’370302199101011111′,
‘张三’,’一班’,’男’,25,70,65,76)
insert into stu values(1002,’370302199101011112′,
‘李四’,’一班’,’女’,25,76,85,66)
insert into stu values(1003,’370302199201011111′,
‘李琦’,’一班’,’男’,24,30,95,46)
insert into stu values(1004,’370302199301011112′,
‘齐娜’,’一班’,’女’,23,60,65,68)
insert into stu values(1005,’370302199201011112′,
‘王琦’,’二班’,’女’,24,74,68,76)
insert into stu values(1006,’370302199302011111′,
‘唐骑’,’二班’,’男’,23,76,67,75)
insert into stu values(1007,’370302199103011112′,
‘崔倩’,’二班’,’女’,25,70,35,86)
insert into stu values(1008,’370302199404011111′,
‘牛超’,’二班’,’男’,22,72,64,78)
insert into stu values(1009,’370302199107011112′,
‘王雪’,’三班’,’女’,25,73,64,77)
insert into stu values(1010,’370302199207011111′,
‘张瑞’,’三班’,’男’,24,77,65,77)
insert into stu values(1011,’370302199203011111′,
‘许可’,’三班’,’男’,24,74,63,72)
insert into stu values(1012,’370302199004011111′,
‘余克’,’三班’,’男’,26,50,45,86)
sp_renamedb lian0831,xuesheng
sp_renamedb xuesheng,lian0831
select* from stu
select yufen from stu
–将yufen作为语文成绩显示出来
select yufen as 语文 from stu
–查询年龄为24之丁之年,语文分数,性别
select yufen,name,sex from stu where age=24
–查询yufen在60到80以内的总人口的姓名
select name from stu where yufen between 75and 80
–查询时自动去又,并无删,对一列去还显示
select distinct yufen from stu where age=24
–查询同一班年为24的女性校友的语文成绩
select yufen as 语文,name as 姓名 from stu where age=23 and sex=’女’ and
banji=’一班’
–查询同一次的齿也23还是24的学员姓名
select age as 年龄,name as ‘姓名’ from stu where age=23 or age=24 and
banji =’一班’
–查询年龄以22暨23底具有数据
select*from stu where age in(22,23)
–查询学号不是1003及1011里面的享有数据
select * from stu where code not in(1003,1011)
–不在1003和1011之间的
select * from stu where code not between 1003 and 1011

–模糊查询

–百分号可以表示任意字符,长度不限
select *from stu where name like ‘%李%’
–下划线表示任意搜索一个字符
select *from stu where name like’许_’
–引号里面括号外加下划线,意思呢擅自一个括号里之价
select *from stu where name like’_[许可,徐克]’

–升序降序
–按照语文分数升序
select *from stu order by yufen asc
–按照数私分降序
select shufen as 数学 from stu order by shufen desc
–按照数区划降序前三号称的保有数据
select top 3* from stu order by shufen desc
–年龄也25之数分降序排列
select *from stu where age=25 order by shufen desc
–年龄也25的数分降序排列,找有第一独
select top 1*from stu where age=25 order by shufen desc

–数学函数
–绝对值
select ABS(-5)–得出结果
print abs(-7)–得出消息
select *from stu where ABS(shufen)>=85
–取上限 ceiling
select yufen from stu where CEILING(yufen)>75
–取下限 floor 取下限
select FLOOR(1.7)
–n次方 power
select POWER(2,4)
select ‘这是’+’年龄的平方:’,POWER(age,2) from stu
–round 四放弃五副,逗号后边表示第几各项
select ROUND(222.336,1)
–平方根
select SQRT(4)
–平方
select SQUARE(4)

 

–字符串函数
–ASCII 返回字符串中首字母之ASCII编码
select ASCII(‘name’)
select ASCII (name) from stu
select name,ascii(name) from stu where ASCII(name)>200
–char 将ASCII转化为对承诺字符
select CHAR(77)
select CHAR(yufen) from stu
–charindex 在一个表达式中找找另一个表达式,并回到起始位置
–在逗号的尾寻找逗号前面的字符段,返回首字母的目录,索引从1初步
select CHARINDEX(‘ef’,’abcdefghi’)
select CHARINDEX(‘1992’,cid) from stu–返回值为0说明莫找到。
–字符串的拼接
select ‘a’+’b’+’c’
–difference 返回相似度,用0-4表示相似度
select DIFFERENCE(‘a’,’abc’)
–left 逗号前是字符串,后边是从漏洞百出望右侧截取多少
select left(‘abcdef’,3)
–right 于右侧为左截取
select right(‘123456’,3)
select LEFT(name,1)from stu where age=23
–len 查询字符串长度,前面的空格算,后面的空格不到底
select LEN (‘ abc ‘)
select LEN(name) from stu where yufen>=75
–lower 将大写转化为题写
select lower(‘ABCdef’)
–小写转大写
select UPPER(‘abcDEF’)
–删除左边空格,相当于.net里的trimstart
select LTRIM(‘ a’)
–删除右边的空格
select RTRIM(‘ 123 ‘)
–patindex相当给charindex,返回寻找的字符段的首字母所在的目录
select
PATINDEX(‘%cd%’,’abcdefg’)–查找的字符串前后要加%,与charindex不均等。
–replace 替换显示,并无是反
–率先独凡是摸索的位置字符串,第二个是如果挑变的字符段,第三单凡是要是反化的字符段。
select yufen,name,replace(sex,’女’,’lady’) from stu where age=25 order
by yufen asc
–replicate 复制,可规定次数
select REPLICATE(‘a’,2)–显示的凡给复制的有限周
–reverse翻转字符串
select REVERSE(‘abcd ‘)
select REVERSE(name) from stu where age=24
–打印空格
select ‘a’+SPACE(15)+’b’
–str 将一个小数转化为字符串,1参数是原本数,
–2参数是字符串长度(要超过等于原数的平头部分的长),
–3参数是截取小数点后几各,小数点也终究一个
select STR(1111.111,6,3)
–stuff 参数1凡源字符串,参数2凡打第几号索引开始,
–参数3代表为后去除几各项(包括索引到的开始的字符),
–参数4象征一旦插入的字符串
select STUFF(‘abcde12345′, 1,2,’哈哈’)
–substring 于指定索引截取指定长度的字符串
select yufen ,SUBSTRING(name,1,1)from stu where age=24 order by yufen
asc

 

–时间日期函数
SELECT @@DATEFIRST AS ‘1st Day’, DATEPART(dw, GETDATE()) AS ‘Today’
SELECT GETDATE()
SELECT @@DATEFIRST–系统常量,直接返回一个价值
–dateadd,参数1凡增加的项,参数2是充实多少,参数3凡是在谁时间达添
select DATEADD(YEAR,2,’2007-01-01′)–month day
–datediff 相差多少,参数1是点名特别数,餐宿2是开始,参数3凡结束
select DATEDIFF(MONTH,’2007-01-01′,’2007-05-01′)
–datename 返回参数1指定的类别
select DATENAME(WEEKDAY,’2011-1-2′)–(year,month ,day,week)
–datepart等同于datename区别为datename返回字符串,datepart返回int

–getdate获得当地时间日期
select GETDATE()
–isdate判断时间日期格式是否是,正确与否1,错误也0
select ISDATE(‘1-2-31’)
–精确落时间
select SYSDATETIME()

 

–数据易,函数转换
–cast
select CAST(123 as varchar(20))–值类型转化为字符串
select CAST(1.23 as int)–decimal类型转化为int
select CAST(123 as decimal(18,2))–int类型转化为decimal
select CAST(‘1.23’ as decimal(18,2))–字符串类型转化为decimal
select CAST(‘12.3’ as int)–字符串转int

–convert–前面写类型,后止写原数,与cast相反
select convert(int ,’123′)

 

相关文章