SQL基礎語句（一看就會版）

DDL Data Definition Language

數據定義語言摆屯，在定義或改變表結構慨菱、數據類型胀葱、表之間的鏈接等等使用注服，一般使用視圖工具創(chuàng)建導出

-- 關鍵字大寫
-- 創(chuàng)建表 如果表明是關鍵字要加反引號

CREATE TABLE `student`
--  PRIMARY KEY AUTO_INCREMENT 創(chuàng)建時設定主鍵 
(
id INT(11) NOT NULL,
name VARCHAR(64) NOT NULL,
age INT(11) DEFAULT NULL,
city VARCHAR(64) DEFAULT '北京'
)
SELECT * FROM student;

CREATE TABLE score
(
student_id INT(11) NOT NULL,
grade FLOAT NOT NULL
)

-- 增加/修改/刪除字段（增加列）
ALTER TABLE student ADD COLUMN idcard VARCHAR(64) NULL; 
ALTER TABLE student MODIFY city VARCHAR(128) DEFAULT '上海';
ALTER TABLE student DROP idcard;

-- 如何增加約束
-- 為表增加主鍵
ALTER TABLE student ADD PRIMARY KEY (id);

-- 增加唯一約束
ALTER TABLE student ADD UNIQUE INDEX uq_student_idcard(idcard);

-- 增加默認約束
ALTER TABLE student MODIFY city VARCHAR(64) DEFAULT '山西';

-- 主外鍵
ALTER TABLE score ADD constraint fk_score_student_id FOREIGN KEY (student_id) REFERENCES studb.student(id);

DML Data Manipulate Language

數據庫操縱語言好啰，要熟練手寫

-- 插入數據
-- 1.每次插入一行數據浪秘，不能只插入一部分數據滞项，插入的數據是否有效將按照整行的完整性來要求檢驗
-- 2.每個數據值的數據類型君丁、精度筹麸、位數必須與要應列明精確匹配
-- 3.不能為標識符指定值
-- 4.如果某字段設置為不能為空活合，則必須插入數據
-- 5.插入數據是必須符合檢查性約束的要求
-- 6.有缺省值的列，可以使用DEFAULT關鍵字來代替實際插入的值  
INSERT INTO student (name,age,city,idcard)
VALUES ('張三',18,'北京','100');
SELECT * from student;

-- 更新數據
-- 可以一次更新多列物赶，用逗號隔開
-- 可以指定更新的條件白指，如果有多個條件可以用AND OR NOT來修飾
-- 如果行的數據被更新過了，則不再受影響
UPDATE student SET age=28,city='上海' WHERE id=7 AND name='張三';

-- 刪除
-- 刪除是整行刪除酵紫，不需要提供列明
-- 如果要刪除的表是主表的話告嘲，要先刪除子表
DELETE FROM student WHERE id=7;

-- TRUNCATE 截斷 清空表
-- TRUNCATE 會重置標識種子（清空斷號保留）DELETE會保留斷號（id）
-- TRUNCATE不會寫入日志错维，DELETE會寫入日志，可以恢復
TRUNCATE TABLE student;

DELETE from student;


-- 查詢
-- 查詢在北京的同學信息id和name 并且按照ID正序排序ASC;(降序DESC)
SELECT id,name FROM student WHERE city='北京' ORDER BY id ASC;

-- 別名 列明+AS+別名 AS可以省略
SELECT id,name,city AS home FROM student WHERE city='北京' ORDER BY id DESC;

-- 查詢空列 IS NULL不能用=
SELECT * FROM student WHERE level IS NULL;

-- 常量列 (固定沒有的列)
SELECT id,name,'中國' AS country FROM student WHERE city='北京' ORDER BY id ASC;

-- 分頁 LIMIT 索引(index),條數橄唬；如果只寫一個值赋焕，代表查詢幾條
SELECT * from student
LIMIT 2,3

-- 查詢同學都來自哪些不同的城市 DISTINCT 相同的只出現一次
SELECT DISTINCT city
FROM student;

-- 加法 +只能用來+數字  字符串相連 CONCAT(str1,str2,...)
SELECT 1+1;
SELECT 1+'1';
SELECT 1+'a';
SELECT 'a'+'b';
SELECT CONCAT('a','b');

準備數據

ALTER TABLE `student`
ADD COLUMN `province`  varchar(50) NULL AFTER `city`,
ADD COLUMN `birthday`  date NULL AFTER `province`,
ADD COLUMN `gender`   int(11) NULL AFTER `birthday`,
ADD COLUMN `email`  varchar(50) NULL AFTER `gender`;

INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('1', '郭靖', '1', '1', '濟南', '山東省', '1982-09-03', 1, '1@qq.com');
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('2', '黃蓉', '2', '2', '濟南', '山東省', '1982-09-03', 0, '2@qq.com');
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('3', '楊過', '3', '3', '終南山', '陜西省', '1979-09-03', 1, '3@qq.com');
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('4', '小龍女', '4', '4', '終南山', '陜西省', '1970-09-03', 0, '4@qq.com');
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('5', '歐陽鋒', '5', '5', '白駝山', '新疆', '1989-09-09', 1, '5@qq.com');

INSERT INTO `course` VALUES ('1', '語文');
INSERT INTO `course` VALUES ('2', '數學');
INSERT INTO `course` VALUES ('3', '英語');

INSERT INTO `score` VALUES ('1', '1', '100');
INSERT INTO `score` VALUES ('1', '2', '90');
INSERT INTO `score` VALUES ('1', '3', '70');
INSERT INTO `score` VALUES ('2', '1', '100');
INSERT INTO `score` VALUES ('2', '2', '90');
INSERT INTO `score` VALUES ('2', '3', '80');
INSERT INTO `score` VALUES ('3', '1', '100');
INSERT INTO `score` VALUES ('3', '2', '90');
INSERT INTO `score` VALUES ('3', '3', '80');

-- 查詢 
-- 查詢所有山東省的同學
SELECT * FROM student WHERE province='山東省';

-- 查詢所有山東省的男同學
SELECT * FROM student WHERE province='山東省' AND gender=1;

-- 查詢沒有填寫郵箱的同學姓名和身份證號
SELECT name,idcard FROM student WHERE email IS NULL;

-- 把學生的成績升序排列
SELECT * FROM score ORDER BY grade ASC;
-- 多列排序
-- 課程編號升序 成績降序
SELECT * FROM score ORDER BY course_id ASC,grade DESC;

-- 模糊查詢 LIKE 
-- %放前面代表不管前面有幾個，以楊結尾仰楚，放后面代表不管后面有幾個以楊開頭
-- _代表一個字符
SELECT * FROM student WHERE `name` LIKE '楊%' OR `name` LIKE '郭_';
SELECT * FROM student WHERE `name` LIKE '楊_';

字符串函數

-- 字符串函數
-- CONCAT(str1,str2,...)
-- CONCAT_WS(separator,str1,str2,...) 第一個是分隔符,相當于js中join('-')
SELECT CONCAT(province,city) FROM `student`;
SELECT CONCAT_WS('-',province,city) FROM `student`;

-- FORMAT(X,D) 數字格式化
-- FORMAT(X,D) 數字格式化 保留兩位小數隆判，前面位數用逗號隔開
SELECT FORMAT(1000000,2);

-- LOWER(str) 轉小寫字母
SELECT LOWER('Yang');

-- UPPER(str) 轉大寫字母
SELECT UPPER('Yang');

-- LEFT(str,len) 取左邊開始的len個字符
SELECT LEFT('YangLei',4);
-- RIGHT(str,len) 反之
SELECT RIGHT('YangLei',3);


-- LENGTH(str) 去字符長度
SELECT LENGTH('YangLei');

-- SUBSTR 相當于js中substring
-- 1.SUBSTR(str FROM pos FOR len)從第幾位開始截取幾個
SELECT SUBSTR('YangLei' FROM 5 FOR 3);
-- 2.SUBSTR(str FROM pos)從第幾位開始截取到末尾
SELECT SUBSTR('YangLei' FROM 5);
-- 3. SUBSTR(str,pos) 從第幾位開始截取
SELECT SUBSTR('YangLei',4);
-- 4. SUBSTR(str,pos,len)從第幾位開始截取幾個
SELECT SUBSTR('YangLei',5,3);

-- indexof
SELECT INSTR('YangLei','Lei');

-- 姓名首字母大寫，其他字母小寫僧界，并且用_拼接
SELECT CONCAT_WS('_',UPPER(SUBSTRING('bOB',1,1)),LOWER(SUBSTR('bOB',2)));

-- 如何去除空格
SELECT TRIM('   bob   ');
SELECT LTRIM('   bob   ');
SELECT RTRIM('   bob   ');

-- padding補齊位數
SELECT LPAD(1,2,0);
SELECT RPAD(11,2,0);

-- 替換
SELECT REPLACE('bob','b','o'),REPLACE('bob','o','b');

數學函數

-- 數學函數
-- CEIL(X) 向上取整
SELECT CEIL(12.23123);
-- FLOOR(X) 向下取整
SELECT FLOOR(2.333);
-- MOD 取模
SELECT MOD(5,2);
-- POWER(X,Y) 冪運算
SELECT POWER(2,8);
-- ROUND(X) 四舍五入
SELECT ROUND(1.4);
SELECT ROUND(1.4454,2);
-- `TRUNCATE`(X,D) 數字截取
SELECT TRUNCATE(1.666,1);

日期函數

-- 日期函數
-- NOW() 當前日期和時間
SELECT NOW();
-- CURDATE()當前日期
SELECT CURDATE();
-- CURTIME()當前時間
SELECT CURTIME();
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DATE(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 字符串轉為日期
SELECT STR_TO_DATE('05-01-2020','%m-%d-%y');
-- DATE_ADD(date,INTERVAL expr unit) 日期的變化
SELECT DATE_ADD(NOW(),INTERVAL 365 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR);

-- DATEDIFF(expr1,expr2) 計算時間差
SELECT DATEDIFF('2019-1-1',NOW());

-- DATE_FORMAT(date,format) 日期格式化
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')

其他函數

-- 其他函數
SELECT CONNECTION_ID();
SELECT DATABASE();
SELECT VERSION();
select LAST_INSERT_ID();
SELECT USER();

SELECT MD5('123456');//摘要算法
SELECT PASSWORD('123456');//修改當前用戶的密碼
SELECT User,Password from mysql.user;

流程控制函數

-- if 如果0>1返回1侨嘀，否則返回0
SELECT IF(0>1,1,0);

-- CASE
CASE 要判斷的字段或表達式
WHEN 常量1 THEN 返回的值
WHEN 常量2 THEN 返回的值
ELSE 返回的值
END

SELECT
CASE grade
WHEN 100 THEN '滿分'
WHEN 90 THEN '良'
ELSE '一般'
END
FROM score ORDER BY course_id ASC;

SELECT
CASE 
WHEN grade>90 THEN '滿分'
WHEN grade>80 THEN '良'
ELSE '一般'
END
FROM score ORDER BY course_id ASC;

自定義函數

-- 自定義函數是對MySQL的擴展，使用方式和內置函數相同
-- 函數必須要有參數和返回值
-- 函數可以接收任意類型的值捂襟，也可以接收這些類型的參數
-- 函數體由合法的SQL語句組成
-- 函數體可以是簡單的SELECT語句或INSERT語句咬腕，如果是復合結構要用BEGIN...END
-- 函數體也可以包含聲明，循環(huán)和流程控制
-- 返回值只能有一個

-- 函數名要跟著()表示參數
CREATE FUNCTION ZNOW() RETURNS VARCHAR(128)
RETURN DATE_FORMAT(NOW(),'%Y年-%m月-%d日 %h時:%i分:%s秒');
SELECT ZNOW();

CREATE FUNCTION ZNOW2(date VARCHAR(128)) RETURNS VARCHAR(128)
RETURN DATE_FORMAT(date,'%Y年-%m月-%d日 %h時:%i分:%s秒');
SELECT ZNOW2('1994-01-30');

-- 函數體可能不止一行
CREATE FUNCTION ADD_USER(name VARCHAR(64)) RETURNS INT
BEGIN
INSERT INTO student (name) VALUES(name);
RETURN LAST_INSERT_ID();
END

模糊查詢

-- 通配符
-- 代替一個或多個真正的字符葬荷，與LIKE 關鍵字一起使用
-- _表示一個任意字符
-- %表示任意長度的字符串

-- BETWEEN AND
-- 查詢某一列在指定的規(guī)范內的記錄涨共，包括兩個邊界
SELECT * FROM score WHERE grade BETWEEN 80 AND 100;

-- in 
-- 查詢某一列中的值在列出的內容列表中
select * from student where city in ('北京','濟南','西安')

聚合函數
對一組值進行計算，并返回計算后的值宠漩，一般用來統(tǒng)計數據

-- SUM(expr)
-- 累加所有行的值
SELECT SUM(grade) FROM score;
SELECT SUM(grade) FROM score WHERE student_id=1;

-- AVG([DISTINCT] expr)
-- 計算所有行的平均值
SELECT AVG(grade) FROM score;

-- MAX举反、MIN
select MAX(grade) 最高分,MIN(grade) 最低分 from score where student_id = 1;

-- COUNT計算值 此列不為null的記錄數
-- * 就是所有列不為null記錄數
select COUNT(*) from student;
select COUNT(1) from student;
select COUNT(name) from student;
select COUNT(NULL) from student;

分組

-- 單列分組
-- 查詢每個學生的平均分
SELECT student_id,AVG(grade)
FROM score
GROUP BY student_id;

-- 統(tǒng)計每門課程的最高分，并按分數從高到低排序
SELECT course_id,MAX(grade)
FROM score
GROUP BY course_id
ORDER BY MAX(grade) DESC;

-- 多列分組
-- 統(tǒng)計各省男女的總人數
SELECT province,gender,COUNT(*)
FROM student
GROUP BY province,gender

-- 分組的篩選
-- 查看人數大于1的省份
SELECT province,COUNT(*)
FROM student
GROUP BY province
HAVING COUNT(*)>1;

-- 查詢所有分數小于90的次數>1次的同學
SELECT student_id,COUNT(*) 沒達到優(yōu)的次數 
FROM score
WHERE grade<90
GROUP BY student_id
HAVING COUNT(*)>1;

子查詢

  • 子查詢就是指出現在其它SQL語句中的SELECT語句,必須始終出現在圓括號中
  • 子查詢可以包含多個關鍵字或條件
  • 子查詢的外層查詢可以是: SELECT哄孤、INSERT照筑、UPDATE、SET等
  • 子查詢可以返回常量瘦陈、一行數據凝危、一列數據或其它子查詢
-- 查詢年齡大于平均年齡的學生
SELECT * FROM student
WHERE age > (SELECT AVG(age) FROM student);

-- ANY SOME ALL
-- ANY 任何一個
-- SOME 某些
-- ALL 全部
-- 年齡大于陜西省任何一個學生年齡的學生
SELECT * 
FROM student
WHERE age > ANY (SELECT age FROM student WHERE province='陜西省');
-- 年齡大于陜西省所有學生年齡的學生
SELECT * 
FROM student
WHERE age > ALL (SELECT age FROM student WHERE province='陜西省');

-- IN  NOT IN
-- 查詢有考試成績的學生
SELECT *
FROM student
WHERE id IN (SELECT student_id FROM score);

-- EXISTS  NOTEXISTS
SELECT *
FROM student
WHERE EXISTS (SELECT * FROM score WHERE score.student_id = student.id);

表連接

-- 內部連接
-- 查詢所有學生成績
SELECT * FROM student INNER JOIN score ON student.id=score.student_id;

-- 左外連接,基于內連接  如果左邊表沒有對應字段的值，會自動補null
SELECT * FROM student LEFT JOIN score ON student.id=score.student_id;

-- 右外連接 基于內連接
SELECT * FROM student RIGHT JOIN score ON student.id=score.student_id;

-- 外連接 mysql不支持
SELECT * FROM student OUTER JOIN score ON student.id=score.student_id;

-- 多表連接
-- 查看學生姓名晨逝，課程名蛾默，成績
SELECT student.`name`, score.grade, course.course
FROM student,score,course
WHERE student.id=score.student_id AND score.course_id=course.id;

SELECT student.name,score.grade,course.course
FROM student INNER JOIN score ON student.id=score.student_id
INNER JOIN course ON course.id=score.course_id;

-- 無限分類【自身連接】
-- 創(chuàng)建商品分類表 parent_id為0代表頂級分類
CREATE TABLE category(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
name VARCHAR(64),
parent_id INT
)

INSERT INTO category(id,name,parent_id)
VALUES (1,'數碼產品',0),(2,'服裝',0),(3,'食品',0),(4,'iPad',1),(5,'李寧',2),(6,'康師傅',3);

-- 查詢所有頂級分類下的類別數量 （處理方式：同一個表起別名c1 c2）
SELECT c1.id,c1.name,COUNT(*) 二級分類
FROM category c1 INNER JOIN category c2 ON c1.id=c2.parent_id
WHERE c1.parent_id = 0
GROUP BY c1.id;

-- 把所有父id變?yōu)槊Q
SELECT c1.id,c1.name,c2.name 父分類名稱 FROM
category c1 INNER JOIN category c2 ON c2.id=c1.parent_id;

-- 刪除重復記錄(面試常問)
-- 子查詢找要刪除的ID
SELECT * FROM category c1 LEFT JOIN
(SELECT id,`name` FROM category GROUP BY name HAVING COUNT(*)>1) c2
ON c1.`name`=c2.`name`
WHERE c1.id!=c2.id;
-- 通過IN找要刪除的ID
-- 用IN NOT IN實現
SELECT * FROM category c1
WHERE c1.name IN
(SELECT name FROM category GROUP BY name HAVING COUNT(*)>1)
AND c1.id NOT IN
(SELECT MIN(id) FROM category GROUP BY name HAVING COUNT(*)>1)

-- 真正刪除
DELETE FROM category
WHERE name IN
(SELECT name FROM (SELECT name FROM category GROUP BY name HAVING COUNT(*)>1) c1)
AND id NOT IN
(SELECT id FROM (SELECT MIN(id) id FROM category GROUP BY name HAVING COUNT(*)>1) c2)

-- 多表更新
CREATE TABLE province(id int PRIMARY KEY AUTO_INCREMENT,name varchar(50))
INSERT INTO province(name) SELECT DISTINCT province FROM student;
SELECT * FROM province;

-- 更新student省份把省份變?yōu)閕d

UPDATE student INNER JOIN province ON student.province=province.name
SET student.province=province.id;

ALTER TABLE student CHANGE COLUMN `province` `province_id` INT(50) NULL DEFAULT NULL AFTER city;
最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市捉貌，隨后出現的幾起案子支鸡，更是在濱河造成了極大的恐慌，老刑警劉巖趁窃，帶你破解...
    沈念sama閱讀 218,640評論 6贊 507
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件牧挣，死亡現場離奇詭異，居然都是意外死亡醒陆，警方通過查閱死者的電腦和手機瀑构，發(fā)現死者居然都...
    沈念sama閱讀 93,254評論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來刨摩，“玉大人寺晌，你說我怎么就攤上這事世吨。” “怎么了呻征？”我有些...
    開封第一講書人閱讀 165,011評論 0贊 355
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵耘婚，是天一觀的道長。 經常有香客問我陆赋，道長沐祷，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 58,755評論 1贊 294
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任奏甫，我火速辦了婚禮戈轿，結果婚禮上凌受，老公的妹妹穿的比我還像新娘阵子。我一直安慰自己，他們只是感情好胜蛉，可當我...
    茶點故事閱讀 67,774評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布挠进。 她就那樣靜靜地躺著，像睡著了一般誊册。 火紅的嫁衣襯著肌膚如雪领突。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 51,610評論 1贊 305
  • 城市分裂傳說
    那天案怯，我揣著相機與錄音君旦，去河邊找鬼。 笑死嘲碱，一個胖子當著我的面吹牛金砍，可吹牛的內容都是我干的。 我是一名探鬼主播麦锯，決...
    沈念sama閱讀 40,352評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼恕稠，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了扶欣？” 一聲冷哼從身側響起鹅巍，我...
    開封第一講書人閱讀 39,257評論 0贊 276
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎料祠，沒想到半個月后骆捧，有當地人在樹林里發(fā)現了一具尸體，經...
    沈念sama閱讀 45,717評論 1贊 315
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡髓绽，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 37,894評論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年敛苇，在試婚紗的時候發(fā)現自己被綠了。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片梧宫。...
    茶點故事閱讀 40,021評論 1贊 350
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡接谨，死狀恐怖摆碉，靈堂內的尸體忽然破棺而出，到底是詐尸還是另有隱情脓豪，我是刑警寧澤巷帝，帶...
    沈念sama閱讀 35,735評論 5贊 346
  • ?日本核電站爆炸內幕
    正文 年R本政府宣布，位于F島的核電站扫夜，受9級特大地震影響楞泼，放射性物質發(fā)生泄漏。R本人自食惡果不足惜笤闯，卻給世界環(huán)境...
    茶點故事閱讀 41,354評論 3贊 330
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一堕阔、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧颗味，春花似錦超陆、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,936評論 0贊 22
  • 一樁弒父案时呀，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至晶默，卻和暖如春谨娜，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背磺陡。 一陣腳步聲響...
    開封第一講書人閱讀 33,054評論 1贊 270
  • 情欲美人皮
    我被黑心中介騙來泰國打工趴梢， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人币他。 一個月前我還...
    沈念sama閱讀 48,224評論 3贊 371
  • 代替公主和親
    正文 我出身青樓坞靶，卻偏偏與公主長得像，于是被迫代替她去往敵國和親圆丹。 傳聞我的和親對象是個殘疾皇子滩愁，可洞房花燭夜當晚...
    茶點故事閱讀 44,974評論 2贊 355