數據庫筆記總結

一、數據庫初識、表的操作

1. 數據庫初識

1.1 數據庫管理系統 -- DBMS

1.1.1 數據庫管理系統本質：管理一堆文件

它是網絡應用服務端
我們要使用服務端的數據 -- 需要有一個客戶端，客戶端可以自己寫，也可以用第三方工具、數據庫管理軟件的公司出版的官方客戶端
只是人家的管理方式比我們的更高效、更安全

1.2 數據庫管理員 -- DBA

搭建數據庫服務環境
用戶的創建權限的管理
性能\語句的優化
數據庫的第二次開發：讓數據庫具有公司的特質

1.3 數據庫的分類

關系型數據庫：mysql 、oorcle、sqlserver、sqlite
非關系型數據庫：redis mongidb memcache hbase

1.4 名詞

DB -- 數據庫 -- 文件夾
table -- 表 -- 文件
data --- 一條數據 -- 每一行數據

2. 表的操作

2.1 其他命令說明

mysqld install  -- 安裝sql服務
net start mysql   --  啟動服務
net stop mysql   --  停止服務
mysql -uroot(-u后面可以跟具體的用戶名) -p(可以加密碼)  
 	== mysql -uroot -p -h192.168.12.45      -- -h后面加的ip地址可以是別人的，也可以是自己的，可以訪問別人，也可以訪問自己
mysql>set password = password('123');      # 設置密碼
	# 在sql結尾輸入;表示整個sql語句結束，如果沒寫在換行之后補上即可
	# \c表示上一句不執行，然后退出    
create user '用戶名'@'%' identified by '密碼';     # 創建用戶

2.2 文件夾的操作

增：create database 數據庫名;       # 創建數據庫/文件夾
查：show databases;                 # 查看庫(文件夾下的所有庫)
	select datebase();               查看當前的數據庫
use  數據庫名(文件名\數據庫名)        # 切換到具體某個文件夾下/數據庫下

2.3 表/文件的操作

增：create table 文件名/表名(字段名1 數據類型(長度) 約束條件);    # 創建文件/表
查：show tables;           # 查看表
    desc 文件名/表名;        # 查看表結構
    describe 文件/表名         #查看表結構

2.4 數據操作

增：insert into 文件名/表名 values(參數1，參數2，參數3，...);      # 寫數據
刪：delate from 文件名/表名;                 # 刪除整個文件
    delate from  文件名 where num = 1;     # 刪除num= 1 的數據
改：update 文件名 set password = 'alex' where num = 1;            # 修改num = 1這行的密碼
查： select * from 文件名;                  # 查看文件里的所有數據

2.5 數據類型

2.5.1 數字類型

2.5.1.1 整型

tinyint -- 1個字節  帶符號(-128,127)   不帶符號(0,255)--unsigned約束
int -- 4個字節  帶符號(-2**31,2**31-1)   不帶符號(0,2**31-1)--unsigned約束
	 # create table t1(i1,tinyint,i2 int unsigned) # 默認創建的所有都是有符號的，加unsigned為無符號的

2.5.1.2 小數

float -- 單精度 -- 4個字節 
double -- 雙精度 -- 8個字節
	# create table t2(id1 float(7,2),id2 double(8,2));    #顯示小數和整數共7位，小數2位

##### 2.5.2 字符串

char -- 定長字符串 -- 0-255字節
varchar -- 變長字符串  -- 0-65535字節
# 注意：后面必須加長度限制
	# create table t5(v varchar(10),c char(10));

2.5.2 時間類型

date -- YYYY-MM-DD 年-月-日
time -- HH:MM:SS  時：分：秒
year -- YYYY   --年
datetime -- YYYY-MM-DD HH:MM:SS   年-月-日 時：分：秒
timestamp -- YYYYMMDD HHMMSS  年月日 時分秒  # 自動填充時間
	# create table t6(d date,t time,dt datetime);

2.5.3 enum 和set

enum -- 單選 -- 性別 
set -- 多選 -- 興趣愛好等
	# create table t7(name char(18),sex enum('male','female',));
	# create table t8(name char(18),hobby set('抽煙','喝酒','燙頭','翻車'));
    # insert into t8 values('yuan','燙頭','喝酒');
# 可以任意選擇set中的選項，并且自帶去重功能

二、數據操作、單表查詢

1. 數據操作

1.1 增

insert into emp(id,name) values(2,'wusir');                  # 指定添加一個
insert into emp(id,name) values(2,'wusir'),(3,'alex');       # 指定添加兩個
insert into emp2 select * from emp     # 先執行select將emp整個文件數據拿到再添加到emp2中
insert into emp2(id,name) select id,name from emp        # 將獲取到的emp中的id,name添加到emp2中

1.2 改

update 表 set 字段1 = 值1，字段2 = 值2 where 條件;

2.單表查詢

2.1 篩選內容（列）

select emp_name,salary from employee;        # 從整個數據中篩選出姓名和薪資

2.2 在列中使用四則運算

select emp_name, salary*12 from employee;       # 獲取姓名和相對應的年薪

2.3 重命名

select emp_name, salary*12 as annul_salary from employee;    # 將獲取到的年薪重命名為annul_salary

2.4 concat() 函數

select concat('姓名:',emp_name),concat('年薪:',salary*12) from employee;
	# 格式化的作用
select concat_ws('|','a','b','c');
	#  結果：a|b|c  與字符串的join作用相同，有拼接的作用

2.5 去重

select distinct post from employee;        # distinct為關鍵字，post為字段，多個字段去重時，取公共部分

2.6 where語句

2.6.1 比較運算

select * from employee where age > 18;       
	# 取年齡大于18的人的所有數據
select * from employee where salary between 10000 and 20000;    
	# 取薪資在10000到20000之間的人的所有數據

2.6.2 精準運算 -- in

select * from employee where salary in(17000,19000);
	# in是只取確定的值所相關的數據，確定數值，有就輸出，沒有就不輸出
# in(80,90,100) 值是80或90或100

2.6.3 模糊運算 -- like 、regexp

select * from employee where emp_name like 'jin___';
	# 結果：jinxin  jin后面有3個_，數據查找只能匹配到jinxin這個數據
select * from employee where emp_name like 'jin%';
	# 結果：jingliyang、jinxin         %能夠匹配所有，只要是jin開頭的
#  %表示匹配任意多字符，_ 表示匹配一個字符

2.6.4 邏輯運算 -- or 、and、not

select emp_name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000 ;

2.6.4 補充

關鍵字IS NULL(判斷某個字段是否為NULL不能用等號，需要用IS)

select emp_name,post_comment from employee where  post_comment is NULL;

3. 聚合函數

count -- 統計、計數
max -- 最大值
min -- 最小值
avg -- 平均值
sum -- 求和

4. 分組、過濾、排序

4.1 分組

select * from employee group by sex;             # 通過sex分組
分組聚合 -- select * from 表 where 條件 group by 分組；

4.2 過濾 -- having與聚合函數、分組搭配使用

select avg(salary) from employee group by post having avg(salary)>10000;
	# 查詢平均年薪資大于10000的部門

4.3 排序

單列排序：
select * from employee order by salary/salary asc;      # 升序
select * from employee order by salary desc;            # 降序 
雙列排序：
select * from employee order by age ,salary desc       # 在年齡相同的情況下，對年齡進行升序排列，對薪資進行降序排列

4.4 limit

select * from 表 order by 列 limit n;                # 取前n條數據
select * from 表 order by 列 limit m,n;               # 從m+1條開始，取n條
== select * from 表 order by 列 limit n offest m;     # 同上

三、pymysql 、多表查詢

1. pymysql

1.1 pymysql中的查操作

import pymysql 
conn = pymysql.connect(host = 'localhost',user = 'root','password' = '123','database' = 'day38')         # 建立連接(數據庫ip，用戶名，密碼，數據庫名)
cur =  conn.cursor(pymysql.cursors.DictCursor)   # 獲取游標
	# pymysql.cursors.DictCursor使用輸出的是字典的形式，不使用輸出的是元組
ret = cur.execute('select * from books')         # ret是影響行數，在這里表示查到7行數據
row1 = cur.fetchone()              # 每次讀取一行數據，能夠記錄當前的執行位置
row2 = cur.fetchmany(3)            # 按照制定參數取n條(如果之前有fetchone的話，是在fetchone取值的基礎上進行再取)
row3 = cur.fetchall()              # 取所有，過于浪費(如果有fetchone或fetchmany，是在這兩個基礎行再進行取全部的值) 
cur.close()
conn.close()

1.2 增、刪、改操作

import pymysql 
conn = pymysql.connect(host = 'localhost',user = 'root','password' = '123','database' = 'day38')         # 建立連接(數據庫ip，用戶名，密碼，數據庫名)
cur = conn.cursor()
sql = 'insert into books values(%s,%s,%s,%s,%s)'
with open('file',encoding = 'utf-8')as f:
	for line in f:
		try:
			lst = line.strip().split("|")
			cur.execute(sql,lst)
			conn.commit()             # 數據生效，每生成一條數據就提交一次
		except：
			conn.rollback()           # 回滾
cur.close()
conn.close()
# 使用異常，即使出現異常，回滾也是回滾當前的一次，之前的數據都已經正常提交了，也不會出現之前所有數據都異常的現象

2. sql注入

select * from userinfo where username = 'xxx'or 1=1 ;-- and password = 'xxxx'    
	# 當數據使用字符串格式化時，當select時定義任意的字符，當出現;--時 ，sql會認為是語句的結束，--后面的語句會當做注釋，設置的密碼不會被執行，當客戶端輸入任意的用戶名和密碼時都會登錄成功，所有會出現數據不安全的的現象
解決方法：
sql = 'select * from userinfo where username = %s and password = %s;'
ret = cur.execute(sql,(name,password))    
	# 使用pymysql中自帶的數據匹配進行select

3. 多表查詢

3.1 連表

3.1.1 內連接 -- 所有不在條件匹配內的所有數據，都會被剔出連表

方式一：select * from employee,department where dep_id = department.id;        
	# 通過兩個表中的id進行連表，任意一個表中沒有的數據，在連表中不會出現
方式二： select * from employee inner join department on dep_id = department.id;

3.1.2 外連接

左外連接 -- left join -- 以左邊的表為基準進行匹配，左邊表中的內容全部存在

select * from employee left join department on dep_id = department.id;

右外連接 -- right join -- 以右邊的表為基準進行匹配，右邊表中的內容全部存在

select * from employee right join department on dep_id = department.id;

全外鏈接

select * from employee left join department on dep_id = department.id
union
select * from employee right join department on dep_id=department.id;

3.2 多表查詢

以內連接的方式查詢employee和department表，并且employee表中的age字段值

必須大于25，即找出年齡大于25歲的員工以及員工所在的部門

select name,post from employee inner join department on dep_id = department.id where age > 25;
改別名：
select e.name ename,d.name dname from employee e inner join department d on dep_id = d.id where age > 25;

以內連接的方式查詢employee和department表，并且age字段的升序方式顯示

select * from employee inner join department on dep_id = department.id order by age ;

3.3 子查詢

查詢平均年齡在25歲以上的部門名

select dep_id from employee group by dep_id having avg(age) > 25;        # 查詢平均年齡在25歲

四、存儲引擎、約束

1. 存儲引擎 -- 存儲方式、存儲機制

1.1 定義：

表結構存在一個文件中(硬盤中)
表數據存在另一個文件中、內存中

索引(目錄)為了方便查找而設計的一個機制

1.2 存儲引擎的種類

1.2.1 innodb

特點：索引 + 數據、表結構數據的持久化存儲
特性：支持事務、行級鎖、外鍵
1. 事務：具有一致性，多條語句的執行狀態是一致的
```
begin()       # 開啟事務
select id from innot where id = 1 for update;
update innot set id = 2 where id = 1;
commit;       # 提交事務  解鎖被鎖住的事務，讓他們能夠被修改
```
2. 行級鎖：只對涉及到修改的行加鎖，利于并發的修改，但是對于一次性大量修改效率低下

表級鎖：一次性加一把鎖接鎖住了整張表，不利于并發的修改，但是加鎖速度比行級鎖效率要高
外鍵約束：被約束中的數據不能隨意的修改\刪除約束字段要根據被約束表來使用數據

1.2.2 myisam

特點：frm 表結構、MYD表數據、MYI表索引數據的持久化存儲
具有表級鎖

1.2.3 memory -- 只能做緩存

特點：表結構、數據斷電消失

2. 約束

2.1 非空約束 -- not null

create table t1(id int not null,name char(18));
	# 當整形不為空，但是沒有傳值時，默認插入0
create table t2(id int,name char(18) not null);
	# 字符串不為空，但是沒有傳值時，默認插入指定長度的空字符串

2.2 默認值 -- default

create table t3(id int,name char(18),sex enum('male','female')default 'male');
	# 默認性別為男

2.3 非空約束和默認值

create table t3(id int not null,name char(18)) not null,sex enum('male','female') not null default 'male');
	# 設置性別不能為空，并且默認為男

2.4 唯一約束 -- unique

create table t3(id int unique,name char(18));
	# 設置id為唯一條件

2.5 聯合唯一約束 -- unique(字段1，字段2)

create table t5(family char(12),name char(12),unique(family,name));
	# 將family和name拼起來進行比較是否唯一
create table t5(family char(12) not null,name char(12) not null,unique(family,name));
	# 約束各自不能為空，而且聯合唯一

2.6 唯一 + 非空

create table t6(id int not null unique ,name char(12) not null unique);
	# pri是如何產生的？第一個被設置了非空+唯一約束會被定義成主鍵primary key,而且主鍵在整張表中只能有一個

2.7 主鍵

create table t6(id int primary key,name char(12)) not null unique);
	# 設置id為主鍵，設置name不為空并且唯一
create table t5(family char(12),name char(12),primary key(family,name));
	# 約束各自不能為空且聯合唯一，還占用了整張表的主鍵

2.8 自增機制

create table t7(id int auto_increment,name char(12));
	# 報錯， 必須有unique或primary key的約束
create table t7(id int unique auto_increment,name char(12) primary key);
	# id滿足不為空，唯一，自增，name為主鍵
create table t7(id int primary key auto_increment,name char(12));
	# 設置id為主鍵并且唯一，自增
create table t7(id int unique auto_increment,name char(12)) auto_increment = 1000;
	# id自增從1000開始

delete from t7; # 清空表數據但是不能重置auto_increment
truncate table t7; # 清空表并且重置auto_increment
alter table 表名 auto_increment = n; # 修改表的auto_increment值

總結：所有操作都無法改變auto_increment的自動計數，但是我們沒有必要去改變它

2.9 外鍵

insert into class2 values(1,'py27');
insert into stu2 values(1,'日魔',1),(2,'炮手',1)
delete from class2 where cid = 1;
	# 報錯，根據創建的先后順序，得先刪除學生，然后才能刪除班級

2.10 級聯更新

create table class3(cid int primary key,cname char(12));
create table stu3(id int,name char(12),class_id int,foreigin key(class_id) references class(cid) on update cascade);
	# class_id設置外鍵
insert into class3 values(1,'py27');
insert into stu3 values(1,'日魔',1),(2,'炮手',1);
update class3 set cid = 2;
	# 修改了class3中的cid,stu3中相關的數據也會跟著變化，是on update cascade設置導致的

五、表的修改、表與表之間的關系、索引原理

1. 表的修改

alter table 表名 rename 表名；                 				# 給表名重命名
alter table 表名 charset 編碼；                				# 設置編碼
alter table 表名 auto_increment n;            			   # 設置自增的基數為n
alter table 表名 add 字段名 類型(長度) 約束；    			   # 表添加字段
alter table 表名 drop 字段名；                                 # 刪除指定的字段名
alter table 表名 change 字段名 新名字 類型(長度) 約束；          # 更改字段名，類型和約束不變
alter table 表名 modify 字段名 新類型(新長度) 約束；             # 更改指定字段的類型和長度
alter table 表名 change id id 類型(長度) 約束 first;            # 改變id字段的類型和長度并且放在第一位
alter table 表名 change id id 類型(長度) 約束 after age;        # 改變id字段的類型和長度并且放在age的后面
alter table 表名 add 字段名 類型(長度) 約束 first;               # 添加字段名和類型、長度并且放在第一位
alter  table 表名 add 字段名 類型(長度) 約束 after name;         # 添加字段名和類型、長度放在name字段的后面

2. 表與表之間的關系

2.1 一對多

create  table class(id int primary key,cname char(12));
create table student(id int primary key,sname char(16),cid int,foreign key(cid) reference class(id));
# class中的id設置為主鍵，student中的id設置為主鍵，將cid設置為外鍵和class表進行關聯

2.2 多對多

create table class(id int primary key ,cname char(12));
create table teacher(id int primary key,tname char(12));
create table teach_class(id int,cid int,tid int,foreign key(cid) reference class(id),foreign key(tid) references teacher(id));
# 設置class表中的id為主鍵，teacher表中的id為主鍵，多對多時會創建第三張表進行關聯，分別將兩張表中的id添加到teach_class表中，并且設置cid和tid為外鍵，和class表、teacher表進行關聯

2.3 一對一

create table guest(id int primary key,name char(12));
create table student(id int primary key,sname char(12),gid int unique,foreign key(gid) references guest(id));
# 分別設置guest中的id和student中的id為主鍵，設置gid是外鍵和guest中的id進行關聯并且gid唯一,這樣它才會實現一對一，如果沒有唯一限制，兩個表之間還是一對多的關系

2.3 索引原理

磁盤預讀性原理：1個block塊預讀4096個字節 --- Linux中
樹：有根節點 -- root、

? 分支節點 -- branch、

? 葉子節點 -- leaf

平衡樹 --- balance tree -- B樹

B+樹：是為了更好的處理范圍問題而在B樹的基礎上有所優化

--聚集索引/聚簇索引：葉子節點會存儲整行數據(把數據和索引樹存在一起的索引) -- innodb使用的是聚集索引
--輔助索引/非聚集索引：除了主鍵之外的普通索引都是輔助索引，一個索引沒辦法查到整行數據，需要回聚集索引再查一遍(回表)

mysql中innodb存儲引擎的所有的索引樹都是b+

六、索引： index、unique、primary key

1. 操作索引 -- 創建、刪除

1.1 創建索引

create index 索引名 on 表名(字段名);
	eg:create index ind_id on s1(id);
	   create index ind_id2 on s2(id);

1.2 刪除索引

drop index 索引名  on 表名;
	eg:drop index ind_id on 表名;

1.3 正確使用索引

只有對創建了索引的列進行條件篩選的時候效率才能提高
索引對應的列做條件不能參與運算、不能使用函數
當某一列的區分度非常小(重復率高)時，不適合創建索引
當范圍作為條件的時候，查詢結果的范圍越大越慢，越小越快
like 關鍵字：如果使用通配符(%、_)開頭都無法命中索引

多個條件：如果只有一部分創建了索引，條件用and相連，可以提高查詢效率

? 如果用or相連，不能提高查詢效率

and: 
	select count(*) from s1 where id=1000000 and email = '1050603958@qq.com';
or:
	select count(*) from s1 where id=1000000 or email = '1050603958@qq.com';

聯合索引

create index  ind_mix on s1(id,name,email);                                    
select count(*) from s1 where id=1000000 and email = '1050603958@qq.com';      # 快
select count(*) from s1 where id=1000000 or email = '1050603958@qq.com';       # 慢
	# 條件不能使用or 
select count(*) from s1 where id=1000000 ;                                     # 快
select count(*) from s1 where  email = '1050603958@qq.com';                    # 慢
	# 要服從最左前綴原則
select count(*) from s1 where id>1000000 and email = '1050603958@qq.com';      # 慢
	# 從使用了范圍的條件開始之后的索引都失效

1.4 基本概念

1.4.1 explain -- 執行計劃

explain select count(*) from s1 where id = 1000000 and email = '1050603958@qq.com';

1.4.2 覆蓋索引 -- using index

在查詢的過程中不需要回表

explain select count(*) from s1 where id < 100000;

1.4.3 索引合并

explain select count(*) from s1 where id = 1000000 or emaill = '1050603958@qq.com';

posted on 2019-10-19 11:22 馬云的小妹閱讀(184) 評論(0) 收藏舉報

刷新頁面返回頂部

導航

公告

數據庫筆記總結

一、數據庫初識、表的操作

1. 數據庫初識

1.1 數據庫管理系統 -- DBMS

1.1.1 數據庫管理系統本質：管理一堆文件

1.2 數據庫管理員 -- DBA

1.3 數據庫的分類

1.4 名詞

2. 表的操作

2.1 其他命令說明

2.2 文件夾的操作

2.3 表/文件的操作

2.4 數據操作

2.5 數據類型

2.5.1 數字類型

2.5.1.1 整型

2.5.1.2 小數

2.5.2 時間類型

2.5.3 enum 和set

二、數據操作、單表查詢

1. 數據操作

1.1 增

1.2 改

2.單表查詢

2.1 篩選內容（列）

2.2 在列中使用四則運算

2.3 重命名

2.4 concat() 函數

2.5 去重

2.6 where語句

2.6.1 比較運算

2.6.2 精準運算 -- in

2.6.3 模糊運算 -- like 、regexp

2.6.4 邏輯運算 -- or 、and、not

2.6.4 補充

3. 聚合函數

4. 分組、過濾、排序

4.1 分組

4.2 過濾 -- having與聚合函數、分組搭配使用

4.3 排序

4.4 limit

三、pymysql 、多表查詢

1. pymysql

1.1 pymysql中的查操作

1.2 增、刪、改操作

2. sql注入

3. 多表查詢

3.1 連表

3.1.1 內連接 -- 所有不在條件匹配內的所有數據，都會被剔出連表

3.1.2 外連接

3.2 多表查詢

3.3 子查詢

四、存儲引擎、約束

1. 存儲引擎 -- 存儲方式、存儲機制

1.1 定義：

1.2 存儲引擎的種類

1.2.1 innodb

1.2.2 myisam

1.2.3 memory -- 只能做緩存

2. 約束

2.1 非空約束 -- not null

2.2 默認值 -- default

2.3 非空約束和默認值

2.4 唯一約束 -- unique

2.5 聯合唯一約束 -- unique(字段1，字段2)

2.6 唯一 + 非空

2.7 主鍵

2.8 自增機制

總結：所有操作都無法改變auto_increment的自動計數，但是我們沒有必要去改變它

2.9 外鍵

2.10 級聯更新

五、表的修改、表與表之間的關系、索引原理

1. 表的修改

2. 表與表之間的關系

2.1 一對多

2.2 多對多

2.3 一對一

2.3 索引原理

一、數據庫初識、表的操作

二、數據操作、單表查詢

2.6.3 模糊運算 -- like 、regexp

4. 分組、過濾、排序

4.2 過濾 -- having與聚合函數、分組搭配使用

三、pymysql 、多表查詢

3.1.1 內連接 -- 所有不在條件匹配內的所有數據，都會被剔出連表

四、存儲引擎、約束

2.5 聯合唯一約束 -- unique(字段1，字段2)

總結：所有操作都無法改變auto_increment的自動計數，但是我們沒有必要去改變它

五、表的修改、表與表之間的關系、索引原理

六、索引： index、unique、primary key