SQL学习

查询数据

基本查询

SELECT * FROM <表名> ：查询一个表的所有数据

SELECT * FROM <表名> WHERE <条件表达式> ：根据条件查找表内数据，

如从students表中查询符合条件“分数在80以上”且“男生”性别：

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

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

SELECT * FROM students WHERE NOT class_id = 2; --非

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M'; --多个条件查询

投影查询

SELECT 列1, 列2, 列3 FROM <表名> ：让查询结果只返回指定列，如

SELECT id, score, name FROM students;

-- 使用投影查询，并将列名重命名：

SELECT id, score points, name FROM students;

排序

使用ORDER BY对查询数据进行排序

- 按score从低到高 ASC

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

-- 按score从高到低

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

-- 如果score列有相同分数的，再按gender列排序

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

-- 查询一班的学生成绩，并按照倒序排序：

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

分页

分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现

-- 按score从高到低

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

-- 把结果集分页，每页3条记录。要获取第1页的记录

SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;-- 对结果集从0号记录开始，最多取3条

聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数如：SUM、AVG、MAX、MIN

--统计总数量， COUNT(*)和COUNT(id)实际上是一样的效果

SELECT COUNT(*) FROM students;

-- 使用聚合查询并设置结果集的列名为num:

SELECT COUNT(*) num FROM students;

-- 使用聚合查询并设置WHERE条件:

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

-- 按class_id分组:各班人数

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

多表查询

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

得到的行数是两个表行数的乘积，列数是两个表列数之和

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;

连接查询

先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上，使用INNER JOIN语句

注意INNER JOIN查询的写法是：

先确定主表，仍然使用FROM <表1>的语法；

再确定需要连接的表，使用INNER JOIN <表2>的语法；

然后确定连接条件，使用ON <条件...>，这里的条件s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需连接；

可选：加上WHERE子句、ORDER BY等子句。

-- 选出所有学生，同时返回班级名称

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;

修改数据

INSERT

向数据库表中插入一条新记录时，就必须使用INSERT语句：

INSERT INTO<表名> (字段1, 字段2, ...)VALUES(值1, 值2, ...);

INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);

-- 一次性添加多条新记录

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

-- 查询并观察结果:SELECT * FROM students;

UPDATE

更新数据库表中的记录，我们就必须使用UPDATE语句

UPDATE<表名>SET字段1=值1, 字段2=值2, ...WHERE...;

-- -更新id=5,6,7的记录

UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;

-- 使用表达式更新score<80的记录

UPDATE students SET score=score+10 WHERE score<80;

-- 查询并观察结果:

SELECT * FROM students;

DELETE

删除数据库表中的记录，我们可以使用DELETE语句

DELETE FROM <表名> WHERE ...;

-- 删除id=1的记录

DELETE FROM students WHERE id=1;

-- 查询并观察结果:

SELECT * FROM students;

-- 和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据

DELETE FROM students;