MySQL | 基础语法介绍_vlambda技术博客

vlambda
2022-04-15

MySQL | 基础语法介绍

点击箭头处“蓝色字”，关注我哦！！

一、SQL基础

数据定义语言（DDL）：对数据库、表、字段的操作

数据操作语言（DML）：对数据的操作

数据查询语言（DQL）：数据查询

DDL

1、对数据库的操作

(1)show databases;显示所有数据库
(2)show database();查询当前数据库
(3)create database [if not exists] 数据库名 [default charset 字符集][collate 排序规则];创建数据库，utf8存储一个字符占3个字节，utf8mb4存储一个字符占4个字节
(4)drop database [if exists] 数据库名;删除数据库
(5)use 数据库名;进入数据库

2、对表和字段的操作

(1)show tables;查询当前数据库所有表
(2)desc 表名;查询表结构
(3)show create table 表名;查询指定表的建表语句
(4)CREATE TABLE table_name (
   字段1 字段1类型 [comment 字段1注释],
   字段2 字段2类型 [comment 字段2注释],
   ....
   字段n 字段n类型 [comment 字段n注释]
   )[comment 表注释];建表语句，最后一个字段后面没有逗号
(5)CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....; 使用另一个表创建
(6)ALTER TABLE 表名 ADD 字段名 类型 [comment 注释] [约束]; 添加字段
(7)ALTER TABLE 表名 MODIFY 字段名 新数据类型; 修改字段类型
(8)ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型; 修改字段名和字段类型
(9)ALTER TABLE 表名 DROP COLUMN 字段名; 删除字段
(10)ALTER TABLE 表名 RENAME TO 新表名; 修改表名
(11)DROP table [if exists] 表名;删除表
(12)truncate table 表名;删除指定表并重新创建该表

3、数据库备份

(1)BACKUP DATABASE 数据库名 TO DISK = 'filepath';
(2)BACKUP DATABASE 数据库名 TO DISK = 'filepath' WITH DIFFERENTIAL; 差异备份仅备份自上次完全数据库备份以来已更改的数据库部分

DML

1、添加数据

(1)INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); 指定列名和要插入的值
(2)INSERT INTO table_name VALUES (value1, value2, value3, ...); 所有列添加值
(3)INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...),(value1, value2, value3, ...),(value1, value2, value3, ...)...;批量添加
注：插入的时候，字符串和日期类型应该放在引号中

2、修改数据

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

3、删除数据

DELETE FROM table_name WHERE condition;

DQL

1、基本查询

(1)SELECT column1 [as 别名1], column2 [as 别名2], ... FROM table_name; as可省略
(2)SELECT DISTINCT column1, column2, ... FROM table_name; 去除重复记录

2、条件查询

(1)SELECT column1, column2, ... FROM table_name WHERE condition;
(2)SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...; 如果第一个条件与第二个条件都为TRUE，则AND运算符显示一条记录
(3)SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...; 如果第一个条件与第二个条件任何一个条件为TRUE，则OR运算符显示一条记录
(4)SELECT column1, column2, ... FROM table_name WHERE NOT condition; 如果条件前加NOT，则将不显示记录
(5)SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; BETWEEN运算符是包含的：包括开始值和结束值
(6)SELECT column_name(s) FROM table_name WHERE column_name like value1;
下划线(_)代表单个字符,百分号(%)代表任意个字符

3、聚合函数：作用于某一列数据

count、max、min、avg、sum:null值不参与聚合函数的运算，count(*)代表表中有多少行

4、分组查询

(1)select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组过滤条件];
注：where和having的区别：where是分组之前的过滤，不满足where条件则不参与分组，having是分组之后对结果进行过滤，having可以使用聚合函数，where不可以

5、排序查询

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

6、分页查询

(1)select 字段列表 from 表名 limit 起始索引，查询记录数; 第一页数据，起始索引可省略

二、函数

字符串函数、数值函数、日期函数、流程函数

1、字符串函数

(1)concat:字符串拼接
(2)lower:将字符串转换为小写
(3)upper:将字符串转换为大写
(4)lpad:左边用另一个字符串填充一个字符串到一定长度
(5)rpad：右边用另一个字符串填充一个字符串到一定长度
(6)trim：去除字符串首尾空格
(7)substring:从字符串中提取子字符串（从任何位置开始）

2、数值函数

(1)ceil:向上取整
(2)floor:向下取整
(3)mod:取余
(4)rand():返回0-1内的随机数，包括0，不包括1
(5)round(x,y):求x的四舍五入值，保留y位小数
(6)format:将数字格式化为“＃，###，###。##”等格式，舍入到指定的小数位数

3、日期函数

(1)curdate();当前日期
(2)curtime();当前时间
(3)now();当前日期+时间
(4)year(date);指定date的年份
(5)month(date);指定date的月份
(6)day(date);指定date的日
(7)date_add(date,interval expr type);一个时间上加上指定的时间
(8)datediff(date1,date2);date1和date2相差多少天

4、流程函数

(1)if(value,t,f);如果value为true,则返回t,否则返回f
(2)ifNull(value1,value2);如果value1不为空，返回value1，否则返回value2
(3)case when [val1] then [res1] ... else [default] end;如果val1为true,返回res1,否则返回default默认值
(4)case [expr] when [val1] then [res1] ... else [default] end;如果expr的值等于val1，返回res1,否则返回default默认值

三、约束

SQL约束

(1)NOT NULL:约束强制列不接受NULL值
(2)UNIQUE:约束确保列中的所有值都不同
(3)PRIMARY KEY:约束唯一标识表中的每条记录,主键必须包含UNIQUE值，并且不能包含NULL值,一个表只能有一个主键
(4)FOREIGN KEY 是用于将两个表链接在一起的键
(5)CHECK:约束用于限制列中的值范围
(6)DEFAULT:约束用于为列提供默认值
(7)CREATE INDEX index_name ON table_name (column1, column2, ...);用于创建索引
(8)ALTER TABLE table_name DROP INDEX index_name;删除表中的索引
(9)AUTO INCREMENT:在插入表新记录中时自动生成唯一编号

四、多表查询

多表查询

(1)INNER JOIN:选择两个表中具有匹配值的记录
(2)SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; LEFT JOIN关键字返回左表（table1）中的所有记录，以及右表（table2）中的匹配记录。如果没有匹配，则结果从右侧为NULL
(3)RIGHT JOIN:同上相反
(4)SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2; UNION运算符用于组合两个或多个SELECT语句的结果集,每个SELECT语句必须具有相同的列数及顺序，列还必须具有类似的数据类型，union查询结果会去重，union all不会

五、存储过程

存储过程

(1)CREATE PROCEDURE procedure_name [@参数1 参数类型，@参数2 参数类型]
AS
 sql_statement
GO; 
执行存储过程
EXEC procedure_name [参数1值，参数2值];

六、存储引擎

存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，不是基于数据库，存储引擎可被称为表类型，默认InnoDB。

show engines;查看都有哪些存储引擎
存储引擎特点
1、InnoDB:支持事务；行级锁；支持外键foreign key约束，保证数据的完整性和正确性
2、MyISAM:不支持事务、不支持外键；支持表锁；不支持行锁；访问速度快（实际中被MongoDB取代）
3、Memory:存储在内存中，受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。默认Hash索引（实际中被Redis取代）

七、索引

帮助mysql高效获取数据的数据结构（有序）。无索引的时候需要进行全表扫描

索引结构：

Mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构。常用的Btree索引、B+tree索引和Hash索引。详解：https://blog.csdn.net/weixin_46451496/article/details/105136122

索引分类:

聚集索引、二级索引。聚集索引一般为主键id,id直接对应一行数据；二级索引指向id,需要将id通过聚集索引才可以得到一行数据（此操作称之为回表操作）。

索引语法:

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name;创建索引，UNIQUE要求该列没有重复数据
SHOW INDEX FROM table_name;查看索引
DROP INDEX index_name ON table_name;删除索引