數(shù)據(jù)庫(kù)增刪改查

1、Mysql Oracle(甲骨文) 大型數(shù)據(jù)庫(kù) MySql 中小型數(shù)據(jù)庫(kù) DB2 SqlServer .Mysql的發(fā)展:瑞典的Mysql AB公司2008年 Sun公司(JAVA)2009年 Oracle 收購(gòu) sun公司IBM69億美元 sun Eclipse(日蝕)Oracle 74億美元 sunMysql的簡(jiǎn)單使用:1.登陸mysql數(shù)據(jù)庫(kù)win+r ->cmdmysql -uroot -p1234修改密碼：mysql> set password for rootlocalhost = password('1234');此處可能存在異常情況原因：a、未配置環(huán)

2、境變量 b、Mysql服務(wù)未開啟（net start mysql）2.對(duì)庫(kù)的操作a.查看所有的庫(kù)show databases;系統(tǒng)自帶庫(kù):information_schema mysql test b.創(chuàng)建庫(kù)create database day01;(不指定編碼,跟隨數(shù)據(jù)庫(kù)系統(tǒng)編碼)create database db1 default character set gbk;(指定編碼) 查看創(chuàng)建庫(kù)的語句:show create database 庫(kù)名. 修改庫(kù)的編碼:alter database day01 default character set utf8;c.刪除庫(kù)drop datab

3、ase 庫(kù)名.drop database day01;注意: 系統(tǒng)自帶的三個(gè)庫(kù)不能刪除.d.使用庫(kù)use db1;3.對(duì)表的操作表:二維關(guān)系表 有行有列的關(guān)系表.記錄:表中的一行數(shù)據(jù). 字段:表中的一列.常用的字段類型:字符串類型: varchar(長(zhǎng)度) 、char 數(shù)值類型: int(整數(shù)) float double(小數(shù))日期類型: datea.創(chuàng)建表員工表:員工號(hào) 姓名 性別 年齡 職位 薪水 入職日期create table emp(empno varchar(4),name varchar(30),sex varchar(5),age int(3),job varchar(30),

4、 salary int(5),hiredate date);b.查看所有的表show tables;c.查看建表語句show create table 表名.d.查看表結(jié)構(gòu)desc 表名.e.往表中插入數(shù)據(jù)e1.給表中所有的字段插入數(shù)據(jù)insert into emp(empno,name,sex,age,job,salary,hiredate)values('1001','zhangsan','m',22,'developer',10000,'2015-12-21');簡(jiǎn)寫形式:insert into emp va

5、lues('1002','lisi','m',23,'test',8000,'2015-10-10');e2.給表中部分字段插入數(shù)據(jù)insert into emp(empno,name,sex,age) values('1003','cuihua','w',18);解決插入中文問題：（eclipse中的設(shè)置）Connection URL:jdbc:mysql:/localhost:3306/test?useUnicode=true&characterEncod

6、ing=gbk插入中文:insert into emp values('1005','莫小貝','女',12,'武林盟主',20000,'2015-12-12');f.刪除數(shù)據(jù)delete from emp ;->刪除表中所有數(shù)據(jù)delete from emp where empno=1004;MyEclipse配置Mysql連接:1.切換到數(shù)據(jù)庫(kù)界面.2.在DBBroswer中右鍵選擇new3.配置連接:Driver Template: MySQL Connector/JDriver name: 隨便起名字

7、Connection URL: jdbc:mysql:/localhost:3306/test 本機(jī):localhostUser name:rootpassword:1234Mysql常見的錯(cuò)誤1.Can't create database 'xxx' database exists 不能創(chuàng)建xxx庫(kù),因?yàn)橐呀?jīng)存在2.Can't drop database 'xxx' database doesn't exist 不能刪除xxx庫(kù),因?yàn)橐呀?jīng)不存在- 創(chuàng)建庫(kù)create database search default character s

8、et gbk ;- 使用庫(kù)use search;- 創(chuàng)建表- 員工信息表 create table emp(empno int(4), - 員工編號(hào)ename varchar(30), - 員工姓名job varchar(30), - 職位salary int , - 工資bonus int, - 獎(jiǎng)金age int(3), - 年齡sex varchar(1), - 性別hiredate date, - 入職日期manager int(4), - 領(lǐng)導(dǎo)編號(hào)deptno int - 部門編號(hào) );- 部門表: create table dept(deptno int, - 部門編號(hào)dname

9、varchar(50),- 部門名稱loc varchar(80) - 部門位置 ); - 往emp表中插入數(shù)據(jù) 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,'張無忌','程序猿',120

10、00,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

11、,'黃蓉','攻城師',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)

12、;insert into emp values(1008,'韋小寶','實(shí)施',8500,null,29,'m','2013-04-02',1001,30);insert into emp values(1009,'陸無雙','實(shí)施',6000,100,26,'m','2014-12-12',1001,30);insert into emp values(1010,'黃飛鴻','打手',5000,100,20,'m',&

13、#39;2015-03-02',1001,null);- 給dept表插入數(shù)據(jù)insert into dept values(10,'研發(fā)部','北京');insert into dept values(20,'運(yùn)維部','上海');insert into dept values(30,'實(shí)施部','深圳');- 查詢use search ;- 1.查詢emp表中的所有數(shù)據(jù)select * from emp ;- * 通配符 通配了emp表中的所有列。select empno,ename,j

14、ob,salary,bonus,age, hiredate,sex ,manager,deptno from emp ;- select(選擇，篩選.) from(從.地方) - 執(zhí)行順序: from ->select - 2.查詢emp表中員工名字以及員工的薪水select ename,salary from emp ;- 查詢部分字段: select 字段名1,字段名2. from 表;- 3.查詢薪水大于10000的員工.- 查什么 員工->ename ->empno ->salary- 查詢條件是什么 salary >10000select empno,e

15、name,salary from emp where salary>10000;- select from where - 執(zhí)行順序: from -> where ->select - 4.查詢薪水大于等于10000并且小于等于18000的員工- 查什么 empno ename salary- 查詢的條件是什么 10000=<salary<=18000(數(shù)學(xué)中的寫法)- salary >=10000 and && salary <=18000select empno,ename,salary from emp where salary

16、>=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 s

17、alary 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

18、empwhere job in('程序猿','攻城師') and salary between 10000 and 20000 and sex = 'm'- 錯(cuò)誤演示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 empwh

19、ere job='攻城師' or job='程序猿' and salary between 10000 and 20000 and sex = 'm'- 7.查詢獎(jiǎng)金為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.查詢獎(jiǎng)金不是null的員工

20、 select ename ,bonus from emp where bonus is not null ;- 9.查詢所有員工的年薪 12*(salary+bonus) select ename, 12*(salary+bonus) from emp;- 與null做運(yùn)算，結(jié)果都為null- 空值處理函數(shù): ifnull() - ifnull(bonus,0): - 如果bonus為null，則處理成0- 如果bonus不為null,則不處理.按照bonus實(shí)際的值運(yùn)算.select ename,12*(salary+ifnull(bonus,0) from emp ;- 函數(shù): 單行函數(shù)

21、 組函數(shù)(聚合函數(shù)) - 單行函數(shù): 單行函數(shù)會(huì)對(duì)表中的每一條記錄進(jìn)行操作- 并且每一條記錄都會(huì)產(chǎn)生一個(gè)結(jié)果.- now(): 查詢當(dāng)前時(shí)間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,hireda

22、te from emp where year(hiredate) = 2012;- 11.查詢員工的工作年限select ename, year(now()-year(hiredate) from emp ;- 12.查詢實(shí)現(xiàn)如下結(jié)果: 我是xxx,我的職位是xxx- concat('我是',ename,',我的職位是',job ); 連接函數(shù).將給定的字段連接成字符串。select '我是' from emp ;select concat('我是',ename,'，我的職位是',job) description

23、from emp ;- 別名: 就是給查詢的結(jié)果中的列取另外一個(gè)名字- select ename as name from emp ; - name- select ename name from emp ; - as可以省略的- 13.通過查詢實(shí)現(xiàn): 10部門的薪水漲20% - 20部門的薪水漲10%- 30部門的薪水保持不變- 其他部門的薪水漲500- 多路分支 case when then else end- case 開始分支- when 判斷分支條件 then 執(zhí)行分支結(jié)果- when . then.- else 上述情況都不滿足，執(zhí)行else- end 結(jié)束分支select emp

24、no,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(升序 默認(rèn)的排序)- 14.查詢所有的員工，按照薪水降序排序select * from emp order by salary desc ;- 15.查詢所有的員工，按照薪水升序排序select * from emp or

25、der 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 - 執(zhí)行順序: from->where->select->order by - 17.查詢員工名字中帶有'張'的員工- like %:任意個(gè)任意字符- _:一個(gè)任意字符select ename from emp where ename like '%張%&

26、#39;- 18.查詢員工名字中第二個(gè)字為'無'的員工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. 性

27、別是男 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.統(tǒng)計(jì)表中總共有多少條記錄select * from emp ;- count():統(tǒng)計(jì)函數(shù) select count(empno) from emp ; - 10select count(ename)

28、 from emp ; - 10select count(*) from emp ; - 10select count(bonus) from emp ; - 9- null值不會(huì)被統(tǒng)計(jì)。- 組函數(shù)(聚合函數(shù)):- 組函數(shù)是對(duì)表中的一列數(shù)據(jù)進(jìn)行計(jì)算并返回單一的結(jié)果.- 21.查詢所有員工的薪水和.- sum()select sum(salary) from emp ;- 22.查詢所有員工的獎(jiǎng)金和.select sum(bonus) from emp ;select sum(ifnull(bonus,0) from emp ;- 23.查詢所有員工的平均薪水- avg()select avg(

29、salary) from emp ;- 24.查詢所有員工的平均獎(jiǎng)金.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 ; - 2

30、7.員工獎(jiǎng)金的最大值 select max(bonus) from emp ;- 28.員工獎(jiǎng)金的最小值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.查詢每個(gè)職位的薪水總和.select job , sum(salary) from emp group by job ;-

31、 分組(group by 分組字段)- 分組后,select后面只能跟組標(biāo)識(shí),組函數(shù),- 組標(biāo)識(shí)或者組函數(shù)的表達(dá)式.- 不能直接跟單行字段或者單行函數(shù).- 32.查詢每個(gè)部門的平均薪水select deptno,avg(salary) from emp group by deptno; - 33.查詢部門人數(shù)大于等于3人的部門.select deptno,count(empno) from emp group by deptno having count(empno) >=3;- 分組后，條件的過濾不能使用where ，要使用having- 分組前用where 分組后用having- 3

32、4.查詢職位不是程序猿的員工中 !=- 部門平均薪水大于或者等于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 - 執(zhí)行順序:from->where->group by->- having->select->order

33、by- 35.查詢薪水最高的員工- 1. 查詢最高薪水 select max(salary) from emp ;- 2. 查詢最高薪水對(duì)應(yīng)的員工- select ename from emp where salary = 最高薪水.select ename from emp where salary =(select max(salary) from emp );- 子查詢: 當(dāng)一個(gè)查詢的結(jié)果是另一個(gè)查詢的條件時(shí)，- 稱之為子查詢.- 非關(guān)聯(lián)子查詢 關(guān)聯(lián)子查詢- 非關(guān)聯(lián)子查詢:先執(zhí)行子查詢，子查詢的結(jié)果做為主查詢的- 條件,再執(zhí)行主查詢. 子查詢只執(zhí)行一次.- 36. 查詢最晚入職的員工se

34、lect 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)

35、 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!='小龍女'- 多列非關(guān)聯(lián)子查詢- 關(guān)聯(lián)子查詢- 40.哪些員工的薪水等于他本部門的平均薪水select ename from emp where (dep

36、tno,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,'財(cái)務(wù)部','天津');sel

37、ect * from emp ;select * from dept;- 子查詢: 一個(gè)查詢的結(jié)果作為另一個(gè)查詢的條件.- 非關(guān)聯(lián)子查詢 - 先執(zhí)行子查詢，子查詢的返回結(jié)果做為主查詢的條件.- 再執(zhí)行主查詢,子查詢只執(zhí)行一次.- 關(guān)聯(lián)子查詢- 采用循環(huán)的方式,查詢先重外部查詢開始,獲取一條記錄后，- 將其傳入內(nèi)部查詢.內(nèi)部查詢根據(jù)外部查詢傳入的記錄，- 可以查詢結(jié)果,然后將結(jié)果傳回外部查詢中. 外部查詢根據(jù)- 內(nèi)部查詢的結(jié)果判斷此條記錄是否滿足條件，若滿足條件- 則放入結(jié)果集，將來返回，否則放棄此條記錄.- 1.查詢emp表中工資最高的人.select * from emp ;- a.查詢最高

38、的工資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.查詢每個(gè)部門的平均工資select deptno ,avg(salary) from emp group

39、 by deptno ;- b.查詢個(gè)人的工資是否等于本部門平均工資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.d

40、eptno = main_select.deptno);- 連接查詢- 內(nèi)連接 外連接(左外連|右外連|全外連.) 交叉連接. - 內(nèi)連接: 取交集- 外連接: 主(驅(qū)動(dòng))從(匹配)表- 內(nèi)連接 : A inner join B on 連接條件- inner可以省略- 5.查詢每個(gè)人的姓名以及部門名稱.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