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%';