資料庫系統原理（第四章：SQL與關係資料庫基本操作）

嵌入式和動態SQL規則：規定了SQL語句在高級語言程式設計中使用的規範方法，以便適應較為複雜的應用 ...

一、SQL概述

sql是結構化查詢語言（Structured Query Language，SQL）是專門用來與數據庫通信的語言，它可以幫助用戶操作關係資料庫。

SQL的特點：

SQL不是某個特定資料庫供應商專有的語言； SQL簡單易學；SQL強大、靈活，可以進行非常複雜和高級的資料庫操作

SQL的組成：

數據查詢
數據定義語言（Data Definition Language,DDL）
數據操縱（DML）
數據控制（DCL）

*******************數據定義語言**********************

CREATE 創建資料庫或資料庫對象

ALTER 對資料庫或資料庫對象進行修改

DROP 刪除資料庫或資料庫對象

************數據操縱語言（Data Manipulation Language,DML）***********

SELECT 從表或視圖中檢索數據

INSERT 將數據插入到表或視圖中

UPDATE 修改表或視圖中的數據

DELETE 從表或視圖中刪除數據

***************數據控制語言（Data Control Language,DCL）****************

GRANT 用於授予許可權

REVOKE 用於收回許可權

二、MySQL預備知識

嵌入式和動態SQL規則：規定了SQL語句在高級語言程式設計中使用的規範方法，以便適應較為複雜的應用

SQL 調用（以便提高SQL的靈活性、有效性、共用性以及使SQL具有更多的高級語言的特征）：SQL 例程、調用規則

MySQL使用基礎：LAMP模式、WAMP模式 L（Linux） A（Apache）M（MySQL） P(PHP、Perl、Python)

關係資料庫管理系統（RDBMS）：優點：體積小、速度快、開放源代碼、遵循GPL

MySQL擴展語言要素

常量：也稱字面值或標量值

字元串常量：用單引號或雙引號括起來的字元序列，分為ASCII字元串常量和Unicode字元串常量
數值常量：整數常量、浮點數常量
十六進位常量：每對十六進位數字被轉換為一個字元，其最前面有一個字母“X”（或“x”）
時間日期常量：用單引號將表示日期時間的字元串括起來而構成的例如：’2018-06-05’
位欄位值
布爾值：TRUE：1 ；FALSE： 0
NULL值

變數：

用戶變數：用戶變數前常添加一個符號@，用於將其與列名區分開
系統變數：大多數系統變數應用於其他SQL語句中時，必須在系統變數前添加兩個@

表達式 表達式是常量、變數、列名、複雜計算、運算符和函數的組合。

字元型表達式
數值型表達式
日期型表達式

三、數據定義（DDL）

創建資料庫（CREATE）：使用CREATE DATABASE 或 CREATE SCHEMA語句

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
 [DEFAULT]CHARACTER SET[=]charset_name
 |[DEFAULT]COLLATE[=]collation_name

例句：create DATABASE test_sy CHARACTER SET ="UTF8" COLLATION ="utf8_general_ci"； CREATE DATABASE mysql_test;

字元集（character set）：定義了字元以及字元的編碼。
字元序（collation）：指定字元集的校對規則。

查看資料庫（SHOW）：查看資料庫的所有表

SHOW {DATABASES | SCHEMAS}
 [LIKE’pattern’ | WHERE expr]

Like關鍵字用於匹配指定的資料庫名稱； Where從句用於指定資料庫名稱查詢範圍的條件

例句：SHOW DATABASES; SHOW DATABASES LIKE "%test%"；

SHOW TABLES：查看資料庫非系統表

選擇資料庫（USE）：USE db_name; 從一個資料庫“跳轉”到另一個資料庫。

修改資料庫：alert

mysql>ALTER DATABASE mysql_test
 -> DEFAULT CHARACTER SET gb2312
 -> DEFAULT COLLATE gb2312_chinese_ci;

刪除資料庫：

DROP{DATABASE|SCHEMA}[IF EXISTS]db_name

例句：DROP DATABASE hahaha; DROP DATABASE IF EXISTS hahaha;

*****************************表定義**********************************

創建表：數據表是關係資料庫中最重要、最基本的數據對象，也是數據存儲的基本單位。

數據表，被定義為欄位的集合按（行）和（列）的格式來存儲的，每一（行）代表一條記錄，每一（列）代表記錄中一個欄位的取值。

CREATE[TEMPORARY]TABLE tbl_name
 (
 欄位名1 數據類型 [列級完整性約束條件][預設值]
 [，欄位名2 數據類型 [列級完整性約束條件][預設值]]
 [，……]
 [，表級完整性約束條件]
 )[ENGINT=引擎類型]；

若添加“TEMPORARY”，則為臨時表

在一個已有資料庫mysql_test中新建一個包含客戶姓名、性別、地址、聯繫方式等內容的客戶基本信息表，要求將客戶的id號指定為該表的主鍵。

PRIMARY KEY(cust_id)：指定主鍵

數據類型：

整型int
浮點型double
布爾型bool
日期型date
時間戳timestamp
時間型time 定長
字元類型char
可變長字元varchar

更新表（ALTER）：使用ALTER TABLE語句，增加或刪減列、創建或取消索引、更改原有列的數據類型、重新命名列或表，更改表的評註和表的引擎類型，為表重新創建觸發器、存儲過程、索引和外鍵等。

1、ADD COLUMN

例如：向資料庫mysql_test的表customers中添加一列，並命名為 cust_city，要求其不能為NULL，預設值為字元串“Wuhan”，且該列位於原表cust_sex列之後。

ALTER TABLE mysql_test.customers ->ADD COLUMN cust_city char(10)NOT NULL DEFAULT ‘Wuhan’ AFTER cust_sex;

2、CHANGE[COLUMN]子句修改表中列的名稱或數據類型

3、ALTER[COLUMN]子句修改或刪除表中指定列的預設值

　　ALTER TABLE mysql_test.customers ->ALTER COLUMN cust_city SET DEFAULT ‘Beijing’;

4、MODIFY[COLUMN]子句只修改指定列的數據類型，不會幹涉它的列名

　　ALTER TABLE mysql_test.customers ->MODIFY COLUMN cust_name char(20) FIRST;

5、DROP[COLUMN]子句刪除表中多餘的列

ALTER TABLE mysql_test.customers ->DROP COLUMN cust_contact;

6、RENAME[TO]子句為表重新賦予一個表名

ALTER TABLE mysql_test.customers ->RENAME TO mysql_test.backup_customers;

給表重命名錶的第二種寫法：RENAME TABLE db_a.old_table TO db_b.new_table;

7、DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [ ，tbl_name ] … [RESTRICT | CASCADE]

查看表結構：

SHOW [FULL] COLUMNS {FROM | IN} tbl_name[{FROM | IN} db_name] [LIKE’pattern’ | WHERE expr]

例如：SHOW COLUMNS FROM t_role;

{DESCRIBE | DESC} tbl_name [col_name | wild]

例如：DESC t_role;

********************************************索引****************************************

索引：索引是提高數據文件訪問效率的有效方法

索引存在的弊端： 1）索引是以文件的形式存儲的，如果有大量的索引，索引文件可能比數據文件更快達到最大的文件尺寸； 2）索引在提高查詢速度的同時，會降低更新表的速度。

索引的分類：索引通常被創建成單列索引和組合索引

普通索引 INDEX或KEY
唯一性索引 UNIQUE
主鍵 PRIMARY KEY

索引的創建 Create index

例如：在資料庫mysql_test的表customers上，根據客戶姓名列的前三個字元創建一個升序索引index_customers。

create index index_customers on mysql_test.customers (name(3) ASC);

在資料庫mysql_test的表customers上，根據客戶姓名列和客戶id 號創建一個組合索引index_cust。

create index index_cust on mysql_test.customers (cust_name,cust_id);

2、語法項[CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] (index_col_name,…),
用於表示在創建新表的同時創建該表的唯一性索引；
3、語法項[CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,…),
用於表示在創建新表的同時創建該表的外鍵；

索引的創建：使用ALTER TABLE語句創建

1）語法項ADD {INDEX|KEY} [index_name](index_col_name,…), 用於表示在修改表的同時為該表添加索引；
2）語法項ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,…), 用於表示在創建新表的同時為該表添加主鍵；
3）語法項ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] (index_col_name,…), 用於表示在修改表的同時為該表添加唯一性索引；
4）語法項ADD [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,…), 用於表示在創建新表的同時為該表添加外鍵

例如：使用ALTER TABLE語句在資料庫mysql_test中表seller的姓名上添加一列非唯一的索引，取名為index_seller_name。

ALTER TABLE mysql_test.seller ADD INDEX index_seller_name(seller_name);

索引的查看（SHOW INDEX ）

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name ] [WHERE expr]

SHOW index FROM t_test.tb_dept;

索引的刪除：使用DROP INDEX語句

DROP INDEX index_name ON tbl_name；

索引的刪除：使用ALTER TABLE語句

1）選用DROP PRIMARY KEY子句用於刪除表中的主鍵，由於一個表中只有一個主鍵，其也是一個索引；
2）選用DROP INDEX子句用於刪除各種類型的索引；
3）選用DROP FOREIGN KEY子句用於刪除外鍵。

例如：使用ALTER TABLE語句刪除資料庫mysql_test中表customers的主鍵和索引 index_customers。

數據更新

數據查詢

視圖

資料庫系統原理（第四章：SQL與關係資料庫基本操作 ）

一、SQL概述

二、MySQL預備知識

三、數據定義（DDL）

資料庫系統原理（第四章：SQL與關係資料庫基本操作）