Mysql数据库·增删改查

Mysql
Oracle(甲骨文) 大型数据库
MySql 中小型数据库
DB2
SqlServer
.....
Mysql的发展:
瑞典的Mysql AB公司
2008年Sun公司(JAVA)
2009年Oracle 收购sun公司
IBM 69亿美元sun Eclipse(日蚀)
Oracle 74亿美元sun
Mysql的简单使用:
1.登陆mysql数据库
win+r --->cmd
mysql -uroot -p1234
修改密码：mysql> set password for rootlocalhost = password('1234');
此处可能存在异常情况原因：a、未配置环境变量b、Mysql服务未开启（net start mysql）
2.对库的操作
a.查看所有的库
show databases;
系统自带库:
information_schema mysql test
b.创建库
create database day01;(不指定编码,跟随数据库系统编码)
create database db1
default character set gbk;(指定编码)
查看创建库的语句:
show create database 库名.
修改库的编码:
alter database day01
default character set utf8;
c.删除库
drop database 库名.
drop database day01;
注意:
系统自带的三个库不能删除.
d.使用库
use db1;
3.对表的操作
表:二维关系表有行有列的关系表.
记录:表中的一行数据.
字段:表中的一列.
常用的字段类型:
字符串类型: varchar(长度) 、char
数值类型: int(整数) float double(小数)
日期类型: date
a.创建表
员工表:员工号性别年龄职位薪水入职日期
create table emp(
empno varchar(4),
name varchar(30),
sex varchar(5),
age int(3),
job varchar(30),
salary int(5),
hiredate date
);
b.查看所有的表
show tables;
c.查看建表语句
show create table 表名.
d.查看表结构
desc 表名.
e.往表中插入数据
e1.给表中所有的字段插入数据
insert into emp
(empno,name,sex,age,job,salary,hiredate)
values
('1001','zhangsan','m',22,'developer',10000,'2015-12-21');
简写形式:
insert into emp values
('1002','lisi','m',23,'test',8000,'2015-10-10');
e2.给表中部分字段插入数据
insert into emp(empno,name,sex,age) values
('1003','cuihua','w',18);
解决插入中文问题：（eclipse中的设置）
Connection URL:
jdbc:mysql://localhost:3306/test
?useUnicode=true&characterEncoding=gbk 插入中文:
insert into emp values(
'1005','莫小贝','女',12,'武林盟主',20000,'2015-12-12'
);
f.删除数据
delete from emp ;-->删除表中所有数据
delete from emp where empno=1004;
MyEclipse配置Mysql连接:
1.切换到数据库界面.
2.在DBBroswer中右键选择new
3.配置连接:
Driver Template: MySQL Connector/J
Driver name: 随便起名字
Connection URL: jdbc:mysql://localhost:3306/test
本机:
localhost
127.0.0.l
192.168.4.223
User name:root
password:1234
Driver JARs: mysql-connector-java-5.17-bin.jar
Mysql常见的错误
1.Can't create database 'xxx'; database exists
不能创建xxx库,因为已经存在
2.Can't drop database 'xxx'; database doesn't exist
不能删除xxx库,因为已经不存在
-- 创建库
create database search default character set gbk ;
-- 使用库
use search;
-- 创建表
-- 员工信息表
create table emp(
empno int(4), -- 员工编号
ename varchar(30), -- 员工
job varchar(30), -- 职位
salary int , -- 工资
bonus int, -- 奖金
age int(3), -- 年龄
sex varchar(1), -- 性别
hiredate date, -- 入职日期
manager int(4), -- 领导编号
deptno int -- 部门编号
);
-- 部门表:
create table dept(
deptno int, -- 部门编号
dname varchar(50),-- 部门名称
loc varchar(80) -- 部门位置
);
-- 往emp表中插入数据null 空:没有不存在
insert into emp(empno,ename,job,salary,bonus,age,sex,hiredate,manager,deptno) values(1001,'三丰','老板',20000,100,30,'m','2009-02-02',null,10);
insert into emp values(1002,'无忌','程序猿',12000,200,20,'m','2010-03-02',1001,10); insert into emp values(1003,'小龙女','程序猿',10000,300,18,'f','2012-12-02',1001,10); insert into emp values(1004,'过','程序猿',10000,200,22,'m','2012-12-22',1003,10); insert into emp values(1005,'黄蓉','攻城师',15000,100,25,'f','2012-12-28',1001,20); insert into emp values(1006,'郭靖','攻城师',13000,100,28,'m','2013-12-02',1005,20); insert into emp values(1007,'莫愁','秘书',8000,50,22,'f','2013-03-02',1001,30);
insert into emp values(1008,'韦小宝','实施',8500,null,29,'m','2013-04-02',1001,30); insert into emp values(1009,'陆无双','实施',6000,100,26,'m','2014-12-12',1001,30); insert into emp values(1010,'黄飞鸿','打手',5000,100,20,'m','2015-03-02',1001,null);
-- 给dept表插入数据
insert into dept values(10,'研发部','');
insert into dept values(20,'运维部','');
insert into dept values(30,'实施部','');
-- 查询
use search ;
-- 1.查询emp表中的所有数据
select * from emp ;
-- * 通配符通配了emp表中的所有列。

select empno,ename,job,salary,bonus,age,
hiredate,sex ,manager,deptno from emp ;
-- select(选择，筛选..) from(从...地方)
-- 执行顺序: from -->select
-- 2.查询emp表中员工名字以及员工的薪水
select ename,salary from emp ;
-- 查询部分字段: select 字段名1,字段名2.... from 表;
-- 3.查询薪水大于10000的员工.
-- 查什么员工--->ename --->empno --->salary
-- 查询条件是什么salary >10000
select empno,ename,salary
from emp where salary>10000;
-- select from where
-- 执行顺序: from --> where -->select
-- 4.查询薪水大于等于10000并且小于等于18000的员工
-- 查什么empno ename salary
-- 查询的条件是什么10000=<salary<=18000(数学中的写法) -- salary >=10000 and && salary <=18000
select empno,ename,salary from emp
where salary >=10000 and salary <=18000;
-- between and:在...之间.
select empno ,ename, salary from emp
where salary between 10000 and 18000;
select * from emp ;
-- 5.查询薪水是10000或者12000的员工.
select empno,ename,salary from emp
where salary =10000 or salary =12000;
-- in(10000,12000);
select empno ,ename,salary from emp
where salary in(10000,12000);
-- 6.查询职位是程序猿或者是攻城师并且薪水在10000 到
-- 20000之间，并且性别是男的员工
-- 查什么empno ename job salary sex
-- 查询的条件是什么
-- a.职位是程序猿或者是攻城师job in('程序猿','攻城师')
-- b.薪水在10000到20000之间salary between 10000 and 20000 -- c.性别是男sex='m'
-- a and b and c
select empno ,ename,job, salary ,sex from emp
where job in('程序猿','攻城师') and
salary between 10000 and 20000 and
sex = 'm';
-- 错误演示
select empno ,ename,job, salary ,sex from emp
where job='程序猿' or job='攻城师' and
salary between 10000 and 20000 and
sex = 'm';
select empno ,ename,job, salary ,sex from emp
where job='攻城师' or job='程序猿' and
salary between 10000 and 20000 and
sex = 'm';
-- 7.查询奖金为null的员工
select ename ,bonus from emp where bonus = null;
select * from emp ;
-- null: 空没有不存在
-- 和null做比较不能使用= > <.. 要使用is
select ename,bonus from emp
where bonus is null ;
-- 8.查询奖金不是null的员工
select ename ,bonus from emp
where bonus is not null ;
-- 9.查询所有员工的年薪12*(salary+bonus)
select ename, 12*(salary+bonus) from emp;
-- 与null做运算，结果都为null
-- 空值处理函数: ifnull()
-- ifnull(bonus,0):
-- 如果bonus为null，则处理成0
-- 如果bonus不为null,则不处理.按照bonus实际的值运算.
select ename,12*(salary+ifnull(bonus,0))
from emp ;
-- 函数: 单行函数组函数(聚合函数)
-- 单行函数: 单行函数会对表中的每一条记录进行操作
-- 并且每一条记录都会产生一个结果.
-- now(): 查询当前时间
select now() from emp ;
-- 10.查询2012年入职的员工2012-01-01 2012-12-31
select ename ,hiredate from emp
where hiredate between '2012-01-01'
and '2012-12-31';
-- year(date):提取日期中的年份
-- year(2012-12-12)--->2012
select ename,hiredate from emp
where year(hiredate) = 2012;
-- 11.查询员工的工作年限
select ename, year(now())-year(hiredate) from emp ;
-- 12.查询实现如下结果: 我是xxx,我的职位是xxx
-- concat('我是',ename,',我的职位是',job ); 连接函数.将给定的字段连接成字符串。

select '我是' from emp ;
select concat('我是',ename,'，我的职位是',job) description from emp ;
-- 别名: 就是给查询的结果中的列取另外一个名字
-- select ename as name from emp ; -- name
-- select ename name from emp ; -- as可以省略的
-- 13.通过查询实现: 10部门的薪水涨20%
-- 20部门的薪水涨10%
-- 30部门的薪水保持不变
-- 其他部门的薪水涨500
-- 多路分支case when then else end
-- case 开始分支
-- when 判断分支条件then 执行分支结果
-- when .. then....
-- else 上述情况都不满足，执行else
-- end 结束分支
select empno,deptno,salary,
case
when deptno =10 then salary*1.2
when deptno =20 then salary*1.1
when deptno =30 then salary
else salary+500
end new_salary
from emp ;
-- 排序
-- order by 排序字段desc(降序) asc(升序默认的排序) -- 14.查询所有的员工，按照薪水降序排序
select * from emp order by salary desc ;
-- 15.查询所有的员工，按照薪水升序排序
select * from emp order by salary asc;
-- 16.查询所有的员工，按照年薪降序排序
select ename, 12*(salary+ifnull(bonus,0)) year_sal
from emp order by year_sal desc ;
-- select from where order by
-- 执行顺序: from-->where-->select-->order by
-- 17.查询员工名字中带有''的员工
-- like %:任意个任意字符
-- _:一个任意字符
select ename from emp
where ename like '%%';
-- 18.查询员工名字中第二个字为'无'的员工
select ename from emp
where ename like '_无%';
-- 19.查询职位中带有'猿'字并且薪水在10000到20000之间
-- 并且性别是男的员工按照入职日期降序排序.
-- 查什么ename job salary sex hiredate
-- 查询条件是什么
-- 1. 职位中带有'猿'字job like '%猿%'
-- 2. 薪水在10000到20000之间salary between 10000 and 20000 -- 3. 性别是男sex ='m'
-- 1 and 2 and 3
select ename,job,salary ,sex, hiredate
from emp where job like '%猿%' and
salary between 10000 and 20000 and
sex = 'm' order by hiredate desc ;
-- 20.统计表中总共有多少条记录
select * from emp ;
-- count():统计函数
select count(empno) from emp ; -- 10
select count(ename) from emp ; -- 10
select count(*) from emp ; -- 10
select count(bonus) from emp ; -- 9
-- null值不会被统计。

-- 组函数(聚合函数):
-- 组函数是对表中的一列数据进行计算并返回单一的结果.
-- 21.查询所有员工的薪水和.
-- sum()
select sum(salary) from emp ;
-- 22.查询所有员工的奖金和.
select sum(bonus) from emp ;
select sum(ifnull(bonus,0)) from emp ;
-- 23.查询所有员工的平均薪水
-- avg()
select avg(salary) from emp ;
-- 24.查询所有员工的平均奖金.
select avg(bonus) from emp ;
select sum(bonus)/count(bonus) from emp ;
select avg(ifnull(bonus,0)) from emp ;
select sum(bonus)/count(ifnull(bonus,0)) from emp ; -- 25.查询员工薪水的最大值
-- max()
select max(salary) from emp ;
-- 26.查询员工薪水的最小值
-- min()
select min(salary) from emp ;
-- 27.员工奖金的最大值
select max(bonus) from emp ;
-- 28.员工奖金的最小值
select min(bonus) from emp
where bonus is not null ;
-- 29.查询10部门的平均薪水
select avg(salary) from emp where deptno = 10;
-- 30.职位是程序猿的薪水和.
select sum(salary) from emp where job ='程序猿';
-- 31.查询每个职位的薪水总和.
select job , sum(salary) from emp
group by job ;
-- 分组(group by 分组字段)
-- 分组后,select后面只能跟组标识,组函数,
-- 组标识或者组函数的表达式.
-- 不能直接跟单行字段或者单行函数.
-- 32.查询每个部门的平均薪水
select deptno,avg(salary) from emp
group by deptno;
-- 33.查询部门人数大于等于3人的部门.
select deptno,count(empno) from emp
group by deptno having count(empno) >=3;
-- 分组后，条件的过滤不能使用where ，要使用having
-- 分组前用where 分组后用having
-- 34.查询职位不是程序猿的员工中!=
-- 部门平均薪水大于或者等于10000的部门
-- 按照平均薪水降序排序
select deptno ,avg(salary) from emp
where job != '程序猿'
group by deptno having avg(salary) >=10000
order by avg(salary) desc ;
-- select from where group by having order by
-- 执行顺序:from->where->group by->
-- having->select->order by
-- 35.查询薪水最高的员工
-- 1. 查询最高薪水
select max(salary) from emp ;
-- 2. 查询最高薪水对应的员工
-- select ename from emp where salary = 最高薪水.
select ename from emp where salary =
(select max(salary) from emp );
-- 子查询: 当一个查询的结果是另一个查询的条件时，
-- 称之为子查询.
-- 非关联子查询关联子查询
-- 非关联子查询:先执行子查询，子查询的结果做为主查询的-- 条件,再执行主查询. 子查询只执行一次.
-- 36. 查询最晚入职的员工
select ename from emp where hiredate =
(select max(hiredate) from emp );
-- 37.哪些员工的薪水比莫愁的薪水高.
select ename from emp where salary >
( select salary from emp where ename='莫愁');
-- 38.哪些部门的平均薪水比30部门的平均薪水高.
select deptno ,avg(salary) from emp
group by deptno having avg(salary)>
(select avg(salary) from emp where deptno =30);
-- 39.哪些员工和小龙女是同薪水，同职位，不能是小龙女本人.
select ename from emp where salary =
(select salary from emp where ename='小龙女')
and job =
(select job from emp where ename='小龙女')
and ename!='小龙女';
-- 多列非关联子查询
-- 关联子查询
-- 40.哪些员工的薪水等于他本部门的平均薪水
select ename from emp where
(deptno,salary) in
(select deptno ,avg(salary) from emp
group by deptno) ;
insert into emp(empno,ename,job,salary,bonus,age
,sex,hiredate,manager,deptno)
values(1011,'二丰','老板娘',20000,100,30,'m',
'2009-02-02',null,40
);
insert into dept values(40,'财务部','天津');
select * from emp ;
select * from dept;
-- 子查询: 一个查询的结果作为另一个查询的条件.
-- 非关联子查询
-- 先执行子查询，子查询的返回结果做为主查询的条件.
-- 再执行主查询,子查询只执行一次.
-- 关联子查询
-- 采用循环的方式,查询先重外部查询开始,获取一条记录后，-- 将其传入部查询.部查询根据外部查询传入的记录，
-- 可以查询结果,然后将结果传回外部查询中. 外部查询根据-- 部查询的结果判断此条记录是否满足条件，若满足条件
-- 则放入结果集，将来返回，否则放弃此条记录.
-- 1.查询emp表中工资最高的人.
select * from emp ;
-- a.查询最高的工资
select max(salary) from emp ;
-- b.查最高工资的人
select ename from emp where salary =(
select max(salary) from emp
);
-- 2.查询emp表中比莫愁的薪水高的人.
select ename from emp where salary >(
select salary from emp where ename='莫愁' );
-- 3.查询员工的薪水等于本部门的平均薪水.
-- a.查询每个部门的平均工资
select deptno ,avg(salary) from emp
group by deptno ;
-- b.查询个人的工资是否等于本部门平均工资
select ename from emp where (deptno,salary) in( select deptno ,avg(salary) from emp
group by deptno
);
-- 4.哪些员工的工资比他所在部门的平均工资高.
select main_select.ename,main_select.salary
from emp main_select
where salary > (
select avg(salary) from emp sub_select
where sub_select.deptno =
main_select.deptno);
-- 连接查询
-- 连接外连接(左外连|右外连|全外连...) 交叉连接...
-- 连接: 取交集
-- 外连接: 主(驱动)从(匹配)表
-- 连接: A inner join B on 连接条件
-- inner可以省略
-- 5.查询每个人的以及部门名称.
select * from emp ;
select * from dept ;
select ename,dname
from emp e inner join dept d
on e.deptno = d.deptno;
insert into emp values(1012,'莫小贝','武林盟主',25000,500,12,'f','2010-10-10',null,50); delete from emp where empno =1012;
insert into dept values(60,'测试部','');
-- 6.查询部门人数大于等于3人的部门(部门名称)
select dname,count(empno)
from emp e inner join dept d
on e.deptno = d.deptno
group by dname having count(empno) >=3;
-- 外连接: A left outer join B on...
-- A right outer join B on ...
-- outer可以省略。

-- 7. 查询员工名以及部门名称.(外连)
-- 左外连emp主dept从
select ename,dname
from emp e left outer join dept d
on e.deptno = d.deptno ;
-- 右外连
select ename,dname
from dept d right outer join emp e
on d.deptno = e.deptno ;
-- 左外连emp从dept 主
select ename,dname
from dept d left outer join emp e
on d.deptno = e.deptno ;
-- 右外连
select ename,dname
from emp e right outer join dept d
on e.deptno = d.deptno;
-- 创建薪水等级表
create table sal_level(
level int , -- 薪水等级
low_sal int , -- 低值
high_sal int -- 高值
);
insert into sal_level values(1,2001,4000); insert into sal_level values(2,4001,8000); insert into sal_level values(3,8001,12000); insert into sal_level values(4,12001,20000); insert into sal_level values(5,20001,50000); select * from sal_level;
-- 8.查询员工以及薪水对应的等级
select ename,salary,level
from emp e inner join sal_level sl
on e.salary between sl.low_sal and
sl.high_sal;
-- 9.查询员工，部门名称，薪水等级.
select ename, dname ,level
from emp e inner join dept d
on e.deptno= d.deptno inner join
sal_level sl on e.salary between
sl.low_sal and sl.high_sal;
--10分页查询limit a,b a代表下标，b代表几条数据select *from emp limit 0,3;。