操作数据库的神器，SQL语言

SQL简介

　　对数据库进行查询和修改操作的语言叫做 SQL（Structured Query Language，结构化查询语言）。SQL 语言是目前广泛使用的关系数据库标准语言，是各种数据库交互方式的基础。著名的大型商用数据库 Oracle、DB2、Sybase、SQL Server，开源的数据库 PostgreSQL、MySQL，甚至一些小型的数据库 Access 等都支持 SQL。近些年蓬勃发展的 NoSQL 系统最初是宣称不再需要 SQL 的，后来也不得不修正为 Not Only SQL，来拥抱 SQL。

SQL是什么

　　SQL 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。与其他程序设计语言（如 C语言、Java 等）不同的是，SQL 由很少的关键字组成，每个 SQL 语句通过一个或多个关键字构成。

　　1、SQL优点：

　　　　1、一体化：SQL 集数据定义、数据操作和数据控制于一体，可以完成数据库中的全部工作。

　　　　2、使用方式灵活：SQL 具有两种使用方式，可以直接以命令方式交互使用；也可以嵌入使用，嵌入C、C++、Fortran、COBOL、Java 等语言中使用。

　　　　3、非过程化：只提操作要求，不必描述操作步骤，也不需要导航。使用时只需要告诉计算机“做什么”，而不需要告诉它“怎么做”，存储路径的选择和操作的执行由数据库管理系统自动完成。

　　　　4、语言简洁、语法简单：该语言的语句都是由描述性很强的英语单词组成，而且这些单词的数目不多。

 　　2、数据定义语言（Data Definition Language，DDL）

　　　　用来创建或删除数据库以及表等对象，主要包含以下几种命令：

　　　　　　DROP：删除数据库和表等对象

　　　　　　CREATE：创建数据库和表等对象

　　　　　　ALTER：修改数据库和表等对象的结构

　　3、数据操作语言（Data Manipulation Language，DML）

　　　　用来变更表中的记录，主要包含以下几种命令：

　　　　　　SELECT：查询表中的数据

　　　　　　INSERT：向表中插入新数据

　　　　　　UPDATE：更新表中的数据

　　　　　　DELETE：删除表中的数据

　　4、数据查询语言（Data Query Language，DQL）

　　　　用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。

　　5、数据控制语言（Data Control Language，DCL）

　　　　用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：

　　　　　　GRANT：赋予用户操作权限

　　　　　　REVOKE：取消用户的操作权限

　　　　　　COMMIT：确认对数据库中的数据进行的变更

　　　　　　ROLLBACK：取消对数据库中的数据进行的变更

　　标准 SQL 是指符合国际标准的 SQL，而非某个数据库厂商的 SQL 语法（如：Microsoft SQL Server 的 T-SQL，Oracle 的 PL/SQL，MySQL）。
　　标准 SQL 可以在任何数据库中使用，而数据库厂商的 SQL 只适合它们对应的数据库，如 T-SQL 只适合 Microsoft SQL Server。

SQL的基本规则

　　对于SQL 初学者，在写 SQL 语句时，只要遵守下面几个书写规则，就可以避免很多错误。这些规则都非常简单，下面我们来逐一介绍。

　　1、SQL 语句要以分号(;)或\G结尾

　　　　在 RDBMS （关系型数据库）当中，SQL 语句是逐条执行的，一条 SQL 语句代表着数据库的一个操作。我们通常在句子的句尾加注标点表示这句话结束，中文句子以句号。结尾，英文以点号.结尾，而 SQL 语句则使用英文分号;结尾。

　　2、SQL 语句不区分大小写

　　　　SQL 不区分关键字的大小写。例如，不管写成 SELECT 还是 select，解释都是一样的。表名和列名也是如此。

　　　　提示：关键字是数据库事先定义的，有特别意义的单词。

　　　　虽然可以根据个人喜好选择大写还是小写（或大小写混杂），但为了理解起来更加容易。

　　　　　　关键字大写

　　　　　　数据库名、表名和列名等小写

　　需要注意的是，插入到表中的数据是区分大小写的。例如，向数据库中插入单词 Computer、COMPUTER 或 computer，这三个是不一样的数据。

　　3、SQL的注释

　　　　SQL语言的注释是：--

mysql> -- show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

MySQL数据库

　　在SQL语言中，数据库相当于文件夹。

　　1、查看数据库

　　　　查看所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> 

　　　　查看数据库定义

mysql> show create database test01;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

　　　　查看正在使用的数据库

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

　　2、创建数据库

　　　　释义：

　　　　　　<数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。

　　　　　　IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。

　　　　　　[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。

　　　　　　[DEFAULT] COLLATE：指定字符集的默认校对规则。

　　　　格式:

CREATE DATABASE [IF NOT EXISTS] <数据库名>
    [[DEFAULT] CHARACTER SET <字符集名>] 
    [[DEFAULT] COLLATE <校对规则名>];

　　　　案例：

mysql> CREATE DATABASE IF NOT EXISTS test01 CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test01             |
+--------------------+
7 rows in set (0.00 sec)

　　3、修改数据库

　　　　在 MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改，数据库的这些特性都储存在 db.opt 文件中。下面我们来介绍一下修改数据库的基本操作。　　　　

注：字符集和校验规则是存储数据库的一种方式。

　　　　在 MySQL 中，可以使用 ALTER DATABASE 来修改已经被创建或者存在的数据库的相关参数。修改数据库的语法格式为：

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

　　　　语法说明如下：

　　　　ALTER DATABASE 用于更改数据库的全局特性。

　　　　使用 ALTER DATABASE 需要获得数据库 ALTER 权限。

　　　　数据库名称可以忽略，此时语句对应于默认数据库（最新指定数据库）。

　　　　CHARACTER SET 子句用于更改默认的数据库字符集。

　　　　案例：

mysql> show create database test01;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER DATABASE test01 
    -> DEFAULT CHARACTER SET utf8 
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database test01;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

　　4、指定数据库

　　　　在 MySQL 中就有很多系统自带的数据库，那么在操作数据库之前就必须要确定是哪一个数据库。在 MySQL 中，USE 语句用来完成一个数据库到另一个数据库的跳转。

　　　　当用 CREATE DATABASE 语句创建数据库之后，该数据库不会自动成为当前数据库，需要用 USE 来指定当前数据库。其语法格式为：

USE [数据库名称];

注：该语句可以通知 MySQL 把<数据库名>所指示的数据库作为当前数据库。该数据库保持为默认数据库，直到语段的结尾，或者直到遇见一个不同的 USE 语句。 只有使用 USE 语句来指定某个数据库作为当前数据库之后，才能对该数据库及其存储的数据对象执行操作。

　　　　案例：

mysql> use test01;
Database changed
mysql> 

　　5、删除数据库

　　　　当数据库不再使用时应该将其删除，以确保数据库存储空间中存放的是有效数据。删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一同被删除

 　　　　在 MySQL 中，当需要删除已创建的数据库时，可以使用 DROP DATABASE 语句。其语法格式为：

DROP DATABASE [ IF EXISTS ] <数据库名>

　　　　语法说明如下

　　　　　　<数据库名>：指定要删除的数据库名。

　　　　　　IF EXISTS：用于防止当数据库不存在时发生错误。

　　　　　　DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

　　　　案例：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test01             |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database test01;
Query OK, 0 rows affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

　　注意：MySQL 安装后，系统会自动创建名为 information_schema 和 mysql 的两个系统数据库，系统数据库存放一些和数据库相关的信息，如果删除了这两个数据库，MySQL 将不能正常工作。

　　6、数据库注释

 　　　　6.1、单行注释可以使用#注释符，#注释符后直接加注释内容。格式如下：

#注释内容
单行注释使用注释符#的示例如下：
#从结果中删除重复行
SELECT DISTINCT product_id, purchase_price FROM Product;

　　　　6.2、单行注释可以使用--注释符，--注释符后需要加一个空格，注释才能生效。格式如下：

-- 注释内容
单行注释使用注释符--的示例如下：
-- 从结果中删除重复行
SELECT DISTINCT product_id, purchase_price FROM Product;

　　　　和--的区别就是：#后面直接加注释内容，而--的第 2 个破折号后需要跟一个空格符在加注释内容。

　　　　6.3、MySQL多行注释

　　　　多行注释使用/* */注释符。/*用于注释内容的开头，*/用于注释内容的结尾。多行注释格式如下：

/*
第一行注释内容
第二行注释内容
*/

　　　　注释内容写在/*和*/之间，可以跨多行

MySQL数据中的数据类型

　　数据类型（data_type）是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

　　数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。

　　如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

　　MySQL 的数据类型有大概可以分为 5 种，分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

　　1、MySQL整数类型

　　　　整数类型又称数值型数据，数值型数据类型主要用来存储数字。

　　　　MySQL 提供了多种数值型数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大。

　　　　MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。

　　从上表中可以看到，不同类型的整数存储所需的字节数不相同，占用字节数最小的是 TINYINT 类型，占用字节最大的是 BIGINT 类型，占用的字节越多的类型所能表示的数值范围越大。

　　根据占用字节数可以求出每一种数据类型的取值范围。例如，TINYINT 需要 1 个字节（8bit）来存储，那么 TINYINT 无符号数的最大值为 28-1，即 255；TINYINT 有符号数的最大值为 27-1，即 127。其他类型的整数的取值范围计算方法相同，如下表所示。

 　　案例

用utf8mb4创建xiaowu库
mysql> create database xiaowu charset utf8mb4;
使用xiaowu库；
mysql> use xiaowu;
在xiaowu库下创建t1表，id列用int型，name列用varchar型，age用tinyint型
mysql> create table t1(id int ,name varchar(64) ,age tinyint);

说明：手机号是无法存储到int的。一般是使用char类型来存储手机

　　2、MySQL小数类型

　　　　MySQL 中使用浮点数和定点数来表示小数。

　　　　浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。

　　　　浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。

　　　　浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

　　　　下表中列出了 MySQL 中的小数类型和存储需求。

　　　　DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

　　　　从上表中可以看到，DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。

　　　　FLOAT 类型的取值范围如下：

　　　　　　有符号的取值范围：-3.402823466E+38～-1.175494351E-38。

　　　　　　无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。

　　　　DOUBLE 类型的取值范围如下：

　　　　　　有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。

　　　　　　无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。

　　3、MySQL字符串类型

　　　　字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。

　　　　MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。

　　　　下表中列出了 MySQL 中的字符串数据类型，括号中的M表示可以为其指定长度。

　　　　VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

　　　　例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

mysql> CREATE TABLE test05 (
    -> name VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test06( name ENUM("1","2","3") );
Query OK, 0 rows affected (0.01 sec)

　　4、MySQLR日期和时间类型

　　　　MySQL 中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候，可以只使用 YEAR 类型。

　　　　每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。

　　　　下表中列出了 MySQL 中的日期与时间类型。

 　　　　案例

mysql> create database db1 charset utf8;
Query OK, 1 row affected (0.03 sec)

mysql> use db1;
Database changed

mysql> create table t1(id int,name char, date1 date, date2 time, date3 datetime, date4 timestamp, date5 year);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values (1, '1', '2021-09-09','12:12:12','2021-09-09','2021-09-09','2021');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;
+----+------+------------+----------+---------------------+---------------------+-------+
| id | name | date1      | date2    | date3               | date4               | date5 |
+----+------+------------+----------+---------------------+---------------------+-------+
|  1 | 1    | 2021-09-09 | 12:12:12 | 2021-09-09 00:00:00 | 2021-09-09 00:00:00 |  2021 |
+----+------+------------+----------+---------------------+---------------------+-------+
1 row in set (0.07 sec)


datetime 和 timestamp 之间的区别？
    
  1、两者的存储方式不一样
      对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
    对于DATETIME，不做任何改变，基本上是原样输入和输出。
    
  2、两者所能存储的时间范围不一样
      timestamp所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
    datetime所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
    
    mysql> insert into t1 values (1, '1', '2021-09-09','12:12:12','2221-09-09 12','2221-09-09','2021');
        1292 - Incorrect datetime value: '2221-09-09' for column 'date4' at row 1

　　5、MySQL二进制类型

　　　　MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

　　　　下表中列出了 MySQL 中的二进制数据类型，括号中的M表示可以为其指定长度。

 

 　　　　案例：

import pymysql

class BlobDataTestor:
    def __init__(self):
        self.conn = pymysql.connect(host='127.0.0.1', user='root', passwd='123456', db='db1', port=3306)

    def __del__(self):
        try:
            self.conn.close()
        except:
            pass

    def closedb(self):
        self.conn.close()

    def setup(self):
        cursor = self.conn.cursor()
        cursor.execute("""  
             CREATE TABLE IF NOT EXISTS `Dem_Picture` (  
             `ID` int(11) NOT NULL auto_increment,  
             `PicData` mediumblob,  
             PRIMARY KEY (`ID`)  
             ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;  
             """)


    def testRWBlobData(self):
        # 读取源图片数据
        f = open("D:\\1.jpg", "rb")
        b = f.read()
        f.close()

        # 将图片数据写入表
        cursor = self.conn.cursor()
        cursor.execute("INSERT INTO Dem_Picture (PicData) VALUES (%s)", (pymysql.Binary(b)))
        # self.conn.commit()

        # 读取表内图片数据，并写入硬盘文件
        cursor.execute("SELECT PicData FROM Dem_Picture ORDER BY ID DESC limit 1")
        d = cursor.fetchone()[0]
        cursor.close()

        f = open("D:\\1.jpg", "wb")
        f.write(d)
        f.close()


# 下面一句的作用是：运行本程序文件时执行什么操作  
if __name__ == "__main__":

    test = BlobDataTestor()

    try:
        test.setup()
        test.testRWBlobData()
        # test.teardown()
    finally:
        test.closedb()

 

 

 　　6、MySQL系统变量

　　　　在 MySQL 数据库，变量分为系统变量和用户自定义变量。系统变量以 @@ 开头，用户自定义变量以 @ 开头。

　　　　服务器维护着两种系统变量，即全局变量（GLOBAL VARIABLES）和会话变量（SESSION VARIABLES）。全局变量影响 MySQL 服务的整体运行方式，会话变量影响具体客户端连接的操作。

　　　　每一个客户端成功连接服务器后，都会产生与之对应的会话。会话期间，MySQL 服务实例会在服务器内存中生成与该会话对应的会话变量，这些会话变量的初始值是全局变量值的拷贝。

　　　　1、查看系统变量

　　　　　　可以使用以下命令查看 MySQL 中所有的全局变量信息。

SHOW GLOBAL VARIABLES; 

　　　　　　可以使用以下命令查看与当前会话相关的所有会话变量以及全局变量。

SHOW SESSION VARIABLES;

MySQL数据表

　　数据表是数据库的重要组成部分，每一个数据库都是由若干个数据表组成的。换句话说，没有数据表就无法在数据库中存放数据。

　　1、创建数据表

　　　　在创建数据库之后，接下来就要在数据库中创建数据表。所谓创建数据表，指的是在已经创建的数据库中建立新表。

　　　　创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。接下来我们介绍一下创建数据表的语法形式。

　　　　基本语法

　　　　　　在 MySQL 中，可以使用 CREATE TABLE 语句创建表。其语法格式为：

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

　　　　其中，[表定义选项]的格式为：

<列名1> <类型1> [,…] <列名n> <类型n>

　　　CREATE TABLE 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。

　　　这里首先描述一个简单的新建表的例子，然后重点介绍 CREATE TABLE 命令中的一些主要的语法知识点。