MySQL之基本查询（一）（insert || select）

目录

一、表的增删查改

二、表的增加insert

三、表的读取select

where 条件子句

结果排序

筛选分页结果

---

一、表的增删查改

我们平时在使用数据库的时候，最重要的就是需要对数据库进行各种操作。而我们对数据库的操作一般来说也就是四个操作，CRUD : Create(创建，增), Retrieve(读取，查)，Update(更新，改)，Delete（删除，删）。

二、表的增加insert

语法：insert的作用就是向表中插入记录。

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

下面我们先创建一张学生信息表：

mysql> create table stu(-> id int unsigned primary key auto_increment,-> sn int not null unique comment '学号',-> name varchar(20) not null,-> qq varchar(20)-> );

 

单行数据的全列插入 

mysql> insert into stu values (1,1000,'张三',123456);

对于全列插入，我们不指定要插入的列，就表示要对所有列进行数据的插入。

多行数据的指定列插入 

value_list数量必须和指定列数量及顺序一致。 

mysql> insert into stu (id,sn,name,qq) values (3,1002,'王五',12347),(4,1003,'赵六',123),(5,1004,'田七',120);

插入否则更新

我们在进行数据插入时，由于主键或者唯一键对应的值已经存在而导致插入失败，此时有了主键或者唯一键的冲突，我们可以考虑对表中的值进行更新。

可以选择性的进行同步更新操作，语法：

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...

比如插入了下面的数据发生了主键和唯一键的冲突： 

而我们可以对冲突的主键或者唯一键的相关记录进行更新。

mysql> insert into stu (id,sn,name,qq) values (6,1004,'ef',213432432) on duplicate key update name='ef';

 

注：0 row affected：表中有冲突数据，但冲突数据的值和 update 的值相等。
1 row affected：表中没有冲突数据，数据被插入。
2 row affected：表中有冲突数据，并且数据已经被更新。

替换

除了上面发生主键或者唯一键冲突后，进行更新的操作外，我们也可以直接使用我们想插入的数据对原数据进行替换。

语法：

主键 或者 唯一键 没有冲突，则直接插入。
主键 或者 唯一键 如果冲突，则删除后再插入 。

replace into 表名 (value_list) values (value);

原数据：

我们替换掉主键id为5的数据：

mysql> replace into stu (id,sn,name,qq) values (5,1004,'德布劳内',123214);

1 row affected：表中没有冲突数据，数据被插入。
2 row affected：表中有冲突数据，删除后重新插入。

查询最新数据：

三、表的读取select

使用数据库时，用户对于数据库中的数据进行读取查询的操作也非常重要。我们通常使用select来进行读取查询的操作。

语法：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

我们先创建一张学生的成绩信息表：

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);

然后我们向其中插入一些测试数据：

全列查询

全列查询就是对表中的所有数据进行查询并显示出来。 

select * from 表名;

 

通常情况下不建议使用 * 进行全列查询：1、一张表一定是拥有大量的数据的，查询的列越多，意味着需要传输的数据量越大。2、可能会影响到索引的使用。 

指定列查询

在查询时，我们可以将 * 换成我们想要查询的若干个列的名称，以进行指定列查询。

查询字段为表达式 

select查询的字段除了可以是单独的列名称外，也可以是表达式。

当然，我们还可以对查询的字段进行重命名：

语法：as可以省略。

SELECT column [AS] alias_name [...] FROM table_name;

 

对查询结果去重 

对于下面的查询，如果我们想要查找同学的数学成绩有哪些分数，那么我们就不需要相同的分数，所以就需要对数学成绩进行去重：

我们发现，数学成绩为98的同学有两个，所以需要去重。

语法：

select distinct math from exam_result;

where 条件子句

where子句的作用，就是根据where后面的条件在表中进行特定的查找。

在MySQL中，其也有属于自己的各种运算符。

比较运算符

运算符	说明
>，>=，<，<=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!= 和 <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, ...)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

 逻辑运算符

运算符	说明
and	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
or	任意一个条件为 TRUE(1), 结果为 TRUE(1)
not	条件为 TRUE(1)，结果为 FALSE(0)

where子句使用实例（以上面的exam_result表为基本表） 

~ 英语不及格的同学及英语成绩 ( < 60 ) 

mysql> select name,english from exam_result where english < 60;

~ 语文成绩在 [80, 90] 分的同学及语文成绩

两种查询方法：

mysql> select name,chinese from exam_result where chinese <= 90 and chinese >= 80;

也可以使用BETWEEN a0 AND a1进行查询：

mysql> select name,chinese from exam_result where chinese between 80 and 90;

~  数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

我们也可以使用IN (option, ...)进行查看：

~  姓孙的同学及孙某同学（like）

姓孙的同学：

select name,math from exam_result where name like '孙%';

孙某同学：

select name,math from exam_result where name like '孙_';

 

结果排序

语法：order by

SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

 ASC 为升序（从小到大），DESC 为降序（从大到小），默认排序顺序为 ASC。

注：NULL 视为比任何值都小，升序出现在最上面。order by通常都是对查询到的结果进行排序。

~ 查询同学及数学成绩，按数学成绩升序显示

select name,math from exam_result order by math （asc）

也可以显示地表示需要升序排序：

~ 查询同学姓名及总分，按总分由高到低排序

select name,math+english+chinese 总分 from exam_result order by 总分 desc;

 

~ 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;

 

筛选分页结果

语法：

从 0 开始，筛选 n 条结果：

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

从 s 开始，筛选 n 条结果：

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

 从 s 开始，筛选 n 条结果：

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;