您好,登錄后才能下訂單哦!
本文主要給大家介紹SELECT單表、多表查詢升級(jí)及插入刪除,其所涉及的東西,從理論知識(shí)來獲悉,有很多書籍、文獻(xiàn)可供大家參考,從現(xiàn)實(shí)意義來講,億速云累計(jì)多年的實(shí)踐經(jīng)驗(yàn)可分享給大家。
SELECT:查詢
SELECT select-list FROM tb WHERE qualification 根據(jù)標(biāo)準(zhǔn)qualification查找對(duì)應(yīng)的行
查詢語句類型: qualification條件 field領(lǐng)域 distinct獨(dú)特的,沒有重復(fù)的
簡單查詢:
多表查詢:
子查詢:
SELECT * FROM tb_name: 查詢tb_name表的所有信息
SELECT field1,field2 FROM tb_name: 投影顯示所設(shè)定的領(lǐng)域條目(field),一個(gè)field就是一列
SELECT [DISTINCT] * FROM tb_name WHERE qualification;從tb_name表中選擇符合條件的獨(dú)特的不重復(fù)的條目。
FROM子句:表、多個(gè)表、其他SELECT語句
WHERE子句:布爾關(guān)系表達(dá)式 =、>、<、<=、>= 表示大于等于,小于等于,大于,小于,等于。
邏輯關(guān)系:AND(與&&) OR(或||) NOT(非!) XOR(異或)
mysql>SELECT Name,Age,Gender FROM students WHERE NOT Age>20 AND NOT Gender='M'; #選擇查詢年齡不大于20,且性別不是男的數(shù)據(jù),也可以寫成下面的條件
mysql> SELECT Name,Age,Gender FROM students WHERE NOT ( Age>20 OR Gender='M' );
特殊關(guān)系:BETWEEN ... AND ... 在兩者之間之間。
LIKE ''
%:任意長度任意字符
_:任意單個(gè)字符
Usage: SELECT Name FROM students WHERE Name LIKE 'Y%'; 查找Name字段以Y開頭的數(shù)據(jù)。
SELECT Name FROM students WHERE Name LIKE '%ing%';查找Name字段中必須包括ing的字段
SELECT Name FROM students WHERE Name LIKE 'Y___';查找Name字段中Y后面至少跟著3個(gè)字符的
REGEXP或者RLIKE 支持正則表達(dá)式:
Usage:SELECT Name,Age FROM students WHERE Name RLIKE '^[XY].*$';
查找Name字段行首為X或Y的數(shù)據(jù)
IN離散條件的查找:
Usage: SELECT Name,Age FROM students WHERE Age IN (18,20,25);
查找Age字段為18或20或25歲的相關(guān)數(shù)據(jù)
有空值的時(shí)候,比較:IS NULL ,NOT NULL ORDER BY ... 以...某字段升序排列
ORDER BY ... 以...某字段降序排列
Usage: SELECT Name,Age FROM students WHERE Name IS NULL;
查找Name字段為空值的數(shù)據(jù)
SELECT Name,Age FROM students WHERE Name IS NULL;
查找Name字段不為空值的數(shù)據(jù)
按照升序或者降序排列出查找的數(shù)據(jù):ORDER BY field_name {ASC|DESC};
ASC升序排列(默認(rèn)值)DESC降序排列
SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name;
查找CID字段不為空的數(shù)據(jù)并按字段Name的升序排列;
SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name DESC;
查找CID字段不為空的數(shù)據(jù)并按字段Name的降序排列;
字段別名:AS
Usage: SELECT Name AS Student_Name FROM student;
mysql> SELECT Name FROM student;
+------------+
| Name |
+------------+
| Li Lianjie |
| Cheng Long |
| Yang Guo |
| Guo Jing |
+------------+
4 rows in set (0.00 sec)
mysql> SELECT Name AS Student_Name FROM student;
+--------------+
| Student_Name | AS別名為Student_Name
+--------------+
| Li Lianjie |
| Cheng Long |
| Yang Guo |
| Guo Jing |
+--------------+
4 rows in set (0.00 sec)
LIMIT子句:LIMIT [offset,]Count offset偏移多少,Count顯示多少
Usage: SELECT Name AS Student_Name FROM student LIMIT 3;
查找顯示Name別名為Student_Name,只顯示前3個(gè)數(shù)據(jù)
Usage: SELECT Name AS Student_Name FROM student LIMIT 2,2;
查找顯示Name別名為Student_Name,偏移掉前2個(gè)數(shù)據(jù)不顯示,顯示第2個(gè)數(shù)據(jù)后的2個(gè)數(shù)據(jù)
mysql> SELECT Name AS Student_Name FROM student LIMIT 3;
+--------------+
| Student_Name |
+--------------+
| Li Lianjie |
| Cheng Long |
| Yang Guo |
+--------------+
3 rows in set (0.00 sec)
mysql> SELECT Name AS Student_Name FROM student LIMIT 2,2;
+--------------+
| Student_Name |
+--------------+
| Yang Guo |
| Guo Jing |
+--------------+
2 rows in set (0.00 sec)
聚合運(yùn)算:SUM()求和,MIN()最小值,MAX()最大值,AVG()平均值,COUNT()統(tǒng)計(jì)字段中相同數(shù)值的個(gè)數(shù);
新建立一張表:
mysql> CREATE TABLE class(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,Name CHAR(20) NOT NULL UNSIGNED, Age TINYINT NOT NULL);
mysql> INSERT INTO class (Name,Age) VALUES ('Yang Guo',22),('Guo Jing',46),('Xiao Longnv',18),('Huang Rong',40);
mysql> DESC class;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(20) | NO | | NULL | |
| Age | tinyint(4) | NO | | NULL | |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM class;
+----+-------------+-----+
| ID | Name | Age |
+----+-------------+-----+
| 1 | Yang Guo | 22 |
| 2 | Guo Jing | 46 |
| 3 | Xiao Longnv | 18 |
| 4 | Huang Rong | 40 |
+----+-------------+-----+
4 rows in set (0.00 sec)
mysql> SELECT SUM(Age) FROM class;
+----------+
| SUM(Age) |
+----------+
| 126 |
+----------+
1 row in set (0.02 sec)
mysql> SELECT MAX(Age) FROM class;
+----------+
| MAX(Age) |
+----------+
| 46 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT MIN(Age) FROM class;
+----------+
| MIN(Age) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT AVG(Age) FROM class;
+----------+
| AVG(Age) |
+----------+
| 31.5000 |
+----------+
1 row in set (0.00 sec)
分組:GROUP BY ... HAVING qualification 根據(jù)...分組 并且滿足條件qualification
對(duì)于GROUP BY的條件選擇需用HAVING作為條件篩選,而不是用WHERE
mysql> SELECT Age,Gender FROM class1 GROUP BY Gender; #以性別分組顯示
+-----+--------+
| Age | Gender |
+-----+--------+
| 18 | F |
| 22 | M |
+-----+--------+
2 rows in set (0.00 sec)
mysql> SELECT AVG(Age),Gender FROM class1 GROUP BY Gender; #求男同學(xué)和女同學(xué)的平均年齡。
+----------+--------+
| AVG(Age) | Gender |
+----------+--------+
| 29.0000 | F |
| 34.0000 | M |
+----------+--------+
2 rows in set (0.00 sec)
mysql> SELECT Name,AVG(Age) FROM class1 GROUP BY Gender HAVING Name RLIKE 'Y.*';
#求以Gender分組的年齡平局值,且只顯示以Y開頭的Name字段的平均值和姓名
+----------+----------+
| Name | AVG(Age) |
+----------+----------+
| Yang Guo | 34.0000 |
+----------+----------+
1 row in set (0.00 sec)
SELECT用法歸納:
多表查詢:
連接:
交叉連接:笛卡爾乘積 (Usag:SELECT * FROM students,course;查詢students和course表的 內(nèi)容)
自然連接:
Usage: SELECT students.Name,courses.Course FROM students,courses WHERE students.CID1=courses.CID 查詢students表和courses表CID1=CID的選項(xiàng),并顯示名稱
外連接:
左外連接:左表 LEFT JOIN 右表 ON 條件
右外連接:左表 RIGHT JOIN 右表 ON 條件
Usage:
SELECT s.Name,c.Name FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;
顯示左表中所有學(xué)生,并查看他們所選修的課程名稱,沒有選修課程的直接顯示NULL
SELECT s.Name,c.Name FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID;
查看右表中所有選修課程,并查看有哪些學(xué)生選修了,課程沒有被選修的直接顯示NULL
自連接:
對(duì)于一個(gè)表自己的多個(gè)字段進(jìn)行連接查詢
Usage:
SELECT c.Name AS student,s.Name AS teacher FROM students AS c,students AS s WHERE
c.TID=s.SID;
查看students表中TID和SID相同的字段,并顯示他們的學(xué)生名和老師名。AS取別名
子查詢:
查詢語句里面嵌套其他的子查詢,比較操作中使用子查詢,子查詢只能返回單個(gè)值;
Usage: SELECT Name FROM students WHERE Age > (SELECT AVG(Age) FROM students);
查詢年齡大于平均年齡的學(xué)生姓名
IN (): 使用子查詢
Usage:SELECT Name FROM students WHERE Age IN(SELECT Age FROM teacheers);
查詢學(xué)生年齡中與老師年齡一樣的學(xué)生姓名,IN表示子查詢可以是一組數(shù)值。
FROM中也可以插入子查詢:
Usage:SELECT Name,Age FROM (SELECT Name,Age FROM students) AS t WHERE t.Age >= 20;
從SELECT查詢的結(jié)構(gòu)的表中再從中查詢其他符合條件的數(shù)據(jù)
聯(lián)合查詢:
UNION :把兩張表連接成一張表查詢顯示出來
Usage: (SELECT Name,Age FROM class) UNION (SELECT CID,Couse FROM courses)
把courses表中CID和Couse字段跟class表中的Name和Age字段聯(lián)合起來顯示為一張表
mysql> SELECT Name,Age FROM class;
+-------------+-----+
| Name | Age |
+-------------+-----+
| Yang Guo | 22 |
| Guo Jing | 46 |
| Xiao Longnv | 18 |
| Huang Rong | 40 |
+-------------+-----+
4 rows in set (0.00 sec)
mysql> SELECT CID,Couse FROM courses;
+-----+-----------+
| CID | Couse |
+-----+-----------+
| 1 | physics |
| 2 | english |
| 3 | chemistry |
| 4 | maths |
+-----+-----------+
4 rows in set (0.00 sec)
mysql> (SELECT Name,Age FROM class) UNION (SELECT CID,Couse FROM courses);
+-------------+-----------+
| Name | Age |
+-------------+-----------+
| Yang Guo | 22 |
| Guo Jing | 46 |
| Xiao Longnv | 18 |
| Huang Rong | 40 |
| 1 | physics |
| 2 | english |
| 3 | chemistry |
| 4 | maths |
+-------------+-----------+
8 rows in set (0.00 sec)
實(shí)例:
1.挑選出courses表中沒有被students表中的CID2學(xué)習(xí)的課程的課程名稱;
msyql> SELECT Cname FROM courses WHERE CID NOT IN (SELECT DISTINCT CID2 FROM students
WHERE CID2 IS NOT NULL); #SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL從students表中找出CID2不為空的且不重復(fù)的行,然后從courses表中找到CID不在剛才查找的那些行
里面的Cname的值
2.找出students表中CID2有兩個(gè)或者以上的同學(xué)學(xué)習(xí)了的,同一門課程的課程名稱;
msyql> SELECT Cname FROM courses WHERE CID IN (SELECT CID2 FROM students GROUP
BY CID2 HAVING COUNT(CID2) >= 2); #查找一門課程至少2個(gè)同學(xué)學(xué)習(xí),并顯示課程名稱。
3.顯示每一個(gè)課程及其相關(guān)的老師,沒有老師教授的課程將其老師顯示為空;
msyql> SELECT t.Tname,c.Cname FROM teachers AS t RIGHT JOIN courses AS c ON t.CID=c.TID;
4.顯示每一位老師及其所教授的課程,沒有教授的課程保持為null;
msyql> SELECT t.Tname,c.Cname FROM teachers AS t LEFT JOIN courses AS c ON t.CID=c.TID;
5.顯示每位同學(xué)的CID1課程名及其講授了相關(guān)課程的老師名稱;
msyql> SELECT Name,Cname,Tname FROM students,courses,teachers WHERE students.CID1=courses.CID AND courses.CID=teachers.CID;
看了以上介紹SELECT單表、多表查詢升級(jí)及插入刪除,希望能給大家在實(shí)際運(yùn)用中帶來一定的幫助。本文由于篇幅有限,難免會(huì)有不足和需要補(bǔ)充的地方,大家可以繼續(xù)關(guān)注億速云行業(yè)資訊板塊,會(huì)定期給大家更新行業(yè)新聞和知識(shí),如有需要更加專業(yè)的解答,可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后,隨時(shí)幫您解答問題的。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。