数据库添加数据查看数据的语句
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
