Sql語句基礎要點總結

一、新增

1. 向stu表中新增一條學生資訊

INSERT INTO stu VALUES(1008,'zhangsna12',12,'jining','123232465');

2. 另一種方式

INSERT INTO stu(stuid,name,age,tel,address) VALUES(1002,'lisi',13,'31315313','jining');-- 建議用這種

INSERT INTO stu(name,age,tel,address) VALUES('1',13,'31315313','jining'),-- 建議用這種
							                ('2',13,'31315313','jining');

二、修改

SELECT * from stu;

一定要加where條件,修改多個列用逗號隔開

UPDATE stu SET name = '李文英',address='山東' WHERE stuid=1001;

三、刪除

DELETE FROM stu WHERE stuid=1008;

四、單表查詢

1.簡單查詢

-- 1.查詢所有學生資訊
SELECT *FROM student;
-- 1.查詢學生資訊，顯示學生姓名，電話，性別
SELECT name,phone,sex FROM student;-- name為關鍵字，所以要加引號區分
-- 3.查詢哪些班級有學生，顯示班級編號（去重:DISTINCT）
SELECT DISTINCT cid FROM student;
-- 4.使用算術運運算元，as關鍵字用於重新命名，可加可不加
SELECT socre as 加分前的成績, socre+10 加分後的成績 FROM score;

練習1

-- 1.查詢所有員工資訊
SELECT *FROM employee;
-- 1.查詢所有員工的姓名及對應工資
SELECT ename,salary FROM employee;
-- 3.過濾員工表中的重複資料
SELECT DISTINCT *FROM employee;
-- 4.查詢出每位員工的工資，並加一
SELECT salary , salary + 1 FROM employee;
-- 5.使用別名表示姓名，職位，電話，工資
SELECT ename 姓名,phone 電話,salary 工資 FROM employee;

2.WHERE

-- 1.查詢學號為4的學生資訊
SELECT * FROM student WHERE stuid=4;
-- 2.查詢考試成績滿足給定條件的學生學號，分數
SELECT stuid,socre FROM score WHERE socre>=60 AND socre<=80;
SELECT stuid,socre FROM score WHERE socre BETWEEN 60 AND 80;
SELECT stuid,socre FROM score WHERE socre=69 OR socre=79;
SELECT stuid,socre FROM score WHERE socre in(69,79);

3.NOT 非

% 代表多個字元

SELECT stuid,socre FROM score WHERE socre NOT BETWEEN 60 AND 80;

4.LIKE 像

SELECT * FROM student WHERE name LIKE '張%';
SELECT * FROM student WHERE name LIKE '%三';
SELECT * FROM student WHERE name LIKE '%m%';

_代表單個字元

SELECT * FROM student WHERE name LIKE '張_';
SELECT * FROM student WHERE name LIKE '張__';

5.is NULL

SELECT * FROM score WHERE socre IS NULL;
SELECT * FROM score WHERE socre IS NOT NULL;

練習2

SELECT * FROM employee WHERE salary>=5000;
SELECT * FROM employee WHERE salary>=3000 AND salary<=5000;
SELECT * FROM employee WHERE salary in (5000,3000,8000);
SELECT * FROM employee WHERE hiredate BETWEEN '2016-02-01' AND '2016-03-01';
SELECT * FROM employee WHERE manager is NULL;
SELECT ename,job,phone FROM employee WHERE ename LIKE '王%';

6. 聚合函數

1. avg()：求平均值

-- 求 score 成績表中科目編號為1的所有學生平均成績
SELECT AVG(socre) 平均成績 FROM score WHERE subid=1;

2. COUNT():求多少條記錄

-- 用*表示所有列
SELECT COUNT(*) FROM score WHERE subid=1;

3. SUM():求和

-- 求score成績表中所有學生成績的總和
SELECT SUM(socre) FROM score;

4. MAX()和 MIN():求最值

SELECT MAX(socre) 最高分,MIN(socre)最低分 FROM score WHERE subid=2;

求已出成績的各科的平均成績

不使用分組

SELECT DISTINCT subid FROM score;
SELECT AVG(socre) FROM score WHERE subid=1;
SELECT AVG(socre) FROM score WHERE subid=2;
SELECT AVG(socre) FROM score WHERE subid=3;

5. GROUP BY語句

使用分組(含有 每， 各，或帶有聚合函數的情況)

-- mysql中可單獨使用，分組後的列可以不出現在select中
SELECT AVG(socre) FROM score GROUP BY subid;
-- SQLServer中不可單獨使用，需要結合having語句，而且分組後的列必須出現在select中
SELECT AVG(socre),subid FROM score GROUP BY subid;

查詢出所有成績加5分後，成績還是小於60分

HAVING針對查詢結果

SELECT stuid,socre+5 加分後的成績 FROM score HAVING 加分後的成績 <60;

WHERE只能針對表中真正存在的列進行篩選

SELECT stuid, socre FROM score WHERE socre <60;
-- 平均分大於70的科目
SELECT subid,AVG(socre) FROM score GROUP BY subid HAVING AVG(socre)>70;

7. 排序

1. ORDER BY語句

• asc 升序 (預設)
• desc 降序

SELECT * FROM student;
SELECT * FROM student ORDER BY cid,birthday ASC;
SELECT * FROM student ORDER BY phone DESC;

2. LIMIT 語句，用於分頁
   LIMIT 資料的座標，從0開始，顯示的條數

SELECT * FROM student ORDER BY birthday ASC LIMIT 0,5;

練習3

SELECT * FROM employee ORDER BY salary ASC LIMIT 0,3;
SELECT * FROM employee ORDER BY did DESC, hiredate ASC, salary DESC;
SELECT * FROM employee ORDER BY salary DESC LIMIT 2,3;

五、多表查詢

交叉連線

SELECT * FROM student, classinfo;

1.等值連結

-- 查詢李四所在班級，顯示李四所在班級編號，姓名，班級名
-- 1.=連結兩個表
SELECT student.cid,name,cname FROM student, classinfo
-- 2.確定連結條件
where student.cid=classinfo.cid
-- 3.確定其他篩選條件
and name= '李四';

-- 查詢張三c++考試成績，顯示學生姓名，班級名，學號，成績，科目名
SELECT `name`,cname,student.stuid,socre,subname
FROM student, classinfo, `subject`,score
WHERE student.cid=classinfo.cid
AND `subject`.subid=score.subid
AND student.stuid=score.stuid
AND student.`name`= '張三'
AND subname = 'c++';

2.內連線

-- 查詢李四所考科目的平均成績，顯示姓名，平均成績
-- 1.把兩個表連成大表
SELECT `name`,AVG(socre) FROM student INNER JOIN score
-- 2.連結條件
ON student.stuid=score.stuid
-- 3.篩選條件
WHERE `name`='李四';

-- 查詢張三c++考試成績，顯示學生姓名，班級名，學號，成績，科目名
SELECT `name`,cname,student.stuid,socre,subname
FROM student INNER JOIN classinfo
ON student.cid=classinfo.cid
INNER JOIN score
ON student.stuid=score.stuid
INNER JOIN `subject`
ON `subject`.subid=score.subid
WHERE student.`name`= '張三' AND subname = 'c++';

3.外連線

1.左外連線:（推薦使用左外連線替代右外連線）以左表為主，在右表找匹配資料，沒有就用null填充，左表資料必須被查詢出來

而內連線和等值連結是左右兩個表的交集，如果匹配成功則查詢出來，否則不顯示

-- 查詢所有學生課程的考試成績，
-- 查詢結果保留學生ID、姓名、性別、課程ID、成績
SELECT student.stuid,`name`,sex,subid,socre FROM student LEFT JOIN score
ON student.stuid=score.stuid;

2.右外連線：以右表為主，在左表找匹配資料，沒有就用null填充，左表資料必須被查詢出來

-- 而內連線和等值連結是左右兩個表的交集，如果匹配成功則查詢出來，否則不顯示
SELECT student.stuid,`name`,sex,subid,socre FROM student RIGHT JOIN score
ON student.stuid=score.stuid;