深入浅出MySQL-7-记录操作 |

索引：

添加记录
修改记录
删除记录
查询记录
WHERE 筛选条件
GROUP BY分组
ORDER BY排序

添加记录

1	INSERT [INTO] tbl_name[(col_name,...)] {VALUE\|VALUES}(VALUES...);

INSERT语句由集中按用法

1.不指定字段名称

需要按照建表时的字段顺序给每一个字段赋值

1	INSERT tbl_name VALUE(value...)

先创建一个表

-- 测试添加记录

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

USE king;

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 'imooc@qq.com' COMMENT '邮箱'

)ENGINE=INNODB CHARSET=UTF8;

-- 不指定字段名称

INSERT user VALUE(1,'king',24,'382771946@qq.com');

INSERT user VALUES(NULL,'queen',25,'queen@qq.com');

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

可以看到就按照字段顺序依次被添加进来了。

2.列出指定字段

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

-- 列出指定字段的形式

INSERT user(username,email) VALUES('rose','rose@qq.com');

INSERT user(age,email,id,username) VALUES(34,'imooc@qq.com',5,'imooc');

添加的时候第一条记录没有指定id和age，由于id是自增的age默认值是18所以得到了如图所示；第二条记录没有字段顺序指定也可以添加。

3.INSERT … SET的形式

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

1 2	-- INSERT ...SET 的形式 INSERT user SET username='d',age=45,email='d@qq.com';

4.INSERT … SELECT

1	INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件]

5.一次添加多条记录

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

(值,....),

(值,...)

-- 一次插入3条记录

INSERT user VALUES(NULL,'a',DEFAULT,DEFAULT),

(NULL,'b',56,'b@qq.com'),

(NULL,'c',14,'c@qq.com');

可以看到记录被一次性的添加进来了：

修改记录

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

-- 测试更新语句

-- 修改第一个用户的信息 id=1

UPDATE user SET age=29 WHERE id=1;

-- 修改id=3的用户，username age email

UPDATE user SET age=47,email='lilys@qq.com',username='lilys' WHERE id=3;

-- 所有用户年龄+10

UPDATE user SET age=age+10;

-- 将id<=5的用户年龄改为-20，将邮箱改为默认值

UPDATE user SET age=age-20,email=DEFAULT WHERE id<=5;

删除记录

1	DELETE FROM tbl_name [WHERE 条件]

-- 测试删除语句

-- 删除用户名为king

DELETE FROM user WHERE username='king';

-- 删除年龄为24的用户

DELETE FROM user WHERE age=24;

如果不添加条件，表中所有记录都会被删除

删除表中所有数据

1 2	-- 删除表中所有记录 DELETE FROM user;

刚才已经看到了，DELETE 清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER 语句将其重置为1
彻底清空数据表

1	TRUNCATE [TABLE] tbl_name;

这个操作可以清除表中所有记录并且会重置AUTO_INCREMENT的值

查询记录

SELECT 查询

SELECT select_expr,... FROM tbl_name

[WHERE 条件]

[GROUP BY {col_name|position} HAVING 二次筛选]

[ORDER BY {col_name|position|expr} [ASC|DESC]]

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

先建立一个表：

-- 测试查询操作

CREATE TABLE user1(

id INT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',

age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',

sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',

addr VARCHAR(20) NOT NULL DEFAULT '北京',

married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已婚',

salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'

)ENGINE=INNODB CHARSET=UTF8;

INSERT user1 VALUES(1,'king',23,'男','北京',1,50000);

INSERT user1(username,age,sex,addr,married,salary) VALUES('queen',27,'女','上海',0,25000);

INSERT user1 SET username='imooc',age=31,sex='女',addr='北京',salary=40000;

INSERT user1 VALUES(NULL,'张三',38,'男','上海',0,15000),

(NULL,'张三风',38,'男','上海',0,15000),

(NULL,'张子怡',39,'女','北京',1,85000),

(NULL,'汪峰',42,'男','深圳',1,95000),

(NULL,'刘德华',58,'男','广州',0,115000),

(NULL,'吴亦凡',28,'男','北京',0,75000),

(NULL,'奶茶妹',18,'女','北京',1,65000),

(NULL,'刘嘉玲',36,'女','广州',0,15000);

查询表中所有记录

1	SELECT * FROM tbl_name;

指定字段的信息

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

只显示 username、addr、age

库名.表名

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

1 2	-- 查询king数据库下user1表中的所有记录 SELECT * FROM king.user1;

给字段起别名

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

-- 查询user1表中的id 编号 username 用户名 sex 性别

SELECT id AS '编号',username AS '用户名', sex AS '性别'

FROM user1;

但是并不会影响原本字段的名称：

1	SELECT id age,age id,username FROM user1;

给数据表起别名

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

1 2	-- 给表起别名 SELECT id,username FROM user1 AS u;

这里展示还看不出效果，之后会详细说明。

表名.字段名的

1	SELECT tbl_name.col_name,... FROM tbl_name;

-- 测试表名.字段名

SELECT user1.id,user1.username,user1.age FROM user1 ;

SELECT u.id,u.username,u.addr,u.sex FROM user1 AS u;

WHERE 条件

会筛选出符合条件的记录

比较运算符

可以使用：>= < <= != <> <=>

-- 测试WHERE 条件的比较运算符

-- 查询id,username,age id=5的用户

SELECT id,username,age FROM user1

WHERE id=5;

SELECT id,username,age FROM user1

WHERE id=50;

查询 id = 5的字段的id、usernam、age

如果没有显示空集不会显示错误

添加desc字段，并更新id<9的用户赋值并查询NUll：

-- 添加desc字段 VARCHAR(100)

ALTER TABLE user1

ADD userDesc VARCHAR(100);

-- 更新id<=9的用户 userDesc='this is a test'

UPDATE user1 SET userDesc='this is a test'

WHERE id<=9;

-- 查询用户userDesc 为NULL的用户

SELECT id,username,age,userDesc FROM user1

WHERE userDesc=NULL;

可以看到这样是显示不出NULL的，但是数据表中实际上由NULL。

<=> 和 =的区别：<=>可以检测NULL值

-- 检测NULL值

SELECT id,username,age,userDesc FROM user1

WHERE userDesc<=>NULL;

IS [NOT] NULL

检测值是否为NULL或者NOT NULL

-- IS [NOT] NULL检测NULL值

SELECT id,username,age,userDesc FROM user1

WHERE userDesc IS NULL;

指定范围

[NOT] BETWEEN … AND

-- 测试范围BETWEEN AND

-- 查询年龄在18~30之间的用户

SELECT id,username,age,sex FROM user1

WHERE age BETWEEN 18 AND 30;

-- 查询薪水在10000~50000之间的用户

SELECT id,username,age,salary FROM user1

WHERE salary BETWEEN 10000 AND 50000;

SELECT id,username,age,salary FROM user1

WHERE salary NOT BETWEEN 10000 AND 50000;

指定集合

[NOT] IN(值,…)

SELECT id,username,age FROM user1

WHERE id IN(1,3,5,7,9,29,45,78);

SELECT id,username,age FROM user1

WHERE username IN('king','queen','lily','rose');

逻辑运算符

AND：逻辑与

OR：逻辑或

-- 测试逻辑运算符

-- 查询性别为男并且年龄>=20的用户

SELECT id,username,age,sex FROM user1

WHERE sex='男' AND age>=20;

-- id>=5 && age<=30

SELECT id,username,age,sex FROM user1

WHERE id>=5 AND age<=30;

SELECT id,username,age,sex FROM user1

WHERE id>=5 AND age<=30 AND sex='男';

-- 要求sex='女' 并且 addr='北京'

SELECT id,username,age,sex,addr FROM user1

WHERE sex='女' AND addr='北京';

-- 查询薪水范围在60000~10000并且性别为男 addr='北京'

SELECT id,username,age,sex,salary,addr FROM user1

WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';

-- 查询id=1 或者用户名为queen

SELECT id,username,age FROM user1

WHERE id=1 OR username='queen';

匹配字符

[NOT] LIKE

%：任意长度的字符串

_：任意一个字符

-- 测试模糊查询

SELECT id,username,age FROM user1

WHERE username='king';

SELECT id,username,age FROM user1

WHERE username LIKE 'king';

-- 要求用户名中包含三

SELECT id,username,age,sex FROM user1

WHERE username LIKE '%三%';

-- 用户名中包含n

SELECT id,username,age FROM user1

WHERE username LIKE '%in%';

-- 要求查询出姓张的用户

SELECT id,username,age FROM user1

WHERE username LIKE '张%';

-- 查询以风结尾的用户

SELECT id,username,age FROM user1

WHERE username LIKE '%风';

-- 用户名长度为三位的用户

SELECT id,username,age,sex FROM user1

WHERE username LIKE '___';

SELECT id,username,age,sex FROM user1

WHERE username LIKE '张_';

SELECT id,username,age,sex FROM user1

WHERE username LIKE '张_%';

GROUP BY对记录进行分组：

把值相同放到一个组中，最终查询出的结果只会显示组中一条记录

按照sex来分组

1 2	SELECT id,username,age,sex FROM user1 GROUP BY sex;

可以看到被分成了两组，但是我们现在看不到详细的信息。

分组配合GROUP_CONCAT()查看组中某个字段的详细信息

-- 按照性别分组，查询组中的用户名有哪些

SELECT GROUP_CONCAT(username),age,sex,addr FROM user1

GROUP BY sex;

配合聚合函数使用：

COUNT()：统计记录总数，如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来；写COUNT(*)会统计NULL值
SUM()：求和
MAX()：求最大值
MIN()：求最小值
AVG()：求平均值

查看组中有几个人：

-- 按照sex分组，得到用户名详情，并且分别组中的总人数

SELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1

GROUP BY sex;

统计表中的总记录数：

-- 测试COUNT()

SELECT COUNT(*) FROM user1;

SELECT COUNT(id) FROM user1;

如果写的是COUNR(字段名称)的话，字段是NULL的话不会统计进来而写COUNT(*)则会算进来。

按照sex分组，得到用户名详情，并且分别组中的总人数：

-- 按照sex分组，得到用户名详情，并且分别组中的总人数

SELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1

GROUP BY sex;

-- 按照addr分组，得到用户名的详情，总人数，得到组中年龄的总和，年龄的最大值、最小值、平均值和

SELECT addr,

GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers,

SUM(age) AS sum_age,

MAX(age) AS max_age,

MIN(age) AS min_age,

AVG(age) AS avg_age

FROM user1

GROUP BY addr;

配合WITH ROLLUP关键使用：会在记录末尾添加一条记录，是上面所有记录的总和

SELECT GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers

FROM user1

GROUP BY sex

WITH ROLLUP;

-- 按照字段的位置来分组

SELECT id,sex,

GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers,

SUM(salary) AS sum_salary,

MAX(salary) AS max_salary,

MIN(salary) AS min_salary,

AVG(salary) AS avg_salary

FROM user1

GROUP BY 2;

HAVING子句对分组结果进行二次筛选（有一点像where，但是where是第一次筛选，having是第二次筛选）

-- 按照addr分组，统计总人数

SELECT addr,

GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers

FROM user1

GROUP BY addr;

-- 对于分组结果进行二次筛选，条件是组中总人数>=3

SELECT addr,

GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers

FROM user1

GROUP BY addr

HAVING COUNT(*)>=3;

ORDER BY排序：

ORDER BY 字段名称 ASC|DESC

-- 测试排序

-- 按照id降序排列

SELECT id,username,age

FROM user1

ORDER BY id DESC;

-- 按照age升序

SELECT id,username,age

FROM user1

ORDER BY age ;

当第一个字段相同的时候，按第二个字段排序

-- 按照多个字段排序

SELECT id,username,age

FROM user1

ORDER BY age ASC,id ASC;

随机记录

SELECT id,username,age

FROM user1

ORDER BY RAND();

LIMIT 限制结果集显示条数：

有两种形式：

LIMIT 值：显示结果集的前几条记录。
LIMIT offset,row_count：从offset开始，显示几条记录,offset从0开始。

这个语句是电商搜索的时候实现分页效果的核心

注意：查询的是结果集，跟编号没有关系。

-- 测试LIMIT语句

-- 显示结果集的前5条记录

SELECT id,username,age,sex

FROM user1

LIMIT 5;

SELECT id,username,age,sex

FROM user1

LIMIT 0,5;

翻页显示

-- 显示前3条记录

SELECT id,username,age,sex

FROM user1

LIMIT 0,3;

SELECT id,username,age,sex

FROM user1

LIMIT 3,3;

EXAMPLE 完整SELECT语句的形式

-- 测试完整SELECT 语句的形式

SELECT addr,

GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers,

SUM(age) AS sum_age,

MAX(age) AS max_age,

MIN(age) AS min_age,

AVG(age) AS avg_age

FROM user1

WHERE id>=2

GROUP BY addr;

SELECT addr,

GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers,

SUM(age) AS sum_age,

MAX(age) AS max_age,

MIN(age) AS min_age,

AVG(age) AS avg_age

FROM user1

WHERE id>=2

GROUP BY addr

HAVING totalUsers>=2;

SELECT addr,

GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers,

SUM(age) AS sum_age,

MAX(age) AS max_age,

MIN(age) AS min_age,

AVG(age) AS avg_age

FROM user1

WHERE id>=2

GROUP BY addr

HAVING totalUsers>=2

ORDER BY totalUsers ASC;

SELECT addr,

GROUP_CONCAT(username) AS usersDetail,

COUNT(*) AS totalUsers,

SUM(age) AS sum_age,

MAX(age) AS max_age,

MIN(age) AS min_age,

AVG(age) AS avg_age

FROM user1

WHERE id>=2

GROUP BY addr

HAVING totalUsers>=2

ORDER BY totalUsers ASC

LIMIT 0,2;

深入浅出MySQL-7-记录操作

添加记录

1.不指定字段名称

2.列出指定字段

3.INSERT … SET的形式

4.INSERT … SELECT

5.一次添加多条记录

修改记录

删除记录

查询记录

SELECT 查询

EXAMPLE 完整SELECT语句的形式

Related Posts

Leave a Reply Cancel reply