MySQL基礎(chǔ)

MySQL

關(guān)系型數(shù)據(jù)庫(kù)入門(mén)

1. 數(shù)據(jù)持久化

數(shù)據(jù)庫(kù)是數(shù)據(jù)持久化的一種工具， 如果想要做到數(shù)據(jù)持久化必須將數(shù)據(jù)通過(guò)文件保存到硬盤(pán)中。

當(dāng)我們做數(shù)據(jù)持久化操作時(shí)不僅僅是希望能夠把數(shù)據(jù)長(zhǎng)久的保存起來(lái)，更為重要的是我們希望很方便的管理數(shù)據(jù)，在需要數(shù)據(jù)的時(shí)候能夠很方便的把需要的數(shù)據(jù)取出來(lái)。數(shù)據(jù)庫(kù)比起一般的文件，在數(shù)據(jù)管理有明顯的優(yōu)勢(shì)，這也是為什么程序中數(shù)據(jù)的持久化絕大部分都采用的是數(shù)據(jù)庫(kù)

2. 數(shù)據(jù)庫(kù)發(fā)展史

目前數(shù)據(jù)庫(kù)主要分為兩種：關(guān)系型數(shù)據(jù)庫(kù)和NoSQL數(shù)據(jù)庫(kù)(在這兩個(gè)之前還有網(wǎng)狀數(shù)據(jù)庫(kù)、層次數(shù)據(jù)庫(kù)。但是現(xiàn)在都已經(jīng)不用了)。這兒我們主要介紹第一種數(shù)據(jù)庫(kù)，也是就關(guān)系型數(shù)據(jù)庫(kù)。

1970年，IBM的研究員E.F.Codd在Communication of the ACM上發(fā)表了名為A Relational Model of Data for Large Shared Data Banks的論文，提出了關(guān)系模型的概念，奠定了關(guān)系模型的理論基礎(chǔ)。后來(lái)Codd又陸續(xù)發(fā)表多篇文章，論述了范式理論和衡量關(guān)系系統(tǒng)的12條標(biāo)準(zhǔn)，用數(shù)學(xué)理論奠定了關(guān)系數(shù)據(jù)庫(kù)的基礎(chǔ)。

3. 關(guān)系型數(shù)據(jù)庫(kù)的特點(diǎn)

  • 理論基礎(chǔ)：集合論和關(guān)系代數(shù)
  • 具體表象：用二維表組織數(shù)據(jù)

列 — 字段

行 — 記錄

  • 編程語(yǔ)言：結(jié)構(gòu)化查詢(xún)語(yǔ)言(SQL)

4.數(shù)據(jù)庫(kù)產(chǎn)品排名

  • Oracle - 目前世界上使用最為廣泛的數(shù)據(jù)庫(kù)管理系統(tǒng)，作為一個(gè)通用的數(shù)據(jù)庫(kù)系統(tǒng)，它具有完整的數(shù)據(jù)管理功能；作為一個(gè)關(guān)系數(shù)據(jù)庫(kù)，它是一個(gè)完備關(guān)系的產(chǎn)品；作為分布式數(shù)據(jù)庫(kù)，它實(shí)現(xiàn)了分布式處理的功能。在Oracle最新的12c版本中，還引入了多承租方架構(gòu)，使用該架構(gòu)可輕松部署和管理數(shù)據(jù)庫(kù)云。

  • MySQL - MySQL是開(kāi)放源代碼的，任何人都可以在GPL（General Public License）的許可下下載并根據(jù)個(gè)性化的需要對(duì)其進(jìn)行修改。MySQL因?yàn)槠渌俣?、可靠性和適應(yīng)性而備受關(guān)注。

  • SQL Server - 由Microsoft開(kāi)發(fā)和推廣的關(guān)系型數(shù)據(jù)庫(kù)產(chǎn)品，最初適用于中小企業(yè)的數(shù)據(jù)管理，但是近年來(lái)它的應(yīng)用范圍有所擴(kuò)展，部分大企業(yè)甚至是跨國(guó)公司也開(kāi)始基于它來(lái)構(gòu)建自己的數(shù)據(jù)管理系統(tǒng)。

  • PostgreSQL - 在BSD許可證下發(fā)行的開(kāi)放源代碼的關(guān)系數(shù)據(jù)庫(kù)產(chǎn)品。

  • MongoDB - MongoDB是2009年問(wèn)世的一個(gè)面向文檔的數(shù)據(jù)庫(kù)管理系統(tǒng)，由C++語(yǔ)言編寫(xiě)，旨在為Web應(yīng)用提供可擴(kuò)展的高性能數(shù)據(jù)存儲(chǔ)解決方案。雖然在劃分類(lèi)別的時(shí)候后，MongoDB被認(rèn)為是NoSQL的產(chǎn)品，但是它更像一個(gè)介于關(guān)系數(shù)據(jù)庫(kù)和非關(guān)系數(shù)據(jù)庫(kù)之間的產(chǎn)品，在非關(guān)系數(shù)據(jù)庫(kù)中它功能最豐富，最像關(guān)系數(shù)據(jù)庫(kù)。

  • DB2 - IBM公司開(kāi)發(fā)的、主要運(yùn)行于Unix（包括IBM自家的AIX）、Linux、以及Windows服務(wù)器版等系統(tǒng)的關(guān)系數(shù)據(jù)庫(kù)產(chǎn)品。DB2歷史悠久且被認(rèn)為是最早使用SQL的數(shù)據(jù)庫(kù)產(chǎn)品，它擁有較為強(qiáng)大的商業(yè)智能功能。

  • Redis - Redis是一種基于鍵值對(duì)的NoSQL數(shù)據(jù)庫(kù)，它提供了對(duì)多種數(shù)據(jù)類(lèi)型（字符串、哈希、列表、集合、有序集合、位圖等）的支持，能夠滿(mǎn)足很多應(yīng)用場(chǎng)景的需求。Redis將數(shù)據(jù)放在內(nèi)存中，因此讀寫(xiě)性能是非常驚人的。

  • ElasticSearch - ElasticSearch是一個(gè)基于Lucene的搜索服務(wù)器。它提供了一個(gè)分布式多用戶(hù)能力的全文搜索引擎，基于RESTful web接口。Elasticsearch是用Java語(yǔ)言開(kāi)發(fā)的，并作為Apache許可條款下的開(kāi)放源碼發(fā)布，是一種流行的企業(yè)級(jí)搜索引擎。

SQL(結(jié)構(gòu)化查詢(xún)語(yǔ)言)詳解

我們通?？梢詫QL分為三類(lèi)：DDL(數(shù)據(jù)定義語(yǔ)言)、DML(數(shù)據(jù)操作語(yǔ)言)和DCL(數(shù)據(jù)控制語(yǔ)言)

  • DDL - 主要負(fù)責(zé)創(chuàng)建表、刪除表和修改表，涉及的指令有: create、drop、alter

  • DML - 主要負(fù)責(zé)數(shù)據(jù)的增(insert)、刪(delete)、改(update)、查(select)

  • DCL - 通常用于授予權(quán)限(grant)和召回權(quán)限(revoke)

注意: SQL語(yǔ)句中關(guān)鍵字不區(qū)分大小寫(xiě)，并且一條語(yǔ)句結(jié)束必須寫(xiě)分號(hào)

1. DDL(數(shù)據(jù)定義語(yǔ)言)

1.1 創(chuàng)建數(shù)據(jù)庫(kù)
1. create database 數(shù)據(jù)庫(kù)名;     -- 創(chuàng)建指定數(shù)據(jù)庫(kù)；如果該數(shù)據(jù)庫(kù)已經(jīng)存在會(huì)報(bào)錯(cuò)
2. create database if not exists 數(shù)據(jù)庫(kù)名;      -- 當(dāng)指定數(shù)據(jù)庫(kù)不存在的時(shí)候創(chuàng)建數(shù)據(jù)庫(kù)；如果存在就不創(chuàng)建，也不會(huì)報(bào)錯(cuò)
3. create database if not EXISTS 數(shù)據(jù)庫(kù)名 default charset utf8;  -- 創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候設(shè)置字符集編碼方式為utf8,讓數(shù)據(jù)庫(kù)支持中文數(shù)據(jù)的存儲(chǔ)

-- 注意: 可以在通過(guò) character-set-server=utf8 來(lái)設(shè)置MySQL服務(wù)啟動(dòng)時(shí)默認(rèn)使用的字符集

-- 創(chuàng)建school數(shù)據(jù)庫(kù)示例：
CREATE DATABASE IF NOT EXISTS school DEFAULT charset utf8;
1.2 刪除數(shù)據(jù)庫(kù)
1. drop database 數(shù)據(jù)庫(kù)名;     -- 刪除指定數(shù)據(jù)庫(kù)；如果該數(shù)據(jù)庫(kù)不存在會(huì)報(bào)錯(cuò)
2. DROP DATABASE if EXISTS 數(shù)據(jù)庫(kù)名;    -- 當(dāng)指定數(shù)據(jù)庫(kù)存在的時(shí)候刪除數(shù)據(jù)庫(kù)；如果數(shù)據(jù)庫(kù)不存在不會(huì)報(bào)錯(cuò)

-- 刪除school數(shù)據(jù)庫(kù)示例:
DROP DATABASE IF EXISTS school;
1.3 使用/切換數(shù)據(jù)庫(kù)
1. use 數(shù)據(jù)庫(kù)名;         -- 切換到指定數(shù)據(jù)庫(kù)


-- 使用school數(shù)據(jù)庫(kù)示例:
USE school;
1.4 創(chuàng)建表
1. create table if not exists 表名(字段名1 類(lèi)型1 約束1 comment 描述1, 字段2 類(lèi)型2 約束2 comment 描述2,...);
-- 1)表名   - 程序員自己命名，但是一般以t或者tb作為前綴表示表名； 而且要見(jiàn)名知義
-- 2)字段名  - 程序員自己命名，要求見(jiàn)名知義
-- 3)類(lèi)型   - 類(lèi)型必須是當(dāng)前數(shù)據(jù)庫(kù)支持的類(lèi)型名，mysql中常見(jiàn)的類(lèi)型有: int(整數(shù)), float(小數(shù)),char/varchar/text(字符串)，bit(布爾),date(日期)
-- 4)約束   - 創(chuàng)建約束：not null - 不為空, default  - 設(shè)置默認(rèn)值, unique - 值唯一, primary key - 主鍵約束 
-- 4.1)主鍵約束  - 主鍵的值可以確定列表中唯一一條記錄(通過(guò)一個(gè)主鍵值可以找到表中的唯一一條記錄)，所以一般每張表都需要設(shè)置一個(gè)字段為主鍵，主鍵的值也必須是唯一的；一般需要可以通過(guò)auto_increment約束讓整型主鍵自動(dòng)增加
-- 5)comment    -  添加字段說(shuō)明

-- 創(chuàng)建學(xué)生表示例1:
CREATE TABLE IF NOT EXISTS t_student
(
stuid  int not null PRIMARY KEY auto_increment COMMENT '學(xué)號(hào)',
stu_name  varchar(20) not null COMMENT '姓名',
stuage  int COMMENT '年齡', 
stugender   bit default 1 COMMENT '性別',
stubirth   date COMMENT '生日'
);

-- 創(chuàng)建教師表示例2:
CREATE TABLE IF NOT EXISTS t_teacher
(
teaid int not null auto_increment COMMENT '編號(hào)',
teaname  varchar(20) not null COMMENT '姓名',
teaage int comment '年齡',
teatitle varchar(20) DEFAULT '助教' COMMENT '職稱(chēng)',
PRIMARY KEY (teaid)    -- 主鍵設(shè)置可以在后面單獨(dú)設(shè)置
)
1.5 刪除表
1. DROP TABLE if EXISTS 表名;   -- 刪除指定表

-- 刪除教師表示例:
DROP TABLE if EXISTS t_teacher;
1.6 修改表
1.6.1 添加字段/列
1. alter TABLE 表名 add COLUMN 字段名 字段類(lèi)型 約束 comment 描述;   -- 在指定表中添加指定字段

-- 給學(xué)生表添加地址字段示例:
alter TABLE t_student add COLUMN stuaddr varchar(200) DEFAULT '' COMMENT '家庭住址';
1.6.2 刪除字段/列
1. alter TABLE 表名 drop COLUMN 字段名;    -- 刪除指定表中的指定字段

-- 示例:
ALTER TABLE t_student DROP COLUMN stuage;

2. DML(數(shù)據(jù)操作語(yǔ)言)

2.1 增(添加記錄)
1. insert into 表名 values(值1,值2,值3,...)  -- 按照表中字段的順序依次給每個(gè)字段賦值

-- 示例:
insert into t_student values(2,'李四',0,'2017-12-30','成都');


2. insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...);   -- 按指定順序給指定字段賦值

-- 示例:
insert into t_student(stuname, stugender, stubirth,stuaddr) values('夏明',1,'1992-3-12','大連');

-- 一次插入多條記錄  
insert into t_student(stuname, stubirth, stuaddr) values
('小花','1989-10-2','南京'),
('Tom',date(now()),'西安'),
('大黃','2000-1-20','沈陽(yáng)');


-- 值的問(wèn)題: sql中是數(shù)字對(duì)應(yīng)的值直接寫(xiě)，字符串需要使用引號(hào)引起來(lái)，bit類(lèi)型的值只有0或者1, 時(shí)間可以用內(nèi)容是滿(mǎn)足時(shí)間格式字符串也可以是通過(guò)時(shí)間函數(shù)獲取的值
-- 時(shí)間函數(shù): now() - 當(dāng)前時(shí)間  date(now()) - 當(dāng)前日期   year(now()) - 當(dāng)前年   month(now()) - 當(dāng)前月 ....
2.2 刪(刪除記錄)
1. delete from 表名;  -- 刪除指定表中所有記錄

-- 示例:
delete from t_student;



2. delete from 表名 where 條件語(yǔ)句;    -- 刪除滿(mǎn)足條件的記錄

-- 示例:
delete from t_student where stuid=9;     -- 刪除stuid等于9的記錄
delete from t_student where stuage=18 and stugender=0;    -- 刪除stuage等于18并且stugender等于0的記錄
delete from t_student where stuage in (16,17);    -- 刪除stuage是16和stuage是17的記錄
delete from t_student where stuname like 'stu%';   -- 刪除stuname的值是以stu開(kāi)頭的記錄
delete from t_student where stuname like '%1';   -- 刪除stuname的值是以1結(jié)尾的記錄
delete from t_student where not age=18;      -- 刪除stuage不是18的記錄
delete from t_student where birth is NULL;   -- 刪除stubirth是NULL的記錄

-- 條件語(yǔ)句的寫(xiě)法
-- 1)比較運(yùn)算符: =(等于), <>(不等于),>(大于), <(小于),>=(大于等于), <=(小于等于)
-- 2)邏輯運(yùn)算符: and(并且), or(或者), not(非)
-- 3)是否為空: is null(為空), is not null(不為空)
-- 4)范圍: between x and y(在x到y(tǒng)之間), not between x and y(不在x到y(tǒng)之間)
-- 5)字符串匹配: like 字符串（像, like后面的字符串可以使用%表示任意個(gè)任意字符, _表示任意一個(gè)字符）
-- 6)指定集合元素: in (值1,值2,...)(結(jié)果是集合中的元素)
2.3 改(修改數(shù)據(jù)/記錄)
1. update 表名 set 字段1=新值1, 字段2=新值2,...;      -- 將指定表中所有行的指定列/字段的值賦值為新值

-- 示例:
update t_student set stubirth='1992-3-12';     -- 將所有記錄中的stubirth字段都設(shè)置為1992-3-12




2. update 表名 set 字段1=新值1, 字段2=新值2,... where 條件語(yǔ)句;  -- 將表中滿(mǎn)足條件的行中指定字段的值賦值為新值 

-- 示例:
update t_student set stuage=20 where not stuname like 'stu%';  -- 將所有stuname不是stu開(kāi)頭的記錄的stuage字段設(shè)置為20
2.4 查(獲取數(shù)據(jù))
2.4.1 直接查詢(xún)
1. select * from 表名;    -- 獲取指定表中所有行和所有的列(所有數(shù)據(jù))

-- 示例:
SELECT * FROM t_student;


-- 映射
2. select 字段名1,字段名2,... from 表名;   -- 獲取指定表中所有行指定的列

-- 示例:
SELECT stuname,stuid FROM t_student; 



3. select * from 表名 where 條件;    -- 獲取指定表中所有滿(mǎn)足條件的行所有列的數(shù)據(jù)

-- 示例:
SELECT * FROM t_student WHERE stuid>115; 
SELECT stuname,stuage FROM t_student WHERE stuid>115;
2.4.2 列重命名
1. select 字段1 as 新字段1, 字段2 as 新字段2,... from 表名; 
-- 注意： 這兒的as可以省略

-- 示例：
select stuname as '姓名', stuage as '年齡' from t_student;
2.4.3 對(duì)查詢(xún)結(jié)果重新賦值(一般針對(duì)布爾數(shù)據(jù))
1. select if(字段名,值1,值2) from 表名;    -- 查詢(xún)指定字段，并且判斷字段對(duì)應(yīng)的值是0還是1，如果是1結(jié)果為值1，否則為值2
-- 注意: 這兒的if的用法是MySQL專(zhuān)有的
-- MySQL寫(xiě)法: if(字段, 新值1, 新值2)
-- 通用寫(xiě)法: case 字段 when 值 then 新值1 else 新值2 end

-- 示例:
select stuname, if(stugender, '男', '女') from t_student;   -- 查詢(xún)的時(shí)候如果stugender的結(jié)果不再是0或者1而是男或者女
select stuname, case stugender when 1 then '男' else '女' end from t_student; -- 查詢(xún)的時(shí)候如果stugender的結(jié)果不再是0或者1而是男或者女
2.4.4 對(duì)列進(jìn)行合并
1. select concat(字段1,字段2,...) from 表名;  

-- 示例:
select concat(stuname, stuage) from t_student;    -- 將多個(gè)字段的數(shù)據(jù)合并成一個(gè)數(shù)據(jù)返回
select concat(stuname, stuage) as 'nameage' from t_student;    -- 將多個(gè)字段的數(shù)據(jù)合并成一個(gè)數(shù)據(jù)返回并且為其數(shù)據(jù)重命名
select concat(stuname, ':', stuid) as 'name_id' from t_student;  

-- 注意: 數(shù)字和字符串?dāng)?shù)據(jù)可以合并，bit類(lèi)型的數(shù)據(jù)不可以合并
2.4.5 模糊查詢(xún) - 查詢(xún)的時(shí)候時(shí)候通過(guò)like條件來(lái)指定查詢(xún)對(duì)象
-- 示例:
select * from t_student where stu_name like 'stu%' and gender=0;
2.4.6 排序(先按之前的任何語(yǔ)法進(jìn)行查詢(xún)?cè)谂判?
1. select * from 表名 order by 字段;      -- 對(duì)查詢(xún)結(jié)果按照指定字段的值進(jìn)行升序排序 
2. select * from 表名 order by 字段 asc;      -- 對(duì)查詢(xún)結(jié)果按照指定字段的值進(jìn)行升序排序 
3. select * from 表名 order by 字段 desc;      -- 對(duì)查詢(xún)結(jié)果按照指定字段的值進(jìn)行降序排序

-- 示例:
select * from t_student order by stuage;
select * from t_student order by stuage asc;
select * from t_student order by stuage desc;
select * from t_student order by age asc, gender asc;  -- 對(duì)查詢(xún)結(jié)果先按年齡進(jìn)行排序；年齡相同的再按性別進(jìn)行排序
2.4.7 去重
1. select distinct 字段 from 表名;   -- 獲取指定字段的值并且去重

-- 示例:
select distinct stuage from t_student;
2.4.8 限制和分頁(yè)
1. select * from 表名 limit 數(shù)量;  -- 值獲取指定數(shù)量的查詢(xún)結(jié)果

-- 示例:
select * from t_student limit 3;    -- 獲取查詢(xún)結(jié)果的前3條



2. select * from 表名 limit M offset N;   -- 跳過(guò)前N條數(shù)據(jù)獲取M條數(shù)據(jù)

-- 示例:
select * from t_student limit 3 offset 4;   -- 跳過(guò)前4條數(shù)據(jù)獲取3條數(shù)據(jù)



3. select * from 表名 limit M,N;   -- 跳過(guò)前M條數(shù)據(jù)獲取N條數(shù)據(jù)

-- 示例:
select * from t_student limit 3,4;    --  跳過(guò)前3條數(shù)據(jù)獲取4條數(shù)據(jù)

3. 外鍵與E.R圖

3.1 約束管理
3.1.1 添加約束

添加普通約束的方式有兩種，一種是創(chuàng)建表的時(shí)候直接給字段添加相應(yīng)的約束，另一種是通過(guò)修改表的方式添加約束

1. 創(chuàng)建表的時(shí)候添加約束
建表的時(shí)候可以在字段類(lèi)型后面加一個(gè)或者多個(gè)約束

2.通過(guò)添加約束索引的方式添加約束
alter table 表名 add constraint 索引名 約束(字段);
-- 說(shuō)明: 索引名 - 自己隨便命名;  約束 - 當(dāng)前想要添加的約束(但是只支持唯一約束、主鍵約束和外鍵約束)

-- 示例:
alter table t_teacher add constraint uni_tel UNIQUE(teatel);
3.1.2 刪除約束
alter table 表名 drop index 約束索引;

-- 示例:
alter table t_teacher drop index uni_tel;
3.2 外鍵約束
1.什么是外鍵：表中的某個(gè)字段的值是根據(jù)其他表中主鍵的值來(lái)確定的。那么這個(gè)字段就是外鍵 
1.1 不同類(lèi)型的外鍵添加方法：
    多對(duì)一的外鍵的添加： 將外鍵添加到多的一方對(duì)應(yīng)的表中 
    一對(duì)一的外鍵的添加： 將外鍵隨便添加到哪一方，同時(shí)添加值唯一約束
    多對(duì)多的外鍵的添加： 關(guān)系型數(shù)據(jù)庫(kù)中，兩張表沒(méi)法實(shí)現(xiàn)多多的關(guān)系，需要一個(gè)中間表。(中間表有兩個(gè)外鍵分別參照                      多多的兩個(gè)表的主鍵)
1.2 添加外鍵約束語(yǔ)法:
    alter table 表名1 add constraint 外鍵約束索引名 foreign key (字段1) references 表名2 (字段2);
    -- 將表1中的字段1設(shè)置為外鍵，并且讓這個(gè)外鍵的值參照表2中的字段2
    
1.3 刪除外鍵約束
    alter table 表名 drop foreign key 外鍵索引名;
3.3 高級(jí)查詢(xún)
3.3.1 聚合：max()/min()/sum()/avg()/count()
SELECT score FROM tb_score;
SELECT max(score) as max_score FROM tb_score;   -- 獲取tb_score表中字段score的最大值
SELECT min(score) as min_score FROM tb_score;   -- 獲取tb_score表中字段score的最小值
SELECT sum(score) as sum_score FROM tb_score;   -- 獲取tb_score表中字段score的和
SELECT AVG(score) as avg_score FROM tb_score;   -- 獲取tb_score表中字段score的平均值
SELECT COUNT(score) as count_score FROM tb_score WHERE score>80;  -- 統(tǒng)計(jì)tb_score表中字段score大于80的個(gè)數(shù)
3.3.2 分組
SELECT 字段操作  FROM 表名 WHERE 條件 GROUP BY(字段2);
-- 將指定表中滿(mǎn)足條件的記錄按照字段2的進(jìn)行分組(值是一樣的在一個(gè)組里面), 然后再講每個(gè)分組作為整體按照指定字段進(jìn)行指定聚合操作
-- 注意:a.字段操作的位置除了分組字段不用聚合，其他字段都必須聚合   b.分組的時(shí)候where要放到分組前對(duì)需要分組的數(shù)據(jù)進(jìn)行篩選

select stuid, avg(score) from tb_score group by(stuid);

-- having: 分組的時(shí)候，在分組后用having代替where來(lái)對(duì)分組后的數(shù)據(jù)進(jìn)行篩選
select stuid, max(score) from tb_score group by(stuid) having max(score)>90;
select stuid, avg(score) from tb_score group by(stuid) having avg(score)>80;
3.3.3 子查詢(xún)
-- 將一個(gè)查詢(xún)的結(jié)果作為另外一個(gè)查詢(xún)的條件或者查詢(xún)對(duì)象
-- 第一種子查詢(xún): 將查詢(xún)結(jié)果作為另外一個(gè)查詢(xún)的條件

-- 獲取成績(jī)大于90分的學(xué)生姓名
select stuname from tb_student where stuid in 
(select stuid from tb_score where score>90);

-- 第二周子查詢(xún)：將一個(gè)查詢(xún)的結(jié)果作為查詢(xún)對(duì)象提供給另外一個(gè)查詢(xún)。但是第一個(gè)查詢(xún)結(jié)果需要重命名 
select score from (SELECT stuid,score from tb_score where score>80) as t2;
3.3.4 連接查詢(xún) - 同時(shí)查詢(xún)多張表
1. 直接連接
select * from 表名1,表名2,表名3 連接條件 查詢(xún)條件;
-- 注意: 如果既有連接條件又有查詢(xún)條件，查詢(xún)條件必須放在連接條件的后面

-- 查詢(xún)所有學(xué)生的名字和學(xué)院名字
select stuname, collname from tb_student, tb_college where tb_student.colid=tb_college.collid;

2.內(nèi)連接
SELECT * FROM 表1 inner join 表2 on 表2的連接條件 inner join 表3 on 表3的連接條件 ...;
-- 注意: 中間表寫(xiě)在最前面(存在關(guān)聯(lián)其他表外鍵的表)

3.外連接
-- 外連接分為左外連接、右外連接和全連接， 但是在MySQL中支持左外連接和右外連接 
-- 左外連接：將左表中對(duì)應(yīng)字段的所有數(shù)據(jù)取出，然后再對(duì)應(yīng)的右表中字段的值，如果右表對(duì)應(yīng)的值不存在結(jié)果就為null 
-- 右外連接：將右表中對(duì)應(yīng)字段的所有數(shù)據(jù)取出，然后再對(duì)應(yīng)的左表中字段的值，如果左表對(duì)應(yīng)的值不存在結(jié)果就為null 
select * from 表1 left join 表2 on 連接條件;
select * from 表1 right join 表2 on 連接條件;

-- 獲取所有學(xué)生的成績(jī)
select stuname, score from tb_student left join tb_score on tb_student.stuid=tb_score.sid;

4. DCL(數(shù)據(jù)控制語(yǔ)言)

DCL主要提供grant和revoke來(lái)授權(quán)和召回權(quán)限

4.1 用戶(hù)管理
1. 添加用戶(hù)
create user 用戶(hù)名@登錄地址;    -- 創(chuàng)建數(shù)據(jù)用戶(hù)，登錄不需要密碼
-- 登錄地址 - 限制用戶(hù)能夠登錄MySQL的主機(jī)地址，可以賦值為: ip地址(指定地址)、localhost(數(shù)據(jù)庫(kù)本機(jī))、%(任何位置)
create user 用戶(hù)名@登錄地址 identified by 密碼; -- 創(chuàng)建數(shù)據(jù)用戶(hù)，登錄需要輸入指定密碼密碼

-- 示例:
create user zhangsan@localhost;
create user lisi@localhost identified by '123456';

2. 刪除用戶(hù)
drop user 用戶(hù)名;
drop user zhangsan;
4.2 權(quán)限管理
1. 授權(quán)
grant 權(quán)限類(lèi)型 on 數(shù)據(jù)庫(kù).對(duì)象 from 用戶(hù)名;
-- 常見(jiàn)權(quán)限類(lèi)型: delete(刪除權(quán)限), select(查詢(xún)權(quán)限),update(更新權(quán)限),insert(插入權(quán)限), all PRIVILEGES(所有權(quán)限)

-- 示例:
GRANT SELECT on school.tb_student TO 'zhangshan';
GRANT UPDATE on school.tb_student TO 'zhangshan';
GRANT all PRIVILEGES ON school.* TO 'zhangshan';   -- 添加所有權(quán)限


2.召回授權(quán)
revoke 權(quán)限類(lèi)型 on 數(shù)據(jù)庫(kù).對(duì)象 from 用戶(hù)名;

-- 示例:
REVOKE DELETE on school.* FROM 'zhangshan';
REVOKE all PRIVILEGES on school.* FROM 'zhangshan';
REVOKE all PRIVILEGES on school.* FROM 'zhangshan';
REVOKE SELECT on school.tb_student FROM 'zhangshan';
REVOKE UPDATE on school.tb_student FROM 'zhangshan';
4.3 事務(wù)

完成一個(gè)任務(wù)需要執(zhí)行多條sql，但是要求這多個(gè)操作中只要有一個(gè)操作失敗，這個(gè)任務(wù)就失敗，數(shù)據(jù)全部還原；所有的操作都成功，整個(gè)任務(wù)才成功的時(shí)候就使用事務(wù)

-- 開(kāi)啟事務(wù)環(huán)境
begin;
需要執(zhí)行的多個(gè)操作對(duì)應(yīng)的sql語(yǔ)句

-- 提交事務(wù)(只有begin到commit之間的所有的sql都執(zhí)行成功，才會(huì)執(zhí)行commit; 否則執(zhí)行rollback)
COMMIT;
-- 事務(wù)回滾(放棄beigin到commit之間執(zhí)行成功的所有sql語(yǔ)句的結(jié)果)
ROLLBACK;

5. 視圖

視圖是關(guān)系型數(shù)據(jù)庫(kù)中將一組查詢(xún)指令構(gòu)成的結(jié)果集組合成可查詢(xún)的數(shù)據(jù)表的對(duì)象。簡(jiǎn)單的說(shuō)，視圖就是虛擬的表，但與數(shù)據(jù)表不同的是，數(shù)據(jù)表是一種實(shí)體結(jié)構(gòu)，而視圖是一種虛擬結(jié)構(gòu)，你也可以將視圖理解為保存在數(shù)據(jù)庫(kù)中被賦予名字的SQL語(yǔ)句。

使用視圖可以獲得以下好處：

  1. 可以將實(shí)體數(shù)據(jù)表隱藏起來(lái)，讓外部程序無(wú)法得知實(shí)際的數(shù)據(jù)結(jié)構(gòu)，讓訪問(wèn)者可以使用表的組成部分而不是整個(gè)表，降低數(shù)據(jù)庫(kù)被攻擊的風(fēng)險(xiǎn)。
  2. 在大多數(shù)的情況下視圖是只讀的（更新視圖的操作通常都有諸多的限制），外部程序無(wú)法直接透過(guò)視圖修改數(shù)據(jù)。
  3. 重用SQL語(yǔ)句，將高度復(fù)雜的查詢(xún)包裝在視圖表中，直接訪問(wèn)該視圖即可取出需要的數(shù)據(jù)；也可以將視圖視為數(shù)據(jù)表進(jìn)行連接查詢(xún)。
  4. 視圖可以返回與實(shí)體數(shù)據(jù)表不同格式的數(shù)據(jù)，
1. 創(chuàng)建視圖
create view 視圖名 as sql查詢(xún)語(yǔ)句;

-- 示例:
create view vw_student
as SELECT * FROM tb_student;



2. 使用視圖  - 視圖在用的時(shí)候可以直接當(dāng)成表來(lái)使用

-- 示例:
select * FROM vw_student;

select stuname, collname from vw_student, tb_college where vw_student.colid=tb_college.collid;

6. 索引

索引相當(dāng)于書(shū)本的目錄，為表創(chuàng)建索引可以加速查詢(xún)（用空間換時(shí)間）。

索引雖然很好，但是不能濫用：

  • 索引會(huì)占用額外的空間

  • 索引會(huì)讓增刪改變得更慢

如果哪個(gè)列經(jīng)常被用于查詢(xún)的篩選條件那么就應(yīng)該在這個(gè)列上建立索引。

主鍵上有默認(rèn)索引(唯一索引)

1. 創(chuàng)建索引
-- 如果使用模糊查詢(xún)，查詢(xún)條件不以%開(kāi)頭，那么索引有效
-- 如果使用模糊查詢(xún)，查詢(xún)條件以%開(kāi)頭，那么索引無(wú)效(盡量避免)
create index 索引名 on 表名 (字段);    -- 給指定表的指定字段添加索引
create unique index 索引名 on 表名 (字段);  -- 給指定表的指定字段添加唯一索引

-- 示例:
create index idx_stuname on tb_student(stuname);
create unique index idx_stuname on tb_student(stuname);


2. 刪除索引
alter table 表名 drop index 索引名;      -- 刪除指定索引，唯一索引也是這樣刪

-- 示例: 
alter table tb_student drop index idx_stuname;


注意: 可以通過(guò)explain來(lái)查看sql的執(zhí)行計(jì)劃
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • ORA-00001: 違反唯一約束條件 (.) 錯(cuò)誤說(shuō)明：當(dāng)在唯一索引所對(duì)應(yīng)的列上鍵入重復(fù)值時(shí)，會(huì)觸發(fā)此異常。 O...
    我想起個(gè)好名字閱讀 5,985評(píng)論 0贊 9
  • 數(shù)據(jù)庫(kù)概念 數(shù)據(jù)庫(kù)是存放數(shù)據(jù)的倉(cāng)庫(kù)。它的存儲(chǔ)空間很大，可以存放百萬(wàn)條、千萬(wàn)條、上億條數(shù)據(jù)。但是數(shù)據(jù)庫(kù)并不是隨意地將...
    窮到?jīng)]有姓名閱讀 328評(píng)論 0贊 1
  • 本文主要總結(jié)了工作中一些常用的操作及不合理的操作，在對(duì)慢查詢(xún)進(jìn)行優(yōu)化時(shí)收集的一些有用的資料和信息，本文適合有MyS...
    Chting閱讀 686評(píng)論 0贊 1
  • 1.SELECT語(yǔ)句的語(yǔ)法： SELECT column_1,column_2,... FROM table_1 ...
    Aimerwhy閱讀 325評(píng)論 0贊 0
  • 數(shù)據(jù)庫(kù)簡(jiǎn)介關(guān)系型數(shù)據(jù)庫(kù)MySQL安裝和使用SQL語(yǔ)言 一、數(shù)據(jù)庫(kù)簡(jiǎn)介 （一）數(shù)據(jù)庫(kù)的發(fā)展 文件系統(tǒng)：磁盤(pán)文件存儲(chǔ)數(shù)...
    哈嘍別樣閱讀 481評(píng)論 0贊 1

友情鏈接更多精彩內(nèi)容