SQLite

http://www.runoob.com/sqlite/sqlite-tutorial.html

https://docs.python.org/3.6/library/sqlite3.html#connection-objects

#廖雪峰教程  http://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/001388320596292f925f46d56ef4c80a1c9d8e47e2d5711000
import sqlite3   # 导入SQLite驱动:
# 连接到SQLite数据库, 数据库文件是test.db
# 如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('test.db')
# 创建一个Cursor:
cursor = conn.cursor()
# 执行一条SQL语句，创建user表:
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
# 继续执行一条SQL语句，插入一条记录:
cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
cursor.rowcount   #通过rowcount获得插入的行数:
cursor.close()    #关闭Cursor:
conn.commit()     #提交事务:
conn.close()      #关闭Connection


# By Vamei  创建数据库 http://www.cnblogs.com/vamei/p/3794388.html
import sqlite3
# test.db is a file in the working directory.
conn = sqlite3.connect("test.db")
cursor = conn.cursor()
# create tables
cursor.execute('''CREATE TABLE category
      (id int primary key, sort int, name text)''')
cursor.execute('''CREATE TABLE book
      (id int primary key, 
       sort int, 
       name text, 
       price real, 
       category int,
       FOREIGN KEY (category) REFERENCES category(id))''')
conn.commit()   # save the changes
conn.close()     # close the connection with the database


#Vamei 插入数据
import sqlite3
conn = sqlite3.connect("test.db")
c    = conn.cursor()
books = [(1, 1, 'Cook Recipe', 3.12, 1),
            (2, 3, 'Python Intro', 17.5, 2),
            (3, 2, 'OS Intro', 13.6, 2)]
 # execute "INSERT" 
c.execute("INSERT INTO category VALUES (1, 1, 'kitchen')")

# using the placeholder
c.execute("INSERT INTO category VALUES (?, ?, ?)", (2, 2, 'computer'))

# execute multiple commands
c.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)
conn.commit()
conn.close()


# By Vamei   查询数据
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
# retrieve one record
c.execute('SELECT name FROM category ORDER BY sort')
print(c.fetchone())
print(c.fetchone())
# retrieve all records as a list
c.execute('SELECT * FROM book WHERE book.category=1')
print(c.fetchall())
# iterate through the records
for row in c.execute('SELECT name, price FROM book ORDER BY sort'):
    print(row)
    

# By Vamei  更新与删除
conn = sqlite3.connect("test.db")
c = conn.cursor()
c.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))
c.execute('DELETE FROM book WHERE id=2')
conn.commit()
conn.close()
c.execute('DROP TABLE book')  #删除整张表

创建一个新的数据库 <testDB.db>

sqlite3 testDB.db;
sqlite>.databases


CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);



sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

sqlite>.tables
COMPANY     DEPARTMENT

sqlite>DROP TABLE COMPANY;


INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );


INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];


sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

SELECT * FROM COMPANY WHERE SALARY > 50000;


SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
SELECT AGE FROM COMPANY 
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
SELECT * FROM COMPANY 
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
sqlite>  SELECT CURRENT_TIMESTAMP;
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
DELETE FROM COMPANY WHERE ID = 7;

WHERE SALARY LIKE '200%'
查找以 200 开头的任意值
WHERE SALARY LIKE '%200%'
查找任意位置包含 200 的任意值
WHERE SALARY LIKE '_00%'
查找第二位和第三位为 00 的任意值
WHERE SALARY LIKE '2_%_%'
查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY LIKE '%2'
查找以 2 结尾的任意值
WHERE SALARY LIKE '_2%3'
查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY LIKE '2___3'
查找长度为 5 位数，且以 2 开头以 3 结尾的任意值



WHERE SALARY GLOB '200*'
查找以 200 开头的任意值
WHERE SALARY GLOB '*200*'
查找任意位置包含 200 的任意值
WHERE SALARY GLOB '?00*'
查找第二位和第三位为 00 的任意值
WHERE SALARY GLOB '2??'
查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY GLOB '*2'
查找以 2 结尾的任意值
WHERE SALARY GLOB '?2*3'
查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY GLOB '2???3'
查找长度为 5 位数，且以 2 开头以 3 结尾的任意值


SELECT * FROM COMPANY LIMIT 6;
SELECT * FROM COMPANY ORDER BY SALARY ASC;
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
SELECT NAME, SUM(SALARY) 
         FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;


SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2


SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;
SELECT DISTINCT name FROM COMPANY;

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;
SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NOT NULL;
sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;
SELECT C.ID, C.NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;




#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print ("Opened database successfully");
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("Table created successfully");
conn.commit()
conn.close()



#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully";

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("Opened database successfully");

cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
    print ("ID = ", row[0])
    print ("NAME = ", row[1])
    print ("ADDRESS = ", row[2])
    print ("SALARY = ", row[3], "\n")

print ("Operation done successfully");
conn.close()

try:
    conn=sqlite3.connect('test.db')
    cur=conn.cursor()
    sql="select * from hello"
    cur.execute(sql)
    res=cur.fetchall()
    print(conn)
    print(res)
except sqlite3.Error as e:
    print(e)
finally:
    cur.close()
    conn.close()
    print(conn)