資料庫分類+MySQL安裝+ SQL的五大類語言+MySQL增删改查

MySQL簡介

資料庫簡介：

• 用途：用于存儲生活的幾乎一切資料，如：身份、住房、車票、網站、…
• 概念：資料庫伺服器、資料庫、資料表、一行資料(一條)、一列資料(字段)
• 分類：
  • 關系型資料庫：mysql、oracle、SQL server、…
  • 非關系型資料庫：Redis、MongoDB、…
• SQL：Structured Query Language，結構化查詢語言
• SQL分類：
  • 資料定義語言：DDL
  • 資料操作語言：DML
  • 資料查詢語言：DQL
  • 資料控制語言：DCL
  • 資料事務語言：DTL

MySQL(ubuntu)安裝：

• 安裝：sudo apt-get install mysql-server
  • 若安裝出現系統服務問題，很可能是自己添加系統服務的問題，移除即可
• 安全配置：sudo mysql_secure_installation
  • 按照提示，一路進行下去
  • 設定支援遠端連接配接，為後面做準備
• 連接配接測試：mysql -h host -u user -p
  • -h：指定主機，localhost/127.0.0.1表示本機，本機可以省略
  • -u：指定使用者，預設隻有root使用者
  • -p：指定密碼，輸入密碼時要與登入分開，以防不測
  • 示例：mysql [-hlocalhost] -uroot -p，然後根據提示輸入密碼
    • 端口：3306
    • 退出：\h 或 help;
    • 說明：所有的指令都是以’;'結尾

資料定義語言(DDL)

• 檢視庫：show databases;，會顯示資料庫伺服器上所有的資料庫
• 建立庫：create database test;，建立資料庫test
• 删除庫：drop database test;，删除資料庫test
• 選擇庫：use test;，選擇資料庫test
  • 檢視目前選中資料庫：
    • show tables；
    • select database();
• 檢視表：show tables;，檢視目前選中資料庫中的資料表
• 建立表：create table user(username varchar(20), password char(32));
• 檢視表結構：desc user;
• 删除表：drop table user;
• 檢視建立方式：
  • 庫：show create database test;
  • 表：show create table user;
• 修改字元集：
  • 建立時指定：在建立表的語句後指定字元集。
  • 修改配置檔案：sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf，在[mysqld]後面添加
    • character-set-server=utf8
    • 重新開機服務：sudo service mysql restart
    • 說明：以後再建立的庫中的表預設字元集就是utf8
• 修改字段：alter table user modify username char(30);，不能修改名字
• 添加字段：
  • 預設(最後)添加：alter table user add age int(3);
  • 在指定字段後添加：alter table user add email varchar(60) after password;
  • 在最開始出添加：alter table user add id int(11) first;
• 删除字段：alter table user drop age;
• 修改字段名稱：alter table user change email em char(32);
• 修改位置及名稱：add/change/modify
  • alter table user modify em char(32) first;
• 修改表名：alter table user rename new_user;

資料類型

• 整型：tinyint(1個位元組)、smallint(2個位元組)、mediumint(3個位元組)、int(4個位元組)、bigint(8個位元組)
• 浮點類型：
  • float(m, d)，單精度浮點數，4個位元組，m表示總位數，d表示小數位數
  • double(m, d)，雙精度浮點數，8個位元組，m表示總位數，d表示小數位數
  • decimal(m, d)，以字元串的形式存儲浮點數，用于金融領域等要求嚴格的場景
• 字元類型：
  • char：定長字元串，0~255個位元組
  • varchar：變長字元串，0~65535個位元組
• 時間日期：
  • date：日期，格式：2018-04-16
  • time：時間，格式：15:36:30
  • datetime：日期時間，格式：2018-04-16 15:37:38
  • timestamp：時間戳
  • year：年，隻占1個位元組，年份範圍：1901~2155
• 符合類型：
  • set：集合類型，格式：set(s1, s2, …, s63)，最多63種可能
  • enum：枚舉類型，格式：enum(e1,e2, … ,e65535)，最多65535種可能
• 字段修飾：
  • unsigned：無符号數
  • zerofill：高位0填充，防止出現負數
  • auto_increment：自動增加(1)，用于整數，經常與主鍵結合使用
  • default：設定預設值
  • not null：不能為空
• 字元集及存儲引擎
  • 檢視支援的字元集：show character set;，我們通常隻使用utf8，不能書寫成utf-8
  • 檢視支援的存儲引擎：show engines;
    • 常用存儲引擎：MyISAM和InnoDB
• 索引：
  • 說明：簡單了解，就是一本書最前面的目錄，雖然可以提高讀取效率，單并非越多越好。
  • 分類：
    • 普通索引(index)：最基本的索引
    • 唯一索引(unique)：修飾的字段不能重複
    • 主鍵索引(primary key)：是一種特殊的唯一索引，一張表中隻能有一個字段設定
    • 全文索引(fulltext)：多全局資料添加索引。

  • 示例：

    alter table user add index(em); # 給em字段添加普通索引

    alter table user add unique(username); # 給username字段添加唯一索引

    alter table user add primary key(id); # 将id設定為主鍵索引

    alter table user drop index em;				# 删除em字段的普通索引
               

    alter table 表名add fulltext(字段);

• 很多選項都可在建立表時指定，如：

  create table user(

  id int auto_increment,

  name varchar(20),

  primary key(id),

  unique(name)

  )engine=innodb default charset=utf8;

  資料操作語言(DML)

• 說明：在最大多數的使用中，都是對資料的增、删、改、查的操作(CURD)

• 準備：建立一個star表

  create table star(

  id int auto_increment,

  name varchar(20) not null,

  money float not null,

  province varchar(20) default null,

  age tinyint unsigned not null,

  sex tinyint not null,

  primary key(id)

  )engine=innodb default charset=utf8;

• 插入資料：

  • 方式1：不指定字段，按照資料表的資料添加一條資料的全部字段

    insert into star values(1,‘黃曉明’,200000,‘山東’,28,0);

    可以同時插入多條資料，每條一個小括号。

  • 方式2：指定字段，隻需要傳遞指定字段的值

    insert into star(name,money,age,sex,province) values(‘小嶽嶽’,4000000, 33, 0, ‘河南’)；

    可以同時插入多條資料，每條一個小括号

  • 注意：什麼情況下可以不傳字段值
    • 自增的字段
    • 有預設值的
    • 可以為空的
  • 說明：插入資料通常使用方式2，可以根基需要進行資料的插入。
• 查詢資料：
  • select * from star;
• 删除資料：
  • delete from star where id=1;
  • 警告：删除操作一定不要忘了指定條件，否則後果自負。
• 修改資料：
  • update star set age=22,money=8000000 where id=6;
  • 警告：修改操作一定不要忘了指定條件，否則後果自負。

資料查詢語言(DQL)

• 基礎查詢：select * from star;
• 指定字段查詢：select id,name,money from star;
• 排除重複記錄：select distinct name from star;
  • 使用distinct修飾的字段組合不能重複
• 指定條件查詢：

  • 條件：

    符号 說明

    大于             
               

    = 大于等于

    < 小于

    <= 小于等于

    = 等于

    != 或 <> 不等于

    and 并且

    or 或者

    [not] between m and n [不]在指定[m,n]的閉區間

    [not] in [不]在指定的集合中

    [not] like 模糊比對，%表示任意字元

  • 示例：

    select id,name,money from star where id > 4;

    select id,name from star where id between 2 and 5;

    select id,name from star where id in(2,4,6);

    select id,name from star where id not in(2,4,6);

    select id,name from star where name like ‘小%’;

• 結果集排序：
  • 示例：select id,name,money from star order by money desc;
  • order by ：指定排序字段
    • asc：升序，預設的
    • desc：降序
  • 也可以多字段排序，先按照前面的字段排序，再按照後面字段排序
    • 示例：select id,name,money,age from star order by money desc,age asc;
• 限制結果集：

  • 示例：

    select id,name,money from star limit 3; # 取3條資料

    select id,name,money from star limit 2,3; # 偏移2條，然後取3條資料

    select id,name,money from star limit 3 offset 2; # 偏移2條，然後取3條資料

  • 用途：分頁顯示，假設一頁顯示10條資料

    第一頁：limit 0, 10

    第二頁：limit 10, 10

    第三頁：limit 20, 10

    page：表示頁數，pageSize：表示一頁的大小

    查詢條件：limit (page-1)*pageSize, pageSize

• 常用聚合函數

  函數 功能

  count 統計個數

  sum 求和

  max 最大值

  min 最小值

  avg 平均值

  1. 使用count時指定任何字段都行

  2. 使用其他函數時必須要指定字段

     示例：

     select count(*) from star;

     select max(money) as m from star;

     as可以給字段其别名

• 分組操作

  • 示例：

    select * from star group by sex; # 分組

    select count(*), sex from star group by sex; # 分組統計

• 結果集過濾：

  • 示例：

    select count(*) as c,province from star group by province having c>1;

    搜尋所有記錄，然後按照省份分組，統計成員大于1的省份

資料控制語言(DCL)

• 建立使用者：
  • 格式：create user ‘使用者名’@‘主機’ identified by ‘密碼’
  • 示例：create user ‘liusan’@’%’ identified by ‘123456’
• 授權使用者：
  • 格式：grant 權限 privileges on 庫.表 to ‘使用者’@‘主機’ identified by ‘密碼’
  • 示例：grant all privileges on test.* to ‘liusan’@’%’ identified by ‘123456’
• 檢視授權：
  • 格式：show grants for ‘使用者’@‘主機’
  • 示例：show grants from ‘liusan’@’%’
• 重新整理權限：flush privileges;
• 取消授權：
  • 格式：revoke 權限 privileges on 庫.表 from ‘使用者’@‘主機’;
  • 示例：revoke all privileges on test.* from ‘liusan’@’%’;
• 删除使用者：
  • 格式：drop user ‘使用者’@‘主機’
  • 示例：drop user ‘[email protected]’%’
• root使用者遠端登入：
  • sudo mysql_secure_installation，根據提示配置，允許root使用者遠端登入
  • 修改配置檔案：sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
    • 将bind-address=127.0.0.1，改為bind-address=0.0.0.0
  • 給root添加授權主機，%表示所有主機
    • 如：grant all privileges on . to ‘root’@’%’ identified by ‘123456’
    • 給root使用者添加任意主機以123456作為密碼登入，對所有的庫下的所有表用于所有權限

多表聯合查詢

• 隐式内連接配接：沒有出現join關鍵的連接配接
  • 示例：select username,name from user,goods where user.gid=goods.gid;
  • 說明：檢視使用者買的商品名
• 顯式内連接配接：會出現join關鍵字，後面的條件使用on
  • 示例：select username,name from user [inner/cross] join goods on user.gid=goods.gid;
  • 說明：功能同上
  • join前可以加inner，也可以使用cross，也可以省略
• 外左連接配接：以左表為主
  • 示例：select username,name from user left [outer] join goods on user.gid=goods.gid;
  • 說明：以左表為主，顯示左邊所有内容，右表不比對的選項顯示NULL

DTL，資料事務語言

事務的定義：

就是指一組相關的SQL操作，我們所有的操作都是事務中的。

注意：在資料庫中，執行業務的基本機關是【事務】，不是以某一條SQL。

資料庫在預設情況下，事務是都打開的，也就是說它一種處在事務當中的，一個事務的結束，代表着下一個事務的開啟。

執行commit或者rollback指令時，會結束目前事務

作用：用來保證資料的平穩性和可預測性

例如：銀行轉賬業務

SQL1：A賬号向B賬号轉賬10000

update tbl_account set balance=balance-10000 where accountNo=A賬号；

SQL2:

update tbl_account set balance=balance+10000 where accountNo=B賬号；

SQL1和SQL2必須處在同一個事務中，進而保證同時成功或者同時失敗。

事務的四大特性（ACID）：

atomic,原子性，事務是不可分割的，要麼同時成功，要麼同時失敗；

consistency,一緻性，事務一旦結束，記憶體中的資料和資料庫中的資料是保持一緻;

isolation,隔離性，事務之間互不幹擾，一個事務的結束意味着下一個事務的開啟；

duration，持久性，事務一旦送出，則資料持久化到資料庫中，永久儲存

在oracle中，操作事務的指令：

1.commit，送出事務

把事務中所有的資料持久化到磁盤中

2.rollback to 復原點,復原事務

把事務中所做的操作全部取消，回到初始化狀态

3.savepoint 復原點，設定復原點

事務復原時，復原到起點

總結：

1.目前主流的資料庫都是支援事務的，而且其中Oracle支援的最好

2.一個事務不能讀取到另一個事務還沒有送出的資料

3.DDL語句都會自動送出事務

4.DML語句不會自動送出事務，需要手動送出commit

多事務的并發處理機制：

原因：多個事務同時操作一個表中的同一行資料，如果這些操作同時修改的話，就會産生并發問題，如果不處理，則會造成資料的不一緻的情況

資料庫可能産生的并發問題包括：

1.髒讀

是指一個事務正在通路資料，并且對這個資料進行修改，而這種修改還沒有送出到資料庫中，而另一個事務也通路了這個資料，并且使用了這個資料。

解決方法：一個事務在修改資料時，該資料不能被其他事務通路

2.不可重複讀：

是指一個事務多次讀取同一條記錄，如果此時另一個事務也通路并且修改了該資料，則就會出現多次讀取出現資料不一緻的情況，原來的資料變成了不可重複讀取的資料。

解決方法：隻有在修改事務完全送出過後才可以讀取資料

3.幻讀

是指一個事務修改表中的多行記錄，但是此時另一個事務對該表格進行了插入資料的操作，則第一個事務會發現表格中會出現沒有被修改的行，就像發生了幻象一樣；

解決方法：在一個事務送出資料之前，其他事務不能添加資料

Oracle中采用‘鎖’來做并發處理

1.表級排它鎖(X) exclusive mode

2.表級共享鎖(S) share mode

3.表中行級排它鎖 share row exclusive

注：這三種鎖是通過專門的指令來申請的

文法：

lock table tbl_name in mode;

例如：

–以共享鎖鎖表

lock table tbl_emp in share mode;

–以排它鎖鎖表

lock table tbl_emp in exlusive mdoe;

4.行級共享鎖(RS) row share

5.行級排它鎖(RX) row exclusive

注：這兩種鎖無需通過專門的指令申請，而是通過DQL和DML來自動申請的

注意：

1.所有的DQL語句預設情況下都會自動申請RS鎖

2.所有的DML語句預設情況下都會自動申請RX鎖，每一行記錄都有唯一的RX鎖

3.在項目中，為了滿足業務要求，一般select語句需要申請RX鎖

select語句通過for update來申請RX鎖：

select * from s_emp for update;

select * from s_emp for update wait 5;等待5秒

select * from s_emp for update nowait;