测试常用MySQL小结

MySQL复习

一、查看和确认数据库与表的相关信息

1、SHOW DATABASES，查看所有的数据库

2、SELECT DATABASE()，查看当前所在的数据库

3、SHOW tables，查看当前数据库中所有的表

4、DESC 表名，查看表的结构

二、常用的MySQL数据类型

1 、数值类型

常用的数值类型，示例省略了位数的指定

数据类型	含义	对应范围
INT	右侧范围内的整数(最常用)	-231~(231-1)
TINYINT	极小的整数	-128~127，27
SMALLINT	小整数	-215~(215-1)
MEDIUMINT	中等整数	-223~(223-1)
BIGINT	大整数	-263~(263-1)
DOUBLE	双精度浮点数(常用)
FLOAT	单精度浮点数

数值类型还可以用科学技术法表示，即
$$\begin{gathered} nE+m,表示n\times 10^m,如 \\ 6.02E+23表示6.02\times 10^{23} \end{gathered}$$
输入10000(一万)时可用以下形式：

INSERT INTO tb1A (age) VALUES (1E+4);

2、字符串类型

数据类型	含义	对应范围
CHAR	固定长度字符串	长度不超过255个字符
VARCHAR	可变长度字符串(最常用)	1~65532字符。上限取决于使用的字符编码集
TEXT	长文本字符串	长度不超过65535个字符

3、时间日期类型

数据类型	含义	对应范围
DATETIME	日期和时间	1000-01-01 00:00:00 ~ 9999 -12-31 23:59:59
DATE	日期	1000-01-01 ~ 9999 -12-31

输入时间日期类型示例如下：

INSERT INTO t_date (a, b) VALUES ('2023-02-28', '2023-02-28 14:40:45');

结果如下：

三、表的编辑管理

1、表 结构 的编辑管理

1.1 Create table 表名，创建表(增)

创建表的常用参考语法如下：

CREATE TABLE 表名(
列名1 数据类型1 [约束键] [comment '说明此列含义的注释'],
列名2 数据类型2 [约束键],
...
列名n 数据类型n [约束键]
);

如：

CREATE TABLE t_date(
a DATE,
b DATETIME
);

运行结果如下：

也可以从已有的表中复制备份：

CREATE TABLE t_date_copy SELECT * FROM t_date; -- 会连带源表的数据一同复制
CREATE TABLE t_date_copy LIKE t_date; -- 仅源表的结构，但不会一同复制源表数据

1.2 Alter table 表名，修改表的列结构(改)

（1）Add 列名 数据类型，添加列

如为表t_date_copy添加新列

ALTER TABLE t_date_copy ADD new_colnum_c varchar(15);

（2）Modify 列名 新数据类型，只改列的数据类型

修改列的数据类型时，要注意数据类型的兼容性

Modify 列名 新数据类型，将指定列名的数据类型改为特定的类型，如下：

ALTER TABLE t_date_copy MODIFY new_colnum_c varchar(25);

（3）Change 旧列名 新列名 新数据类型，同时列的数据类型和列名

ALTER TABLE t_date_copy CHANGE new_colnum_c rename_colnum_c varchar(30);

（4）Drop 列名，删除列

ALTER TABLE t_date_copy DROP rename_colnum_c;

（5）First/before/after，指定列的位置

在新增或修改列时，可以在新增或修改的同时也能指定对操作的列的顺序。

如在新增的new_colnum_c列的同时把new_colnum_c列放到最前面的代码如下：

ALTER TABLE t_date_copy ADD new_colnum_c varchar(15) FIRST;

或通过after/before放到指定列名之后/前，如

ALTER TABLE t_date_copy ADD new_colnum_c varchar(15) AFTER a;

与modify组合使用也有类似的效果，如：

ALTER TABLE t_date_copy MODIFY new_colnum_c varchar(20) AFTER b;

1.3 Drop table 表名，删除表(删)

DROP TABLE t_date_copy;

2、列的约束

2.1 主键(Primary key)

用于区分唯一的每行数据，方便查找和区分数据，所以主键必须唯一且不能为空，作用类似于id

2.2 唯一键(Unique key)

带有此约束的列不允许值为重复，但允许值为空。

2.3 默认值(Default)

用于设置列的默认值，在向表中插入数据时，若未向此列插入值，则数据库会自动向其中插入所设置的默认值

2.4 自动连续编号(auto_increment)

使列能自动连续编号（默认从一开始自增），如：

可用以下命令初始化auto_increment的初始值

-- init_value 是一个整数，且至少要大于已自增的最大值，如按上面的截图添加数据后，初始化时init_value >= 4
ALTER TABLE t_date_copy AUTO_INCREMENT=init_value;

3、表 数据 的编辑管理

3.1 Insert into … values …，向表中插入(新增)数据

向表中插入单行数据

INSERT INTO 
t_date_copy (unique_colnum, a, b) 
VALUES ('单行数据', '2023-03-01', '2023-03-01 12:32:45');

向表中插入多行数据

INSERT INTO 
t_date_copy (unique_colnum, a, b) 
VALUES ('多行数据——1', '2023-03-01', '2023-03-01 12:32:45'),
('多行数据——2', '2023-03-02', '2023-03-03 14:34:45'),
('多行数据——3', '2023-02-28', '2023-03-01 12:32:50');

3.2 Update … set …更新表中数据

-- 修改数据的语法为
-- UPDATE 表名 SET 列名 = 设置的新值
-- 但实际应用中必须带上 WHERE 条件，如下面的示例
UPDATE t_date_copy SET unique_colnum = '赵六' WHERE id = 4;

3.3 Delete from 表名，删除表中的数据

-- 修改数据的语法为
-- DELETE FROM 表名
-- 但实际应用中必须带上 WHERE 条件，如下面的示例
DELETE FROM t_date_copy WHERE id >= 5;

3.4 Select … from …，查询表中的数据(最常用/最复杂)

-- 查询语句的完整格式
SELECT ~ FROM ~ WHERE ~ GROUP BY ~ HAVING ~ ORDER BY ~;

但是它的执行顺序如下：

基于上图所示执行顺序的原因，在查询的时候要特别注意 别名、子查询、聚合函数的有效范围。

4、使用多个表

4.1 表连接

（1）全连接

只 匹配 两个表满足条件的行。两个表是主次关系是平等的，称之为内连接

SELECT * FROM tu JOIN tv ON tu.tu_vid = tv.tv_vid ;
-- 或用下面的简写
SELECT * FROM tu , tv WHERE tu.tu_vid = tv.tv_vid ;

（2）左外连接

左表为主表，无论匹配与否，都会显示。连接的两个表有主次之分，外连接。

SELECT * FROM tu LEFT JOIN tv ON tu.tu_vid = tv.tv_vid ;

（3）右外连接

右表为主表，无论匹配与否，都会显示。连接的两个表有主次之分，外连接。

SELECT * FROM tu RIGHT JOIN tv ON tu.tu_vid = tv.tv_vid ;

（4）自连接

表与其自身的连接，属于内连接,得到的是其自身的笛卡儿积。

-- SELECT [欲显示的字段] FROM 表A AS 别名1 JOIN 表A AS 别名1;
SELECT
	tv1.tv_vid AS v1_vid,
	tv1.v_id AS v1_id,
	tv2.tv_vid AS v2_vid,
	tv2.v_id AS v2_id
FROM
	tv AS tv1
JOIN tv AS tv2;

四、常用函数

1、聚合函数

常用聚合函数如下：

除count()函数之外，当输入集合是空集(NULL)时，函数的返回值是NULL，可能会出现意料之外的结果，此时要对NULL作适当处理，举例如下：

查询 class_a表中年龄比class_b表中住在北京的所有学生都小的学生， 用聚合函数的结果如下：

2、时间函数

2.1 查看当前时间，now(), curdate(), curtime()

2.2 分隔时间日期的年月日，YEAR(), MONTH(), DAY ()

返回整数

2.3 为时间增加指定的时间间隔，date_add()

2.4 为时间减少指定的时间间隔，date_sub()

2.5 日期时间求差，DATEDIFF()、TIMEDIFF()

2.6 日期格式化输出，date_format()

3、字符串函数

3.1 连接字符串，concate()

将所有的参数转为字符并将之连接起来，返回一个长字符串；

3.2 替换字符串，insert(),replace()

insert(old_string, strat_index, be_replace_substring_length, new_string ), 通过下标指定源字符串的被替换子串，MySQL中的字符串下标是以一开始的，而非传统编程语言的零。

replace(old_string, be_replace_substring, new_string)，将源字符中指定的字符全部替换

3.3 字符串截取，left()、right()

3.4 去除字符串两侧的空格，trim()

4、数学函数

4.1 取整与四舍五入，ceil()、floor()、round()

ceil(x)：返回大于x的最小整数，即向上取整

floor(x)：返回小于x的最大整数，即向下取整

round(x, n)：将x保留n位小数，对结果四舍五入

4.2 生成随机数，rand()

rand(seed),生成0~1之间的随机数，当传入seed时，产生的随机数是固定的。

4.3 求幂/根/余/绝对值，power()、sqrt()、mod()、abs()

根是指平方根。