sql表结构查询语句学习
表结构：
CREATE TABLE [dbo].[student](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NULL,
[age] [int] NULL,
[score] [int] NULL,
CONSTRAINT [PK_mvc_test2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
按年龄分组，查每个年龄的人数，而年龄要大于或等于19
select [age],COUNT(1) '人数'
from student
group by age
having age >= 19
age
人数
19
2
20
1
21
1
33
1
统计总分数
select [name],[score] from student
union all
select '总分',AVG([score]) from student
name
score
Tom
99
Jim
90
Rose
95
XiaoMing
97
Jake
100
Simth
0
总分
80
按id排序，查出第5到第6条记录
select top (2) * from student
where [id] not in
(select top (4) [id] from student)
id
name
age
score
5
Jake
21
100
6
Simth
33
0
insert into book2 (Title,Content,CreatTime)
(select top 200 Title,Content,CreatTime from book)
把一个表的数据更新到另一个表
update student set score=b.score
from student a JOIN tem_student b ON a.id=b.id
sql 条件 case when then end
select [name],
(
case [score]
when 100 then '满分'
when 0 then '鸡蛋'
else CONVERT(varchar(50),[score])
end
) as 分数
from student
select [name],
(
case
when [score]=100 then '满分'
when [score]=0 then '鸡蛋'
else CONVERT(varchar(50),[score])
end
) as 分数
from student
查询表的结构 , 为什么不加这个查出来会多一个name,类型是sysname, b.[name]<>'sysname'
SELECT a.[name] Name,b.[name] DataType,a.[length] Length,colstat IsPrimaryKey FROM syscolumns a
left join systypes b on b.xtype = a.xusertype
where a.[id] = object_id('student') and b.[name]<>'sysname' order by a.[name]
Name
DataType
Length
IsPrimaryKey
id
int
4
1
age
int
4
0
score
int
4
0
name
nvarchar
200
0
--修改字段长度
alter table student alter column [name] varchar(50)
--查询
数据库连接数
SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID]
IN (SELECT [DBID] FROM [Master].[dbo].[SYSDATABASES]
WHERE NAME='student'
)