mysql学习之路

安装：

Linux:

命令：

yum install -y mysql-server mysql-devel mysql

重启：

/etc/init.d.mysql restart

#----------------------------创建数据库---------------------------#

create database db;

CREATE DATABASE db_demo;

#删除数据库 drop database db;
DROP DATABASE db_demo;

#在创建库是指定编码
CREATE DATABASE db_demo CHARACTER SET utf8;

#----------------------------创建表---------------------------#

#使用数据库 use db_demo;
#创建table

CREATE TABLE table_test(
	s_name VARCHAR(40),
	class   VARCHAR(20),
	score  FLOAT
);

#删除表drop table  name
DROP TABLE table_test;

#------------CRUD----------------

insert into talble_name (s1,s2…);

INSERT INTO  table_test(s_name,class,score) VALUES('张三','23',90);

INSERT INTO  table_test VALUES('张三','23',90);
INSERT INTO table_test SET s_name='李四';

INSERT INTO  table_test(s_name,class,score) VALUES('p','23',90),
('a','2',80),
('b','3',80),
('c','4',80),
('d','5',80),
('e','6',80);

INSERT INTO  table_test VALUES('小王',NULL,90);

#= set之后是赋值操作
#’=’ 在where 之后是 关系运算
#删除数据 delete from table 条件

DELETE FROM table_test WHERE s_name='李四';

#------------查询----------------

#select 字段1 ,字段2... from tablename [where];

SELECT s_name,class FROM table_test;
SELECT s_name FROM table_test;
SELECT  * FROM table_test;			#查看所有字段

SELECT s_name FROM table_test WHERE score>=60;
SELECT * FROM table_test WHERE score>=60;
SELECT  s_name AS username FROM table_test WHERE score>=60;

#------------逻辑运算符----------------

SELECT * FROM table_test WHERE class='23' AND score >=60;

#=======DDL操作数据库

SHOW CREATE DATABASE db_demo;
SHOW DATABASES;				#查看所有数据库
#查看当前用户用的数据库
SELECT DATABASE();

SHOW CREATE DATABASE db_demo;
SHOW TABLES;

连接测试代码:

#include<iostream>
#include<string>
#include<mysql.h>

using namespace std;

int main()
{
    //初始化mysal 上下文
    MYSQL mysql;
    mysql_init(&mysql);

    //连接数据库
    if (!mysql_real_connect(
        &mysql,
        "127.0.0.1",	//主机名
        "root",			//用户名
        "qsq123",		//密码
        "student_db",	//数据库名
        3306,
        0,
        0))
    {
        cout << "mysql connect failed!" << endl;
        system("pause");
        return 1;
    }
    cout << "connect success" << endl;
    //插入数据
    //char buf[BUFSIZ] = "INSERT INTO table_stuinfo VALUE(3,011118036,'b',40,30,4,80)";
    string query = "INSERT INTO table_stuinfo VALUE(3,011118036,'b',40,30,4,80)";
    /*int res=mysql_query(&mysql,query.c_str());
    if (res == 0)
    {
        cout << "query success!" << endl;
        getchar();
    }*/
    
    query = "SELECT * FROM  table_stuinfo";
    int res = mysql_query(&mysql, query.c_str());
    if (res == 0)
    {
        cout << "query success!" << endl;
    }
    MYSQL_RES* mysql_res;		
    mysql_res = mysql_store_result(&mysql);		//返回结果集
    //查询字段名
    unsigned int num_fields;
    unsigned int i;
    MYSQL_FIELD *fields;
    num_fields = mysql_num_fields(mysql_res);       //返回结果集中的列数
    fields = mysql_fetch_fields(mysql_res);
    for (i = 0; i < num_fields; i++)
    {
        printf("%s\t",fields[i].name);
    }
    cout << endl;
    int rows=mysql_num_rows(mysql_res);  //返回结果集的行数
   
    //mysql_use_result(&mysql);
    MYSQL_ROW data_row;
    data_row = mysql_fetch_row(mysql_res);       //检索结果集的下一行
    for (int i = 0; i < rows; i++)
    {
        for (int col=0; col < num_fields; col++)
        {
            cout << data_row[col] <<"\t";
        }
        cout << endl;
        data_row = mysql_fetch_row(mysql_res);       //检索结果集的下一行
    }![在这里插入图片描述](https://img-blog.csdnimg.cn/20200306155914823.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dlZWsxMjM1MjA=,size_16,color_FFFFFF,t_70)
    
    mysql_close(&mysql);
    system("pause");
    return 0;
}

输出:

等待继续…