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 | 单精度浮点数 |
数值类型还可以用科学技术法表示,即
n
E
+
m
,
表示
n
×
1
0
m
,
如
6.02
E
+
23
表示
6.02
×
1
0
23
n E + m,表示n\times10^m,如 \\6.02E + 23 表示 6.02 \times 10^{23}
nE+m,表示n×10m,如6.02E+23表示6.02×1023
输入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()
根是指平方根。