Mysql数据库_增删改查.docx

《Mysql数据库_增删改查.docx》由会员分享，可在线阅读，更多相关《Mysql数据库_增删改查.docx（16页珍藏版）》请在三一文库上搜索。

1、MysqlOracle(甲骨文)大型数据库MySql中小型数据库DB2SqlServerMysql的发展:瑞典的Mysql AB公司2008 年 Sun 公司（JAVA）2009年Oracle 收购sun公司IBM 69 亿美元 sun Eclipse（ 日蚀）Oracle 74 亿美元 sunMysql的简单使用：1 .登陆mysql数据库win+r -cmdmysql -uroot -p1234修改密码：mysql set password for rootlocalhost = password(1234);此处可能存在异常情况原因：a、未配置环境变量b、Mysql服务未开启(net s

2、tart mysql)2 .对库的操作a.查看所有的库show databases;系统自带库：information_schema mysql testb.创建库create database day01;(不指定编码，跟随数据库系统编码)create database db1default character set gbk;（ 指定编码 ）查看创建库的语句：show create database 库名.修改库白编码：alter database day01default character set utf8;c.删除库drop database 库名.drop database day

3、01;一、/ 汪后：系统自带的三个库不能删除.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 dat

4、e );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(

5、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 配置 My

6、sql 连接：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 .Cant create database xxx; databas

7、e exists不能创建xxx库，因为已经存在2 .Cant drop database xxx; database doesnt 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),-年龄s

8、ex varchar(1),-性另 Uhiredate 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

9、,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,工2012-12-02,1001,10);insert into emp values(1004, 过 , 程 序 猿 ,10000,200,22,m,2012-12-22,1003,10);insertintoempvalues(1005,黄蓉,攻城师,15000,100,25

10、,工2012-12-28,1001,20);insertintoempvalues(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）;insertintoempvalues（1009,陆无双,实施,6000,100,26,m,2014-12-12,1001,30）;i

11、nsertintoempvalues（1010,黄飞鸿,打手,5000,100,20,m,2015-03-02,1001,null）;- -给dept表插入数据insert intodept values（10,研发部,）;insert intodept values（20,运维部,）;insert intodept values（30,实施部,）;- -查询use search ;- -1.查询emp表中的所有数据select * from emp ;- -* 通配符 通配了 emp表中的所有列。select empno,ename,job,salary,bonus,age, hiredat

12、e,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,salaryfrom emp where salary10000;- -select

13、 from where- - 执行顺序：from - where -select- -4.查询薪水大于等于10000并且小于等于18000的员工- -查什么 empno ename salary- -查询的条件是什么10000=salary=10000 and & salary =10000 and salary 2012select ename,hiredate from emp where year(hiredate) = 2012;- -11.查询员工的工作年限select ename, year(now()-year(hiredate) from emp ;- -12.查询实现如下结果

14、：我是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%2030部门的薪水涨10% 部门的薪水保持不变其他部门的薪水涨

15、 500- - 多路分支case when then else end- -case开始分支- -when判断分支条件 then执行分支结果- -when . then.- -else上述情况都不?足，执行 else- -end结束分支select empno,deptno,salary,casewhen deptno =10 then salary*1.2when deptno =20 then salary*1.1when deptno =30 then salaryelse salary+500end new_salaryfrom emp ;-排序-order by排序字段 desc（降

16、序）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-or

17、der 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. 薪水在 10

18、000 到 20000 之间 salary between 10000 and 20000- -3.性别是男sex =m- -1 and 2 and 3select ename,job,salary ,sex, hiredate from emp where job like %猿 andsalary between 10000 and 20000 and sex = m order by hiredate desc ;- -20.统计表中总共有多少条记录select * from emp ;-count():统计函数select count(empno) from emp ; - 10sel

19、ect 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.查询所有员工的平

20、均薪水一 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

21、(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 em

22、p group by job ;- -分组(group by 分组字段)- -分组后，select后面只能跟组标识，组函数，- -组标识或者组函数的表达式.- -不能直接跟单行字段或者单行函数.- -32.查询每个部门的平均薪水select deptno,avg(salary) from emp group by deptno;- -33.查询部门人数大于等于3人的部门.select deptno,count(empno) from empgroup by deptno having count(empno) =3;- -分组后，条件的过滤不能使用 where ,要使用having- -分组前

23、用where 分组后用having- -34.查询职位不是程序猿的员工中！二- -部门平均薪水大于或者等于10000的部门- -按照平均薪水降序排序select deptno ,avg(salary) from empwhere 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.查询薪

24、水最高的员工- -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

25、 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);-

26、 -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 empgroup b

27、y 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;- -子查询：一个查询的结果作为另一个查询的条件- -非关联子查询- -先执行子查询，子查询的返回结果做为主查询的条件.- -再执行主查询，子查询只执行一次.- -关联子

28、查询- -采用循环的方式，查询先重外部查询开始，获取一条记录后，- -将其传入部查询.部查询根据外部查询传入的记录，- -可以查询结果，然后将结果传回外部查询中.外部查询根据- -部查询的结果判断此条记录是否满足条件，若满足条件- -则放入结果集，将来返回，否则放弃此条记录- -1.查询emp表中工资最高的人.select * from emp ;- -a.查询最高的工资select max(salary) from emp ;- -b.查最高工资的人select ename from emp where salary =( select max(salary) from emp);- -2.

29、查询emp表中比莫愁的薪水高的人.select ename from emp where salary ( select salary from emp where ename= 莫愁);- -3.查询员工的薪水等于本部门的平均薪水.- -a.查询每个部门的平均工资select deptno ,avg(salary) from empgroup by deptno ;- -b.查询个人的工资是否等于本部门平均工资select ename from emp where (deptno,salary) in(select deptno ,avg(salary) from empgroup by d

30、eptno);- -4.哪些员工的工资比他所在部门的平均工资高select main_select.ename,main_select.salary from emp main_selectwhere salary (select avg(salary) from emp sub_selectwhere sub_select.deptno =main_select.deptno);- -连接查询- -连接 外连接（左外连|右外连|全外连）交叉连接- -连接：取交集- -外连接：主（驱动）从（匹配）表- -连接：A inner join B on连接条件- - inner 可以省略- -5.查询

31、每个人的以及部门名称.select * from emp ;select * from dept ;select ename,dnamefrom emp e inner join dept d on e.deptno = d.deptno;insert into emp values(1012, 莫 小 贝 , 武 林 盟 主 ,25000,500,12,f72010-10-10,null,50);delete from emp where empno =1012;insert into dept values(60, 测试部,);- -6.查询部门人数大于等于3人的部门(部门名称)selec

32、t dname,count(empno)from emp e inner join dept d on 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,dnamefrom emp e left outer join dept d on e.deptno = d.deptno ;-

33、- 右外连select ename,dnamefrom dept d right outer join emp e on d.deptno = e.deptno ;- - 左外连 emp 从dept 主 select ename,dnamefrom dept d left outer join emp e on d.deptno = e.deptno ;- -右外连select ename,dnamefrom emp e right outer join dept d on e.deptno = d.deptno;- -创建薪水等级表create table sal_level(level i

34、nt ,-薪水等级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.查询员工以及薪水对应的等级

35、select ename,salary,level from emp e inner join sal_level sl on e.salary between sl.low_sal andsl.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 a代表下标，b代表几条数据select *from emp limit 0,3;