MySQL数据库sql教程-从入门到进阶
1、引言
MySQL 是一个广泛使用的关系型数据库管理系统(RDBMS),以下是对 MySQL 的详细描述:
1.1、基本概述
开源软件:MySQL 是开源的,遵循 GNU 通用公共许可证(GPL),用户可以免费使用、修改和分发。
跨平台:MySQL 可以在多种操作系统上运行,包括 Windows、Linux、macOS 等。
灵活性:MySQL 支持多种存储引擎,例如 InnoDB、MyISAM、Memory 等,用户可以根据应用需求选择合适的存储引擎。
1.2、主要特性
关系型数据库:MySQL 使用表(table)来存储数据,表之间通过关系(通常是外键)进行关联。
SQL 支持:MySQL 使用结构化查询语言(SQL)进行数据查询、插入、更新和删除等操作。
事务处理:MySQL 支持 ACID(原子性、一致性、隔离性、持久性)事务特性,尤其是 InnoDB 存储引擎,提供了完整的事务支持。
安全性:MySQL 提供了多种安全特性,如用户权限管理、数据加密、SSL/TLS 连接等。
高性能:MySQL 具有良好的性能表现,尤其是在高并发和大数据量环境下,通过索引、查询优化等手段,可以显著提高查询速度。
可扩展性:MySQL 支持读写分离、分库分表等扩展策略,可以应对大规模数据存储和访问需求。
2、操作数据库
2.1、create命令创建库
使用 create 命令创建数据库,基础语法:CREATE DATABASE 数据库名;
示例如下:
mysql>create database ods;
2.1.1、进阶语法1:判断创建库是否存在,不存在则创建
如果数据库已经存在,执行 CREATE DATABASE 将导致错误。
为了避免这种情况,你可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 子句:
CREATE DATABASE [IF NOT EXISTS] database_name;
参数说明:
IF EXISTS 是一个可选的子句,表示如果数据库存在才执行删除操作,避免因为数据库不存在而引发错误。
database_name 是你要删除的数据库的名称。
示例如下:
create database if not exists ods;
2.1.2、进阶语法2:判断创建库是否存在,不存在则创建,并指定字符集和排序规则
CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];
示例如下:
create database ods
character set utf8mb4
collate utf8mb4_general_ci;
2.2、drop 命令删除数据库
drop 命令格式:
1、直接删除数据库,不检查是否存在
DROP DATABASE <database_name>;
2、判断数据库是否存在,存在则删除
DROP DATABASE [IF EXISTS] <database_name>;
例如删除名为 ods 的数据库:
直接删除数据库,不检查是否存在
mysql> drop database ods;
删除数据库,如果存在的话
mysql>drop database if exists ods;
特别注意: 在执行删除数据库操作前,请确保你确实想要删除数据库及其所有数据,因为该操作是不可逆的。为了避免误操作,通常建议在执行删除之前备份数据库。
2.3、show命令查看所有数据库
1、 在MySQL中,查看所有可用的数据库:
mysql>show databases;
执行这个命令将列出MySQL服务器中所有的数据库名称。这是最简单直接的方法来获取数据库列表。
请注意,根据您的权限,您可能看不到所有数据库。只有具有足够权限的用户才能看到所有数据库的列表。
2、查看某个数据库的定义信息
在MySQL中,您可以使用SHOW CREATE DATABASE语句来查看特定数据库的定义信息。这将返回创建该数据库的SQL语句,包括字符集和校对规则。
以下是查看数据库定义信息的SQL命令:
SHOW CREATE DATABASE database_name;
mysql> show create database ods;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| ods | CREATE DATABASE `ods` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.04 sec)
2.4、alter命令修改数据库字符信息
如果你想要修改数据库的默认字符集和排序规则,你可以使用alter database语句。
例如,将数据库ods的字符集改为utf8mb4,排序规则改为utf8mb4_unicode_ci:
mysql> alter database ods character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 1 row affected (0.02 sec)
注意:请确保在执行这些操作之前备份你的数据,因为字符集的变更可能会影响文本数据的存储。
mysql> alter database ods character set utf8;
Query OK, 1 row affected (0.00 sec)
注意:MySQL 命令终止符为分号 ; 。
3、操作数据库表
3.1、create命令创建表
创建 MySQL 数据表需要以下信息:
- 表名
- 表字段名
- 定义每个表字段的数据类型
创建一个学生表,示例如下:
mysql> use ods;
Database changed
mysql> create table students (student_id int auto_increment comment '学生id',student_name varchar(50) not null comment '姓名',birth_date datetime comment '出生日期',gender int not null comment '性别',enrollment_date datetime not null comment '入学日期',email varchar(100) unique not null comment '电子邮件',phone_number varchar(20) comment '电话号码',address varchar(255) comment '地址',primary key (student_id)
) comment='学生信息表';
Query OK, 0 rows affected (0.15 sec)
上述 sql 语句做了以下几件事:
- 创建了一个名为 students 的表。
- 添加了表注释 存储学生信息的表。
- 为每个字段添加了适当的类型和约束。
- 为每个字段添加了注释,描述了该字段的用途。
字段解释:
- student_id: 学生的唯一标识符,自动递增,作为主键。
- student_name: 学生的姓名,不能为空。
- birth_date: 学生的出生日期。
- gender: 学生的性别,可选值为 '1'(男)、'0'(女)或 '2'(其他)。
- enrollment_date: 学生的入学日期,不能为空。
- email: 学生的电子邮件地址,唯一且不能为空。
- phone_number: 学生的电话号码,可以为空。
- address: 学生的地址,可以为空。
注意事项:
- varchar 类型用于字符串字段,并指定了最大长度。
- datetime 类型用于日期字段。
- primary key 定义了主键约束,确保 student_id 的唯一性。
- unique 约束确保 email 字段的唯一性。
- 执行这些 sql 语句后,您将在 school_db 数据库中创建一个名为 students 的表,并包含表注释和字段注释。
以上只是一个简单的实例,用到了一些常见的数据类型包括 int, varchar, datetime, 你可以根据实际需要选择不同的数据类型。auto_increment 关键字用于创建一个自增长的列,primary key 用于定义主键。
如果你希望在创建表时指定数据引擎,字符集和排序规则等,可以使用 character set 和 collate 子句:
create table mytable (id int primary key,name varchar(50)
) character set utf8mb4 collate utf8mb4_general_ci;
以上代码创建一个使用 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的表。
注意:MySQL 命令终止符为分号 ; 。
3.2、删除数据表
3.2.1、删除数据库表语法:
以下为删除 mysql 数据表的通用语法:
直接删除表,不检查是否存在
drop table table_name;
检查表是否存在,如果存在则删除
drop table [if exists] table_name;
示例如下:
删除表,如果存在的话
drop table if exists students ;
直接删除表,不检查是否存在
drop table students ;
3.2.2、删除表数据但保留表结构
如果你只是想删除表中的所有数据,但保留表的结构,可以使用 truncate table 语句:
truncate table table_name;
这会清空表中的所有数据,但不会删除表本身。
truncate table students ;
注意事项:
● 备份数据:在删除表之前,确保已经备份了数据,如果你需要的话。
● 外键约束:如果该表与其他表有外键约束,可能需要先删除外键约束,或者确保依赖关系被处理好。
3.3、查看表结构
desc 表名;
mysql> desc students;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| student_id | int(11) | NO | PRI | NULL | auto_increment |
| student_name | varchar(50) | NO | | NULL | |
| birth_date | datetime | YES | | NULL | |
| gender | int(11) | NO | | NULL | |
| enrollment_date | datetime | NO | | NULL | |
| email | varchar(100) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
8 rows in set (0.06 sec)
show create table 表名;
mysql> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (`student_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',`student_name` varchar(50) NOT NULL COMMENT '姓名',`birth_date` datetime DEFAULT NULL COMMENT '出生日期',`gender` int(11) NOT NULL COMMENT '性别',`enrollment_date` datetime NOT NULL COMMENT '入学日期',`email` varchar(100) NOT NULL COMMENT '电子邮件',`phone_number` varchar(20) DEFAULT NULL COMMENT '电话号码',`address` varchar(255) DEFAULT NULL COMMENT '地址',PRIMARY KEY (`student_id`),UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表' |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
3.4、修改表
3.4.1、修改表名
alter table 表名 rename to 新的表名;
把表名为students 修改为 ods_stl_students 。如下:
mysql> alter table students rename to ods_stl_students ;
Query OK, 0 rows affected (0.04 sec)
3.4.2、添加一列
alter table 表名 add 列名 数据类型;
为表ods_stl_students新增一列学生年龄,并注释,如下:
mysql> alter table ods_stl_students add age int COMMENT '年龄';
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.4.3、删除列
alter table 表名 drop 列名;
把新增学生年龄删除,如下:
mysql> alter table ods_stl_students drop age;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.5、insert into插入数据
insert into 插入数据有三种方式:
1、写全所有列名
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
以学生表为例,如下:
mysql> insert into ods_stl_students (student_name, birth_date, gender, enrollment_date, email, phone_number, address)
values
('john', '2000-05-15', '1', '2020-09-01', 'john.doe@example.com', '123-456-7890', '123 main st, anytown, usa'),
('孙悟空', '1999-11-22', '1', '2020-09-01', 'jane.smith@example.com', '098-765-4321', '傲来国花果山水帘洞'),
('alice', '2002-03-30', '0', '2021-01-15', 'alice.johnson@example.com', '555-123-4567', '789 oak st, anytown, usa'),
('唐三', '2001-07-07', '1', '2020-09-15', 'bob.brown@example.com', '555-987-6543', '法斯诺行省诺丁城南圣魂村'),
('emily', '2003-02-14', '1', '2022-02-28', 'emily.davis@example.com', '555-432-1234', '654 maple st, anytown, usa'),
('宁姚', '1998-10-10', '0', '2019-08-30', 'michael.wilson@example.com', '555-765-4321', '剑气长城'),
('sarah', '2001-04-23', '1', '2020-10-01', 'sarah.taylor@example.com', '555-321-6549', '234 cedar st, anytown, usa'),
('miss李', '2002-11-11', '1', '2021-03-15', 'david.lee@example.com', '555-654-3210', '567 ash st, anytown, usa'),
('laura', '2000-06-20', '2', '2020-09-10', 'laura.clark@example.com', '555-876-5432', '876 willow st, anytown, usa'),
('christopher', '1997-12-25', '1', '2018-09-01', 'christopher.anderson@example.com', '555-234-6789', '147 hickory st, anytown, usa');
Query OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
2、不写列名(所有列全部添加)
insert into 表名 values(值1,值2,...值n);
mysql> insert into ods_stl_students values
(1,'john', '2000-05-15', '1', '2020-09-01', 'john.doe@example.com', '123-456-7890', '123 main st, anytown, usa');
Query OK, 1 row affected (0.03 sec)
注意:不写列名时,需要把所有列加上,包括自增id。
3、插入部分数据
insert into 表名(列名1,列名2) values(值1,值2);
mysql> insert into ods_stl_students (student_name, gender, enrollment_date, email,address) values
('孙悟空', '1', '2020-09-01', 'jane.smith@example.com', '傲来国花果山水帘洞');
Query OK, 1 row affected (0.03 sec)
注意:插入部分数据时,首先要确保必填项不可缺失。
3.6、delete删除数据
3.6.1、根据条件删除数据
删除符合条件的行,语法:delete from 表名 where 列名 = 值;
示例如下:
mysql> delete from students where student_id = 1;
Query OK, 1 row affected (0.02 sec)
3.6.2、删除表中所有数据
删除所有行,语法:delete from 表名;
示例如下:
mysql> delete from students;
Query OK, 1 row affected (0.02 sec)
3.6.3、TRUNCATE命令高效删除表中所有数据
3.6.3.1、快速且高效的数据删除
TRUNCATE TABLE命令用于快速删除表中的所有数据,它通过释放存储表数据的数据页来实现数据的清除,而不是逐行删除数据。
由于不记录每一条被删除的行信息,TRUNCATE TABLE的执行速度通常远快于DELETE命令。
语法:truncate table 表名;
清除一张将近100万条数据的日志表,仅用用0.76秒,示例如下:
mysql> select count(*) from sys_log;
truncate sys_log;
+----------+
| count(*) |
+----------+
| 998280 |
+----------+
1 row in set (0.47 sec)Query OK, 0 rows affected (0.76 sec)
3.6.3.2、保留表结构和索引
在删除数据的同时,TRUNCATE TABLE保留表的结构定义,包括列、约束、索引等。
这意味着表在执行TRUNCATE TABLE操作后,仍然保持其原有的结构和索引设置,只是其中的数据被清空了。
3.6.3.3、DDL操作特性
TRUNCATE TABLE是DDL(数据定义语言)操作,而不是DML(数据操纵语言)操作。
因此,它执行后会自动提交且不能回滚,也不会触发任何与删除相关的触发器。
3.6.3.4、不记录到二进制日志
在默认情况下,TRUNCATE TABLE操作不会记录到二进制日志中,这对于主从复制环境下的数据同步可能产生影响。
但需要注意的是,这一行为可以通过特定配置进行改变。
3.6.3.5、权限要求
执行TRUNCATE TABLE命令需要相应的权限,用户需要对目标表拥有ALTER权限才能执行此操作。
这一权限要求确保了只有具有足够权限的用户才能对表进行清空操作,从而维护了数据的安全性。
3.6.3.6、使用场景
TRUNCATE TABLE特别适用于需要快速清理大量数据或定期维护时高效地重置某个表至初始状态的场景。
例如,在数据仓库环境中,可能需要定期清空某些临时表或日志表以释放存储空间;在测试环境中,也可能需要频繁地重置表数据以进行新的测试。
3.7、 update更新数据
3.7.1、根据条件更新数据
带条件的修改,语法:update 表名 set 列名 = 值 where 列名=值;
更新学生表中的电话号码,示例如下:
mysql> update students set phone_number = '13656526356' where student_id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3.7.2、不带条件更新数据
不带条件的更新会更新所有行,语法:update 表名 set 列名 = 值;
更新学生表中的电话号码,示例如下:
mysql> update students set phone_number = '13656526356' ;
select student_id, phone_number from students ;
Query OK, 9 rows affected (0.03 sec)
Rows matched: 10 Changed: 9 Warnings: 0+------------+--------------+
| student_id | phone_number |
+------------+--------------+
| 1 | 13656526356 |
| 2 | 13656526356 |
| 3 | 13656526356 |
| 4 | 13656526356 |
| 5 | 13656526356 |
| 6 | 13656526356 |
| 7 | 13656526356 |
| 8 | 13656526356 |
| 9 | 13656526356 |
| 10 | 13656526356 |
+------------+--------------+
10 rows in set (0.03 sec)
3.7.3、多列更新
要将students表中编号为2的学生的first_name更新为悟空,last_name更新为孙,可以使用以下语句:
示例如下:
mysql> select * from students where student_id =12 ;update students set first_name = '悟空', last_name = '孙' where student_id = 12;select * from students where student_id =12 ;
+------------+------------+-----------+------------+--------+-----------------+------------------------+--------------+--------------------------+
| student_id | first_name | last_name | birth_date | gender | enrollment_date | email | phone_number | address |
+------------+------------+-----------+------------+--------+-----------------+------------------------+--------------+--------------------------+
| 12 | Jane | Smith | 1999-11-22 | Female | 2020-09-01 | jane.smith@example.com | 098-765-4321 | 456 Elm St, Anytown, USA |
+------------+------------+-----------+------------+--------+-----------------+------------------------+--------------+--------------------------+
1 row in set (0.04 sec)Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0+------------+------------+-----------+------------+--------+-----------------+------------------------+--------------+--------------------------+
| student_id | first_name | last_name | birth_date | gender | enrollment_date | email | phone_number | address |
+------------+------------+-----------+------------+--------+-----------------+------------------------+--------------+--------------------------+
| 12 | 悟空 | 孙 | 1999-11-22 | Female | 2020-09-01 | jane.smith@example.com | 098-765-4321 | 456 Elm St, Anytown, USA |
+------------+------------+-----------+------------+--------+-----------------+------------------------+--------------+--------------------------+
1 row in set (0.04 sec)
4、基础函数
4.1、数学函数
4.1.1、ABS(x)
功能:返回x的绝对值。
示例:SELECT ABS(-5); 结果为5。
mysql> SELECT ABS(-5);
+---------+
| ABS(-5) |
+---------+
| 5 |
+---------+
1 row in set (0.10 sec)
4.1.2、CEILING(x)
功能:返回大于或等于x的最小整数(向上取整)。
示例:SELECT CEILING(3.14); 结果为4。
mysql> SELECT CEILING(3.14);
+---------------+
| CEILING(3.14) |
+---------------+
| 4 |
+---------------+
1 row in set (0.04 sec)
4.1.3、FLOOR(x)
功能:返回小于或等于x的最大整数(向下取整)。
示例:SELECT FLOOR(3.14); 结果为3。
mysql> SELECT FLOOR(3.14);
+-------------+
| FLOOR(3.14) |
+-------------+
| 3 |
+-------------+
1 row in set (0.06 sec)
4.1.4、ROUND(x, y)
功能:返回参数x的四舍五入到y位小数的值。
示例:SELECT ROUND(3.14159, 2); 结果为3.14。
mysql> SELECT ROUND(3.14159, 2);
+-------------------+
| ROUND(3.14159, 2) |
+-------------------+
| 3.14 |
+-------------------+
1 row in set (0.06 sec)
4.1.5、TRUNCATE(x, y)
功能:返回数字x截短为y位小数的结果,不进行四舍五入。
示例:SELECT TRUNCATE(3.14159, 2); 结果为3.14。
mysql> SELECT TRUNCATE(3.14159, 2);
+----------------------+
| TRUNCATE(3.14159, 2) |
+----------------------+
| 3.14 |
+----------------------+
1 row in set (0.06 sec)
4.1.6、RAND()
功能:返回0到1内的随机值。可以通过提供一个参数(种子)来生成指定的随机数。
示例:SELECT RAND(); 每次执行都会返回不同的随机数。
mysql> SELECT RAND();
+-----------------------+
| RAND() |
+-----------------------+
| 0.0006828000775378198 |
+-----------------------+
1 row in set (0.06 sec)
4.1.7、MOD(x, y)
功能:返回x除以y的余数。
示例:SELECT MOD(10, 3); 结果为1。
mysql> SELECT MOD(10, 3);
+------------+
| MOD(10, 3) |
+------------+
| 1 |
+------------+
1 row in set (0.06 sec)
4.1.8、PI()
功能:返回圆周率π的值。
示例:SELECT PI(); 结果为3.141593。
mysql> SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.06 sec)
4.2、字符串函数
4.2.1、CONCAT(s1, s2, ..., sn)
功能:将多个字符串连接成一个字符串。
示例:SELECT CONCAT('Hello', ' ', 'World'); 结果为'Hello World'。
mysql> SELECT CONCAT('Hello', ' ', 'World');
+-------------------------------+
| CONCAT('Hello', ' ', 'World') |
+-------------------------------+
| Hello World |
+-------------------------------+
1 row in set (0.05 sec)
4.2.2、CONCAT_WS(sep, s1, s2, ..., sn)
功能:使用指定的分隔符将多个字符串连接成一个字符串。
示例:SELECT CONCAT_WS('-', '2024', '12', '17'); 结果为'2024-12-17'。
mysql> SELECT CONCAT_WS('-', '2024', '12', '17');
+------------------------------------+
| CONCAT_WS('-', '2024', '12', '17') |
+------------------------------------+
| 2024-12-17 |
+------------------------------------+
1 row in set (0.08 sec)
4.2.3、SUBSTRING(str, pos, len) 或 SUBSTR(str, pos, len)
功能:从字符串中提取从位置pos开始的len个字符的子字符串。
示例:SELECT SUBSTRING('Hello, World!', 1, 5); 结果为'Hello'。
mysql> SELECT SUBSTRING('Hello, World!', 1, 5);
+----------------------------------+
| SUBSTRING('Hello, World!', 1, 5) |
+----------------------------------+
| Hello |
+----------------------------------+
1 row in set (0.06 sec)
4.2.4、LEFT(str, len)
功能:返回字符串str从最左边开始的len个字符。
示例:SELECT LEFT('Hello, World!', 5); 结果为'Hello'。
mysql> SELECT LEFT('Hello, World!', 5);
+--------------------------+
| LEFT('Hello, World!', 5) |
+--------------------------+
| Hello |
+--------------------------+
1 row in set (0.06 sec)
4.2.5、RIGHT(str, len)
功能:返回字符串str从最右边开始的len个字符。
示例:SELECT RIGHT('Hello, World!', 6); 结果为'World!'。
mysql> SELECT RIGHT('Hello, World!', 6);
+---------------------------+
| RIGHT('Hello, World!', 6) |
+---------------------------+
| World! |
+---------------------------+
1 row in set (0.07 sec)
4.2.6、TRIM(str)
功能:去除字符串str首部和尾部的所有空格。
示例:SELECT TRIM(' Hello, World! '); 结果为'Hello, World!'。
mysql> SELECT TRIM(' Hello, World! ');
+-------------------------+
| TRIM(' Hello, World! ') |
+-------------------------+
| Hello, World! |
+-------------------------+
1 row in set (0.06 sec)
4.2.7、LTRIM(str)
功能:去除字符串str左侧的空格。
示例:SELECT LTRIM(' Hello, World! '); 结果为'Hello, World! '。
mysql> SELECT LTRIM(' Hello, World! ');
+--------------------------+
| LTRIM(' Hello, World! ') |
+--------------------------+
| Hello, World! |
+--------------------------+
1 row in set (0.06 sec)
4.2.8、RTRIM(str)
功能:去除字符串str右侧的空格。
示例:SELECT RTRIM(' Hello, World! '); 结果为' Hello, World!'。
mysql> SELECT RTRIM(' Hello, World! ');
+--------------------------+
| RTRIM(' Hello, World! ') |
+--------------------------+
| Hello, World! |
+--------------------------+
1 row in set (0.06 sec)
4.2.9、UPPER(str)
功能:将字符串str中的所有字符转换为大写。
示例:SELECT UPPER('hello'); 结果为'HELLO'。
mysql> SELECT UPPER('hello');
+----------------+
| UPPER('hello') |
+----------------+
| HELLO |
+----------------+
1 row in set (0.06 sec)
4.2.10、LOWER(str)
功能:将字符串str中的所有字符转换为小写。
示例:SELECT LOWER('HELLO'); 结果为'hello'。
mysql> SELECT LOWER('HELLO');
+----------------+
| LOWER('HELLO') |
+----------------+
| hello |
+----------------+
1 row in set (0.05 sec)
4.3、日期和时间函数
4.3.1、NOW()
功能:返回当前的日期和时间。
示例:SELECT NOW(); 结果为当前日期和时间,如'2024-12-17 21:36:07'。
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2024-12-17 21:36:07 |
+---------------------+
1 row in set (0.07 sec)
4.3.2、CURDATE() 或 CURRENT_DATE()
功能:返回当前的日期。
示例:SELECT CURDATE(); 结果为当前日期,如'2024-12-17'。
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2024-12-17 |
+------------+
1 row in set (0.12 sec)mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2024-12-17 |
+----------------+
1 row in set (0.06 sec)
4.3.3、CURTIME() 或 CURRENT_TIME()
功能:返回当前的时间。
示例:SELECT CURTIME(); 结果为当前时间,如'21:36:07'。
mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 13:59:12 |
+-----------+
1 row in set (0.06 sec)mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 13:59:24 |
+----------------+
1 row in set (0.06 sec)
4.3.4、DATE_FORMAT(date, fmt)
功能:按照指定的格式fmt格式化日期date。
示例:SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); 结果为'2024-12-17 21:38:17'。
mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
+-----------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2024-12-17 21:38:17 |
+-----------------------------------------+
1 row in set (0.07 sec)
4.3.5、DATE_ADD(date, INTERVAL int keyword)
功能:返回日期date加上一个时间间隔后的结果。
示例:SELECT DATE_ADD(CURDATE(), INTERVAL 6 MONTH); 结果为当前日期加6个月后的日期。
mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 6 MONTH);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL 6 MONTH) |
+---------------------------------------+
| 2025-06-17 |
+---------------------------------------+
1 row in set (0.11 sec)
4.3.6、DATE_SUB(date, INTERVAL int keyword)
功能:返回日期date减去一个时间间隔后的结果。
示例:SELECT DATE_SUB(CURDATE(), INTERVAL 6 MONTH); 结果为当前日期减6个月后的日期。
mysql> SELECT DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
+---------------------------------------+
| DATE_SUB(CURDATE(), INTERVAL 6 MONTH) |
+---------------------------------------+
| 2024-06-17 |
+---------------------------------------+
1 row in set (0.06 sec)
4.4、聚合函数
4.4.1、COUNT(col)
功能:返回指定列中非NULL值的个数。
示例:SELECT COUNT(*) FROM sys_log; 结果为users表中的行数。
mysql> select count(*) from sys_log ;
+----------+
| count(*) |
+----------+
| 499140 |
+----------+
1 row in set (0.41 sec)
4.4.2、SUM(col)
功能:返回指定列的所有值之和。
示例: SELECT SUM(time) FROM sys_log; 结果为sys_log表中time列的总和。
mysql> SELECT SUM(time) FROM sys_log;
+-----------+
| SUM(time) |
+-----------+
| 414517440 |
+-----------+
1 row in set (0.91 sec)
4.4.3、AVG(col)
功能:返回指定列的平均值。
示例:SELECT AVG(time) FROM sys_log; 结果为sys_log表中time列的平均值。
mysql> SELECT AVG(time) FROM sys_log;
+-----------+
| AVG(time) |
+-----------+
| 830.4633 |
+-----------+
1 row in set (0.88 sec)
4.4.4、MAX(col)
功能:返回指定列的最大值。
示例:SELECT MAX(time) FROM sys_log; 结果为sys_log表中time列的最大值。
mysql> SELECT MAX(time) FROM sys_log;
+-----------+
| MAX(time) |
+-----------+
| 7841 |
+-----------+
1 row in set (0.98 sec)
4.4.5、MIN(col)
功能:返回指定列的最大值。
示例:SELECT MIN(time) FROM sys_log; 结果为sys_log表中time列的最小值。
mysql> SELECT MIN(time) FROM sys_log;
+-----------+
| MIN(time) |
+-----------+
| 14 |
+-----------+
1 row in set (0.87 sec)
4.4.6、GROUP_CONCAT(col)
功能:将属于一组的列值连接组合而成的结果。
示例:SELECT GROUP_CONCAT(title) FROM sys_log; 结果为sys_log表中title列的所有值连接成一个字符串。
mysql> SELECT GROUP_CONCAT(title) FROM sys_log;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(title) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登陆,后台登 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.97 sec)
4.5、条件判断函数
4.5.1、IF(condition, value_if_true, value_if_false)
功能:如果condition为真(非零),则返回value_if_true,否则返回value_if_false。
示例:SELECT IF(1>0, 'True', 'False'); 结果为'True'。
mysql> SELECT IF(1>0, 'True', 'False');
+--------------------------+
| IF(1>0, 'True', 'False') |
+--------------------------+
| True |
+--------------------------+
1 row in set (0.09 sec)
4.5.2、CASE WHEN
功能:允许根据一系列条件进行多路分支选择。
示例:SELECT CASE WHEN 1>0 THEN 'True' ELSE 'False' END; 结果为'True'。
mysql> SELECT CASE WHEN 1>0 THEN 'True' ELSE 'False' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'True' ELSE 'False' END |
+--------------------------------------------+
| True |
+--------------------------------------------+
1 row in set (0.08 sec)
SELECT CASE WHEN time < 3000 THEN 'Low'WHEN time >= 3000 AND time < 5000 THEN 'Medium'ELSE 'High'END AS time
FROM sys_log;
4.5.3、IFNULL(expression1, expression2)
功能:如果expression1不为NULL,则返回expression1,否则返回expression2。
示例:SELECT IFNULL(NULL, 10); 结果为10。
mysql> SELECT IFNULL(NULL, 10);
+------------------+
| IFNULL(NULL, 10) |
+------------------+
| 10 |
+------------------+
1 row in set (0.09 sec)
4.5.4、COALESCE(value1, value2, ...)
功能:返回参数列表中的第一个非NULL值。
示例:SELECT COALESCE(NULL, NULL, 3); 结果为3。
mysql> SELECT COALESCE(NULL, NULL, 3);
+-------------------------+
| COALESCE(NULL, NULL, 3) |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.08 sec)
4.5.5、NULLIF(expression1, expression2)
功能:如果expression1等于expression2,则返回NULL,否则返回expression1。
示例:SELECT NULLIF(1, 1); 结果为NULL。
mysql> SELECT NULLIF(1, 1);
+--------------+
| NULLIF(1, 1) |
+--------------+
| NULL |
+--------------+
1 row in set (0.07 sec)
5、常用函数
5.1、字符串处理函数
5.1.1、CHAR_LENGTH()
用途:返回字符串的字符数(与LENGTH()不同,LENGTH()返回的是字节数)。
示例:SELECT CHAR_LENGTH('Hello, 世界!') AS CharCount;
mysql> SELECT CHAR_LENGTH('Hello, 世界!') AS CharCount;
+-----------+
| CharCount |
+-----------+
| 10 |
+-----------+
1 row in set (0.06 sec)
5.1.2、INSTR()
用途:返回子字符串在字符串中第一次出现的位置。
示例:SELECT INSTR('Hello, World!', 'World') AS Position;
mysql> SELECT INSTR('Hello, World!', 'World') AS Position;
+----------+
| Position |
+----------+
| 8 |
+----------+
1 row in set (0.07 sec)
5.1.3、LEFT() 和 RIGHT()
用途:分别从字符串的左侧或右侧提取指定长度的子字符串。
示例:SELECT LEFT('Hello, World!', 5) AS LeftSubstring, RIGHT('Hello, World!', 6) AS RightSubstring;
mysql> SELECT LEFT('Hello, World!', 5) AS LeftSubstring, RIGHT('Hello, World!', 6) AS RightSubstring;
+---------------+----------------+
| LeftSubstring | RightSubstring |
+---------------+----------------+
| Hello | World! |
+---------------+----------------+
1 row in set (0.06 sec)
5.1.4、LPAD() 和 RPAD()
用途:分别在字符串的左侧或右侧填充指定的字符,直到达到指定的长度。
示例:SELECT LPAD('123', 5, '0') AS LeftPadded, RPAD('123', 5, '0') AS RightPadded;
mysql> SELECT LPAD('123', 5, '0') AS LeftPadded, RPAD('123', 5, '0') AS RightPadded;
+------------+-------------+
| LeftPadded | RightPadded |
+------------+-------------+
| 00123 | 12300 |
+------------+-------------+
1 row in set (0.07 sec)
5.2、数学函数
5.2.1、POW() 或 POWER()
用途:返回给定数字的指定幂。
示例:SELECT POW(2, 3) AS Result;
mysql> SELECT POW(2, 3) AS Result;
+--------+
| Result |
+--------+
| 8 |
+--------+
1 row in set (0.11 sec)
5.2.2、MOD()
用途:返回两个数相除的余数。
示例:SELECT MOD(10, 3) AS Remainder;
mysql> SELECT MOD(10, 3) AS Remainder;
+-----------+
| Remainder |
+-----------+
| 1 |
+-----------+
1 row in set (0.06 sec)
5.2.3、RAND()
用途:返回一个0到1之间的随机浮点数。
示例:SELECT RAND() AS RandomNumber;
mysql> SELECT RAND() AS RandomNumber;
+--------------------+
| RandomNumber |
+--------------------+
| 0.8309050619890029 |
+--------------------+
1 row in set (0.05 sec)
5.3、日期和时间函数
5.3.1、DATE_FORMAT()
用途:根据指定的格式返回日期或时间值。
示例:SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS FormattedDateTime;
mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS FormattedDateTime;+---------------------+
| FormattedDateTime |
+---------------------+
| 2024-12-17 14:46:45 |
+---------------------+
1 row in set (0.05 sec)
5.3.2、DAYOFWEEK()
用途:返回日期是星期几(1表示星期日,2表示星期一,依此类推)。
示例:SELECT DAYOFWEEK('2023-10-07') AS DayOfWeek;
mysql> SELECT DAYOFWEEK('2023-10-07') AS DayOfWeek;
+-----------+
| DayOfWeek |
+-----------+
| 7 |
+-----------+
1 row in set (0.08 sec)
5.3.3、TIMESTAMPDIFF()
用途:返回两个日期或日期时间值之间的差异,结果以指定的时间单位表示。
示例:SELECT TIMESTAMPDIFF(DAY, '2023-10-01', '2023-10-10') AS DaysDifference;
mysql> SELECT TIMESTAMPDIFF(DAY, '2023-10-01', '2023-10-10') AS DaysDifference;
+----------------+
| DaysDifference |
+----------------+
| 9 |
+----------------+
1 row in set (0.07 sec)
5.4、聚合函数
5.4.1、 DISTINCT 与聚合函数结合使用
用途:在聚合函数中排除重复值。
示例:SELECT COUNT(DISTINCT id) AS UniqueID FROM sys_log;
mysql> SELECT count(*) FROM sys_log;
SELECT COUNT(DISTINCT id) AS UniqueID FROM sys_log;
+----------+
| count(*) |
+----------+
| 499140 |
+----------+
1 row in set (0.34 sec)+----------+
| UniqueID |
+----------+
| 354 |
+----------+
1 row in set (2.43 sec)
5.4.2、ROW_NUMBER()
未完待续