学习笔记——JavaWeb开发
第一章 数据库相关概念
第二章 MySQL基础1
第三章 MySQL基础2
-
第三章 MySQL基础2
一、DML -- 操作数据
1.1 添加（插入insert）
1.1.1 方式1：VALUES 的方式添加
情况1：为表的所有字段按默认顺序插入数据
INSERT INTO 表名
VALUES (value1,value2,....);值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
举例：
INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);
INSERT INTO departments
VALUES(100, 'Finance', NULL, NULL);情况2：为表的指定字段插入数据
INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。
在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。
举例：
INSERT INTO departments(id, name)
VALUES (80, 'IT');情况3：同时插入多条记录
INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：
默认为所有列添加数据
INSERT INTO 表名
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
或者为指定类添加数据
INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
举例：
mysql> INSERT INTO emp(emp_id,emp_name)
-> VALUES (1001,'shkstart'),
-> (1002,'atguigu'),
-> (1003,'Tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下： ● Records：表明插入的记录条数。 ● Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。 ● Warnings：表明有问题的数据值，例如发生数据类型转换。
一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中
效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。
小结：
-
VALUES也可以写成VALUE，但是VALUES是标准写法。 -
字符和日期型数据应包含在单引号中。(mysql用双引号也可以,但是规范中规定单引号,比如oracle中用单引号)
1.1.2 方式2：将查询结果插入到表中
INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。
基本语法格式如下：
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]-
在 INSERT 语句中加入子查询。
-
不必书写 VALUES 子句。
-
子查询中的值列表应与 INSERT 子句中的列名对应。
举例：
所有列
INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
指定列
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees1.2 修改删除
1.2.1 修改 update
改变表中一行或者多行记录的一个或者多个列的值
-
使用 UPDATE 语句更新数据。语法如下：
UPDATE 表名
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]-
可以一次更新多条数据。
-
使用 WHERE 子句指定需要更新的数据。
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;-
如果省略 WHERE 子句，则表中的所有数据都将被更新。
UPDATE copy_emp
SET department_id = 110;1.2.2 删除 delete
从表中删除一条或者多条记录
-
使用 DELETE 语句从表中删除数据
DELETE FROM table_name [WHERE <condition>];table_name指定要执行删除操作的表；“[WHERE <condition>]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。
-
使用 WHERE 子句删除指定的记录。
DELETE FROM departments
WHERE department_name = 'Finance';-
如果省略 WHERE 子句，则表中的全部数据将被删除
DELETE FROM copy_emp;-
删除中的数据完整性错误 有关联关系的情况
DELETE FROM departments
WHERE department_id = 60;
You cannot delete a row that contains a primary key that is used as a foreign key in another table.
不能删除包含在另一个表中用作外键的主键的行。二、DQL操作
2.1 基础查询
2.1.1 SELECT...
SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句
等同于
select 1+1 FROM DUAL
DUAL:伪表2.1.2 SELECT ... FROM
-
语法：
SELECT 标识选择哪些列
FROM 标识从哪个表中选择-
选择全部列：
SELECT *
FROM 表名;
*: 表示表中的所有的列;一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
在生产环境下，不推荐你直接使用
SELECT *进行查询。
-
选择特定的列查询：
SELECT 字段名1, 字段名1
FROM 表名;MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，读者也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护。
2.1.3 列的别名
-
重命名一个列
-
便于计算
-
紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。(msql也可以使用单引号**)
-
AS 可以省略
-
建议别名简短，见名知意
直接写别名 SELECT name as 姓名, address 地址 from stu; 单引号 (不建议 mysql的语法的不严谨性) SELECT name as '姓名', address '地址' from stu; 双引号 SELECT name as "姓名", address "地址" from stu; 省略 SELECT name "姓名", address "地址" from stu; 必须双引号 SELECT name as "姓名", address "地 址" from stu; 或者 SELECT name "姓名", address '地 址' from stu;
2.1.4 去除重复行 DISTINCT
默认情况下，查询会返回全部行，包括重复行。
SELECT address from stu;
在SELECT语句中使用关键字DISTINCT去除重复行
SELECT DISTINCT address from stu;
联合去重
SELECT DISTINCT address,math from stu;这里有两点需要注意：
-
DISTINCT 需要放到所有列名的前面，不然报错
-
DISTINCT 其实是对后面所有列名的组合进行去重
2.1.5 空值参与运算
-
所有运算符或列值遇到null值，运算的结果都为null
SELECT id,name,address,math,english+2 from stu;这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
-
解决方案: 使用函数IFNULL
SELECT id,name,address,math,IFNULL(english,0)+2 from stu;2.1.6 着重号
-
错误演示 当你查询的表名和sql 关键字重复的时候
SELECT * from order; SELECT * from ORDER; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1 -
解决方案 着重号 tab键上边的 ~ 直接按
SELECT * from `order`; SELECT * from `ORDER`; -
我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来。
2.1.7 查询常数
SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
比如说，我们想对 stu数据表中增加一列字段"总成绩"，
SELECT *, math+IFNULL(english,0) "总成绩" from stu;2.2 条件查询
2.2.1 过滤数据
-
语法：
SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件-
使用WHERE 子句，将不满足条件的行过滤掉
-
WHERE子句紧随 FROM子句
-
2.2.2 算术运算符
算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。
| 运算符 | 名称 | 作用 | 例子 |
|---|---|---|---|
| + | 加法 | 计算两个值或者表达式的和 | select a+b |
| - | 减法 | 计算两个值或者表达式的差 | select a-b |
| * | 乘法 | 计算两个值或者表达式的乘积 | select a*b |
| / 或者DIV | 除法 | 计算两个值或者表达式的商 | select a/b |
| %或者MOD | 取余/求模 | 计算两个值或者表达式的余数 | select a%b |
1．加法与减法运算符
一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）
2．乘法与除法运算符
MySQL 中的 '/' 运算符可以认为只是做一个除法的运算，他不会看你的数据类型是什么，通过‘/’运算符返回的值可以是浮点数。
而DIV计算的是整除，也就是说通过DIV计算返回的一定是你的商，而不会像‘/’一样可以返回一个浮点数。
一个数乘以整数1和除以整数1后仍得原数；
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
3．求模（求余）运算符
# 结果的符号和被模数的符号有关
2.2.3 比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。
比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。
| 运算符 | 名称 | 作用 | |
|---|---|---|---|
| = | 等于运算符 | 判断两个值,字符串,表达式是否相等 | |
| <=> | 安全等于运算符 | 安全的判断两个值,字符串,表达式是否相等 | |
| <> 或者 != | 不等于运算符 | 判断两个值,字符串,表达式是否不相等 | |
| < | 小于运算符 | 判断前面的值,字符串,或表达式是否小于后边的值,字符串,和表达式 | |
| <= | 小于等于运算符 | 判断前面的值,字符串,或表达式是否小于等于后边的值,字符串,和表达式 | |
| > | 大于运算符 | 判断前面的值,字符串,或表达式是否大于后边的值,字符串,和表达式 | |
| >= | 大于等于运算符 | 判断前面的值,字符串,或表达式是否大于等于后边的值,字符串,和表达式 |
1．等号运算符
-
等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
-
在使用等号运算符时，遵循如下规则：
-
如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
-
如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
-
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
-
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
-
-
对比：SQL中赋值符号使用 :=
2．安全等于运算符 安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。
3．不等于运算符 不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。 SQL语句示例如下：
此外，还有非符号类型的运算符：
非符号类型
| 运算符 | 名称 | 作用 | 示例 |
|---|---|---|---|
| IS NULL | 为空运算符 | 判断值,字符串,表达式是否为空 | select b from table where a is null |
| IS NOT NULL | 不为空运算 | 判断值,字符串,表达式是否为不空 | select b from table where a is not null |
| LEAST | 最小值运算符 | 在多个值中返回最小值 | select d from table where c least(a,b) |
| GREATEST | 最大值运算符 | 在多个值中返回最大值 | select d from table where c greatest(a,b) |
| BETWEEN AND | 两个值之间的运算符 | 判断一个值是否在两个值之间 | select d from table where c betweed A and B |
| ISNULL | 为空运算符 | 判断值,字符串,表达式是否为空 | select b from table where a isnull |
| IN | 属于运算符 | 判断一个值是否为列表中的任意一个值 | select d from table where c in(a,b) |
| NOT IN | 不属于运算符 | 判断一个值是否不是列表中的任意一个值 | select d from table where c not in(a,b) |
| LIKE | 模糊匹配运算符 | 判断一个值是否匹配模糊匹配规则 | select b from table where a like c |
| REGEXP | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 | select b from table where a regexp c |
| RLIKE | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 | select b from table where a rlike c |
4. 空运算符 空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。
where 条件筛选的是结果为1(真)的
english<=>null 会返回 0或者 1
如果返回1 表示english为null,不是我想要的 通过not 结果变成0 该筛选结果不予显示
如果返回0 表示english不为null 是我想要的 通过not 结果变成1 该筛选结果显示5. 非空运算符 非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。
6. BETWEEN AND运算符 [包含边界值] BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。
BETWEEN A(下限) AND B(上限)
#数学成绩在60-100
SELECT * from stu where math BETWEEN 60 and 100;
#数学成绩不在60-100
SELECT * from stu where math not BETWEEN 60 and 100;7. IN运算符 IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL(且查询和in选项没有匹配)，则结果为NULL。
#展示年龄在20,30,40的学生
SELECT * FROM stu where age in(20,30,40);
# 如果in选项中有null 且查询条件和in 选项中有匹配 结果返回真
# 如果查询和选项不匹配且选项中有null结果返回null
SELECT 'b' IN ('a','b',NULL)
所以:
SQL中使用in时，会忽略null值的记录，即不会查询出列值为null的数据。
#查询英语成绩在(79,89,99,null)中
SELECT * FROM stu where english in(79,89,99,null);# 有结果8. NOT IN运算符 NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。
SQL中使用not in时，如果 not in后面的选项中没有null，只会查询从列值不为空的列中过滤，即会过滤掉列值为null的数据。
#######
有null值的not in的使用 select * from test_in where sex not in (1, null);
SQL中使用not in时，如果not in后面的选项中有null，则不会查询出来任何数据。
因为sql语句本身直接返回false，所以使用not in时候，要保证not in后的条件值不会出现null，不然可能会出现意想不到的情况。
SELECT * FROM stu where english not in(79,89,99,null);# 没有结果2.2.4 逻辑运算符
| 运算符 | 作用 | 示例 |
|---|---|---|
| NOT 或者 ！ | 逻辑非 | select not a |
| AND 或者 && | 逻辑与 | select a and b |
| OR 或者 || | 逻辑或 | select a or b |
| XOR | 逻辑异或 | select a xor b |
1．逻辑非运算符 逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。
SELECT * FROM stu where english not in(79,89,99)2．逻辑与运算符 逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。
#数学成绩在60-100
SELECT * from stu where math>=60 and math<=100;
SELECT * from stu where math>=60 && math<=100;
#数学成绩不在60-100
SELECT * from stu where not math>=60 and math<=100;
SELECT * from stu where not math>=60 && math<=100;3．逻辑或运算符 逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。
#数学成绩不在60-100
SELECT * from stu where not (math>=60 and math<=100);
SELECT * from stu where math<60 or math>100;
SELECT * from stu where math not BETWEEN 60 and 100;注意：
OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
2.2.5 总结
常用运算符
| 符号 | 功能 |
|---|---|
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| BETWEEN ...AND ... | 在某个范围之内(都包含) |
| IN(.….) | 多选— |
| LIKE占位符 | 模糊查询_单个任意字符%多个任意字符 |
| IS NULL | 是NULL |
| IS NOT NULL | 不是NULL |
| AND或&& | 并且 |
| OR或│ | 或者 |
| NOT或! | 非，不是 |
注意
-
语句中 && 和 and 都表示并且的意思。建议使用 and 。
-
可以使用 between ... and 来实现范围查询。
-
null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not
2.3 模糊查询
LIKE运算符
属于比较运算符
LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
LIKE运算符通常使用如下通配符：
“%”：匹配0个1个或多个字符。
“_”：只能匹配一个字符。2.4 排序查询
2.4.1 排序规则
如果没有使用排序操作, 默认情况下查询返回的数据是按照添加数据的顺序显示的
-
使用 ORDER BY 子句排序
-
ASC（ascend）: 升序 (从上往下 从小到大 默认)
-
DESC（descend）:降序 (从上往下 从大到小)
-
-
ORDER BY 子句在SELECT语句的结尾。
2.4.2 单列排序
**按照年龄升序**
SELECT * FROM stu ORDER BY age asc; # asc可省略
**按照年龄降序**
SELECT * FROM stu ORDER BY age desc;2.4.3 使用列的别名排序
可以使用列的别名作为排序的条件
SELECT *,math+IFNULL(english,0) as 总成绩 FROM stu ORDER BY 总成绩 desc;
# 但是不能作为where 的条件使用 会报错
SELECT
*, math + IFNULL(english, 0) AS 总成绩
FROM
stu
WHERE
总成绩 > 120
ORDER BY
总成绩 DESC;
原因: 执行顺序是 先执行from where 再去select 才有的别名
强调顺序: where 需要在 from 之后 order by 之前2.4.4 多列排序
-
可以使用不在SELECT列表中的列排序。
-
在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
#按照数学成绩降序,如果数学成绩相同,按照英语成绩升序
SELECT * FROM stu ORDER BY math desc,english asc;2.5 聚合函数
2.5.1 常用的单行函数
| 函数 | 使用 |
|---|---|
| 数值函数 | |
| RAND() | 返回0~1的随机值 |
| ROUND(x) | 返回一个对x的值进行四舍五入后，最接近于X的整数 |
| ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
| 字符串函数 | MySQL中，字符串的位置是从1开始的。 |
| CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| CONCAT(s1,s2,......,sn) | 连接s1,s2,......,sn为一个字符串 |
| 日期函数 | |
| CURDATE() ，CURRENT_DATE() | 返回当前日期，只包含年、月、日 |
| CURTIME() ， CURRENT_TIME() | 返回当前时间，只包含时、分、秒 |
| NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前时间，包含年、月、日、时、分、秒 |
| 流程控制函数 | |
| IFNULL(value1, value2) | 如果value1不为NULL，返回value1，否则返回value2 |
| 加密函数 | |
| MD5(str) | 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL |
| SHA(str) | 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。 |
| 信息函数 | |
| VERSION() | 返回当前MySQL的版本号 |
| DATABASE()，SCHEMA() | 返回MySQL命令行当前所在的数据库 |
2.5.2 常用的聚合函数
什么是聚合函数
聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。
聚合函数作用于一组数据，并对一组数据返回一个值。
聚合函数类型
AVG()
SUM()
MAX()
MIN()
COUNT()
聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。
2.5.3 AVG() SUM() MAX() MIN()
正确使用: 可以对数值型数据使用。
SELECT AVG(age), MAX(age),MIN(age), SUM(age)
FROM stu;2.5.4 COUNT函数
-
COUNT(*)返回表中记录总数，适用于任意数据类型。
SELECT COUNT(*)
FROM stu;-
COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(english)
FROM stu;2.5.5 注意点:
问题：用count(*)，count(1)，count(列名)谁好呢?
其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。
问题：能不能使用count(列名)替换count(*)?
不要使用 count(列名)来替代
count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
sum(列) 关于列空值问题
2.6 分组查询
2.6.1 GROUP BY
可以使用GROUP BY子句将表中的数据分成若干组
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];明确：WHERE一定放在FROM后面
在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中
也就是说 分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
#查询男女学生的,数学最高分,数学平均分
SELECT
sex,
max(math) 数学最高分,
avg(math) 数学平均分
FROM
stu
GROUP BY
sex;
非聚合函数使用的字段或者没有在 group by中出现的字段,不能出现的 select中
反之 在 group by中出现的字段不一定非要写在 select 中
但是在mysql中出现不会报错但是没有意义, 在oracle中会报错2.6.2 HAVING
过滤分组：HAVING子句
-
行已经被分组。
-
过滤条件使用了聚合函数。就不能使用where了,必须使用having替换where 否则报错
-
满足HAVING 子句中条件的分组将被显示。
-
HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
#按照性别查询数学的平均分,并且平均分要高于80的
SELECT
sex,
avg(math) avg
FROM
stu
GROUP BY
sex
HAVING
avg > 80;不能在 WHERE 子句中使用聚合函数
2.6.3 WHERE和HAVING的对比
区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。
这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
小结：
| 优点 | 缺点 | |
|---|---|---|
| WHERE | 先筛选数据再关联，执行效率高 | 不能使用分组中的计算函数进行筛选 |
| HAVING | 可以使用分组中的计算函数 | 在最后的结果集中进行筛选，执行效率较低 |
开发中的选择：
WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。
2.7 分页查询
2.7.1 实现规则
-
分页原理
所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
-
MySQL中使用 LIMIT 实现分页
-
格式：
LIMIT [位置偏移量,] 行数第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。
-
举例
--前10条记录： 第一页
SELECT * FROM 表名 LIMIT 0,10; 或者 SELECT * FROM 表名 LIMIT 10;
--第11至20条记录： 第二页
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录： 第三页
SELECT * FROM 表名 LIMIT 20,10;MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。
-
分页显式公式==：（当前页数-1）*每页条数，每页条数==
SELECT * FROM table
LIMIT (PageNo - 1)*PageSize,PageSize;
#从(PageNo - 1)*PageSize位置开始查询, 查询PageSize条注意：LIMIT 子句必须放在整个SELECT语句的最后！包括排序之后
-
使用 LIMIT 的好处
约束返回结果的数量可以
减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
LIMT 0, 条数 ;等价于 LIMT 条数
#查询第一条数据
SELECT * FROM stu limit 0,1;
SELECT * FROM stu limit 1;
#查询第3到5条数据(从第三条数据开始查询,查询3条)
SELECT * FROM stu limit 2,3;
#每页显示两条,展示第三页 (3-1)*2,2
SELECT * FROM stu LIMIT 4,2;2.7.2 分页的拓展
在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字，而且需要放到 SELECT 语句的最后面。
-
如果是 SQL Server 和 Access，需要使用
TOP关键字，比如：
SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC;-
如果是 DB2，使用
FETCH FIRST 5 ROWS ONLY这样的关键字：
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY;-
如果是 Oracle，你需要基于
ROWNUM来统计行数：
SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;需要说明的是，这条语句是先取出来前 5 条数据行，然后再按照 hp_max 从高到低的顺序进行排序。但这样产生的结果和上述方法的并不一样。我会在后面讲到子查询，你可以使用
SELECT rownum, last_name,salary
FROM (
SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE rownum < 10;2.8 DQL-小结
2.8.1 SELECT的执行过程
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页2.8.2 SELECT执行顺序
SELECT 查询时的两个顺序：
1. 关键字的顺序是不能颠倒的：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。
扫一扫
415
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
