SQL入门

---

前言

先给自己挖个坑，SQL今天开始入门，希望可以坚持到底！！

---

一、SQL里的一些基本概念

1.主键

主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。

可以使用多个列作为联合主键，但联合主键并不常用。

2.外键

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

3.索引

通过对数据库表创建索引，可以提高查询速度。

通过创建唯一索引，可以保证某一列的值具有唯一性。

数据库索引对于用户和应用程序来说都是透明的。

二、查询数据

本章就涉及语法了，基本上是语法的文字描述，加一个例子，以及对例子的解读。

1.基本查询

基本查询的语法是SELECT * FROM <表名>

SELECT * FROM students;
#这里就是查询一个名为students的表中的所用条目

2.条件查询

条件查询基本就是SELECT * FROM <表名> WHERE <条件表达式>
条件表达式可以和and or not连用

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
#有括号的需要先进行括号里的运算

3.投影查询

投影查询的基本就是SELECT 列1, 列2, 列3 FROM …；而改列名基本就是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM …

SELECT id, score points, name FROM students WHERE gender = 'M';
#这里将score列更名为points，并加上了上一节的条件查询

4.排序

排序的语句基本就是 ORDER BY 列名 （加DESC是倒序，即从大到小）

SELECT id, name, gender, score #投影
FROM students #基本
WHERE class_id = 1 #条件
ORDER BY score DESC;排序

5.分页查询

分页的语句基本就是LIMIT OFFSET （其中N是最多显示几条记录，M是从第几条开始显示）

SELECT id, name, gender, score #投影
FROM students #基本
ORDER BY score DESC #排序
LIMIT 3 OFFSET 0; #最多显示3条记录，从第0条开始显示

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize，然后根据当前页的索引pageIndex，确定LIMIT和OFFSET应该设定的值：

LIMIT总是设定为pageSize；
OFFSET计算公式为pageSize * (pageIndex - 1)。
这样就能正确查询出第N页的记录集。
其中当省略OFFSET的时候，默认从第0条记录开始。

6.聚合查询

SELECT后跟聚合方式

SELECT COUNT(*) boys #聚合
FROM students #基本
WHERE gender = 'M' #条件

*还可以是其它列名，聚合方式不止COUNT，还有以下

对于聚合查询，SQL还提供了“分组聚合”的功能，分组的语句是GROUP BY 列名，如下：

SELECT COUNT(*) num #聚合
FROM students #简单
GROUP BY class_id #分组

7.多表查询

多表查询的语句是 SELECT * FROM <表1> <表2>

SELECT * FROM students, classes;

得到的是两表的乘积，行数是两表的行数相乘，列数是两表的列数相加；因此，使用多表查询的时候要小心行数“爆炸”。

可以对列进行重命名，同时也可对于表进行重命名

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 #条件筛选

8.连接查询

连接方式一共有四种

注意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 #连接条件

三、修改数据

而对于增、删、改，对应的SQL语句分别是：
INSERT：插入新记录；
UPDATE：更新已有记录；
DELETE：删除已有记录。

1.插入数据

插入数据的语法是 INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);

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

则会在表末插入一行 2, ‘大牛’, ‘M’, 80。
还可以进行多条数据的插入：

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

SELECT * FROM students;

则会在表末插入两行 1, ‘大宝’, ‘M’, 87 和 2, ‘二宝’, ‘M’, 81。

2.更新数据

更新数据的语法是 UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;

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

这里更新了students表中id=1行的name列的值为‘大牛’，score列的值为66。

也可实现对多条数据的更新：

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

更新字段的时候害可以用表达式：

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

对于不存在的条件也不会报错，但也不会更新：

UPDATE students SET score=100 WHERE id=999;
-- 查询并观察结果:
SELECT * FROM students;

没有WHERE语句的时候，UPDATE则会更新整个表，要小心：

UPDATE students SET score=60;

3.删除数据

删除数据的语法是 DELETE FROM <表名> WHERE …;

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

这里删除了students表中id=1的行。

也可实现对多条数据的删除，和UPDATE类似：

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

对于不存在的条件也不会报错，但也不会删除：

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

没有WHERE语句的时候，DELETE则会更新整个表，和UPDATE一样要小心：

DELETE FROM students; 

四、MySQL管理

关于MySQL的管理：

1.数据库

显示数据库的语句是 SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shici              |
| sys                |
| test               |
| school             |
+--------------------+

其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们。其他的是用户创建的数据库。
创建新数据库的语句：

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

删除一个数据库：

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.01 sec)

注意：删除一个数据库将导致该数据库的所有表全部被删除。

对一个数据库进行操作时，要首先将其切换为当前数据库：

mysql> USE test;
Database changed

2.表

显示所有表的语句是 SHOW TABLES

mysql> SHOW TABLES;
+---------------------+
| Tables_in_test      |
+---------------------+
| classes             |
| statistics          |
| students            |
| students_of_class1  |
+---------------------+

查看表结构的语句是 DESC：

mysql> DESC students;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| class_id | bigint(20)   | NO   |     | NULL    |                |
| name     | varchar(100) | NO   |     | NULL    |                |
| gender   | varchar(1)   | NO   |     | NULL    |                |
| score    | int(11)      | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

查看创建表的SQL语句：

mysql> SHOW CREATE TABLE students;
+----------+-------------------------------------------------------+
| students | CREATE TABLE `students` (                             |
|          |   `id` bigint(20) NOT NULL AUTO_INCREMENT,            |
|          |   `class_id` bigint(20) NOT NULL,                     |
|          |   `name` varchar(100) NOT NULL,                       |
|          |   `gender` varchar(1) NOT NULL,                       |
|          |   `score` int(11) NOT NULL,                           |
|          |   PRIMARY KEY (`id`)                                  |
|          | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+
1 row in set (0.00 sec)

创建表使用CREATE TABLE语句，而删除表使用DROP TABLE语句：

mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)

修改表则需要先给表前面加入一列 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;

3.退出

使用EXIT退出MySQL

mysql> EXIT
Bye

五、实用语句

以下是一些非常实用的SQL语句：

1.插入或替换

插入或替换的语法是 REPLACE;

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

若id=1的数据不存在，则实现插入；否则，实现替换。

2.插入或更新

插入或更新的语法是 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的数据不存在，则实现插入；否则，实现对该记录的更新。

3.插入或忽略

插入或忽略的语法是 INSERT IGNORE INTO …;

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

若id=1的数据不存在，则是先插入；否则，不进行任何操作。

4.快照

对表的内容进行复制，可以结合CREATE TABLE和SELECT：

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

SELECT语句的写法和查询数据完全一致。

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表中直接保存查询的结果。

6.强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引idx_class_id必须存在。

总结

本笔记整理参考廖雪峰的SQL教程，感谢大佬的免费学习资源分享！！希望自己学完之后有所收获，有所整理，下次好查询，还有一些测验和考试，因此写了这个入门笔记，希望早日完结撒花~