文章目录
前言
先给自己挖个坑,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查询的写法是:
- 先确定主表,仍然使用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 #连接条件
三、修改数据
而对于增、删、改,对应的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教程,感谢大佬的免费学习资源分享!!希望自己学完之后有所收获,有所整理,下次好查询,还有一些测验和考试,因此写了这个入门笔记,希望早日完结撒花~