mysql前端怎么做_前端学习mysql的详细图文记录

查看数据表数据记录相关操作

添加(插入)数据

插入数据操作在之前说过一个，即：

INSERT tbl_name(字段名称,...)VALUES(值,...);

复制代码

现在再来补充说明一些操作。避免混淆，也为了复习前面的知识，我们新建一个数据库，并在库中建一个表：

-- 新建数据库 data_demo

CREATE DATABASE IF NOT EXISTS data_demo DEFAULT CHARACTER SET 'UTF8';

-- 使用新数据库

USE data_demo;

-- 新建数据表 user

CREATE TABLE IF NOT EXISTS user(

id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',

username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',

age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',

email VARCHAR(50) NOT NULL DEFAULT '123@qqcom' COMMENT '邮箱'

)ENGINE=INNODB CHARSET=UTF8;

复制代码

整体复制上面语句粘贴到命令行中执行，建库建表成功，查看数据表：

不指定字段名称插入数据

我们不需要指定每一个字段名，可以按字段顺序依次插入数据：

-- 在 VALUE 后面的括号中依次写入数据，按照表中字段的顺序

-- 也可以使用关键字 VALUES

INSERT tbl_name VALUE(value...)

复制代码

测试在user中插入一条数据：

INSERT user VALUE(1,'瓶子',18,'000@qq.com');

复制代码

结果：

插入成功，数据正确对应到字段上。由于

id字段是自动增长的，所以可以不指定具体数值，写法如下：

-- 关键字可以用 VALUES

-- 使用 NULL 填充

INSERT user VALUES(NULL,'tom',20,'123@qq.com');

-- 使用 DEFAULT 填充

INSERT user VALUES(DEFAULT,'lily',16,'163@qq.com');

复制代码

可以看到依旧成功插入数据，且

id是自动增长的。

指定字段插入数据

这种就是之前说过的：

INSERT tbl_name(字段名称,...) VALUES(值,...);

复制代码

由于user表中，id是自动增长，age 和 email 都是有默认值的，所以我们可以只指定 username 来插入数据：

INSERT user(username) VALUES('a');

复制代码

结果：

一次插入多条数据

有时候需要一次操作插入多条数据，可以使用命令：

-- 字段名称可以不写

INSERT tbl_name[(字段名称,...)] VALUES(值,...),

(值,...),

(值,...);

复制代码

我们一次插入三条数据试试：

-- 注意标点符号

INSERT user(id,username,age,email) VALUES(NULL,'b',22,'b@qq.com'),

(NULL,'c',23,'c@qq.com'),

(NULL,'d',24,'d@qq.com');

复制代码

结果：

INSERT ... SET 的形式插入数据

命令：

INSERT tbl_name SET 字段名称=值,字段名称=值,...;

复制代码

测试插入一条数据：

INSERT user SET username='abc';

复制代码

INSERT ... SELECT 的形式插入数据

此种形式是可以将 别的表中查到的数据 插入 到当前表中，命令如下：

INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name2;

复制代码

我们可以测试下，先建一个数据表，并插入一些数据：

-- 建表

CREATE TABLE test(

a VARCHAR(10)

);

-- 插入三条数据

INSERT test VALUES('AA'),('BB'),('CC');

复制代码

上述语句执行完之后，我们来尝试使用INSERT...SELECT方式给user插入数据：

INSERT user(username) SELECT a FROM test;

复制代码

结果：

至此，你掌握了好几种插入数据的方式。

修改数据

修改数据的命令很简单：

UPDATE tbl_name SET 字段名称=值,字段名称=值,...[WHERE 条件]

复制代码

其中，WHERE是条件，修改数据时如果不指定条件，会将表中所有数据都修改了，所以使用条件是很必要的，后面会详细说到 WHERE。

在修改数据前，我们先看看 user 表中先有的数据：

我们来修改第一条数据的年龄，将 18 修改为 20，此条数据的唯一标识就是id字段，所以条件是 id=1：

UPDATE user SET age=20 WHERE id=1;

复制代码

如果我们不指定 WHERE，那么将修改所有的数据，比如：

UPDATE user SET age=100;

复制代码

删除数据

删除数据的命令为：

DELETE FROM tbl_name [WHERE 条件];

复制代码

同样，删除操作也应该加上条件，不然就删除了所有的数据，所以删除需谨慎，一定要先写好条件。

删除 id=1 的数据：

DELETE FROM user WHERE id=1;

复制代码

可以看到

id=1 的数据被删除了。

删除表中所有的数据：

DELETE FROM user;

复制代码

此时，表中所有数据被清空。这时候有一个需要注意的地方，那就是所有数据被清空后，AUTO_INCREMENT 的值是不会被重置的，执行命令 SHOW CREATE TABLE user 结果如下：

可以看到即使数据被清空，AUTO_INCREMENT 的值还是保持之前的，再添加的数据的 id 就是 13。在上面表结构章节中最后说到了怎么重置此值，可以往上查阅，这里来重置一下值为 1：

ALTER TABLE user AUTO_INCREMENT=1;

复制代码

彻底清空数据表

还有一个命令干脆直接，直接清空所有数据，并重置 AUTO_INCREMENT 的值，方法：

TRUNCATE [TABLE] tbl_name;

复制代码

由于上面清空来数据表user，所以再重新添加几条数据(不要怕麻烦，一步步实践加深印象)

-- 添加几条数据

-- 再强调一遍，自增值可以使用 NULL 或 DEFAULT 填充；默认值可以使用 DEFAULT 代替

INSERT user VALUES(NULL,'AA',18,DEFAULT),(NULL,'BB',19,DEFAULT),(NULL,'CC',20,DEFAULT);

复制代码

查看表结构，SHOW CREATE TABLE user：

此时，数据有了，AUTO_INCREMENT 值为4，再使用 TRUNCATE 来清空所有：

TRUNCATE user;

复制代码

结果：

查询数据

查询命令 SELECT，前面我们一直有在使用，其实查询操作是灵活多变的，它的语法结构可以总结如下：

-- 这是一个总结性的语法结构，当你需要使用时可以参考，[] 表示可选。后面每一个细节都会说到。

SELECT 表达式(或字段),... FROM tbl_name

[WHERE 条件] -- 条件

[GROUP BY {字段|位置} HAVING 二次筛选] -- 分组

[ORDER BY {字段|位置|表达式}[ASC|DESC]] -- 排序

[LIMIT 限制结果集的显示条数] -- 限制显示条数

复制代码

使用 * 查询所有字段

这也是我们前面一直用的命令：

SELECT * FROM tbl_name;

复制代码

这里也就不多说了，补充一个知识点就是可以直接查询某库中的某表：

SELECT * FROM db_name.tbl_name;

复制代码

比如我们查询 data_demo 数据库中的 user 表，就可以这样：

-- 注意你的库名和表名可能和我的不一样，如果一步步跟我步骤操作的，应该是一样的

SELECT * FROM data_demo.user;

复制代码

注意：我提前在user表中插入了数据，如果你跟我步骤做的，记得也提前插入几条测试数据。查询结果：

这样查询的好处就是不需要先

USE 数据库。

查询指定字段的信息

使用命令：

SELECT 字段名称,... FROM tbl_name;

复制代码

例如：

SELECT username,age FROM user;

复制代码

给字段起别名查询显示

命令：

SELECT 字段名称 [AS] 别名名称,... FROM [db_name.]tbl_name;

复制代码

例如：

SELECT id AS '编号', username AS '用户名',email AS '邮箱' FROM user;

复制代码

给数据表起别名查询

命令：

SELECT 字段名称,... FROM tbl_name [AS] 别名;

复制代码

例如：

SELECT id,username,email FROM user AS a;

复制代码

给表名起别名在这里看不出啥特别的效果，先记住有这种方法。

表名.字段名 查询

命令：

SELECT tbl_name.col_name,... FROM tbl_name;

复制代码

例如：

SELECT user.id,user.username FROM user;

复制代码

同样看不出有啥特别的效果，先记住有这种方法。

查询数据之 WHERE 详解

WHERE 属于查询操作的重要关键字，这里单独一章节说明。WHERE会筛选出符合条件的数据，使用它的方式如下：

SELECT 字段名或表达式,... FROM tbl_name WHERE 条件

复制代码

其中条件是有几种方式的。

通过比较运算符来筛选数据

前面说到的WHERE id=1;就是运用的比较运算符，可以用的比较运算符有：>、>=、 和 <=>，前面五个不用多说，我主要来说说后面这两个。

<>是不等于的意思，跟!=作用是一样的；<=>是等于的意思，跟=作用类似，但有一个区别就是<=>可以检测 NULL 值。举个例子，我们先改造user表的数据如下：

即为了测试增加两个

NULL值，我使用了更新命令

UPDATE user SET age=NULL WHERE id>2;，请根据自己实际情况改造数据。现在分别使用

= 和

<=> 来查询：

-- 使用 =

SELECT * FROM user WHERE age=NULL;

-- 使用 <=>

SELECT * FROM user WHERE age<=>NULL;

复制代码

结果：

可见使用 <=> 可以检测到 NULL 值，其实检测 NULL 值还有另外一个方法，就是使用 IS [NOT] NULL，如下：

指定范围来筛选数据

我们也可以对某个字段指定值的范围来筛选数据，语法如下：

WHERE 字段名 [NOT] BETWEEN ... AND ...

复制代码

比如我们筛选 id 值在 2 和 4 的数据：

SELECT * FROM user WHERE id BETWEEN 2 AND 4;

复制代码

从结果可以看出，查询结果包含了首位和末尾的数据。

指定集合来筛选数据

指定集合查询数据，将在集合内的值筛选出，语法：

WHERE 字段名 [NOT] IN(值,...);

复制代码

比如查 id 值为 1 和 3 的数据：

SELECT * FROM user WHERE id IN (1,3);

复制代码

再比如我们查 username 值为 BB 和 YY 的数据：

SELECT * FROM user WHERE username IN('BB','YY');

复制代码

没有

username=YY 的值，所以只返回

username=BB 的数据。

使用逻辑运算符筛选数据

可以使用两个逻辑运算符：

AND 逻辑与

OR 逻辑或

举例：

-- 查询 id>3 并且 age>20 的数据

SELECT * FROM user WHERE id>3 AND age>20;

-- 查询 id<3 或者 age>20 的数据

SELECT * FROM user WHERE id<3 OR age>20;

复制代码

结果：

此处仅演示作用，可自行建立数据更丰富的表来测试。

模糊查询

模糊查询很有用，它的语法很简单：

WHERE 字段名 [NOT] LIKE ...

复制代码

它通常要结合占位符来使用：

% 表示任意长度的字符串

_ 表示任意一个字符

查询 username 值中含字母 B 的:

SELECT * FROM user WHERE username LIKE '%B%';

复制代码

从结果知道，

查询时是不区分大小写的。

再查询 username 值长度为 4 的：

SELECT * FROM user WHERE username LIKE '____';

复制代码

查询数据之 GROUP BY 分组

分组是把值相同的放到一个组中，语法如下：

GROUP BY 字段名

复制代码

为了演示方便，我新建一个表并插入数据：

CREATE TABLE user1(

id INT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED DEFAULT 18,

sex ENUM('男','女','保密')

)ENGINE=INNODB CHARSET=UTF8;

INSERT user1 VALUES(NULL,'张三',45,'男'),

(NULL,'李四',18,'男'),

(NULL,'王五',24,'保密'),

(NULL,'小丽',24,'女'),

(NULL,'小红',18,'女');

复制代码

现在按照年龄来分组：

SELECT * FROM user1 GROUP BY sex;

复制代码

结果却报错了：

这里的错误意思是说，要查询的列名必须出现在

GROUP BY后面，由于我们查所有的列，分组是按照

sex来的，所以报错了。另外这个错误跟mysql版本有关系，可能你安装的版本不会有这个错误，如果有错误，可以修改语句：

SELECT sex FROM user1 GROUP BY sex;

复制代码

现在就查询成功了。如果就想查询很多列也是有办法的，自行搜索关键词ERROR 1055 (42000)，网上有详细的说明，我就不多说了。再来看看搜索结果，只显示了组中的一条记录，这样很不直观，我们需要每个组中具体有哪些数据，这时候就利用GROUP_CONCAT()这个函数来解决。

分组配合 GROUP_CONCAT() 函数使用

为了查看每个分组中的具体数据详情，我们需要使用 GROUP_CONCAT()函数。 比如我们想要知道每一组中所有的username详情，可以这样写：

SELECT GROUP_CONCAT(username),sex FROM user1 GROUP BY sex;

复制代码

这样就能清晰的知道了每个分组里的情况，同时发现使用

GROUP_CONCAT()函数后，上面的那个限制错误就不会出现了，所以可以查看所有详情：

SELECT GROUP_CONCAT(id),

GROUP_CONCAT(username),

GROUP_CONCAT(age),

sex FROM user1 GROUP BY sex;

复制代码

分组配合聚合函数使用

聚合函数包括：

COUNT() 统计总数

SUM() 求和

MAX() 求最大值

MIN() 求最小值

AVG() 求平均值

需要注意的是聚合函数不是只能用在分组操作中的，只是这里讲分组时可以配合一起用。后续的进阶篇会详细说mysql中的函数操作

举个例子，按照 sex 分组，查看username详情，并且得到每个分组中的总人数，可以这样写：

-- 使用 COUNT(*) 统计分组数据总数

SELECT GROUP_CONCAT(username),sex,COUNT(*) FROM user1 GROUP BY sex;

复制代码

也可以直接在COUNT()函数中传入字段名，如COUNT(username)，结果也能实现：

区别在于：使用 COUNT(*) 可以识别出 NULL 数据，而使用 COUNT(字段名) 识别不出 NULL

其他的几个函数使用方式一样，一起来使用一下：

-- 按性别分组，查看用户名详情，查看年龄详情，统计数据总数量，求年龄的和，求年龄的最大值，求年龄的最小值，求年龄的平均值；

-- 使用 AS 可以起别名

SELECT sex,

GROUP_CONCAT(username) AS username,

GROUP_CONCAT(age) AS age,

COUNT(*) AS total,

SUM(age) AS sum_age,

MAX(age) AS max_age,

MIN(age) AS min_age,

AVG(age) AS avg_age

FROM user1 GROUP BY sex;

复制代码

结果：

分组配合 WITH ROLLUP 关键字使用

使用 WITH ROLLUP 可以在每条记录末尾添加一条记录，是上面所有记录的总和。 例如：

SELECT GROUP_CONCAT(username),

GROUP_CONCAT(age),

COUNT(*)

FROM user1 GROUP BY sex

WITH ROLLUP;

复制代码

注意最后一行是自动添加的，如果是数字就相加，如果是集合就列出所有。

HAVING 子句对分组结果进行二次筛选

什么意思呢？举个例子，我们先来一个分组：

-- 使用 AS 可以起别名

SELECT GROUP_CONCAT(username) AS detail,

COUNT(*) AS total

FROM user1

GROUP BY sex;

复制代码

现在我们要筛选出分组中总数大于等于 2 的分组，那么可以在上面的语句最后加上一句：

SELECT GROUP_CONCAT(username) AS detail,

COUNT(*) AS total

FROM user1

GROUP BY sex

HAVING COUNT(*) >=2;

复制代码

现在显示的就是总数大于等于 2 的分组了。由于我使用了AS起别名，所以也可以直接使用别名，即 HAVING total>=2，结果同样正确：

查询数据之 ORDER BY 排序

使查询结果按照某一顺序排列，排序的命令如下：

-- ASC 升序；DESC 降序。默认是升序 ASC

ORDER BY 字段名称 ASC|DESC

复制代码

先来看一个表的默认显示顺序：

具体请以你自己本地的数据为准，现在可以试试按照年龄升序排列：

-- 因为默认使用的 ASC，所以升序时可以不加 ASC 后缀

SELECT * FROM user1 ORDER BY age [ASC];

复制代码

现在结果是按照年龄升序排列的。有一个小问题就是前两条数据的年龄是一样的，这样的就以

id升序再排列，我们也可以让他们再按照

id降序排列，即多字段排序：

SELECT * FROM user1 ORDER BY age ASC,id DESC;

复制代码

结果如图所示，前两条在年龄相同的情况下，按照

id 降序再排列。

结合条件查询排序

在条件查询的基础上也可以排序，比如查询年龄大于 18 的，且按照id降序排列的：

SELECT * FROM user1 WHERE age>18 ORDER BY id DESC;

复制代码

随机排序

随机排序使用到 RAND()，每次查询结果顺序都不一样：

SELECT * FROM user1 ORDER BY RAND();

复制代码

结果每次都不相同，就不展示了。

查询数据之 LIMIT 限制结果集显示条数

但数据量很大的时候，我们就需要限制每次查询到的数据量，常见的场景就是分页效果。使用关键字 LIMIT 就可以实现这种操作，它的使用方式有两种：

LIMIT count 这种形式表示显示结果集的前 count 条数据

LIMIT offset,count 表示从 offset 开始，显示 count 条数据，offset 从 0 开始，表示偏移量

例如，我的 user1 表中一共五条数据，现在我只想查看前三条数据，我可以：

SELECT * FROM user1 LIMIT 3;

复制代码

又例如我想看从第二条到第四条的数据，那就是偏移了 1，看 3 条数据：

SELECT * FROM user1 LIMIT 1,3;

复制代码

其实 LIMIT 很灵活，可以结合很多语句一起使用，下面给出几个例子，可自行尝试：

-- 更新 user1 表中前三条数据，将 age 都加 3

UPDATE user1 SET age=age+3 LIMIT 3;

-- 将 user1 表数据按照 id 降序排列，再更新前三条数据，将 age 都减 5

-- 这里需要注意，结果是先排序后做减的

UPDATE user1 SET age=age-5 ORDER BY id DESC LIMIT 3;

-- 删除 user1 表中前两条数据

DELETE FROM user1 LIMIT 2;

-- 删除 user1 表中后面两条数据(默认 id 是升序的)

-- 删除后面的数据，可以先按 id 降序排列，再删除

DELETE FROM user1 ORDER BY id DESC LIMIT 2;

复制代码