mysql

最新推荐文章于 2025-11-15 21:45:18 发布

原创最新推荐文章于 2025-11-15 21:45:18 发布 · 153 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

本文深入介绍了MySQL数据库，包括数据库的定义、分类（关系型与非关系型）、SQL语言的五大组成部分（DQL、DML、DDL、DCL、TCL）以及数据类型（数值、字符串、日期时间、枚举和集合）。此外，还详细阐述了数据库和表的操作，如创建、修改、查询和删除，并提供了实例。最后，讨论了各种函数的使用，如聚合函数、数学函数、字符串函数和时间函数，以及列属性（约束）的管理。

Mysql
一、数据库简介
数据
所谓数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储和处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂了。

数据库
数据库（Database，DB）指的是以一定格式存放、能够实现多个用户共享、与应用程序彼此独立的数据集合。举例：车库，水库，数据库

数据库管理系统
数据库管理系统（Database Management System，DBMS）是用来定义和管理数据的软件。如何科学的组织和存储数据，如何高效的获取和维护数据，如何保证数据的安全性和完整性，这些都需要靠数据库管理系统完成。目前，比较流行的数据库管理系统有：Oracle、MySQL、SQL Server、DB2等。

数据库应用程序
数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，如学生管理系统、人事管理系统、图书管理系统等。

数据库管理员
数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。
最终用户
最终用户（User）指的是数据库应用程序的使用者。用户面向的是数据库应用程序（通过应用程序操作数据），并不会直接与数据库打交道。
数据库系统
数据库系统（Database System，DBS）
一般是由数据库、数据库管理系统、数据库应用程序、数据库管理员和最终用户构成。其中DBMS是数据库系统的基础和核心。
二、数据库的分类
　　数据库经过几十年的发展，出现了多种类型。根据数据的组织结构不同，主要分为网状数据库、层次数据库、关系型数据库和非关系型数据库四种。网状数据库、层次数据库已经逐渐被淘汰，目前最常见的数据库模型主要是：关系型数据库和非关系型数据库。

1、关系型数据库
　　关系型数据库模型是将复杂的数据结构用较为简单的二元关系（二维表）来表示。在该类型数据库中，对数据的操作基本上都建立在一个或多个表格上，我们可以采用结构化查询语言（SQL）对数据库进行操作。关系型数据库是目前主流的数据库技术，其中具有代表性的数据库管理系统有：Oracle、DB2、SQL Server、MySQL等。

2、非关系型数据库NOSQL
　　NOSQL（Not Only SQL）泛指非关系型数据库。关系型数据库在超大规模和高并发的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。NOSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。常见的非关系型数据库管理系统有Memcached、MongoDB、Redis等。

三、SQL语言的五个部分
1·数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是：
使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：

INSERT：增加数据

UPDATE：修改数据

DELETE：删除数据

2· 数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（表、索引、视图、触发器、存储过程、函数、表空间等）进行创建、修改和删除操作。其主要包括：

CREATE：创建数据库对象

ALTER：修改数据库对象

DROP：删除数据库对象

3· 数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数据库的权限，其主要包括：

GRANT：授予用户某种权限

REVOKE：回收授予的某种权限

4· 事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：

START TRANSACTION：开启事务

COMMIT：提交事务

ROLLBACK：回滚事务

SET TRANSACTION：设置事务的属性

注意： DML和DDL的不同

数据操作语言(DML)（insert、update、delete）针对表中的数据。

而数据定义语言(DDl)（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure、触发器trigger。

四、数据库字符集和数据类型
字符集编码
MySQL、数据库、表、字段均可设置编码

– 数据编码与客户端编码不需一致

SHOW VARIABLES LIKE ‘character_set_%’ – 查看所有字符集编码项

character_set_client 客户端向服务器发送数据时使用的编码

character_set_results 服务器端将结果返回给客户端所使用的编码

character_set_connection 连接层编码

SET 变量名 = 变量值

SET character_set_client = gbk;

SET character_set_results = gbk;

SET character_set_connection = gbk;

SET NAMES GBK; – 相当于完成以上三个设置

– 校对集

校对集用以排序

SHOW CHARACTER SET [LIKE ‘pattern’]/SHOW CHARSET [LIKE ‘pattern’] 查看所有字符集

SHOW COLLATION [LIKE ‘pattern’] 查看所有校对集

CHARSET 字符集编码设置字符集编码

COLLATE 校对集编码设置校对集编码

数值类型
a. 整型
类型字节范围（有符号位）

tinyint 1字节 -128 ~ 127 无符号位：0 ~ 255

smallint 2字节 -32768 ~ 32767

mediumint 3字节 -8388608 ~ 8388607

int 4字节

bigint 8字节

int(M) M表示总位数

默认存在符号位，unsigned 属性修改

显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改

例：int(5) 插入一个数’123’，补填后为’00123’

在满足要求的情况下，越小越好。

1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。

b. 浮点型
类型字节范围

float(单精度) 4字节

double(双精度) 8字节

浮点型既支持符号位 unsigned 属性，也支持显示宽度 zerofill 属性。

不同于整型，前后均会补填0.

定义浮点型时，需指定总位数和小数位数。

float(M, D) double(M, D)

M表示总位数，D表示小数位数。

M和D的大小会决定浮点数的范围。不同于整型的固定范围。

M既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。

支持科学计数法表示。

浮点数表示近似值。

c. 定点数
decimal – 可变长度

decimal(M, D) M也表示总位数，D表示小数位数。

保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。

将浮点数转换为字符串来保存，每9位数字保存为4个字节。

字符串类型
a. char, varchar
char 定长字符串，速度快，但浪费空间

varchar 变长字符串，速度慢，但节省空间

M表示能存储的最大长度，此长度是字符数，非字节数。

不同的编码，所占用的空间不同。

char,最多255个字符，与编码无关。

varchar,最多65535字符，与编码有关。

一条有效记录最大不能超过65535个字节。

utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符

varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。

varchar 的最大有效长度由最大行大小和使用的字符集确定。

最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。

例：若一个表定义为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？答：(65535-1-2-4-30*3)/3

b. blob, text
blob 二进制字符串（字节字符串）

tinyblob, blob, mediumblob, longblob

text 非二进制字符串（字符字符串）

tinytext, text, mediumtext, longtext

text 在定义时，不需要定义长度，也不会计算总长度。

text 类型在定义时，不可给default值

c. binary, varbinary
类似于char和varchar，用于保存二进制字符串，也就是保存字节字符串而非字符字符串。

char, varchar, text 对应 binary, varbinary, blob.

日期时间类型
一般用整型保存时间戳，因为PHP可以很方便的将时间戳进行格式化。

datetime 8字节日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59

date 3字节日期 1000-01-01 到 9999-12-31

timestamp 4字节时间戳 19700101000000 到 2038-01-19 03:14:07

time 3字节时间 -838:59:59 到 838:59:59

year 1字节年份 1901 - 2155

datetime YYYY-MM-DD hh:mm:ss

timestamp YY-MM-DD hh:mm:ss

YYYYMMDDhhmmss

YYMMDDhhmmss

YYYYMMDDhhmmss

YYMMDDhhmmss

date YYYY-MM-DD

YY-MM-DD

YYYYMMDD

YYMMDD

YYYYMMDD

YYMMDD

time hh:mm:ss

hhmmss

year YYYY

YYYY

枚举和集合
1、枚举(enum)
enum(val1, val2, val3…)

在已知的值中进行单选。最大数量为65535.

枚举值在保存时，以2个字节的整型(smallint)保存。每个枚举值，按保存的位置顺序，从1开始逐一递增。

表现为字符串类型，存储却是整型。

NULL值的索引是NULL。

空字符串错误值的索引值是0。

2、集合（set）
set(val1, val2, val3…)

create table tab ( gender set(‘男’, ‘女’, ‘无’) );

insert into tab values (‘男, 女’);

最多可以有64个不同的成员。以bigint存储，共8个字节。采取位运算的形式。

当创建表时，SET成员值的尾部空格将自动被删除。

五、Mysql数据库的基本操作
数据库的启动

mysql -u 用户名-p密码

查看所有数据库

none
mysql> SELECT DATABASE();
±-----------+
| DATABASE() |
±-----------+
| szxy |
±-----------+
1 row in set (0.00 sec)
显示当前时间、用户名、数据库版本

none
mysql> SELECT now(), user(), version();
±--------------------±---------------±----------+
| now() | user() | version() |
±--------------------±---------------±----------+
| 2020-11-30 16:29:24 | root@localhost | 8.0.22 |
±--------------------±---------------±----------+
1 row in set (0.00 sec)
创建库

CREATE DATABASE[ IF NOT EXISTS] 数据库名数据库选项

数据库选项包括：

CHARACTER SET charset_name//连接字符集的设置
COLLATE collation_name//校验规则的设置
在创建数据库时可以手动更改系统默认的连接字符集和校验规则（客户端，连接和返回结果三者字符集的校验）

none
mysql> create database if not exists zqi;
Query OK, 1 row affected (0.01 sec)
查看数据库详细信息

none
mysql> show create database szxy;
±---------±-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
±---------±-------------------------------------------------------------------------------------------------------------------------------+
| szxy | CREATE DATABASE szxy /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci / /!80016 DEFAULT ENCRYPTION=‘N’ */ |
±---------±-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看服务器数据库字符集

none
mysql> show variables like ‘character_set_server’;
±---------------------±--------+
| Variable_name | Value |
±---------------------±--------+
| character_set_server | utf8mb4 |
±---------------------±--------+
1 row in set (0.00 sec)
修改数据库名称
如果使用的存储引擎是InnoDB，则不可以修改数据库名称只可以修改字符集和校验规则
修改szxy数据库的字符集为utf8

none
mysql> alter database szxy character set utf8;
Query OK, 1 row affected, 1 warning (0.02 sec)
删除数据库

none
mysql> drop database if exists zqi;
Query OK, 0 rows affected (0.01 sec)
六、表的基本操作
1、创建表的简介
语句：CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项]

1.每个字段必须有数据类型
2.最后一个字段后不能有逗号
3.TEMPORARY 临时表，会话结束时表自动消失

2、对于字段的定义：
字段名数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT ‘string’]

表选项

字符集

CHARSET = charset_name
CHARACTER SET charset_name//连接字符集的设置

如果表没有设定，则使用数据库字符集

存储引擎
ENGINE = engine_name

表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同

常见的引擎：
1.InnoDB 存储引擎：Mysql 5.5版本后默认的存储引擎，优点是支持事务，行级锁，外键约束，支持崩溃后的安全恢复；
2.MyISAM 存储引擎：不支持事务和外键，支持全文索引（但只对英文有效），特点是查询速度快；
3.Memory 存储引擎：数据放在内存当中（类似memcache）以便得到更快的响应速度，但是崩掉的话数据会丢失；
不同的引擎在保存表的结构和数据时采用不同的方式

MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引

InnoDB表文件含义：.frm表定义，表空间数据和日志文件

mysql> describe zq1;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int | YES | | NULL | NULL |
| name | varchar(10) | YES | | NULL | NULL |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)
更改存在表的名称
none
mysql> alter table zq1 rename zq2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings:
查看表的创建语句
none
mysql> show create table zq2;
±------±-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±-------------------------------------------------------------------------------------------------------------------------------+
| zq2 | CREATE TEMPORARY TABLE zq2 (
id int DEFAULT NULL,
name varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±------±-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
显示存储引擎的状态信息
none
mysql> show engines;
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
9 rows in set (0.00 sec)
修改表本身的选项

none
mysql> alter table zq2 engine=myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
对表进行重命名

none
mysql> rename table stu1 to stu2;
Query OK, 0 rows affected (0.03 sec)
修改表的字段结构（修改表内部结构)

none
ALTER TABLE 表名操作名
操作名
ADD[ COLUMN] 字段定义 – 增加字段

    AFTER 字段名 -- 表示增加在该字段名后面


    FIRST -- 表示增加在第一个


    ADD PRIMARY KEY(字段名) -- 创建主键


    ADD UNIQUE [索引名] (字段名)-- 创建唯一索引


    ADD INDEX [索引名] (字段名) -- 创建普通索引


    DROP[ COLUMN] 字段名 -- 删除字段


    MODIFY[ COLUMN] 字段名 字段属性 -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)

CHANGE[ COLUMN] 原字段名新字段名字段属性 – 支持对字段名修改

    DROP PRIMARY KEY -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)

DROP INDEX 索引名 – 删除索引

    DROP FOREIGN KEY 外键 -- 删除外键

删除表

none
mysql> drop table if exists zq2;
Query OK, 0 rows affected (0.00 sec)
清空表数据

none
mysql> truncate table stu2;
Query OK, 0 rows affected (0.02 sec)
检查表是否有错误

– 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。

– 可同时插入多条数据记录！

REPLACE 与 INSERT 完全一样，可互换。

INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, …]

查询数据
SELECT 字段列表 FROM 表名[ 其他子句]

– 可来自多个表的多个字段

– 其他子句可以不使用

– 字段列表可以用*代替，表示所有字段

删除数据
DELETE FROM 表名[ 删除条件子句]

没有条件子句，则会删除全部

更改数据
UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]

none

八、函数
聚合函数
SELECT count(id) AS total FROM n; # 总数
SELECT sum(age) AS all_age FROM n; # 总和
SELECT avg(age) AS all_age FROM n; # 平均值
SELECT max(age) AS all_age FROM n; # 最大值
SELECT min(age) AS all_age FROM n; # 最小值

数学函数
SELECT abs(-5); # 绝对值
SELECT bin(15), oct(15), hex(15); # 二进制，八进制，十六进制
SELECT pi(); # 圆周率3.141593
SELECT ceil(5.5); # 大于x的最小整数值6
SELECT floor(5.5); # 小于x的最大整数值5
SELECT greatest(3,1,4,1,5,9,2,6); # 返回集合中最大的值9
SELECT least(3,1,4,1,5,9,2,6); # 返回集合中最小的值1
SELECT mod(5,3); # 余数2
SELECT rand(); # 返回０到１内的随机值，每次不一样
SELECT rand(5); # 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
SELECT round(1415.1415); # 四舍五入1415
SELECT round(1415.1415, 3); # 四舍五入三位数1415.142
SELECT round(1415.1415, -1); # 四舍五入整数位数1420
SELECT truncate(1415.1415, 3); # 截短为3位小数1415.141
SELECT truncate(1415.1415, -1); # 截短为-1位小数1410
SELECT sign(-5); # 符号的值负数-1
SELECT sign(5); # 符号的值正数1
SELECT sqrt(9); # 平方根3
SELECT sqrt(9); # 平方根3

字符串函数
SELECT concat(‘a’, ‘p’, ‘p’, ‘le’); # 连接字符串-apple
SELECT concat_ws(‘,’, ‘a’, ‘p’, ‘p’, ‘le’); # 连接用’,’分割字符串-a,p,p,le
SELECT insert(‘chinese’, 3, 2, ‘IN’); # 将字符串’chinese’从3位置开始的2个字符替换为’IN’-chINese
SELECT left(‘chinese’, 4); # 返回字符串’chinese’左边的4个字符-chin
SELECT right(‘chinese’, 3); # 返回字符串’chinese’右边的3个字符-ese
SELECT substring(‘chinese’, 3); # 返回字符串’chinese’第三个字符之后的子字符串-inese
SELECT substring(‘chinese’, -3); # 返回字符串’chinese’倒数第三个字符之后的子字符串-ese
SELECT substring(‘chinese’, 3, 2); # 返回字符串’chinese’第三个字符之后的两个字符-in
SELECT trim(‘ chinese ‘); # 切割字符串’ chinese ‘两边的空字符-‘chinese’
SELECT ltrim(‘ chinese ‘); # 切割字符串’ chinese ‘两边的空字符-‘chinese ‘
SELECT rtrim(‘ chinese ‘); # 切割字符串’ chinese ‘两边的空字符-‘ chinese’
SELECT repeat(‘boy’, 3); # 重复字符’boy’三次-‘boyboyboy’
SELECT reverse(‘chinese’); # 反向排序-‘esenihc’
SELECT length(‘chinese’); # 返回字符串的长度-7
SELECT upper(‘chINese’), lower(‘chINese’); # 大写小写 CHINESE chinese
SELECT ucase(‘chINese’), lcase(‘chINese’); # 大写小写 CHINESE chinese
SELECT position(‘i’ IN ‘chinese’); # 返回’i’在’chinese’的第一个位置-3
SELECT position(‘e’ IN ‘chinese’); # 返回’i’在’chinese’的第一个位置-5
SELECT strcmp(‘abc’, ‘abd’); # 比较字符串，第一个参数小于第二个返回负数- -1
SELECT strcmp(‘abc’, ‘abb’); # 比较字符串，第一个参数大于第二个返回正数- 1

时间函数
SELECT current_date, current_time, now(); # 2018-01-13 12:33:43 2018-01-13 12:33:43
SELECT hour(current_time), minute(current_time), second(current_time); # 12 31 34
SELECT year(current_date), month(current_date), week(current_date); # 2018 1 1
SELECT quarter(current_date); # 1
SELECT monthname(current_date), dayname(current_date); # January Saturday
SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date); # 7 13 13

控制流函数
SELECT if(3>2, ‘t’, ‘f’), if(3<2, ‘t’, ‘f’); # t f
SELECT ifnull(NULL, ‘t’), ifnull(2, ‘t’); # t 2
SELECT isnull(1), isnull(1/0); # 0 1 是null返回1，不是null返回0
SELECT nullif(‘a’, ‘a’), nullif(‘a’, ‘b’); # null a 参数相同或成立返回null，不同或不成立则返回第一个参数
SELECT CASE 2
WHEN 1 THEN ‘first’
WHEN 2 THEN ‘second’
WHEN 3 THEN ‘third’
ELSE ‘other’
END ; # second

系统信息函数
SELECT database(); # 当前数据库名-test
SELECT connection_id(); # 当前用户id-306
SELECT user(); # 当前用户-root@localhost
SELECT version(); # 当前mysql版本
SELECT found_rows(); # 返回上次查询的检索行数

九、列属性（列约束）
1、表中添加主键

none
mysql> alter table stu3 add primary key(cno);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
2、删除主键

none
mysql> alter table stu3 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0