MySQL基本命令

1. 操作数据库

• 查看有哪些数据库

  MariaDB [(none)]> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  ...
  | mysql              |
  ...
  | performance_schema |
  ...
  | test               |
  +--------------------+
  13 rows in set (0.00 sec)
  

• 创建数据库（名为 mydb）

  MariaDB [(none)]> create database if not exists `mydb` character set utf8 collate utf8_bin;
  Query OK, 1 row affected (0.00 sec)
  

  • 查看有哪些字符集：show character set;
  • 查看有哪些collation：show collation;

• 查看数据库定义

  MariaDB [mydb]> show create database `mydb`;
  +----------+--------------------------------------------------------------------------------+
  | Database | Create Database                                                                |
  +----------+--------------------------------------------------------------------------------+
  | mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
  +----------+--------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  

• 修改数据库

  MariaDB [(none)]> alter database `mydb` character set utf8 collate utf8_bin;
  Query OK, 1 row affected (0.00 sec)
  

• 切换数据库

  MariaDB [(none)]> use `mydb`;
  Database changed
  MariaDB [mydb]>
  

• 删除数据库

  MariaDB [mydb]> drop database if exists `mydb`;
  Query OK, 0 rows affected (0.00 sec)
  

2. 操作表

• 查看有哪些表

  MariaDB [mysql]> show tables;
  +---------------------------+
  | Tables_in_mysql           |
  +---------------------------+
  ...
  | user                      |
  +---------------------------+
  30 rows in set (0.00 sec)
  

• 创建表

  MariaDB [mydb]> create table if not exists `student`
  (
  	`id` int not null auto_increment primary key,
  	`name` varchar(20) not null,
  	`birth` datetime,
  	`updated_at` datetime default now()
  ) engine=InnoDB;
  Query OK, 0 rows affected (0.00 sec)
  

  支持的数据类型

• 查看表结构

  MariaDB [mydb]> describe `student`;
  +------------+-------------+------+-----+-------------------+----------------+
  | Field      | Type        | Null | Key | Default           | Extra          |
  +------------+-------------+------+-----+-------------------+----------------+
  | id         | int(11)     | NO   | PRI | NULL              | auto_increment |
  | name       | varchar(20) | NO   |     | NULL              |                |
  | birth      | datetime    | YES  |     | NULL              |                |
  | updated_at | datetime    | YES  |     | CURRENT_TIMESTAMP |                |
  +------------+-------------+------+-----+-------------------+----------------+
  4 rows in set (0.00 sec)
  

• 查看表定义

  MariaDB [mydb]> show create table `student` \G
  *************************** 1. row ***************************
         Table: student
  Create Table: CREATE TABLE `student` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) COLLATE utf8_bin NOT NULL,
    `birth` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  1 row in set (0.00 sec)
  

• 插入多条记录

  MariaDB [mydb]> insert into `student` (`name`, `birth`) values
  	('zhang san', '1999-01-01'),
  	('li si', '1998-02-02');
  Query OK, 2 rows affected (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  

• 查询所有记录

  MariaDB [mydb]> select * from `student`;
  +----+-----------+---------------------+---------------------+
  | id | name      | birth               | updated_at          |
  +----+-----------+---------------------+---------------------+
  |  1 | zhang san | 1999-01-01 00:00:00 | 2021-10-02 18:34:33 |
  |  2 | li si     | 1998-02-02 00:00:00 | 2021-10-02 18:34:33 |
  +----+-----------+---------------------+---------------------+
  2 rows in set (0.00 sec)
  

• 查询特定记录和特定字段

  MariaDB [mydb]> select `name`, `birth` as `birth day` from `student` where id=1;
  +-----------+---------------------+
  | name      | birth day           |
  +-----------+---------------------+
  | zhang san | 1999-01-01 00:00:00 |
  +-----------+---------------------+
  1 row in set (0.00 sec)
  

• 更新记录

  MariaDB [mydb]> update `student` set `name`='wang wu' where `id`=2;
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  

• 删除指定的记录

  MariaDB [mydb]> delete from `student` where id=2;
  Query OK, 1 row affected (0.00 sec)
  

• 删除所有记录

  MariaDB [mydb]> delete from `student`;
  Query OK, 1 row affected (0.00 sec)
  

• 删除表

  MariaDB [mydb]> drop table if exists `student`;
  Query OK, 0 rows affected (0.00 sec)
  

3. 更多查询操作

• 排序：c1 列升序，c4 列降序

  MariaDB [mydb]> select * from `t2` order by `c1` asc, `c4` desc;
  +------+------+------+
  | c1   | c2   | c4   |
  +------+------+------+
  |    1 |    2 |   80 |
  |    1 |    2 |   40 |
  |   10 |   20 |   40 |
  |   40 |   50 |   70 |
  +------+------+------+
  4 rows in set (0.00 sec)
  

• 限制结果集大小：从偏移位置 2 开始，只取 2 条记录（偏移从 0 开始计）

  MariaDB [mydb]> select * from `t2`;
  +------+------+------+
  | c1   | c2   | c4   |
  +------+------+------+
  |   10 |   20 |   40 |
  |   40 |   50 |   70 |
  |    1 |    2 |   40 |
  |    1 |    2 |   80 |
  +------+------+------+
  4 rows in set (0.00 sec)
  
  MariaDB [mydb]> select * from `t2` limit 2, 2;
  +------+------+------+
  | c1   | c2   | c4   |
  +------+------+------+
  |    1 |    2 |   40 |
  |    1 |    2 |   80 |
  +------+------+------+
  2 rows in set (0.00 sec)
  

• 分组：将记录按照 c1 列的值进行分组，然后对每个分组求和

  MariaDB [mydb]> select sum(`c1`) from `t2` group by `c1`;
  +---------+
  | sum(c1) |
  +---------+
  |       2 |
  |      10 |
  |      40 |
  +---------+
  3 rows in set (0.00 sec)
  

  只对 c1 列的值小于 10 的记录进行分组：

  MariaDB [mydb]> select sum(`c1`) from `t2` group by `c1` having `c1` < 10;
  +-----------+
  | sum(`c1`) |
  +-----------+
  |         2 |
  +-----------+
  1 row in set (0.00 sec)
  

  having 指定要对哪些记录进行分组，where 指定哪些记录能进结果集。