SQL 高级(一)

目录

一、修改表

二、设置主外键约束

三、数据操纵语言

3.1.DML（Data Manipulation Language）

3.2.DML语句——插入单条数据

3.3.DML语句——插入多条数据

3.4.DML语句——将查询结果插入到新表

 3.5.DML语句——更新数据

3.6.DML语句——删除数据

3.7.比较DROP、DELETE与TRUNCATE语句​

四、数据查询语言

五、常用函数

5.1.字符串函数

5.2.日期函数

5.3.聚合函数

5.4.数学函数

六、分页查询

七、子查询

八、SQL语句中指定别名的方法

九、本章总结

---

一、修改表

语法：

1. 修改表名

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

2. 添加字段

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

3. 修改字段

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

4. 删除表中的字段

   ALTER TABLE 表名 DROP 字段名;

使用SQL语句实现以下功能:

1. 将log表改名为action_log

2. 添加备注（comment）字段，数据类型为varchar(70)

3. time字段名改为actionTime，数据类型改为DATETIME

4. 删除备注（comment）字段

二、设置主外键约束

添加主键语法：

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

添加外键语法：

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

注意事项：

MySQL中，MyISAM存储类型的表不支持外键。因此，可以通过建立逻辑关联的方式保证数据的完整性和一致性。

如果项设置复合主键，复合主键的特点是同时创建、同时删除，所以需要把主键删除,如果表中设置了自增，需要先把自增删除，才可以删除主键，如果不先删除自增，而直接删除主键的话会报错。

三、数据操纵语言

3.1.DML（Data Manipulation Language）

DML是对数据库中表记录的执行哪些操作？

• 插入（INSERT）

  • 插入单行数据

  • 插入多行数据

• 将查询结果插入到新表

• 更新（UPDATE）

• 删除（DELETE）

软件开发人员日常使用最频繁的操作

3.2.DML语句——插入单条数据

语法：一次向表中插入一条记录

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

注意：

1. 字段名是可选的，如省略，则依次插入所有字段

2. 多个列表和多个值之间使用逗号分隔

3. 值列表和字段名列表一一对应

4. 如果插入表中的部分字段数据，则字段名列表必填

3.3.DML语句——插入多条数据

语法：一次向表中插入多条记录

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

3.4.DML语句——将查询结果插入到新表

 3.5.DML语句——更新数据

语法：

更新数据记录

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

3.6.DML语句——删除数据

语法：

删除数据记录

DELETE FROM 表名 [WHERE条件];

TRUNCATE TABLE 表名;

注意：

使用 truncate 语句删除表中记录后，将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比delete语句快

3.7.比较DROP、DELETE与TRUNCATE语句

执行速度:

1. 一般来说，DROP>TRUNCATE>DELETE.

2. 使用DROP、TRUNCATE语句时，要慎重.

3. 如果使用DELETE语句删除表中部分数据，要带上WHERE子句，且要注意表空间要足够大.

经验：

1. 如果要删除表，使用DROP语句

2. 如果要保留表但删除表中所有数据，如果与事务无关，可以使用TRUNCATE语句；

3. 如果与事务有关，则使用DELETE语句 

四、数据查询语言

Data Query Language

• 用于查询数据库的表中数据

• 是数据库中最为核心的语言，使用频率最高

语法：

SELECT  <字段名列表>
FROM  <表名或视图>
[WHERE  <查询条件>]
[GROUP BY <分组的字段名>]
[HAVING <条件>]
[ORDER BY <排序的字段名> [ASC 或 DESC]]

经验：

为了提高语句的执行效率，建议：编写查询语句时，采用SELECT 字段列表 FROM 表名 WHERE 条件表达式;

五、常用函数

SQL中将一些常用的数据处理操作封装起来，作为函数提供给程序员使用，可以提高程序员开发效率

MySQL支持的常用函数

1. 字符串函数

2. 时间日期函数

3. 聚合函数

4. 数学函数

5.1.字符串函数

函 数 名	作 用	举 例
CONCAT( str1,str1,…，strn)	连接字符串str1、str2、……、strn为一个完整字符串	SELECT CONCAT( 'MySQL',' is powerful.');返回：MySQL is powerful.
LOWER(str)	将字符串str中所有字符变为小写	SELECT LOWER( 'MySQL is powerful.');返回：mysql is powerful.
UPPER(str)	将字符串str中所有字符变为大写	SELECT UPPER( 'MySQL is powerful.');返回：MYSQL IS POWERFUL.
SUBSTRING( str,num,len)	返回字符串str的第num个位置开始长度为len的子字符串	SELECT SUBSTRING( 'MySQL is powerful.',10,8);返回：powerful
INSERT( str,pos,len,newstr)	将字符串str从pos位置开始，len个字符长的子串替换为字符串newstr	SELECT INSERT( 'MySQL is powerful.',10,0,'very ');返回：MySQL is very powerful.

5.2.日期函数

函数名	作用	举例**（部分结果与当前日期有关）**
CURDATE()	获取当前日期	SELECT CURDATE();返回：2020-08-03
CURTIME()	获取当前时间	SELECT CURTIME();返回：16:54:40
NOW()	获取当前日期和时间	SELECT NOW();返回：2020-08-03 16:55:00
WEEK(date)	返回日期date为一年中的第几周	SELECT WEEK(NOW());返回：31
YEAR(date)	返回日期date的年份	SELECT YEAR(NOW());返回：2020
HOUR(time)	返回时间time的小时值	SELECT HOUR(NOW());返回：16
MINUTE(time)	返回时间time的分钟值	SELECT MINUTE(NOW());返回：56
DATEDIFF(date1,date2)	返回日期参数date1和date2之间相隔的天数	SELECT DATEDIFF(NOW(), '2019-8-8');返回：361
ADDDATE(date,n)	计算日期参数date加上n天后的日期	SELECT ADDDATE(NOW(),5);返回：2020-08-07 16:57:28
UNIX_TIMESTAMP(date)	将日期转换成时间戳	SELECT UNIX_TIMESTAMP( "2020-9-1");返回：1598889600

5.3.聚合函数

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

5.4.数学函数

函数名	作 用	举 例
CEIL(x)	返回大于或等于数值x的最小整数	SELECT CEIL(-2.1);返回：32
FLOOR(x)	返回小于或等于数值x的最大整数	SELECT FLOOR(-2.1);返回：-3
RAND()	返回0~1间的随机数	SELECT RAND();返回：0.15013303621684485

六、分页查询

在SQL Server中使用top关键字实现指定返回查询结果集里指定的记录？

与SQLServer不同，MySQL中使用LIMIT子句限制结果集

语法：

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

注意：

• 第1条记录的位置偏移量是0，第2条记录的位置偏移量是1……

• LIMIT子句经常和ORDER BY子句一起使用，即先对查询结果进行排序，再根据LIMIT子句的参数返回指定的数据

偏移量的计算公式 偏移量=(当前页索引−1)∗每页显示的行数

七、子查询

实现方法一：使用SQL语句分两步完成

1.查找出“姚维新”的出生日期

#查找出“姚维新”的出生日期
SELECT birthDate FROM patient WHERE patientName='姚维新'; #查询结果：1986-10-11

2.使用WHERE子句筛选出所有出生日期比“1986年10月11日”小的病人信息

#使用WHERE子句筛选出年龄比姚维新大的病人信息
SELECT patientName, gender, birthDate, address FROM patient
WHERE birthDate < '1986-10-11' ;

实现方法二：采用子查询实现

SELECT patientName, gender, birthDate, address FROM patient 
WHERE birthDate < 
(SELECT birthDate FROM patient WHERE patientName='姚维新');

什么是子查询？

• 子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询

• 先执行子查询，返回所有来自子查询的结果，再执行外围的父查询，返回查询的最终结果

子查询在WHERE语句中的一般用法

语法：

SELECT …… FROM 表1 
WHERE 字段1 比较运算符 (子查询);

注意：

• 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个

• 先执行子查询，返回所有来自子查询的结果，再执行外围的父查询，返回查询的最终结果

八、SQL语句中指定别名的方法

使用AS关键字，符合ANSI标准

SELECT 字段列表 FROM 表名 AS 表的别名

使用空格，简便的方法

SELECT 字段列表 FROM 表名  表的别名;

可以为表、字段、查询结果指定别名

select 列名 as 列别名 from 表名;

select 列名 列别名 from 表名;

经验：

为某个表命名了别名后，在SELECT语句中出现该表的字段需要指定表名时，就必须统一使用该表的别名；否则将产生语法错误

九、本章总结