20170924 MySQL基礎

  • 數(shù)據(jù)庫簡介
  • 關(guān)系型數(shù)據(jù)庫
  • MySQL安裝和使用
  • SQL語言

一亏栈、數(shù)據(jù)庫簡介

（一）數(shù)據(jù)庫的發(fā)展

  • 文件系統(tǒng)：磁盤文件存儲數(shù)據(jù)
  • 第一代數(shù)據(jù)庫：網(wǎng)狀模型冕末、層次模型
  • 第二代數(shù)據(jù)庫：關(guān)系型數(shù)據(jù)庫肺魁、結(jié)構(gòu)化查詢語言
  • 新一代數(shù)據(jù)庫：“對象—關(guān)系”型數(shù)據(jù)庫

（二）文件管理系統(tǒng)的缺點

  • 編寫應用程序不方便
  • 數(shù)據(jù)冗余不可避免
  • 應用程序依賴性
  • 不支持對文件的并發(fā)訪問
  • 數(shù)據(jù)間聯(lián)系弱
  • 難以按用戶視圖表示數(shù)據(jù)
  • 無安全控制功能

（三）數(shù)據(jù)庫管理系統(tǒng)的優(yōu)點

  • 相互關(guān)聯(lián)的數(shù)據(jù)的集合
  • 較少的數(shù)據(jù)冗余
  • 程序與數(shù)據(jù)相互獨立
  • 保證數(shù)據(jù)的安全、可靠
  • 最大限度地保證數(shù)據(jù)的正確性
  • 數(shù)據(jù)可以并發(fā)使用并能同時保證一致性

（四）數(shù)據(jù)庫管理系統(tǒng)的基本概念

（一）術(shù)語定義
  • 數(shù)據(jù)庫：數(shù)據(jù)的匯集，它以一定的組織形式存于存儲介質(zhì)上
  • DBMS（數(shù)據(jù)庫管理系統(tǒng)）：管理數(shù)據(jù)庫的系統(tǒng)軟件，它實現(xiàn)數(shù)據(jù)庫系統(tǒng)的各種功能漱牵，是數(shù)據(jù)庫系統(tǒng)的核心
  • DBA（數(shù)據(jù)庫管理員）：負責數(shù)據(jù)庫的規(guī)劃、設計枝缔、協(xié)調(diào)布疙、維護和管理等工作
  • 應用程序：指以數(shù)據(jù)庫為基礎的應用程序
（二）DBMS的基本功能
  • 數(shù)據(jù)定義
  • 數(shù)據(jù)處理：增、刪愿卸、改、查
  • 數(shù)據(jù)安全
  • 數(shù)據(jù)備份
（三）數(shù)據(jù)庫系統(tǒng)的架構(gòu)
  • 單機架構(gòu)
  • 大型主機/終端架構(gòu)
  • 主從式架構(gòu)(C/S)
  • 分布式架構(gòu)

二截型、關(guān)系型數(shù)據(jù)庫

（一）基本概念

  • 關(guān)系：關(guān)系就是二維表趴荸，表中的行、列次序并不重要
  • 行 (row)：表中的每一行宦焦，又稱為一條記錄
  • 列 (column)：表中的每一列发钝，又稱為屬性、字段
  • 主鍵 (Primary key)：用于惟一確定一個記錄的字段
  • 域 (domain)：屬性的取值范圍

（二）主流關(guān)系型數(shù)據(jù)庫

MySQL, MariaDB, PostgreSQL, Oracle, MSSQL, DB2

（三）事務: transaction

  • 定義：被視作整體對待的若干個操作
  • 事務的特性：ACID
    A：原子性波闹，一個事務不可分割酝豪。
    當一個事務進行過程中出現(xiàn)了中斷，下一次開始本事務時會執(zhí)行回滾(rollback)操作恢復至事務執(zhí)行前的狀態(tài)精堕；當一個事務完成后孵淘，會執(zhí)行提交(commit)操作，此后無法再恢復至事務執(zhí)行前的狀態(tài)
    C：一致性歹篓，數(shù)據(jù)保持一致
    I：隔離性瘫证，使用鎖機制避免多用戶同時修改某數(shù)據(jù)，可能出現(xiàn)死鎖
    D：持久性庄撮，提交后的改變是不可撤銷的背捌，而尚未提交的數(shù)據(jù)是臟數(shù)據(jù)

（四）實體—聯(lián)系模型（E-R模型）

  • 實體(Entity)：客觀存在并可以相互區(qū)分的客觀事物或抽象事件

    • 在E-R圖中用矩形框表示實體，把實體名寫在框內(nèi)
    • 屬性：實體所具有的特征或性質(zhì)

  • 聯(lián)系(Relationship)：數(shù)據(jù)之間的關(guān)聯(lián)集合洞斯，是客觀存在的應用語義鏈

    • 實體內(nèi)部的聯(lián)系：指組成實體的各屬性之間的聯(lián)系毡庆。
    • 實體之間的聯(lián)系：指不同實體之間聯(lián)系。
    • 實體之間的聯(lián)系用菱形框表示

  • 實體之間聯(lián)系的類型：

    • 一對一聯(lián)系 (1:1)
    • 一對多聯(lián)系 (1:n)
    • 多對多聯(lián)系 (m:n)

（五）數(shù)據(jù)的三要素

  • 數(shù)據(jù)結(jié)構(gòu)烙如，包含兩類

    • 與數(shù)據(jù)類型么抗、內(nèi)容、性質(zhì)有關(guān)的對象
    • 與數(shù)據(jù)之間聯(lián)系有關(guān)的對象

  • 數(shù)據(jù)的操作

    • 數(shù)據(jù)提忍琛：在數(shù)據(jù)集合中提取感興趣的內(nèi)容(SELECT)
    • 數(shù)據(jù)更新：變更數(shù)據(jù)庫中的數(shù)據(jù)(INSERT乖坠、DELETE、UPDATE)

  • 數(shù)據(jù)的約束條件：是一組完整性規(guī)則的集合

    • 實體（行）完整性 (Entity integrity)
    • 域（列）完整性 (Domain Integrity)
    • 參考完整性 (Referential Integrity)

（六）范式(NF)

  • 定義：設計關(guān)系數(shù)據(jù)庫時刀闷，為設計出合理的關(guān)系型數(shù)據(jù)庫而遵從的規(guī)范要求
    各種范式呈遞次規(guī)范熊泵，越高的范式數(shù)據(jù)庫冗余越小

  • 目前有六種范式仰迁，設計關(guān)系型數(shù)據(jù)庫時一般需滿足至第三范式的要求

  • 1NF：無重復的列，每一列都是不可分割的基本數(shù)據(jù)項顽分，同一列中不能有多個值徐许，即實體中的某個屬性不能有多個值或者不能有重復的屬性。除去同類型的字段卒蘸，就是無重復的列

  • 2NF：屬性完全依賴于主鍵雌隅，第二范式必須先滿足第一范式，要求表中的每個行必須可以被唯一地區(qū)分缸沃。通常為表加上一個列恰起，以存儲各個實例的唯一標識PK。非PK的字段需要與整個PK有直接相關(guān)性

  • 3NF：屬性不依賴于其他非主屬性趾牧，滿足第三范式必須先滿足第二范式检盼。第三范式要求一個數(shù)據(jù)庫表中不包含已在其它表中包含的非主關(guān)鍵字信息，非PK的字段間不能有從屬關(guān)系

  • 有時出于性能或其他特殊需求的考慮翘单，需要主動違反范式

（七）SQL概念

（1）基本概念：

  • SQL(Structure Query Language)：結(jié)構(gòu)化查詢語言

  • 應用編程接口
    ODBC：Open Database Connectivity
    JDBC：Java Data Base Connectivity

  • 索引：將表中的一個或多個字段中的數(shù)據(jù)復制一份另存吨枉，并且此些需要按特定次序排序存儲

  • 關(guān)系運算：
    選擇：挑選出符合條件的行
    投影：挑選出需要的字段
    連接：表間字段的關(guān)聯(lián)

（2）約束

  • 約束(constraint)：表中的數(shù)據(jù)要遵守的限制

  • 主鍵：一個或多個字段的組合（復合主鍵），填入的數(shù)據(jù)必須能在本表中唯一標識本行哄芜；必須提供數(shù)據(jù)貌亭，即NOT NULL，一個表只能存在一個

  • 惟一鍵：一個或多個字段的組合认臊，填入的數(shù)據(jù)必須能在本表中唯一標識本行圃庭；允許為NULL，一個表可以存在多個

  • 外鍵：一個表中的某字段可填入的數(shù)據(jù)取決于另一個表的主鍵或唯一鍵已有的數(shù)據(jù)

  • 檢查：字段值在一定范圍內(nèi)

（3）數(shù)據(jù)模型
  • 數(shù)據(jù)抽象
    • 物理層：決定數(shù)據(jù)的存儲格式美尸，即RDBMS在磁盤上如何組織文件
    • 邏輯層：描述存儲什么數(shù)據(jù)冤议，以及數(shù)據(jù)間存在什么樣的關(guān)系
    • 視圖層：描述DB中的部分數(shù)據(jù)，是虛擬的表
  • 關(guān)系模型的分類
    • 關(guān)系模型
    • 基于對象的關(guān)系模型
    • 半結(jié)構(gòu)化的關(guān)系模型：XML數(shù)據(jù)

三师坎、MySQL安裝和使用

（一）MySQL和MariaDB

  • MariaDB是MySQL的分支恕酸，軟件使用與MySQL相同
  • yum安裝：CentOS 6 默認安裝MySQL，CentOS 7默認安裝MariaDB
（1）MariaDB的特性
  • 單進程胯陋，多線程
  • 插件式存儲引擎：
    • 存儲引擎有多種實現(xiàn)版本蕊温，功能和特性可能均略有差別，用戶可根據(jù)需要靈活選擇
    • 主要存儲引擎：MyISAM和InnoDB遏乔，當前絕大多數(shù)使用的是InnoDB（支持事務功能）
（2）MariaDB的程序組成：C/S架構(gòu)

Client：mysql, mysqldump, mysqladmin
Server：mysqld_safe, mysqld, mysqld_multi

（3）服務器監(jiān)聽的兩種socket地址
  • ip socket：監(jiān)聽在tcp的3306端口义矛，支持遠程通信
  • unix sock：監(jiān)聽在sock文件上(客戶機路徑：/tmp/mysql.sock, 服務器路徑：/var/lib/mysql/mysql.sock)，僅支持本機通信
    當本機通信時(server: localhost, 127.0.0.1)盟萨，自動使用unix sock

（二）MariaDB安裝

（1）安裝方式

  • 源代碼：編譯安裝

  • 二進制格式的程序包：展開至特定路徑凉翻，并經(jīng)過簡單配置后即可使用

  • 程序包管理器管理的程序包
    項目官方生成yum倉庫配置信息的地址：https://downloads.mariadb.org/mariadb/repositories/

（2）yum安裝和配置MariaDB
  • 第1步，安裝和開啟服務
yum groupinstall mariadb mariadb-client
systemctl start mariadb
systemctl enable mariadb

  • 第2步捻激，提高安全性

    • 安裝并啟動服務后制轰，發(fā)現(xiàn)輸入mysql命令后可以不需輸入密碼直接進入系統(tǒng)
    • 執(zhí)行select user();命令前计，發(fā)現(xiàn)以root@localhost登錄
    • 執(zhí)行select user,host,password from mysql.user;命令，發(fā)現(xiàn)當前的所有用戶都沒有設置密碼垃杖，同時允許匿名登錄數(shù)據(jù)庫男杈，存在很大安全隱患

    • 執(zhí)行腳本mysql_secure_installation修改數(shù)據(jù)庫安全設置

      • 設置數(shù)據(jù)庫管理員root口令
      • 禁止root遠程登錄
      • 刪除anonymous用戶帳號
      • 刪除test數(shù)據(jù)庫

    • 再次嘗試登錄時，發(fā)現(xiàn)已經(jīng)無法匿名登錄了调俘，執(zhí)行mysql -u root -p以root@localhost身份輸入爭取密碼后成功登錄

  • 第3步伶棒，編輯配置文件/etc/my.cnf 
vim /etc/my.cnf
skip-networking=1     //[mysqld]下添加選項，跳過反向解析過程加速啟動
（3）通用二進制格式安裝MariaDB

  • 第1步彩库，創(chuàng)建用戶并制定數(shù)據(jù)庫數(shù)據(jù)的存放目錄
    useradd -r -m -d /app/dbdata -s /sbin/nologin mysql

  • 第2步肤无，準備二進制程序，要求必須解壓到/usr/local目錄下并命名為mysql

tar xvf mariadb-10.2.8-linux-x86_64.tar.gz -C /usr/local/
cd /usr/local
ln -sv mariadb-10.2.8-linux-x86_64/ mysql     //創(chuàng)建軟鏈接便于切換版本
chown -R root:mysql  /usr/local/mysql/
  • 第3步骇钦，準備配置文件
    配置文件查找次序：后面覆蓋前面的配置文件
    /etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
mkdir /etc/mysql/
cp /usr/local/mysql/support-files/my-large.cnf /etc/mysql/my.cnf     //復制模板文件
vim /etc/mysql/my.cnf         //[mysqld]下添加三個選項
datadir = /app/dbdata         //數(shù)據(jù)庫數(shù)據(jù)存放目錄
innodb_file_per_table= on     //innodb引擎下創(chuàng)建的每個表都是一個文件
skip_name_resolve= on         //禁止主機名解析
  • 第4步舅锄，創(chuàng)建數(shù)據(jù)庫文件
cd /usr/local/mysql
./scripts/mysql_install_db --datadir=/app/dbdata --user=mysql
  • 第5步，準備日志文件
//CentOS 7下的操作
mkdir /var/log/mariadb
touch /var/log/mariadb/mariadb.log
setfacl -R -m  u:mysql:rwx /var/log/mariadb/mariadb.log

//CentOS 6下的操作
touch /var/log/mysqld.log
setfacl -R -m u:mysql:rwx /var/log/mysqld.log
  • 第6步司忱，準備服務腳本，并啟動服務
cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld start
  • 第7步畴蹭，創(chuàng)建環(huán)境變量
vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH

source /etc/profile.d/mysql.sh
  • 第8步坦仍，安全初始化
    執(zhí)行腳本：mysql_secure_installation

（三）MariaDB的使用

（1）客戶端工具

  • mysql：命令行交互式客戶端工具

  • mysql選項：
    -u：用戶名，默認為root
    -h：服務器主機叨襟，默認為localhost
    -p：建議使用用戶密碼繁扎，默認為空密碼

  • mysql用戶賬號由兩部分組成：USERNAME@HOST
    HOST用于限制此用戶可通過哪些遠程主機連接mysql服務
    支持使用通配符：

    • % 匹配任意長度的任意字符怒炸，如：
      172.16.0.0/16 或172.16.%.%
    • _ 匹配任意單個字符
（2）常用命令
  • 運行mysql命令：默認空密碼登錄
mysql>select user();     //查看當前用戶
mysql>use mysql;         //選擇進入名為mysql的數(shù)據(jù)庫
mysql>SELECT user,host,password FROM user;
//從當前數(shù)據(jù)庫名為user的表中查詢每行中user,host,password列的數(shù)據(jù)

  • 安全初始化
    /usr/local/mysql/bin/mysql_secure_installation

  • 登錄系統(tǒng)：mysql -uroot -p
    -u 指定用戶名 -p 指定輸入密碼（直接跟密碼時與p之間無空格）

  • 客戶端命令：本地執(zhí)行
    mysql> help：查詢mysql客戶端命令
    每個命令都完整形式和簡寫格式
    mysql> status 或\s

  • 服務端命令：通過mysql協(xié)議發(fā)往服務器執(zhí)行并取回結(jié)果
    每個命令都必須有結(jié)束符號羡宙，默認為分號
    select version();

四丰涉、SQL語言

（一）SQL語言簡介

（1）SQL語句構(gòu)成

由關(guān)鍵詞(Keyword)組成子句(Clause)戈鲁，多條子句組成一條語句

SELECT *           //SELECT子句埋凯，SELECT為關(guān)鍵詞
FROM products      //FROM子句桑包，F(xiàn)ROM為關(guān)鍵詞
WHERE price>400    //WHERE子句尾抑，WHERE為關(guān)鍵詞
（2）SQL語句分類
  • DDL: Data Defination Language 數(shù)據(jù)定義語言
    CREATE, DROP, ALTER
  • DML: Data Manipulation Language 數(shù)據(jù)操作語言
    INSERT, DELETE, UPDATE
  • DCL：Data Control Language 數(shù)據(jù)控制語言
    GRANT, REVOKE
  • DQL：Data Query Language 數(shù)據(jù)查詢語言
    SELECT
（3）SQL語言規(guī)范
  • 大小寫：
    • 關(guān)鍵詞不區(qū)分大小寫（建議用大寫）
    • 但字符串常量區(qū)分大小寫
  • 跨行：
    • 可單行或多行書寫侮邀，每條語句以";"結(jié)尾
    • 關(guān)鍵詞不能跨多行或簡寫
    • 用空格和縮進來提高語句的可讀性
    • 子句通常位于獨立行念链，便于編輯盼忌，提高可讀性
  • 注釋：
    • SQL標準：
      /*注釋內(nèi)容*/ 多行注釋
      -- 注釋內(nèi)容 單行注釋，注意有空格
    • MySQL注釋：
      #注釋內(nèi)容
（4）數(shù)據(jù)庫對象的命名規(guī)則
  • 必須以字母開頭
  • 可包括數(shù)字和三個特殊字符（# _ $掂墓，不建議使用）
  • 不要使用MySQL的保留字
  • 同一個表下的對象不能同名
（5）數(shù)據(jù)庫操作

  • 創(chuàng)建數(shù)據(jù)庫：CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';

  • 刪除數(shù)據(jù)庫：DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

  • 查看支持所有字符集：SHOW CHARACTER SET;

  • 查看支持所有排序規(guī)則：SHOW COLLATION;

  • 獲取命令使用幫助：HELP KEYWORD;

  • 查看數(shù)據(jù)庫列表： SHOW DATABASES;

（二）SQL語言應用

（1）創(chuàng)建谦纱、刪除表操作

  • 語法：

    • 創(chuàng)建表：CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修飾符, col2 type2 修飾符, ...)
    • 刪除表：DROP TABLE [IF EXISTS] tbl_name;

  • 修飾符

    • NULL：數(shù)據(jù)列可包含NULL值
    • NOT NULL：數(shù)據(jù)列不允許包含NULL值
    • DEFAULT：默認值
    • PRIMARY KEY：主鍵
    • UNIQUE KEY：唯一鍵
    • CHARACTER SET name：指定一個字符集
    • AUTO_INCREMENT：自動遞增，適用于整數(shù)類型
    • UNSIGNED：無符號君编，適用于數(shù)值類型

  • 其他與表相關(guān)的操作

    • 查看所有的引擎：SHOW ENGINES;
    • 查看表：SHOW TABLES [FROM db_name];
    • 查看表結(jié)構(gòu)：DESC [db_name.]tbl_name;
    • 查看表創(chuàng)建命令：SHOW CREATE TABLE tbl_name;
    • 查看表狀態(tài)：SHOW TABLE STATUS LIKE 'tbl_name'\G \G豎直顯示
（2）數(shù)據(jù)類型

  • mysql支持的數(shù)據(jù)類型：

    • 數(shù)值類型
    • 日期/時間類型
    • 字符串(字符)類型

  • 選擇正確的數(shù)據(jù)類型對于獲得高性能至關(guān)重要跨嘉，三大原則：

    • 更小的通常更好，盡量使用可正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型
    • 簡單就好吃嘿，簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期
    • 盡量避免NULL祠乃，包含為NULL的列梦重，對MySQL更難優(yōu)化

  • 整型

    • tinyint(m)：1個字節(jié)范圍
    • smallint(m)：2個字節(jié)范圍
    • mediumint(m)：3個字節(jié)范圍
    • int(m)：4個字節(jié)范圍
    • bigint(m)：8個字節(jié)范圍
    • 加unsigned后綴代表正整數(shù)，取值范圍：0至原范圍最大值*2
    • m指select查詢結(jié)果顯示寬度跳纳，不影響實際取值

  • 浮點型忍饰，近似值

    • float(m,d)：單精度浮點型8位精度(4字節(jié)) m：總位數(shù)，d：小數(shù)位位數(shù)
    • double(m,d)：雙精度浮點型16位精度(8字節(jié)) m：總位數(shù)寺庄，d：小數(shù)位位數(shù)

  • 定點型艾蓝，精確值

    • decimal(m,d)：m為總位數(shù)，d為小數(shù)位位數(shù)
    • 占用空間大斗塘、計算消耗大赢织，盡量只在精確計算時使用

  • 字符串型

    • char(n)固定長度，最多255個字符
    • varchar(n)可變長度馍盟，最多65535個字符
    • tinytext可變長度于置，最多255個字符
    • text可變長度，最多65535個字符
    • mediumtext 可變長度贞岭，最多2的24次方-1個字符
    • longtext可變長度八毯，最多2的32次方-1個字符
    • BINARY(M) 固定長度，可存二進制或字符瞄桨，允許長度為0-M字節(jié)话速，
    • VARBINARY(M) 可變長度，可存二進制或字符芯侥，允許長度為0-M字節(jié)
    • 內(nèi)建類型：ENUM枚舉, SET集合

  • 二進制數(shù)據(jù)：BLOB

    • BLOB和text存儲方式不同泊交，TEXT以文本方式存儲，英文存儲區(qū)分大小寫柱查，而Blob是以二進制方式存儲廓俭，不分大小寫
    • BLOB存儲的數(shù)據(jù)只能整體讀出
    • TEXT可以指定字符集，BLOB不用指定字符集

  • 日期時間類型

    • date：日期'2008-12-2'
    • time：時間'12:25:36'
    • datetime：日期時間'2008-12-2 22:06:44'
    • timestamp：自動存儲記錄修改時間
      字段里的時間數(shù)據(jù)會隨其他字段修改的時候自動刷新唉工，這個數(shù)據(jù)類型的字段可以存放這條記錄最后被修改的時間
    • YEAR(2), YEAR(4)：年份

  • 實驗1：創(chuàng)建數(shù)據(jù)庫hellodb并按下表設置建立第一個表student

列名 id name age gender
含義 學號 姓名 年齡 性別
類型 數(shù)值 字符串 數(shù)值 字符串
范圍 0-200 不超過20個字符 0-100 1個字符
其他 主鍵 不允許為空 無 默認為"m" 
create database hellodb;
use hellodb;
create table student (id tinyint unsigned primary key, name varchar(20) not null, age tinyint unsigned, gender char(1) default "m");
desc student;
  • 實驗2：在hellodb數(shù)據(jù)庫中創(chuàng)建表student1研乒，字段設置與實驗1基本相同，唯一的區(qū)別是：id和name構(gòu)成復合主鍵
use hellodb;
create table student1 (id tinyint unsigned, name varchar(20) not null, age tinyint unsigned, gender char(1) default "m", primary key(id, name));
desc student1;
（3）修改表操作
  • 語法：
ALTER TABLE 'tbl_name'

ADD col_name data_type [FIRST|AFTER col_name]        //添加字段
CHANGE COLUMN old_col_name  new_col_name data_type   //修改字段名
MODIFY col_name data_type                            //修改字段屬性
DROP COLUMN col_name                                 //刪除字段

  • 實驗3：對數(shù)據(jù)庫hellodb的表student一次做如下操作：

    • 在name字段后增加字段phone酵紫，類型為varchar(11)
      alter table student add phone varchar(11) after name;
    • 修改phone字段的屬性為int
      alter table student modify phone int;
    • 將name字段定義為唯一鍵
      alter table student add unique key(name);
    • 建立age字段的索引
      alter table student add index(age);
      show indexes from student; 查看表student建立索引的字段

    主鍵和唯一鍵都會自動建立索引告嘲，name字段的索引也可以看到

    • 刪除phone字段
      alter table student drop phone;
（4）索引

  • 索引是特殊數(shù)據(jù)結(jié)構(gòu)，定義查找時作為查找條件的字段

    • 優(yōu)點：提高查詢速度
    • 缺點：占用額外空間奖地，影響插入速度

  • 創(chuàng)建索引：必須要有索引名稱
    CREATE INDEX index_name ON tbl_name(index_col_name,...);

  • 刪除索引：
    DROP INDEX index_name ON tbl_name;

  • 查看索引：
    SHOW INDEXES FROM [db_name.]tbl_name;

  • 實驗4：索引的添加橄唬、查看、刪除

    • 添加表studen字段gender的索引
      create index genderindex on student(gender);
    • 查看表student的索引（豎列顯示）
      show indexes from student\G;
    • 刪除表student字段gender的索引
      drop index genderindex on student;
（5）DML語句：INSERT, UPDATE, DELETE

  • INSERT：插入行
    INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (val1,...),(...),...

  • UPDATE：更新行
    UPDATE tbl_name SET col1=val1, col2=val2, ... [WHERE clause];

  • DELETE：刪除行
    DELETE FROM tbl_name [WHERE clause];

  • TRUNCATE TABLE tbl_name：清空表

  • 實驗5：SQL的DML語句應用

    • 測試1：按照下表的內(nèi)容参歹，對表student進行賦值
    id name age gender
    1 zhao 25 m
    2 qian 18 f
    3 sun 30 m
    4 li 27 m
    5 zhou 22 f 
    insert into student (id, name, age, gender) values (1, 'zhao', 25, 'm');   //完整寫法
insert into student (name, id, gender, age) values ('qian', 2, 'f', 18);   //注意順序   
insert into student values (3, 'sun', 30, 'm');    //可以省略字段描述仰楚，但賦值按照字段默認順序
insert into student values (4, 'li', 27, 'm'), (5, 'zhou', 22, 'f');        //可以一次賦多個值

    • 測試2：復制student表并命名為表student2，清空表中的內(nèi)容
      create table student2 select * from student

      當空表的字段設置與原表完全相同時，可以使用insert語句復制表內(nèi)容：
      insert into student2 select * from student;

      truncate table student2 truncate語句的效率比delete高僧界，但是由于truncate不記錄日志侨嘀，刪除后的信息無法恢復

    • 測試3：修改、刪除表記錄
      將zhao的年齡改為24
      update student set age=24 where id=1

    將li的年齡改為29捂襟，性別改為f
    update student set age=29, gender='f' where name='li'

    刪除name為li的記錄
    delete from student where name='li';

（6）DQL語句：SELECT

  • 語法：
    SELECT col1,col2,... FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];
    Limit m,n：跳過m個咬腕，要n個

  • 字段表示法：
    *：所有字段
    AS：字段別名，col1 AS alias1

  • WHERE 子句：條件描述方式

    • 操作符：
      • 關(guān)系：>, <, >=, <=, ==, !=
      • 范圍：BETWEEN ... AND ...
      • 模糊匹配 LIKE
        %：任意長度的任意字符
        _：任意單個字符
      • 正則表達式模式匹配：RLIKE
      • 空葬荷，非空判斷：IS NULL 涨共，IS NOT NULL
      • 枚舉：IN （val1,val2,…）
    • 條件邏輯操作：and，or宠漩，not

  • 實驗6：SELECT語句的使用
    當前表student內(nèi)容如下：

    id name age gender
    1 zhao 25 m
    2 qian 18 f
    3 sun 30 m
    4 li 27 m
    5 zhou 22 f
    6 wu 17 m
    7 zheng 29 NULL
    8 wang 33 NULL

    查詢本表信息举反，依次實現(xiàn)以下功能：

    • 查詢本表中字段id, name,age的信息，顯示時id字段顯示“編號”扒吁，name字段顯示“姓名”火鼻，age字段顯示“年齡”
      select id as 編號, name as 姓名, age as 年齡 from student;
    • 查詢本表中年齡大于等于20并且小于等于30的記錄
      select * from student where age >= 20 and age <= 30;或者
      select * from student where age between 20 and 30;
    • 查詢本表中姓名以'z'開頭的記錄，輸出時以年齡升序排序
      select * from student where name like 'z%' order by age;
    • 查詢本表中姓名不以'w'開頭雕崩，并且不以'n'結(jié)尾的記錄
      select * from student where name rlike '^[^w].*[^n]$';
    • 查詢本表中性別信息為空的記錄魁索，輸出時以編號降序排序
      select * from student where gender is null order by id desc;
    • 查詢本表中姓名為"qian", "li", "zhou"的記錄
      select * from student where name in ('qian', 'li', 'zhou');
（7）用戶賬號管理

  • 格式：'user'@'host'
    user：用戶名
    host：允許用戶通過哪些主機遠程連接mysqld服務，包含：
    IP盼铁、網(wǎng)絡地址蛾默、主機名、通配符(%和_)

  • 創(chuàng)建用戶：
    CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];

  • 查看當前用戶:
    SELECT user();

  • 查看用戶：
    SELECT user,host,password FROM user;

  • 刪除用戶：
    DROP USER 'username'@'host';

  • 更改口令：

    • 方法一：
      SET PASSWORD FOR 'user'@'host' = PASSWORD('password');
    • 方法二：
      mysqladmin -u root -pold_password password 'new_password'’
（8）授權(quán)

  • 給用戶授權(quán)：
    GRANT priv_type,... ON [object_type] db_name.tb_name TO 'user'@'host' [IDENTIFIED BY 'password'];

    • priv_type：ALL [INSERT, UPDATE, DELETE, SELECT...]
    • db_name.tb_name:
      *.*：所有庫的所有表
      db_name.*：指定庫的所有表
      db_name.tb_name：指定庫的指定表
      db_name.routine_name：指定庫的存儲過程和函數(shù)

  • 查看指定用戶獲得的授權(quán)：
    SHOW GRANTS FOR 'user'@'host';

  • 回收授權(quán)：
    REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host
    FLUSH PRIVILEGES：手動讓MariaDB的服務進程重讀授權(quán)表

  • 實驗7：用戶賬號管理捉貌、授權(quán)管理

    • 創(chuàng)建賬號hellopeiyang@192.168.136.130并授權(quán)其可以查詢和添加hellodb數(shù)據(jù)庫中的所有表，密碼為'hello'
      grant select, insert on hellodb.* to hellopeiyang@192.168.136.130 identified by 'hello';

    • 查看賬號hellopeiyang@192.168.136.130獲得的授權(quán)
      show grants for hellopeiyang@192.168.136.130;

    • 回收賬號hellopeiyang@192.168.136.130在hellodb數(shù)據(jù)庫的所有表的全部授權(quán)
      revoke all on hellodb.* from hellopeiyang@192.168.136.130;

    • 刪除賬號hellopeiyang@192.168.136.130
      drop user hellopeiyang@192.168.136.130;

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末冬念，一起剝皮案震驚了整個濱河市趁窃，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌急前，老刑警劉巖醒陆，帶你破解...
    沈念sama閱讀 206,126評論 6贊 481
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異裆针，居然都是意外死亡刨摩，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,254評論 2贊 382
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門世吨，熙熙樓的掌柜王于貴愁眉苦臉地迎上來澡刹，“玉大人，你說我怎么就攤上這事耘婚“战剑” “怎么了？”我有些...
    開封第一講書人閱讀 152,445評論 0贊 341
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長嚷闭。 經(jīng)常有香客問我攒岛，道長，這世上最難降的妖魔是什么胞锰？ 我笑而不...
    開封第一講書人閱讀 55,185評論 1贊 278
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任灾锯，我火速辦了婚禮，結(jié)果婚禮上嗅榕，老公的妹妹穿的比我還像新娘顺饮。我一直安慰自己，他們只是感情好誊册，可當我...
    茶點故事閱讀 64,178評論 5贊 371
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布领突。 她就那樣靜靜地躺著，像睡著了一般案怯。 火紅的嫁衣襯著肌膚如雪君旦。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 48,970評論 1贊 284
  • 城市分裂傳說
    那天嘲碱，我揣著相機與錄音金砍，去河邊找鬼。 笑死麦锯，一個胖子當著我的面吹牛恕稠，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播扶欣，決...
    沈念sama閱讀 38,276評論 3贊 399
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼鹅巍，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了料祠？” 一聲冷哼從身側(cè)響起骆捧，我...
    開封第一講書人閱讀 36,927評論 0贊 259
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎髓绽，沒想到半個月后敛苇，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 43,400評論 1贊 300
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡顺呕，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 35,883評論 2贊 323
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年枫攀，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片株茶。...
    茶點故事閱讀 37,997評論 1贊 333
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡来涨，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出启盛，到底是詐尸還是另有隱情扫夜，我是刑警寧澤，帶...
    沈念sama閱讀 33,646評論 4贊 322
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站笤闯，受9級特大地震影響堕阔，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜颗味，卻給世界環(huán)境...
    茶點故事閱讀 39,213評論 3贊 307
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一超陆、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧浦马，春花似錦时呀、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,204評論 0贊 19
  • 一樁弒父案谨娜，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至磺陡，卻和暖如春趴梢，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背币他。 一陣腳步聲響...
    開封第一講書人閱讀 31,423評論 1贊 260
  • 情欲美人皮
    我被黑心中介騙來泰國打工坞靶， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人蝴悉。 一個月前我還...
    沈念sama閱讀 45,423評論 2贊 352
  • 代替公主和親
    正文 我出身青樓彰阴，卻偏偏與公主長得像，于是被迫代替她去往敵國和親拍冠。 傳聞我的和親對象是個殘疾皇子尿这，可洞房花燭夜當晚...
    茶點故事閱讀 42,722評論 2贊 345

推薦閱讀更多精彩內(nèi)容

  • 什么是數(shù)據(jù)庫？ 數(shù)據(jù)庫是存儲數(shù)據(jù)的集合的單獨的應用程序庆杜。每個數(shù)據(jù)庫具有一個或多個不同的API妻味，用于創(chuàng)建，訪問欣福，管理...
    chen_000閱讀 4,030評論 0贊 19
  • 1. Java基礎部分 基礎部分的順序：基本語法，類相關(guān)的語法焦履，內(nèi)部類的語法拓劝，繼承相關(guān)的語法，異常的語法嘉裤，線程的語...
    子非魚_t_閱讀 31,581評論 18贊 399
  • 一. Java基礎部分.................................................
    wy_sure閱讀 3,790評論 0贊 11
  • 1郑临、MySQL啟動和關(guān)閉（安裝及配置請參照百度經(jīng)驗，這里不再記錄屑宠。MySQL默認端口號：3306厢洞；默認數(shù)據(jù)類型格式...
    強壯de西蘭花閱讀 634評論 0贊 1
  • 讀書讀得越多應該越有錢，讀了很多書但是仍然很窮的就是傻逼。每一個中年危機的背后就有一個年輕時代的傻逼躺翻。 ???
    林斯2閱讀 90評論 0贊 0