欢迎食用『主界面』~,这里是赵苦瓜的看板娘desu~

#
【点滴记录】MySQL的基本语法&&Python接入(pymysql)
首页 > 点滴记录    作者:赵苦瓜   2021年1月14日 9:55 星期四   热度:1591°   百度已收录  
时间:2021-1-14 9:55   热度:1591° 

Python&MySQL.jpg

说起MySQL,大家肯定并不陌生。我的博客的数据就是存储在MySQL上面的。

如今它的广泛应用是我们有目共睹的。这篇博客就来记录我初步学习MySQL的一些基本语法。

以下少部分内容是跟着廖雪峰老师进行学习的。https://www.liaoxuefeng.com

MySQL是一种结构化的数据库。

数据模型

数据库按照数据结构来组织、存储和管理数据,实际上,数据库一共有三种模型:

·层次模型   ·网状模型   ·关系模型

层次模型是以上下级的关系组织数据,类似于树状图

网状模型把每个数据节点和其它很多节点连接起来,好比城市之间的路网。

关系模型好比一个二维的表格,类似一个Excel表

相比层次模型和网状模型,关系模型的理解和使用最简单,因此得到了广泛的应用。

关系模型的本质是若干个存储数据的二维表。

记录和字段

表的每一行称为记录(Record),每一个记录是逻辑意义上的数据

表的每一列称为字段(Column),同一个表的每一行记录都拥有相同的若干字段。

字段除类型外还有是否允许为NULL(空,不是0也不是空字符串)一般设置为不允许,可以简化查询条件,提高查询速度。

关系数据库的表与表之间需要建立“一对多”、“多对一”、“一对一”的关系

在关系数据库中,关系是通过主键和外键来维护的。一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。

主键和外键

能够通过某个字段唯一区分出不同的记录,这个字段称为主键。记录一旦插入到表中,主键最好就不要再修改,否则可能会有一系列意想不到的问题。

选取主键的原则:不使用任何业务相关的字段作为主键。因此身份证号、手机号、邮箱地址均不可以用作主键。

一般把主键对应的字段命名为id。常见可以作为id的字段类型有自增整数类型(BIGINT)和全局唯一GUID类型。

关系数据库支持设置两个或更多的字段为主键,这种主键称为联合主键。对于联合主键,允许有一列重复,但不能是所有主键组合起来都重复。没有必要的话尽量不要使用联合主键(会使数据库变得复杂)


外键用来关联两个表

复合键(复合索引)将多个列作为一个索引键

数据类型

对于一个关系表,除了定义每一列的名称外,还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等。如下表所示:

名称 类型 说明
INT 整型 4字节整数类型,范围约+/-21亿
BIGINT 长整型 8字节整数类型,范围约+/-922亿亿
REAL 浮点型 4字节浮点数,范围约+/-1038
DOUBLE 浮点型 8字节浮点数,范围约+/-10308
DECIMAL(M,N) 高精度小数 由用户指定精度的小数,例如,DECIMAL(20,10)表示一共20位,其中小数10位,通常用于财务计算
CHAR(N) 定长字符串 存储指定长度的字符串,例如,CHAR(100)总是存储100个字符的字符串
VARCHAR(N) 变长字符串 存储可变长度的字符串,例如,VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN 布尔类型 存储True或者False
DATE 日期类型 存储日期,例如,2018-06-22
TIME 时间类型 存储时间,例如,12:20:59
DATETIME 日期和时间类型 存储日期+时间,例如,2018-06-22 12:20:59
mysql 4.0版本以下,varchar(50), 指的是50字节,如果存放utf8汉字时,只能存放16个(每个汉字3字节)
mysql 5.0版本以上,varchar(50), 指的是50字符,无论存放的是数字、字母还是UTF8汉字(每个汉字3字节),都可以存放50个。

上面是几种常用的数据类型。还有很多数据类型有别名,如REAL又可以写作FLOAT(24),还有比如TINYINT类型(范围0~255)不是很常用,还要数据库厂商支持的特定数据类型,如JSON。

选择数据类型时要选择合适。通常来说BIGINT和VARCHAR(N)是使用最广泛的。


目前数据库主要分为商用数据库(如Oracle、SQL Sever、DB2等)、开源数据库(如MySQL、PostgreSQL)、桌面数据库(Access)、嵌入式数据库(Sqlite)。

SQL(Structured Query Language)结构化查询语言,主要提供三个操作数据库的功能:DDL(创建、删除、修改表)、DML(添加、删除、更新数据)、DQL(查询数据)


SQL语言关键字不区分大小写,但是针对不同的数据库有不同的情况。

一般采用InnoDB数据库引擎。

分号;代表一条SQL语句的结束。' '单引号引起来的东西表示一个字符串。  数值字段不要用引号。

登录相关

MYSQL -u 用户名 -p             如:MYSQL -u root -p     回车之后会提示你输入密码。登录成功后开头变为mysql>

EXIT 退出登录(mysql数据库仍在后台运行)

数据库操作

SHOW DATABASES;           查看已有的数据库 (列出MySQL数据库管理系统的数据库列表)

USE 数据库名;                  进入一个数据库

SHOW TABLES;                  显示当前数据库下的所有表

SHOW COLUMNS FROM 数据表;        显示数据表的属性,属性类型,主键信息,是否为NULL,默认值等其他信息


SELECT         从数据库中提取数据(查询)

SELECT x,y AS 别名 FROM 数据表;             查看x,y在数据表中的记录(选择x,y这两个字段并把它显示出来)通配符*,表示所有 内容

AS 别名可以省略。as之后的别名不能加引号

SELECT * FROM 数据表 AS 别名;   表别名


WHERE 子句  写条件,满足条件输出

LIMIT 数值     子句,返回指定数目

REGEXP      正则表达式  使用正则表达式写法

ORDER BY 关键字  ASC或DESC,可省略              子句          排序,默认是升序,降序使用DESC

运算符 描述
= 等于
<>

不等于(在SQL的某些版本可写作!=)

> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN

在某个范围内 where x BETWEEN x AND y  

 可以是数值、文本、日期

LIKE

搜索某种模式(匹配)where x like '%miao%' 

 %通配符   _替代一个字符 

IN

指定针对某个列的多个可能值where  x in (x,y,z,...)

逻辑运算符 AND   OR                           NOT

mysql> select * from student where sex='男';
+------+------+------+------+------+------------+
| id   | name | sex  | age  | tel  | adress     |
+------+------+------+------+------+------------+
|    1 | tom  | 男   |   18 |  110 | washington |
+------+------+------+------+------+------------+
1 row in set (0.00 sec)
mysql> select * from student where adress like '%ang%';
+------+-------+------+------+------+----------+
| id   | name  | sex  | age  | tel  | adress   |
+------+-------+------+------+------+----------+
|    2 | Marry | 女   |   16 |  120 | Shanghai |
+------+-------+------+------+------+----------+

JOIN           连接,用来把来自两个或多个表的行结合起来,基于这些表之间的共同字段

内连JOIN(INNER JOIN)是最简单的JOIN    至少有一个匹配,就返回行(INNER可以省略不写)

除此之外还有

LEFT JOIN  即使右表中没有匹配,也从左表中返回所有行         (在某些数据中还被称作LEFT OUTER JOIN)

RIGHT JOIN         即使左表中没有匹配,也从右表中返回所有行         

FULL JOIN          只要其中一个匹配,就返回行

SELECT * from 表1名 inner join 表2名 on 表1字段名=表二字段名


UPDATE        更新数据库中的数据(更新),用于更新表中已经存在的记录

UPDATE 表名 SET 字段1=值1,字段2=值2,... where 哪些字段=哪些值; 通过where来规定哪些需要更新

mysql> update student set tel=333,age=22 where name='jerry';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+------+--------+--------+------+------+------------+
| id   | name   | sex    | age  | tel  | adress     |
+------+--------+--------+------+------+------------+
|    1 | tom    | 男     |   18 |  110 | washington |
|    2 | Marry  | 女     |   16 |  120 | Shanghai   |
|    3 | tom    | male   |   28 |  123 | changsha   |
|    4 | alex   | female |   58 |  444 | haerbing   |
|    5 | sophia | female |   18 |  666 | beijing    |
|    6 | alice  | female |    8 |  555 | beijing    |
|    7 | jerry  | male   |   22 |  333 | tianjing   |
+------+--------+--------+------+------+------------+
7 rows in set (0.00 sec)



DELETE         从数据库中删除数据(删除)用于删除表中的行(一条记录)

DELETE FROM 表名 WHERE 字段名=值;     


INSERT INTO               向数据库中插入新数据。插入时数据类型不同会发生自动转换,如'120'的字符串输入到int的字段里面就会自动转换成数字120

①INSERT INTO 表名 VALUES (按表的结构分别写内容,用逗号隔开)

②INSERT INTO 表名 (字段名1,字段名2,...) VALUES (按前面字段名的顺序分别写内容,用逗号隔开)

在PyMySQL中,可以写为:

sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'     

然后执行的时候传参

cursor.execuse(sql, ('100001', 'Bob', 12))

cursor.commit()

这样写更直观,避免了引号冲突的问题和字符串拼接的问题。

这里最好写一个try,如果出现异常则cursor.rollback()进行数据回滚


同时,由于我们一般使用字典,所以可以构造动态SQL语句。

def sql_test():
    import pymysql
    # 创建连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='密码', db='school')
    # 获取游标
    cursor = conn.cursor()
    data = {
        'id': '1000001',
        'name': 'Bob',
        'age': 18,
    }
    # 使用学生表
    table = 'students'
    keys = ','.join(data.keys())
    values = ','.join(['%s'] * len(data))
    sql = 'INSERT INTO {table}({keys}) VALUES ({values});'.format(table=table, keys=keys, values=values)
    print(sql)
    try:
        if cursor.execute(sql, tuple(data.values())):
            conn.commit()
            print('写入成功')
    except:
        print('写入数据库失败')
        conn.rollback()
    conn.close()


CREATE DATABASE 数据库名  CHARACTER SET UTF8;     创建新数据库 数据库名不带引号  后面的CHARACTER SET UTF8是将字符串的编码设置为UTF-8

ALTER DATABASE        修改数据库


CREATE TABLE 表名

(

字段名1 数据类型(大小) 约束名(可省略),

字段名2 数据类型(大小) 约束名(可省略),

...

);           

创建新表  ,可以这样换行写,也可也都写在一行。

以下类型的约束:

NOT NULL  -非空   UNIQUE   -唯一      PRIMARY KEY -主键  FOREIGN KEY -外键  CHECK -检查  DEFAUT -默认

默认接受空数据     UNIQUE、PRIMARY KEY、 也可也在末尾写  UNIQUE(字段名)  主键只能有一个,并且不能为空

FOREIGN KEY (这张表的字段名) REFERENCES 表名(另一张表的字段)外键用于指向另一个表的主键


清空表并让其主键从1开始自增:

TRUNCATE TABLE 表名


DEFAULT 默认值

AUTO_INCREMENT 自增字段

mysql> CREATE TABLE Persons
    -> (
    -> ID int NOT NULL AUTO_INCREMENT,
    -> LastName varchar(255) NOT NULL,
    -> Address varchar(255),
    -> City varchar(255),
    -> PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> show tables
    -> ;
+------------------+
| Tables_in_school |
+------------------+
| persons          |
| score            |
| student          |
+------------------+
3 rows in set (0.00 sec)

mysql> describe Persons;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| ID       | int          | NO   | PRI | NULL    | auto_increment |
| LastName | varchar(255) | NO   |     | NULL    |                |
| Address  | varchar(255) | YES  |     | NULL    |                |
| City     | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)



ALTER TABLE              变更(改变)数据库表

DROP TABLE               删除表

             数据库表清空后重置主键ID从1开始自增 | 重置自增变量:    truncate table 表名

CREATE INDEX            创建索引(搜索键)

DROP INDEX               删除索引

DROP DATABASE         删除数据库

TRUNCATE TABLE       清空表内的数据,不删除表本身

ALTER TABLE 表名            修改表

增加列  ADD 列名 类型

删除列  DROP 列名(某些数据库系统不允许这种方式)

修改自增的初始值 AUTO_INCREMENT=100

MODIFY COLUMN 列名 类型

DESCRIBE 表名    查看表的结构

SHOW INDEX FROM 数据表;          显示数据表的详细索引信息

SET names utf8;                将names字段设置为utf-8编码






用户相关

CREATE USER '用户名'@'主机地址' IDENTIFIED BY '密码'; 

【需要root权限】创建用户   本地主机:localhost 任意主机:通配符%   密码可以为空,空的话IDENTIFIED BY也可也不写


GRANT 权限名称 ON 数据库名.表名 TO ‘用户名’@‘主机地址’;

【需要root权限】授予用户权限    权限名称:SELECT(查询)、INSERT(插入)、UPDATA(更新)等,多个权限用逗号隔开。所有权限用ALL。数据库和数据表的通配符是*


REVOKE 权限名称 ON 数据库名.表名 FROM '用户名'@主机地址;

【需要root权限】撤销用户权限


SET PASSWORD FOR '用户名'@'主机地址' = PASSWORD(‘新密码’)

【需要root权限】给任意用户修改密码          PASSWORD函数可以将明文的密码改为密文放在数据库里。

注意:password() 加密函数已经在 8.0.11 中移除了,可以使用 MD5() 函数代替。


SET PASSWORD = PASSWORD('新密码')

给当前用户修改密码。

注意:password() 加密函数已经在 8.0.11 中移除了,可以使用 MD5() 函数代替。

(之前还有另外一种改密码的方式比较常用, update user set password = MD5('这里是密码') where user = 'test' and host = 'localhost';  但是在新版里面不再用password字段,而是换成了authentication_string,这个需要注意。)


DROP USER '用户名'@'主机地址';

【需要root权限】删除用户


查看MySQL安装目录 SELECT @@basedir

查看MySQL数据存放目录 SELECT @@datadir


————————————————————————————————————————

网易公开课补充内容

https://study.163.com/course/courseLearn.htm?courseId=1005609009#/learn/video?lessonId=1052862224&courseId=1005609009

数据库:关联表的集合

数据表:数据的矩阵

冗余:对于数据的多倍存储。降低了存储的性能,但是安全性更好。

索引:类似于目录

参照完整性:要求不能使用不存在的实体

————————————————————————————————————————

Python操作MySQL数据库主要有两种方式:

·原生接入模块 pymysql       ·ORM框架 SQLAchrmy

pymysql

安装:pip install pymysql

import pymysql



conn = pymysql.connect(host='localhost', port=3306, user='root', password='密码', db='数据库名')    #创建连接

cursor = conn.cursor()                      #创建游标



effect_row = cursor.execute('SQL语句')         #可以执行原生的SQL语句,effect_row是被影响的行数



#以下与查询连用。NULL在Python会变成None。被fetch过的内容不能再被fetch

ret_1 = cursor.fetchone()   获取一条 

ret_many = cursor.fechmany(条数)   获取多条   

ret_all = cursor.fetchall()所有结果

#多条默认返回二维元组。如果想要返回字典,创建游标时括号里面写cursor=pymysql.cursors.DictCursor



conn.commit( )   #提交

cursor.close()  #关闭游标

conn.close()  #关闭连接


本文作者:赵苦瓜      文章标题: 【点滴记录】MySQL的基本语法&&Python接入(pymysql)
本文地址:https://blog.jixiaob.cn/?post=36
版权声明:若无注明,本文皆为“赵苦瓜のBlog~”原创,转载请保留文章出处。

返回顶部    首页    后花园  
版权所有:赵苦瓜のBlog~    站长: 赵苦瓜    程序:emlog   鲁ICP备20030743号-1   鲁公网安备37048102006726 萌ICP备20222268号    sitemap