Database

1 Insert

//添加一条数据
Insert into Users(name,email) Values('AA','A@umich.edu')

2 Delete

//只删除符合where条件的数据

DELETE FROM User WHERE email = 'ted@umich.edu'

3 Update

//将符合where条件的数据的name变量更改为AA
UPDATE Users SET name = 'AA' where email ='A@umich.edu'

4 Select

//查找所有数据
//查找符合where条件的数据
SELECT*FROM Users
SELECT*FROM Users WHERE email='A@umich.edu'

//order用法,搜索所有email的数据
SELECT*FROM Users OEDER BY email

一个小项目

import sqlite3

conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()

cur.execute('''
            DROP TABLE IF EXISTS Counts''')

cur.execute('''CREATE TABLE Counts(email TEXT,count INTEGER)''')

fh = open('short.txt')

for line in fh:
    if not line.startswith('From: '):continue
    pieces = line.split()
    email = pieces[1]
    cur.execute('SELECT count FROM Counts Where email = ?',(email,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (email,count)
        VALUES (?,1)''',(email,))
    else:
        cur.execute('Update Counts SET count = count +1 WHERE email =?',(email,))
    conn.commit()

sqlstr = 'SELECT email,count FROM Counts ORDER BY count DESC LIMIT 10'

for row in cur.execute(sqlstr):
    print (str(row[0]),row[1])
cur.close()