本文共 9256 字，大约阅读时间需要 30 分钟。

Python编程

1. 设计一个装饰器函数，如果被装饰的函数返回字符串则将字符串每个单词首字母大写。（10分）

   def titlize_string(func):    @wraps(func)    def wrapper(*args, **kwargs):        result = func(*args, **kwargs)        if isinstance(result, str):            result = result.title()        return result    return wrapper

2. 有N个人（编号从1到N）围成一圈从编号为1的人开始依次报数，报到M的人出列，下一个人重新从1开始报数，直到留下最后一个人。输入N和M，求出最后一个人的编号。（5分）

   n = int(input('n = '))m = int(input('m = '))persons = [x for x in range(1, n + 1)]while len(persons) > 1:    n = len(persons)    index = m % n if m > n else m    if index > 0:        persons = persons[index:] + persons[:index - 1]    else:        persons = persons[:n - 1]print(persons[0])

3. 写一个函数，传入的参数是一个列表（列表中的元素可能也是一个列表），返回传入的列表有多少层嵌套（最大嵌套深度）。（10分）

   def calc_nested_level(items):    if type(items) == list:        max_level = 1        for item in items:            curr_level = calc_nested_level(item)            max_level = max(max_level, curr_level + 1)        return max_level    return 0

4. 编写一个函数判断一个正整数是不是“快乐数”。“快乐数”是一个正整数，每一次将该数替换为它每个位置上的数字的平方和，然后重复这个过程，如果数字变成了1，这个数就是快乐数，如果无限循环始终变不到1，这个数就不是快乐数。（10分）

   def is_happy(num):    temp = set()    while num not in temp:        temp.add(num)        total = 0        while num > 0:            total += (num % 10) ** 2            num //= 10        if total == 1:            return True        num = total    return False

5. 写一个函数，传入一个有若干个整数的列表，该列表中某个元素出现的次数超过了50%，返回这个元素，请将程序的执行效率考虑进去。（5分）

   def find_most_popular(items):    counter, temp = 0, None    for item in items:        if counter == 0:            counter = 1            temp = item        else:            if temp == item:                counter += 1            else:                counter -= 1    return temp

6. 写一个函数，自行实现Python内置函数max的功能，尽可能跟内置的max函数一模一样。（10分）

   def my_max(*args, key=None, default=None):    """    获取可迭代对象中最大的元素或两个及以上实参中最大的元素    :param args: 一个可迭代对象或多个元素    :param key: 提取用于元素比较的特征值的函数，默认为None    :param default: 如果可迭代对象为空则返回该默认值，如果没有给默认值则引发ValueError异常    :return: 返回可迭代对象或多个元素中的最大元素    """    if len(args) == 1 and len(args[0]) == 0:        if default:            return default        else:            raise ValueError('max() arg is an empty sequence')    items = args[0] if len(args) == 1 else args    max_elem, max_value = items[0], items[0]    if key:        max_value = key(max_value)    for item in items:        value = item        if key:            value = key(item)        if value > max_value:            max_elem, max_value = item, value    return max_elem

MySQL数据库

2. 根据下面提供的四张二维表的结构，完成后面的题目。

   用户表：`tb_user`+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| userid   | int(11)     | NO   | PRI | NULL    | auto_increment || nickname | varchar(20) | NO   |     | NULL    | 用户昵称        || tel      | char(11)    | NO   |     | NULL    | 手机号          || cityid   | int(11)     | NO   | MUL | NULL    | 城市编号        || regdate  | date        | NO   |     | NULL    | 注册日期        |+----------+-------------+------+-----+---------+----------------+自行车表：`tb_bike`+-----------+-------------+------+-----+---------+----------------+| Field     | Type        | Null | Key | Default | Extra          |+-----------+-------------+------+-----+---------+----------------+| bikeid    | int(11)     | NO   | PRI | NULL    | auto_increment || statecode | int(11)     | NO   |     | 0       | 状态码          || broken    | tinyint(1)  | NO   |     | 0       | 是否损坏        |+-----------+-------------+------+-----+---------+----------------+城市表：`tb_city`+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| cityid   | int(11)     | NO   | PRI | NULL    | auto_increment || cityname | varchar(20) | NO   |     | NULL    | 城市名称        || ishot    | tinyint(1)  | NO   |     | 0       | 是否热门城市    |+----------+-------------+------+-----+---------+----------------+用车记录表：`tb_record`+-----------+----------+------+-----+---------+----------------+| Field     | Type     | Null | Key | Default | Extra          |+-----------+----------+------+-----+---------+----------------+| recordid  | int(11)  | NO   | PRI | NULL    | auto_increment || userid    | int(11)  | NO   | MUL | NULL    | 用户编号        || bikeid    | int(11)  | NO   | MUL | NULL    | 自行车编号      || begintime | datetime | NO   |     | NULL    | 开始时间        || endtime   | datetime | YES  |     | NULL    | 结束时间        || payway    | int(11)  | YES  |     | NULL    | 支付方式        || cost      | float    | YES  |     | NULL    | 费用            |+-----------+----------+------+-----+---------+----------------+

   • 说出“用户”、“自行车”、“城市”这三个实体之间的关系以及关系的重数。（5分）

     用户 - 自行车：使用关系，多对多用户 - 城市：从属关系，多对一

   • 写出创建上述二维表的建表语句。（10分）

     use exam2;create table `tb_city`(	`cityid` int(11) not null auto_increment,	`cityname` varchar(20) not null comment '热门城市',	`ishot` tinyint(1) not null default 0 COMMENT '是否热门城市',	primary key (`cityid`));create table `tb_user`( `userid` int(11) not null auto_increment, `nickname` varchar(20) not null comment '用户昵称', `tel` char(11) not null comment '手机号', `cityid` int(11) not null comment '城市编号', `regdate` date not null comment '注册日期', primary key (`userid`), foreign key (`cityid`) references tb_city(`cityid`))engine=INNODB;create table `tb_bike`(	`bikeid` int (11) not null auto_increment,	`statecode` int (11) not null COMMENT '状态码',	`broken` TINYINT(1) not null comment '是否损坏',	PRIMARY key (`bikeid`));create table `tb_record`(	`recordid` int(11) not null auto_increment,	`userid` int(11) not null comment '用户编号',	`bikeid` int(11) not null,	`begintime` datetime not null,	`endtime` datetime,	`payway` int(11),	`cost` float,	primary key (`recordid`),	foreign key (`userid`) references tb_user(`userid`),	foreign key (`bikeid`) references tb_bike(`bikeid`));

     答案：

     create table `tb_city`(    `cityid` integer not null auto_increment,    `cityname` varchar(20) not null,    `ishot` boolean not null default 0,    primary key (`cityid`));create table `tb_user`(    `userid` integer not null auto_increment,    `nickname` varchar(50) not null,    `tel` char(11) not null,    `cityid` integer not null,    `regdate` date not null,    primary key (`userid`),    foreign key (`cityid`) references tb_city (`cityid`));create table `tb_bike`(    `bikeid` integer not null auto_increment,    `statecode` integer not null default 0,    `broken` bit not null default 0,    primary key (`bikeid`));create table `tb_record`(    `recordid` integer not null auto_increment,    `userid` integer not null,    `bikeid` integer not null,    `begintime` datetime not null,    `endtime` datetime,    `payway` integer,    `cost` float,    primary key (`recordid`),    foreign key (`userid`) references tb_user (`userid`),    foreign key (`bikeid`) references tb_bike (`bikeid`));

   • 写出查询每个城市各有多少注册的共享单车用户的SQL语句。（10分）

     select cityname,total from (select cityid,count(userid) as total from tb_user group by cityid) t1inner join tb_city t2on t1.cityid=t2.cityid;

     答案：

     select cityname, total from (select cityid, count(cityid) as total from tb_user group by cityid) t1 inner join tb_city t2 on t1.cityid=t2.cityid;

   • 写出查询使用共享单车次数最多的用户的昵称及其所在城市的SQL语句。（10分）

     select nikename,cityname from (select userid,count(userid)) as total from tb_record group by userid  having total=(select max(total) from (select userid,count(userid)) as total group by userid) t1	)) t2 inner join tb_user as t3on t2.userid=t3.useridinner join tb_city as t4 ont3.cityid=t4.cityid;

     答案：

     select nickname, cityname from (select userid, count(userid) as total from tb_record group by userid having total=(select max(total) from (select userid, count(userid) as total from tb_record group by userid) t1)) t2 inner join tb_user as t3 on t2.userid=t3.userid inner join tb_city as t4 on t3.cityid=t4.cityid;

   • 写出查询尚无使用记录的共享单车的编号及其是否损坏的SQL语句。（5分）

     select bikeid,broken from tb_bike t1 where not EXISTS (select 'x' from tb_record t2 where t1.bikeid=t2.bikeid);

     答案:

     select bikeid, broken from tb_bike t1 where not exists (select 'x' from tb_record t2 where t1.bikeid=t2.bikeid);

3. 在MySQL数据库中有名为tb_temp的表，请写出能查询出如下所示结果的SQL。（10分）

   tb_temp表：			   	      查询结果：+------------+----------+		+------------+------+------+| mdate      | mresult  |		| mdate      | 胜   | 负   |+------------+----------+		+------------+------+------+| 2017-04-09 |    胜    |	   | 2017-04-09 |  2   |  2   || 2017-04-09 |    胜    |	   | 2017-04-10 |  2   |  1   || 2017-04-09 |    负    |	   +------------+------+------+| 2017-04-09 |    负    || 2017-04-10 |    胜    || 2017-04-10 |    负    || 2017-04-10 |    胜    |+------------+----------+

   select mdate,		 sum(case mresult when "胜" 1 else 0 end) as 胜，		 sum(case mresult when "负" 1 else 0 end) as 负，from tb_temp group by mdate;

   select 	mdate,	sum(case mresult when '胜' then 1 else 0 end) as 胜,	sum(case mresult when '负' then 1 else 0 end) as 负from tb_temp group by mdate;

   题目：

   
   单纯考虑解此题目：

select  	username as 用户名,一月份,二月份,三月份from(	select		username,		sum(case month when '一月份' then ye else 0 end)as 一月份,		sum(case month when '二月份' then ye else 0 end)as 二月份,		sum(case month when '三月份' then ye else 0 end)as 三月份	from tb_table1 t1 inner join tb_table2 t2	on t1.userid = t2.userid	group by userid )t1;

转载地址：http://pkbvn.baihongyu.com/