（JAVA）04——MySQL的命令行操作

一、命令行操作：

1、本地登录：

mysql -u root -p

2、查看当前的业务逻辑数据库

show databases;

3、进入指定的逻辑数据库

use mysql;

4、查看当前数据库中所有的表：重点关注：user表

show tables;

5、查看表的具体结构

desc user;

6、执行SQL脚本文件：

source D:\sql\empdb.sql

7、查询语句

select * from emp;

select empno,ename from emp;

select * from emp where sal>=2000;

select * from emp where deptno=30;

select count(*) from emp;

select count(1) from emp;

8、模糊查询语句

select * from emp where ename like ‘%A%’;

select * from emp where ename like ‘__A%’;

9、添加语句

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(7369, ‘SMITH’, ‘CLERK’, 7902, now(),800, null, 20);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’,1250, 500, 30);
重点注意：主键自动增加
insert into emp(ename, job, mgr, hiredate, sal, comm, deptno) values(‘SMITH’, ‘CLERK’, 7902, now(),800, null, 20);

10、删除语句：

删除表中所有的数据
delete from emp;
根据条件删除：
delete from emp where deptno=30 and sal <1000;

11、更新语句

update emp set sal=10000.89;

update emp set sal=10000.89 where deptno=20;

update emp set sal=sal+1000 where empno=7788;

update emp set sal=sal+1000,comm=2000.78 where empno=7934;

12、聚合函数

最大值
select max(sal) from emp;
最小值
select min(sal) from emp;
平均值
select avg(sal) from emp;

13、排序

默认情况升序
select * from emp order by sal;

select * from emp order by sal asc;

降序
select * from emp order by sal desc;

14、分页查询

每页显示5条记录：
select * from emp limit 0,10;
select * from emp limit 5;
select * from emp limit 业务表达式,10;

二、第三方的可视化工具操作：很多

如果使用的MySQL是8.0(包括8.0)以上的版本
Navicat_for_MySQL_10连接数据库时要进行修改数据库的参数

如何修改参数：
①、本地登录
mysql -u root -p

②、进入mysql
use mysql;

③、修改远程主机
update user set host=’%’ where user=‘root’;

④、修改root用户的远程登录密码
alter user ‘root’@’%’ identified with mysql_native_password by ‘123456’;

⑤、查看修改后的结果：
select host,user,authentication_string from user;

±----------±-----------------±-----------------------------------------------------------------------+
| host | user | authentication_string |
±----------±-----------------±-----------------------------------------------------------------------+
| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
±----------±-----------------±-----------------------------------------------------------------------+
4 rows in set (0.00 sec)