连接远程MySQL

mysql -h 110.110.110.110 -u root -p

链接本地MySQL

mysql -u root -p

查看数据库

show databases;

结果

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| php                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

选择数据库

use mysql;

查看数据表

show tables；

创建数据库

create database school_info;

删除数据库

drop database school_info;

创建表

CREATE TABLE `school_info`.`school`  (
  `id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NULL COMMENT ‘学校名称‘,
  `create_at` bigint(11) NULL,
  `update_at` bigint(11) NULL,
  `status` tinyint(4) NULL DEFAULT 0 COMMENT ‘状态‘,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = ‘学校信息表‘;

查看数据表的结构

desc school;

结果

+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(200)        | YES  |     | NULL    |                |
| create_at | bigint(11)          | YES  |     | NULL    |                |
| update_at | bigint(11)          | YES  |     | NULL    |                |
| status    | tinyint(4)          | YES  |     | 0       |                |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

添加表数据 - 插入记录

insert into school values(2,"学校3",1625568499,1625568499,1);
或
insert into school(name,create_at,update_at,status) values("学校4",1625568499,1625568499,1);

查看数据表的内容

select * from school;

结果

+----+---------+------------+------------+--------+
| id | name    | create_at  | update_at  | status |
+----+---------+------------+------------+--------+
|  1 | 学校1   | 1625568499 | 1625568499 |      1 |
|  2 | 学校2   | 1625568499 | 1625568499 |      1 |
|  3 | 学校3   | 1625568499 | 1625568499 |      1 |
|  4 | 学校4   | 1625568499 | 1625568499 |      1 |
+----+---------+------------+------------+--------+
4 rows in set (0.00 sec)

添加表字段

alter table school add `adress` varchar(200) NULL COMMENT ‘地址‘ after `name`;

删除列

alter table school drop `adress`;

修改表记录

update school set `name`=‘学校名称1‘ where `id`=1;

删除表记录

delete from school where `id`=4;

清空表记录

truncate table school;

删除数据表

drop table if exists school;

MySQL基础命令