虚拟机安装MySQL数据库，本地连接并进行基本单表查询和多表查询数据库

记录自己所学
虚拟机安装MySQL数据库，本地连接并操作数据库

一、所需工具和环境

VMware，MySQL，XShell，SCP，Navicat

二、Centos7安装MySQL数据库

安装步骤可参考其他博主的博客~ 此处不过多赘述，仅记录关键步骤

1、查看临时密码

grep "password" /var/log/mysqld.log

2、修改密码并设置权限

降低密码的复杂度要求

mysql> set global validate_password_policy=0;

降低密码的长度要求

mysql> set global validate_password_length=1;

修改root用户的密码为root,

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

授权root用户可以远程登录数据库系统

mysql> use mysql;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

刷新权限

mysql> flush privileges;

退出数据库系统

mysql> exit;

3、开放3306端口

由于MySQL默认端口是3306，所以想要远程登陆，必须开放防火墙的3306端口

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload

三、本地下载安装Navicat，并连接数据库

本地下载安装Navicat，用于操作MySQL数据库

下载安装过程不多赘述~

点击连接
连接名可任意取
主机为虚拟机的ip地址


创建成功！

四、创建数据库、数据表

数据库结构

创建数据库

运行sql文件，插入demo表

五、单表查询

关键字（不区分大小写）
– ORDER BY 排序
1、可以指定任何列进行排序
2、可以是正序（关键字是ASC，可以缺省），也可以是倒序（DESC）
3、可以多列排序（保证第一列的完全有序，在第一列的相同的情况下按照第二列排）
4、排序规则
数字：大小
字符串：按照字符集编码
日期：按照年-月-日的数字大小

– DSISTINCT去除重复行
可以单列去重
也可以多列去重（多列联合重复才会去除）

– LIMIT截取指定的数量行
– 用途：用于分页查询
– 5代表截取5行（缺省了行号，默认是0）

SELECT * FROM emp LIMIT 5;

– 指定行号，行号是从0开始
– 从第1行开始，向后截取5行

SELECT * FROM emp LIMIT 0,5;

– 从第6行开始，向后截取5行

SELECT * FROM emp LIMIT 5,5;

– LIKE模糊条件查询
– 以S开头

SELECT * FROM emp WHERE ENAME LIKE 'S%';

– 以S结尾

SELECT * FROM emp WHERE ENAME LIKE '%S';

– 有S就行

SELECT * FROM emp WHERE ENAME LIKE '%S%';

– 统计查询
– 统计函数,5种
– SUM,MAX，MIN，AVG这四个统计函数的括号中的列必须是数值类型，不能是字符串类型
– COUNT这个统计函数的括号种的列可以是仍以类型，且只统计非空行数

	SELECT SUM(SAL) FROM emp;			-- SUM(列名)，用于对列求和
	SELECT MAX(SAL) FROM emp;			-- MAX(列名)，用于对列求最大值
	SELECT MIN(SAL) FROM emp;			-- MIN(列名)，用于对列求最小值
	SELECT AVG(SAL) FROM emp;			-- AVG(列名)，用于对列求平均值
	SELECT COUNT(SAL) FROM emp;		-- COUNT(列名)， 用于对列求行数

– 对AVG()数据的处理

	SELECT CELL(AVG(SAL)) FROM emp;
	SELECT FLOOR(AVG(SAL)) FROM emp;
	SELECT TRUNCATE(AVG(SAL),2) FROM emp; -- TRUNCATE截取指定位数的小数
	SELECT ROUND(AVG(SAL)) FROM emp;	-- ROUND根据第一位小数做四舍五入
	SELECT ROUND(AVG(SAL), 2) FROM emp;	-- ROUND根据第三位小数做四舍五入，2是保留两位小数

六、多表查询

– 两张表做笛卡尔积连接（交叉连接）

SELECT * FROM emp;	-- 8列，14行
SELECT * FROM dept;	-- 3列，4行
SELECT * FROM emp,dept;	-- 11行，56列

– 别名

SELECT ENAME,dname,loc FROM emp AS e,dept AS d where e.deptno = d.deptno;

SELECT ENAME,dname,loc 
FROM emp e,dept d 
where e.deptno = d.deptno;

– 自连接的使用场景

SELECT * FROM emp e1,emp e2
where e1.EMPNO = e2.MGR;	-- 得到的笛卡尔积：16列，196行

SELECT e1.EMPNO,e1.ENAME,e1.JOB,e2.EMPNO,e2.ENAME,e2.JOB
FROM emp e1,emp e2
where e1.EMPNO = e2.MGR;

– 使用笛卡尔积需要注意的问题：笛卡尔积不能太大，会导致数据库服务器因为内存溢出而崩溃

SELECT COUNT(id) FROM address;
-- SELECT * FROM address a1, address a2; -- 不可行的做法

SELECT * FROM address WHERE name='苏州市'

SELECT a1.name,a2.name
FROM (SELECT * FROM address WHERE name='苏州市') a1, address a2
where a1.id = a2.parent_id;