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';")