Mysql数据库学习笔记（一）

Mysql：结构化查询语言，是在关系数据库中应用语言，也是一门编程语言，用于实现数据库管理与信息检索，用于访问和操作数据库，主要包括数据定义、数据操作、数据查询、数据控制。

5.7版本使用时间长，资料容易查找（官网mysql.com）MySQL Community Server/MySQL Workbench/ Connector/Python

sql能做什么？

1. 可以创建和管理数据库、数据表、存储过程、视图等。

2. 可以向数据库中插入新的记录，并可进行修改、删除

3. 查询数据库中记录

4. 可以对数据库进行事务控制和权限管理

• DDL数据定义语言，通过这些语句来创建数据库，数据表，字段，主键，索引等等，包含：create、drop、alter等

• 创建数据库：create  database mydb;show databases;select database();use mydb;

• 建表语句

• 字段类型
  

• 整数型TINYINT/SAMLLINT/MEDIUMINT/INT/BIGINT、对应字节长度1，2，3，4，8

• 小数型FLOAT(M,D)/DOUBLE(浮点)/DEC(M,D)或DECIMAL(M,D):M为全部位数，D为小数位。商城开发用DECIMAL(M,D)
  为了保证最大可能的可移植性，需要使用近似数值数据值存储的代码应使用FLOAT或DOUBLE PRECISION ,不规定精度或位数。

• 字符串类型CHAR(M)固定M长度，M在0~25之间；VARCHAR(M)可变长度，M为0~65535；TEXT长度0~65535字节，值得长度+2个字节；MEDIUMTEXT长度0~167772150字节，值长度+2个字节（一行最大长度：mysql中一行最大长度是65535字节）

• 日期时间类型DATE

  

• 枚举类型

  

• 示例，创建表(create database mydb;use mydb;)展示方式show fields from users \G;或者desc tablename

  

  

  

• 删除数据表：drop table tablename

  

• 创建数据表

• 存储引擎（涉及存储引擎概念，所谓存储引擎就是将数据按照一定结构存储的方式，从而带来不同的性能选择）

• ‍查看mysql支持的引擎：show engines；

• innodb引擎：mysql默认的引擎，支持事务，行锁（大多数情况下）

• myisam引擎：早期版本默认的引擎，具有查询速度快，但是不支持事务，只能锁表（经常读，写入少）

• memory引擎：内存引擎，用于需要快速访问的临时数据（临时性的数据，需要快速读取写入，不常用）

• Archive引擎：归档，且有压缩机制，适用于历史数据归档。

• csv引擎：逻辑上由逗号分隔数据，会为每张表创建一个.csv文件。‍

• mysql数据库类型

  

• 字符编码：不同的编码方式占用不同的字节，utf-8中文占用3个字节。如果程序与数据库编码不一致，可能导致乱码。大多数情况下，网页，程序，数据库都选择utf-8，以保持一致。

  
  

• DML数据操作语言，通过这些语句来插入、更新、删除数据包含：insert、updata、delete

• 插入数据(三种方式插入)
  

  insert into table（field1，field2，....） values(val1,val2,...)不用列出所有得字段，未指定值的列使用默认值或者null
insert into tabel values（val1，val2，....）值与字段数量匹配
insert into tabel set field1=val1，field2=val2..... 当关键字段设置为自增长，关键字可不写

  

  
  

• mysql连接器安装

• pip install mysql-connector-python

• 插件手册：https://pypi.org/project/mysql-connector-python/

• python+mysql connector(python连接数据库)

  

  

  import mysql.connector
连接数据库
cnx= mysql.connector.connect(user="root",password="root",host="127.0.0.1",database="mydb")
创建游标对象cursor
cursor=cnx.cursor()
通过cursor对象执行sql语句
比如：cursor.execute(),执行sql
               cursor.fetchall(),执行查询
事务提交
cnx.commit()
事务回滚。事务通常需要执行一组sql语句。
cnx.rollback()
关闭游标
cursor.close()
释放连接资源
cnx.close()

  

• 执行插入

  delete语句：用于从数据表中删除数据记录

• delete语句：用于从数据表中删除数据记录

  delete from table where condition；将符合condition的记录从数据表中删除；不带where删除所有记录
例子1：delete from users limit 10 删除10条会员记录
例子2：delete from users where id>10 and id<15;删除id在10~15之间的会员记录。

  

• update语句：用于更新数据表的记录

• update table set field1=new_val1,field2=new_val2 where condition
例子1：update users set age=20，sex="男" where id=15（将id=5的会员年龄改为20，性别改为男）
更新、删除均需要cnx.commit()提交事务才能真正更新，不然发生回滚操作无效。
  

• 锁的概念。修改或删除先后顺序

  

  

  update prize set winner="zhangsan" where p_id=1 and winner is null更新
update的时候会给这一行加锁（innodb引擎）锁的机制相对比较复杂，通常情况下，mysql会自动加锁，在对锁概念理解不够清洗情况下，不要手动加锁。

  




• DQL数据查询语言，通过这些语句检索数据，包含：select、where、order by、group by等

• select语句结构

• 从表中检索所有字段 ：select * from table

• 从表中检索特定字段：select field1，field2，field3 from table

• 更改查询后名称：select field as un from table as u

• where条件查询（根据一定的条件condition进行查询）

• select * from table where condition（在SQLAlchemy中，就是使用filter或filter_by方法）

• =，>,<,>=,<=,!=或<>,<=>null值相等，is null，is not null

• between-and区间查询

• select * from users where user_id>100

• select * from users where user_id<>100

• select * from table where field between min and max；

• select * from users where age between 18 and 25

• 单条件查询。在sql中，insert、update、delete、select后面带where子句，用于插入、修改、删除或查询指定条件的记录。

• 多条件查询。在where子句中，使用and、or可以把两个或多个过滤条件结合起来。

• in-存在于指定集合，允许过滤某个字段的多个值。

• select * from users where user_id in (100,110,112);

• select * from users where age in (20,23)

• 模糊查询：like-通配符%匹配，%表示任意一个字符，_表示一个字符；如果没有%和_，相对于运算符=，查询包含xx字符串的所有记录。

• select * from users where username like “%luxp%”

• select * from users where username like“_xp%"

• select * from users where username not like"_xp%"

• regexp-正则表达式匹配

• select * from users where username regexp "l[a-z]+x";

• sql的统计与排序语句

• limit限制查询记录数：如果记录数很对的时候，通常不会在一次查询中取出全部结果，而是通过limit来限制

• select * from table limit offset_start,row_count
  offset_start:偏移起始位置，默认0，查询结果不包括起始位置row_count:记录数

• 例子1：select * from users limit 0,10；取出前十条会员记录

• 例子2：select * from users limit 10 ；取出前十条会员记录，默认offset_start

• 例子3：select * from users limit 10,15 从偏移点10的位置向后取出15条会员记录

• count统计：如果想通过limit 方法来实现翻页的话，需要知道总页数，那么就需要知道有多少条记录。

• select count(1) from table 统计记录数

• select count(field) from table（ field为null的不统计）

• select count(*) from table (全部列统计)

• 例子：select count(*) from users where city=021

• sum-求和：sum对某个列进行求和，count是统计有多少条记录

• select sum(field) from tables

• 例子1-统计上海会员的money总额：select sum(money）from users where city=021

• 例子2-统计比较会员age总和：select sum(age) from users where city=010

• avg-求均值：相对于sum(field)/count()

• select avg(field) from tables

• 例子1-查询上海会员的money平均值：select avg(money) from users where city=021

• 例子2-统计北京会员age的平均值：select avg(age) from users where city=010

• group by-分组统计：将之前的聚合查询进行分组查询

• select func from tables group by field

• 例子1-查询各城市最有money的人：select max(money) from users group by city

• 例子2-统计各城市会员数据：select count() from users group by city

• 例子3-统计各城市会员中男性的数量：select count() from users where sex="男" group by city

• 多字段分组：select count(*),age,city from users group by city,age;

• group_concat

• 应用场景：group by 可以分组统计数量，具体的数量清单统计

  

• group_concat 配合group by 一起使用，用于将某一列的值按照指定的分隔符进行拼接。默认逗号

• distinct去重

• select distinct column_name,column_name from table_name;

• having-对查询结果过滤

• where 条件语句是对field 字段值进行过滤查询，有时候我们需要对查询结果字段进行过滤，比如对count的结果中大于某个值进行过滤，这时候需要having，对分组数据进行筛选

• select count(*) as num from tables group by field having num >20

• order by -排序

• select * from tables order by field desc|asc（降序或升序）

• 例子1-根据用户id降序排序：select * from users order by user_id desc

• 例子2-根据用户id升序排序：select * from users order by user_id asc

• select count(*) as num,age,city from tables group by city,age order by num desc

• 翻页实现步骤

• 1、根据条件，使用count()获取记录总数total

• 2、设定每页显示记录数pagesize

• 3、总页数计算公式ceil往上取整 ：total_pages=ceil(total/pagesize)

• 4、根据当前页数计算查询偏移：start=(current_page-1)*pagesize

• 5、使用limit获取得相应记录：limit start，pagesize

• 组合条件

• and或者or进行条件组合

• select * from users where （username like "%luxp%）and(age>20);

• select * from users where (username like "_xp%”) or （username like "_uxp%"）

• select * from users where (age>20) or (age<18)

• 查询示例

• 通过mysql.connector查询
  import mysql.connector as cOnnectorcnx=connector.connect(user="root",password="root",host="localhost",database="mydb")cursor=cnx.cursor()sql="select * from mydb.new_table where passward=123456"cursor.execute(sql)res=cursor.fetchall()print(res)

• 通过sqlalchemy查询

• pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple

• pip install pymysql

• 需要安装sqlalchemy。

• DCL数据控制语句，通过这些语句控制数据访问权限，包含：grant，commit、rollback

• mysql内置函数

MySQL数据库提供的内置函数，包括数学函数、字符串函数、日期和时间函数、聚合函数、条件判断函数等，这些内置函数可以帮助用户更方便地处理表中的数据，简化用户的操作。



• now()用于返回当前的日期和时间：select now();在实际应用中，大多数业务表都会带一个创建时间的create_time字段，用于记录每一一条数据产生时间。在向表中插入数据时，就可以在insert 语句中使用now()函数。

• insert into user(id,name,creat_time) values (1,"chenquan",now());

• date_format():用于指定格式显示日期/时间

• select name,date_format(birthday,"%Y/%m/%d") from user;

• 聚合函数：是对一组值进行计算，返回单个值

• count、sum、avg、min、max（统计忽略空值）

• ifnull():用于处理null值。

• ifnull(v1,v2)

• case when是流程控制语句，获取更准确和直接的结果。

• case [col_name] when [values1] then [result1] .....else [default] end

• 示例：select id,name, case sex when "男" then 1 else 2 end  as sex1 from employee;

Python数据库API

• Python需要为操作不同的数据库使用不同的模块，但是这些模块应遵循Python制定的DB API协议，被称为Python DB API2.0

• 全局变量用于判断该数据库所支持的功能，通常有以下3个全局变量

• apilevel：该全局变量显示数据库模块的API版本号

• threadsafety：该全局变量指定该数据库模块的线程安全等级

• paramstyle：该全局变量指定当SQL语句需要参数时，可以使用哪种风格的参数。qmark、numeric、named参数。

• 核心API

• connect（）函数：连接数据库，返回数据库连接

• 数据库连接：用于打开游标，开启或提交事务

• 游标：用于执行SQL语句，获取执行的结果。

• 流程：开始-打开connection-获取cursor-执行SQL获取数据、处理数据-关闭cursor-关闭connection-结束

• Python自带SQLite数据库和SQLite数据库的API模块，导入sqlite3模块，通过全局变量可了解该模块支持的特性。它是一个没有进程的数据库，磁盘上一个文件可对应SQLite数据库

• import sqlite3

• cOnn=sqlite3.connect("test.db")#打开数据库连接

• c=conn.cursor()#打开游标

• c.execute()#使用游标的execute方法执行任意sql语句
  
  

  c.execute(
create table user_tb(
              _id integer primary key autoincrement,
              name text,
              pass  text ,
              age integer)；
create table order_tb(
_id integer  primary key autoincrement,
item_name text,
item_price real,
item_number integer,
user_id iteger,
foreign key (user_id ) references user_tb(_id));
)
#插入数据
c.excute( `insert into user_tb values (null,?,?,?)`,("fkjava","345",23))
c.excute( `insert into user_tb values (null,?,?,?)`,("fkpython","789",25))
#重复执行
c.excutemany( `insert into user_tb values (null,?,?,?)`,
          （("fkjava","345",23)，
             ("fkpython","789",25),
             ("fkhtml","712",21)
)
#更新修改数据，（元组形式）
c.excute( `update  user_tb  set pass=?` ,("3456",)）
#获取修改记录，通过游标rowcount来获取
c.rowcount
#查询语句，select语句执行，
c.excute( `select *   from user_tb  where _id=?` ,(23,)）
#所有查询结果通过游标来获取
#返回lie信息
c.description
for col in c.description：
        print(col[0],end="\t")
#通过游标的fetchone()\fetchmany(n)\fetchall()来获取查询结果。
while True:
   row=c.fetchone()
  if  not row:
       braek
 else:
     for d in row:  #输出该行内各单元格的数据
           print(col[0],end="\t")
   print()
#提交事务
conn.commit()
#可忽略数据类型，弱数据类型。
或者通过游标迭代打印
for row in c:
       for  d in row:
            print(d,"\t")
      print()

  

• c.close()

• conn.close()

• SQLite内部只支持NULL,INTEGER、REAL（浮点数）、TEXT(文本)、和BLOB（大二进制对象）这5种对象。

• pymysql
  import pymysqlcOnn=pymysql.connect("user="root",password="root")db=conn.select_db("mydb1")c=db.cursor()sql="select * from employee limit 10"result=c.execute(sql)c.fetchall()c.fetchone()c.close()conn.close()

• ORM：object relational mapping ,对象映射关系

• 使用对象模型而不是sql来操作数据库

• sqlalchemy

Python数据分析实例