python学习第十一天（MySQL)

一、MySQL介绍

    #MySQL(关系型数据库管理系统）
    MySQL是一个有关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS（Relational Datbase Management System,关系数据库管理系统）应用软件。
    MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。由于体积小，速度快，总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。

    #存储引擎
    #在此先介绍几个
    MylSAM:MySQL5.0之前默认的数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务。
    InnoDB：事务型数据库的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5起成为默认数据库引擎。
    BDB: 源自Berkely DB,事务型数据库的另一种选择，支持Commit和Rollback等其他事物特性。
    Memory:所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MySQL重新启动时丢失。
    Cluster/NDB:高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用。
    CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.csv文件。是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。

 　　#主键（primary key)

　　　　表中每一行都应该有一列（或几列）可以唯一标识自己。顾客表可以使用顾客编号，而订单表可以使用订单ID。雇员表可以使用雇员ID。

　　　　唯一标识表中每行的这个列（或这几个列）称为主键。主键用来表示一个特定的行。没有主键，更新或删除表中特定行就极为困难，因为你不能保证操作只涉及相关的行。

　　　　<<<提示：应该总是定义主键，虽然并不总是需要主键，但多数数据库设计者都会保证他们创建的每一个表具有一个主键，以便于以后的数据操作和管理>>>

　　　　#满足主键的条件：

　　　　　1、任意两行都不具有相同的主键值

　　　　　2、每一行都必须具有一个主键值（主键列不允许NULL值）　

　　　　　3、主键列中的值不允许修改或更新

　　　　　4、主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

　　　主键通常定义在表的一列上，但并不是必须这么做，也可以一起使用多个列作为主键。

二、SQL语句：

    #了解一下SQL语句：
    我们需要操作MySQL则需要用到的有：
    创建数据库：
        create database 数据库名;
        create database dbname default charset=utf8;#为数据库添加默认字符编码
    查看数据库：
        show databases; #查看有几个数据库
    删除数据库：
        drop database 数据库名；
    创建数据库中的表：
        create table 表名 engine=innodb default charset=utf8; #engine表示使用的引擎
        create table 表名 (sid int not null auto_increment primary key,student_id int,course_id int,number int,)engine=innodb default charset=utf8; #创建表的同时创建表中的列。
    删除表：
    drop table 表名;
    插入表中的数据：
        insert into 表名 （列名，列名1,....) values (列值，列值1,...);
    更新表中的数据：
        update 表名 set 列名=修改后的值; #会更新该列所有的数据
        update 表名 set 列名=修改后的值 where 条件语句;#更新符合条件的语句。
        删除表中的数据：
        delete from 表名 [where Clause] #不指定where语句，删除表中的所有记录。
        #指定wher子句则删除where条件范围内的。
    

    查询表中的数据：
        select * from 表名;#查询表内所有列的内容
        select * from 表名 where 条件语句; #显示表内符合条件语句的内容。
        select 列名，列名1，... from 表名; #显示表中某几列的内容。
　　　　 select distinct 列名 from 表名; #显示非重复的查询结果。（若使用DISTINCT关键字，它必须直接放在列名的前面）
　　　　 select 列名 from 表名 limit 5; #显示前5行的结果
　　　　 select * from 表名 order by 列名; #根据某列进行排序，默认的排序为升序（asc) 降序排序为desc.
　　　　#in关键字的用法
　　　　 select * from 表名 where 列名 in(列值1，列值2，...) obder by 列名; #查询in关键字给定的范围后，以order by 后的列做升序排序。相当于or语句（select * from 表名 where 列名=列值1 or 列名=列值2，...) obder by 列名;）
　　　　　　#使用in操作符的优点：
　　　　　　　　1、在有很多合法选项时，IN操作符的语法更清楚，更直观。
　　　　　　　　2、在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
　　　　　　　　3、IN操作符一般比一组OR操作符执行得更快。
　　　　　　　　4、IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。
　　　　#like操作符的用法：
　　　　　　通配符%：
　　　　　　select * from 表名 where 列名 like "查找关键字%“; #通配符%的使用，示例：select * from products where prod_name like "Fish%"; %代表搜索模式中给定位置的0个，1个，或多个字符。
　　　　　　<<< 请注意NULL： 通配符%看起来像是可以匹配任何东西，但有个例外，就是NULL。子句WHERE prod_name LIKE "%" 不会匹配产品名称为NULL的行。>>>
　　　　　　通配符_:
　　　　　　select * from 表名 where 列名 like "查找关键字_“; #通配符%的使用，示例：select * from products where prod_name like "Fish_"; _代表搜索模式中给定位置的1个，匹配单个字符，若是两个_则匹配两个字符。
　　　　　　通配符[]：
　　　　　　select * from 表名 where 列名 like "[关键字1关键字2]%"; #通配符[]的使用，示例：select * from customers where cust_contanct like '[JM]%' order by cust_contact; WHERE子句中的模式为'[JM]%'.
　　　　　　[JM]匹配任何以方括号中字母开头的联系人名，它也只能匹配单个字符。因此任何多于一个字符的名字都不匹配。而[JM]之后的%匹配第一个字符之后的任意数目的字符，返回所需要结果。
　　　　　　此通配符可以用前缀字符^（脱字号）来否定。例如：'[^JM]%'表示非J或M开头的联系人名。
　　　　　　<<< 通配符使用的技巧>>>
　　　　　　　　1、不要过度使用通配符。若其他操作符能达到目的，则用其他操作符。
　　　　　　　　2、在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
　　　　　　　　3、仔细注意通配符的位置。若放错地方，可能不会返回想要的数据。

    #分组数据：GROUP BY子句和HAVING子句。
        #分组是使用SELECT语句的GROUP BY子句建立的。
        select vend_id,count(*) as num_prods from procucts group by vend_id; #SELECT 语句指定了两个列：vend_id包含产品供应商的ID，num_prods为计算字段（用COUNT（*）函数建立）。GROUP BY 子句指示DBMS按vend_id排序并分组数据。
　　　　　　这就会对每个vend_id而不是整个表计算num_prods一次。
    <<<在使用GROUP BY子句前，需要知道一些重要的规定>>>
    1、GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致的进行数据分组。
    2、如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
    3、GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
    4、大多数SQL实现不允许GROUP　BY列带有长度可变的数据类型（如文本或备注型字段）。
    ５、除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
    6、如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
    7、GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
　　　　#过虑分组 HAVING
　　　　　　HAVING子句用在GROUP BY子句后过，用于过滤条件（相当于select后的where)
　　　　　　select cust_id,COUNT(*) as orders from Orders GROUP BY cust_id HAVING COUNT（*）>=2 #显示Orders表中，相同cust_id的个数大于等于2的

    #关联表JOIN
　　　# select * from student left join class on student.class_id = class.cid;
　　　#left join 关联的表 on 关联的条件
　　  #left join 跟左面的表里面相关的数据都列出来
　　  #right join 跟右面的表里面相关的数据都列出来
　　　#inner join 两个表进行关联，如果出现有null的值，则不显示出来（删除相应的数据）

    #UNION用于合并两个或多个SELECT语句的结果集，并消去表中任何重复行。
    #UNION内部的SELECT语句必须拥有相同的数量的列，列也必须拥有相似的数据类型。
    同时，每条SELECT语句中的列的顺序必须相同。
    #语法示例：
        SELECT column_name FROM table1
        UNION
        SELECT column_name FROM table2
    <<<默认的，UNION操作符选取不同的值。如果允许重复的值，则使用UNION ALL。当ALL和UNION一起使用时（即UNION ALL），不消除重复行。

    #外键：
    #在已经有的表中添加外键：
    alter table userinfo add foreign key(department_id) references department(did) on delete cascade;
    #userinfo为需要添加外键的表，department_id为表中的列名（需要添加外键的列），department为被关联的表，did为department中的列名（被关联的id）.
    #在已有的表中删除外键：
    alter table userinfo drop foreign key department;
    #userinfo为需要删除外键的表，department为要删除的外键名
    #查询表中是否有外键：
        show create table userinfo\G;
        #userinfo为表名

 

三、Python下的MySQL模块

   #导入MySQL模块
    import pymysql #在python2中导入的是MySQLdb模块，python3中导入的是pymysql
    #连接MySQL
       conn=pymysql.Connect(host="host_ip",port="host_port",name="username",password="pwd",database="dbname",charset="utf8")
    　　cur=conn.cursor()#生成元组形式的结果，  conn.cursor(pymysql.cursors.DictCursor) #生成字典形式的结果　　
    　　v=cur.execute(SQL查询语句)
    　　v.feathall()#查找所有的记录，
    　　v.featchone()#查找一条记录，
    　　v.featchmany([n,m])#查找指定的记录条数
　　　　 v=cur.execute(SQL语句<增，删，改>）
　　　　 conn.commit()#执行增，删，改操作后需要提交动作
　　　　 cur.lastrowid #可获取新增的自增ID号
    　　cur.close()
　　　　 conn.close()