1、在xxx各大学的<<单身群体资料库>>中,用数据库Mysql来存储学生信息。其中,用户信息、星座信息和血型信息分别采用Users、Star和Blood三个表来保存,其中Users表引用了Star和Blood的数据,数据结构如下:
表名 | Users | 作用 | 存储用户的基本信息 | |||
主键 | UserId | |||||
序号 | 字段名称 | 字段说明 | 类型 | 长度 | 属性 | 备注 |
1 | UserId | 主键、自动增长 | Int | 非空 | ||
2 | UserName | 用户名称 | varchar | 30 | 非空 | |
3 | UserPwd | 用户密码 | varchar | 30 | 非空 | |
4 | NickName | 用户昵称 | varchar | 30 | 非空 | |
5 | Sex | 用户性别 | Int | 非空 | 1男-0女 | |
6 | Height | 用户身高 | Int | 允许空 | null(cm) | |
7 | Age | 用户年龄 | Int | 允许空 | null | |
8 | IsDone | 是否已找到对象 | Int | 非空 | 1有-0无 | |
9 | StarId | 星座id | Int | 允许空 | null | |
10 | BloodId | 血型id | Int | 允许空 | null |
表名 | Star | 作用 | 星座数据字典表 | |||
主键 | StarId | |||||
序号 | 字段名称 | 字段说明 | 类型 | 长度 | 属性 | 备注 |
1 | StarId | 主键、自动增长 | Int | 非空 | ||
2 | StarName | 星座名称 | varchar | 30 | 非空 |
表名 | Blood | 作用 | 血型库字典表 | |||
主键 | BloodId | |||||
序号 | 字段名称 | 字段说明 | 类型 | 长度 | 属性 | 备注 |
1 | BloodId | 主键、自动增长 | Int | 非空 | ||
2 | BloodType | 血型 | varchar | 10 | 非空 |
要求:
1、 按以上结构分别建立三张表
2、 用sql语句初始化如下相应的数据
a) 星座:白羊座,金牛座,双子座,巨蟹座,狮子座,处女座,天秤座,天蝎座,射手座,摩羯座,水瓶座,双鱼座
Insert into Star(StarId,StarName) values (1,"白羊座");
Insert into Star(StarId,StarName) values (2,"金牛座");
Insert into Star(StarId,StarName) values (3,"双子座");
Insert into Star(StarId,StarName) values (4,"巨蟹座");
Insert into Star(StarId,StarName) values (5,"狮子座");
Insert into Star(StarId,StarName) values (6,"处女座");
Insert into Star(StarId,StarName) values (7,"天秤座");
Insert into Star(StarId,StarName) values (8,"天蝎座");
Insert into Star(StarId,StarName) values (9,"射手座");
Insert into Star(StarId,StarName) values (10,"摩羯座");
Insert into Star(StarId,StarName) values (11,"水瓶座");
Insert into Star(StarId,StarName) values (12,"双鱼座");
b) 血型:A,B,AB,O
insert into Blood(BloodId,BloodType)value(1,"A");
insert into Blood(BloodId,BloodType)value(2,"B");
insert into Blood(BloodId,BloodType)value(3,"AB");
insert into Blood(BloodId,BloodType)value(4,"O");
3、 向Users表中按字段要求插入12条自定义数据,以便进行下面练习
a) 要求有两条年龄大于32岁的女同学
要求分别有 没有找到对象 和 已经找到对象了的 男女同学
b) 要求有14、16、18、19、30、32岁的女同学,身高有150、155、160等数据
c) 要求有17、16、19、20、23、26岁的男同学,身高有168、170、176、180等数据
4、 根据指定UserId对应的没有找到对象的用户更改为已经找到对象了
update users set IsDone=1 where UserId=6;
5、 删除年龄大于32岁的所有女同学
delete from users where age>32;
6、 查询出所有学生的 主键、用户名称、性别、身高、是否已经找到对象
select UserId,UserName,Sex,Height,IsDone from users;
7、 查询出没有找到对象的年龄在18-22岁之间身高在158cm以上的所有女生
select * from users where Age>18 and Age<32 and IsDone=0 and Height>158 and Sex=0;
8、 查询出已经有找到对象的年龄在20-24岁之间的身高在170cm以上所有男生
select * from users where Age>20 and Age<24 and IsDone=1 and Height>170 and Sex=1;
9、 查询出身高不等于155cm的所有女生的身高和姓名,并查询结果中对应显示“身高”、“姓名”
select UserName as "姓名",Height as "身高" from users where Sex=0 and Height<>155
;
10、 查询出所有学生信息,女生排列在前,男生排列在后,男生女生身高从高到低排列
select * from users order by Sex asc,Height desc;
11、 查询出所有没有登记年龄的学生
select * from users where Age=null;
12、 查询出所有未找到对象的学生的姓名、性别,并在结果集中增加一列说明为”单身群体”
select UserName as "姓名",Sex as "性别","单身群体" as "群体" from users where IsDone=0;
13、 查询身高最高的前三位女同学
select * from users where Sex=0 order by Height desc limit 0,3;
14、 查询老三、老四、老五三女同学
select UserName,Sex from users where UserName="老三" or UserName="老四" or UserName="老五";
15、 查询名称中有“花花”的所有男同学
select * from users where UserName like "%花花%" and Sex=1;
16、 查询用户昵称是以“小”开头的所有同学
17、
18、 select * from users where NickrName like "小%" ;
19、 查询用户昵称是以“小”结束的所有同学
20、 select * from users where NickrName like "%小" ;
21、 查询年龄为16、18、30岁的所有女同学
select * from users where (Age=16 or Age=18 or Age=30)
and Sex=0;
22、 查询所有女生年龄之和并结果中显示“女生总年龄”
mysql> select sum(age) as "女生总年龄" from users where Sex=0;
23、 查询所有男生的平均年龄并结果中显示“男平均年龄”
mysql> select avg(age) as "男生平均年龄" from users where Sex=1;
24、 查询出年龄最大的女生信息
select * from users where Age=(select max(Age) from users where Sex=0);
25、 查询出身高最矮的男生信息
mysql> select * from users where Height=(select min(height) from users where Sex=1);
26、 查询出女生个数并结果中显示“女生总数”
mysql> select count(*) as "女生总人数" from users where Sex=0;
27、 查询出年龄在18-22岁之间的所有女生的姓名、星座名称
mysql> select UserName,StarName from users,star where Sex=0 and users.StarId=star.StarId and (age>18 and age<22);
28、 查询出身高在165-175cm之间的所有男生的姓名、星座名称、血型
select UserName,StarName,BloodType from users,star,blood where users.StarId=star.StarId and users.BloodId=blood.BloodId and Sex=1 and Height>165 and Height<175;
29、 导出tuts数据库进行数据备份存储