知識點

△用資料庫的原因

1文件操作的複雜度
2同步
3併發處理
4安全

△資料庫系統（DBS）
資料庫(DB) + 資料庫管理系統 (DBS)+ 資料庫應用程式 + 資料庫管理員 (BDA)+ 最終用戶

△資料庫管理系統-DBM
網路應用服務端
我們要使用服務端的數據 - 需要有一個客戶端
客戶端可以自己寫 : 未來寫代碼的時候
也可以用別人寫好的 : 第三方的工具 資料庫管理軟體的公司出版的官方客戶端
資料庫管理系統本質上也是管理一堆文件
只不過人家的管理方式比我們更高效 更安全

△資料庫管理員-DBA

搭建資料庫服務環境
用戶的創建 許可權的管理
性能\語句的優化
資料庫的二次開發 : 讓資料庫具有公司的特質

△軟體

mysql : 小公司
甲骨文 oracle : 事業單位 金融企業
微軟 sql server
sqllite

△資料庫的分類

關係型資料庫 mysql oracle sqlserver sqllite
非關係型資料庫 redis mongodb memcache hbase

關係型資料庫

優點:

1、易於維護：都是使用表結構，格式一致
2、使用方便：SQL語言通用，可用於複雜查詢
3、複雜操作：支持SQL，可用於一個表以及多個表之間非常複雜的查詢
缺點:

1、讀寫性能比較差，尤其是海量數據的高效率讀寫
2、固定的表結構，靈活度稍欠
3、高併發讀寫需求，傳統關係型資料庫來說，硬碟I/O是一個很大的瓶頸

非關係型資料庫

優點：
1、格式靈活：存儲數據的格式可以是key,value形式、文檔形式、圖片形式等等
2、速度快，成本低：nosql資料庫部署簡單，基本都是開源軟體

缺點：
1、不提供sql支持，學習和使用成本較高
2、無事務處理
3、數據結構相對複雜，複雜查詢方面稍欠

△ SQL是關係資料庫管理系統的標準語言。

△名詞

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

△三種方法操作資料庫

終端，python，第三方工具

△二.mysql編碼問題

編碼問題
1.臨時解決問題在客戶端執行set XXXX = utf8;
2.永久解決問題fimy. ini添加set xXxx = utf8;
3.實時解決問題create table表名() charset=utf8;

△服務端-安裝啟動關閉

mysqld install    #安裝
net start mysql   #啟動  以管理員身份在cmd中輸入
net stop mysql    #關閉

creat user 'eva'@'%'identified by'123';
#創建用戶eva 在所有網段允許 設置密碼為123

△

1,先跟資料庫進行連接(用賬號和密碼,預設3306埠)

2,創建資料庫:

​ create database 資料庫名:
​ 刪除資料庫（慎用！）：
​ DROP DATABASE 資料庫名稱；
​ 顯示所有資料庫：
​ SHOW DATABASES;
​ 切換資料庫：
​ USE 資料庫名稱 ;

3.創建表:

4.查看表結構

礎數據類型

數字類型

整數 :

tinyint(m) 1個位元組 -128~127

smalint(m) 1個位元組 -32768~32767

==mediumint== 3個位元組 -8388608~8388608

==int(m)== 4個位元組 -2147483648~2147483647

bigint(m) 8個位元組 -9223372036854775808~9223372036854775807

m: 顯示寬度,和數據類型的取值範圍是無關的

(int unsigned) : unsigned 標上表示無符號

小數 :

MySQL中使用浮點數和定點數來表示小數。它們都可以用(m,n)來表示，其中m稱為精度，表示總共的位數；n稱為標度，是表示小數的位數（會四捨五入）

==float==(m,d) 單精度浮點型,4位元組 -3.402823466E+38~-1.175494351E-38

==double==(m,d) 雙精度浮點型,8位元組 -1.7976931348623157E+308~-2.225738585072014E-308

decimal(m,d) 壓縮嚴格的定點數,m+2個位元組 最大取值範圍與double一致

時間類型

==year== 年 酒的產期

==time== 時:分:秒 計時軟體

==date== 年-月-日 入職日期 出賬日期 還款日期

==datetime== 年-月-日 時:分:秒 1000-01-01 00:00:00~9999-12-31 23:59:59 日誌,消費記錄,上下班打卡

timestamp 年-月-日 時:分:秒 不能為空，自動寫當前時間，級聯更新，範圍小

==now==() 函數表示當前時間

預設當前時間並級聯更新

讓datetime 自動寫當前時間，級聯更新
create table 表(
列 datetime NOT NULL         #非空
DEFAULT CURRENT_TIMESTAMP    #自動寫當前時間
ON UPDATE CURRENT_TIMESTAMP  #級聯更新當前時間
)

字元串類型

==char==(255) ：定長的 節省時間 浪費空間 手機號碼、身份證號

==varchar==(65535) ： 變長的 節省空間 浪費時間 評論

枚舉和集合

==enum== 單選

==set== 多選，自動去重

例如建表時定義欄位
愛好 set（“游泳”，“籃球”，“下棋”，“音樂”，“旅游”）
insert into t_1 values("游泳，籃球，音樂")
添加時會自動去重和去掉不存在的

舉例-數字 字元串

create table t1(i1 tinyint,i2 int); #預設創建的數據類型都是有符號的  i1-表頭
create table t2(i1 tinyint unsigned,i2 int unsigned); #給欄位添加一個unsigned表示無符號
create table t3(f1 float,f2 double);

mysql> create table t1(
    id int,name varchar(20),
    sex enum('man','wuman'),
    age int,
    hire_date date,
    post varchar(20),
    post_comment varchar(20), #一般在用char varchar時要限制字元
    salary double(7,2),
    office int,
    depart_id int); 32

舉例-小數

create table t3(f1 float,f2 double);
create table t4(f1 float(7,2));  小數點後2位,小數點前5位

舉例-時間

create table t7(dt datetime NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ TIMESTAMP,y year);  
  NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ TIMESTAMP   #讓datetime的時間在無數據填充的情況下會自動更新到此次修該的時間

舉例-枚舉和集

create table t8(username cha(12),gender enum('male','female'));
create table t8(username cha(12),hobby set('喝酒','抽煙','燙頭','洗腳'));
填充的時候也必須時字元串格式,添加時會自動去重和去掉不存在的

數據操作之前

在維護和操作時，一定要先備份一份，之後再去操作資料庫

會遭遇各的數據丟失的情況:
硬體故障
軟體故障
自然災害
黑客攻擊
誤操作 (占比最大)

備份

1，在MySQL的bin目錄中打開命令視窗（bin目錄中才有複製的命令）
2，輸入：mysqldump –uroot –p test101 > C :\t1.sql （如果有警告可以忽略）

恢復

連接MySQL，創建資料庫
在MySQL的bin目錄中打開命令視窗
輸入：mysql –uroot –p 資料庫名 < d:\t1.sql

管理員賬戶設置密碼

# 預設用戶登陸之後並沒有實際操作的許可權
# 需要使用管理員root用戶登陸

# mysql -uroot -p -h                mysql5.6預設是沒有密碼的  遇到password直接按回車鍵
# mysql> set password = password('root');   給當前資料庫設置密碼
# mysql> select user();  #查看當前用戶
# 輸入“ipconfig”即可查看到本機的ip信息。
# ；表示sql語句的結束
# \c放棄當前要執行的sql語句
# mysql> exit     # 也可以用\q quit退出

創建用戶賬號並授權

mysql> grant all on *.* to 'eva'@'%' identified by '123'

5.6及以下版本
mysql> grant all on *.* to 'eva'@'localhost' identified by '123'

△分兩步:

創建用戶

mysql> create user 'eva'@'192.168.10.%'   IDENTIFIED BY '123';# 指示網段
mysql> create user 'eva'@'192.168.10.5'   # 指示某機器可以連接
mysql> create user 'eva'@'%'                    #指示所有機器都可以連接  
mysql> show grants for 'eva'@'192.168.10.5';查看某個用戶的許可權 

給賬號授權

mysql> grant all on *.* to 'eva'@'%';
#grant 許可權類型 on 資料庫名稱 . *  (或表名)  to 'shang'@'%';

mysql> flush privileges;    # 刷新使授權立即生效

用戶端-操作

操作庫（文件夾）

數據備份: mysqldump –uroot –p test101 > C :\t1.sql   （如果有警告可以忽略）
數據恢復: mysql –uroot –p 資料庫名 < d:\t1.sql
增：create database db1 charset utf8;  charset utf8可省略,創建資料庫
查：show databases;  顯示所有資料庫
改：alter database db1 charset latin1;(改成歐洲的)
刪除: drop database db1;  刪除資料庫（慎用！）
切換:：use db1; 切換資料庫

操作表（文件）

先切換到文件夾下：use db1 
增：create table t1(id int,name char)CHARSET=utf8; 創建表
   
查： select database();            查看當前所在庫
       show tables;                  查看所有的表
       desc 表;                       查看表結構 
       show creat table 表名；  查看建表語句和搜索引擎，顯示的信息更全面
       show engines;           查看MySQL重要引擎
改： ALTER TABLE t_2 ADD age INT; 增加列名 類型
       RENAME TABLE t_1 TO t_2;    更改表名稱
       alter table t_1 modify name char(3);  修改列的數據類型
       alter table t_1 change name name1 char(2);  修改列名和數據類型
       ALTER TABLE t_1 DROP age;    刪除列

刪：drop table t1

操作表數據

增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
     insert into t1(id,name) values (5,'wusir'), (6,'wusir');
     insert into t2 select * from t1; 
      先執行執行select,把查到的內容導入到t2
     insert into t1(id,name) select id,name from t1;
   
查： select * from 表;     查詢表的信息
       select emp_name,salary from employee;  指定列查詢
       select emp_name,salary*12 from employee;   在列中使用四則運算
       select emp_name,salary*12 as annul_salary from employee; 重命名
       select emp_name,salary*12 annul_salary from employee; 重命名
       select distinct post from employee;  去重
       select distinct sex,post from employee;  雙向去重

改： update 表 set 欄位1=值1,欄位2=值2 where 條件;
刪：delete from t_1 where id=1;
        如果不加where：DELETE FROM t_person; 會刪除所有表中的內容


通用建表語句:
CREATE TABLE sanguo(
id INT PRIMARY KEY AUTO_INCREMENT,  # 主鍵自增
NAME VARCHAR(32),
age INT,
arms VARCHAR(32),
country VARCHAR(32),
skill VARCHAR(32)
)engine = innodb CHARSET=utf8;

查

where

having要跟在group by之後，對分組查詢的結果進行過濾（過濾分組）
where要出現在group by之前，執行表中所有數據來進行過濾（過濾行）
另外，having可以用聚合函數，並支持所有where子句操作符數

函數 concat() 拼接

# select concat('姓名 ：',emp_name),concat('年薪:',salary*12) from employee;
# select concat_ws('|','a','b','c')

case when語句

== if條件判斷句

  3SELECT
       (
           CASE
           WHEN emp_name = 'jingliyang' THEN
               emp_name
           WHEN emp_name = 'alex' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       employee;

-- 代表註釋

查詢用的函數

count 統計值

select concat ('<名字:',name, '>'  ,   '<薪資:',salary,'>') from 表;

max 最大值

min 最小值

avg 平均值

sum 求和

ascii（str）

查看字元的ASCII碼值,str是空時返回0

SELECT ASCII('a')

char(數字)

查看ASCII碼值對應的字元

SELECT CHAR(97)

concat 拼接字元串

SELECT CONCAT(12,34,'ab')
SELECT uname,CONCAT(age,'歲') FROM t_user;

length（str）

字元串中包含的字元個數

SELECT LENGTH('abc')

left（str，len）

截取字元串

截取字元串左端的len個字元

SELECT LEFT('qwertyui',3)

right（str,len）截取字元串右端的len個字元

SELECT RIGHT('qwertyui',3)

substring(str,pos,len) 指定位置截取,截取字元串str的位置pos起的len個字元（從1開始）

 SELECT SUBSTRING('qwertyuio',2,3)

截取所有人物的姓
SELECT SUBSTRING(uname,1,1) FROM t_user;  
同時去除重覆項
SELECT DISTINCT SUBSTRING(uname,1,1) FROM t_user;

ltrim（str）

返回刪除了左空格的字元串

SELECT LTRIM('   abc    ')

rtrim（str）

返回刪除了右空格的字元串

SELECT RTRIM('     abc     ')

trim（方向 remstr from str）：返回從某側刪除remstr後的字元串str

方向詞包括both（兩側）、leading（左）、trailing（右）
SELECT TRIM(‘   abc    ’)   --刪除兩側空格
SELECT TRIM(BOTH ‘x’ FROM ‘xxxabcxxx’) --刪除兩側特定字元
SELECT TRIM(LEADING 'x' FROM 'xxxabcxxx')   刪除左側特定字
SELECT TRIM(TRAILING 'x' FROM 'xxxabcxxx')  刪除右側特定字

space（n）

返回由n個空格組成的字元串

replace（str,from_str,to_str)

替換字元串

SELECT REPLACE('123abc123','123','def')

lower(str) upper(str)

大小寫轉換

SELECT LOWER('aBcD')
SELECT UPPER('aBcD')

mod(m,n)

求m%n的餘數

SELECT MOD(3,2)

abs(n)

求絕對值

SELECT ABS(-30)

floor（n)

表示向下取整

SELECT FLOOR(5.6)

ceiling（n)

表示向上取整

SELECT CEILING(5.6)

round(n)

表示將值 n 四捨五入為整數，無小數位

SELECT ROUND(5.6)

round(n,d)

表示將值 n 四捨五入為小數點後 D 位的數值，D為小數點後小數位數
若要保留 n 值小數點左邊的 D 位，可將 D 設為負值

SELECT ROUND(345.6789,2)

pow(x,y)

求x的y次冪

SELECT POW(2,3)

PI()

獲取圓周率

SELECT PI( )

rand()

獲取一個0.0-1.0之間的隨機數

SELECT RAND( )

獲取當前日期

SELECT CURRENT_DATE()

獲取當前時間

SELECT CURRENT_TIME()

獲取當前日期和時間

SELECT NOW()

時間和日期格式化

date_format(data,format)
SELECT DATE_FORMAT('2018-8-8','%Y年%m月%d日')
SELECT DATE_FORMAT(CURRENT_DATE(),'%y年%m月%d日')
UPDATE t_user SET brithday=DATE_FORMAT(CURRENT_DATE(),'%y年%m月%d日')
                                        WHERE uname = '呂布'
format參數可用的值如下：
%Y  年份，返回4 位整數
%y  年份，返回2 位整數
%m  月，返回0-12的整數
%d  日期，返回0-31之間的整數
%H  小時 (00..23)
%h  小時 (01..12)
%i  分鐘(00..59)
%s  秒 (00..59)

單表查詢

公式

使用算數表達式查詢某幾列:年齡增加50

SELECT uname,country,age+50 FROM t1;

as

SELECT age+50 AS '年齡' FROM t_user;   #修改列名的顯示（起別名）註意不要用關鍵字，as可以省略

distinct 查詢時去重

SELECT DISTINCT country FROM t_user;
SELECT DISTINCT country，uname FROM t_user;  #聯合去重
查詢每個國家都有誰，註意：distinct後面的欄位用逗號分隔，逗號兩邊不能有空格

between

a and b [a,b]

# select * from employee where salary between 10000 and 20000;

in

# select * from employee where salary in (17000,19000);

like 模糊查詢

_ 通配符 表示一個字元長度的任意內容

select * from employee where emp_name like 'jin___'

% 通配符 表示任意字元長度的任意內容

select * from employee where emp_name like 'jin%'
select * from employee where emp_name like '%g'
select * from employee where emp_name like '%n%'

regexp 正則匹配

# select * from employee where emp_name regexp '^jin'

查看所有員工中名字是jin開頭，n或者g結果的員工信息
select * from employee where emp_name regexp '^jin.*[gn]$';

IS NULL關鍵字

(判斷某個欄位是否為NULL不能用等號，需要用IS)

     SELECT emp_name,post_comment FROM employee WHERE post_comment IS NULL;
     SELECT emp_name,post_comment FROM employee WHERE post_comment IS NOT NULL;

     SELECT emp_name,post_comment FROM employee WHERE post_comment=''; 
                                            註意''是空字元串，不是null
    ps：
        執行
        update employee set post_comment='' where id=2;
        再用上條查看，就會有結果了

比較運算

= > < >= <= != / <>

select * from employee where age>18;
select * from employee where salary<10000;
select * from employee where salary=20000;

邏輯運算

and or not

and
select * from employee where age>18 and post='teacher';

or
select * from employee where salary<10000 or salary>30000;

not
select * from employee where salary not in (10000,17000,18000);

分組聚合 group by

# 查詢崗位名以及崗位包含的所有員工名字
# select post,group_concat(emp_name) from employee group by post;

# 查詢各部門年齡在20歲以上的人的平均薪資
# select post,avg(salary) from employee where age>20 group by post;

# select * from 表 where 條件 group by 分組

having 對分組之後過濾 (group by 配合having)

having放在分組之後，因為作用的對象不同。WHERE 子句作用於表和視圖，HAVING 子句作用於組

查詢平均薪資大於1w的部門
select avg(salary) from employee group by post having avg(salary) > 10000

1. 查詢各崗位內包含的員工個數小於2的崗位名、崗位內包含員工名字、個數
select post,emp_name,count(id) from employee group by post having count(id)<2

2. 查詢各崗位平均薪資大於10000的崗位名、平均工資
select post,avg(salary) from employee group by post having avg(salary) > 10000

3. 查詢各崗位平均薪資大於10000且小於20000的崗位名、平均工資
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;

order by 排序

    #  asc 升序
    #  select * from employee order by salary;
    #  select * from employee order by salary asc;
    #  desc 降序
    #  select * from employee order by salary desc;

    # select * from employee order by age,salary;
    # select * from employee order by age,salary desc;
    # select * from employee order by age desc,salary;

排列順序從高到低 DESC

limit 分頁

在查詢時可以只檢索前幾條或者中間某幾行數據（數據量很大時，幾百萬條）
SELECT * FROM t_user LIMIT 0,3;
limit 後面的第一個數字設置從哪裡開始檢索（偏移量，從0開始）
limit 後面的第二個數字是設置顯示多少條

# select * from 表 order by 列 limit n; 取前n條
# select * from 表 order by 列 limit m,n; 從m+1開始,取n條
# select * from 表 order by 列 limit n offset m; 從m+1開始,取n條

多表查詢

連表查詢

內連接

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

方式一 ：
select * from employee,department where dep_id = department.id;
方式二 ：
select * from employee inner join department on dep_id = department.id;

外連接

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;

全外連接

別的資料庫里是full join

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

子查詢

select * from 表 where 欄位 = (select ....);
子查詢效率相對於連表查低, 因為 子查詢要查兩張表,連表查詢查一張表

約束

primary key 主鍵 =非空+唯一

創建表時設置主鍵

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

創建表之後設置主鍵

CREATE TABLE t1(
   id INT NOT NULL,
   NAME CHAR(20)
);
ALTER TABLE t1 ADD PRIMARY KEY (id);

自動更新的主鍵

CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
-- id  你自己起的欄位名字。
-- int  數據類型，整型。
-- primary key 定義這個欄位為主鍵。
-- auto_increment 定義這個欄位為自動增長，即如果INSERT時不賦值，則自動加1

auto_increment 自增

自增的必須是主鍵

create table t6(id int auto_increment, name char(12));   # 報錯
create table t8(id int primary key auto_increment, name char(12)) ;
create table t9(id int unique auto_increment, name char(12)) auto_increment=100000; 自增=>非空  非空+唯一約束會被定義成主鍵 

delete from t7; 清空表數據但不能重置auto_increment
truncate table t7;  # 清空表並且重置auto_increment

所有的操作都無法改變auto_increment的自動計數。但是我們也沒有必要去改變它。
1.至少要看到自增的效果
2.至少寫3條數據 4，5，6
3.刪掉第5條，再看結果
4.再insert一條數據
5.刪掉第5條，再看結果
6.再insert一條數據
7.清空整張表
8.再insert一條數據，再看結果

修改auto_increment
alter table 表名 auto_increment = n; 修改表的auto_increment
alter table t7 auto_increment = 1000; 修改表的auto_increment

not null 非空

# create table t1(id int not null,name char(12));
    # 預設插入0
# create table t2(id int,name char(12)  not null);
    # 預設插入空字元串




​```
設置嚴格模式：
    不支持對not null欄位插入null值
    不支持對自增長欄位插入" ”值-空字元串
    不支持text欄位有預設值

直接在mysql中生效(重啟失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
​```

unique唯一 (不能重覆)

# create table t4(id int unique,name char(12));

# 聯合唯一約束
# create table t5(family char(12),name char(12),unique(family,name));

# 約束各自不能為空 且聯合唯一
# create table t5(family char(12) not null,name char(12) not null,unique(family,name)); 


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

default 預設值

create table t3(id int,name char(12),sex enum('male','female') default 'male');
非空約束 和 預設值
create table t3(id int not null,name char(12) not null,sex enum('male','female') not null default 'male');

外鍵

△外鍵：用來和其他表建立聯繫
外鍵具有保持數據完整性和一致性的機制，目前MySQL只在InnoDB引擎下支持
（ENGINE=INNODB）
外鍵是表中的一個列，其值必須在另一個表的主鍵或者唯一鍵中列出
作為主鍵的表稱為 主表，作為外鍵的表稱為 依賴表
外鍵會參照主表的主鍵或唯一鍵

△外鍵的作用有兩點：
1.對子表(外鍵所在的表)的作用：子表在進行寫操作的時候，如果外鍵欄位在父表中找不到對應的匹配，操作就會失敗
2.對父表的作用：對父表的主鍵欄位進行刪和改時，如果對應的主鍵在子表中被引用，操作就會失敗

△以下情況創建外鍵會失敗：
外鍵的引用類型不一樣，如主鍵是int外鍵是char
找不到主表中引用的列
主鍵和外鍵的字元編碼不一樣

foreign key(class_id) references class3(cid) 

沒有建立外鍵：
create table stu(id int,name char(12),class_id int);
create table class(cid int,cname char(12));
insert into stu values (1,'日魔',1),(2,'炮手',1)
insert into class values(1,'py27');
insert into class values(2,'py28');
select * from stu,class where class_id = cid;
delete from stu where id = 1；
delete from class where cid = 1;

stu2 class2
create table class2(cid int unique,cname char(12));
create table stu2(id int,name char(12),class_id int,foreign key(class_id) 
                  references class2(cid));
insert into class2 values(1,'py27');
insert into stu2 values (1,'日魔',1),(2,'炮手',1)
delete from class2 where cid = 1;
insert into class2 values(2,'py28');
update class2 set cid = 1 where cid = 2;  不能修改

關聯關係

一對一

# 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) referances guest(id));

一對多 foreign key

# 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) references class(id));

多對多

# create table class(id int primary key,cname char(12));
# create table teacher(id int primary key,tname char(12));
# create table teach_cls(id int,cid int,tid int,
# foreign key(cid) references class(id)),
# foreign key(tid) references teacher(id))
# );

級聯更新

on update cascade

stu3 class3 級聯更新
create table class3(cid int primary key,cname char(12));
create table stu3(id int,name char(12),class_id int,foreign key(class_id) 
                  references class3(cid) on update cascade);
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設置導致的

存儲引擎

意義

資料庫存儲引擎是資料庫底層軟體組件，資料庫管理系統使用數據引擎進行創建、查詢、更新和刪除數據操作
使用不同的存儲引擎還可以獲得特定的功能

存儲方式、存儲機制

表結構 存在一個文件中 : 硬碟上

表數據 存在另一個文件中、記憶體中

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

存儲引擎的種類

innodb ：

存儲方式為索引+數據 表結構 ,數據的持久化存儲

提供了具有提交、回滾和崩潰恢復能力的事務安全，支持外鍵。但是比起Myisam存儲引擎，InnoDB寫的處理效率差一些並且會占用更多的磁碟空間。MySQL 5.5.5 之後，InnoDB 作為預設存儲引擎.

myisam ：

存儲方式為索引 數據 表結構 數據的持久化存儲 ,有表級鎖

不支持事務、也不支持外鍵，但訪問速度快，對事務沒有要求

memory ：

存儲方式為表結構,數據斷電消失

查看表引擎

show create table books; 

查看MySQL重要引擎

show engines;

設置引擎

建表的時候可以指定引擎

create table innot(id int) engine = innodb;
create table myist(id int) engine = myisam;
create table memot(id int) engine = memory;

事務

一致性 n條語句的執行狀態是一致的

begin；   # 開啟事務
select id from innot where id =1 for update;
update innot set id = 2 where id = 1；
commit；  # 提交事務 解鎖被鎖住的數據，讓他們能夠被修改

mysql鎖

行級鎖 ：只對涉及到修改的行加鎖，利於併發的修改，但是對於一次性大量修改效率低下
表級鎖 ：一次性加一把鎖就鎖住了整張表，不利於併發的修改，但是加鎖速度比行鎖的效率要高
外鍵約束 ：被約束表中的數據不能隨意的修改/刪除 約束欄位據要根據被約束表來使用數據

索引-目錄

索引原理

△磁碟預讀性原理
1個block塊 4096個位元組/9ms

△樹
樹 根節點 分支節點 葉子節點
平衡樹 balance tree - B樹

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

△b+樹 是為了更好的處理範圍問題在b樹的基礎上有所優化
△mysql中innodb存儲引擎的所有的索引樹都是b+樹

優點：

當資料庫中存在很多條記錄，例如幾十萬條，查詢速度就成了一個問題
先在目錄中查詢，然後根據目錄所示的頁碼找到查詢內容，大大縮短查詢時間.
不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行
表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜索數據文件

缺點：

索引也需要占空間，如果我們有大量的索引，索引文件可能會占很多空間
3、當對錶中的數據進行增加、刪除、修改時，索引也需要動態的維護，降低了數據的維護速度。
使用原則：

建議

1.對經常更新的表就避免對其進行索引，對經常用於查詢的欄位應該創建索引
2.數據量小的表最好不要使用索引
3.在不同值少的列上(欄位上)不要建立索引，比如在"性別"欄位上只有男，女兩個不同值

建立一個索引

1，創建表時，主鍵會預設帶有索引

1. 創建表時直接指定

CREATE TABLE mytable(  -- 創建表時直接指定
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX index_1 (username)  
); 

3.補索引

CREATE INDEX index_1 ON t_user(uname)
     創建一個索引    索引名  從  表名  在哪個欄位

刪除索引

drop index index_1 on t_user(uname);

視圖

在創建一個視圖時，只存放視圖的定義，也就是動態檢索數據的查詢語句，並不存放視圖對應的數據，在用戶使用視圖時才去求相對應的數據，所以視圖稱為‘虛表’

-- 查看每個國家的總人數，年齡總和，平均年齡，最高年齡，最低年齡
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user
                    GROUP BY country;

為上面的語句創建視圖：
CREATE VIEW v_user AS
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user     
                GROUP BY country;    
SELECT * FROM v_user; --以後在使用上面的查詢語句時只需要使用視圖名

python連接資料庫

import pymsql

conn=pymyssq.connet
(host='localhost,user='root',pqssword='123',db='資料庫名',charset='utf8') 
                       #先修路-conn

cur = conn.cursor()    #備車-cur

sql = '語句'    #裝貨

cur.execute(sql)        #發車

emps = cur.fetchall()      #帶回交易貨物

for i in data:
    print(i)    #查貨

創一個表的模板

create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一個部門一個屋子
depart_id int
);


#插入記錄
#三個部門：教學，銷售，運營
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龍','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是銷售部門
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('張野','male',28,'20160311','operation',10000.13,403,3), #以下是運營部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3)
;

作業:

資料庫小考

0.建表book，並向表中插入數據
1.查詢egon寫的所有書和價格
2.找出最貴的圖書的價格
3.求所有圖書的均價
4.將所有圖書按照出版日期排序
5.查詢alex寫的所有書的平均價格
6.查詢人民音樂不好聽出版社出版的所有圖書
7.查詢人民音樂出版社出版的alex寫的所有圖書和價格
8.找出出版圖書均價最高的作者
9.找出最新出版的圖書的作者和出版社
10.顯示各出版社出版的所有圖書
11.查找價格最高的圖書，並將它的價格修改為50元
12.刪除價格最低的那本書對應的數據
13.將所有alex寫的書作業修改成alexsb
14.select year(publish_date) from book
自己研究上面sql語句中的year函數的功能，完成需求：
將所有2017年出版的圖書從資料庫中刪除
15.有文件如下，請根據鏈接自學pymysql模塊，使用python寫代碼將文件中的數據寫入資料庫
學python從開始到放棄|alex|人民大學出版社|50|2018-7-1
學mysql從開始到放棄|egon|機械工業出版社|60|2018-6-3
學html從開始到放棄|alex|機械工業出版社|20|2018-4-1
學css從開始到放棄|wusir|機械工業出版社|120|2018-5-2
學js從開始到放棄|wusir|機械工業出版社|100|2018-7-30

# select distinct * from 表 where 條件 group by 欄位
# having 過濾 order by 欄位 limit n offset m;

# 書名    作者  出版社 價格  出版日期
# create table books(bname char(20),
# author char(12),
# press char(20),
# price float(6,2),
# pub_date date);

# insert into books values
# ('倚天屠龍記','egon','北京工業地雷出版社',70,'2019-7-1'),
# ('九陽神功','alex','人民音樂不好聽出版社',5,'2018-7-4'),
# ('九陰真經','yuan','北京工業地雷出版社',62,'2017-7-12'),
# ('九陰白骨爪','jinxin','人民音樂不好聽出版社',40,'2019–8-7'),
# ('獨孤九劍','alex','北京工業地雷出版社',12,'2017-9-1'),
# ('降龍十巴掌','egon','知識產權沒有用出版社',20,'2019-7-5'),
# ('葵花寶典','yuan','知識產權沒有用出版社',33,'2019–8-2');

# 查詢egon寫的所有書和價格
# select bname,price from books where author='egon';

# 找出最貴的圖書的價格
# select max(price) from books;

# 求所有圖書的均價
# select avg(price) from books;

# 將所有圖書按照出版日期排序
# select * from books order by pub_date;

# 查詢alex寫的所有書的平均價格
# select avg(price) from books where author = 'alex'

# 查詢人民音樂不好聽出版社出版的所有圖書
# select * from books where press = '人民音樂不好聽出版社';

# 查詢人民音樂出版社出版的alex寫的所有圖書和價格
# select bname,price from books where press = '人民音樂不好聽出版社' and author = 'alex'

# 找出出版圖書均價最高的作者
# select author from books group by author order by avg(price) desc limit 1;
# select author,avg(price) as avg_price from books group by author order by avg_price desc limit 1;

# 找出最新出版的圖書的作者和出版社
# select author,press from books order by pub_date desc limit 1

# 顯示各出版社出版的所有圖書
# select press,group_concat(bname) from books group by press;

# 查找價格最高的圖書,並將它的價格修改為50元
# select max(price) from books;    # 70
# update books set price=50 where price = 70;

# update books set price=50 order by price desc limit 1;
# update books set price=50 where price = (select * from (select max(price) from books)as t);

# 刪除價格最低的那本書對應的數據
# select min(price) from books;  # 5
# delete from books where price = 5;

# delete from books order by price limit 1;
# delete from books where price = (select * from (select min(price) from books)as t);

# 將所有alex寫的書作者修改成alexsb
# update books set author = 'alexsb' where author = 'alex';

# select year(publish_date) from book
# month(publish_date)
# day(publish_date)
# delete from books where year(publish_date) = 2017;