SQL基础语句（一看就会版）

DDL Data Definition Language

数据定义语言，在定义或改变表结构、数据类型、表之间的链接等等使用，一般使用视图工具创建导出

-- 关键字大写
-- 创建表 如果表明是关键字要加反引号

CREATE TABLE `student`
--  PRIMARY KEY AUTO_INCREMENT 创建时设定主键 
(
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
-- 函数体也可以包含声明，循环和流程控制
-- 返回值只能有一个

-- 函数名要跟着()表示参数
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
-- 查询某一列在指定的规范内的记录，包括两个边界
SELECT * FROM score WHERE grade BETWEEN 80 AND 100;

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

聚合函数
对一组值进行计算，并返回计算后的值，一般用来统计数据

-- 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;

-- 统计每门课程的最高分，并按分数从高到低排序
SELECT course_id,MAX(grade)
FROM score
GROUP BY course_id
ORDER BY MAX(grade) DESC;

-- 多列分组
-- 统计各省男女的总人数
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(*) 没达到优的次数 
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;

-- 无限分类【自身连接】
-- 创建商品分类表 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变为名称
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省份把省份变为id

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阅读 211,042评论 6赞 490
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 89,996评论 2赞 384
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 156,674评论 0赞 345
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,340评论 1赞 283
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,404评论 5赞 384
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,749评论 1赞 289
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,902评论 3赞 405
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,662评论 0赞 266
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,110评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,451评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,577评论 1赞 340
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,258评论 4赞 328
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,848评论 3赞 312
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,726评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,952评论 1赞 264
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,271评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,452评论 2赞 348