1. 已知文件 a.txt 和文件 b.txt 内容如下

a.txt

10.212.36.195    BMC
10.211.172.41    BMC
10.212.36.94      CA
10.212.36.155   OTA

b.txt

10.212.36.195
10.212.36.76
10.212.36.94

要求：
两个文件的行数不定， 请编写命令或者脚本，到的 b.txt 文件中的 IP 对应文件

with open('a.txt') as f:
    a_li = [line.split() for line in f.read().split('\n')[:-1]]


with open('b.txt') as bf:
    b_li = bf.read().split('\n')[:-1]


for aip, w in a_li:
    for bip in b_li:
        if aip == bip:
            print(aip, w)
        else:
            print("Not found!")

2.

a

sed -ri '/Jul/s/^/#' test
或者
sed -rn '/ABC/ s/^/#/p' a.txt

2B---sed -ri '/NetworkManage/ s/date=2012/date=2020/' test

3.

6.

image.png

下面演示的示例 表名 均为小写，表名是区分大小写的

insert into students values("990027",  "胡伟"  ,  22,   "男",   "湖南"),
    ("990652",  "张春明",   24,  "男",   "河北"),
    ("990668",  "王毅"  ,   22,  "女",   "四川"),
    ("990674",  "丁晓晨" ,  24,  "男",   "黑龙江"),
    ("990676",  "合正僧" ,  23,  "男",   "湖南"),
    ("990684",  "刘文革",   24,  "女",   "辽宁"),
    ("991091",  "程会军",   23,  "男",   "山西")

create table enrolls(
 id int not null auto_increment primary key, sno char(6),cno char(2), grade tinyint(3));

insert into enrolls(sno,cno,grade) 
values("990027","c1", 90 ),
("990027","c3", 95 ),
("990027","c4", 83 ),
("990652","c1", 88 ),
("990652","c4", 84 ),
("990668","c3", 90 ),
("990674","c2", 77 ),
("990676","c3", 90 ),
("990684","c3", 85 ),
("990684","c1", 82 ),
("991091","c2", 93 );

create table courses( 
id int not null auto_increment primary key, 
cno char(2), 
cname varchar(6),
credit tinyint(1));

insert into courses(cno, cname,credit) values
("c1","数据库", 3),
("c2","数据结构", 3),
("c3","操作系统", 4),
("c4","软件工程", 3);

1. 统计年龄小于22 岁的人数

select AGE,count(AGE) from students  where AGE <=22 group by AGE;

where 语句必须在 group by 语句前面

报错记录和解决办法

MySQL [students]> select students.cname,count(enrolls.sno)  from enrolls join students on students.cno=enrolls.sno group by enrolls.sno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'students.students.cname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
MySQL [students]> select students.cname,count(enrolls.sno)  from enrolls join students on students.cno=enrolls.sno group by enrolls.sno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'students.students.cname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
MySQL [students]> set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

2. 找出每个学生的平均成绩和所学课程门数

MySQL [students]> select students.cname,avg(enrolls.grade),count(enrolls.sno) as course_num from enrolls join students on students.cno=enrolls.sno group by enrolls.sno;
+-----------+--------------------+------------+
| cname     | avg(enrolls.grade) | course_num |
+-----------+--------------------+------------+
| 胡伟      | 89.3333            |          3 |
| 张春明    | 86.0000            |          2 |
| 王毅      | 90.0000            |          1 |
| 丁晓晨    | 77.0000            |          1 |
| 合正僧    | 90.0000            |          1 |
| 刘文革    | 83.5000            |          2 |
| 程会军    | 93.0000            |          1 |
+-----------+--------------------+------------+```

3. 学生张春明在数据库课考试中作弊，改课成绩应作零分处理

方式一

update enrolls set enrolls.GRADE=0 
    where enrolls.SNO in (select SNO from students where SNAME='张春明')
    and
        enrolls.CNO in (select CNO from courses where CNAME='数据库');

方式二

MySQL [students]> update enrolls,students,courses
    -> set enrolls.grade=0
    -> where enrolls.sno=students.sno
    -> and enrolls.cno=courses.cno
    -> and students.sname="张春明"
    -> and courses.cname="数据库";

方式三

update enrolls join students 
on enrolls.sno=students.sno
 join courses
 on enrolls.cno=courses.cno
 set enrolls.grade=0 
where students.sname="张春明"  and courses.cname="数据库";