mysql基础

发表于 | 分类于

数据库（Database，DB）就是数据存放的地方，是需要长期存放在计算机内的有组织并且可共享的数据集合。这里所说的数据不仅包括普通意义上的数字，还包括文字、图像、音频、视频等，凡是在计算机中所用来描述事物的记录都可以称为数据。

数据库操作

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;

数据表操作

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建一张表，设置其中的字段信息(列名\类型)
CREATE TABLE my_table(
	m_id INT,
	m_name VARCHAR(32),
	m_sex BOOL,
	m_class VARCHAR(32),
	m_start_date DATETIME
)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;

查看表

1
2
3
4
5
6
7
8
-- 显示指定表的字段信息
DESC my_table;

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

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

修改表

1
2
3
4
5
6
7
8
-- 在指定表的每一个位置添加一个字段 [FIRST \ AFTER column]
ALTER TABLE my_table ADD test2 INT AFTER column_name;

-- 使用 MODIFY 修改指定表中某一列的类型
ALTER TABLE my_table MODIFY test1 DOUBLE;

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

删除表

1
2
3
4
5
-- 删除表中的指定字段
ALTER TABLE my_table DROP test1_new;

-- 删除表
DROP TABLE table_name

插入表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 为指定表的所有字段添加一条记录
INSERT INTO my_table VALUE(1, 'xioaming', '男', '36', "2018-1-1 09:41:02");

-- 未指定表的指定字段添加一条记录，非空字段必须添加
INSERT INTO my_table(m_name, m_sex) VALUE('xiaogang', '保密');

-- 给指定表的所有字段添加多行内容
INSERT INTO my_table 
    VALUES
    (1, 'xiaoming', '男', '36', "2018-1-1 09:41:02"),
    (2, 'xiaogang', '女', '35', "2018-4-1 09:41:02"),
    (3, 'xiaohong', '男', '34', "2018-2-1 09:41:02"),
    (4, 'xiaolv', '女', '36', "2018-8-1 09:41:02"),
    (5, 'dabai', '男', '36', "2018-3-1 09:41:02"),
    (6, 'dahei', '女', '35', "2018-5-1 09:41:02"),
    (7, 'zhonglan', '男', '34', "2018-7-1 09:41:02"),
    (8, 'zhongzi', '保密', '38', "2018-6-1 09:41:02"),
    (9, 'xioaming', '保密', '33', "2018-9-1 09:41:02");

更新表

1
2
3
4
5
-- 设置所有的用于性别为女
UPDATE my_table SET sex="女";

-- 待条件的设置某些用户的性别为女
UPDATE my_table SET sex="男" WHERE m_class="36";

删除字段

1
2
-- 删除姓名为小红的字段
DELETE FROM my_table WHERE m_name='xiaohong';

约束类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 表的约束类型
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;

-- 为 my_student 中的 m_class 添加外键关联到 my_class 中的 m_id
-- 在添加数据的过程中，外键必须是一个已经存在的主键
ALTER TABLE my_student ADD FOREIGN KEY(m_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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
-- 简单的查询所有的数据
SELECT * FROM my_student;

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

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

-- 对查询到的结果进行运算，
SELECT m_name, m_score*0.6 FROM my_student;

-- 对查询到的数据取别名
SELECT m_name as 姓名, m_score*0.6 成绩 FROM my_student;

-- 带条件的查询之关系运算符
SELECT m_name FROM my_student WHERE m_class > 1;

-- 带条件的查询之IN，位于某些数据之中
SELECT m_name,m_class FROM my_student WHERE m_class IN(1, 5);

-- 带条件的查询之 BETWEEN AND，位于某个区间
SELECT m_name,m_class FROM my_student WHERE m_class BETWEEN 1 AND 3;

-- 带条件的查询之 LINK _(一个任意字符)  %(任意个任意字符)
SELECT m_name FROM my_student WHERE m_name LIKE "__a%";

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

-- 对查询到的结果，按照学生的分数，以升序进行排序
SELECT * FROM my_student ORDER BY m_score DESC;

-- 以降序查询分数最高的前三位同学的信息(起始，个数)
SELECT * FROM my_student ORDER BY m_score DESC LIMIT 0, 3;

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

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


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

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

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

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

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


-- 多表查询，交叉连接，查询到的是两张表的乘积（笛卡尔积）
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="女"
	);


-- 判断有没有任何一个同学分数小于 50， 如果有则输出后三名
SELECT * FROM my_student, my_class
	WHERE EXISTS
	(
	SELECT m_score FROM my_student WHERE m_score < 50
	) AND my_student.m_class = my_class.m_id
	ORDER BY m_score ASC 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_student.m_class = my_class.m_id AND m_sex='男';

c连接数据库

image-20191207140127978

如果找不到libmysql.dll，则需要将dll粘贴到项目根目录下

项目平台要与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
92
93
94
95
96
97
98
99
100
101
102
// mysql.cpp : 此文件包含 "main" 函数。程序执行将在此处开始并结束。
//

#include <stdio.h>
#include <windows.h>
#include "mysql.h"
#pragma comment(lib, "libmysql.lib")

void check(MYSQL* mysql)
{
	// mysql_errno 返回的是错误码
	if (mysql_errno(mysql) != 0)
	{
		// 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",
		"root", "my_table", 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连接数据库

安装pymysql模块

image-20191207140638874

image-20191207140659997

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
import pymysql

class mysql(object):

    # 连接到数据库
    def __init__(self):
        try:
            # 创建连接对象，连接到数据库
            self.connect = pymysql.connect(host="127.0.0.1",user="root", password="root", db="my_table")
            # 获取到游标对象，游标对象被用于执行查询操作
            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;"))