数据库设计 SQL基础

SQL基础

一、SQL 简介

• 英文：Structured Query Language， 简称SQL
• 结构化查询语言，一门操作关系型数据库的编程语言
• 定义操作所有关系类型数据库的统一标准
• 对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

二、SQL 通用语法

1. SQL语句可以一行或多行书写，以分号结尾。
2. MySQL数据库的 SQL 语句不区分大小写，关键字建议使用大写。
3. 注释
   • 单行注释： – 注释内容 或 #注释内容(MySQL特有)
   • 多行注释： /* 注释 */

三、SQL分类

• DDL(Date Definition Language) 数据定义语言，用来定义数据库对象：数据库，表，列等
• DML(Date Manipulation Language) 数据操纵语言，用来对数据库中表的数据进行增删改
• DQL(Date Query Language) 数据查询语言，用来查询数据库中表的记录(数据)
• DCL(Date Control Language) 数据控制语言，用来定义数据库的权限和安全级别，以及创建用户

四、DDL

操作数据库

1. 查询

SHOW DATABASES;

2. 创建

• 创建数据库

CREATE DATABASE 数据库名称;

• 创建数据库(判读，如果不存在则创建)

CREATE DATABASE IF NOT EXISTS 数据库名称;

3. 删除

• 删除数据库

DROP DATABASE 数据库名称;

• 删除数据库(判断，如果存在则删除)

DROP DATABASE IF EXISTS 数据库名称;

4. 使用

• 查看当前使用数据库

SELECT DATABASE();

• 使用数据库

USE 数据库名称；

操作表

1. 查询

• 查询当前数据库下所有表的名称

SHOW TABLES;

• 查询表结构

DESC 表名称;

2. 创建

CREATE TABLE 表名 (
	字段名1 数据类型1，
	字段名2 数据类型2，
	...
	字段名n 数据类型n
);

3. 删除

• 删除表

DROP TABLE 表名;

• 删除表时判断表是否存在

DROP TABLE IF EXISTS 表名;

4. 修改

• 修改表名

ALTER TABLE 表名 RENAME TO 新的表名

• 添加一列

ALTER TABLE 表名 ADD 列名 数据类型;

• 修改数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

• 修改列名和数据类型

ALTER TABLE 表名 CHANGE 新列名 新数据类型;

• 删除列

ALTER TABLE 表名 DROP 列名;

五、DML

1. 增加

• 给指定列添加数据

INSERT INTO 表名(列名1, 列名2, ...) VALUES (值1, 值2,...);

• 给全部列添加数据

INSERT INTO 表名 VALUES (值1, 值2,...);

• 批量添加数据

INSERT INTO 表名(列名1, 列名2, ...) VALUES (值1, 值2,...), (值1, 值2,...), (值1, 值2,...),...;
INSERT INTO 表名 VALUES (值1, 值2,...), (值1, 值2,...), (值1, 值2,...),...;

2. 修改

• 修改表数据

UPDATE 表名 SET 列名1=值1, 列名2=值2,... [WHERE 条件];

注意：如果没有加 WHERE 条件，则将表中所有数据都修改

3. 删除

1. 删除数据

DELETE FROM TABLE 表名 [WHRER 条件]

注意：如果没有加 WHERE 条件，则将表中所有数据都删除

六、DQL

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后条件
ORDER BY
	排序字段
LIMIT
	分页限定

1. 基础查询

• 查询多个(指定)字段

SELECT * FROM 表名;
SELECT 列名1, 列名2,... FROM 表名; 	-- 列名1, 列名2,... 表示字段列表

• 去重

SELECT DISTINCT * FROM 表名;
SELECT DISTINCT 列名1, 列名2,... FROM 表名;

• 别名

SELECT 列名1 AS(可省略) 别名1, 列名2 AS(可省略) 别名2,... FROM 表名;

2. 条件查询(WHERE)

• 语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

• 条件

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN…AND…	在某个范围之内(都包含)
IN(…)	多选一
LIKE占位符	模糊查询 _单个任意字符 %多个任意字符
IS NULL	是 NULL
IS NOT NULL	不是 NULL
AND 或 &&	与
OR 或 ||	或
NOT 或 !	非

注意：NULL值的比较不能用 = 或 != 来比较，要使用 IS NULL 和 IS NOT NULL 来比较

3. 排序查询(ORDER BY)

• 语法

SELECT * FROM 表名 ORDER BY 排序字段名1 [排序方式1], 排序字段名2 [排序方式2]...;

排序方式：

• ASC: 升序排列(默认值)
• DESC: 降序排列
  注意：如果有多个排条件，当前面的条件值一样时，才会根据第二条件进行排序

4. 分组查询(GROUP BY)

聚合函数

1. 概念：将一列数据作为一个整体，进行纵向计算
2. 聚合函数分类

函数	功能
count(列名)	统计数量(一般选用不为null的列)
sum(列名)	求和
avg(列名)	求平均值
max(列名)	最大值
min(列名)	最小值

3. 聚合函数语法

SELECT 聚合函数名(列名) FROM	表;

注意：null值不参与所有聚合函数运算
count(列名) 中的列名一般选择 主键列 或 *

分组查询

1. 分组查询语法

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]；

注意： 分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

2. where 和 having 的区别
   • 执行时机不一样：where 是分组之间进行限定，不满足 where 条件的不参与分组，而 having 是分组之后对结果进行过滤
   • 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以
     执行顺序： where > 聚合函数 > having

5. 分页查询(LIMIT)

• 分页查询语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询条目数;

• 起始索引： 从0开始
  计算公式： 起始索引 = (当前页码-1) * 每页显示的条数

tips:

• 分页查询 limit 是 MySQL 数据库的方言
• Oracle 分页查询用的是 rownumber
• SQL Server 分页查询使用 top

补充：

• 数据类型

分类	数据类型	大小 / byte(s))	描述
数值类型	TINYINT	1	小整数值
	SMALLINT	2	大整数值
	MEDIUMINT	3	大整数值
	INT或INTEGER	4	大整数值
	BIGINT	8	极大整数值
	FLOAT	4	单精度浮点数值
	DOUBLE	8	双精度浮点数值
	DECIMAL		小数值
日期和时间类型	DATE	3	日期值
	TIME	3	时间值或持续时间
	YEAR	1	年份值
	DATATIME	8	混合日期和时间值
	TIMESTAMP	4	混合日期和时间值，时间戳
字符串类型	CHAR	0~255	定长字符串
	VARCHAR	0~63355	边长字符串
	TINYBLOB	0~255	不超过255字符的二进制字符串
	TINYTEXT	0~255	短文本字符串
	BLOB	0~63355	二进制形式的长文本数据
	TEXT	0~63355	长文本数据
	MEDIUMBLOB	0~16777215	二进制形式的中等长度文本数据
	MEDIUMTEXT	0~16777215	中等长度文本数据
	LONGLOB	0~4294967295	二进制形式的极大文本数据
	LONGTEXT	0~4294967295	极大文本数据