数据库编程——网站开发热身上篇完结

# -*- coding: utf-8 -*-
"""
This is a temporary script file.
By CJ
"""
#sqlite3数据库模板
import sqlite3
#一、打开
conn=sqlite3.connect('test.db')
cur=conn.cursor()
#二、操作
cur.execute('CREATE TABLE demo(name varchar(20),age int);')#创表
cur.execute('INSERT INTO demo VALUES("CJ",24);')#增项
cur.execute('UPDATE demo SET name="GJL" WHERE age=24;')#改项
cur.execute('SELECT * FROM demo;')#查项
for i in cur.fetchall():print(type(i),i,i[0],i[1])#<class 'tuple'> ('GJL', 24) GJL 24
cur.execute('DELETE FROM demo WHERE name="GJL";')#删项
cur.execute('DROP TABLE demo;')#删表
conn.commit()#提交事务（经测试若没有这行则上一句删表会不执行）
#三、关闭
cur.close()
conn.close()
'''
数据库开发一般流程：
一、画E-R图（Entity-Relationship）
二、关系表设计
1、实体：多少实体先建多少个表
2、实体属性：每个实体的表的属性
3、关系实现：
    1比1（两个表有同一主键）
    1比N（N表属性加一外键列与1表主键关联）
    M比N（由两表的主键生成单独的表）
4、补充约束：除主键与外键外，还有非空与唯一以及索引
举例：学生系统
1、实体：学生证，学生，老师，课程
2、实体属性
    校卡表：学号，始日，终日
    学生表：学号，年龄，名字
    老师表：工号，年龄，名字，课号
    课程表：课号，学时，名字
3、关系约束
    1比1：校卡表与学生表用同一主键
    1比N：老师表中加入课程表的课号为外键，即多个老师上一堂课
    M比N：用学生表的学号与课程表的课号生成一个新表，两都皆是主键也是外键
    （除主键与外键约束外，还有非空，唯一，索引）
三、关系表实现
1、数据库创建
    对sqlite3：获得连接话柄时就自动绑定一个数据库，若不存在就会自动创建，对应数据库文件后缀是db，例如：database_name.db
    对mysql8：获得连接话柄时就自动绑定一个数据库，若不存在就会报错，所以要人工预先创建一个数据库，可以用Navicat可视化软件或命令台实现
        在命令台可以用代码创建数据库文件：CREATE DATABASE `cj_database` CHARACTER SET 'utf8';
        （删除的写法：DROP DATABASE database_name;如DROP DATABASE `cj_database`;）
2、数据表创建：
    语法：CREATE TABLE table_name(num int,str varchar(20)));
    举例：CREATE TABLE demo(num int,str varchar(20)));
    （删除的写法：DROP TABLE table_name;如DROP TABLE demo;）
四、数据表维护
1、增
    语法：INSERT INTO table_name(col_1,col_2,...) VALUES(val_1,val2,...);
    举例：INSERT INTO course(id,period,title) VALUES(1,40,'AI');
2、删
    语法：DELETE FROM table_name [WHERE 条件表达式];
    举例：DELETE FROM course WHERE id=1;
3、改
    语法：UPDATE table_name SET col_1=val_2,col_2=val_2,... [WHERE 条件表达式];
    举例：UPDATE course SET age=age+1;
4、查
    语法：SELECT [distinct|top] col_1,col_2,... FROM table_name [WHERE 条件表达式]
            [GROUP BY 分组列[HAVING 分组筛选条件表达式]][ORDER BY col_1 [ASC/DESC],col_2[ASC/DESC],...]
    举例一：选择学时且每个值只显示一次：SELECT distinct period FROM course;
    举例二：查询所有课程结果按课号倒序：SELECT * FROM course ORDER BY id DESC;
    举例三：按学时分组并组计每个课时课程数，并且只返回课程个数大于3的课时：
            SELECT period, COUNT(*) FROM course GROUP BY period HAVING count(*)>3;
5、查（多表连接）
    语法：SELECT col_1,col2,... FROM table_name1 JOIN类型 table_name2 ON 连接条件表达式 [WHERE 条件表达式];
    举例：SELECT teacher.* FROM teacher INNER JOIN course on teacher.course_id=course.id WHERE teacher.age=30;
五、补充说明
JOIN类型：
    INNER JOIN：内连接
    LEFT JOIN：左连接
    RIGHT JOIN：右连接
    FULL JOIN：全连接
HWERE条件表达式：
    数值操作：=,<>,>,<,<=,>=,between
    字符操作：like,in
    逻辑操作：IS NULL,AND,OR
SQL聚集函数：
    COUNT(*):统计记录个数
    AVG(col):计算某列平均值
    MAX(col):找出某列最大值
    MIN(col):找出某列最小值
    VAR(col):计算某列方差
    FIRST(col):返回某列第1个值
    LAST(col):返回某列最后1个值
事务控制语句:
    BEGIN TRANSACTION：启动一个新事务，其后所有语句被封装，待遇到ROLLBACK或COMMIT为止
    ROLLBACK：回滚事务，取消本次事务中执行成功的语句
    COMMIT：提交事务，DML语句更新生效
'''
#mysql数据库模板
import mysql.connector
#一、打开
conn=mysql.connector.connect(
    user='root',
    password='j1234567',
    host='localhost',
    port='3306',
    database='cj_database',
    use_unicode=True)
conn.autocommit=True#每条DML语句自动提交，相当于关闭了事务，故无需commit()方法提交事务
cur = conn.cursor()
#二、操作
cur.execute('''create table user_tb(
	user_id int primary key auto_increment,
	username varchar(255),
	password varchar(255), 
	gender varchar(255))''')#建表（跨行必须用'''）
cur.execute('''create table order_tb(
	order_id integer primary key auto_increment,
	item_name varchar(255) unique,
	item_price double not null,
	user_id int,
    foreign key(user_id) references user_tb(user_id) )''')#建表（注意主键，外键，唯一，非空，自增的写法）
cur.execute('insert into user_tb values(null,"cj","1314","male")')#增（注意传入时空值的写法）
cur.execute('insert into user_tb values(null,"gjl","520","female")')
cur.execute('insert into order_tb values(null,"mouse",10.5,2)')
cur.execute('insert into order_tb values(null,"keyboard",20.5,1)')
cur.execute('UPDATE order_tb SET item_name="mouses" WHERE item_name="mouse";')#改
cur.execute('SELECT * FROM user_tb JOIN order_tb on order_tb.user_id=user_tb.user_id;')#查
while True:
    row=cur.fetchone()
    if not row:break
    print(row)
cur.execute('DELETE FROM order_tb WHERE item_price=20.5;')#删项
cur.execute('DROP TABLE order_tb;')#删表
cur.execute('DROP TABLE user_tb;')
#三、关闭
cur.close()
conn.close()
'''
output:
(1, 'cj', '1314', 'male', 2, 'keyboard', 20.5, 1)
(2, 'gjl', '520', 'female', 1, 'mouses', 10.5, 2)
'''

#概念复习：
#特性(ACID)：原子性、一致性、隔离性、持久性
#键(超键/候选键/主键)：唯一标识，无冗余，人为选择（顺带一提，码=键）
#技术(存储过程/事务/游标/触发器)：sql语句集合，整体要么做要么不做，逐行操作，特定事件发生时执行
#范式(1NF/2NF/3NF/BCNF/4NF/5NF)：不可分，非主完全依赖，非主直接依赖，候选码/候选键唯一
   #特别地：单主属性，即只一个关键字，必定符合2NF

---

2020-12-10日补充：

一、下载好Mysql之后打开命令行输入密码进入后，需要加两句才能进行表操作：show句可省略

create database cj_test;

show databease;

use cj_test;

二、然后用本博文上面的代码：

CREATE TABLE demo(name varchar(20),age int);

show tables;

INSERT INTO demo VALUES("CJ",24);

UPDATE demo SET name="GJL" WHERE age=24;

SELECT * FROM demo;

三、下面介绍Index的知识点：增查删

create index myindex on demo(age);

show index from demo;

drop index myindex on demo;

 

当然，其实Index可以在create表时就建了，有三种索引，写法差不多：

建表同时建立单索引

CREATE TABLE t_user1(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX (userName) #关键字INDEX
);

建表同时建立唯一索引（可以是单或多）

CREATE TABLE t_user2(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
UNIQUE INDEX index_userName(userName) #关键字UNIQUE和INDEX
);

建表同时建立联合索引

CREATE TABLE t_user3(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX index_userName_password(userName,PASSWORD)
);

 

什么情况下可以创建索引？

1.字段的数值有唯一性的限制，比如id

2.频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下

3.需要经常 GROUP BY 和 ORDER BY 的列

4.UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引

5.DISTINCT 字段需要创建索引

6.做多表 JOIN 连接操作时，创建索引需要注意连接表的数量不要超过3张

什么时候不需要创建索引?（起不到定位作用的字段）

1.WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引；

2.表记录太少，比如少于 1000 行，那么是不需要创建索引；

3.字段中如果有大量重复数据，也不用创建索引，比如性别字段；

4.频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率；

什么情况下索引失效?

1.如果索引列进行了表达式计算，使用函数，则会失效

2.在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

3.当我们使用 LIKE 进行模糊查询的时候，表达式不能是 % 开始

4.索引列尽量设置为 NOT NULL 约束（INT型默认设为0，字符串型默认设为空）