主流数据库的使用教程

迷南。 2022-03-26 09:10 135阅读 0赞

### 主流数据库的使用教程 ###

*  前言
 *   *  一. sqlite
     *   *  1.1 安装
         *  1.2 DDL(数据定义语言)
         *  1.3 DML(数据操作语言)
         *  1.4 python操作sqlite
     *  二. MySQL
     *   *  2.1 安装MySQL
         *  2.2 MySQL使用基础
         *  2.3 MySQL基本使用
         *  2.4 python 操作MySQL
     *  三、MongoDB
     *   *  3.1 安装MongoDB
         *  3.2 python操作MongoDB

# 前言 #

> 我的个人网站：[https://www.gentlecp.com][https_www.gentlecp.com]

本文仅就个人使用中常见的数据库使用方法进行介绍，内容简练，拒绝废话。

## 一. sqlite ##

开源的嵌入式关系数据库，相较其他数据库安装运行**非常**方便，实现一个文件即数据库。

### 1.1 安装 ###

python2.5以上版本中自带sqlite3，只要安装了python直接就可以import sqlite3使用  
其他的具体可参考[此网站][Link 1]

### 1.2 DDL(数据定义语言) ###

**创建数据库**

CREATE DATABASE db_name

**删除数据库**

DROP DATABASE db_name

**创建新表**

CREATE TABLE table_name(
    				column1 [type] (PRIMARY KEY) (NOT NULL),
    				column2 [type],
    				....

**注：用\[\]包围的意为多类型变量，实际使用需要用具体类型替换,用()包围的意为可有可无的语句,用\{\}包围的意为具体值**  
SQL语句不区分大小写，个人建议写代码的时候已有关键字采用大写，其他如表名，列名由自己编写的名称采用小写，直观便于区分理解。  
下面是具体可选的type（数据类型）与对应含义

<table> 
 <thead> 
  <tr> 
   <th>type</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>INTEGER(size)<br>INT(size)<br>SMALLINT(size)<br>TINYINT(size)</td> 
   <td>整数类型，size代表最大的位数，也可以不写，有趣的是即便你存储位数超过你限定的位数，一般也会<br>正常显示，具体可以看看<a href="https://blog.csdn.net/goodcat12/article/details/7777320" rel="nofollow">这篇文章</a></td> 
  </tr> 
  <tr> 
   <td>DECIMAL(size,d)<br>NUMERIC(size,d)</td> 
   <td>小数类型，size同整数类型，d为小数点右侧的最大位数</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(size)</td> 
   <td>可变长的字符串，即根据字符串实际大小分配长度（空间），一般字符串的用这个就行了</td> 
  </tr> 
  <tr> 
   <td>date(yyyymmdd)</td> 
   <td>日期类型，形式为year+month+day</td> 
  </tr> 
 </tbody> 
</table>

**修改表**

ALTER TABLE table_name ADD column_name [type]  #添加列
    ALTER TABLE table_name ALTER COLUMN column_name [type] #修改某列数据类型
    ALTER TABLE table_name DROP COLUMN column_name #删除表中某列

**删除表**

DROP TABLE table_name

### 1.3 DML(数据操作语言) ###

**查数据**

SELECT * FROM table_name #获取某表中所有数据
    SELECT (DISTINCT) column1,column2,... FROM table_name #查询某表中特定列的数据,DISTINCT用于去除表中重复值
    SELECT column1,column2,... FROM table_name WHERE column [operator] {value} #带条件的查询，value若是字符串需加''
    SELECT column1,column2,... FROM table_name WHERE 
    		column1 [operator1] {value1} AND column2 [operator2] {value2} #多条件查询，AND可用OR替换
    SELECT column1,column2,... FROM table_name 
    		ORDER BY column1 (DESC), column2 (ASC) #排序，先按照column1降序排列，再按照column2升序排列

查询数据还有许多复杂的语句，但是没必要贪多，以上几条能熟练运用就可以了。  
下面是具体可选的条件语句中的操作符\[operator\]

<table> 
 <thead> 
  <tr> 
   <th>operator</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;</td> 
   <td>不等于，有些数据库可以用 != 但是建议统一用这个</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
  </tr> 
  <tr> 
   <td>BETWEEN</td> 
   <td>在此范围内搜索</td> 
  </tr> 
  <tr> 
   <td>LIKE</td> 
   <td>按某种模式搜索，用法可参考<a href="http://www.runoob.com/mysql/mysql-like-clause.html" rel="nofollow">此文章</a></td> 
  </tr> 
 </tbody> 
</table>

**插入数据**

INSERT INTO table_name(column1,column2,...) VALUES({value1},{value2},...) #往特定表的特定列中插入数据

实际代码中编写插入数据的时候\{value\}一般用%s替代，代表可变的数据，进行excute执行SQL语句操作的时候将数据作为参数传入。  
**更新数据**

UPDATE table_name SET column1 = {value1} WHERE column2 = {value2} 
    			#修改指定表column2的值为value2的column1的值为value1，好绕口- -|||

**删除数据**

DELETE FROM table_name WHERE column = {value} #删除指定表中column值为value的单条数据

### 1.4 python操作sqlite ###

**创建数据库**

import sqlite3
    
    #创建数据库
    con = sqlite3.connect('test.db') #在硬盘上创建数据库，生成.db文件
    #con = sqlite3.connect(':memory') #在内存中创建临时数据库，程序结束后自动销毁
    
    cur = con.cursor() #游标对象，用于支持sql语句的执行以及记录的获取

**建表**

SQL_CREATE_TABLE = ''' CREATE TABLE person( id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(30), age INT(4) ) ''' #SQL语句的编写建议像这样分行，增强可读性
    
    cur.execute(SQL_CREATE_TABLE)

**插入数据**

data_single = (1,"GentleCP",100)
    SQL_INSERT_DATA = ''' INSERT INTO person(id,name,age) VALUES(?,?,?) ''' #注意这里我采用了占位符，这种方式能够规避SQL注入，建议统一
    cur.execute(SQL_INSERT_DATA, data_single)
    try:
        con.commit() #插入数据后只是在缓冲区，需要提交事务才能写到数据库
    except:
        con.rollback() #如果提交失败，则回滚之前的插入操作
    
    data_multiple = [
        (2,"HappyCP",10),
        (3,"SadCP",28),
    ]
    cur.executemany(SQL_INSERT_DATA, data_multiple)
    try:
        con.commit() #插入数据后只是在缓冲区，需要提交事务才能写到数据库
    except:
        con.rollback() #如果提交失败，则回滚之前的插入操作

**查询数据**

SQL_QUERY = ''' SELECT * FROM person '''
    cur.execute(SQL_QUERY) 
    res = cur.fetchall() #光查询还看不到数据，需要用游标的fetch方法获取
    #res = cur.fetchone() #获取一条
    #res = cur.fetchmany(5) #获取多条
    print('查询所有数据：'+ str(res))
    
    #带条件的查询
    SQL_QUERY_CON = ''' SELECT name FROM person WHERE id=2 '''
    cur.execute(SQL_QUERY_CON) 
    res = cur.fetchone() 
    print('带条件的查询：'+ str(res))

![在这里插入图片描述][20190119103805919.jpg]  
**修改、删除数据**

SQL_MODIFY = ''' UPDATE person SET name=? WHERE id=? '''
    SQL_DELETE = ''' DELETE FROM person WHERE id=? '''
    cur.execute(SQL_MODIFY,('BeautifulCP',1))
    cur.execute(SQL_DELETE,(3,)) #注意传入得是元组
    
    try:
        con.commit()
    except:
        con.rollback()
    cur.execute(SQL_QUERY)
    res = cur.fetchall()
    print('修改，删除后的数据：'+ str(res))
    con.close()  #全部操作执行完毕后关闭数据库连接

![在这里插入图片描述][20190119103931633.jpg]  
对于sqlite数据库常用的操作如上所示，还有一些其他的上文提到的SQL语句，可自行尝试。

## 二. MySQL ##

MySQL是关系型数据库，关联数据库将数据库存储在表中。

### 2.1 安装MySQL ###

**ubantu**  
打开终端(Terminal)，输入如下命令

sudo apt-get install mysql-server
    sudo apt-get install mysql-client
    sudo apt-get install libmysqlclient-dev

安装过程中需要根据提示设置账号密码（密码不要忘！！！），安装完成后运行如下命令检验安装是否成功：

sudo netstat -tap | grep mysql

如果看到mysql的socket处于listen状态表示安装成功。  
**Windows**  
Windows下安装mysql比较麻烦，这里提供[傻瓜式安装教程][Link 2]

### 2.2 MySQL使用基础 ###

**MySQL数据类型**

<table> 
 <tbody> 
  <tr> 
   <td>数据类型大类</td> 
   <td>数据类型</td> 
   <td> 含义</td> 
   <td> 简单理解</td> 
  </tr> 
  <tr> 
   <td> 字符串 </td> 
   <td>CHAR(size)</td> 
   <td>固定长度，最多255个字符</td> 
   <td>很短的字符用它，如姓名</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(size)</td> 
   <td>可变长度，最多255个字符，超过了会自动转成TEXT</td> 
   <td>节省空间</td> 
  </tr> 
  <tr> 
   <td>TINTTEXT</td> 
   <td>可变长度，最多255个字符</td> 
   <td>效果同CHAR,<a href="https://www.oschina.net/question/114978_18335" rel="nofollow">TEXT与CHAR傻傻分不清？</a></td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>可变长度，最多65535个字符</td> 
   <td>存较大量文字信息</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>可变长度，最多2^24-1个字符</td> 
   <td>比前者更大</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>可变长度，最多2^32-1个字符</td> 
   <td>比前者更大</td> 
  </tr> 
  <tr> 
   <td> 整数 </td> 
   <td>TINYINT(size)</td> 
   <td>1个字节,存储数字范围(-128~127)</td> 
   <td>很短的字符用它，如姓名</td> 
  </tr> 
  <tr> 
   <td>SMALLINT(size)</td> 
   <td>2个字节,存储数字范围(-32768~32767)</td> 
   <td>存大一丢丢的数字，例如你的工资:)</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT(size)</td> 
   <td>3个字节,存储数字范围(-8388608~8388607)</td> 
   <td>存稍大的数字，例如你努力十年后的工资:)</td> 
  </tr> 
  <tr> 
   <td>INT(size)</td> 
   <td>4个字节,存储数字范围(-2147483648~2147483647)</td> 
   <td>存极大的数字，例如你梦想的工资:)</td> 
  </tr> 
  <tr> 
   <td>BIGINT(size)</td> 
   <td>8个字节,存储数字范围( -2^63~2^63-1)</td> 
   <td>存天文数字，例如马云的工资:)</td> 
  </tr> 
  <tr> 
   <td> 二进制数据</td> 
   <td>TINYBLOB(size)</td> 
   <td>0~255个字符</td> 
   <td>同字符串类，但是存储的是二进制数据</td> 
  </tr> 
  <tr> 
   <td>BLOB(size)</td> 
   <td>0~65535个字符</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB(size)</td> 
   <td>0-2^24个字符</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB(size)</td> 
   <td>0~2^32个字符</td> 
  </tr> 
  <tr> 
   <td>时间</td> 
   <td>DATE</td> 
   <td>yyyy-mm-dd</td> 
   <td>不用解释了吧:)</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>hh:mm:ss</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>yyyy-mm-dd hh:mm:ss[.fraction]</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>yyyy-mm-dd hh:mm:ss[.fraction],范围限制在 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999</td> 
  </tr> 
 </tbody> 
</table>

### 2.3 MySQL基本使用 ###

MySQL的DDL与DML与sqlite大同小异，在此不一一列举，后续会在具体代码中说明介绍。  
**1. 连接数据库**

mysql -u [user_name] -p [password] #连接本地数据库
    mysql-h [host_ip] -u [user_name] -p [password] #连接远程数据库

**2.修改密码**

mysqladmin -u [user_name] -p [old_pwd] -password [new_pwd]

**注:以上操作需在C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin目录控制台下输入操作，或将该路径添加到环境变量path中**  
更多控制台对于MySQL的详细操作，可以[看这里][Link 3]  
**附:一般我们对于MySQL的常规操作可以通过可视化工具代替实现，此处推荐使用[navicat][]，包含了对sqlite，MySQL等一系列数据库的可视化。**

### 2.4 python 操作MySQL ###

python有许多操作MySQL的模块，个人推荐使用pymysql，安装只需用pip在终端或控制台输入如下命令：

pip install pymysql

在需要对MySQL数据库操作的时候导入该模块即可。

**创建，连接数据库**

import pymysql
    
    conn = pymysql.connect(
        host="localhost",
        user="root",
        passwd="root",
        database="test",
        charset="UTF8")
    cur = conn.cursor()

**其他操作与sqlite3基本一致，不过注意pymysql中占位符是%s**

## 三、MongoDB ##

MongoDB介于关系型数据库与非关系型数据库之间，他的存储更类似于一种文件的形式。MongoDB将关系型数据库中的表转换成了集合（Collections的形式），相比于MySQL等关系型数据库，它更加自由，你无需像创建表，一个个定义字段，然后用sql语句进入增删查改。每一条存储在MongoDB中的数据采用键值对的形式，所以，我们插入一条MongoDB数据需是json格式。

### 3.1 安装MongoDB ###

*  **windows**  
    windows中安装MongoDB较复杂，网上有许多安装教程，这里给出一个作为[参考][Link 4]。
 *  **Linux**
    
        sudo apt-get install mongodb   # ubantu
        yum install mongodb  # centos
    
    linux中会自动创建一个/data/db目录，作为dbpath的目录。  
    输入mongo，即可进入mongodb交互界面  
    \[外链图片转存失败(img-acwPb7kb-1563946716193)([http://note.youdao.com/yws/res/11274/7C60156389A14D6DB229271270071B5D][http_note.youdao.com_yws_res_11274_7C60156389A14D6DB229271270071B5D])\]  
    以下为一些交互常用命令：
    
        show dbs  # 显示所有数据库，同MySQL show databases;
        use db_name  # 使用该数据库，同MySQL use db_name;
 *  **mac os**  
    mac中需要先安装homebrew，利用brew命令安装mongodb
    
        brew install mongodb
        brew services start mongodb  # 启动mongodb服务
    
    其余操作同linux

### 3.2 python操作MongoDB ###

安装完成MongoDB后，下面是如何使用编程语言操作mongodb，这里介绍python对mongodb的操作步骤。

pip install pymongo

安装完pymongo包后就可进行对mongodb的操作了。以下为一些常用操作：

*  **连接到客户端**
    
        import pymongo
        
        client = pymongo.MongoClient()  # 连接到默认mongo客户端，默认是localhost,27017
        # client = pymongo.MongoClient('107.109.12.1',9000) # 指定主机和端口连接
 *  **创建、连接数据库**
    
        db = client['test_db']  # 创建数据库，若存在则连接
        # db = client.test_db 也可以用这种形式
 *  **获取集合**
    
        collection1 = db['collection1']  # 创建&获取集合
        # collection1 = db.collection1 # 也可以用这种形式
 *  **插入数据**  
    在python中一个字典对象为一条数据
    
        data = { 'name':'cp','age':1}
        collection1.insert_one(data)  # 插入一条数据
    
    结果如下：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dlbnRsZUNQ_size_16_color_FFFFFF_t_70]
    
    可以看到mongodb会自动给插入的数据对象赋一个\_id属性，自动识别值的数据类型。因此同样的数据在mongodb看来是不一样的，多次插入同一条数据会被赋值不同的id存储。
    
        datas = [
            { 'name':'cp','age':2},
            { 'name':'dd','age':3},
            { 'gender':'xx'}
        ]
        collection1.insert_many(datas)  # 插入多条，需用列表包裹
        # collection1.insert(datas) # insert方法已经被弃用了，建议用insert_many
 *  **查找数据**  
    查找数据需要提供数据的至少一个键与值作为查找标准（若有多个满足返回第一个），返回是json的形式。
    
        # 查找单个数据
        print(collection1.find_one({ 'name':'cp'}))
        print(collection1.find_one({ 'age':1}))
        print(collection1.find_one({ 'age':99}))
    
    ![在这里插入图片描述][20190611164035643.png]
    
        for res in collection1.find({ 'name':'cp'}):  # find方法返回一个cursor游标，是可迭代对象
            print(res)
    
    ![在这里插入图片描述][20190611164352423.png]
 *  **计数**  
    有时候我们指向知道集合中有多少数据满足条件，则可根据条件进行计数。
    
        print(collection1.count_documents({ }))  # 查询集合数据总数
        print(collection1.count_documents({ 'name':'cp'}))  # 查询集合name为cp的数据总数
 *  **范围查询**  
    满足更加复杂的查询条件。
    
        # 查找所有年龄大于2的数据并按name排列
        for res in collection1.find({ 'age':{ '$gt':2}}).sort('name'):
            print(res)
    
    ![在这里插入图片描述][20190611165329140.png]
 *  **索引**  
    之前说mongo对同样的数据看作不一样，是因为它会自动给数据添加id属性，每个id属性唯一区分一条数据，我们也可以手动添加索引，来避免重复数据的添加。同时索引也有利于我们进行数据的查找。
    
        collection1.create_index([('user_id', pymongo.ASCENDING)],unique = True)  # 创建索引,升序排列，降序是pymongo.DESCENDING
        print(sorted(list(collection1.index_information())))  # 查看索引信息
        
        data1 = { 'user_id':3,'name':'hahah','age':100}
        data2 = { 'user_id':3,'name':'jjj','age':113}
        collection1.insert_one(data1)
        collection1.insert_one(data2)
    
    当创建的索引文档已经存在时，再次插入会报异常。  
    ![在这里插入图片描述][2019061117285537.png]
 *  **修改数据**  
    修改数据传入两个参数，第一个参数用于找到指定数据，第二个用于修改该数据
    
        collection1.update_one({ 'name':'cp'},{ '$set':{ 'age':100}})  # 修改的json参数第一个为$set后面接一个字典传入键值
        collection1.update_many({ 'name':'cp'},{ '$set':{ 'age':100}})  # 找到多条满足条件的均修改
    
    修改前：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dlbnRsZUNQ_size_16_color_FFFFFF_t_70 1]  
    修改后：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dlbnRsZUNQ_size_16_color_FFFFFF_t_70 2]
 *  **删除数据**
    
        collection1.delete_one({ 'name':'cp'})  # 删除一条名为cp的数据
        collection1.delete_many({ 'name':'cp'})  # 删除所有名为cp的数据

[https_www.gentlecp.com]: https://www.gentlecp.com
[Link 1]: http://www.runoob.com/sqlite/sqlite-installation.html
[20190119103805919.jpg]: /images/20220326/3e5d1dbfd2d2439bb05e6cd0066cf23a.png
[20190119103931633.jpg]: /images/20220326/a4ad5075340c4a4f8635c0d05ae10adb.png
[Link 2]: https://blog.csdn.net/qq_34444097/article/details/82315587
[Link 3]: http://www.cnblogs.com/whgk/p/6131285.html
[navicat]: https://www.navicat.com.cn/
[Link 4]: https://www.cnblogs.com/zhoulifeng/p/9429597.html
[http_note.youdao.com_yws_res_11274_7C60156389A14D6DB229271270071B5D]: http://note.youdao.com/yws/res/11274/7C60156389A14D6DB229271270071B5D
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dlbnRsZUNQ_size_16_color_FFFFFF_t_70]: /images/20220326/b5899bd6aeb24b649b8409571d85cef8.png
[20190611164035643.png]: /images/20220326/3ad1b6cfa7a2437888e14603d5ad1c90.png
[20190611164352423.png]: /images/20220326/bff4dae8bac847c2a9a3e3cbf551c36f.png
[20190611165329140.png]: /images/20220326/cf2e9c979b8a43feb99317553e99de4f.png
[2019061117285537.png]: /images/20220326/6dbef48c330d4f06879e8fd86392593f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dlbnRsZUNQ_size_16_color_FFFFFF_t_70 1]: /images/20220326/6ca60bb07d004d25895c6b3ff168a04f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0dlbnRsZUNQ_size_16_color_FFFFFF_t_70 2]: /images/20220326/3c7eda4f655144709ae51f4c594cb06c.png