【Linux】 MySQL

安装：

命令如下：

$ sudo apt-get install mysql-server
$ sudo apt-get install mysql-client
$ sudo apt-get install libmysqlclient-dev

其中查看当前mysql是否开启：

$ netstat -tap | grep mysql

开启mysql：

$ mysql -u username -p 
Enter password: 

---

编码：

创建新用户：

GRANT ALL ON *.* TO **LETENO** IDENTIFIED BY 'PASSWORD';
创建用户LETENO， 密码PASSWORD，赋予的权限是：all

（all 这个应该是跟操作grant的用户有关，如果是root，则为root权限）

ON *.* 表示所有表，第一个* 应该表示数据库？抑或是创建数据库的人？(待考证,应该是前者), 
因为记得在Oracle中识别一个表需要的不仅仅是表名，还有创建者，如leteno.student_list(难不成Oracle是用创建表的用户作为表所存的数据库名（命名空间？）)）

//2015.06.07
*.*,第一个*表示数据库名。

创建数据库：

CREATE DATABASE LETENO_DB;
USE LETENO_DB
创建数据库LETENO_DB,以及切换到LETENO_DB数据库。

创建表：

CREATE TABLE student ( 
    sno int(11) NOT NULL auto_increment, 
    sname varchar(30), 
    age int(11), 
    PRIMARY KEY (sno) 
);

注意：此处的auto_increment，本人尝试过一开始直接添加学生的sno=100，接下来默认添加的学生的sno为101 102… 又再添加学生sno为1，默认添加的学生为103 104…
要想重置auto_increment:

ALTER TABLE student AUTO_INCREMENT=1;

貌似这个还要看mysql的类型，我的不能用。。。
看来还是oracle比较底层，可以操纵sequence实现increment的起始，步长等。（可能还没学到mysql内部的操作）

类型

此处仅列举标准SQL类型，目的是为了标准化，规范化。

类型	关键字	注意事项
布尔	BOOL	取值有TRUE FALSE NULL
字符	CHAR CHAR（N） VARCHAR（N）	VARCHAR是可变长数组，而CHAR得恰好有N个字符。
数值	INT DOUBLE NUMERIC(P,S)	P代表总共有多少位数字，S代表其中小数部分多少位。比double精确，效率会较慢，毕竟得检查。
时间	DATE DATETIME TIME TIMESTAMP	常见的是TIMESTAMP 存储1970 - 2037年之间的时间戳（时间这么短，原因1，用32位存储；*原因2，时间用秒计），注意使用DATE DATETIME得小心，最好先查阅手册
自增长	AUTO_INCREMENT	只能是主键的属性
空值	NULL 、 NOT NULL	色即是空空
主键	PRIMARY KEY	其物理含义是该字段是唯一的,不存在相同的

增删改查：

INSERT INTO student (sno, sname, age) VALUES (1, 'leteno', 13);
DELETE FROM student WHERE SNAME=‘TEMP’；
UPDATE student SET AGE = 18 WHERE SNO=2;
SELECT * FROM student WHERE SNAME='LETENO';

*1）增加的还有另外一种方式：
INSERT INTO student values（1, ‘leteno’, 13）;
不过不常用，它里面的属性值必须一一对应，而且不得缺漏（否则，会报：Column count doesn’t match value count）
*2）表名的大小写敏感，也就是STUDENT 会报：Table ‘LETENO_DB.STUDENT’ does not exist(.前面是数据库的名字。。。)，（*但是列名确是大小写不分，age与AGE，而且连字段值也不分大小写，leteno与LETENO）
**3）使字段值区分大小写：（对字段添加一个binary属性）

1）创建时： ... sname varchar(30) binary, ...
2）ALTER TABLE student MODIFY COLUMN sname varchar(30) BINARY；

---

进阶：

mysql命令

这些命令在shell中执行，mysql命令可以在 mysql> 中执行，这些命令在shell中都有选项-u < .username > -p <.password>(password 一般不连同指令一块出现，不安全，一般省去，在下面noecho输入)，一般当你输入下面的命令时都会被deny，没权限，得登陆 (后接-u <.username> -p)

1）myisamchk命令
与修复相关，其中isam是Indexed Sequence Access Method，因为基于索引，所以查找方便。
（仍然未知，先空着）
2）mysql 命令：
最有用的 应该是:

status  显示服务器状态信息
edit    打开默认编辑器（一般为vi），在里面编辑sql语句，wq后分号执行上述语句。
source  后接filename，执行filename里面的SQL语句。
tee     后接filename，将终端所有输出包括指令输出到filename中(这个是双向重定向，输出内容在filename，终端中都会出现)。

3）mysqladmin

create/drop <database>
status      与mysql命令的不同，此处显示的是整个mysql服务器的状态，比如queries per second
            (而mysql命令在mysql> 中是表示当前DB的状态， 在shell中表示的是一些Variables)
shutdown    停止服务器
ping        查看服务器是否关闭一般返回 mysqld is alive

4）mysqldump
这个指令是将数据库文件全部（包括数据）或部分以SQL命令集合的方式输出，指令如下：

mysqldump -u <username> -p <database name> > file.store
如 mysqldump -u root -p mysql > mysql.dump 

意外发现数据库“mysql”中有表’db‘， select全部一下，发现有我之前创立的数据库LETENO_DB，也就是说，所有数据库都将在mysql.db中保存，里面的字段包括Host, User，Db, 还有各种privilege。估计该表保存的是一个用户（User@Host）对于数据库（Db）的操作的权限（privilege）
下面是部分截图：

mysqldump命令还有一些选项：

-e  使用扩展的insert语法，能加快转储数据的加载速度 
-t  只转储表中的数据（DML？）
-d  只转储表的结构（DDL？）

注：
1）观摩了一下扩展的，应该是这么写的INSERT INTO student values （1，’ZHENG‘， 13）， （2， ’LETENO‘，11），（3，’YOUNG‘，33）；
2）发现他们添加数据（INSERT）的时候，先把表锁住 LOCK TABLES ’student‘ WRITE;在解锁UNLOCK TABLES；(此处估计应该写成UNLOCK TABLES ‘student’ 尝试了一下，不行，得只能用UNLOCK TABLES)
2.1）不过，尝试了一下lock之后在其他地方（另开终端）insert，无反应，直到我unlock之后才能添加进去。
2.2）尝试了unlock tables student read；发现“然并卵”，难不成select不属于表的读取（read）的范畴？那会是什么呢？
2.3）我又尝试在lock之后在另外一处也lock一下，发现无反应，也就是说这个是独占锁。
2.4）这个锁的粒度是进程，不是用户，当我另开终端，以同样的用户登入，发现还是操作不了，被锁上，等待解锁。

5）mysqlimport
与上面的指令是逆过程，参数是 文本文件 + 数据库名字
其实可以通过在mysql> 下：

mysql> create database NEW_DB
mysql> source mysql.dump.file

6） mysqlshow
一般需要用户登入（-u username -p），作用是：快速查看mysql的详细信息，如里面的数据库等等
直接上图：(我这里的用户proxy是咩有密码的)

参数	结果
无参	列出所有可用的数据库
一个数据库为参数	列出该数据库中的表
数据库和表名为参数	列出表中的列
数据库、表和列为参数	列出指定列的详细信息

---

其他命令：

1) grant 命令
详情看 编码 > 创建新用户

2）revoke 命令
revoke <privilege> on <tables> from <user>
如REVOKE INSERT ON LETENO_DB.* FROM proxy;
注: revoke 只能撤回用户的权利，要完全删除用户，你得在mysql.user表中删除相应的用户记录。

mysql> DELETE FROM mysql.user WHERE user = "proxy"
mysql> FLUSH PRIVILEGES

单纯使用上面俩语句，是无法完全清除proxy用户的痕迹的。（当然，proxy无法再次登陆）但是对于数据库表的操作权限还没清除干净，可以通过select * from mysql.db where User="proxy"发现，仍然有proxy的痕迹，而且此时调用revoke撤proxy的权，发现报Error 1141：There is no such grant defined for user 'proxy' on host '%';估计是查找user表时发现查无此人（其实user表也是有权限priv字段的）；所以说删除用户时要有一定顺序，先撤权，再删人。否则会删不干净的。
其实也未必，不能使用revoke，就使用最古老最简单的方法：delete
delete from mysql.db where user='proxy'
这样就删干净了。。。。

3）密码相关：
修改用户密码可以通过直接修改mysql.user中对应的字段达到目的，只不过一般密码都是加密过的，直接设置有些欠妥。所以mysql提供password(<password>)方法对密码加密，范例如下：

UPDATE mysql.user SET password=password("newpassword") WHERE user="proxy";

---

C语言访问MySQL数据库

1）编译

gcc -o a.out connect1.c -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient

2）编程
初始化：MYSQL* mysql_init(MYSQL *)
连接：MYSQL*

mysql_real_connect( MYSQL *connection, 
                    const char *server_host, 
                    const char *sql_user_name, 
                    const char *sql_password, 
                    const char *db_name, 
                    unsigned int port_number,
                    const char *unix_socket_name,
                    unsigned int flags);

关闭连接
mysql_close(MYSQL *connection);

例程：

#include <stdlib.h>
#include <stdio.h>

#include "mysql.h"

int main(int argc, char *argv[]) 
{
  MYSQL *conn_ptr;

  conn_ptr = mysql_init(NULL);
  if (!conn_ptr) 
  {
    fprintf(stderr, "mysql_init failed\n");
    return EXIT_FAILURE;
  }

  conn_ptr = mysql_real_connect(conn_ptr,
                                "localhost",
                                "proxy",
                                NULL,
                                "LETENO_DB",
                                0,
                                NULL,
                                0);

  if (conn_ptr)
  {
    printf("Connection success\n");
  } else 
  {
    printf("Connection failed\n");
  }

  mysql_close(conn_ptr);

  return EXIT_SUCCESS;  
}

错误处理：
unsigned int mysql_errno(MYSQL *connection)
char *mysql_error(MYSQL *connection)

执行mysql语句
int mysql_query(MYSQL *connection, const char *query)

受影响行数：
my_ulonglong mysql_affected_rows(MYSQL *connection)

获得resultset：
MYSQL_RES * mysql_store_result(MYSQL*);
一般用于mysql_query之后

取得resultset的一行结果，相当于resultset.next()
MYSQL_ROW mysql_fetch_row(MYSQL_RES)
其中MYSQL_ROW 是一个数组，数组的元素对应着查询的每个结果，也就是MYSQL_ROW[i] == mysqlrow.getString(i);
至于数组的大小：
int mysql_field_count(MYSQL*);

还能获得结果集的每个字段的属性：
MYSQL_FIELD * mysql_fetch_field(MYSQL_RES*)
其中 MYSQL_FIELD {
char *name;
char *table;
int type;
int length;
int flags; (列定义的标志，例如自增长AUTO_INCREMENT_FLAG 区分大小写BINARY_FLAG)
}
例子程序：

#include <stdlib.h>
#include <stdio.h>

#include "mysql.h"

MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;

void display_header();
void display_row();


int main(int argc, char *argv[]) {
  int res;
  int first_row = 1;


  mysql_init(&my_connection);
  if (mysql_real_connect(&my_connection,
                         "localhost",
                         "proxy",
                         NULL,
                         "LETENO_DB",
                         0,
                         NULL,
                         0)) {

    printf("Connection success\n");

    res = mysql_query(&my_connection,
                      "SELECT sno, sname, age FROM student WHERE age>5");

    if (res) {
      printf("SELECT error: %s\n", mysql_error(&my_connection));
    } else {
      res_ptr = mysql_store_result (&my_connection);
      if (res_ptr) {

        while((sqlrow = mysql_fetch_row(res_ptr))) {
          if (first_row) {
            display_header();
            first_row = 0;
          }
          display_row();
        }
      }

      if (mysql_errno(&my_connection)) {
          fprintf(stderr, "Retrive error: %s\n", mysql_error(&my_connection));
      }
       mysql_free_result(res_ptr);
  }

    mysql_close(&my_connection);
} else {
      fprintf(stderr, "Connection failed\n");
      if (mysql_errno(&my_connection)) {
        fprintf(stderr, "Connection error %d: %s\n",
                mysql_errno(&my_connection), mysql_error(&my_connection));
      }
 }
    return EXIT_SUCCESS;
}


void display_header() {

  MYSQL_FIELD *field_ptr;

  printf("Column details:\n");
  while ((field_ptr = mysql_fetch_field(res_ptr)) != NULL) {
    printf("\t Name: %s\n", field_ptr->name);
    printf("\t Type: ");
    if (IS_NUM(field_ptr->type)) {
      printf("Numeric field\n");
    } else {
      switch(field_ptr->type) {
      case FIELD_TYPE_VAR_STRING:
        printf("VARCHAR\n");
      break;
      case FIELD_TYPE_LONG:
        printf("LONG\n");
      break;
      default:
        printf("Type is %d, check in mysql_com.h", field_ptr->type);
      }
    }

    printf("\t Max width %ld\n", field_ptr->length);
    if (field_ptr->flags & AUTO_INCREMENT_FLAG)
      printf("\t Auto increments\n");
    printf("\n");
  }
}



void display_row(){
  unsigned int field_count;

  field_count = 0;

  while (field_count < mysql_field_count(&my_connection)) {
    printf("%s ", sqlrow[field_count]);
    field_count ++;
  }
  printf("\n");
}

---

疑难杂症：

Table ‘LETENO_DB.STUDENT’ does not exist：

mysql的表都有“命名空间”，也就是数据库名。比如你这个表是放置在DB数据库下：
方法1） use DB
方法2） 使用里面的表（比如student）时， 写上全称DB.student