学而时习之 不亦说乎

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指定多个可能值,固定的值 相当于多个=加andIN (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;#返回指定列的不同值的数目,简单地说相同的值算一个