数据库:CentOS使用MySQL的详细步骤，及C API编程

1.1 执行yum命令安装MySQL

yum -y install mysql mysql-server

1.2 把添加MySQL进开机启动项，并立即启动MySQL

chkconfig --levels 235 mysqld on/etc/init.d/mysqld start

1.3 打开mysqld

service mysqld start

1.4 设置MySQL root帐号密码 （如果出了问题，比如Mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost'，请参考1.5）
mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQLSERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): <-- 输入系统root密码
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] <-- ENTER
New password: <-- 你的MySQL root密码
Re-enter new password: <-- 你的MySQL root密码
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for

1. This is intended only for testing, and to make the installation

go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] <-- ENTER
... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] <-- ENTER
... Success!
By default, MySQL comes with a database named 'test' that anyone can

1. This is also intended only for testing, and should be removed

before moving into a production environment.
Remove test database and access to it? [Y/n] <-- ENTER
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] <-- ENTER
... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

1.5 Mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost'问题的解决
这种问题需要强行重新修改密码，方法如下：

/etc/init.d/mysql stop   (service mysqld stop )
/usr/bin/mysqld_safe --skip-grant-tables
另外开个SSH连接
[root@localhost ~]# mysql
mysql>use mysql
mysql>update user set password=password("123456") where user="root";
mysql>flush privileges;
mysql>exit
pkill -KILL -t pts/0 可将pts为0的**用户(之前运行mysqld_safe的用户窗口)强制踢出

正常启动 MySQL：/etc/init.d/mysql start   (service mysqld start)

1.6 通过命令行操作数据库

1.6.1 连接

mysql -u用户名 -p密码

1.6.2 常用命令

创建一个数据库：

mysql> create database [databasename];

列出所有数据库：

mysql> show databases;

切换到一个数据库：

mysql> use [db name];

显示一个数据库的所有表：

mysql> show tables;

查看数据表的字段格式：

mysql> describe [table name];

删除一个数据库：

mysql> drop database [database name];

删除一个数据表：

mysql> drop table [table name];

显示一个数据表的所有数据：

mysql> SELECT * FROM [table name];

返回指定数据表的各列信息：

mysql> show columns from [table name];

使用值“whatever”过滤显示选定的某些行：

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

显示所有包含name为”Bob”和phone number为“3444444”的记录：

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

显示所有不包含name为”Bob”和phone number为“3444444”的记录，并以phone_number字段排序：

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

显示所有的name以字母“bob”开头和phone number为“3444444”的记录：

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

显示name以字母“bob”开头和phone number为“3444444”的第1至第5条记录：

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

使用正则表达式查找记录：使用“正则表达式二进制”强制区分大小写：此命令查找以a开头的任何记录：

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

返回唯一不同的记录：

mysql> SELECT DISTINCT [column name] FROM [table name];

以升序或降序显示选定的记录：

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

返回行数：

mysql> SELECT COUNT(*) FROM [table name];

统计指定列值的总和：

mysql> SELECT SUM(*) FROM [table name];

联结表：

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

新建一个用户：以root登录：切换到mysql数据库，创建用户，刷新权限：

# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));

mysql> flush privileges;

从unix命令行更改用户密码：

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

从mysql命令行更改用户密码：以root登录，设置密码，更新权限：

# /etc/init.d/mysql stop

# mysqld_safe --skip-grant-tables &

# mysql -u root

mysql> use mysql;

mysql> update user set password=PASSWORD("newrootpassword") where User='root';

mysql> flush privileges;

mysql> quit

# /etc/init.d/mysql stop

# /etc/init.d/mysql start

root密码为空时，设置root密码：

# mysqladmin -u root password newpassword

更新root密码：

# mysqladmin -u root -p oldpassword newpassword

允许用户“bob”从localhost以密码“passwd”连接服务器：以root登录，切换mysql数据库：设置权限，更新权限：

# mysql -u root -p

mysql> use mysql;

mysql> grant usage on *.* to bob@localhost identified by 'passwd';

mysql> flush privileges;

为数据库db设置权限：以root登录，切换到mysql数据库，授予权限，更新权限：

# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

mysql> flush privileges;

或者

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges;

更新已存在表的数据：

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

删除表中[field name] = ‘whatever’的行：

mysql> DELETE from [table name] where [field name] = 'whatever';

更新数据库的权限/特权：

mysql> flush privileges;

删除列：

mysql> alter table [table name] drop column [column name];

新增列到db：

mysql> alter table [table name] add column [new column name] varchar (20);

更改列名：

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

增加唯一的列：

mysql> alter table [table name] add unique ([column name]);

设置列值大点：

mysql> alter table [table name] modify [column name] VARCHAR(3);

删除唯一列：

mysql> alter table [table name] drop index [colmn name];

导入一个CSV文件到表：

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

导出所有数据库到sql文件：

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

导出一个数据库：

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

从一个数据库导出一个表：

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

从sql文件还原数据库（数据表）：

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

创建数据表例1：

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

创建数据表例2：

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

1.7 安装C API库及头文件

yum install mysql-devel

1.8 例子

/* testsql.c

  ** An example to use MYSQL C API

  ** Copyright 2004 Coon Xu.

  ** Author: Coon Xu

  ** Date: 05 Nov 2004

  */

   

  #include <mysql.h>

  #include <stdio.h>

int main(){

   MYSQL mysql;     // need a instance to init

   MYSQL_RES *res;

   MYSQL_ROW row;

   char *query;

   int t,r;

 // connect the database 

   mysql_init(&mysql);

   if (!mysql_real_connect(&mysql,"localhost", "mmim", "mmim", "test",0,NULL,0))

   {

       printf( "Error connecting to database: %s/n",mysql_error(&mysql));

   }

   else printf("Connected.../n");

 // get the result from the executing select query

 query = "select * from t1";

   

 t = mysql_real_query(&mysql,query,(unsigned int) strlen(query));

 if (t)

 {

    printf("Error making query: %s/n",

      mysql_error(&mysql));

 }

 else printf("[%s] made.../n", query); 

 res = mysql_store_result(&mysql); 

 while(row = mysql_fetch_row(res))

 {

  for(t=0;t<mysql_num_fields(res);t++)

  {

   printf("%s ",row[t]);

  }

  printf("/n");

 }

 

 printf("mysql_free_result.../n");

 mysql_free_result(res);     //free result after you get the result

 

 sleep(1);    

 

 // execute the insert query

 query = "insert into t1(id, name) values(3, 'kunp')";

 t = mysql_real_query(&mysql,query,(unsigned int) strlen(query));

 if (t)

 {

    printf("Error making query: %s/n",

      mysql_error(&mysql));

 }

 else printf("[%s] made.../n", query); 

   

    mysql_close(&mysql);

   

   return 0;

}

1.9 编译

gcc testmysql.c -I/usr/include/mysql -L/usr/lib64/mysql -lmysqlclient

1.10 具体执行结果不确定，因为我没仔细看代码，这个例子主要是用来证明我按照好了mysql，并且可以使用c api操作