面试题请求解答，谢谢老师

[786825435_0948]

[seveniruby (思寒)]

你也是真够懒的。。。好歹把哪道题也发下，别直接上图，你又不是一个都不会。。

[786825435_0948]

哇塞，居然可以得到校长垂青，好惊喜啊！但同时又好尴尬！因为万万没想到此情此景让我们相遇：实不相瞒，上面截出来的题都是我不会的

[xiaopeng (霍格沃兹_小鹏)]

2.1 select class,avg(english) from cust,mark where cust.studentno = mark.studentno group by class

[xiaopeng (霍格沃兹_小鹏)]

2.2 SELECT studentno,if(english is NULL,-1,english)english FROM cust LEFT JOIN mark on cust.studentno = mark.studentno

[Stone1688 (石头)]

import random

def getData():
    count = 0
    l = []
    while count != 20:
        data = random.randint(10000,99999)
        if len(set(list(str(data)))) <= 2:
            l.append(data)
            count += 1
    return l

# 验证生成 20 个随机数字
assert len(getData()) ==20
# 校验重复数字不大于 2
assert len(set(len(set(str(item))) for item in getData())) <=2

[Stone1688 (石头)]

select a.student_no ,ifnull(b.english,-1) as english from cust a
left join mark b on a.stutdent_no = b.student_no ;

[xiaopeng (霍格沃兹_小鹏)]

2.3 SELECT studentno,telno,address FROM cust WHERE address LIKE “%深圳%”
UNION
SELECT studentno,telno,address FROM cust WHERE telno LIKE “0755%”
有点强行用union的感觉

[xiaopeng (霍格沃兹_小鹏)]

3 多表的关联字段设计是否合理，字段是否有创建索引

[Stone1688 (石头)]

是的

[1013271554_1990]

import random
import re
import pytest


def get_data(count=20):
    '''
    生成20个由5位数字或字母构成的字符串
    数字最多不能超过2个
    :return: 随机验证码
    '''
    l = []
    while True:
        str = '1234567890qazxswedcvfrtgbnhyujmkiolpQAZXSWEDCVFRTGBNHYUJMKIOLP'
        code = ''
        data = random.sample(str, 5)
        k = 0
        for j in data:
            if j.isdigit():
                k += 1
        if k <= 2:
            for item in data:
                code += item
            l.append(code)
        if len(l) == count:
            break
    return l


@pytest.mark.parametrize('code', get_data())
def test_data(code):
    '''
    验证生成的字符串的确没有大于2个的数字
    :return:
    '''
    item = re.findall(r'\d+\.?\d*', code)
    count = 0
    for i in item:
        count += len(i)
    print(count)
    assert count <= 2