1 安装mysql数据库和C/C++ API
#sudo apt-get install mysql-server mysql-client libmysql++-dev
2 将lib文件拷到公用文件夹
#sudo cp -R /usr/lib/mysql/* /usr/lib/
3 mysql指令键入:
#mysql -u root -p
按提示输入密码后进入mysql指令键入状态,如:mysql>
4 创建数据库
mysql>CREATE DATABASE URL
mysql>use URL
5 创建数据表
mysql>CREATE TABLE url_list(id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, url varchar(128));
6 相关操作
mysql> insert into url_list (url) values ( '10.16.1.186:8080');
Query OK, 1 row affected (0.22 sec)
mysql> SELECT id, url FROM url_list;
+----+------------------+
| id | url |
+----+------------------+
| 1 | 10.16.1.186:8080 |
+----+------------------+
1 row in set (0.00 sec)
mysql> delete from url_list where url='10.16.1.186:8080';
Query OK, 1 row affected (0.06 sec)
mysql> SELECT id, url FROM url_list;
Empty set (0.00 sec)
mysql>
7 删除表
mysql>drop TABLE url_list;
8 检索表中多次出现的记录
mysql> SELECT id, url FROM url_list;
+----+-------------+
| id | url |
+----+-------------+
| 1 | 10.16.1.186 |
| 2 | 10.16.1.186 |
| 3 | 10.16.1.187 |
| 4 | 10.16.1.187 |
| 5 | 10.16.1.188 |
| 6 | 10.16.1.189 |
+----+-------------+
6 rows in set (0.00 sec)
mysql> select * from url_list where (url_list.url) in (select url from url_list group by url having count(url) > 1);
+----+-------------+
| id | url |
+----+-------------+
| 1 | 10.16.1.186 |
| 2 | 10.16.1.186 |
| 3 | 10.16.1.187 |
| 4 | 10.16.1.187 |
+----+-------------+
4 rows in set (0.00 sec)
9,插入多个字段的记录
insert into url_list (url, port) values ( '10.16.1.189', '8084');
10,查找一个字段重复出现另一字段不重复
mysql> select * from url_list;
+----+-------------+------+
| id | url | port |
+----+-------------+------+
| 1 | 10.16.1.186 | 8080 |
| 2 | 10.16.1.186 | 8081 |
| 3 | 10.16.1.187 | 8082 |
| 4 | 10.16.1.187 | 8082 |
| 5 | 10.16.1.188 | 8083 |
| 6 | 10.16.1.189 | 8084 |
| 7 | 10.16.1.187 | 8085 |
+----+-------------+------+
7 rows in set (0.00 sec)
mysql> select * from url_list where (url_list.url) in (select url from url_list group by url having count(distinct port) > 1);
+----+-------------+------+
| id | url | port |
+----+-------------+------+
| 1 | 10.16.1.186 | 8080 |
| 2 | 10.16.1.186 | 8081 |
| 3 | 10.16.1.187 | 8082 |
| 4 | 10.16.1.187 | 8082 |
| 7 | 10.16.1.187 | 8085 |
+----+-------------+------+
5 rows in set (0.00 sec)
mysql> select * from url_list where (url_list.url) in (select url from url_list group by url having count(distinct port) > 1) group by url, port;
+----+-------------+------+
| id | url | port |
+----+-------------+------+
| 1 | 10.16.1.186 | 8080 |
| 2 | 10.16.1.186 | 8081 |
| 3 | 10.16.1.187 | 8082 |
| 7 | 10.16.1.187 | 8085 |
+----+-------------+------+
4 rows in set (0.00 sec)
贻笑大方了。。。。。。
二、新建表时判断表是否已经存在
from: http://blog.sina.com.cn/s/blog_5e2613830100bvsw.html
2. select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='dbname'
3. 如果表不存在就建立这个表,那么可以直接用
4. 从模板表创建表:create table if not exists like old_table_name;