MySQL之CRUD

 

一、基础查询

 

语法：
select 查询列表 form 表名；

特点：
(1)查询列表可以是：表中的字段、常量值、表达式、函数
(2)查询的结果是一个虚拟的表格

1.1查询表中的单个字段
select last_name from t_mysql_employees;

1.2查询表中的多个字段
select last_name,salary,email from t_mysql_employees;

1.3查询表中的所有字段
select * from t_mysql_employees;

1.4 查询常量值
select 100+1 as name;

1.5查询表达式
select 100%90;

1.6查询函数
select version();

1.7起别名：使用空格
select last_name 姓，first_name 名 from t_mysql_employees;

1.8去重：查询员工表中涉及到的所有的部门编号
select distinct department_id from t_mysql_employees;

1.9 +号的作用
Java中的+号
1.运算符，两个操作数都为数值型
2.只要有一个操作数为字符串

MySQL中的+号
仅仅只有一个功能：运算符
1.select 100+90; 两个操作数都为数值型，则作加法运算
2.select '123'+90; 只要其中一方为字符型，试图将字符型数值转换成数值型

二、过滤和排序数据

2.1过滤查询案例语法
select 查询列表 from 表名 where 筛选条件；

2.2 按逻辑表达式筛选
逻辑运算符：
作用：用于连接条件表达式
&& || !
and or not

&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false

示例：查询工资z在10000到20000之间的员工名、工资以及奖金
select last_name,salary,commission_pct FROM t_mysql_employees
where salary>=10000 and salary<=20000;

示例：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
select * from t_mysql_employees where
not(department_id>=90 AND department_id<=110) or salary>15000;

2.3模糊查询
like、between、and、in、is、null

2.3.1：like：
特点：
一般和通配符搭配使用
通配符：
% 任意多个字符,包含0个字符
_ 任意单个字符

示例：查询员工名中包含字符a的员工信息
select * from employees where last_name like '%a%';#abc

示例：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
select last_name,salary FROM t_mysql_employees WHERE last_name LIKE '__n_l%';

示例：查询员工名中第二个字符为_的员工名
select last_name FROM t_mysql_employees where last_name LIKE '_$_%' ESCAPE '$';

2.3.2：between、and
特点：
使用between and 可以提高语句的简洁度
包含临界值、两个临界值不要调换顺序

示例：查询员工编号在100到120之间的员工信息
between:
select * from t_mysql_employees where employee_id between 100 and 120;

and:
select * from t_mysql_employees where employee_id <= 120 and employee_id>=100;

2.3.3:in
含义：判断某字段的值是否属于in列表中的某一项
特点：
使用in提高语句简洁度
in列表的值类型必须一致或兼容
in列表中不支持通配符

示例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
select last_name,job_id from t_mysql_employees where
job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';

2.3.4:is、null

特点：=或<>不能用于判断null值
is null或is not null 可以判断null值

示例：查询没有奖金的员工名和奖金率
select last_name, commission_pct from t_mysql_employees where
commission_pct is null;

2.3.5:安全等于<=>
示例：查询没有奖金的员工名和奖金率
select last_name,commission_pct from t_mysql_employees where
commission_pct <=>NULL;

示例：查询工资为12000的员工信息
select last_name,salary from t_mysql_employees where salary <=> 12000;

is、null、<=>
is null:仅仅可以判断NULL值，可读性较高，建议使用
<=>    :既可以判断NULL值，又可以判断普通的数值，可读性较低

三、order、by 子句

排序查询
语法：
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;

特点：
(1)asc代表的是升序、可以省略，desc代表的是降序

(2)order by子句可以支持 单个字段、别名、表达式、函数、多个字段

(3) order by子句在查询语句的最后面，除了limit子句

3.1 示例：按单个字段排序
select * from t_mysql_employees order by salary desc;

3.2 添加筛选条件再排序
示例：查询部门编号>=90的员工信息，并按员工编号降序
select * from t_mysql_employees where department_id>=90 order by employee_id desc

3.3按表达式排序
示例：查询员工信息 按年薪降序
select *,salary*12*(1+IFNULL(commission_pct,0)) from t_mysql_employees
order by salary*12*(1+IFNULL(commission_pct,0)) desc;

3.4按别名排序
示例：查询员工信息 按年薪升序
select *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 from t_mysql_employees order by 年薪 asc;

3.5按函数排序
示例：查询员工名，并且按名字的长度降序
select LENGTH(last_name),last_name from t_mysql_employees order by LENGTH(last_name) desc;

3.6排序练习:
示例：查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
from t_mysql_employees order by 年薪 DESC,last_name asc;

示例：选择工资不在8000到17000的员工的姓名和工资，按工资降序
select last_name,salary from t_mysql_employees where salary not BETWEEN 8000 AND 17000
order by salary desc;
 

四、分组查询

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点：
1、sum、avg一般用于处理数值型
  max、min、count可以处理任何类型
2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍
一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

4.1、简单 的使用
select sum(salary) from t_mysql_employees;
select avg(salary) from t_mysql_employees;
select min(salary) from t_mysql_employees;
select max(salary) from t_mysql_employees;
select count(salary) from t_mysql_employees;

select SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
from t_mysql_employees;

4.2分组函数练习

4.2.1查询公司员工工资的最大值，最小值，平均值，总和
select MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和
from t_mysql_employees;

4.2.2查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
1.
select MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
from t_mysql_employees;

2.
select DATEDIFF('1995-2-7','1995-2-6');

4.2.3查询部门编号为90的员工个数
select count(*) from t_mysql_employees where department_id = 90;
 

okk……