这是我的史上最细SQL实战系列的第一篇文章：基础知识篇。本系列文章核心不在于总结Mysql相关的八股（Mysql超全八股可见此文：https://www.nowcoder.com/discuss/583297999409889280?sourceSSR=search），本系列旨在：1.快速掌握sql知识，会写面试时常见的sql题目；2.了解在实际工作中需要注意的超多数据库和表设计相关的知识和坑点。

本系列文章分为3个部分：1基础知识篇；2sql笔试篇；3公司级数据库与表设计相关知识与坑点总结。

另外：

想要学习Java冲实习或冲春招的，我能助你一臂之力，我之前整理了高质量可速成的魔改外卖项目话术和7000字轮子项目话术，还有超全超精品八股大全专栏，怎么写简历，怎么包装实习经历，怎么0基础速成冲春招和实习等等等等精品帖子，大家可以去看看我的精品文章汇总帖子：https://www.nowcoder.com/discuss/721704696242536448?sourceSSR=users

我的八股大全、算法、项目话术全专栏（20w人学习，超千人订阅，牛客最受欢迎最高质量java八股专栏，内容包含： 1.八股大全：多一句没有少一句不行的最精简八股整理，完全可以应付校招社招的八股拷打！ 2.速成项目话术：目前有魔改苍穹外卖项目话术（额外扩展了很多技术亮点），能速成拿去面试，后面会更新魔改黑马点评、商城项目等等热门高质量项目话术 3.智力题超详细题解汇总； 4.面试时非技术问题话术整理，绝对震惊面试官一年； 5.算法lc hot100全题系列题解：绝对通俗易懂：https://www.nowcoder.com/creation/manager/columnDetail/j8ZZk0

---------------------------------------------------------------------------------

1.基础知识

1.1 什么是关系型数据库？

关系数据库管理系统RDBMS(Relational Database Management System)的特点：

• 数据以表格的形式出现
• 每行为各种记录
• 每列为记录所对应的数据域
• 许多的行和列组成一张表单
• 若干的表单组成database

1.2 数据类型

MySQL 支持多种数据类型，包括：

• 数值类型：INT、FLOAT、DOUBLE、DECIMAL
• 字符串类型：CHAR、VARCHAR、TEXT、BLOB
• 日期与时间类型：DATE、TIME、DATETIME、TIMESTAMP

1.2.1 数值类型

DECIMAL说明：

1）M 表示数据的最大总长度(不包括小数点，范围为1～65)；D 表示：固定小数位（范围0～30，但不得超过M）；

例：decimal(5,2)

可以存储123.45 ，存入数据的时候，按四舍五入计算。

2）在计算总长度时要优先考虑小数位，也就是D的约束

例：DECIMAL(5,3)

1.2345 --- 小数点后最多3位，自动四舍五入数据截断后保存，1.235

1.2 --- 小数未满部分补0。按照1.200保存。

123.45 --- 因为小数部分未满3位，要补0.所以保存应该123.450。所以整个位数超出了5，有问题。

3）D不能超过M值，若D等于M，如DECIMAL(5,5)

最大存储值为0.99999

4）适用场景

适合保存货币值，比如话费就可以用decimal来装的

int(xx)说明：

int(11)不是限制int的长度为11位，而是字符的显示宽度，例如插入数据1，显示为00000000001。

在字段类型为int时，无论你显示宽度设置为多少，int类型能存储的最大值和最小值永远都是固定的。

1.2.2 日期类型

1.2.3 字符串类型

CHAR和VARCHAR都需要指定长度，他们的区别在于：

1）存储方式不同：

• char 对英文(ASCII)字符占用1个字节，对一个汉字占用2个字节，varchar 对每个英文(ASCII)字符都占用2个字节，对一个汉字也只占用两个字节。但对于utf8，一个字符都会占用3个字节。
• char(n)，如果实际使用字符不足n，会在后面用空格补全存入数据库中。varchar(n)则不会。
• 因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销：是用来保存数据所占用的空间长度，如果数据本身小于127个字符：额外开销一个字节；如果大于127个，就开销两个字节。例如对于utf8：

2）char效率高于varchar

因此当在长度固定的场景，例如：身份证号，手机号，电话等用char效率高，空间也不浪费

1.2.4 二进制类型

2.数据库相关操作

查询所有数据库:
mysql> show databases;

创建数据库（tests）并编码（utf-8）：
mysql> create database tests character set utf8 collate utf8_general_ci;

选择(使用)数据库:
mysql> use tests;

查询当前正在使用的数据库名称
mysql> select database();

创建数据库:
mysql> create database 数据库名称;

判断数据库不存在再创建: 
mysql> create database if not exists 数据库名;

删除数据库:
mysql> drop database 数据库名称;

判断数据库存在再删除:
mysql> drop database if exists 数据库名称;

3.数据表相关操作

创建表：

CREATE TABLE [if not exists] 表名(
属性名 数据类型 [完整性约束条件],
. .
属性名 数据类型 [完整性约束条件]

)[ENGINE=引擎名 AUTO_INCREMENT=自动累加起始值 CHARSET=编码格式;];

查看所有数据表：
mysql> show tables;

创建数据表：
mysql> create table 表名 (
	ID int not null primary key, # 不为空，设为主键
	name varchar(20)
	);

复制数据表：
mysql> create table 表名 like 来源数据表名;

查看表结构：
mysql> desc 数据表;
或
mysql> describe 数据表;
show create table 数据表；查看表详细结构，可以看到建表sql
修改表名:
mysql> alter table 表名 rename to 新表名;

修改表的字符集(编码):
mysql> alter table 表名 character set 字符集名称;

添加字段：
mysql> alter table 表名 add 字段 数据类型;

删除列:
mysql> alter table 表名 drop 列名;

删除表
mysql> drop table 表名;
或
mysql> drop table if exists 表名 ;

添加数据:
mysql> insert into 表名(列名1,列名2,...) values(值1,值2,...);

添加多条数据
mysql> insert into 表名(列名1,列名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;

删除数据:
mysql> delete from 表名 where 条件

如果有用到自增ID，又想删除全表的话，用delete删除的话有个弊端就是ID还是会从原有的基础上往上类。建议最好使用：
mysql> truncate 表名;
因为该方式效率较高，把原表直接drop掉然后再新建一张一模一样的。所以ID还是从1开始自增。

修改数据：
mysql> update 表名 set 字段1 = 设置值1, 字段2 = 设置值2,... where 条件;
如果不加任何条件，则全表修改。

部分查询数据条件：
SELECT * FROM test WHERE id >= 100;
SELECT * FROM test WHERE id >= 100 AND  id <=100;
SELECT * FROM test WHERE id BETWEEN 100 AND 100;
SELECT * FROM test WHERE id IN (1,3,4);
// 关于NULL
SELECT * FROM test WHERE name = NULL; 错误,因为null值不能使用=或（!=) 判断
SELECT * FROM test WHERE name IS NULL;(正确)
SELECT * FROM test WHERE name  IS NOT NULL;(正确)
// 查询姓林的有哪些？< like>
SELECT * FROM test WHERE NAME LIKE '林%';
// 查询姓名第二个字是中是的人
SELECT * FROM test WHERE NAME LIKE "_中%";
// 查询姓名是三个字的人（注：为三个下划线_）
SELECT * FROM test WHERE NAME LIKE '___';
// 查询姓名中包含中的人
SELECT * FROM test WHERE NAME LIKE '%中%';

4.约束

4.1 主键约束Primary Key

能够唯一确定一张表中的的一条记录,我们通过给某个字段添加约束, 可以使得这个字段不重复且不为空.

create table user (
	id int primary key auto_increment, // 在创建表时，添加主键约束，并且完成主键自增	
	name varchar(20)
 );
-- 联合主键: 由多个字段联合组成的主键, 只要联合的主键加起来不重复就可以.联合主键中的任何一个字段都不能为空.
create table user2 (
 	id int,
 	name varchar(20),
 	password varchar(20),
 	primary key(id, name)
);

表创建完成后：

添加主键:
alter table user add primary key(id);
或
alter table user modify id int primary key;
删除主键:
alter table user drop primary key;

4.2 唯一约束unique

unique 约束修饰的字段的值不可以重复。

create table user1 (
 	id int primary key auto_increment,
  	phone_num varchar(20) unique
  	 );
 create table user2 (
 	id int primary key auto_increment,
  	name varchar(20),
  	unique(id, name) // 表示两个字段在一起不重复就可以
  	 );

也可以在表创建完成后, 通过

添加unique约束:
alter table user3 add unique(phone_num);
或
alter table user3 modify phone_num varchar(20) unique;
删除unique约束:
alter table user3 drop index phone_num;

4.2.3 主键（PRIMARY KEY）与UNIQUE 对比

1）UNIQUE 约束唯一标识数据库表中的每条记录。它 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

2）PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

3）每张表可以有多个 UNIQUE，但是每个表只能有一个PRIMARY KEY。

4）UNIQUE 可为空，而PRIMARY KEY不能

4.3 非空约束not null

not null 修饰的字段不能为空NULL

create table user3 (
	id int primary key auto_increment,
	name varchar(20) not null
	);
删除非空约束:
alter table user3 modify name varchar(20);

4.4 默认约束default

当我们插入字段值时候,如果对应的字段没有插入值,则会使用默认值.如果传入了值,则不会使用默认值.

create table user4(
	id int primary key auto_increment,
	age int default 18,
	name varchar(20) not null
	);

4.5 外键约束：foreign key

MySQL的外键约束用来在两个表数据之间建立链接，其中一张表的一个字段被另一张表中对应的字段约束。也就是说，设置外键约束至少要有两种表，被约束的表叫做从表（子表），另一张叫做主表（父表），属于主从关系。

其中主表中的关联字段叫做主键，外表中的关联字段叫做外键。

外键约束主要作用就是能够让表与表之间的数据建立关联，使数据更加完整，关联性更强。

create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
// 班级
create table classes(
	id int primary key,
	name varchar(20)
	);	
// 学生表
create table student (
		id	int primary key,
		name varchar(20),
		class_id int,
		constraint 'fk' foreign key(class_id) references classes(id)
		);

4.5.1 主键与外键

1）主键是唯一标识一条记录，不能有重复的，不允许为空

2）外键可以有重复的, 可以是空值，用来和其他表建立联系用的。

关于完整性，可以通过一个例子来说明

假如有两种表，一张用户账户表（用于存储用户账户），一张是账户信息表（用于存储账户中的信息）。

1）我不小心将用户账户表中的某个用户删除了，那么账户信息表中与这个用户有关的数据就变成无源数据了，找不到其属于哪个用户账户，导致用户信息不完整。

2）我在账户信息表中随便添加了一条数据，而其在用户账户表中没有对应的用户，这样用户信息也是不完整的。

如果有了外键约束，将用户账户表设为主表，账户信息表设为从表，那么就无法直接往账户信息表中插入数据，在用户账户表中删除用户，账户信息表中的用户信息也会被删除。外键约束的方式可以使两张紧密的联系在一起，保障数据完整性和一致性的同时，日常维护也更加方便。 外键创建规则： 1）必须有主表才可以设置从表。

2）主表必须实际存在。

3）必须为主表定义主键。

4）外键列的数据类型必须和主键列的数据类型相同。

5）外键列的数量必须和主键列的数量相同。

6）外键可以不是外表中的主键，但必须和主表关联字段相对应。

7）主从表创建时，存储引擎必须是InnoDB。

4.5.2 外键的使用条件

1）两个表必须是InnoDB表，MyISAM表暂时不支持外键

2） 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

3）外键关系的两个表的列必须是数据类相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以

5.查询语句基本操作

查询所有记录
例如:查询student表中的所有记录.
select * from student;

查询指定字段
例如:查询student中的sname,ssex,class.
select sname,ssex,class from student;

查询教师表中所有的单位即不重复的depart列. <排除重复distinct>
select distinct depart from teacher;

查询score表中成绩在60到80之间的所有记录 <查询区间 between…and…>
select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree < 80;

查询score表中成绩为85,86或88的记录<(not)in范围查询>
select * from score where degree in(85, 86, 88);

查询student表中’95031’班或性别为’女’的同学记录. <or 表示或者>
select *from student where class = '95031' or sex = '女';

以class降序查询student表的所有记录 <降序:desc, 升序asc,默认升序(省略)>.
select * from student order by class desc;

查询student表中第二个为'好'的同学记录. <like模糊匹配>
“%”代表任意字符且任意长度，“_” 代表任意单个字符
select *from student where name like '_好%';

查询"95031’班的学生人数 <统计 count>
select count(*) from student where class = '95031';

5.1 group by分组查询

在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。

1）与 GROUP_CONCAT()函数一起使用

GROUP_CONCAT将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

例：将所有学生按年级分组

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;

2）与COUNT函数一起使用

例：查询各年级的学生人数

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;

3）与 HAVING 一起使用(限制输出的结果)

HAVING 子句可以让我们筛选分组后的各组数据。它的用法与where类似，但是在GROUP BY后只能用HAVING不能用WHERE。

例：

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;

4）与 WITH ROLLUP 一起使用(最后加入一个总和行)；

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

5.2 sql聚合函数

 AVG() – 返回一组值的平均值。
 COUNT() – 返回一组中项目数目。
 MAX() – 返回一组中的最大值。
 MIN() – 返回一组中的最小值
 SUM() – 返回一组中所有值或不同值的和

5.3 limit分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

注意：

1.起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。

2.分页查询是数据库的方言，不同的数据库的实现方式不同，mysql中用的是LIMIT。

3.如果查询的是第一页的数据，起始索引可以省略，直接简写成limit 10（这个10是每页显示的数据数）。

查询第11，12条数据
SELECT * FROM t_student LIMIT 10,2;

6.连接查询

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。

1 Join(Inner Join)内连接 查出两表完全匹配的部分。