MySQL初学者，以及Python操作MySQL

MySQL数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来* 像一个简单的电子表格。
• 列: 一列(数据元素)包含了相同的数据。字段，如果能够唯一标记某个字段，那么这就是主键。
• 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。

数据类型：

原则：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省内存空间。

数值类型：严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

• TINYINT:小整数值
• SMALLINT:大整数值
• MEDIUMINT：大整数值
• INThuoINTEGER：大整数值
• BIGINT：极大整数值
• FLOAT：单精度浮点数值
• DOUBLE：双精度浮点数值
• DECIMAL：小数值

特别说明的类型如下：

• decimal表示浮点数，如decimal(5,2)表示共存五位数，小数占2位。
• char表示固定长度的字符串，如char(3),如果填充’ab’时就会补一个空格
• varchar表示可变长度的字符串，如varchar(3),填充’ab’时就会储存’ab’.
• 字符串text表示储存大文本，当字符大于4000时推荐使用。

*链接数据库：
mysql -uroot -p
mysql -uroot -pmysql

• 退出数据库：
  exit/quit/ctrl+d
• 显示数据库版本：
  select version();
• 显示时间：
  select now();
• 查看所有数据库：
  show databases;
• 创建数据库：
  使用create命令创建数据库。
  create database 数据库名;

create database 数据库名
charset=utf8;

• 查看创建数据库的语句：
  show create database 数据库名;
• 查看当前使用的数据库：
  select database();
• 使用数据库
• use 数据库的名字：
  use 数据库名称。
• 删除数据库：
  drop database 数据库名;

drop table 表名；

数据表的操作

• 查看当前数据库中所有表：
  show tables;
• 创建表
• auto_increment表示自动增长
• not null 表示不能为空。
• primary key 表示主键。
• default 默认值。
• creat table 数据库名称(字段 类型 约束[,字段 类型 约束])
• 创建classes表(id,name)：

create table xxxxx(id int,name varchar())

create table yyyyy(id int primary key not null auto_increment , name varchar(30))

• desc 数据表的名字（查看表的结构的详细信息）：
  desc xxxxxx;
• 查看表的创建的语句：
  show create table 表的名字;
• 添加字段：
  alter table 表名 add 列明 类型;
• 修改字段：不重命名版
  alter table 表名 modify 列名 类型及约束;
• 修改字段：重命名版
  alter table 表名 change 原名 新名 类型及约束;
• 删除字段
  alter table 表名 drop 列名;

增删改查：

• 增加：
• 全列插入：

insert [into] 表名 values(…)

主键字段 可以使用 0 null default 来占位。

向classes表中插入 一个班级:

insert into classes values(…)

如果你的第一个字段为自增，插入操作要这样写：insert into 表名 (列1, 列2,…) values (值1, 值2,…)

枚举中的下标从1开始

• 部分插入
  insert into 表名(列1，列2，…)values(值1,值2,…);

insert into students (name,gender) values (“小乔”,2)

insert into students values(全部值);

• 修改
  udpate 表名 set 列1 = 值1,列2 = 值2… where 条件;

update students set age=22,gender=1 where id=3;

• 删除
  物理删除
  delete from 表名 where 条件

逻辑删除

用一个字段来表示，这条信息是否已经不能再使用了。

给student表添加一个is_delete字段 bit 类型。

alter table stdents add is_delete bit default 0;

update students set is_delete=1 where id=6;

• 查询的基本使用
  查询所有列表：

select * from 表明;

一定条件查询：

select * from students where id>3;

查询指定序列：

select 列1,列2,列3,…from 表名;

可以使用as为列或表指定别名

select 字段 as 别名,gender as 性别 from students;

字段的顺序：

select id as 序号, genter as 性别, name as 性别 from students;

查询：

• 查询所有字段:
  select * from 表名;

• 查询指定字段：
  select 列1,列2,…from 表名;

• 使用as给字段起别名
  select 字段 as 名字… from 表名;
  select 表名.字段… from 表名

• 可以通过as给表起别名：
  select 别名.字段… from 表名 as 别名;

这样写是错误的:
select students.mame,students.age from students as s;

如果起了别名就要用

• 消除重复行
  distinct 字段;

如：select distinct time from xxxx;

条件查询

比较运算符：

>

<

>=

<=

=

!=

select 字段 from 表名 where …

例如：
select * from students where age>18;

逻辑运算符：

• and
  如：18岁到20岁之间的所有学生信息：
  select * from students where age>18 and age<20;

18岁以上的女性
select * from students where age>18 and gender=“女”;

• or

如：18岁以上或者身高超过180以上：
select * from students where age>18 or high >180;

• not

不在 18岁以上的女性

select * from students where not(age >18 and gender = “女”);

模糊查询

• like

% 替换1个或者多个

_ 替换一个

例如：查询姓名中 以“小”开始的名字：
select * from students where name like “小%”;

带有“小”的名字：
select * from students where name like “%小%”;

• rlike 正则

如：查询以周开始:

select * from students where name rlike “^周.*$”;

范围查询：

• in(1,8,213)表示在一个非连续的范围内

查询 年龄为18，34的姓名：

select name,age from students where age in (18,34);

select name,age from students where age = 18 or age = 34;

• not in 不非连续的范围之内

年龄不是为18，34的姓名：

select name,age from students where not in(18,34);

• between…and…表示在一个连续的范围内

查询 年龄在18到34之间的信息：

select name,age from students where age between 18 and 34;

• not between … and…表示不在一个连续的范围内

查询 年龄不在18到34之间的信息

select * from students where age not between 18 and 34;

select * from students where not age between 18 and 34;

• 空判断
  判断 is null

非空 is not null

排序

• order by 字段
• asc从小到大排列，即升序。
• desc从大到小排序，即降序。

查询年龄在18到34之间的男性，按照年龄从小到大排序:

select * from students where (age between 18 and 34) and gender = “男” order by age;

order by 默认从小到到大排

select * from students where (age between 18 and 34) and gender = “男” order by age asc;

查询年龄在18到34之间的女性，按照身高从高到低排序:

select * from students where (age between 18 and 34) and gender = “女” order by height desc;

• order by 多个字段

查询年龄在18到34之间的女性，按照身高从高到低排序，如果身高相同的情况下按照年龄从小到大排序：

select * from students where (age between 18 and 34) and gender = “女” order by height desc ,id desc;

必须先判断order by 后边的第一个再判断后边的

按照年龄从小到大，身高从高到矮的排序：

select * from students order by age asc,height desc;

having是在先排序好的再寻找。

聚合函数

• 总数
• count

查询男性有多少人，女性有多少人：

select count(*) as 男性人数 from students where gender = “男”;

• 最大值
• max

查询最大年龄:

select max(age) from students
查询女性的最高身高：

select max(height) from students where gender = “女”;

• 最小值:min

• 求和:sum

• 平均值:avg

• 四舍五入 :round(结果 , 1) 保留一位小数

计算所有人的平均年龄，保留2位小数

select round(sum(age)/count(*),2) from students;

分组

• gruop by

按照性别分组，查询所有的性别：

select gender from students gruop by gender;

计算每种性别中的人数：

select gender,count(*) from students group by gender;

count()函数是对分组后进行处理。

计算男性的人数：

select gender,count(*) from students where gender =1 group by gender;

• group_concat(…)

查询同种性别中的姓名：

select gender,group_concat(name,"_",age,"",id) from students where gender = 1 group by gender;

因为在group_concat()函数里面数字结果会连在一起。

分页：

• limit start,count

限制查询出来的数据个数：

select * from students where gender = 1 limit 2;

查询前五个数：

select * from students limit 0,5;

每页显示2个，第3个页面：

select * from students limit 4,2;

规律：

limit(第n页-1)*每个的个数，每页的个数;

每页显示2个，显示第6页的信息，按年龄从小到大排序：

select * from students where gender = 2 other by height desc limit 10,2;

连接查询

• 内连接查询：查询的结果为两个表匹配到的数据。

• inner join … on

• select … from 表A inner join 表B;

select * from students inner join classes;

查询有能够对应班级的学生以及班级：

select * from students inner join classes on students.cls_id=classes.id;

按要求显示姓名，班级：

select students.*,classes.name from students inner join classes on students.cls_id=classes.id;

给表起名字：

select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;

select c.xxx,s.xxx from students as s inner join classes as c on … order by …;

当同一个班级的时候，按照学生的id进行从小到大排序：

select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by s.id;

• 左连接查询：查询的结果为两个表匹配到的数据。
• left join

查询每位学生对应的班级信息

select * from students as s left join classes as c on s.cls_id=c.id;

• 右连接查询：查询的结果为两个表匹配的数据。

自关联

查询出山东省有哪些市：

select * from areas as provice inner join areas as city on city.pid=provice.aid
having province.title=“山东省”;

三范式

目前有迹可寻的共有八种范式，一般需要遵守3范式即可。

• 第一范式：强调的是列的原子性，即列不能够再分成其他几列。
• 第二范式：首先是第一范式，另外包含两部分，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖与主键，而不能只依赖于主键的一部分。
• 第三范式：首先是第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖，即不能存在；非主键列A依赖于非主键列B,非主键列B依赖于主键的情况。

外键的约束：对数据的有效性进行验证。

• 关键字：foreign kry,只有innodb数据库引擎支持外键约束。
• 对于已经存在的数据表如何更新外键的约束：
• reference:引用后边的内容。

alter table goods add foreign key (cate_id) reference goods_cates(id);

尽量不要使用外键，这样会大大降低性能。

用python操作MySQL数据库：

开始，创建connection,获取cursor，(执行查询，执行命令，获取数据，处理数据)，关闭cursor,关闭connection,结束

• fetchone()：每次只取出一个
• fetchmany(n)：n为取出的个数
• fetchall()：取出所有

查询一行数据

from pymysql import *
# 创建Connect连接
coon = connect(host='local host',port=3306,user='root',password='mysql',database='jing_dong')
# 获得Cursor对象
cs1 =conn.cursor()
# 执行select语句，并返回受影响的行数，查询一条数据
count = cs1.execute('select id,name from goods where id>=4')
# 打印受影响的行数
print("查询到的%d条数据：" % count)

for i in range(count):
# 获取查询的结果
    result = cs1.fetchone()
#打印结果
    print(result)

关闭Cursor对象
cs1.close()
conn.close()

if __name__ == "__main__":
    main()

模拟京东

from pymysql import connect


class JD(object):
    def __init__(self):
        # 创建Connection连接
        self.conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing_dong', charset='utf8')
        # 获得Cursor对象
        self.cursor = self.conn.cursor()

    def __del__(self):
        # 关闭Cursor对象
        self.cursor.close()
        self.conn.close()

    def execute_sql(self, sql):
        self.cursor.execute(sql)
        for temp in self.cursor.fetchone():
            print(temp)

    def show_all_items(self):
        """显示所有的商品"""
        sql = "select * from goods;"
        self.execute_sql(sql)

    def show_cates(self):
        sql = "select name from goods_cates;"
        self.execute_sql(sql)

    def show_brands(self):
        sql = "select name from goods_brands;"
        self.execute_sql(sql)

    def add_brands(self):
        item_name = input("输入新商品分类的名称")
        sql = """insert into goods_brands (name) values("%s")""" % item_name
        self.cursor.execute(sql)
        self.conn.commit()

    def get_info_by_name(self):
        find_name = input("请输入要查询的商品的名字")
        # sql = """select * from goods where name='%s';""" % find_name
        # self.execute_sql(sql)
        sql = "select * from goods where name=%s"
        self.cursor.execute(sql, [find_name])
        print(self.cursor.fetchall())

    @staticmethod
    def print_menu():
        print("---京东----")
        print("1: 所有商品")
        print("2: 所有的商品分类")
        print("3: 所有的商品品牌分来")
        print("4: 添加一个商品分类")
        print("5: 根据名字查询一个商品")
        return input("请输入功能对应的序号：")

    def run(self):
        while True:
            num = self.print_menu()
            if num == "1":
                # 查询所有商品
                self.show_all_items()
            elif num == "2":
                # 查询分类
                self.show_cates()
            elif num == "3":
                # 查询品牌分类
                self.show_brands()
            elif num == "4":
                # 添加商品分类
                self.add_brands()
            elif num == "5":
                # 根据名字查询商品
                self.get_info_by_name()
            else:
                print("输入有误，重新输入...")


def main():
    # 1.创建一分京东商城对象
    jd = JD()

    # 2.调用这个对象的run方法，让其运行
    jd.run()


if __name__ == '__main__':
    main()

视图

• 通俗的讲视图就是一条SELECT语句执行后返回的结果集，所以我们在创建视图的时候，主要的工作就落在创建这条SELECT查询语句上。

• 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据(数据表数据发生了改变，视图也会跟着改变)

• 提高了重用性，就像一个函数。

• 对数据库重构，却不影响程序的运行。

• 提高了安全性能，可以对不同的用户

• 让数据更加清晰。

create view 想要起的名字 as 想要查询的结果。

将刚刚查询的结果，只作为一张虚拟表，这就是视图。

事务

• 开启事务，变更会维护到本地缓存中：
  begin 或者 start transaction;

• 提交事务，将缓存中的数据变更维护到物理表中：
  commit

回滚事务：

• 放弃缓存中变更的数据：
  rollback;

索引：

• 开启运行时间监测:

set profiling=1;

• 查看运行的时间:

show profiling;

• 查看索引：
  show index from 表名;

• 创建索引：
  如果指定的字段是字符串，需要指定长度，建议长度与定义字段时的长度一致。

字段类型如果不是字符串，可以不用填写长度部分

create index 索引名称 on 表明(字段名称(长度));

• 删除索引：
  drop index 索引名称 on 表名;

这里需要特别注意的是，执行完excute那一行语句以后，一定一定要记得写commit语句，不然的话，数据库插入操作是不会成功的！！！

如：cursor.connection.commit();
y

创建账户&授权

grant 权限列表 on 数据库 to ‘用户名’@‘访问主机’ identified by ‘密码’;
示例1

创建一个laowang帐号，密码为123456，只能通过本地访问，并且只能对jing_dong数据库中的所有表进行读操作
step1:使用root登录

回车后写密码，然后回车

step2:创建账户并授予所有权限

grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';

说明

• 可以操作Python数据库的所有表，方式为：jing_dong.*
• 访问主机通常使用百分号%表示此账户可以使用任何ip的主机登录访问此数据库
• 访问主机可以设置成localhost或具体的ip，表示只允许本机或特定主机访问
• 查看用户有哪些权限

show grants for laowang@localhost;

step3:退出登录
quit

刷新权限

flush privileges；

修改密码

使用root登录，修改mysql数据库的user表

• 使用password()函数进行密码加密

update user set authentication_string=password('新密码') where user='用户名'
例：
update user set authentication_string=password('123') where user='laowang';

• 注意修改完成后需要刷新权限

刷新权限：flush privileges

删除账户

• 语法1：使用root登录

drop user '用户名'@'主机';
例：
drop user 'laowang'@'%';

• 语法2：使用root登录，删除mysql数据库的user表中数据

delete from user where user='用户名';
例：
delete from user where user='laowang';

—— 操作结束之后需要刷新权限
flush privileges

• 推荐使用语法1删除用户，如果使用语法1删除失败，采用语法2方式

mysql主从

• 读写分类
• 数据备份
• 负载均衡

备份
运行mysqldump命令

mysqldump -uroot -p 数据库名 > python.sql
# 按提示输入mysql的密码

恢复

• 连接mysql，创建新的数据库
• 退出连接，执行如下命令

mysql -uroot -p 新数据库名 < python.sql
# 根据提示输入mysql密码

在主服务器Ubuntu上进行备份，执行命令：

mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

说明

• -u：用户名
• -p：示密码
• –all-databases:导出所有数据库
• –lock-all-tables:执行操作时锁住所有表，防止操作时有数据修改
• ~-master_db.sql:导出的备份数据（sql文件）位置，可自己指定