数据库基础总结《一》MySql数据库

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。 在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软 件。 MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就 增加了速度并提高了灵活性。 MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业 版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网 站数据库。

MySql环境安装配置 

windows下的较为简单，在这里就不做介绍了。

Ubuntu 下的安装

sudo apt-get install MySQL-server MySQL-client

设置root用户密码

在user/bin目录下运行 mysql_secure_installation，再根据提示运行设置，之后再重启mysql服务。

MySQL启动时命令为     sudo mysql -u root -p

服务管理命令
            service MySQL start
            service MySQL stop
或者 重启    service MySQL restart  

允许远程连接的配置方法：找到mysql配置文件并修改

sudo vi /etc/mysql/mysql.conf.d/mysql.cnf
将该文档中的bind-address=127.0.0.1 注释掉
注意：若文件找不到可以一步步cd |ls找到该文件

卸载命令：

sudo apt-get --purge remove mysql-server mysql-common mysql-client

相关术语

 主键：一个记录中有若干个属性，其中一个能唯一标识该记录，该属性就是主键 比如一条记录包括身份证号，姓名，年龄，身份证号是唯一确定这个人的，它就是主键

外键：外键是与另一张表的关联，能确定另一个表中的记录 比如： 有三个表： 客户表：记录客户的信息，如客户编号，客户名称，地址，联系方式等 商品表：记录商品的信息，比如商品编号，商品名称，品牌，单价，库存数量等 订单表：包括订单信息 一条订单记录：包括客户编号，商品编号，商品数量，金额等属性 客户编号是客户表中的主键，它就是订单表的外键

约束：一种限制，通过对表的行或列的数据做出限制，来确保数据的完整性、唯一性 比如：在订单记录中，指定的客户编码，必须是客户表中存在的客户 商品编号，必须是商品表中存在的商品。

表字段约束

• 主键  primary key
• 非空  not  null
• 唯一   unique
• 默认   default
• 外键    foreign key

表字段类型

常见类型

• int   整数       decimal    复数          float   浮点数
• 字符串：varchar   text
• 日期：datetime
• 布尔：bit

MySql  命令

连接命令
      打开黑窗口（终端）需要提前配置环境变量
      mysql -uuser -p
      -u后为用户   回车后要输入密码
      完整命令  mysql -h 192.168.12.200 -p 3306 -uroot -p
      -h 为主机地址    -p端口值

      退出登录
      quit/exit/(\q)

数据库操作

       显示所有数据库
       show databases;

       创建数据库
       create database 名;

       删除数据库
       drop database 名；

       切换数据库
       use 名；

       查看当前数据库
       select database();
  
       
表操作

       查看当前数据内的所有表
       show tables;

       创建表
       create table 名(列及类型);

       create table students(id int auto_increment primary key not null,
                             sname varchar(10) not null,
                             );
        
       修改表
       alter table 表名 add|change|drop 列名 类型；
       
       alter table 表名 add 列名 类型；--添加某一列
       alter table 表名 change 原列名 新列名 类型； --修改表的列属性名
       alter table 表名 drop 列名； --删除表的某一列
       alter table 表名 rename 新表名； --修改表名


      删除表
      drop table 表名;

      查看表结构
      desc 表名;


数据操作

       查询
       select * from 表名;     查询所有的表的内容

       
       增加
       
       全列插入：insert into 表名 values(...)
       缺省插入：insert into 表名(列1,...) values(值1,...)
       缺省插入：insert into 表名 set field1=value1,field2=value2
       同时插入多条数据：insert into 表名 values(...),(...)...;
      主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准
    
      修改
         update 表名 set 列1=值1,.....where 条件

      删除
          delete from 表名 where...;


外键

      MySQL创建关联表可以理解为是两个表之间有个外键关系，但这两个表必须满足三个条件
         1.两个表必须是InnoDB数据引擎
         2.使用在外键关系的域必须为索引型(Index)
         3.使用在外键关系的域必须与数据类型相似
      外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

      定义外键
             
      constraint name foreign key (teaid) references tea (id) on delet restrict on restrict;

     添加外键
     alter table stu add constraint name foreign key(teaid) references tea(id);

     删除外键
     alter table stu drop foreign key name;

     在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决   于：在定义子表的外键时指定的on update/on delete子句。


     CASCADE 删除包含与已删除键值有参照关系的所有记录
     SET NULL 修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL 的字段)
     RESTRICT 拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
     NOACTION 啥也不做


数据备份与恢复
     
     在终端窗口中执行：
                    mysqldump -uroot -p 数据库名 >~/Desktop/备份文件.sql;
                    按提示输入密码

     恢复：
         连接mysqk，创建数据库
         退出连接，执行如下命令
         mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql
         根据提示输入mysql密码


数据库的授权

      grant all on *.* to zzy@localhost identified by '1234'; 给与所有数据库权限
      grant select on db.stu to zzy@localhost identified by '1234'; 给与指定的数据库
      
      删除用户
      use mysql
      delete from user where user ='zzy';
      修改密码
      use mysql
      update user set password=password('新密码') where User="zzy" ;


知识进阶

      as 在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中

      distinct   消除重复行
                 在select后面列前使用distinct可以消除重复的行
                 select distinct gender from students;
        
      where   条件关键词
              使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
              select * from 表名 where 条件;

      比较运算符
                等于=  大于>  小于<  大于等于 >=   小于等于 <=   不等于 != / <>
                查询编号大于3的学生
                select * from students where id>3;
                查询编号不大于4的科目
                select * from subjects where id<=4;
                查询姓名不是“黄蓉”的学生
                select * from students where sname!='黄蓉';
                查询没被删除的学生
                select * from students where isdelete=0;

      逻辑运算符
                and  or  not
                查询编号大于3的女同学
                select * from students where id>3 and gender=0;

      模糊查询 
                like    %表示任意多个字符    _表示一个任意字符
                查询姓黄的学生
                select * from students where sname like '黄%';
                查询姓黄并且名字是一个字的学生
                select * from students where sname like '黄_';
                查询姓黄或叫靖的学生
                select * from students where sname like '黄%' or sname like '%靖%';

      范围查询
                in表示在一个非连续的范围内
                查询编号是1或3或8的学生
                select * from students where id in(1,3,8);

                between ... and ...表示在一个连续的范围内
                查询学生是3至8的学生
                select * from students where id between 3 and 8;
                查询学生是3至8的男生
                select * from students where id between 3 and 8 and gender=1;

      空判断
                注：null是空，而''并不是空，其代表有内容
                判空is null
                   查询没有填写地址的学生
                   select * from students where hometown is null;
                判非空is not null
                   查询填写了地址的学生
                   select * from students where hometown is not null;
                   查询填写了地址的女生
                   select * from students where hometown is not null and gender=0;

      优先级顺序
                小括号 > not > 比较运算符 > 逻辑运算符
                and比or先运算，若同时出现并希望or先运算则用小括号
       
      聚合函数
                count(*)表示计算总行数，括号中写星与列名，结果是相同的
                查询学生总数
                select count(*) from students;

                max(列)表示求此列的最大值
                查询女生的编号最大值
                select max(id) from students where gender=0;

                min(列)表示求此列的最小值
                查询学生最小编号
                select min(id) from students where gender=0;

                sum(列)表示求此列的和
                查询男生的编号之和
                select sum(id) from students where gender=1;

                avg(列)表示求此列的平均值
                查询女生的编号平均值
                select avg(id) from students where gender=0;

       分组
                按照字段分组，表示此字段相同的数据会被放到一个组中 
                分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中

                可以对分组后的数据进行统计，做聚合运算
                select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
                查询男女生总数
                select gender as 性别,count(*) from students group by gender;
                查询各城市人数
                select hometown as 家乡,count(*) from students group by hometown;  
       排序     
                为了方便查看数据，可以对数据进行排序
                select * from 表名 order by 列1 asc|desc,列2 asc|desc,...
                将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
                asc从小到大排列，即升序
                desc从大到小排序，即降序

                查询男生学生信息，按学号降序
                select * from students where gender=1 order by id desc; 

       分页     
                获取部分行
                当数据量过大时，在一页中查看数据是一件非常麻烦的事情
                select * from 表名 limit start,count
                从start开始，获取count条数据
                start索引从0开始
               
                示例：分页
                已知：每页显示m条数据，当前显示第n页
                求第n页的数据
                select * from students limit (n-1)*m,m

      内连接查询
        
                是指所有查询出的结果都是能够在连接的表中有对应记录的。
                select e.empName,d.deptName from t_employee e
                INNER JOIN t_dept d ON e.dept = d.id;

      左外连接

                是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null。
                SELECT e.empName,d.deptName
                from t_employee e
                LEFT OUTER JOIN t_dept d
                on d.id = e.dept;

      右外链接
 
                右外连接是同理的，只是基准表的位置变化了而已
                SELECT e.empName,d.deptName
                from t_employee e
                RIGHT OUTER JOIN t_dept d
                on d.id = e.dept;

      全外链接
                把两张表的字段都查出来，没有对应的值就显示null，但是注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。  
                select e.empName,d.deptName
                FROM t_employee e
                left JOIN t_dept d
                ON e.dept = d.id
                UNION
                select e.empName,d.deptName
                FROM t_employee e
                RIGHT JOIN t_dept d
                ON e.dept = d.id;

     自连接查询

                自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名
                select empName from t_employee where id in (select id from t_employee where id<10);


事务
   
      事务： 为了保证数据的完整性，成批的SQL要么全部执行，要么全部不执行。
      通常用来操作数据量大，复杂度高的数据
      事务通常用来管理update insert delete
      MySQL中只有使用innerDB引擎才支持事务
      ACID特性
      原子性：要么全部执行，要么全部不执行
      一致性 ：事务开始前结束后对数据的完整性不会破坏
      隔离性 ：允许多个并发事务同时对数据的读写，防止多个并发执行引起的不一致
      持久性：事务处理结束后，对数据的修改是永久的
      常用操作
      begin 开始事务
      rollback 回滚
      commit 结束事务
      想要使用事务必须使用INNODB引擎


Python操作MySQL数据库

      配置Python中的mysql模块
            Python3 中安装 pymysql
      
      Connection对象
            作用：建立与数据库的连接。
            创建对象：调用connect()方法,con = pymsql.Connect()
                     参数host：连接的mysql主机，如果本机是'localhost'
                     参数port：连接的mysql主机的端口，默认是3306
                     参数db：数据库的名称
                     参数user：连接的用户名
                     参数password：连接的密码
                     参数charset：通信采用的编码方式，默认是'gb2312'，要求与数据库创建时指定的 编码一致，否则中文会乱码

            对象的方法：
                     close()关闭连接
                     commit()事务，所以需要提交才会生效
                     rollback()事务，放弃之前的操作
                     cursor()返回Cursor对象，用于执行sql语句并获得结果

      Cursor对象
            作用：游标对象，用于操作执行sql语句
            创建对象：cursor = con.cursor()
            
            对象的方法
                close()关闭
                execute(operation [, parameters ])执行语句，返回受影响的行数
                fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
                fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
                fetchmany(n)执行查询时，获取n行，一行构成一个元组，再将这些元组装入一个元组返回
                next() 执行查询语句时，获取当前行的下一行()
                scroll(value[,mode])将行指针移动到某个位置
                mode表示移动的方式
                mode的默认值为relative，表示基于当前行移动到value，value为正则向下移动，value为负则向上移动
                mode的值为absolute，表示基于第一条数据的位置，第一条数据的位置为0
                对象的属性
                rowcount只读属性，表示最近一次execute()执行后受影响的行数


CURD(Create Update Read Delete)

        create:

               创建testInsert.py文件，向学生表中插入一条数据
               import pymysql
               try:
                  conn=pymysql.connect  (host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
                  cs1=conn.cursor()
                  count=cs1.execute("insert into students(sname) values('张良')")
                  print(count)
                  conn.commit()
                  cs1.close()
                  conn.close()
              except Exception as e:
                  conn.rollback()
                  print(e)

         update:

               创建testUpdate.py文件，修改学生表的一条数据
               
               import MySQLdb
               
               try:
                   conn=pymysql.connect (host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
                   cs1=conn.cursor()
                   count=cs1.execute("update students set sname='刘邦' where id=6")
                   print(count)
                   conn.commit()
                   cs1.close()
                   conn.close()
                except Exception,e:
                   conn.rollback()
                   print(e)


         read:
            
               创建testSelectOne.py文件，查询一条学生信息

               import pymysql

               try:
                   conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
                   cur=conn.cursor()
                   cur.execute('select * from students where id=7')
                   result=cur.fetchone()
                   result=cur.fetchall()
                   print(result)
                   cur.close()
                   conn.close()
              except Exception as e:
                   print(e) 


         delete:

                创建testDelete.py文件，删除学生表的一条数据

                import MySQLdb

                try:
                    conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
                    cs1=conn.cursor()
                    count=cs1.execute("delete from students where id=6")
                    print(count)
                    conn.commit()
                    cs1.close()
                    conn.close()
               except Exception,e:
                    print(e)


ORM_SQLALChemy

                   ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上。操作数据库像操作对象一样，是不是很简单？  
                   但是由谁来做这个转换呢？所以ORM框架应运而生。
                   在Python中，最有名的ORM框架是SQLAlchemy。

            框架安装
                   需要安装sqlalchemy以及mysql-connector-python两个模块

            操作流程：

#引入模块
import sqlalchemy
# print(sqlalchemy.__version__)
创建连接实例
from sqlalchemy import create_engine
engine = create_engine("mysql+mysqlconnector://root:123456@localhost/sqlalchemytest")
# result = engine.execute("show tables")
# print(result.fetchall())
创建会话
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine); #通过bind 参数使Session连接到数据库
# print(Session)
创建对应于MySql数据表的类
#新建类
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String #需要列的类型
Base = declarative_base() #Base为所有自定一类的父类
class Test(Base):
    __tablename__ = "test" #必须写 决定于mysql那个表关联
    id = Column(Integer, primary_key=True) # 关联到表中id列
    name = Column(String(45))

与数据库交互
session = Session()
#查询
result = session.query(Test.id,Test.name).filter(Test.id==1).first()
print(result)
#添加
# session.add( Test(id=0,name='wangzhenxing'))
# session.commit()
#修改
# t5 = session.query(Test).filter(Test.id == 5).first()
# t5.name="caoxiang"
# session.commit()
# session.query(Test).filter(Test.id == 4).update({Test.name:"456" })
# session.commit()
#删除
# t4 = session.query(Test).filter(Test.id==4).first()
# session.delete(t4)
# session.commit()
# session.query(Test).filter(Test.id==2).delete()
# session.commit()