create unique index stusno on student(sno); --创建索引
create clustered index stuname on student(sname);-- 创建聚簇索引
drop index student.stuname; -- 删除索引
alter table student add constraint PK1_ID primary key(sno);--修改表的sno为主键
--创建表
create table student (
sno char(5) not null unique,
sname char(20) unique,
ssex char(1),
sage int,
sdept char(15));
--删除表
drop table student;
ALTER TABLE student DROP CONSTRAINT unique --没有解决
alter table student drop CONSTRAINT sname ;
--建立WoolAdminName表
create table WoolAdminName
(
ID int identity(1,1) not null,
AdminName varchar(30) not null,
pwd varchar(30) not null
)
--建立管理员WoolAdminName表约束
go
alter table WoolAdminName
add constraint PK_ID primary key(ID)
insert into WoolAdminName values('admin','admin')
create table course(
cno char(5) not null primary key,
sname char(20) ,
cpno char(5) not null,
ccredit int );
create table sc(
cno char(5),
sno char(5),
grade int,
primary key(sno,cno)
)
select * from student;
insert into student values('1','ren','m',20,'信息工程');
insert into student values('2','xi','w',18,'公共管理');
insert into student values('3','hai','m',22,'管理科学');
insert into student values('4','hui','w','19','信息工程');
select sname 姓名, 40-sage 年龄 from student;
select sname 姓名, sage 年龄 from student where sage >=20;
select sname 姓名, sage 年龄 from student group by sage having max(sage) ;
use test1;
alter table student drop unique(sname);
select sname,'Year of Birth:',2007-sage birthday,lower(sdept) from student;
select * from student;
select ssex from student;
select distinct ssex from student;
select * from student where sage =22;
select * from student where sage <>22;
select * from student where sage !=22;
select * from student where sage>21;
select * from student where sage !>21;
--下边的形式不对
select * from student where sage not > 21;
--上边的形式不对 应改成下边的形式
select * from student where not sage > 21;
select * from student where sage between 19 and 21;-- between and
select * from student where sage not between 19 and 21;--not between and
select * from student where sage in (19,20,21);-- in
select * from student where sage not in (19,20,21);-- not in
select * from student where sname like 'h\_i'escape '\'; -- like
select * from student where sname not like 'h%'; -- not like
select * from student where sname is null; -- is null
select * from student where sname is not null; -- is not null
select * from student where sname like 'h%' and sage>20;--and
select * from student where sname like '%i' or sage<20;-- or
select * from student where sage in (19,20,21)order by sage desc;-- order by
select * from student order by sdept desc,sage desc;--系按降序排列，同系中的按年龄降序排列
select count(*) from student; -- 统计元组个数
select count(*) from student where sage>20; -- 有条件统计元组个数
select count(distinct ssex) from student;-- 统计一列中 不重复的值的个数
select count(ssex) from student;--统计一列中值的个数
select sum(sage) from student;-- 计算一列值的总和（此列必为数值型）
select avg(sage) from student; -- 计算一列值的平均值
select max(sage) from student; -- 求一列值的最大值
select min(sage) from student; -- 求一列值的最小值
select sno from sc group by sno having count(*)>=2;
SELECT *
FROM dbo.course INNER JOIN
dbo.sc ON dbo.course.cno = dbo.sc.cno INNER JOIN
dbo.student ON dbo.sc.sno = dbo.student.sno
select student.*,sc.* from student,sc where student.sno=sc.sno;--等值连接
select student.*,sc.* from student,sc;--广义笛卡尔积
--等值连接去掉重复的列就是自然连接
select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno;
--自连接
select first.cno,second.cpno from course first,course second where first.cpno=second.cno;
SELECT p1.pub_id,p2.pub_id,p1.pr_info
FROM pub_info AS p1 INNER pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
--外连接
--join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比
--较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用
--的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。
--外--连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)
--和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹
--配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的
--数据行。
--交叉连接(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的
--数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
--连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑
--运算符等构成。
--无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接
--连接。-
select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno *;--错误的写法
-- 右外连接
select student.sno,sname,ssex,sage,sdept,cno,grade from student right join sc on student.sno=sc.sno ;
--左外连接
SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b
ON a.username=b.username
--嵌套查询
use test1;
select sno, sname from student where sno in (
select sno from sc where cno in
(select cno from course where sname='数据结构')
);
select student.sno,student.sname from student, sc, course
where student.sno=sc.sno and sc.cno=course.cno and course.sname='数据结构';
select sname,sage,sdept from student where sage<any(select sage from student where sdept='信息工程');
select sname,sage,sdept from student where sage<all(select sage from student where sdept='信息工程');
--exists关键字
select sname from student where exists (select * from sc where sno=student.sno and cno='1');
select sname from student where not exists (select * from sc where sno=student.sno and cno='1');
select * from course;
insert into course values('1','语文','2','');
insert into course values('2','数学','2','');
insert into course values('3','英语','2','');
insert into course values('4','c语言','2','');
insert into course values('5','数据结构','2','');
select * from sc;
insert into sc values('1','1','');
insert into sc values('1','2','');
insert into sc values('2','2','');
insert into sc values('2','1','');
insert into sc values('3','3','');
insert into sc values('5','3','');