SQL知识之数据库基础

1、基本概念

数据库：将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。

数据库管理系统（DBMS）：用来管理数据库的计算机系统

DBMS分类：关系型数据库（MySQL、SQL Server等），非关系型数据库（MongoDB等）

表：用来管理数据的二维表，由行和列组成。

列：表的列（垂直方向）称为字段，代表了保存在表中的数据项目。

行：表的行（水平方向）称为记录，相当于一条数据，关系型数据库必须以行为单位进行数据读写。

SQL：结构化查询语言，是一门专门用来与数据库通信的语言。

2、SQL的基本书写规则

• SQL语句要以分号（;）结尾，不过现在很多数据库连接工具（如Navicat）都支持不加分号的SQL语句。
• SQL语句中关键字不区分大小写，不过关键字大写易于区分辨别。
• 插入到表中的数据是区分大小写的，如Computer、computer和COMPUTER三者是不一样的。
• 字符串和日期常数需要使用单引号括起来，注意是单引号，而不是双引号（""）。
• 数字常数无需加注单引号，直接书写数字即可。

3、数据库的基本操作

3.1 数据库的创建

语法： CREATE DATABASE 数据库名称;

 CREATE DATABASE newDB;
 create database newDB charset=utf8;      # MySQL 创建数据库时指定编码方式

3.2 查询和使用数据库

SHOW DATABASES;     # 查看所有数据库
USE newDB;          # 进入数据库

3.3 删除数据库

DROP DATABASE newDB;    # 删除数据库要小心谨慎，一旦删除，所有数据都会丢失

4、数据表的基本操作

4.1 数据表的创建

语法：

CREATE TABLE 表名(

​ 列名 数据类型，

​ …

​ 列名n 数据类型

)

CREATE TABLE user_info(
	id INT,
	name VARCHAR(20)
);

4.2 查看数据库

SHOW TABLES;               # 查看所有表
SHOW CREATE TABLE user_info;   # 查看建表语句
DESCRIBE user_info;            # 以表格方式查看表

4.3 删除表

DROP TABLE user_info;

4.4 修改表

ALTER TABLE user_info RENAME user_info1;      # 修改表名
ALTER TABLE user_info ADD sex VARCHAR(20);    # 增加列
ALTER TABLE user_info DROP sex;               # 删除列
ALTER TABLE user_info MODIFY name VARCHAR(50);  # 修改列的类型
ALTER TABLE user_info CHANGE name new_name VARCHAR(50);   # 同时修改列名、列类型

4.5 向表中插入数据

INSERT INTO user_info VALUES(1, 'mike');    # 一次插入单条数据
 
INSERT INTO user_info VALUES(2, 'lucy'),(3,'peter');  # 一次插入多条数据，数据之间用逗号隔开即可

4.6 更新表中的数据

UPDATE user_info SET name = 'mike_new' WHERE id = 1;  # 更新指定条件的数据
UPDATE user_info SET name = 'mike_new';               # 更新所有数据

4.7 删除表中的数据

DELETE FROM user_info WHERE id = 1;      # 删除指定条件的数据
DELETE FROM user_info;                   # 删除所有数据

5、SQL主要的数据类型

5.1 MySQL 数据类型

在 MySQL 中，有三种主要的类型：字符串、数字和日期/时间类型。

字符串类型：

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

• 经常变化的字段用VARCHAR
• 知道固定长度的用CHAR，如身份证等
• 超过 255 字符的只能用 VARCHAR或者 TEXT
• 能用 VARCHAR的地方不用 TEXT
• 能够选择数字类型的字段尽量选择数字类型而不是字符串类型，因为字符串类型会降低查询和连接的性能，并会增加内存开销。

数字类型 ：

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期/时间类型：

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01/2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

• 对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC时区进行存储，查询时，将其又转化为客户端当前时区进行返回。
• 对于DATETIME，不做任何改变，基本上是原样输入和输出。

5.2 SQL Server 数据类型

String 类型：

数据类型	描述	存储
char(n)	固定长度的字符串。最多 8,000 个字符。	Defined width
varchar(n)	可变长度的字符串。最多 8,000 个字符。	2 bytes + number of chars
varchar(max)	可变长度的字符串。最多 1,073,741,824 个字符。	2 bytes + number of chars
text	可变长度的字符串。最多 2GB 文本数据。	4 bytes + number of chars
nchar	固定长度的 Unicode 字符串。最多 4,000 个字符。	Defined width x 2
nvarchar	可变长度的 Unicode 字符串。最多 4,000 个字符。
nvarchar(max)	可变长度的 Unicode 字符串。最多 536,870,912 个字符。
ntext	可变长度的 Unicode 字符串。最多 2GB 文本数据。
bit	允许 0、1 或 NULL
binary(n)	固定长度的二进制字符串。最多 8,000 字节。
varbinary	可变长度的二进制字符串。最多 8,000 字节。
varbinary(max)	可变长度的二进制字符串。最多 2GB。
image	可变长度的二进制字符串。最多 2GB。

Number 类型：

数据类型	描述	存储
tinyint	允许从 0 到 255 的所有数字。	1 字节
smallint	允许介于 -32,768 与 32,767 的所有数字。	2 字节
int	允许介于 -2,147,483,648 与 2,147,483,647 的所有数字。	4 字节
bigint	允许介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。	8 字节
decimal(p,s)	固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
numeric(p,s)	固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
smallmoney	介于 -214,748.3648 与 214,748.3647 之间的货币数据。	4 字节
money	介于 -922,337,203,685,477.5808 与 922,337,203,685,477.5807 之间的货币数据。	8 字节
float(n)	从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。	4 或 8 字节
real	从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。	4 字节

Date 类型：

数据类型	描述	存储
datetime	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。	8 字节
datetime2	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。	6-8 字节
smalldatetime	从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。	4 字节
date	仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。	3 bytes
time	仅存储时间。精度为 100 纳秒。	3-5 字节
datetimeoffset	与 datetime2 相同，外加时区偏移。	8-10 字节
timestamp	存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 值基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量