æ°æ®åº
æ°æ®åºï¼Databaseï¼æ¯æç §æ°æ®ç»ææ¥ç»ç»ãåå¨å管çæ°æ®çä»åºãæ¯ä¸ªæ°æ®åºé½æä¸ä¸ªæå¤ä¸ªä¸åç API ç¨äºå建ï¼è®¿é®ï¼ç®¡çï¼æç´¢åå¤å¶æä¿åçæ°æ®ã
æ°æ®åº mysql Â
mysql å±äº å ³ç³»æ°æ®åºç®¡çç³»ç»(Relational Database Management System)ï¼æå¦ä¸ç¹ç¹ï¼
- æ°æ®ä»¥è¡¨æ ¼çå½¢å¼åºç°
- æ¯è¡ä¸ºåç§è®°å½å称
- æ¯å为记å½å称æ对åºçæ°æ®å
- 许å¤çè¡ååç»æä¸å¼ 表å
- è¥å¹²ç表åç»ædatabase
æ¯è¯
åä½ï¼åå¨ä¸¤åæ°æ®ï¼åä½éä½äºæ§è½ï¼ä½æé«äºæ°æ®çå®å ¨æ§ã
主é®ï¼ä¸»é®æ¯å¯ä¸çãä¸ä¸ªæ°æ®è¡¨ä¸åªè½å å«ä¸ä¸ªä¸»é®ãä½ å¯ä»¥ä½¿ç¨ä¸»é®æ¥æ¥è¯¢æ°æ®ã
å¤é®ï¼å¤é®ç¨äºå ³è两个表ã
å¤åé®ï¼å¤åé®ï¼ç»åé®ï¼å°å¤ä¸ªåä½ä¸ºä¸ä¸ªç´¢å¼é®ï¼ä¸è¬ç¨äºå¤åç´¢å¼ã
ç´¢å¼ï¼ä½¿ç¨ç´¢å¼å¯å¿«é访é®æ°æ®åºè¡¨ä¸çç¹å®ä¿¡æ¯ãç´¢å¼æ¯å¯¹æ°æ®åºè¡¨ä¸ä¸åæå¤åçå¼è¿è¡æåºçä¸ç§ç»æã类似äºä¹¦ç±çç®å½ã
åç §å®æ´æ§: åç §çå®æ´æ§è¦æ±å ³ç³»ä¸ä¸å 许å¼ç¨ä¸åå¨çå®ä½ãä¸å®ä½å®æ´æ§æ¯å ³ç³»æ¨¡åå¿ é¡»æ»¡è¶³çå®æ´æ§çº¦ææ¡ä»¶ï¼ç®çæ¯ä¿è¯æ°æ®çä¸è´æ§ã
Â
mysqlæ°æ®åºä¸ºä»ä¹ä¸æ¯ææ°æ®å ¨é¨æ¾å°ä¸ä¸ªå¤§ä»åºå ï¼èæ¯å»ºå¤ä¸ªè¡¨å¢ï¼
建å¤ä¸ªè¡¨ï¼å¯ä»¥æé«å¯¹æ°æ®åºçå¤çé度 å çµæ´»æ§
Â
ubuntu MySQLå®è£
sudo apt-get install mysql-server
apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
å¯å¨ï¼åæ¢ï¼éå¯ sudo /etc/init.d/mysql start|stop|restart
客æ·ç«¯è¿æ¥ mysql -h127.0.0.1 -uroot -p
Â
å¤é¨è¿æ¥mysql éè¦ä¿®æ¹é ç½®æ件
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
注éæ以ä¸ä¸è¡å³å¯ï¼ bind-address = 127.0.0.1
æ¥çmysql çæ¬
mysqladmin --version
设置å¯ç
mysqladmin -u root password "xxxx";
è¿æ¥mysql
mysql -u root -p
Enter password:*******
å建ç¨æ·
CREATE USER [email protected] IDENTIFIED BY password;
CREATE USER 'qb'@'localhost' IDENTIFIED BY '123123';
æææ个ç¨æ· å¯ä»¥è®¿é®çæ°æ®åºå表
GRANT ALL privileges ON databasename.tablename TO 'username'@'host'
FLUSH PRIVILEGES;
privilegesï¼ç¨æ·çæä½æéï¼å¦SELECTï¼INSERTï¼UPDATEçï¼å¦æè¦æäºæçæéå使ç¨ALL
databasenameï¼æ°æ®åºå
tablenameï¼è¡¨åï¼å¦æè¦æäºè¯¥ç¨æ·å¯¹æææ°æ®åºå表çç¸åºæä½æéåå¯ç¨*表示ï¼å¦*.*
Â
æ¾ç¤ºæ°æ®åº
show databases;
æ¾ç¤ºæç´¢å¼æ
show engines;
使ç¨æ个æ°æ®åº
use xxx;
æ¾ç¤ºæ°æ®åºè¡¨
show tables;
æ¾ç¤ºè¡¨å±æ§ï¼å±æ§ç±»åï¼ä¸»é®ä¿¡æ¯ ï¼æ¯å¦ä¸º NULLï¼é»è®¤å¼çå ¶ä»ä¿¡æ¯ã
show columns from tablename;
æ¾ç¤ºè¡¨ç´¢å¼ä¿¡æ¯
show index from tablename;
å建æ°æ®åº
create database tets_mysql;
å建æ°æ®åºè¡¨
create table `dev_stu_info`(
`id` int(11) unsigned not null auto_increment comment 'ç¨æ·id',
`name` varchar(32) collate utf8mb4_bin not null default '' comment 'ç¨æ·å',
`passwd` varchar(32) collate utf8mb4_bin not null default '' comment 'å¯ç ',
`location` varchar(32) collate utf8mb4_bin not null default '' comment 'å°å',
`sex` tinyint(1) unsigned not null default '0' comment 'æ§å«',
primary key (`id`),
key `idx_name` (`name`)
)engine=innodb auto_increment=2 default charset=utf8mb4 collate=utf8mb4_bin
æ¤å¤å建å符éï¼è¡¨å段 主é®ï¼ç´¢å¼ï¼æ°æ®åºå¼æ ï¼ä»2å¼å§èªå¢
æå ¥æ°æ®
æå
¥ä¸æ¡æ°æ®
insert into dev_stu_info (`id`,`name`,`passwd`,`location`,`sex`) values(null,'joohn','123123','广å·','1');
æ·»å å段
alter table dev_stu_info add column `create_time` timestamp null default current_timestamp comment 'æ¶é´';
ä¿®æ¹è¡¨ä¸çæ个å段
alter table dev_stu_info change column `name` `name` varchar(32) character set 'utf8' not null comment 'å§å';
èªå·±é æ°æ®
åå¨è¿ç¨
delimiter $$
create procedure insert_userrole(in start int ,in max_num int )
begin
declare i int default 0;
declare j int default 0;
set autocommit=0;
repeat
set i=i+1;
set j = rand()%2;
set @NAME = 'asjiodfgaujksfghkajlsjd';
insert into dev_stu_info (`id`,`name`,`passwd`,`location`,`sex`,`create_time`) values(null,substr(@NAME,rand()%3+1,4),substr(@NAME,rand()%2+1,7),substr(@NAME,rand()%3+1,6),j,current_timestamp());
until i=max_num end repeat;
commit;
end $$
call insert_userrole(1,100);
æ°æ®åºå¤ä»½ä¸æ¢å¤
å¤ä»½
mysqldump -h主æºå -Pç«¯å£ -uç¨æ·å -på¯ç [-R] æ°æ®åºå > æ件å.sql
mysqldump -uroot -p123456 -R test_mysql > /home/qb/test_mysql .dump Â
-Rå¤ä»½åå¨è¿ç¨,ä¸å ä¸å¤ä»½åå¨è¿ç¨ æ·è´å°å
¶ä»è®¡ç®æº scp -P22 [email protected]:æ¬å°å¤ä»½çæ°æ®åºæ件路å¾Â  è¿ç«¯çéè¦åå¨çä½ç½®Â  Â
æ¢å¤(æ°æ®åºéè¦å
å建好)
mysql -h主æºå -Pç«¯å£ -uç¨æ·å -på¯ç æ°æ®åºå < æ件å.sql mysql -uroot -p123456 test_mysql < /home/qb/test_mysql.dump
mysqläºå¡ï¼ç´¢å¼ï¼åå¨å¼æ ï¼é«æ§è½mysqlï¼
start transaction;
update dev_userinfo set amount = amount-1000 where name='xiaoming';
update dev_userinfo set amount = amount+1000 where name='dabao';
commit; //rollback
mysql  索å¼æ¯ç¨B+æ æ¥åçï¼myisamååºå«
  Â
   myisam   --   B+éé¢åçé½æ¯ç´¢å¼ + æåå ·ä½æ°æ®çæé
   innodb   --   B+æ éé¢åçæ¯ ç´¢å¼+æ°æ®
  Â
   ç¸å½äºmyisaméé¢åçæ¯ä¸ä¸ªå¼ç¨ï¼innodbéé¢åçæ¯å ·ä½æ°æ®
  Â
   äºå¡æ¯mysqlæ§è¡çæå°åå -- ååæ§
   äºå¡çæ§è¡å¦ä½ä¿è¯ååæ§ï¼
      mysqlæä½äºå¡çæ¶åï¼æ¯ä¼å»ºç«ä¸ä¸ªundolog临æ¶è¡¨ï¼è¿éé¢ä¼æè¡çº§é
      æ¤å¤çåæ»ï¼è¥ä¸æ¡æ°æ®æ§è¡ä¸æåçè¯ï¼åundologéé¢çè¯å¥å°±ä¸æ§è¡ï¼ç´æ¥è¿å
     Â
   ä»ä¹æ¶åä¼åºç°undolog失败çæ åµï¼
   å½sqlä¾èµäºå¦å¤ä¸ä¸ªç»æçæ¶åï¼å¦å¦å¤ä¸ä¸ªç»æéè¦éè¿æç§å¤æ计ç®
   å¦æ计ç®åºæ¥çç»æä¸åæ³ï¼ä¸å¯¹çè¯ï¼å°±ä¼æ§è¡å¤±è´¥
  Â
   myisamæ¯æ²¡æundologçï¼å 为myisamä¸æ¯æäºå¡æä½
æ°æ®åºçé
   表级é   --   myisam    ï¼å¯¹ä¸æ¡æ°æ®è¿è¡æä½çæ¶åï¼ä¼éä½æ´ä¸ªè¡¨ï¼
   è¡çº§é   --   innodb Â
   页级é   --   ä¸ä¸ªé¡µæ¯4K
  Â
  Â
   innodbæ¯æäºå¡ï¼myisamä¸æ¯æäºå¡æ¯ä¸ºä»ä¹ï¼
      éè¦åå æ¯ï¼innodbæ¯è¡çº§éçï¼myisamæ¯è¡¨çº§éçï¼è¡¨çº§é没åæ³æ¯æäºå¡
      ä¾å¦ï¼
      myisamç¨äºå¡æ¥æä½çæ¶åï¼å æä½ä¸æ¡æ°æ®ï¼å·²ç»æ表ç»éä½äºï¼
      ä¸ä¸ä¸ªæ¥æä½æ°æ®ç就没æåæ³æä½ï¼å èæ æ³å®ç°äºå¡å¤ç
ç´¢å¼
ç´¢å¼æ¯ä»ä¹ï¼
   æ¯å¯¹æ°æ®åºè¡¨ä¸ä¸åæå¤åçå¼è¿è¡æåºçä¸ç§ç»æï¼ä½¿ç¨ç´¢å¼å¯å¿«é访é®æ°æ®åºè¡¨ä¸çç¹å®ä¿¡æ¯
 b+æ çç»æï¼æææ°æ®æ¯åå¨å¶åèç¹ä¸çï¼
   å¶åèç¹çæ°æ®æ¯éè¿é¾è¡¨ä¸²èµ·æ¥ç
     Â
   myisam æ¥è¯¢çæ¶åï¼æ¯ææ´é¢æ å ¨é¨å å ¥å åï¼æ¥è¯¢å¯»å°å ·ä½idï¼åå»ç£çæ¾æ°æ®
   对äºæ¥æ¾å ·ä½æ个æ°æ®çæ¶åæçå¾é«
  Â
   innodb ï¼æ¥è¯¢çæ¶åï¼æ¯ææ çä¸é¨åæ¾å ¥å åï¼æ¥è¯¢å®å°±å¯ä»¥ç´æ¥åæ°æ®ï¼
   对äºèå´æ¥è¯¢çæ¶åï¼æçæ¯è¾é«
  Â
innodbä¸æ¯æå ¨æç´¢å¼ï¼myisamæ¯æå ¨æç´¢å¼ï¼ä¸ºä»ä¹ï¼
   ä¾å¦æå ¥ä¸ç¯ææ¡£å°æ°æ®åºä¸ï¼innodbå 为æ¯ç´¢å¼åæ°æ®æ¾ä¸èµ·ï¼
   å¦ææ¯å ¨æç´¢å¼çè¯ï¼ç¸å½äºå¤å¶äºä¸ä»½æ°æ®åºï¼å èä¸æ¯æ
  Â
binlog -- ç¨äºåæ»ï¼ä¸æ¯äºå¡åæ»ï¼
   æ§è¡è¯å¥ä¼äº§çæ§è¡è®°å½ï¼åæ¾ insertï¼updateï¼deleteï¼
   mysql é群  ä»æºåæ¥ä¸»æºæ°æ®çæ¶åï¼æ¯å»è¯»ä¸»æºçbinlog
  Â
   ä»æºéé¢æ¯æä¸ä¸ªrelay logç
   relay log å¤ä»½binlogçå ¨é¨ï¼è¿æ¯å¤ä»½å¢éï¼
Â
linux C/C++ä¸Â 使ç¨ä»£ç æ¥æä½ mysqlï¼ä¸»è¦ç¨å°ä¸ä¸å 个å½æ°
å½æ°ååä¸ç»æä½
1.MYSQL *mysql_init(MYSQL *mysql)
2.MYSQL *mysql_real_connect(MYSQL *mysql,
3.int mysql_query(MYSQL *mysql, const char *stmt_str)
4.MYSQL_RES *mysql_use_result(MYSQL *mysql)
5.MYSQL_RES *mysql_store_result(MYSQL *mysql)
6.MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
7.void mysql_free_result(MYSQL_RES *result)
8.void mysql_close(MYSQL *mysql);
Â
åä¸ä¸ªç®åæ°æ®åºæä½çdemo
#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
int main() {
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
char server[] = "127.0.0.1";
char user[] = "root";
char password[] = "123123";
char database[] = "test_mysql";
conn = mysql_init(NULL);
//é¾æ¥æ°æ®åºï¼mysql conn 对象ï¼ipå°åï¼ç¨æ·åï¼å¯ç ï¼æ°æ®åºï¼
if(mysql_real_connect(conn, server, user, password, database, 0, NULL, 0) == NULL)
{
//å¦æé¾æ¥å¤±è´¥ï¼æå°é误å·ï¼åé误信æ¯
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
return -1;
}
//æ§è¡sqlè¯å¥ æ¥è¯¢æ´å¼ 表çä¿¡æ¯
if (mysql_query(conn, "select * from dev_userinfo")) {
printf("%s\n", mysql_error(conn));
return -1;
}
//å¾å°ç»æé
res = mysql_use_result(conn);
//ä¸è¡ä¸è¡çå°æ°æ®è¯»åºæ¥
while ((row = mysql_fetch_row(res)) != NULL) {
int count = res->field_count;//å¾å°ä¸è¡ä¸ï¼æå¤å°å
for(int i = 0 ;i<count;i++){
//æå°ä¸è¡ä¸æ¯ä¸åçå¼
printf("%s\t\t", row[i]);
}
printf("\n");
}
mysql_free_result(res);//éæ¾å
å
mysql_close(conn);//å
³éè¿æ¥
return 0;
}
Â