三、MySql03 SQL 高级(一)

三、SQL 高级(一)

3.1 修改数据表结构

3.1.1 修改数据表的表名

• 修改数据表的表名 语法

ALTER TABLE 旧表名 RENAME [TO] 新表名;

• [TO] 为可选，使用与否不影响效果
• 此语句只修改表的名称，不会修改表的结构

3.1.2 修改数据表的字段

• 修改表中的字段包含字段名和数据类型
• 修改数据表字段 语法

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

• 若不需要修改数据类型，则与原类型保持一致，不可为空
• 若修改时未添加属性，则修改后的字段无任何属性约束

3.1.3 给数据表添加字段

• 向已有的数据表添加字段
• 给数据表添加字段 语法

ALTER TABLE 表名 ADD 字段名 数据类型 [属性];

3.1.4 删除数据表的字段

• 删除数据表当中的某个已有字段
• 删除数据表的字段 语法

ALTER TABLE 表名 DROP 字段名

3.1.5 添加主外键约束

• 添加主键约束 语法

ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名(主键字段)

• 添加外键约束 语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 关联表名(关联字段)

3.2 DML 语句

DML 语句：执行 增、删、改、语句

3.2.1 插入数据语句

使用 INSERT 语句向数据表中插入数据

• 插入单行数据 语法

INSERT INTO 表名 [(字段名列表)] VALUES (值列表);

• 字段名列表时可选项，若省略，则依次插入所有字段
• 值列表必须和字段名列表的数量相同，且数据类型一致
• 多个字段名和多个值之间使用逗号隔开
• 插入多行数据 语法

INSERT INTO 表名 [(字段名列表)] VALUES(值列表1),(值列表2),...,(值列表n);

• 将查询结果插入新列表 语法1

INSERT INTO 表名(字段1,字段2,...) SELECT 字段1,字段2,... FROM 原表;

• 将查询结果插入新列表 语法2

SELECT 字段1,字段2,...  INTO 新表 FROM 原表;

• 将查询的数据插入到新创建的表中 语法

CREATE TABLE 新表(SELECT 字段1,字段2,...  FROM 原表)

3.2.2 更新数据语句

使用 UPDATE 语句更新数据表中的数据

• 更新数据语句 语法

UPDATE 表名 SET 字段1=值1,字段2=值2,...,字段n=值n [WHERE 条件]

3.2.3 删除数据语句

从数据表中删除数据可以使用 DELETE 语句 或 TRUNCATE 语句

• 使用 DELETE 语句删除数据 语法

DELETE FROM 表名 [WHERE 条件]

• 使用 TRUNCATE 语句清除整个表 语法

TRUNCATE TABLE 表名

• 该语句可清空表内的所有数据

3.3 数据查询

3.3.1 DQL 语句

3.3.3 分页查询

回顾数据查询语句 (SELECT 语句) 语法如下

SELECT *或<字段名列表> FROM <表名或视图> [WHERE <查询条件>] [GROUP BY <分组的字段名>] [HAVING <条件>] [ORDER BY <排序的字段名>] [ASC 或 DESC] [LIMIT [位置偏移量,]行数];

• WHERE 子句为可选项，如果选择该选项，则将限定查询条件，查询结果必须满足此查询条件
• GROUP BY 子句表明查询出来的数据按指定字段进行分组
• HAVING 子句用于筛选组
• ORDER BY 子句指定按什么顺序显示查询出来的数据，需要指定排序字段。默认按照升序排列，也可以通过代码指定 升序（ASC）或 降序（DESC）
• LIMIT 来指定查询结果返回条数
  • 位置偏移量表示结果从结果集的第几条数据开始选取（记录的偏移位置从0开始开始计算，表示第一条记录，第二条记录的偏移位置是1，以此类推）
  • 行数指以偏移位置为起点，选取记录的条数

3.3.2 常用函数

基于已有数据进行数据的统计分析计算等需求

1、字符串函数

• 可以对字符串进行各种处理
• MySQL 中常用的字符串处理函数

函数名	函数说明	函数示例
CONCAT(str1,str2,…,strn)	将字符串 str1、str2、…、strn连接为一个完整的字符串	SELECT CONCAT(‘L’,‘Q’,‘E’); 返回：LQE
LOWER(str)	将字符穿 str 中所有字符变为小写	SELECT LOWER(‘LqE’) 返回：lqe
UPPER(str)	将字符串 str 中所有的字符变为大写	SELECT UPPER(‘lQe’) 返回：LQE
SUBSTRING(str,num,len)	返回字符串 str 的第 num个位置开始，长度为 len 的子字符串	SELECT SUBSTRING(‘ABCDEFG’,3,4) 返回：CDEF
INSERT(str,pos,len,newstr)	将字符串 str 从 pos 位置开始，将len 个字符长度的子串替换为字符串 newstr	SELECT INSERT(‘1234’,3,0,‘ABCDE’) 返回：12ABCDE34

2、时间日期函数

• 获取当前日期，对日期进行操作
• MySQL 中常用的日期函数

函数名	函数说明	示例
CURDATE()	获取当前日期	SELECT CURDATE(); 返回：2023-03-11
CURTIME()	获取当前时间	SELECT CURTIME(); 返回：01:54:30
NOW()	获取当前日期和时间	SELECT NOW(); 返回：2023-03-11 01:55:12
WEEK(date)	返回参数 date 为一年中的第几周	SELECT WEEK(NOW()); 返回: 10
YEAR(date)	返回参数 date 的年份	SELECT YEAR(NOW()); 返回：2023
HOUR(time)	返回参数 time 的小时值	SELECT HOUR(NOW())； 返回：1
MINUTE(time)	返回参数 time 的分钟值	SELECT HOUR(NOW()); 返回：57
DATEDIFF(date1,date2)	返回参数 date1 和参数 date2 之间相隔的天数	SELECT DATEDIFF(NOW(),‘2019-8-8’) 返回：1311
ADDDATE(date,n)	计算参数 date 在 n 天后的日期	SELECT ADDDATE(NOW(),4); 返回：2023-3-15 01:58:36

3、聚合函数

• 对已有数据进行汇总，常见的有求和、平均值、最大值、最小值等
• MySQL 中常用的聚合函数

函数名	函数说明
COUNT()	返回某字段的行数
MAX()	返回某字段的最大值
MIN()	返回某字段的最小值
SUM()	返回某字段的和
AVG	返回某字段的平均值

4、数学函数

• 可以进行数值运算
• MySQL 中常见的数学函数

函数名	函数说明	示例
CELL(x)	返回大于或等于数值 x 的最小整数，向上取整	SELECT CELL(2.1) 返回：3
FLOOR(x)	返回小于或等于数值 x 的最大整数，向下取整	SELECT FLOOR(2.9) 返回：2
RAND()	返回 0~1 的随机数	SELECT RANG(); 返回：0.15013303621684485