mysql-视图特性，用户管理和使用c连接

mysql视图特性

一、视图基本概念

定义：视图（view）是一个虚拟表，内容由查询定义。与真实表类似，包含列和行数据。
特点：
- 视图数据与基表（base table）双向影响：修改视图数据会影响基表，基表数据变化也会同步到视图。
- 不存储实际数据，仅保存查询逻辑。

二、视图基本操作

创建视图

create view 视图名 as select语句;

案例：

-- 创建联合emp和dept表的视图  
create view v_ename_dname as  
select ename, dname from emp, dept where emp.deptno = dept.deptno;

查询视图：

select * from v_ename_dname order by dname;

修改视图数据（影响基表）

-- 更新视图中的ename字段  
update v_ename_dname set ename='test' where ename='clark';  
-- 基表emp中对应数据也被修改  
select * from emp where ename='test';

修改基表数据（影响视图）

-- 修改基表emp的deptno字段  
update emp set deptno=10 where ename='james';  
-- 视图中对应数据同步更新  
select * from v_ename_dname where ename='james';

删除视图
```
drop view 视图名;  
```

三、视图规则与限制

命名唯一性：视图名必须唯一，不可与表或其他视图重名。
性能影响：视图基于复杂查询时，可能降低查询效率。
功能限制：
- 不支持索引、触发器、默认值。
- 视图中使用order by时，若外部查询再次使用order by，视图的排序会被覆盖。
安全性：通过视图可限制用户访问特定数据（如隐藏敏感字段）。
联合使用：视图可与表一起使用，例如在复杂查询中嵌套。

四、实战案例

牛客oj题：针对actor表创建视图actor_name view

-- 创建视图，仅包含actor表的first_name和last_name  
create view actor_name_view as  
select first_name, last_name from actor;

五、重难点总结

视图与基表的数据同步机制：

视图数据变动会直接映射到基表，反之亦然。

示例：

update v_ename_dname set ename='test' where ename='clark'; -- 修改视图影响基表  
update emp set deptno=10 where ename='james';            -- 修改基表影响视图

视图的查询优化：
- 避免基于复杂查询创建视图（如多表连接、聚合函数），可能导致性能下降。
- 优先选择简单查询或已优化的子查询。
安全性管理：
- 通过视图限制用户访问特定列或行（如屏蔽薪资字段）。
- 示例：
```
create view emp_public as select ename, job from emp; -- 仅公开姓名和岗位  
```
视图的排序覆盖问题：
- 若视图定义包含order by，外部查询的order by会覆盖视图原有排序。

六、常见问题与解决方案

问题1：视图更新失败
原因：视图包含聚合函数、分组（group by）、去重（distinct）等操作时，可能无法直接更新。
解决：仅对简单查询（如单表select）创建的视图进行更新操作。
问题2：视图查询性能差
原因：视图基于复杂查询或未优化的子查询。
解决：
- 对基表添加索引。
- 将复杂查询拆分为多个简单视图。

mysql用户管理

一、用户信息与基本操作

用户信息存储：
- 用户数据存储在系统数据库mysql的user表中。
- 关键字段：
  - host：允许登录的主机（localhost仅限本机，%允许任意主机）。
  - user：用户名。
  - authentication_string：加密后的密码。
  - *_priv：权限字段（如select_priv、insert_priv）。
创建用户：
```
create user '用户名'@'主机名' identified by '密码';  
```
示例：
```
create user 'wbb'@'localhost' identified by '12345678';  
```
注意：
- 密码需满足复杂度策略（可通过show variables like 'validate_password%';查看策略）。
- 避免使用%作为主机名（开放远程登录风险高）。
删除用户：
```
drop user '用户名'@'主机名';  
```
示例：
```
drop user 'whb'@'localhost';  
```
常见错误：未指定主机名（如drop user whb）会导致删除失败。

修改密码：

用户自改密码：

set password = password('新密码');

root修改其他用户密码：

set password for '用户名'@'主机名' = password('新密码');

示例：

set password for 'wbb'@'localhost' = password('87654321');

二、权限管理

权限分类：
- 数据操作：select、insert、update、delete。
- 结构操作：create、alter、drop。
- 管理权限：grant option（授权权限）、super（管理员权限）。

授权语法：

grant 权限列表 on 数据库.对象 to '用户名'@'主机名';

常用通配符：

*.*：所有数据库的所有对象。
test.*：test库的所有表。
test.student：test库的student表。

示例：

grant select, insert on test.* to 'whb'@'localhost';  
grant all privileges on *.* to 'admin'@'%' with grant option;

回收权限：

revoke 权限列表 on 数据库.对象 from '用户名'@'主机名';

示例：

revoke delete on test.* from 'whb'@'localhost';  
revoke all privileges on *.* from 'admin'@'%';

查看权限：

show grants for '用户名'@'主机名';

输出示例：

+-----------------------------------------------------+  
| Grants for whb@localhost                             |  
+-----------------------------------------------------+  
| GRANT USAGE ON *.* TO 'whb'@'localhost'              |  
| GRANT SELECT, INSERT ON `test`.* TO 'whb'@'localhost' |  
+-----------------------------------------------------+

权限刷新：

flush privileges;  -- 修改权限后强制刷新（多数情况自动生效）

三、实战案例与注意事项

场景1：限制用户访问范围

创建仅能访问test库的用户：

create user 'dev'@'192.168.1.%' identified by 'secure_pass';  
grant select, insert, update on test.* to 'dev'@'192.168.1.%';

场景2：禁止用户删除数据
- 赋予select、insert权限但不给delete：
```
grant select, insert on sales.* to 'user1'@'localhost';  
```
安全建议：
- 避免使用root账户进行日常操作。
- 限制用户主机范围（如仅允许内网IP）。
- 定期审计权限（show grants）。

四、重难点总结

主机名的作用：
- localhost限制本地访问，%允许任意远程访问。
- 生产环境建议绑定具体IP或域名。
权限分配原则：
- 最小权限原则：仅授予用户必要权限。
- 避免all privileges：除非完全信任用户。
密码策略管理：
- 修改密码复杂度规则（如set global validate_password_policy=low;）。
- 定期更新密码。
常见错误处理：
- 权限未生效：执行flush privileges或重新登录。
- 无法删除用户：确保主机名与创建时一致。

mysql使用c语言连接

一、准备工作

安装mysql connector/c库
- 从mysql官网下载对应平台的connector/c库。
- 库文件包含：
  - include/：头文件（如mysql.h）。
  - lib/：静态库（.a）和动态库（.so）。
验证mysql服务状态
- 确保mysql服务已启动：
```
systemctl status mysql  
```

二、代码示例与接口详解

基础代码结构

#include <stdio.h>  
#include <mysql.h>  int main() {  // 初始化mysql连接句柄  mysql *conn = mysql_init(null);  if (!conn) {  fprintf(stderr, "mysql_init failed\n");  return 1;  }  // 连接数据库  if (!mysql_real_connect(  conn,                   // mysql句柄  "localhost",            // 主机名  "root",                 // 用户名  "password",             // 密码  "testdb",               // 数据库名  0,                      // 端口（默认3306）  null,                   // unix_socket  0                       // 客户端标志  )) {  fprintf(stderr, "连接失败: %s\n", mysql_error(conn));  mysql_close(conn);  return 1;  }  // 设置字符集为utf8（避免中文乱码）  mysql_set_character_set(conn, "utf8");  // 执行sql查询  if (mysql_query(conn, "select * from student")) {  fprintf(stderr, "查询失败: %s\n", mysql_error(conn));  mysql_close(conn);  return 1;  }  // 获取查询结果  mysql_result *result = mysql_store_result(conn);  if (!result) {  fprintf(stderr, "获取结果失败: %s\n", mysql_error(conn));  mysql_close(conn);  return 1;  }  // 输出结果  int num_fields = mysql_num_fields(result);  mysql_row row;  while ((row = mysql_fetch_row(result))) {  for (int i = 0; i < num_fields; i++) {  printf("%s\t", row[i] ? row[i] : "null");  }  printf("\n");  }  // 释放资源  mysql_free_result(result);  mysql_close(conn);  return 0;  
}

关键api函数
- mysql_init：初始化mysql连接句柄。
- mysql_real_connect：建立数据库连接，需指定主机、用户、密码、数据库名等参数。
- mysql_set_character_set：设置字符集（必做，否则中文乱码）。
- mysql_query：执行sql语句（如查询、插入、更新）。
- mysql_store_result：保存查询结果到mysql_result对象。
- mysql_fetch_row：逐行读取结果数据。
- mysql_free_result和mysql_close：释放资源并关闭连接。

三、编译与运行

编译命令
```
gcc -o app app.c -i/path/to/mysql/include -l/path/to/mysql/lib -lmysqlclient  
```
- -i：指定头文件路径（include/目录）。
- -l：指定库文件路径（lib/目录）。
- -lmysqlclient：链接mysql客户端库。
解决动态库加载问题
- 若运行时报错libmysqlclient.so.18: cannot open shared object file，需设置动态库路径：
```
export ld_library_path=/path/to/mysql/lib:$ld_library_path  
```

四、重难点与常见问题

字符集设置
- 默认字符集为latin1，需在连接后调用mysql_set_character_set(conn, "utf8")避免中文乱码。
内存管理
- 必须释放资源：
  - mysql_free_result(result)：释放查询结果内存。
  - mysql_close(conn)：关闭连接并释放句柄。
- 内存泄漏是常见错误，需严格检查资源释放逻辑。

错误处理

所有mysql api调用后需检查返回值，使用mysql_error(conn)获取错误信息。

示例：

if (mysql_query(conn, "select * from table")) {  fprintf(stderr, "error: %s\n", mysql_error(conn));  
}

事务操作
- 开启事务：mysql_autocommit(conn, 0)（0表示关闭自动提交）。
- 提交事务：mysql_commit(conn)。
- 回滚事务：mysql_rollback(conn)。

五、实战注意事项

连接参数校验
- 确保主机名、用户名、密码、数据库名正确。
- 若连接远程数据库，需在mysql服务端配置允许远程访问（如修改bind-address）。
sql注入防范
- 避免直接拼接sql语句，使用预处理语句（mysql_stmt_prepare）。
性能优化
- 批量操作时，使用事务减少io次数。
- 查询大量数据时，分页处理（limit子句）。