T-SQL 操作练习

create table Person #新建表格
(
    Ids int auto_increment primary key, #主键,自增
    Name varchar(50) not null, #非空
    Age int,
    Sex bit,
    Nation varchar(50) references Nation(Code) #外键
);
create table Person_1 #新建表格
(
    Ids int auto_increment primary key,
    Name varchar(50) not null,
    Age int,
    Sex bit,
    Nation varchar(50) references Nation(Code)
);
create table Nation #新建表格
(
    Code varchar(50) primary key,
    Name varchar(50)
);

drop table Person_1;#删除表格

#增加加数据 insert into 表名[(列)] values(数据.对应,可为空)

insert into Person values('','张三',18,1,'汉族');
insert into Person values('','李四',28,2,'回族');
insert into Person values('','李五',28,2,'回族');
insert into Person(Name) values('王五');
insert into Nation values('001','汉族');
insert into Nation values('002','回族');

#删除数据 delete from 表名;删除整个表
#delete from 表名 where 条件
#delete form Person where Ids=3;

#更改数据 update 表名 set 列 = "" where 条件;
update Person set Name="张八" where Ids=2;
update Person set Nation="维吾尔族" where Name="王五";

#查找数据
select * from Person;
select * from Nation where Code='002';

delete from Person where name="李五";

#################################################
#新建表
create table Family
(
    Name varchar(50) not null,
    Number int,
    Code varchar(50) primary key,
    Color varchar(50) references Color(Code)
);
create table Color
(
    Code int auto_increment primary key,
    Name varchar(50)
);
#CRUD
#insert into 表名 values(数据),
#insert into 表名(列) values(数据),
insert into Family values('1户',4,'001','red');
insert into Family values('2户',3,'002','0002');
insert into Family values('4户',3,'003','0002');
insert into Family(Code) values('004');
insert into Family(Name) values('3户');
insert into Color values('','red');
insert into Color values('','blue');
insert into Color values('','blue');

#update 
#update Table_Name set Cols_Name='' where Condition
update Family set Color='white' where Code='001';

#delete
#delete from Table_Name;
#delete from Table_Name where Condition;
delete from Family where Name='4户';
delete from Color where Code='3';

#select
#select * from Table_Name;
#select * from Table_Name where Condition;

select * from Family ;
select * from Color where Code='2'