mysql基础2

1、基础查询

/*
演示基础查询
select开头 查询的内容 from 表或者结果集查询结果集。
select 查询的结果是一个虚拟的表格，保存在客户端的内存中，这个虚拟的表叫做
*/
#查询1：查询常量
SELECT '110';
#查询数值型，直接写值，不需要写单引号或者双引号。加上单引号也行。
SELECT '管理员';
#查询字符型和日期型，必须写单引号或者双引号。

#查询2：查询表达式运行结果
SELECT 999+110;
#如果+两端都是数值那么就直接相加，结果集中是结果。
SELECT 999+'abc';
#如果+两端有一端是字符，那么会首先将字符转成数值，如果转换成功则相加，并返回结果，如果转换失败，则变成0再加。
SELECT 999+'123';
SELECT '999'+'abc';
SELECT CONCAT('999','abc');
SELECT 111+NULL #+号两端有一端是null，那么返回结果就一定是null。
#如果想执行两个字符串的拼接需要使用CONCAT()函数。

#查询3：函数的返回值
SELECT VERSION();
#返回当前mysql数据库的版本信息
SELECT DATABASE();
#返回当前书库的名字

#查询4：返回表中的单个列,select 后面是列名 from后面是表名，查询单个列就只写一个列名。
SELECT employee_id FROM employees;
#查询5：查询返回多个列，select 后面是列名，多个列名用逗号分隔。 from后面是表名。
SELECT employee_id,first_name,last_name FROM employees;
#查询6：查询返回所有列，select 后面是所有列名，用逗号分隔，from后面是表名。查询的时候select后面写 * 代表查询所有列。
SELECT
  employee_id,
  first_name,
  last_name,
  email,
  phone_number,
  job_id,
  salary,
  commission_pct,
  manager_id,
  department_id
FROM employees;

SELECT * FROM employees;

#练习，查所有员工的编号，名字，和年薪
SELECT employee_id,first_name,last_name,
(salary * 12 *(1+IFNULL(commission_pct,0))) 
FROM employees;

#查询7：给列起别名,两种形式，在列后加as关键字加列的别名。列后空格加列的别名。别名可以使用单引号，也可以不使用。
SELECT employee_id,first_name,last_name,
(salary * 12 *(1+IFNULL(commission_pct,0))) AS '年薪'
FROM employees;

SELECT employee_id,first_name,last_name,
(salary * 12 *(1+IFNULL(commission_pct,0))) 年薪
FROM employees;

SELECT employee_id,first_name,last_name,
(salary * 12 *(1+IFNULL(commission_pct,0))) '年 薪'
FROM employees;#在列名的别名中如果有特殊字符，则别名必须两端加单引号。

2、条件检索

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

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


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

SELECT first_name,last_name,salary FROM employees 
WHERE NOT(salary >= 5000 AND salary <= 12000);

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

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

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


SELECT first_name,last_name,department_id FROM employees
WHERE department_id IN (20,50);


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


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


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


#8. 选择姓名中有字母 a，也有 e 的员工姓名
SELECT first_name,last_name FROM employees
WHERE first_name LIKE '%a%' AND first_name LIKE '%e%';

SELECT first_name,last_name FROM employees
WHERE first_name LIKE '%a%e%' OR first_name LIKE '%e%a%';

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


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


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

3、排序查询

/*
排序查询 order by
排序子句order by永远出现在整条sql的最后
*/

#排序1:单个列排序
#按照员工的工资升序排列 asc是按照列的升序排列 如果不写默认就是asc
SELECT * FROM employees ORDER BY salary ASC;
#按照员工的工资降序排列 desc是降序排列
SELECT * FROM employees ORDER BY salary DESC;
#按照员工的名进行降序排列 如果是字符就按照字典顺序就行排列
SELECT * FROM employees ORDER BY first_name DESC;

#排序2:多个列排序
#按照员工的工资降序排列 desc是降序排列 如果工资相同就按照名字的升序排列
#按照多个列排序的时候，用逗号分隔各排序规则，在前面的就是高排序规则，写在后面的就是低排序规则，个排序规则单独指定升序或者降序。
SELECT * FROM employees ORDER BY salary DESC,first_name;

4、条件查询

/*

条件检索
select 列名 from 表名
where 检索条件
执行带有where子句的查询语句是，逐行检索，匹配每一行的条件，如果这一样的条件符合where子句的表达式，那么这行数据
就会被提取出来，如果条件不符合where子句，则这一行数据被过滤掉。结果就是结果集中保留的都是符合where子句检索条件的记录。

where子句中可以写哪些形式
1、关系运算符组成的表达式
关系运算符：=,>,<,>=,<=,!=或<>

2、逻辑运算符组成的表达式
逻辑运算符： and,or,not  (&&,||,!不推荐)

3、模糊查询
like , between and, in ,is null,is not null

*/


#条件查询1：关系运算符
#查询所有工资大于等于10000的员工信息
SELECT * FROM employees WHERE salary >= 10000;
#查询所有隶属IT部门的员工信息
SELECT * FROM employees WHERE department_id = 60; 
#所有manager_id不是103的员工信息
SELECT * FROM employees WHERE manager_id <> 103;

#条件查询2：逻辑运算符
#所有工资在15000到25000之间的员工信息 and与 运算符两端的表达式必须同时满足条件才能满足整个条件。
SELECT * FROM employees WHERE salary >= 15000 AND salary <= 25000;
#所有manager_id是103或者102的员工信息 or或 运算符两端的表达式有一端满足条件，整个条件就满足。
SELECT * FROM employees WHERE manager_id=102 OR manager_id=103 OR manager_id=114;

#练习：工资大于15000并且部门编号是60，或者manager_id是102员工信息。and得有优先级高于or
SELECT * FROM employees WHERE manager_id=102 OR salary>5000 AND department_id=60;

#manager_id不是103的所有员工信息
SELECT * FROM employees WHERE NOT(manager_id = 103);
#工资不在10000到20000之间的员工信息
SELECT * FROM employees WHERE NOT(salary >10000 AND salary < 20000);

#模糊查询
#所有名字以e开头的员工信息:like关键字代表像什么什么的样子，必须和通配符配合。
#%通配符代表任意长度个任意字符。
SELECT * FROM employees WHERE first_name LIKE 'e%';
#所有名字有e的员工信息
SELECT * FROM employees WHERE first_name LIKE '%e%';
#查询第二个字母是e的员工信息:_通配符，一个长度个任意字符
SELECT * FROM employees WHERE first_name LIKE '_e%';
#查询倒数第三个字母是f的员工信息:
SELECT * FROM employees WHERE first_name LIKE '%f__';
#查询第二个字母是_的员工信息:转译字符 \_就不是通配符了，是_的意思。
SELECT * FROM employees WHERE first_name LIKE '_\_%';
#查询第二个字母是_的员工信息:任意字符做转译字符 $_就不是通配符了，是_的意思。自定义转译字符不推荐使用字母
SELECT * FROM employees WHERE first_name LIKE '_$_%' ESCAPE('$');


#between and模糊查询,一个数值的区间查询。包含边界值。必须是小数在前大数在后。
#查询所有工资在10000到20000之间的员工
SELECT * FROM employees WHERE salary >= 10000 AND salary <= 20000;
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;

#in模糊查询,只要检索条件匹配in后面任何一个条件，就会符合检索条件。
#manager_id是102或者103或者114的员工信息
SELECT * FROM employees WHERE manager_id=102 OR manager_id=103 OR manager_id=114;
SELECT * FROM employees WHERE manager_id IN (102,103,114);

#is null 和 is not null
#查询出所有manager_id为null员工信息
SELECT * FROM employees WHERE manager_id IS NULL;
#查询出所有manager_id不为null员工信息
SELECT * FROM employees WHERE manager_id IS NOT NULL;
#查询出所有年底有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

5、字符函数

/*
Sql语句中的函数
函数是一个有特定功能的方法或者代码块，当调用函数的时候，这个方法或者代码块就运行，返回结果。
函数有的有参数，有的没有参数，但是都有返回值。
单行函数：字符函数，数学函数，日期函数，流程控制函数，其他函数。
*/

#字符函数
#转大写函数
SELECT UPPER('abc');
SELECT UPPER(first_name) FROM employees;
#转小写函数
SELECT LOWER('ABC');
SELECT LOWER(first_name) FROM employees;
#拼接字符串函数
SELECT UPPER(CONCAT('a','b','c'));
SELECT CONCAT(first_name,'-',last_name) FROM employees;
#截取子字符串,在mysql的字符串中，索引从1开始。从源串截取，第二个参数位置开始一直到字符串末尾。
SELECT SUBSTR('hello world',7);
#截取子字符串，三个参数的形式，第一个参数源串，第二个参数从这个位置开始截取，第三个参数截取的长度。
SELECT SUBSTR('hello world',7,2);
#截取所有员工名字中的前三个字母
SELECT SUBSTR(first_name,1,3) FROM employees;
#获取字符串的字节数，如果是英文字母，一个字母一个字节，如果是中文，一个汉字三个字节
SELECT LENGTH('你好啊');
#获取目标串在源串中出现的第一次的索引，返回0代表没有找到目标串
SELECT INSTR('one world one dream','one');
#lpad和rpad左填充和右填充，源串加上填充串的长度正好是给出的参数长度。
SELECT LPAD('刘德华',10,'*');
SELECT RPAD('郭富城',10,'*');
#去掉两端空格,不能去掉中间的空格
SELECT LENGTH(TRIM('   张学友  '));
#从源串中用新串替换老串
SELECT REPLACE('蔡徐坤不认识蔡徐坤','蔡徐坤','刘德华');

6、数学函数

/*
数学函数

*/

#向上取整
SELECT CEIL(-3.15);#大于参数的最小的整数

#向下取整
SELECT FLOOR(-3.15);#小于参数的最大的整数

#四舍五入
SELECT ROUND(-3.15);

#取随机数
SELECT RAND();#去0到1之间的浮点数

#取绝对值
SELECT ABS(-3);

#取余
SELECT MOD(15,4);#相当于15%4

7、日期函数

/*
日期函数
*/

#取系统时间
SELECT NOW();

#取系统时间，只有时分秒
SELECT CURTIME();

#取系统时间，只有年月日
SELECT CURDATE();

/*
其他函数
*/
SELECT IFNULL('hello','ok');
SELECT IFNULL(NULL,'ok');
SELECT IFNULL('NULL','ok');

8、流程控制函数

/*
流程控制函数
*/

#判断控制函数
SELECT IF(10=9,'one','two');#if函数有三个参数，第一个参数是一个条件表达式，表达式返回true则显示第二个参数，表达式返回false则显示第三个参数。


#多重判断，相当于多重if-else
/*
select * 
case 
when 条件表达式1 then 表达式1
when 条件表达式2 then 表达式2
.......
else 表达式n
end
*/

SELECT first_name,last_name,
CASE 
WHEN salary >2000 AND salary < 4000 THEN salary * 1.1
WHEN salary >=4000 AND salary < 8000 THEN salary * 1.5
WHEN salary >=8000 AND salary < 12000 THEN salary * 2.0
ELSE salary * 2.5
END '工资'
FROM employees;


#多重判断，switch-case
/*
select * 
case 
when 等值判断 then 表达式1
when 等值判断 then 表达式2
.......
else 表达式n
end
*/

SELECT first_name,last_name,
CASE 
WHEN job_id='AD_PRES' THEN salary * 1.1
WHEN job_id='AD_VP' THEN salary * 1.5
WHEN job_id='IT_PROG' THEN salary * 2.0
ELSE salary * 2.5
END '工资'
FROM employees;

9、分组函数

/*
分组函数,分组函数只能返回一行记录，不要将分组函数和普通列放在一起查，除非这个列就是分组列。
*/

#AVG()取平均值
#看看所有员工的平均工资
SELECT AVG(salary) FROM employees; 

#SUM()去总和
#看看所有员工的工资和
SELECT SUM(salary) FROM employees;

#MAX()去最大值
#看看工资最高是多少
SELECT MAX(salary) FROM employees;

#MIN()去最大值
#看看工资最低是多少
SELECT MIN(salary) FROM employees;

#COUNT()统计行数
#员工信息表中有多少名员工的工资是超过10000
SELECT COUNT(*) FROM employees WHERE salary>10000;

/*
分组查询 分组查询子句group by
*/

#查询IT岗位所有员工的平均工资
SELECT AVG(salary) FROM employees WHERE job_id='IT_PROG';

#查询每个岗位的员工的平均工资
#group by 后面的列就是分组列，列值相等的数据会被分到一组中。
#分组函数在没有分组前是对全表数据进行检索，一旦分组，就会按照各个组分别检索的方法运算。
#不要将普通的列和分组函数放在一起查询，除非这个列是分组列。
SELECT job_id,AVG(salary),COUNT(*) FROM employees GROUP BY job_id;

#查询每个岗位的员工的平均工资大于5000
#where是过滤分组前的原表数据
#having是过滤分组之后的组。
SELECT job_id,AVG(salary),COUNT(*) FROM employees GROUP BY job_id HAVING AVG(salary)>5000;

SELECT job_id,AVG(salary),COUNT(*) 
FROM employees 
WHERE salary>3000
GROUP BY job_id 
HAVING AVG(salary)>5000;

10、多表连接查询

/*
多表连接查询，当需要的数据来自于多张表的时候需要多表连接，内连接和外连接
本文档演示的是内连接。
*/

-- SQL92版本

#1、等值连接
#两表内连接必须具备以下条件：
#两个表各自拥有一个列，这两个列的含义一样。
#内连接必须要求两张表中都有匹配记录才能连接返回，如果一张表中有匹配记录另一张表没有则不能返回。
#WHERE子句中的employees.job_id = jobs.job_id表示连接条件，即如果两个表中的两条记录的job_id相等，则两条记录会连接成一条记录返回。
SELECT employees.first_name,employees.last_name,employees.job_id,jobs.job_title
FROM employees,jobs
WHERE employees.job_id = jobs.job_id;

#内连接查询规范的方式就是表起别名，表名点列名，列起别名
SELECT e.first_name fna,e.last_name lna,e.job_id jid,j.job_title jti
FROM employees e,jobs j
WHERE e.job_id = j.job_id AND e.salary > 10000;


#2、非等值连接,非等值就是不需要各自拥有一个含义相同的列，可以让一个表的列在另一个表的某一个区间里。
SELECT e.first_name,e.job_id,e.salary,j.grade
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;


#3、自连接
#查询员工名和他的主管id及主管名
SELECT e.first_name ename,e.manager_id managerid,m.first_name managername
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;




-- SQL99版本

#1、等值连接
#使用inner join 来实现内连接
#表a (inner) join 表b on 连接条件
SELECT e.first_name,e.last_name,j.job_title 
FROM employees e INNER JOIN jobs j
ON e.job_id = j.job_id;

SELECT e.first_name,e.last_name,j.job_title 
FROM employees e INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE e.salary > 10000;

#2、非等值连接
SELECT e.first_name,e.job_id,e.salary,j.grade
FROM employees e INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;



#3、自连接
SELECT e.first_name ename,e.manager_id managerid,m.first_name managername
FROM employees e INNER JOIN employees m
ON e.manager_id = m.employee_id;

#查出员工的名字，岗位编号，部门编号和部门名
SELECT e.first_name,e.job_id,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

SELECT e.first_name,e.job_id,e.department_id,d.department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;


#查出员工的名字，岗位编号，部门编号、部门名称和部门经理的编号
SELECT e.first_name,e.job_id,e.department_id,d.department_name,d.manager_id
FROM employees e,departments d
WHERE e.department_id = d.department_id;

SELECT e.first_name,e.job_id,e.department_id,d.department_name,d.manager_id
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;

#查出员工的名字，岗位名称，部门名，三表内连接
SELECT e.first_name,j.job_title,d.department_name
FROM employees e,jobs j,departments d
WHERE e.job_id = j.job_id AND e.department_id = d.department_id;


SELECT e.first_name,j.job_title,d.department_name
FROM employees e INNER JOIN jobs j
ON j.job_id = e.job_id
INNER JOIN departments d
ON e.department_id = d.department_id;

11、子查询

/*
子查询
一个查询语句的数据源是另一个查询语句的查询结果集。
*/

#查看所有没有员工的部门名称
#第一步使用左外连接查询出所有部门并连接员工表找到没有员工的部门
SELECT e.first_name,d.department_name
FROM departments d LEFT JOIN employees e
ON e.department_id = d.department_id;
#第二步，找到所有first_name is null的部门
SELECT tableone.department_id,tableone.department_name
FROM
    (SELECT e.first_name,d.department_name,d.department_id
    FROM departments d LEFT JOIN employees e
    ON e.department_id = d.department_id) AS tableone
WHERE tableone.first_name IS NULL;


#查询出所有工资比Adam高的员工信息
#1、找到Adam员工的工资
SELECT salary FROM employees WHERE first_name = 'Adam';

#2、找到所有比Adam员工工资高的员工
#单行子查询
SELECT first_name,salary FROM employees
WHERE salary > (SELECT salary FROM employees WHERE first_name = 'Adam');

#多行子查询
#假设我有两个Adam员工，那么SELECT salary FROM employees WHERE first_name = 'Adam';会返回两条记录
SELECT first_name,salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE first_name = 'Adam');

12、外连接

/*
外连接分为：左外连，右外连，全外连
*/

#左外连接
#看看哪些部门有哪些员工，哪些部门没有员工。
#左外连接首先返回左表全部记录，然后使用连接条件匹配右表，有匹配的就连接返回，无匹配的插null返回。
SELECT e.first_name,d.department_name
FROM departments d LEFT JOIN employees e
ON e.department_id = d.department_id;

#右外连接
#右外连接，首先返回右表全部记录，使用连接条件匹配左表，有匹配记录连接返回，无匹配记录则插null返回。
SELECT e.first_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

13、分页查询

/*
分页查询
*/

#一共有50条符合条件的记录，一页10条，想看第三页。
#第一页1-10 第二页11-20 第三页21-30 ........ 第五页 41-50 索引40
SELECT * FROM employees;
SELECT COUNT(*) FROM employees;

#LIMIT关键字后面需要跟两个参数，第一个参数是开始显示的索引位置，这个索引从0开始，第二个参数是显示多少条
#第一页应该显示从第一条到第十条记录，但是由于limit索引从0开始所以代码如下
SELECT * FROM employees LIMIT 0,10;#0,10从0索引开始显示10条
#第二页应该显示从第十一条到第二十条记录，但是由于limit索引从0开始所以代码如下
SELECT * FROM employees LIMIT 10,10#0,10从10索引开始显示10条
#第三页应该显示从第二十一条到第三十条记录，但是由于limit索引从0开始所以代码如下
SELECT * FROM employees LIMIT 20,10#20,10从20索引开始显示10条


#一共有40条匹配条件的记录，一页显示8条，想看第二页 第一页1-8 索引0 第二页9-16 索引8
#做分页的时候，关键要素一：页面尺寸(一页显示多少条记录) pageSize = 8
#关键要素二：当前页码 currPage = 2
#关键要素三：总记录数(符合检索条件的所有数据) totalCount = 40
#计算：计算总页数 int pageCount = totalCount % pageSize==0? totalCount / pageSize : (totalCount / pageSize)+1
#计算：本页第一条记录的索引 int index = 8;
SELECT * FROM employees LIMIT 8,8;