嵌入式数据库开发编程MySQL（全）

• 一、mysql
• 二、Linux C下MYSQL的API

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

-- 登录MYSQL
mysql -u root -p

-- 查看所有的数据库
show databases;

-- 使用数据库
use mysql;
-- 查看所有表
show tables;
-- 查看所有用户信息
select user from user;

-- 创建用户
create user "c++class"@"localhost" identified by "123456";

-- 修改密码
set password for "c++class"@"localhost" = PASSWORd("666666");

-- 授予权限
grant select on *.* to "c++class"@"localhost";
grant all on *.* to "c++class"@"localhost";

-- 创建表
create table int_test(
tiny_int tinyint,
small_int smallint,
medium_int mediumint,
commint_int int,
big_int bigint
);
-- 显示表的结构
describe int_test;

-- 插入数据
insert into int_test values(1,1,1,1,1);
insert into int_test values(200,1,1,1,1);  -- ERROR 1264 (22003): Out of range value for column 'tiny_int' at row 1
insert into int_test values(1,1,1,'a',1);  -- ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'commint_int' at row 1

-- 创建学生表
create table student(
name varchar(10) not null,
age tinyint unsigned not null,
sex char(6)
);

-- 修改表
alter table student rename stu;
alter table stu drop sex;
alter table stu add sex char(6);
alter table stu add id int unsigned not null primary key auto_increment first;
alter table stu add birth date after name;
alter table stu add tel char(11) default '-';

-- 插入数据
insert into stu (id,name,birth,age,sex,tel) values(1000,"zhangsan","1999-9-9",90,"femal","11111111111");
insert into stu (name,birth,age,sex,tel) values("lisi","1993-9-19",40,"mal","22222222222");
insert into stu values(1002,"xiaozhang","1939-9-9",90,"femal","11213141511"),(1003,"xiaoming","1979-9-19",80,"femal","55555555555");
insert into stu (id,name,birth,age,sex) values(1005,"xiaowang","1993-9-19",40,"mal");
insert into stu (id,name,birth,age,sex) values(1005,"xiaowang","1993-9-19",40,"mal");
insert into stu stu values(1006,"张三","1998-8-8",21,"male","11111111111");

-- 查询数据
select * from stu;
select name from stu;
select name from stu where id > 1002;
select name from stu where id between 1001 and 1004;

-- 修改字符集
alter table stu convert to character set utf8;

-- 修改数据
update stu set tel = "88888888888" where name = "张三";

-- 删除记录
delete from stu where id = 1000;

-- 删除数据库
drop database test;

-- 创建数据库
create database test;

-- 事务操作
-- 创建表
create table account(
id int unsigned not null primary key,
name varchar(20),
money int
)charset utf8;

--插入数据
insert into account values(1000,"xiaozhang",2000),(1001,"xiaoming",5000),(1002,"张三",1000);

-- 小张给小明转账500
update account set money = money - 500 where id = 1000;

-- 停电了 数据出错

-- 开启事务
start transaction;

-- 小张给小明转账500
update account set money = money - 500 where id = 1000;

-- 小明增加500块
update account set money = money + 500 where id = 1001;

-- 提交
commit;



-- 开启事务
start transaction;

-- xiaozhang转出500
update account set money = money - 500 where id = 1000;

-- 设置回滚点
savepoint sp1;

-- 小明转出500块
update account set money = money - 500 where id = 1001;

-- 设置回滚点
savepoint sp2;

-- 张三增加1000块
update account set money = money + 1000 where id = 1002;

-- 回滚到sp1;
rollback to sp1;

-- 提交
commit;

#include 
#include 
#include 
using namespace std;

MYSQL *Init()
{
    MYSQL *m;
    m = mysql_init(NULL); //创建mysql对象并且初始化，返回对象的地址
    if (NULL == m)
    {
        cout<>id>>name>>birth>>age>>sex>>tel;
    sprintf(sql,"insert into stu (id,name,birth,age,sex,tel) values(%d,'%s','%s',%d,'%s','%s')",
    id,name,birth,age,sex,tel);
    ret = mysql_query(m,sql);
    if(ret != 0)
    {
        cout<< mysql_error(m)<name; //打印字段名
    }
    cout< 
 
 
-lmysqlclient