MySQL的基础命令

MySQL的基础命令

登录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.53306端口中

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 INTOINSERT 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 <跳过行数> <返回行数>;

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

打赏

订阅评论
提醒
guest

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

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

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

MySQL的基础命令