查询操作练习
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 ’ ′ ; 这 句 话 是 让 '; 这句话是 让 ′;这句话是让 拥有转义字符的形式和功能
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 排序的字段;
- 升序 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;