qt-mysql-4.6.2-25.el6.x86_64

mysql-connector-odbc-5.1.5r1144-7.el6.x86_64

mysql-bench-5.1.66-2.el6_3.x86_64

mysql-devel-5.1.66-2.el6_3.x86_64

libdbi-dbd-mysql-0.8.3-5.1.el6.x86_64

mysql-test-5.1.66-2.el6_3.x86_64

mysql-libs-5.1.66-2.el6_3.x86_64

mysql-5.1.66-2.el6_3.x86_64

mysql-connector-java-5.1.17-6.el6.noarch

mysql-server-5.1.66-2.el6_3.x86_64

[root@localhost home]# rpm -e mysql-connector-odbc-3.51.26r1127-1.el5 --nodeps --allmatches
    
[root@localhost home]# rpm -e libdbi-dbd-mysql-0.8.1a-1.2.2 --nodeps --allmatches
    
[root@localhost home]# rpm -e mysql-server-5.0.77-3.el5 --nodeps --allmatches
    
[root@localhost home]# rpm -aq | grep mysql
    
[root@localhost home]# 

rm -f /etc/my.cnf
    
rm -rf /var/lib/mysql
    
rm -rf /var/share/mysql
    
rm -rf /usr/bin/mysql*

unzip V46610-01-MySQL Database 5.6.20 RPM for Oracle Linux RHEL 6 x86 (64bit).zip

MySQL-client-advanced-5.6.20-1.el6.x86_64.rpm
  
MySQL-devil-advanced-5.6.20-1.el6.x86_64.rpm
    
MySQL-server-advanced-5.6.20-1.el6.x86_64.rpm
    
MySQL-embedded-advanced-5.6.20-1.el6.x86_64.rpm
  
MySQL-shared-advanced-5.6.20-1.el6.x86_64.rpm
    
MySQL-shared-compal-advanced-5.6.20-1.el6.x86_64.rpm
   
MySQL-test-advanced-5.6.20-1.el6.x86_64.rpm
    
README.txt

rpm -ivh MySQL-server-advanced-5.6.****-1.el6.x86_64.rpm
    
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
    
You will find that password in '/root/.mysql_secret'.
    
You must change that password on your first connect,
    
no other statement but 'SET PASSWORD' will be accepted.
    
See the manual for the semantics of the 'password expired' flag.
    
Also, the account for the anonymous user has been removed.
    
In addition, you can run:
    
    /usr/bin/mysql_secure_installation
    
which will also give you the option of removing the test database.
    
This is strongly recommended for production servers.
    
See the manual for more instructions.
    
Please report any problems at http://bugs.mysql.com/
    
The latest information about MySQL is available on the web at
    
http://www.mysql.com
    
Support MySQL by buying support/licenses at http://shop.mysql.com
    
New default config file was created as /usr/my.cnf and
    
will be used by default by the server when you start it.
    
You may edit this file to change server settings

rpm -ivh MySQL-client-advanced-5.6.****-1.el6.x86_64.rpm

# 查看状态
systemctl status mysqld 
# 启动mysql
systemctl start mysqld 
# 关闭mysql
systemctl stop mysqld 
# 重新启动mysql
systemctl restart mysqld 
# 开机启动mysql
systemctl enable mysqld 
# 关闭开机启动mysql
systemctl disable mysqld
# 另外一种操作方式
service mysqld status 

cat /root/.mysql_secret

service mysql start

service mysql stop

PID TTY     TIME CMD

7586 ?         00:00:50 mysqld     守护进程

#先创建一个用户
create user 'tone'@'%' identified by '123123';
 
#再进行授权
grant all privileges on *.* to 'tone'@'%' with grant option;

mysql -u root -p
#然后输入您的密码

use mysql;
--因为 mysql 数据库中存储了用户信息的 user 表。   

select host, user, authentication_string, plugin from user; 
--执行完上面的命令后会显示一个表格
--查看表格中 root 用户的 host，默认应该显示的 localhost，只支持本地访问，不允许远程访问。

GRANT ALL ON *.* TO 'root'@'%';
GRANT ALL ON 表示所有权限，% 表示通配所有 host，可以访问远程。
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你自己的密码';

--所有操作后，应执行    
flush privileges;

    --输入访问的 host 和密码，报 2059 错误，这是因为 MySql8.0 版本 和 5.7 的加密规则不一样，而现在的可视化工具只支持旧的加密方式。
    
    --出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password.
    --解决问题方法有两种,一种是升级navicat驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password.
    
    --这里采用第二种方式 ：
        
        --修改加密规则:
        ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
        password 为你当前密码。
    
        --更新 root 用户密码:
        ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; 
        password 为你新设置的密码。
    
        --刷新权限:
        FLUSH PRIVILEGES; 
        --OK，设置完成，再次使用 Navicat 连接数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itcast             |
| itcast1            |
| itcast2            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.01 sec)

mysql> show create database itcast;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                              |
+----------+-----------------------------------------------------------------+
| itcast   | CREATE DATABASE `itcast` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
  
/* `itcast` 两端是反单引号, esc下的键, 使用``包含数据库的名字是区分大小写的 */

/* 我们以root用户登录, 用户下有很多数据库, 需要指定, 当前使用哪一个数据库, 操作的就是那个数据库中的数据 */
mysql> use itcast1;
Database changed
mysql> use itcast2;
Database changed

mysql> create database itheima;                          /* utf8 */

mysql> create database itheima character set latin1; 

-- 修改字符集, 不能改名
alter database itcast character set utf8;

drop database itcast;

drop table 表名;
-- oracle中可以这么做, mysql中不行
drop table 表名 purge

    -- 当前时间: now() == sysdate()
    mysql> select sysdate() from dual;
    +---------------------+
    | sysdate()           |
    +---------------------+
    | 2019-12-02 09:03:42 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select now() from dual;
    +---------------------+
    | now()               |
    +---------------------+
    | 2019-12-02 09:04:35 |
    +---------------------+
    1 row in set (0.00 sec)
    -- addtime(time, s) 给当前实际, 添加对应的秒数
    select addtime('09:04:35',20) from dual; 
    +------------------------+
    | addtime('09:04:35',20) |
    +------------------------+
    | 09:04:55               |
    +------------------------+
    
    -- 当前日期: 年, 月, 日
    mysql> select current_date() from dual;
    +----------------+
    | current_date() |
    +----------------+
    | 2019-12-02     |
    +----------------+
    1 row in set (0.00 sec)
    
    -- 当前时间: 时, 分, 秒
    mysql> select current_time() from dual;    
    +----------------+
    | current_time() |
    +----------------+
    | 09:08:10       |
    +----------------+
    1 row in set (0.00 sec)
    
    -- 时间戳
    mysql> select current_timestamp() from dual;    
    +---------------------+
    | current_timestamp() |
    +---------------------+
    | 2019-12-02 09:09:47 |
    +---------------------+
    1 row in set (0.00 sec)
    
    -- 将给定时间的日期取出
    select date(now()) from dual;                 
    +-------------+
    | date(now()) |
    +-------------+
    | 2019-12-02  |
    +-------------+
    1 row in set (0.00 sec)
    
    -- date_add(), 第二个参数的type: 天: day, 月:month, 年: year
    mysql> select date_add(now(), interval 1 day) from dual;               
    +---------------------------------+
    | date_add(now(), interval 1 day) |
    +---------------------------------+
    | 2019-12-03 09:14:57             |
    +---------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select date_add(now(), interval 1 month) from dual;   
    +-----------------------------------+
    | date_add(now(), interval 1 month) |
    +-----------------------------------+
    | 2020-01-02 09:15:27               |
    +-----------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select date_add(now(), interval 1 year) from dual;     
    +----------------------------------+
    | date_add(now(), interval 1 year) |
    +----------------------------------+
    | 2020-12-02 09:15:37              |
    +----------------------------------+
    1 row in set (0.00 sec)
    
    -- 计算两个日期的差值
    mysql> select datediff('2019-12-01 09:18:06', '2019-1-02 09:18:06'); 
    +-------------------------------------------------------+
    | datediff('2019-12-01 09:18:06', '2019-1-02 09:18:06') |
    +-------------------------------------------------------+
    |                                                   333 |
    +-------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select datediff('2018-12-01 09:18:06', '2019-1-02 09:18:06'); 
    +-------------------------------------------------------+
    | datediff('2018-12-01 09:18:06', '2019-1-02 09:18:06') |
    +-------------------------------------------------------+
    |                                                   -32 |
    +-------------------------------------------------------+
    1 row in set (0.00 sec)
    -- 单独显示当前年, 月, 日
    select year(now()) "年", month(now()) "月", day(now()) "日" from dual;
    -- 单独显示当前日期, 时间, 时间戳
    -- 显示昨天, 今天, 明天
    select  date_sub(now(), interval 1 day), now(), date_add(now(), interval 1 day) from dual;

-- 字符串连接
    -- mydql中可以连接多个字符串, oracle中只能连接两个字符串
    mysql> select concat("hello", " world", " 你好,", "世界") from dual;
    +-------------------------------------------------+
    | concat("hello", " world", " 你好,", "世界")     |
    +-------------------------------------------------+
    | hello world 你好,世界                           |
    +-------------------------------------------------+
    
-- 大小写转换
    mysql> select ucase("hello"), lcase("WORLD") from dual;
    +----------------+----------------+
    | ucase("hello") | lcase("WORLD") |
    +----------------+----------------+
    | HELLO          | world          |
    +----------------+----------------+
    1 row in set (0.00 sec)
    
-- 字符串截取, 从左侧开始
    mysql> select left("hello,world", 5) from dual;                      
    +------------------------+
    | left("hello,world", 5) |
    +------------------------+
    | hello                  |
    +------------------------+
    1 row in set (0.00 sec)
    
    
-- 字符串比较
    mysql> select strcmp("abc", "bca") from dual;
    +----------------------+
    | strcmp("abc", "bca") |
    +----------------------+
    |                   -1 |  -- 小于
    +----------------------+
    1 row in set (0.00 sec)
    
    mysql> select strcmp("aaa", "aa") from dual;     
    +---------------------+
    | strcmp("aaa", "aa") |
    +---------------------+
    |                   1 | -- 大于
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select strcmp("aaa", "aaa") from dual;
    +----------------------+
    | strcmp("aaa", "aaa") |
    +----------------------+
    |                    0 | -- 等于
    +----------------------+
    1 row in set (0.00 sec)

    -- 进制转换
    mysql> select conv(100, 10, 16) from dual; 
    +-------------------+
    | conv(100, 10, 16) |
    +-------------------+
    | 64                |
    +-------------------+
    
    -- 保留指定的小数位数, 可以四舍五入
    mysql> select format(12.345, 2) from dual;
    +-------------------+
    | format(12.345, 2) |
    +-------------------+
    | 12.35             |
    +-------------------+
    1 row in set (0.00 sec)
    
    -- 取集合中的最小值
    mysql> select least(12, 1, 3, 4, 0) from dual;
    +-----------------------+
    | least(12, 1, 3, 4, 0) |
    +-----------------------+
    |                     0 |
    +-----------------------+
    1 row in set (0.00 sec)
    
    -- 生成随机数
    -- 生成的随机数范围: 0-1
    mysql> select rand(now()) from dual;
    +--------------------+
    | rand(now())        |
    +--------------------+
    | 0.8633374812671333 |
    +--------------------+
    1 row in set (0.00 sec)

    -- oracle 日期格式: yyyy-mm-dd hh24:mi:ss 不区分大小写
    to_char()  日期转字符串
    to_date()  字符串转日期
    
    -- mysql 日期格式: %Y-%m-%d %H:%i:%s    区分大小写
    date_format() 日期转字符串
    str_to_date() 字符串转日期

    -- 分组函数
    count(); -- 求总数
    avg();     -- 求平均值
    sum();       -- 求和
    min();       -- 最小值
    max();     -- 最大值
    
    -- having 必须出现在group by 后边, 不能写where
    -- where 写在 from 的后边
    -- order by: 默认升序 asc, 降序 desc
    select 列名|表达式 as "别名" from 表名 "表的别名" where 表达式 group by 列名 having 条件 order by asc | desc
    -- 求两个表的笛卡尔积 cross join
    -- 最简单的两个表查询
    select * from dept, emp;
    select * from dept cross join emp;

-- mysql+oracle
select d.deptno, d.dname, e.empno, e.ename from dept d, emp e where d.deptno=e.deptno;
    
-- mysql+oracle
-- 将表之间的逗号替换为 inner join --> 简写为 join
-- 将 where 替换为 on
select d.deptno, d.dname, e.empno, e.ename from dept d  join emp e on d.deptno=e.deptno;

  -- oracle
  select d.deptno, d.dname, count(e.empno) from dept d, emp e where d.deptno=e.deptno(+) group by d.deptno, d.dname;
    
    -- mysql + oracle
    -- 表之间的逗号替换为:   left outer join -> outer 可以省略 -> left join
    -- where -> on
    select d.deptno, d.dname, count(e.empno) from dept d left outer join emp e on d.deptno=e.deptno group by d.deptno, d.dname;

    -- oracle
    select d.deptno, d.dname, count(e.empno) from dept d, emp e where e.deptno(+)=d.deptno group by d.deptno, d.dname;
    
    -- mysql + oracle
    -- 表之间的逗号替换为:   right outer join -> outer 可以省略 -> right join
    -- where -> on
    select d.deptno, d.dname, count(e.empno) from emp e right join dept d on e.deptno=d.deptno group by d.deptno, d.dname;

      -- 在mysql中不支持全外连接, oracle中是支持的
    
      -- full outer join -> 简写: full join
      -- 有可能emp表给dept提供补充, 也有可能dept给emp提供补充
      select d.deptno, d.dname, count(e.empno) from emp e full join dept d on e.deptno=d.deptno group by d.deptno, d.dname;

-- oracle
select e.ename "员工", nvl(b.ename, 'jump') "领导"  from emp e, emp b where e.mgr=b.empno(+); 
 
-- mysql
-- mysql 中的ifnull 等价于 oracle 中的 nvl
select e.ename "员工", ifnull(b.ename, 'jump') "领导"  from emp e left join emp b on e.mgr=b.empno; 

create table 表名(
    列名 列的类型 default 默认值,
    列名 列的类型,
    列名 列的类型
);

create table persion(
    id int,            -- 主键, 自动增长
    age int,            -- 非空
    name varchar(20),    -- 唯一
    deptno int        -- 外键
);

-- auto_increment只能放到列的位置
-- not null 非空只能在列上添加约束
create table persion(
    id int auto_increment,            -- 主键, 自动增长
    age int not null,            -- 非空
    name varchar(20),    -- 唯一
    deptno int,        -- 外键
    constraint pk_id_xxx primary key(id) ,
    constraint un_name_xxx unique(name),
    constraint fk_deptno_xx foreign key(deptno) references dept(deptno)
);

create table persion1(
    id int primary key auto_increment,            -- 主键, 自动增长
    age int not null,            -- 非空
    name varchar(20) unique,    -- 唯一
    deptno int references dept(deptno)    -- 外键
);

-- 修改事务提交方式
 
-- 手动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
-- 提交数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
-- 数据回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
 
-- 自动提交
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

// 参数: NULL
// 返回值: 返回函数申请的内存的地址
MYSQL *mysql_init(MYSQL *mysql)  

 // 返回值: 失败-> NULL, 成功了-> 返回值和第一个参数的地址相同
MYSQL *mysql_real_connect(
                MYSQL *mysql,     // mysql_init函数的返回值
                // 本地: localhost, 远程连接:192.168.xx.xx;
                const char *host,     // mysql服务器地址, IP/主机名      
                const char *user,     // mysql数据用户名, root       
                const char *passwd,   // root用户的密码
                const char *db,       // 数据库的名字       
                // 如果port==0, 使用默认端口, !=0是使用指定的端口
                unsigned int port,    // 数据库的端口, 默认3306    
                const char *unix_socket,   // NULL  
                unsigned long client_flag  // 0
);

// 添删查改的sql语句都是可以执行的
// 参数:
//         mysql: mysql_real_connect()函数的返回值
//      query: sql语句, 语句的结尾不要加 ;
// 返回值: 成功-> 0, 失败->非0
int mysql_query(MYSQL *mysql, const char *query);

// 将结果集保存到本地内存中
// 返回值: 错误->null, 成功->非空
// 参数 mysql: mysql_real_connect() 返回值
// 结果集中保存了多条记录, 每条记录中有若干个字段
MYSQL_RES *mysql_store_result(MYSQL *mysql);

unsigned int mysql_num_fields(MYSQL_RES *result)

/ mysql.h
typedef struct st_mysql_field {
  char *name;                 /* Name of column */
  char *org_name;             /* Original column name, if an alias */
  char *table;                /* Table of column if column was a field */
  char *org_table;            /* Org table name, if table was an alias */
  char *db;                   /* Database for table */
  char *catalog;          /* Catalog for table */
  char *def;                  /* Default value (set by mysql_list_fields) */
  unsigned long length;       /* Width of column (create length) */
  unsigned long max_length;   /* Max width for selected set */
  unsigned int name_length;
  unsigned int org_name_length;                                                                                        
  unsigned int table_length;
  unsigned int org_table_length;
  unsigned int db_length;
  unsigned int catalog_length;
  unsigned int def_length;
  unsigned int flags;         /* Div flags */
  unsigned int decimals;      /* Number of decimals in field */
  unsigned int charsetnr;     /* Character set */
  enum enum_field_types type; /* Type of field. See mysql_com.h for types */
  void *extension;
} MYSQL_FIELD;
 
// 返回值 中保存了所有的字段名, 返回值是一个数组
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result);
 
MYSQL_FIELD *fields = mysql_fetch_fields(result);
for(int i=0; i<num; ++i) {
    fields[i].name
}

typedef char** MYSQL_ROW;
// 函数每调用一次, 从结果集中取出一条记录(行)
// 参数: 结果集, mysql_store_result()返回值
// 返回值: char* row[], char**执向一个指针数组, 数组中的每一个元素都是一个字符串, 字符串就是字段值
//        不管字段原来是什么类型, 结果集中都是以字符串形式保存的
// 如果还有下一行返回非空, 没有下一行, 或者失败了返回NULL
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

// 参数是 mysql_store_result() 函数的返回值
void mysql_free_result(MYSQL_RES *result);
 
// 参数是: mysql_init() 函数的返回值
void mysql_close(MYSQL *mysql);

// 获取字节编码
const char *mysql_character_set_name(MYSQL *mysql);
// 设置字节编码
// csname: 编码的名字, utf8
int mysql_set_character_set(MYSQL *mysql, char *csname);

// mode==0 -> 手动提交, mode1 -> 自动提交事务
my_bool mysql_autocommit(MYSQL *mysql, my_bool mode);
my_bool mysql_commit(MYSQL *mysql);
my_bool mysql_rollback(MYSQL *mysql);

// 返回错误的描述
const char *mysql_error(MYSQL *mysql);
// 返回错误的编号
unsigned int mysql_errno(MYSQL *mysql);

#include <mysql.h>
 
// 动态库名: libmysqlclient.so
// 静态库名: libmysqlclient.a

find 路径 -name 文件名
locate 文件名  
    
# centos
# 切换到root下安装
yum install mlocate
updatedb    # 只需要做一次, 安装完成之后做

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include <mysql.h>
 
int main() {
    MYSQL *mysql = mysql_init(NULL);
    if (mysql == nullptr) {
        printf("mysql_init() failed\n");
        return -1;
    }
    
    mysql_real_connect(mysql, "192.168.213.128", "root", "zzc123", "scott", 0, NULL, 0);
    if (mysql == NULL) {
        printf("mysql_real_connect() failed\n");
        return -1;
    }
    printf("服务器连接成功!\n");
    printf("原字符编码: %s\n", mysql_character_set_name(mysql));
    mysql_set_character_set(mysql, "utf8");
    printf("现字符编码: %s\n", mysql_character_set_name(mysql));
    
    int ret = mysql_query(mysql, "select * from emp");
    
    if (ret != 0) {
        printf("mysql_query(select) failed: %s\n", mysql_error(mysql));
        return -1;
    }
    
    MYSQL_RES *res = mysql_store_result(mysql);
    int colNum = mysql_num_fields(res);
    //fields是一个结构体数组
    MYSQL_FIELD *fields = mysql_fetch_fields(res);
    
    for (int i = 0; i < colNum; i ++) {
        //遍历结构体内名字字段
        printf("%s\t", fields[i].name);
    }
    printf("\n");
    
    //读取每一行
    //二级指针
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(res)) != NULL) {
        for (int i = 0; i < colNum; i ++) {
            printf("%s\t", row[i]);
        }
        printf("\n");
    }
    
    //释放结果集
    mysql_free_result(res);
    
    //设置事务手动提交
    mysql_autocommit(mysql, 0);
    
    //将下面的四个操作设置为事务
    //插入数据
    int fl1 = mysql_query(mysql, "insert into dept values(11, '海军', 'china')");
    int fl2 = mysql_query(mysql, "insert into dept values(12, '革命军', 'china')");
    int fl3 = mysql_query(mysql, "insert into dept values(13, '马戏团', 'china')");
    //更新数据
    int fl4 = mysql_query(mysql, "update dept set dname='群众' where loc='china'");
    
    //全部成功
    if (!fl1 && !fl2 && !fl3 && !fl4) {
        //提交事务
        mysql_commit(mysql);
    } else {
        //事务回滚
        mysql_roolback(mysql);
    }
    
    //释放资源
    mysql_close(mysql);
    
    return 0;
}