linux之MySQL查询和常用函数

MySQL day02

客户端的使用

Navicat的使用

1. 建立连接

2. 内置数据库简介

   注意：MySQL用户下，是数据库，数据库中保存表。

3. 双击打开某一个数据库

4. 执行sql

导入测试数据

为方便学习我们导入测试数据，此时我们新建数据库，将数据添加到数据库中即可！！！

1. 新建数据库

2. 导入sql

数据库的一些概念

1. 表

   表是数据库组织、存储数据的基本单位，表中保存着多行多列的数据。不同类型的数据保存到不同的表中，比如:用户信息保存在user表中，学员信息保存在student表中，员工信息保存在employees表中。

2. 行

   行是表中存储数据的基本单位，一行数据通常包含多个字段（列）。向表中添加数据，再少要添加一行。

3. 字段（列）

   一个字段用来描述一行数据的某个特征，是一行数据的组成部分。比如：用户要包含用户名、密码等内容，user表中就有用户名、密码这些字段（列）。

4. 主键

   本质上就是特殊的字段：用来在一张表中唯一的标识一行数据的字段。主键要求：唯一、不能为null

5. 外键

   也是一个特殊的字段：用来记录表和表之间关系的列，比如：员工一定有其所属的部门，那么employees员工表和departments部门表就存在关系，为了记录员工的部门，就可以在员工表中定义一列department_id保存自己所属部门的id（对应着departments中的department_id主键），这样员工表department_id就记录了员工和部门的关系。employees表的 employee_id就是一个外键。

6. 数据库

   为了方便对表管理，MySQL中通过库管理表，也就是说表又存在于数据库中。通常一个项目对应一个库。

         

查询SQL

SQL（Structured Query Language，结构化查询语言）， 是用于访问和处理数据库的标准的计算机语言，核心功能是提供了对表中数据增、删、改、查操作。

需要注意的是：SQL是一种标准，并不为哪个数据库独有。所有的关系型数据库都支持SQL，并在标准语法外提供了各自的私有扩展（不用过于担心私有扩展带来的碎片化和学习成本的增加，私有扩展只是很少的一部分）。绝大多数的SQL语句可以不用修改的在任何数据库正确运行。

简单的查询

1. 查询部分列

   语法：select 列名1,列名2,...
        from 表名;
   示例：
   select employee_id,first_name,email,salary
   from employees;

2. 查询所有列

   语法：select *
         from 表名;
   示例：
   select *
   from employees;
   ​
   另外一种查询所有列：
   select 列名1,列名2,...，最后一个列名 
   from 表名;
   实战开发时推荐，显式写出列名的方式：可读性好,易于维护

3. 数学运算

   语法：
   select 数字列名+数字,数字列名-数字,数字列名*数字,数字列名/数字,数字列名%数字
   from 表名;
   示例：
   select salary,salary+100,salary-100,salary*12,salary/30
   from employees;

4. 别名

   select 列名 as 别名,列名2 as 别名2
   from 表名;
   示例：
   select salary as 月薪,salary*12 as 年薪,salary/30 as 日薪
   from employees as e;
   ​
   注意：列名起别名时，as可以省略
   示例：
   select salary as 月薪,salary*12 as 年薪,salary/30 as 日薪
   from employees as e;

5. 去重

   语法：
   select distinct 列名1,列名2,...
   from 表名;
   示例：
   select distinct job_id,department_id 
   from employees;
   注意：查询出的所有列相同才算相同

6. 查询表详情

   查询一个表的信息：
   describe 表名; 
   示例：describe employees;
   注意：describe 可以简写为 desc

条件查询

单条件查询

语法：
select 列名,...
from 表名
where 条件;
​
比较运算符： >  <  >=  <=   =   != 
示例：查询薪资大于等于8000的员工
select * 
from employees
where salary >= 8000;
​
--查询薪资小于等于15000的员工
select *
from employees
where employee_id <= 150;
​
​
注意：mysql针对字符串比较时默认不区分大小写，如要区分大小写添加关键字binary
select *
from employees
where first_name = 'steven';
​
select *
from employees
where binary first_name = 'steven';

多条件查询

语法：使用and或者or连接多个条件
and 连接条件，连接的所有条件必须同时成立
or  连接条件，连接的条件任意一个成立即可以
​
示例：查询薪资大于等于8000且薪资小于等于15000的员工
select * 
from employees
where salary >= 8000 and salary <= 15000;
​
--查询薪资大于等于8000且员工编号大于等于170的员工
select * 
from employees
where salary >= 8000 or employee_id >= 170

区间查询

语法 between 起始值 and 截止值
示例：查询薪资在8000~15000范围的数据
select * 
from employees
where salary >= 8000 and salary <= 15000;
--改造为范围查询：
select * 
from employees
where salary between 8000 and 15000;

查询薪资不在8000~15000之间的数据
select *
from employees
where salary < 8000 or salary > 15000;

select *
from employees
where salary not between 8000 and 15000;

枚举查询

语法：列 in(值1，值2，值3，...)
--查询 60 90 100 部门的员工
select *
from employees
where department_id = 60
or department_id = 90
or department_id = 100;

select * 
from employees
where department_id in (60,90,100);

--不是 60 90 100 部门的员工
select *
from employees
where department_id not in (60,90,100);

case when

语法：
case
       when 条件1 then 结果1
         when 条件2 then 结果2
         when 条件3 then 结果3
           ...
         else 其他结果  
     end 
类似于java中if（条件）{结果}else

示例：
select salary,
         case 
            when salary >= 15000 then '高薪'
            when salary >= 10000 then '中等'
            when salary >= 7000 then '一般'
            else '很低'
         end 薪资等级
from employees;

空值查询

--查询没有提成的员工
select *
from employees
where commission_pct is null;

--查询有提成的员工
select *
from employees
where commission_pct is not null;

注意：null的比较要使用is 

模糊查询

语法： select *
from 表名
where 列名 like '匹配模式';
匹配模式的构成：
 % 表示n个任意的字符
 _ 表示1个任意的字符
 
--查询first_name 以A开头的员工
select *
from employees
where first_name like 'A%';

--查询first_name 包含a的员工
select *
from employees
where first_name like '%a%';

--查询first_name第3个字符为a的员工
select *
from employees
where first_name like '__a%';
--注意：示例中使用的是2个下划线

排序

在查询数据时，通常需要根据某些列的值进行排序显示。

语法：

select 列名

  from 表名

  where 条件

  order by 列名  [asc(默认值，升序)|desc(降序)]；

单列排序

--根据薪资排序 
select *
from employees
order by salary asc ;

select *
from employees
order by salary desc;

--条件查询和排序联合使用
--对salary>=8000的员工按照薪资降序排列
select *
from employees
where salary >= 8000
order by salary desc;

多列排序

语法：
select 列名,...
from 表名
where 条件
order by 列名1 排序规则，列名2 排序规则,... 

排序效果：整体先根据列1进行排序，如果列1值相同时，再根据列2值排序，以此类推。
示例：
--先根据薪资降序排列，然后薪资相同根据id升序排列
select * 
from employees
order by salary desc,employee_id asc;

函数

MySQL内置了很多函数，提供了特定的功能。

单行函数

单行函数：作用到表中的每一行数据，表中有多少行，就得到多少行新结果。

concat(列名,...)

concat()用于拼接多列的值

select concat(first_name,last_name) 姓名
from employees;

select concat(first_name,'-',last_name) 姓名
from employees;

mod(m,n)

mod()等同于数学运算中的%，标准的SQL中并不支持直接使用%，使用mod()更有通用性。

select mod(5,2)
from dual; 
相当于Java中的5%2

length(数据)

length()获取数据的长度。

select first_name,length(first_name)
from employees;

select *
from employees
where length(first_name) = 6;

now() sysdate()

now()和sysdate()用于获取系统时间。

select now(),sysdate()
from employees;

注意：now()和sysdate()后可以省略from表
select now(),sysdate();

str_to_date(str,format)

str_to_date将日期格式的字符串转换为日期数据。

日期格式： 
	%Y：代表4位的年份 
	%y：代表2为的年份

	%m：代表月, 格式为(01……12) 
	%c：代表月, 格式为(1……12)

	%d：代表月份中的天数,格式为(00……31) 
	%e：代表月份中的天数, 格式为(0……31)

    %H：代表小时,格式为(00……23) 
    %k：代表 小时,格式为(0……23) 

	%i： 代表分钟, 格式为(00……59) 

    %s：代表 秒,格式为(00……59)
--将'2020-01-06 10:20:30' 字符串转换为相应的日期数据
SELECT str_to_date('2020-01-06 10:20:30','%Y-%m-%d %H:%i:%s')

date_format(date,format)

date_format(date,format)将日期和时间数据转换为指定的字符串。

--将系统时间按照 年-月-日 时:分:秒 的格式输出字符串
SELECT date_format(now(),'%Y-%m-%d %H:%i:%s');

组（多行）函数

组函数：作用到一组（多行）数据，每组数据得到一个结果。一张表没有显式分组前，默认当做一个组处理。

1. sum(列)

   获取某一列的总和

2. avg(列)

   获取某一列的平均值

3. max(列)

   获取某一列的最大值

4. min(列)

   获取某一列的最小值

5. count(列)

   获取某一列有值（会忽略null）的个数

select sum(salary),
	avg(salary),
	max(salary),
	min(salary),
	count(salary),
	count(commission_pct),
	count(*)
from employees;

注意：
通常使用count(*)统计表中数据的行数。