MySQL笔记02

MySQL笔记02

SQL的语言分类

DQL（Data Query Language）：数据查询语言
	select 
DML(Data Manipulate Language):数据操作语言
	insert 、update、delete
DDL（Data Define Languge）：数据定义语言
	create、drop、alter
TCL（Transaction Control Language）：事务控制语言
	commit、rollback

DQL语言的学习

进阶1：基础查询

语法：
SELECT 要查询的东西
【FROM 表名】;

类似于Java中 :System.out.println(要打印的东西);
特点：
	①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
	② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

#进阶1：基础查询
/*
语法：
select 查询列表 from 表名; (用鼠标选中再点执行)
类似于Java中的：System.out.println(打印的东西);
特点：
	1、查询列表可以是表中的字段、常量值、表达式、函数
	2、查询的结果是一个虚拟的表格
*/

#做查询前先打开指定的库名
USE myemployees;

#1、查询表中的单个字段
SELECT last_name FROM employees;
#2、查询表中的多个字段
SELECT last_name,salary,email FROM employees;
#3、查询表中的所有字段
#方式一：
SELECT 
	`first_name`,
	`last_name`,
	`email`,
	`phone_number`,
	`job_id`,
	`salary`,
	`commission_pct`,
	`manager_id`,
	`department_id`,
	`hiredate`
FROM
	employees;
#方式二：
SELECT * FROM employees;
#4、查询常量值
SELECT 100;
SELECT 'john';
#5、查询表达式
SELECT 100%98;
#6、查询函数
SELECT VERSION();

#7、为某个字段起别名
/*
a、便于理解
b、如果要查询的字段有重名的情况，使用别名可以区分开
*/
#方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
#方式二：使用空格
SELECT last_name 姓, first_name 名 FROM employees;
#案例：查询salary，显示结果为out put
SELECT salary AS "out put" FROM employees;

#8、去重
#案例：查询员工表中设计到的所有部门编号
SELECT DISTINCT department_id FROM employees;

#9、+号的作用
/*
Java中的+号
a、运算符，两个操作数都为数值型
b、连接符，只要有一个操作数为字符串

MySQL中的+号
仅仅只有一个功能：运算符

SELECT 100 + 90; 两个操作数都是数值型，则做加法运算
SELECT "123" + 90; 其中一方为字符型，视图将字符型数值转换成数值型，
			如果转换成功，则继续做加法运算，
SELECT "john" + 90;	如果转换失败，则将字符型数值转换成0
SELECT NULL + 90;	只要其中一方为NULL，则结果一定为NULL
*/
#案例：查询员工名和姓连接成一个字段，并显示为姓名
SELECT CONCAT("a", "b", "c") 结果;
SELECT 
	CONCAT(last_name, first_name) 姓名 
FROM 
	employees;

基础查询练习

#1. 下面的语句是否可以执行成功 （可以）
SELECT last_name , job_id , salary AS sal
FROM employees;

#2. 下面的语句是否可以执行成功 （可以）
SELECT * FROM employees;

#3. 找出下面语句中的错误
/*select employee_id , last_name，
salary * 12 “ANNUAL SALARY”
from employees;
*/
SELECT employee_id , last_name,
salary * 12 "ANNUAL SALARY"
FROM employees;

#4. 显示表 departments 的结构，并查询其中的全部数据
#a、显示表departments 的结构：
DESC departments;
#b、查询其中的全部数据：
SELECT * FROM `departments`;

#5. 显示出表 employees 中的全部 job_id（不能重复）
SELECT DISTINCT job_id FROM employees;

#6. 显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT
SELECT
	CONCAT(`first_name`, ',', `last_name`, ',', `commission_pct`) AS out_put
FROM
	employees;
#`commission_pct`字段中有部分值为NULL，与任何字段拼接都是NULL
#解决：使用IFNULL()函数
SELECT 
	IFNULL(`commission_pct`, 0) AS 奖金率,
	`commission_pct`
FROM
	employees;
	
SELECT
	CONCAT(`first_name`, ',', `last_name`, ',', IFNULL(commission_pct, 0)) AS out_put
FROM
	employees;

进阶2：条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据
语法：
select 
	要查询的字段|表达式|常量值|函数
from 
	表
where 
	条件 ;

分类：
一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>

二、逻辑表达式
示例：salary>10000 && salary<20000

逻辑运算符：

	and（&&）:两个条件如果同时成立，结果为true，否则为false
	or(||)：两个条件只要有一个成立，结果为true，否则为false
	not(!)：如果条件成立，则not后为false，否则为true

三、模糊查询
示例：last_name like 'a%'

条件查询练习

#进阶2：条件查询
/*
语法：
	SELECT
		查询列表
	FROM
		表名
	WHERE
		筛选条件;
分类：
	1、按条件表达式筛选
		条件运算符：> < = !=【<>】 >= <=
	2、按逻辑表达式筛选
		逻辑运算符：&& || ! 
		and or not
	3、模糊查询：
		like
		between and
		in
		is null
*/

#1、按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT
	*
FROM
	employees
WHERE
	salary > 12000;
#案例2：查询部门编号不等于90的员工名和部门编号

#2、按逻辑表达式筛选
#案例1：查询工资在10000到20000之间的员工名、工资以及奖金
SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary >= 10000 
AND 
	salary <= 20000;
#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT
	*
FROM
	employees
WHERE
	department_id >= 90
AND
	department_id <= 110
OR
	salary > 15000;

#模糊查询
/*
like
特点：
a、一般和通配符搭配使用
	通配符：
		%任意多个字符，包含0个
		_任意单个字符
between and
注意事项：
	a、可以提高语句简洁度 相当于>= && <=
	b、包含临界值
	c、两个临界值不能颠倒
in
判断某字段的值是否属于in列表中的某一项
特点：
	a、使用in提高语句简洁度
	b、in列表的值类型必须一致或兼容(可隐式转换)
	c、不支持通配符
is null | is not null
注意事项：
	=或<>不能用于判断null值
*/
#like
#案例1：查询员工名中包含字符a的员工信息
SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '%a%';
#案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT
	last_name,
	salary
FROM
	employees
WHERE
	last_name LIKE '__n_l%';
#案例3：查询员工名中第二个字符为_的员工名
SELECT
	last_name
FROM
	employees
WHERE
#	last_name like '_\_%';
#也可以使用ESCAPE转义
	last_name LIKE '_$_%' ESCAPE '$';

#between and
#案例1：查询员工编号在100到120之间的所有员工信息
SELECT
	*
FROM
	employees
WHERE
	employee_id BETWEEN 100 AND 120;

#in
#案例1：查询员工的工种编号是IT_PROG、AD_PRES、AD_VP中的一个员工名和工种编号
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN('IT_PROG', 'AD_PRES', 'AD_VP');
	
#is null
#案例1：查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;
#案例2：查询有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NOT NULL;

#安全等于<=>
#案例1：查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=> NULL;
#案例2：查询工资为12000的员工信息
SELECT
	*
FROM
	employees
WHERE
	salary <=> 12000;
/*
IS NULL 与 <=>

IS NULL:仅仅可以判断NULL值，可读性高，建议使用
<=>    :既可以判断NULL值，又可以判断普通数值，可读性低
*/

#练习1：查询员工号为176的员工的姓名和部门号和年薪
SELECT
	last_name,
	department_id,
	salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
	employees
WHERE
	employee_id = 176;

条件查询练习

#1、查询没有奖金，且工资小于18000的salary, last_name
SELECT
	salary,
	last_name
FROM
	employees
WHERE
	commission_pct IS NULL AND salary < 18000;

#2、查询employees表中，job_id不为‘IT’或者工资为12000的员工信息
SELECT
	*
FROM
	employees
WHERE
	job_id <> 'IT' OR salary = 12000;

#3、查看部门departments表的结构
DESC departments;

#4、查询部门departments表中涉及到了那些位置编号
SELECT
	location_id
FROM
	departments;
	
#5、经典面试题：
#试问：select * from employees ;
#和select * from employees where commission_pct like '%%' and last_name like '%%';
#结果是否一样？说明原因

#如果判断的字段有NULL值就不一样，没有就一样

查询练习

#1. 查询工资大于 12000 的员工姓名和工资
SELECT 
	last_name,
	salary
FROM
	employees
WHERE
	salary > 12000;

#2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT
	last_name,
	department_id,
	salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
	employees
WHERE
	employee_id = 176;

#3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT 
	last_name,
	salary
FROM
	employees
WHERE
	salary < 5000 OR salary > 12000;

#4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT
	last_name,
	department_id
FROM
	employees
WHERE
	department_id = 20 OR department_id = 50;

#5. 选择公司中没有管理者的员工姓名及 job_id
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	manager_id IS NULL;

#6. 选择公司中有奖金的员工姓名，工资和奖金级别
SELECT 
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NOT NULL;

#7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT
	CONCAT(last_name, first_name) AS 姓名
FROM
	employees
WHERE
	CONCAT(last_name, first_name) LIKE '__a%';


#8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT
	last_name
FROM 
	employees
WHERE
	last_name LIKE '%a%' OR last_name LIKE '%e%';
	
#9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT 
	*
FROM 
	employees
WHERE
	first_name LIKE '%e';

#10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT
	CONCAT(last_name, first_name) AS 姓名,
	job_id AS 职位
FROM 
	employees
WHERE
	department_id BETWEEN 80 AND 100;

#11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT
	last_name,
	job_id AS 职位
FROM 
	employees
WHERE
	manager_id = 100 OR manager_id = 101 OR manager_id = 110;