关于Mysql使用的一些总结

1.数据库的备份与恢复

-- 数据库备份 在库外执行，将数据库备份，在当前目录生成一个xxx.sql文件
mysqldump -uroot -p 数据库名 > xxx.sql;
  -- 数据库恢复 在库外执行，将数据库恢复到其他数据库中去
mysql -uroot -p 新数据库名 < xxx.sql; 复制代码

2.事务的四大特性

原子性:

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性:

数据库总是从一个一致性的状态转换到另一个一致性的状态。（例如在转账过程中系统崩溃，支票账户中也不会损失，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

隔离性:

通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。

持久性:

一旦事务提交，则其所做的修改会永久保存到数据库。

事务的使用

在使用事务之前，先要确保表的存储引擎是 InnoDB 类型, 只有这个类型才可以使用事务，MySQL数据库中表的存储引擎 默认 是 InnoDB 类型。

表的存储引擎说明:

表的存储引擎就是提供存储数据一种机制，不同表的存储引擎提供不同的存储机制。

查看 MySQL 数据库支持的表的存储引擎:

-- 查看MySQL数据库支持的表的存储引擎show engines;复制代码

说明:

• 常用的表的存储引擎是 InnoDB 和 MyISAM

• InnoDB 是支持事务的

• MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表

开启事务:

begin;--或者start transaction;复制代码

• 开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不维护到物理表中

• MySQL数据库默认采用自动提交( autocommit )模式，如果没有显示的开启一个事务,那么每条 sql 语句都会被当作一个事务执行提交的操作

set autocommit = 0; 
 --设置autocommit = 0 变为了手动提交事务，默认为1为自动提交
insert into 表名(name) values('张无忌');
-- 需要执行手动提交，数据才会真正添加到表中, 验证的话需要重新打开一个连接窗口查看表的数据信息，因为是临时关闭自动提交模式
commit  --提交事务​
-- 另外重新打开一个终端窗口，连接MySQL数据库服务端
mysql -uroot -p​
-- 然后查询数据,如果上个窗口执行了commit，这个窗口才能看到数据select * from 表名;复制代码

回滚事务:

放弃本地缓存文件中的缓存数据, 表示回到开始事务前的状态

rollback; --回滚事务复制代码

3.索引的使用

索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

应用场景:

当数据库中 数据量很大 时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。

查看表中已有索引:

show index from 表名;复制代码

说明:

• 主键列会自动创建索引

索引的创建:

-- 创建索引的语法格式
-- alter table 表名 add index 索引名[可选](列名, ..)
-- 给name字段添加索引
alter table 表名 add index my_name (name);复制代码

说明:

• 索引名不指定，默认使用字段名

索引的删除:

-- 删除索引的语法格式
-- alter table 表名 drop index 索引名
-- 如果不知道索引名，可以查看创表sql语句
show create table 表名;
alter table 表名 drop index my_name;复制代码

4.SQL语句

聚合函数的介绍

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。下面是一些常见的聚合函数：

1. count(col): 表示求指定列的总行数

2. max(col): 表示求指定列的最大值

3. min(col): 表示求指定列的最小值

4. sum(col): 表示求指定列的和

5. avg(col): 表示求指定列的平均值

分组查询

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

语法格式：

GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]复制代码

HAVING对分组后的数据进行条件过滤，WITH ROLLUP在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果。

-- 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from 表名 group by gender with rollup;复制代码

下面对数据库比较常见的SQL语句做一些简单总结：

insert into 表名(name) values ('黄蓉'),('郭靖'),('黄药师'); --一次插入多列;
​select * from 表名 where not age between 10 and 25; --年龄不在10到25之间；
​select * from 表名 where  age in(20,23); --年龄是20，23的；
​select * from 表名 where height is null; --身高不为空；
​select * from 表名 order by age desc,height desc; --年龄按降序，年龄相同身高按降序排序；
--求平均价格,并且保留两位小数
select round(avg(price),2) from 表名;
--查询所有价格大于平均价格的商品，并且按价格降序排序
select id,name,price from 表名 where price > (select round(avg(price),2) from 表名) order by price desc;
 --分页查询
select * from 表名 limit start,count 
 -- limit是分页查询关键字
-- start表示开始行索引，默认是0-
- count表示查询条数复制代码

5.连接查询

内连接查询

查询两个表中符合条件的 共有 记录。

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2复制代码

• inner join 就是内连接查询关键字

• on 就是连接查询条件

左连接查询

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充。

select 字段 from 左表 left join 右表 on 左表.字段1 = 右表.字段2复制代码

• left join 就是左连接查询关键字

• on 就是连接查询条件

右连接查询

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充。

select 字段 from 左表 right join 右表 on 左表.字段1 = 右表.字段2复制代码

• right join 就是右连接查询关键字

• on 就是连接查询条件

6.外键约束

外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性。

对已存在的字段添加外键约束

-- 为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);复制代码

在创建数据表时设置外键约束

-- 创建学校表
create table school( id int not null primary key auto_increment,  
                        name varchar(10));
​-- 创建老师表
create table teacher( id int not null primary key auto_increment, 
                      name varchar(10),
                     s_id int not null,  
                     foreign key(s_id) references school(id));复制代码

删除外键约束

-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table teacher;​
-- 获取名称之后就可以根据名称来删除外键约束
alter table teacher drop foreign key 外键名;复制代码

7.pymysql的使用

pymysql 是 python 连接 mysql 的一个比较常用的库。可通过 pip 安装。

pip show pymysql 
 --查看是否装pymysql及查看装后的信息；
pip install pymysql --安装复制代码

import pymysql
'''
host=None,  # Mysql所在的主机IP地址，如果在本地使用localhost
user=root,  # 用户名
password="mysql",# 密码
database='py', # 使用哪个数据库
port=3306,    # mysql的端口，默认3306
charset='utf8' # 数据库编码格式
'''
conn = pymysql.connect(host = 'None',user = 'root',                       password='mysql',database='py',
                       port = 3306,charset = 'utf8') 

sql = 'select * from students' #待执行的sql语句
cursor = conn.cursor() # 获取游标
raw_count = cursor.execute(sql) #cursor执行sql语句，返回影响的行数
print('raw_count:',raw_count)
# one = cursor.fetchone() # 返回一个数据，格式是元祖
# print('one:',one)
res = cursor.fetchall() # 返回所有查询数据，是一个复合元祖
for one in res:
    print(one)
cursor.close() # 关游标
conn.close()   # 关闭数据库连接复制代码

上面的代码演示了查询语句的基本使用。

try:
    sql = "insert into students(name,age)values(%s,%s),(%s,%s)" # %s参数占位,插入两行
    cursor = conn.cursor() # 获取游标
    raw_count = cursor.execute(sql,['西门吹雪',25,'独孤求败',30]) #返回影响行数
    print('raw_count:',raw_count) 
    conn.commit() #提交事务
except Exception as e:
    print(e)
    conn.rollback() # 出现错误，对数据进行回滚
finally:    
    cursor.close()
    conn.close()复制代码

防止 sql 注入，用 %s 占位，可传入任意类型数据, %s 占位不需要带引号。 上面的代码演示了执行 sql 插入的操作。下面演示删除与更新 sql 的代码。注意一下 cursor 的 execute() 方法，如果传入第二个参数，可以是列表或元祖。

conn = pymysql.connect(host = 'None',user = 'root',
                       password='mysql',database='py',
                       port = 3306,charset = 'utf8')

try:
    # sql = "delete from students where id = %s" # 删除
    sql = "update students set age = %s where id = %s" # 修改
    cursor = conn.cursor() # 获取游标
    # raw_count = cursor.execute(sql,(9,))  #删除id=9的数据
    raw_count = cursor.execute(sql,(18,19)) # 修改
    print('raw_count:',raw_count)
    conn.commit() #提交事务
    res = cursor.fetchall() #遍历所有记录
    for one in res:
        print(one)
except Exception as e:
    print(e)
    conn.rollback() # 出现错误，对数据进行回滚
finally:    
    cursor.close()
    conn.close()复制代码