运维技术指导【4】企业面试MySQL试题练习

问题1
create table t_test(
    id int(4) not null primary key auto_increment,
    time varchar(20) not null,
    degree varchar(10) not null,
    score double(3,1));

问题2
insert into t_test 1,'2005-05-09','优',98.1);

问题3
select degree,count(*),avg(score) from t_test group by degree;
+--------+----------+------------+
| degree | count(*) | avg(score) |
+--------+----------+------------+
| 优     |        1 |   98.00000 |
| 及格   |        1 |   89.50000 |
| 良     |        1 |   89.50000 |
+--------+----------+------------+

问题4
update t_test set time='2005-05-11' where degree='及格';

B
C
A
A

delete和truncate删除数据的区别
	truncate table test执行更快，清空物理文件，清空表中的所有内容
	delete from test是逻辑删除，按行删除，而且可以通过where语句选择要删除的行

 select * from A where time benteen '2006-01-01' and '2006-01-31';

create 创建表
alter  修改表
drop   删除表
3306

A
C
C
BD
D

使用mysqldump备份
	
		备份所有库：[root@kongd ~]# mysqldump -uroot -p -A -B > all.db.sql
		备份student库：mysqldump -uroot -p  -B student > student.sql
		备份表：mysqldump -uroot -p  student score > student_score.sql


		恢复：
			方法1： mysql -uroot -p < 备份文件
			方法2：进入数据库 source 备份文件

授权：grant all on *.* to smart  identified by "123456";

重置密码
		1) 停止数据库
			[root@kongd ~]# systemctl stop mariadb
		2) 启动时加上--skip-grant-tables 跳过权限表
			[root@kongd ~]# mysqld_safe --skip-grant-tables --user=mysql 
		3) 登录数据库，修改密码
			[root@kongd ~]# mysql
			MariaDB [(none)]> update mysql.user
				-> set password=password('123')
				-> where User="root" and Host="localhost";
			Query OK, 1 row affected (0.00 sec)
			Rows matched: 1  Changed: 1  Warnings: 0

			MariaDB [(none)]> flush privileges;
			Query OK, 0 rows affected (0.00 sec)
	
		验证：
			停止：
				[root@kongd ~]# yum install psmisc -y
				[root@kongd ~]# killall -9 mysqld
				mysqld: no process found
				
			启动：
				[root@kongd ~]# systemctl start mariadb
				[root@kongd ~]# mysql -uroot -p123456 -e 'show databases;'
				+--------------------+
				| Database           |
				+--------------------+
				| information_schema |
				| mysql              |
				| performance_schema |
				+--------------------+

1、表：table1 （FId，Fclass，Fscore），用最高效最简单的SQL列出各班成绩最高的列表，显示班级、成绩两个字段。

select Fclass,max(Fscore) from table1 group by Fclass;

select * from db1.abc order by db1.abc.uid desc limit 0,20; 

使用mysqldump备份
备份test库：mysqldump -uroot -p  -B test > test.sql
恢复test库：mysql -uroot -p < test.sql

select insert_time,count(*) from submit_message_send_histroy_201503 group by insert_time;

show open tables where In_use > 0;

update monitor_info set monitor_command="info01.log" where monitor_command="info.log";

根据题目条件，写出相应的命令：

学生表：Student (Sno, Sname, Ssex , Sage,  Sdept)
               学号，姓名，性别，年龄，所在系   Sno为主键
课程表：Course  (Cno,   Cname,)
               课程号，课程名    Cno为主键
学生选课表：SC (Sno,   Cno,     Score)
               学号，课程号，成绩   Sno,Cno为主键

为保证答案一致性，表中添加数据如下：
Student表插入数据
INSERT INTO Student VALUES (1002, ‘李四’, ‘男’, 20, ‘英语’);
INSERT INTO Student VALUES (1003, ‘王五’, ‘男’, 22, ‘计算机’);
INSERT INTO Student VALUES (1004, ‘赵六’, ‘女’, 20, ‘英语’);
INSERT INTO Student VALUES (1005, ‘钱七’, ‘男’, 21, ‘计算机’);
INSERT INTO Student VALUES (1006, ‘孙八’, ‘男’, 20, ‘计算机’);
INSERT INTO Student VALUES (1007, ‘胡九’, ‘女’, 19, ‘计算机’);
INSERT INTO Student VALUES (1008, ‘武十’, ‘男’, 20, ‘计信管’);

Course表插入数据
INSERT INTO Course VALUES (1, ‘英语’);
INSERT INTO Course VALUES (2, ‘高数’);
INSERT INTO Course VALUES (3, ‘计算机’);
INSERT INTO Course VALUES (4, ‘单片机’);
INSERT INTO Course VALUES (5, ‘java’);
INSERT INTO Course VALUES (6, ‘MySQL’);
INSERT INTO Course VALUES (7, ‘Linux’);
INSERT INTO Course VALUES (8, ‘C++’);

SC表插入数据
INSERT INTO SC VALUES (1001, 2, 45);
INSERT INTO SC VALUES (1001, 3, 85);
INSERT INTO SC VALUES (1001, 4, 74);
INSERT INTO SC VALUES (1002, 1, 90);
INSERT INTO SC VALUES (1003, 1, 54);
INSERT INTO SC VALUES (1003, 2, 62);
INSERT INTO SC VALUES (1003, 3, 81);
INSERT INTO SC VALUES (1003, 4, 80);
INSERT INTO SC VALUES (1003, 5, 72);
INSERT INTO SC VALUES (1003, 6, 88);
INSERT INTO SC VALUES (1003, 7, 77);
INSERT INTO SC VALUES (1003, 8, 67);
INSERT INTO SC VALUES (1004, 1, 92);
INSERT INTO SC VALUES (1005, 1, 53);
INSERT INTO SC VALUES (1005, 4, 63);
INSERT INTO SC VALUES (1005, 7, 77);
INSERT INTO SC VALUES (1006, 1, 70);
INSERT INTO SC VALUES (1006, 2, 82);
INSERT INTO SC VALUES (1006, 4, 78);
INSERT INTO SC VALUES (1006, 6, 68);
INSERT INTO SC VALUES (1006, 8, 55);
INSERT INTO SC VALUES (1007, 2, 76);
INSERT INTO SC VALUES (1007, 4, 90);
INSERT INTO SC VALUES (1007, 6, 89);
INSERT INTO SC VALUES (1008, 1, 81);
INSERT INTO SC VALUES (1008, 2, 48);

1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。

创建student表
create table student(sno int(10) primary key,sname varchar(20) unique,ssex varchar (10),ssage int(10),sdept varchar(20) default '计算机',check(ssex in ('男','女'))); 

创建course表
create table course(cno int(10) primary key,cname varchar(16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建sc表
create table sc(sno int(10),cno int(10), score int(10),primary key (sno,cno),foreign key(sno) references student(sno),foreign key(cno) references course(cno));

2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。

alter table student alter column sage smallint;

3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。

create unique index sc_index on sc(sno asc,cno asc);

4.向student表添加一条纪录：200201,张三，男，21，计算机。

INSERT INTO `Student` VALUES (200201, '张三', '男', 21, '计算机');

5.选修了2号课程且成绩低于70的的学生每人成绩增加5分。

update sc set score=score+5 where cno=2 and score<=70;

6.删除选修了课程名称为“单片机”的学生的选课成绩记录。

 delete from sc where cno=(select cno from course where cname='单片机');

7.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。

create view stu_info as  select student.sname,student.ssex,course.cno,sc.score from student,sc,course where student.sno=sc.sno and sc.cno=course.cno;

8.查询不及格学生的姓名。

 select sname,score from sc,student where sc.sno=student.sno and score<60;

9.查询选修四门以上课程的学生学号。

select sno from (select sno,count(*) num from sc group by sno)t where t.num>=4;

10.查询2号课程的最高分的学生的姓名。

select sname from student where sno=(select sno from (select sno,max(score) from sc where cno=2)t);