資料庫入門

来源:https://www.cnblogs.com/-xct/archive/2019/12/03/11974802.html

知識點 △用資料庫的原因 1文件操作的複雜度 2同步 3併發處理 4安全 △資料庫系統(DBS) 資料庫(DB) + 資料庫管理系統 (DBS)+ 資料庫應用程式 + 資料庫管理員 (BDA)+ 最終用戶 △資料庫管理系統 DBM 網路應用服務端 我們要使用服務端的數據 需要有一個客戶端 客戶端可以 ...


知識點

△用資料庫的原因

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;

您的分享是我們最大的動力!

更多相關文章
  • MySQL高可用 Galera Cluster [toc] Galera Cluster Galera Cluster:集成了Galera插件的MySQL集群,是一種新型的,數據不共用的,高度冗餘的高可用方案,目前Galera Cluster有兩個版本,分別是Percona Xtradb Clust ...
  • 使用oracle客戶端連接資料庫,從oracle官網下載客戶端instantclient_18_3工具,到目錄下打開cmd命令視窗: 個人網盤客戶端工具:https://pan.baidu.com/s/1lM4Faat7Mq0gr7rzmfjtSQ 1.輸入sqlplus; 2.輸入連接信息:use ...
  • 需求 SQL Server的作業歷史(Job)記錄是保存在msdb庫中的,很方便就查詢相關的Job定義,計劃和歷史記錄,而MySQL的event卻沒有歷史記錄。為方便查看event是否正常執行以及執行結果,通過以下兩個步驟來實現類似的功能。 實現 1. 在mysql庫創建event執行的歷史記錄表 ...
  • 問題描述:情形是當主庫真正出現異常之後,才會執行的操作,那麼我們執行過failover 之後,如何在重新構建DG,這裡我們利用flashback database來重構。模擬前主庫要開啟閃回區,否則要重新搭建DG了 1.由於主庫已經不可訪問,下麵所有的操作都在備庫完成,先取消實時同步 SQL> al ...
  • 有時會發現資料庫system表空間增長很快,使用以下語句查看system表空間使用量。也可以使用toad直接看。 執行以下語句查看是哪個對象占用較大 一般發現都是發現是AUD$審計表占用資源量大。 直接登錄資料庫,清理掉SYS.AUD$表。 如果想關閉資料庫審計,可以參考以下鏈接 https://w ...
  • spark 各個版本的application 調度演算法還是有這明顯的不同之處的。從spark1.3.0 到 spark 1.6.1、spark2.x 到 現在最新的spark 3.x ,調度演算法有了一定的修改。下麵大家一起學習一下,最新的spark 版本spark-3.0的Application 調 ...
  • 說明: 1)該實驗所有過程均是本人親自敲命令完成,所有代碼運行正確 2)安裝過程使用的是suse11 sp3操作系統,後續的實驗過程換成了麒麟中標,因此部分路徑可能存在差異 3)安裝過程使用了命令行安裝,圖形界面簡單,因此本文沒有介紹 4)job部分命令行操作太繁瑣,建議使用圖形界面操作,因此本文也 ...
  • sqlServer2012(936 簡體中文GBK )為例: 例如: varchar(10),只能存儲10個英文字元或數字,也只能存儲5個漢字; char(10),只能存儲10個英文字元或數字,也只能存儲5個漢字; nvarchar(10),即存儲10個英文字元或數字,也能存儲10個漢字; ncha ...
一周排行
  • 前言 上一篇文章主要介紹了ObjectPool的理論知識,再來介紹一下Microsoft.Extensions.ObjectPool是如何實現的. 核心組件 ObjectPool ObjectPool 是一個泛型抽象介面,他抽象了兩個方法Get和Return Get方法用於從對象池獲取到可用對象,如 ...
  • 國內優秀的WPF開源控制項庫,Panuon.UI的優化版本。一個漂亮的、使用樣式與附加屬性的WPF UI控制項庫,值得向大家推薦使用與學習。 今天站長(Dotnet9,站長網址:https://dotnet9.com, 微信公眾號:dotnet9_com)推薦另一款開源的WPF控制項庫(PanuonUI. ...
  • WGS-84坐標系:全球定位系統使用,GPS、北斗等 GCJ-02坐標系:中國地區使用,由WGS-84偏移而來 BD-09坐標系:百度專用,由GCJ-02偏移而來 (PS:源於項目需求,本來是想讀圖片的經緯度顯示在百度離線地圖上的。後來發現定位偏差太大,仔細一想,原來是圖片和百度使用的坐標系不一樣。 ...
  • .NET Core3.1發佈 我們很高興宣佈.NET Core 3.1的發佈。實際上,這隻是對我們兩個多月前發佈的.NET Core 3.0的一小部分修複和完善。最重要的是.NET Core 3.1是長期支持(LTS)版本,並且將支持三年。和過去一樣,我們希望花一些時間來發佈下一個LTS版本。額外的 ...
  • based on https://stackoverflow.com/questions/659013/accessing-a-shared-file-unc-from-a-remote-non-trusted-domain-with-credentials ...
  • private static void PathCopyFilesWithOriginalFolder() { int sourceFilesNum = 0; try { string sourceDir = @"E:\Source"; string destDir = @"E:\Dest"; st... ...
  • 前言 上一次資料庫災備和性能優化後,資料庫專家建議,在不擴容的情況下,客戶端不能再頻繁的掃描資料庫了!一句驚醒夢中人,因為我也發現資料庫越來越卡了,自從上個項目上線後,就出現了這個情況。後來分析其原因,發現客戶端每3秒中掃描一次資料庫,一共5000+客戶端,可想而知,頻繁掃描嚴重影響到資料庫性能。所 ...
  • 2019.12.4今天開通博客,跌跌撞撞學了3年C#,感覺有了基礎但還不夠深入,有些東西學了又忘,特此開通博客做一個記錄,記錄下以後學習中的每一個知識點,再接再厲,每天進步一點點!!!!!! ...
  • 本人剛接觸.net core 由於公司項目需要部署在Linux上 近些日子學習和網上大面積搜教程 我在這給大家歸攏歸攏借鑒的教程做了套方案(我寫的可以實現 但不一定是最好的 僅供參考) 我只用過core3.0 之前的版本沒接觸過 首先需要使用Nginx反代理的項目那一定是web框架的ASP.NET ...
  • WinFrm應用程式調用WebService服務 關於WebService的創建、發佈與部署等相關操作不再贅述,傳送門如下:C# VS2019 WebService創建與發佈,並部署到Windows Server 2012R 此篇記錄一下客戶端的調用,以便後續學習使用,不足之處請指出。 建立WinF ...
x