mysql 学习笔记（5）-- 增删改查

最新推荐文章于 2024-01-18 16:21:57 发布

不学无术小垃圾

最新推荐文章于 2024-01-18 16:21:57 发布

阅读量260

点赞数

原文链接：https://www.runoob.com/mysql/mysql-insert-query.html

版权

FROM 菜鸟教程: https://www.runoob.com/mysql/mysql-insert-query.html

1. 插入数据

方式一：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

实例如下：

insert into runoob_tbl (runoob_title, runoob_author, submission_date)
VALUES
("myTest", "test", NOW());

PS: 实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。

注意：

（1）如果数据是字符型，必须使用单引号或者双引号，如："value"。

（2）如果一条SQL语句太长，我们可以通过回车键来创建一个新行来编写 SQL 语句，SQL 语句的命令结束符为分号 ;。

（3）规定插入的列名时，可以不用加反引号。

添加多列 实例如下：

方式二：

如果所有列都需要添加数据，可以不规定列进行数据添加 👇

实例：

insert into runoob_tbl
values
(null,"test2", "me", NOW());

如果添加过主键自增（PRINARY KEY AUTO_INCREMENT）第一列在增加数据的时候，可以写为0或者null，这样添加数据可以自增，从而可以添加全部数据，而不用特意规定那几列添加数据。

添加多列 实例如下：

insert into runoob_tbl
values
(0, 'test4', 'test',now()),
(0, 'test5', 'test',now()),
(0, 'test6', 'test',now());

2. 查询数据

1. 查询某个表的所有记录

select * from 表名;

2. mysql 查询数据通用的语法

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。(相当于mongodb中的skip)

2.1 WHERE 子句（筛选数据的条件）

1. select 语句使用 where 子句从数据表中读取数据的通用语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

使用实例如下：

select * from runoob_tbl where runoob_author='test';

操作结果：

注意：使用 where 子句的字符串比较是不会区分大小写的，但可以通过 BINARY(或者小写的 binary) 关键字来设定where子句的字符串比较是区分大小写的。

使用实例如下：

（1）

select * from runoob_tbl where binary runoob_author='test';

操作结果：

（2）

select * from runoob_tbl where binary runoob_author='TEST';

操作结果：

2.2 like 子句（like子句可以用来替代等号，通常用作模糊查询，等号则相当于精确查询）

like 的执行效率：LIKE运算符要对字段数据进行逐一扫描匹配，实际执行的效率比较差。

语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在 WHERE 子句中指定任何条件。
你可以在 WHERE 子句中使用LIKE子句。
你可以使用LIKE子句代替等号 =。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用 AND 或者 OR 指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

***************************** like 匹配/模糊匹配，会与 % 和 _ 结合使用 *****************************

'%a'     // 以a结尾的数据
'a%'     // 以a开头的数据
'%a%'    // 含有a的数据
'_a_'    // 三位且中间字母是a的
'_a'     // 两位且结尾字母是a的
'a_'     // 两位且开头字母是a的

***************************** 在 where like 的条件查询中，SQL 提供了四种匹配方式，如下：*****************************

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个 ---- 使用的不是like，而是regexp。
[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

实例1：

 select * from test where name like 'zhang%';

实例2：

select * from test where name regexp 'zhang[1-9]';

2.3 union 操作符

作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

语法：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

union all 和 union distinct 的区别：

union all ：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

union distinct ：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

理解：union 是将多个表的数据查询结果放到一个集合里（不用非得是相同的字段名，就是将结果都放到一个集合里面，返回结果的字段名以第一个查询条件为准, 需要注意的是要返回的字段个数要相同！）

eg. 见 https://www.runoob.com/mysql/mysql-union-operation.htm

2.4 排序（order by ）

语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。
你可以添加 WHERE...LIKE 子句来设置条件。

按照拼音排序：

如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：

SELECT * 
FROM runoob_tbl
ORDER BY runoob_title;

如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：

SELECT * 
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);

2.5 分组（ group by 语句）

GROUP BY 语句根据一个或多个列对结果集进行分组。

理解：得到的结果中，每行的数据就是分组的每项（不知道对不对）

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

语法：

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

例子：https://www.runoob.com/mysql/mysql-group-by-statement.html

eg. 统计一个学生在每个班级的学习的课程数

select clas_id, count(clas_id) from student_study where student_id = 10 group by clas_id;

使用 with rollup : 实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

eg. 统计每个学生的总学习时长

select student_id, sum(study_time) from student_study group by student_id with rollup;

使用 coalesce（汇总） : 设置取代 null 的名称 select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

select coalesce(clas_id, '总时长'), sum(study_time) from student_study where student_id = 2120 group by clas_id with rollup;

2.6 having 子句：

1. 筛选分组后的数据 (不能使用where去筛选分组后的数据，因为where只能查询表中存在的字段)

select coalesce(student_id, '学习总时长'), sum(study_time) as total_study_time from student_study group by(student_id) with rollup having sum(study_time) > 10000;

2. 和where的作用类似

eg.（理解：使用having的话，having 子句查询得是 select 语句中使用的字段->待确定）

2.7 where / group by / having

where：数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。

group by: 对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。

having：用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。

执行顺序

select –>where –> group by–> having–>order by

2.8 正则表达式（ regexp 操作符）

eg.

select * from runoob_tbl
where runoob_title regexp 'n$';

2.9 mysql 连接的使用（从多个表中读取数据）

你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

eg.https://www.runoob.com/mysql/mysql-join.html

注意：MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样

inner join 使用 eg. (注意用到是 on ，不是 where )

select a.runoob_id, a.runoob_author, b.runoob_count 
from runoob_tbl a inner join tcount_tbl b 
on a.runoob_author = b.runoob_author;

操作结果：

left join 使用eg.

select a.runoob_id, a.runoob_author, b.runoob_count 
from runoob_tbl a leftjoin tcount_tbl b 
on a.runoob_author = b.runoob_author;

结果：

right join 使用eg.

select a.runoob_id, a.runoob_author, b.runoob_count 
from runoob_tbl a right join tcount_tbl b 
on a.runoob_author = b.runoob_author;

结果：

注：以下两种写法的查询结果是相同的：
使用 inner join 查询：

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

使用 where 查询：

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

2.9 NULL 值处理（查询值为NULL / NOT NULL 的数据）

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

例子：https://www.runoob.com/mysql/mysql-null.html

eg1.

select * from runoob_test_tbl
where runoob_count is null;

eg2.

select * from runoob_test_tbl
where runoob_count is not null;

3. 更新记录

语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。

实例：

update runoob_tbl set runoob_author='M' where runoob_id=10;

4. 删除记录

语法: （delete from 表名 from where 删除条件）

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

实例1：删除 runoob_tbl 数据表中 runoob_id为12的数据

delete from runoob_tbl where runoob_id=12;

实例2：删除 runoob_tbl 数据表中的所有数据

delete from runoob_tbl;

4.1 delete / drop / truncate 区别：

delete，drop，truncate 都有删除表的作用，区别在于：

1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。