MySQL常用语句

语句分类

• DDL(Data Definition Language) 数据定义语言，用来定义数据库对象：数据库，表，列等
• DML(Data Manipulation Language) 数据操作语言，用来对数据库中表的数据进行增删改
• DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录(数据)
• DCL(Data Control Language) 数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

DDL操作数据库

查询所有数据库

SHOW DATABASES;

创建数据库

CREATE DATABASE 数据库名称;

创建数据库(判断，如果不存在则创建)

CREATE DATABASE IF NOT EXISTS 数据库名称;

删除数据库

DROP DATABASE 数据库名称;

删除数据库(判断，如果存在则删除)

DROP DATABASE IF EXISTS 数据库名称;

使用数据库

USE 数据库名称;

DDL操作表

创建表

• 语法

CREATE TABLE 表名 (字段名 数据类型, 字段名 数据类型);

• 示例

CREATE TABLE 表名 (
    字段名 数据类型,
    字段名 数据类型,
    ...
    字段名 数据类型
);

查看所有表

SHOW TABLES;

查询表结构

DESC 表名;

删除表

DROP TABLE 表名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

单独添加一个字段

ALTER TABLE 表名 ADD 字段名 数据类型;

修改某字段的数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型;

修改字段名和数据类型

ALTER TABLE 表名 CHANGE 字段名 新字段名 新数据类型;

删除某一字段

ALTER TABLE 表名 DROP 字段名;

DML表数据的增删查改

给表新增数据

给指定列添加数据

• 语法

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

• 示例

INSERT INTO goods (NAME, price, sales_volume, produced_date) VALUES ('华为P40', 5999, 1000, '2020-08-20');

给全部列添加数据

• 语法

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

• 示例

INSERT INTO goods VALUES ('小米11', 4999, 5000, '2020-12-28');

给表批量添加数据

• 注意
  • 字段名和值的数量要对应
  • 值的类型和字段的类型要对应
  • 除了数值类型，其他数据类型的数据都需要加引号（单引双引都行，推荐单引）

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

修改表中数据

• 注意：修改语句中必须加条件，如果不加条件，则会将所有数据都修改

UPDATE 表名 SET 字段名=新的值 [WHERE 条件];

删除表中的数据

• 注意：删除语句中必须加条件，如果不加条件，则会将所有数据都删除

DELETE FROM 表名 [WHERE 条件];

DQL查询表中数据

查询指定字段的数据

SELECT 字段名1, 字段名2 FROM 表名;

查询所有字段的数据

SELECT * FROM 表名;

去除重复行

SELECT DISTINCT 字段名1 FROM 表名;

计算列的值(四则运算)

SELECT 字段名1 (+ - * /) 字段名2 FROM 表名;

起别名查询

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

条件查询

SELECT 字段名 FROM 表名 WHERE 条件;

• 比较运算符

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<>或!=	不等于

• 逻辑运算符

AND或&&	并且
OR或||	或者
NOT或!	非、不是

• 范围

BETWEEN...AND...	在某个范围之内（都包含）
IN(...)	多选一

• NULL的处理

IS NULL	是NULL
IS NOT NULL	不是NULL

模糊查询

• MySQL的通配符
  • %: 表示任意多个字符
  • _: 表示一个字符

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

查询数据排序

• 排序方式
  • ASC: 升序
  • DESC: 降序

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

聚合函数

• 注意：

  • 记录为NULL的不统计
  • 如果不是数值类型，那么计算结果为0

• COUNT： 统计指定列记录数

• SUM： 计算指定列的数值和

• MAX： 计算指定列的最大值

• MIN： 计算指定列的最小值

• AVG： 计算指定列的平均值

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

分组查询

• 注意：分组之后，查询的字段为分组字段和聚合函数，查询其他字段无任何意义

SELECT * FROM 表名 GROUP BY 字段名;

-- 带分组条件
SELECT * FROM 表名 GROUP HAVING 分组过滤条件;

• having与where的区别
  • where是在分组前对数据进行过滤，having是在分组后对数据进行过滤
  • where后面不可以使用聚合函数，having后面可以使用聚合函数

分页查询

• 注意事项

• 分页查询 limit 是MySQL数据库的方言，Oracle 分页查询使用 rownumber，SQL Server分页查询使用 top

• offset是指偏移量，可以认为是跳过的记录数量，不写则默认为0

• length是指需要显示的总记录数

SELECT * FROM 表名 LIMIT offset, length;

表约束

• 约束：对表中的数据进行限定，保证数据的正确性、有效性、完整性

约束	说明
PRIMARY KEY	主键约束
UNIQUE	唯一约束
NOT NULL	非空约束
DEFAULT	默认值约束
FOREIGN KEY	外键约束
CHECK	检查约束

• 注意
  • MySQL不支持检查约束
  • 约束通常是在创建表结构的时候创建

主键约束

• 主键的作用：用来区分表中的数据

• 主键的特点

  • 主键必须是唯一不重复的值
  • 主键不能包含NULL值

-- 建表
CREATE TABLE 表名 (
    字段名 字段类型 PRIMARY KEY,
    字段名 字段类型
);

-- 建表并设置外键约束
CREATE TABLE 表名(
   列名 数据类型,
   [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
); 

删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY;

建表后，单独添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

设置主键自增长

• 主键，如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

• 注意：AUTO_INCREMENT 的字段类型必须是数值类型

唯一约束

• 唯一约束的作用：让字段的值唯一，不能重复

CREATE TABLE 表名 (
    字段名 字段类型 UNIQUE,
    字段名 字段类型
);

非空约束

• 非空约束的作用：让字段的值不能为NULL

CREATE TABLE 表名 (
    字段名 字段类型 NOT NULL,
    字段名 字段类型
);

默认值约束

• 默认值约束的作用：如果这个字段不设置值，就使用默认值

CREATE TABLE 表名 (
    字段名 字段类型 DEFAULT 值,
    字段名 字段类型
);

外键约束

• 外键：一张表中的某个字段引用其他表的主键，这个字段称为外键
• 主表：将数据给别人用的表
• 副表/从表：使用别人数据的表

新建表时，增加外键约束

CREATE TABLE 表名 (
    字段名 字段类型,
    字段名 字段类型,
    -- 添加外键约束
    [CONSTRAINT 外键约束名] FOREIGN KEY(外键字段名) REFERENCES 主表(主键字段名)
);

• 关键字解释
  • CONSTRAINT: 表示约束外键约束名： 给外键约束取个名字,将来通过约束名可以删除这个约束
  • FOREIGN KEY(外键字段名): 指定某个字段作为外键
  • REFERENCES 主表(主键字段名) : 引用主表的主键的值

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

已有表增加外键约束

ALTER TABLE 从表 ADD [CONSTRAINT 外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);