Mysql 常用语句（学习笔记）

发表于：2020-11-17分类：数据库笔记

常用sql语句

插入数据 INSERT INTO

INSERT INTO table_name VALUES (value1,value2,value3,...);
#按字段顺序插入
INSERT INTO table_name (column1,column2,column3,...) 
                    VALUES (value1,value2,value3,...);
#按指定字段插入

查询数据 SELECT

SELECT * FROM table_name;  #表可以是多个表，分开
#查询所有字段
SELECT column_name,column_name FROM table_name;
#查询指定字段（又叫投影查询）

SELECT column_name,column_name #指定字段
FROM table_name #指定表名
[WHERE Clause] #查询条件

WHERE子句

重要知识点

[WHERE condition1 [AND [OR]] condition2.....

from多表查询时，应当使用where 指定查询条件
可以使用and 或 or 指定多个条件，可以配合括号例如（a or b）and c，
删除（DELETE）和更新（UPDATE）时同样适用where
字符串比较根据ASCII码，中文字符比较根据数据库设置

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>	不等于，检测两个值是否相等，如果不相等返回true,注释：在 SQL 的一些版本中，该操作符可被写成 !=	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。
BETWEEN / NOT BETWEEN	选取介于两个值之间的数据范围内的值。	BETWEEN value1 AND value2
LIKE / NOT LIKE	搜索某种模式 %为通配符，支持正则表达式	LIKE 's%' s开头的值
IN / NOT IN	指定多个可能值，固定的值相当于多个=加and	IN (value1,value2,...)
IS NULL / IS NOT NULL	断是否等于null值	num IS NULL
<=>	当比较的的两个值相等或者都为 NULL 时返回 true

排序ORDER BY ，分页 limit

[LIMIT N][ OFFSET M] #返回数量 & 偏移数量（跳过的数量）

#limit和offset用法
#mysql里分页一般用limit来实现，limit可以是范围，limit
select* from article LIMIT 1,3 #这是偏移的简写
select * from article LIMIT 3 OFFSET 1
#上面两种写法都表示取2,3,4三条条数据

ORDER BY column_name,column_name ASC|DESC;
#根据指定的一个或多个字段排序，asc正序、升序，Desc倒序、降序

多表查询

知识点：
给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：
此功能很少使用

SELECT * FROM students, classes;
#会得到2个表的乘积，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

SELECT
    s.id sid,#s..的id变 sid
    s.name,#s..的name不变
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
#表别名s..变s，c..变c

连接查询（连表） JOIN

知识点：
JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；

#常用的内连接 INNER JOIN
#INNER JOIN只返回同时存在于两张表的行数据
#查询所有学生，同时返回班级名称
SELECT s.id, s.name, s.class_id, c.name class_name FROM students s #确定主表s，并别名c.name为class_name
INNER JOIN classes c #需要连接的表别名c
ON s.class_id = c.id; #需要连接的字段，也就是连接条件
#如果连接条件被连表中不存在就不会输出，例如主表有1，2，3，附表只有1，2，只有1,2会被输出
#简单地说只有相等的才会被输出


#外连接OUTER JOIN（右）
#RIGHT OUTER JOIN返回右表都存在的行
#所有班级有哪些学生
SELECT s.id, s.name, s.class_id, c.name class_name FROM students s#确定主表s，并别名c.name为class_name
RIGHT OUTER JOIN classes c #需要连接的表别名c
ON s.class_id = c.id; #需要连接的字段，也就是连接条件
#如果附表中有没有条件连接的，也就是主表有1，2，3，附表有1，2,3,4，那么会为4单独输出一行，其他值填充为NULL


#左连接
#LEFT OUTER JOIN返回左表都存在的行
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s #确定主表s，并别名c.name为class_name
LEFT OUTER JOIN classes c #需要连接的表别名c
ON s.class_id = c.id; #需要连接的字段，也就是连接条件
#如果主表中有没有条件连接的，例如主表有1，2，3，附表只有1，2，那么会输出1,2,3 其中3的class_name会被填充NULL

#全连接：
#FULL OUTER JOIN，它会把两张表的所有记录全部选择出来
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s #确定主表s，并别名c.name为class_name
FULL OUTER JOIN classes c #需要连接的表别名c
ON s.class_id = c.id; #需要连接的字段，也就是连接条件
#把所有主表和附表中有和没有条件连接的，例如主表有1，2，3附表只有1，5，全部输出，无连接条件的，全部填充为null，输出类似1有，2null，3null，5null

连接说明

分组查询 GROUP BY

重要知识点

GROUP BY 可以讲数据表分组进行查询,可以连表，一般会搭配函数

SELECT column_name, function(column_name) #字段范围,及套用的函数
FROM table_name #来自表
[ WHERE Clause ] #条件
GROUP BY column_name; #分组字段

#例如有 小明，男 小花，女 小张，女
SELECT sex,group_concat(name) FROM namelist GROUP BY sex;
#得到这样的分组结果，男女都有谁
#小明         男
#小张，小花     女

筛选分组后的数据 HAVING

知识点：WHERE 关键字无法与聚合类函数一起使用，所以要用HAVING

SELECT column_name, aggregate_function(column_name) #选取字段，及套用的函数
FROM table_name #来自表
[ WHERE Clause ] #条件
GROUP BY column_name #分组字段
HAVING aggregate_function(column_name) operator value;#筛选条件

#例如 小张 100 小李 200 小王 100 小张 100
SELECT name, SUM(num)
FROM table_name
GROUP BY name
HAVING SUM(num)>=200
#结果 小张200 小李200
#目的是获取num和大于等于200的人

取唯一值 DISTINCT

SELECT DISTINCT sex FROM namelist #DISTINCT 取有哪几种性别(列中仅选取唯一不同的值)
#得到 男/女

修改数据 UPDATE

请注意UPDATE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！

UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;
#更新指定表，指定条件，指定字段的值

SET score=score+10 #+10

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

删除数据 DELETE

请注意DELETE语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

DELETE FROM students WHERE id=1;
DELETE FROM students WHERE id>=5 AND id<=7;
#删除指定表指定条件

插入或替换、更新、忽略

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
#替换指定表中id=1的行，如果不存在就插入

INSERT INTO students(id, class_id, name, gender, score) 
                    VALUES (1, 1, '小明', 'F', 99)
                    ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
#插入一条数据id=1，如果已经存在就更新update后的内容

INSERT IGNORE INTO students (id, class_id, name, gender, score) 
                    VALUES (1, 1, '小明', 'F', 99);
#插入一条数据如果存在就忽略

合并结果 UNION

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
#列数量必须一致，类型应当相似
#取两个表中列的唯一值例如a表1，2，2，3；b表3，4，5；得到1，2，3，4，5

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
#取2个表中列全部的值，得到1，2，2，3，3，4，5

内置函数

#总数
SELECT COUNT(*) FROM students;#统计一共有多少条记录
SELECT COUNT(column_name) FROM table_name;#返回指定列的值的数目（NULL 不计入）
SELECT COUNT(DISTINCT column_name) FROM table_name;#返回指定列的不同值的数目，简单地说相同的值算一个

My1e0n

学而时习之不亦说乎

Mysql 常用语句（学习笔记）

常用sql语句

插入数据 INSERT INTO

查询数据 SELECT

WHERE子句

排序ORDER BY ，分页 limit

多表查询

连接查询（连表） JOIN

分组查询 GROUP BY

筛选分组后的数据 HAVING

取唯一值 DISTINCT

修改数据 UPDATE

删除数据 DELETE

插入或替换、更新、忽略

合并结果 UNION

内置函数

My1e0n

学而时习之 不亦说乎

Mysql 常用语句（学习笔记）

常用sql语句

插入数据 INSERT INTO

查询数据 SELECT

WHERE子句

排序ORDER BY ，分页 limit

多表查询

连接查询（连表） JOIN

分组查询 GROUP BY

筛选分组后的数据 HAVING

取唯一值 DISTINCT

修改数据 UPDATE

删除数据 DELETE

插入或替换、更新、忽略

合并结果 UNION

内置函数

学而时习之不亦说乎