資料庫系統原理（第四章：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;

1. 字符集（character set）：定義了字符以及字符的編碼，
2. 字符序（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，

四、資料更新（資料操縱語言DML）

插入資料 

• INSERT…VALUES陳述句
• INSERT…SET陳述句
• INSERT…SELECT陳述句

例如：

1、使用INSERT…VALUES陳述句向資料庫mysql_test的表customers中 插入這樣一行完整資料：（901，張三 ，F，北京市，朝陽區）

　　INSERT INTO mysql_test.customers    VALUES (901,’張三’,’F’,’北京市’,’朝陽區’);

2、使用INSERT…VALUES陳述句向資料庫mysql_test的表customers中插入這樣一行 資料，要求該資料目前只用明確給出cust_name列和cust_address列的資訊，即分別為 ‘李四’‘武漢’，而cust_id由系統自動生成，cust_sex列選用表中默認值，另外 cust_contact列的值暫不確定，可不用指定

INSERT INTO mysql_test.customers   VALUES (0,’李四’,DEFAULT,’武漢市’,NULL);

********************使用INSERT…SET陳述句插入部分列值資料***************************

語法：INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},…

例如：使用INSERT…SET陳述句向資料庫mysql_test的表customers中插入 資料：名為李四 ，地址為武漢，性別默認

insert into mysql_test.customers set cu_name="李四"，cu_address="武漢"，cu_sex=DEFAULT；

***********************使用INSERT…SELECT陳述句插入子查詢資料****************************

語法：INSERT [INTO] tbl_name [(col_name,…)] SELECT…

例句：INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name；

洗掉資料

語法：DELETE FROM tbl_name [WHERE where_condition ] [ORDER BY … ] [LIMIT row_count ]

例如：使用DELETE陳述句洗掉資料庫mysql_test的表customers中客戶名 為“李四”的客戶資訊，

delete from mysql_test.customers where cu_name="李四"；

修改資料

語法：UPDATE tbl_name SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]… [WHERE where_condition] [ORDER BY …] [LIMIT row_count]

例如：使用UPDATE陳述句將資料庫mysql_test的表customers中姓名為“ 張三”的客戶的地址更新為“武漢”

UPDATE mssql_test.customers   SET cust_address=‘武漢市’   WHERE cust_name=‘張三’;

五、資料查詢

 SELECT陳述句

 例如：查詢資料庫mysql_sest的表customers中各個客戶的姓名、性別和地址資訊

        SELECT cust_name,cust_sex,cust_address   FROM mysql_test.customers;

定義并使用列的別名：SELECT cust_name,cust_address AS 地址,cust_contact    FROM mysql_test.customers;

替換查詢結果集中的資料

SELECT 
CASE id
WHEN 1 then "yf"
when 2 then "cs"
WHEN 3 THEN "yw"
WHEN 4 THEN "jl"
ELSE "qt" END AS NAME
FROM tb_dept;

 聚合函式通常是資料庫系統中一類系統（內置函式）

*******************FROM子句與多表連接查詢**********************

交叉連接，又稱笛卡爾積

• SELECT * FROM tbl1 CROSS JOIN tbl2;
• SELECT * FROM tbl1,tbl2

內連接：SELECT   *  FROM tb_student INNER JOIN tb_score   ON tb_student.studentNo=tb_score.studentNo

• 關于內連接的使用，可以將一個表與它本身進行連接，這種連接方式稱為自連接；
• 關于內連接的使用，如若在ON子句的連接條件中使用運算子“=”，則此 連接方式為（等值連接）

外連接：

• 左外連接：在FROM子句中使用關鍵字LEFT OUTER JOIN或LEFT JOIN
• 右外連接：在FROM子句中使用關鍵字RIGHT OUTER JOIN或RIGHT JOIN

問答題：寫出在MySQL中，內連接的語法格式，

簡述左外連接和右外連接的區別，

• 左外連接：也稱左連接，以左表為基表，在FROM子句中使用關鍵字“LEFT OUTER JOIN”或關鍵字“LEFT JOIN”來連接兩張表，
• 右外連接：也稱右連接，以右表為基表，在FROM子句中使用關鍵字“RIGHT OUTER JOIN”或關鍵字“RIGHT JOIN”來連接兩張表，

where子句

判定范圍：

1、當查詢的過濾條件被限定在值的某個范圍時，可以使用關鍵字“BETWEEN”，

例如：SELECT * FROM mysql_test.customers ->WHERE cust_id BETWEEN 903 AND 912；

2、使用關鍵字“IN”可以指定一個值的列舉表，該表中會列出所有可能的值

判定空值： is  null ；is not null

子查詢—結合關鍵字“IN”使用的子查詢：主要用于判定一個給定值是否存在于子查詢的結果集中

子查詢—結合關鍵字“EXISTS”使用的子查詢：子查詢的結果集不為空，則回傳TRUE，否則回傳FALSE

GROUP BY子句與分組資料:

HAVING子句:HAVING where_condition(指定過濾條件)

having 陳述句與實際有出入為了考試請按照書本上的來哪怕他錯了

 ORDER BY子句：排序

例句：在資料庫mysql_test的表customers中依次按照客戶姓名和地址的降序方式輸出客戶的姓名和性別

SELECT cust_name,cust_sex FROM mysql_test.customers -> ORDER BY cust_name DESC,cust_address DESC;

 LIMIT：使用LIMIT子句限制被SELECT陳述句回傳的行數

六、視圖

什么是視圖：

• 視圖是資料庫中的一個物件，它是資料庫管理系統提供給用戶的以多種角度觀察資料庫中資料的一種重要機制，
• 視圖不是資料庫中真實的表，而是一張虛擬表，其自身并不存盤資料，

使用視圖的優點

• 集中分散資料
• 簡化查詢陳述句
• 重用SQL陳述句
• 保護資料安全
• 共享所需資料
• 更改資料格式

使用CREATE VIEW創建視圖

語法；CREATE VIEW view_name[(column_list)]  AS select_statement  [WITH [CASCADED | LOCAL] CHECK OPTION]

• create or replace view的意思就是若資料庫中已經存在這個名字的視圖的話，就替代它，若沒有則創建視圖；
• create則不進行判斷，若資料庫中已經存在的話，則報錯，說物件已存在；

在資料庫mysql_test中創建視圖customers_view，要求該視圖包含客戶資訊表customers中所有男客戶的資訊，并且要求保證今后對該視圖資料的修改都必須符合客戶性別為男性這個條件

CREATE OR REPLACE VIEW mysql_test.customers_view   AS  SELECT*FROM mysql_test.customers  WHERE cust_sex=‘M’  WITH CHECK OPTION;

使用DROP VIEW陳述句洗掉視圖：

DROP VIEW [IF EXISTS] view_name [,view_name]… [RESTRICT | CASCADE]

使用ALTER VIEW陳述句對已有視圖的定義（結構）進行修改

ALTER VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL]CHECK OPTION]

使用SHOW CREATE VIEW陳述句查看已有視圖的定義（結構）

SHOW CREATE VIEW view_name

使用DELETE陳述句通過視圖洗掉基本表的資料：

示例：洗掉視圖customers_view中姓名為“周明”的客戶資訊

DELETE FROM mysql_test.customers_view -> WHERE cust_name=‘周明’

在視圖customers_view中查找客戶id號為905的客戶姓名及其地址

標籤：MySQL

上一篇：win下maridb 10.1.8下主從復制配置

下一篇：windows下安裝mysql