用户登录: mysql -h主机地址 -u用户名 -p密码 连接MYSQL;刚装好的MYsql是没有密码的
修改密码:mysqladmin -u用户名 -p旧密码 password 新密码 修改密码
增加用户: grant select on 数据库.* to 用户名@登录主机 identify by '密码'
删除用户 :mysql>delete from mysql.user where user ='yushan' ;
mysql>flush privileges; (刷新系统权限表)
查看用户:SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
查看数据库中具体某个用户的权限
mysql> show grants for 'cactiuser'@'%';
插入语句: eg: INSECT INTO children(fname, age) VALUES(‘ann’,3);
use mysql;
show tables; 显示库中的数据表
describe 表名; 显示数据表的结构
create database 库名; 建库
use 库名;
create table 表名 (字段设定列表); 建表
drop database 库名;
drop table 表名; 删库和删表
delete from 表名; 将表中记录清空
select * from 表名; 显示表中的记录
编译:gcc -I/usr/include/mysql *.c -L/usr/lib/mysql -lmysqlclient -o *
MYSQL *mysql_init(MYSQL *);
//这里称之为载入函数吧,返回的MYSQL指针要用到后续的函数中
int mysql_options(MYSQL *connection, enum option_to_set,const char *argument);
//设置MYSQL*的一些属性,比如超时时间等
MYSQL *mysql_real_connect(MYSQL *connection,
const char *server_host,
const char *sql_user_name,
const char *sql_password,
const char *db_name,
unsigned int port_number,//置0连接默认端口,一般为3306
const char *unix_socket_name,//NULL
unsigned int flags);//无另外属性时置0
//连接函数
void mysql_close(MYSQL *connection);
//关闭连接
unsigned int mysql_errno(MYSQL *connection);
//返回错误代码
char *mysql_error(MYSQL *connection);
//返回错误信息
int mysql_query(MYSQL *connection, const char *query);
//执行sql语句
my_ulonglong mysql_affected_rows(MYSQL *connection);
//返回执行语句过后受影响的行数
MYSQL_RES *mysql_store_result(MYSQL *connection);
//返回执行结果,适用于数据量较小时
my_ulonglong mysql_num_rows(MYSQL_RES *result);
//返回上面函数返回结果的行数
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
//抽取一条记录,返回NULL时表示抽取完记录或者错误
void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);
//调整数据位置,offset为0时,下次调用mysql_fetch_row将返回result第一条记录
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result);
//返回当前的位置
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset);
//移动数据位置,并返回先前的位置,可以和上一个函数结合使用
void mysql_free_result(MYSQL_RES *result);
//释放result空间
MYSQL_RES *mysql_use_result(MYSQL *connection);
//返回执行结果,适用于数据量较大时
unsigned int mysql_field_count(MYSQL *connection);
//返回查询结果中的列数(column数)
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result);
//获得查询结果中的列名等信息(表头信息)
#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"
int res;
MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
int insert(char *mysql);
void display_header();
void display_row();
int main(int argc, char *argv[])
{
int first_row = 1;
mysql_init(&my_connection);// 数据链接初始化
if (mysql_real_connect(&my_connection, "localhost", "test", "test", "test", 0, NULL, 0)){
printf ("Connection succeess \n");//数据库连接
// 执行insert 语句插入一行
//insert(&my_connection);
//查询语句
res = mysql_query (&my_connection, "SELECT childno, fname, age FROM children WHERE age > 5");// 要指行的语句
if(res){
printf("SELECT error: %s\n",mysql_error(&my_connection) );
}else {
res_ptr = mysql_use_result(&my_connection);// 指向结果指针
if(res_ptr){
// pirntf("Retrieved %lu rows\n", (unsigned long)mysql_num_result(res_ptr));
while ((sqlrow = mysql_fetch_row(res_ptr))){
if(first_row){
display_header();
first_row = 0 ;
}
display_row();
}
if (mysql_errno(&my_connection))
{
fprintf(stderr, "Retrieve error: %s\n", mysql_error(&my_connection) );
}
mysql_free_result(res_ptr);
}
}
mysql_close(&my_connection);
} else {//连接失败,返回错误信息
fprintf(stderr, "Connection failed\n" );
if (mysql_errno(&my_connection)){
fprintf(stderr, "Connection errno %d: %s\n",
mysql_errno(&my_connection), mysql_error(&my_connection) );
}
}
return EXIT_SUCCESS;
}
int insert(char *mysql)
{
res = mysql_query(&my_connection, "INSERT INTO children(fname, age) VALUES('ann', 3)");
if(! res){
printf("Insered %lu rows \n", (unsigned long)mysql_affected_rows(&my_connection) );
} else{
fprintf(stderr, "Insert error %d: %s \n", mysql_errno(&my_connection), mysql_error(&my_connection) );
}
//查询最后插入数据的id号
res = mysql_query(&my_connection, "SELECT LAST_INSERT_ID()");
if(res){
printf("SECLECT error : %s\n", mysql_error(&my_connection) );
} else {
res_ptr = mysql_use_result(&my_connection);
if(res_ptr) {
while ((sqlrow = mysql_fetch_row(res_ptr))){
printf("We inserted childno %s\n", sqlrow[0] );
}
}
mysql_free_result(res_ptr);
}
}
void display_header(){
MYSQL_FIELD *field_ptr;
printf("Colume details: \n");
while((field_ptr = mysql_fetch_field(res_ptr)) != NULL){
printf("\t Name: %s\n", field_ptr->name);
printf("\t Type:");
if (IS_NUM(field_ptr->type )){
printf("Numeric field\n");
}else {
switch(field_ptr->type){
case FIELD_TYPE_VAR_STRING:
printf("VARCHAR\n");
break;
case FIELD_TYPE_LONG:
printf("LONG \n");
break;
default :
printf("TYpe is %d, check in mysql_com.h\n", field_ptr->type );
}/*swith*/
}/* else*/
printf("\t Max width %ld\n", field_ptr->length);
if (field_ptr->flags & AUTO_INCREMENT_FLAG){
printf("\t Auto increments \n");
printf("\n");
}/*while*/
}
}
void display_row(){
unsigned int field_count;
field_count = 0;
while (field_count < mysql_field_count(&my_connection)){
if (sqlrow[field_count]) printf(" %s", sqlrow[field_count] );
else printf("NULL");
field_count ++;
}
printf("\n");
}
/*
ddy@ddy-K45VD:~/桌面/mysql$ ./mysql
Connection succeess
Colume details:
Name: childno
Type:Numeric field
Max width 11
Auto increments
Name: fname
Type:VARCHAR
Max width 30
Name: age
Type:Numeric field
Max width 11
1 Jenny 21
2 Andrew 17
*/
修改密码:mysqladmin -u用户名 -p旧密码 password 新密码 修改密码
增加用户: grant select on 数据库.* to 用户名@登录主机 identify by '密码'
删除用户 :mysql>delete from mysql.user where user ='yushan' ;
mysql>flush privileges; (刷新系统权限表)
查看用户:SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
查看数据库中具体某个用户的权限
mysql> show grants for 'cactiuser'@'%';
插入语句: eg: INSECT INTO children(fname, age) VALUES(‘ann’,3);
use mysql;
show tables; 显示库中的数据表
describe 表名; 显示数据表的结构
create database 库名; 建库
use 库名;
create table 表名 (字段设定列表); 建表
drop database 库名;
drop table 表名; 删库和删表
delete from 表名; 将表中记录清空
select * from 表名; 显示表中的记录
编译:gcc -I/usr/include/mysql *.c -L/usr/lib/mysql -lmysqlclient -o *
MYSQL *mysql_init(MYSQL *);
//这里称之为载入函数吧,返回的MYSQL指针要用到后续的函数中
int mysql_options(MYSQL *connection, enum option_to_set,const char *argument);
//设置MYSQL*的一些属性,比如超时时间等
MYSQL *mysql_real_connect(MYSQL *connection,
const char *server_host,
const char *sql_user_name,
const char *sql_password,
const char *db_name,
unsigned int port_number,//置0连接默认端口,一般为3306
const char *unix_socket_name,//NULL
unsigned int flags);//无另外属性时置0
//连接函数
void mysql_close(MYSQL *connection);
//关闭连接
unsigned int mysql_errno(MYSQL *connection);
//返回错误代码
char *mysql_error(MYSQL *connection);
//返回错误信息
int mysql_query(MYSQL *connection, const char *query);
//执行sql语句
my_ulonglong mysql_affected_rows(MYSQL *connection);
//返回执行语句过后受影响的行数
MYSQL_RES *mysql_store_result(MYSQL *connection);
//返回执行结果,适用于数据量较小时
my_ulonglong mysql_num_rows(MYSQL_RES *result);
//返回上面函数返回结果的行数
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
//抽取一条记录,返回NULL时表示抽取完记录或者错误
void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);
//调整数据位置,offset为0时,下次调用mysql_fetch_row将返回result第一条记录
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result);
//返回当前的位置
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset);
//移动数据位置,并返回先前的位置,可以和上一个函数结合使用
void mysql_free_result(MYSQL_RES *result);
//释放result空间
MYSQL_RES *mysql_use_result(MYSQL *connection);
//返回执行结果,适用于数据量较大时
unsigned int mysql_field_count(MYSQL *connection);
//返回查询结果中的列数(column数)
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result);
//获得查询结果中的列名等信息(表头信息)
#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"
int res;
MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
int insert(char *mysql);
void display_header();
void display_row();
int main(int argc, char *argv[])
{
int first_row = 1;
mysql_init(&my_connection);// 数据链接初始化
if (mysql_real_connect(&my_connection, "localhost", "test", "test", "test", 0, NULL, 0)){
printf ("Connection succeess \n");//数据库连接
// 执行insert 语句插入一行
//insert(&my_connection);
//查询语句
res = mysql_query (&my_connection, "SELECT childno, fname, age FROM children WHERE age > 5");// 要指行的语句
if(res){
printf("SELECT error: %s\n",mysql_error(&my_connection) );
}else {
res_ptr = mysql_use_result(&my_connection);// 指向结果指针
if(res_ptr){
// pirntf("Retrieved %lu rows\n", (unsigned long)mysql_num_result(res_ptr));
while ((sqlrow = mysql_fetch_row(res_ptr))){
if(first_row){
display_header();
first_row = 0 ;
}
display_row();
}
if (mysql_errno(&my_connection))
{
fprintf(stderr, "Retrieve error: %s\n", mysql_error(&my_connection) );
}
mysql_free_result(res_ptr);
}
}
mysql_close(&my_connection);
} else {//连接失败,返回错误信息
fprintf(stderr, "Connection failed\n" );
if (mysql_errno(&my_connection)){
fprintf(stderr, "Connection errno %d: %s\n",
mysql_errno(&my_connection), mysql_error(&my_connection) );
}
}
return EXIT_SUCCESS;
}
int insert(char *mysql)
{
res = mysql_query(&my_connection, "INSERT INTO children(fname, age) VALUES('ann', 3)");
if(! res){
printf("Insered %lu rows \n", (unsigned long)mysql_affected_rows(&my_connection) );
} else{
fprintf(stderr, "Insert error %d: %s \n", mysql_errno(&my_connection), mysql_error(&my_connection) );
}
//查询最后插入数据的id号
res = mysql_query(&my_connection, "SELECT LAST_INSERT_ID()");
if(res){
printf("SECLECT error : %s\n", mysql_error(&my_connection) );
} else {
res_ptr = mysql_use_result(&my_connection);
if(res_ptr) {
while ((sqlrow = mysql_fetch_row(res_ptr))){
printf("We inserted childno %s\n", sqlrow[0] );
}
}
mysql_free_result(res_ptr);
}
}
void display_header(){
MYSQL_FIELD *field_ptr;
printf("Colume details: \n");
while((field_ptr = mysql_fetch_field(res_ptr)) != NULL){
printf("\t Name: %s\n", field_ptr->name);
printf("\t Type:");
if (IS_NUM(field_ptr->type )){
printf("Numeric field\n");
}else {
switch(field_ptr->type){
case FIELD_TYPE_VAR_STRING:
printf("VARCHAR\n");
break;
case FIELD_TYPE_LONG:
printf("LONG \n");
break;
default :
printf("TYpe is %d, check in mysql_com.h\n", field_ptr->type );
}/*swith*/
}/* else*/
printf("\t Max width %ld\n", field_ptr->length);
if (field_ptr->flags & AUTO_INCREMENT_FLAG){
printf("\t Auto increments \n");
printf("\n");
}/*while*/
}
}
void display_row(){
unsigned int field_count;
field_count = 0;
while (field_count < mysql_field_count(&my_connection)){
if (sqlrow[field_count]) printf(" %s", sqlrow[field_count] );
else printf("NULL");
field_count ++;
}
printf("\n");
}
/*
ddy@ddy-K45VD:~/桌面/mysql$ ./mysql
Connection succeess
Colume details:
Name: childno
Type:Numeric field
Max width 11
Auto increments
Name: fname
Type:VARCHAR
Max width 30
Name: age
Type:Numeric field
Max width 11
1 Jenny 21
2 Andrew 17
*/