MySql的基础运用

MySql简介

MySql 为关系型数据库(Relational Database Management System)。

上图为MySQL的结构模型，数据库中包含着数据库，第二级数据库中包含着各种创建的表。

MySql的数据类型

整型

数据类型	含义
tinyint(m)	1个字节
smallint（m）	2个字节
mediumint(m)	3个字节
int（m）	4个字节
bigint(m)	8个字节

浮点型（在数据库中存放的是近似值）

数据类型	含义
float（m,d）	单精度浮点型 8位精度（4字节） m总个数，d小数位
double（m,d）	双精度浮点型 16位精度（8字节）m总个数，d小数位

定点数（在数据库中存放的是精确值）

数据类型	含义
decimal(m,d)	参数m<65,m是总个数；d<30且d<m 是小数位

字符串

数据类型	含义
char（n）	固定长度，最多255字符
varchar（n）	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方 -1个字符
longtext	可变长度，最多2的32次方 -1个字符

char和varchar：

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。

varchar和text：

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用

时间日期类型

数据类型	含义
date	日期’2019-8-15’
time	时间‘15:32:23’
datetime	日期时间’2019-8-15 15:32:23’
timestamp	自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新。

数据类型属性

关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递减，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

MySql使用

登录

mysql -u root -p   //root为账户名

输入密码（安装时设置或者创建用户时设置）

查看所有数据库

show database;

选择（使用）数据库

use mydatabase;

查看所有表

show tables;

查看用户(该表在mysql数据库里)

select user from user

创建用户

create user "jsetc"@"localhost"  identified by "123"; 	//用户名jsetc,地址localhost,密码123

更改用户密码

set password for "jsetc"@"localhost" = PASSWORD("000");  //改密码为000

给用户授权（管理员模式下）

grant insert,create on *.* to "jsetc"@"localhost";   //给用户jsetc创建表和写入数据的权限

创建数据库

create database mydatabase; 			 //创建名为mydatabase的数据库

创建表

create table student(name varchar(10) not null,
age tinyint unsigned not null,
sex char(6)
);                     			//创建名为student的表,此创建方法不支持中文写入

查看表结构

describe student;

插入数据

insert into student (name,age,sex) values("jack",18,"male");  

修改表名

alter table student rename stu;  		 //把名为student的表重命名为stu

删除字段

alter table student drop sex; 

添加字段

alter table student add sex char(6);

alter table student add id int unsigned not null primary key auto_increment first;
//在最前面添加字段id，无符号整型不为空，主键自动添加  primary(不能重复)

alter table student add birthday date after name;
alter table student tel char(11) default  '-';    //默认填充‘-’

修改字符集

alter table student convert to character set utf8;  	//使表student支持中文写入

修改数据

update student set tel = "100" where id = 123;

删除记录

delete from student where id = 123;

删除表

drop table drop;

删除数据库

drop database mydatabase;

事务操作

create table account(
id int unsigned primary key not null,
name varchar(10),
account varchar(16),
money bigint
)charset utf8;		//创建银行账户表

insert int account values(1,"甲"，"0000000000000000001",9000),
(2,"乙"，"0000000000000000002",9000),
(3,"丙"，"0000000000000000003",9000)；    //添设三个账户

start transaction;   	//开设事务 （退出终端，事务日志被清空，所以数据库不会改动）

uodate account set money  = money - 500 where id = 2;
uodate account set money  = money - 500 where id = 3;
uodate account set money  = money + 1000 where id = 1;  	//乙丙给甲转账

commit;  	 //当所有命令执行完，提交指令，数据库的数据才会被修改

回滚点

create table account(
id int unsigned primary key not null,
name varchar(10),
account varchar(16),
money bigint
)charset utf8;  //创建银行账户表

insert int account values(1,"甲"，"0000000000000000001",9000),
(2,"乙"，"0000000000000000002",9000),
(3,"丙"，"0000000000000000003",9000)；    //添设三个账户

start transaction;    //开设事务

uodate account set money  = money - 500 where id = 2;
savepoint sp1;    //设置回滚点
uodate account set money  = money - 500 where id = 3;
savepoint sp2;
uodate account set money  = money + 1000 where id = 1;   //乙丙给甲转账

rollback to sp1;   //回到sp1   ，第二第三条命令撤销

commit；

MySql的程序调用

#include <iostream>
#include <mysql.h>
#include <stdio.h>
#include <iomanip>
   
 using namespace std;
   
  MYSQL *Init()
  {   
 	MYSQL *mysql;
 
 	mysql = mysql_init(NULL);     //初始化mysql对象，返回对象地址
 	if (NULL == mysql)
 	{
 		cout << "mysql_init " << mysql_error(mysql) << endl;
 		return NULL;
 	}
 
 	    //连接服务器
 	mysql = mysql_real_connect(mysql, "localhost", "root", "root", "mydatabase", 0, NULL, 0);
 	if (NULL == mysql)
 	{
 		cout << "连接失败" << endl;
 		cout << "mysql_real_connect " << mysql_error(mysql) << endl;
 		return NULL;
 	}
 	return mysql;
 }
  
 void InsertDatabase(MYSQL *mysql)
 {   
	if (NULL == mysql)
 		return;
 
 	   //设置字符集
 	int ret = mysql_query(mysql, "set names utf8;");
 	if (ret != 0)
 	{
 		cout << "mysql_query : " << mysql_error(mysql) << endl;
 		return;
 	}
 
 	int id;
 	cin >> id;
 	char sql[128] = {0};
 	sprintf(sql, "insert into account values (%d, '张三', '0000000000000003', 3000);", id);
 
 	 ret = mysql_query(mysql, sql);
 	if (ret != 0)
 	{
 		cout << "mysql_query : " << mysql_error(mysql) << endl;
 		return;
 	}
 	cout << "插入成功" << endl;
 }
  
 void GetResult(MYSQL *mysql)
 {
 	if (NULL == mysql)
 		return;
	char sql[128] = "select * from account;";
 	int ret = mysql_query(mysql, sql);   //查询结果
 	if (ret != 0)
 	{
 		cout << "mysql_query : " << mysql_error(mysql) << endl;	
	}
 
 	MYSQL_RES *res;
 	res = mysql_store_result(mysql);   //获取查询到的结果
 	if (NULL == res)
 	{
 		cout << "mysql_store_result : " << mysql_error(mysql) << endl;
 	}
 
 	//获取字段数
 	unsigned int field;
 	field = mysql_num_fields(res);
 
 	MYSQL_FIELD *f;                         //结构体，保存每列数据
 	while(f = mysql_fetch_field(res))
 	{
 		cout << setw(20) << setiosflags(ios::left) << f->name;                   //获取字段名
 	}
 	cout << endl;
 
 	MYSQL_ROW row;                       //数组 保存一行数据（包括所有字段）
 	while (row = mysql_fetch_row(res))
 	{
 		for (int i = 0; i < field; i++)
 		{
			cout << setw(20) << setiosflags(ios::left) << row[i];
 		}
 		cout << endl;
 	}	
}
 
int main()
 {
	MYSQL *mysql;

	mysql = Init();    //初始化mysql对象	
	if (NULL == mysql)	
	{
		cout << "init failure" << endl;
	}
	else
	{
		cout << "连接服务器成功" << endl;
	}

	InsertDatabase(mysql);
	GetResult(mysql);

	return 0;
}

编译命令

g++ mysql.cpp -o mysql -lmysqlclient

mysql_init()

MYSQL *mysql_init(MYSQL *mysql)

分配或初始化与mysql_real_connect()相适应的MYSQL对象。如果mysql是NULL指针，该函数将分配、初始化、并返回新对象。
否则，将初始化对象，并返回对象的地址。如果mysql_init()分配了新的对象，当调用mysql_close()来关闭连接时。将释放该对象。

返回值
初始化的MYSQL*句柄。如果无足够内存以分配新的对象，返回NULL。
错误
在内存不足的情况下，返回NULL。

mysql_real_connect

MYSQL *mysql_real_connect
(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db,unsigned int port, const char *unix_socket, unsigned long client_flag)

mysql_real_connect()尝试与运行在主机上的MySQL数据库引擎建立连接。在你能够执行需要有效MySQL连接句柄结构的任何其他API函数之前，mysql_real_connect()必须成功完成。
参数的指定方式如下：
第1个参数应是已有MYSQL结构的地址。调用mysql_real_connect()之前，必须调用mysql_init()来初始化MYSQL结构。通过mysql_options()调用，可更改多种连接选项。请参见25.2.3.48节，“mysql_options()”。
“host”的值必须是主机名或IP地址。如果“host”是NULL或字符串"localhost"，连接将被视为与本地主机的连接。如果操作系统支持套接字（Unix）或命名管道（Windows），将使用它们而不是TCP/IP连接到服务器。
“user”参数包含用户的MySQL登录ID。如果“user”是NULL或空字符串""，用户将被视为当前用户。在UNIX环境下，它是当前的登录名。在Windows ODBC下，必须明确指定当前用户名。
“passwd”参数包含用户的密码。如果“passwd”是NULL，仅会对该用户的（拥有1个空密码字段的）用户表中的条目进行匹配检查。这样，数据库管理员就能按特定的方式设置MySQL权限系统，根据用户是否拥有指定的密码，用户将获得不同的权限。
注释：调用mysql_real_connect()之前，不要尝试加密密码，密码加密将由客户端API自动处理。
“db”是数据库名称。如果db为NULL，连接会将默认的数据库设为该值。
如果“port”不是0，其值将用作TCP/IP连接的端口号。注意，“host”参数决定了连接的类型。
如果unix_socket不是NULL，该字符串描述了应使用的套接字或命名管道。注意，“host”参数决定了连接的类型。
client_flag的值通常为0，但是，也能将其设置为标志的组合，以允许特定功能。

mysql_real_query（）

int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)
执行由“query”指向的SQL查询，它应是字符串长度字节“long”。正常情况下，字符串必须包含1条SQL语句，而且不应为语句添加终结分号（‘;’）或“\g”。如果允许多语句执行，字符串可包含由分号隔开的多条语句。
对于包含二进制数据的查询，必须使用mysql_real_query()而不是mysql_query()，这是因为，二进制数据可能会包含‘\0’字符。
此外，mysql_real_query()比mysql_query()快，这是因为它不会在查询字符串上调用strlen()。
如果希望知道查询是否应返回结果集，可使用mysql_field_count()进行检查

返回值
如果查询成功，返回0。如果出现错误，返回非0值。

mysql_store_result（）

MYSQL_RES *mysql_store_result(MYSQL *mysql)

对于成功检索了数据的每个查询（SELECT、SHOW、DESCRIBE、EXPLAIN、CHECK TABLE等），必须调用mysql_store_result()或mysql_use_result() 。
对于其他查询，不需要调用mysql_store_result()或mysql_use_result()，但是如果在任何情况下均调用了mysql_store_result()，它也不会导致任何伤害或性能降低。通过检查mysql_store_result()是否返回0，可检测查询是否没有结果集（以后会更多）。
如果希望了解查询是否应返回结果集，可使用mysql_field_count()进行检查。
mysql_store_result()将查询的全部结果读取到客户端，分配1个MYSQL_RES结构，并将结果置于该结构中。
如果查询未返回结果集，mysql_store_result()将返回Null指针（例如，如果查询是INSERT语句）。
如果读取结果集失败，mysql_store_result()还会返回Null指针。通过检查mysql_error()是否返回非空字符串，mysql_errno()是否返回非0值，或mysql_field_count()是否返回0，可以检查是否出现了错误。
如果未返回行，将返回空的结果集。（空结果集设置不同于作为返回值的空指针）。
一旦调用了mysql_store_result()并获得了不是Null指针的结果，可调用mysql_num_rows()来找出结果集中的行数。
可以调用mysql_fetch_row()来获取结果集中的行，或调用mysql_row_seek()和mysql_row_tell()来获取或设置结果集中的当前行位置。
一旦完成了对结果集的操作，必须调用mysql_free_result()。

返回值
具有多个结果的MYSQL_RES结果集合。如果出现错误，返回NULL。
错误
如果成功，mysql_store_result()将复位mysql_error()和mysql_errno()。

· CR_COMMANDS_OUT_OF_SYNC
以不恰当的顺序执行了命令。

· CR_OUT_OF_MEMORY
内存溢出。

· CR_SERVER_GONE_ERROR
MySQL服务器不可用。

· CR_SERVER_LOST
在查询过程中，与服务器的连接丢失。

· CR_UNKNOWN_ERROR
出现未知错误。

mysql_num_fields()

unsigned int mysql_num_fields(MYSQL_RES *result)

要想传递MYSQL*参量取而代之，请使用无符号整数mysql_field_count(MYSQL *mysql)。

返回结果集中的行数。

注意，你可以从指向结果集的指针或指向连接句柄的指针获得行数。如果mysql_store_result()或mysql_use_result()返回NULL，应使用连接句柄（因而没有结果集指针）。在该情况下，可调用mysql_field_count()来判断mysql_store_result()是否生成了非空结果。这样，客户端程序就能采取恰当的行动，而不需要知道查询是否是SELECT语句（或类似SELECT的语句）。在下面的示例中，介绍了执行该操作的方式。

示例：

MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;
if (mysql_query(&mysql,query_string))
{
	// error
}
else 		// query succeeded, process any data returned by it
{
	result = mysql_store_result(&mysql);
	if (result) // there are rows
	{
		num_fields = mysql_num_fields(result);
			// retrieve rows, then call mysql_free_result(result)
	}
	else // mysql_store_result() returned nothing; should it have?
	{
		if (mysql_errno(&mysql))
		{
			fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
		}
		else if (mysql_field_count(&mysql) == 0)
		{
			// query does not return data
			// (it was not a SELECT)
			num_rows = mysql_affected_rows(&mysql);
		}
	}
}

另一种可选方式是（如果你知道你的查询应返回结果集），使用检查“mysql_field_count(&mysql) is = 0”来替换mysql_errno(&mysql)调用。仅当出错时才应使用它。

mysql_fetch_row

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

检索结果集的下一行。在mysql_store_result()之后使用时，如果没有要检索的行，mysql_fetch_row()返回NULL。
在mysql_use_result()之后使用时，如果没有要检索的行或出现了错误，mysql_fetch_row()返回NULL。

行内值的数目由mysql_num_fields(result)给出。如果行中保存了调用mysql_fetch_row()返回的值，将按照row[0]到row[mysql_num_fields(result)-1]，访问这些值的指针。行中的NULL值由NULL指针指明。
可以通过调用mysql_fetch_lengths()来获得行中字段值的长度。对于空字段以及包含NULL的字段，长度为0。通过检查字段值的指针，能够区分它们。如果指针为NULL，字段为NULL，否则字段为空。

返回值
下一行的MYSQL_ROW结构。如果没有更多要检索的行或出现了错误，返回NULL。
错误
注意，在对mysql_fetch_row()的两次调用之间，不会复位错误。
· CR_SERVER_LOST
在查询过程中，与服务器的连接丢失。

· CR_UNKNOWN_ERROR
出现未知错误。

示例：

MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;

num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
	unsigned long *lengths;
	lengths = mysql_fetch_lengths(result);
	for(i = 0; i < num_fields; i++)
	{
		printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL");
	}
	printf("\n");
}