【基础篇】MySQL数据库详解：基础知识详解

 

• 一、SQL分类
  • 1.DDL
  • 2.DML
  • 3.DQL
  • 4.DCL
• 二、函数
  • 1.字符串函数
  • 2.数值函数
  • 3.日期函数
  • 4.流程函数
• 三、约束
  • 1.概述
  • 2.约束演示
  • 3.外键约束
• 四、多表查询
  • 1.多表关系
  • 2.多表查询表述
  • 3.内连接
  • 4.外连接
  • 5.自连接
  • 6.子查询
• 五、事务
  • 1.事务简介
  • 2.事务操作
  • 3.事务四大特性
  • 4.并发事务问题
  • 5.事务隔离级别

一、SQL分类

SQL语句，根据其功能，主要分为四类：DDL、DML、DQL、DCL。

分类	全程	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

1.DDL

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

1.1 数据库操作

(1) 查看所有数据库

SHOW DATABASES;	

 

(2) 创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [default charset 字符集] [collate 排序规则];

①创建一个test1数据库，使用数据库默认字符：

CREATE DATABASE test1;
CREATE DATABASE IF NOT EXISTS test1;

 

②创建一个test2数据库,并指定字符集：

CREATE DATABASE test2 charset utf8mb4;

 

(3)切换数据库

USE 数据库名;

 

(4)查询当前数据库

SELECT DATABASE();

 

(5)删除数据库

DROP DATABSE [IF EXISTS] 数据库名;

 

1.2 表操作

1.2.1 表操作-查询创建

(1)查询当前数据库所有表

SHOW TABLES;	

 

(2)创建表结构

CREATE TABLE 表名(
	字段1 类型 [COMMENT 字段注释],
	字段2 类型 [COMMENT 字段注释],
	字段3 类型 [COMMENT 字段注释]
	...
) [COMMENT 表注释];

 

(3)查看表结构

DESC 表名;

 

(4)查看表的建表语句

SHOW CREATE TABLE 表名;

 

1.2.2 表操作-数据类型

(1)数值类型

类型	大小	有符合范围	无符号范围	描述
TINYINT	1byte	(-128,127)	(0,255)	小整数值
SMALLINT	2bytes	(-32768,32767)	(0,65535)	大数值
MEDIUMINT	3bytes	(-8388608,8388607)	(0,16777215)	大数值
INT/INTEGER	4bytes	(-2147483648,2147483647)	(0,4294967295)	大数值
BIGINT	8bytes	(-2^63,2^63-1)	(0,2^64-1)	极大整数值
FLOAT	4bytes	(-3.402823466 E+38,3.402823466351 E+38)	0 和 (1.175494351 E-38,3.402823466 E+38)	单精度浮点数值
DOUBLE	8bytes	(-1.7976931348623157 E+308,1.7976931348623157 E+308)	0和(2.2250738585072014E-308,1.7976931348623157E+308)	双精度浮点数值
DECIMAL	bytes	依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

(2)字符串类型

类型	大小	描述
CAHR	0-255 bytes	定长字符串(需要指定长度)
VARCHAR	0-65535bytes	变长字符串(需要指定长度)
TINYBLOB	0-255bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65535 bytes	二进制形式的长文本数据
TEXT	0-65535bytes	长文本数据
MEDIUMBLOB	0-16777215bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777215bytes	中等长度文本数据
LONGBLOB	0-4294967295bytes	二进制形式的极大文本数据
LONGTEXT	0-4294967295bytes	极大文本数据

(3)日期时间类型

类型	大小	范围	格式	描述
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	混合日期和时间值，时间戳

表操作-案例

❝

设计一张员工信息表，要求如下：

1. 编号（纯数字）

2. 员工工号 (字符串类型，长度不超过10位)

3. 员工姓名（字符串类型，长度不超过10位）

4. 性别（男/女，存储一个汉字）

5. 年龄（正常人年龄，不可能存储负数）

6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）

7. 入职时间（取值年月日即可）

❞

 

1.2.3 表操作-修改

(1)添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

①为emp表增加一个新的字段"昵称"为 nickname ,类型为 varchar(20) ：

 

(2)修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

①将emp表中的字段 workno 的数据类型改为 INT：

(3)修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

①将emp表中的字段 nickname 修改为 username，类型为 varchar(30)：

 

(4)删除字段

ALTER TABLE 表名 DROP 字段名;

①将emp表的字段 username 删除：

(5)修改表名

ALTER TABLE 表名 RENAME TO 新表名;

①将emp表的表名改为 employee：

1.2.4 表操作-删除

(1)删除表

DROP TABLE [IF EXISTS] 表名;

 

(2)删除指定表，并重新创建表(清空表数据)

TRUNCATE TABLE 表名;

 

2.DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

2.1 添加数据

(1)给指定字段添加数据

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

①给employee表中所有的字段添加数据：

INSERT INTO employee(id, workno, name, gender, age, idcard, entrydate) VALUES(1,'1001','job','男',18,'123456789123456789','2021-11-02');

 

(2)给全部字段添加数据

①插入数据到employee表：

INSERT INTO employee VALUES(2,'1002','aim','女',19,'521347854123695412','2021-5-6');

 

(3)批量添加数据：

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

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

①批量插入数据到employee表：

INSERT INTO employee VALUES(3,'1003','boy','男',20,'147154213541236981','2021-7-5'),(4,'1004','kim','女',21,'421346954122495422','2022-3-1')

 

2.2 修改数据

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

①修改id为1的数据，将name修改为kim：

UPDATE employee SET name='kim' WHERE id=1;

 

②将所有员工的入职日期修改为：2008-01-01：

UPDATE employee SET entrydate = '2008-01-01';

 

2.3 删除数据

DELETE FROM 表名 [WHERE 条件];

①删除gender为女的员工：

DELETE FROM employee WHRER gender='女';

 

②删除所有员工：

DELETE FROM employee;

3.DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

查询关键字: SELECT

数据准备工作：

create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
	entrydate date comment '入职时间'
)comment '员工表';

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

3.1 基本语法

DQL 查询语句