sqlite查找结果的排序与限制

""" limit和order by

稍微成熟点的select语句
SELECT ... FROM table_name [WHERE condition]
[ORDER BY column_1, column_2, ...] [LIMIT count [OFFSET count]]

limit:限制查询结果的行数
order by:按照指定列排序查询结果

外键限制
foreign key (this_column_names) references table_name (reference_column_names)

the statistics of this file:
lines(count)    understand_level(h/m/l)    classes(count)    functions(count)    fields(count)
000000000078    ----------------------l    00000000000000    0000000000000001    ~~~~~~~~~~~~~
"""

import time
import sqlite3

__author__ = '与C同行'


def print_pretty_outcome(cursor):
    print('-' * 80)
    all_outcome = cursor.fetchall()
    for column_desc in cursor.description:
        print(f'{column_desc[0]:<20}', end='')
    print()
    for item in all_outcome:
        for cell in item:
            try:
                print(f'{cell:<20}', end='')
            except TypeError:
                cell = 'None'
                print(f'{cell:<20}', end='')
        print()
    print('-' * 80)
    print()


if __name__ == '__main__':
    print(f'当前时间:{time.ctime()}')
    conn = sqlite3.connect('learn.db')
    c = conn.cursor()

    c.execute('''create table salary_tb(
                     name text not null,
                     salary integer,
                     person_id integer,
                     foreign key(person_id) references report_tb(student_number) )''')
    c.executemany('insert into salary_tb values (?, ?, ?)',
              (('杨过', 10000, 1901),
               ('啊龙', 8000, 1902),
               ('雄儿', 8000, 1903),
               ('业儿', 6000, 1904),
               ('业儿', 5000, 1904),
               ))
    conn.commit()

    print('使用limit语句限制查询数据行数')
    c.execute('select * from salary_tb limit 2')
    print_pretty_outcome(c)

    print('limit和offset一起使用来偏移查询结果')
    c.execute('select * from salary_tb limit 2 offset 1')
    print_pretty_outcome(c)

    print('使用order by语句来排序查询结果')
    c.execute('select * from salary_tb order by salary limit 3')
    print_pretty_outcome(c)

    print('desc倒序排列')
    c.execute('select * from salary_tb order by salary desc')
    print_pretty_outcome(c)

    c.close()
    conn.close()

总结：limit
可以和offset
结合使用来偏移查询结果，order by
可以声明升序排序或者降序排序，完整的select
查询语句其实很长，想要完全掌握还需要逐渐熟悉。