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 等
基本数据类型
数值类型
| 数据类型 | 存储空间（Bytes） | 表示范围 |
|---|---|---|
| TINY INT | 1 | -128~127 |
| SMALL INT | 2 | -32768~32767 |
| MEDIUM | 3 | -8388608~8388607 |
| INT | 4 | -2147483648~2147483647 |
| BIG INT | 8 | ±9.22x1018 |
| FLOAT(M,D) | 7 | 8 位精度，M 表示数字的总位数，D 表示小数点后面数字位数 |
| DOUBLE | 8 | 16 位精度，M 表示数字的总位数，D 表示小数点后面数字位数 |
字符串类型
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 不能有默认值
| TEXT OR BLOB | 最大字符串长度 | 表示范围 |
|---|---|---|
| TINYTEXT | 255 | 28-1 |
| TEXT OR BLOB | 65535 | 216-1 |
| MEDIUM TEXT OR MEDIUMBLOB | 16777215 | 224-1 |
| LONG TEXT OR LONGBLOB | 4294967295 | 232-1 |
4.ENUM
ENUM:枚举类型
取值范围：在创建表时通过枚举方式显示指定。
特点：忽略大小写；只允许值从集合中选取单个值，不能一次选取多个值
- 对于1~255个成员的枚举需要1个字节存储
- 对于255~65535个成员的枚举需要2个字节存储
- 最多允许有65535个成员
5.SET
SET：集合对象。可以包含0~64个成员，其所占存储空间大小是因集合成员数量的不同而有所不同。
| 集合成员数量 | 所占存储空间（Bytes） |
|---|---|
| 1~8 | 1 |
| 9~16 | 2 |
| 17~24 | 3 |
| 25~32 | 4 |
| 33~64 | 8 |
SET 和 ENUM的区别：SET可以一次选择多个成员，而ENUM只能选择一个
6.日期时间类型
| 类型 | 日期格式 | 范围 | 所占存储空间 （Bytes） |
|---|---|---|---|
| YEAR | YYYY | 1925~2155 | 1 |
| TIME | HH:MM:SS | -838:59:59~838:59:59 | 3 |
| DATE | YYYY-MM-DD | 1000-01-01~999-12-31 | 3 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:01 ~ 9999-12-31 23:59:59 |
8 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC |
4 |
不管使用哪种类型的时间，系统都会关注时区问题。具体使用根据具体情境具体分析。
数据插入
方法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;