【Mysql】 MySQL 常用命令

目录

一、通用操作

1.1 启动/关闭/登录

1.2 数据库连接管理

1.2.1 查看数据库最大连接数

1.2.2 当前数据库连接数

1.2.3 修改数据库最大连接数

二、数据库操作

2.1 查看所有数据库列表

2.2 创建/删除/进入数据库

三、 表操作

3.1 对表或表结构操作

3.1.1 查看表列表

3.1.2 创建表

3.1.3 查看表结构

3.1.4 删除表

3.1.5 增加字段

3.1.6 删除字段

3.1.7 修改字段类型

3.2 对表内容进行操作

3.2.1 插入数据

3.2.2 清空表

3.2.3 复制表

3.2.4 修改表内容

3.3 查询

3.3.1 取别名

3.3.2 查询去重(distinct)

3.3.3 条件查询

3.3.4 范围查询

四、查询进阶

4.1 分组函数(group by, having)

4.1.1 使用 group by 进行分组查询

4.1.2 使用 having 对结果进行再次筛选

4.2 子查询

4.3 多表查询

4.3.1 多表查询

4.3.2  join(联合) ... on（条件）

4.3.3  between ... and 

4.3.4 复杂查询

---

一、通用操作

1.1 启动/关闭/登录

# 启动

service mysqld start

# 关闭

service mysqld start

# 登陆

mysql -uroot -proot.123

 

1.2 数据库连接管理

1.2.1 查看数据库最大连接数

show variables like '%max_connection%';

1.2.2 当前数据库连接数

show status like  'Threads%';

  

1.2.3 修改数据库最大连接数

set global max_connections=1000;
flush privileges;

 

二、数据库操作

2.1 查看所有数据库列表

show databases;

2.2 创建/删除/进入数据库

# 创建数据库
create database testDB;

# 删除数据库
drop database testDB;

# 进入某个数据库
use testDB;

 

三、 表操作

3.1 对表或表结构操作

3.1.1 查看表列表

shouw tables;

3.1.2 创建表

auto_increment 令主键自增长

auto_increment =4 代表从4开始自增长

primary key 用于指定主键

create table test333(
	id int(4) not null auto_increment COMMENT '用户ID',
	name varchar(50) default null COMMENT '用户名称',
	money float default null COMMENT '金额',
	primary key(id)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

3.1.3 查看表结构

desc testDB;

3.1.4 删除表

drop table testDB;

3.1.5 增加字段

alter table testDB add count int(4);

3.1.6 删除字段

alter table testDB drop count;

3.1.7 修改字段类型

alter table testDB modify count varchar(50)

 

3.2 对表内容进行操作

3.2.1 插入数据

insert into test333(name,money,count) VALUES('小明',500.00,'666');

3.2.2 清空表

delete from testDB;

3.2.3 复制表

# 复制表结构，建新表
create table testDB2 like testDB

# 将所有旧表的数据插入新表
insert into stu2 select * from stu;

3.2.4 修改表内容

# 修改字段的所有值
update testDB set money=500.00;

# 条件修改
update stu set sex = 1 where num = 3;

# 多字段修改
update stu set name='hahah',sex='2',age=18,schooldate='2019-09-22' where num=1;

3.2.5 增加/删除外键

增加外键：

alter table tom2 add foreign key(class) references class(id);

删除外键：

alter table tom2 drop foreign key tom2_ibfk_1;

查看外键名称 ：

show create table tom2;

 

3.3 查询

3.3.1 取别名

# 查询 student 表中字段为 classDate 的数据，并以date字段的形式展现
select classDate date from student;
select classDate date, sName name from student;
select classDate as date from student;

3.3.2 查询去重(distinct)

# 查询去重只能查询一个字段，不能同时查询多个字段
select distinct sex from stu;

3.3.3 条件查询

# 条件查询
select * from stu where sex ='2';
select * from stu where age <= 24;
select * from stu where age <> 24; // 查看年龄不等于24的学生信息

# 多条件查询
select * from stu where age < 24 and sex = 'man'; // 查找年龄小于24岁的男同学的信息

3.3.4 范围查询

# 查找年龄在23~25岁之间的同学的信息
select * from stu where age in(23,24,25); 

# 查找年龄在23~25岁之间的同学的信息
select * from stu where age between 23 and 25;  

# 查找除年龄在23~25岁外的同学的信息
select * from stu where age not in(23,24,25);

# 获取结果为 null 或不为 null 的数据
// 查询雇员表中工资不为空的数据
select * from emp where sal is not null; 

# 查询雇员表中工资为空的数据
select * from emp where sal is null; 

 

四、查询进阶

4.1 分组函数(group by, having)

4.1.1 使用 group by 进行分组查询

根据组的类别查询组的平均工资

select avg(sal) from emp group by depno;

  

此时有 3 个组

  

 

分别查出三个组的所有员工平均工资

 

  

 

4.1.2 使用 having 对结果进行再次筛选

使用 having 关键字，可以让搜索结果根据最后结果的字段再次筛选

先筛选出各个部门的平均工资，并以avg_sal展示

select avg(sal) avg_sal,deptno from emp group by deptno;

若此时只要显示 avg_sal > 2000的搜索结果

select avg(sal) avg_sal,deptno from emp group by deptno where avg_sal > 2000;

原因是没有使用 having 字段，他可以让你在搜索结果后，再次筛选，而不是使用 where

去掉where，改为having，则搜索成功

select avg(sal) avg_sal,deptno from emp group by deptno having avg_sal > 2000;

 

4.2 子查询

子查询即将一个查询的结果作为另一个查询的数据源或者条件，在语句展现为循环嵌套

# 搜索工资高于当前所属部门的平均工资的人
select ename,deptno from emp where sal > (select avg(sal) from emp where deptno);

# 搜索是经理人的人
select ename,mgr from emp where empno in (select mgr from emp);

# 多层次子查询

1. 前提条件查出来的结果只能以表的形式展现，即临时表，要给临时表起别名

2. 分组函数的结果不能嵌套，但可以起别名

# 查找雇员表中平均薪资最高的部门
select max(avg_sal),deptno from (select avg(sal) avg_sal,deptno from emp group by deptno) avg_table;

 

4.3 多表查询

4.3.1 多表查询

# 查部门表和雇员表的数据，显示出雇员所在的部门名称

select ename,dname from emp e,dept d where e.deptno=d.deptno;

 

4.3.2  join(联合) ... on（条件）

select ename,dname from emp e join dept d on e.deptno=d.deptno;

 

4.3.3  between ... and 

select ename,dname,grade from emp e join dept d join salgrade s on e.deptno = d.deptno and e.sal between losal and hisal;

 

4.3.4 复杂查询

# 部门平均的薪水等级
select grade,t.deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) t join salgrade s on t.avg_sal between s.losal and s.hisal;

# 雇员之中哪些人是经理人
select e.empno,e.ename from emp e join emp t on e.empno = t.mgr;

# 平均薪水最高的部门编号
select deptno,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t where t.avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal from emp group by deptno)no) e);

# 平均薪水最高的部门名称
select t.deptno,avg_sal,dname from (select deptno,avg(sal) avg_sal from emp group by deptno) t join dept on t.avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal from emp p group by deptno) e) and dept.deptno = t.deptno;

#平均薪水的等级最低部门的部门名称
select temp.deptno,temp.avg_sal,grade from salgrade join (select t.deptno,avg_sal,dname from (select deptno,avg(sal) avg_sal from emp group by deptno) t join dept on t.avg_sal = (select min(avg_sal) from (select avg(sal) avg_sal from emp p group by deptno) e) and dept.deptno = t.deptno) temp on temp.avg_sal between losal and hisal;