一、mysql
sudo apt-get install mysql-server mysql-client libmysqlclient-dev -- 登录MYSQL mysql -u root -p -- 退出MYSQL quit; -- 查看所有的数据库 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"; -- 删除用户 drop user "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;
二、Linux C下MYSQL的API
#include <iostream> #include <mysql/mysql.h> #include <iomanip> using namespace std; MYSQL *Init() { MYSQL *m; m = mysql_init(NULL); //创建mysql对象并且初始化，返回对象的地址 if (NULL == m) { cout<<mysql_error(m)<<endl; //打印错误信息 return NULL; } m = mysql_real_connect(m,"localhost","c++class","1","mysql",0,NULL,0); if (NULL == m) { cout<<mysql_error(m)<<endl; //打印错误信息 return NULL; } return m; } void InsertDatabase(MYSQL *m) { if (NULL == m) { return; } int ret = mysql_query(m,"set names utf8"); if (ret != 0) { cout<<mysql_error(m)<<endl; return; } char sql[128] = {0}; int id,age; char name[32] = {0},birth[32] = {0},sex[12] = {0},tel[12] = {0}; cout<<"input:id,name,birth,age,sex,tel:"<<endl; cin>>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)<<endl; //打印错误信息 return; } cout<<"插入成功"<<endl; } void FindDataBase(MYSQL *m) { if(m == NULL) { return; } int ret = mysql_query(m,"set names utf8"); if(ret != 0) { cout<< mysql_error(m)<<endl; //打印错误信息 return; } ret = mysql_query(m,"select * from stu;"); if(ret != 0) { cout<< mysql_error(m)<<endl; //打印错误信息 return; } MYSQL_RES * res = mysql_store_result(m); //获取查询到的结果 if(NULL == res) { cout<< mysql_error(m)<<endl; //打印错误信息 return; } //获取字段数 int 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 r; //数组 while(r = mysql_fetch_row(res)) { for(int i = 0 ; i < field;i++) { cout<<setw(20)<<setiosflags(ios::left)<<r[i]; } cout<<endl; } } int main(int argc, char const *argv[]) { MYSQL *mysql; //创建mysql变量 mysql = Init(); if(mysql != NULL) { cout<<"链接数据库成功"<<endl; } else { cout<<"初始化数据库失败"<<endl; } //插入数据 InsertDatabase(mysql); FindDataBase(mysql); return 0; }
-lmysqlclient
