Mysql初学习(1)

本文最后更新于:6 个月前

Mysql中的查询数据以及修改数据语法


查询数据

基本查询

1
SELECT * FROM students;

使用SELECT * FROM students时,SELECT是关键字,表示将要执行一个查询,*表示“所有列”,FROM表示将要从哪个表查询,本例中是students表。

1
SELECT 100+200;

条件查询

1
SELECT * FROM students WHERE score >=80;

SELECT语句可以通过WHERE条件来设定查询条件,查询结果是满足查询条件的记录。例如,要指定条件“分数在80分或以上的学生”,写成WHERE条件就是SELECT * FROM students WHERE score >= 80

其中,WHERE关键字后面的score >= 80就是条件。score是列名,该列存储了学生的成绩,因此,score >= 80就筛选出了指定条件的记录:

1
SELECT * FROM <表名> WHERE <条件表达式>

俩条件同时成立

score >= 80 AND gender = ‘M’

1
SELECT * FROM students WHERE score >= 80 AND gender = 'M';

俩条件满足其一:

1
SELECT * FROM students WHERE score >= 80 OR gender = 'M';

不满足该条件:(不常用)

1
2
3
SELECT * FROM students WHERE NOT class_id = 2;

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

加括号可以改变其优先级,not优先级最高

条件 表达式举例1 表达式举例2 说明
使用=判断相等 score = 80 name = ‘abc’ 字符串需要用单引号括起来
使用>判断大于 score > 80 name > ‘abc’ 字符串比较根据ASCII码,中文字符比较根据数据库设置
使用>=判断大于或相等 score >= 80 name >= ‘abc’
使用<判断小于 score < 80 name <= ‘abc’
使用<=判断小于或相等 score <= 80 name <= ‘abc’
使用<>判断不相等 score <> 80 name <> ‘abc’
使用LIKE判断相似 name LIKE ‘ab%’ name LIKE ‘%bc%’ %表示任意字符,例如’ab%’将匹配’ab’,’abc’,’abcd’

投影查询

我们只想返回一个表中的某几列数据

例如,从students表中返回idscorename这三列:

SELECT id, score, name FROM students;

投影时还可以将投影的列名更改

它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...

例如,以下SELECT语句将列名score重命名为points,而idname列名保持不变:

SELECT id, score points, name FROM students;

排序

我们使用SELECT进行查询时,数据库通常使用主键排序,如果我们想按照其他条件排序

如果我们要根据其他条件排序怎么办?可以加上ORDER BY子句。例如按照成绩从低到高进行排序:

1
2
3
SELECT id, name, gender, score FROM students ORDER BY score;

SELECT id, name, gender, score FROM students ORDER BY score DESC;

(加上DESC表示倒叙)

如果score列有相同的数据,进一步按照其他列排序使用ORDER BY score DESC, gender表示先按score列倒序,如果有相同分数的,再按gender列排序:

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

如果有WHERE子句,那么ORDER BY子句要放到WHERE子句后面。

1
2
3
4
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

如果数据比较多,需要分页显示

这个查询可以通过LIMIT <M> OFFSET <N>子句实现。

1
2
3
4
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示,对结果集从0号记录开始,最多取3条。注意SQL记录集的索引从0开始。

如果要查询第2页,那么我们只需要“跳过”头3条记录,也就是对结果集从3号记录开始查询,把OFFSET设定为3:

1
2
3
4
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

OFFSET是可选的,如果只写LIMIT 15,那么相当于LIMIT 15 OFFSET 0

在MySQL中,LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15

使用LIMIT <M> OFFSET <N>分页时,随着N越来越大,查询效率也会越来越低。

聚合查询

查找一共有多少条记录

1
SELECT COUNT(*) FROM students;

COUNT(*)表示查询所有列的行数,要注意聚合的计算结果虽然是一个数字,但查询的结果仍然是一个二维表,只是这个二维表只有一行一列,并且列名是COUNT(*)

设置别名

1
SELECT COUNT(*) num FROM students;

COUNT(*)COUNT(id)实际上是一样的效果。

函数 说明
SUM 计算某一列的合计值,该列必须为数值类型
AVG 计算某一列的平均值,该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值

注意,MAX()MIN()函数并不限于数值类型。如果是字符类型,MAX()MIN()会返回排序最后和排序最前的字符。

要特别注意:如果聚合查询的WHERE条件没有匹配到任何行,COUNT()会返回0,而SUM()AVG()MAX()MIN()会返回NULL

如果要统计多个班的数据

1
SELECT COUNT(*) num FROM students GROUP BY class_id;

执行这个查询,COUNT()的结果不再是一个,而是3个,这是因为,GROUP BY子句指定了按class_id分组,因此,执行该SELECT语句时,会把class_id相同的列先分组,再分别计算,因此,得到了3行结果。

但是这3行结果分别是哪三个班级的,不好看出来,所以我们可以把class_id列也放入结果集中:

1
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

也可以使用多个列进行分组。例如,我们想统计各班的男生和女生人数:

1
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查询

查询多个表的数据

例如,同时从students表和classes表的“乘积”,即查询数据,可以这么写:

查询多张表的语法是:SELECT * FROM <表1> <表2>

1
SELECT * FROM students, classes;

它表示两个表两两乘在一起,行的总数是两个表的行数相乘

如果两个表有列的名字相同则可以给他们起别名

1
2
3
4
5
6
7
8
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;

也可这也设置

1
2
3
4
5
6
7
8
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c;

也可以添加条件

1
2
3
4
5
6
7
8
9
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

连接查询

先查询所有学生的数据

1
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

利用班级id查询班级名字

1
2
3
4
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
  1. 先确定主表,仍然使用FROM <表1>的语法;
  2. 再确定需要连接的表,使用INNER JOIN <表2>的语法;
  3. 然后确定连接条件,使用ON <条件...>,这里的条件是s.class_id = c.id,表示students表的class_id列与classes表的id列相同的行需要连接;
  4. 可选:加上WHERE子句、ORDER BY等子句。

外连接

1
2
3
4
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

区别:如果class.id中只有1,2,3而c.id有1,2,3,4则外连接会显示id为4的数据,而其他数据全为null,内连接则不会出现这一行

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

指向那一边就会把哪一边多出来的数据列出来

JOIN查询仍然可以使

可用WHERE条件和ORDER BY排序。

修改数据

INSERT

基本语法为:INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);

例如,我们向students表插入一条新记录,先列举出需要插入的字段名称,然后在VALUES子句中依次写出对应字段的值:

1
2
3
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
-- 查询并观察结果:
SELECT * FROM students;

字段顺序一定要和数值得对应顺序一致,字段得顺序不一定要和数据表中字段顺序一致

可一次性添加多个记录

1
2
3
4
5
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);

SELECT * FROM students;

UPDATE

跟新字段数据,基本语法:UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;

1
2
3
UPDATE students SET name='大牛', score=66 WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students WHERE id=1;

where条件实际上和select语句得条件是一样的,所以完全可以一次性跟新多条记录

1
2
3
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
-- 查询并观察结果:
SELECT * FROM students;

在update得语句中也可以使用表达式

1
2
3
UPDATE students SET score=score+10 WHERE score<80;
-- 查询并观察结果:
SELECT * FROM students;

如果where语句未匹配到任何字段,不会报错也不会有任何得数据跟新

注意,如果没有任何where语句,那么跟新得是表中得所有数据,所以在跟新数据之前最好先用select语句测试where条件是否筛选除了数据集

1
UPDATE students SET score=60;
  • 在使用mysql这类真正得关系数据库进行跟新数据时,会返回跟新得行数以及where条件匹配的行数

例如,更新id=1的记录时:

1
2
3
mysql> UPDATE students SET name='大宝' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

当更新id=999的记录时:

1
2
3
mysql> UPDATE students SET name='大宝' WHERE id=999;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

MySQL会返回0,可以从打印的结果Rows matched: 0 Changed: 0看到

DELETE

DELETE语句的基本语法是:

1
DELETE FROM <表名> WHERE ...;

删除id=1的数据:

1
2
3
DELETE FROM students WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students;

当然也可以使用where语句一次性删除多条记录

1
2
3
DELETE FROM students WHERE id>=5 AND id<=7;
-- 查询并观察结果:
SELECT * FROM students;

和跟新数据相同,如果where语句未匹配到任何数据,则不会有任何数据删除

若未使用where语句,则会删除掉所有数据表中的数据

在使用MySQL这类真正的关系数据库时,DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。

例如,分别执行删除id=1id=999的记录:

1
2
3
4
5
mysql> DELETE FROM students WHERE id=1;
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM students WHERE id=999;
Query OK, 0 rows affected (0.01 sec)

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载先请联系作者且注明出处!