MySql常用命令

前言

2022年10月31号
本文是关于我在MySQL学习中学到的简单SQL通用语法。
按照黑马程序员的课程记下的常用命令。
本人目前是计算机学习的小白一枚，在此和各位一起讨论计算机学习的相关知识，感谢大家点赞评论，感兴趣的可以私聊关注一起学习。

SQL通用语法

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

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

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

• 注释: 单行注释: – 注释内容 或 # 注释内容(MySQL特有)

• 多行注释: /* 注释内容 */

SQL分类

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

  1.DDL

DDL语句对数据库进行操作

• 查询
  查询数据库

SHOW DATABASES;

查询带上钱数据库

SELECT DATABASE();

1. 创建

CREATE DATABASE [IF NOT EXISTS] 数据库名[DEFAULT CHARSET 字符集] [COLLATE排序规则];
  （字符集：utf8、汉字支持三个字节，utf8mb4支持四个字节）

2. 删除

DROP DATABASE [ IF EXISTS] 数据库名;

3. 使用

USE 数据库名;

4. 查询当前数据库所有表

SHOW TABLES;

5. 查询表结构

DESC 表名;

6. 查询指定表的建表语句

SHOW CREATE TABLE 表名;

7. 创建

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 表名 MODIFY 字段名 新数据类型(长度);

• 修改字段名和字段类型

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

• 删除字段

ALTER TABLE 表名 DROP 字段名;

• 修改表名

ALTER TABLE 表名 RENAME TO 新表名;

• 删除表

DROP TABLE [IF EXISTS] 表名;

• 删除指定表，并重新创建该表(删除表的数据保留表的结构)

TRUNCATE TABLE 表名:

  2.DML

DML语句对数据库表中的数据进行增删改

• 给指定字段添加数据

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

• 给全部字段添加数据

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

• 批量添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...)VALUES (值1, 值2, ...), (值1, 值2, ...);
或者是
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

• 对表修改数据

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

对表删除数据

DELETE FROM 表名 [WHERE条件]；

  3.DQL

DQL语句基本查询

• 查询多个字段

SELECT 字段1，字段2, 字段3 ... FROM 表名;

• 查询所有字段

SELECT * FROM 表名;

• 设置别名

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

• 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

• 条件查询

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

• 聚合函数

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

• 分组查询

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

• 排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
 注意： 排序方式  ASC(升序（默认值）)，DESC(降序)

• 分页查询

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

  4.DCL

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 '用户名'@'主机名';

常用函数

函数	作用
CONCAT	将字符串接在一起
REPLACE	将某个字符（串）替代另一个指定字符（串）
UPPER	将字符串里的小写字母转换成大写字母
LOWER	将字符串里的大写字母转换成小写字母
SUBSTR	获取指定位置的字符串
LEFT	返回从最左侧的指定长度的字符串
RIGHT	返回从最右侧的指定长度的字符串
INSTR	查找指定字符串，并返回其位置
LENGTH	获取字符串、数字、日期等字节长度
IFNULL	检查NULL值，并使用新值替代（仅MySQL支持）

CONCAT

-- 基本语法
SELECT CONCAT(STR1,['字符',] STR2)
FROM TABLE_NAME;
/* CONCAT后面可以用字段名串接字符，字符加单引号，字符与字段名之间逗号隔开。*/

-- 操作范例
SELECT CONCAT(LAST_NAME,' ', FIRST_NAME) AS NAME
FROM EMPLOYEE_TBL;
-- 将字段'LAST_NAME',空格' ','FIRST_NAME'串接起来，返回到NAME字段。

REPLACE

• REPLACE函数用于一个字符串把另一个字符串整个替换掉

SELECT REPLACE(CLOUMN,'VALUE1','VALUE2')
FROM TABLE_NAME;
/*这里CLOUNMN为字段名，VALUE1为字段里需要替换的字符串，VALUE2为替换的字符串*/

SELECT CITY,REPLACE(CITY,'IND','A')
FROM EMPLOYEE_TBL;
-- 将字段'CITY'中所有数据的'IND'替换成'A'

UPPER 和 LOWER

SELECT UPPER(CITY)
FROM EMPLOYEE_TBL;

SELECT LOWER(CITY)
FROM EMPLOYEE_TBL;
-- 将字段'CITY'中的所有字符转换为大写（小写）。

SUBSTR、LEFT 和 RIGHT

• SUBSTR函数用于从指定位置获取指定长度的字符串

SELECT CLOUMN_NAME,SUBSTRING(CLONMU_NAME,STRING POSITION,LENGTH)
FROM TABLE_NAME;
/*STARING POSITION指定是字符串开始截取的位置
  LENGTH 指的是从指定位置截取字符的数量 */

SELECT EMP_ID ,SUBSTRING(EMP_ID,2,3) AS ID
FROM EMPLOYEE_TBL;
-- 返回'EMP_ID'字段的第2到第4个字符

• LEFT和RIGHT函数用于返回从最左侧（右侧）的指定长度的字符串

SELECT LEFT(COLUMN_NAME,LENGTH) -- LENGTH指的返回字符串的长度
FROM TABLE_NAME;

SELECT LEFT(EMP_ID,3),RIGHT(CITY,4)
FROM EMPLOYEE_TBL;
--返回字段'EMP_ID'左侧的3个字符，'CITY'字段右侧的4个字符

INSTR

• INSTR函数用于寻找指定的字符，并返回其位置

SELECT INSTR(COLUMN_NAME,'STRING'）--STRING待查询的字符
FROM TABE_NAME;

SELECT PROD_DESC,INSTR(PROD_DESC,'A')
FROM PRODUCTS_TBL;
--返回字段'PROD_DESC'中A首次出现的位置

LENGTH

• LENGTH函数用于获取字符串、数字、日期等字节长度

SELECT LENGTH(COLUMN_NAME)
FROM TABLE_NAME;

SELECT PROD_DESC,LENGTH(PROD_DESC)
FROM PRODUCTS_TBL;
--返回字段'PROD_DESC'的长度

IFNUL

• IFNULL函数用于查找NULL值，并用另一个值替代（数据类型必须相同）
• 仅MySQL支持

SELECT PAGER,IFNULL(PAGER,1790)
FROM EMPLOYEE_TBL;
--将字段'PAGER'中的NULL值替换为1790。

数学函数

函数	作用
ABS	返回绝对值
ROUND	数据的四舍五入，可以指定保留小数长度
SQRT	平方根
POWER	求幂
EXP	求指数
SIN、COS、TAN	求三角函数
CEILING、FLOOR	上下取整

• 算术函数比较简单，举几个例子就能看懂

SELECT ABS(-20); --返回-20的绝对值。

SELECT SQRT(49); -- 返回49的平方根，若括号内为负数，则返回NULL。

SELECT CEILING(-3.35), CEILING(3.35); --对-3.35和 3.35想上取整。

SELECT  ROUND(-1.14), ROUND(-1.9), ROUND(1.14), ROUND(1.9);
--对数值进行四舍五入。

日期函数

函数	作用
NOW（）	获取当前系统时间
DATE_ADD（）	增加时间间隔
DATE_SUB（）	减少时间间隔
DATE_FORMAT	将日期转换成字符串
STR_TO_DATE（）	字符串转换成日期

• NOW（）返回系统时间

SELECT NOW(); --返回当前系统时间（日期+时间）

• DATE_ADD（）增加时间间隔

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 在当前系统日期基础上加上1天

• 参考下面表格，可以进行增加更加复杂的时间间隔，间隔可以为负数

设置时间间隔例子	含义说明
INTERVAL ‘3’ DAY（HOUR、MINUTE、SECOND）	增加3天（小时、分钟、秒）
INTERVAL ‘4 5’ DAY_HOUR	时间间隔为4天5小时
INTERVAL ‘5 6:7’ DAY_MINUTE	时间间隔为5天6小时7分钟
INTERVAL ‘5 6:7:8’ DAY_SECOND	时间间隔为5天6小时7分钟8秒

假如说这里指定间隔到了单位秒，必须在INTERVAL子句中包含’_SECOND’。
同理，指定间隔到了单位小时，必须在INTERVAL子句中包含’_HOUR’

SELECT DATE_ADD(NOW(), INTERVAL'1 01:15:30' DAY_SECOND);
--时间间隔增加了1天1小时15分30秒。

• DATE_SUB（）减少时间间隔，和DATE_ADD（）用法一致，这里不一一赘述了
• DATE_FORMAT 用于将日期转换成字符串

SELECT STR_TO_DATE('08/12/2020', '%m/%d/%Y'); --返回字符串2020-08-12
SELECT STR_TO_DATE('08/12/2020' , '%m/%d/%Y'); --返回字符串2020-08-12
SELECT STR_TO_DATE('08.12.2020', '%m.%d.%Y'); --返回字符串2020-08-12
SELECT STR_TO_DATE('17:21:35', '%H:%i:%s'); --返回字符串17:21:35
SELECT STR_TO_DATE('08.12.2020 17:22:35', '%m.%d.%Y %H:%i:%s');  
--返回字符串2020-08-12 17:22:35
%Y:年 , %m:月 , %d:日 , %H:小时 , %i:分钟 , %s:秒

约束

主键约束(PRIMARY KEY) 简写PK

• 添加单列主键

create table 表名(
   ...
   <字段名> <数据类型> primary key 
   ...
) ;

• 添加多列主键（联合主键）

create table 表名(
   ...
   primary key （字段1，字段2，…,字段n)
);
--当主键是由多个字段组成时，不能直接在字段名后面声明主键约束

• 通过修改表结构台南佳主键

create table 表名(
   ...
);
alter table <表名> add primary key（字段列表);

• 删除主键约束

alter table <数据表名> drop primary key;

自增约束(AOTU_INCREMENT)

MySql特有

• 添加自增约束

字段名 数据类型 auto_increment
----操作
create table t_user1( 
  id int primary key auto_increment, 
  name varchar(20) 
);
--通过给字段添加 auto_increment 属性来实现主键自增长
--默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1

• 指定自增字段初始值

---id 值就会从 5 开始往上增加
create table t_user2 ( 
  id int primary key auto_increment, 
  name varchar(20)
)auto_increment=5;

• 删除自增约束

alter table 表名  modify <字段名> <字段类型>

非空约束(NOT NU)

• 添加非空约束

alter table 表名 modify <字段名> <类型> not null; 

• 删除非空约束

alter table 表名 modify <字段> <类型>;

• 唯一性约束(UNIQUE)

方式1：<字段名> <数据类型> unique ;
方式2：alter table 表名 add constraint 约束名(自定义) unique(字段名);

• 删除唯一约束

alter table <表名> drop index <唯一约束名>;

默认约束(DEFAULT)

方式1： <字段名> <数据类型> default (默认值);
方式2: alter table 表名 modify 列名 类型 default 默认值;

• 删除默认约束

alter table <表名> modify column <字段名> <类型> default null; 

外键约束(FOREIGN KEY) 简写FK

• 设置外键约束

 CREATE TABLE tb_emp6 (
    id INT(11) PRIMARY KEY,
    deptId INT(11),
    CONSTRAINT fk_emp_dept1
    FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    
 --创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id
 
 CREATE TABLE tb_emp6 (
    id INT(11) PRIMARY KEY,
    deptId INT(11),
    CONSTRAINT fk_emp_dept1
    FOREIGN KEY(deptId) REFERENCES tb_dept1(id)

• 修改表时添加约束

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

• 删除外键

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