第四十一篇 Mysql单表查询规范

最新推荐文章于 2024-10-01 17:33:05 发布

Laughing@me

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量143

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_45503700/article/details/103537992

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

心得：一点一点的进步，不说别的至少我很充实，享受这种向目标一步一步靠近的感觉～

数据库基本的基本指令已经掌握，这一篇介绍表单查寻的各种规范语法。

1.先创建表和数据

#创建表
create table employee(
	id int not null primary key auto_increment,
	name char(20) not null,
	age int not null,
	sex enum("male","female") not null default "male",
	salary float unsigned,
	department char(20)
);

desc employee;

insert into employee values
	(null,"tom","26","female","33000","QA"),
	(null,"alex","18","male","3000","HR"),
	(null,"boy","45","male","5000","HR"),
	(null,"cily","66","female","80000","QA"),
	(null,"will","78","male","6500","IT"),
	(null,"tian","18","male","3000","IT");

select * from employee;
#############################################
mysql> select * from employee;
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  1 | tom  |  26 | female |  33000 | QA         |
|  2 | alex |  18 | male   |   3000 | HR         |
|  3 | boy  |  45 | male   |   5000 | HR         |
|  4 | cily |  66 | female |  80000 | QA         |
|  5 | will |  78 | male   |   6500 | IT         |
|  6 | tian |  18 | male   |   3000 | IT         |
+----+------+-----+--------+--------+------------+
6 rows in set (0.00 sec)

mysql>

2.基本查询语法：

2.1 直接查询全部内容
select 字段 from 表;

2.2 避免重复使用distinct字符，加在字段前
select distinct department from employee;

select distinct age from employee;
+-----+
| age |
+-----+
|  26 |
|  18 |
|  45 |
|  66 |
|  78 |
+-----+
5 rows in set (0.01 sec)

2.3 可以使用四则运算
select name salary*12 as “年薪” from employee;

select name,salary*12 as "年薪" from employee;
+------+--------+
| name | 年薪   |
+------+--------+
| tom  | 396000 |
| alex |  36000 |
| boy  |  60000 |
| cily | 960000 |
| will |  78000 |
| tian |  36000 |
+------+--------+

2.4 定义显示格式

select concat("名字:",name,"  年龄:",age) from employee;
+----------------------------------------+
| concat("名字:",name,"  年龄:",age)     |
+----------------------------------------+
| 名字:tom  年龄:26                      |
| 名字:alex  年龄:18                     |
| 名字:boy  年龄:45                      |
| 名字:cily  年龄:66                     |
| 名字:will  年龄:78                     |
| 名字:tian  年龄:18                     |
+----------------------------------------+
6 rows in set (0.00 sec)

3.where 限定使用

3.1. 比较运算符：> < >= <= <> !=

select * from employee where age>30 and sex="male";
+----+------+-----+------+--------+------------+
| id | name | age | sex  | salary | department |
+----+------+-----+------+--------+------------+
|  3 | boy  |  45 | male |   5000 | HR         |
|  5 | will |  78 | male |   6500 | IT         |
+----+------+-----+------+--------+------------+

3.2. between 80 and 100 值在10到20之间,是一个闭合的空间，多用于数值区间。

mysql> select * from employee where age between 30 and 80;
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  3 | boy  |  45 | male   |   5000 | HR         |
|  4 | cily |  66 | female |  80000 | QA         |
|  5 | will |  78 | male   |   6500 | IT         |
+----+------+-----+--------+--------+------------+
3 rows in set (0.00 sec)

#加了not 取反
mysql> select * from employee where age not between 30 and 80;
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  1 | tom  |  26 | female |  33000 | QA         |
|  2 | alex |  18 | male   |   3000 | HR         |
|  6 | tian |  18 | male   |   3000 | IT         |
+----+------+-----+--------+--------+------------+
3 rows in set (0.00 sec)

3.3. in(80,90,100) 值是10或20或30

select * from employee where age in (18,66,26);
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  1 | tom  |  26 | female |  33000 | QA         |
|  2 | alex |  18 | male   |   3000 | HR         |
|  4 | cily |  66 | female |  80000 | QA         |
|  6 | tian |  18 | male   |   3000 | IT         |
+----+------+-----+--------+--------+------------+

3.4. like 'egon%'进行模糊匹配
pattern可以是%或_，
%表示任意多字符
_表示一个字符

select * from employee where sex like "fe%";
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  1 | tom  |  26 | female |  33000 | QA         |
|  4 | cily |  66 | female |  80000 | QA         |
+----+------+-----+--------+--------+------------+

3.5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
and 的优先级高于or

select * from employee where age >10 and age<50 or salary>10000;
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  1 | tom  |  26 | female |  33000 | QA         |
|  2 | alex |  18 | male   |   3000 | HR         |
|  3 | boy  |  45 | male   |   5000 | HR         |
|  4 | cily |  66 | female |  80000 | QA         |
|  6 | tian |  18 | male   |   3000 | IT         |
+----+------+-----+--------+--------+------------+

4. 分组查选 group by

分组：将所有的记录按照某个相同字段进行归类

分组语法在where语法之后发生，分组后可以统计每一组的相关信息
select查询的字段只能是分组的字段，想要获取组内的其他相关信息，需要借助函数

4.1 分组设置：

在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。我们需要设置为这个模式，省去了不必要的麻烦。设置完必须退出。

#系统默认设置
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#去掉ONLU_FULL_GROUP_BY模式
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

#只设置ONLY_FULL_GROUP_BY模式
set global sql_mode='ONLY_FULL_GROUP_BY';

4.2 group by和group_concat()函数使用

select department,group_concat(name) from employee group by department;
+------------+--------------------+
| department | group_concat(name) |
+------------+--------------------+
| HR         | alex,boy           |
| IT         | will,tian          |
| QA         | tom,cily           |
+------------+--------------------+

4.3 group by和聚合函数一起使用
查看每组人数

select department,count(id) from employee group by department;
+------------+-----------+
| department | count(id) |
+------------+-----------+
| HR         |         2 |
| IT         |         2 |
| QA         |         2 |
+------------+-----------+

4.4 聚合函数
聚合函数在没有分组的情况下，默认列表的全部为一组
max()
min()
avg()
sum()
count()

select department,max(salary) from employee group by department;
select department,min(salary) from employee group by department;
select department,avg(salary) from employee group by department;
select department,sum(salary) from employee group by department;
select department,count(id) from employee group by department;
##########################################
mysql> select department,max(salary) from employee group by department;
+------------+-------------+
| department | max(salary) |
+------------+-------------+
| HR         |        5000 |
| IT         |        6500 |
| QA         |       80000 |
+------------+-------------+
3 rows in set (0.00 sec)

mysql> select department,min(salary) from employee group by department;
+------------+-------------+
| department | min(salary) |
+------------+-------------+
| HR         |        3000 |
| IT         |        3000 |
| QA         |       33000 |
+------------+-------------+
3 rows in set (0.00 sec)

mysql> select department,avg(salary) from employee group by department;
+------------+-------------+
| department | avg(salary) |
+------------+-------------+
| HR         |        4000 |
| IT         |        4750 |
| QA         |       56500 |
+------------+-------------+
3 rows in set (0.00 sec)

mysql> select department,sum(salary) from employee group by department;
+------------+-------------+
| department | sum(salary) |
+------------+-------------+
| HR         |        8000 |
| IT         |        9500 |
| QA         |      113000 |
+------------+-------------+
3 rows in set (0.00 sec)

mysql> select department,count(id) from employee group by department;
+------------+-----------+
| department | count(id) |
+------------+-----------+
| HR         |         2 |
| IT         |         2 |
| QA         |         2 |
+------------+-----------+
3 rows in set (0.00 sec)

5 having过滤

执行优先级从高到低：where > group by > having

having和where的用法一致，都可以使用运算符和运算逻辑，但having是发生在分组之后再进行过滤。所以它可以使用group by中定义的聚合函数。

mysql> select department,group_concat(salary) from employee group by department; 
+------------+----------------------+
| department | group_concat(salary) |
+------------+----------------------+
| HR         | 3000,5000            |
| IT         | 6500,3000            |
| QA         | 33000,80000          |
+------------+----------------------+
3 rows in set (0.00 sec)

#having中使用聚合函数
mysql> select department,group_concat(salary) from employee group by department having avg(salary)>5000;
+------------+----------------------+
| department | group_concat(salary) |
+------------+----------------------+
| QA         | 33000,80000          |
+------------+----------------------+

6.排选查询

默认升序：
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
设置降序：
SELECT * FROM employee ORDER BY salary DESC;

mysql> select * from employee order by age desc,id asc;
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  5 | will |  78 | male   |   6500 | IT         |
|  4 | cily |  66 | female |  80000 | QA         |
|  3 | boy  |  45 | male   |   5000 | HR         |
|  1 | tom  |  26 | female |  33000 | QA         |
|  2 | alex |  18 | male   |   3000 | HR         |
|  6 | tian |  18 | male   |   3000 | IT         |
+----+------+-----+--------+--------+------------+

7.限制查询记录limit

mysql> select * from employee limit 3;
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  1 | tom  |  26 | female |  33000 | QA         |
|  2 | alex |  18 | male   |   3000 | HR         |
|  3 | boy  |  45 | male   |   5000 | HR         |
+----+------+-----+--------+--------+------------+
3 rows in set (0.00 sec)

#从第三个数开始查询，0开始
mysql> select * from employee limit 2,3;
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  3 | boy  |  45 | male   |   5000 | HR         |
|  4 | cily |  66 | female |  80000 | QA         |
|  5 | will |  78 | male   |   6500 | IT         |
+----+------+-----+--------+--------+------------+
3 rows in set (0.00 sec)

8. 正则查询

类似于python和shell语法模式

mysql> select * from employee where name regexp '^a.*';
+----+------+-----+------+--------+------------+
| id | name | age | sex  | salary | department |
+----+------+-----+------+--------+------------+
|  2 | alex |  18 | male |   3000 | HR         |
+----+------+-----+------+--------+------------+
1 row in set (0.00 sec)

mysql> 
mysql> select * from employee where name regexp '.*o{1}.*';
+----+------+-----+--------+--------+------------+
| id | name | age | sex    | salary | department |
+----+------+-----+--------+--------+------------+
|  1 | tom  |  26 | female |  33000 | QA         |
|  3 | boy  |  45 | male   |   5000 | HR         |
+----+------+-----+--------+--------+------------+