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

  • 相关阅读:
    Shortest path of the king
    二分查找c++简单模板
    2017广东工业大学程序设竞赛B题占点游戏
    2017广东工业大学程序设竞赛C题爬楼梯
    2017广东工业大学程序设竞赛E题(倒水)
    p1250 种树 贪心
    P1248 加工生产调度 贪心
    P1209 [USACO1.3]修理牛棚 Barn Repair 贪心
    P1607 [USACO09FEB]庙会班车Fair Shuttle 贪心
    P2602 [ZJOI2010]数字计数 数位dp
  • 原文地址:https://www.cnblogs.com/archermeng/p/7537415.html
Copyright © 2020-2023  润新知