数据库学习-MySQL基础篇（二）

DML主要实现对表的增删改查

1. 表记录的插入

基本语法

INSERT INTO TableName (column1,column2……columnN) VALUES(value1,value2……valueN);

向students中插入记录

注意：

• 如果VALUES后面跟的是表全部列的属性值，且顺序一致，那么TableName后面括号及内容可以省略。
• 对于可控列，非空但有默认值的列以及自增列可省略不写。
• 如果数据是字符型，必须加单引号或者双引号。
  

2. 表记录的查询

(1) SELECT语句的基本语法

SELECT [DISTINCT] SelectList FROM TableList --从哪些表中选择哪些列进行展示
WHERE RowConstraint --满足什么样的条件
GROUP BY GroupingColumns --结果按照什么条件分组
ORDER BY SortingColumns --结果按照什么条件排序
HAVING GroupingConstraint --分组需要满足的条件
LIMIT Count; --限制结果中的行数

(2) SELECT用法举例

1) 单表查询

使用到的两个表格

• 全列查询

SELECT * FROM t1;

• 查询指定的列

SECLET i1 FROM t1;

• 查询字段为表达式

SELECT i1+10 FROM t1;

注意：可将查询结果中的列指定别名，如i1+10可以指定为i1Plus

SELECT i1+10 AS i1Plus FROM t1;
-- 或者
SELECT i1+10 i1Plus FROM t1;

• 查询结果去重

SELECT DISTINCT i1 FROM t1;

• 条件查询 WHERE
  基本查询运算符：>,=,<,<>,!=,!>,!<,=>,=<

SELECT * FROM t1
WHERE i1<>2;

AND与OR

SELECT * FROM t1
WHERE i1=2 AND c1='b';

BETWEEN ……AND……

SELECT * FROM t1
WHERE i1 BETWEEN 1 AND 2;

注意：从查询结果看是左闭右闭区间
IN

SELECT * FROM t1
WHERE i1 IN (1,2);
```![在这里插入图片描述](https://img-blog.csdnimg.cn/20201106112626148.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzEwNjU3Mg==,size_16,color_FFFFFF,t_70#pic_center)
模糊查询LIKE
```sql
SELECT * FROM t1
WHERE i1 LIKE '1%';

通配符	含义
%	匹配任意多个字符（0-无数个）
_	匹配任意一个字符

• 对查询结果进行分组

SELECT c1,COUNT(i1) FROM t1 GROUP BY c1;

注意：如果有WHERE子句必须在GROUP BY子句之前

• ORDER BY子句

SELECT c1,MAX(i1) FROM t1 GROUP BY c1 ORDER BY c1 DESC; --默认升序排列，需要降序排列则增加DESC，指定升序排列则增加ASC

• HAVING

SELECT c1,COUNT(i1) FROM t1 GROUP BY c1 HAVING c1 IN ('a','b','c');

• LIMIT（分页功能）

SELECT c1,COUNT(i1) FROM t1 GROUP BY c1 LIMIT 1,3;

2) 多表查询

a. 使用连接实现多表查询

使用到的两个表格

• 内连接 INNER JOIN(CROSS JOIN/JOIN)：把一个表里的行与另一个表里的行进行匹配

SELECT * FROM t1 INNER JOIN t2;

WHERE和ON(当两个表列同名时，也可以使用USING())

SELECT * FROM t1 INNER JOIN t2 WHERE t1.i1=t2.i2;
-- 或者
SELECT * FROM t1 INNER JOIN t2 ON t1.i1=t2.i2;

注意：在查询指定列时，当指定列在不同的表中都存在时，必须要用表名.列名的方式区分两个表中的同名列；当进行自连接时，则可以对表设置别名

SELECT t1.i1, t1Plus.i1 FROM t1 INNER JOIN t1 AS t1Plus;

• 左连接与右连接（内连接只会显示在两个表中都匹配的行，外连接除了显示同样的匹配结果还可以把其中一个表在另一个表中没匹配的行也显示出来）
  左连接：当左表与右表的行匹配时，两行的内容会连接作为一个输出行，当左表与右表的行不匹配时，左表的行仍然会被选择作为输出行
  右连接：与左连接相同，只是基准行是右表的行

SELECT * FROM t1 LEFT JOIN t2 ON t1.i1=t2.i2; --注意：这里使用WHERE会报错

b. 使用子查询实现多表检索

• 带关系比较运算符的子查询（=,<>,>,>=,<,<=）

-- 找出t1中和t2的c2值为‘c’对应的i2标号相同的i1对应的c1值
SELECT c1 FROM t1
WHERE i1 = 
(SELECT i2 FROM t2
  WHERE c2 = 'c');

• 带IN与NOT IN的查询语句

-- 查询t1中c1在t2中c2的值对应的记录
SELECT * FROM t1
WHERE c1 IN 
(SELECT c2 FROM t2);

• ALL，ANY和SOME子查询

-- 查询t1中i1小于t2中所有i2的记录
SELECT * FROM t1
WHERE i1 <= ALL(SELECT i2 FROM t2);

-- 查询t1中i1大于t2中任意一条i2的记录
SELECT * FROM t1
WHERE i1 >= ANY(SELECT i2 FROM t2);

• EXISTS和NOT EXISTS 测试某个子查询是否返回了行

SELECT EXISTS (SELECT * FROM t2);

• 相关子查询
  相关子查询即子查询引用了外层查询的值即子查询依赖于外层查询，子查询不能作为单独的一条查询语句去执行。

SELECT i2 FROM t2 WHERE (SELECT i1 FROM t1 WHERE i1 = i2);

查询原理：把值从外层查询传递到子查询，并检测他们是否满足子查询中的条件

• FROM 中的子查询

SELECT * FROM (SELECT * FROM t1) AS t;

c) 使用UNION(把多个查询结果合并为一个)实现多表检索

SELECT i1 FROM t1 UNION SELECT c2 FROM t2;

注意：UNION结果集中的列名来自第一个SELECT语句的列名；多个SELECT语句应该具有相同个数的列，但是列名和数据类型可以不相同；默认情况下，UNION会将结果集中重复的结果去掉。
UNION和ORDER BY及LIMIT联合使用

-- 需要把每个SELECT语句使用括号括起来且ORDER BY 不能引用表名，即如果某个列是表名.列名，那就必须要取个别名才能进行排序
(SELECT i1 FROM t1) UNION (SELECT c2 FROM t2) ORDER BY i1;
-- ORDER及LIMIT也可以用在UNION的某个或多个单个的SELECT中
(SELECT i1 FROM t1 ORDER BY i1) UNION (SELECT c2 FROM t2 ORDER BY c2);

3.表记录的更新

(1) UPDATE基本语法

1) 单表更新

UPDATE TableName 
SET ColumnsName = xxx
WHERE Contions;

2) 涉及多个表的更新

UPDATE TableName1,TableName2 
SET TableName1.ColumnsName……TableName2.ColumsName
WHERE TableName1……TableName2;

(2) UPDATE举例

1) 单表更新

-- 更改表t1中i1值为1的c1值为'd'
UPDATE t1
SET c1 = 'd'
WHERE i1 = 1;

2) 多表更行举例

UPDATE t1,t2
SET i1=i2
WHERE c1 = c2;

4.表记录的删除

(1) DELETE基本语法

1) 单表删除

DELETE FROM TableName WHERE Contions;

2)涉及多表的删除

-- 根据多表的匹配结果删除单个表中的数据
DELETE TableName1 FROM TableName1 INNER JOIN TableName2 ON TableName1.Column1 = TableName2.Column2;
-- 根据多表的匹配结果删除多个表中的数据
DELETE TableName1,TableName2 FROM TableName1 INNER JOIN TableName2 ON TableName1.Column1 = TableName2.Column2;

(2) DELETE用法举例

1) 单表删除

-- 删除t1中c1值为'a'的行
DELETE FROM t1 WHERE c1 = 'a';

2) 涉及多表的删除

-- 根据多表的匹配结果删除单个表中的数据
DELETE t1 FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2;

-- 根据多表的匹配结果删除单个表中的数据
DELETE t1,t2 FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2;