常用查询--SQLSever

use test1;
select * from sys.tables;

use test1
sp_columns S1;

-- 修改表S1的字段name长度为10
alter table S1
alter column [name] nvarchar(10);

-- 修改表S1的字段age为int型
alter table S1
alter column age int;

-- 修改表S1的字段age不为空(null)
alter table S1
alter column age int not null;

-- 向数据表S1中添加字段score1, score2, score3, grade
alter table S1
add score1 numeric(3, 1), score2 numeric(3, 1), score3 numeric(3, 1), grade int  not null default 1 

-- 设置数据表S的id为主键
alter table S1
add constraint S1_Pri primary key (id);

-- 更改数据表S1的字段名age为sAge
alter table S1
EXEC sp_rename 'S1.age', 'sAge', 'column';

-- 向数据表S1中添加记录
use test1
insert into S1 values('chen', 19, '001', '男', '99.5', 1, '88', '80');
insert into S1 values('java', 26, '002', '男', '97.5', 1, '78', '91');
insert into S1 values('python', 30, '003', '女', '96.5', 1,'61', '70');
insert into S1 values('c', 44, '004', '女', '60', 1, '59', '92');
insert into S1 values('c++', 44, '005', '女', '70',default, '82', '82');
insert into S1 values('db', 19, '006', '女', '99.5', 1, '91', '87');
insert into S1 values('php', 19, '007', '女', null, 1, '92', '84');


-- 查看数据表S1中的所有记录
use test1
select * from S1;

-- 查看数据表S中有多少个年级
use test1
select distinct grade from S1;

-- 查看数据表S中的全体学生的姓名，年龄，学号
-- method 1
use test1
select [name]  as '姓名', sAge '年龄', id '学号' from S1;


-- method 2
use test1
select [name]  as 姓名, sAge 年龄, id 学号 from S1;

-- 查询数据表S中字段id为001的学生的分数和年级
use test1
select [name] '姓名', score1 '分数', grade '年级' from S1 where id = '001';

-- 查询数据表S中字段分数大于等于80的学生
use test1
select * from S1
where score1 >= 80;

-- 查询数据表S中分数大于等于80并且性别是男的学生
select * from S1 
where score1 >= 80 and sex = '男';

-- 查询数据表S中分数大于等于80并且性别是男,年级为1的学生
use test1
select * from S1
where score1 >= 80 and sex = '男' and grade = 1;

-- 查询数据表S分数不在80--90的学生
-- method 1
use test1
select * from S1
where score1 not between 80 and 100;

-- 查询数据表S中字段学号为001或002或005并且分数大于80的学生
-- method 1
use test1
select * from S1
where id in ('001','002', '005') and score1 >= 80;

-- method 2
use test1
select * from S1
where (id = '001' or id = '002' or id = '005') and score >= 80;

-- 查询数据表S字段学号既不是001也不是002的学生
use test1
select * from S1
where id <> '001' and id <> '002';

-- 查询数据表S中字段名字带有c的学生
use test1
select * from S1
where [name] like '%c%';

-- 查询数据表S中姓c的学生
use test1
select * from S1
where [name] like 'c%';

-- 查询数据表S中字段名字第二个是h的学生
use test1
select * from S1
where [name] like '_h%';

-- 查询数据表S中字段分数为空的学生
use test1
select * from S1
where score1 is null;

-- 查询第三个学生的记录
SELECT TOP 1 * FROM dbo.TB_Student WHERE StuID NOT IN (SELECT TOP 2 StuID FROM dbo.TB_Student);