同样先是建立要用的表
CREATE TABLE courses (
id int unsigned NOT NULL AUTO_INCREMENT,
student varchar(30) DEFAULT NULL ,
sub varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
grade int DEFAULT NULL,
teacher varchar(255) NOT NULL,
PRIMARY KEY (id),
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
将查询结果插入到其他表中
方法一
insert into .. select ..
把查询结果插入到指定表中,也就是表复制。(subjects 表)
-- 创建科目表
CREATE TABLE subjects(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
-- 查询 courses 表中科目的分类信息
SELECT sub FROM courses GROUP BY sub;
-- 将查询结果插入到 subjects 表中
INSERT INTO subjects(name) SELECT sub FROM courses GROUP BY sub;
-- 添加新科目信息
INSERT INTO subjects(name) VALUES ('English');
方法二
create table .. select 列名 ..
创建表并插入数据 (teachers 表)
-- 查询教师
SELECT teacher FROM courses GROUP BY teacher;
-- 创建教师表并插入数据
CREATE TABLE teachers(
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(50) NOT NULL
--这里下面的别名要跟 teachers 表中要插入的字段名字一样才可以插入
)SELECT teacher name FROM courses GROUP BY teacher;
修改表结构
-- 更改 courses 表的字段 sub 和 teacher 的值为对应的 id
UPDATE courses c INNER JOIN subjects s ON c.sub = s.name SET c.sub = s.id;
UPDATE courses c INNER JOIN teachers t ON c.teacher = t.name SET c.teacher = t.id;
-- 更改 sub 和 teachers 为 s_id 和 t_id
ALTER TABLE courses CHANGE sub s_id int UNSIGNED NOT NULL, CHANGE teacher t_id int UNSIGNED NOT NULL;
添加外键连接
ALTER TABLE courses ADD foreign key(s_id) references subjects(id), ADD foreign key(t_id) references teachers(id);
pymysql
import pymysql
conn=connect(参数列表)
* 参数host:连接的mysql主机,如果本机是'localhost'
* 参数port:连接的mysql主机的端口,默认是3306
* 参数user:连接的用户名
* 参数password:连接的密码
* 参数database:数据库的名称
* 参数charset:通信采用的编码方式,推荐使用utf8
# 连接后获取游标
cur = conn.cursor()
try:
# 操作
# 添加 SQL 语句
# sql = "insert into students(name) values('刘璐'), ('王美丽');"
# 删除 SQ L语句
# sql = "delete from students where id = 5;"
# 修改 SQL 语句
sql = "update students set name = '王铁蛋' where id = 6;"
# 执行 SQL 语句
row_count = cursor.execute(sql)
print("SQL 语句执行影响的行数%d" % row_count)
# 提交数据到数据库
conn.commit()
except Exception as e:
# 回滚数据, 即撤销刚刚的SQL语句操作
conn.rollback()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
这里就要注意 SQL注入 这个问题了
SQL 注入:用户提交带有恶意的数据与SQL语句进行字符串方式的拼接,从而影响了SQL语句的语义,最终产生数据泄露的现象。
这个问题我会在后面理解了再发详细的,这里就先发死板的知识总结
防止 SQL 注入就用 prepared statement , 也就是 语句参数化,确保 data 就是 data ,没有代码的执行力。
- SQL语言中的参数使用 %s 来占位,此处不是 python 中的字符串格式化操作
- 将 SQL 语句中 %s 占位所需要的参数存在一个列表中,把参数列表传递给 execute 方法中第二个参数
防 SQL注入 示例
rom pymysql import connect
def main():
find_name = input("请输入物品名称:")
# 创建Connection连接
conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()
# 非安全的方式
# 输入 ' or 1 = 1 or ' (单引号也要输入)
# sql = "select * from goods where name='%s'" % find_name
# print("""sql===>%s<====""" % sql)
# # 执行select语句,并返回受影响的行数:查询所有数据
# count = cs1.execute(sql)
# 安全的方式
# 构造参数列表
params = [find_name]
# 执行select语句,并返回受影响的行数:查询所有数据
count = cs1.execute("select * from goods where name=%s", params)
# 注意:
# 如果要是有多个参数,需要进行参数化
# 那么params = [数值1, 数值2....],此时sql语句中有多个%s即可
# %s 不需要带引号
# 打印受影响的行数
print(count)
# 获取查询的结果
# result = cs1.fetchone()
result = cs1.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cs1.close()
# 关闭Connection对象
conn.close()
if __name__ == '__main__':
main()
事务
用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行,要么完全地都不执行, 它是一个不可分割的工作执行单元。
事务的四大特性
-
原子性(Atomicity) 一个事务必须被视为一个不可分割的最小工作单元,只能全部提交成功或者失败回滚。
-
一致性(Consistency) 数据库总是从一个一致性的状态转换到另一个一致性的状态。
-
隔离性(Isolation) 隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
-
持久性(Durability) 持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的。
-
事务使用的数据库引擎是 InnoDB 类型(默认)
-
MyISAM 不支持事务,优势是访问速度快,对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表
开启事务
begin;
或者
start transaction;
开启事务后,对表格的修改只保存在 MySQL 的缓存文件中,需要提交才能维护到物理表中。
默认采用自动提交 (autocommit) 模式,如果没有显示的开启一个事务,那么每条sql语句都会被当作一个事务执行提交的操作。
当设置 autocommit=0 就是取消了自动提交事务模式,直到显示的执行commit和rollback表示该事务结束。
set autocommit = 0;
insert into students(name) values('刘三峰');
-- 需要执行手动提交,数据才会真正添加到表中, 验证的话需要重新打开一个连接窗口查看表的数据信息,因为是临时关闭自动提交模式
commit
-- 重新打开一个终端窗口,连接MySQL数据库服务端
mysql -uroot -p
-- 然后查询数据,如果上个窗口执行了commit,这个窗口才能看到数据
select * from students;
提交事务:
将本地缓存文件中的数据提交到物理表中,完成数据的更新。
commit;
回滚事务:
放弃本地缓存文件中的缓存数据, 表示回到开始事务前的状态
rollback;
索引
MySQL 中的 ‘键’,保存数据表所用记录的位置信息,提供更快的查询速度。 外键约束也是提供了 索引。
查看表中已有索引: (主键列会自动创建索引)
SHOW index from 表名;
创建索引名 (不指定名字,默认使用字段名)
-- 创建索引的语法格式
-- alter table 表名 add index 索引名[可选](列名, ..)
-- 给name字段添加索引
alter table classes add index my_name (name);
删除索引
-- 删除索引的语法格式
-- alter table 表名 drop index 索引名
-- 如果不知道索引名,可以查看创表sql语句
show create table classes;
alter table classes drop index my_name;
这里可以引申 MySQL 统计运行命令的时间的方法:
- 设置profiling
SET profiling=1;
- 执行sql
SELECT * FROM 'test_table';
3.查看结果
SHOW profiles;
4.多条命令后也可以继续查看
联合索引
-- 创建联合索引
alter table teacher add index (name,age);
减少磁盘空间开销,因为每创建一个索引,其实就是创建了一个索引文件,那么会增加磁盘空间的开销。
联合索引的最左原则
在使用联合索引的时候,我们要遵守一个最左原则,即index(name,age)支持 name 、name 和 age 组合查询,而不支持单独 age 查询,因为没有用到创建的联合索引。
索引优点:
加快数据的查询速度
索引缺点:
创建索引会耗费时间和占用磁盘空间,并且随着数据量的增加所耗费的时间也会增加
索引使用原则:
通过优缺点对比,不是索引越多越好,而是需要自己合理的使用。
对经常更新的表就避免对其进行过多索引的创建,对经常用于查询的字段应该创建索引,
数据量小的表最好不要使用索引,因为由于数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果。
在一字段上相同值比较多不要建立索引,比如在学生表的"性别"字段上只有男,女两个不同值。相反的,在一个字段上不同值较多可是建立索引。