sql

--create database stu --创建数据库

--查询所有的数据库
--exec sp_helpdb
--exec sp_databases
--查询指定数据库
--exec sp_helpdb jieTest

--查询数据库文件
--exec sp_helpfile

--查看文件组信息
--exec sp_helpfilegroup

--exec sp_renamedb 'xiugai','xiugai1'

--drop database JieTest

--insert into [JieTest].dbo.AddressInfo values(1,'jie','','');

--create database student
--use student
--create table t_student
--(
-- sno char(10),
--   sname varchar(10),
--   ssex char(4),
--   sage int,
--   sdep char(20),
--   sphone char(15)
--)

--drop table [student].dbo.t_student

--添加约束
--create table t_student
--(
--   sno char(10) primary key,
--   sname varchar(10) not null,
--   ssex char(4) check(ssex='男' or ssex='女') default '男',
--   sage int check(sage>=0 and sage<=150),
--   sdep char(20),
--   sphone char(15) unique,
--)

--查看所有的约束
--exec sp_helpconstraint t_student

--insert into t_student values('01','张三','男',25,'软工','110')
--insert into t_student values('02','聂英鹏','男',21,'软工','119')
--insert into t_student values('03','王鹏','男',17,'软工','17')
--insert into t_student values('04','李书豪','男',22,'网工','11045')
--insert into t_student values('05','溜溜','女',20,'软工','110777')

--create table course
--(
--  cno char(10) primary key,
--  cname varchar(20)-------------课程名字
--)

--insert into course values('01','网站开发')
--insert into course values('02','C语言')
--insert into course values('03','JSP')
--insert into course values('04','JAVA')
--insert into course values('05','C#')

--create table t_score
--(
--  sno char(10),
--  cno char(10),
--  score real check(score>=0 and score<=100),
--  constraint pkk primary key(sno,cno),---------------联合主键
--  constraint fk1 foreign key(sno) references t_student,  --外键
--  constraint fk2 foreign key(cno) references course      --外键
--)

--insert into t_score values('01','01',98)
--insert into t_score values('01','02',78)
--insert into t_score values('02','01',100)
--insert into t_score values('02','03',75)

--insert into t_score values('10','01',98)----违反了外键约束
--insert into t_score values('01','09',98)----违反了外键约束
 
--查看表结构
--exec sp_help t_student

--修改列的名字
--exec sp_rename 't_student.sphone','phonenumber'

--1 全部增加信息:按列的顺序增加
--insert into t_student values('06','刘小慧','女',19,'动画','1111')

--2、增加部分内容:sno,sname,sphone,ssex
--insert into t_student(sno,sname ,ssex, phonenumber) values('07','qq','男','123456')

--select top 2 * from t_student
--where sno not in(select top 2 sno from t_student)

--select * from t_student

--select distinct sdep from t_student

--select sdep from t_student group by sdep

--select * from t_student where sdep<>'软工'

--select * from t_student where sage between 22 and 25

--select * from t_student where sdep='软工' or sdep='网工'

--select * from t_student where sdep in('软工' ,'网工')

--select sno+sname as 联合显示 from t_student

--select SUM(sage) as tot from t_student 

--select AVG(sage) from t_student

--select MAX(sage) from t_student

--select MIN(sage) from t_student

--select * from t_student
----模糊查询
----%:任意字符
----select * from t_student where sname like '王%'
----下划线:任意单个字符
--select * from t_student where sname like '王_'
----检索出第二个字是’英‘

--select * from t_student where sname like '_英%'

--默认是升序
--select * from t_student order by sage
--select * from t_student order by sage asc
--select * from t_student order by sage desc
--select * from t_student
----统计男女人数
--select ssex,COUNT(*) as 人数 from t_student group by ssex
--select ssex,AVG(sage) as 人数 from t_student group by ssex
--select ssex,AVG(sage) as 人数 from t_student group by ssex having AVG(sage)>20

--select * from t_student
--select * from t_score
--内连接:等值连接,自然连接;等值(只显示多个表中都具有的内容)
--select t_student.sno,sname,ssex,sage,score from t_student,t_score where t_student.sno = t_score.sno

--select t_student.sno,sname,cno,score from t_score inner join t_student
--on t_score.sno=t_student.sno

--select t_student.sno,sname,cno,score from t_score join t_student
--on t_score.sno=t_student.sno

--左外连接：left join ...on(以左表为主,全显示,右表为辅,不够用空补齐,多余的不显示)
--select * from t_student left join t_score on t_student.sno = t_score.sno
--右外连接：right join ...on(以右表为主,全显示,左表为辅,不够用空补齐,多余的不显示)
--select * from t_student right join t_score on t_student.sno = t_score.sno
--完全连接:full join ...on
--select * from t_student full join t_score on t_score.sno=t_student.sno
--select * from t_student left join t_score on t_score.sno=t_student.sno where score is null
--select * from t_student full join t_score on t_score.sno=t_student.sno where score is null
--select * from t_student right join t_score on t_score.sno=t_student.sno where score is null

--select a.sname,a.sdep from t_student as a join t_student as b on a.sname=b.sname where a.sno<>b.sno
--交叉连接：cross join(笛卡尔积)
--select * from t_student
--select * from course
--select * from t_student cross join course
--数学函数
--select ABS(-12),ABS(12)----求绝对值

--select CEILING (12.5),CEILING (-12.5)

--select FLOOR (12.5),FLOOR (-12.5)

--select PI()

--select RAND()

--select SQRT(144),SQUARE (12)

--索引:聚集索引和非聚集索引
--索引位置和实际存储位置在同一个页面上。clustered
--非聚集索引：(书的目录) 索引位置和实际存储位置不在同一个页面上。nonclustered
--唯一索引
--在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型
--exec sp_helpindex t_student