##################################
#encoding=utf8 #
#version =py27, py33 #
#author =sanhe #
#date =2014-10-31 #
# #
# (\ (\ #
# ( -.-)o I am a Rabbit! #
# o_(")(") #
# #
##################################
"""PRIMARY KEYS关键字的用法
"""
from __future__ import print_function
from prettytable import from_db_cursor as fdc
import sqlite3
conn = sqlite3.connect("wc3_demo_db.db")
c = conn.cursor()
try: # 多重PRIMARY KEYS
c.execute("""CREATE TABLE multiple_pkey (address TEXT, zipcode TEXT, price INTEGER, PRIMARY KEY (address, zipcode) );""")
except:
pass
c.execute("INSERT INTO multiple_pkey VALUES (?,?,?)", ("1400 S", "22202", 1000))
c.execute("INSERT INTO multiple_pkey VALUES (?,?,?)", ("1400 S", "75000", 2000))
c.execute("SELECT * FROM multiple_pkey")
print(fdc(c))
#date =2014-10-31 #
# #
# (\ (\ #
# ( -.-)o I am a Rabbit! #
# o_(")(") #
# #
##################################
"""DISTINCT 关键字的用法
SQL SELECT DISTINCT Syntax
--------------------------
SELECT DISTINCT column_name,column_name
FROM table_name;
阅读完本脚本之后思考如下问题：
为什么DISTINCT语句不支持部分DISTINCT？能不能只让country DISTINCT，
而其他的列随机的输出？
"""
from __future__ import print_function
from prettytable import from_db_cursor as fdc
import sqlite3
conn = sqlite3.connect("wc3_demo_db.db")
c = conn.cursor()
c.execute("SELECT DISTINCT country FROM customers")
print(fdc(c))
c.execute("SELECT DISTINCT country, city FROM customers")
print(fdc(c)) # 是按照 country, city 两者共同的独立性。现在country里是不是有重复的了?
SELECT column_name(s) FROM table2;
SQL UNION ALL Syntax
--------------------
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
"""
from __future__ import print_function
from prettytable import from_db_cursor as fdc
import sqlite3
conn = sqlite3.connect("wc3_demo_db.db")
c = conn.cursor()
c.execute("""
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;""")
print(fdc(c))
c.execute("""
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
""")
print(fdc(c))
# #
# (\ (\ #
# ( -.-)o I am a Rabbit! #
# o_(")(") #
# #
##################################
"""DISTINCT 关键字的用法
SQL SELECT DISTINCT Syntax
--------------------------
SELECT DISTINCT column_name,column_name
FROM table_name;
阅读完本脚本之后思考如下问题：
为什么DISTINCT语句不支持部分DISTINCT？能不能只让country DISTINCT，
而其他的列随机的输出？
"""
from __future__ import print_function
from prettytable import from_db_cursor as fdc
import sqlite3
conn = sqlite3.connect("wc3_demo_db.db")
c = conn.cursor()
c.execute("SELECT DISTINCT country FROM customers")
print(fdc(c))
c.execute("SELECT DISTINCT country, city FROM customers")
print(fdc(c)) # 是按照 country, city 两者共同的独立性。现在country里是不是有重复的了?