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()
