数据库笔记1 MySQL基础操作命令 分类清晰

MySQL 基础操作命令，包括对数据库、数据表、数据的操作。

一、对库的操作

1.1 查看

show schemas/databases;

1.2 选择库

use demo;

1.3 创建

create database/schema demo;

1.4 删除

drop database/schema demo;

二、对表的操作

2.1 查看

show tables;

2.2 创建

create table if not exists job(
    -> id int not null auto_increment,
    -> job_name varchar(50) not null,
    -> job_desc varchar(200) not null,
    -> primary key (id));

2.3 删除

drop table job;

2.4 清空表数据

delete from job;

2.5 表字段操作

2.5.1 查看表字段

desc job;
或者
show create table job;

2.5.2 增加表字段:add

alter table job add job_desc varchar(200);

2.5.3 修改表字段:change,修改表字段类型及名称：modify

alter table job change job_desc job_text varchar(200);
alter table job modify job_name varchar(50);

2.5.4 删除表字段:drop

alter table job drop job_desc; 
如果数据表中只剩余一个字段则无法使用DROP来删除字段

2.5.5 移动表字段位置：add和modify时才可用

alter table job add job_desc first;
alter table job modify job_desc varchar(200) after id;

三、 数据操作

3.1 增：

insert into job (job_name,job_desc) values ("前端工程师","前端开发xxx");
insert into job values (2,"测试","测试系统的bug");
insert into job values (5,"后台开发工程师","系统后台服务接口的开发xxxx");
insert into job (job_name,job_desc) values ("美工","设计界面UI");

3.2 删：

delete from job where job_name = "测试";

3.3 改:

update job set job_desc = "测试";
update job set job_desc = "前端测试xxxxx" where job_name = "前端工程师";

3.4 查:

select job_desc from job where job_name = "test";