说起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()  #关闭连接

搜索