MySQL資料庫操作

標籤：原始碼   功能   轉換   .sql   ide   方便   選擇   精度   nal   

 一、簡介Mysql是什嗎？

• MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，目前屬於Oracle 旗下產品。MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關聯式資料庫管理系統) 應用軟體。
• MySQL是將資料儲存在不同的表中，而不是將所有資料放在一個大倉庫內，這樣就增加了速度並提高了靈活性。
• MySQL所使用的 SQL 語言是用於訪問資料庫的最常用標準化語言。MySQL 軟體採用了雙授權政策，分為社區版和商業版，由於其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站資料庫。
• 由於其社區版的效能卓越，搭配 PHP 和 Apache可組成良好的開發環境

資料庫簡介：

　　是資料結構化的結合。電子化的檔案櫃，資料庫是指以一定的方式儲存在一起的資料的集合。

　　資料庫的組成：

　　　　資料庫由表組成，資料庫映射到系統中是一個目錄
　　　　表是由相關資料群組成，表映射到系統中是檔案
　　　　記錄是由一定數量不同屬性的欄位組成的

　　資料庫管理系統是為了管理資料庫而設計的電腦軟體，一般情況下有儲存、截取、維護及備份等功能。

資料庫的分類：

　　關係型資料庫

　　　　是建立在關聯式模式基礎上的資料庫

　　　　常見的關係型資料庫：
　　　　　　MySQL：開放原始碼
　　　　　　MariaDB：Mysql的替代品
　　　　　　Oracle：閉源
　　　　　　SQL Server：微軟的
　　　　　　DB2：IBM
　　　　　　Access：微軟公司

　　　　非關係型資料庫：
　　　　　　與關係型資料庫最明顯的區別：不再使用SQL作為查詢語言
　　　　　　MongoDB
　　　　　　hadoop

Mysql的結構:

　　C/S結構：用戶端/伺服器結構 用戶端和伺服器端都的裝軟體

　　CentOS7:
　　　　Server：mariadb-server
　　　　Client ：mariadb

　　RedHat6：
　　　　Server：mysql-server
　　　　Client ：mysql

二、登入、協助

　　MySQL預設情況下支援匿名登陸

匿名登陸

# mysql

給root使用者佈建密碼

# mysqladmin -u root passwordNew password: Confirm new password:

本地登陸：

# mysql -u root -p123456注意：-p後面一定沒有空格

遠程登陸：

# mysql -u root -h 192.168.1.2 -p123456

協助：

　 　　官方網站：http://www.mysql.com
　　　　　　　　 http://dev.mysql.com/doc

MySQL擷取用戶端協助

MariaDB [(none)]>help; 或者 ？ 或者 \h MariaDB [(none)]> create databases-> \c 清除當前輸入的sql語句，注意一定要在沒敲分號的時候使用MariaDB [(none)]> exit 退出 quit \q MariaDB [(none)]> use farm; 切換資料庫MariaDB [(none)]> \u testMariaDB [(farm)]> source qqfarm.sql; 執行sql指令碼MariaDB [test]> \. qqfarm.sqlMariaDB [(none)]> system ls; 調用系統命令MariaDB [(none)]> \! ls;

　　

忘記MySQL密碼修改設定檔

#vim /etc/my.cnf
[mysqld]skip-grant-table //添加 跳過許可權表datadir=/var/lib/mysql

重啟服務

# systemctl restart mariadb.service

登陸mysql

# mysql
MariaDB [(none)]> select user,password,host from mysql.user; //查看mysql庫中user表的user、password、host三個欄位+------+-------------------------------------------+-----------------+| user | password | host |+------+-------------------------------------------+-----------------+| root | *23AE809DDACAF96A | localhost || root | | ula.example.com || root | | 127.0.0.1 || root | | ::1 || | | localhost || | | ula.example.com |+------+-------------------------------------------+--------------------+MariaDB [(none)]> update mysql.user set password=password("123") where user="root" and host="localhost"; //更新密碼MariaDB [(none)]> exitBye

將設定檔中跳過許可權表的選項刪除或注釋

#vim /etc/my.cnf
[mysqld]#skip-grant-table datadir=/var/lib/mysql

重啟服務

# systemctl restart mariadb.service

　　

 

三、基本SQL語句分類：

　　DML Data Manipulation Language 資料庫操縱語言
　　　　INSERT 插入資料 insert
　　　　DELETE 刪除資料 delete
　　　　UPDATE 更新資料 update

　　DDL Data Definition Language 資料庫定義語言
　　　　資料庫、表、索引、預存程序、函數
　　　　CREATE 建立 create
　　　　DROP 刪除drop
　　　　ALTER 修改 alter

　　DCL Data Control Language 資料庫控制語言
　　　　例：控制使用者的存取權限
　　　　GRANT 授權 grant
　　　　REVOKE 撤銷 revoke

　　DQL Data Query Language 資料庫查詢語言
　　　　SELECT 查詢 select

基本的sql語句：

　　SQL語句中的大小寫問題：
　　　　1）關鍵字、函數名、列名和索引名不區分大小寫
　　　　2）資料庫的名字、表名、別名區分大小寫(因為linux是區分大小寫)

庫的操作

#查看所有庫show databases;#建立庫create database 庫名;#建立庫並指定字元集create database 庫名 DEFAULT CHARACTER SET utf8;#庫的字元集alter database databasename DEFAULT CHARACTER SET utf8;#查看庫的屬性　　　　　 show create database 庫名;#切換庫use apple;#刪除庫drop database apple;#查看現在使用的庫select database();

表的操作

#建立表create table t1(id int,name char(10));#查看錶的結構desc t1;#查看當前庫中所以表show tables;  #查看錶的屬性show create table t1\G;#添加欄位alter table 表名 操作 欄位名 資料類型 [約束];#追加alter table t1 add TEL int(11);#在name欄位後面插入sex欄位alter table t1 add sex char(10) after name;#添加欄位在第一列alter table t1 add UID int(10) first;#修改欄位名及屬性alter table t1 change id ID int(5);#僅修改欄位屬性alter table t1 modify ID int(10);#刪除欄位alter table t1 drop UID;  #修改表名alter table t1 rename to t2;  #刪除表drop table nametable;  

記錄的操作

#添加記錄insert into t1 set id=2,name=‘jim‘;insert into t1 (id,name) values(3,‘tom‘);#添加多條記錄insert into t1 (id,name) values(8,‘harry‘),(9,‘kitty‘);insert into t1 values (5,‘kiki‘),(6,‘jack‘),(7,‘banana‘);#所有欄位都要有資料insert into t1 values (4,‘apple‘);#更新記錄update t2 set age=18 where name is NULL;#滿足where後面的條件做什麼事update info_name set TEL="456789123" where NUM=2 ;#多條件定位，&&=and 兩個條件都滿足update info_name set SEX="m" where NUM=2 && AGE=18 ; #||=or  任意滿足其一條件update info_name set SEX="m" where NUM=3 || AGE=18;#刪除記錄delete from info_name where NAME=‘tom‘ and SEX=‘m‘;    delete from info_name where SEX is null;#清空表記錄delete from info_name;

　　

四、函數基本函數

#database()     查看當前所在的庫select database(); #user()     查看當前登陸的使用者select user();

彙總函式 

#sum()    求和函數select sum(age) from t1;#avg()    平均值函數select avg(age) from t1;#max()    最大值函數select max(age) from t1;#min()    最小值函數select min(age) from t1;#count()    統計sex欄位每種有多少條記錄select sex,count(sex) from t1 group by sex;#統計一下mysql成績在80分以上的人數select mysql,count(*) from score where mysql>80;#統計表中有多少條非空的記錄select count(*) from pass;

時間函數

#查看目前時間select curtime();#查看當前日期select curdate();#查看系統時間select now();select sysdate();

其他函數

#concat()    串連函數select concat(username,uid) from pass;select concat(username,‘ ‘,uid) from pass;select concat(username,‘:‘,uid) from pass;select concat(username,‘:‘,uid) as ‘username-uid‘ from pass;#lower()    轉換成小寫update pass set username=‘ROOT‘ where username=‘root‘;select lower(username) from pass;#upper()    轉換成大寫select upper(username) from pass;#length()    求長度 select username,length(username) from pass;

　　

五、記錄與表的複製

　　要求：有兩張表，但是欄位數不同

準備表

MariaDB [test]> create table t1(id int primary key,name varchar(20));MariaDB [test]> insert into t1 values (1,‘jim‘),(2,‘tom‘),(1000,‘jack‘);

記錄的複製

#複製全部記錄MariaDB [test]> insert into user1(id,name) select * from user;#複製部分記錄insert into user2(id,name) select * from user where id<1000;

複製表複製部分表（不可以複製主鍵、外鍵和索引）

MariaDB [test]> create table user3 select * from user;

完全複製表（可以複製表的結構）

MariaDB [test]> create table user4 select name from user;

只複製表的結構

　　方法一：不複製主鍵、外鍵、索引

MariaDB [test]> create table user5 select * from user where 1<0;

　　方法二：完全複製

MariaDB [test]> create table user6 like user;MariaDB [test]> select * from user6;

　　

六、資料匯入和匯出匯入資料

　　文法：load data infile ‘檔案名稱‘ into table 表名;（分割符為空白格後者定位字元）

load data infile "/opt/test.txt" into table test;

　　注意：1）檔案不要放在/tmp和/root目錄下，MySQL讀取不到。

　　　　　2）mysql使用者對匯入的檔案只要需要讀的許可權

　　　　　3）檔案用引號引起來

　　文法：load data infile ‘檔案名稱‘ into table 表名 fields terminated by ‘分隔字元‘；（任何分隔字元）

load data infile ‘/opt/pass.txt‘ into table pass fields terminated by ‘:‘;

匯出資料

select *from passwd into outfile ‘/test/pass‘ fields terminated by ‘:‘;

　　注意：1）匯出前檔案名稱一定不存在。

　　　　　2）mysql使用者對目錄要有讀寫權限

　　　　　3）匯出檔案預設以|t為欄位分隔符號，\n為行分隔字元

 

七、使用者權限管理查看使用者

MariaDB [(none)]> select user,host,password from mysql.user;

刪除使用者

　　文法：drop mysql.user 使用者名稱@‘主機‘;

MariaDB [(none)]> drop user [email protected]‘ula.example.com‘;MariaDB [(none)]> delete from mysql.user where user!=‘root‘;　　

 建立使用者

　　建立無限制無密碼無ip限制使用者

MariaDB [(none)]> create user ‘sonfer‘;

　　建立本機使用者並設定密碼

create user [email protected] identified by ‘sonfer123‘;

　　建立遠程登陸的使用者

　　　　文法：create user 使用者名稱@‘ip‘ identified by ‘sonfer123‘;

create user [email protected]‘192.168.1.71‘ identified by ‘sonfer123‘;

　　注意：允許所有IP登陸不是‘%‘，應該寫‘%.%.%.%‘，可能是MariaDB的一個BUG。

設定密碼使用mysqladmin命令

[[email protected] mysql]# mysqladmin -u root -p123 password ‘456‘

使用set password命令

MariaDB [(none)]> set password for ‘root‘@‘localhost‘=password(‘123‘);

更新表記錄

MariaDB [(none)]> update mysql.user set password=password(‘2‘) where user=‘root‘ and host=‘localhost‘;MariaDB [(none)]> flush privileges;

用戶端登陸伺服器

　　文法：mysql -u使用者名稱 -p密碼 -h伺服器IP

mysql -usonfer-h 192.168.1.1 -psonfer123

　　

許可權

• 全域層級

　　　　全域許可權適用於一個給定伺服器中的所有資料庫。這些許可權儲存在mysql.user表中。

• 資料庫層級

　　　　資料庫許可權適用於一個給定資料庫中的所有目標。這些許可權儲存在mysql.db和mysql.host表中。

• 表層級

　　　　表許可權適用於一個給定的表中的所有列。這些許可權儲存在mysql.tables_priv表中。

• 列層級

　　　　列許可權適用於一個給定的表中的單一列。這些許可權儲存在mysql.colummns_priv表中。

授權

　　文法：grant 許可權 on 庫名.表名 to 使用者名稱@‘主機名稱‘ identified by ‘密碼‘; 　　（修改密碼，可建立使用者）

　　許可權列表　　　

1. alter
2. create
3. delete
4. drop
5. index
6. insert
7. select
8. update
9. file　　　                 讀或寫伺服器上的檔案
10. all　　　　              所有許可權
11. show databases

授予全部許可權

MariaDB [(none)]> grant all on *.* to [email protected] identified by ‘test‘;MariaDB [(none)]> flush privileges;  //重新整理許可權

授予部分許可權

MariaDB [(none)]> grant select,insert,update on test.* to [email protected]‘192.168.1.%‘ identified by ‘123‘;

查看目前使用者許可權

MariaDB [(none)]> show grants;

查看其他使用者權限

MariaDB [(none)]> show grants for sonfer;

　

授權經驗原則（ 許可權控制主要出於安全考慮，因此需要遵循一個幾個經驗原則）

1. 只授予能滿足需要的最小許可權，防止使用者幹壞事。比如說使用者只是需要查詢，那就只給select許可權就可以了。不要給使用者賦予update，insert或者delete。
2. 建立使用者的時候限制使用者的登陸主機
3. 初始化資料庫的時候刪除沒有密碼的使用者。
4. 為每個使用者佈建滿足密碼複雜度的密碼。
5. 定期清理不需要的使用者。

回收許可權

　　文法：revoke 許可權 on 庫名.表名 from 使用者@‘主機‘;

MariaDB [(none)]> revoke all on *.*  from  [email protected]‘%‘;      //回收全部許可權MariaDB [(none)]> revoke insert on *.*  from  [email protected]‘%‘;   //回收部分許可權

　　

八、備份備份的分類　類型

　　　根據服務是否線上繼續分為熱備份、溫備份和冷備份
　　　　　熱備份：讀寫均不受影響
　　　　　溫備份：僅可以執行讀操作
　　　　　冷備份：也稱為離線備份，讀寫操作均終止。

　　　根據備份時是否之間複製資料檔案分為物理備份和邏輯備份
　　　　　物理備份：直接複製資料檔案，速度快。
　　　　　邏輯備份：通常是將資料匯出至文字檔中。速度慢、丟失浮點精度；方便使用文本根據直接進行處理、可移植能力強。

　　　根據備份資料內容分為完全備份、增量備份和差異備份
　　　　　完全備份：備份全部資料。
　　　　　增量備份：僅備份上次完全備份或增量備份以後變化的資料。
　　　　　差異備份：僅備份上次完全備份以來變化的資料

　　   備份時間的選擇：資料庫訪問量較小的時候做備份

 

使用mysqldump備份備份單個資料庫

　　文法：mysqldump -u root -p2 庫名 > /tmp/name.sql

[[email protected] mysql]# mysqldump -u root -p2 data1 > /tmp/data1.sql

備份單個表
　　文法：# mysqldump -u root -p2 庫名 表名 > /tmp/name.sql

備份多個表
　　文法：mysqldump -u root -p2 庫名 表名 表名 > /tmp/name.sql

多庫備份 -B,--databases
　　文法：mysqldump -u root -p2 -B 庫名 庫名 > /tmp/name.sql

全庫備份 -A, --all-databases
　　文法：mysqldump -u root -p2 -A > /tmp/name.sql

 

恢複單庫恢複

MariaDB [(none)]> drop database data1;   //刪庫MariaDB [(none)]> create database data1; //再建庫#第一種恢複方式：[[email protected] mysql]# mysql -u root -p2 data1 < /tmp/data1.sql #第二種恢複方式：[[email protected] mysql]# mysql -u root -p2MariaDB [(none)]> use schoolMariaDB [school]> source /tmp/data1.sql

表恢複

[[email protected] mysql]# mysql -u root -p2 school < /tmp/table.sql

多庫恢複(恢複之前是不需要手動建立資料庫的)

[[email protected] mysql]# mysql -u root -p2 < /tmp/ddata.sql

全庫恢複

[[email protected] mysql]# mysql -u root -p2 < /tmp/all.sql

 

九、日誌管理四種日誌

日誌存放的位置：資料目錄下
/var/lib/mysql/ rpm

　　錯誤記錄檔：記錄mysql伺服器的啟動、運行和關閉過程。

/var/log/mariadb/mariadb.logMariaDB [(none)]> show variables like ‘log_error‘;+---------------+------------------------------+| Variable_name | Value | +---------------+------------------------------+| log_error | /var/log/mariadb/mariadb.log |+---------------+------------------------------+1 row in set (0.03 sec)

　　二進位日誌

　　　　記錄使用者對資料庫所做的所有的ddl和dml操作，不會記錄查詢語句，對資料的災難恢複有至關重要的作用。
　　　　ddl：create、drop、alter
　　　　dml：insert、update、delete
　　　　二進位日誌作用：做AB複製、恢複

　　通用查詢日誌：general_log

　　　　記錄所有對資料庫的請求資訊(包括登陸、查詢)，預設不開啟
　　　　日誌量較大、佔用磁碟空間，一般不開啟

　　慢查詢日誌：

　　　　記錄的是查詢時間超過指定時間的查詢語句，一般用於最佳化查詢，預設不開啟

 

開啟各種日誌，修改設定檔開啟通用查詢日誌臨時開啟，重啟服務失效

MariaDB [(none)]> show variables like ‘general_log‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log | OFF |+---------------+-------+S1 row in set (0.00 sec)MariaDB [(none)]> set global general_log=on;MariaDB [(none)]> show variables like ‘general_log‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log | ON |+---------------+-------+1 row in set (0.00 sec)

通用查詢日誌/var/lib/mysql/短主機名稱.log

[[email protected] mysql]# cat /var/lib/mysql/name.log 

永久設定

[[email protected] mysql]# vim /etc/my.cnflog=general_select_log //=號右邊的記錄檔名可自訂[[email protected] mysql]# systemctl restart mariadb[[email protected] mysql]# cat /var/lib/mysql/general_select_log 

開啟慢查詢日誌

[[email protected] mysql]# vim /etc/my.cnflog-slow-queries=slow-log //=號右邊的記錄檔名可自訂long_query_time=5 //設定慢查詢逾時時間 單位是：秒[[email protected] mysql]# systemctl restart mariadb[[email protected] mysql]# mysql -u root -p2MariaDB [(none)]> select sleep(6); //執行一個超過5秒的查詢操作[[email protected] mysql]# cat /var/lib/mysql/slow-log 

開啟二進位日誌

[[email protected] mysql]# vim /etc/my.cnflog-bin=log_bin.log //=號右邊的記錄檔名可自訂log-bin-index=log_bin.index[[email protected] mysql]# systemctl restart mariadb

二進位日誌的查看 mysqlbinlog

[[email protected] mysql]# mysqlbinlog log_bin.000001 # at 4 //事件發生的位置資訊#170606 14:51:00 //事件發生的時間資訊

使用二進位日誌進行資料恢複使用位置點進行恢複

[[email protected] mysql]# mysqlbinlog --start-position=245 --stop-position=534 log_bin.000001 |mysql -u root -p2--start-position 起始位置點--stop-position 結束位置點

使用事件發生的時間

# mysqlbinlog --start-datetime=‘17-06-06 15:25:04‘ --stop-datetime=‘17-06-06 15:26:15‘ log_bin.000002 | mysql -u root -p2--start-datetime 起始時間--stop-datetime 結束時間

查看日誌資訊

MariaDB [(none)]> show master logs;+----------------+-----------+| Log_name | File_size |+----------------+-----------+| log_bin.000001 | 1079 || log_bin.000002 | 1523 |+----------------+-----------+2 rows in set (0.00 sec)

查看二進位日誌裡的操作記錄

MariaDB [(none)]> show binlog events;

查看當前使用的二進位記錄檔及所處的位置

MariaDB [(none)]> show master status\G;

　　

對日誌的管理刪除日誌 （不要輕易刪除）刪除某個記錄檔之前的所有日誌

MariaDB [(none)]> purge master logs to ‘log_bin.000002‘;Query OK, 0 rows affected (0.05 sec)

刪除多少天以前的記錄檔

MariaDB [(none)]> purge master logs before date_sub(current_date,interval 1 day);

重設二進位日誌

MariaDB [(none)]> reset master;

手動產生新的二進位記錄檔

MariaDB [(none)]> flush logs;

　　

MySQL資料庫操作