create database db_mydb;

--创建数据库
create database db_mydb
on(
name='mydb',
filename='e:\db_mydb.mdf',
size=10,
maxsize=50,
filegrowth=5
)

--创建日志文件
log
on(
name='mydb_log',
filename='e:\db_mydb_log.ldf',
size=3,
maxsize=20,
filegrowth=1
)

--删除数据库
drop database db_mydb;
--备份数据库
backup database db_mydb to disk='e:\mydb.dat' with format,name='db_mydb';
--恢复数据库
restore database db_mydb from disk='e:\mydb.dat'
--转移数据库
with move 'mydb' to 'd:\db_mydb.mdf',
move 'mydb_log' to 'e:\db_mydb_log.ldf';

--创建学生表
use db_mydb;

create table tb_students(
stu_num char(10) not null unique,
stu_name varchar(20) not null,
stu_sex varchar(2) not null,
stu_age int not null,
stu_tel varchar(11) not null unique,
stu_qq varchar(10) not null
--primary key(stu_num)
);

--课程表
create table tb_courses(
course_num char(8) primary key,
course_name varchar(50) not null,
course_xf int not null,
course_des varchar(200)
);

--成绩表
create table tb_grades(
grade_snum char(10) not null,
grade_cnum char(8) not null,
grade_score int,
primary key(grade_snum,grade_cnum), --在创建表的时候，将制定的字段设置为主键（联合主键）
foreign key(grade_snum) references tb_students(stu_num), --这两句在创建表的时候，将表中的字段设置为外键和
constraint fk_grades_courses foreign key(grade_cnum) references tb_courses(course_num) --指定表中的主键关联
);

--删除数据表数据
drop table tb_students;
drop table tb_courses;
drop table tb_grades;

--修改表结构

--1.添加字段
alter table tb_students add stue_des varchar(200);
--2.删除字段
alter table tb_students drop column atu_des;
--3.修改表中字段的类型
alter table tb_students alter column stu_qq int not null;
--4.添加表中的约束（主键）
--alter table tb_students add constraint primary key(stu_num);
alter table tb_students add constraint pk_tb_students primary key(stu_num);
alter table tb_grades add constraint pk_tb_grades primary key(grade_snum,grads_cnum);
--5.添加表中的约束（外键）
alter table tb_grades add constraint fk_grades_students foreign key(grade_snum) references tb_students(stu_num);
alter table tb_grades add constraint fk_grades_courses foreign key(grade_cnum) references tb_courses(courses_num);

--对数据表中数据的操作：

--1.添加操作：insert into <tableName>(,,,) values(,,,);
--添加2个学生信息
insert into tb_students(stu_num,stu_name,stu_sex,stu_age,stu_tel,stu_qq)
values('2013030231','张全蛋','男',20,'15007123781','437234087');
insert into tb_students(stu_num,stu_name,stu_sex,stu_age,stu_tel,stu_qq)
values('2013030234','全张蛋','男',30,'15007123782','444444444');
insert into tb_students(stu_num,stu_name,stu_sex,stu_age,stu_tel,stu_qq)
values('2013030235','全蛋张','女',15,'15007123783','555555555');
insert into tb_students(stu_num,stu_name,stu_sex,stu_age,stu_tel,stu_qq)
values('2013030232','李小花','女',18,'15007123789','748748748');
insert into tb_students(stu_num,stu_name,stu_sex,stu_age,stu_tel,stu_qq)
values('2013030233','赵铁柱','男',21,'13838385438','174174174');

--添加2个课程信息
insert into tb_courses(course_num,course_name,course_xf,course_des)
values('10011001','Java',4,'杨涛主讲');
insert into tb_courses(course_num,course_name,course_xf,course_des)
values('10011002','C++',4,'蒋小青主讲');
insert into tb_courses(course_num,course_name,course_xf,course_des)
values('10011003','美术',2,'梵高主讲');
insert into tb_courses(course_num,course_name,course_xf,course_des)
values('10011004','音乐',3,'贝多芬主讲');

--2.删除操作：delete from tb_courses where <>;
delete from tb_courses; --删除表中所有数据
delete from tb_students where stu_age<20 and stu_sex='女';

--3.修改操作：update <tableName> set <> where <>;
update tb_students set stu_age='22' where stu_num='2013030233';
update tb_students set stu_sex='男',stu_tel='15858585588' where stu_qq='174174174';

--4.查询操作：select * from <tableName>;
--查询某张表中所有数据(*代表所有列)
select * from tb_students;
select * from tb_courses;
select stu_name,stu_tel,stu_qq from tb_students;
--计算列查询
select stu_name,2015-stu_age from tb_students;
--给查询出的列去一个名
select stu_name as '姓名',2015-stu_age as '出生年份' from tb_students;
--条件查询
select stu_name,stu_age from tb_students where stu_age<=20;
--多条件查询
select stu_name,stu_age from tb_students where stu_age>=19 and stu_age<=21;
--模糊查询
select * from tb_students where stu_name like '张%'; --(%表示0~任意个字符)
select * from tb_students where stu_name like '%张';
select * from tb_students where stu_name like '%张%';
select * from tb_students where stu_name like '%张%' and stu_name not like '张%';
select * from tb_students where stu_name like '_%张%'; --(下划线表示除了该字符以外的任意一个字符)
--对查询结果进行排序
select * from tb_students;
select * from tb_students order by stu_age;
select * from tb_students order by stu_age asc; --(asc表示升序且可以省略，desc表示降序)
select * from tb_students order by stu_age desc;
select * from tb_students order by stu_sex desc,stu_age desc; --(先比较前面的，不满足时再比较后面的)
select * from tb_students where stu_sex='女' order by stu_age desc;
--聚合函数查询(返回的是个数值)
--统计函数count
select count(stu_num) from tb_students;
--求和函数sum
select sum(stu_age) from tb_students where stu_sex='男';
--求平均值avg
select avg(stu_age) from tb_students;
--最大值max
select max(stu_age) from tb_students;
--最小值min
select min(stu_age) from tb_students;
--分组查询(按什么分组，前面只能按什么筛选)(having 只用在group by之后，用来对分组之后的数据再次筛选)
select stu_sex,count(stu_num) as '总人数',avg(stu_age) as '平均年龄' from tb_students
group by stu_sex having avg(stu_age)<20;
--单表分页查询(pageSize表示,pageNum表示)

--一页显示5条 1（1~5） 2（6~10） 3（11~15） 4（16~18）
--查询第一页
select top(5) * from tb_students;

--查询第2页，就是排除第1页之后的前5条
select top(5) * from tb_students where stu_num not in (select top(1*5) stu_num from tb_students)

--查询第3页，就是排除前2页之后的前5条
select top(5) * from tb_students where stu_num not in (select top(2*5) stu_num from tb_students)

--查询第4页，就是排除前3页之后的前5条
select top(5) * from tb_students where stu_num not in (select top(3*5) stu_num from tb_students)

--查询第N页，就是排除前N-1页只有的前5条
select top(pageSize) * from tb_students where stu_num not in
(select top(pageSize*(pageNum-1)) stu_num from tb_students where stu_sex='男') and stu_sex='男';

select * from tb_students;
delete from tb_students where stu_num not in(select top(3) stu_num from tb_students);

--多表联合查询
select * from tb_students,tb_courses;
--内连接
--等值连接
select * from tb_students s,tb_grades g where s.stu_num=g.grade_snum;
--自然连接

--外连接(左连接，右连接，全连接)