# 幫助help create user;# 查看當前用戶select user();# 創建用戶create user 'egon'@'localhost' identified by '123';create user 'alex'@'%' identified by '123';create ...
# 幫助
help create user;
# 查看當前用戶
select user();
# 創建用戶
create user 'egon'@'localhost' identified by '123';
create user 'alex'@'%' identified by '123';
create user 'ton'@'192.168.1.%' identified by '123';
# 授權(直接創建用戶後面加上identified by '123')
grant select on *.* to 'egon'@'localhost' identified by '123';
grant select (id,name),update (name) on db1.t1 to 'egon'@'localhost';
flush privileges;
# 回收許可權
revoke select on db1.* from 'egon'@'localhost';
# 許可權對應表
mysql.db/mysql.user/mysql.table_priv/mysql.columns_priv
update mysql.user set authentication_strings=password('123') where user='root' and host='localhost';
# 遠程連接
mysql -h192.168.1.1 -ualex -p123
# 增刪改查庫(文件夾)
create database db1 charset utf8;
show create database db1;
alter database db1 charset gbk;
drop database db1;
# 增刪改查表(文件)
use db1;
create table t1(id int primary key auto_increment,name char(10) not null unique)engine=innodb default charset utf8;
show tables;
show create table t1;
alter table t1 add age int;
alter table t1 drop age int;
alter table t1 modify id int primary key auto_increment;
alter table t1 charset utf8;
drop table t1;
# 查看表結構
describe t1; # desc
# 操作文件內容(增刪改查)
insert into db1.t1(id,name) values(1,'egon'),(2,'egon2');
truncate t1; # 數據量比較大情況下,刪除速度快
# 拷貝表結構和記錄(鍵不會拷貝),創建一個新表
create table t_copy select * from t1;
# 只拷貝表結構,不拷貝記錄
create table t_copy select * from t1 where 1=2;
# 查看編碼
\s
# 以行顯示
;前加上\G
一、數據類型
1、整數類型
TINYINT SMALLINT MEDIUMINT INT BIGINT
註意:為該類型指定寬度時,僅僅只是指定查詢結果的顯示寬度,與存儲範圍無關
create table t7(x int(3) zerofill);前面8位用0填充(預設int(11))
2、浮點型
定點數類型 DEC等同於DECIMAL
浮點類型:FLOAT DOUBLE
create table t4(salary float(5,2));#5代表有效數字位,2代表小數位
insert into t4 values (-3.33);
3、字元串類型(寬度既是字元數)
char:定長,簡單粗暴,浪費空間,存取速度快
varchar:可變長,精準,節省空間,存取速度慢
4、日期
datetime 2017-01-01 10:00:00
date:2017-01-01
time:10:00:00
year:2017
insert into student values(1,'alex',now(),now(),now(),now());
insert into student values(1,'alex','2017-01-01','2017','2017-01-01 10:00:00','10:00:00');
insert into student values(1,'alex','2017/01/01','2017','2017-01-01 10:00:00','10:00:00');
insert into student values(1,'alex','20170101','2017','20170101100000','100000');
5、枚舉與集合(set可取多個)
create table student(sex enum('male','female'),hobbies set('music','read'));
insert into student values('male','music,read');
insert值不在範圍內則存為空
二、表約束條件
1、not null、default
create table student(
sex enum('male','female')not null default 'male',
hobbies set('music','read'));
2、unique
#多列唯一
create table services(
name char(10),
host char(15),
port int,
constraint host_port unique(host,port));
3、auto_increment
auto_increment_offset偏移量(起始值),auto_increment_increment步長
#步長,對指定表生效
create table services(
id int primary key auto_increment)
auto_increment=10;
#查看變數
show variables like '%auto_in%';
#對當前回話生效
set session auto_increment_offset=200;
set session auto_increment_increment=2;
#所有回話生效
set global auto_increment_offset=200;
set global auto_increment_increment=2;
註意:偏移量不能大於步長,否則偏移量會被忽略,預設從1開始
4、foreign key (on delete cascade 刪除操作聯動)
create table emp_info(
...
dep_id int,
constraint fk_depid_id foreign key(dep_id) references dep_info(id)
on delete cascade
on update cascade
);
create table dep_info(
id int primary key auto_increment,
...
);
三、表查詢
1、單表查詢
1)group by
#聚合列
select depart_id,group_concat(name) from employee group by depart_id;
#計數
select depart_id,count(name) from employee group by depart_id;
#最大值,最小值min,sum,avg
select depart_id,max(name) from employee group by depart_id;
2)關鍵字的執行優先順序(重點)
from
where
group by
按照select後的欄位取新的虛擬表,有聚合函數則將組內數據進行聚合
having
distinct
order by
limit
3)limit(做分頁)
select * from emp limit 0,3;#起始,步長(1-3)
4)distinct
select distinct sex from emp;
5)select欄位上做運算
select name,salary*12 年薪(annual_salary) from emp;
2、多表查詢
1)簡單查詢
#笛卡爾積拼成一張虛擬表
select * from department,employee;
#內連接:按照on條件取相同部分
select * from department,employee where department.id=employee.dep_id;
select * from employee inner join department on department.id=employee.dep_id;
#左連接:按照on條件取相同部分再保留左表的記錄
select * from employee left join department on department.id=employee.dep_id;
#右連接:按照on條件取相同部分再保留右表的記錄
select * from employee right join department on department.id=employee.dep_id;
#full連接:按照on條件取相同部分再保留左右兩表的記錄
select * from employee left join department on department.id=employee.dep_id
union
select * from employee right join department on department.id=employee.dep_id;
2)子查詢
select * from employee where dep_id in (select id from department where name in ('技術','銷售'));
四、索引
1、原理
目的在於提高查詢效率,本質是不斷縮小想要獲取的數據的範圍來篩選結果。
2、索引的數據結構
B+樹是通過二叉查找樹,再由平衡二叉樹,B樹演化而來
###b+樹性質
1.索引欄位要儘量的小
2.索引的最左匹配特性
五、MySQL索引管理
1、MySQL常用的索引
普通索引INDEX:加速查找
唯一索引:
-主鍵索引PRIMARY KEY:加速查找+約束(不為空、不能重覆)
-唯一索引UNIQUE:加速查找+約束(不能重覆)
聯合索引:
-PRIMARY KEY(id,name):聯合主鍵索引
-UNIQUE(id,name):聯合唯一索引
-INDEX(id,name):聯合普通索引
2、索引的兩大類型hash與btree
hash類型的索引:查詢單條快,範圍查詢慢
btree類型的索引:b+樹,層數越多,數據量指數級增長(我們就用它,因為innodb預設支持它)
3、創建/刪除索引的語法
創建索引
創建表時:
create table s1(
id int,
name char(6),
index(id)#沒有約束的普通索引只能加在最後
);
創建表後:
create index name on s1(name);
create index id_name on s1(id,name);#聯合普通索引
create unique index name on s1(name);
刪除索引
drop index name on s1;
4、正確使用索引
1 範圍問題
2 儘量選擇區分度高的列作為索引
3 =和in可以亂序
4 索引列不能參與計算,保持列“乾凈”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很簡單,b+樹中存的都是數據表中的欄位值,但進行檢索時,需要把
所有元素都應用函數才能比較,顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’)
5 and/or
條件:
a = 10 and b = 'xxx' and c > 3 and d =4
索引:
製作聯合索引(d,a,b,c)
條件:
a = 10 or b = 'xxx' or c > 3 or d =4
索引:
製作聯合索引(d,a,b,c)
6 最左首碼匹配原則
聯合索引的第二個好處是在第一個鍵相同的情況下,已經對第二個鍵進行了排序處理
5、慢查詢優化的基本步驟
0.先運行看看是否真的很慢,註意設置SQL_NO_CACHE
1.where條件單表查,鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起,單表每個欄位分別查詢,看哪個欄位的區分度最高
2.explain查看執行計劃,是否與1預期一致(從鎖定記錄較少的表開始查詢)
3.order by limit 形式的sql語句讓排序的表優先查
4.瞭解業務方使用場景
5.加索引時參照建索引的幾大原則
6.觀察結果,不符合預期繼續從0分析
6、慢日誌管理
慢日誌
- 執行時間 > 10
- 未命中索引
- 日誌文件路徑
配置:
- 記憶體
show variables like '%query%';
show variables like '%queries%';
set global 變數名 = 值
- 配置文件
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf內容:
slow_query_log = ON
slow_query_log_file = D:/....
註意:修改配置文件之後,需要重啟服務
六、資料庫備份與恢復
#1. 物理備份: 直接複製資料庫文件,適用於大型資料庫環境。但不能恢復到異構系統中如Windows。
#2. 邏輯備份: 備份的是建表、建庫、插入等操作所執行SQL語句,適用於中小型資料庫,效率相對較低。
#3. 導出表: 將表導入到文本文件中。
1、使用mysqldump實現邏輯備份
#語法:
# mysqldump -h 伺服器 -u用戶名 -p密碼 資料庫名 > 備份文件.sql
#示例:
#單庫備份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql
#多庫備份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
#備份所有庫
mysqldump -uroot -p123 --all-databases > all.sql
二、恢復邏輯備份
#方法一:
mysql -uroot -p123 < /backup/all.sql
mysql -uroot -p123 db1 < /backup/db1.sql
#方法二:
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/db1.sql
三、備份/恢復案例
四、實現自動化備份
五、表的導出和導入
六、資料庫遷移
http://www.cnblogs.com/linhaifeng/articles/7525619.html
七、pymysql模塊
1、execute使用
pip3 install pymysql
import pymysql
user = input('user>>').strip()
pwd = input('password>>').strip()
conn = pymysql.connect(host='localhost',user='root',password='123456',database='day47',charset='utf8')
cursor = conn.cursor() # 游標既mysql>
#執行sql
sql = 'select * from user where user=%s and password=%s;'
rows = cursor.execute(sql, [user,pwd])
print('%s rows in set'%rows) #列印rows是受影響行數
cursor.close()
conn.close()
if row:
print('sucessful')
else:
print('failed')
2、增刪改
sql = 'insert into user(name,password) values(%s,%s);'
rows = cursor.executemany(sql, [(user,pwd),(user,pwd)]) #相當於自動迴圈列表執行execute
print(cursor.lastrowid)#查詢最後的自增長ID
print('%s rows in set'%rows) #列印rows是受影響行數
conn.commit()
3、查fetchone,fetchmany,fetchall,沒有返回None
sql = 'select * from user;'
rows = cursor.execute(sql)
res1 = cursor.fetchone()#元組
res2_4 = cursor.fetchmany(3)#元組嵌套元組,沒有返回None
print('%s rows in set'%rows) #列印rows是受影響行數
conn.commit()
4、游標移動
cursor.scroll(0,mode='absolute')#絕對位置
cursor.scroll(0,mode='relative')#相對位置
八、視圖
定義:視圖就是一張虛擬表,把經常查詢的sql語句存為視圖方便再次使用
1、語法:
create view user_dep
as
select user.id uid,user.name uname,dep.id depid,dep.name depname
from user left join dep on user.dep_id=dep.id;
#查看視圖
show create table user_dep;
#修改視圖
alter view user_dep as 新查詢語句;
九、觸發器
create
TRIGGER trigger_name
trigger_time trigger_event
on tb1_name for each row
begin
...;
end
delimiter //
create
TRIGGER tri_after_insert_cmd_log
after insert
on cmd_log for each row
begin
if new.is_success='no' then
insert into err_log(cmd_name,sub_time) values(NEW.cmd_name,NEW.sub_time);
end if;
end //
delimiter ;
#刪除觸發器
delete trigger trigger_name;
十、事務
事務用於將某些操作的多個SQL作為原子性操作,一旦有某一個出現錯誤,即可回滾到原來的狀態,從而保證資料庫數據完整性。
start transaction;
rollback; #如果任一sql異常,可用此命令回滾
commit; #如果所有sql正常,可用此命令提交
十一、存儲過程
存儲過程包含了一系列可執行的sql語句,存儲過程存放於MySQL中,通過調用它的名字可以執行其內部的一堆sql
1、無參數
delimiter //
create procedure procedure_name()
begin
...
end //
delimiter ;
#調用存儲過程
call procedure_name();
cursor.callproc('p1');
2、有參數
delimiter //
create procedure procedure_name(
in min int,
in max int,
out res int
)
begin
select * from test where id between min and max;
set res=1;
end //
delimiter ;
#調用存儲過程
set @n=1 #全局變數
call p1(3,7,@n);
select @n; #查看變數值
#python調用
import pymysql
conn = pymysql.connect(host='localhost',user='root',password='123456',database='db1',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
res = cursor.callproc('p1', args=(3,7,隨意)) #_p1_0=3,_p1_1=7,_p1_2=123
conn.commit()
print(res.fetchall()) #只能拿到存儲過程的select結果
rows = cursor.execute('select @_p1_0,@_p1_1,@_p1_2')
print(cursor.fetchall())
cursor.close()
conn.close()
#inout參數
delimiter //
create procedure p1(
inout m int
)
begin
select * from test where id > m;
set m=1;
end //
delimiter ;
#mysql
set @x=2
call p1(@x)
select @x
3、查看存儲過程
show create procedure p1\G
#查看所有存儲過程(某一類用like)
show procedure status like 'p1%';
4、刪除存儲過程
drop procedure proc_name;
十二、存儲過程應用於事務
delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
DELETE from tb1; #執行失敗
insert into blog(name,sub_time) values('yyy',now());
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表執行成功
END //
delimiter ;
#在mysql中調用存儲過程
set @res=123;
call p5(@res);
select @res;
#在python中基於pymysql調用存儲過程
cursor.callproc('p5',(123,))
print(cursor.fetchall()) #查詢select的查詢結果
cursor.execute('select @_p5_0;')
print(cursor.fetchall())
十二、函數
1、內置常用函數
1)數學函數
ROUND(x,y)
返回參數x的四捨五入的有y位小數的值
RAND()
返回0到1內的隨機值,可以通過提供一個參數(種子)使RAND()隨機數生成器生成一個指定的值。
2)聚合函數(常用於GROUP BY從句的SELECT查詢中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的個數
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由屬於一組的列值連接組合而成的結果
3)字元串函數
CHAR_LENGTH(str)
查看字元數。
CONCAT(str1,str2,...)
字元串拼接
如有任何一個參數為NULL ,則返回值為 NULL。
CONCAT_WS(separator,str1,str2,...)
字元串拼接(自定義連接符)
CONCAT_WS()不會忽略任何空字元串。 (然而會忽略所有的 NULL)。
CONV(N,from_base,to_base)
進位轉換
例如:
SELECT CONV('a',16,2); 表示將 a 由16進位轉換為2進位字元串表示
FORMAT(X,D)
將數字X 的格式寫為'#,###,###.##',以四捨五入的方式保留小數點後 D 位, 並將結果以字元串的形式返回。若 D 為 0, 則返回結果不帶有小數點,或不含小數部分。
例如:
SELECT FORMAT(12332.1,4); 結果為: '12,332.1000'
INSERT(str,pos,len,newstr)
在str的指定位置插入字元串
pos:要替換位置其實位置
len:替換的長度
newstr:新字元串
特別的:
如果pos超過原字元串長度,則返回原字元串
如果len超過原字元串長度,則由新字元串完全替換
INSTR(str,substr)
返回字元串 str 中子字元串的第一個出現位置。
LEFT(str,len)
返回字元串str 從開始的len位置的子序列字元。
LOWER(str)
變小寫
UPPER(str)
變大寫
REVERSE(str)
返回字元串 str ,順序和字元順序相反。
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不帶有len 參數的格式從字元串str返回一個子字元串,起始於位置 pos。帶有len參數的格式從字元串str返回一個長度同len字元相同的子字元串,起始於位置 pos。 使用 FROM的
格式為標準 SQL 語法。也可能對pos使用一個負值。假若這樣,則子字元串的位置起始於字元串結尾的pos 字元,而不是字元串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。
4)日期和時間函數
CURDATE()或CURRENT_DATE() 返回當前的日期
CURTIME()或CURRENT_TIME() 返回當前的時間
DAYOFWEEK(date) 返回date所代表的一星期中的第幾天(1~7)
DAYOFMONTH(date) 返回date是一個月的第幾天(1~31)
DAYOFYEAR(date) 返回date是一年的第幾天(1~366)
DAYNAME(date) 返回date的星期名,如:SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根據指定的fmt格式,格式化UNIX時間戳ts
HOUR(time) 返回time的小時值(0~23)
MINUTE(time) 返回time的分鐘值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名,如:SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回當前的日期和時間
QUARTER(date) 返回date在一年中的季度(1~4),如SELECT QUARTER(CURRENT_DATE)
WEEK(date) 返回日期date為一年中第幾周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)
5)加密函數
MD5()
計算字元串str的MD5校驗和
PASSWORD(str)
返回字元串str的加密版本,這個加密過程是不可逆轉的,和UNIX密碼加密過程使用不同的演算法。
6)控制流函數
CASE WHEN[test1] THEN [result1]...ELSE [default] END
如果test1是真,則返回result1,否則返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END
如果test和val1相等,則返回result,否則返回default
IF(test,t,f)
如果test是真,返回t;否則返回f
IFNULL(arg1,arg2)
如果arg1不是空,返回arg1,否則返回arg2
NULLIF(arg1,arg2)
如果arg1=arg2返回NULL;否則返回arg1
isnull(arg)
空返回true
內置函數重點:
1、char_length:查看字元數
select char_length(name) from t1;
#設置填充字元模式為全字元匹配模式
set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
2、length:查看位元組數
3、date_format
select date_format('2017-01-01 11:11:11','%Y-%m-%d %H:%i:%S')
example:
提取sub_time欄位的值,按照格式後的結果即"年月"來分組
SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');
4、自定義函數
delimiter //
create function f1(
x int,
y int)
returns int
begin
declare num int;
set num = x + y;
return (num);
end //
delimiter ;
#刪除函數
drop function func_name;
#執行函數
select UPPER('egon') into @res;
SELECT @res;
# 在查詢中使用
select f1(11,nid),name from tb2;
十三、流程式控制制
1、條件語句
delimiter //
create function f5(
i int
)
returns int
begin
declare res int default 0;
if i = 10 then
set res=100;
elseif i = 20 then
set res=200;
elseif i = 30 then
set res=300;
else
set res=400;
end if;
return res;
end //
delimiter ;
2、迴圈語句
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
# my.ini
[client]
default-character-set=utf8
user='root'
password='123456'
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
secure_file_priv='E:\\'