运维第4天

南天软件：
1、已知库表t_test中有以下记录
     1 2005-05-09 优      98.1
     2 2005-05-09 优      89.5
     3 2005-05-09 优      86.5
     4 2005-05-09 良      77.1
     5 2005-05-10 良      75.3
     6 2005-05-10 良      71.1
     7 2005-05-10 及格   65.2
     1）请写出构建t_test的SQL语句
     2）根据上述记录内容，请写出插入t_test表的SQL语句（写一条插入语句即可）
     3）用一条语句，从表中查出优和良，各多少条记录，平均分各是多少？
     4）把及格记录的日期改为2005-05-11

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 values(8,'2005-05-10','良',72.5);
3）mysql> select degree,count(*),avg(score) from t_test where degree='优' or degree='良' group by degree;
+--------+----------+------------+
| degree | count(*) | avg(score) |
+--------+----------+------------+
| 优     |        3 |   91.36667 |
| 良     |        4 |   74.00000 |
+--------+----------+------------+
2 rows in set (0.00 sec)
4）update t_test set time='2005-05-11' where degree='及格';
mysql> select * from t_test;
+----+------------+--------+-------+
| id | time       | degree | score |
+----+------------+--------+-------+
|  1 | 2005-05-09 | 优     |  98.1 |
|  2 | 2005-05-09 | 优     |  89.5 |
|  3 | 2005-05-09 | 优     |  86.5 |
|  4 | 2005-05-09 | 良     |  77.1 |
|  5 | 2005-05-10 | 良     |  75.3 |
|  6 | 2005-05-10 | 良     |  71.1 |
|  7 | 2005-05-11 | 及格   |  65.2 |
|  8 | 2005-05-10 | 良     |  72.5 |
+----+------------+--------+-------+
8 rows in set (0.00 sec)

青牛软件：

7：B
8：C
9：A
10：A

简述SQL语句中Truncate和delete的区别

区别1：truncate是删除全部数据，而delete可以删除部分数据也可以删除全部数据。
区别2：truncate删除数据不可回滚，而delete删除数据可以回滚
区别3：truncate的执行速度比delete快
区别4：执行truncate会释放空间，能够降低最高水位线(HWM)，而执行delete不会释放空间，不能降低水位线，并且用delete删除，表的大小没有改变。

填空题：
1：写出一条SQL语句：取出表A中满足时间2006年1月1日至2006年1月31日的记录（时间字段为time）

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

3：在SQL中，建立，修改和删除数据库中基本表结构的命令分别为_____、_____和_____命令。

create、alter、drop

4：mysql数据库的默认端口是_____。

3306端口

1：A
2：C
3：C
4：BD
5：D

汉邦高科：
1、mysql备份命令？mysql如何对smart用户授权访问，密码为123456，请写出命令？

2、简单说一下mysql忘记密码后改如何处理？

使用mysqldump备份
		备份所有库:mysqldump -uroot -p -A -B > all.db.sql
		备份student库：mysqldump -uroot -p  -B student > student.sql
		备份表：mysqldump -uroot -p  student score > student_score.sql

	grant all on * to smart  identified by "123456";
		恢复：
			方法1： mysql -uroot -p < 备份文件
			方法2：进入数据库 source 备份文件

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

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

2：mysql中，数据库db1中表abc，将abc的uid进行倒序排序，并只取出前20行

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

3：如何使用mysql命令进行备份和恢复？以test库为例，创建一个备份，并再用此备份进行恢复。

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

4：Mysql中，根据需求写出sql语句：
     信息数据表submit_message_send_history_201503
     字段：insert_time为时间，charge_count为计费条数（每条信息计费条数不同，可能为1或大于1）
     求：统计并展示出每天的计费条数

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

5：如何查看mysql是否锁表？

show open tables where In_use > 0;

6：数据表monitor_info，将monitor_command字段中的“info.log”字符串，替换为“info01.log”，写出替换的完整sql语句

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为主键

1.用SQL语句创建学生表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 ('男','女'))); 

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

create table sc(sno int(10),cno int(10), score int(10),primary key (sno,cno),foreign key(sno) references student1(sno),foreign key(cno) references course(cno));

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

alter table student1 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 student1.sname,student1.ssex,course.cno,sc.score from student1,sc,course where student1.sno=sc.sno and sc.cno=course.c

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

select sname,score from sc,student1 where sc.sno=student1.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 student1 where sno=(select sno from (select sno,max(score) from sc where cno=2)t);

为保证答案一致性，表中添加数据如下：

insert into Student
values(1001,‘张三’,‘男’,21,‘计算机’);

准备插入数据
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, ‘计信管’);

mysql> select * from Student;
±-----±-------±-----±------±----------+
| Sno | Sname | Ssex | Ssage | Sdept |
±-----±-------±-----±------±----------+
| 1001 | 张三 | 男 | 21 | 计算机 |
| 1002 | 李四 | 男 | 20 | 英语 |
| 1003 | 王五 | 男 | 22 | 计算机 |
| 1004 | 赵六 | 女 | 20 | 英语 |
| 1005 | 钱七 | 男 | 21 | 计算机 |
| 1006 | 孙八 | 男 | 20 | 计算机 |
| 1007 | 胡九 | 女 | 19 | 计算机 |
| 1008 | 武十 | 男 | 20 | 计信管 |
±-----±-------±-----±------±----------+

CREATE TABLE Course (
Cno int(4) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘课程号’,
Cname varchar(16) DEFAULT NULL COMMENT ‘课程名’,
PRIMARY KEY (Cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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++’);

mysql> select * from Course;
±----±----------+
| Cno | Cname |
±----±----------+
| 1 | 英语 |
| 2 | 高数 |
| 3 | 计算机 |
| 4 | 单片机 |
| 5 | java |
| 6 | MySQL |
| 7 | Linux |
| 8 | C++ |
±----±----------+
8 rows in set (0.00 sec)

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);

mysql> select * from SC;
±-----±----±------+
| Sno | Cno | Score |
±-----±----±------+
| 1001 | 2 | 45 |
| 1001 | 3 | 85 |
| 1001 | 4 | 74 |
| 1001 | 6 | 80 |
| 1001 | 7 | 88 |
| 1002 | 1 | 90 |
| 1003 | 1 | 54 |
| 1003 | 2 | 62 |
| 1003 | 3 | 81 |
| 1003 | 4 | 80 |
| 1003 | 5 | 72 |
| 1003 | 6 | 88 |
| 1003 | 7 | 77 |
| 1003 | 8 | 67 |
| 1004 | 1 | 92 |
| 1005 | 1 | 53 |
| 1005 | 4 | 63 |
| 1005 | 7 | 77 |
| 1006 | 1 | 70 |
| 1006 | 2 | 82 |
| 1006 | 4 | 78 |
| 1006 | 6 | 68 |
| 1006 | 8 | 55 |
| 1007 | 2 | 76 |
| 1007 | 4 | 90 |
| 1007 | 6 | 89 |
| 1008 | 1 | 81 |
| 1008 | 2 | 48 |
±-----±----±------+
28 rows in set (0.00 sec)