主頁 > 資料庫 > 資料庫入門

資料庫入門

2020-09-21 20:00:17 資料庫

知識點

△用資料庫的原因

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)
;

作業:

資料庫小考

書名 作者 出版社 價格 出版日期
倚天屠龍記 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

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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/99388.html

標籤：MySQL

上一篇：高分高分.....關于pb9讀取unicode檔案的問題,讀出來的blob長度為1687,用fromunicode函式一轉換,只能顯示前幾個字符.急~~~~~~~~~

下一篇：請大哥們幫忙，定義變數后如何給該變數賦值？

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂