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

原创 2016年08月30日 23:15:01

讲什么

如果你是一个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)也能正常插入,但是在开发过程中,数据库发生了修改,那么没有括号的插入语句会产生错误,并且将难以定位错误,所以还是建议使用时加上哦。

总结

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

MYSQL——《SQL必知必会》学习笔记

2017/12/4周一 一天看了一半的图灵程序设计丛书的这本《SQL必知必会》第4版,数据库入门经典书 平台:Windows下的命令行操作 数据库:MYSQL 1.安装会出现的问题 ...

SQL必知必会 第4版pdf

下载地址:网盘下载 内容简介  · · · · · · SQL语法简洁,使用方式灵活,功能强大,已经成为当今程序员不可或缺的技能。 本书是深受世界...

【书山有路】SQL必知必会 第6课

本章的主题是“用通配符进行过滤”,主要介绍什么是通配符,如何使用通配符以及怎样使用LIKE操作进行通配搜索。概念 通配符:用来匹配值的一部分的特殊字符。 搜索模式:由字面值、通配符或者两者构成的搜索条...
  • mrbcy
  • mrbcy
  • 2017年04月01日 09:59
  • 271

《SQL必知必会》(1-7)

《SQL必知必会》读书笔记 说明:本书SQL语句均使用PLSQL Developer客户端,在oracle数据库中实践。 1. 基本概念 表(table):某种特定类型数据的结构化清单。 模式...

测试人员必知必会的SQL知识09

大家晚上好,今天我们继续讲解查询。今天要用的数据库:http://download.csdn.net/detail/xc5683/4764010。今天给大家的是一个mdf和ldf文件,这两个一个是主数...
  • xc5683
  • xc5683
  • 2012年11月13日 20:58
  • 5895

《SQL必知必会》学习笔记(二):数据库基础和简单SELECT

摘要:本文主要是对《SQL必知必会》Lesson 1-3学习总结,主要包括数据库的相关概念,简单的SELECT语句(没有WHERE)以及带ORDER BY的排序检索。...
  • zwxtfqd
  • zwxtfqd
  • 2015年11月18日 20:30
  • 769

测试人员必知必会的SQL知识06

大家晚上那个好,昨天我们已经说到了,怎么创建表,修改表和删除表。这些都像盖房子只搭了个框架,还得向里面填东西才可以。数据库数据库就是管理数据的吗,今天我们就继续看一下怎么向表中填入数据,管理这些数据。...
  • xc5683
  • xc5683
  • 2012年11月10日 19:39
  • 5297

【书山有路】SQL必知必会 第2课

这一课的主题是“检索数据”,介绍如何使用SELECT语句从表中检索一个或多个数据列。提示 SQL语句后要加分号 对SQL关键字使用大写,对列名和表命使用小写。这样做使代码更易于调试和阅读。 多数SQL...
  • mrbcy
  • mrbcy
  • 2017年03月31日 12:47
  • 346

SQL必知必会(第3版)学习笔记【16-22章】

第16章 更新和删除数据更新数据 UPDATE语句 更新表中特定行 更新表中所有行 UPDATE Customers SET cust_email = 'kim@thetoystore.com' ...

读书笔记--SQL必知必会04--过滤数据

4.1 使用WHERE子句 在SELECT语句中,数据根据WHERE子句中指定搜索条件进行过滤。 搜索条件(search criteria)也称为(filter condition)。 WHERE子句...
  • anliven
  • anliven
  • 2017年06月06日 21:44
  • 54
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:必知必会的SQL——你懂得了多少
举报原因:
原因补充:

(最多只允许输入30个字)