python下SQlite数据库练习实例

python下SQlite数据库练习实例

# -*- coding:utf-8 -*-

import sqlite3

connect = sqlite3.connect('test.db')

cursor = connect.cursor()

# 1,创建一个学生成绩表 Grades，学号 姓名 成绩 名次

sql = 'CREATE TABLE Grades(num INTEGER PRIMARY KEY, name TEXT, grade INTEGER, rank INTEGER )'

# cursor.execute(sql)

# 2，往表中插入10数据

sql = 'INSERT INTO Grades(num,name,grade,rank) VALUES(8,"吼吼",50,89)'

# cursor.execute(sql)

# connect.commit()

# 3，查询成绩大于60分的人的名字

sql = 'SELECT name,grade FROM Grades WHERE grade > 60'

# rs = cursor.execute(sql)

# for x in rs:

# print(x)

# 4，查询成绩小于60分的人的个数

sql = 'SELECT COUNT(*) FROM Grades WHERE grade < 60'

# rs = cursor.execute(sql)

# for x in rs:

# print(x)

# connect.commit()

# 5，把成绩小于60分的人的成绩修改为60分

sql = 'UPDATE Grades SET grade = 60 WHERE grade < 60'

# cursor.execute(sql)

# connect.commit()

# 6，删除所有学号小于100的人的信息

sql = 'DELETE FROM Grades WHERE num < 100'

# cursor.execute(sql)

# connect.commit()

# 7，查询姓“张”的人的所有信息

sql = 'SELECT * FROM Grades WHERE name LIKE "张%"'

# rs = cursor.execute(sql)

# for x in rs:

# print(x)

# connect.commit()

# 8，查询所有人的成绩，按照成绩从高到低排列

sql = 'SELECT grade FROM Grades ORDER BY grade DESC'

# rs = cursor.execute(sql)

# for x in rs:

# print(x)

# connect.commit()

# 9，查询所有人的成绩，按照名次从小到大排列

sql = 'SELECT rank FROM Grades ORDER BY rank ASC'

# rs = cursor.execute(sql)

# for x in rs:

# print(x)

# connect.commit()

# 关闭游标

cursor.close()

# # 关闭数据库连接

connect.close()