MySQL入门

数据库概念

我们常说的数据库其实是数据库+数据库管理系统。

数据库采用的是c/s模式（服务器客户端）

其中表为二维表类型的数据库叫做关系型数据库

表为键值对类型的数据库叫做非关系型数据库

二维表：

Linux MySQL基础操作

登录：

mysql -u用户名 -p密码
//例如 mysql -uroot -p123456

添加用户（本地/远程）

create user '用户名'@'本地/远程' identified WITH mysql_native_password by '密码'
//例如创建本地管理员用户 create user 'root'@'localhost' identified WITH mysql_native_password by '123456'
//例如创建远程管理员用户 create user 'root'@'%' identified WITH mysql_native_password by '123456'

如果需要远程登录我们MySQL服务器

常用SQL语句

对数据库的操作

查看所有数据库：

show databases;

创建数据库：

create database 数据库名称 charset=utf8；
//charset utf8:字符集，当数据库中要写中文时添加

查看当前使用的数据库：

select database();

使用某个数据库：

use 数据库名；

删除数据库：

drop database 数据库名;

对表的操作

查看所有表：

show tables;

创建表：

create table 表名(字段 类型 约束,...);
//例如 create table student(id int,name varchar(20),year int);可以不写约束

类型：

整形：

字符串类型：

约束：

向表中插入数据：

例如：

insert into 表名 values(字段1的值，字段2的值..);
//例如：insert into student values(103,"小张") ;

更新表中数据：

例如：

update 表名 set 某个字段=新值 where 另外字段=此字段的值;
//例如：updata student set name="小黄" where id=101;

删除表中某行数据：

例如：

delete from 表名 where 要删除行的某一字段=此字段的值；
//例如：delete from student where id=101;

查看这个表中的数据：

select * from 表名;

对用户的操作

C语言连接MySQL服务器

例1：连接上student数据库给test_index表上插入10000条数据

例2：打印student数据库中stu1表中的锁有数据

API接口

所需头文件：

#include<mysql/mysql.h>

MYSQL* mysql_init( MYSQL* mysql ):初始化连接句柄

mysql：连接句柄

MYSQL* mysql_real_connect( MYSQL* mysql,const char* host,const char* user,const char* password,const char* db,const char* port,const char* unix_socket,unsigned long clientflag ):连接数据库，如果返回NULL则连接失败

mysql：连接句柄

host：主机名（"localhost"或"127.0.0.1"或NULL都可以）

user：要连接MySQL数据库的用户名

passward：此用户的密码

db：要连接的数据库名称

port：MySQL的端口3306

unix_socket：一般给NULL

clientflag：一般写0

bool mysql_query( MYSQL* mysql,char* buff ):执行sql语句

mysql：连接句柄

buff：存放sql语句的数组

返回值：为0代表成功

MYSQL_RES* mysql_store_result( MYSQL* mysql ):获取结果集（必须在mysql_query执行后且mysql_query必须执行搜索语句）

mysql：连接句柄

unsigned int mysql_num_rows( MYSQL_RES* res ):获取结果集中的行数

res:结果集

unsigned int mysql_field_count( MYSQL* mysql ):获取数据库结果集所在表的列数

mysql：连接句柄

MYSQL_ROW mysql_fetch_row( MYSQL_RES* res ):获取结果集中一行数据

res：结果集

返回值：结果集一行数据存放在一个行结构（相当于数组，每一个空间存放这一行中每列的信息）中（动态开辟的空间，最后需要释放）

void mysql_free_result( MYSQL_RES* res ):释放结果集所申请的空间

res：结果集

void mysql_close( MYSQL* mysql ):断开连接

mysql:连接句柄

代码实现

例1：

#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<unistd.h>
#include<mysql/mysql.h>

int main()
{
    MYSQL mysql;
    MYSQL *p = mysql_init(&mysql);  //初始化连接句柄
    if(p == NULL)
    {
        mysql_close(&mysql);
        printf("mysql_init err\n");
        exit(0);
    }

    p = mysql_real_connect(&mysql,"localhost","root","20011128","student",3306,NULL,0);  //连接MySQL数据库
    if(p == NULL)
    {
        mysql_close(&mysql);
        printf("mysql connect err\n");
        exit(0);
    }
    printf("mysql connect success\n");
    char buff[128] = {0};
    for(int i =0;i<10000;i++)
    {
        sprintf(buff,"insert into test_index values('hello-%d')",i);  //执行sql语句
        if(mysql_query(&mysql,buff) != 0)
        {
            break;
        }
    }
    mysql_close(&mysql);
    exit(0);
}

结果演示：

例2：

#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<unistd.h>
#include<mysql/mysql.h>

int main()
{
    MYSQL mysql;
    MYSQL* p = mysql_init(&mysql);  //初始化连接句柄
    if(p == NULL)
    {
        mysql_close(&mysql);
        printf("mysql_init err\n");
        exit(0);
    }
    p = mysql_real_connect(&mysql,"localhost","root","20011128","student",3306,NULL,0);  //连接数据库
    if(p == NULL)
    {
        mysql_close(&mysql);
        printf("mysql connect err\n");
        exit(0);
    }
    printf("connect success\n");

    char *sql = "select * from stu1";   //查询stu1表的所有信息
    int n = mysql_query(&mysql,sql);    //执行sql语句
    if(n != 0)
    {
        mysql_close(&mysql);
        printf("mysql_query err\n");
        exit(0);
    }
    MYSQL_RES* res = mysql_store_result(&mysql);  //获取结果集
    if(res == NULL)
    {
        printf("get result err\n");
        mysql_close(&mysql);
        exit(0);
    }
    int r = mysql_num_rows(res);  //结果集行数
    printf("行数:%d\n",r);

    int l = mysql_field_count(&mysql);  //列数
    printf("列数:%d\n",l);

    for(int i =0;i < r;i++)
    {
        MYSQL_ROW row = mysql_fetch_row(res);   //获取结果集中一行数据
        for(int j =0;j < l;j++)
        {
            printf("%s    ",row[j]);
        }
        printf("\n");
    }
    mysql_free_result(res);   //释放结果集
    mysql_close(&mysql);
    exit(0);
}

结果演示：

视图

概念：

视图是一个”虚表”，用大白话说，就是从已经存在的表的全部字段或数据中，挑选出来一部分字段或数据，组成另一张”并不存在的表”,这张虚表被称之”视图”，视图中的字段与对应的数据均来自已经存在的表，对于视图来说，这些已经存在的表就被称为”基表”,基表可以是一张表，也可以是多张表， 视图的本质可以理解为一条查询语句，视图中显示的结果，就是这条查询语句查询出的结果。

为什么需要视图：

视图中的数据可能来自于多张基表，如果基表有很多，而且基表之间存在复杂的关系，当我们需要找出某些数据时，可能需要执行一条复杂的sql语句，如果我们把这个复杂的sql语句创建为视图，我们就可以直接从视图的结果中使用简单的sql语句查询出需要的结果，这使我们容易理解和使用。

视图命令

在创建视图之前，需要先查看当前用户是否具有创建视图的权限

select create_view_priv,select_priv from mysql.user where user='root' and host='localhost';

创建视图（如果此视图已存在，则替代原来的视图）

create or replace view 视图名称 as select * from 基表1名称，基表2名称... where 条件;（不写where视图和基表就完全一致）
//例如：create or replace view view_demo as select * from student,guide where suid <= 2;

删除视图（如果视图存在则删除）

drop view if exists 视图名称;

查看某一数据库中的所有视图

select * from information_schema.views where table_schema='数据库名称'\G

查看视图的结构

desc 视图名;

查看视图中的所有内容

select * from 视图名;

修改视图

alter view 视图名称 as select 要保留的字段1名称,要保留的字段2名称,... from 基表名称;

索引与事务

前言

在谈到数据库的索引与事务时，我们一定要谈论数据库的搜索引擎！

搜索引擎

查看当前数据库的搜索引擎

show engines;

索引

索引就是用来加快查询速度的。

其中：InnoDB搜索引擎下索引底层是采用B+树完成的（B+树深度变化不大，查询的稳定程度较好）

索引命令

对表中的某一字段添加索引：

create index 索引名称 on 表名(字段名)；
//如果此字是字符串类型，还要再字段名后加(字节个数)
//例如: create index myindex on student(name(20));

删除表中某一字段的索引

drop index 索引名称 on 表名;
//例如：drop index myindex on student;

查看运行时间：

set profiling=1;
运行语句...
show profiles;

查看表的信息：

show create table 表名\G

给表中某一字段添加索引可以极大减少查询时间，那么给表中所有字段都加不是更好？

答：并非索引越多越好，索引越多，底层构建的B+树越复杂，虽然查询速度极大的缩短，但是会对插入操作造成很大的影响。

事务

概念

事务，在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元。

（可以理解为一组sql语句，要么全做成功，要么不做（包括做了一部分，但有一部分出错就要回滚））

事务的特性(ACID)

原子性

对于事务中的语句，要么全部执行成功，要么全部失败回滚，不可能只对其中一部分进行操作。

一致性

从一个正确的状态转换为另一个正确的状态，不会产生中间状态。

隔离性

我这个事务正在读一些数据，而其他事务修改这些数据对我的影响称作隔离性（我提交之前，你能看到我修改的数据还是看不到）

隔离级别

在SQL标准中定义有四大隔离级别：

READ UNCOMMITTED 未提交读

如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据

READ COMMITTED 提交读

如果是一个读事务，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

REPEATABLE READ 可重复读

在这一事务中，连续两次对同一数据的查询，得到的结果相同（即便别的事务修改并提交了这个数据）。

脏读：当前事务读取某条记录时，别的事务更改了这条记录中的数据，当前事务再读取同一记录时，产生了两种不同结果。

幻读：当前事务读取某一范围的记录时，别的事务向这一范围中插入新的记录，当前事务再读取该范围的记录时就会产生幻行，平白无故多出几条数据。

SERIALIZABLE 可串行化

事务只能一个接着一个地执行，但不能并发执行，序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读

有关隔离性的命令

查看当前系统（全局）的隔离性

select @@GLOBAL.transaction_isolation;

设置当前系统（全局）隔离级别

set global transaction isolation level 隔离级别名称

设置当前会话隔离级别

set session transaction isolation level 隔离级别名称

持久性

只要事务提交，其所作的修改会永久保存在数据库中。

事务命令

注意：必须是InnoDB引擎才可以使用事务

开启事务： //开启事务后执行修改命令，变化会存放在本地缓存中，提交事务后才会维护到物理表中

begin;

回滚事务: //撤销事务中的操作

rollback;

提交事务

commit;