1.数据库的设计
(1)创建学校库
create database school;
use school;
(2)创建老师表
#创建老师表
create table class(id int primary key auto_increment,
class_name varchar(32) not null,
teacher varchar(32) not null);
#插入记录
insert into class values(101,'高一1班','吴老师');
insert into class(class_name,teacher)values('高一2班','何老师');
insert into class(class_name,teacher)values('高一3班','孟老师');
insert into class(class_name,teacher)values('高一4班','张老师');
#查询插入的记录
select* from class ;
2.从VS2019里连接数据库
(1)连接school库
*功能:连接数据库
*输入:数据库句柄
*返回值: true - 成功 false - 失败
bool connect_db ( MYSQL & mysql) {
mysql_init ( & mysql) ;
mysql_options ( & mysql, MYSQL_SET_CHARSET_NAME, "gbk" ) ;
if ( mysql_real_connect ( & mysql, "127.0.0.1" , "root" , DB_PASSWORD, "school" , 3306 , NULL , 0 ) == NULL ) {
cout << "连接失败,错误原因:" << mysql_error ( & mysql) << endl;
return false ;
} else {
cout << "连接成功" << endl;
return true ;
}
}
(2)完整代码
# include <mysql.h>
# include <iostream>
# include <string>
# define DB_PASSWORD "123456789"
using namespace std;
typedef struct student_info
{
int classid;
string class_name;
string teacher;
} studentinfo;
int main ( ) {
MYSQL mysql;
char sql[ 256 ] ;
MYSQL_RES* res;
MYSQL_ROW row;
studentinfo class_DB;
int in_class;
if ( connect_db ( mysql) == false ) {
return 0 ;
}
cout << "请输入你所在的班级:" ;
cin >> in_class;
snprintf ( sql, 256 , " select *from class where id=%d;" , in_class) ;
bool ret = mysql_query ( & mysql, sql) ;
if ( ret) {
cout << "数据库查询出错,错误原因:" << mysql_error ( & mysql) ;
mysql_close ( & mysql) ;
return false ;
}
res = mysql_store_result ( & mysql) ;
row = mysql_fetch_row ( res) ;
if ( row == NULL ) {
mysql_free_result ( res) ;
mysql_close ( & mysql) ;
return 0 ;
}
class_DB. classid = atoi ( row[ 0 ] ) ;
class_DB. class_name = row[ 1 ] ;
class_DB. teacher = row[ 2 ] ;
cout << "班主任:" << class_DB. teacher << " "
<< "所教班级:" << class_DB. class_name<< endl;
mysql_free_result ( res) ;
mysql_close ( & mysql) ;
system ( "pause" ) ;
return 0 ;
}
(3)测试结果