数据库1-基本概念，SQL三大类型（DDL，DML，DCL）和数据库数据表的基本操作

一 基本概念

基本概念

数据(Data)

记录(Record)

数据表(Data Table，或者Table)

数据库(Database)

定义

是描述主观或客观存在的人、事、物特征的符号记录（Record），描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机

是指代描述事物的一系列特征而组成的相关信息集合，在计算机中描述一个事物的记录，就相当于文件里的一行内容。

隔，依次定义各个信息代表的具体意义，相当于定义一张表格一样

就如表格一样，在第二行以后还可以继续写入其他人的相关信息记录。最终保存为一个文件的形式。我们可以简单理解这个文件就是一个数据表

是一种按照特定数据结构来组织、存储和管理数据的仓库。

通俗地理解，数据库就是一个存储数据表的特殊文件夹，只不过这个仓库可以将数据按照特定的数据结构进行高效压缩存储在磁盘上，同时为了方便用户组织和管理数据，数据库还会专门配套了数据库管理系统（DBMS）。用户通过操作数据库管理系统提供的功能，就可以有效的组织和管理存储在数据库中的数据。

示例

name = "张蔷"

 

	name	sex	age		province
	张蔷	女	18		山西

这样我们就可以了解张蔷，性别为女，年龄18岁，出生于2000年，来自于山西等相关信息。

 

	name	sex	age		province
	张蔷	女	18		山西
	李雷	男	20		湖南

 

 

 

二  SQL三大类型

SQL语句的底层本质就是对操作数据的数学公式进行封装而得到的函数关键字，那么根据不同底层不同函数的用途，SQL语句通常分3大类型

数据定义语言（Data Definition Language，DDL）	用于创建或删除数据库、数据表、字段的SQL语句，包含以下几种指令：
数据操作语言（Data Manipulation Language，DML）	用于对数据表中的数据进行增删查改的。
数据控制语言（Data Control Language，DCL）	用于对控制数据库的操作权限的，包括用户权限以及数据操作权限

 

三 基本操作

数据库操作	1. 创建数据库 create database [if not exists] 数据库名 [character set 编码字符集]; 2. 查看数据库 show databases; -- 查看所有数据库 show databases like '%base%'; -- 查看名字中包含base的数据库 show databases like '____'; -- 查看名字中四字的数据库 show create database 数据库名; -- 查看指定数据库的建库sql语句 3. 修改数据库【一般不用】 alter database 数据库名 [character set 编码字符集]; 4. 删除数据库 drop database [if exists] 数据库名; 5. 使用数据库 use 数据库名; -- 使用数据库/切换数据库 select database(); -- 查看当前使用的数据库
数据表操作	1. 创建数据表 create table [if not exists] 表名 ( 字段名1 数据类型[ ( 存储空间 ) 字段约束 ], 字段名2 数据类型[ ( 存储空间 ) 字段约束 ], ..... 字段名n 数据类型[ ( 存储空间 ) 字段约束 ], primary key(一个 或 多个 字段名) -- 注意，最后一段定义语句，不能有英文逗号的出现，否则报错。 ) [engine = 存储引擎 character set 字符集]; 注意： 上面SQL语句中，小括号中的定义字段语句后面必须以英文逗号结尾，而最后一个字段的定义语句不能有英文逗号出现，否则报错。 在同一张数据表中，字段名是不能相同，否则报错！ 创建数据表的SQL语句中，存储空间和字段约束是选填的，而字段名和数据类型则是必须填写的。 2. 查看数据表 查看所有数据表 show tables; 查看表结构 -- 方式1：简单查看 describe 表名; desc 表名; -- desc是describe的缩写 -- 方式2：查看信息 describe table 表名; desc table 表名; 查看建表语句 show create table 表名 \G; 3. 删除数据表 -- 直接删除 drop table 表名; -- 判断删除，判断表存在了，再删除，防止删除不存在的表导致错误出现 drop table if exists 表名; 4. 重置表信息 保留数据表结构，但是把数据表存储的数据清空以及数据表的状态清零，相当于删除原表，并新建一张一模一样的空数据表。 truncate table 表名; 5. 修改数据表 数据表重命名 alter table 表名 rename 新表名; 增加字段 -- 添加一个字段 alter table 表名 add 字段名1 数据类型 [完整性约束条件…]; -- 一次性添加多个字段 alter table 表名 add 字段名1 数据类型 [完整性约束条件…], add 字段名2 数据类型 [完整性约束条件…], ... add 字段名n 数据类型 [完整性约束条件…]; -- 在指定位置添加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; -- 在第一个字段之前，新增字段（新的字段就变成了表的第一个字段） ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; -- 在指定字段之后新增字段 删除字段 alter table 表名 drop 字段名; 修改字段 -- 方式1: 不提供的字段名修改(针对原字段直接覆盖性修改) alter table 表名 modify 字段名 数据类型 [完整性约束条件…]; -- 方式2: 提供字段名的修改(相当于删除原字段， 新增一个字段进行修改，保留原字段的数据类型) alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; -- 方式3：提供字段名的修改(相当于删除原字段，不保留源字段的数据类型) alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…]; -- 方式4：修改字段排列顺序/在增加的时候指定字段位置 ALTER TABLE 表名 CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
数据表就相当于存储数据的特殊文件，数据表中的一条记录就相当于普通文件的一行内容。 在磁盘上创建一个存储数据表的文件夹。数据库相当于存储数据表的文件夹。

附：

数据表操作中的数据类型和约束条件详细说明如下

1 数据类型

数据库里面的数据在保存时也要通过指定数据的类型来告诉数据库管理系统，这些数据有什么用途，所以也会有对应的数据类型。数据类型是为了节约内存,提高计算速度，尽量使用存储空间少的类型。常用数据类型有数值类型、字符串类型、时间日期类型、枚举类型。mysql的单表数据表可以支持最多亿级（2000W-5000W是比较合适的）

1.1 数值类型：

MySQL中的数值类型提供了整型、浮点型、定点数。

对于小数的表示，MYSQL分为两种方式：浮点数（float）和定点数（Decimal）。浮点数包括float(单精度)和double(双精度), 而定点数只有decimal一种，在mysql中底层以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度要求高的数据。

分类	数据类型	存储大小	有符号范围（signed）	无符号范围(unsigned)	使用场景	示例
整型	tinyint(m)	1个字节	(-128，127)	(0，255)	年龄，分类的编号	drop table if exists test1; create table test1 ( id int auto_increment primary key, age tinyint unsigned, score tinyint, number smallint, chinese_achievement float(4,1), -- 当前小数中总位数为4，其中小数位最多1个数字，整数位3个 math_achievement double(4,1), -- english_achievement decimal(4,1) );   insert into test1 (age, score, number, chinese_achievement, math_achievement, english_achievement) values (20, 100, 30000, 60.5, 93.5, 100);
整型	smallint(m)	2个字节	(-32 768，32 767)	(0，65 535)	商品分类编号，员工编号，
整型	int(m)	4个字节	(-2147483648~2147483647)	(0，4 294 967 295)	一般数据表的主键id
浮点型	float(m,d)	8位精度，4个字节	单精度，近似值的小数 m总个数，d小数位	0，(1.175 494 351 E-38，3.402 823 466 E+38)	数值类型的时间戳，带小数的经纬度
浮点型	double(m,d)	16位精度，8个字节	双精度，近似值的小数 m总个数，d小数位	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
定点数	decimal(m,d)		精确值，精确值的小数 m总个数，d小数位 依赖于m和d的值	依赖于m和d的值	货币，积分

1.2 字符串类型

MySQL中针对文本内容的存储类型提供了字符串与文本两种格式，其中按存储方式不同，又分为固定长度（定长）与可变长度（变长）两种，按存储格式不同，又分为普通字符格式与二进制格式两种。

SQL 语句中（单|双）引号都能表示字符串或文本。

数据类型(n指定存储的长度上限)	大小	描述	应用场景	示例
char(n)	0-255字符	定长字符串	姓名，验证码	create table test2 ( id int auto_increment primary key, name char(15), username varchar(15), password varchar(250), description text, avatar blob, info json );   insert into test2 (name ,username, password, description, avatar, info) values ("李磊", "lilei202209", sha1("123456"), "很长的一段自我介绍....", "abc", '{"address": "北京市"}');
varchar(n)	0-65535字符	变长字符串	账号，密码，文章标题，商品标题
text	0-65 535字符	可变长度文本	文章内容，
blob	0-65 535字符	可变二进制文本	小图标、二进制的认证信息
json	0-4 294 967 295字符	可变二进制json文本 (也叫bson: binary json)	主要实现一些NoSQL数据的存储

char与varchar的区别：

1.char(n) 若存入字符数小于n，则以空格补于其后，SQL语句查询时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。

2.char(4)不管是存入几个字符，都将占用4个字符空间，varchar是存入的实际字符数+1个结束符号（n<=255）或2个字节(n>255)，所以varchar(4), 存入3个字符将占用4个字符空间。

3.因为char类型是固定长度存储空间的，所以在数据库查询速度要比varchar类型的快，因为varchar是动态空间分配，所以每次查找的是否要判断结束符的位置。

varchar字符串和text文本的区别：

1. varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个结束符号（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。

2. text类型不能有默认值，注意json也不能有默认值。

3. varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text。

索引：index，主要为了加快查询数据的数据的一种技术，类似书籍的目录。

 

1.3 日期类型

数据类型	取值范围	日期格式	零值	使用场景	示例
year	1901~2155	YYYY	0000	电影年份，图书年份	create table user_info ( id int auto_increment primary key comment "主键ID", nickname varchar(50) comment "昵称", username varchar(32) comment "登陆账户", password varchar(500) comment "登录密码", birthday date comment "出生日期", created_time timestamp default current_timestamp() comment "创建时间", updated_time timestamp comment "更新时间", deleted_time timestamp default null comment "虚拟删除时间" );   insert into user_info (nickname, username, password, birthday) values ("小灰灰", "root", sha1("123456"), "2000-10-21");
date	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00	生日
time	-838:59:59~838:59:59	HH:MM:SS	00:00:00	餐牌时间，会议时间
datetime	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00	添加时间，更新时间，删除时间，登陆时间
timestamp	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00	添加时间，更新时间，删除时间，登陆时间

1.4 枚举与集合

ENUM只允许从值集合中选取单个值，而不能一次取多个值。

set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

类型	大小 (字节)	用途	示例
enum	对1-255个成员的枚举需要1个字节存储; 对于255-65535个成员，需要2个字节存储; 最多允许65535个成员。	单选：选择性别，现居地城市	create table users ( id int auto_increment primary key comment "主键ID", nickname varchar(50) comment "昵称", username varchar(32) comment "登陆账户", password varchar(500) comment "登录密码", birthday date comment "出生日期", education enum("小学","初中", "高中", "中专", "大专", "本科") comment "学历", hobby set("game", "code", "shopping", "swim", "play ball"), created_time timestamp default current_timestamp() comment "创建时间", updated_time timestamp comment "更新时间", deleted_time timestamp default null comment "虚拟删除时间" );   insert into users (nickname, username, password, birthday, education, hobby) values ("小灰灰", "root", sha1("123456"), "2000-10-21", "小学", "game");
set	1-8个成员的集合，占1个字节 9-16个成员的集合，占2个字节 17-24个成员的集合，占3个字节 25-32个成员的集合，占4个字节 33-64个成员的集合，占8个字节	多选：兴趣、爱好、标签

2 字段约束

也叫完整性约束条件，主要是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

约束类型	SQL关键字	语法	描述	示例
填充	zerofill	字段名 整型 zerofill	为数据表中的整型字段设置数值左边补0	create table goods_info( id int unsigned not null auto_increment comment "主键ID", goods_number int zerofill unsigned not null comment "商品进货号", title varchar(100) not null comment "商品标题", company varchar(100) not null comment "商品厂商", description text null comment "商品描述", country varchar(50) default "中国" comment "产地", unique (title, company), index (title), index (country), primary key (id, goods_number) );
无符号	unsigned	字段名 数据类型 unsigned	为数据表中的数值类型字段设置数值指定不能小于0，可以让字段值的取值范围，在正数范围内增加1倍。
默认值	default	字段名 数据类型 default 默认值	为数据表中的字段指定默认值。但blob、text与json类型不支持default。
非空	not null	字段名 数据类型 not null	非空字段指字段的值不能为NULL。
唯一索引	unique	列级约束 字段名 数据类型 unique 表级约束 unique (字段名 1，字段名 2…)	用于保证数据表中字段的不同行的值唯一性，即表中字段的值不能重复出现在多行。 列级约束定义在一个列上，只对该列起约束作用； 表级约束是独立于列的定义，可以应用在一个表的多个列上。
主键索引	primary key	列级约束 字段名 数据类型 primary key 表级约束 primary key(字段名 1,字段名2…)	一个表中只能有一个主键。可以指定单个字段为单列主键，也可以指定多个字段为联合主键。
自动增长	auto_increment	字段名 数据类型 auto_increment	一个表中只能有一个自动增长的字段，该字段类型是整数类型，一般用于设置主键。 自动增长值从1开始自增，每次加1。
索引	index / key	index / key (字段名)	给对应的字段的值设置添加索引（目的让当前字段的值在被删除，修改，查询时，加快执行速度）
外键索引	foreign key	constraint 外键名 foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]	用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。