JAVA学习之-MYSQL基础

常用命令：

进入数据库： mysql -u用户名 -p
===============================
退出：exit
===============================
查看所有数据库：show databases;
===============================
创建：create database 数据库名 character set utf8/gbk;
===============================
查看数据库详情：show create database 数据库名;
===============================
删除数据库： drop database 数据库名;
===============================
使用数据库：use 数据库名;
===============================
创建表：格式： create table 表名(字段1名 字段1类型,字段2名 字段2类型...);
===============================
查询表详情：show create table 表名;
===============================
创建表时指定引擎和字符集： create table 表名(字段1名 字段1类型，字段2名 字段2类型...) engine=innodb/myisam charset=utf8/gbk;
===============================
查看表字段： desc 表名;
===============================
删除表：drop table 表名;
===============================
查询所有表：show tables;
===============================
修改表名rename table 原名 to 新名;
===============================
修改引擎和字符集：alter table 表名 engine=myisam/innodb charset=utf8/gbk;
===============================
添加表字段
最后添加：alter table 表名 add 字段名 字段类型;
最前面添加：alter table 表名 add 字段名 字段类型 first;
在xxx后面添加：alter table 表名 add 要加的字段名 字段类型 after 被加的字段名;
===============================
删除表字段:alter table 表名 drop 字段名;
===============================
修改字段名和类型：alter table 表名 change 原名 新名 新类型;
===============================
======
数据相关
======
===============================
全表插入格式：insert into 表名 values(值1,值2,...);
===============================
指定字段插入格式：insert into 表名(字段1,字段2)values(值1,值2);
===============================
批量插入:在values后面通过，将多组数据写到一起
insert into 表名 values(值1,值2,值3),(值1,值2,值3);
===============================
接收时解码字符集：set names gbk/utf8;
===============================
查询数据:select 字段信息 from 表名 where 条件
select name from t1;//查询所有name
select name,age from t1;//查询name和age
select * from t1;//查询所有信息
select * from t1 where id<3;//查询id小与3的所有信息
===============================
修改数据:update 表名 set 字段名=值 where 条件;
update t1 set age=50 where name=‘马化腾‘;//马化腾age改为50
===============================
删除数据：delete from 表名 where 条件;
delete from t1;//全部删除
===============================

======
SQL数据类型
======

===============================
整数：int 和 bigint(等效long)
===============================
浮点数：double(m,d):超高精度浮点数
===============================
字符串:char(m)m代表字符长度 固定长度 最大255
varchar(m)可变长度 最大65535 超过255建议用text
text(m)可变长度 最大65535
===============================
日期:date:只能保存年月日
time:只能保存时分秒
datetime: 保存年月日时分秒，默认值为null，最大值：9999-12-31
timestamp（时间戳）:保存年月日时分秒，默认值为当前系统时间：2038-1-19
===============================

======
主键约束
======

===============================
主键：表示数据唯一性的字段称为主键
约束：创建表时给表字段添加的限制条件
主键约束： 限制主键的值 唯一且非空
create table t2(id int primary key, name varchar(10),age int);
insert into t2 values(1,‘aaa‘,10);//成功！
insert into t2 values(1,‘bbb‘,20);//失败 不能重复
insert into t2 values(null,‘ccc‘,30);//失败 不能为null
===============================
主健约束+自增,从历史最大值基础+1 只增不减 
create table 表名(id int primary key auto_increment,name varchar(10));
===============================

======
sql文件导入数据库
======

===============================
windows: source 某盘:/tables.sql;
导入失败就用\\
===============================
Linux:source /home/soft01/桌面/tables.sql;
===============================

======
is null 和 is not null
======

===============================
查询没有领导的员工姓名
select 要查询的 from 表名 where 领导 is null;
===============================
查询有领导的员工信息
select * from 表名 where 领导 is not null;
===============================

======
去重
======

===============================
查询员工表有哪些部门编号
select distinct 部门 from 表名;
===============================
查询员工表都是有哪些工作部门
select distinct 部门 from 表名;
===============================

======
比较运算符> < >= <= !=和<>
======

===============================
查询工资2000以下的员工和工资
select ename,sal from emp where sal<2000;
===============================
2. 查询职位(job)是manager的员工姓名、工资、职位
select ename,sal,job from emp where job=‘manager‘;
===============================
查询工资小于等于1600的员工姓名、职位、工资
select ename,job,sal from emp where sal<=1600;
===============================
查询不是10号部门的员工姓名和部门编号（两种写法）
select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;
===============================

======
and和or
======

===============================
查询需要同时满足多个条件用and
===============================
查询只需满足一个条件用or
===============================
查询10号部门中工资低于2000的员工姓名，工资和部门编号
select ename,sal,deptno from emp where deptno=10 and sal<2000; 
===============================
查询部门是30号或者工资高于3000的员工信息
select * from emp where deptno=30 or sal>3000;
===============================

======
模糊查询like
======

===============================
%代表0-多个未知字符_代表一个未知字符
_ 代表1个未知字符 
1. 查询第一个字符是x x%
2. 查询最后一个字符是y %y
3. 倒数第二个字符是x %x_
4. 以x开头以y结尾 x%y
5. 包含x %x%
6. 第二个字符是x倒数第三个是y _x%y__
===============================
查询名字以j开头的员工姓名
select ename from emp where ename like ‘j%‘;
===============================
查询名字第二个字符是l的员工姓名和工资
select ename,sal from emp where ename like ‘_l%‘;
===============================
查询包含a的员工姓名
select ename from emp where ename like ‘%a%‘;
===============================

===============================

======
between x and y 包含x和y
======

===============================
查询工资在2000到3000之间的员工姓名和工资
select ename,sal from emp where sal>=2000 and sal=<3000;
select ename,sal from emp where sal between 2000 and 3000;
===============================

======
in
======

===============================
查询工资为800，1300，1500的员工信息
select * from emp where sal=800 or sal=1300 or sal=1500;
select * from emp where sal in (800,1300,1500);
===============================

======
排序
======

===============================
order by 字段名 asc升序(默认)/desc降序;
===============================
查询每个员工的姓名和工资按照工资升序排序
select ename,sal from emp order by sal;
===============================
查询30号部门每个员工的姓名和奖金按照奖金降序排序
select ename,comm from emp where deptno=30 order by comm desc;
===============================

======
分页查询
======

===============================
limit 跳过条数,请求条数/每页条数
===============================
查询员工表信息工资降序 第三页 每页两条数据
select * from emp order by sal desc limit 4,2;
===============================
查询员工表工资前三名的员工姓名和工资
select ename,sal from emp order by sal desc limit 0,3;
===============================
查询工资最低的员工信息
select * from emp order by sal limit 0,1;
===============================

======
数值计算 + - * / %
======

===============================
查询每个员工的姓名、工资和年终奖(工资*5)
select ename,sal,sal*5 from emp;
===============================
查询员工表中每个员工涨薪1000块钱之后的姓名、涨薪前工资和涨薪后工资
select ename,sal,sal+1000 from emp;
===============================

======
聚合函数
======

===============================
对查询的多条数据进行统计查询： 平均值，最大值，最小值，求和，计数
===============================

======
平均值 avg(字段名)
======

===============================
查询30号部门的平均工资
select avg(sal) from emp where deptno=30;
===============================
查询工作是manager的员工的平均工资
select avg(sal) from emp where job=‘manager‘;
===============================

======
最大值 max(字段名)
======

===============================
查询10号部门工资最高值
select max(sal) from emp where deptno=10;
===============================

======
最小值 min(字段名)
======

===============================
查询20号部门工资的最低值
select min(sal) from emp where deptno=20;
===============================

======
求和 sum(字段名)
======

===============================
查询员工表中 30号部门的工资总和
select sum(sal) from emp where deptno=30;
===============================

======
计数 count(字段名/*)
======

===============================
如果制定字段有null值则不计入数值
===============================
查询20号部门工资大于2000块钱的员工人数
select count(*) from emp where deptno=20 and sal>2000;
===============================

======
分组查询
======

===============================
格式： group by 字段：题目需求中出现每个、每种时使用分组查询，
每个什么就以什么作为分组的字段
===============================
查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
===============================
查询每种工作的最高工资
select job,max(sal) from emp group by job;
===============================
查询每个部门的工资总和
select deptno,sum(sal) from emp group by deptno;
===============================
查询每个领导mgr的下属人数
select mgr,count(*) from emp 
where mgr is not null group by mgr;
===============================

======
having 
======

===============================
from 后面只能写普通字段的条件
如果条件是聚合函数的条件则需要使用having
having一定要和group by 结合使用
格式：select .... from 表名 where ..... group by .... having....
order by .... limit ....;
===============================
查询每个部门的平均工资 要求查询平均工资大于2000的
select deptno,avg(sal) from emp group by deptno
having avg(sal)>2000;
别名： 
select deptno 部门编号,avg(sal) a from emp group by deptno
having a>2000;
===============================
查询emp表中工资在1000-3000之间的员工，每个部门的编号，
工资总和，平均工资，过滤掉平均工资低于2000的部门，最后按照平均工资进行升序排序.
select deptno,sum(sal),avg(sal) a from emp 
where sal between 1000 and 3000
group by deptno
having a>=2000 
order by a;
===============================

=====
子查询（嵌套查询）
=====

===============================
查询工资高于平均工资的员工信息
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
===============================
查询员工表中工资最高的员工信息
select * from emp where sal=(select max(sal) from emp);
===============================
查询工资高于20号部门最高工资的员工信息
select * from emp where sal>(select max(sal) from emp where deptno=20);
===============================
查询和jones相同工作的其它员工信息
select job from emp where ename=‘jones‘;
select * from emp where job=(select job from emp where ename=‘jones‘) and ename!=‘jones‘;
===============================
查询工资最低员工的同事们的信息(同事指相同部门)
先得到最低工资
select min(sal) from emp;
通过最低工资查所在部门编号
select deptno from emp sal=(select min(sal) from emp);
通过部门编号查询该部门的人 排除掉工资最低的
select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
===============================
查询king的部门信息（需要用到dept表 ）
select deptno from emp where ename=‘king‘;
select * from dept where deptno=(select deptno from emp where ename=‘king‘);
===============================

======
关联查询
======

===============================
同时查询多张表的查询方式称为关联查询
-关联查询必须写关联关系，如果不写会得到两张表数据的乘积（这个乘积叫做笛卡尔积，这是一个错误的查询结果 工作中切记不要出现） 
===============================
查询每个员工的姓名和对应的部门名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
===============================
关联查询查询方式：
1. 等值连接： select * from A,B where A.x=B.x and A.age=18;
2. 内连接： select * from A join B on A.x=B.x where A.age=18;
===============================
查询每个员工的姓名和对应的部门名 
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
===============================
等值连接和内连接查询到的数据是两张表的交集数据
外连接：select * from A left/right join B on A.x=B.x where A.age=18;
外连接查询的是一张表的全部和另外一张表的交集数据
查询所有部门的名称和对应的员工姓名
select d.dname,e.ename from emp e right join dept d on e.deptno=d.deptno;
===============================
总结： 如果查询的是两张表的交集数据则使用等值连接或内连接（推荐）如果查询的是一张表的全部和另一张表的交集则使用外连接
===============================

======
关联关系
======

===============================
关联关系指创建表时两张表之间存在的业务关系
外键：用于建立关系的字段称为外键，一个表只有一个主键但是可以有多个外键
===============================

======
一对一
======

===============================
有AB两张表，A表中一条数据对应B表中一条数据，同时B表中一条数据也对应A表中的一条数据
如何建立关系：在从表中添加外键指向主表的主键
===============================

======
一对多
======

===============================
有AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条数据
如何建立关系：在多的表中添加外键指向另外一张表的主键
===============================

======
多对多
======

===============================
有AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的多条数据
如何建立关系：创建一个单独关系表，表中两个外键字段指向其它两个表的主键

JAVA学习之-MYSQL基础

标签：技术   删除   order by   having   values   等值连接   建表   myisam   分页查询