跳至主要內容

05. 命令行客户端MySQL的使用

Frankie.Lee2024年10月28日大约 7 分钟

05. 命令行客户端MySQL的使用

目标

  • 能够知道使用命令行连接数据库命令
  • 能够写出增、删、改、查的SQL语句

Navicat操作MYSQL

1. 登录和登出数据库

MySQL服务器安装完成之后，默认就已经安装了命令行客户端,在连接MySQL服务器之前需要知道MySQL服务器的IP地址及对应的端口(默认3306)。 Windows服务器可以通过ipconfig查看IP地址，Linux中可以通过ifconfig查看IP地址。

1. 登录数据库

  • 连接(登录)命令：

    • mysql –hhost –u用户名 –p密码 –P端口号

      • 说明： host表示服务器的IP地址或者域名,不填则表示为本机

      • P端口号不加时则表示默认3306端口 image-20230210103104661

  • 登录成功后, 输入如下命令查看效果

    # 显示当前时间
select now();

2. 登出(退出)数据库

quit 或 exit

2. 数据库操作的SQL语句

  1. SQL 语言中的注释
单行注释：
格式：-- 注释内容
多行注释
格式：/* 注释内容 */
  1. 查看所有数据库
show databases;
  1. 创建数据库
-- 语法：create database 数据库名 charset=utf8;
create database db_python charset=utf8;
-- 指定字符集
create database db_python CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
  1. 使用数据库
-- 语法： use 数据库名;
use db_python
  1. 查看当前使用的数据库
select database();
  1. 查看建库语句
-- 语法：show create database 数据库名
show create database db_python;
  1. 删除数据库-慎重
-- 语法：drop database 数据库名
drop database db_python;

3. 表结构操作的SQL语句

操作数据表之前要选通过use打开对应的数据库

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

    show tables;

  2. 创建表

    -- 语法：
create table 表名(
    字段名称 数据类型 可选的约束条件,
    column1 datatype contrai,
    ...
);

-- 例：
create table students(
     id int unsigned primary key auto_increment,
     name varchar(20) not null,
     age tinyint unsigned default 0,
     height decimal(5,2),
     gender enum('男','女','人妖','保密')
);

  3. 查看表的创建语句

    -- show create table 表名
-- 例：
show create table students;

  4. 查看表结构

    desc 表名

  5. 修改表-添加字段

    -- alter table 表名 add 列名 类型 约束;
-- 例：
alter table students add birthday datetime;

  6. 修改表-修改字段类型

    -- alter table 表名 modify 列名 类型 约束;
-- 例：
alter table students modify birthday date not null;

-- 说明:
-- modify: 只能修改字段类型或者约束，不能修改字段名

  7. 修改表-修改字段名和字段类型

    -- alter table 表名 change 原名 新名 类型及约束;
-- 例：
alter table students change birthday birth datetime not null;

-- 说明:
-- change: 既能对字段重命名又能修改字段类型还能修改约束

  8. 修改表-删除字段

    -- alter table 表名 drop 列名;
-- 例：
alter table students drop birthday;

  9. 删除表

    -- drop table 表名;
-- 例：
drop table students;

4. 表数据操作的SQL语句

如果数据库名、表名、字段名，与sql关键字重复，查询时可以使用反引号 ` 来修饰该名称

  1. 查询数据

    -- 1. 查询所有列
select * from 表名;
-- 例：
select * from students;
-- 2. 查询指定列
select 列1, 列2, ... from 表名;
-- 例：
select id, name from students;

  2. 添加数据

    -- 1. 全列插入：值的顺序与表结构字段的顺序完全一一对应
-- 主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null或者default)
-- 在全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值
-- insert into 表名 values (...)
-- 例:
insert into students values(0, 'xx', default, default, '男');
-- 2. 部分列插入：值的顺序与给出的列顺序对应
-- insert into 表名 (列1,...) values(值1,...)
-- 例:
insert into students(name, age) values('王二小', 15);
-- 3. 全列多行插入
-- insert into 表名 values(...),(...)...;
-- 例:
insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');
-- 4. 部分列多行插入
-- insert into 表名(列1,...) values(值1,...),(值1,...)...;
-- 例：
insert into students(name, height) values('刘备', 1.75), ('曹操', 1.6);

    • 说明

      • 主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null或者default)

      • 在全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值

  3. 修改数据

    -- update 表名 set 列1=值1,列2=值2... where 条件
-- 例：
update students set age = 18, gender = '女' where id = 6;

  4. 删除数据

    -- delete from 表名 where 条件
-- 例：
delete from students where id = 5;

    • 问题

      • 上面的操作称之为物理删除，一旦删除就不容易恢复，工作中大部分使用逻辑删除

        -- 添加删除表示字段，0表示未删除 1表示删除
alter table students add isdelete bit default 0;
-- 逻辑删除数据
update students set isdelete = 1 where id = 8;

    • 说明

      • 逻辑删除，本质就是修改操作

  5. 删除数据-其他方式

    -- Truncate 删除数据:
-- truncate table 表名
-- 例: 删除学生表的所有数据(保留表结构, 重置auto_increment的值)
truncate table students

-- Drop 删除表:
-- drop table 表名
-- 例: 删除学生表(包括所有数据和表结构)
drop table students

5. 三种删除数据的区别

  • drop

    • 格式：drop table 表名
    • 删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除
    • 如果想删除表，建议使用drop，且删除数据效率最高
    • 不能回滚

  • truncate

    • 格式： truncate table 表名
    • Truncat删除数据时，表结构会保留，只清空表中的数据
    • 自增长字段从1开始(自增长被重置)
    • 执行效率低于drop命令, 高于delete
    • 不能回滚

  • delete

    • 格式： delete from 表名 -- 删除所有记录
    • delete可以通过where子句删除部分记录
    • delete删除所有数据时，自增长字段不会从1开始,ID标识列不连续
    • 语句执行删除的过程是每次从表中删除一行,效率相对低
    • 删除时同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作（可回滚）

执行速度一般来说：drop > truncate > delete

6. 数据的备份和还原 【会操作就行】

  • 掌握数据的备份和还原
  • 在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

1. 命令行方式

  1. 备份格式
mysqldump -u用户名 -p密码 数据库 > 文件的路径
  1. 还原格式
SOURCE 导入文件的路径;

注意：还原的时候需要先登录MySQL,并选中对应的数据库

2. 使用navicat备份和还原

  1. 备份
1556014204516
1556014204516
  1. 还原
1556014259647
1556014259647

3. 小结

  1. 会使用navicat操作就行了, 工作里面一般是运维在处理, 运维处理的话一般使用定时任务自动备份.

7. 小结

  • 登录数据库: mysql -uroot -p
  • 退出数据库: quit 或者 exit 或者 ctr + d
  • 创建数据库: create database 数据库名 charset=utf8;
  • 使用数据库: use 数据库名;
  • 删除数据库: drop database 数据库名;
  • 创建表: create table 表名(字段名 字段类型 约束, ...);
  • 修改表-添加字段: alter table 表名 add 字段名 字段类型 约束
  • 修改表-修改字段类型: alter table 表名 modify 字段名 字段类型 约束
  • 修改表-修改字段名和字段类型: alter table 表名 change 原字段名 新字段名 字段类型 约束
  • 修改表-删除字段: alter table 表名 drop 字段名;
  • 删除表: drop table 表名;
  • 查询数据: select * from 表名; 或者 select 列1,列2,... from 表名;
  • 插入数据: insert into 表名 values (...) 或者 insert into 表名 (列1,...) values(值1,...)
  • 修改数据: update 表名 set 列1=值1,列2=值2... where 条件
  • 删除数据: delete from 表名 where 条件
上次编辑于: 2024/12/21 14:32:53
贡献者: lxtao369