--2

create database test on primary
(name='test',filename='d:/database/test.mdf',size=3MB,maxsize=unlimited,filegrowth=1MB)
log on
(name='test_log',filename='d:/database/test_log.ldf',size=3MB,maxsize=unlimited,filegrowth=10%);

use test;
--drop database test;

--alter database test add file(name='test1',filename='d:/database/test1.mdf',size=3MB,maxsize=unlimited,filegrowth=1MB);

--创建表or replace
create table student(
 sid int not null,
 name varchar(32),
 sex char(2),
 age int,
 birthday datetime
 );
drop table student;
--int char varchar decimal(x,y) float real datetime text
create table grade (
 gid int,
 sid int,
 score int
 );
insert into student(sid, name, sex, age, birthday) values(1004, '王五', '男', 100, '1987-1-2');
select * from student;
select * from grade;
delete student where sid=1002;
drop table student;

insert into grade values(102,1001, 46,'SQL');
--delete student;
--delete grade;

alter table student add constraint pk_student_sid primary key(sid);
alter table student add constraint ck_student_sex check(sex in ('男', '女'));
alter table student add constraint ck_student_age check(age between 1 and 100);
--alter table grade add constraint pk_grade_gid primary key(gid);
alter table grade add gname varchar(32);
alter table grade add constraint uk_grade_gname unique(gname);
alter table grade add constraint fk_grade_student_sid foreign key(sid) references student(sid);
alter table grade drop constraint fk_grade_student_sid;

--drop table student;
--drop table grade;
create table student(
 sid int primary key,
 name varchar(32) default '无名',
 sex char(2) check(sex in ('男', '女')),
 age int check(age between 1 and 100),
 birthday datetime not null
 );
create table grade (
 gid int primary key,
 sid int,
    foreign key(sid) references student(sid)on delete set null ,
 gname varchar(32) unique,
 score int check(score between 0 and 100)
);
create table newtable (
 sid int,
 name varchar(32),
 gname varchar(32),
 score int
);
drop table grade;
select * from student;
select * from grade;
select * from newtable;
delete student where sid=1001;
--CRUD
--增加
insert into tablename() values(……);

insert into student values(1002, '李四', '男', 32, '1986-1-2');
select * from student where sid=1001;
insert into student(sid, name,  age,sex,birthday ) values(1003, '王五',  32,'男','1999-9-9');

insert into newtable
 select student.sid, student.name, grade.gname, grade.score from student ,grade  where student.sid=grade.sid;

insert into student output inserted.sid,inserted.name,inserted.sex,inserted.age,inserted.birthday
values(1004, '赵六', '男', 32, '1986-1-2');

delete newtable;
select * from newtable;
--修改
update student set sex='女';

update student set age=22 where sex='女';

--查询
select * from student;
select * from student where sid=1001;
select name as 姓名,age as 年龄 from student where sid=1001;

select name ,age into table2 from student;
delete table2;
select * from table2;
select * from student;
--删除
delete from student;
truncate table student;

delete from student where sid=1001;