MySQL基礎(chǔ)

一,、 了解數(shù)據(jù)庫(kù)

1.1. 相關(guān)概念

• 數(shù)據(jù)庫(kù)（database,，簡(jiǎn)稱DB）：保存有組織的數(shù)據(jù)的容器（通常是一個(gè)文件或一組文件）。

  誤用導(dǎo)致混淆 人們通常用數(shù)據(jù)庫(kù)這個(gè)術(shù)語(yǔ)來(lái)代表他們使用的數(shù)據(jù)庫(kù)軟件,。這是不正確的,，確切地說(shuō),數(shù)據(jù)庫(kù)軟件應(yīng)當(dāng)稱為DBMS（數(shù)據(jù)庫(kù)管理系統(tǒng)）,。數(shù)據(jù)庫(kù)是通過(guò)DBMS創(chuàng)建和操縱的容器。數(shù)據(jù)庫(kù)可以是保存在硬設(shè)備上的文件,，但也可以不是,。

• 數(shù)據(jù)庫(kù)管理系統(tǒng)（Database Management System，簡(jiǎn)稱DBMS）：是為管理數(shù)據(jù)庫(kù)而設(shè)計(jì)的電腦軟件系統(tǒng),，一般具有存儲(chǔ),、截取、安全保障,、備份等基礎(chǔ)功能,。

1.2. 關(guān)系型數(shù)據(jù)庫(kù)

? 關(guān)系型數(shù)據(jù)庫(kù)（Relational database）：是創(chuàng)建在關(guān)系模型上的數(shù)據(jù)庫(kù)，以行列的形式來(lái)存儲(chǔ)數(shù)據(jù),。方便用戶的理解,，在關(guān)系型數(shù)據(jù)庫(kù)中一系列的行和列稱為表，一組表組成數(shù)據(jù)庫(kù),。（單庫(kù)的表容量是固定：可以進(jìn)行分庫(kù)分表的操作）,，可以將關(guān)系型數(shù)據(jù)庫(kù)理解為二維數(shù)據(jù)表格模型，而一個(gè)關(guān)系型數(shù)據(jù)庫(kù)是由二維表及其之間的關(guān)系組成的數(shù)據(jù)的組織,。

1.2.1 常用的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)

• MySQL

  最受歡迎的開源的SQL數(shù)據(jù)庫(kù)管理系統(tǒng)

  2003年Mysql5.0：支持SQL特性,，事務(wù)，視圖,、存儲(chǔ)過(guò)程,、觸發(fā)器等功能。

  2010年MySQL5.5,，InnoDB存儲(chǔ)引擎變?yōu)镸ySQL的默認(rèn)存儲(chǔ)引擎,。

  優(yōu)勢(shì)：

  • MySQL是開放源代碼的，可以免費(fèi)使用（甚至可以修改源碼）
  • MySQL服務(wù)器是一個(gè)快速的,、易于使用的數(shù)據(jù)庫(kù)服務(wù)器
  • MySQL可以在不同的操作系統(tǒng)中使用

• MariaDB

  是由MySQL的創(chuàng)始人主導(dǎo)開發(fā)的,。擔(dān)心Oracle將MySQL閉源。目前大型的互聯(lián)網(wǎng)公司紛紛拋棄MySQL

  轉(zhuǎn)入到MariaDB,。

• PostgreSQL

  完整的支持了SQL標(biāo)準(zhǔn),，開源，可以在不同的操作系統(tǒng)中運(yùn)行,。

• Oracle數(shù)據(jù)庫(kù)

  最先將關(guān)系型數(shù)據(jù)庫(kù)轉(zhuǎn)到桌面計(jì)算機(jī)上,。客戶/服務(wù)器結(jié)構(gòu)的概念,。

  Oracle數(shù)據(jù)庫(kù)的優(yōu)勢(shì)：

  • 兼容性（采用SQL標(biāo)準(zhǔn)）
  • 可移植性（window,linux,unix,dos）
  • 可連接性（支持各種網(wǎng)絡(luò)傳輸協(xié)議：TCP/IP,,、DECnet,LU6.2)
  • 高生產(chǎn)率（提供了多種開發(fā)工具，可以方便用戶快速的開發(fā)）
  • 開放性（oracle良好的兼容性,、可以移植性,、可連接性和高生產(chǎn)率使用oracle具有良好的開放性)

• SQL Server

  微軟旗下,，和.net，在國(guó)內(nèi)廣泛用于電力,，保險(xiǎn)等行業(yè),。2017版之前的SQL Server只支持windows操作

  系統(tǒng)。2017年后SQL Server可以運(yùn)行在windows,linux,docker等平臺(tái),。

• SQLite

  廣泛應(yīng)用與嵌入式開發(fā)中,。

• Sybase

  PowerDesginer數(shù)據(jù)庫(kù)建模工具,。

1.2.2 SQL語(yǔ)言

? SQL（Structured Query Language：結(jié)構(gòu)化查詢語(yǔ)言）,。

? SQL是數(shù)據(jù)庫(kù)查詢和設(shè)計(jì)語(yǔ)言，用于存取數(shù)據(jù),、查詢,、更新、管理關(guān)系數(shù)據(jù)庫(kù),。與其他程序設(shè)計(jì)語(yǔ)言的差別是,，SQL由很少的關(guān)鍵字組成，每個(gè)SQL語(yǔ)言通過(guò)一個(gè)或多個(gè)關(guān)鍵字構(gòu)成,。

? SQL基于關(guān)系代數(shù)和元組關(guān)系演算,，包括一個(gè)數(shù)據(jù)定義語(yǔ)言和數(shù)據(jù)操縱語(yǔ)言。SQL的范圍包括數(shù)據(jù)插入,、查詢,、更新和刪除，數(shù)據(jù)庫(kù)模式創(chuàng)建和修改,，以及數(shù)據(jù)訪問控制,。盡管SQL經(jīng)常被描述為，而且很大程度上是一種聲明式編程（4GL）,，但是其也含有過(guò)程式編程的元素,。

? SQL的優(yōu)點(diǎn)：

• 幾乎所有的RDBMS都支持SQL。

• SQL簡(jiǎn)單易學(xué),。

• 使用方式靈活：SQL2種使用方式,，可以直接以命令方式交互使用；也可以嵌入到其他程序設(shè)計(jì)語(yǔ)

  言中使用(jdbc)

• 非過(guò)程化：“做什么",不需要使用sql告訴計(jì)算機(jī)"怎么做"

注意：

SQL語(yǔ)句不區(qū)分大小寫,，對(duì)SQL中的關(guān)鍵字進(jìn)行大寫,，而對(duì)表名、列名,、數(shù)據(jù)庫(kù)名稱使用小寫,。可

以提高代碼的閱讀星和可維護(hù)性,。

SQL包含四個(gè)部分：

• DDL(Data Defifinition Language)：數(shù)據(jù)定義語(yǔ)言

  CREATE,DROP,ALTER
  

• DML(Data Manipulate Language)：數(shù)據(jù)操縱語(yǔ)言

  INSERT,UPDATE,DELETE
  

• DQL(Data Query Lanaguage)：數(shù)據(jù)查詢語(yǔ)言

  SELECT
  

• DCL(Data Control Language)：數(shù)據(jù)控制語(yǔ)言

  commit,rollback
  

1.2.3 相關(guān)概念

• 表

  表（table） 某種特定類型數(shù)據(jù)的結(jié)構(gòu)化清單

  模式（schema） 關(guān)于數(shù)據(jù)庫(kù)和表的布局及特性的信息

• 列和數(shù)據(jù)類型**

  列（Colum） 表中的一個(gè)字段,。所有的表都是由一個(gè)或多個(gè)列組成的,。

  數(shù)據(jù)類型（datatype） 所允許的數(shù)據(jù)的類型。每個(gè)表列都有相應(yīng)的數(shù)據(jù)類型,，它限制（或允許）該列中存儲(chǔ)的數(shù)據(jù),。

• 行

  行（row） 表中的一個(gè)記錄

• 主鍵

  主鍵（primary key） 一列（或一組列），其值能夠唯一區(qū)分表中每個(gè)行

• 外鍵

  外鍵（foreign key） 父數(shù)據(jù)表（Parent Entity）的主鍵（primary key）會(huì)放在另一個(gè)數(shù)據(jù)表,，當(dāng)做屬性以創(chuàng)建彼此的關(guān)系,，而這個(gè)屬性就是外鍵。

1.3. 非關(guān)系型數(shù)據(jù)庫(kù)

NOSQL(Not Only SQL)是對(duì)不同于傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)管理系統(tǒng)的統(tǒng)稱,。

隨著web2.0的興起,，傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)在處理海量數(shù)據(jù)時(shí)，會(huì)顯得力不從心,，從而產(chǎn)生了NOSQL,，主流NOSQL，都才用KEY-VALUE的形式,。

常見的非關(guān)系型數(shù)據(jù)庫(kù)：Redis ,HBase,MongoDb,CouchDB

二、 MySQL基礎(chǔ)

2.1. MySQL的安裝與配置

2.1.1 下載MySQL

在官網(wǎng),，現(xiàn)在最新版的MySQL：https://dev./downloads/mysql/

將下載好的壓縮文件,，解壓至【安裝目錄】, D:/DataBase/mysql。

如果提示缺少dll文件,，去 [https://www.microsoft.com/zh-cn/download/confirmation.aspx? id=48145](https://www.microsoft.com/zh-cn/download/confirmation.aspx? id=48145 ) 下載組件并安裝,。

2.1.2 初始化

在初始化時(shí)，需要使用已管理員身份啟動(dòng)的cmd,在 windows/system32/cmd.exe ,，右鍵以管理員身份運(yùn)行

#使用dos命令,，進(jìn)入到mysql的bin目錄中 
cd d:#進(jìn)入 
cd d:/DataBase/mysql/bin 
#使用mysqld命令進(jìn)行初始化 
##進(jìn)行初始化，同時(shí)創(chuàng)建隨機(jī)的密碼,，并顯示在控制塔中 
mysqld --basedir=d:/DataBase/mysql --datadir=d:/DataBase/mysql/mysql_data --initialize --console 
#隨機(jī)生成的密碼iiowpr<2felX

2.1.3 配置文件

將配置文件存儲(chǔ)在 d:/DataBase/mysql/mysql_data/my.ini

[client]
default-character-set=utf8mb4
port            = 3306

[mysqld]
port            = 3306
basedir         = D:/DataBase/mysql
datadir         = D:/DataBase/mysql/mysql_data

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

default-time_zone='+8:00'
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password

max_allowed_packet = 256M
max_connections=20

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
default-character-set=utf8mb4
auto-rehash

2.1.4 安裝服務(wù)

#在install后面可以添加安裝的服務(wù)名稱,，默認(rèn)使用MySQL作為服務(wù)名稱 
mysqld --install MySQL --defaults-file=D:/DataBase/mysql/mysql_data/my.ini

2.1.5 啟動(dòng)MySQL服務(wù)

• 使用命令方式啟動(dòng)

  net start mysql
  

• 使用服務(wù)方式啟動(dòng)

  在運(yùn)行中輸入services.msc，在【服務(wù)】中右鍵MySQL,，啟動(dòng)

2.1.6 登錄MySQL

#客戶端登錄命令 
mysql -h 主機(jī)地址 -u 用戶名 -p 用戶密碼
mysql -uroot -p;

2.1.7 修改密碼

通過(guò)命令修改root用戶的密碼

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; 
exit;
#退出 
mysql -uroot -p123456;

2.1.8 刪除服務(wù)

• 刪除注冊(cè)表（不推薦）

• 使用命令刪除服務(wù)

  SC DELETE mysql -- 服務(wù)的名稱
  

2.2. 認(rèn)識(shí)MySQL

2.2.1 MySQL介紹

MySQL是一個(gè)關(guān)系型的數(shù)據(jù)庫(kù)管理系統(tǒng),，由MySQL AB公司開發(fā)，目前屬于Oracle公司,。

MySQL體積較小,、速度快、成本較低,、開放源代碼,。同時(shí)支持跨平臺(tái)。MySQL集群搭建及分庫(kù)分表。

MySQL特性：

• MySQL使用C和C++編寫,，保證了源代碼的可移植性,。
• 跨平臺(tái)：支持主流的操作系統(tǒng)（Windows,Linux,Mac os ,HP-UX)
• 對(duì)編程語(yǔ)言的支持，對(duì)多種編程語(yǔ)言提供了API(C,C++,JAVA,PYTHON,PHP,Perl,Ruby)
• 支持多線程,，充分利用CPU資源,。（服務(wù)器單核（A,B）
• 優(yōu)化的SQL查詢算法，能提高查詢效率,。
• 提供了TCP/IP,ODBC和JDBC等多種數(shù)據(jù)庫(kù)連接途徑
• 支持多種存儲(chǔ)引擎
• 提供了用于管理,，檢查，優(yōu)化數(shù)據(jù)庫(kù)操作的管理工具,。

2.2.2 MySQL服務(wù)端常用程序

• mysqld

  mysqld是MySQL的后臺(tái)程序（進(jìn)程),只有該程序運(yùn)行后,，客戶端才可以連接訪問數(shù)據(jù)庫(kù)。

• mysqld_safe

  也是服務(wù)器啟動(dòng)腳本,。在Unix中使用mysqld_safe

• mysql.server

  服務(wù)器啟動(dòng)腳本,。是通過(guò)調(diào)用mysqld_safe來(lái)啟動(dòng)mysql服務(wù)器的

• mysqld_multi

  服務(wù)器啟動(dòng)腳本，可以啟動(dòng)或停止系統(tǒng)中安裝的多個(gè)mysql服務(wù)器

• mysql_install_db

  用于默認(rèn)權(quán)限創(chuàng)建Mysql授權(quán)表,，只能在系統(tǒng)首次安裝mysql時(shí)執(zhí)行，并且只執(zhí)行一次,。

2.2.3 MySQL客戶端常用程序

• mysql

  通過(guò)交互式SQL語(yǔ)句輸入來(lái)執(zhí)行的命令行工具,。（Index)

• mysqldump

  用戶備份數(shù)據(jù)的。（將MYSQL數(shù)據(jù)庫(kù)轉(zhuǎn)存至一個(gè)文件中.sql,.csv)

• mysqlshow

• error

• mysqlcheck

• mysqlhotcopy(MyISAM)

三,、 MySQL架構(gòu)

邏輯架構(gòu)：

• 第一層為客戶端的連接認(rèn)證，C/S都有此架構(gòu)
• 第二層為服務(wù)器層,，包含MySQL的大多數(shù)核心服務(wù)功能
• 第三層包含了存儲(chǔ)引擎,，服務(wù)器通過(guò)API與其通信，API規(guī)避了不同存儲(chǔ)引擎的差異,，不同存儲(chǔ)引擎也不會(huì)互相通信,，另外存儲(chǔ)引擎不會(huì)去解析SQL(InnoDB是例外，它會(huì)解析外鍵定義,，因?yàn)榉?wù)器本身沒有實(shí)現(xiàn)該功能)

四、 存儲(chǔ)引擎

4.1. InnoDB

MySQ5.5及更高版本,，默認(rèn)存儲(chǔ)引擎使用InnoDB,，它提供了事務(wù)安全表（兼容ACID)，支持外鍵引用的

完整性約束,。支持事務(wù)的提交,，回滾和緊急數(shù)據(jù)恢復(fù)。它支持行級(jí)鎖定,?？梢詫?shù)據(jù)存儲(chǔ)在集群索引

中,，從而減少了基于主鍵查詢的I/O次數(shù)

4.2 MyISAM

管理非事務(wù)性表，提高了存儲(chǔ)和檢索的效率,，支持全文搜索,。

4.3 MEMORY*

將數(shù)據(jù)存儲(chǔ)在RAM中，數(shù)據(jù)的存儲(chǔ),、查詢更快

4.4 MERGE

將多個(gè)類似的MYISAM表分組為一個(gè)表,，可以處理非事務(wù)性表。

4.5 EXAMPLE

開發(fā)人員學(xué)習(xí)如何變成存儲(chǔ)過(guò)程,，不能存儲(chǔ)和查詢數(shù)據(jù)

4.6 ARCHIVE

用于存儲(chǔ)海量數(shù)據(jù),，單不支持索引

4.7 CSV

以,來(lái)分割數(shù)據(jù)并存儲(chǔ)

4.8 BLACKHOLE

只接受數(shù)據(jù)，不存儲(chǔ)數(shù)據(jù)

4.9 FEDERATED

將數(shù)據(jù)存儲(chǔ)到遠(yuǎn)程數(shù)據(jù)庫(kù)中

mysql數(shù)據(jù)庫(kù)中分為行和列,。數(shù)據(jù)在計(jì)算機(jī)上存儲(chǔ)是以頁(yè)為單位存儲(chǔ)的,。

五,、 數(shù)據(jù)類型

數(shù)據(jù)類型是定義列中可以存儲(chǔ)什么數(shù)據(jù)以及該數(shù)據(jù)實(shí)際怎樣存儲(chǔ)的基本規(guī)則

5.1 串?dāng)?shù)據(jù)類型

字符串類型指CHAR,、VARCHAR、BINARY,、VARBINARY,、BLOB、TEXT,、ENUM和SET,。

/*	
	TEXT：存放富文本編輯器中的數(shù)據(jù) 
	BLOB:用于存放二進(jìn)制文本數(shù)據(jù) 
*/
#1. 枚舉 
CREATE TABLE test_enum( 
    n1 ENUM('a','b','c') 
);
INSERT INTO test_enum(n1) values('a'); 
INSERT INTO test_enum(n1) values('b'); 
INSERT INTO test_enum(n1) values('c'); 
INSERT INTO test_enum(n1) values('d'); -- 超出枚舉范圍的會(huì)報(bào)錯(cuò) 

#2. 集合類型 SET關(guān)鍵字聲明 
CREATE TABLE test_set( 
    n1 SET('a','b','c') 
);
insert into test_set(n1) values('a'); 
insert into test_set(n1) values('a,b'); 
insert into test_set(n1) values('a,b,c'); 
insert into test_set(n1) values('a,b,c,d'); -- 超出范圍會(huì)報(bào)錯(cuò)

5.2 數(shù)值數(shù)據(jù)類型

5.2.1 整數(shù)類型（精確值）

#默認(rèn)情況下,，整型時(shí)有符號(hào) 
#以前版本超出范圍只會(huì)保留臨界值,，8.0版本超出范圍會(huì)報(bào)錯(cuò) 
#在創(chuàng)建表時(shí)，可以通過(guò)unsigned來(lái)指定該整型為無(wú)符號(hào)的 
CREATE TABLE test_int( 
    n1 INT,-- id的范圍-2 147 483 648,，2 147 483 647 
    n2 INT UNSIGNED -- 用unsigned關(guān)鍵字來(lái)指名該字段時(shí)無(wú)符號(hào)的 
);

#整型數(shù)據(jù)的長(zhǎng)度（大?。┦怯深愋蜎Q定，整型后加括號(hào)，用于指定數(shù)據(jù)的顯示寬度,，一般與ZEROFILL關(guān)鍵字 一起使用,，作用是在左側(cè)自動(dòng)填充0已達(dá)到自定寬度,一旦使用ZEROFILL，有符號(hào)就會(huì)變成無(wú)符號(hào) 

CREATE TABLE test_int2( 
    n1 INT(8) ZEROFILL, 
    n2 INT UNSIGNED 
);

5.2.2 定點(diǎn)類型（精確值）

5.2.3 浮點(diǎn)類型（近似值）

/*浮點(diǎn)型：
    float(M,D) 
    double(M,D) 
定點(diǎn)型
    decimal(M,D) 
    numeric(M,D) 
    M:代表小數(shù)位+整數(shù)位的總長(zhǎng)度 D:小數(shù)位的長(zhǎng)度 
    默認(rèn)情況下： float,double,numeric,decimal會(huì)根據(jù)插入的數(shù)據(jù)來(lái)自動(dòng)調(diào)整精度(float(10,3),10.000) 
    decimal默認(rèn)MD為(10,0) 和整形一樣，超出范圍時(shí),，報(bào)錯(cuò) 
    
    定點(diǎn)型的精度比浮點(diǎn)型的精度高,，設(shè)計(jì)到貨幣金額的字段一般都用定點(diǎn)型（decimal,numeric） 
*/
CREATE TABLE test_float( n1 float(10,3), n2 numeric(10,2) );
insert into test_float(n1,n2) values(11.1,12.2); 
insert into test_float(n1,n2) values(11.11111,12.2); 
insert into test_float(n1,n2) values(11.1,222.2222222); 
insert into test_float(n1,n2) values(11.1,22222222221111111111111111111111111111111.0);

5.2.4 其他數(shù)值類型

5.3 日期時(shí)間數(shù)據(jù)類型

timestamp時(shí)間戳，微信公總號(hào),、支付寶開發(fā)過(guò)程中,，參數(shù)是時(shí)間戳。timestamp能更好的體現(xiàn)時(shí)區(qū),，

如果項(xiàng)目對(duì)時(shí)區(qū)比較敏感,，選擇日期類型時(shí)推薦使用timestamp

CREATE TABLE test_date( 
    n1 DATETIME 
);
INSERT INTO test_date values('2020-01-01');

六,、 SQL語(yǔ)句

6.1 基礎(chǔ)語(yǔ)句

6.1.1 操作數(shù)據(jù)庫(kù)

#查看mysql版本信息 
SELECT VERSION();-- 系統(tǒng)函數(shù),，常量 
#查看mysql中有哪些數(shù)據(jù)庫(kù) 
SHOW DATABASES; 
#創(chuàng)建數(shù)據(jù)庫(kù) 
CREATE DATABASE t2;-- t2數(shù)據(jù)庫(kù)名稱 
#切換數(shù)據(jù)庫(kù)（默認(rèn)登錄后沒有使用任何數(shù)據(jù)庫(kù)，需要操作t2數(shù)據(jù)庫(kù) 
USE t2；-- t2需要切換的數(shù)據(jù)庫(kù) 
#刪除數(shù)據(jù)庫(kù) 
DROP DATABASE t2;-- t2就是需要?jiǎng)h除的數(shù)據(jù)庫(kù)的名稱

6.1.2 操作表

#創(chuàng)建表 
/*語(yǔ)法:
	CREATE TABLE 表名( 
	字段名 類型 【約束】,， 
	字段名 類型 【約束】, ...
	字段名 類型 【約束】 
	);
*/
#查看有哪些表 
SHOW TABLES; 
#查看表結(jié)構(gòu) 
DESC test_int;-- test_int是表名 
#修改表 
ALTER TABLE test_int CHANGE n2 n3 varchar(10); -- 將n2字段重命名為n3
#刪除表 
DROP TABLE test_int;

6.1.3 操作數(shù)據(jù)

#C 插入 
-- INSERT INTO 表名(字段列表) VALUES(值列表) 
INSERT INTO test_int2(n1,n2) VALUES(1,2); 
#R 查詢 
SELECT * FROM test_int2; 
#U 更新 
-- UPDATE 表名 set 字段1=值1,，字段2=值2 【W(wǎng)HERE 篩選】 
UPDATE test_int2 set n2=100; 
#D 刪除
--  DELETE FROM 表名
DELETE FROM 表名 DELETE FROM test_int2;

6.1.4 導(dǎo)入導(dǎo)出

#項(xiàng)目開發(fā)環(huán)境和生產(chǎn)環(huán)境
##數(shù)據(jù)庫(kù)的導(dǎo)出 
mysqldump -uroot -p123456 java1908z > d:\java1908z.sql 
##將生產(chǎn)環(huán)境中的數(shù)據(jù)庫(kù)導(dǎo)入到開發(fā)環(huán)境中來(lái) 
#1.創(chuàng)建數(shù)據(jù)庫(kù) 
CREATE DATABASE java1908z; 
#2.切換數(shù)據(jù)庫(kù) 
USE java1908z; 
#3.導(dǎo)入數(shù)據(jù)庫(kù) 
SOURCE D:\java1908z.sql;

6.2 DDL(Data Defifinition Language)：數(shù)據(jù)定義語(yǔ)言

6.2.1 關(guān)鍵字

• CREATE

  CREATE在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)對(duì)象，凡是數(shù)據(jù)庫(kù),、數(shù)據(jù)表,、數(shù)據(jù)庫(kù)索引、存儲(chǔ)程序,、用戶函數(shù),、觸發(fā)程序或是用戶自定義類型等對(duì)象，都可以使用CREATE指令來(lái)創(chuàng)建,。

  CREATE DATABASE d1;-- 創(chuàng)建數(shù)據(jù)庫(kù)--
  CREATE TABLE　t1;-- 創(chuàng)建數(shù)據(jù)表--
  CREATE INDEX i1;-- 創(chuàng)建數(shù)據(jù)表索引--
  CREATE PROCEDURE p1;-- 創(chuàng)建存儲(chǔ)程序--
  CREATE FUNCTION f1;-- 創(chuàng)建用戶函數(shù)--
  CREATE　TRIGGER　tr1;-- 創(chuàng)建觸發(fā)程序--
  

• ALTER

  ALERT以不同方式修改現(xiàn)有對(duì)象的結(jié)構(gòu),，相較于CREATE需要完整的數(shù)據(jù)對(duì)象參數(shù)，ALERT則是可以按照要修改的幅度來(lái)決定使用的參數(shù),。

  ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL;-- 在表doc_exa中加入列,名稱為cilumn_b,類型為varchar(20),允許為NULL--
  ALTER TABLE doc_exb DROP COLUMN column_b ; -- 在表doc_exb中移出column_b列--
  alter table scm.scm_d_pp_detail_1h  change  column PP_ID_2H PP_ID_1H varchar(50)  -- 列改名
  alter table test modify address char(10) -- 修改表列類型--
  

• DROP

  DROP則是刪除數(shù)據(jù)庫(kù)對(duì)象的指令,，并且只需要指定刪除的數(shù)據(jù)庫(kù)對(duì)象名稱即可，在DDL語(yǔ)法中是最簡(jiǎn)單的,。

  DROP TABLE myTable;--刪除myTable表--
  DROP VIEW myView;--刪除myView視圖--
  

6.2.2 數(shù)據(jù)庫(kù)的管理

#1. 創(chuàng)建數(shù)據(jù)庫(kù) 
#語(yǔ)法1：CREATE DATABASE 庫(kù)名; 
CREATE DATABASE t1; 
#語(yǔ)法2：CREATE DATABASE IF NOT EXISTS 庫(kù)名 -- 當(dāng)庫(kù)名不存在時(shí),，創(chuàng)建他，如果存在,，不執(zhí)行 
CREATE DATABASE IF NOT EXISTS t1; 
#2. 修改數(shù)據(jù)庫(kù) 
##一般不去修改數(shù)據(jù)庫(kù)名稱 
##修改數(shù)據(jù)的編碼格式 
ALTER DATABASE t1 CHARACTER SET utf8;-- 修改t1的字符編碼格式 
#3. 刪除數(shù)據(jù)庫(kù) 
#語(yǔ)法1：DROP DATABASE 庫(kù)名; 
DROP DATABASE t2; 
#語(yǔ)法2： DROP DATABASE IF EXISTS 庫(kù)名 -- 當(dāng)庫(kù)名存在時(shí)刪除,，否則不執(zhí)行 
DROP DATABASE IF EXISTS t2;

6.2.3 數(shù)據(jù)表的管理

/*創(chuàng)建表
	CREATE TABLE 表名( 
		列名 類型【(長(zhǎng)度) 約束】, ... 
		列名 類型【(長(zhǎng)度) 約束】, 
		【表約束】, 
		【表約束】 )	
  修改表： 
	ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名【列的類型(長(zhǎng)度) 約束】 
  刪除表： 
  	DROP TABLE 表名 
 */
#1.創(chuàng)建表 
CREATE TABLE b_company( 
    id INT NOT NULL AUTO_INCREMENT COMMENT '主鍵ID', 
    name VARCHAR(20) NOT NULL COMMENT '公司名稱', 
    record_date DATE COMMENT '注冊(cè)時(shí)間', 
    PRIMARY KEY(id) 
);
#2.修改表名稱 
ALTER TABLE b_company RENAME TO bcompany; 
#3.修改name字段的名稱 
company_name ALTER TABLE bcompany CHANGE COLUMN name company_name VARCHAR(30) COMMENT '公司注 冊(cè)名稱'; 
#4.修改company_name字段的類型和約束 
ALTER TABLE bcompany MODIFY COLUMN company_name VARCHAR(20) NOT NULL;
#5.添加列 
ALTER TABLE bcompany ADD COLUMN fr_name VARCHAR(20) NOT NULL; 
#6.刪除列 
ALTER TABLE bcompany DROP COLUMN fr_name; 
#7.刪除表 
DROP TABLE bcompany; DROP TABLE IF EXISTS bcompany;-- 加入bcompany存在，將其刪除,，否則不執(zhí)行 
#8.截?cái)啾恚ㄇ蹇毡?，與DELETE的差別，truncate表時(shí),，自增主鍵重新開始計(jì)算,，而刪除時(shí)，不會(huì)從1開始 TRUNCATE TABLE bcompany; 
#9.復(fù)制表結(jié)構(gòu) 
CREATE TABLE b_company LIKE bcompany; 
#10.復(fù)制表結(jié)構(gòu)并賦值數(shù)據(jù) 
CREATE TABLE b_comapny2 SELECT * FROM bcompany; 
#11.如果賦值部分表結(jié)構(gòu),，不復(fù)制數(shù)據(jù) 
CREATE TABLE b_company3 SELECT id,company_name FROM bcompany WHERE 0; 
#12.復(fù)制部分表結(jié)構(gòu),，并復(fù)制數(shù)據(jù) 
CREATE TABLE b_company4 SELECT id,company_name FROM bcompany;

6.2.4 約束的管理

數(shù)據(jù)庫(kù)管理系統(tǒng)不僅提供了數(shù)據(jù)的保存，數(shù)據(jù)的完整性,，合法性進(jìn)行限制，這種限制就叫約束,。

常見約束

• PRIMARY KEY 主鍵約束，每張表中只能有一個(gè)主鍵約束,。表中唯一一列可以確定一行的列稱為主

  鍵。主鍵不能為空,，表中可以沒有主鍵,。mysql中如果沒有顯示的定義主鍵,，mysql InnoDB存儲(chǔ)

  引擎會(huì)自動(dòng)生成一個(gè)隱藏的自增主鍵。

• NOT NULL 非空約束,，標(biāo)識(shí)該列的數(shù)據(jù)不允許為空

• DEFAULT 默認(rèn)約束

• UNIQUE 唯一約束,。唯一約束的列可以為空

• CHECK 檢查約束（檢查你輸入的數(shù)據(jù)是否滿足我自定義的約束條件）

• FOREIGN KEY（外鍵約束）：外鍵約束是用于限制2張表之間的關(guān)系的，保證了外鍵字段中的數(shù)據(jù)

  來(lái)源必須源于另一張表,。主表中的非主鍵字段指向另一個(gè)表的主鍵字段,。主表中的該非主鍵字段是一個(gè)外鍵。

約束的添加時(shí)機(jī)

• 創(chuàng)建表時(shí)添加約束
• 修改表時(shí)添加約束

約束分類

• 列級(jí)約束：支持默認(rèn),、非空,、主鍵，唯一約束,、檢查約束,，不支持外鍵約束
• 表級(jí)約束：CONSTRAINT 約束名 約束類型(字段名)，一般不添加非空約束,，默認(rèn)約束

#創(chuàng)建表時(shí)添加約束 使用列級(jí)約束 
#用于存儲(chǔ)王者榮耀（吃雞）用戶信息 
CREATE TABLE b_user( 
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    age INT DEFAULT 18,-- 默認(rèn)值 
    sex CHAR(1) CHECK(sex='男' OR sex='女'), 
    mobile CHAR(11) UNIQUE, ipaddr INT 
);
#在創(chuàng)建表時(shí)添加表級(jí)約束 
CREATE TABLE b_user2(
    id INT NOT NULL AUTO_INCREMENT,-- 自增策略 
    age INT DEFAULT 18, sex CHAR(1), 
    mobile CHAR(11), 
    ipaddr INT, 
    -- 表級(jí)約束 
    PRIMARY KEY(id),-- 增加了一個(gè)名為p的主鍵約束,，但是主鍵約束默認(rèn)的名稱
    PRIMARY CONSTRAINT uq UNIQUE(mobile), 
    CONSTRAINT fk FOREIGN KEY(ipaddr) REFERENCES s_ipaddr(id)-- 添加外鍵約束 
);
INSERT INTO b_user(sex,mobile,ipaddr) values('男','1111',1); 
INSERT INTO b_user(sex,mobile,ipaddr) values('未知','1111',2);
#服務(wù)器列表 
CREATE TABLE s_ipaddr( 
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(20) 
);

#修改表時(shí)添加約束
CREATE TABLE b_user3( 
    id INT, 
    age INT,-- 默認(rèn)值 
    sex CHAR(1),
    mobile CHAR(11),
    ipaddr INT 
);
#添加主鍵約束 
ALTER TABLE b_user3 MODIFY COLUMN id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;-- 修改的列級(jí)約束
ALTER TABLE b_user3 ADD PRIMARY KEY(id);-- 表級(jí)約束 
#添加默認(rèn)約束
ALTER TABLE b_user3 MODIFY COLUMN age INT DEFAULT 18; 
#添加檢查約束【8.0不能修改檢查約束】 
ALTER TABLE b_user3 MODIFY COLUMN sex CHAR(1) CHECK(sex='男' or sex='女'); 
#添加唯一約束 
ALTER TABLE b_user3 MODIFY COLUMN mobile CHAR(11) UNIQUE;-- 添加列級(jí)唯一約束 
ALTER TABLE b_user3 ADD UNIQUE(mobile);-- 添加表級(jí)唯一約束 
#添加外加約束 
ALTER TABLE b_user3 ADD FOREIGN KEY(ipaddr) REFERENCES s_ipaddr(id);

#刪除約束
/*可以使用列級(jí)約束的 
	ALTER TABLE 表名 MODIFY COLUMN 字段名 字段類型 
  可以使用表級(jí)約束的，并且有約束名稱（PRIMARY KEY),， 	
  	ALTER TABLE 表名 DROP 約束類型 約束名 
*/
#刪除外鍵 
ALTER TABLE b_user2 DROP FOREIGN KEY fk; 
#刪除主鍵 
ALTER TABLE b_user3 DROP PRIMARY KEY; 
#刪除唯一約束 
ALTER TABLE b_user3 MODIFY COLUMN mobile VARCHAR(11); 
##DROP INDEX 唯一鍵的名稱 
ALTER TABLE b_user3 DROP INDEX mobile; 

##查詢b_users表中的index 
SHOW INDEX FROM b_user3;

6.3 DML(Data Manipulation Language)：數(shù)據(jù)操縱語(yǔ)言

6.3.1 關(guān)鍵字

• INSERT

  #單行插入 
  INSERT INTO 表名【(字段1,，字段2，字段3)】 VALUES (值1,值2,，值3) 
  ##注意 值列表的個(gè)數(shù)和字段列表的個(gè)數(shù)完全一致,，并且類型一致 
  INSERT INTO b_user(name) VALUES('張三');
  INSERT INTO b_user VALUES(1,'李四','男','2020-01-01'); 
  -- 如果表名后沒有字段列表，VALUES后的值列表中的個(gè)數(shù)和表字段個(gè)數(shù)一致,，并且值列表的順序和字段列表的 順序一致,。一般如果主鍵列自增，不顯式的給自增列賦值 
  -- 在值列表中字符,、日期字段都應(yīng)該使用單引號(hào)括起來(lái) 
  -- 如果表中有NOT NULL字段,，該字段必須賦值 
  -- 如果表中沒有NOT NULL，可以賦值,，也可以不賦值（NULL) 
  #多行插入 
  INSERT INTO 表名【(字段1，字段2,，字段3..)】 VALUES (值1,，值2，值3...),(值1,，值2,，值 3...),(值1，值2,，值3...) 
  INSERT INTO b_user(name,sex,birthday) VALUES('王寶強(qiáng)','男','2020-01-01'), ('王','男','2020-01-01'),('王1','男','2020-01-01'); 
  #批量插入
  ##將一個(gè)查詢結(jié)果全部插入到數(shù)據(jù)庫(kù)中 INSERT INTO 表名【(字段1,，字段2,，字段3...)】(SELECT c1,c2,c3 FROM 表名 【W(wǎng)HERE 篩選條 件】) 
  CREATE TABLE temp_user LIKE b_user; INSERT INTO temp_user(name,sex,birthday) (SELECT name,sex,birthday FROM b_user WHERE user_id>2); 
  
  ##注意:字段列表應(yīng)該和SELECT后的顯示字段列表個(gè)數(shù)，類型一致
  

  了解

  INSERT INTO 表名 SET 字段名=值,，字段名=值 
  INSERT INTO temp_user SET name='kobe',sex='男',birthday='1976-01-01';
  

• UPDATE

  /*語(yǔ)法:
  	UPDATE 表名 SET 字段名=值,字段名=值 注意：最后一個(gè)沒有逗號(hào) WHERE 篩選條件*/
  #將temp_user表中的所有性別都改為男 
  UPDATE temp_user SET sex='女'; 
  UPDATE temp_user SET sex='男' WHERE user_id=1 OR user_id=4;
  

• DELETE

  /*語(yǔ)法:
  	DELETE FROM 表名 WHERE 篩選條件 注意：如果沒有篩選條件,，等于清空整張表*/
  #刪除ID=3的用戶信息 
  DELETE FROM temp_user WHERE user_id=3; 
  ##企業(yè)開發(fā)過(guò)程中謹(jǐn)慎的去使用delete語(yǔ)句 
  #1.先用查詢語(yǔ)句 
  SELECT * FROM temp_user WHERE user_id=2; 
  #2.將SELECT *改為DELETE 
  DELETE FROM temp_user WHERE user_id=2; 
  ##TRUNCATE（截?cái)啾?和DELETE的差別 
  #1. TRUNCATE在截?cái)啾頃r(shí)，自增列重新從1開始自增,，DELETE不影響自增列 
  #2. DELETE支持事務(wù)回滾,，而TRUNCATE不支持事務(wù)回滾
  

6.4 DQL(Data Query Language)：數(shù)據(jù)查詢語(yǔ)言

6.4.1 關(guān)鍵字

• select

6.4.2 基礎(chǔ)查詢

/*#基礎(chǔ)語(yǔ)法 
	SELECT查詢字段列表（最后一個(gè)字段不加逗號(hào)） FROM表名 
  #執(zhí)行順序 
	先執(zhí)行 FROM 再執(zhí)行SELECT 
	注意：
	查詢字段列表中：字段，函數(shù),，表達(dá)式,，常量 查詢結(jié)果是一張?zhí)摂M的二維表，不能對(duì)查詢結(jié)果進(jìn)行更新和刪除,。 
*/
#查詢常量 
SELECT 1; 
#查詢函數(shù) 
SELECT VERSION(); 
#表達(dá)式 
SELECT 100+200; 
SELECT 1>2;-- mysql中沒有boolean類型,，0代表false,1代表true 
#查詢單個(gè)字段 
SELECT name FROM temp_user; 
#多字段查詢 
SELECT * FROM temp_user; SELECT name,sex FROM temp_user;-- 在企業(yè)開發(fā)過(guò)程中推薦使用 
#多個(gè)字段+常量+函數(shù)+表達(dá)式 
SELECT name,sex,2,VERSION(),100+200,user_id*2 FROM temp_user; 
#IFNULL(參數(shù)1，參數(shù)2),，參數(shù)1放字段名,，參數(shù)2放參數(shù)1字段為空時(shí)，你需要替換的值 
SELECT name,IFNULL(birthday,'未知') FROM temp_user; 
#別名設(shè)置 AS 關(guān)鍵字設(shè)置,，別名可以用單引號(hào)括起來(lái),，也可以不加單引號(hào) 
SELECT name,IFNULL(birthday,'未知') AS 'birthday'FROM temp_user; 
#別名設(shè)置，省去AS關(guān)鍵字,，別名可以用單引號(hào)括起來(lái),，也可以不加單引號(hào) 
SELECT name,IFNULL(birthday,'未知') birthday FROM temp_user; 
#去重 
DISTINCT SELECT DISTINCT name FROM temp_user; 
#字符拼接(在oracle和sqlserver中字符拼接直接用+可以拼接) 
##語(yǔ)法:CONCAT(字符1，字符2),，將字符1和字符2拼接
SELECT CONCAT('籃球',name) AS name FROM temp_user;

6.4.3 條件查詢

/*語(yǔ)法結(jié)構(gòu)： 
	SELECT 查詢字段列表 FROM表名 WHERE篩選條件 
	篩選條件： 
        1.條件表達(dá)式：>,>=,<,<=,=,!=,<>,IS NULL,IS NOT NULL 
        2.邏輯表達(dá)式:AND,OR,NOT 
        3.模糊查詢:LIKE,BETWEEN AND,IN 
	條件查詢的執(zhí)行順序:
        1.FROM 
        2.WHERE 
        3.SELECT 
*/
#條件表達(dá)式 
##查詢年齡>18的運(yùn)動(dòng)員 
SELECT * FROM temp_user WHERE age>18; 
##查詢年齡不等于18 
SELECT * FROM temp_user WHERE age!=18; 
SELECT * FROM temp_user WHERE age<>18; 
#注意：對(duì)于NULL值的條件表達(dá)式,，只能使用IS NULL或IS NOT NULL 
#查詢生日不為空的 
SELECT * FROM temp_user WHERE birthday IS NOT NULL; 
SELECT * FROM temp_user WHERE birthday IS NULL; 
##如果篩選條件中包含2個(gè)或以上個(gè)條件表達(dá)式，我們應(yīng)該在條件表達(dá)式中間使用邏輯表達(dá)式 
#查詢年齡>18的并且生日不能為空 
SELECT * FROM temp_user WHERE age>18 AND birthday IS NOT NULL; 
#查詢年齡<=18 或生日為空的 
SELECT * FROM temp_user WHERE age<=18 OR birthday is null; 
SELECT * FROM temp_user WHERE NOT(age>18 AND birthday IS NOT NULL); 
#模糊查詢 
##模糊查詢的語(yǔ)法:SELECT 查詢字段列表 FROM 表名 WHERE 字段名 LIKE '通配符+字符' 
###2種通配符 
####% 代表任意（0或多個(gè)）個(gè)任意字符 
SELECT * FROM temp_user WHERE name LIKE 'kobe%';-- 查詢已kobe開頭的用戶 
SELECT * FROM temp_user WHERE name like '%kobe';-- 以kobe結(jié)尾的用戶 
SELECT * FROM temp_user WHERE name like '%kobe%';-- 包含kobe的用戶 
####_ 代表1個(gè)任意字符,，如果要表示_字符,，使用\_ 
SELECT * FROM temp_user WHERE name like 'kobe_';-- 是以kobe開頭，但是應(yīng)該是5個(gè)字符 
kobe_ SELECT * FROM temp_user WHERE name like '_kobe';-- 以kobe結(jié)尾,，前面只能有一個(gè)任意字符 
SELECT * FROM temp_user WHERE name like '_kobe_';-- 包含kobe,，前后只能有一個(gè)字符 
##BETWEEN AND 閉合區(qū)間查詢 
###語(yǔ)法： BETWEEN 值1 AND 值2 == 字段>=值1 AND 字段<=值2 值1必須小于值2
SELECT * FROM temp_user WHERE user_id BETWEEN 1 AND 2; 
SELECT * FROM temp_user WHERE user_id>=1 AND user_id <=2; 
#IN子句可以使用or來(lái)替換 
##查詢user_id 在1,3,4中的數(shù)據(jù) 
SELECT * FROM temp_user where user_id=1 OR user_id=3 OR user_id=4; 
SELECT * FROM temp_user WHERE user_id in(1,3,4); 
##注意：在in的集合中不允許出現(xiàn)通配符 
SELECT * FROM temp_user WHERE user_id in(1,3,%); -- 錯(cuò)誤寫法

注意

在實(shí)際開發(fā)中篩選條件中的條件表達(dá)式，邏輯表達(dá)式,，模糊查詢經(jīng)常會(huì)混合使用,，在混合使用

時(shí)，使用()來(lái)提升優(yōu)先級(jí)

#查詢年齡>18并且name是已kobe開頭的,或者年齡<18 并且name是包含kobe的,，或者年齡=18 并且 name=kobe 
SELECT * FROM temp_user WHERE (age>18 AND name like 'kobe%') OR (age<18 AND name like '%kobe%') OR (age=18 AND name='kobe');

6.4.4 排序

對(duì)查詢結(jié)果進(jìn)行順序調(diào)整

/*基本語(yǔ)法: 
	SELECT查詢字段列表 FROM表名 【W(wǎng)HERE篩選條件 】ORDER BY 排序字段列表 
	注意：
        1.默認(rèn)排序使用ASC升序排列（不加ASC|DESC),DESC降序（數(shù)字（1,2,3）,，字符（根據(jù)各個(gè)國(guó)家的 排序情			況）） 	   
        2.多個(gè)字段，按出現(xiàn)的先后順序排列 
        3.在排序字段列表中支持：?jiǎn)蝹€(gè)字段,，多個(gè)字段,，表達(dá)式,，函數(shù)，別名 
        4.ORDER BY 放在查詢語(yǔ)句的最后（除了LIMIT) 
	執(zhí)行順序
		1. FROM 2. WHERE 3. SELECT 4. ORDER BY 
*/
CREATE TABLE n1( 
    n1 INT PRIMARY KEY, 
    n2 INT,
    n3 INT, 
    n4 INT, 
    n5 VARCHAR(20) 
);
INSERT INTO n1 values(1,2,3,4,'a'); 
INSERT INTO n1 values(5,2,3,4,'a'); 
INSERT INTO n1 values(2,1,3,4,'b'); 
INSERT INTO n1 values(3,5,3,4,'c'); 
INSERT INTO n1 values(4,3,2,4,'d'); 
# 按n1進(jìn)行升序排列 
SELECT * FROM n1 ORDER BY n1+1 ASC;
# 按n1進(jìn)行降序排列 
SELECT n1 as tn1,n2,n3,n4,n5 FROM n1 ORDER BY n1 DESC; 
# 按n2,n3升序排列 
SELECT * FROM n1 ORDER BY n2,n3;

6.4.5 單行函數(shù)

6.4.6 分組函數(shù)

6.4.7 分組查詢

6.4.8 連接查詢

6.4.9 聯(lián)合查詢

6.4.10 子查詢

6.4.11 分頁(yè)查詢

6.5 DCL(Data Control Language)：數(shù)據(jù)控制語(yǔ)言

6.5.1 關(guān)鍵字

• commit
• rollback