数据库基础知识

存储结构

  1. 层次模型

  2. 网状模型

  3. 关系模型：用二维表格结构表达实体类型及实体间联系的数据模型

  4. 对象模型

数据库的基本操作

创建查看删除数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 当指定名称的数据库不存在时创建它，并设置编码和排序规则
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 显示当前的所有数据库
SHOW DATABASES;

-- 显示数据库的创建语句，可以用于查看默认编码方式
SHOW CREATE DATABASE db_name;

-- 修改数据库的默认编码方式
ALTER DATABASE db_name CHARACTER SET GBK; 

-- 如果数据库存在，则删除它
DROP DATABASE IF EXISTS db_name;

-- 使用某一个数据库
USE db_name;

数据类型

整数类型：

类型 字节 最小值（有/无符号） 最大值（有/无符号）
TINYINT 1 -128/0 127/255
SMALLINT 2 -32768/0 32767/65535
MEDIUMINT 3 -8388608/0 8388607/16777215
INT/INTEGE 4 -2147483648/0 2147483647/4294967295
BIGINT 8 -9223372036854775808/0 9223372036854775807/17446744073709551615

小数类型：M 表示总位数，D 表示小数位数

类型 字节 最小值（理论） 最大值（理论）
FLOAT 4 -3.402823466E+38 3.402823466E+38
DOUBLE 8 -1.7976931348623157E+308 1.7976931348623157E+308
DECIMAL [(M [, D])] 变长 默认是 (10, 2) M 最大是65，D, 最大是30 (+65 个 9)

字符串与二进制类型：

类型 最大长度 备注
char 255 Char(M), M: 字符数
varchar 65535 编码不同，字符数不同：GBK <= 32767，UTF8 <= 21845
tinyText, text, mediumText, longtext 2^8+1, 2^16+2, 2^24+3, 2^32+4 定义时，通常不用指定长度，自己算
enum enum(‘shanghai’, ‘beijing’, ‘hangzhou’) 内部存储是整数类型表示，字段值只能是某一枚举选项
Set 1, 2, 3, 4, 8
Binary, varbinary, blob Binary(5), varbinary(varchar), blob(text) 作类比 二进制数据（字节而非字符）

创建数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建一张表，设置其中的字段信息(列名\类型)
CREATE TABLE my_table(
	m_id INT,
	m_name VARCHAR(32),
	m_sex enum('男', '女', '保密'),
	m_class VARCHAR(32),
	m_start_date DATETIME
)CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 显示指定表的字段信息
DESC my_table;

-- 查看数据库的所有的表
SHOW TABLES;

-- 查看创建表的时候实际使用的语句
SHOW CREATE TABLE my_table;

修改数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#修改表名
RENAME TABLE my_able TO tab;

-- 在指定表的每一个位置添加一个字段 [FIRST \ AFTER column]
ALTER TABLE my_table ADD test2 INT AFTER m_name;
ALTER TABLE my_table ADD t INT FIRST;

-- 使用 MODIFY 修改指定表中某一列的类型
ALTER TABLE my_table MODIFY t DOUBLE;
DESC my_table;
-- 使用 CHANGE 修改指定表中某一列的类型和列名
ALTER TABLE my_table CHANGE t test1 INT;
DESC my_table;

-- 删除表中的指定字段
ALTER TABLE my_table DROP test1;

-- 删除表
DROP TABLE tab;
SHOW TABLES;

数据的基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 为指定表的指定字段添加一条记录，非空字段必须添加
INSERT INTO my_table VALUE(1, 'xiaoming', '男', '36', now());

-- 给指定表的所有字段添加多行内容
INSERT INTO my_table
VALUES
	( 1, 'xiaoming', '男', '36', "2018-1-1 09:41:02" ),
	( 2, 'xiaohong', '女', '33', "2018-9-1 09:41:02" ),
	( 3, 'xiaotian', '男', '36', "2017-10-1 09:41:02" ),
	( 4, 'xiaoyi', '男', '35', "2018-11-11 09:41:02" ),
	( 5, 'xiaolan', '女', '35', "2018-5-4 09:41:02" ),
	( 6, 'xiaojin', '女', '34', "2017-3-1 09:41:02" );

-- 设置所有的性别为女
UPDATE my_tab SET m_sex = "女";

-- 带条件地设置某些用户的性别为男
UPDATE my_tab SET m_sex = "男" WHERE m_class = '36';

-- 将所有35班的用户性别设为“男”，姓名设为“xxx”
UPDATE my_tab SET m_sex = "男", m_name = 'xxx' WHERE m_class = '35';

-- 删除姓名为"xiaohong"的字段
DELETE FROM my_tab WHERE m_name = "xiaohong";

-- 删除所有信息
DELETE FROM my_tab;

表的约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 表的约束类型
CREATE TABLE my_student (
-- 主键约束：非空且唯一的，通常是 id 值，并且是自增的
m_id INT PRIMARY KEY AUTO_INCREMENT,
-- 唯一约束，非空约束：不能重复的，且非空
m_name VARCHAR ( 32 ) UNIQUE NOT NULL,
-- 默认约束，设置了默认约束的字段可以不进行手动的添加
m_sex ENUM ( '男', '女', '保密' ) DEFAULT '保密',
-- 外键，关联到班级表的主键
m_class INT NOT NULL 
) CHARACTER 
SET utf8 COLLATE utf8_general_ci;

-- 班级表，描述班级的信息
CREATE TABLE my_class(
m_id INT PRIMARY KEY AUTO_INCREMENT,
m_name VARCHAR(32) NOT NULL UNIQUE,
m_count INT NOT NULL DEFAULT 0
)CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 为 my_student 中的 m_class 添加外键关联到 my_class 中的 m_id
-- 在添加数据的过程中，外键必须是一个已经存在的主键
ALTER TABLE my_student ADD FOREIGN KEY(my_class) REFERENCES my_class(m_id);

单表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- 简单的查询所有的数据
SELECT * FROM my_table;

-- 查询指定表的某几列数据
SELECT m_name, m_sex FROM my_table;

-- 当前的学生分布在哪些班级
SELECT m_class FROM my_table;
SELECT DISTINCT m_class FROM my_table;	#去重


CREATE TABLE my_tab(
	m_id INT,
	m_name VARCHAR(32),
	m_sex enum('男', '女', '保密'),
	m_class VARCHAR(32),
	m_score INT
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO my_tab
VALUES
	( 1, 'xiaoming', '男', '36', 90),
	( 2, 'xiaohong', '女', '33', 78),
	( 3, 'xiaotian', '男', '36',  80),
	( 4, 'xiaoyi', '男', '35', 67 ),
	( 5, 'xiaolan', '女', '35', 94 ),
	( 6, 'xiaojin', '女', '34', 88 );
-- 对查询到的结果进行运算，	
SELECT m_name, m_score*0.6 FROM my_tab;
-- 对查询到的数据取别名
SELECT m_name as 姓名, m_score*0.6 as 成绩 FROM my_tab;

-- 带条件的查询之关系运算符
SELECT m_name, m_class FROM my_tab WHERE m_class > 34;
-- 带条件的查询之IN，位于某些数据之中
SELECT m_name, m_class FROM my_tab WHERE m_class IN(34, 35, 36);
-- 带条件的查询之 BETWEEN AND，位于某个区间
SELECT m_name, m_class FROM my_tab WHERE m_class BETWEEN 34 AND 36;
-- 带条件的查询之 LIKE _(一个任意字符)  %(任意个任意字符)
SELECT m_name, m_class FROM my_tab WHERE m_name LIKE "_____i%";

-- 查询所有分数大于 80 且性别为女的同学
SELECT * FROM my_tab WHERE m_score > 80 AND m_sex = "女";

-- 对查询到的结果，按照学生的分数，以降序 DESC 进行排序，升序 ASC
SELECT * FROM my_tab ORDER BY m_score DESC;
-- 以降序查询分数最高的前三位同学的信息(起始，个数)
SELECT * FROM my_tab ORDER BY m_score DESC LIMIT 0, 3;

-- 统计学生个数 COUNT(*|列名)   统计记录的条数
SELECT COUNT(*) FROM my_tab;

-- 统计玩家平均积分 AVG(数值类型列名)  平均值
SELECT AVG(m_score) FROM my_tab;

-- 统计用户的总分和 SUM (数值类型列名) 求和
SELECT SUM(m_score) FROM my_tab;

-- 获取用户的最高分积分 MAX(列名)  最大值
SELECT MAX(m_score) FROM my_tab;

-- 可以使用 md5 和 sha1 等函数直接地获取计算后的结果
SELECT md5(sha1("jdsojw"));

-- 分组查询，枚举出指定列的指定信息进行运算
SELECT m_class, COUNT(*) FROM my_tab GROUP BY m_class;

-- 查询班级数大于 34 的班级每个班实际有多少同学
SELECT 	m_class, COUNT(*) FROM my_tab GROUP BY m_class HAVING m_class > 34;

多表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- 初始化数据
CREATE TABLE my_class(
m_id INT PRIMARY KEY AUTO_INCREMENT,
m_name VARCHAR(32) NOT NULL UNIQUE,
m_count INT NOT NULL DEFAULT 0
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO my_class
VALUES
(1, "33", 33),
(2, "34", 34),
(3, "35", 35),
(4, "36", 36);

CREATE TABLE my_student (
m_id INT PRIMARY KEY AUTO_INCREMENT,
m_name VARCHAR ( 32 ) UNIQUE NOT NULL,
m_sex ENUM ( '男', '女', '保密' ) DEFAULT '保密',
m_class INT NOT NULL, 
m_score INT 
) CHARACTER 
SET utf8 COLLATE utf8_general_ci;

INSERT INTO my_student
VALUES 
(1, 'xiaoming', '男', 4, 98),
(2, 'xiaogang', '女', 3, 68),
(3, 'xiaohong', '男', 2, 81),
(4, 'xiaolv', '女', 4, 75),
(5, 'dabai', '男', 4, 43),
(6, 'dahei', '女', 3, 89),
(7, 'zhonglan', '男', 2, 92),
(8, 'zhongzi', '保密', 3, 86),
(9, 'xioaming', '保密', 1, 66);


-- 多表查询，交叉连接，查询到的是两张表的乘积（笛卡尔积）
SELECT * FROM my_class, my_student;

-- 多表查询，内连接，将两张表的字段进行了比较
SELECT * FROM my_class, my_student WHERE my_class.m_id = my_student.m_class;

-- 左外连接查询， on 后面是查询条件，以左边的 my_class 为主，如果右边没有数据匹配，就填充 null
SELECT * FROM my_class LEFT JOIN my_student ON my_class.m_id = my_student.m_class;

-- 子查询：外层查询的查询结果依赖于内层的查询， in 条件，结果是否在集合中
SELECT DISTINCT * FROM my_class
	WHERE m_id IN 
	(
		SELECT m_class FROM my_student WHERE m_sex = "女"
	);

-- 判断有没有任何一个同学分数大于 90， 如果有则输出前三名
SELECT * FROM my_student, my_class
	WHERE EXISTS 
	(
		SELECT m_score FROM my_student WHERE m_score > 90
	)AND my_student.m_class = my_class.m_id
	ORDER BY m_score DESC LIMIT 0, 3;
	
-- 如果有男生和任何一个女生的成绩相同就输出信息
SELECT * FROM my_student, my_class
	WHERE m_score = ANY
	(
		SELECT m_score FROM my_student WHERE m_sex = "女"
	)AND my_student.m_class = my_class.m_id AND m_sex = "男";
	
-- 如果有男生的成绩大于任何一个女生的成绩就输出信息
SELECT * FROM my_student, my_class
	WHERE m_score > ALL
	(
		SELECT m_score FROM my_student WHERE m_sex = "女"
	)AND my_class.m_id = my_student.m_class AND m_sex = "男";

#CPP - MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
// 使用 mysql 提供的进行数据库编程，需要保证当前的应用哦个
// 程序版本(x86/x64)，库文件版本和本机的数据库完全一致。需
// 要将安装目录下的整个 inlcude 文件夹和 lib 路径下的 
// libmysql.lib 和 libmysql.dll 拷贝当当前的项目路径下
#include <stdio.h>
#include <windows.h>
#include "include/mysql.h"
#pragma comment(lib, "libmysql.lib")
// 检查数据库的执行是否成功
void Check(MYSQL* mysql) {
	//mysql_errno 返回的是错误码
	if (mysql_errno(mysql)) {
		//mysql_error 返回的是错误信息
		printf("连接数据库出错：%s\n", mysql_error(mysql));
		system("pause");
		exit(0);
	}
}
//向指定的表中添加数据
void Insert(MYSQL* mysql, LPCSTR sql) {
	// 直接执行添加数据的操作
	mysql_query(mysql, sql);

	// 需要检查数据是否添加成功
	Check(mysql);
}
//查询指定的数据
void Select(MYSQL* mysql, LPCSTR sql) {
	// 直接执行添加数据的操作
	mysql_query(mysql, sql);
	Check(mysql);
	// 获取到查询的结果值
	MYSQL_RES* pRes = NULL;
	pRes = mysql_use_result(mysql);
	Check(mysql);
	// 获取到查询结果有多少
	int nColCount = mysql_num_fields(pRes);
	// 输出每一列的列名
	MYSQL_FIELD* pField = nullptr;
	for (int i = 0; i < nColCount; ++i)
	{
		// 从结果中获取到每一列的名称
		pField = mysql_fetch_field(pRes);
		// 某一些情况下不存在列名不存在的就是 null
		if (pField == nullptr)
			continue;
		printf("%s | ", pField->name);
	}
	puts("");
	// 输出每一个查询到的字段
	MYSQL_ROW row = nullptr;
	while (row = mysql_fetch_row(pRes))
	{   // 获取每一行的信息
		for (int i = 0; i < nColCount; ++i)
		{    // 需要提防产生空指针的情况
			if (row[i] != nullptr)
				printf("[%s] ", row[i]);
			else
				printf("[null] ");
		}
		printf("\n");
	}
}
int main(int argc, char* argv[])
{
	// 修改控制台的默认字符编码
	system("chcp 65001");

	// 1. 初始化 mysql 数据库
	mysql_library_init(argc, argv, nullptr);

	// 2. 操作 mysql 数据库需要提供一个 MYSQL 对象
	MYSQL mysql = { 0 };
	mysql_init(&mysql);

	// 3. 连接数据库，需要提供明文的数据库信息
	mysql_real_connect(&mysql, "127.0.0.1", "root",
		"password", "db_name", 3306, nullptr, 0);
	Check(&mysql);

	// 4. 向数据库中添加信息
	//Insert(&mysql, "INSERT INTO my_class(m_name, m_count) VALUE('41', 41);");

	// 5. 从数据库中查询信息
	Select(&mysql, "SELECT * FROM my_student;");

	// 6. 清理 mysql 数据库
	mysql_library_end();

	return 0;
}

Python - MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import pymysql
class mysql(object):
    # 连接到数据库
    def __init__(self):
        try:
            # 创建连接对象，连接到数据库
            self.connect = pymysql.connect(host="127.0.0.1",
                user="root", password="password", db="db_name")
            # 获取到游标对象，游标对象被用于执行查询操作
            self.cursor = self.connect.cursor()
        except Exception as e:
            # 一旦出现问题，这里会打印错误信息
            print(e)

    # 添加数据到表中
    def insert(self, sql):
        try:
            # 使用 execute 执行 sql 指令
            self.cursor.execute(sql)
            # 任何对 sql 数据库执行修改的操作都需要提交
            self.connect.commit()
        except Exception as e:
            # 如果指令执行出错，理论应该回滚操作
            self.connect.rollback()
            # 输出错误信息
            print(e)

    # 查询数据，返回查询到的数据和数量
    def select(self, sql):
        try:
            # 使用 execute 执行 sql 指令
            self.cursor.execute(sql)
            # 使用 fetchall 获取所有结果
            result = self.cursor.fetchall()
            # 使用 rowcount 获取返回的条目数量
            count = self.cursor.rowcount
            # 查询到的结果是一个由所有的条目组成的元组
            # 元组的每一个元素又是一个元组，这个元组中
            # 保存的是查询到的每一列的信息
            return count, result
        except Exception as e:
            # 如果指令执行出错，理论应该回滚操作
            self.connect.rollback()
            # 输出错误信息
            print(e)

if __name__ == "__main__":
    sql = mysql()
    #sql.insert("INSERT INTO my_class VALUE(43, '43', 43);")
    print(sql.select("SELECT * FROM my_student;"))