连接
import sqlite3
con = sqlite3.connect(":memory:")
c = con.cursor()
# Create table
c.execute('''CREATE TABLE COMPANY
(ID integer, NAME text, AGE integer, ADDRESS text, SALARY real)''')
# Larger example that inserts many records at a time
purchases = [(1,'Paul',32,'California',20000.0),
(2,'Allen',25,'Texas',15000.0),
(3,'Teddy',23,'Norway',20000.0),
(4,'Mark',25,'Rich-Mond',65000.0),
(5,'David',27,'Texas',85000.0),
(6,'Kim',22,'South-Hall',45000.0),
(7,'James',24,'Houston',10000.0)]
c.executemany('INSERT INTO COMPANY VALUES (?,?,?,?,?)', purchases)
# Create table
c.execute('''CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);''')
# Larger example that inserts many records at a time
purchases = [(1, 'IT Billing', 1 ),
(2, 'Engineering', 2 ),
(3, 'Finance', 7 )]
c.executemany('INSERT INTO DEPARTMENT VALUES (?,?,?)', purchases)
# Save (commit) the changes
con.commit()
# 显示所有记录
c.execute("SELECT * FROM COMPANY;")
for row in c:
print(row)
print()
# 显示所有记录
c.execute("SELECT * FROM DEPARTMENT;")
for row in c:
print(row)
print("
连接(JOIN)")
c.execute("SELECT * FROM COMPANY,DEPARTMENT;")
for row in c:
print(row)
print("
交叉连接(CROSS JOIN)")
#c.execute("SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;")
c.execute("SELECT * FROM COMPANY CROSS JOIN DEPARTMENT;")
for row in c:
print(row)
print("
内连接(INNER JOIN)")
c.execute("SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;")
for row in c:
print(row)
print("
左外连接(LEFT OUTER JOIN)")
c.execute("SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;")
for row in c:
print(row)
子查询
import sqlite3
'''子查询'''
con = sqlite3.connect(":memory:")
c = con.cursor()
# Create table
c.execute('''CREATE TABLE COMPANY
(ID integer, NAME text, AGE integer, ADDRESS text, SALARY real)''')
# Larger example that inserts many records at a time
purchases = [(1,'Paul',32,'California',20000.0),
(2,'Allen',25,'Texas',15000.0),
(3,'Teddy',23,'Norway',20000.0),
(4,'Mark',25,'Rich-Mond',65000.0),
(5,'David',27,'Texas',85000.0),
(6,'Kim',22,'South-Hall',45000.0),
(7,'James',24,'Houston',10000.0)]
c.executemany('INSERT INTO COMPANY VALUES (?,?,?,?,?)', purchases)
# Save (commit) the changes
con.commit()
# ====================================================================================
# SELECT 语句中的子查询使用
# ====================================================================================
print('='*30)
print('SELECT 语句中的子查询使用')
print('='*30)
c.execute("SELECT * "
"FROM COMPANY "
"WHERE ID IN (SELECT ID "
"FROM COMPANY "
"WHERE SALARY > 45000);")
for row in c:
print(row)
# ====================================================================================
# INSERT 语句中的子查询使用
# ====================================================================================
print('='*30)
print('INSERT 语句中的子查询使用')
print('='*30)
# Create table
c.execute('''CREATE TABLE COMPANY_BKP
(ID integer, NAME text, AGE integer, ADDRESS text, SALARY real)''')
c.execute("INSERT INTO COMPANY_BKP "
"SELECT * FROM COMPANY "
"WHERE ID IN (SELECT ID "
"FROM COMPANY);")
c.execute("SELECT * FROM COMPANY_BKP")
for row in c:
print(row)
# ====================================================================================
# UPDATE 语句中的子查询使用
# ====================================================================================
print('='*30)
print('UPDATE 语句中的子查询使用')
print('='*30)
# 子查询
c.execute("UPDATE COMPANY_BKP "
"SET SALARY = SALARY * 0.50 "
"WHERE AGE IN (SELECT AGE FROM COMPANY_BKP "
"WHERE AGE >= 27 );")
c.execute("SELECT * FROM COMPANY_BKP")
for row in c:
print(row)
# ====================================================================================
# DELETE 语句中的子查询使用
# ====================================================================================
print('='*30)
print('DELETE 语句中的子查询使用')
print('='*30)
# 子查询
c.execute("DELETE FROM COMPANY_BKP "
"WHERE AGE IN (SELECT AGE FROM COMPANY_BKP "
"WHERE AGE > 27 );")
c.execute("SELECT * FROM COMPANY_BKP")
for row in c:
print(row)
c.execute("SELECT tbl_name FROM :memory: WHERE type = 'table';")