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 testscorecreate table testscore(  id int primary key AUTO_INCREMENT,  START varchar(20));--create bloodtypecreate table bloodtype(  id int primary key AUTO_INCREMENT,  bloodtype varchar(20));--create usercreate 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 testscoreselect * from testscore;insert into testscorevalues(1,'白羊座');insert into testscore(start)values('白羊座');update testscore set start='金牛座' where id=2;--insert date which be leftinsert 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 bloodtypeinsert into bloodtypevalues(1,'A型');select * from bloodtype;--insert other datasinsert into bloodtype(bloodtype)values('B型');insert into bloodtype(bloodtype)values('C型');insert into bloodtype(bloodtype)values('D型');--insert data into userinsert into uservalues(1,'0000','豆豆',default,5,1);select * from user;--insert into uservalues(2,'00A0','小强',default,4,4);insert into uservalues(3,'0000','静静',default,3,2);insert into uservalues(4,'00B0','.NET',default,6,3);insert into uservalues(5,'0CC0','bobo',default,2,1);insert into uservalues(6,'0BB0','我爱C#',default,4,2);insert into uservalues(7,'0AA0','风筝','女',1,1);insert into uservalues(8,'A000','benben',default,1,1);insert into uservalues(9,'000C','吕洞宾',default,11,1);insert into uservalues(10,'A000','清凉一夏','女',6,2);--query all table datasselect * from user;select * from testscore;select * from bloodtype;/*  done the test*/--query user's name and sex which the bloodtype is O typeselect u.nickname 姓名,u.sex 性别 from user u,bloodtype b where u.bloodtypeid=b.id and b.id=3;-- question 2select u.nickname 姓名,u.sex 性别  from user u,testscore t,bloodtype bwhere u.bloodtypeid=b.id and u.starid=t.id and b.bloodtype='A型' and t.start='白羊座';--question 3update user set nickname='天外飞仙' where nickname='.NET';--question 4select u.nickname,u.sex,t.start,b.bloodtype  from user u,testscore t,bloodtype bwhere u.starid=t.id and u.bloodtypeid=b.id;--question 5select u.nickname 用户姓名,u.sex 性别 from user u where u.loginpwd like '%A%';