mysql 语法1

1、注释

单行 --
多行 /**/

2、SQL 分类

主要分为四类DDL、DML、DQL、DCL

DDL

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

常用指令

CREATE：在数据库中创建对象
ALTER：更改数据库的结构
DROP：从数据库中删除对象
TRUNCATE：从表中删除所有记录，包括为记录分配的所有空间
COMMENT：向数据字典添加注释
RENAME：重命名一个对象

DML

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

常用指令

SELECT：从数据库检索数据（也有说法认为 SELECT 是 DRL：数据检索语言）
INSERT：将数据插入表中
UPDATE：更新表中已存在的数据
DELETE：删除表中的所有记录，但记录的空间仍然保留
MERGE：UPSERT 操作（插入或更新）
CALL：调用 PL/SQL 或 Java 子程序
EXPLAIN PLAN：解释访问数据的路径
LOCK TABLE：控制并发性

DCL

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

常用指令

GRANT：赋予用户数据库访问权限
REVOKE：撤销使用 GRANT 命令赋予的访问权限

TCL

TCL英文全称是Transaction Control Language，用于管理数据库事务的语句集

常用指令

COMMIT：保存所做的工作。
SAVEPOINT：确定一个事务中的点，以便之后可以回滚到这一点。
ROLLBACK：将数据库恢复到最后一次 COMMIT 之前的状态。
SET TRANSACTION：更改事务选项，如隔离级别和回滚段的使用

3、mysql 数据类型

数值类型

• 整型：
  • TINYINT：1字节
  • SMALLINT：2字节
  • MEDIUMINT：3字节
  • INT 或 INTEGER：4字节
  • BIGINT：8字节
• 浮点数：
  • FLOAT：单精度浮点数
  • DOUBLE：双精度浮点数
• 定点数：
  • DECIMAL或 NUMERIC：用于存储精确的小数

字符串类型

• 固定长度字符串：
  • CHAR：固定长度的字符串
• 可变长度字符串：
  • VARCHAR：可变长度的字符串
• 文本数据类型：
  • TINYTEXT：最大长度 255 字符
  • TEXT：最大长度 65,535 字符
  • MEDIUMTEXT：最大长度 16,777,215 字符
  • LONGTEXT：最大长度 4,294,967,295 字符
• 二进制数据类型：
  • BINARY：固定长度二进制字符串
  • VARBINARY：可变长度二进制字符串
  • BLOB：二进制大对象
  • MEDIUMBLOB：中等长度二进制大对象
  • LONGBLOB：长二进制大对象

日期和时间类型

• DATE：日期 YYYY-MM-DD
• TIME：时间 HH:MM:SS
• DATETIME：日期和时间 YYYY-MM-DD HH:MM:SS
• TIMESTAMP：时间戳 YYYY-MM-DD HH:MM:SS
• YEAR：年份 1901-2155

枚举类型

• ENUM：一个字符串对象，只能有一个值，从列表中选择

集合类型

• SET：一个字符串对象，可以有 0 或多个值，从列表中选择

空间数据类型

• GEOMETRY：表示空间数据
• POINT：表示一个点
• LINESTRING：表示一系列点构成的线
• POLYGON：表示一个多边形
• 等等...

JSON 数据类型

• JSON：用于存储有效的 JSON 文档

其他数据类型

• BIT：二进制位字段
• BOOLEAN：逻辑布尔值 TRUE 或 FALSE

4、常用语句

4-1、数据库相关

查询所有数据库

show databases;

创建数据库

create database [ if not exists ] 数据库名

删除数据库

drop database [ if exists ] 数据库名 ;

切换数据库

use 数据库名 ;

4-2、表相关

查询当前数据库所有表

show tables;

查看指定表的表结构

desc 表名

查看指定表的建表结构

show create table 表名 ;

创建表结构

create table tb_user(
    id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别'
) comment '用户表';

表修改

1）添加表字段

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

2）修改表字段

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

3）修改字段名和字段类型

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

4）删除字段

ALTER TABLE 表名 DROP 字段名;

5）修改表名

ALTER TABLE 表名 RENAME TO 新表名;

表删除

1）删除指定表

DROP TABLE [ IF EXISTS ] 表名; 

2）删除指定表, 并重新创建表

TRUNCATE TABLE 表名;

插入数据

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, ...) ;

1、插入数据时，指定的字段顺序需要与值的顺序是一一对应的
2、字符串和日期型数据应该包含在引号中

修改数据

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

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

删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;

1、DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

2、DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。

查询

SELECT
  字段列表
FROM
  表名列表
WHERE
  条件列表
GROUP BY
  分组字段列表
HAVING
  分组后条件列表
ORDER BY
  排序字段列表
LIMIT
  分页参数

1）设置别名

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名; 

2）去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

3）条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表 ; 1

常见条件查询运算符

<>,!=,不等于
in（...）,在in之后的列表中的值，多选一
between ... and ...,
like 占位符, 模糊匹配(_匹配单个字符, %匹配任意个字符)
is null,是NULL

常用的逻辑运算符

AND 或 && ,并且 (多个条件同时成立)
OR 或 || 或者 (多个条件任意一个成立)
NOT 或 ! 非 , 不是

4）聚合函数

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

SELECT 聚合函数(字段列表) FROM 表名 ; 

常见的聚合函数

count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和

5）分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
后过滤条件 ];

where 和 having 区别

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

6）排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

desc 降序
asc 升序

示例：

7）分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ; 

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

4-3 用户相关

查询用户

select * from mysql.user;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户

DROP USER '用户名'@'主机名' ;

示例

CREATE USER 'JaShin'@'localhost' IDENTIFIED BY '密码'; 

权限相关

常见权限名
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE

查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; 1

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

4-3 多表连接

左连接
left join

右连接
right join

内连接
inner join

全连接

字段

Limit

select * from user limit [起始条数，从0开始],[查询条数总数]；

select * from user limit (curentPage-1) * PageSize, PageSize;   

limit m,n 返回偏移量为m之后的n条数据，即先获取m+n条记录，然后丢弃前面的m条，返回之后的n条记录
limit n 返回开头的n条数据，相当于limit 0, n

Like

select * from user where name like '%a%'

-- [charlist] 匹配 名字以 "A" 或 "L" 或 "N" 开头的人：
SELECT * FROM user WHERE name LIKE '[ALN]%'

通配符

% 代表零个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist] 不在字符列中的任何单一字符