mysql 基础操作

通用语法及分类

• DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段）

• DML: 数据操作语言，用来对数据库表中的数据进行增删改

• DQL: 数据查询语言，用来查询数据库中表的记录

• DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限

DDL（数据定义语言）

数据定义语言

数据库操作

查询所有数据库：SHOW DATABASES;查询当前数据库：SELECT DATABASE();创建数据库：CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];删除数据库：DROP DATABASE [ IF EXISTS ] 数据库名;使用数据库：USE 数据库名;

注意事项

• UTF8 字符集长度为 3 字节，有些符号占 4 字节，所以推荐用 utf8mb4 字符集

表操作

查询当前数据库所有表：SHOW TABLES;查询表结构：DESC 表名;查询指定表的建表语句：SHOW CREATE TABLE 表名;

创建表：

CREATE TABLE 表名(  字段1 字段1类型 [COMMENT 字段1注释],  字段2 字段2类型 [COMMENT 字段2注释],  字段3 字段3类型 [COMMENT 字段3注释],  ...  字段n 字段n类型 [COMMENT 字段n注释])[ COMMENT 表注释 ];

最后一个字段后面没有逗号

添加字段：ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];例：ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];例：将 emp 表的 nickname 字段修改为 username，类型为 varchar(30)ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

删除字段：ALTER TABLE 表名 DROP 字段名;

修改表名：ALTER TABLE 表名 RENAME TO 新表名

删除表：DROP TABLE [IF EXISTS] 表名;删除表，并重新创建该表：TRUNCATE TABLE 表名;

DML（数据操作语言）

添加数据

指定字段：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 条件 ];例：UPDATE emp SET name = 'Jack' WHERE id = 1;

删除数据：DELETE FROM 表名 [ WHERE 条件 ];

DQL（数据查询语言）

语法：

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

基础查询

查询多个字段：SELECT 字段1, 字段2, 字段3, ... FROM 表名;SELECT * FROM 表名;

设置别名：SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;

去除重复记录：SELECT DISTINCT 字段列表 FROM 表名;

转义：SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'/ 之后的_不作为通配符

条件查询

语法：SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件：

例子：

-- 年龄等于30select * from employee where age = 30;-- 年龄小于30select * from employee where age < 30;-- 小于等于select * from employee where age <= 30;-- 没有身份证select * from employee where idcard is null or idcard = '';-- 有身份证select * from employee where idcard;select * from employee where idcard is not null;-- 不等于select * from employee where age != 30;-- 年龄在20到30之间select * from employee where age between 20 and 30;select * from employee where age >= 20 and age <= 30;-- 下面语句不报错，但查不到任何信息select * from employee where age between 30 and 20;-- 性别为女且年龄小于30select * from employee where age < 30 and gender = '女';-- 年龄等于25或30或35select * from employee where age = 25 or age = 30 or age = 35;select * from employee where age in (25, 30, 35);-- 姓名为两个字select * from employee where name like '__';-- 身份证最后为Xselect * from employee where idcard like '%X';

聚合查询（聚合函数）

常见聚合函数：

语法：SELECT 聚合函数(字段列表) FROM 表名;例：SELECT count(id) from employee where workaddress = "广东省";

分组查询

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

where 和 having 的区别：

• 执行时机不同：where 是分组之前进行过滤，不满足 where 条件不参与分组；having 是分组后对结果进行过滤。

• 判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

例子：

-- 根据性别分组，统计男性和女性数量（只显示分组数量，不显示哪个是男哪个是女）select count(*) from employee group by gender;-- 根据性别分组，统计男性和女性数量select gender, count(*) from employee group by gender;-- 根据性别分组，统计男性和女性的平均年龄select gender, avg(age) from employee group by gender;-- 年龄小于45，并根据工作地址分组select workaddress, count(*) from employee where age < 45 group by workaddress;-- 年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;

注意事项

• 执行顺序：where > 聚合函数 > having

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

排序查询

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

排序方式：

• ASC: 升序（默认）

• DESC: 降序

例子：

-- 根据年龄升序排序SELECT * FROM employee ORDER BY age ASC;SELECT * FROM employee ORDER BY age;-- 两字段排序，根据年龄升序排序，入职时间降序排序SELECT * FROM employee ORDER BY age ASC, entrydate DESC;

注意事项

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

分页查询

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

例子：

-- 查询第一页数据，展示10条SELECT * FROM employee LIMIT 0, 10;-- 查询第二页SELECT * FROM employee LIMIT 10, 10;

注意事项

• 起始索引从 0 开始，起始索引 = （查询页码 - 1） * 每页显示记录数

• 分页查询是数据库的方言，不同数据库有不同实现，MySQL 是 LIMIT

• 如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

DQL 执行顺序

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

DCL

管理用户

查询用户：

USER mysql;SELECT * FROM user;

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

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

删除用户：DROP USER '用户名'@'主机名';

例子：

-- 创建用户test，只能在当前主机localhost访问create user 'test'@'localhost' identified by '123456';-- 创建用户test，能在任意主机访问create user 'test'@'%' identified by '123456';create user 'test' identified by '123456';-- 修改密码alter user 'test'@'localhost' identified with mysql_native_password by '1234';-- 删除用户drop user 'test'@'localhost';

注意事项

• 主机名可以使用 % 通配

权限控制

常用权限：

更多权限请看权限一览表

查询权限：SHOW GRANTS FOR '用户名'@'主机名';

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

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

注意事项

• 多个权限用逗号分隔

• 授权时，数据库名和表名可以用 * 进行通配，代表所有