slqite3练习

 

连接

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("\n连接（JOIN）") c.execute("SELECT * FROM COMPANY,DEPARTMENT;") for row in c: print(row) print("\n交叉连接（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("\n内连接（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("\n左外连接（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';")

 

转载于:https://www.cnblogs.com/hhh5460/p/5205946.html

相关资源：JAVA上百实例源码以及开源项目