SQL学习记录

========================================================================================

========================================================================================

2017/09/11

一、创建表格

CREATE TABLE db(col1 textType, col2 textType, ....);

PRIMARY KEY 主键

二、删除表格

DROP TABLE db;

三、在db中插入数据

INSERT INTO db(col1, col2, ...) VALUES (val1, val2, ...);

四、删除db中的数据

DELETE FROM db WHERE condition;

五、查询db中的数据

1. 按条件查找

SELECT col1, col2, ... FROM db WHERE condition1 (AND/OR condition2) (IN (val1, val2, ...) (BETWEEN val1 AND val2) (LIKE condition) );

2. 按照某列的值分类

SELECT col1, col2, ... FROM db GROUP BY col1 (HAVING condition);

3. 按照升序/降序排列

SELECT col1, col2, ... FROM db WHERE condition (/GROUP BY col1) ORDER BY col ASC/DESC;

4. 查找某一列中有哪些值

SELECT DISTINCT col1 FROM db;

5. 对某列使用别名

SELECT col1 AS new_col_name FROM db;

六、更新表格中的数据

UPDATE db SET col = new_value WHERE condition;

七、函数

COUNT() -- 计算db中有多少项资料被选出

SUM() -- 计算db col栏的总数

AVG() -- 计算db col栏的平均值

MAX() -- 计算db col栏的最大值

MIN() -- 计算db col栏的最小值

之前参加某个公司的笔试题中有这么一个数据库的题

超市有一个数据库db：

customer      good       cnt

甲                  A             2

乙                  B             3

丙                  C             1

甲                  A             2

乙                  B             3

要求找出买了两种不同类型的顾客

SELECT customer FROM db GROUP BY customer HAVING COUNT(good) == 2;

==参加笔试的时候还没有学习过SQL，木有做出来

================================================================================

================================================================================

2017/9/13

这几天学了下python，附上上面数据库查询的python实现（插入代码中居然没有python选项。。）

import sqlite3

#create db
conn = sqlite3.connect('shop.db')
#achieve db cursor
cu = conn.cursor()

cur = cu.execute("select count(*) from sqlite_master where type = 'table' and name = 'shoplist'")
if cur.fetchone():
    #drop table
    cu.execute("drop table shoplist")
    conn.commit()

#create table
cu.execute("create table shoplist (customer text, good text, cnt integer)")
#insert data
cu.execute("insert into shoplist (customer, good, cnt) values ('甲', 'A', 2)")
cu.execute("insert into shoplist (customer, good, cnt) values ('乙', 'B', 3)")
cu.execute("insert into shoplist (customer, good, cnt) values ('丙', 'C', 1)")
cu.execute("insert into shoplist (customer, good, cnt) values ('甲', 'B', 2)")
cu.execute("insert into shoplist (customer, good, cnt) values ('乙', 'C', 3)")
conn.commit()

#select data
cursor = cu.execute("select customer from shoplist group by customer having count(good) == 2")
print("Customer bought two type good:")
for row in cursor.fetchall():
    print("customer =", row[0])

Customer bought two type good:
customer = 乙
customer = 甲