Python编程
-
设计一个装饰器函数,如果被装饰的函数返回字符串则将字符串每个单词首字母大写。(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
-
有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])
-
写一个函数,传入的参数是一个列表(列表中的元素可能也是一个列表),返回传入的列表有多少层嵌套(最大嵌套深度)。(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
-
编写一个函数判断一个正整数是不是“快乐数”。“快乐数”是一个正整数,每一次将该数替换为它每个位置上的数字的平方和,然后重复这个过程,如果数字变成了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
-
写一个函数,传入一个有若干个整数的列表,该列表中某个元素出现的次数超过了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
-
写一个函数,自行实现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数据库
-
根据下面提供的四张二维表的结构,完成后面的题目。
用户表:`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) t1 inner join tb_city t2 on 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 t3 on t2.userid=t3.userid inner join tb_city as t4 on t3.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);
-
-
在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;