【数据库】MySQL的一些基础知识

ALTER TABLE 表名 DROP 属性名

删除数据表

DROP TABLE 数据库名.表名;

用户管理

创建用户

CREATE USER 'username'@'host' IDENTIFIED By 'password';

username：欲创建用户的用户名

password：用户密码

host：主机地址，例如 localhost、192.168.1.1

用户删除

DROP USER 'username'@'host';

用户权限的授予与回收

授予用户权限

GRANT privilegs ON dbname.tablename TO 'username'@'host';

privilegs：表示要授予的用户权限，例如 SELECT、INSERT

回收用户权限

GRANT privilleges ON dbname.tablename FROM 'username'@'host';

设置与更改用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

SQL 基本语法

简介

SQL：Structure Query Language 结构化查询语言

这是 关系型 数据库的通用语言

SQL 语句主要划分为以下三种类型

• DDL（Data Definition Languages）语句。

• 数据定义语句，定义了不同数据库、表、列、索引等数据库记录
• 常用的语句关键字包括 CREATE、DROP、ALTER 等

• DML（Data Manipulation languages）语句

• 数据操作语句，用于添加、删除、更新和查询数据库记录
• 常用的语句关键字包括 INSERT、DELETE、UPDATE、SELECT 等

• DCL （Data Control Languages）语句

• 数据库控制语句，定义了不同数据库、表、字段、用户的访问权限和安全级别
• 常用的关键语句包括 GRANT、REVOKE 等

基本数据类型

数值类型

字符串类型

1. CHAR 与 VARCHAR

CHAR 与 VARCHAR 用于声明常规字符

CHAR：定义固定长度字符串，取值范围 0~255

VARCHAR：定义可变长度字符串，取值范围 0~65535

2.BINARY 与 VARBINARY

BINARY 与 VARBINARY：存储二进制字符串

特点：无字符集、会排序和比较基于列值字节的数值

注意：保存 BINAYRY 值时，右边未使用部分会填充 0x00（零字节）以达到指定长度，而非空格

3.TEXT 与 BLOB

TEXT 与 BLOB：以对象类型保存的文本与二进制

TEXY：存储字符串

BOLB：存储二进制字符串

注意：

1.当保存或检索 BLOB 和 TEXT 时不删除尾部空格

2.当比较时，会用空格对 TEXT 扩充以适应比较的对象

3.对于 BLOB 和 TEXT 的索引：必须指定索引前缀的长度。而 CHAR 和 VARCHAR 的索引前缀长度是可选的。

4.BLOB 和 TEXT 不能有默认值

4.ENUM

ENUM:枚举类型

取值范围：在创建表时通过枚举方式显示指定。

特点：忽略大小写；只允许值从集合中选取单个值，不能一次选取多个值

• 对于1~255个成员的枚举需要1个字节存储
• 对于255~65535个成员的枚举需要2个字节存储
• 最多允许有65535个成员

5.SET

SET：集合对象。可以包含0~64个成员，其所占存储空间大小是因集合成员数量的不同而有所不同。

SET 和 ENUM的区别：SET可以一次选择多个成员，而ENUM只能选择一个

6.日期时间类型

不管使用哪种类型的时间，系统都会关注时区问题。具体使用根据具体情境具体分析。

数据插入

方法1

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

VALUES后面的值的排列顺序和数量要和该表中存储的列名和顺序排列一致

方法2

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

VALUES后面的值的排列要和INTO子句后面的列名保持一致

一次插入多条：

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

注意逗号、最后一句没有逗号、分号

数据修改

UPDATE 表名 SET 列名 = 新值 WHERE 列名 = 某值;

• 同时修改多个表中的数据

UPDATE 表1,表2,······
SET
表1.列1 = 值,
表2.列1 = 值
 ···
WHERE 条件;

数据删除

DELETE FROM 表名 WHERE 列名 = 值;

• 一次删除多个表的数据

DELETE FROM 表1,表2,···
FROM 表1,表2,···
WHERE 条件;

数据查询

1.常规查询

SELECT 字段 FROM 表名 WHERE 条件;

2.条件查询

SELECT 字段 FROM 表名 WHERE 条件;

WHERE 子句使用注意事项：

• WHERE 子句可以指定任何条件
  
• WHERE子句的条件可以是一个或者多个，连接词可以使用AND或者OR
  
• WHERE 子句的条件类似与程序语言的if条件，可以使用比较运算符，如下表

3.联合查询

SELECT 条件 FROM 表1
UNION
SELECT 条件 FROM 表2;

UNION 和 UNION ALL的主要区别：

UNION ALL 用于将结果直接合并在一起，

而UNION用于将UNION ALL后的结果进行一次DISTINCT，以删除重复结果记录。

4.不重复查询

SELECT DISTINCT 字段 FROM 表名;

5.模糊查询

SELECT 字段 FROM 表名 WHERE 字段 LIKE 值

这里的值是模糊的，因此需要用到通配符，通配符有两种：

• %匹配0个或者多个字符，可以匹配任意类型和长度的字符，对长度没有限制
• _匹配任意单个字符，常用来限制表达式的字符长度

6.排序查询

SELECT 字段 FROM 表名 WHERE 条件 ORDER BY 
FIELD1 DESC/ASC,
FIELD2 DESC/ASC,
 ······
;

排序方式有两种

• DESC降序排列
• ASC升序排列

ORDER BY后面可以跟多个不同的排序字段

7.限制查询

返回前几条或者中间某几条数据

SELECT 字段 LIMIT 起始偏移量,行数;

其实偏移量默认为0，可以不填写，只写行数就可以了。

由于查询结果集顺序的不确定性，通常限制查询和排序查询一起使用

8.聚合

对数据进行汇总操作

SELECT  字段 OP_NAME FROM 表名 WHERE 条件 GROUP BY 
FILED1,
FILED2,
 ······
WITH ROLLUP HAVING 条件;

OP_NAME：表示要进行的集合操作，也就是聚合函数，常用聚合函数如下：

• 求和：SUM
• 记录数：COUNT
• 最大值：MAX
• 最小值：MIN

GROUP BY表示要进行分类聚合的字段

WITH ROLLUP 表示对是否分类聚合后的结果进行再汇总，这个关键字是可选的

HAVING 表示对分类后的结果进行条件过滤

9.连接

（1）内连接（INSERT JOIN）

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_column1 = table2.common_column2;

able1.common_column1 = table2.common_column2 是连接条件

只有满足此条件的记录才会合并为一行。

以上 SQL 语句将产生 table1 和 table2 的交集，只有 table1 和 table2 中匹配的行才被返回。

（2）左连接（LEFT JOIN）

LEFT JOIN：

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_column1 = table2.common_column2;

able1.common_column1 = table2.common_column2 是两个表的连接条件。

以上 SQL 语句将产生 table1 的全集，而 table2 中匹配的则有值，不能匹配的则以 NULL 值取代。

（3）右连接（RIGHT JOIN）

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_column1 = table2.common_column2;

table1.common_column1 = table2.common_column2 是两个表的连接条件。

以上 SQL 语句将产生 table2 的全集，而 table1 中匹配的则有值，不能匹配的则以 NULL 值取代。

（4）全连接（FULL JOIN）

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_column1 = table2.common_column2;

table1.common_column1 = table2.common_column2 是两个表的连接条件。

以上 SQL 语句将产生 table1 和 table2 的并集

（5）自连接

SELECT a.column1, b.column1...
FROM table1 AS a, table1 AS b
WHERE a.common_column < b.common_column;

使用 WHERE 子句来达到自连接的目的。

（6）交叉连接

也叫笛卡尔连接。笛卡尔连接有两种语法，可以使用 CROSS JOIN 关键字，也可以使用不带 WHERE 子句的 SELECT FROM 命令

#第一种写法（推荐）
SELECT table1.column1, table2.column2...
FROM table1 CROSS JOIN table2
#第二种写法
SELECT table1.column1, table2.column2...
FROM  table1, table2

SQL事务

事务四大特性

1.原子性（Atomicity）

事务包含的操作，要么全部成功，要么全部失败

2.一致性（Consistency）

事务必须使数据库从一个一致性状态变成另外一个一致性状态。

（一个事务在执行之前和执行之后都必须处于一致性状态）

举例：

• 商家有5件商品，买家有100元，进行交易后，买家有三件商品，商家有两件商品和100元。
• 交易前：两人一共有100元，5件商品
• 交易后：两人一共有100元，5件商品

3.隔离性（lsolation）

多用户并发访问数据库时，数据库为每一个用户开启的事务不能被其他事务的操作所干扰，多个并发事务之间相互隔离。

隔离效果：对于任意两个并发事务，总有一个要先执行，一个后执行，不能同时执行。

4.持久性（Durability）

一个事务一旦被提交了，那么其对数据库中数据的改变是永久性的，即使在数据库遇到故障的情况下也不会丢失提交事务的操作

事务提交

查看数据库是否开启自动提交功能

show variables like 'autocommit';

如果开启了自动提交，则法案会结果为ON

关闭自动提交：set autocommit=0;

手动进行事务提交：commit;

事务回滚

与事务提交结合使用。

当事务中多条SQL语句在执行过程中，由于系统故障等原因，导致部分SQL语句不能执行成功时，事务中已执行的语句结果应该回退到未执行的状态，这个操作成为事务回滚。

命令：rollback;