MySQL

2019-05-31 本文已影响0人 icon6

1.基本查询

1.1查询数据库表的数据

SELECT * FROM <表名>

SELECT 表示关键字，表示将要执行一个查询
* 表示所有列
FROM 表示将要从哪个表查询

1.2 条件查询

SELECT * FROM <表名> WHERE <条件语句>

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2
```
SELECT * FROM students WHERE score>=90 AND gender = "F"
```
第二种条件是<条件1> OR <条件2>表示满足条件1或者满足条件2
```
SELECT * FROM students WHERE score>80 OR gender="M"
```
第三种条件NOT <条件>表示不符合该条件的记录
```
SELECT * FROM students WHERE NOT class_id=2
```
上述NOT条件NOT class_id = 2其实等价于class_id <> 2，因此，NOT查询不是很常用。
要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算
```
SELECT *FROM students WHERE (score>80 AND score<90) AND gender = "M"
```
如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

常用的条件表达式

条件	表达式举例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'

模糊查询

SELECT * FROM students WHERE name LIKE "小%"

1.3 投影查询

如果我们希望返回某列数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列
```
SELECT id,name,score FROM students
```
使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。
```
SELECT id, score points, name FROM students;
```

2. 排序

一般是根据主键来排序，如果想通过其他条件排序使用ORDER BY 默认ASC可以省略即ORDER BY score ASC和ORDER BY score效果一样。
```
SELECT * FROM students ORDER BY score 
```
如果要反过来，按照高到底排序，我们可以加上DESC表示“倒序”
```
SELECT * FROM students ORDER BY score DESC
```
如果列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：
```
SELECT * FROM students ORDER BY score DESC,gender
```

3. 分页查询

分页查询可以通过LIMIT <M> OFFSET <N>子句实现
```
SELECT * FROM students ORDER BY score DESC LIMIT 3 OFFSET 0
```
- LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始
- 分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：
- LIMIT总是设定为pageSize；
- OFFSET计算公式为pageSize * (pageIndex - 1)。

4. 聚合查询

如果我们要统计一张表的数据量，例如，想查询students表一共有多少条记录，我们可以使用SQL内置的COUNT()函数查询：
```
SELECT COUNT(*) FROM students
```
- COUNT(*) 表示查所有的列
通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：
```
SELECT COUNT(*) number FROM students
```
聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：
```
SELECT COUNT(*) number FROM students WHERE score>80
```

除了COUNT()函数外，SQL还提供了如下聚合函数：

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

分组

SELECT class_id,COUNT(*) classes FROM students GROUP BY class_id

SELECT class_id,gender,AVG(score) FROM students  GROUP BY class_id,gender

 SELECT class_id,AVG(score) FROM students GROUP BY class_id

5. 多表查询

查询多张表的语法是：SELECT * FROM <表1> <表2>
```
SELECT * FROM students,classes
```
多表查询的结果集可能非常巨大，要小心使用。

6. 链接查询

班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列
```
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;
```

7. INSERT

INSERT语句的基本语法是：

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

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

8. UPDATE

UPDATE语句的基本语法是：

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

UPDATE students SET name="阿黄",score="10" WHERE id=1

9. DELETE

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...;

DELETE FROM students WHERE id=1;

DELETE FROM students WHERE id>5 AND id<8; /* 删除多条数据 */

DELETE FROM students; /* 不带WHERE条件 删除整个表数据 */

10. 实用的SQL语句

10.1 插入或替换

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

10.2 插入或更新

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

10.3 插入或忽略

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

10.4 快照

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

新创建的表结构和SELECT使用的表结构完全一致。

10.5 写入查询结果

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

例如，创建一个统计成绩的表statistics，记录各班的平均成绩：

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

然后，我们就可以用一条语句写入各班的平均成绩：

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：