sql语句（基础篇）

sql语句

增

插入单条语句
字段的名称和后面的值须一一对应
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

一次插入多条语句

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

删

语法
DELETE FROM <表名> WHERE ...;

改

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

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

查

基本查询

SELECT * FROM <表名>

条件查询

SELECT * FROM students WHERE score >= 80;

投影查询

SELECT 列1, 列2, 列3 FROM ...;

排序查询

SELECT * FROM students ORDER BY score 默认升序
SELECT * FROM students ORDER BY score DESC 降序

分页查询

LIMIT size OFFSET num : 返回size条，从num条开始

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

获取有多少页 (每页三条)
SELECT CEILING(count(id) / 3) FROM students

聚合查询

sql语句中使用聚合函数的操作称为聚合查询

聚合查询
SELECT COUNT(*) FROM students;

分组聚合查询
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

sql常用聚合函数

多表查询

语法
SELECT * FROM <表1> <表2>;

多表查询的结果集可能非常巨大，要小心使用。

连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

INNER JOIN 查询写法

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
3. 然后确定连接条件，使用ON <条件>这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接
4. 可选：加上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;

查看更多连接查询

其他

库操作

SHOW DATABASES : 显示所有数据库
CREATE DATABASE 数据库名 : 创建数据库
DROP DATABASE 数据库名 : 删除数据库
USE 数据库名 ： 切换数据库

表操作

SHOW TABLES : 显示当前数据库下所有表
DESC 表名 ： 展示表结构
SHOW CREATE TABLE 表名 : 展示创建表的sql语句
DROP TABLE 表名 : 删除表
修改表操作
修改表就比较复杂。如果要给students表新增一列birth，使用：
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

要修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

删除列，使用：
ALTER TABLE students DROP COLUMN birthday;

退出mysql
使用EXIT命令退出MySQL
注意EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行。

实用sql语句

在编写SQL时，灵活运用一些技巧，可以大大简化程序逻辑。

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

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

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

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定

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

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

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

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

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

写入查询结果集
如果查询结果集需要写入到表中，可以结合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表中直接保存查询的结果：

> SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average      |
+----+----------+--------------+
|  1 |        1 |         86.5 |
|  2 |        2 | 73.666666666 |
|  3 |        3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)

文章出于廖雪峰的sql教程