必知必会的SQL——你懂得了多少

讲什么

如果你是一个SQL菜鸟，只会用select * from table;类似这样简单的语句，或者说你对SQL语句自认为半知半解，那么恭喜你来对了。本文主要介绍基础SQL语法，助你扫盲，打通基本脉络。

---

数据库定义语言（DDL）

数据类型

1、数字家族：

tinyint	smallint	mediumint	int	bigint
1个字节 （正整数）	2个字节	3个字节	4个字节	8个字节

（1）在mysql中integer与int的效果是相同的，一般整数使用int即可。
（2）decimal和numeric为精确整数型数据，在mysql中可以互相替代，不适宜于自增长（identify）的数据类型，用法是decimal（长度,小数位数），整数部分位数和数字大小有关。
（3）real和float都是浮点型，real=float（24），注意：double是双精度型。

2、字符串：

char是固定长度的字符串，不足用空格补齐；varchar是用于可变长度的字符串。看上去char似乎一无是处，其实不然，varchar的灵活是以牺牲数据库存储和查询的性能为代价，所以当字符串长度差不多时，用char有其好处。

3、时间（MYSQL中）：

datetime	timestamp	date	year
8字节	4字节	3字节	1个字节
YYYY-MM-DD HH:MM:SS	YYYY-MM-DD HH:MM:SS	YYYY-MM-DD	YYYY
1000-01-01 00:00:00 ~ 9999	1970-01-01 00:00:01 ~ 2038	1000-01-01~ 9999-12-31	1901 ~ 2155

注：timestamp有一个特性，在insert和update时能够以当前时间插入/更新。

基本模式定义

1、创建表

create table r
( A1  D1
  ... ...
  An  Dn 
  <完整性约束>);

2、删除表

drop table r;

3、清空表

delete from r;

4、给已有表添加属性

alter table r add A D;//表中已有数据增加的新属性都默认为null

4、从已有表删除属性

alter table r drop A;

数据库操作语言（DML）

SQL查询

单表查询

SQL查询是数据库最常用、最重要的功能之一。
查询由以select、from、where开头的三个子句组成,其中where子句还可以由and和or连接。
通常，最简单的查询为单关系查询:

select A from r where ...

通常，由于去重会花费一定的时间，因此，默认是不去重的（all），此外，我们可以通过对属性添加关键词distinct来达到强制去重的效果

select [all/distinct] A from r where ...  //方括弧表示可有可无

算数运算

对于数字类型的数据可以使用四则运算法则，例如：

//好处是能够在查询的过程中就对数据进行初步处理
select name,grade*1.2 from students 

对于数字、字符串和时间还可以使用<、<=、=、>=和>进行运算，例如：

select year from time where year>2000 and year<2017

多表查询

例如有如下两个关系：
people（ID,name,age,job）， employee（job,offer）
用逗号连接的2个关系生成笛卡尔积，通常两个关联的表还要满足外键约束：

select * from people,employee where people.job=employee.job 

注意：即使两个表建立了外键约束也不能省去后面的where子句，因为查询不会对数据库中的数据产生改变，因此，查询语句不会默认满足外键约束。
两个表在查询from子句执行后生成如下表格

ID	name	age	job	job1	offer

为了方便这种查询,同时减少冗余的列，于是有了自然连接：

select name,offer from people natural join employee 

它将生成下表

ID	name	age	job	offer

但是自然连接也有其不足，即它默认两个表中属性名相同的属性进行比较，但是当两个表中有多个属性相同而我们只需要其中一个时，这是不符合我们要求的，这时，我们可以使用连接：

select name,offer from people natural join employee using job

顺便提一下，以上查询只会查到有工作的人的信息，如果一个人没有工作，那么他将不会出现在查询结果中，如果我们想要他出现在结果中，我们必须用到外连接。

附加运算

此外，我们在查询的时候还可以玩一些小花样，如更名运算。它常用于以下情况：
（1）不同的表中有相同的属性名，我们希望在查询结果中加以区分；
（2）算数运算的结果（如offer*2）名称发生了变化，我们希望它不变；
（3）关系的自身比较，示例如下：
如果我们想从表people（id,name,job,offer）中找出offer比至少一个程序员高的所有人，可以这样写

SELECT p1.* from people AS p1,people AS p2 where p1.offer>p2.offer and p2.job='程序员'//不了解的可以试试

此外，模糊运算like也是很常用的工具，%表示任意字符串，_表示任意一个字符，例如

select * from employee where job like '%开发工程师';//表示选择工作名字以开发工程师结尾的职位 

还有一些如*、order by、between等比较简单的关键词就不赘述了。

集合运算

集合运算是查询结果之间的集合运算。通常，对相同表的不同查询条件会产生不同的结果，这些结果可以视作集合，能够进行集合的∩、 ∪和－运算，使用的关键词分别是intersect、union和except。例如：

(select name from people) except (select name from people join employee using job)//查到没有工作的人的名字

聚集函数

聚集函数查到的通常不再是一个关系而是一个值。SQL提供5个固有聚集函数：

avg	min	max	sum	count
平均值	最小值	最大值	总和	计数

//查询所有有工作的人的平均工资
select avg(offer) as avg_offer from people join employee using job 

此外，我们用到的还有分组查询，示例如下

//按职位进行分组查询工作名称和该工作从事者的平均工资
select job,avg(offer) from people join employee using job group by job

有时我们对特定分组（比如低收入职业人群）比较感兴趣，这时就用到分组限定子查询，示例如下:

//按职位分组，查询平均工资低于2000的职位名和其中工作者的平均工资
select job,avg(offer) from people join employee using job group by job having avg(offer)<2000

嵌套查询

嵌套查询通常比较少用，但是了解一下也不是坏处。要理解嵌套查询的关键是要理解：查询的本质是从一个或多个关系中产生一个新的关系，因此嵌套查询中允许使用in、some和all与比较符合结合，如>all(关系)，示例如下：

//查询所有平均工资低于2000的职位的全部工作人员
select name from people where job in (select avg(offer) as avg_offer from people join employee using job having avg(offer)<2000 )

空

1、在对整个元组进行查询时，元组中如有空值，不影响查询；
2、在对单个属性进行查询、运算时，空值常被当做没有这一条记录，在count运算中尤为注意；
3、想查询属性为空在mysql中可以用A is null来判断，不要用A=null;

SQL修改

1、删除：delete from r where P;
2、插入：insert into r（A1、A2）values(a1,a2);
3、更新：upadate r set A=? where ?.
顺便一提，插入时虽然可以不要表名后面的那个括号（A1、A2）也能正常插入，但是在开发过程中，数据库发生了修改，那么没有括号的插入语句会产生错误，并且将难以定位错误，所以还是建议使用时加上哦。

总结

这是我第一次写博客，总结的东西都浅薄得很，有的东西讲得不够清楚，虽然我开始也是想讲的更加详细一点的，但是写着写着发现篇幅实在是太多了，朋友们未必愿意看下去，分成几个博客我又觉得没必要，所以就成这个样子了。由于我的能力有限，写的有谬误的地方还望大家指正。