MySQL命令及用法（精华版）

目录

DDL（数据定义语言）

数据库操作 

表操作

DML（数据操作语言）

DQL（数据查询语言）

基本查询

条件查询

聚合函数  

分组查询

排序查询

分页查询

DCL（数据控制语言）

用户管理

权限控制

函数

字符串函数

数值函数

日期函数

流程函数

约束

多表查询

内连接

外连接

自连接

联合查询

 子查询

标量子查询

列子查询

行子查询

表子查询

事务

---

DDL（数据定义语言）

用来定义数据库、表、字段。

数据库操作 

查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();
创建数据库
CREATE DATABASE [ IF NOT EXISTS ] 数据库名〔DEFAULT CHARSET,字符集][ COLLATE 排序规则];
删除数据库
DROP DATABASE [ IF EXISTS]数据库名;
使用数据库
USE 数据库名;
注意：[]里的内容是可写可忽略的命令

表操作

查询当前数据库所有表
SHOW TABLES;
查询表结构
DESC 表名;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
添加字段
ALTER TABLE 表名 ADD 字段名类型(长度) [COMMENT注释] [约束];
修改数据类型
ALTER TABLE 表名 MODIFY 字段名新数据类型(长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名新字段名类型(长度) [COMMENT注释] [约束];
删除字段
ALTER TABLE 表名 DROP 字段名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
删除表
DROP TABLE [ IF EXISTS] 表名;
删除指定表，并重新创建该表
TRUNCATE TABLE 表名;创建表
CREATE TABLE 表名(字段1 类型 [COMMENT 注释],字段2 类型 [COMMENT 注释],...字段n 类型 [COMMENT 注释]
)[COMMENT 表注释]

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条件];
删除数据
DELETE FROM 表名 [WHERE条件];

DQL（数据查询语言）

用来查询表的数据。

SELECT字段列表
FROM表名列表
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
ORDER BY排序字段列表
LIMIT分页参数注意：命令的执行顺序为：FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY、LIMIT

基本查询

查询多个字段
SELECT 字段1,字段2,字段3... FROM 表名;
SELECT * FROM 表名;
设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2]...FROM 表名;
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

条件查询

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

条件的比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN...AND...	在...到...之间（包括这两个值）
IN(值1,值2...)	在这个括号内的某个值，多选一
占位符LIKE 或 LIKE占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是空
AND 或 &&	并且
OR 或 ||	或者
NOT 或 !	非

聚合函数  

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

聚合函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

分组查询

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

注意：

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

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

例如：用性别分组，再用count聚合函数来统计不同性别的人数，或直接查询性别来查看有哪些不同的性别。若分组后不用聚合函数或分组字段，则分组后的数据无任何意义。

排序查询

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

注意：
排序方式：asc为升序（默认）、desc为降序。
优先会按照第一个排序方式，当第一个排序字段相同时，再用第二个排序方式，以此类推。

分页查询

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

起始索引：前面要忽略的几条数据。

查询记录数：当前要查询出来几条数据。

例如：3,4。则是忽略前面的3条数据，查询后面的4条数据。也就是查询出第4、5、6、7数据。 

若直接从第一个数据开始查询，则起始索引为0，也可以忽略，直接写第二个索引。

DCL（数据控制语言）

用户管理

用来创建数据库用户和访问权限。

查询用户USE mysql;
SELECT * FROM user;
创建用户
CREATE USER ‘用户名'@'主机名’ IDENTIFIED BY ‘密码';
修改用户密码
ALTER USER ‘用户名'@'主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码';
删除用户
DROP USER ‘用户名'@'主机名';

主机名可以用%来通配 

权限控制

查询权限
SHOW GRANTS FOR ‘用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名'@'主机名;

多个权限可以用逗号分隔。

数据库名和表名可以用*进行通配，代表所有。 

权限	说明
ALL、ALL PRIVILEGES	所有权限
SELECT	查询权限
INSERT	插入权限
UPDATE	修改权限
DELETE	删除权限
ALTER	修改表
DROP	删除数据库、表、视图
CREATE	创建数据库、表

函数

该函数不是只用在查询的聚合函数，它可以用在各种语句中，这里举例两个SQL。
查询
SELECT 函数(参数);
修改
UPDATE 表名 SET 字段=函数(参数);

字符串函数

字符串函数	功能
CONCAT(S1,S2...Sn)	字符串拼接，将这几个字符串拼接起来
LOWER(str)	将字符串全转为小写
UPPER(str)	将字符串全转为大写
LPAD(str,n,pad)	左填充，用pad字符串对str填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用pad字符串对str填充，达到n个字符串长度
TRIM(str）	去掉字符串的头部和尾部的空格
SUBSTRING(str,start,len)	返回字符串str的start开始的len个长度的字符串

数值函数

数值函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的余数
RAND()	返回0~1的随机数
ROUND(x,y)	求x的四舍五入的值，保留y位小数

日期函数

日期函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔expr后的时间值 例如：SELECT date_add(now(), INTERVAL 70 year) 返回的就是当前日期后的70年
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

流程函数

流程函数	功能
lF(value , t , f)	如果value为true，则返回t，否则返回f
IFNULL(value1 , value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [res1] ...ELSE [ default ] END	如果val1为true，返回res1,...否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] ...ELSE [ default ] END	如果expr的值等于vall，返回res1,..否则返回default默认值

约束

约束是作用在表上的字段中的，在创建表或修改表时添加、修改、删除约束。 

在创建表时可以添加约束
CREATE TABLE 表名(id int primary key auto_increment comment '主键',name varchar(10) not null unique comment '姓名',age int check(age > 0 && age < 120) comment '年龄',status char(1) default '1' comment '状态',gender char(1) comment '性别'
) comment '用户表';

外键语法：
CREATE TABLE 表名(id INT PRIMARY KEY,  -- 自己的主键 idTid INT,             -- 这是外键列-- 其他列定义CONSTRAINT fk_dep_t FOREIGN KEY (Tid) REFERENCES t(id) --创建一个外键，是Tid对应t表的id。该外键的名称为fk_dep_t
);

新增表的外键语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称（自己取名） FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

约束	功能
非空约束 NOT NULL	限制该字段的数据不能为null
唯一约束 UNIQUE	保证该字段的所有数据都是唯一、不重复的
主键约束 PRIMARY KEY	主键是一行数据的唯一标识，要求非空且唯一
默认约束 DEFAULT	保存数据时，如果未指定该字段的值，则采用默认值
检查约束(8.0.16版本之后) CHECK	保证字段值满足某一个条件
外键约束 FOREIGN KEY	用来让两张表的数据之间建立连接，保证数据的一致性和完整性

多表查询

多表查询分类
>连接查询
内连接:相当于查询A、B交集部分数据
外连接:
        左外连接:查询左表所有数据，以及两张表交集部分数据
        右外连接:查询右表所有数据，以及两张表交集部分数据
自连接:当前表与自身的连接查询，自连接必须使用表别名
>子查询

内连接

隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE条件...;
显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

外连接

左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON条件...;
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON条件...;

自连接

用处：当一个表内要根据这个表查询这个表的其他行。

例如：当一个职工表内，有一个字段为head_id是他的领导id，要查询所有员工和他的领导，就要用自连接先查询员工的head_id，再查询head_id对应的id。

SELECT a.name, b.name from dep a, dep b where a.head_id=b.id;

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件..;

联合查询

把两次的查询结果通过上下拼接起来。

注意：两次查询的列数要相同，不然拼接会报错。

select 字段列表 from 表A ...
UNION [ALL] --ALL可以省略，省略就是去重
select 字段列表 from 表B ...;

 子查询

子查询又称为嵌套查询，把一个查询的结果作为第二个查询的条件。

子查询最外部的语句可以是INSERT/UPDATE/ DELETE / SELECT的任何一个。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

根据子查询结果不同，分为:
标量子查询（子查询结果为单个值)

列子查询(子查询结果为一列)
行子查询(子查询结果为一行)

表子查询(子查询结果为多行多列) 

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
常用的操作符:=、 >、>=、<、<=

例如：查询出一个id，再用这个id去查询其他内容
select * from emp where dept_id = (select id from dept where name = ‘销售部');

列子查询

子查询返回的结果是一列（可以是多行)，这种子查询称为列子查询。
常用的操作符:IN 、NOT IN 、ANY 、SOME 、ALL

列子查询操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

例如：查询销售部和市场部的id，在通过和一列多行的内容去查询其他数据
select * from emp where dept_id in (select id from dept where name = ‘销售部’ or name = '市场部');

行子查询

子查询返回的结果是一行（可以是多列)，这种子查询称为行子查询。
常用的操作符:= 、<> 、IN 、NOT IN

例如：查询名字的小明的一行多列的数据，再根据这个数据去查询其他数据
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '小明');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符:IN

例如：查询多行多列数据，再根据这个数据去查询其他数据
select * from emp where (job , salary) in ( select job，salary from emp where name = ‘小明’or name =‘小强’);

事务

查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit=0;
开启事务
START TRANSACTION 或 BEGIN ;
提交事务
COMMIT;
回滚事务
ROLLBACK;