MySQL學習筆記No.01

SQL語句分類：

DDL語句

DDL是數據定義語句,是對數據庫內部的對象進行創建、刪除、修改等操作的語句.create、drop、alter等(DBA常用)

DML語句

DML是數據操作語句,指對數據庫表記錄的基本操作，insert、update、delete、select等(開發常用)

DCL語句

DCL是數據控制語句,用於控制不同數據段直接的許可和訪問級別的語句.定義了數據庫、表、字段、用戶的訪問權限和安全級別.主要是grant、revoke等(DBA常用)

數據庫基本操作

數據庫的增刪改查

庫操作

創建庫

mysql > create database db1;
​

查看庫

mysql > show databases; 
mysql > show create databasee db1; //更詳細的查看庫

使用庫

mysql > use db1;

表操作

創建表

mysql > create tables 表名(字段 類型(修飾符))

查看錶

查看所有表：mysql > show tables ;
查看錶記錄：mysql > show table status like '表名' ；
查看錶結構：mysql > desc '表名' ；
查看錶記錄：mysql > select * from '表名' ;

修改表

修改表名：mysql > rename table '源表名' to '新表名' ；

字段

添加字段：mysql > alter table '表名' add '字段' '修飾符';
刪除字段：mysql > alter table '表名' drop '字段' ；
修改字段：mysql > alter table '表名' change '舊字段' '新字段' '修飾符';

記錄

添加記錄：insert into '表名(記錄，記錄)' values (),(),();
 ：insert into '表名' values (),(),();
修改記錄：update '表名' set 字段='' where 字段='';
刪除記錄：delete from '表名' where '字段'='' ;

查詢操作

查詢

簡單查詢：select * from '表名';
避免重複：select distinct '字段' from '表名';
條件查詢：select 字段,字段 from 表名 where id<=5(條件);
四則運算查詢：select id,dep_id,id*dep_id from company.employee5 where id<=5;
定義顯示格式一：select id*dep_id as "id and dep_id's sum" from company.employee5 where id<=5;
定義顯示格式：SELECT CONCAT(name, ' annual salary: ', salary*14) AS Annual_salary FROM employee5; //定義格式+四個運算，CONCAT是關鍵字
多條件：select '字段，字段‘ from '表名' WHERRE '條件一' AND '條件二';
關鍵字between and：
select '字段，字段' from '表名' where 字段 BETWEEN 

'條件一' AND '條件二';
​
​
----------
排序查詢：
select '字段' from '表名' ORDER BY '排序字段'；//字段後加DESC正序，ASC反序
​
限制查詢的記錄數：
select '字段' from '表名' ORDER BY '字段，DESC|ACS' LIMIT '數字'; //數字有兩種的是(5,從初始位置到第五個)(2,5,第三個開始，共顯示五個)
​
使用集合的查詢：
select COUNT(*) from '表名'；
select COUNT(*) FROM '表名' WHERE dep_id=101;
select MAX(salary) FROM '表名';
select MIN(salary) FROM '表名';
select AVG(salary) FROM '表名';
select SUM(salary) FROM '表名';
select SUM(salary) FROM '表名' WHERE dep_id=101;
​
分組查詢：
select '字段' from '表名' group by 字段; //可參考下列面試題
​
模糊查詢：
select '字段' from '表名' LIKE '關鍵字'；
​
正則表達式查詢：
select * from '表名' where '字段' REGEXP '關鍵字'； 
​

刪庫

刪庫：drop database '庫名'； 

刪表：drop table '表名'

高級操作

連接數據庫

# mysql -uroot -p -h10.18.44.209 -p3306
​
授權
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

修改數據庫密碼

# vim /etc/my.cnf 追加
validate_password=off
# systemctl restart mysqld
方法一：
mysql > SET PASSWORD FOR user3@'localhost'='new_password';
mysql > flush privileges;
方法二：
mysql > update mysql.user set password=password('newpassword') where user='root';
mysql > flush privileges;
方式三：
mysql > set password for 'root'@'localhost'=password('newpassord');

編譯安裝需要參數

​
[[email protected] ~]# cmake . \\
-DWITH_BOOST=boost_1_59_0/ \\ 指定boost目錄
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \\ 指定安裝目錄
-DSYSCONFDIR=/etc \\ 配置文件位置  

-DMYSQL_DATADIR=/usr/local/mysql/data \\ 數據目錄（非常重要 錯誤日誌、數據存放位置）
-DINSTALL_MANDIR=/usr/share/man \\ man手冊
-DMYSQL_TCP_PORT=3306 \\ 默認端口3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \\ sock文件位置，用來做網絡通信的 
-DDEFAULT_CHARSET=utf8 \\ 字符集的支持 默認
-DEXTRA_CHARSETS=all \\ 字符集的支持 擴展支持
-DDEFAULT_COLLATION=utf8_general_ci \\ 字符集的支持 設定默認排序規則
-DWITH_READLINE=1 \\ 可以上下泛歷史命令 
-DWITH_SSL=system \\ 使用證書登陸 （安全 但是影響傳輸速度） 
-DWITH_EMBEDDED_SERVER=1 \\ 編譯嵌入式服務器支持 
-DENABLED_LOCAL_INFILE=1 \\ 支持從本機導入
-DWITH_INNOBASE_STORAGE_ENGINE=1 默認引擎 數據如何存儲的庫
​

msyql中now()、sysdate()、curdate()區別

SELECT date_sub(date_sub(date_format(now(),'%y-%m-%d '),interval extract( day from now())-1 day),interval 1 month)
上個月第一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract( day from now()) day),interval 0 month)
上個月最後一天
​
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract( day from now())-1 day),interval 0 month)
這個月第一天

Mysql日誌管理

error log 錯誤日誌 排錯 /var/log/mysqld.log【默認開啟】
bin log 二進制日誌 備份 增量備份 DDL DML DCL 

Relay log 中繼日誌 複製 接收 replication master 
slow log 慢查詢日誌 調優 查詢時間超過指定值
​
Error Log
log-error=/var/log/mysqld.log
Binary Log(用於備份恢復數據)
產生binlog日誌：
log-bin=/var/log/mysql-bin/slave2
serve-id=2
​
# mkdir /var/log/mysql-bin/slave2
#chmod mysql.mysql /var/log/mysql-bin/slave
#systemctl restart mysqld
​
​
1. 重啟mysqld 會截斷舊日誌產生新的日誌
2. 刷新日誌會截斷舊日誌產生新的日誌
mysql> flush logs 
3. 刪除所有binlog（禁用）
mysql> reset master
4. 刪除部分日誌
mysql> PURGE BINARY LOGS TO 'mysql-bin.010';
mysql> PURGE BINARY LOGS BEFORE '2016-04-02 22:46:26';
5. 暫停binlog日誌功能（僅對當前會話生效）
mysql> SET SQL_LOG_BIN=0;
mysql> SET SQL_LOG_BIN=1;
​
讀取binlog日誌：
# mysqlbinlog mysql.000002
​
按datetime讀取：
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2018-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" --stop-datetime="2018-12-05 11:02:54" 
 
按position讀取：
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 

​
查看帶加密的binlong日誌
mysqlbinlog --base64-output=decode-rows -v 日誌文件
​
根據binlog恢復數據：
根據時間點恢復數據
# mysqlbinlog --start-datetime='2014-11-25 11:56:54' --stop-datetime='2014-11-25 11:57:41' tiger-bin.000001 | mysql -u root -p1
​
根據位置點恢復數據
# mysqlbinlog --start-position 106 --stop-position 527 tiger-bin.000001 | mysql -u root -p1 
​
刷新bin-log日誌:
#mysqladmin flush-logs
​
去除binlog加密：
transaction_isolation=repeatable-read
binlog_format=mixed
​
慢查詢：
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3
​
# mkdir /var/log/mysql-slow/
# chown mysql.mysql /var/log/mysql-slow/
# systemctl restart mysqld
​
查看慢查詢日誌
測試:BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);
​