mysql练习

drop database if exists mydb;
create database mydb;
use mydb;
create table stu(
  s_id int primary key AUTO_INCREMENT,
  s_name varchar(10),
  s_sex char(2),
  s_birth date
  -- date时default now()不对，得改成timestamp
);
insert into stu(s_name,s_sex,s_birth) values('tom','男','2001-11-13');
insert into stu(s_name,s_sex,s_birth) values('tony','男','2001-01-13');
insert into stu(s_name,s_sex,s_birth) values('lily','女','2002-11-13');
select * from stu;

--create testscore
create table testscore(
  id int primary key AUTO_INCREMENT,
  START varchar(20)
);
--create bloodtype
create table bloodtype(
  id int primary key AUTO_INCREMENT,
  bloodtype varchar(20)
);
--create user
create table user(
   id int primary key AUTO_INCREMENT,
   loginpwd varchar(10),
   nickname varchar(20),
   sex varchar(10) default '男',
   starid int REFERENCES testscore(id),
   bloodtypeid int REFERENCES bloodtype(id)
   /*
   ,
   CONSTRAINT FK_starid FOREIGN key(starid) REFERENCES testscore(id),
   CONSTRAINT FK_bloodtypeid FOREIGN key(bloodtypeid) REFERENCES bloodtype(id)  
   */
);
--insert data into testscore
select * from testscore;
insert into testscore
values(1,'白羊座');
insert into testscore(start)
values('白羊座');
update testscore set start='金牛座' where id=2;
--insert date which be left
insert into testscore(start)
values('双子座');
insert into testscore(start)
values('巨蟹座');
insert into testscore(start)
values('狮子座');
insert into testscore(start)
values('处女座');
insert into testscore(start)
values('天秤座');
insert into testscore(start)
values('天蝎座');
insert into testscore(start)
values('射手座');
insert into testscore(start)
values('摩羯座');
insert into testscore(start)
values('水瓶座');
insert into testscore(start)
values('双鱼座');
--insert data into bloodtype
insert into bloodtype
values(1,'A型');
select * from bloodtype;
--insert other datas
insert into bloodtype(bloodtype)
values('B型');
insert into bloodtype(bloodtype)
values('C型');
insert into bloodtype(bloodtype)
values('D型');
--insert data into user
insert into user
values(1,'0000','豆豆',default,5,1);
select * from user;
--
insert into user
values(2,'00A0','小强',default,4,4);
insert into user
values(3,'0000','静静',default,3,2);
insert into user
values(4,'00B0','.NET',default,6,3);
insert into user
values(5,'0CC0','bobo',default,2,1);
insert into user
values(6,'0BB0','我爱C#',default,4,2);
insert into user
values(7,'0AA0','风筝','女',1,1);
insert into user
values(8,'A000','benben',default,1,1);
insert into user
values(9,'000C','吕洞宾',default,11,1);
insert into user
values(10,'A000','清凉一夏','女',6,2);
--query all table datas
select * from user;
select * from testscore;
select * from bloodtype;

/*
  done the test
*/
--query user's name and sex which the bloodtype is O type
select u.nickname 姓名,u.sex 性别 from user u,bloodtype b 
where u.bloodtypeid=b.id and b.id=3;

-- question 2
select u.nickname 姓名,u.sex 性别  from user u,testscore t,bloodtype b
where u.bloodtypeid=b.id and u.starid=t.id and b.bloodtype='A型' and t.start='白羊座';

--question 3
update user set nickname='天外飞仙' where nickname='.NET';

--question 4

select u.nickname,u.sex,t.start,b.bloodtype  from user u,testscore t,bloodtype b
where u.starid=t.id and u.bloodtypeid=b.id;

--question 5
select u.nickname 用户姓名,u.sex 性别 from user u 
where u.loginpwd like '%A%';