复习一下Mysql数据库的各种命令

特别提醒

！！！所有有关删除、替换、去重的语句，一定要慎重慎重再慎重使用！！！
！！！精选查询关键字组合，注意查询效率

一、前言

MySQL作为一种流行的关系型数据库管理系统，支持多种语言类型，其中一些常见的包括：

DDL（Data Definition Language，数据定义语言）：用于定义和管理数据库对象（如表、索引、视图等），包括CREATE、ALTER、DROP、TRUNCATE等命令。

DML（Data Manipulation Language，数据操作语言）：用于对数据进行查询、插入、更新和删除等操作，包括SELECT、INSERT、UPDATE、DELETE等命令。

DCL（Data Control Language，数据控制语言）：用于控制访问和权限，包括GRANT、REVOKE、COMMIT、ROLLBACK等命令。

TCL（Transaction Control Language，事务控制语言）：用于控制事务的提交和回滚，包括BEGIN、COMMIT、ROLLBACK等命令。

除了上述基本的语言类型，MySQL还支持存储过程、触发器、函数等高级特性，在日常开发中也经常使用。这些特性可以帮助我们更好地组织和管理数据库对象和业务逻辑，提高开发效率和代码可维护性。

二、DDL

1. CREATE：创建数据库或表结构
   (1)创建数据库
   CREATE DATABASE dbname;
   (2)创建表
   CREATE TABLE tablename (colname1 datatype1 [not null], colname2 datatype2 [, ...]);
2. ALTER：修改数据库或表结构
   (1)在表中添加新列
   ALTER TABLE tablename ADD COLUMN colname datatype [after existing_colname];
   (2)修改列的数据类型
   ALTER TABLE tablename MODIFY COLUMN colname new_datatype;
   (3)删除列
   ALTER TABLE tablename DROP COLUMN colname;
   (4)重命名表
   ALTER TABLE tablename RENAME TO new_tablename;
3. DROP：删除数据库或表结构
   (1)删除数据库
   DROP DATABASE dbname;
   (2)删除表【数据和表结构一起删掉】
   DROP TABLE tablename;
4. TRUNCATE：清空表中数据
   (1)清空表中所有数据【仅保留表结构，速度很快，不能撤销】
   TRUNCATE TABLE tablename;

三、DML

1. SELECT：查询数据
   (1)查询表中指定列的数据
   SELECT colname1 [, colname2, ...] FROM tablename [WHERE condition];
   (2)查询表中所有列的数据
   SELECT * FROM tablename;
   (3)统计表中数据的总数
   SELECT COUNT(*) FROM tablename;

2. INSERT：插入新数据
   (1)插入单行数据
   INSERT INTO tablename (colname1 [, colname2, ...]) VALUES (value1 [, value2, ...]);
   (2)插入多行数据
   INSERT INTO tablename (colname1 [, colname2, ...]) VALUES (value1 [, value2, ...]), (value1 [, value2, ...]);

3. UPDATE：更新数据
   (1)更新符合条件的记录
   UPDATE tablename SET colname = new_value WHERE condition;

4. DELETE：删除数据
   (1)删除符合条件的记录【能删除内容，但是自增序列不会重置】
   DELETE FROM tablename WHERE condition;

四、DQL

DQL（Doctrine Query Language），是 Doctrine 对象关系映射（ORM）框架中的一种查询语言。通过 DQL，可以使用面向对象的方式来查询数据库，并且不需要直接编写 SQL 语句，从而提高代码的可维护性和可读性。

基本语法

SELECT 字段1,字段2,...
FROM 表名1,表名2
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数
;

1. 基本查询

(1)全部字段
select * from tablename;
(2)部分字段，并起个别名
select colname1 as col1,colname2 as col2 from tablename;
(3)去掉重复记录
select distinct cloname1,colname2 from tablename;

2. 条件查询

select colname from tablename where condition;

(1)比较运算符

符号	说明
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
between…and…	在某个范围之内
in(…)	在in后面的列表中选1个
like	模糊匹配（_匹配单个字符，%匹配任意字符）
is null	是null值

(2)逻辑运算符【优先级：非>与>或】

运算符	关键字	说明
!	NOT	非
&&	AND	与
`		`

3. 聚合查询

select 聚合函数(colname) from tablename;

函数	说明
count(colname)	总行数
max(colname)	最大值
min(colname)	最小值
avg(colname)	求平均
sum(colname)	求和
round(colname, 2)	四舍五入，保留两位小数

4. 分组查询(group by … having)

（执行顺序：where>聚合函数>having）
SELECT 字段1,字段2,... FROM 表名1,表名2 WHERE 条件列表 GROUP BY colname1,colname2... HAVING 分组后条件列表;

5. 排序查询（order by）

SELECT 字段1,字段2,... FROM 表名1,表名2 ORDER BY colname1,colname2
(1)升序：asc（默认）
(2)降序：desc

6. 分页查询(limit 起始索引,查询记录数)

SELECT 字段1,字段2,... FROM 表名1,表名2 LIMIT 2,10;

7. 关联查询

1. JOIN：关联查询

关键字	说明
INNER JOIN	内连接，只返回两个表中共同匹配的数据
LEFT JOIN	左连接，返回左表中的所有数据和右表中与左表匹配的结果
RIGHT JOIN	右连接，返回右表中的所有数据和左表中与右表匹配的结果
FULL OUTER JOIN	外连接，返回两个表中的所有数据
CROSS JOIN	笛卡尔积或交叉连接，返回两个表中所有可能的组合

2. UNION：合并查询结果

UNION：将两个或多个SELECT语句的结果集进行合并，去除重复的记录，并按照第一个 SELECT 语句中的列名和顺序返回结果。

以下是一个简单的 UNION 的例子：

假设有两个表，分别是 employees1 和 employees2，两个表结构相同，包含员工的信息，包括员工ID、姓名和薪水等字段。现在要通过 UNION 查询两个表中所有员工的信息，SQL语句如下：

SELECT employee_id, first_name, last_name, salary FROM employees1 
UNION 
SELECT employee_id, first_name, last_name, salary FROM employees2;

这个 SQL 语句使用了 UNION 关键字将两个 SELECT 语句的结果集进行合并，并去除重复的记录。由于 employees1 和 employees2 表结构相同，因此可以直接使用相同的列名。查询结果将会返回一个新的表格，包含了两个表中所有员工的信息。

需要注意的是，UNION 操作会耗费一定的计算资源，尤其是对大型数据集的操作，应该谨慎使用。同时，由于 UNION 去除重复记录需要额外的计算，可能会影响查询性能，特别是在数据量较大时。

8. 子查询

子查询（Subquery）是一个 SELECT 语句嵌套在另一个 SELECT、INSERT、UPDATE 或 DELETE 语句中的查询，用于获取一些特定的数据集合或作为其他查询的条件。子查询可以出现在 SELECT 语句中的 WHERE 子句中、FROM 子句中或 HAVING 子句中。

以下是一个简单的子查询的例子：

假设有两个表，分别是 employees 和 departments，其中 employees 表包含员工的信息，包括员工ID、姓名和部门ID等字段，而 departments 表包含部门的信息，包括部门ID和部门名称等字段。现在要查询所有没有分配到部门的员工的信息，SQL语句如下：

SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments);

这个 SQL 语句使用了子查询，在主查询中嵌套了一个 SELECT 语句，用于返回 departments 表中所有部门ID的值。然后，主查询中的 WHERE 子句使用 NOT IN 条件将 employees 表中所有部门ID不在子查询结果集中的员工筛选出来。查询结果将会返回一个新的表格，包含了所有没有分配到部门的员工的信息。

需要注意的是，MySQL 的子查询可能会影响查询性能，特别是在进行大量计算或处理大量数据时。因此，在编写子查询时应该尽量优化，尽可能减少计算量和提高查询效率。

四、添加索引

1.聚集索引

也称为主键索引。聚集索引会按照索引顺序来存放数据，因此每个表只能有一个聚集索引。如果表中没有定义主键，则 InnoDB 存储引擎会选择一个唯一非空索引代替主键。
(1)创建主键(primary key)
alter table tablename add primary key(id)
(2)创建外键(foreign key)
ALTER TABLE employees ADD CONSTRAINT fk_employee_department_id FOREIGN KEY (department_id) REFERENCES departments(id);

2.非聚集索引(index)

也称为辅助索引。非聚集索引并不会改变数据的物理存储顺序，而是在数据行上建立指向数据的指针，因此可以建立多个非聚集索引。当查询条件不使用聚集索引时，MySQL 会自动使用非聚集索引。
alter table tablename add index 索引名(colname)

3.唯一索引(unique index)

该类型的索引会强制确保列中的所有值都是唯一的。与聚集索引和非聚集索引类似，唯一索引也可以是单列索引或者多列索引。
ALTER TABLE employees ADD UNIQUE INDEX idx_employee_email (email);

4.全文索引(fulltext index)

全文索引可以用于匹配文本字段内容，支持对文本字段进行高效的搜索操作。需要注意的是，全文索引仅适用于 MyISAM 和 InnoDB 引擎，且只能应用于 CHAR、VARCHAR 和 TEXT 类型的字段。在添加全文索引时，需要谨慎选择需要添加索引的列，并进行适当测试和优化。同时，也需要考虑索引对数据库性能和存储空间的影响，以权衡利弊。
ALTER TABLE articles ADD FULLTEXT INDEX idx_article_title (title);

五、复制表或者批量插入

1. 复制表结构和内容，不包括主键、自增值等信息
   CREATE TABLE tablename1 SELECT * FROM tablename2;
2. 仅复制表结构，不包括内容、主键、自增值等信息
   CREATE TABLE tablename1 SELECT * FROM tablename2 where 1=2;
3. 在两表结构相同的情况下，复制内容
   INSERT INTO tablename1 SELECT * FROM tablename2;
4. 查看创建表的命令，然后copy，该表名
   SHOW CREATE TABLE tablename;