网页资讯视频图片知道文库贴吧地图采购
进入贴吧全吧搜索
赵建玲吧 关注：1贴子：76
  • 2回复贴，共1页

杨中科 SQL基础中用到的代码,自己一点一点打的

只看楼主收藏回复

create table T_Employee(FNumber VARCHAR(20),FName VARCHAR(20),FAge INT,FSalary NUMERIC(10,2),PRIMARY KEY(FNumber));
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)VALUES(*DEV001*,*Tom*,25,8300);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)VALUES(*DEV002*,*Jerry*,28,2300.80);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)VALUES(*SALE001*,*John*,23,5000);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)VALUES(*SALE002*,*Kerry*,28,6200);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)VALUES(*SALE003*,*Stone*,22,1200);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)VALUES(*HR001*,*Jane*,25,2200.88);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)VALUES(*HR002*,*Tina*,25,5200.36);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)VALUES(*IT001*,*Smith*,28,3900);
INSERT INTO T_Employee(FNumber,FAge,FSalary)VALUES(*IT002*,27,2800);
---------Lesson7抑制重复数据
alter table T_Employee add FSubCompany varchar(20); --分公司
alter table T_Employee add FDepartment varchar(20); --部门
alter table T_Employee add FInDate datetime; --入职日期
update T_Employee set FInDate=*2010-09-07 12:00:00*, FSubCompany=*Beijing*,FDepartment=*development*where FNumber=*DEV001*;
update T_Employee set FInDate=*2011-09-07 12:00:00*, FSubCompany=*Shenzhen*,FDepartment=*development*where FNumber=*DEV002*;
update T_Employee set FInDate=*2012-09-07 12:00:00*, FSubCompany=*Beijing*,FDepartment=*HumanResource*where FNumber=*HR001*;
update T_Employee set FInDate=*2009-09-07 12:00:00*, FSubCompany=*Beijing*,FDepartment=*HumanResource*where FNumber=*HR002*;
update T_Employee set FInDate=*2010-09-07 12:00:00*, FSubCompany=*Shenzhen*,FDepartment=*InfoTech*where FNumber=*IT001*;
update T_Employee set FInDate=*2011-09-07 12:00:00*, FSubCompany=*Beijing*,FDepartment=*InfoTech*where FNumber=*IT002*;
update T_Employee set FInDate=*2011-09-07 12:00:00*, FSubCompany=*Beijing*,FDepartment=*Sales*where FNumber=*SALE001*;
update T_Employee set FInDate=*2012-09-07 12:00:00*, FSubCompany=*Shenzhen*,FDepartment=*Sales*where FNumber=*SALE002*;
update T_Employee set FInDate=*2009-09-07 12:00:00*, FSubCompany=*Beijing*,FDepartment=*Sales*where FNumber=*SALE003*;
-----------建立临时工表
create table TempEmployee(FIdCardNumber varchar(20),FName varchar(20),FAge int not null,PRIMARY KEY(FIdCardNumber));
insert into TempEmployee(FIdCardNumber,FName,FAge)values(*1234567890121*,*Sarani*,20);
insert into TempEmployee(FIdCardNumber,FName,FAge)values(*1234567890122*,*Tom*,24);
insert into TempEmployee(FIdCardNumber,FName,FAge)values(*1234567890123*,*YaLaHa*,18);
insert into TempEmployee(FIdCardNumber,FName,FAge)values(*1234567890124*,*Tina*,26);
insert into TempEmployee(FIdCardNumber,FName,FAge)values(*1234567890125*,*Konkaya*,30);
insert into TempEmployee(FIdCardNumber,FName,FAge)values(*1234567890126*,*Fotifa*,27);
----------------最后一讲 Join
CREATE TABLE [T_Customers](
[ID] [INT] NOT NULL,
[NAME][NVARCHAR](50) COLLATE Chinese_PRC_CI_AS Null,
[Age][int] null
);
INSERT INTO [T_Customers]([ID],[NAME],[Age])VALUES(1,N*tom*,10);
INSERT INTO [T_Customers]([ID],[NAME],[Age])VALUES(2,N*jerry*,15);
INSERT INTO [T_Customers]([ID],[NAME],[Age])VALUES(3,N*john*,22);
INSERT INTO [T_Customers]([ID],[NAME],[Age])VALUES(4,N*lily*,18);
INSERT INTO [T_Customers]([ID],[NAME],[Age])VALUES(5,N*lucy*,18);
CREATE TABLE[T_Orders](
[ID][int] NOT NULL,
[BillNo][nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[CustomerId] [int] NULL
);
INSERT INTO [T_Orders]([ID],[BillNo],[CustomerId])VALUES(1,N*001*,1)
INSERT INTO [T_Orders]([ID],[BillNo],[CustomerId])VALUES(2,N*002*,1)
INSERT INTO [T_Orders]([ID],[BillNo],[CustomerId])VALUES(3,N*003*,3)
INSERT INTO [T_Orders]([ID],[BillNo],[CustomerId])VALUES(4,N*004*,2)
INSERT INTO [T_Orders]([ID],[BillNo],[CustomerId])VALUES(5,N*005*,2)
INSERT INTO [T_Orders]([ID],[BillNo],[CustomerId])VALUES(6,N*006*,5)
INSERT INTO [T_Orders]([ID],[BillNo],[CustomerId])VALUES(7,N*007*,4)
INSERT INTO [T_Orders]([ID],[BillNo],[CustomerId])VALUES(8,N*008*,5)
IP属地:浙江1楼2014-08-05 16:03回复
    ------子查询
    CREATE TABLE T_Reader
    (Fid int not null,
    Fname VARCHAR(50),
    FYearOfBirth int,
    FCity varchar(50),
    FProvince VarChar(50),
    FYearOfJoin int);
    --DROP TABLE T_Readers
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(1,*Tom*,1979,*TangShan*,*Hebei*,2003);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(2,*Sam*,1981,*LangFang*,*Hebei*,2001);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(3,*Jerry*,1966,*DongGuan*,*GuangDong*,1995);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(4,*Lily*,1972,*JiaXing*,*ZheJiang*,2005);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(5,*Marry*,1985,*BeiJing*,*BeiJing*,1999);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(6,*Kelly*,1977,*ZhuZhou*,*HuNan*,1995);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(7,*Tim*,1982,*YongZhou*,*HuNan*,2001);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(8,*King*,1979,*JiNan*,*ShanDong*,1997);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(9,*John*,1979,*QingDao*,*ShanDong*,2003);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(10,*Lucy*,1978,*LuoYang*,*HeNan*,1996);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(11,*July*,1983,*ZhuMaDian*,*HeNan*,1999);
    insert into T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin) values(12,*Fige*,1981,*JinCheng*,*ShanXi*,2003);
    Create table T_Book(
    Fid int not null,
    FName varchar(50),
    FYearOfPublished int,
    FCategoryCode int
    );
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(1,*About J2EE*,2005,4);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(2,*Learning Hibernate*,2003,4);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(3,*Two Cites*,1999,1);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(4,*Jane Eyre*,2001,1);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(5,*Oliver Twist*,2002,1);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(6,*History of China*,1982,2);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(7,*History of England*,1860,2);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(8,*History of America*,1700,2);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(9,*History of The World*,2008,2);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(10,*Atom*,1930,3);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(11,*RELATIVITY*,1945,3);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(12,*Computer*,1970,3);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(13,*Astronomy*,1971,3);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(14,*How To Singing*,1771,5);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(15,*DaoDeJing*,2001,6);
    insert into T_Book(FId,FName,FYearOfPublished,FCategoryCode) values(16,*Obedience to Authority*,1995,6);
    --最喜欢的图书
    Create table T_ReaderFavorite(FcategoryCode int ,FReaderID int);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(1,1);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(5,2);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(2,3);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(3,4);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(5,5);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(1,6);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(1,7);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(4,8);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(6,9);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(5,10);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(2,11);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(2,12);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(1,12);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(3,1);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(1,3);
    insert into T_ReaderFavorite(FCategoryCode,FReaderId) values(4,4);
    Create table T_Category(
    Fid int not null,
    FName varchar(50)
    );
    INSERT INTO T_Category(Fid,FName)VALUES(1,*Story*);
    INSERT INTO T_Category(Fid,FName)VALUES(2,*History*);
    INSERT INTO T_Category(Fid,FName)VALUES(3,*Theory*);
    INSERT INTO T_Category(Fid,FName)VALUES(4,*Teachnoloy*);
    INSERT INTO T_Category(Fid,FName)VALUES(5,*Art*);
    INSERT INTO T_Category(Fid,FName)VALUES(6,*Philosophy*);
    IP属地:浙江2楼2014-08-05 16:38
    回复

      扫二维码下载贴吧客户端

      下载贴吧APP
      看高清直播、视频！
      • 2回复贴，共1页
      发表回复
      发贴请遵守贴吧协议及“七条底线”贴吧投诉
      内  容:
        查看全部
      发 表
      保存至快速回贴

      发布成功
      去贴吧APP订阅抽奖结果通知
      扫码进入贴吧APP
      ···········
      开奖后第一时间站内信通知
      ···········
      中奖结果全员公示