Mysql数据库·增删改查.doc

Mysql Oracle(甲骨文) 大型数据库 MySql 中小型数据库 DB2 SqlServer .Mysql的发展:瑞典的Mysql AB公司2008年 Sun公司(JAVA)2009年 Oracle 收购 sun公司IBM69亿美元 sun Eclipse(日蚀)Oracle 74亿美元 sunMysql的简单使用:1.登陆mysql数据库win+r ->cmdmysql -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(小数)日期类型: datea.创建表员工表:员工号 姓名 性别 年龄 职位 薪水 入职日期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中右键选择new3.配置连接:Driver Template: MySQL Connector/JDriver name: 随便起名字Connection URL: jdbc:mysql:/localhost:3306/test 本机:localhost127.0.0.l192.168.4.223User name:rootpassword:1234Driver JARs: mysql-connector-java-5.17-bin.jarMysql常见的错误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 >10000select 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 <=18000select empno,ename,salary from emp where salary >=10000 and salary <=18000;- between and:在.之间.select empno ,ename, salary from empwhere salary between 10000 and 18000;select * from emp ;- 5.查询薪水是10000或者12000的员工.select empno,ename,salary from empwhere salary =10000 or salary =12000;- in(10000,12000);select empno ,ename,salary from empwhere 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 empwhere job in('程序猿','攻城师') and salary between 10000 and 20000 and sex = 'm'- 错误演示select empno ,ename,job, salary ,sex from empwhere job='程序猿' or job='攻城师' and salary between 10000 and 20000 and sex = 'm'select empno ,ename,job, salary ,sex from empwhere 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-31select ename ,hiredate from emp where hiredate between '2012-01-01' and '2012-12-31'- year(date):提取日期中的年份- year(2012-12-12)->2012select 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_salaryfrom 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 ; - 10select count(ename) from emp ; - 10select count(*) from emp ; - 10select 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) >=10000order 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_selectwhere 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 don 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 don e.deptno = d.deptnogroup 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,dnamefrom dept d right outer join emp eon 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 ,levelfrom 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