pymysql前传（一文详解SQL常见语句）

写在开始

• 不同的数据库在实现具体功能时对应的SQL语句可能不能会不通，本篇博客以MySQL数据库作为例子
• 本博客参考：
  【菜鸟教程：SQL教程】
  【一文详解python中的数据库操作】
• 由于笔者水平有限，博客中难免会出现一些错误或者不准确的地方，烦请指点

SQL基础篇

结构化查询语言，用于管理关系数据库

• 数据库常见操作为
  增：插入
  删：删除
  改：更新
  查：查询
• SQL对大小写不敏感
• 每条SQL语句末端使用分号是比较标准的写法

数据库与数据表

一个数据库通常包含一个或者多个数据表，每一个表都有一个姓名作为标识，表中包含带有数据的记录

创建数据库

//使用下面语句可以创建一个数据库
CREATE DATABASE dbname;

创建数据表

//使用下面的语句可以创建一个数据表，其中columns表示列名，data_type规定了该列的数据类型，size规定了该列中数据的最大长度
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
//举例：创建一个名为books的数据表
CREATE TABLE books(
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    category varchar(50) NOT NULL,
    price decimal(10,2) DEFAULT NULL,
    publish_time date DEFAULT NULL,
    PRIMARY KEY(id)
)

sql约束

在上面创建一个数据表的举例中我们看到了其他的sql约束语句比如NOT NULL PRIMARY KEY等，sql约束常常用来规定表中的数据规则，下面我们来看一下SQL中的常见约束

NOT NULL

强制该列数据不接受NULL值，当插入或更新数据时尝试为该列赋予NULL值，操作将会被终止

• 添加NOT NULL约束
  ALTER TABLE table_name MODIFY column int NOT NULL;
• 删除NOT NULL约束
  ALTER TABLE table_name MODIFY column int NULL;

UNIQUE

约束唯一标识数据表中的每条数据，可以理解为：当某个数据表中有多条数据（即多行数据）时，约束某一列数据不互相重复（比如某个数据表中有一列name数据，当使用语句UNIQUE(name)设置约束后，name列的每一个名字都是不相同的）

//使用举例
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
UNIQUE (column_name)
);

• 添加QNIQUE约束
  ALTER TABLE table_name ADD UNIQUE (column_name)
• 删除QNIQUE约束
  ALTER TABLE table_name DROP INDEX column_name

PRIMARY KEY

用于约束唯一标识数据库表中的每条记录。可以理解为： 每个数据表都应该有且只有一个 主键，常常使用数据表的ID列作为主键即使用语句PRIMARY KEY(ID)设置约束

//使用举例,这里只介绍使用一列数据创建PRIMARY KEY约束的情况
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
PRIMARY KEY(column_name)
);

• 添加PRIMARY KEY约束
  ALTER TABLE table_name ADD PRIMARY KEY (column_name)
• 删除PRIMARY KEY约束
  ALTER TABLE table_name DROP PRIMARY KEY

CHECK

用于限制某列值的范围。

//举例
CREATE TABLE Persons
(
Id int NOT NULL CHECK (Id>0),
Name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255)
)

• 添加CHECK约束
  ALTER TABLE Persons ADD CHECK (Id>0)
• 删除CHECK约束
  ALTER TABLE Persons DROP CHECK (Id>0)

DEFAULT

DEFAULT 约束用于向列中插入默认值。

• 添加DEFAULT约束
  ALTER TABLE table_name ALTER column_name SET DEFAULT 'str'
• 删除DEFAULT约束
  ALTER TABLE table_name ALTER column_name DROP DEFAULT

AUTO INCREMENT

在每次插入新记录时，自动地创建主键字段的值。

//举例
CREATE TABLE Persons
(
Id int NOT NULL AUTO_INCREMENT,
Name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id)
)

AUTO_INCREMENT 的默认起始值是 1，每条有新记录添加递增 1，可使用下面的 SQL 语法改变默认起始值：

ALTER TABLE Persons AUTO_INCREMENT=num

MySQL数据类型

Text（文本）

子类	保存的数据类型	字符max长度
CHAR(size)	固定长度的字符串（可包含字母、数字以及特殊字符）	255
VARCHAR(size)	可变长度的字符串（可包含字母、数字以及特殊字符）	255（>255将被转换为TEXT）
TINYTEXT	字符串	255
TEXT	字符串	65535

Number（数字）

子类	描述	数值范围
TINYINT(size)	带符号-128到127 ，无符号0到255。
SMALLINT(size)	带符号范围-32768到32767，无符号0到65535, size 默认为 6。
MEDIUMINT(size)	带符号范围-8388608到8388607，无符号的范围是0到16777215。 size 默认为9
INT(size)	带符号范围-2147483648到2147483647，无符号的范围是0到4294967295。 size 默认为 11
BIGINT(size)	带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。size 默认为 20
FLOAT(size,d)	带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。

Date/Time（日期/时间）

	描述	格式	支持范围
DATE()	日期	YYYY-MM-DD	从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME()	日期+时间	YYYY-MM-DD HH:MM:SS	从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIMESTAMP()	时间戳。	格式：YYYY-MM-DD HH:MM:SS	从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME()	时间。	HH:MM:SS	从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR()	年份	2 位或 4 位格式的年。	4 位允许的值：1901 到 2155。2 位所允许的值：70 到 69，表示从 1970 到 2069。

操作数据库

SELECT

用于从数据库中查询数据，返回一个结果表

// column是要选择的字段名称，table_name为待查询表的名称
SELECT column1,column2,... FROM table_name

//一列可能会包含多个重复值，可以使用DISTINCT 关键词返回唯一不同的值
SELECT DISTINCT column1,column2,... FROM table_name

//WHERE用于提取满足指定条件的数据
SELECT column1,column2,... FROM table_name WHERE condition
// 举例：
// SELECT * FROM Websites WHERE country='CN';

WHERE子句中的运算符

//WHERE的条件组合
SELECT * FROM Websites WHERE country='CN' AND id=0;
// SELECT * FROM Websites WHERE country='CN' OR id=0;

// 从表中选取数据按照某一列的升序（ASC）或降序排列，ORDER BY默认升序排列
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ...,ASC|DESC;

TOP子句

规定了返回数据量的最大值，但并非所有数据库系统都支持SELECT TOP语句

MYSQL语法（LIMIT）

SELECT column_name(s) FROM table_name LIMIT number;
//举例：下面的例子将会返回表Persons中的5行数据
//SELECT * FROM Persons LIMIT 5;

LIKE操作符与通配符

// LIKE后常跟通配符表明搜索列中的指定模式
SELECT * FROM table_name WHERE column_name LIKE '%...'

通配符：替代字符串中的人和网其他字符，在SQL语句中常常同LIKE操作符一起使用，常见通配符如下

//举例
//'G%'：以字母G开始的字符
//'https%'：以字符串https开始的所有网站
SELECT * FROM Persons WHERE url LIKE 'https%'

//'%oo%'：可以理解为在url列中选取所有包含字符串 "oo" 的网站
SELECT * FROM Websites WHERE url LIKE '%oo%';

//'_oogle'：可以理解为在name列中选取数据：一个任意字符开始，后续为 "oogle" 的所有客户：
//比如'Google','hoogle'都在选择的范围内
SELECT * FROM Websites WHERE name LIKE '_oogle';

//'^[GFS]'：可以理解为在name列选择数据：以G或F或S开头的所有名称
SELECT * FROM Websites WHERE name REGEXP '^[GFS]'
//比如：Face,Google等都在选择的范围内
// '^[^A]：表示不以字母A开头的网站

IN操作符

// in操作符允许在WHERE的condition中规定多个值
SELECT column1, column2, ... FROM table_name WHERE column IN (value1, value2, ...);
//举例：
//SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程');

BETWEEN操作符

//选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
SELECT column1, column2, ... FROM table_name WHERE column BETWEEN value1 AND value2;
//SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;

INSERT

//INSERT INTO向表中插入新数据，两种写法
//第一种，无需指明要出入数据的列名，只提供被插入的值即可
INSERT INTO table_name VALUES(value1,value2,...)
//第二种，指明被插入的列名以及被插入的值
INSERT INTO table_name (column1,column2,...) VALUES(value1,value2,..)
//举例：
//INSERT INTO Websites (name, url, alexa, country) VALUES ('百度','https://www.baidu.com/','4','CN');

UPDATE

更新表中的数据

//WHERE字段规定了哪些数据需要更新，如果不指明所有数据都将会被更新
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
//举例：下面例子将只会更新name='菜鸟教程'的行的列数据
//UPDATE Websites SET alexa='5000', country='USA' WHERE name='菜鸟教程';
//上面例子中如果不指明WHERE 字段的codition,所有alexa和country列的值都将会被改变，所有使用update语句要特别小心

DELETE

用于删除表中的数据

//WHERE字段规定了哪些数据将会被删除，
DELETE FROM table_name WHERE condition;
//举例：
//DELETE FROM Websites WHERE name='Facebook' AND country='USA';

//在不删除表的情况下，删除表中所有的行,但表结构、属性、索引将保持不变
DELETE FROM table_name;

SQL进阶篇

To be continue…