SQL基础操作

create database test;
use test;
--创建表
create table student(
Sno char(12) not null primary key,
Sname varchar(20) not null,
Ssex varchar(4) not null check(Ssex ='男'or Ssex ='女') default ('男'), --default和check约束
Sdate Date not null,
Sdept char(10) default('SE')
);

--删除学生表
drop table student;


insert into student(Sno,Sname,Sdate,Sdept) --插入数据
values('201258104015','李军','1998-02-03','SE');


insert into student
values ('201258104016','李军','男','1998-02-03','SE');


select * from student;




use Educ;
select * from Student;


--创建Course表
create table Course(
Cno char(10)not null primary key,
Cname varchar(10) not null unique,
CBefore char(10) null,
Ctimes smallint not null,
CScore int not null,
foreign key (CBefore) references Course (Cno)--外键
);


drop table Course;


insert into Course (Cno,Cname,Ctimes,CScore)
values ('C001','高数',32,'4');


insert into Course (Cno ,Cname,CBefore, Ctimes ,CScore)
values ('C002','C语言','C001',32,'6');


insert into Course (Cno,Cname ,CBefore , Ctimes , CScore)
values ('C003','java语言','C002',64,'8');


Select * from Course;

--创建SC表
Create table SC(
Sno char(12) not null,
Cno char(10) not null ,
SCscore int null check(SCscore>=0 and SCscore<=100),--check约束
foreign key (Sno) references student(Sno), --外键约束
foreign key (Cno) references Course (Cno),
primary key(Sno,Cno)--联合主键
);


--check约束
alter table  SC 
add constraint Ch_score check (SCscore>0);


drop table SC;--删除表


select * from SC;


insert into SC (Sno , Cno , SCscore)
values ('201258104015','C001',null);


insert into SC (Sno , Cno , SCscore)
values ('201258104015','C002',100);


--修改student表的结构,增加一个地址字段
alter table student add Saddress varchar(20) null;
select * from Student;
--修改student表的结构，修改address字段名称
alter table student alter column Saddress  varchar(25) null;
--修改student表中的结构，删除student表的saddress字段
alter table student drop column Saddress;


--数据操作，insert,update ,delete ,select操作


--表自增长
create table test1(
testId bigint primary key identity(1,1),
testName varchar(10) not null default('李军'));


insert into test1(testName) values('李军');


--手动复制表(常用做压力测试的数据生成)
insert into  test1(testName)  (select testName from test1);
select COUNT(*) from test1;


select * from test1;


--复制表和该表结构(若该表不存在，则创建该表)
select testId ,testName into test2 from test1;
select COUNT(*) from test2;