bitsCN.com
下面贴出我在实际工作中遇到mysql操作数据表的sql命令，如有不对的地方，请多指教：
c++链接mysql头文件命令 g++ is_in_polygon.cpp -o is_in_polygon -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclienteclipse 设置mysql project->setting->properties->tool settings->libraries-libraries(l) write into:mysqlclient. project->properties->tool settings->libraries->libraries search path write into:/usr/lib/mysql. project->properties->c/c++ build->environment->cplus_include_path and c_include_path 加入:/usr/include/mysql建立数据表 use test; create table test_info ( id integer not null, content varchar(64) not null, primary key (id) ); delete from test_info; insert into test_info values (2010, 'hello, line suped seped "end' ); 向数据表导入数据 load data local infile '/tmp/test.csv' into table test_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '/r/n'; 增加列 alter table t_icf_day add new_field_id int(5); alter table t_icf_day add column day_id BIGINT primary key auto_increment; 设主键 alter table userinfo add prmariy key (userId); 删除表drop tabledrop table if exits '%s_T_ICF_HIST_DATE'删除列 alter table t2 drop column c;查找不重复的数据insert into T_ICF_HIST_D select a.* from China_t_icf_hist_d a,(select c_gouki,c_kisyu,count(*) from %s_T_ICF_DAY group by c_gouki,c_kisyu having count(*)>=1) as b where a.c_gouki=b.c_gouki and a.c_kisyu=b.c_kisyu;",重命名列alter table t1 change a b integer;改变列的类型 alter table t1 change b b bigint not null; alter table infos change list list tinyint not null default '0';重命名表 alter table t1 rename t2;多表查询 select c.nom, e.nom from consultant c, affaire a, besoin b, salarie sa, site s, entreprise e where c.consultant_id=a.consultant_id and a.besoin_id=b.besoin_id and b.salarie_id=sa.salarie_id and sa.site_id=s.site_id and s.entreprise_id=e.entreprise_id插入符合条件的列 insert into gansu_icf_hist_d select b.* from gansu_t_icf_day a, T_ICF_HIST_D b where a.c_kisyu=b.c_kisyu and a.c_gouki=b.c_gouki; insert into gansu_day select a.* from t_icf_day a, gansu_gis_convert_result b where a.d_hassei=b.d_hassei and a.c_gouki=b.c_gouki;查询后，插入表中 insert into gansu_gis_convert_result SELECT * FROM t_gis_convert_result_icf_other where nv_place='GANSU, China';向表中添加数据1 insert into employee values (’200301’,’zhangsan’,’m’,’1978/5/8’);2 insert into employee values (’200302’,’lisi’,’f’,’1973/3/20’);创建索引1 create table test1 (test1_id char(4),name char(20), index idx_test1(name(10)));2 create index idx_employee on employee(name); 用create为name列创建索引察看索引 1 show index from employee;2 show index from products;删除索引 drop index idx_employee on employee; alter table products drop index idx_products;查看代码select * from gansu_day group by c_kisyu and d_hassei and c_gouki having count(*) > 1;多表查询insert into yunnan_gis_convert_result SELECT * FROM t_gis_convert_result_icf_AWS where nv_place='YUNNAN, China' union allSELECT * FROM t_gis_convert_result_icf_AXA_AWU where nv_place='YUNNAN, China' union all SELECT * FROM t_gis_convert_result_icf_other where nv_place='YUNNAN, China';insert into LIAONING_T_ICF_HIST_D select a.* from China_t_icf_hist_d a,(select c_gouki,c_kisyu,count(*) from LIAONING_T_ICF_DAY group by c_gouki,c_kisyu having count(*)>=1) as b where a.c_gouki=b.c_gouki and a.c_kisyu=b.c_kisyu;远程访问数据库 http://hi.baidu.com/andycai/blog/item/5c8dabcc97fa931701e9281f.html http://blog.csdn.net/uixor_/article/details/6762194
其实直接看mysql的syntax就可以，不过没有这样直观。
下面给出c++链接mysql语句
MYSQL_RES *Querysql(char *sql) { MYSQL_RES *res; MYSQL_ROW row; char *server = "localhost";/*服务器名*/ char *user = "root";/*用户名*/ char *password = ""; /* 此处改成你的密码 */ char *database = "EserviceDB";/*数据库名*/ MYSQL *conn = mysql_init(NULL); /* Connect to database */ if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) { fprintf(stderr, "%s/n", mysql_error(conn)); return res; } /* send SQL query */ if (mysql_query(conn, sql)) {//sql语句 fprintf(stderr, "%s/n", mysql_error(conn)); return res; } res = mysql_store_result(conn);//保存查询结果 mysql_close(conn); return res;}这个函数主要用来链接数据库，返回带有数据格式为：MYSQL_RES,主要用于查询操作：
void NoQuery(char *sql) { MYSQL_RES *res; MYSQL_ROW row; char *server = "localhost";/*服务器名*/ char *user = "root";/*用户名*/ char *password = ""; /* 此处改成你的密码 */ char *database = "EserviceDB";/*数据库名*/ MYSQL *conn = mysql_init(NULL); if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) { fprintf(stderr, "%s/n", mysql_error(conn)); printf("the connection fail!"); } if (mysql_query(conn, sql)) {//sql语句 fprintf(stderr, "%s/n", mysql_error(conn)); printf("the query fail!"); } else printf("query insert sql sucess"); mysql_close(conn);}该函数主要用来插入，删除，添加功能。
bitsCN.com
