sql重点总结

声明：以下内容总结参考部分来自廖雪峰sql教程

一、关系数据库概述

数据模型

数据库一共有三种模型：

• 层次模型

• 网状模型

• 关系模型

• 随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。因为关系模型理解和使用起来最简单。

• 关系模型是基于数学理论建立的

• 域是一组具有相同数据类型的值的集合。例:
   整数
   实数
   介于某个取值范围的整数
   指定长度的字符串集合
   {‘男’，‘女’}

• 笛卡尔积
  给定一组域D1，D2，…，Dn，允许其中某些域是相同的。
  D1，D2，…，Dn的笛卡尔积为：
  D1×D2×…×Dn ＝｛（d1，d2，…，dn）｜di属于Di，i＝1，2，…，n｝
   所有域的所有取值的一个组合
   不能重复

• 元组（Tuple）
   笛卡尔积中每一个元素（d1，d2，…，dn）叫作一个n元组（n-tuple）或简称元组
   (张清玫，计算机专业，李勇)、
   (张清玫，计算机专业，刘晨) 等都是元组

• 分量（Component）
   笛卡尔积元素（d1，d2，…，dn）中的每一个值di 叫作一个分量
   张清玫、计算机专业、李勇、刘晨等都是分量

• 基数（Cardinal number）
   若Di（i＝1，2，…，n）为有限集，其基数为mi（i＝1，2，…，n），则D1×D2×…×Dn的基数M为：
   笛卡尔积的表示方法
   笛卡尔积可表示为一张二维表
   表中的每行对应一个元组，表中的每列对应一个域

例如，给出3个域：
D1=导师集合SUPERVISOR=｛张清玫，刘逸｝
D2=专业集合SPECIALITY=｛计算机专业，信息专业｝
D3=研究生集合POSTGRADUATE=｛李勇，刘晨，王敏｝

• D1，D2，D3的笛卡尔积为

D1×D2×D3＝｛
        (张清玫，计算机专业，李勇)，(张清玫，计算机专业，刘晨)，
        (张清玫，计算机专业，王敏)，(张清玫，信息专业，李勇)，
        (张清玫，信息专业，刘晨)，(张清玫，信息专业，王敏)，
        (刘逸，计算机专业，李勇)，(刘逸，计算机专业，刘晨)，
        (刘逸，计算机专业，王敏)，(刘逸，信息专业，李勇)，
        (刘逸，信息专业，刘晨)，(刘逸，信息专业，王敏) ｝

• 基数为2×2×3＝12

数据类型

对一个关系表，每一列需要定义其名称以及数据类型，关系数据库支持的标准数据类型包括数值、字符串、时间等：

主流关系数据库

目前，主流的关系数据库主要分为以下几类：

商用数据库，例如：Oracle，SQL Server，DB2等；
开源数据库，例如：MySQL，PostgreSQL等；
桌面数据库，以微软Access为代表，适合桌面应用程序使用；
嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序

SQL

SQL，Structured Query Language是结构化查询语言的缩写，可以对数据库进行增删改查。虽然SQL已经被ANSI组织定义为标准，但是各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。核心功能的SQL各个数据库都支持，但不常用的功能各数据库的支持程度不同。
总的来说，SQL语言定义了这么几种操作数据库的能力：

• DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

• DML：Data Manipulation Language

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

• DQL：Data Query Language

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

语法特点

SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

所以，本教程约定：SQL关键字总是大写，以示突出，表名和列名均使用小写。

关系模型

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。

关系数据库的表和表之间需要建立**“一对多”，“多对一”和“一对一”**的关系，这样才能够按照应用程序的逻辑来组织和存储数据。
例如，一个班级表：

每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”：

在关系数据库中，关系是通过主键和外键来维护的。

主键

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。

所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。

作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

• 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；

• 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

外键

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。
classes表格

students表格

定义一个外键需要4行，好啰嗦QAQ

alter table students                # 更新表格
add constraint fk_class_id    # 外键约束的名称fk_class_id可以任意
foreign key (class_id)           # 指定了class_id作为外键
fererences classes (id);        # 外键参照classes表的id列

只有最后一行有分号
要删除一个外键约束，也是通过ALTER TABLE实现的：

alter table students
drop foreign key fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

多对多

• 一对多：通过一个表的外键关联到另一个表，我们可以定义出一对多关系。

“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：



通过中间表teacher_class可知teachers到classes的关系：

id=1的张老师对应id=1,2的一班和二班；
id=2的王老师对应id=1,2的一班和二班；
id=3的李老师对应id=1的一班；
id=4的赵老师对应id=2的二班。

同理可知classes到teachers的关系：

id=1的一班对应id=1,2,3的张老师、王老师和李老师；
id=2的二班对应id=1,2,4的张老师、王老师和赵老师；
因此，通过中间表，我们就定义了一个“多对多”关系。

一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。
例如，students表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表contacts，我们就可以得到一个“一对一”关系：

把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。
例如对student表，对score列创建索引：

alter table students
add index idx_score (score);

索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

alter table students
add index idx_name_score (name, score);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

二、查询数据

基本查询

select * from students
*表示“所有列”

条件查询

SELECT * FROM <表名> WHERE <条件表达式>
常用的条件表达式：

• 比较运算符：>, <, >=, <=, =, ~=, between...and..
• 逻辑运算符：与，或，非
• like: %表示匹配任意字符任意长度，_表示任意单个字符
• in <值表>，not in <值表>：WHERE Sdept IN ('CS','MA’,'IS' );

多个条件判断：如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

投影查询

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。
SELECT id, score, name FROM students;

使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。

投影查询同样可以接WHERE条件，实现复杂的查询:
SELECT id, score points, name FROM students WHERE gender = 'M';

排序

SELECT id, name, gender, score FROM students ORDER BY score;
默认升序
ORDER BY score desc; 降序

聚合查询

SELECT COUNT(*) FROM students;
要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：
SELECT COUNT(*) num FROM students;

COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件
SELECT COUNT(*) boys FROM students WHERE gender = 'M';

其他聚合函数：

要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

分组聚合 group by

我们知道，可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。如果要继续统计二班、三班的学生数量，难道必须不断修改WHERE条件来执行SELECT语句吗？

SELECT COUNT(*) num FROM students GROUP BY class_id;

但是这3行结果分别是哪三个班级的，不好看出来，所以我们可以把class_id列也放入结果集中：

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

我们再试试把name放入结果集
SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id;
不出意外，执行这条查询我们会得到一个语法错误，因为在任意一个分组中，只有class_id都相同，name是不同的，SQL引擎不能把多个name的值放入一行记录中。因此，聚合查询的列中，只能放入分组的列。

也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

查询中having用法

SELECT子句：指定要显示的属性列
FROM子句：指定查询对象（基本表或视图）
WHERE子句：指定查询条件
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语：只有满足指定条件的组才予以输出
ORDER BY子句：对查询结果表按指定列值的升序或降序排序 

在查询过程中执行顺序：from > where > group（含聚合）> having > order > select

• having用法举例：

SC表，学生选课成绩表

• 查询平均成绩大于等于90分的学生学号和平均成绩

select Sno, AVG(Grade)
from SC
group by Sno
having AVG(Grade) >= 90;

• 查询选修了3门以上课程的学生学号。

select Sno
from SC
group by Sno
having count(*) > 3;

HAVING短语与WHERE子句的区别：
作用对象不同：WHERE子句作用于基表或视图，从中选择满足条件的元组；HAVING短语作用于组，从中选择满足条件的组。
having是在分组后对数据进行过滤
where是在分组前对数据进行过滤

WHERE子句中是不能用聚集函数作为条件表达式

多表查询（笛卡儿积）

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。
SELECT * FROM students, classes;

查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。积的意思也就是students表有M行，classes表有N行，那么结果有M*N行。多表查询又称笛卡尔查询，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录。

合并后若有两列名称相同，可以设置别名：

select
	studenets.id sid,
	students.name,
	students.gender,
	students,score,
	classes.id cid,
	classes.name cname
from students, classes

除了给列起别名，也可以给表起别名，FROM <表名1> <别名1>, <表名2> <别名2>

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;

连接查询（左右内外连接）

连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。有以下几种不同的连接类型：

• 内连接是选出两张表都存在的记录：是最常用的一种连接查询
  
• 左外连接是选出左表存在的记录：
  
• 右外连接是选出右表存在的记录：
  
• 全连接则是选出左右表都存在的记录：
  
  查询语法：
  SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>
  上图中所谓的左表，右表是根据on条件来判断的，例如ON students.class_id = classes.id;，那么左表就是students，右表就是classes

举例说明，先说明，以下例子中，students表中，初始class_id只有1，2，3, classes表中class_id有1，2，3，4.

• 内连接

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;

注意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
right outer join classes c
on s.class_id = c.id

右表是classes表，其class_name集合有1，2，3，4,但是根据ON条件s.class_id = c.id，左表students表中并不存在class_id=4的行，所以学生相关的列如name、gender、score都为NULL。

• 左外连接
  我们给students表增加class_id=5一行，由于classes表并不存在id=5的行，所以，左外的结果会增加一行，对应的class_name是NULL：
  INSERT INTO students (class_id, name, gender, score) values (5, '新生', 'M', 88);

select s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
from students s
left outer join classes c
on s.class_id = c.id;

• 全连接
  它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：

select s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
from students s
full outer join classes c
on s.class_id = c.id;

三、插入行和列

这部分是指插入、修改、删除表中的数据，而第四部分管理数据库是指创建、删除表格。三是针对表中的数据，采用的动词是insert, update/alter, delete，四是针对整个表格，采用动词为create, drop。

插入行和列

• 插入行
  INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
  插入一条记录：
  insert into students (class_id, name, gender, score) values (2, '大牛'，‘M’, 90);
  注意：

1. id是一个自增主键，它的值可以由数据库自己推算出来。可以不写
2. 字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。也就是说，可以写INSERT INTO students (score, gender, name, class_id) …，但是对应的VALUES就得变成(80, 'M', '大牛', 2)。

插入多条记录：

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

• 插入列, 新增一列birth

alter table students add column birth varchar(10) not null;

修改

- 修改行
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
举例：
UPDATE students SET name='大牛', score=66 WHERE id=1;

在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：
UPDATE students SET score=score+10 WHERE score<80;

注意：

1. 如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新。
2. UPDATE语句可以没有WHERE条件，例如，UPDATE students SET score=60;这时，整个表的所有记录都会被更新。所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

- 修改列
要修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：
alter table students change column birth birthday varchar(20) not null;

删除行和列

• 删除行
  DELETE FROM <表名> WHERE ...;

举例：DELETE FROM students WHERE id=1;

注意：

1. 如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。
2. 不带WHERE条件的DELETE语句会删除整个表的数据

• 删除列
  alter table students drop column birthday;

管理MySQL

管理数据库 (增删改查)

• 列出所有数据库：show databases;
• 创建新数据库：create database test; test是数据库的名字
• 删除数据库：drop database test;
• 切换为当前数据库：use test

核心的9个动词

管理表

• 列出当前数据库的所有表：SHOW TABLES;

创建表：

create table students

CREATE TABLE <表名>
      (
      <列名> <数据类型> <列级完整性约束条件>,
      <列名> <数据类型>  <列级完整性约束条件>, 
   …
      <表级完整性约束条件>
      );

<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

CREATE TABLE Student          
         (Sno   CHAR(9) PRIMARY KEY,       /* 列级完整性约束条件,Sno是主码*/                  
        Sname CHAR(20) UNIQUE,             /* Sname取唯一值*/
        Ssex    CHAR(2),
        Sage   SMALLINT,
        Sdept  CHAR(20)
        ); 

创建课程表，表级约束是外码，被参照表是course，被参照列是cno

	CREATE TABLE  Course
             (
             Cno CHAR(4) PRIMARY KEY,
        	 Cname  CHAR(40),            
         	 Cpno     CHAR(4),               	                      
            Ccredit  SMALLINT，
            FOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
            ); 

CREATE TABLE  SC
          (Sno  CHAR(9), 
           Cno  CHAR(4),  
           Grade  SMALLINT，
           PRIMARY KEY (Sno,Cno),  
                          /* 主码由两个属性构成，必须作为表级完整性进行定义*/
           FOREIGN KEY (Sno) REFERENCES Student(Sno),
                         /* 表级完整性约束条件，Sno是外码，被参照表是Student */
           FOREIGN KEY (Cno)REFERENCES Course(Cno)
                          /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
        ); 

修改表

• 删除表：drop table students