MySQL——增删改查基础语法笔记

查询数据

SELECT

SELECT 语法

SELECT [ALL | DISTINCT]{* | table.* | table.field1 [as alias1][,table.field2[as alias2]][,...]]}FROM table_name [as table_alias]	[LEFT | RIGHT | INNER JOIN table_name2] -- 联合查询	[WHERE ...] -- 筛选条件	[GROUP BY ...] -- 按字段分组	[HAVING] -- 过滤次要条件	[ORDER BY ...] -- 排序	[LIMIT (n-1)*pageSize,pageSize] -- 分页

​ ​ ​ ​

查询示例

-- 查询某个数据表全部记录(有字段未赋值也显示为空)SELECT * FROM 表名;-- *表示全部数据项,也可选择多个项SELECT 字段1,字段2,字段3 FROM 表名;-- distinct 去除查询结果中的重复数据SELECT DISTINCT 字段名 FROM 表名;-- between查询值在一定区间内的数据(包括边界点),也可替换为符号<,>,<=等SELECT * FROM 表名 WHERE 字段名 BETWEEN 下限 AND 上限;-- in查询具体数据SELECT * FROM 表名 WHERE 字段名 IN(数据1,数据2,数据3,...);-- 以升降序查询ORDER BY ... DESC (descend) -- desc为降序,asc为升序,默认升序不用写SELECT * FROM 表名 ORDER BY 字段名 DESC;-- 多重条件时默认第一条件,第一条件相同第二条件SELECT * FROM 表名 ORDER BY 字段1 ASC,字段2 DESC;-- count统计SELECT COUNT(*) FROM 表名 WHERE 字段名='...';-- MAX,MIN的用法SELECT MAX(字段名) FROM 表名;-- avg求平均SELECT AVG(字段名) FROM 表名 WHERE 附加条件;-- 多表联查SELECT 字段1,字段2,字段3 FROM 表1,表2 WHERE 限制条件(表1.字段1=表2.字段1);

​ ​ ​ ​

查询时的分页排序 LIMIT

分页

-- 语法:LIMIT 起始条数,页面大小-- 从符合条件的第六条记录开始显示,一页十条记录SELECT ... FROM ...WHERE ...LIMIT 6,10;-- 查询 LIMIT (n-1)*pageSize,pageSize; 即为从第n页开始显示数据,n为当前页

​ ​ ​ ​

排序

-- 升序ASC,降序DESC,不写默认升序-- ORDER BY置于筛选条件后-- 以降序查询SELECT ... FROM ...WHERE ...ORDER BY 字段名 DESC;

​ ​ ​ ​

SELECT语句中的执行次序

​ ​ ​ ​ 1.FROM

​ ​ ​ ​ 2.ON

​ ​ ​ ​ 3.JOIN

​ ​ ​ ​ 4.WHERE

​ ​ ​ ​ 5.GROUP BY

​ ​ ​ ​ 6.WITH CUBE or WITH ROLLUP

​ ​ ​ ​ 7.HAVING

​ ​ ​ ​ 8.SELECT

​ ​ ​ ​ 9.DISTINCT

​ ​ ​ ​ 10.ORDER BY

​ ​ ​ ​ 11.TOP

​ ​ ​ ​

SELECT的特殊用法

-- 查询系统版本SELECT VERSION();-- 用于计算SELECT 表达式 AS 计算结果;-- 查询自增步长 SELECT @@auto_increment_increment;-- 将某项数据所有查询结果+1SELECT 字段1 +1 AS "+1后" FROM 表1;

​ ​ ​ ​

模糊查询

​ ​ ​ ​ 通常采用 LIKE 字段来进行模糊查询,查询语句如下:

SELECT 字段 FROM 表 WHERE 某字段 LIKE '条件'; -- 条件一定要用''括起来

​ ​ ​ ​

LIKE存在四种匹配模式:

  • %:表示任意0个或多个字符,可匹配任意类型和长度的字符
-- 查询学生表中所有姓王的同学SELECT `id`,`name` FROM `student` WHERE `name` LIKE '王%';-- 查询学生表中所有名字中带有'翔'的同学SELECT `id`,`name` FROM `student` WHERE `name` LIKE '%翔%';-- 查询学生表中所有名字中同时带有'翔'和'腾'的同学SELECT `id`,`name` FROM `student` WHERE `name` LIKE '%翔%' AND '%腾%';

​ ​ ​ ​

  • _:表示任意单个字符,常用来限制表达式的字符长度
-- 查询学生表中所有姓王,且名字为两字的同学SELECT `id`,`name` FROM `student` WHERE `name` LIKE '王_';-- 查询学生表中所有姓王,且名字为三字的同学SELECT `id`,`name` FROM `student` WHERE `name` LIKE '王__';-- 查询学生表中所有中间字为'嘉'的同学SELECT `id`,`name` FROM `student` WHERE `name` LIKE '_嘉_';

​ ​ ​ ​

  • [ ]:表示括号内所列字符中的一个,指定一个字符、字符串或范围,要求所匹配对象为它们中的任意一个。如果括号中有一系列字符,可以简写为 “1-5”,“b-f”
-- 查询学生表中所有姓王、李、赵,且名字为两字的同学SELECT `id`,`name` FROM `student` WHERE `name` LIKE '[王李赵]_';-- 查询学生表中所有学号开头为2016、2017、2018、2019的同学SELECT `id`,`name` FROM `student` WHERE `id` LIKE '201[6-9]%';

​ ​ ​ ​

  • [^]:表示不在括号内所列字符之内的单个字符。用法和[ ]相同。
-- 查询学生表中姓氏除王、李、赵外名字为两字的同学SELECT `id`,`name` FROM `student` WHERE `name` LIKE '[^王李赵]_';-- 查询学生表中所有学号开头不为2016、2017、2018、2019的同学SELECT `id`,`name` FROM `student` WHERE `id` LIKE '201[^6-9]%';

​ ​ ​ ​

联表查询

JOIN连接

​ ​ ​ ​ SQL中使用JOIN把两个或多个表结合起来,JOIN常与ON一同使用。

JOIN类型 作用
INNER JOIN 如果表中有至少一个匹配,则返回行
RIGHT JOIN 即使左表中没有匹配,也从右表返回所有的行
LEFT JOIN 即使右表中没有匹配,也从左表返回所有的行
FULL JOIN 只要其中一个表中存在匹配,则返回行
-- inner join查询示例SELECT s.studentno,`studentname`,`subjectno`,`studentresult`FROM `student` AS sINNER JOIN `result` AS rON s.studentno = r.studentno;

​ ​ ​ ​

自连接

​ ​ ​ ​ 自己的表和自己连接,把一张表看成两张一模一样的表。

-- 自连接查询示例SELECT s.studentno,`studentname`,`subjectno`,`studentresult`FROM `student` AS a,`student` AS bWHERE a.studentno = b.subjectno;

​ ​ ​ ​

子查询

​ ​ ​ ​ 在WHERE后嵌套一个查询语句

SELECT ...FROM ...WHERE (SELECT ... FROM ...)

​ ​ ​ ​

增添数据

INSERT

-- 往表中添加记录,添加项需要与字段数对应INSERT INTO 表名(字段1,字段2...) VALUES('值1','值2'...);INSERT INTO 表名(字段名) VALUES('...');-- 插入多个字段用逗号分开INSERT INTO 表名(字段1,字段2)VALUES('值1','值2'),('值3','值4')...;

​ ​ ​ ​

删除数据

DELETE

-- 删除数据表中id为103的数据项,如果没加限制条件会直接删除整张表DELETE FROM 库名 WHERE id='103';-- 删除数据库DROP DATABASE 库名;

​ ​ ​ ​

TRUNCATE

-- 完全清空一个数据库表,表的结构和索引约束不变,删除数据库表时常用TRUNCATE `student`;

​ ​ ​ ​

DELETE 和 TRUNCATE 删除表的异同:

  • 相同:都能删除数据,且不会删除表的结构
  • 不同:
    • TRUNCATE 会重新设置自增列,计数器会归零,而 DELETE 不会归零
    • TRUNCATE 不会影响事务

​ ​ ​ ​

修改数据

UPDATE

-- 修改数据UPDATE 库名 SET 字段1='小明' WHERE 字段1='张三';UPDATE 库名 SET 字段1='小明' WHERE id='7355608';-- 如未加入限制条件,则会修改所有数据(所有name都会变成小明)UPDATE 库名 SET `name`='小明';-- 修改多个属性,使用逗号隔开UPDATE 库名 SET `name`='小明', `sex`='男' WHERE id='7355608';