《SQL必知必会》读书笔记，30分钟入门SQL！（一）

2022-01-09 08:50:36

准备

工具

mycli，一个使用python编写的终端工具，支持语法高亮，自动补全，多行模式，并且如果你熟悉vi的话，可以使用vi-mode快速移动，编辑。总之，vi + mycli 简直是神器！

同样， postgreSQL 可以使用pgcli。

  1. pip install -U mycli    # 默认你已经安装了pip

样例表

示例中有两个表，分为 student 学生表与 class 班级表。student 表中有 class_id 关联 class 表。以下是两个表数据的 sql。另外，最后有三道小练习题会用到样例表。


create table class
( id int(11) not null auto_increment,
  name varchar(50) not null,
  primary key (id)
);
create table student
( id int(11) not null auto_increment,
  name varchar(50) not null,
  age smallint default 20,
  sex enum('male', 'famale'),
  score tinyint comment '入学成绩',
  class_id int(11),
  createTime timestamp default current_timestamp,
  primary key (id),
  foreign key (class_id) references class (id)
);
insert into class (name)
values ('软件工程'), ('市场营销');
insert into student (name, age, sex, score, class_id)
values ('张三', 21, 'male', 100, 1);
insert into student (name, age, sex, score, class_id)
values ('李四', 22, 'male', 98, 1);
insert into student (name, age, sex, score, class_id)
values ('王五', 22, 'male', 99, 1);
insert into student (name, age, sex, score, class_id)
values ('燕七', 21, 'famale', 34, 2);
insert into student (name, age, sex, score, class_id)
values ('林仙儿', 23, 'famale', 78, 2);



SQL 基础

术语

Database数据库值一系列有关联数据的集合，而操作和管理这些数据的是DBMS，包括MySQL，PostgreSQL，MongoDB，Oracle，SQLite等等。RDBMS 是基于关系模型的数据库，使用 SQL 管理和操纵数据。另外也有一些 NoSQL 数据库，比如 MongoDB。因为NoSQL为非关系型数据库，一般不支持join操作，因此会有一些非正则化(denormalization)的数据，查询也比较快。

Table具有特定属性的结构化文件。比如学生表，学生属性有学号，年龄，性别等。schema (模式) 用来描述这些信息。 NoSQL 不需要固定列，一般没有 schema，同时也利于垂直扩展。

Column表中的特定属性，如学生的学号，年龄。每一列都具有数据类型。

Data Type每一列都具有数据类型，如 char, varchar，int，text，blob, datetime，timestamp。根据数据的粒度为列选择合适的数据类型，避免无意义的空间浪费。如下有一些类型对比

  • char, varchar 需要存储数据的长度方差小的时候适合存储char，否则varchar。 varchar 会使用额外长度存储字符串长度，占用存储空间较大。两者对字符串末尾的空格处理的策略不同，不同的DBMS又有不同的策略，设计数据库的时候应当注意到这个区别。
  • datetime, timestamp datetime 存储时间范围从1001年到9999年。 timestamp 保存了自1970年1月1日的秒数，因为存储范围比较小，自然存储空间占用也比较小。日期类型可以设置更新行时自动更新日期，建议日期时间类型根据精度存储为这两个类型。如今 DBMS 能够存储微秒级别的精度，比如 mysql 默认存储精度为秒，但可以指定到微秒级别，即小数点后六位小数
  • enum 对于一些固定，不易变动的状态码建议存储为 enum 类型，具有更好的可读性，更小的存储空间，并且可以保证数据有效性。

小问题：如何存储IP地址？

Row

数据表的每一行记录。如学生张三。


检索数据

# 检索单列
select name from student;
# 检索多列
select name, age, class from student;
# 检索所有列
select * from student;
# 对某列去重
select distinct class from student;
# 检索列-选择区间
# offset 基数为0，所以 `offset 1` 代表从第2行开始
select * from student limit 1, 10;
select * from student limit 10 offset 1;


排序

默认排序是 ASC，所以一般升序的时候不需指定，降序的关键字是 DESC。使用 B-Tree 索引可以提高排序性能，但只限最左匹配。关于索引可以查看以下 FAQ。

# 检索单列
select name from student;
# 检索多列
select name, age, class from student;
# 检索所有列
select * from student;
# 对某列去重
select distinct class from student;
# 检索列-选择区间
# offset 基数为0，所以 `offset 1` 代表从第2行开始
select * from student limit 1, 10;
select * from student limit 10 offset 1;


上一篇：22 个常用的 Python 工具包（一)


下一篇：《SQL必知必会》读书笔记，30分钟入门SQL！（二）