MySQL 的高级使用

最新推荐文章于 2024-08-20 16:41:46 发布

临咸鱼

最新推荐文章于 2024-08-20 16:41:46 发布

阅读量168

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_35695511/article/details/103729839

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

同样先是建立要用的表

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 查询，因为没有用到创建的联合索引。

索引优点：
加快数据的查询速度

索引缺点：
创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加

索引使用原则：
通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引，
数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

临咸鱼

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL 的高级使用

同样先是建立要用的表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 in...
复制链接

扫一扫

专栏目录