数据库上手避坑之--从特定的表删除符合条件的数据

代码sqlDeleteData.c

 1 /* *=+=+=+=+* *** *=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=            
 2   * 作者代号: *** :guochaoxxl
 3   * 版权声明: *** :(魎魍魅魑)GPL3
 4   * 联络信箱: *** :guochaoxxl@163.com
 5   * 文档用途: *** :深入理解C指针
 6   * 文档信息: *** :~/WORKM/sqlInserData.c
 7   * 修订时间: *** :2020年第47周 11月23日 星期一 下午09:13 (328天)
 8   * 代码说明: *** :自行添加
 9  * *+=+=+=+=* *** *+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+*/
10 #include<stdio.h>
11 #include<mysql/mysql.h>
12 
13 #define USER "gsc"
14 #define PWD "××××××"
15 #define DATABASENAME "student_info"
16 #define TABLENAME "stu_info"
17 
18 typedef struct _stu
19 {
20     int stuId;
21     char stuName[40];
22     char gender[10];
23     int phone;
24     char email[40];
25     char account[40];
26     char code[40];
27     int adminId;
28 } Stu;
29 
30 int main(int argc, char **argv)
31 {
32     //定义句柄
33     MYSQL conn;
34 
35     //初始化句柄
36     if(NULL == mysql_init(&conn))
37     {
38         printf("mysql init error!\n");
39         return -1;
40     }
41 
42     if(NULL == mysql_real_connect(&conn,"localhost",USER, PWD,  DATABASENAME,0,NULL,0))
43     {
44         printf("%s\n", mysql_error(&conn));
45         return -1;
46     }
47 
48     printf("连接数据库成功！\n");
49 
50     //设置字符集
51     mysql_set_character_set(&conn,"utf8");
52     char sql[512];
53     Stu s1={102,"张四","女",13307922222,"zhangsi@163.com","zhangsi","110077", 902};
54     sprintf(sql, "insert into stu_info values(%d,'%s','%s',%d,'%s','%s','%s',%d)",s1.stuId,s1.st   uName,s1.gender,s1.phone,s1.email,s1.account,s1.code,s1.adminId);
55 
56     if(0 != mysql_query(&conn,sql))
57     {
58         printf("%s\n",mysql_error(&conn));
59         return -1;
60     }
61     printf("数据插入成功！\n");
62 
63     sprintf(sql, "delete from stu_info where stuId=101 and stuName=\"董于\"");
64     if(0 != mysql_query(&conn,sql))
65     {
66         printf("%s\n",mysql_error(&conn));
67         return -1;
68     }
69     printf("数据删除成功！\n");
70 
71     mysql_close(&conn);
72 
73     return 0;
74 }

　　Makefile文件：

sqlDeleteData:sqlDeleteData.c
     gcc -Wall -g -I/usr/include/mariadb -o sqlDeleteData sqlDeleteData.c -lmysqlclient

　　执行结果：

sqlDeleteData 
连接数据库成功！
数据插入成功！
数据删除成功！

　　数据库查询结果：

 mysql -u sqlxxl -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 10.5.8-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> ls
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ls' at line 1
MariaDB [(none)]> use student_info
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [student_info]> select * from stu_info;　　　　　　　　//有三条数据，两条是stuName="董于"
+-------+---------+--------+-----------+------------------+----------+--------+---------+
| stuId | stuName | gender | phone     | email            | account  | code   | adminId |
+-------+---------+--------+-----------+------------------+----------+--------+---------+
|   101 | 董于    | 女     | 423019223 | dongyu@163.com   | dongyu   | 110066 |     901 |
|   101 | 董于    | 女     | 423019223 | dongyu@163.com   | dongyu   | 110066 |     901 |
|   101 | 张三    | 女     | 423019223 | zhangsan@163.com | zhangsan | 110066 |     901 |
+-------+---------+--------+-----------+------------------+----------+--------+---------+
3 rows in set (0.001 sec)

MariaDB [student_info]> select * from stu_info;　　　　　　//删除了stuName="董于"，增加了一条stuName="张四"的数据
+-------+---------+--------+-----------+------------------+----------+--------+---------+
| stuId | stuName | gender | phone     | email            | account  | code   | adminId |
+-------+---------+--------+-----------+------------------+----------+--------+---------+
|   101 | 张三    | 女     | 423019223 | zhangsan@163.com | zhangsan | 110066 |     901 |
|   102 | 张四    | 女     | 423020334 | zhangsi@163.com  | zhangsi  | 110077 |     902 |
+-------+---------+--------+-----------+------------------+----------+--------+---------+
2 rows in set (0.001 sec)