sql学习4

 数据库简单的用处（四）

if exists (select * from sysdatabases where name='zhongfei') drop database zhongfei go create database zhongfei go use zhongfei go create table T_Person (FName varchar(20) primary key,Fage int,FRemark varchar(20)); create table T_Debt(FNumber varchar(20) primary key,FAmount numeric(10,2) not null,FPerson varchar(20),Foreign key(Fperson) references T_Person(Fname));   go select * from T_Person select * from t_debt     use master drop table T_person drop  table T_Debt   insert into T_Person(FName,FAge,FRemark) values('tom','18','usa') insert into T_Person(FName,FAge,FRemark) values('nanwang','18','usa') insert into T_Person(FName,FAge,FRemark) values('xiaofeng','18','usa') insert into T_Person(FName,FAge,FRemark) values('nana','18','usa') insert into T_Person(FName,FAge,FRemark) values('feifei','20','china') insert into T_Person values('zhengzheng','17','english') insert into T_Person values('kuku','19','banana') insert into T_Person values('shuaishuai','25','china')     insert into T_Debt(FNumber,FPerson) values('1','tom')   insert into T_Debt(FNumber,FAmount,FPerson) values('1','200','tom') insert into T_Debt(FNumber,FAmount,FPerson) values('2','300','nana')     update T_Person set FRemark='sunan'   update T_person set Fage =16 where Fname='tom'   update t_person set fage = 13  where fname='nana' or fname='shuaishuai'   update t_person  set fremark='china' where fname='nana'      update t_debt set famount = 123 where fperson='tom'   delete from t_person delete from t_debt   delete from t_person where fage>18 or fremark ='china'     create table t_employee(fnumber varchar(20),fname varchar(20),fage int,fsalary decimal(10,2),primary key(fnumber)) select  * from t_employee insert into t_employee values('edvoo1','tom',25,8300); insert into t_employee values('edvoo2','nana',21,3500); insert into t_employee values('edvoo3','leilei',27,3300); insert into t_employee values('edvoo4','shuaishuai',20,2500.38); insert into t_employee values('edvoo5','caicai',23,2300); insert into t_employee values('edvoo6','maomao',25,8300.80); insert into t_employee values('edvoo7','tom1',25,85300);     select fnumber from t_employee   select fname,fage from t_employee   select fnumber number ,fname name,fage age,fsalary salary from t_employee   select fname,fsalary from t_employee  where fsalary<5000   select fname,fage,fsalary from t_employee  where fsalary>5000 and fage>20   select max(fsalary) salary  from  t_employee where fage >25 or fage <30   select avg(fage) from t_employee where fsalary>3000   select sum(fsalary) from t_employee   select count(*) from t_employee   select * from t_employee  order by fage desc   select * from t_employee order by fage asc   select * from t_employee order by fage asc ,fsalary desc     select * from t_employee  where fage >22  order by fage asc,fsalary desc     select * from t_employee where fname like 'n%'   select * from t_employee  where fname like '%ai'   select * from t_employee  where fname like '%a%'     select * from t_employee  where fname like '_i'   select * from t_employee where fname like 't[oa]m'     select * from t_employee where fname like '[a-z]ai'   select * from t_employee where fname like 'n[^e]%'   select * from t_employee where fname like '%i'   select * from t_employee where fname like '%a_'     select * from t_employee where fage <24 and fsalary>2500     select * from t_employee where not (fage<24) and not fsalary<2500     select fage,fnumber,fname from t_employee where fage = 20 or fage=25 or fage=27   select fage,fnumber,fname from t_employee where fage in(20,25,27)   select * from t_employee where fage between 23 and 27   select * from t_employee where (fage between 23 and 27) or(fsalary between 2800 and 6500)