MySQL基礎

一、 了解資料庫

1.1. 相關概念

• 資料庫（database，簡稱DB）：保存有組織的資料的容器（通常是一個檔案或一組檔案），

  誤用導致混淆 人們通常用資料庫這個術語來代表他們使用的資料庫軟體，這是不正確的，確切地說,資料庫軟體應當稱為DBMS（資料庫管理系統），資料庫是通過DBMS創建和操縱的容器，資料庫可以是保存在硬設備上的檔案，但也可以不是，

• 資料庫管理系統（Database Management System，簡稱DBMS）：是為管理資料庫而設計的電腦軟體系統，一般具有存盤、截取、安全保障、備份等基礎功能，

1.2. 關系型資料庫

? 關系型資料庫（Relational database）：是創建在關系模型上的資料庫，以行列的形式來存盤資料，方便用戶的理解，在關系型資料庫中一系列的行和列稱為表，一組表組成資料庫，（單庫的表容量是固定：可以進行分庫分表的操作），可以將關系型資料庫理解為二維資料表格模型，而一個關系型資料庫是由二維表及其之間的關系組成的資料的組織，

1.2.1 常用的關系型資料庫管理系統

• MySQL

  最受歡迎的開源的SQL資料庫管理系統

  2003年Mysql5.0：支持SQL特性，事務，視圖、存盤程序、觸發器等功能，

  2010年MySQL5.5，InnoDB存盤引擎變為MySQL的默認存盤引擎，

  優勢：

  • MySQL是開放源代碼的，可以免費使用（甚至可以修改原始碼）
  • MySQL服務器是一個快速的、易于使用的資料庫服務器
  • MySQL可以在不同的作業系統中使用

• MariaDB

  是由MySQL的創始人主導開發的，擔心Oracle將MySQL閉源，目前大型的互聯網公司紛紛拋棄MySQL

  轉入到MariaDB，

• PostgreSQL

  完整的支持了SQL標準，開源，可以在不同的作業系統中運行，

• Oracle資料庫

  最先將關系型資料庫轉到桌面計算機上，客戶/服務器結構的概念，

  Oracle資料庫的優勢：

  • 兼容性（采用SQL標準）
  • 可移植性（window,linux,unix,dos）
  • 可連接性（支持各種網路傳輸協議：TCP/IP,、DECnet,LU6.2)
  • 高生產率（提供了多種開發工具，可以方便用戶快速的開發）
  • 開放性（oracle良好的兼容性、可以移植性、可連接性和高生產率使用oracle具有良好的開放性)

• SQL Server

  微軟旗下，和.net，在國內廣泛用于電力，保險等行業，2017版之前的SQL Server只支持windows操作

  系統，2017年后SQL Server可以運行在windows,linux,docker等平臺，

• SQLite

  廣泛應用與嵌入式開發中，

• Sybase

  PowerDesginer資料庫建模工具，

1.2.2 SQL語言

? SQL（Structured Query Language：結構化查詢語言），

? SQL是資料庫查詢和設計語言，用于存取資料、查詢、更新、管理關系資料庫，與其他程式設計語言的差別是，SQL由很少的關鍵字組成，每個SQL語言通過一個或多個關鍵字構成，

? SQL基于關系代數和元組關系演算，包括一個資料定義語言和資料操縱語言，SQL的范圍包括資料插入、查詢、更新和洗掉，資料庫模式創建和修改，以及資料訪問控制，盡管SQL經常被描述為，而且很大程度上是一種宣告式編程（4GL），但是其也含有程序式編程的元素，

? SQL的優點：

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

• SQL簡單易學，

• 使用方式靈活：SQL2種使用方式，可以直接以命令方式互動使用；也可以嵌入到其他程式設計語

  言中使用(jdbc)

• 非程序化：“做什么",不需要使用sql告訴計算機"怎么做"

注意：

SQL陳述句不區分大小寫，對SQL中的關鍵字進行大寫，而對表名、列名、資料庫名稱使用小寫，可

以提高代碼的閱讀星和可維護性，

SQL包含四個部分：

• DDL(Data Defifinition Language)：資料定義語言

  CREATE,DROP,ALTER
  

• DML(Data Manipulate Language)：資料操縱語言

  INSERT,UPDATE,DELETE
  

• DQL(Data Query Lanaguage)：資料查詢語言

  SELECT
  

• DCL(Data Control Language)：資料控制語言

  commit,rollback
  

1.2.3 相關概念

• 表

  表（table） 某種特定型別資料的結構化清單

  模式（schema） 關于資料庫和表的布局及特性的資訊

• 列和資料型別**

  列（Colum） 表中的一個欄位，所有的表都是由一個或多個列組成的，

  資料型別（datatype） 所允許的資料的型別，每個表列都有相應的資料型別，它限制（或允許）該列中存盤的資料，

• 行

  行（row） 表中的一個記錄

• 主鍵

  主鍵（primary key） 一列（或一組列），其值能夠唯一區分表中每個行

• 外鍵

  外鍵（foreign key） 父資料表（Parent Entity）的主鍵（primary key）會放在另一個資料表，當做屬性以創建彼此的關系，而這個屬性就是外鍵，

1.3. 非關系型資料庫

NOSQL(Not Only SQL)是對不同于傳統的關系型資料庫的資料庫管理系統的統稱，

隨著web2.0的興起，傳統的關系型資料庫在處理海量資料時，會顯得力不從心，從而產生了NOSQL，主流NOSQL，都才用KEY-VALUE的形式，

常見的非關系型資料庫：Redis ,HBase,MongoDb,CouchDB

二、 MySQL基礎

2.1. MySQL的安裝與配置

2.1.1 下載MySQL

在官網，現在最新版的MySQL：https://dev.mysql.com/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 初始化

在初始化時，需要使用已管理員身份啟動的cmd,在 windows/system32/cmd.exe ，右鍵以管理員身份運行

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

2.1.3 組態檔

將組態檔存盤在 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 安裝服務

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

2.1.5 啟動MySQL服務

• 使用命令方式啟動

  net start mysql
  

• 使用服務方式啟動

  在運行中輸入services.msc，在【服務】中右鍵MySQL，啟動

2.1.6 登錄MySQL

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

2.1.7 修改密碼

通過命令修改root用戶的密碼

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

2.1.8 洗掉服務

• 洗掉注冊表（不推薦）

• 使用命令洗掉服務

  SC DELETE mysql -- 服務的名稱
  

2.2. 認識MySQL

2.2.1 MySQL介紹

MySQL是一個關系型的資料庫管理系統，由MySQL AB公司開發，目前屬于Oracle公司，

MySQL體積較小、速度快、成本較低、開放源代碼，同時支持跨平臺，MySQL集群搭建及分庫分表，

MySQL特性：

• MySQL使用C和C++撰寫，保證了源代碼的可移植性，
• 跨平臺：支持主流的作業系統（Windows,Linux,Mac os ,HP-UX)
• 對編程語言的支持，對多種編程語言提供了API(C,C++,JAVA,PYTHON,PHP,Perl,Ruby)
• 支持多執行緒，充分利用CPU資源，（服務器單核（A,B）
• 優化的SQL查詢演算法，能提高查詢效率，
• 提供了TCP/IP,ODBC和JDBC等多種資料庫連接途徑
• 支持多種存盤引擎
• 提供了用于管理，檢查，優化資料庫操作的管理工具，

2.2.2 MySQL服務端常用程式

• mysqld

  mysqld是MySQL的后臺程式（行程),只有該程式運行后，客戶端才可以連接訪問資料庫，

• mysqld_safe

  也是服務器啟動腳本，在Unix中使用mysqld_safe

• mysql.server

  服務器啟動腳本，是通過呼叫mysqld_safe來啟動mysql服務器的

• mysqld_multi

  服務器啟動腳本，可以啟動或停止系統中安裝的多個mysql服務器

• mysql_install_db

  用于默認權限創建Mysql授權表，只能在系統首次安裝mysql時執行，并且只執行一次，

2.2.3 MySQL客戶端常用程式

• mysql

  通過互動式SQL陳述句輸入來執行的命令列工具，（Index)

• mysqldump

  用戶備份資料的，（將MYSQL資料庫轉存至一個檔案中.sql,.csv)

• mysqlshow

• error

• mysqlcheck

• mysqlhotcopy(MyISAM)

三、 MySQL架構

邏輯架構：

• 第一層為客戶端的連接認證，C/S都有此架構
• 第二層為服務器層，包含MySQL的大多數核心服務功能
• 第三層包含了存盤引擎，服務器通過API與其通信，API規避了不同存盤引擎的差異，不同存盤引擎也不會互相通信，另外存盤引擎不會去決議SQL(InnoDB是例外，它會決議外鍵定義，因為服務器本身沒有實作該功能)

四、 存盤引擎

4.1. InnoDB

MySQ5.5及更高版本，默認存盤引擎使用InnoDB，它提供了事務安全表（兼容ACID)，支持外鍵參考的

完整性約束，支持事務的提交，回滾和緊急資料恢復，它支持行級鎖定，可以將資料存盤在集群索引

中，從而減少了基于主鍵查詢的I/O次數

4.2 MyISAM

管理非事務性表，提高了存盤和檢索的效率，支持全文搜索，

4.3 MEMORY*

將資料存盤在RAM中，資料的存盤、查詢更快

4.4 MERGE

將多個類似的MYISAM表分組為一個表，可以處理非事務性表，

4.5 EXAMPLE

開發人員學習如何變成存盤程序，不能存盤和查詢資料

4.6 ARCHIVE

用于存盤海量資料，單不支持索引

4.7 CSV

以,來分割資料并存盤

4.8 BLACKHOLE

只接受資料，不存盤資料

4.9 FEDERATED

將資料存盤到遠程資料庫中

mysql資料庫中分為行和列，資料在計算機上存盤是以頁為單位存盤的，

五、 資料型別

資料型別是定義列中可以存盤什么資料以及該資料實際怎樣存盤的基本規則

5.1 串資料型別

字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET，

/*	
	TEXT：存放富文本編輯器中的資料 
	BLOB:用于存放二進制文本資料 
*/
#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'); -- 超出列舉范圍的會報錯 

#2. 集合型別 SET關鍵字宣告 
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'); -- 超出范圍會報錯

5.2 數值資料型別

5.2.1 整數型別（精確值）

#默認情況下，整型時有符號 
#以前版本超出范圍只會保留臨界值，8.0版本超出范圍會報錯 
#在創建表時，可以通過unsigned來指定該整型為無符號的 
CREATE TABLE test_int( 
    n1 INT,-- id的范圍-2 147 483 648，2 147 483 647 
    n2 INT UNSIGNED -- 用unsigned關鍵字來指名該欄位時無符號的 
);

#整型資料的長度（大小）是由型別決定，整型后加括號，用于指定資料的顯示寬度，一般與ZEROFILL關鍵字 一起使用，作用是在左側自動填充0已達到自定寬度,一旦使用ZEROFILL，有符號就會變成無符號 

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

5.2.2 定點型別（精確值）

5.2.3 浮點型別（近似值）

/*浮點型：
    float(M,D) 
    double(M,D) 
定點型
    decimal(M,D) 
    numeric(M,D) 
    M:代表小數位+整數位的總長度 D:小數位的長度 
    默認情況下： float,double,numeric,decimal會根據插入的資料來自動調整精度(float(10,3),10.000) 
    decimal默認MD為(10,0) 和整形一樣，超出范圍時，報錯 
    
    定點型的精度比浮點型的精度高，設計到貨幣金額的欄位一般都用定點型（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 其他數值型別

5.3 日期時間資料型別

timestamp時間戳，微信公總號、支付寶開發程序中，引數是時間戳，timestamp能更好的體現時區，

如果專案對時區比較敏感，選擇日期型別時推薦使用timestamp

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

六、 SQL陳述句

6.1 基礎陳述句

6.1.1 操作資料庫

#查看mysql版本資訊 
SELECT VERSION();-- 系統函式，常量 
#查看mysql中有哪些資料庫 
SHOW DATABASES; 
#創建資料庫 
CREATE DATABASE t2;-- t2資料庫名稱 
#切換資料庫（默認登錄后沒有使用任何資料庫，需要操作t2資料庫 
USE t2；-- t2需要切換的資料庫 
#洗掉資料庫 
DROP DATABASE t2;-- t2就是需要洗掉的資料庫的名稱

6.1.2 操作表

#創建表 
/*語法:
	CREATE TABLE 表名( 
	欄位名 型別 【約束】， 
	欄位名 型別 【約束】, ...
	欄位名 型別 【約束】 
	);
*/
#查看有哪些表 
SHOW TABLES; 
#查看表結構 
DESC test_int;-- test_int是表名 
#修改表 
ALTER TABLE test_int CHANGE n2 n3 varchar(10); -- 將n2欄位重命名為n3
#洗掉表 
DROP TABLE test_int;

6.1.3 操作資料

#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 【WHERE 篩選】 
UPDATE test_int2 set n2=100; 
#D 洗掉
--  DELETE FROM 表名
DELETE FROM 表名 DELETE FROM test_int2;

6.1.4 匯入匯出

#專案開發環境和生產環境
##資料庫的匯出 
mysqldump -uroot -p123456 java1908z > d:\java1908z.sql 
##將生產環境中的資料庫匯入到開發環境中來 
#1.創建資料庫 
CREATE DATABASE java1908z; 
#2.切換資料庫 
USE java1908z; 
#3.匯入資料庫 
SOURCE D:\java1908z.sql;

6.2 DDL(Data Defifinition Language)：資料定義語言

6.2.1 關鍵字

• CREATE

  CREATE在資料庫中創建一個物件，凡是資料庫、資料表、資料庫索引、存盤程式、用戶函式、觸發程式或是用戶自定義型別等物件，都可以使用CREATE指令來創建，

  CREATE DATABASE d1;-- 創建資料庫--
  CREATE TABLE　t1;-- 創建資料表--
  CREATE INDEX i1;-- 創建資料表索引--
  CREATE PROCEDURE p1;-- 創建存盤程式--
  CREATE FUNCTION f1;-- 創建用戶函式--
  CREATE　TRIGGER　tr1;-- 創建觸發程式--
  

• ALTER

  ALERT以不同方式修改現有物件的結構，相較于CREATE需要完整的資料物件引數，ALERT則是可以按照要修改的幅度來決定使用的引數，

  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則是洗掉資料庫物件的指令，并且只需要指定洗掉的資料庫物件名稱即可，在DDL語法中是最簡單的，

  DROP TABLE myTable;--洗掉myTable表--
  DROP VIEW myView;--洗掉myView視圖--
  

6.2.2 資料庫的管理

#1. 創建資料庫 
#語法1：CREATE DATABASE 庫名; 
CREATE DATABASE t1; 
#語法2：CREATE DATABASE IF NOT EXISTS 庫名 -- 當庫名不存在時，創建他，如果存在，不執行 
CREATE DATABASE IF NOT EXISTS t1; 
#2. 修改資料庫 
##一般不去修改資料庫名稱 
##修改資料的編碼格式 
ALTER DATABASE t1 CHARACTER SET utf8;-- 修改t1的字符編碼格式 
#3. 洗掉資料庫 
#語法1：DROP DATABASE 庫名; 
DROP DATABASE t2; 
#語法2： DROP DATABASE IF EXISTS 庫名 -- 當庫名存在時洗掉，否則不執行 
DROP DATABASE IF EXISTS t2;

6.2.3 資料表的管理

/*創建表
	CREATE TABLE 表名( 
		列名 型別【(長度) 約束】, ... 
		列名 型別【(長度) 約束】, 
		【表約束】, 
		【表約束】 )	
  修改表： 
	ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名【列的型別(長度) 約束】 
  洗掉表： 
  	DROP TABLE 表名 
 */
#1.創建表 
CREATE TABLE b_company( 
    id INT NOT NULL AUTO_INCREMENT COMMENT '主鍵ID', 
    name VARCHAR(20) NOT NULL COMMENT '公司名稱', 
    record_date DATE COMMENT '注冊時間', 
    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 '公司注 冊名稱'; 
#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存在，將其洗掉，否則不執行 
#8.截斷表（清空表，與DELETE的差別，truncate表時，自增主鍵重新開始計算，而洗掉時，不會從1開始 TRUNCATE TABLE bcompany; 
#9.復制表結構 
CREATE TABLE b_company LIKE bcompany; 
#10.復制表結構并賦值資料 
CREATE TABLE b_comapny2 SELECT * FROM bcompany; 
#11.如果賦值部分表結構，不復制資料 
CREATE TABLE b_company3 SELECT id,company_name FROM bcompany WHERE 0; 
#12.復制部分表結構，并復制資料 
CREATE TABLE b_company4 SELECT id,company_name FROM bcompany;

6.2.4 約束的管理

資料庫管理系統不僅提供了資料的保存，資料的完整性，合法性進行限制，這種限制就叫約束，

常見約束

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

  鍵，主鍵不能為空，表中可以沒有主鍵，mysql中如果沒有顯示的定義主鍵，mysql InnoDB存盤

  引擎會自動生成一個隱藏的自增主鍵，

• NOT NULL 非空約束，標識該列的資料不允許為空

• DEFAULT 默認約束

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

• CHECK 檢查約束（檢查你輸入的資料是否滿足我自定義的約束條件）

• FOREIGN KEY（外鍵約束）：外鍵約束是用于限制2張表之間的關系的，保證了外鍵欄位中的資料

  來源必須源于另一張表，主表中的非主鍵欄位指向另一個表的主鍵欄位，主表中的該非主鍵欄位是一個外鍵，

約束的添加時機

• 創建表時添加約束
• 修改表時添加約束

約束分類

• 列級約束：支持默認、非空、主鍵，唯一約束、檢查約束，不支持外鍵約束
• 表級約束：CONSTRAINT 約束名 約束型別(欄位名)，一般不添加非空約束，默認約束

#創建表時添加約束 使用列級約束 
#用于存盤王者榮耀（吃雞）用戶資訊 
CREATE TABLE b_user( 
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    age INT DEFAULT 18,-- 默認值 
    sex CHAR(1) CHECK(sex='男' OR sex='女'), 
    mobile CHAR(11) UNIQUE, ipaddr INT 
);
#在創建表時添加表級約束 
CREATE TABLE b_user2(
    id INT NOT NULL AUTO_INCREMENT,-- 自增策略 
    age INT DEFAULT 18, sex CHAR(1), 
    mobile CHAR(11), 
    ipaddr INT, 
    -- 表級約束 
    PRIMARY KEY(id),-- 增加了一個名為p的主鍵約束，但是主鍵約束默認的名稱
    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);
#服務器串列 
CREATE TABLE s_ipaddr( 
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(20) 
);

#修改表時添加約束
CREATE TABLE b_user3( 
    id INT, 
    age INT,-- 默認值 
    sex CHAR(1),
    mobile CHAR(11),
    ipaddr INT 
);
#添加主鍵約束 
ALTER TABLE b_user3 MODIFY COLUMN id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;-- 修改的列級約束
ALTER TABLE b_user3 ADD PRIMARY KEY(id);-- 表級約束 
#添加默認約束
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;-- 添加列級唯一約束 
ALTER TABLE b_user3 ADD UNIQUE(mobile);-- 添加表級唯一約束 
#添加外加約束 
ALTER TABLE b_user3 ADD FOREIGN KEY(ipaddr) REFERENCES s_ipaddr(id);

#洗掉約束
/*可以使用列級約束的 
	ALTER TABLE 表名 MODIFY COLUMN 欄位名 欄位型別 
  可以使用表級約束的，并且有約束名稱（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)：資料操縱語言

6.3.1 關鍵字

• INSERT

  #單行插入 
  INSERT INTO 表名【(欄位1，欄位2，欄位3)】 VALUES (值1,值2，值3) 
  ##注意 值串列的個數和欄位串列的個數完全一致，并且型別一致 
  INSERT INTO b_user(name) VALUES('張三');
  INSERT INTO b_user VALUES(1,'李四','男','2020-01-01'); 
  -- 如果表名后沒有欄位串列，VALUES后的值串列中的個數和表欄位個數一致，并且值串列的順序和欄位串列的 順序一致，一般如果主鍵列自增，不顯式的給自增列賦值 
  -- 在值串列中字符、日期欄位都應該使用單引號括起來 
  -- 如果表中有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('王寶強','男','2020-01-01'), ('王','男','2020-01-01'),('王1','男','2020-01-01'); 
  #批量插入
  ##將一個查詢結果全部插入到資料庫中 INSERT INTO 表名【(欄位1，欄位2，欄位3...)】(SELECT c1,c2,c3 FROM 表名 【WHERE 篩選條 件】) 
  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); 
  
  ##注意:欄位串列應該和SELECT后的顯示欄位串列個數，型別一致
  

  了解

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

• UPDATE

  /*語法:
  	UPDATE 表名 SET 欄位名=值,欄位名=值 注意：最后一個沒有逗號 WHERE 篩選條件*/
  #將temp_user表中的所有性別都改為男 
  UPDATE temp_user SET sex='女'; 
  UPDATE temp_user SET sex='男' WHERE user_id=1 OR user_id=4;
  

• DELETE

  /*語法:
  	DELETE FROM 表名 WHERE 篩選條件 注意：如果沒有篩選條件，等于清空整張表*/
  #洗掉ID=3的用戶資訊 
  DELETE FROM temp_user WHERE user_id=3; 
  ##企業開發程序中謹慎的去使用delete陳述句 
  #1.先用查詢陳述句 
  SELECT * FROM temp_user WHERE user_id=2; 
  #2.將SELECT *改為DELETE 
  DELETE FROM temp_user WHERE user_id=2; 
  ##TRUNCATE（截斷表)和DELETE的差別 
  #1. TRUNCATE在截斷表時，自增列重新從1開始自增，DELETE不影響自增列 
  #2. DELETE支持事務回滾，而TRUNCATE不支持事務回滾
  

6.4 DQL(Data Query Language)：資料查詢語言

6.4.1 關鍵字

• select

6.4.2 基礎查詢

/*#基礎語法 
	SELECT查詢欄位串列（最后一個欄位不加逗號） FROM表名 
  #執行順序 
	先執行 FROM 再執行SELECT 
	注意：
	查詢欄位串列中：欄位，函式，運算式，常量 查詢結果是一張虛擬的二維表，不能對查詢結果進行更新和洗掉， 
*/
#查詢常量 
SELECT 1; 
#查詢函式 
SELECT VERSION(); 
#運算式 
SELECT 100+200; 
SELECT 1>2;-- mysql中沒有boolean型別，0代表false,1代表true 
#查詢單個欄位 
SELECT name FROM temp_user; 
#多欄位查詢 
SELECT * FROM temp_user; SELECT name,sex FROM temp_user;-- 在企業開發程序中推薦使用 
#多個欄位+常量+函式+運算式 
SELECT name,sex,2,VERSION(),100+200,user_id*2 FROM temp_user; 
#IFNULL(引數1，引數2)，引數1放欄位名，引數2放引數1欄位為空時，你需要替換的值 
SELECT name,IFNULL(birthday,'未知') FROM temp_user; 
#別名設定 AS 關鍵字設定，別名可以用單引號括起來，也可以不加單引號 
SELECT name,IFNULL(birthday,'未知') AS 'birthday'FROM temp_user; 
#別名設定，省去AS關鍵字，別名可以用單引號括起來，也可以不加單引號 
SELECT name,IFNULL(birthday,'未知') birthday FROM temp_user; 
#去重 
DISTINCT SELECT DISTINCT name FROM temp_user; 
#字符拼接(在oracle和sqlserver中字符拼接直接用+可以拼接) 
##語法:CONCAT(字符1，字符2)，將字符1和字符2拼接
SELECT CONCAT('籃球',name) AS name FROM temp_user;

6.4.3 條件查詢

/*語法結構： 
	SELECT 查詢欄位串列 FROM表名 WHERE篩選條件 
	篩選條件： 
        1.條件運算式：>,>=,<,<=,=,!=,<>,IS NULL,IS NOT NULL 
        2.邏輯運算式:AND,OR,NOT 
        3.模糊查詢:LIKE,BETWEEN AND,IN 
	條件查詢的執行順序:
        1.FROM 
        2.WHERE 
        3.SELECT 
*/
#條件運算式 
##查詢年齡>18的運動員 
SELECT * FROM temp_user WHERE age>18; 
##查詢年齡不等于18 
SELECT * FROM temp_user WHERE age!=18; 
SELECT * FROM temp_user WHERE age<>18; 
#注意：對于NULL值的條件運算式，只能使用IS NULL或IS NOT NULL 
#查詢生日不為空的 
SELECT * FROM temp_user WHERE birthday IS NOT NULL; 
SELECT * FROM temp_user WHERE birthday IS NULL; 
##如果篩選條件中包含2個或以上個條件運算式，我們應該在條件運算式中間使用邏輯運算式 
#查詢年齡>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); 
#模糊查詢 
##模糊查詢的語法:SELECT 查詢欄位串列 FROM 表名 WHERE 欄位名 LIKE '通配符+字符' 
###2種通配符 
####% 代表任意（0或多個）個任意字符 
SELECT * FROM temp_user WHERE name LIKE 'kobe%';-- 查詢已kobe開頭的用戶 
SELECT * FROM temp_user WHERE name like '%kobe';-- 以kobe結尾的用戶 
SELECT * FROM temp_user WHERE name like '%kobe%';-- 包含kobe的用戶 
####_ 代表1個任意字符，如果要表示_字符，使用\_ 
SELECT * FROM temp_user WHERE name like 'kobe_';-- 是以kobe開頭，但是應該是5個字符 
kobe_ SELECT * FROM temp_user WHERE name like '_kobe';-- 以kobe結尾，前面只能有一個任意字符 
SELECT * FROM temp_user WHERE name like '_kobe_';-- 包含kobe，前后只能有一個字符 
##BETWEEN AND 閉合區間查詢 
###語法： 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來替換 
##查詢user_id 在1,3,4中的資料 
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的集合中不允許出現通配符 
SELECT * FROM temp_user WHERE user_id in(1,3,%); -- 錯誤寫法

注意

在實際開發中篩選條件中的條件運算式，邏輯運算式，模糊查詢經常會混合使用，在混合使用

時，使用()來提升優先級

#查詢年齡>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 排序

對查詢結果進行順序調整

/*基本語法: 
	SELECT查詢欄位串列 FROM表名 【WHERE篩選條件 】ORDER BY 排序欄位串列 
	注意：
        1.默認排序使用ASC升序排列（不加ASC|DESC),DESC降序（數字（1,2,3），字符（根據各個國家的 排序情			況）） 	   
        2.多個欄位，按出現的先后順序排列 
        3.在排序欄位串列中支持：單個欄位，多個欄位，運算式，函式，別名 
        4.ORDER BY 放在查詢陳述句的最后（除了LIMIT) 
	執行順序
		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進行升序排列 
SELECT * FROM n1 ORDER BY n1+1 ASC;
# 按n1進行降序排列 
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 單行函式

6.4.6 分組函式

6.4.7 分組查詢

6.4.8 連接查詢

6.4.9 聯合查詢

6.4.10 子查詢

6.4.11 分頁查詢

6.5 DCL(Data Control Language)：資料控制語言

6.5.1 關鍵字

• commit
• rollback

標籤：MySQL

上一篇：sql查詢陳述句把兩個欄位的值在一起顯示

下一篇：Navicat Premium安裝