MySQL基础

MySQL基础

DDL-数据库操作

1. 查看当前所有的数据库

SHOW DATABASES;

2. 创建数据库

CREATE DATABASE 数据库名;

3. 切换数据库

USE 数据库名;

4. 查看当前正在使用的数据库

SELECT DATABASE();

5. 删除数据库

DROP DATABASE 数据库名;

---

DDL-表操作

1. 查看当前数据库内的所有表

SHOW TABLES;

2. 创建表

CREATE TABLE [IF NOT EXISTS] 表名 (字段名 字段类型, 字段名 字段类型, ...);

3. 展示表结构

DESC 表名;

4. 展示表创建代码

SHOW CREATE TABLE 表名;

5. 修改表

# 新增字段
ALTER TABLE 表名 ADD 字段 字段类型 [COMMENT 注释][约束];
# 删除字段
ALTER TABLE 表名 DROP 字段;
# 修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新的字段类型;
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新的字段类型 [COMMENT 注释][约束];
# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

6. 删除表

# 删除表
DROP TABLE [IF EXISTS] 表名;
# 删除表数据但保留表结构
TRUNCATE TABLE 表名;

---

DML

1. 添加数据

# 给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...) [(值1, 值2, ...), ...];
# 给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...) [(值1, 值2, ...), ...];

注意：

• 插入数据时指定字段顺序需要与值一一对应。
• 字符串和日期型数据需要包含在引号中。
• 插入数据的大小应当在字段的规定范围之内。

2. 修改数据

UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ... [WHERE 条件];

注意：

• 若不添加WHERE条件将修改全表数据。

3. 删除数据

DELETE FROM 表名 [WHERE 条件];

注意：

• DELETE语句的条件如果没有则会删除整张表的所有数据。
• DELETE只能删除一条或多条记录（行）而不能删除某一个字段的值（列），若要删除字段值可以用UPDATE … SET NULL。

---

DQL

1. DQL代码结构

SELECT
	字段1, 字段2, 字段3, ...
FROM
	表名
WHERE
	筛选条件
GROUP BY
	分组字段1, 分组字段2, 分组字段3, ...
HAVING
	筛选条件
ORDER BY
	排序字段1 排序方式1, 排序字段2 排序方式2, 排序字段3 排序方式3, ...
LIMIT
	分页参数;
[OFFSET 偏移量]

注意：

• WHERE后跟筛选条件使用比较运算符进行判断。

  

• 筛选条件可以用AND/OR/NOT等逻辑运算符进行组合。

• GROUP BY经常与聚合函数一起使用，NULL值不参与聚合函数的运算。

  

• COUNT(*)/COUNT(1)/COUNT(字段名)的区别：

  • COUNT(*)性能会经过SQL优化，查询全表中的所有记录，不会忽略某列列值为NULL的情况。
  • COUNT(1)统计查询结果的行数，不考虑表中的任何列的值，与COUNT(*)类似，不会忽略某列列值为NULL的情况。
  • COUNT(字段名)统计指定列的非空值的数量，只统计指定列中不为NULL的行。

• WHERE与HAVING的区别：

  • 执行时机不同：WHERE是分组前过滤，不满足条件则不参与分组；HAVING是分组后对其结果进行过滤。
  • 判断条件不同：WHERE不可以对聚合函数进行判断；HAVING中可以出现聚合函数。

• 分组后查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

• ORDER BY中的排序方式可选择ASC和DESC，其中ASC是默认值。

• LIMIT与OFFSET的关系：

  • LIMIT后接一个参数代表取数结果的限制数量，例如LIMIT 5就会取出符合条件的5条记录。
  • LIMIT后接两个参数表示从几个记录后开始返回限制数量的查询结果，例如LIMIT 1,2就会取出符合条件的第二和第三条记录。
  • OFFSET后接一个参数代表跳过几个记录，例如OFFSET 3就会跳过符合条件的前三条记录从第四条开始取数。

2. DQL执行顺序

FROM>(JOIN)>WHERE>GROUP BY>HAVING>SELECT>ORDER BY>LIMIT

3. 多表查询

4. 联合查询

SELECT 字段名 FROM 表A [...]
UNION [ALL]
SELECT 字段名 FROM 表B [...];

注意：

• UNION ALL直接将多次查询的结果合并，UNION会在合并后将结果去重。
• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致，字段顺序可以在SELECT中调整。

5. 子查询

• a. 列子查询

  列子查询指的是嵌套查询中内层代码查询出来的是一列多行结果。

  eg：查询比财务部（dept_name）所有人工资（salary）都高的员工信息。

  SELECT * 
  FROM emp 
  WHERE salary > ALL(
      SELECT salary FROM emp WHERE dept_id = (
          SELECT id FROM dept where dept_name = '财务部'
      )
  );
  

  eg：查询比财务部（dept_name）任意一人工资（salary）高的员工信息。

  SELECT *
  FROM emp
  WHERE salary > ANY(
      SELECT salary FROM emp WHERE dept_id = (
          SELECT id FROM dept where dept_name = '财务部'
      )
  );
  

• b. 行子查询

  行子查询指的是嵌套查询中内层代码查询出来的是一行多列结果。

  eg：查询员工名（name）为“张无忌”的工资（salary）和直属领导（manager_id）相同的员工薪资。

  SELECT * 
  FROM emp 
  WHERE (salary, manager_id) = (
      SELECT salary, manager_id FROM emp WHERE name = '张无忌'
  );
  

• c. 表子查询

  表子查询指的是嵌套查询中内层代码查询出来的是多行多列结果。

  eg：查询职位（job）和工资（salary）与员工名（name）为“鹿杖客”、“宋远桥”相同的员工信息。

  SELECT *
  FROM emp
  WHERE (job, salary) in (
      SELECT job, salary FROM emp WHERE name in ('鹿杖客', '宋远桥')
  )
  

---

约束

1. 约束的概念

约束是作用于表中字段上的规则，用以限制存储在表中的数据。

2. 约束的目的

约束能保证数据库中数据的正确性、有效性和完整性。

3. 约束的分类

4. 外键约束

• a. 添加外键

  # 创建表时添加外键
  CREATE TABLE 表名 (
      字段名 字段类型,
      ...
      [CONSTRAINT] 外键约束名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) [ON UPDATE CASCADE ON DELETE SET NULL]
  );
  # 添加外键
  ALTER TABLE 表名 ADD CONSTRAINT 外键约束名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) [ON UPDATE CASCADE ON DELETE SET NULL];
  

• b. 删除外键

  ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名称;