python 标准库 sqlite3 介绍(二）

import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()#创建游标
#SQL 语句(包含SQL 关键字、表名、列名）大小写不敏感
 #创建table employee
c.execute('''CREATE TABLE employee
          (ID INTEGER PRIMARY KEY, name TEXT , age INTEGER, address TEXT, salary REAL )''')
emp_records =[ [1,'Paul', 32, 'California', 25000.0],
             [2,'Allen', 25, 'California', 20000.0],
             [3,'Teddy', 23, 'Norway', 22000.0],
            [4,'Mark', 25, 'Rich-Mond', 25000.0],
            [5,'David', 27, 'Norway', 20000.0],
            [6,'Kim', 34, 'California', 65000.0],
           [7,'James', 25, 'Houston', 22000.0]]
# 增加多行数据
c.executemany("INSERT INTO employee VALUES (?, ?,?,?,?)", emp_records)

#LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配。
#这里有两个通配符与 LIKE 运算符一起使用，这些符号可以被组合使用。
#百分号（%）代表零个、一个或多个数字或字符。
#下划线（_）代表一个单一的数字或字符。
c.execute("SELECT name, salary  FROM employee WHERE name LIKE '%d%'")
print("名字（含'd'), 薪资:")
for row in c.fetchall():
    print(row)
print()
print("名字（第二个字母是'a'), 薪资:")
for row in c.execute("SELECT name, salary  FROM employee WHERE name LIKE '_a%'"):
    print(row)

import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()#创建游标
#SQL 语句(包含SQL 关键字、表名、列名）大小写不敏感
 #创建table employee
c.execute('''CREATE TABLE employee
          (ID INTEGER PRIMARY KEY, name TEXT , age INTEGER, address TEXT, salary REAL )''')
emp_records =[ [1,'Paul', 32, 'California', 25000.0],
             [2,'Allen', 25, 'Texas', 20000.0],
             [3,'Teddy', 23, 'Norway', 22000.0],
            [4,'Mark', 25, 'Rich-Mond', 25000.0],
            [5,'David', 27, 'Texas', 45000.0],
            [6,'Kim', 34, 'South-Hall', 65000.0],
           [7,'James', 25, 'Houston', 15000.0]]
# 增加多行数据
c.executemany("INSERT INTO employee VALUES (?, ?,?,?,?)", emp_records)

 #创建table department
c.execute('''CREATE TABLE department
             (id INTEGER PRIMARY KEY, dept TEXT , emp_ID INTEGER UNIQUE NOT NULL  )''')
dept_records= [[1, 'IT Billing', 1],
          [2, 'Engineerin', 2],
          [3, 'Finace', 6]]
c.executemany("INSERT INTO department VALUES (?, ?,?)", dept_records)
          
conn.commit()
  
c.execute("SELECT * FROM employee")
print("雇员信息:")
for row in c.fetchall():
    print(row)
c.execute("SELECT * FROM department")
print("部门信息:")
for row in c.fetchall():
    print(row)
print()
'''交叉连接（CROSS JOIN)
把第一个表的每一行与第二个表的每一行进行匹配(两个表的笛卡尔积）
如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。由于交叉连接（CROSS JOIN）
有可能产生非常大的表，或产生无意义的查询结果。使用时必须谨慎'''
c.execute("SELECT emp_ID, name, dept FROM employee  CROSS JOIN department")
print("CROSS JOIN")
print("雇员ID, 姓名，部门:")
for row in c.fetchall():
    print(row)
print()
'''内连接（INNER JOIN）
根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1
中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接
谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。内连接（INNER JOIN）是最常见
的连接类型，是默认的连接类型。INNER 关键字是可选的。'''
c.execute('''SELECT emp_ID, name, dept from employee  inner JOIN department
          on employee.ID = department.emp_id''')
print("INNER JOIN")
print("雇员ID, 姓名，部门:")
for row in c.fetchall():
    print(row)
print()
'''可以使用 USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：'''
c.execute('''SELECT * FROM employee  JOIN department USING (ID)''')
print("INNER JOIN(JOIN ... USING)")
print("雇员ID, 姓名，年龄，城市，薪资，部门，部门ID:")
for row in c.fetchall():
    print(row)
print()
'''自然连接（NATURAL JOIN）类似于 JOIN...USING，只是它会自动测试存在两个表中的每一列的值
之间相等值：'''
c.execute('''SELECT * FROM employee  NATURAL JOIN department''')
print("INNER JOIN(NATURAL JOIN)")
print("雇员ID, 姓名，年龄，城市，薪资，部门，部门ID:")
for row in c.fetchall():
    print(row)
print()
'''外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：
LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。外连接（OUTER JOIN）
声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。
最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个
表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。'''
c.execute('''SELECT emp_ID, name, dept from employee  LEFT OUTER JOIN department
          on employee.ID = department.emp_id''')
print("LEFT OUTER JOIN")
print("雇员ID, 姓名，部门:")
for row in c.fetchall():
    print(row)
print()
'''可以使用 USING 表达式声明外连接（OUTER JOIN）条件。这个表达式指定一个或多个列的列表：'''
c.execute('''SELECT * FROM Employee  LEFT OUTER JOIN department USING (ID)''')
print("LEFT OUTER JOIN(JOIN ... USING)")
print("雇员ID, 姓名，年龄，城市，薪资，部门，部门ID:")
for row in c.fetchall():
    print(row)
print()

'''UPDATE 查询用于修改表中已有的数据。
使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition]
'''
c.execute('''UPDATE employee SET salary =?  where salary< ?''', (22000, 22000))
conn.commit()
print("低的调高")
c.execute("select * from employee")
for row in c.fetchall():
    print(row)
print()
c.execute('''UPDATE employee SET salary = (select salary from employee)*1.10 ''')#可以和select嵌套使用
conn.commit()
print("普调")
c.execute("select * from employee")
for row in c.fetchall():
    print(row)  
print()

'''DELETE 用于删除表中已有的记录。
可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。
DELETE FROM table_name
WHERE [condition]   '''
print("删除ID 为7的记录")
c.execute('''DELETE from employee where id =7 ''')
#conn.commit()
c.execute("select * from employee")
for row in c.fetchall():
    print(row)
print()

print("回滚最近一次commit() 之后未commit的所有操作")
c.execute("ROLLBACK")
c.execute("select * from employee")
for row in c.fetchall():
    print(row)

'''DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
使用此命令时要特别注意，因为一旦一个表被删除，表中所有信息也将永远丢失。删库跑路...@^@
DROP TABLE COMPANY'''
c.execute('''DROP TABLE employee''')
conn.commit()
c.execute("select * from employee")
print(c.fetchone())
#报错 sqlite3.OperationalError: no such table: employee