mysql dba 面试题 mysql数据库dba面试题及答案

转载

小蝌蚪 2023-08-25 22:48:16

文章标签 mysql dba 面试题字段用户名 Max 文章分类 MySQL 数据库

++++++++++++++++++++++++DBA1问题+答案:阿坤作+++++++++++++++++++++++++++
#本答案所有操作均在userdb数据库下操作且全程不用退出数据库哦!

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

create table user(
 -> name char(50),
 -> password char(5),
 -> uid int,
 -> gid int,
 -> comment char(150),
 -> homedir varchar(60),
 -> shell char(50));
 ! mkdir /myload; cp /etc/passwd /myload

load data infile “/myload/passwd” into table userdb.user fields terminated by “:” lines terminated by “\n”;
在用户名字段下方添加s_year字段存放出生年份默认值是1990;
alter table user add s_year int(10) default 1990 after name;
在用户名字段下方添加字段名sex 字段值只能是girl 或boy 默认值是 boy;
alter table user add sex enum(‘gril’,‘boy’) default ‘boy’ after name;
4.在sex字段下方添加 age字段存放年龄不允许输入负数。默认值是 21;
alter table user add age tinyint unsigned default 21 after sex;
5.把uid字段值是10到50之间的用户的性别修改为
girl;update user set sex=‘gril’ where uid<50 and uid>10;
6.统计性别是girl的用户有多少个。
select count(*) as “Num(girl)” from user where sex=‘gril’;
查看性别是girl用户里 uid号最大的用户名叫什么。
写法一：select name as “GirlName[Max(uid)]” from user where sex=‘gril’ order by uid desc limit 1;
写法二：select name as “GirlName[Max(uid)]” from user where sex=‘gril’ order by uid desc limit 0,1;
添加一条新记录只给name、uid 字段赋值值为rtestd 1000;
insert into user(name,uid) values(‘rtestd’,1000);
添加一条新记录只给name、uid 字段赋值值为rtest2d 2000
insert into user(name,uid) values(‘rtest2d’,2000);
显示uid 是四位数的用户的用户名和uid值。
方法一:select name as “Name(Uid=4)”,uid as “Uid号” from user where uid like ‘____’;
方法二:select name as “Name(Uid=4)”,uid as “Uid号” from user where uid regexp ‘…’;
显示名字是以字母r 开头且是以字母d结尾的用户名和uid。
方法一:select name as “Name(^r.*d $mysql dba 面试题 mysql数据库dba面试题及答案_字段$ )”,uid as “Uid号” from user where name regexp ‘^r.*d$’;
查看是否有名字以字母a开头并且是以字母c结尾的用户。
方法一:select * from user where name like ‘a%c’;
方法二:select * from user where name regexp 'a.c’;
写法一:select count() as "Num(^a.c $mysql dba 面试题 mysql数据库dba面试题及答案_Max_02$ ’;
写法二:select count() as “Num(^a.*c$的用户)” from user where name like ‘a%c’;
把gid 在100到500间用户的家目录修改为/root
update user set homedir=’/root’ where gid<500 and gid>100;
把用户是 root 、 bin 、 sync 用户的shell 修改为 /sbin/nologin
update user set shell=’/sbin/nologin’ where name=‘root’ or name=‘sync’ or name=‘bin’;
查看 gid 小于10的用户都使用那些shell
select name,shell from user where gid<10;
删除名字以字母d开头的用户。
方法一：delete from user where name regexp ‘^d.*’;
方法二：delete from user where name like ‘d%’;
查询 gid 最大的前5个用户使用的 shell
select shell as “Max(shell) Top 5 Info” from user order by gid desc limit 5;
select shell as “Max(shell) Top 5 Info” from user order by gid desc limit 0,5;
查看那些用户没有家目录
select name as “Name(homedir is null)” from user where homedir is null;
把gid号最小的前5个用户信息保存到/mybak/min5.txt文件里。
! mysql -p123456 -e “select * from userdb.user order by gid asc limit 0,5;” > /mybak/min5.txt
#其中查询的方法有两种:
select * from user order by gid asc limit 0,5;
select * from user order by gid limit 5;
使用系统命令useradd 命令添加登录系统的用户名为lucy
! useradd lucy
把lucy用户的信息添加到user1表里
! tail -1 /etc/passwd > /myload/lucy.txt;mysql -p123456 -e “load data infile ‘/myload/lucy.txt’ into table userdb.user1 fields terminated by ‘:’ lines terminated by ‘\n’;”;

#以下操作回到user表
22. 删除表中的 comment 字段
alter table user drop infocomment;
23. 设置表中所有字段值不允许为空

#改不为空前必须满足表中没有NULL值如果有必须加一个数据
 update user set passwd=‘x’;
 update user set gid=0 where name like ‘r%d’;
 update user set homedir=’/’ where name like ‘r%d’;
 update user set shell=’/’ where name like ‘r%d’;
 #修改所有字段不为空
 alter table user modify shell char(50) not null,
 modify name char(50) not null,
 modify sex enum(‘gril’,‘boy’) not null,
 modify age tinyint(3) not null,
 modify s_year int(10) not null,
 modify homedir char(60) not null,
 modify uid int(11) not null,
 modify gid int(11) not null,
 modify passwd char(5) not null;

24. 删除root 用户家目录字段的值
update user set homedir=’’ where name=“root”;
25. 显示 gid 大于500的用户的用户名家目录和使用的shell
select homedir as “Homedir(Gid>500)”,shell as “Shell(Gid>500)” from user where gid>500;
26. 删除uid大于100的用户记录
delete from user where uid>100;
27. 显示uid号在10到30区间的用户有多少个。
方法一:select count() as “10~30内用户的个数” from user where uid>10 and uid<30;
方法二:select count() as “10~30内用户的个数” from user where uid between 10 and 30;
方法三:select count() as “10~30内用户的个数” from user where uid between 10 and 30 having “10~30内用户的个数”;
28. 显示uid号是100以内的用户使用的shell。
select shell as “Uid号100内用户所用的Shell” from user where uid between 0 and 100;
select shell as “Uid号100内用户所用的Shell” from user where uid<=100;
29. 显示uid号最小的前10个用户的信息。
写法一:select * from user order by gid asc limit 0,10 ;
写法二: select * from user order by gid limit 10 ;
30. 显示表中第10条到第15条记录
select * from user limit 10,15;
31.显示uid号小于50且名字里有字母a 用户的详细信息
方法一:select * from user where uid<50 and name regexp “a”;
方法二:select * from user where uid between 0 and 50 and name regexp “a”;
方法三:select * from user where uid<50 and name like “%a%”;
方法四:select * from user where uid between 0 and 50 and name like “%a%”;
32.只显示用户 root bin daemon 3个用户的详细信息。
#由于和16题冲突需要插入一条daemon用户信息
insert into user values (‘daemon’,‘boy’,‘21’,‘1990’,‘x’,‘2’,‘2’,’/sbin’,’/sbin/nologin’);
select * from user where name=‘root’ or name=‘bin’ or name=‘daemon’;
33. 显示除root用户之外所有用户的详细信息。
select * from user where name!=‘root’;
34. 统计username 字段有多少条记录
select count() as “COUNT(username)” from user ;
35. 显示名字里含字母c 用户的详细信息
方法一:select * from user where name like “%c%”;
方法二:select * from user where name regexp “c”;
36. 在sex字段下方添加名为pay的字段，用来存储工资，默认值15000.00
alter table user add pay float default 15000.00 after sex;
37. 把所有女孩的工资修改为10000
update user modify set pay=10000 where sex=“gril”;
38. 把root用户的工资修改为30000
update user modify set pay=30000 where name=“root”;
39. 给adm用户涨500元工资
update user modify set pay=pay + 500 where name=“adm”;
40. 查看所有用户的名字和工资
select name as “姓名”,pay as “工资” from user;
41. 查看工资字段的平均值
select avg(pay) as “人均工资” from user;
42. 显示工资字段值小于平均工资的用户名。
方法一:select name as “姓名(低于平均工资)” from user where pay<14735.29411764706;
方法二:select name as “姓名(低于平均工资)” from user where pay between 0 and 14735.29411764706;
43. 查看女生里uid号最大用户名
写法一：select name as “用户名[MAX(uid)]” from user where sex=“gril” order by uid desc limit 1;
写法二：select name as “用户名[MAX(uid)]” from user where sex=“gril” order by uid desc limit 0,1;
44. 查看bin用户的uid gid 字段的值及这2个字段相加的和
select uid,gid, uid + gid as “sum(uid+gid)” from user where name=“bin”;