(1)用c程式碼(t2)建立DATABASE ilvssell (可支援中文字欄位內容)
if (mysql_query(con, "CREATE DATABASE ilvssell default character set utf8")) { fprintf(stderr, "%s\n", mysql_error(con)); mysql_close(con); exit(1); }
(2)用mysql指令建立ilvssell的連線用戶treehrt
treehrt@treehrt-fire:~/mysqlc$ mysql -p -uroot Enter password: : mysql> CREATE USER treehrt@localhost IDENTIFIED BY 'abc123abc123'; Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON ilvssell.* to treehrt@localhost; Query OK, 0 rows affected (0.00 sec)
mysql> quit Bye
(3)用c程式碼(t3)建立第一個資料表 new_cust 顧客基本資料表
if (mysql_query(con, "DROP TABLE IF EXISTS new_cust")) { fprintf(stderr, "%s\n", mysql_error(con)); mysql_close(con); exit(1); } printf("DROP TABLE new_cust ok!\n");
char st[]="CREATE TABLE new_cust (uid char(10) default 'x', remain float default 0, inyear char(5) default '2018', dept char(10) default 'x', grade char(10) default '1' , class char(10) default 'x', cardid char(10) primary key, uname char(10) default 'x', pid char(10) default 'x', addr varchar(80) default 'x', tel char(15) default 'x', oldclass char(10) default 'x', sno char(2) default 'x', lostc int default 0 , gen_code char(1) default '0' , in_use char(1) default '1', cid4 char(4) default 'x' )" ;
if (mysql_query(con, st)) { fprintf(stderr, "%s\n", mysql_error(con)); mysql_close(con); exit(1); } printf("Execute ok! %s\n", st);
(4)用mysql指令顯示新建立ilvssell.new_cust資料表的欄位結構
mysql> desc ilvssell.new_cust; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | uid | char(10) | YES | | x | | | remain | float | YES | | 0 | | | inyear | char(5) | YES | | 2018 | | | dept | char(10) | YES | | x | | | grade | char(10) | YES | | 1 | | | class | char(10) | YES | | x | | | cardid | char(10) | NO | PRI | NULL | | | uname | char(10) | YES | | x | | | pid | char(10) | YES | | x | | | addr | varchar(80) | YES | | x | | | tel | char(15) | YES | | x | | | oldclass | char(10) | YES | | x | | | sno | char(2) | YES | | x | | | lostc | int(11) | YES | | 0 | | | gen_code | char(1) | YES | | 0 | | | in_use | char(1) | YES | | 1 | | | cid4 | char(4) | YES | | x | | +----------+-------------+------+-----+---------+-------+ 17 rows in set (0.00 sec)
mysql>
mysql> select column_name from information_schema.columns where table_name='new_cust'; +-------------+ | column_name | +-------------+ | uid | | remain | | inyear | | dept | | grade | | class | | cardid | | uname | | pid | | addr | | tel | | oldclass | | sno | | lostc | | gen_code | | in_use | | cid4 | +-------------+ 17 rows in set (0.00 sec)
mysql>
(5)用c程式碼(t4)建立為第一個資料表 new_cust 顧客基本資料表新增一筆資料
mysql_query(con, "set names utf8");
char st[]="insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,712003,'王一志' ,'B301',1000)" ;
if (mysql_query(con, st)) { fprintf(stderr, "%s\n", mysql_error(con)); mysql_close(con); exit(1); }
(6)用mysql指令顯示新加入ilvssell.new_cust資料表的所有紀錄
mysql> use ilvssell; mysql> insert into new_cust (inyear,cardid,uname,cid4,remain) values (105,312005,'蕃茄' ,'B301',1000);
mysql> select inyear,cardid,uname,cid4,remain from ilvssell.new_cust; +--------+--------+-----------------------+------+--------+ | inyear | cardid | uname | cid4 | remain | +--------+--------+-----------------------+------+--------+ | 105 | 312005 | 蕃茄 | B301 | 1000 | | 107 | 812001 | 王一志 | B301 | 1000 | +--------+--------+-----------------------+------+--------+ 2 rows in set (0.00 sec)
mysql>
|