关系数据库-6-[mysql8]在centos7中安装和常用查询

mysql删除表数据几种情况
在CentOS中默认安装有MariaDB，这个是MySQL的分支，但为了需要，还是要在系统中安装MySQL，而且安装完成之后可以直接覆盖掉MariaDB。

1 下载并安装MySQL官方的 Yum Repository

网址https://dev.mysql.com/downloads/repo/yum/
#wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
#yum -y install mysql80-community-release-el7-3.noarch.rpm

1.1 在线安装

(1)安装
#yum install -y mysql-community-server
(2)启动
#systemctl start mysqld.service
#systemctl status mysqld.service
(3)在日志文件中找出密码
#grep “password” /var/log/mysqld.log
2020-09-14T13:43:54.432258Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ip9IPXahnK.5
(4)登录数据库
#mysql -uroot -p
(5)修改密码
mysql> alter user ‘root’@‘localhost’ identified by ‘BigData@123456’;
(6)密码复杂度
mysql> SHOW VARIABLES LIKE ‘validate_password%’;
mysql> set global validate_password.policy=LOW;
mysql> set global validate_password.length=6;
mysql> alter user ‘root’@‘localhost’ identified by ‘bigdata’;
(7)配置mysql允许远程访问
创建远程登录对象
mysql> create user ‘root’@‘%’ identified by ‘bigdata’;
授权远程登录

mysql> grant all privileges on *.* to 'root'@'%' with grant option;

强制刷新
mysql> flush privileges;
(8)mysql8的用户密码问题
说明： 由于mysql8的密码采用了“caching_sha2_password”模式，而老版的是“mysql_native_password”模式，所以造成很多客户端连接不上。
解决方案: 1. 升级客户端 2.修改mysql8的密码模式
【修改mysql8的密码模式】
方式一：
在my.cnf 文件中添加：default_authentication_plugin=mysql_native_password 然后重启mysql，之后新建的用户都是“mysql_native_password”模式的。
方式二：
mysql> alter user ‘root’@‘%’ identified with mysql_native_password by ‘bigdata’;
mysql> flush privileges;

1.2 离线安装

(1)下载并安装MySQL官方的 Yum Repository
网址https://dev.mysql.com/downloads/repo/yum/
#wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
#yum -y install mysql80-community-release-el7-3.noarch.rpm
(2)下载rpm包
#yum install --downloadonly --downloaddir=/root -y mysql-community-server
#yum localinstall -y *.rpm
其余步骤同在线安装

2 常用查询

2.1 单表查询

create database test；
use test；
create table student(
	id int(3) primary key auto_increment,
	name varchar(20) not null,
	grade float,
	gender char(2)
	);
insert into student(name,grade,gender)
values ("songjiang",40,"男"),
	("wuyong",100,"男"),
	("qinming",90,"男"),
	("husanniang",88,"女"),
	("sunerniang",66,"女"),
	("wusong",86,"男"),
	("linchong",92,"男"),
	("yanqing",90,null);
(1)查询指定字段
select id,name,grade,gender from student;
select * from student;
select name,grade from student;

(2)按条件查询
(2-1)带关系运算符的查询
select id ,name from student where id=4;
select name,gender from student where name="wusong";
select name,gender from student where id=2;
select name,grade from student where grade>80;
(2-2)带in关键字的查询
判断某个字段的值是否在指定集合中
select id,name from student where id in (1,2,3);
select id,name from student where id not in (1,2,3);
(2-3)带between and关键字的查询
select id ,name from student where id between 2 and 5;
select id ,name from student where id not between 2 and 5;
(2-4)空值查询
select id,name,grade,gender from student where gender is null;
select id,name,grade,gender from student where gender is not null;
(2-5)带distinct关键字的查询
select gender from student;
select distinct gender from student;作用于一个字段
select distinct gender,name from student;作用于多个字段
(2-6)带like关键字的查询
select id,name from student where name like "s%";匹配s开头任意长度字符串
select id,name from student where name like "w%g";匹配w和g之间任意长度字符串
select id,name from student where name like "%y%";匹配含y的字符串
select id,name from student where name not like "%y%";
select id,name from student where name like "wu_ong";匹配含单个字符的字符串
(2-7)带and关键字的多条件查询
select id,name,gender from student where id<5 and gender="女";

select id,name,gender from student
where id in (1,2,3,4) and name like "%ng" and grade<80;

(2-8)带or关键字的多条件查询
select id,name,gender from student where id<3 or gender="女";

(3)高级查询
(3-1)聚合函数
select count(*) from student;
select sum(grade) from student;
select avg(grade) from student;
select max(grade) from student;
select min(grade) from student;
select sum(grade),gender from student where gender = "男";

(3-2)对查询结果排序
select * from student order by grade;默认升序排列
select * from student order by grade asc;指定升序
select * from student order by grade desc;指定降序
先按gender升序，相同的gender，再按grade降序
select * from student order by gender asc,grade desc;

(3-3)分组查询
select count(*),gender from student group by gender;
select sum(grade),gender from student group by gender;

关键字having对分组后的内容进行过滤
select sum(grade),gender from student group by gender having sum(grade)<300;
select sum(grade) as he,gender from student group by gender order by he desc;

(3-4)使用limit限制查询结果数量
select * from student limit 4;
select * from student order by grade limit 4;


(4)为表和字段取别名
(4-1)为表取别名
select * from student as s where s.gender="女";
(4-2)为字段取别名
select name as stu_name,gender as stu_gender from student;
select name as stu_name,gender stu_gender from student;

2.2 多表查询

多表操作
use test;
create table department(
	did int(4) not null primary key,
	dname varchar(36)
	);
create table employee(
	id int(4) not null primary key,
	name varchar(36),
	age int(2),
	did int(4) not null
	);

insert into department values 
	(1,"网络部"),
	(2,"媒体部"),
	(3,"研发部"),
	(5,"人事部");

insert into employee(id,name,age,did) values
	(1,"王红",20,1),
	(2,"李强",22,1),
	(3,"赵四",20,2),
	(4,"郝娟",20,4);

(1)连接查询
(1-1)交叉连接
两表中所有数据组合，实际很少用
select * from department cross join employee;

(1-2)内连接(简单连接、自然连接)
inner可以省略
select te.name,td.dname 
	from employee te inner join department td 
	on te.did = td.did;
	
select te.name,td.dname 
	from employee te join department td 
	on te.did = td.did;
	
where条件句也可以实现
select te.name,td.dname 
	from employee te,department td 
	where te.did = td.did;

(1-3)外连接(左连接)
左表的所有记录
select td.did,td.dname,te.name 
	from department td left join employee te 
	on td.did=te.did;

(1-4)外连接(右连接)
右表的所有记录
select td.did,td.dname,te.name 
	from department td right join employee te 
	on td.did=te.did;

(1-5)复合条件连接查询

select te.name,te.age,td.dname
	from department td inner join employee te
	on td.did = te.did;

select te.name,te.age,td.dname
	from department td inner join employee te
	on td.did = te.did
	order by age;

select te.name,te.age,td.dname
	from department td inner join employee te
	on td.did = te.did
	order by te.age;

(2)子查询
首先会执行子查询中的语句，
返回的结果作为外层查询的过滤条件。
(2-1)带in关键字的子查询
select * from department 
	where did in (select did from employee where age=20);
select * from department 
	where did not in (select did from employee where age=20);

(2-2)带exists关键字的子查询
不产生任何数据，只返回true或false，
当返回值为true时，外层查询才会执行。
select * from department 
	where exists (select did from employee where age > 21);

(2-3)带any关键字的子查询
select * from department 
	where did > any (select did from employee);

(2-4)带all关键字的子查询
select * from department 
	where did > all (select did from employee);

(2-5)带比较运算符的子查询
select * from department 
	where did =  (select did from employee where name="赵四");

2.3 应用举例

SQL GROUP BY对多个字段进行分组。
在平时的开发任务中我们经常会用到MYSQL的GROUP BY分组， 用来获取数据表中以分组字段为依据的统计数据。比如有一个学生选课表，表结构如下：

create database test；
use test；
create table selectClass(
	id int(3) primary key auto_increment,
	Subject varchar(20) not null,
	Semester char(2) not null,
	Attendee varchar(20) 
	);
insert into selectClass(Subject,Semester,Attendee)
values ("ITB001","1","John"),
	("ITB001","1","Bob"),
	("ITB001","1","Mickey"),
	("ITB001","2","Jenny"),
	("ITB001","2","James"),
	("MKB114","1","John"),
	("MKB114","1","Erica");

(1)统计每门课程有多少个学生报名

select Subject,count(*) from selectClass group by Subject;

产生这个结果的原因是：GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。
(2)统计出每门课程每个学期有多少人选择

select Subject,Semester,count(*) from selectClass group by Subject,Semester;

产生这个结果的原因是：GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。
上面SQL的意思是，对selectClass表中的数据进行分组，将具有相同Subject和Semester字段值的记录放到同一个分组里去， 然后对每个分组中的数据应用聚合函数(COUNT，SUM， AVG等)。
(3)统计出每门课程每个学期有多少人选择并按数量降序排列

select Subject,Semester,count(*) as num from selectClass group by Subject,Semester order by num desc;

对分组后的统计结果，进行排序显示。

总结：
在MYSQL中使用GROUP BY对表中的数据进行分组时，
GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里，
GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。

2.4 删除表数据

2.4.1 truncate、delete与drop

1、drop table table_name :
删除表全部数据和表结构，立刻释放磁盘空间，不管是Innodb和MyISAM;
实例，删除学生表：drop table student;

2、truncate table table_name :
删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是Innodb和MyISAM;
实例，删除学生表：truncate table student;

3、delete from table_name :
删除表全部数据，表结构不变，对于MyISAM会立刻释放磁盘空间，InnoDB不会释放磁盘空间;
实例，删除学生表：delete from student;

4、delete from table_name where xxx :
带条件的删除，表结构不变，不管是innodb还是MyISAM都不会释放磁盘空间;
实例，删除学生表中姓名为 “张三” 的数据：
delete from student where T_name = “张三”;

5、delete操作以后，使用optimize table table_name会立刻释放磁盘空间，不管是innodb还是myisam;
实例，删除学生表中姓名为 “张三” 的数据：
delete from student where T_name = “张三”;
实例，释放学生表的表空间：
optimize table student;

6、delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

总结
1、当你不再需要该表时，用drop;
2、当你仍要保留该表，但要删除所有记录时， 用truncate;
3、当你要删除部分记录时，用delete。

2.4.2 相同点

1.truncate和不带where子句的delete、以及drop都会删除表内的数据。
2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

2.4.3 不同点

1.truncate和delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

2.delete语句是数据库操作语言(DML)，这个操作会放到rollback segement中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
truncate、drop是数据库定义语言(DDL)，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。

3.delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不动
drop语句将表所占用的空间全部释放。
truncate语句缺省情况下见空间释放到minextents个extent，除非使用reuse storage；truncate会将高水线复位(回到最开始)。

4.速度，一般来说: drop> truncate > delete

5.安全性：小心使用drop和truncate，尤其没有备份的时候.否则哭都来不及。
使用上,想删除部分数据行用delete，注意带上where子句. 回滚段要足够大.
想删除表,当然用drop
想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

7、TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

8、TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

9、对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

10、TRUNCATE TABLE不能用于参与了索引视图的表。

3 卸载mysql8

3.1 删除已安装的mysql包

因为这个mysql是直接用yum安装的，所以删除过程容易不少。
(1)查找下安装的包

yum list installed | grep mysql
或者
rpm -qa |grep -i mysql

(2)开始卸载
依次执行 yum remove 包名

yum remove mysql-community-common-8.0.26-1.el7.x86_64
yum remove mysql-community-client-plugins-8.0.26-1.el7.x86_64
yum remove mysql80-community-release-el7-3.noarch
yum remove mysql-community-server-8.0.26-1.el7.x86_64
yum remove mysql-community-libs-compat-8.0.26-1.el7.x86_64
yum remove mysql-community-devel-8.0.26-1.el7.x86_64
yum remove mysql-community-client-8.0.26-1.el7.x86_64
yum remove mysql-community-libs-8.0.26-1.el7.x86_64
核查是否卸载成功
rpm -qa |grep -i mysql

3.2 删除残留文件夹

(1)查找mysql相关目录

find / -name mysql

(2)删除相关目录

rm -rf /var/lib/mysql/
rm -rf /usr/lib64/mysql/

3.3 删除配置文件

(1)删除配置文件：
rm -rf /etc/my.cnf

(2)删除/var/log/mysqld.log
如果不删除这个文件，会导致新安装的mysql无法生存新密码，导致无法登陆
rm -rf /var/log/mysqld.log