本文共 4786 字，大约阅读时间需要 15 分钟。

MySQL服务操作与数据库管理

MySQL服务操作

启动MySQL服务

net start mysql;

停止MySQL服务

net stop mysql;

登录MySQL

• 显式登录（密码可见）：

  mysql -u用户名 -p密码

• 隐式登录（密码不可见）：

  mysql -u用户名 -p密码

• 登录指定IP地址的MySQL：

  mysql -hIP地址 -u用户名 -p密码

• 登录指定IP地址的MySQL（可用 alternatives）：

  mysql --host=IP地址 --user=root --password=root

• 退出MySQL：

  exit quit

SQL注释

单行注释

-- 这是单行注释，后面必须有空格# 这是单行注释

多行注释

/* 这是多行注释，可以和Java一样使用 */

DDL：操作数据库

创建数据库

• 创建新数据库：

  create database 数据库名称;

• 检查数据库是否存在：

  create database if not exists 数据库名称;

• 创建指定字符集的数据库：

  create database 数据库名称 character set 字符集名称;

• 创建指定字符集的数据库并检查存在性：

  create database if not exists 数据库名称 character set 字符集名称;

查询数据库信息

• 查询所有数据库：

  show databases;

• 查询数据库字符集：

  show create database 数据库名称;

• 查询当前数据库：

  select database();

更新数据库字符集

alter database 数据库名称 character set 字符集名称;

删除数据库

• 删除指定数据库：

  drop database 数据库名称;

• 检查数据库存在性后删除：

  drop database if exists 数据库名称;

使用数据库

use 数据库名称;

DDL：操作表

创建表

• 创建新表：

  create table 表名(    列名1 数据类型1,    ...    列名n 数据类型n);

• 创建表的示例：

  create table student(    id int,    name varchar(10),    age int,    score double(4,1),    birthday date,    insert_time timestamp);

• 复制表结构：

  create table 表名 like 被复制的表名;

查询表结构

• 查询表的所有列：

  desc 表名;

更新表操作

• 修改表名：

  alter table 表名 rename to 新表名;

• 修改表字符集：

  alter table 表名 character set 字符集名称;

• 添加单列：

  alter table 表名 add 列名 数据类型;

• 添加多列：

  alter table 表名 add(列名1 数据类型1, ...);

• 修改列数据类型：

  alter table 表名 modify 要修改的列名 修改后的数据类型;

• 修改列名称和数据类型：

  alter table 表名 change 要修改的列名 修改后的列名 修改后的数据类型;

• 删除单列：

  alter table 表名 drop 列名;

• 删除表：

  drop table 表名;

• 检查表存在性后删除：

  drop table if exists 表名;

DML：操作数据

插入数据

• 插入单列数据：

  insert into 表名(列1, ...) values(值1, ...);

• 插入所有列数据：

  insert into 表名 values(值1, ..., 值n);

更新数据

• 更新指定列数据：

  update 表名 set 列名1 = 值1, ... where 条件;

• 更新多行数据：

  update 表名 set 列名1 = 值1, ... where 条件;

删除数据

• 删除所有数据：

  delete from 表名;

• 根据条件删除数据：

  delete from 表名 where 条件;

DQL：查询数据

基础查询

• 查询所有数据：

  select * from 表名;

• 查询指定列数据：

  select 要查询的列名1, ..., 要查询的列名n from 表名;

• 去重复查询：

  select distinct 要查询的列名1, ..., 要查询的列名n from 表名;

• 替换null值：

  select ifnull(要查询的列名1, 替换后的值) from 表名;

• 给列起别名：

  select 列名1 as 别名1, 列名2 as 别名2 from 表名;

条件查询

• 大于条件：

  select * from 表名 where 列名 > 指定参数;

• 大于等于条件：

  select * from 表名 where 列名 >= 指定参数;

• 等于条件（注意 MySQL 不支持 ==，使用 =）：

  select * from 表名 where 列名 = 指定参数;

• 不等于条件（注意使用 !=）：

  select * from 表名 where 列名 != 指定参数;

• 范围条件（大于等于和小于等于）：

  select * from 表名 where 列名 >= 指定参数1 and 列名 <= 指定参数2;

• 在列表中（IN）：

  select * from 表名 where 列名 in (指定参数1, 指定参数2);

• 查询null值：

  select * from 表名 where 列名 is null;

• 查询不为null值：

  select * from 表名 where 列名 is not null;

• 模糊查询（如三个字符）：

  select * from 表名 where 列名 like '___';

• 任意字符长度：

  select * from 表名 where 列名 like '%';

• 指定开头和结尾：

  select * from 表名 where 列名 like '_指定字符%';

排序查询

• 升序排序：

  select * from 表名 order by 列名1, 列名2;

• 降序排序：

  select * from 表名 order by 列名1 desc, 列名2 desc;

聚合函数

• 计算记录数量：

  select count(*) from 表名;

• 计算指定列的最大值：

  select max(列) from 表名;

• 计算指定列的最小值：

  select min(列) from 表名;

• 计算指定列的和：

  select sum(列) from 表名;

• 计算指定列的平均值：

  select avg(列) from 表名;

分组查询

• 根据指定列分组：

  select 列 from 表名 group by 分组条件;

• 在having子句中使用聚合函数：

  select * from 表名 group by 分组条件 having 条件;

• 在where子句中使用条件：

  select * from 表名 where 条件 group by 分组条件 having 条件;

分页查询

• 限制显示条数：

  select * from 表名 limit 起始索引, 显示条数;

事务操作

事务提交方式

• 查询事务提交方式：

  select @@autocommit;

• 修改事务提交方式：

  set @@autocommit = 参数;

• 手动提交事务：

  commit;

事务隔离级别

• 查询当前隔离级别：

  select @@tx_isolation;

• 修改数据库隔离级别：

  set global transaction isolation level 隔离级别;

• 开始事务：

  start transaction;

权限管理

用户管理

• 切换到mysql数据库：

  use mysql;

• 查询用户表：

  select * from user;

• 创建用户（需管理员权限）：

  create user '用户名'@'主机名' identified by '密码';

• 删除用户：

  drop user '用户名'@'主机名';

• 修改用户密码：

  update user set password = password('新密码') where user = '用户名';

• 使用 set 密码（可选）：

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

授权管理

• 查询权限：

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

• 授予权限（*表示所有权限）：

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

• 撤销权限：

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

数据库备份与还原

备份数据库

mysqldump -u用户名 -p密码 数据库 [表名称] > 保存路径/文件名.sql;

还原数据库

mysql -u用户名 -p密码

use 数据库名称;

source 保存路径/文件名.sql;

多表查询

内连接

• 隐式内连接：

  select * from 表1, 表2 where 表1.关联字段 = 表2.关联字段;

• 显式内连接：

  select * from 表1 inner join 表2 on 表1.关联字段 = 表2.关联字段;

外连接

• 左外连接：

  select * from 表1 left outer join 表2 on 表1.关联字段 = 表2.关联字段;

• 右外连接：

  select * from 表1 right outer join 表2 on 表1.关联字段 = 表2.关联字段;

子查询

• 查询最高成绩的学生：

  select * from 学生表 where 学生表.成绩 = (select max(成绩) from 学生表);

• 查询成绩小于平均成绩的学生：

  select * from 学生表 where 学生表.成绩 < (select avg(成绩) from 学生表);

• 查询学习语文或数学的学生：

  select * from 学生表 where 学生表.课程id in (select 课程id from 课程表 where name = '语文' or name = '数学');

• 查询2020年9月1日入职的学生及班级信息：

  select * from 班级表, (select * from 学生表 where 入职时间 = '2020-9-1') as 学生表 where 班级表.id = 学生表.班级id;

• 查询班级人数及相关信息：

  select 班级表.班级id, 班级表.班级名称, (select count(id) from 学生表 group by 班级id) as 班级人数 from 班级表;

事务管理

事务提交方式

• 查询事务提交方式：

  select @@autocommit;

• 修改事务提交方式：

  set @@autocommit = 参数;

• 手动提交事务：

  commit;

事务隔离级别

• 查询当前隔离级别：

  select @@tx_isolation;

• 修改数据库隔离级别：

  set global transaction isolation level 隔离级别;

• 开始事务：

  start transaction;

通过以上内容，可以全面了解MySQL的操作与管理，包括数据库和表的创建、查询、修改等操作，权限管理，以及事务操作等。