Day SQL查詢 - 簡書

一宾尚、SQL語法補充

1.條件語句的寫法

在SQL中可以通過 where 條件語句來對操作對象進行篩選 - 篩選

a. 比較運算符： =, <>, >, <, >=, <=

注意: 判斷一個字段的值是否為空不用使用=和<>, 而是使用is null 和 is not null

SELECT addr FROM t_student;
SELECT stuname FROM t_student WHERE addr is NULL;    -- 判斷是否為NULL（空）
SELECT stuname FROM t_student WHERE addr='';     -- 判斷是否是空串

b. 邏輯運算符: and, or, not

c. where 字段名 between 值1 and 值2

篩選指定的字段的值在值1和值2之間

SELECT stuname,birth FROM t_student WHERE birth BETWEEN '1990-1-1' AND '1999-12-31';

d. where 字段名 in 集合

篩選出字段值是集合中的元素;(集合是使用括號括起來里面多個值)

SELECT * FROM t_student WHERE stuname in ('小花', '小明', '路飛');

e. like操作

SELECT * FROM t_student WHERE stuname like ('王%%'); -- 選出姓王的

2.數(shù)據(jù)類型

varchar(size): 不定長字符串俯在，size決定的是最大長度
char(size): 定長字符
text: 不限長度(最大是255個字符)
int/tinyint(-128~127)
FLOAT(size,d)/DOUBLE(size,d) - 這兒的size和d的值都有約束效果
bit: 只有0和1兩個值
date/datetime/time: 值可以是時間函數(shù)的結(jié)果产园，也可以時間字符串炕贵；計算或者是比較的時候內(nèi)部是按時間處理的

3.去重

SELECT DISTINCT addr FROM t_student;

二、添加約束

1.創(chuàng)建表的時候添加約束

CREATE TABLE if not EXISTS t_college(
collid int, 
collname VARCHAR(20) NOT NULL,   -- 創(chuàng)建表的時候添加約束
website VARCHAR(1024), 
intro VARCHAR(200),
PRIMARY KEY(collid)
);

2.通過添加約束索引的方式添加約束

a.添加約束

alter table 表名 add constraint 索引名約束 (字段名);
說明: 索引名 - 自己隨便命名侦副，用來指向當前添加的約束豆村；約束 - 需要添加的約束(支持唯一約束、主鍵約束和外鍵約束)

ALTER TABLE t_college ADD CONSTRAINT con_website UNIQUE (website);

給t_college表中的website添加unique約束脸秽，約束索引為con_website

b.刪除約束

alter table 表名 drop index 約束索引;

ALTER TABLE t_college DROP index con_website;

三儒老、外鍵和E.R圖

1.什么是外鍵：

表中的某個字段的值是根據(jù)其他表中主鍵的值來確定的。那么這個字段就是外鍵

1.1 對應(yīng)關(guān)系

多對一的外鍵的添加：將外鍵添加到多的一方對應(yīng)的表中

一對一的外鍵的添加：將外鍵隨便添加到哪一方记餐，同時添加值唯一約束
多對多的外鍵的添加：關(guān)系型數(shù)據(jù)庫中驮樊，兩張表沒法實現(xiàn)多多的關(guān)系，需要一個中間表。(中間表有兩個外鍵分別參照多多的兩個表的主鍵)

1.2 怎么添加外鍵：

a.添加外鍵對應(yīng)的字段

alter TABLE tb_student add COLUMN collid int;

b.給設(shè)計好的外鍵對應(yīng)的字段添加外鍵約束
alter table 表1 add constraint 索引名 foreign key (字段1) references 表2 (字段2);
將表1中的字段1設(shè)置為外鍵囚衔，并且讓這個外鍵的值參照表2中的字段2

ALTER TABLE tb_student ADD CONSTRAINT fk_collid_stu 
FOREIGN KEY (collid) 
REFERENCES  tb_college (collid);

c.刪除外鍵約束
alter table 表名 drop foreign key 外鍵索引名; - 可以刪除外鍵約束挖腰，但是外鍵索引還存在；需要額外的把索引刪掉
注意: 刪除外鍵約束的時候直接刪除約束的索引無效练湿，必須先將約束刪掉猴仑，然后再刪除索引.

ALTER TABLE tb_student DROP FOREIGN KEY fk_collid_stu;
ALTER TABLE tb_student DROP INDEX fk_collid_stu;

d. 多對多的外鍵約束

多對多的時候需要創(chuàng)建中間表分別來關(guān)聯(lián)兩個兩個表。

-- 添加學生的外鍵約束 
ALTER TABLE tb_score ADD CONSTRAINT fk_stuid_score 
FOREIGN KEY (stuid) 
REFERENCES tb_student (stuid);
-- 添加課程的外鍵約束
ALTER TABLE tb_score ADD CONSTRAINT fk_couid_score 
FOREIGN KEY (couid) 
REFERENCES tb_course (couid);

四肥哎、高級查詢操作

1.聚合：

max()/min()/sum()/avg()/count()
SELECT 聚合函數(shù)(字段) FROM 表名 WHERE 條件;

按條件多表查詢指定字段數(shù)據(jù)辽俗，然后將查詢結(jié)果做相應(yīng)的聚合運算。聚合運算的結(jié)果是最后結(jié)果

SELECT mark FROM tb_score;    -- 獲取tb_score表中的所有分數(shù)值
SELECT max(mark) as maxmark FROM tb_score;      -- 獲取tb_score表中所有分數(shù)的最大值
SELECT min(mark) FROM tb_score; 
SELECT sum(mark) FROM tb_score; 
SELECT avg(mark) FROM tb_score WHERE scoreid>=123;   -- 計算平均值的時候如果參與運算的對象的值為NULL,那么這個數(shù)據(jù)不會參與計算
SELECT count(mark) FROM tb_score WHERE mark>=90;

2.分組

SELECT 字段操作 FROM 表名 WHERE 條件 GROUP BY(字段2);
將指定表中滿足條件的記錄按照字段2的進行分組(值是一樣的在一個組里面), 然后再講每個分組作為整體按照指定字段進行指定聚合操作

-- 求每個學生的平均成績
SELECT stuid,avg(mark) FROM tb_score WHERE scoreid <= 115 GROUP BY(stuid) ;

注意:a.字段操作的位置除了分組字段不用聚合篡诽，其他字段都必須聚合 b.分組的時候where要放到分組前對需要分組的數(shù)據(jù)進行篩選

having: 分組的時候崖飘，在分組后用having代替where來對分組后的數(shù)據(jù)進行篩選

-- 獲取平均分數(shù)大于60分的學生的id
SELECT stuid,avg(mark) FROM tb_score GROUP BY(stuid) HAVING avg(mark)>60;

3.子查詢:

將一個查詢操作的結(jié)果作為另外一個查詢的數(shù)據(jù)源

-- 在tb_score表中獲取成績是大于90分的學生的id
SELECT stuid FROM tb_score WHERE mark>90 and stuid is not NULL;

獲取成績大于90分的學生的名字

SELECT stuname FROM tb_student WHERE stuid in 
(SELECT stuid FROM tb_score WHERE mark>90 and stuid is not NULL);  
SELECT stuid,mark FROM tb_score WHERE mark > 90 and stuid is  not NULL;

將一個查詢的結(jié)果作為查詢對象提供給另外一個查詢。但是第一個查詢結(jié)果需要重命名

SELECT mark FROM (SELECT stuid,mark FROM tb_score WHERE mark > 90 and stuid is  not NULL) as t2;