DQL 数据查询语言

mysql入门：

# 1.查看所有数据库
show DATABASES;
 
# 2.使用数据库
use myemployees;
 
# 3.查看当前数据库
SELECT DATABASE();
 
# 4.查看当前数据库所有的表
show TABLES;
 
# 5.创建数据库
CREATE DATABASE text_DATABASE;
 
# 6.查询表的所有信息
SELECT * FROM departments;
 
# 7.查询表的结构
DESCRIBE departments;
 
# 查询表的结构，以下也可以
DESC departments;
 
# 8.查看数据库版本
SELECT VERSION();
 
# 9.删除数据库
DROP DATABASE text_DATABASE;

简单查询

# 1.查询单字段(某一列)
SELECT job_id FROM jobs;
 
# 2.查询多个字段(n列)
SELECT job_id,job_title from jobs;

起别名

# 1.改字段名字
SELECT job_id as '员工id' FROM jobs;
# 同
SELECT job_id '员工id' FROM jobs;

 字段计算

# 1.乘以一个数返回
SELECT min_salary*12 FROM jobs;
# 2.两个字段相加返回
SELECT min_salary+max_salary as hybrid FROM jobs;

条件查询

# 1.大于多少返回（可以大于等于>=）
SELECT min_salary FROM jobs WHERE min_salary>7000;
 
# 2.小于多少返回（可以小于等于<=）
SELECT min_salary FROM jobs WHERE min_salary<7000;
 
# 3.等于
SELECT min_salary FROM jobs WHERE min_salary=3000;
 
# 4.大于并且小于
SELECT min_salary FROM jobs WHERE min_salary> 3000 and min_salary < 5000;
# 同上
SELECT min_salary FROM jobs WHERE min_salary BETWEEN 3000 and 5000;

is null

# is null判断是否为null 不能用  = null
SELECT * from employees WHERE commission_pct is null;
# is not null判断是否为null 不能用  != null
SELECT * from employees WHERE commission_pct is not null;

and or

# 1.and 并且 
SELECT job_id,salary FROM employees WHERE job_id = 'AD_VP' and salary > 10000;
 
# 2.or 或者
SELECT job_id,salary FROM employees WHERE job_id = 'AD_VP' or job_id = 'IT_PROG' ;
 
# 3.or 优先级比 and 高
SELECT job_id,salary FROM employees WHERE job_id = 'AD_VP' and salary>10000 or salary>4000;
 
# 4.用()
SELECT job_id,salary FROM employees WHERE job_id = 'AD_VP' and (salary>10000 or salary>4000);

in    not in 

# 1.in IN 运算符用来判断表达式的值是否位于给出的列表中；如果是，返回值为 1，否则返回值为 0。
SELECT job_id,salary FROM employees WHERE salary in(10000,20000);
 
# 2.not in NOT IN 的作用和 IN 恰好相反，								
#	NOT IN 	用来判断表达式的值是否不存在于给出的列表中；
# 如果不是，返回值为 1，否则返回值为 0。
SELECT job_id,salary FROM employees WHERE salary not in(10000,20000);

模糊查询 like 

# 1.% 表示任意0个或多个字符，可匹配任意类型和长度的字符。有些情况下是中文，需用两个百分号（%%）表示
SELECT last_name  from employees WHERE last_name LIKE '%o%';
 
# 2._ 表示任意单个字符。匹配单个任意字符，它常用来限定表达式的字符长度语句
SELECT last_name  from employees WHERE last_name LIKE '_o%';
 
# 3.\转义字符 
SELECT last_name  from employees WHERE last_name LIKE '%\_%';
 
# 注意：不是所有数据管理软件都支持中括号（[ ]）通配符，例如MySQL不支持
# 注意：不是所有数据管理软件都支持中括号（[ ]）通配符，例如MySQL不支持
# 注意：不是所有数据管理软件都支持中括号（[ ]）通配符，例如MySQL不支持
# 4. []表示括号内所列字符中的一个（类似正则表达式）。
#指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个
SELECT last_name  from employees WHERE manager_id LIKE '[1]%';
 
# 5. ^表示不在括号所列之内的单个字符。其取值和 [ ] 相同，但它要求所匹配对象为指定字符以外的任一个字符
SELECT last_name  from employees WHERE last_name LIKE '^1';

排序 order by 

# 1.升序
SELECT last_name,salary  from employees  ORDER BY salary;
# 同
SELECT last_name,salary  from employees  ORDER BY salary ASC;
 
# 2.降序
SELECT last_name,salary  from employees  ORDER BY salary DESC;
 
 
# 3.两个字段排序
# 如果工资一样，那么就按照姓名降序
SELECT last_name,salary  from employees  ORDER BY salary,last_name DESC;

大小写 

# 1.小写
SELECT lower(last_name) from employees; 
# 2.大写
SELECT UPPER(last_name) from employees; 

截取字符     SUBSTR(str FROM pos FOR len)

# 1.截取一个字符一个长度 
# 在mysql里面 是从1开始的
SELECT SUBSTR(last_name,1,1) from employees;
 
# 2.截取一个字符两个长度 
SELECT SUBSTR(last_name,1,2) from employees;

查看长度 LENGTH(str)

# 1.返回长度
SELECT LENGTH(last_name) from employees;
# 2.结合substr使用
SELECT SUBSTR(last_name,1,LENGTH(last_name)) from employees;

拼接多个字段  CONCAT(str1,str2,...)

# 1.拼接两个字段返回一个字段
SELECT (last_name,first_name) FROM employees;

随机数 RAND()

# 1.产生一个 [0-1)的随机数
SELECT RAND()
 
# 2.长生一个 [0-100)的随机数
SELECT RAND()*100 from employees;
 
# 3.使用随机种子产生随机数 
# 相同的原因是因为行都是last_name
SELECT RAND(last_name) from employees;
# 不相同
SELECT RAND(123) from employees;

四舍五入 ROUND(X)

# 1.不保留小数
SELECT ROUND(RAND());
 
# 2.保留两位小数
SELECT ROUND(RAND(),2);

统计 最大值 最小值 平均 求和 count() max() min() avg() sum()

# 1.统计
SELECT count(salary) from employees;
 
# 2.最大值
SELECT max(salary) from employees;
 
# 3.最小值
SELECT min(salary) from employees;
 
# 4.平均
SELECT avg(salary) from employees;
 
# 5.求和
SELECT sum(salary) from employees;

执行循序

# mysql执行循序是from,WHERE,GROUP BY,SELECT,ORDER BY;
# 下面执行循序错误 min本身就是在select 里面才能出的结果，放到前面就会出现执行错误
# 分组函数必须分组后才能执行
SELECT salary from employees WHERE salary >= MIN(salary);

分组查询 GROUP BY

# 1.利用job_id 进行分组，求和salary
SELECT sum(salary),job_id from employees GROUP BY job_id
 
# 2.利用job_id 进行分组，求和salary。求和salary进行降序
SELECT sum(salary),job_id from employees GROUP BY job_id ORDER BY sum(salary) desc;
 
# 3.利用部门id，工种号进行分组，来统计最低工资多少
SELECT department_id,job_id,MIN(salary) from employees GROUP BY department_id,job_id ORDER BY MIN(salary) desc;

筛选条件 having

必须跟order by 一起出现
 
# 1.在用department_id分组后，选出工资最大的出来，并筛选整体最大工资5000以上的
SELECT max(salary),department_id from employees GROUP BY department_id HAVING max(salary) > 5000;
# 2.选出个题工资大于16000的，在进行求和输出。WHERE先SELECT后
SELECT sum(salary),department_id from employees WHERE salary>16000 GROUP BY department_id

去重 DISTINCT

# 1.去重一个字段
SELECT DISTINCT salary from employees;
# 2.去重多个字段
SELECT  min(salary), department_id from employees GROUP BY department_id;
# 下面没用
SELECT COUNT(DISTINCT department_id,salary) from employees;

 内连接等值连接 INNER JOIN

# 笛卡尔乘积现象，假如A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为m*n行。
SELECT j.job_id,e.salary from jobs j,employees e ;
SELECT * from jobs j,employees e ;
# 交叉连接 同上
SELECT j.job_id,e.salary from jobs j CROSS JOIN employees e ;
 
# 解决办法
SELECT * from jobs j,employees e WHERE j.job_id=e.job_id;
# 同上
SELECT * from jobs j inner JOIN  employees e ON j.job_id=e.job_id;
# 同上
SELECT * from jobs j inner JOIN  employees e USING(job_id);

内连接非等值连接

# 1.查询员工的工资和等级
SELECT grade_level,salary  FROM employees e JOIN job_grades j ON salary BETWEEN lowest_sal AND highest_sal;

自连接 自己跟自己做比对

# 1.查询员工名和上级的名称
SELECT e.employee_id '员工编号',e.last_name '员工名字',m.employee_id '领导编号',m.last_name '领导名字'
from employees e JOIN employees m 
on e.manager_id = m.employee_id;

外连接

# 1.左外连接
SELECT e.employee_id '员工编号',e.last_name '员工名字',m.employee_id '领导编号',m.last_name '领导名字'
from employees e LEFT JOIN employees m 
on e.manager_id = m.employee_id;
 
# 2.右外连接
SELECT e.employee_id '员工编号',e.last_name '员工名字',m.employee_id '领导编号',m.last_name '领导名字'
from employees e RIGHT JOIN employees m 
on e.manager_id = m.employee_id;
 
# 3.左连接
# 4.右连接
# 5.全外连接=内连接的结果+表1中有但表2没用的+表2中有但表1没有的
# 6.两个表独有的数据，没有交集部分

 子连接

# 1.放在where后面
# 查询工资大于最小工资
SELECT salary from employees WHERE salary >(SELECT min(salary) from employees);
 
# 2. 放在from后面
# 查询每个职业平均工资，并分好等级
# 先把每个职业平均工资算出来
SELECT job_id,AVG(salary ) from employees GROUP BY job_id;
# 再把上面当成一个表进行内查询
SELECT  j.grade_level,e.job_id,e.salary
from (SELECT job_id,AVG(salary) as salary from employees GROUP BY job_id) as e JOIN job_grades as j
ON e.salary BETWEEN j.lowest_sal and j.highest_sal;
 
#3.EXISTS
select * from emp where EXISTS (select id from dept where dept.id=emp.dep_id);
用: exists后面一定是子查询语句，不能用（值1，值2）代替；where exists （查询），
结构中没有列；exists后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立，该条记录保留。
exists （查询），只要子查询不会空  则where条件就返回真。

联合查询 UNION

将多条查询语句的结果合并成一个结果
 
# 1.查找姓名包含o的或者工资大于10000
SELECT last_name,salary from employees WHERE last_name LIKE '%o%'
UNION 
SELECT last_name,salary from employees WHERE salary > 10000;

分页查询 LIMIT

# 1.查询前5条员工的信息
SELECT * from employees LIMIT 0,5;
# 同上
SELECT * from employees LIMIT 5;
 
# 2.查询第11条到25条
SELECT * from employees LIMIT 10,15;