查询操作练习

查询操作练习
1、导入数据

2、数据sql文件

3、了解表结构

4、基础查询
#单行注释
/*
多行注释
*/
– 单行注释

#基础查询

/*
select:查看、选择、显示
语法：
select 要显示的东西
类似于 System.out.println(要打印的东西);

要显示的东西：
①常量
②表达式
③函数
④字段，但要显示字段必须添加from 表名
语法：
select 字段 from 表;

*/
#1.显示常量
SELECT 100;
SELECT ‘john’;

#2.显示表达式
SELECT 98*23;

#3.显示函数(方法)
SELECT LENGTH(‘john’);
SELECT VERSION();

#4.显示表中的字段
SELECT last_name FROM employees;

#5.显示表中的多个字段
SELECT last_name,phone_number,first_name,salary,commission_pct,manager_id
FROM employees;

#6.显示表中所有字段
SELECT * FROM employees;

#7.起别名
#方式一：
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二：
SELECT last_name 姓,first_name 名 FROM employees;

#示例：别名：out name
SELECT last_name “out name” FROM employees;

#8.如何去重
#查询employees表中的所有部门编号
– distinct
SELECT DISTINCT department_id FROM employees;

5、条件查询
#条件查询
/*
概念：根据指定的条件过滤出数据
语法：
select 字段 from 表名
where 条件;

分类：
1、按条件表达式查询
条件运算符：

、< 、>=、 <=、 =、 !=、 <>、 ，<=>安全等于，用于判断是否为null

2、按逻辑表达式查询
逻辑运算符：
and、&& 两个条件都为true，结果为true，反之为false
or、|| 两个条件只要有一个为true，结果为true，反之为false
not、! 如果连接的条件本身为false，结果为true，反之为false

//优先级
not > and > or

3、模糊查询：
*/

#一、按条件表达式查询

#案例1：查询月薪>5000的员工信息
SELECT * FROM employees WHERE salary>5000;

#案例2：查询月薪=12000的员工信息
SELECT * FROM employees WHERE salary=12000;

#案例 查询月薪不等于12000的员工信息
SELECT * FROM employees WHERE salary<>12000;

#案例3：查询没有奖金的员工名
SELECT last_name,commission_pct FROM employees
WHERE commission_pct<=>NULL;

#二、按逻辑表达式查询

#案例1：查询月薪在5000到12000的员工工资和姓名
SELECT salary,last_name FROM employees WHERE salary>= 5000 AND salary<=12000;

#案例2：查询月薪不在5000到12000的员工工资和姓名
SELECT salary,last_name FROM employees WHERE NOT(salary>= 5000 AND salary<=12000);

#案例3：查询部门编号=90 或 月薪>10000并且月薪<15000的员工信息
SELECT * FROM employees WHERE department_id = 90 OR (salary>10000 AND salary<15000);

– ceil rand
SELECT CEIL(RAND()*30);

6、基础查询练习
#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;

#4. 显示表departments的结构，并查询其中的全部数据 desc 表名; #显示结构
DESC departments;
SELECT * FROM departments;

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

#6.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
SELECT CONCAT(last_name,’,’,first_name,’,’,email) out_put FROM employees;

/*
+的功能：做运算
特点：
①如果一方为字符型，则试图将字符型转换为数值型，如果转换成功，则继续运算
如果转换失败，则显示为0
②如果一方为null，则结果肯定为null
③如果两方都为数值型，则直接运算
select 10+19; #29
select ‘123’+100; #223
select ‘john’+89; #89
select null+‘1234’;

*/

#7.显示出表employees部门编号在80-100之间 的姓名、职位
SELECT last_name,job_id,department_id FROM employees
WHERE department_id>=80 AND department_id<=100;

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

7、模糊查询

模糊查询

/*
1.like
一般搭配着通配符使用，用作字符型的判断
通配符：
% 任意多个字符
_ 任意单个字符 1

2.between and
特点：在…之间
①等价于 使用 字段>=值1 and 字段<=值2
②两个临界值是包含的 [1,10]
③两个临界值不能颠倒顺序

3.in
特点：判断某个字段的值是否在某个列表内
in(值1，值2，…)

等价于 or

4.is null/is not null
*/

#1.like

#案例1：查询姓名中包含字符 e的员工信息
SELECT * FROM employees
WHERE last_name LIKE ‘%e%’;

#案例2：查询姓名中第二个字符为e，第四个字符为a的员工信息
SELECT * FROM employees
WHERE last_name LIKE ‘_e_a%’;

#案例3：查询姓名中第三个字符为_的员工信息
SELECT * FROM employees
WHERE last_name LIKE ‘___%’;

// 斜线 \ -> window 斜线
/ -> Linux 斜线
// 反斜线

#ESCAPE 自定义 转移符
SELECT * FROM employees
WHERE last_name LIKE ‘__KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '’;

// ESCAPE ’${}^{\prime };这句话是让$ 拥有转义字符的形式和功能

UPDATE employees SET last_name=’’ ‘’ WHERE employee_id=100;

#2.between and

#案例1：显示出表employees部门编号在80-100之间 的姓名、职位
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

WHERE department_id BETWEEN 100 AND 80; ×

#等价于
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id<=100 AND department_id>=80;

#3.in
#案例1：显示出表employees的manager_id 是 100,101,110 的员工姓名、职位

SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id IN(100,101,110);

#等价于
SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id=100 OR manager_id=101 OR manager_id = 110;

#案例2：查询 job_id 为AD_VP或ST_MAN或SA_MAN
SELECT last_name,job_id,manager_id
FROM employees
WHERE job_id IN(‘AD_VP’,‘S__MAN’);# ×

#4.is null / is not null

#案例1：查询没有奖金的员工
SELECT * FROM employees
WHERE commission_pct IS NULL;

#案例2：查询有奖金的员工
SELECT * FROM employees
WHERE commission_pct IS NOT NULL;

SELECT * FROM employees
WHERE NOT(commission_pct IS NULL);

SELECT * FROM employees WHERE last_name IS NULL;

8、模糊查询练习

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

#2.查询员工号为176的员工的姓名和部门号和年薪
SELECT IFNULL(commission_pct,0),commission_pct FROM employees;

SELECT last_name,department_id,salary12(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE employee_id=176;

#3.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
WHERE NOT(salary BETWEEN 5000 AND 12000);

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

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

or

#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 last_name FROM employees
WHERE last_name LIKE ‘__a%’;

#8.选择姓名中有字母a和e的员工姓名
SELECT last_name FROM employees
WHERE last_name LIKE ‘%a%’ AND last_name LIKE ‘%e%’;
#或
SELECT last_name FROM employees
WHERE last_name LIKE ‘%a%e%’ OR last_name LIKE ‘%e%a%’;

#9.显示出表employees表中 first_name 以 'e’结尾的员工信息
SELECT first_name FROM employees
WHERE first_name LIKE ‘%e’;

#案例：
SELECT last_name,phone_number
FROM employees
WHERE email IS NULL;

#常见错误
#1.表名不存在

TABLE ‘myemployees.employee’ doesn’t exist

#2.列名错误

Unknown column ‘phone_numbe’ in ‘FIELD LIST’

#3.语法错误

You have an error in your SQL syntax; check the manual

that corresponds to your MySQL server version

for the right syntax to use near 'FROM employees

Select
From
Where
Group by
Having
Order by
limit

9、排序查询
#排序查询
/*
语法：
select 字段|表达式|常量
from 表
【where 条件】
order by 排序的字段;

1. 升序 asc
   降序 desc
   默认是 升序asc

2.可以按表达式、函数、别名、多个字段进行排序
3.一般来讲order by放到查询的语句的最后，在不使用分页的情况下
*/

#一、按单个字段进行排序
SELECT * FROM employees ORDER BY salary ;
SELECT * FROM employees ORDER BY salary DESC;

#二、按多个字段进行排序
SELECT * FROM employees
ORDER BY salary DESC,employee_id ASC;

#三、按表达式排序
#案例：按年薪降序
SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary12(1+IFNULL(commission_pct,0)) DESC;

#四、按别名排序
#案例：按年薪降序
SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;

#五、按函数排序
#案例：按姓名中的字节长度大小降序
SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY LENGTH(last_name) DESC;

10、排序查询练习
大家好，刚刚我们学习了排序查询，现在，我们来做一个练习
#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,department_id,salary12(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name;

#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees
WHERE email LIKE ‘%e%’
ORDER BY LENGTH(email) DESC,department_id ASC;

11、查询练习

#一、显示当前连接的所有数据库名称
SHOW DATABASES;
SELECT DATABASE();

#二、查询employees表中的所有数据，并按年薪升序
SELECT * FROM employees
ORDER BY salary*12+(1+IFNULL(commission_pct,0)) ASC;

#三、查询employee表中 ，部门编号在80到100之间的的last_name,employee_id 信息
SELECT last_name,employee_id
FROM employees WHERE department_id>=80 AND department_id<=100;

SELECT last_name,employee_id
FROM employees WHERE department_id BETWEEN 80 AND 100;

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

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