mysqlsql命令大全

下面貼出我在實際工作中遇到mysql操作數據表的sql命令，如有不對的地方，請多指教：

c++鏈接mysql頭文件命令
   g++ is_in_polygon.cpp -o is_in_polygon -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient

eclipse 設置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 table
drop 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 all
SELECT * 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);
}

該函數主要用來插入，刪除，添加功能。