[大数据]数据库学习——SQL之DDL、DML、DCL的使用

1、基础知识

数据存储的过程

从系统构架的层次上看，MySQL数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列

在MySQL中，一条完整的数据存储过程分为四步：

• 创建数据库
• 确认字段
• 创建数据表
• 插入数据

2、创建和管理库

2.1 创建数据库

• 创建数据库

CREATE DATABASE 数据库名;

• 创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

• 判断数据库是否已经存在，不存在则创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名; 

DATABASE 不能改名，一些可视化工具改名实质是建新库，把所有表复制到新库，再删除旧库完成的

2.2 使用数据库

• 查看当前的数据库

SHOW DATABASES;

• 查看当前正在使用的数据库

SELECT DATABASE();

• 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

• 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;

• 使用/切换数据库

USE 数据库名;

2.3 修改数据库

• 更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集;

2.4 删除数据库

• 删除指定的数据库

DROP DATABASE 数据库名;
DROP DATABASE IF NOT EXISTS 数据库名;

3、创建和管理表

3.1 创建表

CREATE TABLE [IF NOT EXISTS] 表名(
    字段1, 数据类型 [约束条件] [默认值], 
    字段2, 数据类型 [约束条件] [默认值], 
    字段3, 数据类型 [约束条件] [默认值]
)engine=innodb default charset utf8;

eg:
create table tb_user( 
    id bigint primary key auto_increment, 
    username varchar(20) not null unique, 
    password varchar(20) not null 
)engine=innodb default charset utf8;

3.2 查看数据表结构

SHOW CREATE TABLE 表名;
DESC 表名;

eg:
mysql> desc test;
ERROR 1146 (42S02): Table 'test.test' doesn't exist
mysql> desc tb_user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   | UNI | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

3.3 修改表

追加一个列

ALTER TABLE 表名 ADD[COLUMN] 字段名 字段类型;

eg:
ALTER TABLE tb_user
ADD job_id varchar(15);

修改一个列

ALTER TABLE 表名 MODIFY[COLUMN] 字段名 字段类型[DEFAULT 默认值];

eg:
ALTER TABLE tb_user
MODIFY salary double(9,2) default 1000;

重命名一个列

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

eg:
ALTER TABLE tb_user
CHANGE department_name dept_name varchar(15);

删除一个列

ALTER TABLE 表名
DROP[COLUMN] 字段名;

eg:
ALTER TABLE tb_user
DROP[COLUMN] dept_name;

3.4 删除表

DROP TABLE [IF EXISTS] 数据表;

3.5 清空表

TRUNCATE TABLE语句：

• 删除表中所有数据

• 释放表的存储空间

TRUNCATE TABLE tb_user;

TRUNCATE 与DELETE的区别

4、数据处理之增删改

4.1 插入数据

INSERT INTO 表名
VALUES
(value1,values2),
(value3,values4),
(value5,values6);

4.2 更新数据

UPDATE 表名
SET 条件1;
WHERE 条件2;

eg:
UPDATE employees
SET id=60
WHERE id=13;

4.3 删除数据

DELETE FROM 表名
WHERE 条件;

eg:
DELETE FROM tb_user
WHERE id=3;

5、MySQL数据类型

常见数据类型的属性：

• unsigned设置列为无符号型，只能存放大于等于0的数据，禁用负值。当使用无符号类型时取值范围由于没有负数部分，从而导致上限扩大一倍

create table t3(id int unsigned);

mysql> insert into t3 value(-10); -- 不允许存储负数 
ERROR 1264 (22003): Out of range value for column 'id' at row 1 
mysql> insert into t3 value(255); -- 存储数据的上线扩大一倍 
Query OK, 1 row affected (0.01 sec)

• 类型名后添加括号，其中包含一个正整数，例如int(5)，这里的含义并不是要求只能存放5位长度的整数；含义是当进行查询时自动使用空格填充到5个长，如果真实数据长度大于5，则按实际输出

mysql> create table t4(id int(2)); 
Query OK, 0 rows affected (0.02 sec) 

mysql> insert into t4 values(99999); 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into t4 values(9); 
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4; 
+-------+ 
| id    | 
+-------+ 
| 99999 | 
|     9 | 
+-------+ 
2 rows in set (0.00 sec)

• auto_increment一般用于主键，可以实现该列的自动生成连续整数值

mysql> create table t8(id int auto_increment,name varchar(20)); -- 指定自增长，但不指定主键则会报错
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 

mysql> create table t8(id int primary key auto_increment,name varchar(20)); 
Query OK, 0 rows affected (0.03 sec) 

mysql> insert into t8 values(null,'yan1');-- 插入数据时不指定插入的值或者设置 插入值为null则会自动添加一个自增值 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into t8 values(99,'yan1'); -- 如果插入数据时不指定对应的值，则 自增长max(id)+1;如果指定对应的值，则自增效果失效 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into t8(name) values('yan1'); 
Query OK, 1 row affected (0.01 sec) 

mysql> select * from t8; 
+-----+------+ 
| id  | name | 
+-----+------+ 
|   1 | yan1 | 
|  99 | yan1 | 
| 100 | yan1 | 
+-----+------+ 
3 rows in set (0.00 sec)

• zerofill自动填0如果查询显示时，实际数据小于指定位宽，则自动添加0值

mysql> create table t9(id int(5) zerofill); 
Query OK, 0 rows affected (0.03 sec) 

mysql> insert into t9 values(12); 
Query OK, 1 row affected (0.01 sec) 

mysql> select * from t9;
+-------+ 
| id    | 
+-------+ 
| 00012 | 
+-------+ 
1 row in set (0.00 sec)

mysql> insert into t9 values(123456); 
Query OK, 1 row affected (0.01 sec) 

mysql> select * from t9; 
+--------+ 
| id     | 
+--------+ 
|  00012 | 
| 123456 | 
+--------+ 
2 rows in set (0.00 sec)

• default设置默认值

mysql> create table t10(id int default 0,name varchar(20)); 
Query OK, 0 rows affected (0.03 sec) mysql> insert into t10 values(11,'yan1');-- 指定对应的数据，则default无效 Query OK, 1 row affected (0.01 sec) 

mysql> insert into t10 values(null,'yan1'); -- 指定对应的数据，则default无 效，即使设置的值为null 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into t10(name) values('yan1'); -- 只有不针对这个列进行数据插入 时default才能生效 
Query OK, 1 row affected (0.00 sec) 

mysql> select * from t10; 
+------+------+ 
| id   | name | 
+------+------+ 
|   11 | yan1 | 
| NULL | yan1 | 
|    0 | yan1 | 
+------+------+ 
3 rows in set (0.00 sec)

• null未知的数据：不是空字符串、?任何具体值null值比较使用的运算符是is null/is not null

mysql> select null=null; -- =表示等值比较 
+-----------+ 
| null=null | 
+-----------+ 
|      NULL |
+-----------+ 
1 row in set (0.00 sec) 

mysql> select null!=null; -- 比较不相等 
+------------+ 
| null!=null | 
+------------+ 
|       NULL | 
+------------+ 
1 row in set (0.00 sec) 

mysql> select null is null; 
+--------------+ 
| null is null | 
+--------------+ 
|            1 | 
+--------------+ 
1 row in set (0.00 sec) 

mysql> select null is not null;
+------------------+ 
|null is not null  | 
+------------------+ 
|                0 | 
+------------------+ 
1 row in set (0.00 sec)

5.1 数值类型

(表是截来的，但学习是真的哈哈哈)

如何选择？

• TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

• SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

• MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。

• INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

• BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

系统故障产生的成本远超过增加几个字段存储空间所产生的成本

decimal和numeric作为字符串存储浮点数，可以实现浮点数的精确存储，并不是float和double种使用二进制浮点数存储。使用方法numeric(总位宽，小数位数)，小数位数必须小于等于总位宽；小数位数最大值30，总位宽最大值为65，注意可能存储的数据会超出范围，其中的符号位和小数点不占位宽

mysql> create table t5(id numeric(5,3)); 
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t5 values(99.999); 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into t5 values(-99.999); -- 位宽统计中符号位不算 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into t5 values(100); -- 整数超出范围报错 
ERROR 1264 (22003): Out of range value for column 'id' at row 1 

mysql> insert into t5 values(9.1235); -- 小数位数多余则自动四舍五入 
Query OK, 1 row affected, 1 warning (0.00 sec) 

mysql> insert into t5 values(9.1234); 
Query OK, 1 row affected, 1 warning (0.01 sec) 

mysql> select * from t5; 
+---------+ 
| id      | 
+---------+ 
| 99.999  | 
| -99.999 | 
| 9.124   | 
| 9.123   | 
+---------+ 
4 rows in set (0.00 sec)

int和numeric

• int类型不能保存小数位，存储小数时会自动进行四舍五入

mysql> create table t6(id int); 
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t6 values(99.99); 
Query OK, 1 row affected (0.01 sec) 

mysql> select * from t6; 
+------+ 
| id   | 
+------+ 
|  100 | 
+------+ 
1 row in set (0.00 sec)

• numeric可以保存小数位，如果小数位数为0，可以模拟得到int类型的存储效果。采用的实际存储方式为字符串。查询效率远低于int

5.2 字符串类型

• char(n)：定长字符串 0<=n<=255
  • 长度限制不区分中英文，表示是允许最多存储多少个字符
  • 使用length函数获取的是字节数，不是字符数。在utf-8编码种一个汉字占用3个字节

mysql> create table c2(id char(5)); 
Query OK, 0 rows affected (0.03 sec) 

mysql> insert into c2 values("yan"); 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into c2 values("中国人民好"); Query OK, 1 row affected (0.01 sec) 

mysql> insert into c2 values("中国人民好1"); ERROR 1406 (22001): Data too long for column 'id' at row 1

• varchar(n)：可变长字符串 0<=n<=65535
  • 实际存储多少个字符就占多少个位置，每个列所占用的长度根据具体内容变化

create table t1(id varchar(20));

char和varchar：

• char属于定长，varchar属于变长。选择最基本的依据就是定长使用char，变长使用varchar

• char允许的最大长度为255，varchar允许的最大长度为65535

• 如果存储的字符串长度变化不大，推荐优先考虑使用char，例如针对口令进行md5加密，固定长度的行不容易产生碎片；对于长度很短的列，char的性能优于varchar，是因为varchar需要引入额外一个或者两个字节存储字符串长度

• char在计算种会自动去除空格；varchar不会去掉空格，但是进行字符串比较时，会去除空格后进行比较；在插入数据时系统会自动去除字符串默认多余的空格

5.3 大对象类型Lob

大对象类型可以分为2大类：文本类型和二进制类型

• BLOB是一个二进制大对象，可以容纳可变长度的数据，其中分为tinyblob、blob、mediumblob、longblob。一般用于存储二进制数据，例如声音、图像、视频等

• Text是一个文本大对象，可以容纳可变长度的数据，其中分为tinytext、text、mediumtext、longtext，一般用于存储文本类型数据，例如小说、商品简介

二者的区别：blob是二进制类型，可以容纳可变长度的数据，区分大小写；text是一个不区分大小写的blob

mysql> create table c9(id text); 
Query OK, 0 rows affected (0.02 sec) 

mysql> create table c8(id blob); 
Query OK, 0 rows affected (0.02 sec) 

mysql> insert into c9 values("Abc"); 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into c8 values("Abc"); 
Query OK, 1 row affected (0.01 sec) 

mysql> select * from c9 where id='abc'; -- c9是text，text不区分大小写
+------+ 
| id | 
+------+ 
| Abc | 
+------+ 
1 row in set (0.00 sec) 

mysql> select * from c8 where id='abc'; 
Empty set (0.00 sec)

5.4 日期时间类型

-- 时间戳类型
create table dt2(id timestamp default current_timestamp,name varchar(20));

mysql> insert into dt3(name) values('yan1'); 
Query OK, 1 row affected (0.01 sec) 
mysql> select * from dt3; 
+---------------------+------+ 
| id                  | name | 
+---------------------+------+ 
| 2022-04-17 15:07:33 | yan1 | 
+---------------------+------+ 
1 row in set (0.00 sec)

• now()：获取系统的当前时间

select now();

5.5.集合和枚举类型

• set集合类型，可以设置多个集合成员，允许从多个集合成员中选择多个数据进行插入

create table set1(id int,hobby set('抽烟','喝酒','烫头'));

insert into set1 values(1,'烫头','抽烟'); -- 正确
insert into set1 values(2,'烫头','抽烟','打麻将'); -- 报错，打麻将不属于set中定义的值
insert into set1 values(3,'烫头','烫头');  -- 实际只插入了一个值，自动去除重复值

• enum枚举类型，可以设置允许选用的多个值，列只能 赋予某个枚举成员值

create table enum1(id int,sex enum('男','女','不确定'));

insert into enum1 values(1,'男');
insert into enum1 values(2,'男','女'); -- 报错

5.6 位类型

bit(n)：n的最大上限值为64

bit(1)表示可以存储一个二进制位，只能存储0或1

bit(8)表示存储8个二进制位的值，取值范围为0-255

总结

• 定义数据类型就是定义列，数据类型决定数据的特性【域完整性】
• 数据类型主要分为字符串类型、定点数和浮点数类型、日期类型、大对象类型【慎用】
• 数据类型在不同的存储引擎上表现不同
• 数据所采用的数据类型是根据业务需求和对应的数据类型特征进行选择，不建议使用过于复杂的数据类型

6、约束

基础知识

为什么需要约束？

为了保证数据的完整性

什么叫做约束？

对标中字段的限制

约束的分类：

1. 约束字段的个数：单列约束vs多列约束
2. 约束的作用范围：列级约束vs表级约束
   • 列级约束：将此约束声明在字段的后面
   • 表级约束：在表中所有的约束声明完之后，在所有字段的后面声明的约束
3. 约束的作用
   • not null（非空约束）
   • unique（唯一性约束）
   • primary key（主键约束）
   • foreign key（外键约束）
   • check（检查约束）
   • default（默认值约束）

如何添加约束？

CREATE TABLE时添加约束

ALTER TABLE时增加约束

NOT NULL

NOT NULL不能组合非空，只能单独某个列限定非空

unique

create table student_course( 
    id int, 
    sid int, 
    cid int, 
    score int, 
    unique key(sid,cid) #复合唯一 
);

primary key

主键约束相当于唯一约束+非空约束，即不允许重复，不允许空值

• 一个表只能有一个主键约束
• 可以复合主键
• 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复

AUTO_INCREMENT

• 一个表最多只能有一个自增长列
• 当需要产生唯一标识符或顺序值时，可设置自增长
• 自增长列约束的列必须是键列（主键列，唯一键列）
• 自增约束的列的数据类型必须是整数类型
• 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

create table employee( 
    eid int auto_increment, 
    ename varchar(20) 
);
# ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

create table employee( 
    eid int primary key, 
    ename varchar(20) unique key auto_increment 
);
# ERROR 1063 (42000): Incorrect column specifier for column 'ename' 因为ename不是整数类 型

关于自增长的持久化问题：

MySQL5.7：

#插入4个空值
INSERT INTO test
VALUES(0),(0),(0),(0);

#查询结果
mysql> SELECT * FROM test1; 
+----+
| id | 
+----+
|  1 | 
|  2 | 
|  3 | 
|  4 | 
+----+
4 rows in set (0.00 sec)

#删除id=4的记录之后，再插入一条空值
mysql> SELECT * FROM test1; 
+----+
| id | 
+----+
|  1 | 
|  2 | 
|  3 | 
|  5 | 
+----+
4 rows in set (0.00 sec)

#虽然删除了id=4的记录，但再次插入时，id从5开始

#于是重启数据库之后再次插入
| id | 
+----+
|  1 | 
|  2 | 
|  3 | 
|  4 | 
+----+
4 rows in set (0.00 sec)
#此时应该分配的是6，但是结果是4.这是因为自增主键没有持久化。

在MySQL5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，此计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

MySQL8.0：

mysql> SELECT * FROM test1; 
+----+
| id | 
+----+
|  1 | 
|  2 | 
|  3 | 
|  6 | 
+----+
4 rows in set (0.00 sec)
#此时自增变量已经持久化

MySQL将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中，如果数据库重启，InnoDB会根据重做日志中的信息来持久化计数器的内存值

FOREIGN KEY

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

• 从表的外键列，必须引用/参考主表的主键或唯一约束的列

  为什么？因为被依赖/被参考的值必须是唯一的

• 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。

• 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表

• 删表时，先删从表（或先删除外键约束），再删除主表

• 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

• 在“从表”中指定外键约束，并且一个表可以建立多个外键约束

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。

• 删除外键约束后，必须 手动 删除对应的索引

create table 主表名称(
    字段1 数据类型 primary key，
    字段2 数据类型
);

create table 从表名称(
    字段1 数据类型 primary key，
    字段2 数据类型，
    [CONSTRAINT <外键约束名称>] FOREIGN KEY(从表中的某个字段 references 主表名(被参考字段))
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样 
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列


eg:
create table dept( #主表 
	did int primary key, #部门编号 
	dname varchar(50) #部门名称 
);

create table emp(#从表 
	eid int primary key, #员工编号 
	ename varchar(5), #员工姓名 
	deptid int, #员工所在的部门
	foreign key (deptid) references dept(did) #在从表中指定外键约束 #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号 
);

说明：

（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。

（2）删除表时，先删除从表emp，再删除主表dept

一些问题：

问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？

答：不是的

问题2：建和不建外键约束有什么区别？

答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限

制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。

不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的 引用完整

性 ，只能依 靠程序员的自觉 ，或者是 在Java程序中进行限定 。例如：在员工表中，可以添加一个员工的

信息，它的部门指定为一个完全不存在的部门。

问题3：那么建和不建外键约束和查询有没有关系？

答：没有

阿里开发规范

【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。

面试1、为什么建表时，加not null default ‘’ 或 default 0

答：不想让表中出现null值。

面试2、为什么不想要 null 的值

答:（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通

常返回null。

（2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0

面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？

在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（

AUTO_INCREMENT的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

面试4、并不是每个表都可以任意选择存储引擎？

外键约束（FOREIGN KEY）不能跨引擎使用。

MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来

保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不

能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖