MySQL命令

常用基本命令

show databases;            --列出所有可用的数据库
use your_database;         --选择要使用的数据库
show tables;               --列出所选数据库中的所有表
describe/desc 表名;         --显示表的信息
create database 数据库名;   --创建一个数据库
exit        --退出连接
drop database 数据库名;     --直接删除数据库，不检查是否存在；或drop database [if existe] 数据库名;
drop table 表名;            --直接删除表，不检查是否存在
insert into 表名(列1，列2，，，) values(值1，值2，，，)  --插入数据

创建数据库

--创建数据库
create database 数据库名;
create database [if not exists] 数据库名;
--使用数据库
use 数据库名;

创建表

create table user(
    id int auto_increment primary key,
    Username varchar(50) not null unique,
    Password varchar(50) not null,
    Email varchar(50) not null unique
);

实体-关系模型

绘制 ER 图：

用户 (User)
---------
ID (PK)
用户名 (Username)
密码 (Password)
邮箱 (Email)

文章 (Post)
---------
ID (PK)
标题 (Title)
内容 (Content)
用户ID (UserID, FK)
发布时间 (PublishTime)

评论 (Comment)
---------
ID (PK)
内容 (Content)
用户ID (UserID, FK)
文章ID (PostID, FK)
发布时间 (PublishTime)

表结构设计

根据 ER 图设计表结构：

create database BlogDB;

use BlogDB;

create table User(
    ID int auto_increment primary key,
    Username varchar(50) not null unique,
    Password varchar(50) not null,
    Email varchar(50) not null unique
);

create table Post(
    ID int auto_increment primary key,
    Title varchar(100) not null,
    Content text not null,
    UserID int,
    PublishTime datetime,
    foreign key (UserID) references User(ID)
);

create table Comment(
    ID int auto_increment primary key,
    Content text not null,
    UserID int,
    PostID int,
    PublishTime datetime,
    foreign key (UserID) references USer(ID),
    foreign key (PostID) references Post(ID)
);

添加索引

--帮助mysql快速查询、更新数据库表中的数据
--为某一列创建索引
create index idx_username on user (Username);
--创建唯一索引
create unique index idx_username on user (Username);
--删除索引
drop index idx_username on (Username); 

查询数据

--选择所有列的所有行
select * from users;    
--选择特定列的所有行
select column1,column2 from 表名;  
--添加 where 子句，选择满足条件的行  
select * from users where is_active = true; 
--添加 order by 子句，按照某列升序排列  
select * from users order by birthday;   
--添加 order by 子句，按照某列降序排列
select * from users order by brithday desc;    
--添加 limit 子句，限制返回的行数
select * from users limit 10;  

EXPLAIN

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

➤ 通过EXPLAIN，我们可以分析出以下结果：

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

• --基本用法
  explain select * from your)table where some_column = 'some_value';
  --查看执行计划
  expain format=json select * from your_table where some_column = 'some_value';
  --查看更新、删除等操作的执行计划：
  explain delete from your_table where some_column = 'some_value';
  explain update your_table set some_column = 'new_value' where some_column = 'some_value';
  --查看多表连接查询的执行计划
  explain select a.*,b.* from table_a a join table_b b on a.id = b.a_id where a.some_column = 'some_value';

  EXPLAIN语句返回的结果包括以下列：

• id：查询中操作的标识符，查询中每个操作有一个唯一的id。

• select_type：查询的类型，比如简单查询、联合查询、子查询等。

• table：操作的表名。

  • partitions：匹配的分区信息。

  • type：连接类型，显示查询中表的连接类型，比如ALL、index、range、ref、eq_ref、const、system和NULL。

  • possible_keys：查询中可能使用的索引。

  • key：查询中实际使用的索引。

  • key_len：使用的索引键长度。

  • ref：显示索引的哪一列被使用了。

  • rows：估计的扫描行数。

  • filtered：估计的表的行数中满足条件的百分比。

  • Extra：额外的信息，比如Using index、Using where、Using temporary、Using filesort等。

  • 外键

    引用另一张表的主键，用于建立表之间的关联

  • --创建外键依赖
    ----假设你有两个表 orders 和 customers，其中 orders 表中的 customer_id 列依赖于 customers 表中的 id 列。
    
    --创建表并添加外键约束
    create table customers(
        id int primary key,
        name varchar(100) not null
    );
    create table orders(
        id int primary key,
        order_date int,
        cutomer_id int,
        foreign key (customer_id) references customers(id)
    );
    --添加外键到现有表
    alter table orders
    add constraint fk_customer
    foreign key(customer_id)
    references customer(id)

    --外键操作规则
    cascade：级联操作，删除或更新父表中的记录时，自动删除或更新子表中的相关记录。
    set null：删除或更新父表中的记录时，自动将子表中的外键列设置为 NULL。
    restrict：限制操作，删除或更新父表中的记录时，如果存在依赖关系则操作被拒绝。
    no action：类似于restrict，但是在 SQL 标准中，no action 是立即检查外键约束，而 restrict 是延迟检查。
    set default：删除或更新父表中的记录时，自动将子表中的外键列设置为默认值。

    示例

  • --使用 cascade
    create table orders(
        id int primary key,
        order_date date,
        customer_id int,
        foreign key(customer_id) references customers(id) on delete cascade on updata cascade
    );

    --使用 set null
    create table orders(
        id primary key,
        order_date date,
        customer_id int,
        foreign key (customer_id) references customers(id) on delete set null on update ser null
    );