MySQL的基础命令

MySQL的基础命令

Posted at Last edit Comments 0 Comment109 Views0 Likes

登录mysql

本地登录

# 格式
mysql -h<主机名> -P<端口> -u<用户名> -p<密码>

# 举例(-p后面不写密码，回车后会提示输入密码)
mysql -hlocalhost -P3306 -uroot -p

# 如果-h是localhost或127.0.0.1，可以省略不写
mysql -P3306 -uroot -p
  • -h mysql服务器所在的服务器ip或域名，一般为lcoalhost(或127.0.0.1)，因为mysql一般不会允许远程连接(远程都是通ssh映射到127.0.0.1)；
  • -P 大写P，表示Port，mysql服务器端口；
  • -u 表示User，mysql服务器用户名；
  • -p 小写p，表示password，mysql服务器密码，但是一般我们不在-p后面写密码，而是直接按回车(因为命令会有历史记录，翻历史记录就能看到密码，不安全)，然后mysql会提示让你输入密码(输入过程不会有任何显示，保证输入对了按回车即可登录)，如下所示
> mysql -uroot -p
Enter password:

通过ssh远程登录

如果远程mysql服务器允许远程登录，那么登录方法与本地登录一样，只要把-h指定的ip由127.0.0.1改为具体的远程服务器ip就可以。

但是实际使用中，应该没有人会把mysql服务器设置成允许外网访问(也不推荐这样做)，一般都是设置为只允许本地(即127.0.0.1或localhost)访问，所以我们想登录mysql，有两种方法：

  • 1、先登录到服务器里，然后使用本地登录方式登录就可以；
  • 2、不需要登录到服务器，而是在本地建立一个ssh隧道，把远程端口映射到本地端口，这样就可以用本地登录方式来登录了；

方式1没什么好说的，这里主要讲方式2，假设远程服务器的用户名和ip为：[email protected]，用以下命令可以建立一个本地端口转发进程，用于把发往本地端口3307中的数据转发到10.37.129.5的3306端口中

ssh -f -N -L localhost:3307:localhost:3306 [email protected]

这样，我们就可以使用以下命令在本地电脑上来连接远程服务器10.37.129.5中的mysql

mysql -h 127.0.0.1 -P 3307 -uroot -p

虽然mysql连接的是本机的3307，但因为有前面的端口转发ssh进程，它会把3307端口中的数据通过10.37.129.5这台机转发到localhost:3306中，而对10.37.129.5这台机来说，localhost刚好是它自己，于是这个数据最终被转发到10.37.129.5这台机的3306端口中(3306端口就是mysql默认端口)。

要详细了解建立ssh端口转发隧道看这里：SSH端口转发(SSH隧道)。

一些常用知识点

登录mysql后

-- 显示都有哪些库
show databases;

-- 选择使用某个库，<库名>来自前面的show databases
use `<库名>`;

-- 选择使用某个库之后，再看看这个库下边有哪些表
show tables;

-- 查看表中的数据(这个后面会重点讲，这里只是提一下)
select * from `<表名>` limit <n>;

-- 查看当前正在使用哪个库
select database();

-- 查看表结构(desc是describe的简写，你直接用describe也是可以的)
desc `<表名>`;

-- 查看表结构的另一种方法(显示结果与desc完全一样)
show columns from `<表名>`;

-- 加个full，可以查看的更全(比desc多三个属性：Collation,Privileges,Comment)
show full columns from `<表名>`;

查看当前数据库还可以用\r（输入\r按回车）

mysql> \r
Connection id:    41
Current database: *** NONE ***

-r是reconnect，重新与服务器连接的意思，连接后，它会显示当前数据库，如果没有就显示NONE，如上所示。

使用use选择数据库后，再使用\r就能看到当前使用的数据库

mysql> \r
Connection id:    42
Current database: employees

分号与\G的关系

MySQL中几乎所有语句都要用分号结束(除了use <库名>可以不用)，否则你按回车后只是换行书写，而不是执行sql语句。

分号;是MySQL客户端提交sql语句的符号，代表将你在终端中书写的sql语句提交给mysql服务器执行，但事实上分号;只是\g的缩写(注意这个g是小写的)，g代表go，go语句在历史上以及现在在其它sql系统中，都是用于批量提交sql语句给服务器执行。

登录mysql后，输入?或\help或直接help，再按回车，就可以查看帮助

mysql > ?
mysql > \help
mysql > help

帮助信息中，有以下三项

edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
go (\g) Send command to mysql server.

可以看到，\G其实是ego，而\g是go，ego的e目前没有找到解释，有人猜测是extended-go的缩写。虽然ego理论上应该用它的第一个字母e来代表它，即应该写成\e，但如上所示，\e被edit占用了，所以它不能用\e，那为什么不用大写的\E呢？这个就不清楚了。

另外在man mysql中搜索--vertical，如下所示

o –vertical, -E Print query output rows vertically (one line per column value). Without this option, you can specify vertical output for individual

所以其实ego就是--vertical, -E这两个选项，只不过这两个选项不能直接用在mysql里面，而是在启动时，或者在配置文件中，可以用而已。

所以，mysql中所有用到分号;的地方，都可以用\g代替，注意是代替，不是添加，意思是你写了\g就不用再在后面添加分号了

-- 直接用“\g”代替分号“;”，因为分号“;”本来就是“\g”的缩写
desc <表名>\g

查看employees表结构

mysql> desc employees\g
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

知道了小写的\g的作用，那么就不难理解\G的作用了，因为都是g，所以都表示向服务器提交sql语句，但区别是它代表把返回结果竖向显示，因为在终端中，横向显示不够宽的时候，它是会很乱的，所以这时候就要竖向显示。

\G代表向服务器提交sql语句并返回竖向显示结果(注意，不需要再加分号;了)

desc <表名>\G

查看employees表结构（竖向显示输出结果）

mysql> desc employees\G
*************************** 1. row ***************************
  Field: emp_no
   Type: int
   Null: NO
    Key: PRI
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: birth_date
   Type: date
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: first_name
   Type: varchar(14)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 4. row ***************************
  Field: last_name
   Type: varchar(16)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 5. row ***************************
  Field: gender
   Type: enum('M','F')
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 6. row ***************************
  Field: hire_date
   Type: date
   Null: NO
    Key:
Default: NULL
  Extra:
6 rows in set (0.01 sec)

desc与explain

首先说答案：这两个关键字的功能是完全一样的，可以相互替换使用。

desc全名为describe，只不过因为它比较长，我们使用的时候，通常只用它的前四个字母，但你直接用describe也是可以的

-- 使用describe查看表结构
describe <表名>;

-- 使用explain，功能完全一样，也是查看表结构
explain <表名>;

打开13.8.2 EXPLAIN Statement，往下滚动一下，能找到以下句子

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).

翻译：DESCRIBE和EXPLAIN语句是同义词。在实际使用中，DESCRIBE更常用于获取有关表结构的信息，而 EXPLAIN用于获取查询执行计划（即MySQL如何执行查询的说明）。

可以看到，DESCRIBE(平时使用可简写成DESC)和EXPLAIN是同义词，使用的时候功能是一样的，虽然功能完全一样，虽然可以相互替换使用，但在实际使用中，DESCRIBE更常用于获取有关表结构的信息(格式：desc <表名>)，而EXPLAIN更常在sql优化和索引优化中使用(用来放在select语句前面，分析它的执行情况，以便知识如何优化)。

注释符号

MySQL中有三种注释符号(见9.7 Comments)

-- 这是注释(推荐)

# 这是注释

/* 
这是注释1
这是注释2
*/

但有一种注释是这样的

/*!<数字> <语句> */

实例

/*!40000 ALTER TABLE `wp_posts` DISABLE KEYS */;

那么这种注释看上去是注释，但其实MySQL是会执行它的，感叹号后面的数字表示该语句在哪个版本的mysql上执行，读的时候是4.00.00这么读的，这个都是0不好区分，换一个，比如40103，就读作4.01.03(当然0开头一般不会被读出来，所以一般读作4.1.3)。

这种是mysql的一个特殊处理，也就是它从sql上来说，就是注释，但mysql会特别的去处理这个注释，这样这个sql在mysql上导入的话，这个“注释的语句”就会被运行，但是在其它非mysql中导入的话，这个注释就会被当成真正的注释。

单双反引号的区别

在ANSI标准sql中，单引号括住的为字符串，而双引号括住的为“标识符(identifiers)”，哪些是标识符呢？比如字段名、表名、库名这些就属于标识符。

所以，根据标准sql，我从mysql表中查询数据应该是这样的

select "emp_no","first_name","last_name" from "employees" where last_name='Facello' limit 5;

但mysql默认并没有遵循这个标准，mysql默认设置中，双引号与单引号的作用相同，都表示字符串，而标识符则用反引号来括住，即上述语句在mysql中一般写成

select `emp_no`,`first_name`,`last_name` from `employees` where last_name='Facello' limit 5;

但我们也可以把mysql的sql模式设置为标准模式（只是可以设置，但不建议设置，因为我觉得用反引号挺好的），在设置前先查看之前sql模式，因为我们只是临时设置测试一下，等下还要还原回去

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.01 sec)

然后设置为ANSI模式（只是临时设置，只有当前会话有效）

mysql> SET SESSION sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

查看是否设置成功（可以看到已经设置成功了，其中“ANSI_QUOTES”就是用于设置引号的ANSI标准），具体文档描述在这里

mysql> select @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode                                                                     |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

此时我们再来执行前面用双引号括住字段和表名的那句sql，可以看到，执行成功了

mysql> select "emp_no","first_name","last_name" from "employees" where last_name='Facello' limit 5;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|  10001 | Georgi     | Facello   |
|  10327 | Roded      | Facello   |
|  12751 | Nahum      | Facello   |
|  15346 | Kirk       | Facello   |
|  15685 | Kasturi    | Facello   |
+--------+------------+-----------+
5 rows in set (0.01 sec)

测试完后，我们再恢复原来的sql模式（注意，你原来的可能跟我的不一样，要看你自己的）

mysql> SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

其实，一般情况下，字段名、表名、库名是可以不用双引号或者反引号括起来的，只有当它们的名称与sql的关键字相同时，才需要括起来，因为你不括起来就会被当成sql关键字了，肯定会报错。

不过按标准来说，我们还是应该把它们括住的(无论是反引号还是双引号，反正就是要括住比较好，万一有与关键字冲突的呢)。

另外就是，虽然mysql中默认单引号和双引号作用相同，但我们还是要用单引号，不要用双引号，这样习惯之后，万一以后用别的数据库，也能习惯用单引号。

最后附上几个词的英文名称
单引号：single quotes
双引号：double quotes
反引号：backstack
ANSI：American National Standards Institute，美国国家标准委员会。

库/表的相关操作

创建/删除数据库

创建数据库

-- 写全
CREATE DATABASE `<数据库名>` DEFAULT CHARSET <字符集> COLLATE <校对集>;
-- 举例
CREATE DATABASE `testdb` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 简写(不指定字符集与校对集，将会使用默认值)
CREATE DATABASE `<数据库名>`;
-- 举例
CREATE DATABASE `testdb`;

关于字符集，请看MySQL字符集与校对集。

删除数据库

DROP DATABASE `<库名>`;

#举例
DROP DATABASE `testdb`;

创建/删除表

创建一个名为user的表(字段有什么属性详见：添加字段)

CREATE TABLE IF NOT EXISTS `user`(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    -- 设置主键
    PRIMARY KEY(`id`),
    -- 设置普通索引，<索引名>可写可不写，不写默认为列名
    INDEX <索引名1>(`name`),
    -- 设置唯一索引，InnoDB表可指定索引排序为DESC(不指定默认为ASC)
    UNIQUE INDEX <索引名2>(`nickname` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';

省略一些参数后的简单写法

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    -- 设置主键
    PRIMARY KEY(`id`),
) COMMENT='用户表';
  • 省略IF NOT EXISTS，用户判断被创建的表是否存在，如果存在则不创建，所以如果被创建的表不存在的情况下，加不加IF NOT EXISTS都是一样的；
  • 省略ENGINE=InnoDB，在mysql8.0中，默认引擎就是InnoDB，所以不指定默认就是InnoDB；
  • 省略AUTO_INCREMENT=1，自增值默认从1开始，如果你设置为2，那么它就会从2开始；
  • 省略DEFAULT CHARSET=utf8mb4，用于设置默认字符集，如果不设置，默认会使用它所属库的字符集；
  • 省略COLLATE=utf8mb4_general_ci，用于设置默认校对集，如果不设置，默认会使用它所属库的校对集，其实这个是跟字符集一起的，都是跟在DEFAULT后面的；

删除表

-- 格式
DROP TABLE <表名>;

-- 举例
DROP TABLE `user`;

清空表：清空后，如果有auto_increment字段，则该字段会被重置为从1开始

TRUNCATE `<表名>`;

字段相关操作

添加字段

-- 格式，AFTER `<字段>`表示该字段要创建在某个字段后面(注意，并没有一个叫BEFORE的关键字可以用，你要设置排序就要用AFTER或者下边的FIRST)
ALTER TABLE `<表名>` ADD COLUMN `<新字段>` <字段类型(长度)> <其它属性> COMMENT '字段备注' AFTER `<字段>`;

-- 格式，只要字段不是在第一位，都可以用AFTER `<字段名>`来决定它在所有字段中的排序，如果要放在第一位，因为前面没有字段，自然就不可能用AFTER，此时可用FIRST来指定让它位于第一位
ALTER TABLE `<表名>` ADD COLUMN `<新字段>` <字段类型(长度)> <其它属性> COMMENT '字段备注' FIRST;

-- 省略COLUMN，默认就是ADD COLUMN，省略FIRST或AFTER，默认放在最后
ALTER TABLE `<表名>` ADD `<新字段>` <字段类型(长度)> <其它属性> COMMENT '字段备注';

举例：给前面创建/删除表中创建的user表添加一个username字段

ALTER TABLE `user` ADD `username` varchar(20) COMMENT '用户名';

上面这个例子没有<其它属性>，那么其它属性到底有什么呢？我们使用以下命令查看它有哪些属性

mysql> show full columns from user;
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+
| Field    | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment  |
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+
| id       | int unsigned | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references | id       |
| name     | varchar(20)  | utf8mb4_general_ci | NO   | MUL |         |                | select,insert,update,references | name     |
| nickname | varchar(50)  | utf8mb4_general_ci | YES  | UNI | NULL    |                | select,insert,update,references | nickname |
| age      | tinyint      | NULL               | YES  |     | NULL    |                | select,insert,update,references | age      |
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+
4 rows in set (0.06 sec)

其中Collation(校对集)和Privileges(权限)我们没必要设置，因为它们默认会使用表的校对集和权限，所以我们能设置的属性还剩四个：

  • Default：用于设置字段默认值(就是我插入一行，但是不给这个字段插入指定一个值时，它会自动使用你设置的默认值)，用DEFAULT <默认值>来表示，比如DEFAULT 0就是默认为0；
  • Null：用于设置是否允许为空(NULL)，默认是允许为空的，如果你不允许字段为空，那么就用NOT NULL表示。并且，设置NOT NULL后一定要设置一个默认值(虽然数据库不强制)，但如果不设置默认值，它默认就是空值，这会导致你插入一行没有给这个字段指定值时，它会默认用NULL，但你又不允许为NULL，所以就会报错；
  • Key：用于设置“约束(constraint)”，原则上，“约束”用于维护表的完整性，而“索引”用于加快查找速度，但由于mysql的“约束”是通过索引来实现的，所以“约束”和“索引”是同时存在的，就是说，你创建一个约束，它会生成一个索引，而你创建一个索引，它也会生成一个约束，在“Key”列你看到的都是索引类型：PRI、MUL、UNI，PRI就是primary key(主键索引)，MUL就是multi(表示允许该列有多个相同值，其实就是普通索引)，UNI就是unique(唯一索引)；
  • Extra：用于设置额外的一些设置，主要包括：是否自增、是否自动更新为当前时间、是否使用一连串默认值；

NULL和DEFAULT举例

-- 使用DEFAULT ''，表示设置默认值为空字符串
ALTER TABLE `user` ADD `username` varchar(20) DEFAULT '' COMMENT '用户名';

-- 使用NOT NULL表示不允许为空
ALTER TABLE `user` ADD `username` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名';

Key举例

Key的话你可以简单认为设置索引就行(外键之类的一般通过程序逻辑来操作而不是通过数据库，这里就不讲了)，以下是给username添加一个唯一索引(在Key列会显示UNI)

ALTER TABLE `user` ADD UNIQUE INDEX(`username`);

更多设置索引请看参见：MySQL索引的创建与删除。

Extra举例

自增(auto_increment)：

  • 一个表只能有一个自增字段；
  • 自增字段不能设置默认值，否则报错：Invalid default value for ‘字段名’；
  • 自增字段必须要有索引，普通、唯一、主键索引都可以。很多资料说自增字段必须是主键或唯一索引，但我亲自测试(MySQL V8.0.29)，确实要求要索引(无索引会报错)，但不要求索引一定是主键或者唯一索引，普通索引也可以；

在实际使用中，auto_increment都是跟主键一起的(暂未见其它案例，虽然你可以这么做)，为了测试，我们先建一个测试表test1，这个表只有一个name字段

CREATE TABLE IF NOT EXISTS `test1`(
    `name` VARCHAR(20) COMMENT '姓名'
);

然后我们再给它添加一个自增的id字段

ALTER TABLE `test1` ADD `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'id字段';

然后再用以下语句查看，可以看到新增的id字段的Extra里确实有“auto_increment”(自增)了

mysql> SHOW FULL COLUMNS FROM `test1`;
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+
| Field | Type        | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment  |
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+
| name  | varchar(20) | utf8mb4_0900_ai_ci | YES  |     | NULL    |                | select,insert,update,references | 姓名     |
| id    | int         | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references | id字段   |
+-------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+----------+
2 rows in set (0.00 sec)

是否自动更新为当前时间：
当字段设置为TIMESTAMP或DATETIME时间类型时，可用ON UPDATE CURRENT_TIMESTAMP来指定自动该字段，自动更新的意思是，只要这一行的某个字段数据有变化，那么该字段就会自动更新为数据变化那一刻的时间，用来表示这行的数据在什么时候被更新了，对于updated_at这种表示更新时间的字段来说最适合。

给test1表添加一个update_at字段

ALTER TABLE `test1` ADD update_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间';
  • DATETIME 指定字段类型为DATETIME类型，这种类型的格式是标准的时间格式：YYYY-MM-DD hh:mm:ss，当然它还可以有毫秒和微秒数，具体见：这里；
  • DEFAULT CURRENT_TIMESTAMP 表示把默认值设置为当前时间戳，这样插入一条数据并且不给该字段赋值时，它就会自动插入当前时间；
  • ON UPDATE CURRENT_TIMESTAMP 当行中任意一个字段的值被修改时(修改为原值不算)，这个字段就会自动更新为当前时间；

再去看看字段，发现新添加的update_at字段的Extra确实有一个“DEFAULT_GENERATED on update CURRENT_TIMESTAMP”

mysql> SHOW FULL COLUMNS FROM `test1`;
+-----------+-------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+--------------+
| Field     | Type        | Collation          | Null | Key | Default           | Extra                                         | Privileges                      | Comment      |
+-----------+-------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+--------------+
| id        | int         | NULL               | NO   | PRI | NULL              | auto_increment                                | select,insert,update,references | id字段       |
| name      | varchar(20) | utf8mb4_0900_ai_ci | YES  |     | NULL              |                                               | select,insert,update,references | 姓名         |
| update_at | datetime    | NULL               | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | 更新时间     |
+-----------+-------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+--------------+
3 rows in set (0.00 sec)

TIMESTAMP和DATETIME的区别详见：11.2.2 The DATE, DATETIME, and TIMESTAMP Types。

是否使用一连串默认值：
一连串默认值，其实是一个省略的写法，它代表你同时设置三个属性：NOT NULL, AUTO_INCREMENT, UNIQUE KEY。

由于AUTO_INCREMENT一个表只能有一个，所以我们先删除前面test1表的id字段

-- 删除id字段
ALTER TABLE `test1` DROP `id`;

-- 添加id字段，使用了：SERIAL DEFAULT VALUE 属性
ALTER TABLE `test1` ADD `id` INT SERIAL DEFAULT VALUE COMMENT 'id字段';

新增加的id字段信息如下所示

mysql> SHOW FULL COLUMNS FROM `test1`;
+-----------+-------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+--------------+
| Field     | Type        | Collation          | Null | Key | Default           | Extra                                         | Privileges                      | Comment      |
+-----------+-------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+--------------+
| name      | varchar(20) | utf8mb4_0900_ai_ci | YES  |     | NULL              |                                               | select,insert,update,references | 姓名         |
| update_at | datetime    | NULL               | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | 更新时间     |
| id        | int         | NULL               | NO   | PRI | NULL              | auto_increment                                | select,insert,update,references | id字段       |
+-----------+-------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+--------------+
3 rows in set (0.09 sec)

可以看到，Null是NO(说明是不允许为空，也就是NOT NULL), AUTO_INCREMENT也有了，但Key是显示PRI，PRI不是primary key的意思吗？难道它会自动设置主键？其实并不是的，unique key就是唯一键(其实就是唯一索引)，只不过由于InnoDB的存储引擎原理是使用聚簇索引(行数据存放在叶子节点)，所以InnoDB必须要有一个“行唯一标识符”，我们称它为rowid。

通常来说，一个表的rowid都是主键，但如果你没有创建主键，它就会退而求其次，寻找其它适合做rowid的键，而由于“非空唯一键”可唯一标识一行，所以当表中没有主键的时候，“非空唯一索引”就被选作rowid(这时它起到的效果与主键一样，所以会显示成PRI)，而如果“非空唯一索引”都没有，那其实mysql内部是会自己创建一个隐藏的对用户不可见的rowid(占6个字节)。

关于这个，在官方文档中也是有说明的：14.6.2.1 Clustered and Secondary Indexes。

删除字段

-- 删除单个字段
ALTER TABLE `<表名>` DROP `<字段>`;

-- 删除多个字段
ALTER TABLE `<表名>` DROP `<字段1>`, DROP `<字段2>`, ……, DROP `<字段n>`;

修改字段

只修改字段属性：用modify

ALTER TABLE `<表名>` MODIFY `<字段名>` <字段类型(长度)> <其它属性> COMMENT '这是备注';

-- 举例
ALTER TABLE `user` MODIFY `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id-修改后';

修改字段名及字段属性：用change

ALTER TABLE `<表名>` CHANGE `<原字段名>` `<新字段名>` <字段类型(长度)> <其它属性> COMMENT '这是备注';

-- 举例(注意，由于id是主键，修改名称时，只写其它属性就好，不能把primary key也带上，这样会报错，原因是primary key不属于字段属性，而属于主键，你修改属性时未带上primary key并不代表mysql会删掉primary key)
ALTER TABLE `user` CHANGE `id` `id2` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id-change后';

其实change只不过是要多写一个修改后的名称，如果你不需要改字段名，你也可以用change，只需要把新字段名写成跟原字段名一样就行。

数据操作

插入数据

-- 给指定字段插入数据，未指定的字段必须允许为空或有默认值，否则会报错，如果值是字符串，要用单引号括起来
INSERT INTO `<表名>`(`<字段1>`,`<字段2>`,……,`<字段n>`) VALUES(值1, 值2, ……, 值n);

-- 一次插入多行，只需要在VALUES后面用多个括号括住每行的值，括号之间用英文逗号隔开
INSERT INTO `<表名>`(`<字段1>`,`<字段2>`,……,`<字段n>`) VALUES(值1, 值2, ……, 值n),(值1, 值2, ……, 值n),(值1, 值2, ……, 值n);

-- 如果你要向所有字段插入数据，则字段可省略不写，括号内值的数量必须刚好与字段数量对应，顺序也要对应
INSERT INTO `<表名>` VALUES(值1, 值2, ……, 值n),(值1, 值2, ……, 值n),(值1, 值2, ……, 值n);

举例，先创建一个表

CREATE TABLE `user`(
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
    `name` VARCHAR(20) DEFAULT ''  COMMENT '姓名',
    `age` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '年龄'
);

-- 举例：向全部字段插入时，可以不写字段名，值的数量要与字段数量一致，顺序也要与字段顺序一致
INSERT INTO `user` VALUES(1, '张三', 18);

-- 如果有自增字段，那么自增字段的值可以用NULL代替，它实际会自动自增，而不会插入NULL
INSERT INTO `user` VALUES(NULL, '李四', 20),(NULL,'王五', 22),(NULL, '赵六', 21);

-- 与自增字段同理，如果字段是有默认值的，也可以用NULL代替，它实际上并不会插入NULL，而是会使用字段默认值
INSERT INTO `user` VALUES(NULL, '李四', NULL);

替换行：REPLACE INTO与INSERT INTO几乎一样，除了一个区别，如果插入的数据中，主键或唯一键的数据已存在，则REPLACE INTO会把它删掉，再插入新的(相当于替换，不会导致自增字段自增)，而INSERT INTO会报错已存在，详见：13.2.9 REPLACE Statement。

更新数据

-- 如果值是字符串，需要用单引号括住
UPDATE `<表名>` SET `<字段名>`=值 WHERE <条件>;

-- 举例
UPDATE `user` SET `name`='张三2' WHERE <条件>;

批量替换某个字段的值

UPDATE `<表名>` SET `<字段名>`=REPLACE(`<字段名>`, 原值, 新值) WHERE <条件>;

-- 例：把所有张三都替换为李四
UPDATE `user` SET `name`=REPLACE(`name`, '张三', '李四') WHERE <条件>;

删除数据

-- 注意，一定不能忘了加where条件，否则你会把整个表数据都删掉
DELETE FROM `<表名>` WHERE <条件>;

-- 举例
DELETE FROM `user` WHERE id=1;

查询数据

-- 查询指定字段
SELECT `<字段1>`, `<字段2>`, ……, `<字段n>` FROM `<表名>` WHERE <条件> ORDER BY <排序字段> LIMIT <跳过行数> <返回行数>;

-- 查询所有字段
SELECT * FROM `<表名>` WHERE <条件> ORDER BY <排序字段> LIMIT <跳过行数> <返回行数>;

查询数据要写的话非常多，这里暂时不往下写。

扫一扫打赏
觉得文章对你有用的话鼓励一下我吧
打赏
Mysql

©版权声明

本文章由博主『Bruce』撰写，采用『CC BY-NC-SA 4.0(创作共用许可协议4.0)』授权发布，转载请以链接形式标明本文地址。
订阅评论
提醒
guest

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。

0 评论
内联反馈
查看所有评论
0
希望看到您的想法，请您发表评论x

扫码在手机查看
iPhone请用自带相机扫
安卓用UC/QQ浏览器扫

MySQL的基础命令