泰興市住房和城鄉(xiāng)建設局網(wǎng)站,什么是seo標題優(yōu)化,衡水網(wǎng)站設計費用,sem競價代運營公司SQL#xff08;Structured Query Language#xff0c;結構化查詢語言#xff09;是操作關系型數(shù)據(jù)庫的核心工具#xff0c;無論是后端開發(fā)、數(shù)據(jù)分析、運維監(jiān)控#xff0c;還是大數(shù)據(jù)處理#xff0c;SQL都是不可或缺的技能。從簡單的“查詢數(shù)據(jù)”到復雜的“多表關聯(lián)分析”…SQLStructured Query Language結構化查詢語言是操作關系型數(shù)據(jù)庫的核心工具無論是后端開發(fā)、數(shù)據(jù)分析、運維監(jiān)控還是大數(shù)據(jù)處理SQL都是不可或缺的技能。從簡單的“查詢數(shù)據(jù)”到復雜的“多表關聯(lián)分析”SQL的語法體系既基礎又靈活。本文將從**基礎語法、進階語法、常用函數(shù)、性能優(yōu)化小貼士**四個維度結合實戰(zhàn)案例詳細講解常見SQL語法覆蓋MySQL、Oracle、SQL Server等主流數(shù)據(jù)庫的通用用法幫你快速掌握SQL的核心應用。一、先搭個基礎示例表結構為了讓語法示例更易理解我們先定義三個常用的示例表后續(xù)案例均基于這三張表1. 學生表student字段名類型說明student_idINT學生ID主鍵student_nameVARCHAR(50)學生姓名ageINT年齡genderVARCHAR(10)性別class_idINT班級ID外鍵2. 班級表class字段名類型說明class_idINT班級ID主鍵class_nameVARCHAR(50)班級名稱teacherVARCHAR(50)班主任3. 成績表score字段名類型說明score_idINT成績ID主鍵student_idINT學生ID外鍵course_nameVARCHAR(50)課程名稱scoreDECIMAL(5,2)分數(shù)二、SQL基礎語法數(shù)據(jù)庫與表的核心操作SQL基礎語法主要分為**DDL數(shù)據(jù)定義語言******和******DML數(shù)據(jù)操作語言**前者負責定義數(shù)據(jù)庫/表結構后者負責操作數(shù)據(jù)。1. DDL數(shù)據(jù)庫與表的定義創(chuàng)建/修改/刪除1數(shù)據(jù)庫操作-- 1. 創(chuàng)建數(shù)據(jù)庫指定字符集避免中文亂碼 CREATE DATABASE IF NOT EXISTS school_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 2. 使用數(shù)據(jù)庫 USE school_db; -- 3. 刪除數(shù)據(jù)庫謹慎使用 DROP DATABASE IF EXISTS school_db;2表操作-- 1. 創(chuàng)建學生表含主鍵、數(shù)據(jù)類型、注釋 CREATE TABLE IF NOT EXISTS student ( student_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 學生ID, student_name VARCHAR(50) NOT NULL COMMENT 學生姓名, age INT COMMENT 年齡, gender VARCHAR(10) COMMENT 性別, class_id INT COMMENT 班級ID, -- 外鍵關聯(lián)班級表可選視業(yè)務場景而定 FOREIGN KEY (class_id) REFERENCES class(class_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT學生表; -- 2. 修改表結構添加字段、修改字段、刪除字段 -- 添加字段新增手機號字段 ALTER TABLE student ADD COLUMN phone VARCHAR(20) COMMENT 手機號 AFTER age; -- 修改字段修改age字段類型為TINYINT ALTER TABLE student MODIFY COLUMN age TINYINT COMMENT 年齡; -- 刪除字段刪除手機號字段 ALTER TABLE student DROP COLUMN phone; -- 3. 刪除表謹慎使用 DROP TABLE IF EXISTS student; -- 4. 清空表數(shù)據(jù)保留表結構自增主鍵重置 TRUNCATE TABLE student;2. DML數(shù)據(jù)的增刪改查核心中的核心1插入數(shù)據(jù)INSERT-- 1. 插入單條數(shù)據(jù)指定字段 INSERT INTO student (student_name, age, gender, class_id) VALUES (張三, 18, 男, 1); -- 2. 插入多條數(shù)據(jù)批量插入效率更高 INSERT INTO student (student_name, age, gender, class_id) VALUES (李四, 19, 男, 1), (王五, 18, 女, 2), (趙六, 20, 男, 2); -- 3. 從其他表插入數(shù)據(jù)將查詢結果插入表中 INSERT INTO student_backup (student_id, student_name, age) SELECT student_id, student_name, age FROM student WHERE gender 女;2查詢數(shù)據(jù)SELECTSELECT是SQL中使用最頻繁的語法基礎用法如下-- 1. 查詢所有字段不推薦性能差僅測試用 SELECT * FROM student; -- 2. 查詢指定字段 SELECT student_id, student_name, age FROM student; -- 3. 帶條件查詢WHERE子句 SELECT student_name, age FROM student WHERE gender 男 AND age 18; -- 4. 去重查詢DISTINCT SELECT DISTINCT class_id FROM student; -- 5. 別名AS可省略給字段/表起別名簡化代碼 SELECT s.student_id AS id, s.student_name AS name FROM student s;3更新數(shù)據(jù)UPDATE-- 修改單條數(shù)據(jù)必須加WHERE條件否則修改全表 UPDATE student SET age 20, class_id 3 WHERE student_id 1; -- 批量修改數(shù)據(jù) UPDATE student SET class_id 2 WHERE gender 女;4刪除數(shù)據(jù)DELETE-- 刪除單條數(shù)據(jù)必須加WHERE條件否則刪除全表 DELETE FROM student WHERE student_id 1; -- 批量刪除數(shù)據(jù) DELETE FROM student WHERE class_id 3;三、SQL進階語法解鎖復雜數(shù)據(jù)操作基礎語法只能處理單表數(shù)據(jù)而實際業(yè)務中往往需要多表關聯(lián)、數(shù)據(jù)聚合、分頁等復雜操作這就需要用到進階語法。1. 聯(lián)表查詢多表數(shù)據(jù)關聯(lián)JOIN聯(lián)表查詢是進階語法的核心主要分為**內連接、左連接、右連接、全連接**四種其中內連接和左連接最常用。1內連接INNER JOIN只返回兩表匹配的數(shù)據(jù)-- 查詢學生姓名及其所屬班級名稱僅顯示有班級的學生 SELECT s.student_name, c.class_name FROM student s INNER JOIN class c ON s.class_id c.class_id;2左連接LEFT JOIN返回左表所有數(shù)據(jù)右表匹配不到則為NULL-- 查詢所有學生姓名及其所屬班級名稱無班級的學生也顯示班級名稱為NULL SELECT s.student_name, c.class_name FROM student s LEFT JOIN class c ON s.class_id c.class_id;3右連接RIGHT JOIN返回右表所有數(shù)據(jù)左表匹配不到則為NULL-- 查詢所有班級名稱及其對應的學生姓名無學生的班級也顯示學生姓名為NULL SELECT s.student_name, c.class_name FROM student s RIGHT JOIN class c ON s.class_id c.class_id;4全連接FULL JOIN返回兩表所有數(shù)據(jù)匹配不到則為NULL注意MySQL不支持直接使用FULL JOIN可通過UNION組合左連接和右連接實現(xiàn)Oracle、SQL Server支持FULL JOIN。-- MySQL實現(xiàn)全連接 SELECT s.student_name, c.class_name FROM student s LEFT JOIN class c ON s.class_id c.class_id UNION SELECT s.student_name, c.class_name FROM student s RIGHT JOIN class c ON s.class_id c.class_id;2. 子查詢嵌套在主查詢中的查詢語句子查詢是將一個查詢結果作為另一個查詢的條件或數(shù)據(jù)源分為**標量子查詢、列子查詢、表子查詢**。1標量子查詢返回單個值一行一列-- 查詢與張三同班級的學生先查張三的班級ID再查該班級的學生 SELECT student_name FROM student WHERE class_id (SELECT class_id FROM student WHERE student_name 張三);2列子查詢返回一列數(shù)據(jù)多行一列使用IN/ANY/ALL-- 查詢班級1或班級2的學生返回多個class_id SELECT student_name FROM student WHERE class_id IN (SELECT class_id FROM class WHERE class_name IN (一班, 二班));3表子查詢返回多行多列作為臨時表-- 查詢成績大于80分的學生姓名先查成績表的學生ID再關聯(lián)學生表 SELECT s.student_name FROM student s INNER JOIN (SELECT student_id FROM score WHERE score 80) sc ON s.student_id sc.student_id;3. 分組與聚合數(shù)據(jù)統(tǒng)計分析GROUP BY 聚合函數(shù)GROUP BY用于將數(shù)據(jù)按指定字段分組結合聚合函數(shù)如COUNT、SUM、AVG實現(xiàn)數(shù)據(jù)統(tǒng)計。-- 1. 統(tǒng)計每個班級的學生人數(shù) SELECT c.class_name, COUNT(s.student_id) AS student_count FROM class c LEFT JOIN student s ON c.class_id s.class_id GROUP BY c.class_name; -- 2. 統(tǒng)計每個學生的總成績 SELECT s.student_name, SUM(sc.score) AS total_score FROM student s LEFT JOIN score sc ON s.student_id sc.student_id GROUP BY s.student_name; -- 3. 分組后過濾HAVING統(tǒng)計學生人數(shù)大于5的班級 -- 注意HAVING用于分組后過濾WHERE用于分組前過濾 SELECT c.class_name, COUNT(s.student_id) AS student_count FROM class c LEFT JOIN student s ON c.class_id s.class_id GROUP BY c.class_name HAVING student_count 5;4. 排序與分頁控制數(shù)據(jù)展示順序和數(shù)量1排序ORDER BY默認升序ASC降序用DESC-- 按年齡降序、姓名升序排序 SELECT student_name, age FROM student ORDER BY age DESC, student_name ASC;2分頁不同數(shù)據(jù)庫語法不同是業(yè)務中必用的語法如列表頁展示-- MySQLLIMIT 偏移量, 每頁條數(shù)偏移量從0開始 -- 示例查詢第2頁數(shù)據(jù)每頁10條 SELECT student_name, age FROM student ORDER BY student_id LIMIT 10, 10; -- OracleROWNUM 子查詢 SELECT * FROM ( SELECT s.*, ROWNUM rn FROM student s ORDER BY student_id ) WHERE rn BETWEEN 11 AND 20; -- SQL ServerOFFSET FETCH SELECT student_name, age FROM student ORDER BY student_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;四、SQL常用函數(shù)提升數(shù)據(jù)處理效率SQL提供了豐富的內置函數(shù)涵蓋字符串、數(shù)值、日期、聚合等類型以下是最常用的函數(shù)示例。1. 字符串函數(shù)-- 1. 長度LENGTHMySQL/ LENSQL Server SELECT LENGTH(student_name) AS name_length FROM student; -- 2. 拼接CONCATMySQL/ SQL Server/ ||Oracle SELECT CONCAT(student_name, -, gender) AS name_gender FROM student; -- 3. 大小寫轉換UPPER大寫/ LOWER小寫 SELECT UPPER(student_name) AS upper_name FROM student; -- 4. 截取字符串SUBSTRING通用 SELECT SUBSTRING(student_name, 1, 1) AS first_char FROM student; -- 截取第一個字符2. 數(shù)值函數(shù)-- 1. 四舍五入ROUND SELECT ROUND(score, 1) AS round_score FROM score; -- 保留1位小數(shù) -- 2. 取整CEIL向上取整/ FLOOR向下取整 SELECT CEIL(score) AS ceil_score, FLOOR(score) AS floor_score FROM score; -- 3. 求和/平均值/最大值/最小值SUM/AVG/MAX/MIN聚合函數(shù) SELECT SUM(score) AS total, AVG(score) AS avg, MAX(score) AS max, MIN(score) AS min FROM score;3. 日期函數(shù)日期函數(shù)是處理時間數(shù)據(jù)的核心不同數(shù)據(jù)庫語法略有差異以下是MySQL的常用示例-- 1. 獲取當前時間NOW()日期時間/ CURDATE()僅日期/ CURTIME()僅時間 SELECT NOW(), CURDATE(), CURTIME(); -- 2. 日期格式化DATE_FORMAT SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s) AS format_time; -- 3. 日期加減DATE_ADD/DATE_SUB SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week; -- 7天后 SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH) AS last_month; -- 1個月前 -- 4. 提取日期部分YEAR/MONTH/DAY/HOUR SELECT YEAR(NOW()) AS year, MONTH(NOW()) AS month, DAY(NOW()) AS day;五、SQL語法性能優(yōu)化小貼士掌握語法的同時也要注意性能優(yōu)化避免寫出“慢SQL”。以下是幾個實用的優(yōu)化技巧1. 避免使用SELECT *只查詢需要的字段SELECT *會查詢所有字段不僅增加網(wǎng)絡傳輸量還會導致無法使用覆蓋索引嚴重影響性能。2. 給查詢條件字段加索引索引是提升查詢速度的關鍵給WHERE、JOIN、ORDER BY等子句中的字段添加索引如主鍵索引、普通索引、聯(lián)合索引。-- 給student表的class_id字段添加普通索引 CREATE INDEX idx_student_class_id ON student(class_id);3. 盡量使用WHERE代替HAVINGWHERE在分組前過濾數(shù)據(jù)HAVING在分組后過濾數(shù)據(jù)使用WHERE可以減少分組的數(shù)據(jù)量提升性能。4. 批量操作數(shù)據(jù)批量插入/更新數(shù)據(jù)比單條操作效率高得多盡量使用INSERT INTO ... VALUES (...), (...)的批量語法。5. 避免在WHERE子句中使用函數(shù)或運算在WHERE子句中對字段使用函數(shù)或運算會導致索引失效例如-- 錯誤索引失效 SELECT * FROM student WHERE YEAR(birthday) 2000; -- 正確改寫為范圍查詢使用索引 SELECT * FROM student WHERE birthday 2000-01-01 AND birthday 2000-12-31;六、實戰(zhàn)案例綜合運用SQL語法最后我們通過一個綜合案例將上述語法串聯(lián)起來加深理解需求查詢每個班級的“數(shù)學”課程平均分且平均分大于80分按平均分降序排列SELECT c.class_name, AVG(sc.score) AS avg_math_score FROM class c LEFT JOIN student s ON c.class_id s.class_id LEFT JOIN score sc ON s.student_id sc.student_id WHERE sc.course_name 數(shù)學 GROUP BY c.class_name HAVING avg_math_score 80 ORDER BY avg_math_score DESC;七、總結SQL的語法體系看似繁雜但核心邏輯是**“數(shù)據(jù)定義→數(shù)據(jù)操作→數(shù)據(jù)統(tǒng)計”**。對于初學者先掌握基礎的增刪改查和聯(lián)表查詢再逐步學習聚合函數(shù)、子查詢等進階語法對于資深開發(fā)者重點在于結合業(yè)務場景優(yōu)化SQL性能寫出高效、可維護的代碼。記住SQL是一門“熟能生巧”的技能多寫、多練、多分析慢查詢才能真正掌握它。希望本文的語法解析和實戰(zhàn)案例能幫你夯實SQL基礎應對日常開發(fā)和數(shù)據(jù)分析的需求。