MySql的SQL语句

MySql的SQL语句

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。

2. SQL语句可以使用空格/缩进来增强语句的可读性。

3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4. 注释:

   • 单行注释:--注释内容或#注释内容(MySQL特有)
   • 多行注释:/*注释内容*/

SQL分类

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

数据类型

数据类型	说明
BIT(n)	固定长度位字段类型，存储比特位数为 n 的值。
TINYINT	微小整数类型，用于存储非常小的整数值。
BOOL 或 BOOLEAN	布尔类型，用于存储真或假的值。
SMALLINT	小整数类型，用于存储较小范围的整数值。
MEDIUMINT	中等整数类型，介于 INT 和 SMALLINT 之间的整数值。
INT 或 INTEGER	普通整数类型，用于存储常见的整数值。
BIGINT	大整数类型，用于存储更大范围的整数值。
FLOAT(p)	单精度浮点数类型，可指定精度 p，用于存储小数值。
DOUBLE(p)	双精度浮点数类型，可指定精度 p，用于存储更高精度的小数值。
DECIMAL(p, s)	高精度小数类型，用于存储精确的小数值，其中 p 表示总位数，s 表示小数位数。
CHAR(n)	定长字符串类型，存储固定长度的字符串，最大长度为 n。
VARCHAR(n)	变长字符串类型，存储可变长度的字符串，最大长度为 n。
BINARY(n)	定长二进制字符串类型，存储固定长度的二进制数据，最大长度为 n。
VARBINARY(n)	变长二进制字符串类型，存储可变长度的二进制数据，最大长度为 n。
TINYBLOB	微小二进制大对象类型，用于存储非常小的二进制数据。
BLOB	二进制大对象类型，用于存储可变长度的大二进制数据。
MEDIUMBLOB	中等二进制大对象类型，用于存储中等大小的二进制数据。
LONGBLOB	长二进制大对象类型，用于存储较大的二进制数据。
TINYTEXT	微小文本类型，用于存储非常小的文本数据。
TEXT	文本类型，用于存储可变长度的文本数据。
MEDIUMTEXT	中等文本类型，用于存储中等大小的文本数据。
LONGTEXT	长文本类型，用于存储较大的文本数据。
ENUM('value1', 'value2', ...)	枚举类型，用于从预定义的值列表中选择一个值存储。
SET('value1', 'value2', ...)	集合类型，用于从预定义的值列表中选择多个值存储。
DATE	日期类型，用于存储年、月、日的日期值。
TIME	时间类型，用于存储时、分、秒的时间值。
DATETIME	日期时间类型，用于存储日期和时间的组合值。
TIMESTAMP	时间戳类型，用于存储自 UTC 时间纪元（1970 年1月1日）以来的秒数。常用于记录数据的最后修改时间。
YEAR	年份类型，用于存储年份值。

DDL

数据库操作

查询

查询所有数据库

show databases;

查询当前数据库

select database();

创建

create database [if not exists] 数据库名 [default charset 字符集[utf8mb4]][collate 排序规则[utf8mb4_unicode_ci]];

删除

drop database [if exists] 数据库名;

使用

use 数据库名;

表操作

查询

查询当前数据库所有表

show tables;

查询表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

创建

创建表

create table 表名{
	字段1 字段1类型 [comment 字段1注释],
	字段2 字段2类型 [comment 字段2注释],
	字段3 字段3类型 [comment 字段3注释],
	字段4 字段4类型 [comment 字段4注释]
}[comment 表注释];

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 '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';

修改

添加字段

alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

修改数据类型

alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

删除字段

alter table 表名 drop 字段名;

修改表名

alter table 表名 rename to 新表名;

删除

删除表

drop table [if exists] 表名;

删除指定表，并重建该表（用于删除所有数据）

truncate table 表名;

DML

介绍

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

• 添加数据(insert)
• 修改数据(update)
• 删除数据(delete)

添加数据

给指定字段添加数据

insert into 表名(字段名1,字段名2,...) values(值1,值2,....);

给全部字段添加数据

insert into 表名 values(值1,值2,....);

批量添加数据

insert into 表名(字段名1,字段名2,...) values(值1,值2,....),(值1,值2,....),(值1,值2,....);
insert into 表名 values(值1,值2,....),(值1,值2,....),(值1,值2,....);

注意:

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。·字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

修改数据

update 表名 set 字段名1 = 值1, 字段名2 = 值2,...[where 条件];

注意:修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据

delete from 表名 [where 条件];

注意:

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE语句不能删除某一个字段的值(可以使用UPDATE)。

DQL

介绍

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

语法

基本查询

查询多个字段

select 字段1,字段2,字段3,...from 表名;
select * from 表名;

设置别名

select 字段1 [as 别名1],字段2 [as 别名2],字段3 [as 别名3]... from 表名;

去除重复记录

select distinct 字段列表 from 表名;

条件查询

语法

select 字段列表 from 表名 where 条件列表;

条件

运算符	描述	示例
=	等于某个值	SELECT * FROM table WHERE column = value;
!= 或 <>	不等于某个值	SELECT * FROM table WHERE column != value;
>	大于某个值	SELECT * FROM table WHERE column > value;
<	小于某个值	SELECT * FROM table WHERE column < value;
>=	大于等于某个值	SELECT * FROM table WHERE column >= value;
<=	小于等于某个值	SELECT * FROM table WHERE column <= value;
BETWEEN…AND…	在某个范围内	SELECT * FROM table WHERE column BETWEEN low AND high;
LIKE 占位符(_、%)	匹配某种模式	SELECT * FROM table WHERE column LIKE 'pattern';
IN(…)	在给定的值列表中	SELECT * FROM table WHERE column IN (value1, value2, ...);
IS NULL	为 NULL	SELECT * FROM table WHERE column IS NULL;

逻辑运算符	描述	示例
AND 或 &&	逻辑与	SELECT * FROM table WHERE cond1 AND cond2;
OR 或 ||	逻辑或	SELECT * FROM table WHERE cond1 OR cond2;
NOT 或 !	逻辑非	SELECT * FROM table WHERE NOT condition;

聚合函数

介绍

将一列数据作为一个整体，进行纵向计算。

常见的聚合函数

聚合函数	描述	示例
COUNT(*)	计算行数（包括 NULL 值）	SELECT COUNT(*) FROM table;
COUNT(column)	计算非 NULL 值行数	SELECT COUNT(column) FROM table;
SUM(column)	计算指定列的总和	SELECT SUM(column) FROM table;
AVG(column)	计算指定列的平均值	SELECT AVG(column) FROM table;
MAX(column)	获取指定列的最大值	SELECT MAX(column) FROM table;
MIN(column)	获取指定列的最小值	SELECT MIN(column) FROM table;

语法

select 聚合函数(字段列表) from 表名;

分组查询

分组查询（Group By）用于将结果集按照指定的列进行分组，并对每个分组应用聚合函数，以便对每个分组生成汇总结果。

语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where与having区别

• 执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
• 判断条件不同: where不能对聚合函数进行判断，而having可以。

注意：

• 执行顺序: where >聚合函数> having .
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

语法

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

排序方式

• ASC：升序(默认)
• DESC：降序

注意:如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

语法

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意

• 起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

执行顺序

DCL

介绍

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

查询用户

use mysql;
select * from user;

创建用户

create user '用户名'@'主机名' identified by '密码';

修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户

drop user '用户名'@'主机名';

注意:

• 主机名可以使用%通配。

权限控制

权限	描述
SELECT	允许用户读取（查询）表中的数据
INSERT	允许用户向表中插入新的行数据
UPDATE	允许用户更新表中现有行的数据
DELETE	允许用户从表中删除现有行数据
CREATE	允许用户创建新的数据库和表
DROP	允许用户删除现有的数据库和表
ALTER	允许用户修改现有的数据库结构（表结构）
GRANT	允许用户授予或撤销其他用户的权限
REFERENCES	允许用户定义外键约束
INDEX	允许用户创建和删除索引
ALL PRIVILEGES or ALL	允许用户执行所有权限的操作，相当于赋予全部权限
CREATE TEMPORARY TABLES	允许用户创建临时表
EXECUTE	允许用户执行存储过程和函数
FILE	允许用户在服务器上读写文件
PROCESS	允许用户查看当前运行的进程
RELOAD	允许用户重新加载服务器配置文件
REPLICATION CLIENT	允许用户查看主从复制信息
SHOW DATABASES	允许用户查看所有数据库列表
SHUTDOWN	允许用户关闭数据库服务器
SUPER	允许用户执行一些特殊的系统级操作，如修改全局变量或关闭服务器

查询权限

show grants for '用户名'@'主机名';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

撤销权限

revoke 权限列表 on 数据库名.表名 from'用户名'@'主机名';

ENT | 允许用户查看主从复制信息 |
| SHOW DATABASES | 允许用户查看所有数据库列表 |
| SHUTDOWN | 允许用户关闭数据库服务器 |
| SUPER | 允许用户执行一些特殊的系统级操作，如修改全局变量或关闭服务器 |

查询权限

show grants for '用户名'@'主机名';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

撤销权限

revoke 权限列表 on 数据库名.表名 from'用户名'@'主机名';