Mysqlsql command Overview

The following shows the SQL commands used to operate data tables in mysql in my actual work. If any of these statements is incorrect, please advise:

C ++ connects to the mysql header file command g ++ is_in_polygon.cpp-o is_in_polygon-I/usr/include/mysql-L/usr/lib/mysql-lmysqlclienteclipse to set mysql project-> setting-> properties-> tool settings-> libraries-libraries (l) write into: mysqlclient. project-> properties-> tool settings-> libraries search path write into:/usr/lib/mysql. project-> properties-> c/c ++ build-> environment-> cplus_include_path and c_include_path Add:/usr/include/mysql CREATE data table 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'); import data to the data table load data local infile '/tmp/test.csv 'into table test_info fields terminated ', 'optionally enclosed by '"'escaped by'" 'Lines terminated by '\ r \ n'; add the 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; Set primary key alter table userinfo add prmariy key (userId ); delete table drop tabledrop table if exits '% s_icf_hist_date' Delete the alter table t2 drop column c; find non-duplicate data insert into T_ICF_HIST_D select. * 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. c_gouki = B. c_gouki and. c_kisyu = B. c_kisyu; ", rename the alter table t1 change a B integer; change the column type alter table t1 change B bigint not null; alter table infos change list tinyint not null default '0'; rename alter table t1 rename t2; multi-table query select c. nom, e. nom from consultant c, affaire a, besoin B, salarie sa, site s, entreprise e where c. consultant_id =. consultant_id and. 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 a qualified column insert into gansu_icf_hist_d select B. * from gansu_t_icf_day a, T_ICF_HIST_D B where. c_kisyu = B. c_kisyu and. c_gouki = B. c_gouki; insert into gansu_day select. * from t_icf_day a, gansu_gis_convert_result B where. d_hassei = B. d_hassei and. c_gouki = B. c_gouki; after the query, insert the insert into gansu_gis_convert_result SELECT * FROM t_gis_convert_result_icf_other where nv_place = 'gansu, China'; add data 1 insert into employee values ('123 ', 'hangsan', 'M', '2014/1/8'); 2 insert into employee values ('000000', 'lisi', 'F', '2014/1/20 '); create index 1 create table test1 (test1_id char (4), name char (20), index idx_test1 (name (10); 2 create index idx_employee on employee (name ); use create to create an index for the name column to view the index 1 show index from employee; 2 show index from products; Delete the index drop index idx_employee on employee; alter table products drop index idx_products; view the code 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 orders where nv_place = 'yunnan, china 'Union allSELECT * FROM nation 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. * 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. c_gouki = B. c_gouki and. c_kisyu = B. c_kisyu; remote database access http://hi.baidu.com/andycai/blog/item/5c8dabcc97fa931701e9281f.html  http://blog.csdn.net/uixor_/article/details/6762194

In fact, you can directly look at mysql syntax, but it is not so intuitive.

The following is a c ++ link mysql statement.

MYSQL_RES * Querysql (char * SQL) {MYSQL_RES * res; MYSQL_ROW row; char * server = "localhost";/* server name */char * user = "root "; /* username */char * password = "";/* change the password here */char * database = "EserviceDB "; /* database name */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 statement fprintf (stderr, "% s \ n ", mysql_error (conn); return res;} res = mysql_store_result (conn); // Save the query result mysql_close (conn); return res ;}

This function is mainly used to connect to the database and return the data in the format of MYSQL_RES. It is mainly used for query operations:

Void NoQuery (char * SQL) {MYSQL_RES * res; MYSQL_ROW row; char * server = "localhost";/* server name */char * user = "root "; /* username */char * password = "";/* change the password here */char * database = "EserviceDB "; /* database name */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 statement fprintf (stderr," % s \ n ", mysql_error (conn )); printf ("the query fail! ");} Else printf (" query insert SQL sucess "); mysql_close (conn );}

This function is mainly used to insert, delete, and add functions.