MySQL

MySQL 基础语句

MySQL 基础操作语句

基础操作

在介绍 MySQL 基础操作前，需要先安装 MySQL 。

  • Linux 系统安装 MySQL

    1
2
3
4
5
6
7
8

    # debian, ubuntu
sudo apt update && sudo apt install mysql

# centos 7
yum update && install mysql

# centos 8, rocky linux
dnf update && dnf install mysql

  • Windows 安装 MySQL 官网下载 msi 软件，一步步安装（可以参考菜鸟教程）。

  • Docker 安装 MySQL 官方镜像

    1
2
3
4
5

    docker serach mysql
docker pull mysql:latest
docker run --name mysql-study -e MYSQL_ROOT_PASSWORD="<ENTER YOUR PASSWORD>" -d mysql:latest
docker container ls
docker logs mysql-study

数据库操作

下面将创建数据库 mysql_test，在此数据库下创建数据表 test，然后进行一系列的数据库操作。

  • 连接 MySQL Server

    1
2
3
4
5

    # 使用容器时需要先进入容器内
docker exec -it mysql-study /bin/bash

`MySQL` -u root -p
<ENTER YOUR PASSWORD>

    进入 MySQL Server 后每条指令都需要以 ; 作为指令结束的标志。

  • 查看帮助

    1

    HELP;

  • 查看数据库

    1

    SHOW databases;

  • 创建数据库

    1
2
3
4

    CREATE DATABASE test01;
# 设置数据库编码和排序方式，可以在库级别设置，也可以在表级别设置
CREATE DATABASE test01 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW databases;

  • 查看库编码格式

    1
2
3
4
5
6
7

    # 查看单个数据库
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'test01';
# 查看所有数据库
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata;

  • 设置或更新库编码格式

    1

    ALTER DATABASE test01 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

  • 重命名数据库

    • MySQL 5.1 版本之前可以使用

      1

      RENAME DATABASE test01 TO mysql_test;

    • 建新库，重命名表名，删除老库

      1
2
3
4

      CREATE DATABASE mysql_test CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
# test01 中所有的表名都要更改，相当于将表从 test01 移动到 mysql_test
RENAME TABLE test01.tb TO mysql_test.tb;
DROP DATABASE test01;

    • 建新表，导出老表，导入新表，删除老表

      1

      CREATE DATABASE mysql_test CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

      1
2

      mysqldump -u root -p<ENTER YOUR PASSWORD> --set-gtid-purged=OFF test01 > /tmp/test01.sql
mysqldump -u root -p<ENTER YOUR PASSWORD> mysql_test < /tmp/test01.sql

      1

      DROP DATABASE test01;

    尽量不要变更数据库名称！！！

数据表操作

  • 创建数据表

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    CREATE TABLE mysql_test.test (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  gender BOOLEAN,
  address TEXT,
  birth DATE COMMENT 'format just like 1970-01-01',
  description VARCHAR(255),
  create_time DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB CHARSET = utf8mb4;

    表级别编码方式高于库级别编码方式。

    MySQL 有多种存储引擎，各有优缺点。其中，最常用的两种存储引擎是 InnoDB 和 MyISAM。

    • InnoDB 是 MySQL 5.5 及以后版本中的默认存储引擎。它支持事务、使用行级锁、支持外键关联、支持热备份等特性，容灾性强，能够在崩溃后安全恢复。
    • MyISAM 则不支持事务、使用表级锁、并发性差、主机宕机后易损坏、灾难恢复性不佳。但是，它可以配合锁实现操作系统下的复制备份、迁移，数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能。

    总之，InnoDB 更适合写密集的表，而 MyISAM 更适合读密集的表。

  • 查看建表语句

    1

    SHOW CREATE TABLE mysql_test.test;

  • 查看数据表结构

    1
2
3

    DESCRIBE mysql_test.test;

SHOW COLUMNS FROM mysql_test.test;

  • 设置或更新存储引擎

    1
2

    ALTER TABLE mysql_test.test ENGINE = MyISAM;
ALTER TABLE mysql_test.test ENGINE = InnoDB;

  • 设置或更新编码

    1

    ALTER TABLE mysql_test.test CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

  • 重命名表名

    1
2

    RENAME TABLE mysql_test.test TO mysql_test.test01;
RENAME TABLE mysql_test.test01 TO mysql_test.test;

  • 添加列

    1

    ALTER TABLE mysql_test.test ADD COLUMN description TEXT;

    TEXT 类型 IO 性能较差，适合存储大文本数据。

  • 更改列

    1

    ALTER TABLE mysql_test.test CHANGE COLUMN address address VARCHAR(255) NOT NULL COMMENT 'address in id card';

  • 删除列

    1

    ALTER TABLE mysql_test.test DROP COLUMN description;

  • 删除数据表

    1

    DROP TABLE mysql_test.test;

字段操作

  • 添加主键

    1
2
3

    ALTER TABLE mysql_test.test ADD CONSTRAINT ID PRIMARY KEY (id);
# 也可以变更列属性
ALTER TABLE mysql_test.test CHANGE COLUMN id id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY;

    CONSTRAINT ID 的意思是为主键取个名称 ID。

  • 删除主键

    1
2
3

    # 对于自增主键，必须先去掉自增属性，才能删除主键属性
ALTER TABLE mysql_test.test MODIFY id BIGINT UNSIGNED NOT NULL; # 尽量只删除自增属性
ALTER TABLE mysql_test.test DROP PRIMARY KEY;

    一张表只能有一个主键，主键自带唯一性且不能为空 主键应当不影响业务逻辑，也不应该更新主键值

  • 添加索引

    1
2
3
4

    # 添加索引
ALTER TABLE mysql_test.test ADD INDEX name (name);
# 添加唯一索引
ALTER TABLE mysql_test.test ADD UNIQUE INDEX (name);

  • 删除索引

    1

    ALTER TABLE mysql_test.test DROP INDEX name;

    添加索引能够加快检索速度，唯一索引确保数据唯一性，索引可以为空 考虑到会建立多个索引，可以为每个索引添加名称（名称不可以重复），不添加时默认使用列名

  • 添加联合索引

    1
2
3
4

    # 添加联合索引
ALTER TABLE mysql_test.test ADD INDEX name_address (name, address);
# 添加唯一联合索引
ALTER TABLE mysql_test.test ADD UNIQUE INDEX name_address (name, address);

  • 删除联合索引

    1

    ALTER TABLE mysql_test.test DROP INDEX name_address;
    • 联合索引名称需要与列名称相关，最好能够望名知义
    • 联合索引的排序和查询都是按照字段的先后顺序进行的，所以让数据重复概率低，字符短，经常使用的列排在联合索引的最左边
    • 联合索引遵循最左前缀匹配原则，即查询条件中只能使用索引的最左侧部分

增删改查操作

  • 增加数据

    1

    INSERT INTO mysql_test.test (name, address) VALUES ('张三', 'JX');

    只能使用单引号，不能使用双引号

  • 查询数据

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    # 查询表中所有数据
SELECT * FROM mysql_test.test;
# 查询表中单列所有数据
SELECT name FROM mysql_test.test;
# 条件查询
SELECT name, address FROM mysql_test.test WHERE id = 1;
# 多条件查询
SELECT name, address FROM mysql_test.test WHERE id = 1 AND gender = false;
# 按 id 升序排序，从 id>0 的数据中查询前 10 条数据，不足 10 条时返回所有数据
SELECT name, gender, address FROM mysql_test.test WHERE id > 0 ORDER BY id LIMIT 10;

  • 更新数据

    1
2
3
4

    # 更新表中所有数据
UPDATE mysql_test.test SET gender = true;
# 更新表中满足条件的数据
UPDATE mysql_test.test SET gender = true WHERE id = 1;

  • 删除数据

    1
2
3
4

    # 清空表中所有数据
DELETE FROM mysql_test.test;
# 清空表中满足条件的数据
DELETE FROM mysql_test.test WHERE gender = true;
comments powered by Disqus