数据查询语言

语句格式

SELECT [ALL|DISTINCT] <目标列表达式> 
FROM <表名> 
[WHERE <条件表达式>] 
[GROUP BY <列名> [HAVING <条件表达式>]]
[ORDER BY <列名> [ASC|DESC]];

显示数据库

show databases

使用表前需要选择表位于的数据库

use databasesname

显示表

show tables;

显示表列

SHOW COLUMNS FROM 表名（快捷方式是DESC 表名）

查询数据

1、检索单列：SELECT 列名 FROM 表名;

2、检索多列：SELECT 列名1,列名2 FROM 表名;（逗号隔开）

3、检索所有列：SELECT * FROM 表名;

4、检索键值不同的行：SELECT DISTINCT 列名 FROM 表名;

5、限制条件

（1）SELECT 列名 FROM 表名 LIMIT 5; 表示输出该列最多5行

（2）SELECT 列名 FROM 表名 LIMIT 5,5; 表示输出该列从第五行开始的5行

起别名

SELECT <列名> as <别名> FROM <表名>;

排序

1、按单列以字母顺序排序：SELECT 列名 FROM 表名 ORDER BY 列名；

2、按多列以字母顺序排序：SELECT 列名1,列名2 FROM 表名 ORDER BY 列名1,列名2;

例如：SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name

先按prod_price 排序再按prod_name 排序。（仅在prod_price相同的情况下对prod_name排序）

3、降序排列：SELECT 列名 FROM 表名 ORDER BY 列名 DESC；（DESC只作用于前面的列名）

筛选

1、只检索所需数据

​ 如找到商品价格为2.50的商品名称

​ SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

2、不匹配查询

​ 如查找不是由供应商制造的所有产品

​ SELECT vend_id,prod_name FROM products WHERE vend_id != 1003;

3、范围查找

​ 如查找价格在5~10之间的商品

​ SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10 ;

4、空值查找（空值包括0、空字符串）

​ WHERE 列名 IS NULL；

5、操作符组合搜索

​ （1）WHERE 配合 AND ；（AND优先级更高）条件’与’

​ （2）WHERE 配合 OR；条件’或’

​ （3）WHERE 配合IN 在集合内满足条件的行

​ 如找价格为5、10的所有商品（元素为5和10的集合）

​ SELECT prod_name,prod_price FROM products WHERE prod_price IN(5,10);

​ （4） WHERE 配合NOT

​ 否定跟在它之后的条件

6、通配符模糊匹配（通配符搜索效率较低，避免过度使用通配符）

​ LIKE操作符匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它相应的行也不会被返回（除非使用通配符）

​ （1）%通配符：%表示任意字符的匹配，且不计字符的多少

​ （2）_通配符：与%通配符的用法一致，但区别在于下划线只能匹配单个字符

7、正则表达式

用于匹配文本的特殊的串，关键字REGEXP

1、基本字符匹配：REGEXP ‘字符串’

2、 匹配几个字符之一：REGEXP ‘[123] 字符串’（匹配1或2或3）

3、匹配范围：REGEXP ‘[1-3]字符串’ （匹配1-3）

4、匹配特殊字符：REGEXP ‘\\.’（转义字符，mysql要求两个反斜杠）

字符类
[:alnum:]	任意字母和数字
[:alpha:]	任意字符
[:blank:]	空格和制表符
[:cntrl:]	ASCII控制字符
[:digit:]	任意数字
[:graph:]	任意空白字符（不包括空格）
[:lower:]	任意小写字母
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格的任意空白字符
[:upper:]	任意大写字母
[:xdigit:]	任意十六进制数字
元字符
*	0个或多个匹配
+	1个或多个匹配
？	0个或1一个匹配
{n}	指定数目匹配
{n,}	不少于指定数目匹配
{n,m}	指定范围匹配
^	定位文本的开始
$	定位文本的结尾
[[:<:]]	定位词的开始
[[:>:]]	定位词的结尾

函数

数据处理函数

数据处理函数是用来处理数据的，包括以下应用

1：用于处理文本串
2：用于在数值数据上进行算数操作
3：用于处理日期和时间值并从这些值中提取特定成分
4：返回DBMS正使用的特殊信息

文本处理函数

Left（） 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转化为小写
LTrim() 去掉串左边的空格
Riight() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转化为大写

时间处理函数

日期格式：yyyy-mm-dd

datatype数据类型包含日期和时间值，例如：2021-09-22 11:30:05

如果只需要日期部分，使用Date()函数；如果只需要时间部分，则使用Time()函数

AddDate()	增加一个日期
AddTime()	增加一个时间
curDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	
Date_Format()	返回一个格式化的日期或时间串
Day()	返回天数部分
DayOfWeek()	返回对应的星期几
Hour()	返回小时部分
Minute()	返回分钟部分
Month()	返回月份部分
Now()	返回当前日期和时间
Second()	返回秒钟部分
Time()	返回时间部分
Year()	返回年份部分

数值处理函数

Abs()	返回绝对值
Cos()	返回余弦值
Exp()	返回指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回随机数
Sin()	返回正弦值
Sqrt()	返回平方根
Tan()	返回正切值

聚集函数

是一个值的集合为输入，返回单个值的函数

1、AVG()函数

功能：返回某列的平均值

2、COUNT()函数

功能：返回某列的行数

其中COUNT(*)表示对表中行的数目进行技术，不管表列中包含的空值还是非空值

3、MAX()函数

功能：返回某列的最大值

4、MIN()函数

功能：返回某列的最小值

5、SUM()函数

功能：返回某列值之和

分组查询

关键字GROUP BY

使用分组查询能够根据分组对象，将同类组别聚集。

例如 GROUP BY Sno即根据课程号进行分组，将同属于一个课程号的元组聚集成一个组

GROUP BY规定

1、GROUP BY子句可以包含任意数目的列

2、在GROUP BY子句中嵌套1分组，数据将在最后规定的分组上进行汇总（指定的所有列一起计算）

3、GROUP BY子句中列出的列必须是检索列或有效的表达式（不能是聚集函数）

4、因为关系数据库中不允许表中有表，所以除了聚集函数外，SELECT语句中的列必须要在GROUP中出现

5、如果分组列中具有NULL值，NULL将作为一个分组返回

过滤分组

WHERE过滤指定的是行而不分组，所以在某些情况下无法用WHERE对分组进行过滤（WHERE在分组前过滤）

HAVING是针对分组的过滤（HAVING在分组后过滤）

连接查询

若查询涉及两个表以上的表，则称为连接查询

等值连接

例如查询选修了课程号"C1"的学生的学号和姓名

SELECT Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND Cno = 'C1';

使用不同语法规则：

SELECT Sno, Sname FROM Student INNER JOIN SC ON Student.Sno = SC.Sno WHERE Cno = 'C1'

这里两个表之间的关系以INNER JOIN指定， 连接条件使用ON而不是WHERE(传递给NO的实际条件与传递给WHERE的相同)。使用INNER JOIN的好处是明确使用连接语法能够确保不会忘记连接条件，同时也能提高性能

等值连接基本上都是自然连接，自然连接要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。

自连接

本质是把一张表当成两张表来使用。在使用自联结时，我们需要为表定义别名，避免出现二义性。

例如要查找所有和编号为20652书同类的书籍编号和书名

SELECT p1.book_no, p1.book_name FROM books AS p1, books AS p2 WHERE p1.book_type = p2.book_type AND p2.book_no = '20652';

外部连接

当出现包含没有关联行的一些行，需要使用外部连接

外部连接分为左连接（LEFT JOIN）、右连接（RIGHT JOIN）、 全连接（FULL JOIN）

1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

例如显示所有学生的学号，姓名，选课数量（包括没有选课的学生），便需要用到外部连接

SELECT student.sno, sname, COUNT(cno) AS 选修课数量 FROM student LEFT OUTER JOIN sc ON student.sno = sc.sno GROUP BY student.sno, sname;

嵌套查询

嵌套查询指一个查询块中嵌入另一个查询块

例如查询既选修了1课程又选修了2课程的学生学号

SELECT Sno FROM sc WHERE Cno = 1 AND sno IN(SELECT Sno FROM sc WHERE cno = 2);

嵌套查询是从内向外处理的

复合查询

复合查询：执行多条查询语句，并将结果作为单个查询结果集返回

关键字UNION

UNION规则

1、UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔

2、UNION中的每个查询必须包含相同的列、表达式、聚合函数（不需要以相同次序出现）

3、列数据类型必须兼容（不必完全相同）

UNION从查询结果中自动出去了重复的行，这与使用多个WHERE子句条件一样。如果不需要除去重复的行，则可以使用UNION ALL。

使用UNION可以极大地简化复杂的WHERE子句，简化从多个表中检索数据的工作。