linux编程mysql

使用mysql的root登入后设置

mysql -u root -p

先创建新的数据库

create database db1;

建立db数据库的用户

grant all on.* to dbuser1 identified by '123456';

退出

quit

使用新用户登入

mysql -u dbuser1 -p

use db1

create table table3 (id int primary key auto_increment, name varchar(20), age int)

#include <stdio.h>#include <stdlib.h>#include <unistd.h>#include <string.h>#include <error.h>#include <mysql/mysql.h>#define BUFSIZE 1024MYSQL mysql;MYSQL *connection;void leselect(){char sql[BUFSIZE];memset(sql, 0, sizeof(sql));strcpy(sql, "please input select SQL\n");write(STDOUT_FILENO, sql, strlen(sql));char name[BUFSIZE];memset(name, 0, sizeof(name));read(STDIN_FILENO, name, sizeof(name));name[strlen(name)-1] = 0;memset(sql, 0, sizeof(sql));sprintf(sql, "select * from table3 where name = '%s'",name);if(mysql_query(connection, sql) != 0){printf("select failed");}MYSQL_RES *result = mysql_store_result(connection);MYSQL_FIELD *field;MYSQL_ROW row;int count = 0;while(1){field = mysql_fetch_field(result);if(field == NULL)break;printf("%s\t",field->name);count ++;}printf("\n");while(1){row = mysql_fetch_row(result);if( row == NULL)break;int i;for(i =0; i<count; i++){printf("%s\t",(const char*)row[i]);}printf("\n");}mysql_free_result(result);}void insert(){char sql[BUFSIZE];memset(sql, 0, sizeof(sql));strcpy(sql, "please input insert SQL is name and age\n");write(STDOUT_FILENO, sql, strlen(sql));char name[BUFSIZE];memset(name, 0, sizeof(name));read(STDIN_FILENO, name, sizeof(name));name[strlen(name)-1] = 0;memset(sql, 0, sizeof(sql));char age[BUFSIZE];memset(age, 0, sizeof(age));read(STDIN_FILENO, age, sizeof(age));age[strlen(age)-1] = 0;sprintf(sql, "insert into table3 (name,age)values('%s', '%s');",name,age);printf("%s",sql);if(mysql_query(connection, sql) != 0){printf("insert failed\n");}else{printf("insert success\n");}}void delete(){char sql[BUFSIZE];memset(sql, 0, sizeof(sql));strcpy(sql, "please input delete name\n");write(STDOUT_FILENO, sql, strlen(sql));char name[BUFSIZE];memset(name, 0, sizeof(name));read(STDIN_FILENO, name, sizeof(name));name[strlen(name)-1] = 0;memset(sql, 0, sizeof(sql));sprintf(sql, "delete from table3 where name = '%s'",name);if(mysql_query(connection, sql) != 0){printf("success failed\n");}else{printf("delete success\n");}}void alter(){char sql[BUFSIZE];memset(sql, 0, sizeof(sql));strcpy(sql, "please input update name\n");write(STDOUT_FILENO, sql, strlen(sql));char name[BUFSIZE];memset(name, 0, sizeof(name));read(STDIN_FILENO, name, sizeof(name));name[strlen(name)-1] = 0;char age[BUFSIZE];memset(age, 0, sizeof(age));read(STDIN_FILENO, age, sizeof(age));age[strlen(age)-1] = 0;memset(sql, 0, sizeof(sql));sprintf(sql, "update table3 set age = '%s' where name = '%s'",age,name);if(mysql_query(connection, sql) != 0){printf("update failed\n");}else{printf("update success\n");}}int main(){mysql_init(&mysql);connection = mysql_real_connect(&mysql, "localhost", "dbuser1", "123456", "db1", 0, 0, 0);if ( connection == NULL){printf("connect failed, %s\n", mysql_error(&mysql));return -1;}if (  mysql_query(connection, "set names utf8") != 0){printf("set names failed, %s\n", mysql_error(&mysql));}char buf[BUFSIZE];memset(buf, 0, sizeof(buf));strcpy(buf, "please choose\n1:insert\n2:delete\n3:alter\n4:select\n");write(STDOUT_FILENO, buf, strlen(buf));memset(buf, 0, sizeof(buf));read(STDIN_FILENO, buf, sizeof(buf));if(strncmp(buf, "4", 1) == 0){leselect();}if(strncmp(buf, "3", 1) == 0){alter();}if(strncmp(buf, "2", 1) == 0){delete();}if(strncmp(buf, "1", 1) == 0){insert();}mysql_close(connection);return 0;}

gcc -o lesql lesql.c -lmysqlclient编译