数据基础课程第3天练习题参考答案及解析

参考答案及解析

1、把/etc/passwd文件的内容存储到userdb库下的user表里，并做如下配置：

create database userdb default charset=utf8;

create table userdb.user(name char(20) not null ,password char(1) default"X" not null,uid int(11) ,gid int(11),comment char(200)not null default"",homedir char(200) not null default"",shell char(200) not null default"")default charset=utf8;

show variables like “%file_priv”;

system cp /etc/passwd /var/lib/mysql-files/ ;

system ls /var/lib/mysql-files/ ;

load data infile “/var/lib/mysql-files/passwd” into table userdb.user fields terminated by “:” lines terminated by “\n”;

select * from userdb.user;

2、在用户名字段下方添加s_year字段 存放出生年份 默认值是1990
alter table userdb.user add s_year char(4) default “1990” after name;

3、在用户名字段下方添加字段名sex 字段值只能是girl 或boy 默认值是 boy
alter table userdb.user add sex enum(“girl”,“boy”) default “boy” after name;

（alter table userdb.user modify sex set(“girl”,“boy”,“gril”) default “boy”;
update userdb.user set sex=“girl” where uid > 10 and uid < 50;
alter table userdb.user modify sex enum(“girl”,“boy”) default “boy”;）
4、 在sex字段下方添加 age字段 存放年龄 不允许输入负数。默认值 是 21
alter table userdb.user add age tinyint unsigned default 21 after sex;

5、 把uid字段值是10到50之间的用户的性别修改为 girl
update userdb.user set sex=“girl” where uid > 10 and uid < 50;

6、 统计性别是girl的用户有多少个。
select count() from userdb.user where sex=“girl”;
±---------+
| count() |
±---------+
| 4 |
±---------+
1 row in set (0.00 sec)

7、 查看性别是girl用户里 uid号 最大的用户名 叫什么。
select name,sex,uid from userdb.user where sex=“girl” order by uid desc;
±---------±-----±----+
| name | sex | uid |
±---------±-----±----+
| mysql | girl | 27 |
| ftp | girl | 14 |
| games | girl | 12 |
| operator | girl | 11 |
±---------±-----±----+
4 rows in set (0.05 sec)

8、 添加一条新记录只给name、uid 字段赋值 值为rtestd 1000
insert into userdb.user(name,uid) values(“rtestd”,1000);

9、 添加一条新记录只给name、uid 字段赋值 值为rtest2d 2000
insert into userdb.user(name,uid) values(“rtest2d”,2000);

10、 显示uid 是四位数的用户的用户名和uid值。
select name,uid from userdb.user where uid like “____”;
±--------±-----+
| name | uid |
±--------±-----+
| rtestd | 1000 |
| rtest2d | 2000 |
±--------±-----+
2 rows in set (0.00 sec)

11、 显示名字是以字母r 开头 且是以字母d结尾的用户名和uid。
select name,uid from userdb.user where name like “r%d”;;
±--------±-----+
| name | uid |
±--------±-----+
| rtestd | 1000 |
| rtest2d | 2000 |
±--------±-----+
2 rows in set (0.00 sec)
select name,uid from userdb.user where name regexp “^r.*d$”;
±--------±-----+
| name | uid |
±--------±-----+
| rtestd | 1000 |
| rtest2d | 2000 |
±--------±-----+
2 rows in set (0.00 sec)

12、 查看是否有 名字以字母a开头 并且是 以字母c结尾的用户。
select name,uid from userdb.user where name regexp “^a.*c$”;
Empty set (0.00 sec)
select name,uid from userdb.user where name like “a%c”;
Empty set (0.00 sec)
13、 把gid 在100到500间用户的家目录修改为/root
update userdb.user set homedir="/root" where gid>100 and gid<500;
14、 把用户是 root 、 bin 、 sync 用户的shell 修改为 /sbin/nologin
update userdb.user set shell="/sbin/nologin" where name in (“root”,“bin”,“sync”);

15、 查看 gid 小于10的用户 都使用那些shell
select name,shell,gid from userdb.user where gid < 10 order by gid asc;
±---------±---------------±-----+
| name | shell | gid |
±---------±---------------±-----+
| root | /sbin/nologin | 0 |
| sync | /sbin/nologin | 0 |
| shutdown | /sbin/shutdown | 0 |
| halt | /sbin/halt | 0 |
| operator | /sbin/nologin | 0 |
| bin | /sbin/nologin | 1 |
| daemon | /sbin/nologin | 2 |
| adm | /sbin/nologin | 4 |
| lp | /sbin/nologin | 7 |
±---------±---------------±-----+
9 rows in set (0.00 sec)

16、 删除 名字以字母d开头的用户。
delete from userdb.user where name like “d%”;
Query OK, 2 rows affected (0.00 sec)
delete from userdb.user where name regexp “^d”;

17、 查询 gid 最大的前5个用户 使用的 shell
select name,shell,gid from userdb.user order by gid desc limit 5;
±----------------±--------------±-----+
| name | shell | gid |
±----------------±--------------±-----+
| polkitd | /sbin/nologin | 998 |
| chrony | /sbin/nologin | 996 |
| systemd-network | /sbin/nologin | 192 |
| games | /sbin/nologin | 100 |
| nobody | /sbin/nologin | 99 |
±----------------±--------------±-----+
5 rows in set (0.00 sec)

18、 查看那些用户没有家目录
select name,homedir from userdb.user where homedir="";
±--------±--------+
| name | homedir |
±--------±--------+
| rtestd | |
| rtest2d | |
±--------±--------+
2 rows in set (0.01 sec)

19、 把gid号最小的前5个用户信息保存到/mybak/min5.txt文件里。
show variables like “%_file_priv”;
±-----------------±----------------------+
| Variable_name | Value |
±-----------------±----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
±-----------------±----------------------+
1 row in set (0.01 sec)

]# vim /etc/my.cnf
]# mkdir /mybak
]# chown mysql:mysql /mybak
]# systemctl restart mysqld

select * from userdb.user order by gid asc limit 5 into outfile “/mybak/min5.txt” ;

20、 使用系统命令useradd 命令添加登录系统的用户 名为lucy
system useradd lucy;

21、 把lucy用户的信息 添加到user1表里
create table userdb.user1(name char(20),password char(1) default “X”,uid int(10),gid int(10),comment char(200) default"",homedir char(200)def200)default"",shell char(200) default"") DEFAULT CHARSET=utf8;

22、 删除表中的 comment 字段
alter table userdb.user drop comment;

23、 设置表中所有字段值不允许为空
show create table userdb.user \G
desc userdb.user;
update userdb.user set gid=“1000” where uid=“1000”;
update userdb.user set gid=“2000” where uid=“2000”;
alter table userdb.user modify gid int(11) not null;
alter table userdb.user modify age tinyint(3) unsigned DEFAULT ‘21’ not null;
alter table userdb.user modify sex enum(‘girl’,‘boy’) DEFAULT ‘boy’ not null;
alter table userdb.user modify s_year char(4) DEFAULT ‘1990’ not null;
desc userdb.user;
±---------±--------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±--------------------±-----±----±--------±------+
| name | char(20) | NO | | NULL | |
| sex | enum(‘girl’,‘boy’) | NO | | boy | |
| age | tinyint(3) unsigned | NO | | 21 | |
| s_year | char(4) | NO | | 1990 | |
| password | char(1) | NO | | X | |
| uid | int(11) | NO | | NULL | |
| gid | int(11) | NO | | NULL | |
| homedir | char(180) | NO | | | |
| shell | char(180) | NO | | | |
±---------±--------------------±-----±----±--------±------+
9 rows in set (0.00 sec)

24、 删除root 用户家目录字段的值
update userdb.user set homedir=""where name=“root”;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

25、 显示 gid 大于500的用户的用户名 家目录和使用的shell
select name,homedir,shell,gid from userdb.user where gid>500id>500 order by gid;
±--------±----------------±--------------±-----+
| name | homedir | shell | gid |
±--------±----------------±--------------±-----+
| chrony | /var/lib/chrony | /sbin/nologin | 996 |
| polkitd | / | /sbin/nologin | 998 |
| rtestd | | | 1000 |
| rtest2d | | | 2000 |
±--------±----------------±--------------±-----+
4 rows in set (0.00 sec)

26、 删除uid大于100的用户记录
delete from userdb.user where uid>100;
Query OK, 5 rows affected (0.00 sec)

27、 显示uid号在10到30区间的用户有多少个。
select count(uid) from userdb.user where uid > 10 and uid < 30;
±-----------+
| count(uid) |
±-----------+
| 4 |
±-----------+
1 row in set (0.00 sec)

28、 显示uid号是100以内的用户使用的shell。
select distinct shell from userdb.user where uid<100;
±---------------+
| shell |
±---------------+
| /sbin/nologin |
| /sbin/shutdown |
| /sbin/halt |
| /bin/false |
±---------------+
4 rows in set (0.00 sec)
29、 显示uid号最小的前10个用户的信息。
select * from userdb.user order by uid asc limit 10;
±---------±-----±----±-------±---------±----±----±----------------±---------------+
| name | sex | age | s_year | password | uid | gid | homedir | shell |
±---------±-----±----±-------±---------±----±----±----------------±---------------+
| root | boy | 21 | 1990 | x | 0 | 0 | | /sbin/nologin |
| bin | boy | 21 | 1990 | x | 1 | 1 | /bin | /sbin/nologin |
| adm | boy | 21 | 1990 | x | 3 | 4 | /var/adm | /sbin/nologin |
| lp | boy | 21 | 1990 | x | 4 | 7 | /var/spool/lpd | /sbin/nologin |
| sync | boy | 21 | 1990 | x | 5 | 0 | /sbin | /sbin/nologin |
| shutdown | boy | 21 | 1990 | x | 6 | 0 | /sbin | /sbin/shutdown |
| halt | boy | 21 | 1990 | x | 7 | 0 | /sbin | /sbin/halt |
| mail | boy | 21 | 1990 | x | 8 | 12 | /var/spool/mail | /sbin/nologin |
| operator | girl | 21 | 1990 | x | 11 | 0 | /root | /sbin/nologin |
| games | girl | 21 | 1990 | x | 12 | 100 | /usr/games | /sbin/nologin |
±---------±-----±----±-------±---------±----±----±----------------±---------------+
10 rows in set (0.00 sec)

30、 显示表中第10条到第15条记录
select * from userdb.user limit 9,15;
±--------±-----±----±-------±---------±----±----±-------------------±--------------+
| name | sex | age | s_year | password | uid | gid | homedir | shell |
±--------±-----±----±-------±---------±----±----±-------------------±--------------+
| games | girl | 21 | 1990 | x | 12 | 100 | /usr/games | /sbin/nologin |
| ftp | girl | 21 | 1990 | x | 14 | 50 | /var/ftp | /sbin/nologin |
| mysql | girl | 21 | 1990 | x | 27 | 27 | /var/lib/mysql | /bin/false |
| sshd | boy | 21 | 1990 | x | 74 | 74 | /var/empty/sshd | /sbin/nologin |
| postfix | boy | 21 | 1990 | x | 89 | 89 | /var/spool/postfix | /sbin/nologin |
| nobody | boy | 21 | 1990 | x | 99 | 99 | / | /sbin/nologin |
±--------±-----±----±-------±---------±----±----±-------------------±--------------+
6 rows in set (0.00 sec)

31、 显示uid号小于50且名字里有字母a 用户的详细信息
select * from userdb.user where uid < 50 and name regexp “a”;
±---------±-----±----±-------±---------±----±----±----------------±--------------+
| name | sex | age | s_year | password | uid | gid | homedir | shell |
±---------±-----±----±-------±---------±----±----±----------------±--------------+
| adm | boy | 21 | 1990 | x | 3 | 4 | /var/adm | /sbin/nologin |
| halt | boy | 21 | 1990 | x | 7 | 0 | /sbin | /sbin/halt |
| mail | boy | 21 | 1990 | x | 8 | 12 | /var/spool/mail | /sbin/nologin |
| operator | girl | 21 | 1990 | x | 11 | 0 | /root | /sbin/nologin |
| games | girl | 21 | 1990 | x | 12 | 100 | /usr/games | /sbin/nologin |
±---------±-----±----±-------±---------±----±----±----------------±--------------+
5 rows in set (0.00 sec)

32、 只显示用户 root bin daemon 3个用户的详细信息。
select * from userdb.user where name in(“root”,“bin”,“daemon”);
±-----±----±----±-------±---------±----±----±--------±--------------+
| name | sex | age | s_year | password | uid | gid | homedir | shell |
±-----±----±----±-------±---------±----±----±--------±--------------+
| root | boy | 21 | 1990 | x | 0 | 0 | | /sbin/nologin |
| bin | boy | 21 | 1990 | x | 1 | 1 | /bin | /sbin/nologin |
±-----±----±----±-------±---------±----±----±--------±--------------+
2 rows in set (0.00 sec)

33、 显示除root用户之外所有用户的详细信息。
select * from userdb.user where name!=“root”;
±---------±-----±----±-------±---------±----±----±-------------------±---------------+
| name | sex | age | s_year | password | uid | gid | homedir | shell |
±---------±-----±----±-------±---------±----±----±-------------------±---------------+
| bin | boy | 21 | 1990 | x | 1 | 1 | /bin | /sbin/nologin |
| adm | boy | 21 | 1990 | x | 3 | 4 | /var/adm | /sbin/nologin |
| lp | boy | 21 | 1990 | x | 4 | 7 | /var/spool/lpd | /sbin/nologin |
| sync | boy | 21 | 1990 | x | 5 | 0 | /sbin | /sbin/nologin |
| shutdown | boy | 21 | 1990 | x | 6 | 0 | /sbin | /sbin/shutdown |
| halt | boy | 21 | 1990 | x | 7 | 0 | /sbin | /sbin/halt |
| mail | boy | 21 | 1990 | x | 8 | 12 | /var/spool/mail | /sbin/nologin |
| operator | girl | 21 | 1990 | x | 11 | 0 | /root | /sbin/nologin |
| games | girl | 21 | 1990 | x | 12 | 100 | /usr/games | /sbin/nologin |
| ftp | girl | 21 | 1990 | x | 14 | 50 | /var/ftp | /sbin/nologin |
| mysql | girl | 21 | 1990 | x | 27 | 27 | /var/lib/mysql | /bin/false |
| sshd | boy | 21 | 1990 | x | 74 | 74 | /var/empty/sshd | /sbin/nologin |
| postfix | boy | 21 | 1990 | x | 89 | 89 | /var/spool/postfix | /sbin/nologin |
| nobody | boy | 21 | 1990 | x | 99 | 99 | / | /sbin/nologin |
±---------±-----±----±-------±---------±----±----±-------------------±---------------+
14 rows in set (0.00 sec)

34、 统计username 字段有多少条记录
select count(name) from userdb.user ;
±------------+
| count(name) |
±------------+
| 15 |
±------------+
1 row in set (0.00 sec)

35、 显示名字里含字母c 用户的详细信息
select * from userdb.user where name regexp “c”;
±-----±----±----±-------±---------±----±----±--------±--------------+
| name | sex | age | s_year | password | uid | gid | homedir | shell |
±-----±----±----±-------±---------±----±----±--------±--------------+
| sync | boy | 21 | 1990 | x | 5 | 0 | /sbin | /sbin/nologin |
±-----±----±----±-------±---------±----±----±--------±--------------+
1 row in set (0.00 sec)

36、 在sex字段下方添加名为pay的字段，用来存储工资，默认值15000.00
alter table userdb.user add pay int default “15000.00” after sex;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

37、 把所有女孩的工资修改为10000
update userdb.user set pay=“10000.00” where sex=“girl”;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0

38、 把root用户的工资修改为30000
update userdb.user set pay=“30000.00” where name=“root”; ";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

39、 给adm用户涨500元工资
select name,pay from userdb.user where name=“adm”;
±-----±------+
| name | pay |
±-----±------+
| adm | 15000 |
±-----±------+
1 row in set (0.00 sec)

update userdb.user set pay=pay+500 where name=“adm”;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

select name,pay from userdb.user where name=“adm”;
±-----±------+
| name | pay |
±-----±------+
| adm | 15500 |
±-----±------+
1 row in set (0.00 sec)
40、 查看所有用户的名字和工资
select name,pay from userdb.user ;
±---------±------+
| name | pay |
±---------±------+
| root | 30000 |
| bin | 15000 |
| adm | 15500 |
| lp | 15000 |
| sync | 15000 |
| shutdown | 15000 |
| halt | 15000 |
| mail | 15000 |
| operator | 10000 |
| games | 10000 |
| ftp | 10000 |
| mysql | 10000 |
| sshd | 15000 |
| postfix | 15000 |
| nobody | 15000 |
±---------±------+
15 rows in set (0.00 sec)

41、 查看工资字段的平均值
select avg(pay) from userdb.user ;
±-----------+
| avg(pay) |
±-----------+
| 14700.0000 |
±-----------+
1 row in set (0.04 sec)

42、 显示工资字段值小于平均工资的用户名。
select name,pay from userdb.user where pay<(select avg(pay) from userdb.user);
±---------±------+
| name | pay |
±---------±------+
| operator | 10000 |
| games | 10000 |
| ftp | 10000 |
| mysql | 10000 |
±---------±------+
4 rows in set (0.00 sec)

43、 查看女生里uid号最大用户名
select name,sex,uid from userdb.user where uid=(select max(uid) from userdb.user where sex=“girl”);
±------±-----±----+
| name | sex | uid |
±------±-----±----+
| mysql | girl | 27 |
±------±-----±----+
1 row in set (0.00 sec)

44、 查看bin用户的uid gid 字段的值 及 这2个字段相加的和
select name,uid,gid, uid+gid from userdb.user where name="biame=“bin”;
±-----±----±----±--------+
| name | uid | gid | uid+gid |
±-----±----±----±--------+
| bin | 1 | 1 | 2 |
±-----±----±----±--------+
1 row in set (0.00 sec)