数据库学习——SQL之SELECT查询语句

SELECT的基本使用

SELECT 指定列/*
FROM 表名;

Tips:

一般情况下，最好不要使用通配符‘*’。

虽然*节省输入查询语句的时间，但降低查询和所使用的应用程序的效率。

通配符的优势是：当不知道所需要的列的名称时，可以通过它获取它们。

在生产环境下，不推荐你直接使用 SELECT * 进行查询。

列的别名

SELECT last_name AS name, commission_pct comm
FROM employees;
SELECT last_name "Name", salary*12 "Annual Salary" 
FROM employees;

重命名一个列，可（不）使用AS，可（不）使用双引号

去除重复行

SELECT DISTINCT depatment
FROM employees;

空值

• 所有运算符或者列值遇到null值，运算结果都是null
• MySQL中，空值!=字符串，空字符串的长度是0，控制的长度为空。MySQL中，空值占用空间。

着重号

#错误
SELECT * FROM ORDER;
#正确
SELECT * FROM `ORDER`;

使用着重号防止表中的字段、表名等和保留字、数据库系统或常用方法冲突

显示表的结构

DESCRIBE employees; 
或
DESC employees;
+----------------+-------------+------+-----+---------+-------+
| Field          |        Type | Null | Key | Default | Extra | 
+----------------+-------------+------+-----+---------+-------+
| employee_id    |      int(6) |   NO | PRI |       0 |       | 
| first_name     | varchar(20) |  YES |     |    NULL |       | 
+----------------+-------------+------+-----+---------+-------+

各字段的含义：

• Field：字段名称
• Type：字段类型
• Null：表示该列是否可以存储NULL值
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次
• Default：表示该列是否有默认值，如果有，那么值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

运算符

算术运算符

+ - * / %

mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+
1 row in set (0.01 sec)

mysql> select 1/2;
+--------+
| 1/2    |
+--------+
| 0.5000 |
+--------+
1 row in set (0.00 sec)

mysql> select '5a'+2;  -- a=1
+--------+
| '5a'+2 |
+--------+
|      7 |
+--------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'a5'+2; -- a=0
+--------+
| 'a5'+2 |
+--------+
|      2 |
+--------+
1 row in set, 1 warning (0.00 sec)

比较运算符

= !=/<> < > <= >=

• in或者not in

  in用于判断某个列的取值是否为指定的值，使用in运算符时指定的值是离散的数据，不是连续值

select * from tb_student where age in(18,28,15)
含义是 age=18 or age=28 or age=15
select * from tb_student where age not in(18,28,15)
含义是 age!=18 and age!=28 and age!=15

• between and

  用于判断数据是否在指定的范围内，连续值

select * from tb_student where score between 60 and 85;

• like/not like

  主要针对字符串类型数据进行模糊查询，通配符_和%

select * from tb_student where name not like '张%';

• is null/is not null

逻辑运算符

	语法	说明
&&或and	a and b或者 a && b	逻辑与，a和b都为true，则为true
||或者or	a || b或者 a or b	逻辑或，一个为true，则为true
not或者!	not a或者!a	逻辑非

mysql> SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL; 
+----------+---------+------------+------------+
| 1 AND -1 | 0 AND 1 | 0 AND NULL | 1 AND NULL | 
+----------+---------+------------+------------+
|        1 |       0 |          0 |       NULL | 
+----------+---------+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL; 
+---------+--------+-----------+-----------+--------------+
| 1 OR -1 | 1 OR 0 | 1 OR NULL | 0 || NULL | NULL || NULL | 
+---------+--------+-----------+-----------+--------------+
|       1 |      1 |         1 |      NULL |         NULL | 
+---------+--------+-----------+-----------+--------------+
1 row in set, 2 warnings (0.00 sec)

位运算符

& | ^ ~ >> <<

运算符的优先级

正则表达式查询

五类聚集函数

聚集函数用于对一个集合中的数据进行处理，不是一行一行的数据

count统计行数 sum求和 max最大值 min最小值 avg平均值

count计数

语法：count([all/distinct] 列名称)

select count(*) from tb_student; 

select count(1) from tb_student; 

select count(distinct dept) from tb_student; -- 获取系的个数 
mysql> select * from tb_student; 

select count(dept) from tb_student; -- 进行计数统计时，null不参与统计

统计值

用于数值类型的列，不要用于其他类型。max min sum avg

• max/min可以用于日期类型比较，最新的日期最大

mysql> select max(age) from tb_student; 
+----------+ 
| max(age) | 
+----------+ 
|       19 | 
+----------+ 
1 row in set (0.00 sec) 

mysql> select min(age) from tb_student;
+----------+ 
| min(age) | 
+----------+ 
|       16 |
+----------+ 
1 row in set (0.00 sec)

mysql> select avg(age) from tb_student; -- null值不参与计算，除非使用空值处理函数 将其转换为确定数值才可以 
+----------+ 
| avg(age) |
+----------+ 
|  16.7143 | 
+----------+ 
1 row in set (0.00 sec)

mysql> select sum(age),max(age),min(age),avg(age) from tb_student;

分组

group by

可以使用group by子句对查询结果进行分组处理，经常会使用聚集函数

• 如果不使用分组，聚集函数则用于处理所有查询结果数据

• 如果使用分组，则分别作用于各个分组查询的结果数据

获取男女生的平均年龄

mysql> select sex,avg(age) from tb_student group by sex; 
+------+----------+ 
| sex  | avg(age) | 
+------+----------+ 
|    1 |  17.0000 | 
|    0 |  16.0000 | 
| NULL |  16.0000 | 
+------+----------+ 
3 rows in set (0.00 sec)

having

可以对分组进行条件选择

需要获取人数多余2人的不同性别学生的平均年龄

• 按照性别分组

• 要统计的组必须人数多余2人，小于等于2人的分组不进行显示处理

select sex,avg(age) from tb_student group by sex having count(1)>2;

having和where之间的区别

只有满足条件的数据才会输出显示

最大区别在于：作用的对象不同

• where子句用于基表或者视图，从中选择满足条件的元组

• having子句用于分组，从多个分组中选择满足条件的分组

排序

ORDER BY

SELECT 指定列
FROM 表名
ORDER BY 某一列 ASC[升序]/DESC[降序]
#ORDER BY 在SELECT 语句的结尾
#多列排序，按顺序以此排列

分页

LIMIT[位置位移量] 行数

-- 前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;”返回的结果相同。