第三章 C++与Mysql数据库
3.1 数据库的概念和基本操作
数据库处理的数据一般是表格状的数据,行称为row,列称为column。每一行称为一条记录record,每一列称为一个字段field。数据库就是存放数据的地方。一个可以操作数据库并提供相关服务的软件,称为数据库服务器。数据库服务器的基本的操作包括增删改查。Mysql就是这样的一个数据库服务器。有时,我们把数据库服务器简称为数据库。
C/S模式(客户端/服务器):客户端和服务器通过网络连接交互数据。
本文所用Mysql版本为mysql-5.5.24-win32,是比较老的版本。测试软件使用的是SQLyog。用Mysql作为服务器,SQLyog作为客户端。测试的具体操作可以参考其他大佬的CSDN博客,有详细说明。
3.1.1 添加和删除数据库
完成SQLyog和Mysql的链接,进入SQLyog的界面。左边栏里是4个默认的数据库,其中information_scheme,mysql和performance_scheme是不能更改的,text数据库是给用户测试用的库。可以在该栏直接创建或删除数据库。
一个数据库中可以存放多个表,表可以有多个列数据类型有多种,包括:整数,小数,字符串,长文本,时间和二进制数据。新建表时,要定义其每一列的属性(类型、索引、自增和默认值),统称为scheme(大纲)。表的操作包括增删改。
3.1.2 表
一、列的属性
列名 | 风格:log_text或logText | 注意不要使用SQL关键字作为列名,如init,text,type,limit和group。 |
数据类型 | 整数:tinyint(1字节),smallint(2字节),int(4字节)和long(8字节)等。 小数:float,double 字符串: char(定长字符串,输入不超过N个字符,在内存中占用N个字节,优点是查询块) varchar(变长字符串,输入不超过N个字符,在内存中占用实际长度,可节省空间) 长文本:text(优点是存储超长文本) 时间:data,time,datetime 二进制数据blob(大块数据) | |
非空 | 空值NULL | |
自增 | 对于整数字段,可以选择为自增。 | 当为自增时,该列的值无需制定,内部自动按照顺序填充 |
二、表的定义
主键:用于唯一地标识一条记录。该字段不允许重复,且不允许为NULL。
联合主键:将多个字段联合起来,构成该表的主键。但是并不推荐使用,可以多添加一个字段,单独记录ID。
三、索引
用于加快排序与查找。
比如,要查找student表中所有年龄在1992-1994的学生的记录。在未加索引时,需要从头遍历每一条记录,比较其birthday字段。当记录的存储是未经排序的时候,这种查询速度是很慢的,
索引是用该字段的值单独存储,并且是排序后存储。好处是查询速度快。缺点是:1、建立和更新索引需要时间;2、索引本身占据存储空间。索引包括数据和指向的记录号,后者类似于指针。
唯一索引:该索引的字段不能重复。主键其实就是一种唯一索引。
普通索引:允许重复。
一个数据读多写少时,可以考虑增加多个索引以加快查询速度。相反如果操作频繁,加索引时要慎重。
3.1.3 SQL语句
SQL:Structured Query Language,称为结构化查询语言,用于跟SQL服务器对话。SQL属于解释执行的编程语言,其代码文件通常称为脚本,直接被解释器执行,
例3.1.1 SQL脚本实例
/*进入example数据库*/
USE EXAMPLE; /*每个SQ;语句末尾都要添加分号*/
/*查询student表*/
SELECT * FROM student; /*关键词建议大写*/
例3.1.2 SQL脚本实例
USE `example`; /*使用example数据库*/
DROP TABLE IF EXISTS `sample`; /*如果存在就删除这张表*/
CREATE TABLE `sample`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(16) NOT NULL,
PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk;
insert into `sample`(`id`,`name`) /*插入数据*/
values(1,`Li Ming`),(2,`Yang Hua`);
SQLyog客户端负责将上述脚本发送给Mysql服务器,然后Mysql返回执行结果。所以SQL语言是客户端与服务器之间对话的语言。
一、使用SQL语句来创建与删除数据库
创建 | 删除 |
CREATE DATABASE `example`; | DROP DATABASE `example`; |
如果名称里无特殊字符,可以不用加``(这个不是单撇号,是tab键上面那个符号)。
二、表的创建与删除
创建 | 删除 |
CREATE TABLE `simple`( `id` int(11) NOT NULL, `name` char(32) DEFAULT NULL, PRIMARY KEY(`id`) }; | DROP TABLE `simple`; |
三、SQLyog的导入与导出
SQLyog可以将数据库中的内容导出,也可以导入数据。导出的内容不仅包含表的结构,还可以包含表的内容数据。
四、INSERT添加记录
在开始操作之前,需要使用USE来指定要操作的数据库:USE `数据库名`
Eg:
USE `example`;
添加记录:INSERT INTO`表名`,(`列名`) VALUES (‘数据’);
Eg:
INSERT INTO `student`
(`id`, `name`, `birthday`, `cellphone`)
VALUES
('201511', 'wang', '1992‐12‐2', '18601088987');/*数据用单撇号括起来*/
注意:
- 列名要与列值一一对应。
- 不一定要给出每一列的值。
- 可以一次INSERT多行数据
- 空值以NULL指定
五、SELECT查询记录:WHERE
SELECT语句用于查询表中的数据。
SELECT `列` FROM `表`
SELECT * FROM `student`;/*用*表示所有列*/
SELECT `id`, `name` FROM `student`;/*只查看`id`和`name`*/
更改列的名字:
SELECT `id` AS `学号` , `name` AS `姓名` FROM `student`;
一般来说,用户只想查询符合条件的记录。语法如下:
SELECT `列` FROM `表` WHERE 条件
Eg:
WHERE id=20151;
WHERE name='wang bo';
/*当然也可以使用数值比较*/
SELECT * FROM `student` WHERE `id` <> 201511;/* <>表示不等于号*/
SELECT * FROM `student` WHERE `birthday` < ‘1994-1-1’;/*日期要用单撇号括起*/
/*字符串比较大小,比较的是ACSII码,类似于C++的strcmp*/
WHERE name = 'shao fa';
/*LIKE通配符比较*/
WHERE name LIKE 'shao%';/*查找所有以’shao’开头的*/
WHERE name LIKE '%fa';/*查找所有以’fa’结尾的*/
%就是通配符!
SQL的与或非分别对应:AND,OR和NOT。
SELECT * FROM `student` WHERE (`birthday` < IS NOT NULL) AND (`id`<201516);
六、SELECT查询记录:排序与分页
Mysql中使用ORDER BY这一个关键字对SELECT查询结果进行排序,所有满足条件的行称为结果集ResultSet。
ORDER BY `列名` ASC;升序
ORDER BY `列名` DESC;降序
SELECT * FROM `student` ORDER BY `birthday` ASC;
SELECT * FROM `student` WHERE `birthday` IS NOT NULL ORDER BY `birthday` ASC;
LIMIT子句用于指定每次取回的行号范围,完成分页显示。
LIMIT strat,n;
/*表示从第start行开始,第0行就是符合条件的第1行,n表示取多少行*/
那如何知道结果集中有多少行呢?可以使用COUNT函数来得到结果集的个数。
SELECT COUNT(`id`) AS `result` FROM `student` WHERE NAME LIKE `shao%`;
/*COUNT(`id`)用于对ID字段进行计数*/
DAYOFWEEK()函数用于求一个日期对应是星期几。
SELECT `name`, `birthday` ,DAYOFWEEK(`birthday`) AS `result`
FROM `student`
七、SELECT查询记录:联合查询(属于内联)
例3.1.3 数据库example下有3个表student,exam和game。student为学生的基本信息,包括学号和姓名,exam为记录所有学生所有考试的成绩,game表示某次竞赛的成绩,记录初始成绩为preTest,决赛成绩为finalText。所有的数据通过stuId查询。
1、列出本次考试的所有姓名,成绩。
Eg:
SELECT * FROM student, exam WHERE student.id = exam.stuId ;
2、列出本次考试的参与者的姓名,成绩(5个学生仅2人参加)。
Eg:
SELECT * FROM student, game WHERE student.id = game.stuId ;
前表5条记录,后表2条记录,最后仅有2条记录。即只有两个表中的共同项被列出(交集)。
上述操作有可能显示多列,其中有些列不需要反馈,这句需要列的指定。
SELECT student.id, student.name, exam.chinese /*从student表中抽取id,name,从exam中抽取chinese*/
FROM student, exam
WHERE exam.`stuId` = student.id;
可以为表设置临时别名,以简化上述SQL代码。
SELECT t1.id, t1.name, t2.chinese
FROM student t1, exam t2 /*将student重命名为t1,将exam重命名为t2*/
WHERE t1.id = t2.stuId;
八、内联与外联
内联INNER JOIN | 外联 |
SELECT * FROM student , game WHERE student.id = game.stuId; 等价于: SELECT * FROM student INNER JOIN game ON student.id = game.stuId; | 左外联LEFT OUTER JOIN 右外联RIGHT OUTER JOIN 全外联FULL OUTER JOIN |
关键字: ……FROM……INNER JOIN……ON…… ON指定了连接的条件。 | 左外联为例:在student为主,game为辅,缺的项以NULL显示。效果如图3.1. SELECT * FROM student LEFT OUTER JOIN game ON game.stuId = student.id ; |
内联的特点:返回两个表的交集。 | Mysql不支持全外连接。 |
图 3.1 左外联
九、修改与删除记录
UPDATE修改记录 | DELETE |
UPDATE <表> SET <列1>= <值1> , <列2>= <值2> WHERE <条件> ; /* WHERE指定限制条件,即要修改哪些行*/ | 删除所有记录 DELETE FROM <表> ; 删除满足条件的记录 DELETE FROM <表> WHERE <条件> ; |
Eg: UPDATE `student` SET `birthday` = '1992‐06‐12' , `cellphone` = '18612111127' WHERE `id` = 201514; /*更新多个字段时,用逗号分开*/ | Eg: DELETE FROM `simple`; DELETE FROM `student` WHERE `id` < 201518 AND `id` >201513 |