柚子快報(bào)激活碼778899分享：mysql 數(shù)據(jù)庫（頭歌）

http://yzkb.51969.com/

目錄

一. MySQL數(shù)據(jù)庫 -?初始MySQL

第1關(guān)：創(chuàng)建數(shù)據(jù)庫

第2關(guān)：創(chuàng)建表

第3關(guān)：使用主鍵約束?? ?

第4關(guān)：外鍵約束?

第5關(guān)：添加常用約束

二. MySQL開發(fā)技巧 -?索引? ? ?

普通索引：

唯一索引：

主鍵索引：

組合索引：

刪除索引

查詢表中索引

三. MySQL數(shù)據(jù)庫 -?單表查詢（一）

第1關(guān)：基本查詢語句

第2關(guān)：帶 IN 關(guān)鍵字的查詢

第3關(guān)：帶 BETWEEN AND 的范圍查詢

四. MySQL數(shù)據(jù)庫 -?單表查詢（二）

第1關(guān)：帶 LIKE 的字符匹配查詢

第2關(guān)：查詢空值與去除重復(fù)結(jié)果

第3關(guān)：帶 AND 與 OR 的多條件查詢

五. MySQL數(shù)據(jù)庫 -?單表查詢（三）

第1關(guān)：對查詢結(jié)果進(jìn)行排序

第2關(guān)：分組查詢

第3關(guān)：使用 LIMIT 限制查詢結(jié)果的數(shù)量

六. MySQL數(shù)據(jù)庫 -?使用聚合函數(shù)查詢

第1關(guān)：COUNT( )函數(shù)

第2關(guān)：SUM( )函數(shù)

第3關(guān)：AVG( )函數(shù)

第4關(guān)：MAX( )函數(shù)

第5關(guān)：MIN( )函數(shù)

七.?數(shù)據(jù)查詢（一）

第1關(guān)：按條件查詢單表的所有字段

第2關(guān)：查詢唯一值

第3關(guān)：統(tǒng)計(jì)查詢

第4關(guān)：分組查詢

第5關(guān)：數(shù)據(jù)排序

八.數(shù)據(jù)查詢（二）

第1關(guān)：多表查詢

第2關(guān)：多表查詢及統(tǒng)計(jì)分組

第3關(guān)：子查詢

第4關(guān)：多表子查詢

九. MySQL數(shù)據(jù)庫 -?連接查詢

第1關(guān)：內(nèi)連接查詢

第2關(guān)：外連接查詢

第3關(guān)：復(fù)合條件連接查詢

十. MySQL數(shù)據(jù)庫 - 子查詢

第1關(guān)：帶比較運(yùn)算符的子查詢

第2關(guān)：關(guān)鍵字子查詢

十一. MySQL數(shù)據(jù)庫 -?分組選擇數(shù)據(jù)

第1關(guān)：GROUP BY 與 聚合函數(shù)

第2關(guān)：使用 HAVING 與 ORDER BY

十二. MySQL數(shù)據(jù)庫 -?數(shù)據(jù)庫和表的基本操作（一）

第1關(guān)：查看表結(jié)構(gòu)與修改表名

第2關(guān)：修改字段名與字段數(shù)據(jù)類型

第3關(guān)：添加與刪除字段

第4關(guān)：修改字段的排列位置

第5關(guān)：刪除表的外鍵約束

十三. MySQL數(shù)據(jù)庫 -?數(shù)據(jù)庫和表的基本操作（二）

第1關(guān)：插入數(shù)據(jù)

第2關(guān)：更新數(shù)據(jù)

第3關(guān)：刪除數(shù)據(jù)

十四. MySQL開發(fā)技巧 - 視圖

視圖的定義

創(chuàng)建視圖

操作視圖

刪除視圖

十五. MySQL開發(fā)技巧 - 分頁和索引

第1關(guān)：MySQL 分頁查詢

第2關(guān)：索引（單列索引）

第3關(guān)：索引（組合索引）

十六. MySQL數(shù)據(jù)庫 - 其他函數(shù)的使用

第1關(guān)：字符函數(shù)

第2關(guān)：數(shù)學(xué)函數(shù)

第3關(guān)：日期時間函數(shù)和流程控制類函數(shù)

十七. 數(shù)據(jù)庫E-R圖

一. MySQL數(shù)據(jù)庫 -?初始MySQL

第1關(guān)：創(chuàng)建數(shù)據(jù)庫

連接數(shù)據(jù)庫 ? ? ?

????????mysql -u用戶名 -p密碼

????????mysql -uroot -p123123 -h127.0.0.1

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?（在平臺上連接數(shù)據(jù)庫還需要加上一句-h127.0.0.1）

查看已存在的數(shù)據(jù)庫?

????????show databases;

創(chuàng)建數(shù)據(jù)庫?

????????create database MyDb(數(shù)據(jù)庫名);

刪除數(shù)據(jù)庫

? ? ? ? drop database MyDb(數(shù)據(jù)庫名);

第2關(guān)：創(chuàng)建表

?選擇數(shù)據(jù)庫

????????USE MyDb;

創(chuàng)建表

????????CREATE TABLE 表名 ????????( ????????? ? 字段名 數(shù)據(jù)類型, ????????? ? 字段名 數(shù)據(jù)類型, ????????? ? 字段名 數(shù)據(jù)類型 ????????);

查看表的基本結(jié)構(gòu)

????????DESCRIBE 表名;

????????DESC 表名; ?（簡寫）

刪除表

????????drop table?t_user(表名);

第3關(guān)：使用主鍵約束?? ?

????????????表的主鍵就可以看做是人的身份證

添加主鍵約束

????????1. 在定義列的同時指定主鍵

????????2. 在定義完所有列之后指定主鍵

多字段聯(lián)合主鍵

????????CREATE TABLE t_emp2 ????????( ? ????????? name VARCHAR(32), ????????? ? deptId INT, ????????? ? salary FLOAT, ????????? ? PRIMARY KEY(name,deptId) ????????)

第4關(guān)：外鍵約束?

? 外鍵就是另一張表中的主鍵,外鍵的主要作用就是保持?jǐn)?shù)據(jù)的一致性，完整性

添加外部關(guān)鍵字 ????????

CONSTRAINT 外鍵名 FOREIGN KEY 字段名 REFERENCES 主表名(主鍵名)

?

第5關(guān)：添加常用約束

添加唯一約束

????????唯一約束（Unique Constraint）要求該列唯一，允許為空，但是只能有一個空值。唯一約束可以確保一列或者幾列不出現(xiàn)重復(fù)值。

????????CREATE TABLE t_dept ( ????????? ? id INT PRIMARY KEY, ????????? ? name VARCHAR(22) UNIQUE,? ? ????????? ? location VARCHAR(50) ????????)

????????

添加非空約束??

????????CREATE TABLE t_dept( ? ????????? id INT PRIMARY KEY, ? ????????? name VARCHAR(22) NOT NULL, ? ????????? location VARCHAR(50) ????????)

默認(rèn)約束? ?

????????給字段一個默認(rèn)值

????????CREATE TABLE t_emp ( ????????????????id INT PRIMARY KEY, ????????????????name VARCHAR(22), ????????????????sex VARCHAR(2) DEFAULT '男' ????????) DEFAULT CHARSET=utf8;

注：1.如果是添加字符串型默認(rèn)值要使用單引號，如果是整型則不需要加任何符號；

????????2.如果要添加的是中文默認(rèn)值，則需要加上DEFAULT CHARSET=utf8，使用英文字符則不需要

自動增加屬性值

????????經(jīng)常有個需求就是，在每次插入新紀(jì)錄時，系統(tǒng)自動生成字段的主鍵值，默認(rèn)情況下初始值和增量都為1。

????????CREATE TABLE t_tmp ????????( ? ????????? id int PRIMARY KEY AUTO_INCREMENT, ? ????????? name VARCHAR(32) ????????)

在數(shù)據(jù)庫MyDb中創(chuàng)建表t_user，表結(jié)構(gòu)如下：

字段名稱數(shù)據(jù)類型備注idINT用戶ID，主鍵，自動增長usernamevarchar(32)用戶名，非空，唯一sexvarchar(4)性別，默認(rèn)“男”

????CREATE DATABASE MyDb;

????use MyDb;

????create table t_user

????(id int PRIMARY KEY AUTO_INCREMENT,

? ? ? username VARCHAR(32) NOT NULL UNIQUE,

? ? ? sex varchar(4) DEFAULT '男'

????)DEFAULT CHARSET =utf8;

二. MySQL開發(fā)技巧 -?索引? ? ?

????????數(shù)據(jù)庫索引是一種提高數(shù)據(jù)庫系統(tǒng)性能的方法。索引能讓數(shù)據(jù)庫服務(wù)器更快地查找和獲取表中指定的行，但是索引也給數(shù)據(jù)庫系統(tǒng)帶來了一定的開銷，所以我們應(yīng)該謹(jǐn)慎地使用它們。

普通索引：

????????僅加速查詢， 最基本的索引，沒有任何限制，是我們大多數(shù)情況下使用到的索引

????????1. 創(chuàng)表時創(chuàng)建普通索引

????????????????CREATE table mytable( ????????????????????????id INT NOT NULL, ????????????????????????username VARCHAR(16) NOT NULL, ????????????????????????INDEX [indexName] (username) ????????????????);

????????2. 建表后創(chuàng)建普通索引

? ? ? ? ? ? ? ? ??? ? ? ? ? ? ? ?

?????create INDEX 索引名稱 on 表名(字段名);

? ?? ?#或者

?????ALTER TABLE 表名 ADD INDEX 索引名稱 (字段名);

????????????????創(chuàng)建名為idx_age的普通索引：

????????????????create index idx_age on student(age);

唯一索引：

????????索引列中的值必須是唯一的，但允許為空值

? ? ? ? ? ?

?????CREATE UNIQUE INDEX 索引名稱 ON 表名(字段名);

?????#或者

?????ALTER TABLE 表名 ADD UNIQUE (字段名);

????????????????創(chuàng)建名為uniq_classes的唯一索引：

????????????????CREATE UNIQUE INDEX uniq_classes ON student(classes);

主鍵索引：

????????主鍵索引一般在建表時創(chuàng)建，會設(shè)為 int 而且是 AUTO_INCREMENT自增類型的，例如一般表的id字段。

????????

????CREATE TABLE mytable (

????id int(11) NOT NULL AUTO_INCREMENT,

????PRIMARY KEY (id)

????); ????????

????????創(chuàng)建名為pk_student的主鍵索引：

????????create table student(

? ????????? stu_id int not null ,

? ????????? name varchar(25) not null,

????????? ? age int not null,

????????? ? sex char(2) not null,

????????? ? classes int not null,

????????? ? grade int not null,

? ????????? primary key (stu_id) ?

????????);

組合索引：

???????組合索引就是在多個字段上創(chuàng)建一個索引。（應(yīng)用場景：當(dāng)表的行數(shù)遠(yuǎn)遠(yuǎn)大于索引鍵的數(shù)目時，使用這種方式可以明顯加快表的查詢速度）

? ? ? ? ? ?

???CREATE INDEX 索引名稱 ON 表名(字段1,字段2，字段3);

???#或者

???ALTER TABLE 表名 ADD INDEX 索引名稱(字段1，字段2，字段3);

????????

????????創(chuàng)建名為idx_group的組合索引：

????????CREATE INDEX idx_group ON student(name,sex,grade);

刪除索引

? ? ?

???#使用drop刪除索引

???drop index index_name on table_name ;

???#使用alter刪除索引

???alter table table_name drop index index_name ;

???alter table table_name drop primary key ; #刪除主鍵索引

查詢表中索引

???? ?show index from 表名;

三. MySQL數(shù)據(jù)庫 -?單表查詢（一）

第1關(guān)：基本查詢語句

查詢數(shù)據(jù)表中指定字段的內(nèi)容:

SELECT 字段名 FROM 表名;

SELECT 字段名1, 字段名2 FROM 表名;?

????????提示： MySQL 中的 SQL 語句對關(guān)鍵字的大小寫不敏感， SELECT 和 select 是一樣的。 但是許多開發(fā)者更喜歡將關(guān)鍵字大寫，表名和字段名使用小寫，養(yǎng)成這種習(xí)慣，以后寫出來的腳本就更容易閱讀和維護(hù)了。

查詢數(shù)據(jù)表中的所有內(nèi)容:

SELECT *? FROM 表名;

?????????提示： 雖然使用星號 * 看起來是一件很方便的事情，但是建議在不想獲取整張表的情況下，使用字段名來進(jìn)行查詢。很顯然，它會降低查詢的效率。

查詢字段Name和Salary的內(nèi)容；

????????SELECT Name, Salary

????????FROM tb_emp;

查詢整張表的內(nèi)容。

????????SELECT *

????????FROM tb_emp;

第2關(guān)：帶 IN 關(guān)鍵字的查詢

帶IN關(guān)鍵字的查詢

? IN關(guān)鍵字被用在WHERE語句的后邊，用來過濾所需要查詢的內(nèi)容。更形象的說，IN關(guān)鍵字的使用情形就像點(diǎn)名，點(diǎn)到誰誰就要站出來。

SELECT 字段名 FROM 表名 WHERE 字段名 IN (n1,n2,n3,...);

帶NOT IN關(guān)鍵字的查詢

? ? ?查詢的是除了括號中出現(xiàn)內(nèi)容外的所有內(nèi)容。

SELECT 字段名 FROM 表名 WHERE 字段名 NOT IN (n1,n2,n3,...);?

查詢當(dāng)Id不等于1時，字段Name和Salary的內(nèi)容:

SELECT Name ,Salary

FROM tb_emp

WHERE Id not IN (1);

第3關(guān)：帶 BETWEEN AND 的范圍查詢

帶BETWEEN AND關(guān)鍵字的查詢

????????某一范圍內(nèi)符合條件的數(shù)據(jù),?BETWEEN AND需要兩個參數(shù)支持，一個是范圍的開始值，另一個是結(jié)束值。如果字段值滿足指定的范圍查詢條件，就返回這些滿足條件的數(shù)據(jù)內(nèi)容。

SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN n1 AND n2;?

帶NOT BETWEEN AND關(guān)鍵字的查詢

????????表示查詢指定范圍之外的值。

SELECT 字段名 FROM 表名 WHERE 字段名 NOT BETWEEN n1 AND n2;

查詢當(dāng)字段Salary范圍在3000~5000時，字段Name和Salary的內(nèi)容。

SELECT Name,Salary

FROM tb_emp

WHERE Salary BETWEEN 3000 AND 5000;

四. MySQL數(shù)據(jù)庫 -?單表查詢（二）

第1關(guān)：帶 LIKE 的字符匹配查詢

使用通配符%模糊匹配數(shù)據(jù)內(nèi)容

百分號通配符%可以匹配任意長度的字符，甚至包括零字符。

SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符%';

其中 % 的位置可以根據(jù)需要在字符間變化。

使用通配符_模糊匹配數(shù)據(jù)內(nèi)容

下劃線通配符_只能模糊匹配1個字符。

?SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '字符_';

其中`_`的位置可以根據(jù)需要在字符間變化。

查詢所有Name以字母C為起始的員工的Name和Salary的內(nèi)容

SELECT Name,Salary

FROM tb_emp

WHERE Name LIKE 'C%';

第2關(guān)：查詢空值與去除重復(fù)結(jié)果

查詢空值

????????這個NULL既不代表0，也不代表空字符，而是代表一種未知的狀態(tài)，比如不適用或者放著等將來有合適數(shù)據(jù)了再添加進(jìn)去。

SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL;?

去除重復(fù)結(jié)果

????????消除數(shù)據(jù)表中重復(fù)的數(shù)據(jù),DISTINCT

SELECT DISTINCT 字段名 FROM 表名;?

使用關(guān)鍵字IS NULL返回?cái)?shù)據(jù)表中字段DeptId為NULL的所有字段的內(nèi)容，注意，返回的是指定行所有字段的內(nèi)容；

????????SELECT *

????????FROM tb_emp

????????WHERE DeptId IS NULL;

使用關(guān)鍵字DISTINCT返回?cái)?shù)據(jù)表中字段Name不重復(fù)的內(nèi)容，注意，只需返回字段Name的內(nèi)容。

????????SELECT DISTINCT Name

????????FROM tb_emp;

第3關(guān)：帶 AND 與 OR 的多條件查詢

帶AND關(guān)鍵字的多條件查詢

? ?滿足所有條件的查詢才會被返回。

?SELECT 字段名 FROM 表名 WHERE 表達(dá)式1 AND 表達(dá)式2;?

帶OR關(guān)鍵字的多條件查詢

? ?滿足兩個條件中的其中一個條件即可返回結(jié)果。

SELECT 字段名 FROM 表名 WHERE 表達(dá)式1 OR 表達(dá)式2;

提示：

????????1. OR 可以和 AND 一起使用。但是 AND 的優(yōu)先級要高于 OR 的優(yōu)先級。

? ? ? ? 2. 關(guān)鍵字IN，它能實(shí)現(xiàn)與OR相同的功能，雖然兩種寫法功能一樣，但是更推薦使用 IN 關(guān)鍵字。因?yàn)樗粌H邏輯清晰，執(zhí)行的速度也會快于 OR 關(guān)鍵字。更重要的是，使用 IN 關(guān)鍵字，以后可以執(zhí)行更加復(fù)雜的嵌套語句。

使用關(guān)鍵字AND返回?cái)?shù)據(jù)表中字段DeptId為301并且薪水大于3000的所有字段的內(nèi)容，其中DeptId的倒數(shù)第二個字母為i的大寫；

????????SELECT *

????????FROM tb_emp

????????WHERE DeptId = 301 AND Salary > 3000;

使用關(guān)鍵字IN返回?cái)?shù)據(jù)表中字段DeptId為301和303的所有字段的內(nèi)容。

????????SELECT ?*

????????FROM tb_emp

????????WHERE DeptId IN('301', '303');

五. MySQL數(shù)據(jù)庫 -?單表查詢（三）

第1關(guān)：對查詢結(jié)果進(jìn)行排序

對查詢結(jié)果排序

? ?對讀取的語句進(jìn)行排序，使用Order By子句來設(shè)定

SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC[DESC]];

指定排序方向

ASC?升序關(guān)鍵字DESC?降序關(guān)鍵字

查詢1班同學(xué)的所有信息以成績降序的方式顯示結(jié)果

????????SELECT *

????????FROM tb_score

????????WHERE class_id IN(1)

????????ORDER BY score DESC;

第2關(guān)：分組查詢

分組查詢的單獨(dú)使用

? ?查詢的是每個分組中首次出現(xiàn)的一條記錄。

SELECT 字段名 FROM 表名 GROUP BY 字段名;

對班級名稱進(jìn)行分組查詢

????????SELECT *

????????FROM tb_class

????????GROUP BY class_id ;

第3關(guān)：使用 LIMIT 限制查詢結(jié)果的數(shù)量

LIMIT的使用

? ?限制查詢結(jié)果的數(shù)量。

SELECT 字段名 FROM 表名 LIMIT [OFFSET,] 記錄數(shù);

參數(shù)說明：

第一個參數(shù)，OFFSET，可選參數(shù)，表示偏移量，如果不指定默認(rèn)值為0，表示從查詢結(jié)果的第一條記錄開始，若偏移量為1，則從查詢結(jié)果中的第二條記錄開始，以此類推。 第二個參數(shù)，記錄數(shù)，表示返回查詢結(jié)果的條數(shù)

查詢班級中第2名到第5名的學(xué)生信息，并根據(jù)學(xué)生成績進(jìn)行降序排序。

????????SELECT *

????????FROM tb_score

????????WHERE score ?

????????ORDER BY score DESC LIMIT 1,4;

六. MySQL數(shù)據(jù)庫 -?使用聚合函數(shù)查詢

第1關(guān)：COUNT( )函數(shù)

COUNT()函數(shù)基本使用

? ? ? ? 用來統(tǒng)計(jì)記錄的總條數(shù)

? select count(*/字段名) from 數(shù)據(jù)表;

查詢該表中一共有多少條數(shù)據(jù)

????????select count(*)

????????from tb_class;

查詢此表中367 班有多少位學(xué)生,以班級id和對應(yīng)人數(shù)格式輸出

????????select classid,count(*)

????????from tb_class

????????where classid=367;

第2關(guān)：SUM( )函數(shù)

SUM()函數(shù)基本使用

? 對數(shù)據(jù)表的某列進(jìn)行求和操作

? select sum(字段名) from 數(shù)據(jù)表;

查詢該表中學(xué)生的總成績

????????select sum(score)

????????from tb_class;

查詢學(xué)生語文課程的總分?jǐn)?shù)

????????select course, sum(score)

????????from tb_class

????????where course='語文';

第3關(guān)：AVG( )函數(shù)

AVG()函數(shù)基本使用

????????對數(shù)據(jù)表的某列進(jìn)行求平均值操作

? ?select avg(字段名) from 數(shù)據(jù)表;

查詢表中該班級三位同學(xué)語文的平均分?jǐn)?shù)以及對應(yīng)的課程名

????????select course, avg(score)

????????from tb_class

????????where course='語文';

查詢表中該班級三位同學(xué)英語的平均分?jǐn)?shù)以及對應(yīng)的課程名

????????select course, avg(score)

????????from tb_class

????????where course='英語';

第4關(guān)：MAX( )函數(shù)

MAX()函數(shù)基本使用

? MAX()函數(shù)是求某列的最大數(shù)值

? select max(字段名) from 數(shù)據(jù)表;

查詢語文課程中的最高分?jǐn)?shù)

????????select course ,max(score)

????????from tb_class

????????where course='語文';

查詢英語課程中的最高分?jǐn)?shù)

????????select course ,max(score)

????????from tb_class

????????where course='英語';

第5關(guān)：MIN( )函數(shù)

MIN()函數(shù)基本使用

????????求某列的最小數(shù)值

? ? ?select min(字段名) from 數(shù)據(jù)表

查詢語文課程中的最低分?jǐn)?shù)

????????select course,min(score)

????????from tb_class

????????where course='語文';

查詢英語課程中的最低分?jǐn)?shù)

????????select course,min(score)

????????from tb_class

????????where course='英語';

七.?數(shù)據(jù)查詢（一）

第1關(guān)：按條件查詢單表的所有字段

select * from 數(shù)據(jù)表 where 查詢條件

1.查詢街道信息(jdxx)數(shù)據(jù)表的開福區(qū)(qxmc)的所有字段????????select * from jdxx where qxmc="開福區(qū)" ;

2.查詢街道信息(jdxx)數(shù)據(jù)表的開福區(qū)和岳麓區(qū)(qxmc)的所有字段

????????select * from jdxx where qxmc="開福區(qū)" or qxmc="岳麓區(qū)";

3.查詢街道信息(jdxx)數(shù)據(jù)表的長沙市(cs)的西湖街道（name)所有字段

????????select * from jdxx ?where name="西湖街道" and cs="長沙市";

第2關(guān)：查詢唯一值

distinct ?<字段名>去掉重復(fù)的查詢結(jié)果

根據(jù)jdxx數(shù)據(jù)表

查詢湖南省(sf)所有的區(qū)縣名稱(qxmc)，每個區(qū)縣只出現(xiàn)一次

? select distinct qxmc from jdxx where sf="湖南省";

查詢長沙市(cs)所有的區(qū)縣名稱(qxmc)，每個區(qū)縣只出現(xiàn)一次

? select distinct qxmc from jdxx where cs="長沙市";

第3關(guān)：統(tǒng)計(jì)查詢

統(tǒng)計(jì)查詢函數(shù):

?SUM ? ?計(jì)算數(shù)值列的和 ?AVG ? ?計(jì)算數(shù)值列的平均值 ?MAX ? ?計(jì)算列（數(shù)值、日期、字符）的最大值 ?MIN ? ?計(jì)算列（數(shù)值、日期、字符）的最小值 ?COUNT ? ?計(jì)算查詢結(jié)果的數(shù)目

對jdxx統(tǒng)計(jì)查詢

查詢湖南省的街道個數(shù)

select count?(name) from jdxx where sf="湖南省" ;

查詢長沙市的街道個數(shù)

select count?(name) from jdxx where cs="長沙市" ;

查詢湖南省的區(qū)縣個數(shù)

select count?(distinct qxmc) from jdxx where sf="湖南省" ;

查詢長沙市的區(qū)縣個數(shù)

select count?(distinct qxmc) from jdxx where cs="長沙市" ;

第4關(guān)：分組查詢

select 表達(dá)式列表 from 數(shù)據(jù)表 where 條件 group by 分組字段 having 分組條件

統(tǒng)計(jì)每個省的名稱和街道數(shù)目

select sf ,?count(*) from jdxx

group by sf;

顯示街道數(shù)目大于200個的城市和街道數(shù)目

select cs ,?count(*) from jdxx

group by cs having count(name)>200;

顯示長沙市每個區(qū)縣的街道數(shù)目

select qxmc ,?count(*) from jdxx

where cs='長沙市'

group by qxmc;

第5關(guān)：數(shù)據(jù)排序

排序數(shù)據(jù)

Order by <字段名1> ASC | DESC, <字段名2> ASC | DESC……

首先按照<字段名1>的順序排列記錄，若多條記錄的<字段名1>值相同，則按<字段名2>的順序排列 ASC升序 DESC降序 （默認(rèn)升序）。使用limit 位置偏移量 <行數(shù)> 指定查詢結(jié)果的數(shù)量 ，位置偏移量指示從第幾行開始顯示，0表示從第1行開始，若省略默認(rèn)為0，<行數(shù)>指定返回的記錄條數(shù)。

顯示?jdtj 數(shù)據(jù)表中街道個數(shù)最多的10個的所有字段

????????select * from jdtj

????????order by jdgs desc

????????limit 10;

顯示 jdtj 數(shù)據(jù)表中街道個數(shù)最少的10個的所有字段

????????select * from jdtj

????????order by jdgs asc

????????limit 10;

按街道個數(shù)從高到底，街道個數(shù)相同的按省份升序排序顯示 jdtj 數(shù)據(jù)表中街道個數(shù)大于35的所有字段

????????select * from jdtj

????????where jdgs>35

????????order by jdgs desc,sf asc;

八.數(shù)據(jù)查詢（二）

第1關(guān)：多表查詢

????????在連接查詢中引用兩個表的公共字段時，必須在字段前添加表名作為前綴，否則系統(tǒng)會提示出錯。

二表查詢:

select *? from 數(shù)據(jù)表1，數(shù)據(jù)表2? where 連接條件

或

from 數(shù)據(jù)表1 join 數(shù)據(jù)表2 on 連接條件

三表查詢:

from 數(shù)據(jù)表1 ,數(shù)據(jù)表2 ,數(shù)據(jù)表3 where 連接條件1 and 連接條件2

根據(jù)讀者(reader)和借閱(borrow)數(shù)據(jù)表，查詢王穎珊的借閱記錄，包括條形碼txm、借閱日期jyrq、還書日期hsrq

????????select txm , jyrq , hsrq

????????from borrow , reader

????????where reader.dzzh = borrow.dzzh and xm='王穎珊';

根據(jù)圖書(book)和借閱(borrow)數(shù)據(jù)表，查詢李白全集被借閱的情況：包括讀者證號dzzh、借閱日期jyrq、還書日期hsrq

????????select dzzh , jyrq , hsrq

????????from borrow , book

????????where borrow.txm = book.txm and book.sm='李白全集';

根據(jù)讀者(reader)、圖書(book)和借閱(borrow)數(shù)據(jù)表查詢沒有被歸還的借閱信息：包括讀者證號dzzh、姓名xm、電話dhhm、條形碼txm、書名sm、借閱日期jyrq

提示：通過isnull(表達(dá)式)可以判斷表達(dá)式是否NULL值

????????select reader.dzzh , xm , dhhm , book.txm , sm , borrow.jyrq

????????from reader,book,borrow

????????where reader.dzzh=borrow.dzzh and book.txm = borrow.txm and isnull(hsrq);

第2關(guān)：多表查詢及統(tǒng)計(jì)分組

注意：order by <表達(dá)式1>,<表達(dá)式2> 表示首先按第一個表達(dá)式的值排序，第一個表達(dá)式的值相同的再按第二個表達(dá)式的值排序

第一題： 統(tǒng)計(jì)每本書借閱的次數(shù),顯示書名和借閱次數(shù)（借閱次數(shù)命名為jycs),按借閱次數(shù)降序排列,借閱次數(shù)相同的按書名降序排列 （提示：borrow數(shù)據(jù)表的一條數(shù)據(jù)對應(yīng)一次借閱）

????????select sm,count(*)jycs ?

????????from borrow left join book on book.txm=borrow.txm

????????group by sm

????????order by count(*) desc,sm desc;

第二題： 統(tǒng)計(jì)借閱次數(shù)在2次以上的圖書的借閱的次數(shù),顯示書名和借閱次數(shù)，按借閱次數(shù)降序排列,借閱次數(shù)相同的按書名降序排列

????????select sm,count(*)jycs ?

????????from borrow left join book on book.txm=borrow.txm

????????group by sm having(count(*)>=2)

????????order by count(*) ?desc,sm desc;

第三題 統(tǒng)計(jì)每個出版社的圖書的借閱次數(shù),顯示出版社的名稱和借閱次數(shù)，按借閱次數(shù)降序排列,借閱次數(shù)相同的按出版社降序排列

????????select cbs,count(*)jycs

????????from borrow left join book on book.txm=borrow.txm

????????group by cbs

????????order by count(*) desc,cbs desc;

第四題： 統(tǒng)計(jì)每位讀者借閱的次數(shù),顯示姓名和借閱次數(shù),按借閱次數(shù)降序排列，借閱次數(shù)相同的按姓名降序排列

????????select xm,count(*)jycs

????????from borrow left join reader on borrow.dzzh=reader.dzzh

????????group by xm

????????order by ?count(*) desc,xm desc;

第五題： 統(tǒng)計(jì)研究生讀者借閱的次數(shù),顯示姓名和借閱次數(shù)，按借閱次數(shù)降序排列，借閱次數(shù)相同的按姓名降序排列

????????select xm,count(*)jycs

????????from borrow left join reader on borrow.dzzh=reader.dzzh

????????where sf='研究生'

????????group by xm

????????order by count(*) desc,xm desc;

第3關(guān)：子查詢

????????在select語句中，一個查詢語句完全嵌套在另一個查詢語句的where或having的條件短語中，稱為子查詢或嵌套查詢。 通常把條件短語中的查詢成為子查詢，父查詢則使用子查詢的查詢結(jié)果作為查詢條件。

查詢與李白全集同一個出版社的圖書的書名（不包括李白全集）

????????select sm

????????from book

????????where cbs = (

????????????????select cbs

????????????????from book ????????

????????????????where sm ='李白全集'

????????????????) and sm !='李白全集';

查詢高于圖書的平均售價(sj)的圖書的書名和售價

????????select sm, sj

????????from book

????????where sj > (

????????????????select avg(sj)

????????????????from book);

查詢售價最高的圖書的條形碼、書名和售價

????????select txm, sm, sj

????????from book

????????where sj = (

????????????????select max(sj)

????????????????from book);

查詢售價最低的圖書的條形碼、書名和售價

????????select txm, sm, sj

????????from book

????????where sj = (

????????????????select min(sj)

????????????????from book);

第4關(guān)：多表子查詢

查詢曾經(jīng)借過圖書的讀者的讀者證號和姓名

select distinct reader.dzzh,xm

from reader,borrow

where reader.dzzh = borrow.dzzh

order by reader.dzzh asc;

查詢曾經(jīng)沒有被借閱的圖書的條形碼和書名

select book.txm, sm

from book left join borrow?on book.txm = borrow.txm

where jyrq is null;

查詢與孫思旺借過相同圖書的讀者的讀者證號和姓名，按讀者證號升序排列

select distinct reader.dzzh, xm

from reader,borrow

where reader.dzzh = borrow.dzzh and txm in(

????????select borrow.txm

????????from reader,borrow,book

????????where reader.dzzh = borrow.dzzh and borrow.txm = book.txm and xm = '孫思旺'

????????) and xm !='孫思旺'

order by reader.dzzh asc;

查詢借閱過李白全集的讀者所借過的其他圖書的書名 按書名升序排列

?select distinct sm

?from borrow,book

?where borrow.txm = book.txm and dzzh in(

????????select dzzh

????????from borrow,book

????????where borrow.txm = book.txm and sm ='李白全集'

????????)and sm !='李白全集'

order by sm asc;

九. MySQL數(shù)據(jù)庫 -?連接查詢

第1關(guān)：內(nèi)連接查詢

內(nèi)連接查詢

僅將兩個表中滿足連接條件的行組合起來作為結(jié)果集，稱為內(nèi)連接； 關(guān)鍵字：[inner] join ... on

?表1 [inner] join 表2 on 表1.字段=表2.字段

????????從表1中取出每一條記錄，去表2中與所有的記錄進(jìn)行匹配，匹配必須是某個條件在表1中與表2中相同，最終才會保留結(jié)果，否則不保留。inner 關(guān)鍵字可省略不寫；on 表示連接條件：條件字段就是代表相同的業(yè)務(wù)含義，大多數(shù)情況下為兩張表中的主外鍵關(guān)系

查詢數(shù)據(jù)表tb_student，tb_class中學(xué)生姓名（studentName）和對應(yīng)的班級名稱（className）

????????select tb_student.name as studentName , tb_class.name as className

????????from tb_student join tb_class on tb_class.id = tb_student.class_id ;

第2關(guān)：外連接查詢

外連接查詢

以某張表為主，取出里面的所有記錄，然后每條與另外一張表進(jìn)行連接，不管能不能匹配上條件，最終都會保留。能匹配，正確保留；不能匹配，其它表的字段都置空（null），稱為外連接。 外連接查詢分為左外連接查詢和右外連接查詢 關(guān)鍵字：left/right [outer] join ... on?

? ?表1 left/right [outer] join 表2 on 表1.字段=表2.字段

左外連接：在內(nèi)連接的基礎(chǔ)上，還包含表1中所有不符合條件的數(shù)據(jù)行，并在其中的表2列填寫 NULL；右外連接：在內(nèi)連接的基礎(chǔ)上，還包含表2中所有不符合條件的數(shù)據(jù)行，并在其中的表1列填寫 NULL。

使用左外連接查詢所有學(xué)生姓名和對應(yīng)的班級

????????select??tb_student.name as studentName ,? tb_class.name as className

????????from??tb_class right join tb_student on tb_class.id=tb_student.class_id;

使用右外連接查詢所有學(xué)生姓名和對應(yīng)的班級

????????select??tb_student.name as studentName ,? tb_class.name as className

????????from??tb_class left join tb_student on tb_class.id=tb_student.class_id;

第3關(guān)：復(fù)合條件連接查詢

復(fù)合條件連接查詢

????????復(fù)合條件連接查詢，就是在連接查詢的過程中，通過添加過濾條件來限制查詢結(jié)果，使查詢結(jié)果更加精確。

查詢所有班級里分?jǐn)?shù)在90分以上的學(xué)生的姓名和學(xué)生的成績以及學(xué)生所在的班級

????????select s1.name as studentName,score,s2.name as className

????????from tb_student as s1 ,tb_class as s2

????????where s1.class_id=s2.id and s1.score>90 order by score desc;

十. MySQL數(shù)據(jù)庫 - 子查詢

第1關(guān)：帶比較運(yùn)算符的子查詢

子查詢

嵌套在查詢內(nèi)部，且必須始終出現(xiàn)在圓括號內(nèi)

分為四類：

標(biāo)量子查詢：返回單一值的標(biāo)量，最簡單的形式； 列子查詢：返回的結(jié)果集是 N 行一列； 行子查詢：返回的結(jié)果集是一行 N 列； 表子查詢：返回的結(jié)果集是 N 行 N 列。

? ?eg: SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);

帶比較運(yùn)算符的子查詢

帶有比較運(yùn)算符的子查詢是指父查詢和子查詢使用比較運(yùn)算符連接的嵌套查詢 使用場景：當(dāng)用戶能夠確切的知道內(nèi)層查詢返回的是單個值時,可以使用比較運(yùn)算符

運(yùn)算符說明>大于>=大于或等于=等于!= 或 <>不等于<小于<=小于或等于

查詢大于所有平均年齡的員工姓名與年齡

????????select name,age

????????from tb_emp

????????where age > (

????????????????select avg(age)

????????????????from tb_emp ) ;

第2關(guān)：關(guān)鍵字子查詢

ALL關(guān)鍵字

? ALL必須接在一個比較運(yùn)算符的后面，表示與子查詢返回的所有值比較都為 TRUE則返回TRUE。

????????eg:??SELECT col1 FROM table1 WHERE col1 > ALL (SELECT col2 FROM table2)

ANY和SOME關(guān)鍵字

? ANY與比較操作符聯(lián)合使用，表示與子查詢返回的任何值比較為TRUE ，則返回TRUE。 SOME是ANY的別名，一般用的比較少。

IN關(guān)鍵字

IN的意思就是指定的一個值是否在這個集合中，如果在就返回TRUE；否則就返回FALSE了，同IN(項(xiàng)1，項(xiàng)2，...) IN 是 = ANY 的別名，二者相同，但 NOT IN 的別名卻不是 <> ANY 而是 <> SOME

使用 ALL 關(guān)鍵字進(jìn)行查詢

? ? select position,salary

? ? from tb_salary

? ? where salary > ALL (

? ?? ? select salary

? ?? ? from tb_salary

? ?? ? where position="java");

使用 ANY 關(guān)鍵字進(jìn)行查詢

? ? select position,salary

? ? from tb_salary

? ? where salary > ANY (

? ? ? ?select salary

? ? ? ?from tb_salary

?? ? ?where position="java");

使用 IN 關(guān)鍵字進(jìn)行查詢

? ? select position,salary

? ? from tb_salary

? ? where salary IN (

? ? ? ?select salary

? ? ? ?from tb_salary

? ? ? ?where position="java");

十一. MySQL數(shù)據(jù)庫 -?分組選擇數(shù)據(jù)

第1關(guān)：GROUP BY 與 聚合函數(shù)

GROUP BY與聚合函數(shù)的使用

select [聚合函數(shù)] 字段名 from 表名 [where 查詢條件]　[group by 字段名]

????????注:?select指定的字段要么就要包含在Group By語句的后面，作為分組的依據(jù)；要么就要被包含在聚合函數(shù)中。

????????查詢表中2,3,4年級中分別男女的總?cè)藬?shù)

? ? select gradeId,sex,count(*)

? ? from student

? ? where gradeId in(2,3,4)

? ? group by gradeId,sex;

第2關(guān)：使用 HAVING 與 ORDER BY

使用having子句進(jìn)行分組篩選

? having子句用來對分組后的數(shù)據(jù)進(jìn)行篩選,常與Group by連用

select [聚合函數(shù)] 字段名 from 表名 [where 查詢條件] [group by 字段名] [having 字段名 篩選條件]

? select語句中，where、group by、having子句和聚合函數(shù)的執(zhí)行次序如下：

where子句從數(shù)據(jù)源中去除不符合條件的數(shù)據(jù)；然后group by子句搜集數(shù)據(jù)行到各個組中；接著統(tǒng)計(jì)函數(shù)為各個組計(jì)算統(tǒng)計(jì)值；最后having子句去掉不符合其組搜索條件的各組數(shù)據(jù)行。

Having與Where的區(qū)別

? where子句都可以用having代替，區(qū)別在于where過濾行，having過濾分組

where子句的作用是在對查詢結(jié)果進(jìn)行分組前，將不符合where條件的行去掉，where條件中不能包含聚組函數(shù)，使用where條件過濾出特定的行 having子句篩選滿足條件的組，在分組之后過濾數(shù)據(jù)，條件中經(jīng)常包含聚組函數(shù)，使用having條件過濾出特定的組，也可以使用多個分組標(biāo)準(zhǔn)進(jìn)行分組

Group By?和?Order By

? ?select [聚合函數(shù)] 字段名 from 表名 [where 查詢條件]　[group by 字段名] [order by 字段名 排序方向]

查詢表中至少有兩門課程在90分以上的學(xué)生信息

? ? select sno,count(*)

? ? from tb_grade

? ? where score>=90

? ? group by sno having count(*)>=2;

查詢表中平均成績大于90分且語文課在95分以上的學(xué)生信息

? ? select sno,avg(score)

? ? from tb_grade

? ? where sno in (

? ? ? ?select sno

? ? ? ?from tb_grade

? ? ? ?where score>=95 and pno='語文')

? ? group by sno having avg(score)>=90;

十二. MySQL數(shù)據(jù)庫 -?數(shù)據(jù)庫和表的基本操作（一）

第1關(guān)：查看表結(jié)構(gòu)與修改表名

查看數(shù)據(jù)表基本結(jié)構(gòu)

describe表名；?

查看數(shù)據(jù)表詳細(xì)結(jié)構(gòu)

show create table表名;

修改表名

alter table舊表名 rename新表名;?

把數(shù)據(jù)表tb_emp改名為jd_emp；

? ALTER TABLE tb_emp RENAME jd_emp;

查看該數(shù)據(jù)庫下數(shù)據(jù)表的列表；

? SHOW TABLES;

查看數(shù)據(jù)表jd_emp的基本結(jié)構(gòu)。

? DESCRIBE jd_emp;

第2關(guān)：修改字段名與字段數(shù)據(jù)類型

修改字段名

? ? ? ? alter table表名 change舊字段名 新字段名 新數(shù)據(jù)類型;?

????????提示： 如果不需要修改字段的數(shù)據(jù)類型，可以把新字段的數(shù)據(jù)類型設(shè)置為和原來一樣，不要空著它

修改字段數(shù)據(jù)類型

? ? ? ? ?alter table表名 modify字段名 數(shù)據(jù)類型;

把數(shù)據(jù)表tb_emp的字段Id改名為prod_id，數(shù)據(jù)類型不變

? ?alter table ?tb_emp change Id prod_id int(11);

把數(shù)據(jù)表tb_emp字段Name的數(shù)據(jù)類型改為varchar(30)

? ?alter table tb_emp modify ?Name varchar(30);?

??

第3關(guān)：添加與刪除字段

添加字段

? ?ALTER TABLE 表名 ADD 新字段名 數(shù)據(jù)類型 [約束條件] [FIRST|AFTER] 已存在字段名;?

????????在表的最后一列添加字段

? ? ? ? ? ?不做[FIRST|AFTER]的位置說明，在添加字段時MySQL會默認(rèn)把新字段加入到表的最后一列。

? ?eg: 字段prod_country添加到表Mall_products的最后一列

? ? ? alter table Mall_products add prod_country varchar(30);

????????在表的第一列添加字段

? ? ? ? ? ? 做FIRST的位置說明

????????eg:??字段prod_country添加到表Mall_products的第一列?

????????alter table Mall_products add prod_country varchar(30) frist;

????????在表的指定列后添加字段

? ? ? ? ? ? 做AFTER的位置說明，注明添加在哪個字段后面

? ?eg:prod_country添加到表Mall_products的 prod_name字段的后面。

? ? ? ? ? ? ? alter table Mall_products add prod_country varchar(30) after prod_name;

刪除字段

????????alter table表名 drop字段名;?

????????eg:? 字段prod_price從表Mall_products中刪除

???????????????alter table Mall_products drop prod_price;

在數(shù)據(jù)表tb_emp的Name字段后添加字段Country，數(shù)據(jù)格式為varchar(20)

? ?alter table tb_emp add?Country varchar(20) after?Name;

刪除數(shù)據(jù)表tb_emp中的字段Salary

? ?alter table?tb_emp drop Salary;

第4關(guān)：修改字段的排列位置

修改字段的排列位置?

????????ALTER TABLE 表名 MODIFY 字段1 數(shù)據(jù)類型 FIRST|AFTER 字段2;?

? ?字段1指要修改位置的字段，F(xiàn)IRST與AFTER 字段2為可選參數(shù)

????????修改字段為表的第一個字段

????????做FIRST的位置說明

? ? ? ? eg:? 把字段prod_price調(diào)整到表Mall_products的第一列

????????????????alter table Mall_products modify prod_price float first;

????????修改字段到表的指定列之后

????????做AFTER 字段2的位置說明

? ? ? ? eg:? 字段prod_price調(diào)整到字段prod_country的后面

????????????????alter table? Mall_products?modify?prod_price float?after?prod_country;

將數(shù)據(jù)表tb_emp的Name字段移至第一列，數(shù)據(jù)格式不變

???????alter table?tb_emp modify Name varchar(25) first;

將DeptId字段移至Salary字段的后邊，數(shù)據(jù)格式不變

????????alter table tb_emp modify?DeptId INT(11) after Salary;

第5關(guān)：刪除表的外鍵約束

刪除表的外鍵約束

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵約束名;

????????刪除數(shù)據(jù)表tb_emp的外鍵約束emp_dept

????????alter table?tb_emp drop foreign key emp_dept;

十三. MySQL數(shù)據(jù)庫 -?數(shù)據(jù)庫和表的基本操作（二）

第1關(guān)：插入數(shù)據(jù)

為表的所有字段插入數(shù)據(jù)

????????INSERT INTO 表名 (字段名) VALUES (內(nèi)容);

? ?insert into MyUser(name,age) values('zhnagsan',18);

為表的指定字段插入數(shù)據(jù)

? ?insert into MyUser(name) values('lisi'),('fawaikuangtu'),('zhangsan');

為空數(shù)據(jù)表tb_emp同時添加3條數(shù)據(jù)內(nèi)容

????????insert?into tb_emp (Id,Name,Deptid,Salary)

????????values?(1,'Nancy',301,2300.00),(2,'Tod',303,5600.00),(3,'Carly',301,3200.00);

第2關(guān)：更新數(shù)據(jù)

更新表中指定的內(nèi)容

UPDATE 表名 SET 字段名1 = 內(nèi)容1, 字段名2 = 內(nèi)容2, 字段名3 = 內(nèi)容3 WHERE 過濾條件;

? ? ? ?eg:? 表Mall_products2 中 Span?換成?Pakistan,地區(qū)代碼換為?92 。

? ? ?UPDATE Mall_products2

? ? ?SET country_name = "Pakistan", country_id = 92

? ? ?WHERE id = 2;

數(shù)據(jù)表tb_emp?中?Carly改為Tracy，相應(yīng)的，301改為302，3200.00改為4300.00

????????update?tb_emp

????????set?Name = "Tracy", Deptid = 302, Salary = 4300.00

????????where?Id=3;

第3關(guān)：刪除數(shù)據(jù)

刪除表中的指定行

????????DELETE FROM 表名 WHERE 條件語句;

????????eg:??表Mall_products2同時刪除包含Span和Italy的兩行數(shù)據(jù)內(nèi)容

? ? ? ?delete?from Mall_products2

? ? ? ?where id=2 or id=3;

刪除表中的所有行

????????DELETE FROM 表名;?

????????eg:? 同時刪除表Mall_products2的所有數(shù)據(jù)內(nèi)容

????????????????delete?from Mall_products2;??????

? ? ? ? ?提示： TRUNCATE TABLE 語句也可以用來刪除表中的所有記錄。但是與 DELETE 不同的是，TRUNCATE TABLE 語句直接刪除的是表，而不是表中的內(nèi)容，刪除結(jié)束后還會重新創(chuàng)建一個表。所以它的執(zhí)行速度會比 DELETE 語句快。 語法為：truncate table表名;

將數(shù)據(jù)表tb_emp中Salary大于3000的數(shù)據(jù)行刪除。

????????????????delete?from?tb_emp where?Salary>3000;

十四. MySQL開發(fā)技巧 - 視圖

視圖的定義

????????視圖（view）是一種虛擬存在的表，是一個邏輯表，本身并不包含數(shù)據(jù)。通過視圖，可以展現(xiàn)基表（用來創(chuàng)建視圖的表）的部分?jǐn)?shù)據(jù)；視圖數(shù)據(jù)來自定義視圖的查詢表，使用視圖的大部分情況是為了保障數(shù)據(jù)安全性，提高查詢效率。

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

????????①. 簡單：使用視圖的用戶完全不需要關(guān)心后面對應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件，對用戶來說已經(jīng)是過濾好的復(fù)合條件的結(jié)果集

????????②. 安全：使用視圖的用戶只能訪問他們被允許查詢的結(jié)果集，對表的權(quán)限管理并不能限制到某個行或列，但是通過視圖就可以簡單的實(shí)現(xiàn)

????????③. 數(shù)據(jù)獨(dú)立：一旦視圖的結(jié)構(gòu)確定了，可以屏蔽表結(jié)構(gòu)變化對用戶的影響，源表增加列隊(duì)視圖沒有影響；源表修改列名，則可以通過修改視圖來解決，不會造成對訪問者的影響

創(chuàng)建視圖

? ? CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

? ? VIEW view_name [(column_list)]

? ? AS select_statement

? ? [WITH [CASCADED | LOCAL] CHECK OPTION]

????????參數(shù)說明：

OR REPLACE：表示替換已有視圖； ALGORITHM：表示視圖選擇算法，默認(rèn)算法是UNDEFINED(未定義的)： MySQL 自動選擇要使用的算法 ；merge合并；temptable臨時表； column_list：可選參數(shù)，指定視圖中各個屬性的名詞，默認(rèn)情況下與select語句中查詢的屬性相同； select_statement：表示select語句； [WITH [CASCADED | LOCAL] CHECK OPTION]：表示視圖在更新時保證在視圖的權(quán)限范圍之內(nèi)；cascade是默認(rèn)值，表示更新視圖的時候，要滿足視圖和表的相關(guān)條件；local表示更新視圖的時候，要滿足該視圖定義的一個條件即可。

操作視圖

????????視圖是邏輯表，也就是說視圖不是真實(shí)的表，但操作視圖和操作普通表的語法是一樣的。用戶可以在視圖中無條件地使用select語句查詢數(shù)據(jù)。但使用insert、update和delete操作需要在創(chuàng)建視圖時滿足以下條件（滿足以下條件的視圖稱為可更新視圖）：

from子句中只能引用有1個表（真實(shí)表或可更新視圖）； 不能包含 with、distinct、group by、having、limit等子句； 不能使用復(fù)合查詢，即不能使用union、intersect、except等集合操作； select子句的字段列表不能包含聚合、窗口函數(shù)、集合返回函數(shù)。

刪除視圖

????????若視圖不再被需要，我們可以將其刪除，且視圖的刪除并不影響源表中的數(shù)據(jù)

? ? DROP VIEW view_name;

在student表上創(chuàng)建視圖stu_view，查詢math、chinese字段并定義一個math+chinese成績之和字段，值為兩者之和；

????????????????CREATE VIEW stu_view

????????????????AS select math , chinese , math+chinese

????????????????FROM student;

在student表和stu_info表上，創(chuàng)建stu_classes視圖，查詢出stu_id、姓名和班級，查詢條件為兩表中的stu_id字段相同。

????????????????CREATE VIEW stu_classes

????????????????AS??select? student.stu_id , student.name , stu_info.classes

????????????????FROM student , stu_info

????????????????WHERE student . stu_id = stu_info . stu_id;

十五. MySQL開發(fā)技巧 - 分頁和索引

第1關(guān)：MySQL 分頁查詢

limit 和 offset 實(shí)現(xiàn)的分頁查詢

? ? ? ? eg:? 檢索記錄行符合條件的10條數(shù)據(jù) ????????select * from table where xxx="xxx" limit 10;

? ? ? ? eg:? 檢索記錄行符合條件的11-20條數(shù)據(jù)

????????select * from table where xxx="xxx" limit 10 offset 10;

????????或

????????select * from table where xxx="xxx" limit 10,10;

? ? ? ? 提示：limit之后的數(shù)字代表偏移量，offset代表返回記錄的最大值，可以直接把offset直接省略掉

大數(shù)據(jù)量下分頁查詢優(yōu)化

????????select * from table where xxx="xxx" limit 100,100;

????????select * from table where xxx="xxx" limit 1000,100;

????????select * from table where xxx="xxx" limit 10000,100;

????????select * from table where xxx="xxx" limit 100000,100;

????????select * from table where xxx="xxx" limit 1000000,100;

????????優(yōu)化

????????直接定位到偏移量所在記錄，先查詢到偏移量位置，再進(jìn)行分頁：

????????select *

????????from table

????????where xxx="xxx" and id>=(

????????????????select id

????????????????from table

????????????????where xxx="xxx"limit 100000,1)

????????limit 100;

按prod_id升序查詢表products中第6-10行數(shù)據(jù),只要求prod_id字段即可（prod_id從1開始）；

????????select prod_id

????????from products

????????limit 5,5;

利用子查詢優(yōu)化查詢出按prod_id升序的第10-15行數(shù)據(jù)，只要求prod_id字段即可。

????????select prod_id

????????from products

????????where prod_id>=(

????????????????select prod_id

????????????????from products

????????????????limit 10,1)

????????limit 5;

第2關(guān)：索引（單列索引）

索引是一張?zhí)厥獾谋恚摫肀４媪酥麈I與索引字段，并指向?qū)嶓w表的記錄。

單列索引分類和創(chuàng)建

????????1.主鍵索引

????????????????主鍵索引一般在建表時創(chuàng)建，不允許有空值并且值唯一，一般會設(shè)為 int 而且是 AUTO_INCREMENT 自增類型的

????????????????create table `student` (

????????????????`ID` int(11) not null?auto_increment,

????????????????primary key(`ID`)

????????????????) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

????????2.普通索引

? ? ? create?index?name_index on?`student`(`name`);

? ? ? 或

? ? ? alter table student add?index?name_index(`name`);

????????????????注：這里的 ` 不是單引號，而是鍵盤 1 數(shù)字左邊的符

????????3.唯一索引

????????????????和主鍵索引類似，要求字段唯一，但是可以允許字段為空

? ? ?create?unique index?name_index on?`student`(`name`);

? ? ? ? ? ? ?唯一索引可以用來對數(shù)據(jù)進(jìn)行強(qiáng)制性要求，可以禁止某表的某個字段出現(xiàn)重復(fù)數(shù)據(jù)

創(chuàng)建student表結(jié)構(gòu)并且設(shè)置id為主鍵索引

????????????????create table student (

????????????????? ? id int(11) not null auto_increment,

????????????????? ? primary key?(`id`),

????????????????? ? name varchar(20) not null,

????????????????? ? score int(10)

????????????????);

對name建立唯一索引 (索引名name_index)

????????????????create unique index name_index on`student`(`name`);

對score建立普通索引?(索引名score_index)

????????????????create index score_index on `student`(`score`);

????????????????SHOW INDEX FROM student;

第3關(guān)：索引（組合索引）

創(chuàng)建組合索引

? ? ? ? eg:? 創(chuàng)建三個索引：name,name city,name city score

? ? ?alter table student add index name_city_score (name,city,score);

在表person?中的?name、age、address字段上創(chuàng)建組合索引，索引名為name_city_score

????????alter table person add index name_city_score?(?name, age, address?);

十六. MySQL數(shù)據(jù)庫 - 其他函數(shù)的使用

第1關(guān)：字符函數(shù)

字符串的常用函數(shù)：

函數(shù)作用CONCAT()字符串的拼接CONCAT_WS()使用指定的字符將目標(biāo)字符串進(jìn)行拼接FORMAT()數(shù)字格式化LOWER()字符串全部轉(zhuǎn)換為小寫UPPER()字符串全部轉(zhuǎn)換為大寫LEFT()獲取左側(cè)的字符串RIGHT()獲取右側(cè)的字符串LENGTH()計(jì)算字符串的長度LTRIM()刪除前導(dǎo)的空格RTRIM()刪除后續(xù)的空格TRIM()刪除前導(dǎo)和后續(xù)的空格SUBSTRING()字符串截取SUBSTRING_INDEX()分隔字符串REPLACE()字符串替換[NOT] LIKE模式匹配

1. CONCAT()與CONCAT_WS()

將字符進(jìn)行連接

concat()

concat_ws()

????????????????使用指定字符進(jìn)行連接，最少需要三個參數(shù)，第一個參數(shù)為指定連接符：

2. left()與right()

????????左右側(cè)的字符獲取，需要兩個參數(shù)，第一個參數(shù)是從哪個字符串中獲取，第二個參數(shù)是獲取的長度

3. LTRIM()、RTRIM() 與 TRIM()

????????去掉字符串中含有的空格

ltrim()

????????去掉其中字符前導(dǎo)的空格

rtrim()

????????去掉其中字符后導(dǎo)的空格

trim()

????????去掉其中字符全部的空格，刪除字符串中自定字符

????????????????刪除前導(dǎo)?? : leading

????????????????刪除后導(dǎo)的?? : trailing

????????????????刪除全部???：both

4.SUBSTRING()、SUBSTRING_INDEX()與REPLACE()

substring()

????????用于字符串的截取

????????????????????????從第一位（注意第一位為1而非0）開始截取MySQL的二位字符

substring_index()

????????通過特殊符號將字符串分隔?

????????????????????????將字符串www.educoder.net通過.進(jìn)行分隔，可從前后取字符串?

replace()

????????替換字符串中間自定字符

????????以首字母大寫，其他字母小寫的方式顯示所有員工的姓名。

? ?select concat?(upper?( left ( Name, 1 ) ) , lower?(?substr?( Name from 2 ) ) )

? ?as 'Name'

? ?from employee;

第2關(guān)：數(shù)學(xué)函數(shù)

數(shù)值運(yùn)算符

??常與數(shù)值結(jié)合使用的函數(shù)：

函數(shù)說明CEIL()進(jìn)一（向上）取整FLOOR()舍一（向下）取整POWER()冪運(yùn)算SQRT()平方根運(yùn)算ROUND()四舍五入ABS()絕對值計(jì)算RAND()生產(chǎn)0-1的隨機(jī)數(shù)TRUNCATE()數(shù)字截取DIV整數(shù)除法MOD取余數(shù)(取模)

1. CEIL()和FLOOR()

CEIL()函數(shù) :? 向上取整FLOOR():? 向下取整

2. DIV和MOD

?div?整數(shù)除法函數(shù)?

mod?取模函數(shù)

3. ROUND()、POWER()、SQRT()和TRUNCATE()

ROUND()函數(shù)：四舍五入

POWER()函數(shù)：冪運(yùn)算

????????2^3=8

SQRT()函數(shù)：平方根運(yùn)算

TRUNCATE()函數(shù)：數(shù)字截取，只做截取，不發(fā)生四舍五入， 下面示例為：

比較運(yùn)算符

函數(shù) 說明[NOT] BETWEEN...AND...[不]在范圍內(nèi)[NOT] IN()[不]在列出值范圍內(nèi)IS [NOT] NULL[不]為空

BETWEEN...AND...：指定一個范圍，只要選擇的數(shù)字在這個范圍內(nèi)則為1否則為0

IN()：指定列出值，只要選擇的數(shù)字存在其中則為1否則為0

IS NULL：判斷字段是否為空

?

????????????????根據(jù)下面表達(dá)式修改學(xué)生的最終成績，結(jié)果使用四舍五入保留兩位小數(shù)：

update Score set s_score=TRUNCATE(s_score - ROUND(SQRT((POWER(4,4)-POWER(3,3))/POWER(2,2)),2),2);

第3關(guān)：日期時間函數(shù)和流程控制類函數(shù)

日期函數(shù)

? 常用的函數(shù)：

函數(shù)說明NOW()當(dāng)前日期和時間CURDATE()當(dāng)前日期CURTIME()當(dāng)前時間DATE_ADD()日期變化DATEDIFF()日期差值DATE_FORMAT()日期格式化YEAR()返回日期的年份MONTH()返回日期的月份WEEK()返回日期的周號

1.? NOW()、CURDATE()和CURTIME()

now():返回當(dāng)前日期和時間

curdate(): 單獨(dú)返回當(dāng)前日期curtime():?返回當(dāng)前時間

2.? DATE_ADD()、DATEDIFF()和DATE_FORMAT()

date_add():?日期的變化，第一個參數(shù)為指定時間，第二個參數(shù)為增加（減少）的單位值（YEAR、MONTHS、WEEK、DAY）

datediff(): 返回二個日期之間的差值

DATE_FORMAT(): 日期格式化, 日期格式轉(zhuǎn)換成另一種格式

? 以下是在format字符串中常用的說明符:

格式描述%d月的天，數(shù)值（00-31）%H小時 （00-23）%h小時 （01-12）%m月，數(shù)值（00-12）%pAM 或 PM%s秒（00-59）%T時間，24-小時（hh:mm:ss）%Y年，4?位

3.? YEAR()和MONTH()

流程控制類函數(shù)

函數(shù)說明IF()判斷，流程控制IFNULL()字段值是否為空CASE搜索語句

IF()函數(shù)：IF(a,b,c)?如果a為真返回b，否則返回c

IFNULL()函數(shù)：IFNULL(a,b)?如果a不為null返回a，否則返回b

CASE：CASE WHEN a then b end?判斷如果a為真返回b。

????????查詢所有學(xué)生的出生年份及至2019-01-01時的年齡

????????????????select year(s_birth) year,'2019-01-01'-s_birth '年齡' from Student;

????????查詢所有課程中的最高分、最低分、平均分和及格率，按課程id升序輸出

????????????????select c.c_id 課程id,

????????????????c_name 課程名,

????????????????max(s_score) 最高分,

????????????????min(s_score) '最低分',

????????????????round(avg(s_score),2) '平均分',

????????????????round((count(s_score>=60 or null)/count(s_score))*100,2) '及格率'

????????????????from Score s,Course c

????????????????where s.c_id=c.c_id

????????????????group by s.c_id;

十七. 數(shù)據(jù)庫E-R圖?

編程要求

繪制六個模塊的 E-R 圖，分別為課程、選課、學(xué)生、班級、學(xué)院、教師。

課程包含以下字段名： 課程編號、課程名稱、學(xué)時、學(xué)分、人數(shù)上限、上課時間、上課教室、開課學(xué)期、開始選課時間、選課結(jié)束時間、課程編號。

選課包含以下字段名： 選課時間、成績。

學(xué)生包含以下字段名： 學(xué)號、姓名、密碼、性別、出生年月、電話、家庭住址、學(xué)生簡介。

班級包含以下字段名： 班級編號、班級名稱、年度。

學(xué)院包含以下字段名： 學(xué)院編號、學(xué)院名稱。

教師包含以下字段名： 教師編號、教師姓名、密碼、性別、專業(yè)、職稱、教師簡介。

各個模塊之間的關(guān)系如下所示：

課程與學(xué)生之間的關(guān)系為選課：學(xué)生與課程的關(guān)系為多對多。

學(xué)生與班級的關(guān)系為所在：學(xué)生與班級的關(guān)系為多對 1。

學(xué)生與學(xué)院的關(guān)系為所屬：學(xué)生與學(xué)院的關(guān)系為多對 1。

班級與學(xué)院直接的關(guān)系為所屬：班級與學(xué)院的關(guān)系為多對 1。

教師與學(xué)院直接的關(guān)系為所屬：教師與學(xué)院的關(guān)系為多對 1。

教師與課程的關(guān)系為負(fù)責(zé)：教師與課程的關(guān)系為 1 對多。

柚子快報(bào)激活碼778899分享：mysql 數(shù)據(jù)庫（頭歌）

http://yzkb.51969.com/

相關(guān)鏈接