玩转 MySQL：从基础到进阶

创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARACTER SET 字符集 COLLATE 排序规则字符集]。
删除数据库：DROP DATABASE [IF EXISTS] 数据库名。
修改数据库编码：ALTER DATABASE 数据库名 CHARACTER SET 字符集。
创建表：CREATE TABLE 表名(列名列类型 [列的特征], ...)。
删除表：DROP TABLE 表名。
修改表：ALTER TABLE 表名 ADD/DROP/MODIFY/CHANGE 列名列类型 [列的特征]。
重命名表：ALTER TABLE 表名 RENAME TO 新表名。

DDL 操作示例：

-- 创建数据库 mydb1，使用 utf8mb4 字符集和 utf8mb4_bin 排序规则
CREATE DATABASE IF NOT EXISTS mydb1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- 创建表 stu，包含 sid, sname, age, gender 四个字段
CREATE TABLE stu (
  sid CHAR(6) PRIMARY KEY,
  sname VARCHAR(20) NOT NULL,
  age INT,
  gender VARCHAR(10) NOT NULL
);

-- 插入数据
INSERT INTO stu (sid, sname, age, gender) VALUES ('S_1001', '张三', 20, '男');
INSERT INTO stu (sid, sname, age, gender) VALUES ('S_1002', '李四', 22, '女');

4.DML 操作：

插入数据：INSERT INTO 表名(列名1,列名2, ...) VALUES(值1,值2, ...)。
修改数据：UPDATE 表名 SET 列名1=值1, ... [WHERE 条件]。
删除数据：DELETE FROM 表名 [WHERE 条件]。

DML 操作示例：
-- 查询所有学生信息
SELECT * FROM stu;

-- 更新学生信息
UPDATE stu SET sname='李四', age=23 WHERE sid='S_1002';

-- 删除学生信息
DELETE FROM stu WHERE sid='S_1001';

5.DCL 操作：

查询用户：SELECT * FROM mysql.user。
创建用户：CREATE USER 用户名@地址 IDENTIFIED BY '密码'。
给用户授权：GRANT 权限1, ..., 权限n ON 数据库.* TO 用户名。
撤销授权：REVOKE 权限1, ..., 权限n ON 数据库.* FROM 用户名。
查看用户权限：SHOW GRANTS FOR 用户名。
删除用户：DROP USER 用户名。
修改用户密码：USE mysql; UPDATE USER SET authentication_string=PASSWORD('密码') WHERE User='用户名' and Host='IP'; FLUSH PRIVILEGES;。

DCL 操作示例：
-- 查询所有用户
SELECT * FROM mysql.user;

-- 创建用户 zhangsan，密码为 123456
CREATE USER zhangsan@localhost IDENTIFIED BY '123456';

-- 给 zhangsan 用户授权，允许访问 mydb1 数据库的所有表
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb1.* TO zhangsan@localhost;

-- 撤销 zhangsan 用户的授权
REVOKE ALL ON mydb1.* FROM zhangsan@localhost;

-- 删除 zhangsan 用户
DROP USER zhangsan@localhost;

6.数据查询 (DQL)

6.1基本查询：

查询所有列：SELECT * FROM 表名。
查询指定列：SELECT 列名1,列名2, ... FROM 表名。

6.2条件查询：

使用 WHERE 子句添加条件，例如 =, !=, <>, <, <=, >, >= 等。
使用 BETWEEN … AND 查询范围。
使用 IN(set) 查询指定集合。
使用 IS NULL 查询 NULL 值。
使用 AND 和 OR 进行逻辑运算。

6.3模糊查询：

使用 LIKE 关键字和通配符 % 和 _ 进行模糊匹配。

6.4字段控制查询：

使用 DISTINCT 去除重复记录。
使用列名 AS 别名给列名添加别名。
使用函数进行计算，例如 IFNULL。

6.5排序：

使用 ORDER BY 子句进行排序，可以指定 ASC (升序) 或 DESC (降序)。

6.6聚合函数：

使用 COUNT(), MAX(), MIN(), SUM(), AVG() 等函数进行纵向计算。

6.7分组查询：

使用 GROUP BY 子句进行分组，可以使用 HAVING 子句进行分组后过滤。

6.8分页查询：

使用 LIMIT 子句进行分页，例如 LIMIT 起始索引, 查询记录数。

6.9执行顺序：

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT。

DCL 部分操作示例：
-- 查询所有用户
SELECT * FROM mysql.user;

-- 创建用户 zhangsan，密码为 123456
CREATE USER zhangsan@localhost IDENTIFIED BY '123456';

-- 给 zhangsan 用户授权，允许访问 mydb1 数据库的所有表
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb1.* TO zhangsan@localhost;

-- 撤销 zhangsan 用户的授权
REVOKE ALL ON mydb1.* FROM zhangsan@localhost;

-- 删除 zhangsan 用户
DROP USER zhangsan@localhost;

7.完整性约束

7.1约束概念：

约束是作用于表中字段上的规则，用于限制存储在表中的数据，保证数据的正确性、有效性和完整性。

7.2约束分类：

非空约束 (NOT NULL)
主键约束 (PRIMARY KEY)
唯一约束 (UNIQUE)
默认约束 (DEFAULT)
外键约束 (FOREIGN KEY)
检查约束 (CHECK)

7.3主键：

主键是一行数据的唯一标识，要求非空且唯一。
创建表时可以使用 PRIMARY KEY 关键字指定主键。
主键列的值不能为 NULL，也不能重复。

7.4主键自增长：

MySQL 提供主键自动增长功能。
创建表时可以使用 AUTO_INCREMENT 关键字指定主键自增长。

7.5非空约束：

指定非空约束的列不能没有值。
插入记录时必须给添加了非空约束的列指定值。

7.6唯一约束：

保证该字段的所有数据都是唯一、不重复的。
查询数据时可以使用 UNIQUE 索引进行快速检索。

7.7外键约束：

用来让两张表的数据之间建立连接，保证数据的一致性和完整性。
创建表时可以使用 FOREIGN KEY 关键字指定外键。

7.8删除/更新行为：

CASCADE: 级联删除/更新子表记录。
SET NULL: 将外键列设置为 NULL。
NO ACTION/RESTRICT: 不允许删除/更新父表记录。

完整性约束示例：
-- 创建表 stu，使用 sid 作为主键
CREATE TABLE stu (
  sid CHAR(6) PRIMARY KEY,
  sname VARCHAR(20) NOT NULL,
  age INT NOT NULL,
  gender VARCHAR(10) NOT NULL
);

-- 创建表 emp，使用 empno 作为主键，并设置 deptno 为外键，关联 dept 表的 deptno
CREATE TABLE emp (
  empno INT PRIMARY KEY,
  ename VARCHAR(50) NOT NULL,
  deptno INT,
  FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

8.多表查询

8.1联合查询：

合并多个查询结果集。
使用 UNION 或 UNION ALL 合并结果集，UNION 会去除重复记录，UNION ALL 不会去除重复记录。

8.2连接查询：

连接多张表进行查询。
使用 JOIN 关键字连接多张表，包括内连接、外连接（左外连接、右外连接）、自连接。

联合查询和连接查询示例：

多表查询示例：
-- 查询所有学生信息以及他们所在的部门名称
SELECT stu.*, dept.dname FROM stu JOIN dept ON stu.deptno = dept.deptno;

-- 查询所有销售员信息以及他们的部门和部门经理信息
SELECT emp.*, dept.dname, manager.ename AS manager_name FROM emp JOIN dept ON emp.deptno = dept.deptno
JOIN emp manager ON emp.mgr = manager.empno;

8.3子查询：

嵌套查询，即 SELECT 语句中包含 SELECT 语句。
根据子查询结果形式分为标量子查询、列子查询、行子查询、表子查询。

子查询示例：

子查询示例：
-- 查询工资高于平均工资的员工信息
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

-- 查询所有工资高于部门平均工资的员工信息
SELECT * FROM emp WHERE sal > (
  SELECT AVG(sal) FROM emp e WHERE e.deptno = emp.deptno
);

四、MySQL 系统函数

1.数值型函数：

ABS: 求绝对值。
SQRT: 求二次方根。
MOD: 求余数。
CEIL/FLOOR: 向上/向下取整。
RAND: 生成随机数。
SIGN: 返回参数符号。
POW/POWER: 求幂。
SIN/ASIN: 求正弦/反正弦值。
COS/ACOS: 求余弦/反余弦值。
TAN/ATAN: 求正切/反正切值。
COT: 求余切值。

2.字符串函数：

LENGTH: 计算字符串长度。
CONCAT: 合并字符串。
INSERT: 替换字符串。
LOWER/UPPER: 转换大小写。
LEFT/RIGHT: 截取字符串。
TRIM: 去除空格。
REPLACE: 替换字符串。
SUBSTRING: 截取字符串。
REVERSE: 反转字符串。

3.日期和时间函数：

CURDATE/CURRENT_DATE: 返回当前日期。
CURTIME/CURRENT_TIME: 返回当前时间。
NOW/SYSDATE: 返回当前日期和时间。
UNIX_TIMESTAMP: 获取 UNIX 时间戳。
FROM_UNIXTIME: 将 UNIX 时间戳转换为时间格式。
MONTH/MONTHNAME: 获取月份。
DAYNAME/DAYOFWEEK: 获取星期几。
WEEK: 获取周数。
DAYOFYEAR/DAYOFMONTH: 获取年中的天数/月中的天数。
YEAR: 获取年份。
TIME_TO_SEC/SEC_TO_TIME: 时间转换。
DATE_ADD/ADDDATE: 日期相加。
DATE_SUB/SUBDATE: 日期相减。
ADDTIME/SUBTIME: 时间相加/相减

4.流程控制函数：

IF: 如果条件为真，则返回结果1，否则返回结果2。
IFNULL: 如果被检测值为 NULL，则返回默认值，否则返回被检测值。
CASE: 根据不同条件返回不同的结果。

5.分析函数 (MySQL 8.0+):

ROW_NUMBER: 为查询结果生成唯一的序号。
RANK: 为具有相同值的行生成相同的排名。
DENSE_RANK: 为具有相同值的行生成连续的排名。

五、总结：

MySQL 数据库是构建现代 Web 应用程序的核心组成部分，它为我们提供了一种高效、可靠的方式来存储和管理数据。通过学习 MySQL 数据库，我们可以掌握数据定义、数据操作和数据查询的强大功能，并能够运用这些功能构建各种类型的数据库应用程序。

学习 MySQL 数据库，不仅仅是学习一种数据库管理系统，更是学习一种思维方式，一种解决问题的方法。通过学习 MySQL 数据库，我们可以更好地理解数据之间的关系，并能够运用数据库设计范式构建高效、可靠的数据库系统。同时，我们也能够掌握 SQL 语言，一种强大的数据查询和操作工具，它可以帮助我们更有效地处理数据，并从中提取有价值的信息。