第六周作业1-10题
- 完成将server和client端的mysql配置默认字符集为utf8mb4;
设置服务器默认的字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
设置MySQL客户端默认的字符集
vim /etc/my.cnf
# 针对MySQL客户端
[mysql]
default-character-set=utf8mb4
# 针对所有MySQL客户端
[client]
default-character-set=utf8mb4
- 掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin
获取SQL命令的帮助可以通过以下几种途径：
1.使用SQL命令行工具：大多数SQL命令行工具都支持输入help或\h命令，以获取SQL命令的帮助信息。例如，在MySQL命令行工具中，输入\h命令将显示可用的命令列表，输入help 命令名称可以查看该命令的详细信息。
2.查阅SQL文档：SQL有广泛的在线文档资源，例如MySQL官方文档、Oracle官方文档、PostgreSQL官方文档等，这些文档包含了SQL命令的详细介绍和使用方法。
# MySQL官方文档
https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
#MARIADB官方文档
3.通过搜索互联网资源：在互联网上搜索可以找到大量的SQL教程和文章，这些资源通常包含了SQL命令的使用示例和详细解释。
4.参考数据库管理系统的帮助文档：某些数据库管理系统提供了内置的帮助文档，可以在该文档中查找SQL命令的使用方法和语法。
CREATE DATABASE testdb character set utf8 COLLATE utf8_bin;
- 总结mysql常见的数据类型。
MySQL支持许多不同的数据类型，每种数据类型都具有不同的特性和用途。以下是MySQL常见的数据类型：
1.整型（INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT）：用于存储整数值，不同的整型数据类型支持的范围不同。
2.浮点型（FLOAT、DOUBLE、DECIMAL）：用于存储带小数点的数字，不同的浮点型数据类型支持的精度不同。
3.字符串型（CHAR、VARCHAR、TEXT、BLOB）：用于存储字符型数据，不同的字符串型数据类型支持的长度和存储方式不同。
4.日期/时间型（DATE、TIME、DATETIME、TIMESTAMP、YEAR）：用于存储日期和时间信息，不同的日期/时间型数据类型支持的精度和存储方式不同。
5.枚举和集合型（ENUM、SET）：用于存储具有限定值的数据，ENUM类型只能存储单个值，而SET类型可以存储多个值
在设计数据库时选择正确的数据类型可以使数据库更高效地处理数据，提高数据库性能，在设计MySQL数据库时需要根据业务场景和实际需求选择合适的数据类型。
- 创建一个主机表host,放在testdb中，要求字段 1) 主键自增id 无符号, tinyint. 2) hostname可变字符长度256，可为空。。3）ip 可变字符长度256，可为空。4）账号，可变字符长度256，可为空。5）密码，可变字符长度256，可为空。6）创建时间，时间类型，非空。7）更新时间，时间类型，默认当前时间。8）区域，只能在华南，华北，华东，三个区域之一。9）端口，无符号整数，可为空。10）外网地址，可变字符长度256，可为空。11）内网地址，可变字符长度256，可为空。
USE testdb;
CREATE TABLE host(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
hostname VARCHAR(256),
ip VARCHAR(256),
account VARCHAR(256),
password VARCHAR(256),
create_time DATETIME NOT NULL,
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
region ENUM('华南'，'华北'，'华东') NOT NULL,
port INT UNSIGNED,
external_address VARCHAR(256),
internal_address VARCHAR(256)
);
- 给testdb.host表中添加多条数据。
USE testdb;
INSERT INTO host (hostname, ip, account, password, create_time, region, port, external_address, internal_address)
VALUES ('host1', '10.1.1.1', 'root', 'password123', '2023-06-01 08:00:00', '华南', 3306, 'www.magedu.com', NULL),
('host2', '10.1.1.2', 'root', 'password456', '2023-06-01 09:00:00', '华南', 3306, 'www. magedu.com', NULL),
('host3', '10.1.1.3', 'root', 'password789', '2023-06-01 10:00:00', '华东', NULL, NULL, '10.1.1.1'),
('host4', '10.1.1.4', NULL, NULL, '2023-06-01 11:00:00', '华北', NULL, 'www. magedu.com', '10.1.1.2');
- 根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。
DDL（Data Definition Language）是数据库语言中的一类，用于定义数据库结构、模式和约束等。常用的DDL命令有以下几种：
CREATE：用于创建数据库对象，如表、视图、索引、存储过程、触发器等。
ALTER：用于修改数据库对象，如修改表结构、修改视图、修改索引等。
DROP：用于删除数据库对象，如删除表、删除视图、删除存储过程等。
TRUNCATE：用于清空表中的数据。
DDL操作通常由数据库管理员或开发人员使用，用于创建和维护数据库对象以确保数据的正确性和一致性。与DDL相对的是DML（Data Manipulation Language），用于操作数据库中的数据，如查询、插入、更新和删除数据等。
删除表
DROP TABLE 'host';
修改表名
ALTER TABLE host RENAME 'machine';
添加字段
ALTER TABLE machine ADD phone VARCHAR(11) AFTER password;
修改字段类型
ALTER TABLE machine MODIFY phone int;
修改字段名称和类型
ALTER TABLE machine CHANGE COLUMN phone mobile char(11);
删除字段
ALTER TABLE machine DROP COLUMN mobile;
修改字符集
ALTER TABLE machine character set utf8;
修改数据类型和字符集
ALTER TABLE machine CHANGE id id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT;
删除字段
ALTER TABLE machine DROP monile;
查看表结构
DESC machine;
DML（Data Manipulation Language）是数据库语言中的一类，用于操作数据库中的数据，如查询、插入、更新和删除数据等。常见的DML命令有以下几种：
SELECT：用于查询数据表中的数据。
INSERT：用于向数据表中插入新的数据。
UPDATE：用于修改数据表中的数据。
DELETE：用于删除数据表中的数据。
DML操作通常由应用程序或最终用户使用，并且是最经常执行的操作之一。与DML相对的是DDL（Data Definition Language），用于定义数据库结构、模式和约束等。
简单查询
SELECT * FROM machine WHERE id<3;
SELECT * FROM machine WHERE region='华东';
SELECT * FROM machine WHERE account IS NULL;
SELECT * FROM machine WHERE ip like '192%';
记录去重
SELECT DISTINCT region from machine;
判断是否为NULL
SELECT * FROM machine where internal_address IS NULL;
字段别名
SELECT hostname AS 主机名, ip, account AS 账号, password AS 密码, create_time AS 创建时间, region AS 地区, port AS 端口号, external_address AS 外部地址, internal_address AS 内部地址 FROM machine
分页查询
SELECT * FROM machine LIMIT 0,3;
SELECT * FROM machine LIMIT 3;
分组统计
SELECT region, count(*) AS 数量 FROM machine GROUP BY region;
- 导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例。
判断是否为NULL
ifnull函数判断指定的字段是否为空值，如果空值则指定默认值
mysql> select stuid,name,ifnull(classID,'无班级') from students where classid is null;
+-------+-------------+-----------------------------+
| stuid | name | ifnull(classID,'无班级') |
+-------+-------------+-----------------------------+
| 24 | Xu Xian | 无班级 |
| 25 | Sun Dasheng | 无班级 |
+-------+-------------+-----------------------------+
2 rows in set (0.00 sec)
聚合函数
排序
跳过前三个只显示后续的2个
先过滤再排序
多列排序
正序排列时将null的记录排列到最后
- 基于hellodb 库, 总结子查询，关联查询 ，交叉连接，内连接，左连接，右连接，完全连接，自连接。
子查询是指将一个SQL语句嵌套在另一个SQL语句中，并使用内部查询的结果作为外部查询的条件来筛选数据。子查询通常用于需要动态获取条件的情况下，比如在WHERE子句中使用子查询来动态筛选数据。
一个基本的子查询语法如下：
SELECT column
FROM table1
WHERE column_name operator
(SELECT column_name FROM table2 WHERE condition);
在这个语法中，内部查询通过SELECT、FROM和WHERE等关键字来实现条件筛选，而外部查询则通过WHERE子句中的operator运算符和内部查询的结果进行匹配。
例如，如果我们要从orders表中获取id列包含在customers表格的id列中的所有订单信息，则可以使用以下的SQL子查询：
SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers);
联合查询（UNION） 是 SQL 中一种常见的数据合并技术，用于在两个或多个 SELECT 语句的结果中取并集。
如果您需要从两个或多个表格中检索相似的数据，可使用 UNION 进行合并。 联合查询将多个 SELECT 语句的结果组合成一个结果集，其中每个 SELECT 语句产生的行数和列数必须相同。 联合查询返回的结果集中不包含重复的行。
以下是 UNION 查询的示例，假设我们有两个表 students 和 teachers：
我们可以使用如下 SQL 语句来进行联合查询：
SELECT id, name FROM students
UNION
SELECT id, name FROM teachers;
内连接（INNER JOIN）是 SQL 中常见的表格连接技术之一，也是最常用的一种。它使用一个或多个列作为键在两个或多个表格之间建立连接，返回符合连接条件的行。
内连接根据 SELECT 语句中指定的连接条件将两个或多个表格中的行匹配，并将符合条件的行连接起来，生成一个包含联接后结果的表格。内连接只返回那些在至少两个匹配表格中都存在的行。
可以使用如下 SQL 语句来进行 INNER JOIN 查询：
SELECT t1.id, t1.name, t2.age
FROM t1
INNER JOIN t2 ON t1.id = t2.id;
左连接（LEFT JOIN 或 LEFT OUTER JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及左表格中未匹配的行。
具体来说，左连接会先选取左表格中的所有行，并在右表格中查找与左表格中的每一行匹配的行。如果找到了匹配的行，则将两个表格中的数据合并成一行；如果没有找到匹配的行，则将左表格中的这一行与 NULL 值合并成一行。因此，左连接会保留左表格中未匹配的行。
可以使用如下 SQL 语句来进行左连接查询：
SELECT t1.id, t1.name, t2.age
FROM t1
LEFT JOIN t2 ON t1.id = t2.id;
右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及右表格中未匹配的行。
具体来说，右连接会先选取右表格中的所有行，并在左表格中查找与右表格中的每一行匹配的行。如果找到了匹配的行，则将两个表格中的数据合并成一行；如果没有找到匹配的行，则将右表格中的这一行与 NULL 值合并成一行。因此，右连接会保留右表格中未匹配的行。
可以使用如下 SQL 语句来进行右连接查询：
SELECT t1.id, t1.name, t2.age
FROM t1
RIGHT JOIN t2 ON t1.id = t2.id;
完全连接（FULL OUTER JOIN 或 FULL JOIN）是 SQL 中的一种表格连接技术，用于连接两个或多个表格并返回符合连接条件的所有行以及左右表格中未匹配的行。
具体来说，完全连接会先选取左表格和右表格中的所有行，并在彼此之间查找是否有匹配的行。如果找到了匹配的行，则将两个表格中的数据合并成一行；如果没有找到匹配的行，则将该行与 NULL 值合并成一行。因此，完全连接会保留左右表格中未匹配的行。
可以使用如下 SQL 语句来进行完全连接查询：
SELECT t1.id, t1.name, t2.age
FROM t1
FULL JOIN t2 ON t1.id = t2.id;
自连接（Self Join）是一种在同一个表内进行连接的操作。具体来说，自连接是指将一个表格视为两个不同的表格，并在这两个“表格”之间进行连接。
自连接有时候可以用来解决一些比较复杂的数据查询问题，例如可以用自连接查询出一个员工与其经理的名字。这个查询需要在同一张表中查找员工和经理，并将它们关联起来。
9.总结SELECT语句处理顺序
一条SELECT语句的处理顺序主要分为以下几个步骤：
1.FROM子句：先执行FROM子句，从指定的表或视图中获取数据。
2.WHERE子句：在FROM子句获取的数据上执行WHERE子句，过滤出符合条件的行。需要注意的是，WHERE子句不能使用SELECT子句中定义的别名，因为它们还未被计算出来。
3.GROUP BY子句：在WHERE子句过滤出的数据上执行GROUP BY子句，将数据按照指定列进行分组，然后对每个分组执行聚合函数，如SUM、AVG、MAX、MIN等。
4.HAVING子句：在GROUP BY子句分组后的结果上再次进行过滤，筛选出符合条件的分组。
5.SELECT子句：计算所有SELECT子句中的表达式，包括聚合函数，生成最终结果集。需要注意的是，SELECT子句中使用的列别名只有在整个SELECT语句执行完成后才会被计算出来，因此不能在WHERE子句或GROUP BY子句中使用别名。
6.ORDER BY子句：在最终结果集上执行ORDER BY子句，按照指定的列进行排序，得到最终的查询结果。
10. 总结mysql事件管理,用户管理，权限管理。
MySQL事件是一种定时任务，可以在指定时间点自动执行一些指定的SQL语句。使用MySQL事件管理功能可以方便地实现各种定时操作，比如备份、统计、清理数据等。
下面是MySQL事件管理的一些重要操作：
创建事件：使用CREATE EVENT语句来创建一个新的事件，语法格式如下：
CREATE EVENT event_name
ON SCHEDULE schedule
DO
event_body
其中event_name表示事件的名称，schedule设置事件的定时计划，event_body中指定需要执行的SQL语句
修改事件：使用ALTER EVENT语句来修改已有的事件，语法格式如下：
ALTER EVENT event_name
ON SCHEDULE schedule
DO
event_body
删除事件：使用DROP EVENT语句来删除已有的事件，语法格式如下：
DROP EVENT event_name
查看事件：使用SHOW EVENTS语句来查看当前数据库中所有的事件信息。
启用/禁用事件：使用ENABLE/DISABLE命令来启用或禁用事件，语法格式如下：
ALTER EVENT event_name ENABLE;
ALTER EVENT event_name DISABLE;
需要注意的是，事件的创建和修改需要管理员或具有SUPER权限的用户来执行，否则会提示权限不足的错误。此外，MySQL事件只能在MySQL服务器打开了事件调度器的情况下使用，如果事件调度器被禁用，则无法使用MySQL事件功能。
MySQL用户管理是指对数据库用户账号进行创建、修改、删除和权限控制等操作的过程。关于MySQL用户管理的一些基本操作：
创建用户：使用CREATE USER语句来创建一个新的MySQL用户，语法格式如下：
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
其中'username'是新用户的用户名，'localhost'表示该用户只能在本地连接到MySQL服务器，IDENTIFIED BY 'password'指定用户的密码。
修改用户：使用ALTER USER语句来修改已有的MySQL用户信息，语法格式如下：
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
删除用户：使用DROP USER语句来删除已有的MySQL用户，语法格式如下：
DROP USER 'username'@'localhost';
授权：使用GRANT语句为用户授予不同的权限，语法格式如下：
GRANT permission ON database.table TO 'username'@'localhost';
其中permission可以是SELECT、INSERT、UPDATE、DELETE等操作，database.table表示要授权的数据库和表名，'username'@'localhost'表示要授权的用户和主机限制条件。例如，授权mage用户在mydb数据库下的数据表mytable上执行SELECT和INSERT操作：
GRANT SELECT, INSERT ON mydb.mytable TO 'mage'@'localhost';
撤销授权：使用REVOKE语句撤销已有的授权，语法格式如下：
REVOKE permission ON database.table FROM 'username'@'localhost';
需要注意的是，用户管理涉及到数据库安全性问题，因此在进行创建、修改、删除和授权操作时需要谨慎，避免对数据库造成不必要的影响或安全隐患。建议仅为需要访问数据库的用户授予最小权限，定期清理无用的用户账号，以确保数据库的安全可靠。
MySQL权限管理是为了保护数据库的安全和完整性，授权用户对数据库的某些操作有不同程度的访问权限。MySQL提供了多种类型的权限，包括全局权限、数据库级权限、表级权限、列级权限等。
以下是关于MySQL权限管理的一些基本操作：
授予权限：使用GRANT语句为用户赋予相应的权限，语法格式如下：
GRANT permission ON database.table TO 'username'@'localhost';
其中permission可以是SELECT、INSERT、UPDATE、DELETE等操作，database.table表示要授权的数据库和表名，'username'@'localhost'表示要授权的用户和主机限制条件。
例如，授权jack用户在mydb数据库下的数据表mytable上执行SELECT和INSERT操作：
GRANT SELECT, INSERT ON mydb.mytable TO 'jack'@'localhost';
撤销权限：使用REVOKE语句撤销已有的权限，语法格式如下：
REVOKE permission ON database.table FROM 'username'@'localhost';
查看权限：使用SHOW GRANTS语句来查看当前用户的权限信息，例如：
SHOW GRANTS FOR 'mage'@'localhost';
全局权限：全局权限适用于所有数据库和表，使用GRANT语句授予全局权限的语法如下：
GRANT permission ON *.* TO 'username'@'localhost';
例如，授权mage用户具有SELECT和INSERT全局权限，则执行以下命令：
GRANT SELECT, INSERT ON *.* TO 'mage'@'localhost';
数据库级权限和表级权限：除了全局权限，MySQL还支持数据库级权限和表级权限。使用GRANT语句授予数据库级和表级权限的语法如下：
# 授予某个数据库的权限
GRANT permission ON database.* TO 'username'@'localhost';
# 授予某个数据表的权限
GRANT permission ON database.table TO 'username'@'localhost';
例如，授权mage用户在mydb数据库下具有SELECT和INSERT权限，则执行以下命令
GRANT SELECT, INSERT ON mydb.* TO 'mage'@'localhost';
或者授权mage用户在mydb数据库中的mytable数据表上具有SELECT和INSERT权限，则执行以下命令：
GRANT SELECT, INSERT ON mydb.mytable TO 'mage'@'localhost';.
需要注意的是，在进行MySQL权限管理时，需要谨慎操作，避免对数据库造成不必要的影响或安全隐患。建议只授予必要的权限，并定期清理不必要的用户和权限，确保数据库的安全可靠
浙公网安备 33010602011771号