【042】Mysql 基本使用

内容目录

一、数据库操作1、数据库常用操作及数据集2、查看数据库性能常用命令3、数据库权限二、数据表操作1、常用表操作2、数据查询语言3、分组函数4、分组函数的调用5、条件查询（WHERE）6、模糊查询7、排序查询（ORDER BY）8、分组查询（GROUP BY）三、sql高级函数1、连接查询（JOIN）2、子查询

一、数据库操作

1、数据库常用操作及数据集

  mysql是关系型数据库，sql语言可以很好地对数据库进行操作管理，进而进行数据分析。

#数据链接：https://pan.baidu.com/s/1zN_krdYv7Mlo3uD4NCSX1Q
#提取码：j921
#@path.sql 或source path.sql  --执行sql脚本
#
select version();  --显示mysql数据库软件版本    
#
mysql -u root -p   --不显示密码连接
#
show databases;  --查看所有数据库
#    
use 数据库的名字；  --使用数据库
#    
select database();  --查看当前使用的数据库
#
select now();  --显示时间
#    
select year(now());  --显示时间中的年份
#
drop database 数据库名;   --删除数据库
#

2、查看数据库性能常用命令

   配置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有4148个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内。

##快查询
show global status;  --快速查询MySQL服务器运行各种状态值，数据比较多，没有针对性
#
mysql> show variables;  --快速查询MySQL服务器配置信息语句：
#
##慢查询
show variables like '%slow%';
show global status like '%slow%'; 

#

3、数据库权限

#创建用户
CREATE USER 用户名@‘IP地址’ IDENTIFIED BY '密码'；#在指定ip访问
CREATE USER 用户名@’%’  IDENTIFIED BY '密码'； #所有ip都可访问

#给用户授权
GRANT 权限 ON 库名.表名 to 用户名@’IP地址’;
#如果是所有权限，则权限处用ALL
#如果是多个权限，中间用,隔开
#如果是所有库，库名用*
#如果是所有表，表名用*
#如果是所有IP，ip地址用%

#撤销权限
REVOKE 权限 ON 库名.表名 to 用户名@’IP地址’;

#查看权限
SHOW GRANTS FOR 用户名@’IP地址’;

#删除用户
DROP USER 用户名@‘IP地址’

二、数据表操作

1、常用表操作

#
show tables;  --查看当前数据库中所有表    
#
create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]); --创建表    
#
desc 数据表的名字;  --查看表结构    
#
show create table 表名字;  --查看表的创建语句    
#
alter table 表名 add 列名 类型；  --添加字段    
#
alter table 表名 modify 列名 类型及约束;  --修改字段，不重命名版
alter table 表名 change 原名 新名 类型及约束;  ----修改字段，重命名版
#
alter table 表名 drop 列名;  --删除字段    
###
drop table 表名;  --删除表
#
#

2、数据查询语言

书写顺序：
SELECT、DISTINCT、FROM、JOIN ON、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT

执行顺序：
FROM、 JOIN ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT

3、分组函数

  分组函数又称为聚合函数、统计函数、组函数，所有分组函数对null值处理为忽略它，而非当做0。常用的分组函数有以下几个：

#
SUM() --对数值型数据求和，用+相加，符合+的运算法则
#
AVG() --对数值型数据求平均
#
MAX() --对所有可比较类型求最大值
#
MIN() --对所有可比较类型求最小值
#
COUNT() --统计非空个数

4、分组函数的调用

SELECT SUM(salary) FROM employees;  --691400.00
SELECT COUNT(*) FROM employees;  --统计表的总行数107
SELECT COUNT(1) FROM employees;  --统计表的总行数107

5、条件查询（WHERE）

  运算符做条件

#查询部门编号不等于90的员工信息
SELECT * FROM employees WHERE department_id<>90;

  逻辑表达式做条件

#查询部门编号大于50小于100的员工信息
SELECT * FROM employees WHERE department_id > 50 AND department_id < 100;

6、模糊查询

• LIKE模糊匹配,%任意0个或多个字符; _任意1个字符；无法匹配null;

• BETWEEN a AND b,匹配a-b范围内的内容,包括a和b临界;

• IN(list),匹配list中的内容;    

• IS NULL / IS NOT NULL    ,判断是否为null,建议使用这个而非<=>。

#查询last_name第二个字符是a的员工
SELECT * FROM employees WHERE last_name LIKE '_a%';

#查询工资在10000到20000的员工信息，包括10000和20000
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;

#查询职位为D_VP和FI_MGR的员工信息
SELECT * FROM employees WHERE job_id IN ('D_VP','FI_MGR');

#查询没有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;

7、排序查询（ORDER BY）

ASC：升序，默认项；DESC：降序。
基本排序

#按月薪降序
SELECT * FROM employees ORDER BY salary DESC;
#按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) salary_total 
FROM employees ORDER BY salary_total DESC;

按函数排序

#按名字长度降序排序
SELECT * FROM employees ORDER BY LENGTH(last_name) DESC;

按多个字段排序

#先按salary降序排序，同样salary的按employee_id升序排序
SELECT * FROM employees ORDER BY salary DESC , employee_id ASC;

8、分组查询（GROUP BY）

SELECT 查询内容 FROM 表名 [WHERE 条件] GROUP BY 分组列表 [HAVING 条件]

• 查询内容中的字段，必须要出现在分组列表中

• WHERE是在分组前对原始表筛选，HAVING是在分组后对结果表筛选

• 分组查询中，若用分组函数做筛选的条件，一定是在HAVING子句中

按单字段分组

#查询每个部门的员工数量
SELECT department_id,COUNT(1) FROM employees GROUP BY department_id ;

按多字段分组

#查询每个部门，每个职务的平均工资
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;

按函数分组

#按名字长度分组，并筛选出数量大于5的名字长度
SELECT COUNT(1) c,LENGTH(last_name) len_name FROM employees 
GROUP BY len_name HAVING c>5;

三、sql高级函数

1、连接查询（JOIN）

内连接（INNER JOIN）

#sql92等值连接，查询每个员工的job_title
SELECT e.last_name,e.job_id,job_title FROM employees e , jobs j where e.job_id = j.job_id;
#sql99等值连接，查询每个员工的job_title
SELECT e.last_name,e.job_id,job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id;

#sql92非等值连接，查询某个员工的工资等级
SELECT e.last_name,j.grade_level FROM employees e,job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
#sql99非等值连接，查询某个员工的工资等级
SELECT e.last_name,j.grade_level FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

#sql92自连接，查询某个员工及其对应的领导
#查询某个员工及其对应的领导
SELECT e1.last_name 'employee',e2.last_name 'manager' FROM employees e1,employees e2 WHERE e1.manager_id = e2.employee_id;

#sql99自连接，查询某个员工及其对应的领导
SELECT e1.last_name 'employees',e2.last_name 'manager' FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

外连接（OUTER JOIN）
  外连接又包括左外连接、右外连接和全外连接。多用来查询一张表中有，另一张表中没有的记录。对于主从表的区分，有以下几种：

• 左外连接：主表 LEFT JOIN 从表

• 右外连接：从表 RIGHT JOIN 主表

• 全外连接：表1 FULL JOIN 表2，其中表1表2分别轮流作为主表，查询结果是两张表的"并集"。另外，全外连接在MySQL中不支持使用。

#左外连接（LEFT JOIN ON）,查询没有迟到记录的员工名
SELECT last_name,late.count_late FROM employees LEFT JOIN late ON employees.employee_id = late.employee_id WHERE late.count_late IS NULL;

#右外连接（RIGHT JOIN ON）,查询没有迟到记录的员工名
SELECT last_name,late.count_late FROM late RIGHT JOIN employees ON employees.employee_id = late.employee_id WHERE late.count_late IS NULL;

#全外连接（FULL JOIN ON）,全外连接在mysql中不支持，语法如下：
SELECT beauty.name FROM boys FULL OUTER JOIN beauty ON beauty.boyfriend_id = boys.id ;

交叉连接（CROSS JOIN）
  交叉连接（CROSS JOIN）,效果即笛卡尔积形式，即两个表的所有记录都一一匹配一遍，查询的结果一共A×B条记录（A、B分别为两个表的记录数）

SELECT * FROM jobs CROSS JOIN locations;

2、子查询

子查询在SELECT后面

#查询每个部门的信息加部门的人数
SELECT * ,(SELECT COUNT(1) FROM employees WHERE 
employees.department_id = departments.department_id) FROM departments;

子查询在FROM后面

#查询每个部门的平均工资的工资等级
SELECT department_id,department_name,a , j.grade_level 
FROM (SELECT e.department_id ,d.department_name, AVG(salary) a 
FROM employees e LEFT JOIN departments d ON  e.department_id=d.department_id 
GROUP BY e.department_id) tmp INNER JOIN job_grades j ON 
tmp.a BETWEEN j.lowest_sal AND j.highest_sal;

子查询在WHERE/HAVING后面

#标量子查询：查询工资最少的员工信息
SELECT * FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

#列子查询：查询location_id为1400或1500或2700的部门中的所有员工姓名
SELECT last_name FROM employees WHERE department_id IN (SELECT department_id 
FROM departments WHERE location_id IN(1400,1500,2700));

#行子查询：查询编号最小并且工资最高的员工信息，满足行子查询的条件笔记苛刻，所以用的不多
SELECT * FROM employees WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary) FROM employees);

子查询在EXISTS后面（相关子查询）

EXISTS(SELECT 语句)：有记录，则为1，无记录，则为0；相关子查询是先执行外查询，在由EXISTS过滤；都能用IN代替

#查询有员工的部门名
SELECT department_name FROM departments d 
WHERE EXISTS(SELECT * FROM employees e WHERE e.department_id = d.department_id);

#用IN代替
SELECT department_name FROM departments d
 WHERE d.department_id IN (SELECT DISTINCT department_id FROM employees);

About Me：小婷儿

● 本文作者：小婷儿，专注于python、数据分析、数据挖掘、机器学习相关技术，也注重技术的运用

● 作者博客地址：https://blog.csdn.net/u010986753

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● 微信：tinghai87605025 联系我加微信群

● QQ：87605025

● QQ交流群py_data ：483766429

● 公众号：python宝 或 DB宝

● 提供OCP、OCM和高可用最实用的技能培训

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

如果你觉得到文章对您有帮助，欢迎赞赏哦！有您的支持，小婷儿一定会越来越好！