MySQL—SQL基础篇(DML)

如无特殊说明 以下示例sql语句中，使用的表 student
包含字段 id name age gender city

$参考资料$
思维导图：https://processon.com/mindmap/6004e42fe401fd261bc70815
SQL基础：https://www.w3school.com.cn/sql/index.asp

SQL概念

• What is SQL?
  结构化查询语言
• What can SQL do？
  面向数据库执行查询
  从数据库取出，插入，更新，删除数据
  创建数据库，表、视图、存储过程
  设置和分配权限
• How to apply SQL？
  RDBMS 指的是关系型数据库管理系统。是SQL的基础。建立在各种的数据之上的操作

DML

select

简单的查询案例

select

# select 列名称 from 表名称
select name from student

select *

# select * from 表名称
select * from student

星号（*）是选取所有列的快捷方式

查询常用的关键字

where

# select 列名称 from 表名称 where 列 运算符 值
select * from student where name = '张三'

常用的运算符：= <> > < >= <= like between
and&or

select * from student where age > 18 and gender = '女'
select * from student where age = 18 and gender = '男'

like

# LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
# select 列名 from student where 列名 like pattern
select * from student where name like '张%' 

"%" 可用于定义通配符（模式中缺少的字母）。
通配符

# % 替代一个或多个字符
select * from student where name like '%嘉%'
# _ 仅代替一个字符
select * from student where name like '_嘉'
# [charlist] 字符列表中的任意单一字符
select * from student where city like '[ABC]%'
# [^charlist] [!charlist] 不在字符列表中的任何单一字符
select * from student where name like '[!ABC]%'

in

# select * from 表名 where 列名 in (v1,v2,v3)
select * from student where id in (1, 2, 3)

between

# SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
select * from student where name between '张三' and '诸葛'

Alias

# 通过使用 SQL，可以为列名称和表名称指定别名（Alias）。
# SELECT column_name(s) FROM table_name AS alias_name
# SELECT column_name AS alias_name FROM table_name
select * from student as s

limit

# limit 子句用于规定要返回的记录的数目
# SELECT column_name(s) FROM table_name LIMIT number
# 有几种情况
## 查询10条数据，索引从0到9，第1条记录到第10条记录
select * from student limit 10;
# 等同于
select * from student limit 0,10;
# 查询8条数据，索引从5到12，第6条记录到第13条记录
select * from t_user limit 5,8;

MySQL的下标索引是从1开始的
distinct

# SELECT DISTINCT 列名称 FROM 表名称
select distinct name from student 

order by

# 对结果集进行排序
select * from student order age esc # 升序
selcet * from student order age desc # 降序

连接查询

SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。

数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。

假如有两个表分别是 Persons 和 Orders

请注意，"Id_P" 列是 Persons 表中的的主键。这意味着没有两行能够拥有相同的 Id_P。即使两个人的姓名完全相同，Id_P 也可以区分他们。

请注意，"Id_O" 列是 Orders 表中的的主键，同时，"Orders" 表中的 "Id_P" 列用于引用 "Persons" 表中的人，而无需使用他们的确切姓名。

"Id_P" 列把上面的两个表联系了起来。

应用两个表

# 常规方法
select p.LastName, p.FirstName, o.OrderNo from Persons as p, Orders as o where p.Id_p = o.Id_p

使用连接查询 join

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

不同的 SQL JOIN，除了我们在上面的例子中使用的 INNER JOIN（内连接），我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

inner join

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
语法规则

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

还是使用上面的表

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
INNER JOIN Orders ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

left join

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
语法规则

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

使用上面的表

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

right join

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
语法规则

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

使用上面的表

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行。

full join

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
语法规则

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

使用上面的表

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
FULL JOIN Orders ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。

union

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
有两种情况分别是union 和 union all

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

union

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

union all

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

UNION ALL 命令和 UNION 命令几乎是等效的，UNION 操作符选取不同的值，不过 UNION ALL 命令会列出所有的值。
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

insert

INSERT INTO 语句用于向表格中插入新的行。
语法

INSERT INTO 表名称 VALUES (值1, 值2,....)
# 或则
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

例子

INSERT INTO student VALUES ('张三', 18, '男', '北京')
INSERT INTO student(name,age,gender,city) VALUES ('张三', 18, '男', '北京')

update

Update 语句用于修改表中的数据。
语法

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

示例

# 更新某一列
update student set age = 22 where name = '张三'
# 更新若干列
update student set age = 22,  city= '上海' where name = '李四'

delete

DELETE 语句用于删除表中的行。
语法

DELETE FROM 表名称 WHERE 列名称 = 值

示例

# 删除某一条记录
DELETE FROM student WHERE name = 'Wilson' 
# 删除整个表的数据
delete from student;
delete * from student;