python3 数据库基本操作及与python的连接

1.基本操作
（1）数据库的层面操作
创立数据库： CRAETE DATABASE 名字 CHA=utf-8
删除 ： DROP DATABASE name
显示所有数据库： SHOW DATABASE
选择数据库 ： USE name

（2）表层面的操作
创立表：
CREATR TABLE name(
id INT PRIMARY KEY, int代表数据类型
NAME VARCHAR(32)
)
显示所有表 ： SHOW TABLE
修改表列：
lter table 表名 add（增加列）列名 类型 ALTER TABLE t_person ADD age INT;
change（修改列名和数据类型） ALTER TABLE t_person CHANGE age age_num VARCHAR(10);
drop（删除列 ALTER TABLE t_person DROP ageNum;
modify（修改列的数据类型） ALTER TABLE t_person MODIFY age VARCHAR(10);
查看表结构 : DESC t_person;
删除表 DROP TABLE t_person;
更改表名称 RENAME TABLE t_person TO t_user;
查看表的创建语句 SHOW CREATE TABLE t_person;

在表中放数据：
1.INSERT INTO t_person VALUES(‘刘备’,‘蜀国’);
2.另一种写法：INSERT INTO t_person (NAME,contry) VALUES (‘曹操’,‘魏国’)
当列数比较多时，可以用上面的方法指定列名选择性增加，顺序必须一一对应
3.连续添加：INSERT INTO t_person VALUE (‘孙权’,‘吴国’),(‘小乔’,‘吴国’);
后面的括号可以有任意个
修改数据:UPDATE t_person SET NAME = ‘大乔’ WHERE contry = ‘蜀国’;
删除数据：DELETE FROM t_person WHERE id=1

主键创立：
1.id int primary key,
2.自动更新的主键 id INT PRIMARY KEY AUTO_INCREMENT,
查询
1.查询所有 SELECT * FROM t_user;
2.查一列 SELECT uname FROM t_user;
3.查询某几列 SELECT uname,country,age FROM t_user;
4.查询某几列，并且所有人年龄增加50（使用算数表达式） SELECT uname,country,age+50 FROM t_user;
5.修改列名的显示（起别名）注意不要用关键字 SELECT age+50 AS ‘年龄’ FROM t_user;
6.查询时去除重复项（国家会重复） SELECT DISTINCT country FROM t_user;
7.distinct：作用范围是后面所有字段的组合（可以有多个字段） SELECT DISTINCT country，uname FROM t_user;
限制查询条件
查出某个国家所有年龄超过20的人：
SELECT uname,age FROM t_user WHERE age>20 AND country=‘魏国’;
设置查询条件
WHERE 子句中可以使用等号 = 来设定获取数据的条件， country=‘魏国’
但是有时候我们需要获取含有某个字符的所有记录，这时我们就需要在 WHERE 子句中使用 LIKE 子句
SQL LIKE 子句中使用百分号 %字符来表示任意字符，如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的
SELECT uname,age FROM t_user WHERE age>20 AND uname LIKE ‘吕%’;
分页：
在查询时可以只检索前几条或者中间某几行数据（数据量很大时，几百万条
limit 后面的第一个数字设置从哪里开始检索（偏移量，从0开始）
limit 后面的第二个数字是设置显示多少条
SELECT * FROM t_user LIMIT 0,3;
排序：
1.从高到低 SELECT DISTINCT country,uname,age FROM t_user ORDER BY country,age DESC;
2.从低到高（默认） SELECT DISTINCT country,uname,age FROM t_user ORDER BY country,age ASC;
聚合函数：
为了快速得到统计数据（多条数据的统计结果），提供了5个聚合函数
count()： 查询表中某项数据一共包含多少条（统计总数）
查询表中一共有多少个人（只会检索一列） SELECT COUNT(uname) FROM t_user ;
max(列)：求此列的最大值
SELECT MAX(age) FROM t_user ;
min(列)：求此列的最小值
SELECT MIN(age) FROM t_user ;
sum(列)：求此列的和
SELECT SUM(age) FROM t_user ;
avg(列)：求此列的平均值
SELECT AVG(age) FROM t_user ;
内置函数
字符串函数：
ascii（str）查看字符的ASCII码值,str是空时返回0
SELECT ASCII(‘a’)
char(数字)查看ASCII码值对应的字符
SELECT CHAR(97)
concat（str1,str2,…）拼接字符串
SELECT CONCAT(12,34,‘ab’)
SELECT uname,CONCAT(age,‘岁’) FROM t_user;
length（str）字符串中包含的字符个数SELECT LENGTH(‘abc’)

截取字符串：
left（str，len）截取字符串左端的len个字符
SELECT LEFT(‘qwertyui’,3)
right（str,len）截取字符串右端的len个字符
SELECT RIGHT(‘qwertyui’,3)
substring(str,pos,len) 指定位置截取：截取字符串str的位置pos起的len个字符（从1开始）
SELECT SUBSTRING(‘qwertyuio’,2,3)
SELECT SUBSTRING(uname,1,1) FROM t_user;
截取所有人物的姓
SELECT DISTINCT SUBSTRING(uname,1,1) FROM t_user;
同时去除重复项

去空格：
ltrim（str）：返回删除了左空格的字符串
SELECT LTRIM(’ abc ‘)
rtrim（str）：返回删除了右空格的字符串
SELECT RTRIM(’ abc ')
trim（方向 remstr from str）：返回从某侧删除remstr后的字符串str
方向词包括both（两侧）、leading（左）、trailing（右）
SELECT TRIM(‘ abc ’) --删除两侧空格
SELECT TRIM(BOTH ‘x’ FROM ‘xxxabcxxx’) --删除两侧特定字符
SELECT TRIM(LEADING ‘x’ FROM ‘xxxabcxxx’)
SELECT TRIM(TRAILING ‘x’ FROM ‘xxxabcxxx’)

space（n）：返回由n个空格组成的字符串
replace（str,from_str,to_str）:替换字符串
SELECT REPLACE(‘123abc123’,‘123’,‘def’)
lower(str) upper(str):大小写转换
SELECT LOWER(‘aBcD’)
SELECT UPPER(‘aBcD’)

时间和日期函数
获取当前日期
SELECT CURRENT_DATE()
获取当前时间
SELECT CURRENT_TIME()
获取当前日期和时间
SELECT NOW()

时间和日期格式化
date_format(data,format)
SELECT DATE_FORMAT(‘2018-8-8’,’%Y年%m月%d日’)
SELECT DATE_FORMAT(CURRENT_DATE(),’%y年%m月%d日’)
UPDATE t_user SET brithday=DATE_FORMAT(CURRENT_DATE(),’%y年%m月%d日’) WHERE uname = ‘吕布’
format参数可用的值如下：
%Y 年份，返回4 位整数
%y 年份，返回2 位整数
%m 月，返回0-12的整数
%d 日期，返回0-31之间的整数
%H 小时 (00…23)
%h 小时 (01…12)
%i 分钟(00…59)
%s 秒 (00…59)

（3）数据库的备份：
1，在MySQL的bin目录中打开命令窗口（bin目录中才有复制的命令）
2，输入：mysqldump –uroot –p test101 > D:/t1.sql
数据恢复
连接MySQL，创建数据库
在MySQL的bin目录中打开命令窗口
输入：mysql –uroot –p 数据库名 < d:/t1.sql

（4）char 和 varchar区别：
char与varchar后面接的数据大小为存储的字符数，而不是字节数（MySQL4.1之后）
char定义的是固定长度，长度范围为0-255，存储时，如果字符数没有达到定义的位数，会在后面用空格补全存入数据库中
varchar是变长长度，长度范围为0-65535，存储时，如果字符没有达到定义的位数，也不会在后面补空格

2、分组查询和过滤
过滤：
SELECT 列名 FROM 表名 WHERE 过滤条件
使用where，可以用来过滤单行，**如果想要过滤分组或者聚合之后的数据，**要加having
正确：
查看每个国家的总人数，年龄总和，平均年龄，最高年龄，最低年龄，但是排除某个国家
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user WHERE country!=‘吴国’ GROUP BY country;

列出每个国家小于20岁的人
SELECT country,uname FROM t_user WHERE age<20 GROUP BY country;

错误：
显示每个国家的平均年龄，但是仅显示那些总年龄超过100的国家
SELECT country,AVG(age) FROM t_user WHERE SUM(age)>100 GROUP BY country;
上面的写法报错，where在聚合前先筛选记录，但此时表中并没有sum（age）这条记录
正确：
having在聚合后对组记录进行筛选
SELECT country,AVG(age) FROM t_user GROUP BY country HAVING SUM(age)>100;
注意顺序：having放在分组之后，因为作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组

总结：
having要跟在group by之后，对分组查询的结果进行过滤（过滤分组）
where要出现在group by之前，执行表中所有数据来进行过滤（过滤行）
另外，having可以用聚合函数，并支持所有where子句操作符

3、主键，外键

主键约束（primary key）：要求主键列数据唯一，并且不能为空
唯一约束（unique）：要求该列唯一，允许为空 uname VARCHAR(32) UNIQUE 或者 UNIQUE KEY(uname)
非空约束（not null）：某类内容不能为空 id INT NOT NULL,
外键约束（foreign key）：用于两表间建立关系

主键约束（primary key）： 确定一个表的唯一性
主键约束是数据库中最重要的一种约束
在关系中，主键值不能为空，也不允许出现重复（非空且唯一）
一个表中只允许一个主键
主键是表中能够唯一确定一个行数据的字段

主键字段可以是单字段或多字段的组合

外键：用来和其他表建立联系
外键具有保持数据完整性和一致性的机制，目前MySQL只在InnoDB引擎下支持
（ENGINE=INNODB）
关系：
成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则可以设置成绩表中的学号是学生表的外键同理 成绩表中的课程号是课程表的外键

CREATE TABLE t_student(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	sname VARCHAR(25),
	sex VARCHAR(10)
);

INSERT INTO t_student VALUES (1,"小明","男"),(2,"小红","女");
CREATE TABLE t_class(
	cname VARCHAR(25),
	cid INT,
	FOREIGN KEY(cid) REFERENCES t_student(id)
);

4、索引和视图
索引：当数据库中存在很多条记录，例如几十万条，查询速度就成了一个问题
此时可以建立类似目录的数据库对象，实现快速查询，这就是索引
例如在书中查询某个内容时，先在目录中查询，然后根据目录所示的页码找到查询内容，大大缩短了查询时间
索引的作用：索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间

CREATE INDEX index_1 ON t_user(uname)
     创建一个索引    索引名  从  表名  在哪个字段

CREATE TABLE mytable(  -- 创建表时直接指定
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX index_1 (username)  

删除索引
DROP INDEX index_1 ON t_user
); 

视图：就是一条SELECT语句执行后返回的结果集（显示结果是一个表）

-- 查看每个国家的总人数，年龄总和，平均年龄，最高年龄，最低年龄
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user GROUP BY country;

为上面的语句创建视图：
CREATE VIEW v_user AS
	SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user 	GROUP BY country;
    SELECT * FROM v_user; --以后在使用上面的查询语句时只需要使用视图名

视图的作用：专门进行某些查询
方便操作：减少复杂的SQL语句，增强可读性
更加安全：在外界访问你的数据库时，经常不想让他访问所有数据这时候可以建立视图，在视图中写好查询语句，同时限制外界的访问权限只能通过你给定的视图去查询

5、事务
事务：数据库的不一致性：
事物：是一个操作序列，这些操作只能都做，或者都不做，是一个不可分割的工作单位
例子：
假设该场景发生于一个银行转账背景下，月中到了发工资的日子。学校打算给A老师发放一个月的工资：
1.学校财务核对A老师工资单
2.确认学校账上还有这么多钱
3.向银行提出转账申请，银行扣除学校财务卡上的指定金额
4.银行向A老师工资卡打入指定金额
5.银行汇报双方交易完成
但是，当这个过程执行完第3步的时候，突然断电。待电力系统回复之后，银行并不会继续执行4、5步甚至连1，2，3步的操作记录都丢失了。此时出现了如下的问题：
学校认为，工资已经发出
A老师认为，学校还没有发工资
银行认为，从来就没有发生过转账的事情
整个过程可以用一个词来描述：数据库中的数据产生了“不一致性”

事物：是一个操作序列，这些操作只能都做，或者都不做，是一个不可分割的工作单位
1.在操作MySQL过程中，对于一般简单的业务逻辑或中小型程序而言，无需考虑应用MySQL事务
事务主要用于处理操作量大，复杂度高的数据
2.MySQL中，事务由单独单元中的一个或多个SQL语句组成。在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，要么都做，或者都不做
3.如果单元中某条SQL语句一旦执行失败或产生错误，可以让整个单元回滚。所有受到影响的数据将返回到事务开始以前的状态（保证了数据的完整性）

事物语句：只有使用了 Innodb 数据库引擎的数据库或表才支持事务
开启：begin 开启一个事物
提交：commit 将事务中的SQL语句提交给数据库
回滚：rollback 取消掉之前的所有操作（撤销事务

在这里插入代码片CREATE TABLE t_person(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10),
money INT
) TYPE=INNODB;创建一个InnoDB类型的数据表
或者在创建表之后改变：ALTER TABLE t_person TYPE=INNODB；

INSERT INTO t_person VALUES(1,'小明',1000);
INSERT INTO t_person VALUES(2,'丽丽',2000);

上面的代码中小明有1000块钱，丽丽有2000块钱
接下来要实现小明给丽丽转账500元

BEGIN; -- 开始事物
UPDATE t_person SET money=money-500 WHERE id=1;
UPDATE t_person SET money=money+500 WHERE id=2;
SELECT * FROM t_person; -- 查询结有误果是否
COMMIT; -- 发现结果无误，提交事物，提交后数据库中数据会修改


创建事务的一般过程是：开始事务、创建事务、应用SELECT语句查询数据、提交事务
BEGIN; -- 开始事物
UPDATE t_person SET money=money-500 WHERE id=1;
UPDATE t_person SET money=money+600 WHERE id=2;
SELECT * FROM t_person; -- 查询结有误果是否
ROLLBACK; -- 结果有误，回滚事物，取消所有操作

总结：
我们可以声明一个事务的开始，在确认提交或者指明放弃前的所有操作，都先在一个叫做事务日志的临时环境中进行操作。待操作完成，确保了数据一致性之后，那么我们可以手动确认提交，也可以选择放弃以上操作。
注意： 一旦选择了提交，就不能再利用回滚来撤销更改了

6、关联关系
表的关系：mysql相互关联的表之间存在一对一，一对多（多对一），多对多的关系：
1，一对一的关系：表1中的一条数据，对应表2中的一条数据
这种关系即多个表具有相同的主键，A表中的一条数据对应B表中的一条数据。实际中用的并不多，因为完全可以将这种关系的合并为一张表（一夫一妻）

2.一对多（多对一）的关系：表1中的一条数据对应表2中的多条数据
其中表1的主键是表2的外键，（即表1的某字段作为主键，表2的相同字段绑定到表1的主键字段上）

CREATE TABLE stu(          -- 学生表
    stuId INT,  
    name VARCHAR(10) NOT NULL,  
    PRIMARY KEY(stuId)  
); 

CREATE TABLE score_1(           -- 成绩表
    stuId INT,  
    score VARCHAR(32),      #一个学生有多门成绩
    FOREIGN KEY (stuId) REFERENCES stu(stuId)  
); 

3.多对多的关系：
比如：一个老师教很多学生的课，一个学生选了很多老师的课。那么，老师和学生之间就是多对多的关系多对多的关系要借助于第3张表

1，首先创建老师表，设置id为主键
CREATE TABLE teacher(  
    teacherId INT,  
    NAME VARCHAR(10) NOT NULL,  
    PRIMARY KEY(teacherId)  
);

2，然后创建学生表，同样设置id为主键
CREATE TABLE stu(  
    stuId INT,  
    NAME VARCHAR(10) NOT NULL,  
    PRIMARY KEY(stuId)  
);

3，最后创建一个课程表，将前两张表关系起来
CREATE TABLE score(
    scoresname  VARCHAR(32)
    stuId INT,  
    teacherId INT,  
    FOREIGN KEY (stuId) REFERENCES stu(stuId),  
    FOREIGN KEY (teacherId) REFERENCES teacher(teacherId)  
); 

7、子查询
子查询：子查询在主查询前执行一次，主查询使用子查询的结果

CREATE TABLE stu(
stuID INT,
sname VARCHAR(32),
score INT,
PRIMARY KEY(stuID)
);

INSERT INTO stu VALUES (1,‘xiaoming’,60), (2,‘xiaoli’,70)；

1，如何查询所有比小明成绩高的学生名字
SELECT sname FROM stu WHERE score > (SELECT score FROM stu WHERE sname = 'xiaoming');

2，查询成绩高于平均成绩的学生姓名和成绩
SELECT sname,score FROM stu WHERE score > (SELECT AVG(score) FROM stu); 

8、数据库与python的连接

对数据库进行查询操作：

import pymysql
#连接数据库
conn = pymysql.connect(host='localhost',user='root',password='123123',db='test1',charset='utf8')
#获取cursor对象
cur = conn.cursor()
#编写SQL语句
sql = 'select * from t_user '
#通过cursor的对象去执行SQL语句
cur.execute(sql)
#查看结果
emps = cur.fetchall()   
print(emps)

def exceDML(sql): #用来执行插入
import pymysql
cur = None
conn = None
def getall(sql): #用来执行查询
    # 连接数据库
    conn = pymysql.connect(host='localhost', user='root', password='123123', db='test1', charset='utf8')
    cur = conn.cursor()          #获取cursor对象
    # 通过cursor的对象去执行SQL语句
    cur.execute(sql)
    return cur.fetchall()


def exceDML(sql): #用来执行插入
    conn = pymysql.connect(host='localhost', user='root', password='123123', db='test1', charset='utf8')
    cur = conn.cursor()    
# 通过cursor的对象去执行SQL语句
    cur.execute(sql)
    # 提交事物
    conn.commit()
def close():  #用来关闭连接
    if cur:
        cur.close()
    if conn:
        conn.close()

from day3 import mysqlHelper

name = input("请输入名字：")
id = input("请输入ID：")
sql1 = 'insert into t_user values(%d,"%s")'%(int(id),name)
sql2 = 'select * from t_user'
mysqlHelper.exceDML(sql1)
print(mysqlHelper.getall(sql2))
mysqlHelper.close()