MySQL数据库从零开始学习笔记2

15、数据操作语言：普通查询

        1、记录查询

        最基本的查询语句是由SELECT和FROM关键字组成的

        SELECT * FROM students；        #查询students表的所有信息

        SELECT id，name，sex FROM students；        #查询students表id，name，sex的信息

        2、使用列别名

        通常情况下，SELECT子句中使用了表达式，那么这列的名字就默认为表达式，因此需要一种对列名重命名的机制。需要注意的是重命名只在显示输出的时候使用，数据表列名不会真改变

        SELECT

                ename，        #选择ename列

                sal * 12 AS "annual_salary"  /*选择sal列每一行的值乘以12，并重命名为annual_salary*/

        FROM

                emp;        #从emp表中提取

        3、查询语句的子句执行顺序

             1、词法分析（读取SQL语句）2、FROM（选择数据来源）3、SELECT（选择输出内容）

16、数据操作语言：数据分页

        比如我们看新闻，只会加载少量部分信息，不会一次性加载全部新闻，那样只会浪费CPU时间、内存和网络宽带

        如果去结果的记录很多，则可以使用LIMIT关键字限定结果数量

SELECT ...... FROM ...... LIMIT 起始位置，偏移量；    #没有起始位置则默认为0
例：SELECT empno,ename FROM t_emp LIMIT 0,20;    

17、数据操作语言：结果集排序

        如果没有设置，查询语句不会对结果集进行排序，即如果想让结果按照某种顺序排列，就必须使用ORDER BY子句

SELECT ...... FROM ...... ORDER BY 列名 [ASC/DESC]；    #ASC升序、DESC降序，默认升序
例：
SELECT ename，sal FROM t_emp ORDER BY sal;    #从t_emp表中提取ename和sal数据，并按sal升序排序

        如果排序列是数字类型，数据库就按照数字大小排序，如果是日期类型就按照日期大小排序，如果是字符串就按照字符集序号排序

        当排序字段内容相同时，MySQL会默认按照主键大小来排序两条数据

18、数据操作语言：多个排序字段

        我们可以使用ORDER BY规定首要排序条件和次要排序条件。数据库会先按照首要排序条件排序，如果首要条件相同，那么就会启用次要排序条件接着排序。

SELECT empno，sal，hiredate
FROM t_emp
ORDER BY sal DESC，hiredate ASC；

        排序加分页时，ORDER BY字句写在LIMIT字句前面

19、数据操作语言：去除重复记录

        如果我们查询员工的工表有多少职业时，查询的职业可能有重复，我们可以使用DISTINCT关键字来去除重复的数据

SELECT DISTINCT 字段 FROM ......;
例：
SELECT DISTINCT job FROM t_emp; 

        需要注意的是使用DISTINCT子句中只能查询一列数据，如果查询多列，去除重复记录就会失效，DISTINCT关键字只能在SELECT子句中使用一次

SELECT DISTINCT job,sal FROM t_emp;    #查询多列，去重失效
SELECT DISTINCT job,DISTINCT sal FROM t_emp;     #DISTINCT多次使用，错误
SELECT sal，DISTINCT job FROM t_emp;     #只去重job，正确

20、数据操作语言：条件查询（一）

        很多时候，用户感兴趣的只有满足某些条件的数据，此时需要使用WHERE子句来实现数据筛选

SELECT ......FROM ...... WHERE 条件 [AND/OR] 条件 ......；
例：
SELECT job,sal FROM t_emp WHERE sal>=2000; 

21、运算符

        常用的有以下四类运算符：1、数学运算符，2、比较运算符，3、逻辑运算符，4、按位运算符

1、数学运算符

表达式	意义	例子
+	加法	1+2
-	减法	2-1
*	乘法	1*2
/	除法	1/2
%	求模（取余数）	10%3

2、比较运算符

表达式	意义	例子
>	大于	age>18
>=	大于等于	age>=18
<	小于	sal<3000
<=	小于等于	sal<=3000
=	等于	sal=3000
！=	不等于	age！=18
IN	包含	age IN(18,28,38)
IS NULL	为空	comm IS NULL
IS NOT NULL	不为空	cel IS NOT NULL
BETWEEN AND	范围	sal BETWEEN 2000 AND 3000
LIKE	模糊查询	age LIKE "1%"
REGEXP	正则表达式	num REGEXP ”[0-9]{3}"

3、逻辑运算符

表达式	意义	例子
AMD	与	age>19 AND age<30
OR	或	age>20 OR sex="男"
NOT	非	NOT sex=“女”
XOR	异或	age>18 XOR sex="男"

4、按位运算符

表达式	意义	例子
&	位与	3 & 7
|	位或	3 | 7
~	位取反	~10
^	位异或	3^7
<<	左移	10<<1
>>	右移	10>>1

        WHERE子句的注意事项：WHERE子句中，条件执行顺序是从左到右的，故我们应该把索引条件筛选掉记录最多的条件放在左侧

22、数据操作语言：聚合函数

        聚合函数在数据的查询分析中，应用十分广泛。聚合函数可以对数据求和、求最大值、最小值、平均值等

        例：求公司员工平均月收入是多少？

        SELECT AVG(sal+IFNULL(comm,0)) FROM t_emp;        #IFNULL(A,X)函数，如果A列里的值为NULL则NULL值会自动换成X值

23、聚合函数：SUM函数

        SUM函数用于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加。SUM函数求和会排除NULL值

24、聚合函数：MAX、MIN函数

        MAX函数用于获取非空值的最大值，MIN函数用于获取非空最小值

25、聚合函数：AVG函数

        AVG函数用于获取非空值的平均值，非数字数据统计结果为0

26、聚合函数：COUNT函数

        COUNT(*)用于获得包含空值的记录数，COUNT(列名)用于获取不包含空值的记录数

27、分组查询

        为什么要分组呢?

                因为在默认情况下聚合（汇总）函数是对全表范围内的数据做统计

        GROUP BY子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理

        例:

SELECT deptno,AVG(sal) FROM t_emp
GROUP BY deptno;    #分组计算每个部门的平均工资

28、逐级分组

        数据库支持多列分组条件，执行的时候逐级分级组。

29、对SELECT子句要求

        查询语句中如果包含GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定：SELECT子句中可以包含聚合函数，或者GROUP BY子句的分组列，其余内容不可出现在SELECT子句中

30、WITH ROLLUP，放在GROUP BY 后面，对分组结果集再次做汇总计算。

31、GROUP_CONCAT函数

        该函数可以把分组查询中的某个字段拼接成一个字符串

32、各种子句的执行顺序FROM>WHERE>GROUP BY>SELECT>ORDER BY>LIMIT

33、数据操作语言：HAVING子句

34、数据操作语言：表连接查询

        从多张表中提取数据，必须指定关联条件，如果不定义关联条件就会出现无条件连接，两张表的数据会交叉连接，产生笛卡尔积。

        例：SELECT e.empno,e.ename,d.dname FROM t_emp e JOIN t_dept d on e.deptno=d.deptno;

35、表连接的分类

        表连接分为两种：内连接和外连接

        内连接是结果集中只保留符合连接条件的记录

        外连接是不管符不符合连接条件，记录都要保留在结果集中

36、内连接

        内连接是常见的一种表连接，用于查询多张关系表符合连接条件的记录

        内连接的多种语法形式

        SELECT......FROM 表1 JOIN 表2 ON 连接条件；

        SELECT......FROM 表1 JOIN 表2 WHERE 连接条件；

        SELECT......FROM 表1 , 表2 WHERE 连接条件；

37、外连接

        外连接与内链接的区别在于，除了符合条件记录之外，结果集中还会保留不符合条件的记录

        外连接分为左外连接和右外连接，左外连接就是保留左表所有记录与右表做连接。如果右表有符合条件的记录就与左表连接，如果没有就用NULL与左表连接。右外连接也是如此

        外连接的形式

        SELECT......FROM 表1 LEFT(RIGHT) JOIN 表2 ON 连接条件；

38、外连接的注意事项

        内连接只保留符合条件的记录，所以查询条件在ON子句和WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来

39、子查询

        子查询是一种查询中嵌套查询的子句

        子查询可写在三个地方：WHERE子句、FROM子句、SELECT子句，但是中有FROM子句查询是最可取的

40、WHERE子查询和SELECT子查询属于相关子查询，相关子查询会反复多次执行，故应避免使用，FROM子查询只会执行一次，故查询效率很高

41、单行子查询和多行子查询

        单行子查询的结果集只有一条记录，多行子查询有多条记录

        多行子查询只能出现在WHERE子句和FROM子句中

42、WHERE子句中的多行子查询

        WHERE子句中，可使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果的条件判断